使用EPPlus读写excel文件并保存
之前在网上看到大部分的意见都认为“对于Excel 97-2003格式，还是用NPOI最好；而对于2007(xlsx)以上版本，可以使用EPPlus”。然后在实际操作中有发现，使用NPOI的话，读取xlsx文件会出点问题，但是写入的话，个人感觉EPPlus语言简洁美观点（此地仅个人感官，实际使用以个人为主）
关于excel读写，网上还找的别的：
- NPOI(http://npoi.codeplex.com/)
- MyXls(http://sourceforge.net/projects/myxls/)
- Koogra(http://sourceforge.net/projects/koogra/)
- ExcelLibrary(http://code.google.com/p/excellibrary/)
- ExcelPackage(http://excelpackage.codeplex.com/)
- EPPlus(http://epplus.codeplex.com/)
- 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;//设置单元格垂直居中
//针对单行设置样式 Cells[row,col]
worksheet.Cells[i, 1].Style.HorizontalAlignment = ExcelHorizontalAlignment.Left;//居左
下面提供完整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); }
