.NetCore WebAPI 導入、導出Excel文件

導入

思路：上傳Excel文件，使用MemoryStream 在內存中加載，使用NPOI讀取內容到Model類中。

/// <summary>
/// 導入Excel文件
/// </summary>
/// <param name="excelFile"></param>
/// <returns></returns>
[HttpPost]
public IActionResult UploadUserInfo(IFormFile excelFile)
{
    try
    {
        var postFile = Request.Form.Files[0];
        string extName = Path.GetExtension(postFile.FileName);
        if (!new string[] { ".xls", ".xlsx" }.Contains(extName))
        {
            return Ok(new
                      {
                          error = 1,
                          msg = "必須是Excel文件"
                      });
        }

        MemoryStream ms = new MemoryStream();
        postFile.CopyTo(ms);
        ms.Position = 0;
        IWorkbook wb = null;
        if (extName.ToLower().Equals(".xls")) // 97-2003版本
        {
            wb = new HSSFWorkbook(ms);
        }
        else
        {
            wb = new XSSFWorkbook(ms); // 2007以上版本
        }

        ISheet sheet = wb.GetSheetAt(0);

        //總行數（0開始）
        int totalRow = sheet.LastRowNum;
        // 總列數（1開始）
        int totalColumn = sheet.GetRow(0).LastCellNum;

        List<Stu> stuList = new();
        for (int i = 1; i <= totalRow; i++)
        {

            IRow row = sheet.GetRow(i);
            // 判定第5列的值是不是日期，日期的值類型可以按日期來讀，也可以用數據的方式來讀
            var isDate = DateUtil.IsCellDateFormatted(row.GetCell(4));                   

            string StuName = row.GetCell(0).StringCellValue;
            int Sex = row.GetCell(1).StringCellValue == "男" ? 0 : 1;
            string Phone = ((long)row.GetCell(2).NumericCellValue).ToString();
            int CId = (int)row.GetCell(3).NumericCellValue;
            DateTime InDate = row.GetCell(4).DateCellValue;
            decimal JF = (decimal)row.GetCell(5).NumericCellValue;

            // 第6列有可能是空的
            string Pic = "";
            if(row.GetCell(6) != null)
            {
                CellType type = row.GetCell(6).CellType;
                if (type != CellType.Blank)
                {
                    Pic = row.GetCell(6).StringCellValue;
                }
            }

            int State = (int)row.GetCell(7).NumericCellValue;

            var stu = new Stu
            {
                StuName = StuName,
                Sex = Sex,
                Phone = Phone,
                CId = CId,
                InDate = InDate,
                JF = JF,
                Pic =Pic,
                State = State,
                IsOk = true,
            };
            stuList.Add(stu);
        }
        db.Stu.AddRange(stuList);
        db.SaveChanges();
        wb.Close();
        return Ok(new
                  {
                      error = 0,
                      importCount = stuList.Count,
                      msg = ""
                  });
    }
    catch (Exception)
    {
        throw;
    }
}

導出

導出后端

思路：使用NPOI使用 IWorkBook ，一行一行寫入要導出數據，最終返回 FileContentResult

默認（不使用模板）

/// <summary>
/// 導出所有的信息為Excel
/// </summary>
/// <returns></returns>
[HttpGet]
public IActionResult ExportExcel()
{
    try
    {
        var list = db.Stu.Where(s => s.IsOk).ToList();
        
        IWorkbook wb = new XSSFWorkbook();
        ISheet sheet = wb.CreateSheet("Sheet1");
        // 第一行 標題
        IRow row = sheet.CreateRow(0);
        row.CreateCell(0).SetCellValue("姓名");
        row.CreateCell(1).SetCellValue("性別");
        row.CreateCell(2).SetCellValue("手機號碼");
        row.CreateCell(3).SetCellValue("學院");
        row.CreateCell(4).SetCellValue("入學日期");
        row.CreateCell(5).SetCellValue("綜合積分");
        row.CreateCell(6).SetCellValue("照片");
        row.CreateCell(7).SetCellValue("狀態");
        // 第二行 寫數據
        int i = 1;
        foreach (var item in list)
        {
            row = sheet.CreateRow(i);
            row.CreateCell(0).SetCellValue(item.StuName);
            row.CreateCell(1).SetCellValue(item.Sex == 0 ? "男" : "女");
            row.CreateCell(2).SetCellValue(double.Parse(item.Phone));
            row.CreateCell(3).SetCellValue(item.CId);

            // 日期格式的導出
            ICell cell = row.CreateCell(4);
            ICellStyle style = wb.CreateCellStyle();
            IDataFormat format = wb.CreateDataFormat();
            style.DataFormat = format.GetFormat("yyyy-MM-dd");
            cell.CellStyle = style;
            cell.SetCellValue(DateTime.Parse(item.InDate.ToString("yyyy-MM-dd")));

            row.CreateCell(5).SetCellValue((double)item.JF);
            row.CreateCell(6).SetCellValue(item.Pic);
            row.CreateCell(7).SetCellValue(item.State);
            i++;
        }
        // 寫 WorkBook信息到 內存流中
        byte[] buffer = null;
        using (MemoryStream ms = new MemoryStream())
        {
            wb.Write(ms);
            buffer = ms.ToArray();
        }
         // .xlsx文件對應的Mime信息
         var mime = new FileExtensionContentTypeProvider().Mappings[".xlsx"];
        return File(buffer, mime, "學生信息.xlsx");

    }
    catch (Exception)
    {
        throw;
    }
}

使用模板

/// <summary>
/// 導出Excel（使用模板）
/// </summary>
/// <returns></returns>
[HttpGet]
public IActionResult ExportExcelByTemplate()
{
    try
    {
        IWorkbook wb = null;
        var template = Directory.GetCurrentDirectory() + @"\wwwroot\Template\template.xlsx";
        // 按模板內容創建 IWorkbook
        using(FileStream fs = new FileStream(template, FileMode.OpenOrCreate))
        {
            wb = new XSSFWorkbook(fs);
        }

        var list = db.Stu.Where(s => s.IsOk).ToList();

        ISheet sheet = wb.GetSheetAt(0);
        int i = 1;
        IRow row = null;
        foreach (var item in list)
        {
            row = sheet.CreateRow(i);
            row.CreateCell(0).SetCellValue(item.StuName);
            row.CreateCell(1).SetCellValue(item.Sex == 0 ? "男" : "女");
            row.CreateCell(2).SetCellValue(double.Parse(item.Phone));
            row.CreateCell(3).SetCellValue(item.CId);

            // 日期格式的導出
            ICell cell = row.CreateCell(4);
            ICellStyle style = wb.CreateCellStyle();
            IDataFormat format = wb.CreateDataFormat();
            style.DataFormat = format.GetFormat("yyyy-MM-dd");
            cell.CellStyle = style;
            cell.SetCellValue(DateTime.Parse(item.InDate.ToString("yyyy-MM-dd")));

            row.CreateCell(5).SetCellValue((double)item.JF);
            row.CreateCell(6).SetCellValue(item.Pic);
            row.CreateCell(7).SetCellValue(item.State);
            i++;
        }

        byte[] buffer = null;
        using (MemoryStream ms = new MemoryStream())
        {
            wb.Write(ms);
            buffer = ms.ToArray();
        }
         // .xlsx文件對應的Mime信息
         var mime = new FileExtensionContentTypeProvider().Mappings[".xlsx"];
        return File(buffer, mime, "學生信息.xlsx");

    }
    catch (Exception)
    {
        throw;
    }
}

導出前端（調用）

使用 axios 調用

// 導出為Excel文件(.xlsx)
// 簡單方法
    exportExce() {
      let url =
        "http://localhost:23474/api/Stu/ExportExcel?page=1&size=4&bId=0"; //可以在路徑中傳遞參數
      window.location.href = url;
    },
// 標準方法
exportExcel() {
    this.axios
        .get("http://localhost:23474/api/Stu/ExportExcel", {
        responseType: "blob",
    })
        .then((res) => {
        var blob = new Blob([res.data], {
            type: "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet",
        });
        var a = document.createElement("a"); // js創建一個a標簽
        var href = window.URL.createObjectURL(blob); // 文檔流轉化成Base64
        a.href = href;
        a.download = "學生數據.xlsx"; // 下載后文件名
        document.body.appendChild(a);
        a.click(); // 點擊下載
        document.body.removeChild(a); // 下載完成移除元素
        window.URL.revokeObjectURL(href);
    });
},