using System;
|
using System.Collections.Generic;
|
using System.Linq;
|
using System.Text;
|
using System.Threading.Tasks;
|
using wcftest.orm;
|
using wcftest.EnumDefine;
|
using logtxtWrite;
|
using OfficeOpenXml.Drawing;
|
using OfficeOpenXml;
|
using OfficeOpenXml.Style;
|
using wcftest.sapEntity;
|
using System.IO;
|
using System.Drawing;
|
using Newtonsoft.Json;
|
using wcftest.EnumDefine.Sys;
|
|
|
namespace wcftest.sql
|
{
|
public static class baseData
|
{
|
public static List<T> findStorage<T>(string Name, string name, string No, string no, string tableName)
|
{
|
List<T> tableList = new List<T>();
|
try
|
{
|
using (dbModel mod = new dbModel())
|
{//SELECT * FROM [weicai_LA20001].[dbo].[tool] where toolName like '%工%' and toolNo like '%%'
|
string sql = "select * from [dbo].[" + tableName + "] where " + Name + " like '%" + name + "%' and " + No + " like '%" + no + "%'";
|
tableList = mod.Database.SqlQuery<T>(sql).ToList();
|
}
|
}
|
catch (Exception ex)
|
{
|
logtxt.txtWrite("类名：baseData/函数名：findStorage/ name:" + name.ToString() + "no:" + no.ToString()
|
+ "tableName" + tableName.ToString() + logtxt.oneRow(ex.ToString()), 2);
|
return null;
|
}
|
|
return tableList;
|
}
|
public static void analyse(wirteExcel excelInfo)
|
{
|
#region 验证
|
if (excelInfo.body.Count < 1)
|
{
|
logtxt.txtWrite("类名: baseData 方法名: analyse 缺料明细数量为空", 2);
|
return;
|
}
|
//DateTime.Now.ToString("yyyy_MM_dd_HH_mm_ss")
|
string path = @"D:\缺料分析报表\" + excelInfo.head.workCode + "#第" + excelInfo.head.cequenceQuantity + "套缺料分析表.xlsx";
|
if (!Directory.Exists(Path.GetDirectoryName(path))) Directory.CreateDirectory(Path.GetDirectoryName(path));
|
FileInfo newFile = new FileInfo(path);
|
if (newFile.Exists)
|
{
|
newFile.Delete();
|
newFile = new FileInfo(path);
|
}
|
#endregion
|
|
|
try
|
{
|
using (ExcelPackage package = new ExcelPackage(newFile))
|
{
|
ExcelWorksheet worksheet = package.Workbook.Worksheets.Add("PICK缺料分析");
|
|
Image tablelogo = Properties.Resources.siemensLogo;//获取图片资源
|
//设置表头格式
|
#region 设置表头格式
|
worksheet.Cells[1, 2, 1, 9].Merge = true;//合并单元格 起始行,起始列,目标行,目标列
|
//worksheet.Cells[1, 3].Style.Font.Bold = true;// 字体为粗体
|
worksheet.Row(1).Style.Font.Bold = true;// 字体为粗体;
|
worksheet.Row(2).Style.Font.Bold = true;// 字体为粗体;
|
worksheet.Row(3).Style.Font.Bold = true;// 字体为粗体;
|
worksheet.Cells[1, 3].Style.Font.Color.SetColor(Color.Black);//设置字体为黑色
|
|
worksheet.Cells[1, 2].Style.Font.Size = 16;//字体大小
|
worksheet.Cells[2, 1, 2, 8].Style.Font.Size = 12;//字体大小
|
worksheet.Cells[3, 1, 3, 8].Style.Font.Size = 10;//字体大小
|
worksheet.Row(1).Height = 40;
|
worksheet.Row(2).Height = 25;
|
worksheet.Cells[1, 2].Value = "广州西门子配变智能化工厂-立库区PICKLIST缺料报表";//给指定位置赋值
|
//表头
|
// worksheet.Cells[2, 1, 2, 2].Merge = true;//合并单元格 起始行,起始列,目标行,目标列
|
string productionCode = excelInfo.head.workCode;//生产订单号
|
string saleCode = excelInfo.head.saleCode;//销售单号
|
string groupCode = excelInfo.head.tatalQuantity.ToString();//总套数
|
string groupNumber = excelInfo.head.cequenceQuantity.ToString();//第几套
|
worksheet.Cells[2, 1].Value = "生产订单号:";
|
worksheet.Cells[2, 2].Value = productionCode;
|
worksheet.Cells[2, 3].Value = "跟踪号:";
|
worksheet.Cells[2, 4].Value = saleCode;
|
worksheet.Cells[2, 5].Value = "总套数:";
|
worksheet.Cells[2, 6].Value = groupCode;
|
worksheet.Cells[2, 7].Value = "第几套:";
|
worksheet.Cells[2, 8].Value = groupNumber;
|
for (int j = 0; j < 2; j++)
|
{
|
worksheet.Cells[1, 1, 3, 8].Style.Border.Bottom.Style = ExcelBorderStyle.Thin;
|
worksheet.Cells[1, 1, 3, 8].Style.Border.Left.Style = ExcelBorderStyle.Thin;
|
worksheet.Cells[1, 1, 3, 8].Style.Border.Right.Style = ExcelBorderStyle.Thin;
|
worksheet.Cells[1, 1, 3, 8].Style.Border.Top.Style = ExcelBorderStyle.Thin;
|
}
|
#endregion
|
|
#region 列名和图片
|
string[] tableTop = { "销售单号", "销售项号", "物料编号", "物料名称", "缺料数量", "暂存区数量", "立库区数量", "库存单位" };
|
for (int i = 0; i < 8; i++)
|
{
|
if (i < 5)
|
{
|
worksheet.Column(i + 1).Width = 15;//设置列宽
|
}
|
else
|
{
|
worksheet.Column(i + 1).Width = 12;
|
}
|
worksheet.Column(i + 1).Style.Font.Name = "宋体";//字体
|
worksheet.Column(i + 1).Style.HorizontalAlignment = ExcelHorizontalAlignment.Left;//水平居中
|
worksheet.Column(i + 1).Style.VerticalAlignment = ExcelVerticalAlignment.Center;//垂直居中
|
// worksheet.Cells[1, 1].Style.Border.BorderAround(ExcelBorderStyle.Thin, Color.FromArgb(191, 191, 191));
|
worksheet.Cells[3, i + 1].Value = tableTop[i];
|
}
|
// worksheet.Cells[1, 1, 20, 20].Style.Fill.PatternType = ExcelFillStyle.Solid;//
|
// worksheet.Cells.Style.WrapText = true;//自动换行
|
// worksheet.Cells[1, 20].Style.Fill.BackgroundColor.SetColor(Color.FromArgb(128,128,128));//设置背景颜色
|
ExcelPicture pic = worksheet.Drawings.AddPicture("logo", tablelogo);//插入图片
|
pic.SetSize(105, 53);//设置图片尺寸
|
pic.SetPosition(0, 0, 0, 0);//设置图片位置,以单元格为单位
|
#endregion
|
|
#region 明细
|
for (int k = 0; k < excelInfo.body.Count; k++)
|
{
|
worksheet.Cells[k + 4, 1, k + 4, 8].Style.Border.Top.Style = ExcelBorderStyle.Thin;
|
worksheet.Cells[k + 4, 1, k + 4, 8].Style.Border.Bottom.Style = ExcelBorderStyle.Thin;
|
worksheet.Cells[k + 4, 1, k + 4, 8].Style.Border.Left.Style = ExcelBorderStyle.Thin;
|
worksheet.Cells[k + 4, 1, k + 4, 8].Style.Border.Right.Style = ExcelBorderStyle.Thin;
|
|
worksheet.Cells[k + 4, 1].Value = excelInfo.body[k].saleCode;
|
worksheet.Cells[k + 4, 2].Value = excelInfo.body[k].saleCodeItem;
|
// worksheet.Cells[k + 4, 3].Value = excelInfo.body[k].trackNumber;
|
worksheet.Cells[k + 4, 3].Value = excelInfo.body[k].materalCode;
|
worksheet.Cells[k + 4, 4].Value = excelInfo.body[k].materalName;
|
worksheet.Cells[k + 4, 5].Value = excelInfo.body[k].lackQuantity;
|
worksheet.Cells[k + 4, 6].Value = excelInfo.body[k].tempoarayQuantity;
|
worksheet.Cells[k + 4, 7].Value = excelInfo.body[k].stockQuantity;
|
worksheet.Cells[k + 4, 8].Value = excelInfo.body[k].stockUnit;
|
}
|
#endregion
|
|
package.Save();
|
worksheet.PrinterSettings.Orientation = eOrientation.Portrait;
|
}
|
}
|
catch (Exception ex)
|
{
|
logtxt.txtWrite("类名: baseData 方法名: analyse 产生异常" + JsonConvert.SerializeObject(excelInfo) + ex.Message, 2);
|
}
|
|
}
|
|
}
|
}
|
