private static DataTable ReadWeekData(FileInfo xlsxFile)
{
var dtNew = NPOIExcelHelper.ReadExcel(xlsxFile, true);
if (dtNew == null)
{
return(null);
}
//convert
dtNew.Columns.Add("UpdateTime", typeof(string));
dtNew.Columns["HC FCST"].ColumnName = "HC_FCST";
dtNew.Columns["HC Actual"].ColumnName = "HC_Actual";
dtNew.Columns["HC Support"].ColumnName = "HC_Support";
dtNew.Columns["HC Utilization"].ColumnName = "HC_Utilization";
dtNew.Columns["Case ID in"].ColumnName = "Case_ID_in";
dtNew.Columns["Case ID out"].ColumnName = "Case_ID_out";
dtNew.Columns["Pallet In"].ColumnName = "Pallet_In";
dtNew.Columns["Pallet Out"].ColumnName = "Pallet_Out";
dtNew.Columns["Jobs Rec"].ColumnName = "Jobs_Rec";
dtNew.Columns["Jobs Issue"].ColumnName = "Jobs_Issue";
dtNew.Columns["Reel ID Rec"].ColumnName = "Reel_ID_Rec";
return(dtNew);
}
public void ImportTest()
{
var path1 = Path.Combine("Source", "test1.xlsx");
var path2 = Path.Combine("Source", "test2.xls");
var ds1 = NPOIExcelHelper.ReadExcel(path1);
var ds2 = NPOIExcelHelper.ReadExcel(path2);
var tempTable = ds2.Tables["订单信息"];
}
public IList <BatchTableModelDto> UploadExcel([FromForm] TestFile testFile)
{
var ds = NPOIExcelHelper.ReadExcel(testFile.File);
var tempTable = ds.Tables["订单信息"];
var list = tempTable.ConvertToModel <BatchTableModelDto>();
var dt = list.ListToDataTable();
return(list);
}
private List <BatchTableModelDto> GetExcelData(Stream fileStream, string fileName)
{
List <BatchTableModelDto> list;
try
{
var ds = NPOIExcelHelper.ReadExcel(fileStream, fileName);
if (ds == null)
{
throw new Exception("读取数据失败");
}
if (ds.Tables.Count <= 0)
{
throw new Exception("Excel必须包含一个表");
}
var isHave = ds.Tables.Cast <DataTable>().Any(x => x.TableName == "订单信息");
if (!isHave)
{
throw new Exception("Excel格式有误(请检查Sheet名称是否为“订单信息”)");
}
var dt = ds.Tables["订单信息"];
if (dt == null)
{
throw new Exception("未读取到订单数据");
}
if (dt.Rows.Count < 1)
{
throw new Exception("表必须包含数据");
}
list = dt.ConvertToModel <BatchTableModelDto>().ToList();
list.ForEach(x =>
{
x.是否挑单 = x.是否挑单.IsNullOrEmpty() ? "是" : x.是否挑单;
x.是否超证 = x.是否超证.IsNullOrEmpty() ? "否" : x.是否超证;
});
}
catch (Exception ex)
{
_logger.ErrorFormat("读取违章信息Excel异常【WriteError】,ex【{0}】，message【{1}】", ex, ex.Message);
throw ex;
}
return(list);
}
private DataTable ReadUpload(FileInfo xlsxFile)
{
var dt = NPOIExcelHelper.ReadExcel(xlsxFile);
if (dt == null)
{
return(null);
}
//convert
var dtNew = new DataTable(TBL_UPLOAD);
dtNew.Columns.Add("id", typeof(int));
dtNew.Columns.Add("CommodityCode", typeof(string));
dtNew.Columns.Add("CostItemNumber", typeof(string));
dtNew.Columns.Add("CommodityCodeDescription", typeof(string));
var dtUploadTime = DateTime.Now;
foreach (DataRow row in dt.Rows)
{
var drNew = dtNew.NewRow();
drNew["CommodityCode"] = row["Commodity Code"];
var sKey = row["Cost Item Number"] as string;
if (sKey != null)
{
sKey = sKey.Trim();
}
//skip null
if (string.IsNullOrEmpty(sKey))
{
continue;
}
drNew["CostItemNumber"] = sKey;
drNew["CommodityCodeDescription"] = row["Commodity Code Description"];
dtNew.Rows.Add(drNew);
}
return(dtNew);
}
private static DataTable ReadHCData(FileInfo xlsxFile)
{
var dtNew = NPOIExcelHelper.ReadExcel(xlsxFile, true);
if (dtNew == null)
{
return(null);
}
//convert
dtNew.Columns.Add("UpdateTime", typeof(string));
dtNew.Columns["Warehouse"].ColumnName = "Warehouse";
dtNew.Columns["System Clerk"].ColumnName = "System_Clerk";
dtNew.Columns["Inventory Control"].ColumnName = "Inventory_Control";
dtNew.Columns["RTV & Scrap"].ColumnName = "RTV_Scrap";
dtNew.Columns["Receiving"].ColumnName = "Receiving";
dtNew.Columns["Shipping"].ColumnName = "Shipping";
dtNew.Columns["Forklift Driver"].ColumnName = "Forklift_Driver";
dtNew.Columns["Total"].ColumnName = "Total";
return(dtNew);
}