/// <summary>
/// 打开EXCEL并获取其内容
/// </summary>
/// <param name="reporttype">导入EXCEL时的类型(0:批量成本报表功能使用 1:BOM物料明细使用 2:毛利润报表使用)</param>
/// <param name="fileAddress"></param>
/// <returns></returns>
private DataTable OpenExcelToDataTable(string reporttype, string fileAddress)
{
IWorkbook wk;
//根据reporttype判断所导入的列数
int colnum;
//定义TEMPDT
var dt = new DataTable();
//创建表标题-根据reporttype不同而改变
switch (reporttype)
{
case "0":
dt = dbList.ImportExcelTempdt();
break;
case "1":
dt = dbList.ImportBomExcelTempdt();
break;
default:
dt = dbList.ImportProfitExcelTempdt();
break;
}
//dt = reporttype == "0" ? dbList.ImportExcelTempdt() : dbList.ImportBomExcelTempdt();
using (var fsRead = File.OpenRead(fileAddress))
{
wk = new XSSFWorkbook(fsRead);
//获取第一个sheet
var sheet = wk.GetSheetAt(0);
//获取第一行
//var hearRow = sheet.GetRow(0);
//定义列数
colnum = reporttype == "0" ? 6 : 2;
//创建完标题后,开始从第二行起读取对应列的值
for (var r = 1; r <= sheet.LastRowNum; r++)
{
var result = false;
var dr = dt.NewRow();
var row = sheet.GetRow(r);
if (row == null)
{
continue;
}
for (var j = 0; j < colnum; j++)
{
//循环获取行中的单元格
var cell = row.GetCell(j);
var cellValue = GetCellValue(cell);
if (cellValue == string.Empty)
{
continue;
}
else
{
dr[j] = cellValue;
}
//全为空就不取
if (dr[j].ToString() != "")
{
result = true;
}
}
if (result)
{
//把每行增加到DataTable
dt.Rows.Add(dr);
}
}
}
return(dt);
}
