接昨天的导出

导入excel内容

对比昨天导出的内容增加了一行实体属性名称作为标题行，这样到转换为实体的时候才能找到对应的属性。

导入代码

public IActionResult InportExcel()

{

    var file = "D:\\a.xls";

    var excelHeper = new ExcelHelper();

    var dt = excelHeper.ExcelImport(file,2);

    var list = dt.ToList<Student>();

    return Content("");

}

excel文件路径可以通过上传控件上传到服务器再读取。

注意

如果你导入和发现bool类型不能正确读取请修改一下两个文件的两个地方：

DataTableExtensions的public static IEnumerable ToList(this DataTable dt) where T : class, new()方法增加一个bool类型判断

else if (p.PropertyType == typeof(bool))

{

    p.SetValue(ob, bool.Parse(row[p.Name].ToString()), null);

}

ExcelHelper的private DataTable ReadSheetToDataTable(int headerRowNo, ISheet sheet)方法替换为如下代码或增加一个bool类型判断

private DataTable ReadSheetToDataTable(int headerRowNo, ISheet sheet)

{

    var dt = new DataTable();

    IRow headerRow = sheet.GetRow(headerRowNo);

    int cellCount = headerRow.LastCellNum;

    for (int j = 0; j < cellCount; j++)

    {

        ICell cell = headerRow.GetCell(j);

        dt.Columns.Add(cell.ToString());

    }

    for (int i = (headerRowNo + 1); i <= sheet.LastRowNum; i++)

    {

        IRow row = sheet.GetRow(i);

        DataRow dataRow = dt.NewRow();

        for (int j = 0; j < cellCount; j++)

        {

            if (row.GetCell(j) == null)

            {

                continue;

            }

            ICell cell = row.GetCell(j);

            switch (cell.CellType)

            {

                case CellType.Unknown:

                case CellType.Error:

                    throw new Exception($"第{i + 1}行，列【{dt.Columns[j].ColumnName}】,单元格格式错误");

                    break;

                case CellType.Numeric:

                    if (DateUtil.IsCellDateFormatted(cell))

                    {

                        dataRow[j] = cell.DateCellValue;

                    }

                    else

                    {

                        dataRow[j] = cell.NumericCellValue;

                    }

                    break;

                case CellType.Blank:

                    dataRow[j] = "";

                    break;

                case CellType.Boolean:

                    dataRow[j] = cell.BooleanCellValue;

                    break;

                case CellType.Formula:

                    dataRow[j] = cell.CellFormula ;

                    break;

                case CellType.String:

                default:

                    dataRow[j] = cell.StringCellValue;

                    break;

            }

            //dataRow[j] = row.GetCell(j).ToString();

        }

        bool existsValue = false;

        foreach (DataColumn column in dt.Columns)

        {

            if (dataRow[column.ColumnName] == null || string.IsNullOrEmpty(dataRow[column.ColumnName].ToString()))

            {

                continue;

            }

            existsValue = true;

            break;

        }

        if (existsValue)

        {

            dt.Rows.Add(dataRow);

        }

    }

    return dt;

}