}//解压结束 /// <summary> /// 初始化导出表格样式 /// </summary> /// LZ Add 2016-08-04 /// <param name="book">Excel文件</param> /// <param name="param"></param> /// <param name="paramComment">批注字典 注意:字典的key要与表头key一致</param> /// <returns></returns> public static ISheet CreateSheet(HSSFWorkbook book, Dictionary <string, int> param, Dictionary <string, string> paramComment, string sheetName = "Sheet1") { //添加一个sheet ISheet sheet1 = book.CreateSheet(sheetName); IRow row = sheet1.CreateRow(0); //初始化样式 ICellStyle mStyle = book.CreateCellStyle(); mStyle.Alignment = HorizontalAlignment.Center; mStyle.VerticalAlignment = VerticalAlignment.Center; IFont mfont = book.CreateFont(); mfont.FontHeight = 10 * 20; mStyle.SetFont(mfont); HSSFPatriarch patr = sheet1.CreateDrawingPatriarch() as HSSFPatriarch; NPOI.SS.UserModel.ICreationHelper facktory = book.GetCreationHelper(); HSSFComment comment = null; NPOI.SS.UserModel.IClientAnchor anchor = facktory.CreateClientAnchor(); int i = 0; foreach (var item in param) { //设置列宽 sheet1.SetColumnWidth(i, item.Value * 256); sheet1.SetDefaultColumnStyle(i, mStyle); row.CreateCell(i).SetCellValue(item.Key.ToString()); if (paramComment.ContainsKey(item.Key.ToString())) { //设置批注 anchor = facktory.CreateClientAnchor(); anchor.Col1 = row.GetCell(i).ColumnIndex; anchor.Col2 = row.GetCell(i).ColumnIndex + 1; anchor.Row1 = row.RowNum; anchor.Row2 = row.RowNum + 3; comment = patr.CreateCellComment(anchor) as HSSFComment; comment.String = new HSSFRichTextString(paramComment[item.Key.ToString()].ToString()); comment.Author = ("CySoft"); row.GetCell(i).CellComment = (comment); } i++; } i = 0; sheet1.GetRow(0).Height = 28 * 20; return(sheet1); }
public void UpdateWriteExcelFileLog(ref List <int[]> yellowError) { int yellow_total = yellowError.Count; using (FileStream fs = File.Open(fileName, FileMode.Open, FileAccess.Read)) { try { var ext = Path.GetExtension(fileName).ToLower(); IWorkbook wk; if (ext.Contains(".xlsx")) { wk = new XSSFWorkbook(fs); } else { wk = new HSSFWorkbook(fs); } fs.Close(); ISheet sheet = wk.GetSheetAt(0); IDrawing patr = sheet.CreateDrawingPatriarch(); NPOI.SS.UserModel.ICreationHelper facktory = wk.GetCreationHelper(); /**************************填写模板中设置背景色****************************/ foreach (int[] yellow in yellowError) { ICellStyle yellowStyle = wk.CreateCellStyle(); copyCellStyle(sheet.GetRow(yellow[0]).GetCell(yellow[1]).CellStyle, ref yellowStyle); // 背景色 yellowStyle.FillPattern = FillPattern.SolidForeground; yellowStyle.FillForegroundColor = HSSFColor.LightYellow.Index; sheet.GetRow(yellow[0]).GetCell(yellow[1]).CellStyle = yellowStyle; } FileStream fileStream = new FileStream(fileName, FileMode.Create, FileAccess.Write); wk.Write(fileStream); fileStream.Close(); } catch (Exception e) { FileLog.WriteLog("" + e); } } }
public void UpdateWriteExcelFileGP(string sheetname, ref List <int[]> redError, ref List <int[]> yellowError, ref List <int[]> blueError) { int red_total = redError.Count; int yellow_total = yellowError.Count; using (FileStream fs = File.Open(fileName, FileMode.Open, FileAccess.Read)) { try { var ext = Path.GetExtension(fileName).ToLower(); IWorkbook wk; if (ext.Contains(".xlsx")) { wk = new XSSFWorkbook(fs); } else { wk = new HSSFWorkbook(fs); } fs.Close(); ISheet sheet = wk.GetSheet(sheetname); IDrawing patr = sheet.CreateDrawingPatriarch(); NPOI.SS.UserModel.ICreationHelper facktory = wk.GetCreationHelper(); // 错误类型-批注 Dictionary <int, string> Dic_Comment = new Dictionary <int, string>(); Dic_Comment.Add(1, "未匹配上的省市名"); Dic_Comment.Add(2, "不支持的日期格式"); Dic_Comment.Add(3, "上报时间不能晚于当前时间"); Dic_Comment.Add(4, "当期时间与上次上报的时间格式不相同"); Dic_Comment.Add(5, "指标值包含中文"); Dic_Comment.Add(6, "指标值不是数值"); Dic_Comment.Add(7, "指标值越界"); Dic_Comment.Add(8, "带%的指标小于1"); Dic_Comment.Add(9, "分子÷分母不等于指标值"); Dic_Comment.Add(10, "无法填报"); Dictionary <int, int[]> Dic_Count = new Dictionary <int, int[]>(); Dic_Count.Add(1, new int[2] { 0, 1 }); Dic_Count.Add(2, new int[2] { 0, 1 }); Dic_Count.Add(3, new int[2] { 0, 1 }); Dic_Count.Add(4, new int[2] { 0, 1 }); Dic_Count.Add(5, new int[2] { 0, 0 }); Dic_Count.Add(6, new int[2] { 0, 1 }); Dic_Count.Add(7, new int[2] { 0, 1 }); Dic_Count.Add(8, new int[2] { 0, 0 }); Dic_Count.Add(9, new int[2] { 0, 0 }); Dic_Count.Add(10, new int[2] { 0, 2 }); // 背景色 ICellStyle yellowStyle = wk.CreateCellStyle(); yellowStyle.FillForegroundColor = HSSFColor.LightYellow.Index; yellowStyle.FillPattern = FillPattern.SolidForeground; ICellStyle redStyle = wk.CreateCellStyle(); redStyle.FillForegroundColor = HSSFColor.Red.Index; redStyle.FillPattern = FillPattern.SolidForeground; ICellStyle blueStyle = wk.CreateCellStyle(); blueStyle.FillForegroundColor = HSSFColor.SkyBlue.Index; blueStyle.FillPattern = FillPattern.SolidForeground; ICellStyle yellowErrorStyle = null, redErrorStyle = null, blueErrorStyle = null; /**************************填写模板中设置背景色****************************/ foreach (int[] yellow in yellowError) { if (sheet.GetRow(yellow[0]).GetCell(yellow[1]).CellStyle.GetDataFormatString().Contains("%")) { if (yellowErrorStyle == null) { yellowErrorStyle = wk.CreateCellStyle(); copyCellStyle(sheet.GetRow(yellow[0]).GetCell(yellow[1]).CellStyle, ref yellowErrorStyle); yellowErrorStyle.FillPattern = FillPattern.SolidForeground; yellowErrorStyle.FillForegroundColor = HSSFColor.LightYellow.Index; } sheet.GetRow(yellow[0]).GetCell(yellow[1]).CellStyle = yellowErrorStyle; } else { sheet.GetRow(yellow[0]).GetCell(yellow[1]).CellStyle = yellowStyle; } Dic_Count[yellow[2]][0]++; } foreach (int[] red in redError) { if (sheet.GetRow(red[0]).GetCell(red[1]).CellStyle.GetDataFormatString().Contains("%")) { if (redErrorStyle == null) { redErrorStyle = wk.CreateCellStyle(); copyCellStyle(sheet.GetRow(red[0]).GetCell(red[1]).CellStyle, ref redErrorStyle); redErrorStyle.FillPattern = FillPattern.SolidForeground; redErrorStyle.FillForegroundColor = HSSFColor.Red.Index; } sheet.GetRow(red[0]).GetCell(red[1]).CellStyle = redErrorStyle; } else { sheet.GetRow(red[0]).GetCell(red[1]).CellStyle = redStyle; } Dic_Count[red[2]][0]++; } foreach (int[] blue in blueError) { if (sheet.GetRow(blue[0]).GetCell(blue[1]).CellStyle.GetDataFormatString().Contains("%")) { if (blueErrorStyle == null) { blueErrorStyle = wk.CreateCellStyle(); copyCellStyle(sheet.GetRow(blue[0]).GetCell(blue[1]).CellStyle, ref blueErrorStyle); blueErrorStyle.FillPattern = FillPattern.SolidForeground; blueErrorStyle.FillForegroundColor = HSSFColor.SkyBlue.Index; } sheet.GetRow(blue[0]).GetCell(blue[1]).CellStyle = blueErrorStyle; } else { sheet.GetRow(blue[0]).GetCell(blue[1]).CellStyle = blueStyle; } Dic_Count[blue[2]][0]++; } /**************************问题数据明细****************************/ ISheet sheetLogInfo = null; int index = wk.GetSheetIndex("问题数据明细"); if (index > -1) { wk.RemoveSheetAt(index); } sheetLogInfo = wk.CreateSheet("问题数据明细"); IRow headerRow = sheetLogInfo.CreateRow(0); headerRow.CreateCell(0).SetCellValue("位置"); headerRow.CreateCell(1).SetCellValue("描述"); headerRow.CreateCell(2).SetCellValue("备注"); int rowIndex = 1; string address = null, value1 = "必改", value2 = "自查", value3 = "无法填报", sheetname2 = "#填写模板!"; foreach (int[] red in redError) { IRow dataRow = sheetLogInfo.CreateRow(rowIndex); address = ExcelUtil.getExcelColumnLabel(red[1]) + "" + (red[0] + 1); dataRow.CreateCell(0).SetCellValue(address); dataRow.CreateCell(1).SetCellValue(Dic_Comment[red[2]]); dataRow.CreateCell(2).SetCellValue(value1); sheetLogInfo.GetRow(rowIndex).GetCell(2).CellStyle = redStyle; IHyperlink link = new XSSFHyperlink(HyperlinkType.Document); link.Address = sheetname2 + address; sheetLogInfo.GetRow(rowIndex).GetCell(0).Hyperlink = link; rowIndex++; } foreach (int[] yellow in yellowError) { IRow dataRow = sheetLogInfo.CreateRow(rowIndex); address = ExcelUtil.getExcelColumnLabel(yellow[1]) + "" + (yellow[0] + 1); dataRow.CreateCell(0).SetCellValue(address); dataRow.CreateCell(1).SetCellValue(Dic_Comment[yellow[2]]); dataRow.CreateCell(2).SetCellValue(value2); sheetLogInfo.GetRow(rowIndex).GetCell(2).CellStyle = yellowStyle; IHyperlink link = new XSSFHyperlink(HyperlinkType.Document); link.Address = sheetname2 + address; sheetLogInfo.GetRow(rowIndex).GetCell(0).Hyperlink = link; rowIndex++; } foreach (int[] blue in blueError) { IRow dataRow = sheetLogInfo.CreateRow(rowIndex); address = ExcelUtil.getExcelColumnLabel(blue[1]) + "" + (blue[0] + 1); dataRow.CreateCell(0).SetCellValue(address); dataRow.CreateCell(1).SetCellValue(Dic_Comment[blue[2]]); dataRow.CreateCell(2).SetCellValue(value3); sheetLogInfo.GetRow(rowIndex).GetCell(2).CellStyle = blueStyle; IHyperlink link = new XSSFHyperlink(HyperlinkType.Document); link.Address = sheetname2 + address; sheetLogInfo.GetRow(rowIndex).GetCell(0).Hyperlink = link; rowIndex++; } /**************************问题数据统计****************************/ ISheet sheetLog = null; index = wk.GetSheetIndex("问题数据统计"); if (index > -1) { wk.RemoveSheetAt(index); } sheetLog = wk.CreateSheet("问题数据统计"); headerRow = sheetLog.CreateRow(0); headerRow.CreateCell(0).SetCellValue("描述"); headerRow.CreateCell(1).SetCellValue("次数"); headerRow.CreateCell(2).SetCellValue("备注"); // handling value. rowIndex = 1; foreach (KeyValuePair <int, int[]> kv in Dic_Count) { IRow dataRow = sheetLog.CreateRow(rowIndex); dataRow.CreateCell(0).SetCellValue(Dic_Comment[kv.Key]); dataRow.CreateCell(1).SetCellValue(kv.Value[0]); dataRow.CreateCell(2).SetCellValue(kv.Value[1] == 1 ? value1 : (kv.Value[1] == 0 ? value2 : value3)); sheetLog.GetRow(rowIndex).GetCell(2).CellStyle = kv.Value[1] == 1 ? redStyle : (kv.Value[1] == 0 ? yellowStyle : blueStyle); rowIndex++; } IRow totalRow = sheetLog.CreateRow(rowIndex); totalRow.CreateCell(0).SetCellValue("必改问题合计:" + red_total); totalRow.CreateCell(1).SetCellValue("自查问题合计:" + yellow_total); totalRow.CreateCell(2).SetCellValue("无法填报合计:" + blueError.Count); sheetLog.GetRow(rowIndex).GetCell(0).CellStyle = redStyle; sheetLog.GetRow(rowIndex).GetCell(1).CellStyle = yellowStyle; sheetLog.GetRow(rowIndex).GetCell(2).CellStyle = blueStyle; IRow remarkRow1 = sheetLog.CreateRow(++rowIndex); IRow remarkRow2 = sheetLog.CreateRow(++rowIndex); IRow remarkRow3 = sheetLog.CreateRow(++rowIndex); IRow remarkRow4 = sheetLog.CreateRow(++rowIndex); remarkRow1.CreateCell(0).SetCellValue("注:"); remarkRow2.CreateCell(0).SetCellValue("【必改】问题数据必须修改后才能入库"); remarkRow3.CreateCell(0).SetCellValue("【自查】问题数据请自行核查数据,填报人员对数据准确性负责"); remarkRow4.CreateCell(0).SetCellValue("【无法填报】表示根据数据填报核减模版,该单元格指标为无法填报,该指标数据不入库"); //FileStream fileStream = File.OpenWrite(fileName); FileStream fileStream = new FileStream(fileName, FileMode.Create, FileAccess.Write); //FileStream fileStream = new FileStream(@"D:\\新建文件夹\\test.xlsx",FileMode.Create, FileAccess.Write); wk.Write(fileStream); fileStream.Close(); } catch (Exception e) { FileLog.WriteLog("" + e); } } }