/// <summary>
/// 根据循环遍历Sheet获取数据
/// </summary>
/// <param name="wb"></param>
/// <param name="sheetName"></param>
private void AddVisiableSheetToDataSet(IWorkbook wb, string sheetName)
{
ISheet sh = wb.GetSheet(sheetName);
DateTime productionDate = DateTime.Now.Date.AddDays(10);
//List<ProductiveTaskListModel> listModels = new List<ProductiveTaskListModel>();
//创建列
DataTable dt = ConvertHelper.CreateDataTableFromModel <ProductiveTaskListModel>();
string previousBatch = string.Empty, previousProductionModel = string.Empty, previousHasSmallMaterial = string.Empty;
decimal previousQuantity = 0;
if (sh != null && sh.PhysicalNumberOfRows > 1)//包括表头大于1条记录
{
//获取第一行日期
string title = sh.GetRow(0).GetCell(0).StringCellValue;
string productionDateString = title.Substring(0, title.IndexOf("日")).Replace("年", "-").Replace("月", "-").Replace("日", "-");
try
{
productionDate = DateTime.Parse(productionDateString);
}
catch
{
new Exception("标题日期格式错误");
}
//具体数据获取sh.FirstRowNum 第一行为表头 从第二行开始获取;LastRowNum多加一行
for (int i = 2; i <= sh.LastRowNum + 1; i++)
{
IRow rowdata = sh.GetRow(i);
//ProductiveTaskListModel model = new ProductiveTaskListModel();
DataRow dr = dt.NewRow();
//空行跳过
if (rowdata == null || rowdata.Cells.Count == 0)
{
continue;
}
else
{
//批号有数据则，将当行获取的数据写入
if (GetCellValue(rowdata.GetCell(2, MissingCellPolicy.CREATE_NULL_AS_BLANK)).ToString().Length > 0)
{
previousProductionModel = GetCellValue(rowdata.GetCell(1, MissingCellPolicy.CREATE_NULL_AS_BLANK)).ToString();
previousBatch = GetCellValue(rowdata.GetCell(2, MissingCellPolicy.CREATE_NULL_AS_BLANK)).ToString();
previousQuantity = Convert.ToDecimal(GetCellValue(rowdata.GetCell(3, MissingCellPolicy.CREATE_NULL_AS_BLANK)).ToString());
previousHasSmallMaterial = GetCellValue(rowdata.GetCell(4, MissingCellPolicy.CREATE_NULL_AS_BLANK)).ToString();
dr["FitemName"] = previousProductionModel;
dr["FBatchNo"] = previousBatch;
dr["FQuantity"] = previousQuantity;
dr["FHasSmallMaterial"] = previousHasSmallMaterial;
}
else //没有批号，则取上一个值
{
dr["FitemName"] = previousProductionModel;
dr["FBatchNo"] = previousBatch;
dr["FQuantity"] = previousQuantity;
dr["FHasSmallMaterial"] = previousHasSmallMaterial;
}
dr["FProductionDate"] = productionDate;
dr["FType"] = sheetName;
dr["ID"] = GetCellValue(rowdata.GetCell(0, MissingCellPolicy.CREATE_NULL_AS_BLANK)).ToString();
dr["FPackage"] = GetCellValue(rowdata.GetCell(5, MissingCellPolicy.CREATE_NULL_AS_BLANK)).ToString();
dr["FBucketName"] = GetCellValue(rowdata.GetCell(6, MissingCellPolicy.CREATE_NULL_AS_BLANK)).ToString();
dr["FOrgID"] = GetCellValue(rowdata.GetCell(7, MissingCellPolicy.CREATE_NULL_AS_BLANK)).ToString();
dr["FLabel"] = GetCellValue(rowdata.GetCell(8, MissingCellPolicy.CREATE_NULL_AS_BLANK)).ToString();
dr["FNote"] = GetCellValue(rowdata.GetCell(9, MissingCellPolicy.CREATE_NULL_AS_BLANK)).ToString();
dr["FResidue"] = Convert.ToDecimal(GetCellValue(rowdata.GetCell(13, MissingCellPolicy.CREATE_NULL_AS_BLANK)).ToString());
dr["FBillNo"] = GetCellValue(rowdata.GetCell(14, MissingCellPolicy.CREATE_NULL_AS_BLANK)).ToString();
dr["CreateDate"] = DateTime.Now.Date;
dt.Rows.Add(dr);
}
}
}
// 将DataTable dt 导入到数据库 覆盖任务
new ProductiveTaskListDAL().ImportDataTableSync(dt);
}
/// <summary>
/// 转化为16字节字符串数组
/// </summary>
/// <returns></returns>
public override string ToString()
{
return(ConvertHelper.ByteArrayToHexString(ToArray()));
}
