asp.net core導入excel

接昨天的導出

導入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

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;
}