private DataTable OpenExcelToDataTable(string fileAddress)
{
IWorkbook wk;
//创建表标题
var dt = dtList.Get_Importdt();
using (var fsRead = File.OpenRead(fileAddress))
{
wk = new XSSFWorkbook(fsRead);
//获取第一个sheet
var sheet = wk.GetSheetAt(0);
//获取第一行
//var hearRow = sheet.GetRow(0);
//创建完标题后,开始从第二行起读取对应列的值
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 < row.Cells.Count; 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 == true)
{
//把每行增加到DataTable
dt.Rows.Add(dr);
}
}
}
return(dt);
}
private DataTable OpenExcelToDataTable(string fileAddress, int selectid)
{
IWorkbook wk;
//定义列ID
var colid = 0;
//定义ID变量
var id = 1;
//定义内部色号
var code = string.Empty;
//定义版本日期
var confirmdt = string.Empty;
//定义层
var layer = 0;
//创建表标题
var dt = new DataTable();
dt = selectid == 3 ? dtList.Get_ImportHdt() : dtList.Get_Importdt();
using (var fsRead = File.OpenRead(fileAddress))
{
wk = new XSSFWorkbook(fsRead);
//获取第一个sheet
var sheet = wk.GetSheetAt(0);
//获取第一行
//var hearRow = sheet.GetRow(0);
//创建完标题后,开始从第二行起读取对应列的值
for (var r = 1; r <= sheet.LastRowNum; r++)
{
var result = false;
var dr = dt.NewRow();
//获取当前行(注:只能获取行中有值的项,为空的项不能获取;即row.Cells.Count得出的总列数就只会汇总"有值的列"之和)
var row = sheet.GetRow(r);
if (row == null)
{
continue;
}
//读取每列(固定了共列值37) (固定列为34列=>新导入模板使用 add date:20191009)
colid = selectid == 3 ? 17 : 35;
for (var j = 0; j < colid /*37*//*row.Cells.Count*/; j++)
{
if (j == 0 && selectid != 3)
{
dr[0] = id;
}
else
{
//循环获取行中的单元格
var cell = row.GetCell(j);
var cellValue = GetCellValue(cell);
if (cellValue == string.Empty)
{
if (j == 4 && selectid == 3)
{
dr[j] = code;
}
else if (j == 9 && selectid == 3)
{
dr[j] = confirmdt;
}
else if (j == 10 && selectid == 3)
{
dr[j] = layer;
}
else
{
continue;
}
}
else
{
dr[j] = cellValue;
if (j == 4 && selectid == 3)
{
code = Convert.ToString(dr[j]);
}
else if (j == 9 && selectid == 3)
{
confirmdt = Convert.ToString(dr[j]);
}
else if (j == 10 && selectid == 3)
{
layer = Convert.ToInt32(dr[j]);
}
}
//全为空就不取
if (dr[j].ToString() != "")
{
result = true;
}
}
}
if (result == true)
{
//把每行增加到DataTable
dt.Rows.Add(dr);
}
//自增ID值
if (selectid != 3)
{
id++;
}
}
}
return(dt);
}