使用EPPlus读写excel文件并保存

之前在网上看到大部分的意见都认为“对于Excel 97-2003格式，还是用NPOI最好；而对于2007(xlsx)以上版本，可以使用EPPlus”。然后在实际操作中有发现，使用NPOI的话，读取xlsx文件会出点问题，但是写入的话，个人感觉EPPlus语言简洁美观点（此地仅个人感官，实际使用以个人为主）

关于excel读写，网上还找的别的：

  1. NPOI(http://npoi.codeplex.com/)
  2. MyXls(http://sourceforge.net/projects/myxls/)
  3. Koogra(http://sourceforge.net/projects/koogra/)
  4. ExcelLibrary(http://code.google.com/p/excellibrary/)
  5. ExcelPackage(http://excelpackage.codeplex.com/)
  6. EPPlus(http://epplus.codeplex.com/)
  7. LinqToExcel(http://code.google.com/p/linqtoexcel/)

此地暂时仅介绍EPPlus

EPPlus读取excel：

using (ExcelPackage package = new ExcelPackage(new FileStream(path, FileMode.Open)))
{
    for (int i = 1; i <= package.Workbook.Worksheets.Count; ++i)
    {
        ExcelWorksheet sheet = package.Workbook.Worksheets[i];
        for (int j = sheet.Dimension.Start.Column, k = sheet.Dimension.End.Column; j <= k; j++)
        {
            for (int m = sheet.Dimension.Start.Row, n = sheet.Dimension.End.Row; m <= n; m++)
            {
                string str = GetValue(sheet, m, j);
                if (str != null)
                {
                    // do something
                }
            }
        }
    }
}

EPPlus写入excel：

using (ExcelPackage package = new ExcelPackage())
{
    ExcelWorksheet sheet = package.Workbook.Worksheets.Add("Sheet1");
    sheet.Cells[1, 1].Value = "1";
    sheet.Cells[1, 2].Value = "2";
    sheet.Cells[1, 3].Value = "3";
    sheet.Cells[1, 4].Value = "4";
    sheet.Cells[1, 5].Value = "5";
    sheet.Cells[1, 6].Value = "6";
    using (Stream stream = new FileStream(path, FileMode.Create))
    {
        package.SaveAs(stream);
    }
}

合并单元格：

worksheet.Cells[fromRow, fromCol, toRow, toCol].Merge = true;

excel样式设置:

//表头样式（后面三个感觉没起作用）
worksheet.Row(1).Height = 30;  //设置高度
worksheet.Row(1).Style.Font.Bold = true; //字体加粗
worksheet.Row(1).Style.Font.Size = 11;  //字体大小
worksheet.Row(1).Style.Font.Name = "微软雅黑";  //字体
worksheet.Column(columnIndex).Width = 30;  //设置列宽

worksheet.DefaultRowHeight = 27;//设置单元格默认行高
worksheet.Cells.Style.ShrinkToFit = true;//单元格自动适应大小
worksheet.Cells.Style.WrapText = true;//自动换行
worksheet.Cells.Style.Border.BorderAround(ExcelBorderStyle.Thin, Color.FromArgb(191, 191, 191));//设置单元格所有边框
worksheet.Cells.Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;//设置单元格水平居中
worksheet.Cells.Style.VerticalAlignment = ExcelVerticalAlignment.Center;//设置单元格垂直居中

下面提供完整demo

　　　　　/// <summary>
        /// 导出供应商考核表
        /// </summary>
        /// <returns></returns>
        public IActionResult ExportSupplierAssessment(RaterType type)
        {
            ResultResponse<string> result = new ResultResponse<string>();

            string webRootPath = _hostingEnvironment.WebRootPath;

            //考核表文件夹
            string folder_download = ConfigurationHelper.GetSingleNode("Download_Assessment");
            //虚拟目录
            string appUrl = ConfigurationHelper.GetSingleNode("File_AppUrl");

            try
            {
                //生成考核表文件夹
                var folderPath_download = Path.Combine(webRootPath, folder_download);
                if (!System.IO.Directory.Exists(folderPath_download))
                {
                    System.IO.Directory.CreateDirectory(folderPath_download);
                }

                string fileName_download = $"供应商考核{EnumExtension.GetDescription(type)}打分表" + DateTime.UtcNow.AddHours(8).ToString("yyyyMMddHHmmss") + ".xls";
                FileInfo file = new FileInfo(Path.Combine(webRootPath, folder_download, fileName_download));
                using (ExcelPackage package = new ExcelPackage(file))
                {
                    // 添加worksheet
                    ExcelWorksheet worksheet = package.Workbook.Worksheets.Add("供应商考核表");
                    var columnWidth = 10;

                    //添加头
                    worksheet.Cells[1, 1].Value = "类别";
                    worksheet.Cells[1, 2].Value = "供应商类别";
                    worksheet.Cells[1, 3].Value = "供应商名称";
                    //worksheet.Cells[1, 3].Value = "平均得分";
                    worksheet.Cells[1, 4].Value = "序号";
                    worksheet.Cells[1, 5].Value = "项目名称";

                    var endCells = 10;
                    //学校评分
                    if (type == RaterType.School)
                    {
                        worksheet.Cells[1, 6].Value = "书面考核所占比例";
                        worksheet.Cells[1, 7].Value = "书面考核分";
                        worksheet.Cells[1, 8].Value = "学校考核所占比例";
                        worksheet.Cells[1, 9].Value = "学校评分";
                        worksheet.Cells[1, 10].Value = "备注";
                    }
                    //监理评分
                    else if (type == RaterType.Supervisor)
                    {
                        worksheet.Cells[1, 6].Value = "监理考核所占比例";
                        worksheet.Cells[1, 7].Value = "监理评分";
                        worksheet.Cells[1, 8].Value = "备注";
                        endCells = 8;
                    }
                    else
                    {
                        worksheet.Cells[1, 6].Value = "校产站考核所占比例";
                        worksheet.Cells[1, 7].Value = "计划部评分";
                        worksheet.Cells[1, 8].Value = "施工部评分";
                        worksheet.Cells[1, 9].Value = "设备部评分";
                        worksheet.Cells[1, 10].Value = "备注";
                    }
                    //表头样式（后面三个貌似不起作用）
                    //worksheet.Row(1).Height = 30;  //设置高度
                    //worksheet.Row(1).Style.Font.Bold = true; //字体加粗
                    //worksheet.Row(1).Style.Font.Size = 11;  //字体大小
                    //worksheet.Row(1).Style.Font.Name = "微软雅黑";  //字体


                    //设置列宽
                    for (int i = 1; i < 11; i++)
                    {
                        worksheet.Column(i).Width = i == 3 || i == 5 ? 30 : columnWidth;
                    }
                    worksheet.DefaultRowHeight = 27;//设置单元格默认行高
                    worksheet.Cells.Style.ShrinkToFit = true;//单元格自动适应大小
                    worksheet.Cells.Style.WrapText = true;//自动换行
                    worksheet.Cells.Style.Border.BorderAround(ExcelBorderStyle.Thin, Color.FromArgb(191, 191, 191));//设置单元格所有边框
                    worksheet.Cells.Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;//设置单元格水平居中
                    worksheet.Cells.Style.VerticalAlignment = ExcelVerticalAlignment.Center;//设置单元格垂直居中

                    //从第一行之后开始写
                    var row = 1;

                    //添加值
                    var supplierScoreList = _supplierScoreAppService.GetSupplierScoreListByWait().Data;
                    if (supplierScoreList != null)
                    {
                        foreach (var score in supplierScoreList)
                        {
                            var assessmentList = _supplierAssessmentAppService.GetSupplierAssessmentListBySupplierScoreUid(score.Uid).Data;
                            if (assessmentList != null)
                            {
                                var index = 0;   //项目序号
                                foreach (var assessment in assessmentList)
                                {
                                    index++;
                                    row++;

                                    worksheet.Row(row).CustomHeight = true;//自动调整行高

                                    //公共部分
                                    worksheet.Cells[$"A{row}"].Value = EnumExtension.GetDescription(score.SupplierType);
                                    worksheet.Cells[$"B{row}"].Value = score.SupplierCategoryName;
                                    worksheet.Cells[$"C{row}"].Value = score.SupplierName;
                                    worksheet.Cells[$"D{row}"].Value = index;
                                    worksheet.Cells[$"E{row}"].Value = assessment.ProjectName;

                                    switch (type)
                                    {
                                        case RaterType.School:
                                            worksheet.Cells[$"F{row}"].Value = assessment.WrittenRatio;
                                            worksheet.Cells[$"G{row}"].Value = assessment.WrittenScore;
                                            worksheet.Cells[$"H{row}"].Value = assessment.SchoolRatio;
                                            worksheet.Cells[$"I{row}"].Value = assessment.SchoolScore;
                                            worksheet.Cells[$"G{row}"].Value = assessment.Remark;
                                            break;
                                        case RaterType.Supervisor:
                                            worksheet.Cells[$"F{row}"].Value = assessment.SupervisorRatio;
                                            worksheet.Cells[$"G{row}"].Value = assessment.SupervisorScore;
                                            worksheet.Cells[$"H{row}"].Value = assessment.Remark;
                                            break;
                                        case RaterType.Inside:
                                            worksheet.Cells[$"F{row}"].Value = assessment.InsideRatio;
                                            worksheet.Cells[$"G{row}"].Value = assessment.PlanScore;
                                            worksheet.Cells[$"H{row}"].Value = assessment.ConstructionScore;
                                            worksheet.Cells[$"I{row}"].Value = assessment.EquipmentScore;
                                            worksheet.Cells[$"G{row}"].Value = assessment.Remark;
                                            break;
                                        default:
                                            break;
                                    }
                                }
                                if (assessmentList.Count > 1)
                                {
                                    //合并单元格
                                    var startRow = row - assessmentList.Count() + 1;
                                    worksheet.Cells[startRow, 1, row, 1].Merge = true;  //类型
                                    worksheet.Cells[startRow, 2, row, 2].Merge = true;  //供应商类别
                                    worksheet.Cells[startRow, 3, row, 3].Merge = true;  //供应商名称
                                }
                            }
                        }
                    }

                    package.Save();
                }
                //写入文件
                File(fileName_download, "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet", true);

                //前台访问路径
                var filePath = $"{Request.Host}/" + appUrl + $"/{folder_download}/{fileName_download}";
                result.Data = filePath;
            }
            catch (Exception ex)
            {
                NullLogger.Instance.Info(ex.Message, ex);
            }
            return Ok(result);
        }
【推广】 免费学中医，健康全家人
原文地址：https://www.cnblogs.com/xiaoxiaomini/p/15138750.html