我通过以下代码通过C#导入Excel文件。但是当我检查数据库时,我看到添加了一个空行。
您能帮我更改此代码以删除此空行吗?
public static class ExcelTools
{
static DataSet result = new DataSet();
public static DataTable ExcelToDataTable(string path)
{
var pck = new OfficeOpenXml.ExcelPackage();
pck.Load(File.OpenRead(path));
var ws = pck.Workbook.Worksheets.First();
DataTable tbl = new DataTable();
bool hasHeader = true;
foreach (var firstRowCell in ws.Cells[1, 1, 1, ws.Dimension.End.Column])
{
tbl.Columns.Add(hasHeader ? firstRowCell.Text : string.Format("Column {0}", firstRowCell.Start.Column));
}
var startRow = hasHeader ? 2 : 1;
for (var rowNum = startRow; rowNum <= ws.Dimension.End.Row; rowNum++)
{
var wsRow = ws.Cells[rowNum, 1, rowNum, ws.Dimension.End.Column];
var row = tbl.NewRow();
foreach (var cell in wsRow)
{
row[cell.Start.Column - 1] = cell.Text;
}
tbl.Rows.Add(row);
}
pck.Dispose();
tbl.TableName = System.IO.Path.GetFileNameWithoutExtension(path);
return tbl;
}
}
最佳答案
您可能被最后一个NULL
行所迷惑:
允许您手动添加一行。这意味着它不在您的数据库中。可以肯定的是,您可以计算空行:
select sum(case when [COL] is null then 1 else 0 end) count_nulls
from [TABLE]