NPOI 设置导出的excel内容样式
导出excel时，有时要根据需要加上一些样式，以上几种样式是我在项目中用到的
一、给单元格加背景色只需两步：一是创建单元格背景景色对象；二是给单元格绑定样式
//创建单元格背景颜色对象 HSSFPalette palette = wb.GetCustomPalette(); //调色板实例 palette.SetColorAtIndex(HSSFColor.Orange.Index, (byte)255, (byte)204, (byte)153); HSSFColor hssFColor = palette.FindColor((byte)255, (byte)204, (byte)153); ICellStyle cellStyleGround = wb.CreateCellStyle(); cellStyleGround.FillPattern = FillPattern.SolidForeground; //老版本可能这样写FillPatternType.SOLID_FOREGROUND; cellStyleGround.SetFont(cs_content_Font); cellStyleGround.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center; //水平居中 cellStyleGround.VerticalAlignment = NPOI.SS.UserModel.VerticalAlignment.Center; //垂直居中 cellStyleGround.FillForegroundColor = hssFColor.Indexed;
//给单元格绑定样式 cell.CellStyle = cellStyleGround;
二、给单元格内的字加上样式
//创建工作簿 HSSFWorkbook wb = new HSSFWorkbook(); //创建工作表 HSSFSheet sheet = wb.createSheet(); //为工作表添加行 HSSFRow row = sheet.createRow(0); //添加单元格 HSSFCell cell = row.createCell(0); //创建字体 HSSFFont ftRed = wb.createFont(); ftRed.setStrikeout(true); ftRed.setColor(HSSFColor.RED.index); HSSFFont ftBlue = wb.createFont(); ftBlue.setColor(HSSFColor.BLUE.index); //往单元格中写入的内容,并使用ft格式化"second"单词 String[] subStr = { "first", "second" }; String sText = subStr[0] + "," + subStr[1]; HSSFRichTextString textString = new HSSFRichTextString(sText); textString.applyFont( sText.indexOf(subStr[0]), sText.indexOf(subStr[0]) + subStr[0].length(), ftRed ); textString.applyFont( sText.indexOf(subStr[1]), sText.indexOf(subStr[1]) + subStr[1].length(), ftBlue ); cell.setCellValue(textString);
三、附项目中用到的代码
private void ExportAllPlan(HttpContext context) { string title = DateTime.Now.AddYears(1).Year.ToString() + "年度领导人员因公出国(境)计划表"; HSSFWorkbook wb = new HSSFWorkbook();//创建工作薄 HSSFSheet sheet = (HSSFSheet)wb.CreateSheet(title); //创建工作表 //设置列头样式 HSSFCellStyle cs_Title = (HSSFCellStyle)wb.CreateCellStyle(); //创建列头样式 cs_Title.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center; //水平居中 cs_Title.VerticalAlignment = NPOI.SS.UserModel.VerticalAlignment.Center; //垂直居中 HSSFFont cs_Title_Font = (HSSFFont)wb.CreateFont(); //创建字体 cs_Title_Font.FontHeightInPoints = 12; //字体大小 cs_Title_Font.Boldweight = (short)NPOI.SS.UserModel.FontBoldWeight.Bold; cs_Title.SetFont(cs_Title_Font); //将字体绑定到样式 //设置列宽 for (int i = 0; i < 21; i++) { sheet.SetColumnWidth(i, 50 * 90); } //创建列头1行 HSSFRow row_Title_One = (HSSFRow)sheet.CreateRow(0); string[] titleStr_One = { title, "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "" }; //设置列头样式 HSSFCellStyle cs_Title_one = (HSSFCellStyle)wb.CreateCellStyle(); //创建列头样式 cs_Title_one.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center; //水平居中 cs_Title_one.VerticalAlignment = NPOI.SS.UserModel.VerticalAlignment.Center; //垂直居中 HSSFFont cs_Title_Font_one = (HSSFFont)wb.CreateFont(); //创建字体 cs_Title_Font_one.FontHeightInPoints = 24; //字体大小 cs_Title_Font_one.Boldweight = (short)NPOI.SS.UserModel.FontBoldWeight.Bold; cs_Title_one.SetFont(cs_Title_Font_one); //将字体绑定到样式 for (int i = 0; i < titleStr_One.Length; i++) { HSSFCell cell_Title = (HSSFCell)row_Title_One.CreateCell(i); //创建单元格 row_Title_One.Height = 200*3; cell_Title.CellStyle = cs_Title_one; //将样式绑定到单元格 cell_Title.SetCellValue(titleStr_One[i]); } //合并 sheet.AddMergedRegion(new CellRangeAddress(0, 0, 0, 21)); //创建列头2行 HSSFRow row_Title_Two = (HSSFRow)sheet.CreateRow(1); string[] titleStr_Two = { "分类", "单位名称", "上年计划总数", "上年执行计划数", "上年计划外团组数", "今年申报总数", "压缩调整总数", "取消总数", "计划明细", "", "", "", "", "", "", "", "", "", "合作交流处意见", "俄罗斯处意见", "美洲亚太处意见", "西亚非洲处意见" }; //填充列头内容 for (int i = 0; i < titleStr_Two.Length; i++) { HSSFCell cell_Title = (HSSFCell)row_Title_Two.CreateCell(i); //创建单元格 cell_Title.CellStyle = cs_Title; //将样式绑定到单元格 cell_Title.SetCellValue(titleStr_Two[i]); } //合并 sheet.AddMergedRegion(new CellRangeAddress(1, 2, 0, 0)); sheet.AddMergedRegion(new CellRangeAddress(1, 2, 1, 1)); sheet.AddMergedRegion(new CellRangeAddress(1, 2, 2, 2)); sheet.AddMergedRegion(new CellRangeAddress(1, 2, 3, 3)); sheet.AddMergedRegion(new CellRangeAddress(1, 2, 4, 4)); sheet.AddMergedRegion(new CellRangeAddress(1, 2, 5, 5)); sheet.AddMergedRegion(new CellRangeAddress(1, 2, 6, 6)); sheet.AddMergedRegion(new CellRangeAddress(1, 2, 7, 7)); sheet.AddMergedRegion(new CellRangeAddress(1, 1, 8, 17)); sheet.AddMergedRegion(new CellRangeAddress(1, 2, 18, 18)); sheet.AddMergedRegion(new CellRangeAddress(1, 2, 19, 19)); sheet.AddMergedRegion(new CellRangeAddress(1, 2, 20, 20)); sheet.AddMergedRegion(new CellRangeAddress(1, 2, 21, 21)); sheet.AddMergedRegion(new CellRangeAddress(1, 2, 22, 22)); //创建列头3行 HSSFRow row_Title_Three = (HSSFRow)sheet.CreateRow(2); string[] titleStr_Three = { "", "", "", "", "", "", "", "", "序号", "姓名", "职务", "任务名称", "前往国家", "启程时间（月）", "任务类别", "必要性说明", "备注", "处理意见", "", "", "", "" }; //填充列头内容 for (int i = 0; i < titleStr_Three.Length; i++) { HSSFCell cell_Title = (HSSFCell)row_Title_Three.CreateCell(i); //创建单元格 cell_Title.CellStyle = cs_Title; //将样式绑定到单元格 cell_Title.SetCellValue(titleStr_Three[i]); } try { //设置单元格内容样式 HSSFCellStyle cs_content = (HSSFCellStyle)wb.CreateCellStyle(); //创建列头样式 cs_content.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center; //水平居中 cs_content.VerticalAlignment = NPOI.SS.UserModel.VerticalAlignment.Center; //垂直居中 HSSFFont cs_content_Font = (HSSFFont)wb.CreateFont(); //创建字体 cs_content_Font.FontHeightInPoints = 12; //字体大小 cs_content.SetFont(cs_content_Font); //将字体绑定到样式 //创建单元格背景颜色对象 HSSFPalette palette = wb.GetCustomPalette(); //调色板实例 palette.SetColorAtIndex(HSSFColor.Orange.Index, (byte)255, (byte)204, (byte)153); HSSFColor hssFColor = palette.FindColor((byte)255, (byte)204, (byte)153); ICellStyle cellStyleGround = wb.CreateCellStyle(); cellStyleGround.FillPattern = FillPattern.SolidForeground; //老版本可能这样写FillPatternType.SOLID_FOREGROUND; cellStyleGround.SetFont(cs_content_Font); cellStyleGround.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center; //水平居中 cellStyleGround.VerticalAlignment = NPOI.SS.UserModel.VerticalAlignment.Center; //垂直居中 cellStyleGround.FillForegroundColor = hssFColor.Indexed; //获取计划数据 List<PlanToReport> plans = PlanToReportService.GetPlanByYear(DateTime.Now.AddYears(1).Year.ToString()); //出国处导出的大表中，只导出提交给出国处处长及以后流程的计划，提交给出国处处长之前的计划不导出 for (int i = 0; i < plans.Count; i++) { StateMachineWorkflowInstance workflow = WorkflowRuntime.Current.GetRoot("PLANAPPLY", plans[i].ID); string currState = workflow.CurrentState.Name; string[] NoCanExportExcelPlan = ConfigurationManager.AppSettings["NoCanExportExcelPlanState"].Split(','); for (int j = 0; j < NoCanExportExcelPlan.Length; j++) { if (currState.Trim() == NoCanExportExcelPlan[j].Trim()) { plans.RemoveAt(i); continue; } } } plans.Sort(new PlanToReportComparer()); int unitCategoryfistRow = 3; int planDiffRow = 3; int unitDiffRow = 3; int resultDifRow = 3; string tempCategory = string.Empty; string tempUnitInfo = string.Empty; string tempApprovalResult = string.Empty; int colmNo = 3; for (int k = 0; k < plans.Count; k++) { PlanToReport plan = plans[k]; UnitInfoBase unit = OilDigital.CGGL.BLL.UnitService.GetUnitByCode(plan.PlanUnitCode); string[] planStr = { unit.Category.Name, unit.Name, plan.LastYearPlanSum.ToString(), plan.LastYearCarryOut.ToString(), plan.LastYearUnplanned.ToString(), PlanToReportService.GetPersonInPlan(plan.ID).Count.ToString(), plan.CompressTotal.ToString(), plan.CancelTotal.ToString() }; List<PlanPerson> persons = PlanToReportService.GetPersonInPlan(plan.ID); persons.Sort(new PlanPersonComparer()); int indexNo = 1; for (int i = 0; i < persons.Count; i++) { PlanPersonApprovalService pps = new PlanPersonApprovalService(); // 取消审批 bool isDelete = pps.GetPlanPersonApprovalByIdAndType(persons[i].ID, "deletePerson") !=null; //审批国家 PlanPersonApproval approvalCountry = pps.GetPlanPersonApprovalByIdAndType(persons[i].ID,"country"); bool hasAppCountry = approvalCountry != null; //审批人员 PlanPersonApproval approvalPerson = pps.GetPlanPersonApprovalByIdAndType(persons[i].ID, "person"); bool hasAppPerson = approvalPerson != null; string[] personStr = { indexNo.ToString(), persons[i].Name, persons[i].Position, persons[i].JobName, persons[i].CountryName, persons[i].StartDate, persons[i].MissionType.ToString(), persons[i].Explain, persons[i].Remark, persons[i].ApprovalResults }; HSSFRow row = (HSSFRow)sheet.CreateRow(i + unitCategoryfistRow); bool isMergedplan = false; bool isMergedunit = false; bool isMergedApprovalResult = false; for (int j = 0; j < 22; j++) { HSSFCell cell = (HSSFCell)row.CreateCell(j); cell.CellStyle = cs_content; /* * 导出数据时总体说明： *填充数据8-16为人员数据，0-7和17-22为计划数据 *合并处理规则是：如果当前行和上一行不相同就合并上一行所有相同的，并对最后一行数据时行特殊处理。 */ //1、填充计划数据 if (j >= 0 && j <= 7) { //填充第一行时用计划数据 cell.SetCellValue(planStr[j]); if (j == 0) { if (colmNo != 3 && !tempCategory.Equals(planStr[j])) { sheet.AddMergedRegion(new CellRangeAddress(planDiffRow, colmNo - 1, j, j)); isMergedplan = true; } if (k == plans.Count - 1 && i == persons.Count - 1) { sheet.AddMergedRegion(new CellRangeAddress(planDiffRow, colmNo, j, j)); isMergedplan = true; } } if (j > 0 ) { if (colmNo != 3 && !tempUnitInfo.Equals(planStr[1])) { sheet.AddMergedRegion(new CellRangeAddress(unitDiffRow, colmNo - 1, j, j)); isMergedunit = true; } if (k == plans.Count - 1 && i == persons.Count - 1) { sheet.AddMergedRegion(new CellRangeAddress(unitDiffRow, colmNo, j, j)); isMergedunit = true; } } } //2、填充人员数据 else if (j >= 8 && j <= 17) { if (j == 14) { cell.SetCellValue(new MissionTypeService().GetById(int.Parse(personStr[j - 8])).Name); } else if (j == 12 && hasAppCountry) { cell.SetCellValue(FmatterCellValue(persons[i], wb, "country")); } else if (j == 9 && hasAppPerson) { cell.SetCellValue(FmatterCellValue(persons[i], wb, "person")); } else if (j == 17) { string cellval = persons[i].ApprovalResults; if (!string.IsNullOrEmpty(persons[i].ApprovalResults)) { cellval = persons[i].ApprovalResults.IndexOf("选0") > 0 ? "合并" : persons[i].ApprovalResults; } cell.SetCellValue(cellval); // if (colmNo != 3 && !tempApprovalResult.Equals(new PlanProcess().GetFmatterApprovalResult(persons[i])) && !string.IsNullOrEmpty(new PlanProcess().GetFmatterApprovalResult(persons[i]))) if (colmNo != 3 && !tempApprovalResult.Equals(new PlanProcess().GetFmatterApprovalResult(persons[i]))) { sheet.AddMergedRegion(new CellRangeAddress(resultDifRow, colmNo - 1, j, j)); isMergedApprovalResult = true; } if (k == plans.Count - 1 && i == persons.Count - 1) { sheet.AddMergedRegion(new CellRangeAddress(resultDifRow, colmNo, j, j)); isMergedApprovalResult = true; } if (!string.IsNullOrEmpty(persons[i].ApprovalResults)) { cell.CellStyle = cellStyleGround; } // cell.SetCellValue(new PlanProcess().GetFmatterApprovalResult(persons[i])); } else { cell.SetCellValue(personStr[j - 8]); } if (isDelete && j != 17) { cell.CellStyle = cellStyleGround; } } //3、填充意见数据 else { cell.SetCellValue(""); if (j > 17 && tempUnitInfo.Equals(planStr[1])) { sheet.AddMergedRegion(new CellRangeAddress(i + unitCategoryfistRow - 1, i + unitCategoryfistRow, j, j)); } } } if (isMergedplan) planDiffRow = colmNo; if (isMergedunit) unitDiffRow = colmNo; if (isMergedApprovalResult) resultDifRow = colmNo; tempCategory = planStr[0]; tempUnitInfo = planStr[1]; tempApprovalResult = new PlanProcess().GetFmatterApprovalResult(persons[i]); indexNo++; colmNo++; } unitCategoryfistRow += persons.Count; } } catch (Exception ex) { throw new Exception("导出计划出错，原因：" + ex.Message); } HttpResponse httpResponse = HttpContext.Current.Response; httpResponse.Clear(); httpResponse.Buffer = true; httpResponse.AppendHeader("Content-Disposition", "attachment;filename=" + HttpUtility.UrlEncode(title + ".xls")); httpResponse.ContentEncoding = Encoding.UTF8; httpResponse.ContentType = "application/vnd.ms-excel; charset=UTF-8"; wb.Write(httpResponse.OutputStream); httpResponse.End(); } // private HSSFRichTextString FmatterCellValue(PlanPerson p, HSSFWorkbook wb, string FmatterType) { PlanPersonApproval approval = new PlanPersonApprovalService().GetPlanPersonApprovalByIdAndType(p.ID, FmatterType); StringBuilder countryName = new StringBuilder(); string[] selectCountry = approval.ApprovalResult.Split('、'); string[] allCountry = null; if (FmatterType == "country") { allCountry = p.CountryName.Split('、'); } if (FmatterType == "person") { allCountry = p.Name.Split('、'); } string needDeleteCountry = string.Empty; for (int i = 0; i < allCountry.Length; i++) { bool flag = true; for (int j = 0; j < selectCountry.Length; j++) { if (allCountry[i].Trim() == selectCountry[j].Trim()) { flag = false; } } if (flag) { if (string.IsNullOrEmpty(needDeleteCountry)) { needDeleteCountry = allCountry[i]; } else { needDeleteCountry += "、" + allCountry[i]; } } } String[] subStr = { approval.ApprovalResult, needDeleteCountry }; //创建字体 HSSFFont ftRed = (HSSFFont)wb.CreateFont(); ftRed.IsStrikeout=true; ftRed.Color=HSSFColor.Red.Index; ftRed.FontHeightInPoints = 12; String sText = subStr[0] + "、" + subStr[1]; HSSFRichTextString textString = new HSSFRichTextString(sText); textString.ApplyFont( sText.IndexOf(subStr[1]), sText.IndexOf(subStr[1]) + subStr[1].Length, ftRed ); return textString; }
