private static short GetXLColour(HSSFWorkbook workbook, Color SystemColour) { short s = 0; var XlPalette = workbook.GetCustomPalette(); var XlColour = XlPalette.FindColor(SystemColour.R, SystemColour.G, SystemColour.B); if (XlColour == null) { if (PaletteRecord.STANDARD_PALETTE_SIZE < 255) { XlColour = XlPalette.FindSimilarColor(SystemColour.R, SystemColour.G, SystemColour.B); s = XlColour.Indexed; } } else { s = XlColour.Indexed; } return(s); }
private static short GetXLColour(Color SystemColour) { HSSFWorkbook workbook = new HSSFWorkbook(); short s = 0; HSSFPalette XlPalette = workbook.GetCustomPalette(); NPOI.HSSF.Util.HSSFColor XlColour = XlPalette.FindColor(SystemColour.R, SystemColour.G, SystemColour.B); if (XlColour == null) { if (NPOI.HSSF.Record.PaletteRecord.STANDARD_PALETTE_SIZE < 255) { XlColour = XlPalette.FindSimilarColor(SystemColour.R, SystemColour.G, SystemColour.B); s = XlColour.Indexed; } } else { s = XlColour.Indexed; } return(s); }
public void Test48403() { HSSFWorkbook wb = new HSSFWorkbook(); var color = new Rgb24(0, 0x6B, 0x6B); //decode("#006B6B"); HSSFPalette palette = wb.GetCustomPalette(); HSSFColor hssfColor = palette.FindColor(color.R, color.G, color.B); Assert.IsNull(hssfColor); palette.SetColorAtIndex( (short)(PaletteRecord.STANDARD_PALETTE_SIZE - 1), (byte)color.R, (byte)color.G, (byte)color.B); hssfColor = palette.GetColor((short)(PaletteRecord.STANDARD_PALETTE_SIZE - 1)); Assert.IsNotNull(hssfColor); Assert.AreEqual(55, hssfColor.Indexed); CollectionAssert.AreEqual(new short[] { 0, 107, 107 }, hssfColor.GetTriplet()); wb.Close(); }
/// <summary> /// 低版本设置自定义颜色 /// </summary> /// <param name="workBook"></param> /// <param name="rgbs"></param> private static void SetCustomColor(this HSSFWorkbook workBook, IEnumerable <string> rgbs) { SetOriginalRGB(); // 获取调色板 HSSFPalette pattern = workBook.GetCustomPalette(); short indexed = 8; foreach (var rgb in rgbs) { if (indexed > 63) { indexed = 8; } short tempIndex = pattern.SetCustomColor(rgb, indexed); if (tempIndex == -1) { continue; } indexed = tempIndex; indexed++; } }
private static void SetCustomColor(this HSSFWorkbook workBook, IEnumerable <ColorEntity> listColor) { SetOriginalRGB(); // 获取调色板 HSSFPalette pattern = workBook.GetCustomPalette(); short indexed = 8; foreach (var color in listColor) { if (indexed > 63) { indexed = 8; } short tempIndex = pattern.SetCustomColor(color.RGB, color.Index); if (tempIndex == -1) { continue; } indexed = tempIndex; indexed++; } }
public static HSSFColor setColor(HSSFWorkbook workbook, byte r, byte g, byte b) { try { HSSFPalette palette = workbook.GetCustomPalette(); HSSFColor color = null; color = palette.FindColor(r, g, b); if (color == null) { palette.SetColorAtIndex(HSSFColor.Blue.Index, r, g, b); color = palette.GetColor(HSSFColor.Blue.Index); } return(color); } catch (Exception ex) { throw ex; } }
public void SetColor(int k, int mrow, int mcol, short R, short G, short B) { k = k - 1; string Result = ""; if (wb == null) { return; } ISheet sheet = wb.GetSheetAt(k); mrow = mrow - 1; mcol = mcol - 1; IRow row = sheet.GetRow(mrow); if (row == null) { row = sheet.CreateRow(mrow); } ICell cell = row.GetCell(mcol); //|| (cell.CellType != CellType.String) if ((cell == null)) { cell = row.CreateCell(mcol); } ICellStyle s = wb.CreateCellStyle(); //HSSFColor.GetIndexHash(); HSSFPalette palette = wb.GetCustomPalette(); HSSFColor hssFColor = palette.FindColor((Byte)R, (Byte)G, (Byte)B); s.FillForegroundColor = hssFColor.Indexed; s.FillPattern = FillPattern.SolidForeground; cell.CellStyle = s; }
public override void SetFontColor(System.Drawing.Color color) { IWorkbook workbook = _npoiWorksheet.Workbook; ICellStyle cellStyle = workbook.CreateCellStyle(); cellStyle.CloneStyleFrom(_npoiWorksheet.GetColumnStyle(_columnNum)); if (workbook is HSSFWorkbook) { HSSFWorkbook hssfWorkbook = (HSSFWorkbook)workbook; HSSFPalette palette = hssfWorkbook.GetCustomPalette(); //调色板实例 //palette.SetColorAtIndex((short)8, color.R, color.G, color.B); HSSFColor hssFColor = palette.FindSimilarColor(color.R, color.G, color.B); IFont font = cellStyle.GetFont(workbook); font.Color = hssFColor.Indexed; cellStyle.SetFont(font); } else { //No way! } _npoiWorksheet.SetDefaultColumnStyle(_columnNum, cellStyle); }
private void BuildStyle_Border(HSSFWorkbook workbook, StringBuilder style, String type, BorderStyle xlsBorder, short borderColor) { if (xlsBorder == BorderStyle.NONE) { return; } StringBuilder borderStyle = new StringBuilder(); borderStyle.Append(ExcelToHtmlUtils.GetBorderWidth(xlsBorder)); borderStyle.Append(' '); borderStyle.Append(ExcelToHtmlUtils.GetBorderStyle(xlsBorder)); HSSFColor color = workbook.GetCustomPalette().GetColor(borderColor); if (color != null) { borderStyle.Append(' '); borderStyle.Append(ExcelToHtmlUtils.GetColor(color)); } style.Append("border-" + type + ": " + borderStyle + "; "); }
/// <summary> /// 列头樣式 /// </summary> /// <param name="workbook"></param> /// <returns></returns> private static HSSFCellStyle GetColHeaderStyle(HSSFWorkbook workbook) { var headStyle = workbook.CreateCellStyle() as HSSFCellStyle; headStyle.Alignment = HorizontalAlignment.CENTER; var font = workbook.CreateFont() as HSSFFont; font.FontHeightInPoints = 10; font.Boldweight = 700; headStyle.SetFont(font); var palette = workbook.GetCustomPalette(); palette.SetColorAtIndex((short)10, (byte)0, (byte)176, (byte)240); headStyle.FillForegroundColor = (short)10; headStyle.FillPattern = FillPatternType.SOLID_FOREGROUND; headStyle.BorderBottom = CellBorderType.THIN; headStyle.BorderLeft = CellBorderType.THIN; headStyle.BorderTop = CellBorderType.THIN; headStyle.BorderRight = CellBorderType.THIN; return(headStyle); }
void Test() { filePath = "E:/MyWork/test.xls"; wk = new HSSFWorkbook(); sheet = wk.CreateSheet("mySheet"); HSSFWorkbook hssfWorkbook = new HSSFWorkbook(); //工作簿实例 HSSFPalette palette = hssfWorkbook.GetCustomPalette(); //调色板实例 //palette.SetColorAtIndex((short)8, (byte)0, (byte)200, (byte)0); //PaletteRecord p = new PaletteRecord(); PaletteRecord p = new PaletteRecord(); //调色板实例 //HSSFPalette palette = workbookAll.GetCustomPalette(); //RGB颜色值,第一个值:8~64之间,后面三个值为RGB色值 //palette.SetColorAtIndex((short)8, 179, 179, 179); //颜色实例 //HSSFColor hSSFColor = palette.FindColor(179, 179, 179); //style.FillPattern = CellFillPattern.SOLID_FOREGROUND; //应用颜色到Style //style.FillForegroundColor = hSSFColor.GetIndex(); //List<HSSFColor> colors = new List<HSSFColor>(); Dictionary <int, ICellStyle> colDic = new Dictionary <int, ICellStyle>(); for (int i = 0; i < size; i++) { row = sheet.CreateRow(i); for (int j = 0; j < size / 4; j++) { cell = row.CreateCell(j); //cell.SetCellValue(i + "-" + j); Color col = texture.GetPixel(j * 4, size - i); //Color col = GetArvCol(j * 4, size - i); //if(col.a <= 0.1f) //{ //} short similarColor = palette.FindSimilarColor((byte)(col.r * 255), (byte)(col.g * 255), (byte)(col.b * 255)).Indexed; //透明的地方用白色 if (col.a <= 0.1f) { similarColor = 9; } //Debug.LogError(similarColor.Indexed); if (!colDic.ContainsKey(similarColor)) { ICellStyle s = wk.CreateCellStyle(); colDic.Add(similarColor, s); s.FillForegroundColor = similarColor; s.FillPattern = FillPattern.SolidForeground; cell.CellStyle = s; } else { ICellStyle s = colDic[similarColor]; s.FillForegroundColor = similarColor; s.FillPattern = FillPattern.SolidForeground; cell.CellStyle = s; } //ICellStyle s = wk.CreateCellStyle(); //s.FillForegroundColor = palette.FindSimilarColor((byte)(col.r * 255), (byte)(col.g * 255), (byte)(col.b * 255)).Indexed; //s.FillPattern = FillPattern.SolidForeground; //cell.CellStyle = s; } } fs = File.Create(filePath); wk.Write(fs); fs.Close(); fs.Dispose(); Debug.Log("创建表格成功"); }
/// <summary> /// get the color value for the font /// </summary> /// <param name="wb">HSSFWorkbook</param> /// <returns></returns> public HSSFColor GetHSSFColor(HSSFWorkbook wb) { HSSFPalette pallette = wb.GetCustomPalette(); return(pallette.GetColor(Color)); }
protected override byte[] Export <T>(IEnumerable <T> table, IEnumerable <KeyValuePair <PropertyInfo, TableOptionsAttribute> > tableColumns) { using (var ms = new MemoryStream()) { var workbook = new HSSFWorkbook(); var sheet = workbook.CreateSheet(); var rowIndex = 0; var colIndex = 0; var colCount = tableColumns.Count(); var headerRow = sheet.CreateRow(0); var headerRowCell = headerRow.CreateCell(colIndex); headerRowCell.SetCellValue($"{resource?.Get("CreateDate")} {DateTime.Now.ToString(CultureInfo.CurrentUICulture)}".Trim()); sheet.AddMergedRegion(new CellRangeAddress(rowIndex, rowIndex, colIndex, colCount - 1)); var style = workbook.CreateCellStyle(); style.Alignment = HorizontalAlignment.Right; headerRowCell.CellStyle = style; var tableHeaderRow = sheet.CreateRow(1); // Creating styles var headerCellStyle = workbook.CreateCellStyle(); var palette = workbook.GetCustomPalette(); palette.SetColorAtIndex(HSSFColor.Lavender.Index, ExportConfiguration.HeaderBackgroundColor.R, ExportConfiguration.HeaderBackgroundColor.G, ExportConfiguration.HeaderBackgroundColor.B); headerCellStyle.FillBackgroundColor = HSSFColor.Lavender.Index; headerCellStyle.FillPattern = FillPattern.SolidForeground; var doubleCellStyle = workbook.CreateCellStyle(); doubleCellStyle.DataFormat = HSSFDataFormat.GetBuiltinFormat("0.00000"); var intCellStyle = workbook.CreateCellStyle(); intCellStyle.DataFormat = HSSFDataFormat.GetBuiltinFormat("0"); // Creates the header cells foreach (var tableColumn in tableColumns.Select(item => item.Value)) { var headerCell = tableHeaderRow.CreateCell(colIndex); headerCell.SetCellValue(tableColumn.Title); // Add Style to Cell headerCell.CellStyle = headerCellStyle; colIndex++; } if (table != null) { // Writes the cells foreach (var obj in table) { colIndex = 0; rowIndex++; var dataRow = sheet.CreateRow(rowIndex); foreach (var tableColumn in tableColumns) { var val = tableColumn.Key.GetPropertyValue(obj, tableColumn.Value); if (val != null) { var cell = dataRow.CreateCell(colIndex); if (val is double) { cell.SetCellType(CellType.Numeric); cell.SetCellValue((double)val); cell.CellStyle = doubleCellStyle; } else if (val is short || val is int || val is long) { cell.SetCellType(CellType.Numeric); cell.SetCellValue(Convert.ToInt64(val)); cell.CellStyle = intCellStyle; } else { cell.SetCellValue(string.Format(tableColumn.Value.Format.IsNotNullOrEmpty() ? "{0:" + tableColumn.Value.Format + "}" : "{0}", val)); } } colIndex++; } } } // Auto size the columns for (var i = 0; i < colCount; i++) { sheet.AutoSizeColumn(i); } // Create the stream workbook.Write(ms); return(ms.ToArray()); } }
public ActionResult ExportData(string sheet, string subTitle, List <dynamic> exportData) { #region 加载模板文件到工作簿对象中 HSSFWorkbook hssfworkbook; using (FileStream file = new FileStream(Server.MapPath("/Areas/STAT/Views/STATTemp/排队叫号分析.xls"), FileMode.Open, FileAccess.Read)) { hssfworkbook = new HSSFWorkbook(file); } #endregion #region 根据模板设置工作表的内容 ISheet sheet1 = hssfworkbook.GetSheet("排队叫号分析"); string maintitle = subTitle.Replace("<span style='font-size:12px;'>", "").Replace("</span>", ""); sheet1.GetRow(0).Cells[0].SetCellValue(maintitle); int rowIndex = 4; if (null != exportData) { #region 数据单元格样式 var cellFont = hssfworkbook.CreateFont(); var cellStyle = hssfworkbook.CreateCellStyle(); cellStyle.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.White.Index; cellStyle.FillPattern = NPOI.SS.UserModel.FillPattern.SolidForeground; #endregion sheet1.GetRow(3).Cells[1].SetCellValue(sheet.Split('-')[1]); foreach (var row in exportData) { IRow xlsrow = sheet1.CreateRow(rowIndex); xlsrow.CreateCell(0).SetCellValue(rowIndex - 3); xlsrow.CreateCell(1).SetCellValue(row.NAM); xlsrow.CreateCell(2).SetCellValue(row.CALL_CNT); xlsrow.CreateCell(3).SetCellValue(CommonHelper.DivisionOfPercent(row.CALL_CNT, exportData.Sum(obj => obj.CALL_CNT))); xlsrow.CreateCell(4).SetCellValue(row.HANDLE_CNT); xlsrow.CreateCell(5).SetCellValue(CommonHelper.DivisionOfPercent(row.HANDLE_CNT, exportData.Sum(obj => obj.CALL_CNT))); xlsrow.CreateCell(6).SetCellValue(row.OVERTIME_WAIT_CNT); xlsrow.CreateCell(7).SetCellValue(CommonHelper.DivisionOfPercent(row.OVERTIME_WAIT_CNT, exportData.Sum(obj => obj.CALL_CNT))); xlsrow.CreateCell(8).SetCellValue(row.SECOND_SVR_CNT); xlsrow.CreateCell(9).SetCellValue(CommonHelper.DivisionOfPercent(row.SECOND_SVR_CNT, exportData.Sum(obj => obj.CALL_CNT))); xlsrow.CreateCell(10).SetCellValue(CommonHelper.DivisionOfTimeString(row.WAIT_DUR, row.CALL_CNT)); xlsrow.CreateCell(11).SetCellValue(row.LOCAL_CNT); xlsrow.CreateCell(12).SetCellValue(CommonHelper.DivisionOfPercent(row.LOCAL_CNT, exportData.Sum(obj => obj.CALL_CNT))); xlsrow.CreateCell(13).SetCellValue(row.VOTE_MULTI_CNT); xlsrow.CreateCell(14).SetCellValue(CommonHelper.DivisionOfPercent(row.VOTE_MULTI_CNT, exportData.Sum(obj => obj.CALL_CNT))); xlsrow.CreateCell(15).SetCellValue(row.ABANDON_CNT); xlsrow.CreateCell(16).SetCellValue(CommonHelper.DivisionOfPercent(row.ABANDON_CNT, exportData.Sum(obj => obj.CALL_CNT))); #region 设置单元格样式 foreach (var cell in xlsrow.Cells) { cell.CellStyle = cellStyle; cell.CellStyle.VerticalAlignment = NPOI.SS.UserModel.VerticalAlignment.Center; cell.CellStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center; cell.CellStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin; cell.CellStyle.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin; cell.CellStyle.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin; cell.CellStyle.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin; } #endregion rowIndex++; } IRow footer = sheet1.CreateRow(rowIndex); int totalCALLCNT = exportData.Sum(obj => obj.CALL_CNT); footer.CreateCell(0).SetCellValue("合计"); //#fffdbb footer.CreateCell(1).SetCellValue("合计"); //#fffdbb footer.CreateCell(2).SetCellValue(totalCALLCNT); footer.CreateCell(3).SetCellValue(CommonHelper.DivisionOfPercent(exportData.Sum(obj => obj.CALL_CNT), totalCALLCNT)); footer.CreateCell(4).SetCellValue(exportData.Sum(obj => obj.HANDLE_CNT)); footer.CreateCell(5).SetCellValue(CommonHelper.DivisionOfPercent(exportData.Sum(obj => obj.HANDLE_CNT), totalCALLCNT)); footer.CreateCell(6).SetCellValue(exportData.Sum(obj => obj.OVERTIME_WAIT_CNT)); footer.CreateCell(7).SetCellValue(CommonHelper.DivisionOfPercent(exportData.Sum(obj => obj.OVERTIME_WAIT_CNT), totalCALLCNT)); footer.CreateCell(8).SetCellValue(exportData.Sum(obj => obj.SECOND_SVR_CNT)); footer.CreateCell(9).SetCellValue(CommonHelper.DivisionOfPercent(exportData.Sum(obj => obj.SECOND_SVR_CNT), totalCALLCNT)); footer.CreateCell(10).SetCellValue(CommonHelper.DivisionOfTimeString(exportData.Sum(obj2 => (double)obj2.WAIT_DUR), exportData.Sum(obj2 => obj2.CALL_CNT))); footer.CreateCell(11).SetCellValue(exportData.Sum(obj => obj.LOCAL_CNT)); footer.CreateCell(12).SetCellValue(CommonHelper.DivisionOfPercent(exportData.Sum(obj => obj.LOCAL_CNT), totalCALLCNT)); footer.CreateCell(13).SetCellValue(exportData.Sum(obj => obj.VOTE_MULTI_CNT)); footer.CreateCell(14).SetCellValue(CommonHelper.DivisionOfPercent(exportData.Sum(obj => obj.VOTE_MULTI_CNT), totalCALLCNT)); footer.CreateCell(15).SetCellValue(exportData.Sum(obj => obj.ABANDON_CNT)); footer.CreateCell(16).SetCellValue(CommonHelper.DivisionOfPercent(exportData.Sum(obj => obj.ABANDON_CNT), totalCALLCNT)); #region 底部样式 var footercellFont = hssfworkbook.CreateFont(); var footercellStyle = hssfworkbook.CreateCellStyle(); footercellFont.Boldweight = (short)NPOI.SS.UserModel.FontBoldWeight.Bold; footercellStyle.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.Yellow.Index; footercellStyle.FillPattern = NPOI.SS.UserModel.FillPattern.SolidForeground; var HSSFPalette = hssfworkbook.GetCustomPalette(); Color c = ToColor("#fffdbb"); HSSFPalette.SetColorAtIndex(NPOI.HSSF.Util.HSSFColor.Yellow.Index, c.R, c.G, c.B); #endregion foreach (var cell in footer.Cells) { cell.CellStyle = footercellStyle; cell.CellStyle.VerticalAlignment = NPOI.SS.UserModel.VerticalAlignment.Center; cell.CellStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center; cell.CellStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin; cell.CellStyle.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin; cell.CellStyle.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin; cell.CellStyle.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin; } CellRangeAddress cellRangeAddress = new CellRangeAddress(rowIndex, rowIndex, 0, 1); sheet1.AddMergedRegion(cellRangeAddress); } //强制Excel重新计算表中所有的公式 sheet1.ForceFormulaRecalculation = true; #endregion #region 写入到客户端 MemoryStream ms = new MemoryStream(); //将工作簿的内容放到内存流中 hssfworkbook.Write(ms); ms.Seek(0, SeekOrigin.Begin); return(File(ms, "application/vnd.ms-excel", sheet + ".xls")); #endregion }
private void GenerarFormatos() { HSSFPalette Colores = hssfworkbook.GetCustomPalette(); Colores.SetColorAtIndex(HSSFColor.GREEN.index, (byte)83, (byte)147, (byte)91); //Verde Colores.SetColorAtIndex(HSSFColor.DARK_YELLOW.index, (byte)231, (byte)204, (byte)45); //Amarillo Fuerte Colores.SetColorAtIndex(HSSFColor.LIGHT_YELLOW.index, (byte)248, (byte)241, (byte)196); //Amarillo Suave Colores.SetColorAtIndex(HSSFColor.WHITE.index, (byte)255, (byte)255, (byte)255); //Blanco Colores.SetColorAtIndex(HSSFColor.BLACK.index, (byte)0, (byte)0, (byte)0); //Negro Colores.SetColorAtIndex(HSSFColor.GREY_25_PERCENT.index, (byte)216, (byte)228, (byte)188); //Verde Suave IFont FBlanca12 = hssfworkbook.CreateFont(); FBlanca12.Color = HSSFColor.WHITE.index; FBlanca12.IsItalic = false; FBlanca12.FontHeightInPoints = 12; IFont FBlanca10 = hssfworkbook.CreateFont(); FBlanca10.Color = HSSFColor.WHITE.index; FBlanca10.IsItalic = false; FBlanca10.FontHeightInPoints = 10; IFont FNegra = hssfworkbook.CreateFont(); FNegra.Color = HSSFColor.BLACK.index; FNegra.IsItalic = false; FNegra.FontHeightInPoints = 10; EstTitulos = hssfworkbook.CreateCellStyle(); EstTitulos.Alignment = HorizontalAlignment.CENTER; EstTitulos.FillPattern = FillPatternType.SOLID_FOREGROUND; EstTitulos.FillForegroundColor = HSSFColor.GREEN.index; EstTitulos.VerticalAlignment = VerticalAlignment.CENTER; EstTitulos.SetFont(FBlanca10); EstTitulos.IsLocked = true; EstInvernadero = hssfworkbook.CreateCellStyle(); EstInvernadero.Alignment = HorizontalAlignment.CENTER; EstInvernadero.FillPattern = FillPatternType.SOLID_FOREGROUND; EstInvernadero.FillForegroundColor = HSSFColor.GREY_25_PERCENT.index; EstInvernadero.VerticalAlignment = VerticalAlignment.CENTER; EstInvernadero.SetFont(FNegra); EstInvernadero.IsLocked = true; EstSubtitulos = hssfworkbook.CreateCellStyle(); EstSubtitulos.Alignment = HorizontalAlignment.CENTER; EstSubtitulos.FillPattern = FillPatternType.SOLID_FOREGROUND; EstSubtitulos.FillForegroundColor = HSSFColor.DARK_YELLOW.index; EstSubtitulos.SetFont(FBlanca10); EstSubtitulos.IsLocked = true; EstCeldas = hssfworkbook.CreateCellStyle(); EstCeldas.Alignment = HorizontalAlignment.CENTER; EstCeldas.FillPattern = FillPatternType.SOLID_FOREGROUND; EstCeldas.FillForegroundColor = HSSFColor.LIGHT_YELLOW.index; EstCeldas.SetFont(FNegra); EstCeldas.IsLocked = false; EsString = hssfworkbook.CreateCellStyle(); EsString.Alignment = HorizontalAlignment.CENTER; EsString.FillPattern = FillPatternType.SOLID_FOREGROUND; EsString.FillForegroundColor = HSSFColor.LIGHT_YELLOW.index; EsString.SetFont(FNegra); EsString.IsLocked = false; var formatId = HSSFDataFormat.GetBuiltinFormat("@"); if (formatId == -1) { var newDataFormat = hssfworkbook.CreateDataFormat(); EsString.DataFormat = newDataFormat.GetFormat("@"); } else { EsString.DataFormat = formatId; } RenglonAmarillo = hssfworkbook.CreateCellStyle(); RenglonAmarillo.Alignment = HorizontalAlignment.CENTER; RenglonAmarillo.FillPattern = FillPatternType.SOLID_FOREGROUND; RenglonAmarillo.FillForegroundColor = HSSFColor.LIGHT_YELLOW.index; RenglonAmarillo.SetFont(FNegra); RenglonAmarillo.IsLocked = false; RenglonBlanco = hssfworkbook.CreateCellStyle(); RenglonBlanco.Alignment = HorizontalAlignment.CENTER; RenglonBlanco.FillPattern = FillPatternType.SOLID_FOREGROUND; RenglonBlanco.FillForegroundColor = HSSFColor.WHITE.index; RenglonBlanco.SetFont(FNegra); RenglonBlanco.IsLocked = false; formatId = HSSFDataFormat.GetBuiltinFormat("#,###,##0;-#,###,##0"); if (formatId == -1) { var newDataFormat = hssfworkbook.CreateDataFormat(); EstCeldas.DataFormat = newDataFormat.GetFormat("#,###,##0;-#,###,##0"); } else { EstCeldas.DataFormat = formatId; } EstCeldasDate = hssfworkbook.CreateCellStyle(); EstCeldasDate.Alignment = HorizontalAlignment.CENTER; EstCeldasDate.FillPattern = FillPatternType.SOLID_FOREGROUND; EstCeldasDate.FillForegroundColor = HSSFColor.LIGHT_YELLOW.index; EstCeldasDate.SetFont(FNegra); EstCeldasDate.IsLocked = false; formatId = HSSFDataFormat.GetBuiltinFormat("m/d/yy"); if (formatId == -1) { var newDataFormat = hssfworkbook.CreateDataFormat(); EstCeldasDate.DataFormat = newDataFormat.GetFormat("m/d/yy"); } else { EstCeldasDate.DataFormat = formatId; } EsDateTime = hssfworkbook.CreateCellStyle(); EsDateTime.Alignment = HorizontalAlignment.CENTER; EsDateTime.FillPattern = FillPatternType.SOLID_FOREGROUND; EsDateTime.FillForegroundColor = HSSFColor.LIGHT_YELLOW.index; EsDateTime.SetFont(FNegra); EsDateTime.IsLocked = false; formatId = HSSFDataFormat.GetBuiltinFormat("YYYY-MM-DD"); if (formatId == -1) { var newDataFormat = hssfworkbook.CreateDataFormat(); EsDateTime.DataFormat = newDataFormat.GetFormat("YYYY-MM-DD"); } else { EsDateTime.DataFormat = formatId; } EstCeldasP = hssfworkbook.CreateCellStyle(); EstCeldasP.Alignment = HorizontalAlignment.CENTER; EstCeldasP.FillPattern = FillPatternType.SOLID_FOREGROUND; EstCeldasP.FillForegroundColor = HSSFColor.LIGHT_YELLOW.index; EstCeldasP.SetFont(FNegra); EstCeldasP.IsLocked = false; formatId = HSSFDataFormat.GetBuiltinFormat("0%"); if (formatId == -1) { var newDataFormat = hssfworkbook.CreateDataFormat(); EstCeldasP.DataFormat = newDataFormat.GetFormat("0%"); } else { EstCeldasP.DataFormat = formatId; } EstCeldasPercentLocked = hssfworkbook.CreateCellStyle(); EstCeldasPercentLocked.Alignment = HorizontalAlignment.CENTER; EstCeldasPercentLocked.FillPattern = FillPatternType.SOLID_FOREGROUND; EstCeldasPercentLocked.FillForegroundColor = HSSFColor.LIGHT_YELLOW.index; EstCeldasPercentLocked.SetFont(FNegra); EstCeldasPercentLocked.IsLocked = true; formatId = HSSFDataFormat.GetBuiltinFormat("0%"); if (formatId == -1) { var newDataFormat = hssfworkbook.CreateDataFormat(); EstCeldasPercentLocked.DataFormat = newDataFormat.GetFormat("0%"); } else { EstCeldasPercentLocked.DataFormat = formatId; } }
public ActionResult excelPrint() { HSSFWorkbook workbook = new HSSFWorkbook();// 创建一个Excel文件 // HSSFSheet sheet = workbook.CreateSheet();// 创建一个Excel的Sheet ISheet sheet = workbook.CreateSheet(); sheet.CreateFreezePane(1, 3); // 冻结 // 设置列宽 sheet.SetColumnWidth(0, 1000); sheet.SetColumnWidth(1, 3500); sheet.SetColumnWidth(2, 3500); sheet.SetColumnWidth(3, 6500); sheet.SetColumnWidth(4, 6500); sheet.SetColumnWidth(5, 6500); sheet.SetColumnWidth(6, 6500); sheet.SetColumnWidth(7, 2500); // Sheet样式 ICellStyle sheetStyle = workbook.CreateCellStyle(); // 背景色的设定 HSSFPalette XlPalette = workbook.GetCustomPalette(); // sheetStyle.FillBackgroundColor = 0; sheetStyle.FillPattern = FillPattern.FineDots; // sheetStyle.setFillBackgroundColor(HSSFColor.GREY_25_PERCENT.index); // 前景色的设定 // sheetStyle.setFillForegroundColor(HSSFColor.GREY_25_PERCENT.index); // 填充模式 // sheetStyle.setFillPattern(HSSFCellStyle.Equals) // 设置列的样式 for (int i = 0; i <= 14; i++) { sheet.SetDefaultColumnStyle((short)i, sheetStyle); } // 设置字体 IFont headfont = workbook.CreateFont(); //headfont.setFontName("黑体"); //headfont.setFontHeightInPoints((short)22);// 字体大小 //headfont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);// 加粗 headfont.FontName = "黑体"; headfont.FontHeightInPoints = (short)22; headfont.Boldweight = (short)10; headfont.IsBold = true; // 另一个样式 ICellStyle headstyle = workbook.CreateCellStyle(); //headstyle.setFont(headfont); //headstyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);// 左右居中 //headstyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);// 上下居中 //headstyle.setLocked(true); //headstyle.setWrapText(true);// 自动换行 headstyle.SetFont(headfont); headstyle.Alignment = HorizontalAlignment.Center; headstyle.VerticalAlignment = VerticalAlignment.Center; headstyle.IsLocked = true; headstyle.WrapText = true; // 另一个字体样式 IFont columnHeadFont = workbook.CreateFont(); //columnHeadFont.setFontName("宋体"); //columnHeadFont.setFontHeightInPoints((short)10); //columnHeadFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); columnHeadFont.FontName = "宋体"; columnHeadFont.FontHeightInPoints = (short)10; columnHeadFont.Boldweight = (short)10; // 列头的样式 ICellStyle columnHeadStyle = workbook.CreateCellStyle(); //columnHeadStyle.setFont(columnHeadFont); //columnHeadStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);// 左右居中 //columnHeadStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);// 上下居中 //columnHeadStyle.setLocked(true); //columnHeadStyle.setWrapText(true); //columnHeadStyle.setLeftBorderColor(HSSFColor.BLACK.index);// 左边框的颜色 //columnHeadStyle.setBorderLeft((short)1);// 边框的大小 //columnHeadStyle.setRightBorderColor(HSSFColor.BLACK.index);// 右边框的颜色 //columnHeadStyle.setBorderRight((short)1);// 边框的大小 //columnHeadStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN); // 设置单元格的边框为粗体 //columnHeadStyle.setBottomBorderColor(HSSFColor.BLACK.index); // 设置单元格的边框颜色 columnHeadStyle.SetFont(columnHeadFont); columnHeadStyle.Alignment = HorizontalAlignment.Center; columnHeadStyle.VerticalAlignment = VerticalAlignment.Center; columnHeadStyle.IsLocked = true; columnHeadStyle.WrapText = true; columnHeadStyle.LeftBorderColor = HSSFColor.Black.Index; columnHeadStyle.BorderLeft = BorderStyle.Thin; columnHeadStyle.RightBorderColor = HSSFColor.Black.Index; columnHeadStyle.BorderRight = BorderStyle.Thin; // 设置单元格的背景颜色(单元格的样式会覆盖列或行的样式) columnHeadStyle.FillForegroundColor = HSSFColor.White.Index; IFont font = workbook.CreateFont(); font.FontName = "宋体"; //font.setFontHeightInPoints((short)10); font.FontHeightInPoints = 10; // 普通单元格样式 ICellStyle style = workbook.CreateCellStyle(); style.SetFont(font); //style.setAlignment(HSSFCellStyle.ALIGN_LEFT);// 左右居中 //style.setVerticalAlignment(HSSFCellStyle.VERTICAL_TOP);// 上下居中 //style.setWrapText(true); //style.setLeftBorderColor(HSSFColor.BLACK.index); //style.setBorderLeft((short)1); //style.setRightBorderColor(HSSFColor.BLACK.index); //style.setBorderRight((short)1); //style.setBorderBottom(HSSFCellStyle.BORDER_THIN); // 设置单元格的边框为粗体 //style.setBottomBorderColor(HSSFColor.BLACK.index); // 设置单元格的边框颜色. //style.setFillForegroundColor(HSSFColor.WHITE.index);// 设置单元格的背景颜色. // 另一个样式 ICellStyle centerstyle = workbook.CreateCellStyle(); centerstyle.SetFont(font); centerstyle.Alignment = HorizontalAlignment.Center; // 左右居中 centerstyle.VerticalAlignment = VerticalAlignment.Center; // 上下居中 centerstyle.WrapText = true; //centerstyle.setLeftBorderColor(HSSFColor.BLACK.index); //centerstyle.setBorderLeft((short)1); //centerstyle.setRightBorderColor(HSSFColor.BLACK.index); //centerstyle.setBorderRight((short)1); //centerstyle.setBorderBottom(HSSFCellStyle.BORDER_THIN); // 设置单元格的边框为粗体 //centerstyle.setBottomBorderColor(HSSFColor.BLACK.index); // 设置单元格的边框颜色. //centerstyle.setFillForegroundColor(HSSFColor.WHITE.index);// 设置单元格的背景颜色. try { // 创建第一行 IRow row0 = sheet.CreateRow(0); // 设置行高 row0.Height = 900; //row0.setHeight((short)900); // 创建第一列 ICell cell0 = row0.CreateCell(0); cell0.SetCellValue(new HSSFRichTextString("中非发展基金投资项目调度会工作落实情况对照表")); cell0.CellStyle = headstyle; //cell0.SetCellStyle()=headstyle; /** * 合并单元格 * 第一个参数:第一个单元格的行数(从0开始) * 第二个参数:第二个单元格的行数(从0开始) * 第三个参数:第一个单元格的列数(从0开始) * 第四个参数:第二个单元格的列数(从0开始) */ CellRangeAddress range = new CellRangeAddress(0, 0, 0, 7); sheet.AddMergedRegion(range); // 创建第二行 IRow row1 = sheet.CreateRow(1); ICell cell1 = row1.CreateCell(0); cell1.SetCellValue(new HSSFRichTextString("本次会议时间:2009年8月31日 前次会议时间:2009年8月24日")); //cell1.SetCellStyle(centerstyle); cell1.CellStyle = centerstyle; // 合并单元格 range = new CellRangeAddress(1, 2, 0, 7); sheet.AddMergedRegion(range); // 第三行 IRow row2 = sheet.CreateRow(3); //row2.setHeight((short)750); row2.Height = 750; ICell cell = row2.CreateCell(0); cell.SetCellValue(new HSSFRichTextString("责任者")); cell.CellStyle = columnHeadStyle; cell = row2.CreateCell(1); cell.SetCellValue(new HSSFRichTextString("成熟度排序")); cell.CellStyle = columnHeadStyle; cell = row2.CreateCell(2); cell.SetCellValue(new HSSFRichTextString("事项")); cell.CellStyle = columnHeadStyle; cell = row2.CreateCell(3); cell.SetCellValue(new HSSFRichTextString("前次会议要求\n/新项目的项目概要")); cell.CellStyle = columnHeadStyle; cell = row2.CreateCell(4); cell.SetCellValue(new HSSFRichTextString("上周工作进展")); cell.CellStyle = columnHeadStyle; cell = row2.CreateCell(5); cell.SetCellValue(new HSSFRichTextString("本周工作计划")); cell.CellStyle = columnHeadStyle; cell = row2.CreateCell(6); cell.SetCellValue(new HSSFRichTextString("问题和建议")); cell.CellStyle = columnHeadStyle; cell = row2.CreateCell(7); cell.SetCellValue(new HSSFRichTextString("备 注")); cell.CellStyle = columnHeadStyle; // 访问数据库,得到数据集 List <Inventory_UserInfo> deitelVOList = null; //int m = 4; //int k = 4; for (int i = 0; i < deitelVOList.Count(); i++) { } // UserInfo vo = deitelVOList[i]; //String dname = vo.PerSonName; //List<Workinfo> workList = vo.getWorkInfoList(); //IRow row = sheet.CreateRow(m); //cell = row.createCell(0); //cell.SetCellValue(new HSSFRichTextString(dname)); //cell.CellStyle=centerstyle; //// 合并单元格 //range = new CellRangeAddress(m, m + workList.size() - 1, 0, 0); //sheet.addMergedRegion(range); //m = m + workList.size(); // for (int j = 0; j < workList.size(); j++) // { // Workinfo w = workList.get(j); // // 遍历数据集创建Excel的行 // row = sheet.getRow(k + j); // if (null == row) // { // row = sheet.createRow(k + j); // } // cell = row.createCell(1); // cell.setCellValue(w.getWnumber()); // cell.setCellStyle(centerstyle); // cell = row.createCell(2); // cell.setCellValue(new HSSFRichTextString(w.getWitem())); // cell.setCellStyle(style); // cell = row.createCell(3); // cell.setCellValue(new HSSFRichTextString(w.getWmeting())); // cell.setCellStyle(style); // cell = row.createCell(4); // cell.setCellValue(new HSSFRichTextString(w.getWbweek())); // cell.setCellStyle(style); // cell = row.createCell(5); // cell.setCellValue(new HSSFRichTextString(w.getWtweek())); // cell.setCellStyle(style); // cell = row.createCell(6); // cell.setCellValue(new HSSFRichTextString(w.getWproblem())); // cell.setCellStyle(style); // cell = row.createCell(7); // cell.setCellValue(new HSSFRichTextString(w.getWremark())); // cell.setCellStyle(style); // } // k = k + workList.size(); //} //// 列尾 //int footRownumber = sheet.getLastRowNum(); //HSSFRow footRow = sheet.createRow(footRownumber + 1); //HSSFCell footRowcell = footRow.createCell(0); //footRowcell.setCellValue(new HSSFRichTextString(" 审 定:XXX 审 核:XXX 汇 总:XX")); //footRowcell.setCellStyle(centerstyle); //range = new CellRangeAddress(footRownumber + 1, footRownumber + 1, 0, 7); //sheet.addMergedRegion(range); //HttpServletResponse response = getResponse(); //HttpServletRequest request = getRequest(); //String filename = "未命名.xls";//设置下载时客户端Excel的名称 // // 请见:http://zmx.iteye.com/blog/622529 //filename = Util.encodeFilename(filename, request); //response.setContentType("application/vnd.ms-excel"); //response.setHeader("Content-disposition", "attachment;filename=" + filename); //OutputStream ouputStream = response.getOutputStream(); //workbook.write(ouputStream); //ouputStream.flush(); //ouputStream.close(); } catch (Exception e) { string s = e.ToString(); } return(null); }
/// <summary> /// 设置标题 /// </summary> /// <param name="root"></param> /// <param name="workbook"></param> /// <param name="sheet"></param> /// <param name="columns"></param> internal void SetHeader <T>(Root root, HSSFWorkbook workbook, ISheet sheet, List <PropertyInfo> columns) { if (root.head.defaultwidth.HasValue) { //设置表格默认宽高 sheet.DefaultColumnWidth = root.head.defaultwidth.Value; //12 } if (root.head.defaultheight.HasValue) { //设置表格默认行高 sheet.DefaultRowHeight = (short)root.head.defaultheight.Value; //25 } if (!root.head.borderstyle.IsNullOrEmpty()) { string bStyle = root.head.borderstyle.Trim(); if (!string.IsNullOrEmpty(bStyle)) { switch (bStyle) { case "none": WholeBorderStyle = BorderStyle.None; break; case "solid": WholeBorderStyle = BorderStyle.Thin; break; case "dashed": WholeBorderStyle = BorderStyle.Dashed; break; case "dotted": WholeBorderStyle = BorderStyle.Dotted; break; case "double": WholeBorderStyle = BorderStyle.Double; break; default: WholeBorderStyle = BorderStyle.Thin; break; } } } XlPalette = workbook.GetCustomPalette(); if (!string.IsNullOrEmpty(root.head.bordercolor)) { Color co = ColorTranslator.FromHtml(root.head.bordercolor); XlPalette.SetColorAtIndex(HSSFColor.Plum.Index, (byte)co.R, (byte)co.G, (byte)co.B); WholeBorderColor = NPOI.HSSF.Util.HSSFColor.Plum.Index;//这句代码根据16进制不起作用,起到颜色初始化 } //创建行 List <IRow> rowList = new List <IRow>(); Int32 rowN = root.head.rowspan.GetInt32(); for (Int32 i = 0; i < rowN; i++) { IRow row = sheet.CreateRow(i); rowList.Add(row); } //合并单元格 //填充内容 for (Int32 i = 0; i < root.head.columns.Count; i++) { //读取最重要的区域,0=fromRow,1=toRow,2=fromColumn,3=toColumn ExportColumn ec = root.head.columns[i]; Int32[] c = ec.cellregion.Split(',').ToIntArray(); if (c[2] == c[3]) { PropertyInfo pi = typeof(T).GetProperty(ec.dataIndex); columns.Add(pi); } if (c[0] < c[1] || c[2] < c[3]) //例如1,1,2,2 第二行中的第3列,例如1,1,2,7 第二行中的(第3列到8列),合并列 { CellRangeAddress cellr = new CellRangeAddress(c[0], c[1], c[2], c[3]); sheet.AddMergedRegion(cellr); //设置边框 ((HSSFSheet)sheet).SetEnclosedBorderOfRegion(cellr, WholeBorderStyle, WholeBorderColor); } } // 是否创建表头 if (!rowList.IsNullOrEmpty() && rowList.Count > 0) { //填充内容 for (Int32 i = 0; i < root.head.columns.Count; i++) { //读取最重要的区域,0=fromRow,1=toRow,2=fromColumn,3=toColumn ExportColumn ec = root.head.columns[i]; Int32[] c = ec.cellregion.Split(',').ToIntArray(); CellRangeAddress cellr = new CellRangeAddress(c[0], c[1], c[2], c[3]); //计算text要插入的位置的索引 Int32 txtIndex = -1; Int32 txtRow = -1; ICellStyle cellStyle = GetCellStyle(workbook, ExcelStyle.Title, ec); if ((c[0] == c[1] && c[2] == c[3]) || (c[0] == c[1] && c[2] < c[3])) { //例如1,1,2,2 第二行中的第3列,例如1,1,2,7 第二行中的(第3列到8列) txtIndex = c[2]; txtRow = c[0]; ICell newCell = rowList[txtRow].CreateCell(txtIndex); //设置单元格的高度 if (!ec.height.IsNullOrEmpty()) { rowList[txtRow].Height = (short)(ec.height * 20); } SetHeadCellBold(ec); newCell.SetCellValue(ec.text); //水平对齐 默认居中 cellStyle.Alignment = ec.halign.IsNullOrEmpty() ? HorizontalAlignment.Center : ec.halign.ToHorAlign(); newCell.CellStyle = cellStyle; sheet.AddMergedRegion(cellr); //设置边框 ((HSSFSheet)sheet).SetEnclosedBorderOfRegion(cellr, WholeBorderStyle, WholeBorderColor); } if (c[0] < c[1] && c[2] == c[3]) //合并c[0]到c[1]行 ,列没变 , 'cellregion':'0,1,1,1', { txtIndex = c[2]; txtRow = c[0]; ICell newCell = rowList[txtRow].CreateCell(txtIndex); //设置单元格的高度 if (!ec.height.IsNullOrEmpty()) { rowList[txtRow].Height = (short)(ec.height * 20); } SetHeadCellBold(ec); newCell.SetCellValue(ec.text); //水平对齐 默认居中 cellStyle.Alignment = ec.halign.IsNullOrEmpty() ? HorizontalAlignment.Center : ec.halign.ToHorAlign(); newCell.CellStyle = cellStyle; sheet.AddMergedRegion(cellr); //设置边框 ((HSSFSheet)sheet).SetEnclosedBorderOfRegion(cellr, WholeBorderStyle, WholeBorderColor); } if (c[0] < c[1] && c[2] < c[3]) //合并c[0]到c[1]行 ,列没变 , 'cellregion':'4,5,2,4', { txtIndex = c[2]; txtRow = c[0]; ICell newCell = rowList[txtRow].CreateCell(txtIndex); //设置单元格的高度 if (!ec.height.IsNullOrEmpty()) { rowList[txtRow].Height = (short)(ec.height * 20); } SetHeadCellBold(ec); newCell.SetCellValue(ec.text); //水平对齐 默认居中 cellStyle.Alignment = ec.halign.IsNullOrEmpty() ? HorizontalAlignment.Center : ec.halign.ToHorAlign(); newCell.CellStyle = cellStyle; } //设置单元格的宽度 if (!root.head.defaultwidth.IsNullOrEmpty() && !ec.width.IsNullOrEmpty()) { sheet.SetColumnWidth(i, ec.width * 40); } else { sheet.AutoSizeColumn(i); //每列宽度自适应 } } } }
/// <summary> /// 导出报表 /// </summary> /// <param name="title"></param> /// <param name="fileName"></param> /// <param name="Listname"></param> /// <param name="dic"></param> /// <returns></returns> public ActionResult ExportData(string title, string fileName, string Listname, List <IGrouping <string, STAT_STAFF_LARGE_BUSI_D_Handle_SUB> > dic) { #region 加载模板文件到工作簿对象中 HSSFWorkbook hssfworkbook; using (FileStream file = new FileStream(Server.MapPath("/Areas/STAT/Views/STATTemp/业务办理分析.xls"), FileMode.Open, FileAccess.Read)) { hssfworkbook = new HSSFWorkbook(file); } #endregion #region 根据模板设置工作表的内容 ISheet sheet1 = hssfworkbook.GetSheet("业务办理分析"); int rowIndex = 2; sheet1.GetRow(0).Cells[0].SetCellValue(title); sheet1.GetRow(1).Cells[1].SetCellValue(Listname); if (dic != null && dic.Count > 0) { #region 数据单元格样式 var cellFont = hssfworkbook.CreateFont(); var cellStyle = hssfworkbook.CreateCellStyle(); cellStyle.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.White.Index; cellStyle.FillPattern = NPOI.SS.UserModel.FillPattern.SolidForeground; #endregion var i = 0; var beginRowIndex = rowIndex; foreach (var stat in dic) { ++i; var index = i; var hallname = stat.Key; var templist = stat.ToList(); var busiTotal = templist.Sum(m => m.BUSI_CNT); var convertTotal = templist.Sum(m => m.CONVERT_BUSI_CNT); var handleTotal = templist.Sum(m => m.HANDLE_DUR); var overTimeTotal = templist.Sum(m => m.OVERTIME_HANDLE_CNT); var localTotal = templist.Sum(m => m.LOCAL_CNT); beginRowIndex = rowIndex; foreach (var item in templist) { var xlsrow = sheet1.CreateRow(rowIndex); if (index == i) { xlsrow.CreateCell(0).SetCellValue(i); xlsrow.CreateCell(1).SetCellValue(stat.Key); } else { xlsrow.CreateCell(0); xlsrow.CreateCell(1); } xlsrow.CreateCell(2).SetCellValue(item.DLS_SERIALNAME); xlsrow.CreateCell(3).SetCellValue(item.BUSI_CNT); xlsrow.CreateCell(4).SetCellValue(item.CONVERT_BUSI_CNT.ToString("####")); xlsrow.CreateCell(5).SetCellValue(item.AverageHANDLE.ToTimeString()); xlsrow.CreateCell(6).SetCellValue(item.OVERTIME_HANDLE_CNT); xlsrow.CreateCell(7).SetCellValue(item.TimeoutRate.ToString("P")); xlsrow.CreateCell(8).SetCellValue(item.LOCAL_CNT); xlsrow.CreateCell(9).SetCellValue(item.CityRate.ToString("P")); rowIndex++; index++; #region 设置单元格样式 SetCellStype(xlsrow, cellStyle); #endregion } var totalrow = sheet1.CreateRow(rowIndex); totalrow.CreateCell(0); totalrow.CreateCell(1); totalrow.CreateCell(2).SetCellValue("合计"); totalrow.CreateCell(3).SetCellValue(busiTotal); totalrow.CreateCell(4).SetCellValue(convertTotal.ToString("####")); totalrow.CreateCell(5).SetCellValue((busiTotal == 0 ? 0 : handleTotal / busiTotal).ToTimeString()); totalrow.CreateCell(6).SetCellValue(overTimeTotal); totalrow.CreateCell(7).SetCellValue((busiTotal == 0 ? 0 : (decimal)overTimeTotal / busiTotal).ToString("P")); totalrow.CreateCell(8).SetCellValue(localTotal); totalrow.CreateCell(9).SetCellValue((busiTotal == 0 ? 0 : (decimal)localTotal / (decimal)busiTotal).ToString("P")); var cellRangeAddress = new CellRangeAddress(beginRowIndex, beginRowIndex + templist.Count, 0, 0); sheet1.AddMergedRegion(cellRangeAddress); var cellRangeAddress2 = new CellRangeAddress(beginRowIndex, beginRowIndex + templist.Count, 1, 1); sheet1.AddMergedRegion(cellRangeAddress2); beginRowIndex++; SetCellStype(sheet1.GetRow(rowIndex), cellStyle); var footercellFont = hssfworkbook.CreateFont(); var footercellStyle = hssfworkbook.CreateCellStyle(); footercellFont.Boldweight = (short)NPOI.SS.UserModel.FontBoldWeight.Bold; footercellStyle.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.Yellow.Index; footercellStyle.FillPattern = NPOI.SS.UserModel.FillPattern.SolidForeground; var HSSFPalette = hssfworkbook.GetCustomPalette(); Color c = ToColor("#fffdbb"); HSSFPalette.SetColorAtIndex(NPOI.HSSF.Util.HSSFColor.Yellow.Index, c.R, c.G, c.B); SetCellStype(totalrow, footercellStyle); rowIndex++; } } //强制Excel重新计算表中所有的公式 sheet1.ForceFormulaRecalculation = true; #endregion #region 写入到客户端 var ms = new MemoryStream(); //将工作簿的内容放到内存流中 hssfworkbook.Write(ms); ms.Seek(0, SeekOrigin.Begin); return(File(ms, "application/vnd.ms-excel", fileName + ".xls")); #endregion }
/// <summary> /// 普通单表导出npoi(集合数据+样式) /// </summary> /// <param name="sender"></param> /// <param name="e"></param> protected void Button2_Click(object sender, EventArgs e) { string filename = "test.xls"; Response.ContentType = "application/vnd.ms-excel"; Response.AddHeader("Content-Disposition", string.Format("attachment;filename={0}", filename)); Response.Clear(); HSSFWorkbook hssfworkbook = new HSSFWorkbook(); Sheet sheet1 = hssfworkbook.CreateSheet("Sheet1名称"); CellStyle style = hssfworkbook.CreateCellStyle(); style.Alignment = HorizontalAlignment.CENTER; style.FillBackgroundColor = HSSFColor.PINK.index; var row0 = sheet1.CreateRow(0).CreateCell(0); row0.SetCellValue("This is a Sample");//sheet标题 row0.CellStyle = style; var j = 17; #region 居中/自动换行 CellStyle styleCenter = hssfworkbook.CreateCellStyle(); //样式 styleCenter.Alignment = NPOI.SS.UserModel.HorizontalAlignment.CENTER; //文字水平对齐方式 styleCenter.VerticalAlignment = NPOI.SS.UserModel.VerticalAlignment.CENTER; //文字垂直对齐方式 styleCenter.WrapText = true; //自动换行 sheet1.CreateRow(j).CreateCell(j).CellStyle = styleCenter; var cell17 = sheet1.CreateRow(j).CreateCell(j); var cell172 = sheet1.CreateRow(j).CreateCell(j + 1); cell17.CellStyle = styleCenter; cell17.SetCellValue("VLOOKUP函数和“两列同时匹配”的应用,升的网易博客"); //cell172.SetCellValue("VLOOKUP函数和“两列同时匹配”的应用,升的网易博客"); j++; #endregion #region 设置宽高度 sheet1.SetColumnWidth(1, 20 * 256); //宽度-每个字符宽度是1/256。 所以20 * 256就是20个字符宽度。 var rowwh = sheet1.CreateRow(j); rowwh.HeightInPoints = 50; //高度 rowwh.CreateCell(j).SetCellValue("宽高度"); j++; #endregion #region 自适应宽度(对中文不友好)+自动换行 /*场景: * 12林学1班 * 12林学1班 */ CellStyle autoAndWrap = hssfworkbook.CreateCellStyle(); //样式 autoAndWrap.WrapText = true; //自动换行 var rowwhauto = sheet1.CreateRow(j); var cellauto = rowwhauto.CreateCell(j); cellauto.SetCellValue(j + "自适应宽高度自适应宽高度\n自适应宽高度自适应宽高度\n自适应宽高度自适应宽高度"); sheet1.AutoSizeColumn(j); cellauto.CellStyle = autoAndWrap; j++; #endregion #region 设置背景色 CellStyle style1 = hssfworkbook.CreateCellStyle(); style1.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.BLUE.index; style1.FillPattern = FillPatternType.SOLID_FOREGROUND; sheet1.CreateRow(j).CreateCell(j).CellStyle = style1; j++; #endregion #region 自定义背景色 HSSFPalette palette = hssfworkbook.GetCustomPalette(); //调色板实例 palette.SetColorAtIndex((short)8, (byte)184, (byte)204, (byte)228); HSSFColor hssFColor = palette.FindColor((byte)184, (byte)204, (byte)228); CellStyle style2 = hssfworkbook.CreateCellStyle(); style2.FillPattern = FillPatternType.SOLID_FOREGROUND; style2.FillForegroundColor = hssFColor.GetIndex(); sheet1.CreateRow(j).CreateCell(j).CellStyle = style2; j++; #endregion #region 设置字体颜色 CellStyle style3 = hssfworkbook.CreateCellStyle(); Font font1 = hssfworkbook.CreateFont(); font1.Color = hssFColor.GetIndex();//颜色 style3.SetFont(font1); var cell20 = sheet1.CreateRow(j).CreateCell(j); cell20.CellStyle = style3; cell20.SetCellValue("666666666"); j++; #endregion List <ModelStu> data = StuDaTa.GetData(); string[] arrthead = { "ID", "name", "age", "pc" }; sheet1.AddMergedRegion(new CellRangeAddress(0, 0, 0, arrthead.Length - 1)); Row row1 = sheet1.CreateRow(1); for (int i = 0; i < arrthead.Length; i++) { row1.CreateCell(i).SetCellValue(arrthead[i]); } for (int i = 0; i < data.Count; i++) { Row row = sheet1.CreateRow(i + 2); var colIndex = 0; row.CreateCell(colIndex++).SetCellValue(data[i].id); row.CreateCell(colIndex++).SetCellValue(data[i].name); row.CreateCell(colIndex++).SetCellValue(data[i].age); row.CreateCell(colIndex).SetCellValue(data[i].pc); } MemoryStream file = new MemoryStream(); hssfworkbook.Write(file); Response.BinaryWrite(file.GetBuffer()); Response.End(); hssfworkbook = null; file.Close(); file.Dispose(); }
private void ExportTickets(Tickets tickets) { string fileName; using (var form = new SaveFileDialog()) { form.Filter = "Excel (*.xls)|*.xls|All Files (*.*)|*.*"; form.RestoreDirectory = true; if (form.ShowDialog(this) != DialogResult.OK) { return; } fileName = form.FileName; } var workbook = new HSSFWorkbook(); var sheet = workbook.CreateSheet("Zendesk requests"); var headerStyle = CreateHeaderStyle(workbook); var dateStyle = CreateDateStyle(workbook); var wrapStyle = workbook.CreateCellStyle(); wrapStyle.WrapText = true; var urlFont = workbook.CreateFont(); urlFont.Underline = FontUnderlineType.Single; urlFont.Color = workbook.GetCustomPalette().FindColor(0, 0, 255).Indexed; var urlStyle = workbook.CreateCellStyle(); urlStyle.SetFont(urlFont); // Create the headers. int offset = 0; var row = sheet.CreateRow(0); AddHeader(row, ref offset, "ID", headerStyle); AddHeader(row, ref offset, "Status", headerStyle); AddHeader(row, ref offset, "Priority", headerStyle); AddHeader(row, ref offset, "Type", headerStyle); AddHeader(row, ref offset, "Subject", headerStyle); AddHeader(row, ref offset, "Description", headerStyle); AddHeader(row, ref offset, "Requester", headerStyle); AddHeader(row, ref offset, "Assignee", headerStyle); AddHeader(row, ref offset, "Created", headerStyle); AddHeader(row, ref offset, "Updated", headerStyle); AddHeader(row, ref offset, "Due", headerStyle); for (int i = 0; i < tickets.Items.Count; i++) { row = sheet.CreateRow(i + 1); var ticket = tickets.Items[i]; offset = 0; AddUrl(row, ref offset, (long)ticket["id"], (string)ticket["url"], urlStyle); AddCell(row, ref offset, Prettify((string)ticket["status"])); AddCell(row, ref offset, Prettify((string)ticket["priority"])); AddCell(row, ref offset, Prettify((string)ticket["type"])); AddCell(row, ref offset, (string)ticket["subject"]); AddCell(row, ref offset, (string)ticket["description"], wrapStyle); AddCell(row, ref offset, GetUser(tickets.Users, (long?)ticket["requester_id"])); AddCell(row, ref offset, GetUser(tickets.Users, (long?)ticket["assignee_id"])); AddCell(row, ref offset, (DateTime?)ticket["created_at"], dateStyle); AddCell(row, ref offset, (DateTime?)ticket["updated_at"], dateStyle); AddCell(row, ref offset, (DateTime?)ticket["due"], dateStyle); } using (var stream = File.Create(fileName)) { workbook.Write(stream); } try { Process.Start(fileName); } catch { // Ignore exceptions. } }
private void button1_Click(object sender, EventArgs e) { string fileName; //fileName = System.IO.Path.GetDirectoryName(sourceFileTexBox.Text) + "\\"; fileName = "list_" + System.IO.Path.GetFileName(sourceFileTexBox.Text); fileName += ".xls"; SaveFileDialog saveFileDia = new SaveFileDialog(); saveFileDia.Filter = "Excel文件|*.xls"; saveFileDia.FileName = fileName; DialogResult result = saveFileDia.ShowDialog(); if (DialogResult.OK == result) { fileName = saveFileDia.FileName; if (File.Exists(fileName)) { MessageBox.Show("目标文件已经存在!", "错误"); return; } try { IWorkbook wb = new HSSFWorkbook(); ISheet tb = wb.CreateSheet(System.IO.Path.GetFileName(sourceFileTexBox.Text)); tb.DisplayGridlines = false; tb.CreateRow(5).CreateCell(29).SetCellValue("備考"); tb.GetRow(5).CreateCell(3).SetCellValue("No."); tb.GetRow(5).CreateCell(5).SetCellValue("関数名"); tb.GetRow(5).CreateCell(17).SetCellValue("テスト方法"); ICellStyle bg = (HSSFCellStyle)wb.CreateCellStyle(); IFont ft = wb.CreateFont(); ft.Color = NPOI.HSSF.Util.HSSFColor.White.Index; bg.SetFont(ft); HSSFWorkbook wob = new HSSFWorkbook(); HSSFPalette pa = wob.GetCustomPalette(); NPOI.HSSF.Util.HSSFColor XlColour = pa.FindSimilarColor(23, 55, 93); bg.FillForegroundColor = XlColour.Indexed; //15000; bg.FillPattern = FillPattern.SolidForeground; tb.CreateRow(3); for (int i = 0; i < 50; i++) { tb.GetRow(3).CreateCell(i).CellStyle = bg; } tb.GetRow(3).GetCell(1).SetCellValue(System.IO.Path.GetFileName(sourceFileTexBox.Text)); ICellStyle Border3 = (HSSFCellStyle)wb.CreateCellStyle(); Border3.BorderTop = NPOI.SS.UserModel.BorderStyle.Thick; Border3.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thick; Border3.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin; tb.GetRow(5).GetCell(3).CellStyle = Border3; ICellStyle Border2 = (HSSFCellStyle)wb.CreateCellStyle(); Border2.BorderTop = NPOI.SS.UserModel.BorderStyle.Thick; Border2.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin; ICellStyle Border4 = (HSSFCellStyle)wb.CreateCellStyle(); Border4.BorderTop = NPOI.SS.UserModel.BorderStyle.Thick; Border4.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin; Border4.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin; ICellStyle Border5 = (HSSFCellStyle)wb.CreateCellStyle(); Border5.BorderTop = NPOI.SS.UserModel.BorderStyle.Thick; Border5.BorderRight = NPOI.SS.UserModel.BorderStyle.Thick; Border5.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin; ICellStyle Border6 = (HSSFCellStyle)wb.CreateCellStyle(); Border6.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin; Border6.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thick; Border6.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin; ICellStyle Border7 = (HSSFCellStyle)wb.CreateCellStyle(); Border7.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin; Border7.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin; Border7.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin; ICellStyle Border8 = (HSSFCellStyle)wb.CreateCellStyle(); Border8.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin; Border8.BorderRight = NPOI.SS.UserModel.BorderStyle.Thick; Border8.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin; ICellStyle Border9 = (HSSFCellStyle)wb.CreateCellStyle(); Border9.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin; Border9.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thick; Border9.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thick; ICellStyle Border10 = (HSSFCellStyle)wb.CreateCellStyle(); Border10.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin; Border10.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thick; ICellStyle Border11 = (HSSFCellStyle)wb.CreateCellStyle(); Border11.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin; Border11.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin; Border11.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thick; ICellStyle Border12 = (HSSFCellStyle)wb.CreateCellStyle(); Border12.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin; Border12.BorderRight = NPOI.SS.UserModel.BorderStyle.Thick; Border12.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thick; tb.GetRow(5).CreateCell(4).CellStyle = Border2; tb.GetRow(5).GetCell(5).CellStyle = Border4; for (int i = 6; i < 17; i++) { tb.GetRow(5).CreateCell(i).CellStyle = Border2; } tb.GetRow(5).GetCell(17).CellStyle = Border4; for (int i = 18; i < 29; i++) { tb.GetRow(5).CreateCell(i).CellStyle = Border2; } tb.GetRow(5).GetCell(29).CellStyle = Border4; for (int i = 30; i < 41; i++) { tb.GetRow(5).CreateCell(i).CellStyle = Border2; } tb.GetRow(5).CreateCell(41).CellStyle = Border5; ICellStyle Border1 = (HSSFCellStyle)wb.CreateCellStyle(); Border1.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin; Border1.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin; int lastline = 6; for (int i = 0; i < funcListBox.SelectedItems.Count; i++) { tb.CreateRow(6 + i).CreateCell(5).SetCellValue(funcListBox.SelectedItems[i].ToString()); tb.GetRow(6 + i).CreateCell(3).SetCellValue(" " + (i + 1).ToString()); tb.GetRow(6 + i).GetCell(3).CellStyle = Border6; tb.GetRow(6 + i).CreateCell(4).CellStyle = Border1; tb.GetRow(6 + i).GetCell(5).CellStyle = Border7; for (int j = 6; j < 17; j++) { tb.GetRow(6 + i).CreateCell(j).CellStyle = Border1; } tb.GetRow(6 + i).CreateCell(17).CellStyle = Border7; for (int j = 18; j < 29; j++) { tb.GetRow(6 + i).CreateCell(j).CellStyle = Border1; } tb.GetRow(6 + i).CreateCell(29).CellStyle = Border7; for (int j = 30; j < 41; j++) { tb.GetRow(6 + i).CreateCell(j).CellStyle = Border1; } tb.GetRow(6 + i).CreateCell(41).CellStyle = Border8; lastline += 1; } tb.CreateRow(lastline).CreateCell(3).CellStyle = Border9; tb.GetRow(lastline).CreateCell(4).CellStyle = Border10; tb.GetRow(lastline).CreateCell(5).CellStyle = Border11; for (int j = 6; j < 17; j++) { tb.GetRow(lastline).CreateCell(j).CellStyle = Border10; } tb.GetRow(lastline).CreateCell(17).CellStyle = Border11; for (int j = 18; j < 29; j++) { tb.GetRow(lastline).CreateCell(j).CellStyle = Border10; } tb.GetRow(lastline).CreateCell(29).CellStyle = Border11; for (int j = 30; j < 41; j++) { tb.GetRow(lastline).CreateCell(j).CellStyle = Border10; } tb.GetRow(lastline).CreateCell(41).CellStyle = Border12; for (int i = 0; i < 50; i++) { tb.SetColumnWidth(i, 1024); } using (FileStream fs = File.OpenWrite(fileName)) { wb.Write(fs); } } catch (Exception ex) { MessageBox.Show(ex.ToString()); return; } MessageBox.Show("生成成功!", "成功"); } }
/// <summary> /// 导出Excel /// </summary> /// <param name="lists"></param> /// <param name="head">中文列名对照</param> /// <param name="fileName">文件名</param> public static void ExportExcel(List <T> lists, Dictionary <string, string> head, string fileName) { HSSFWorkbook workbook = new HSSFWorkbook(); MemoryStream ms = new MemoryStream(); HSSFSheet sheet = null; HSSFRow headerRow = null; //设置表头样式 ICellStyle headStyle = workbook.CreateCellStyle(); headStyle.VerticalAlignment = VerticalAlignment.CENTER; headStyle.Alignment = HorizontalAlignment.CENTER; IFont font = workbook.CreateFont(); font.FontHeight = 14 * 14; font.Boldweight = 1000; headStyle.SetFont(font); //设置分组样式 HSSFPalette palette = workbook.GetCustomPalette(); //调色板实例 palette.SetColorAtIndex((byte)8, (byte)204, (byte)204, (byte)0); HSSFColor hssFColor = palette.FindColor((byte)204, (byte)204, (byte)0); ICellStyle GroupStyle = workbook.CreateCellStyle(); GroupStyle.FillPattern = FillPatternType.SOLID_FOREGROUND; GroupStyle.FillForegroundColor = hssFColor.GetIndex(); IFont Groupfont = workbook.CreateFont(); Groupfont.Boldweight = 1000; GroupStyle.SetFont(Groupfont); bool h = false; Type type = typeof(T); PropertyInfo[] properties = type.GetProperties(); var mod = lists.Count() % 65535; var index = lists.Count() / 65535; if (mod > 0) { index = index + 1; } //没有数据时导出表头 if (index == 0) { int i = 0; sheet = workbook.CreateSheet("Sheet0") as HSSFSheet; headerRow = sheet.CreateRow(0) as HSSFRow; foreach (var dic in head)//循环列表头集合作为列数 { string[] names = dic.Key.ToString().Split('@'); string colname = dic.Value.ToString(); ICell cell = headerRow.CreateCell(i); cell.CellStyle = headStyle; cell.SetCellValue(colname); i++; } } for (int idx = 1; idx <= index; idx++) { sheet = workbook.CreateSheet("Sheet" + idx) as HSSFSheet; headerRow = sheet.CreateRow(0) as HSSFRow; var count = 65535; if (idx == index) { count = mod; } for (var j = 0; j < count; j++)//循环记录总数作为行数 { HSSFRow dataRow = sheet.CreateRow(j + 1) as HSSFRow; int i = 0; foreach (var dic in head)//循环列表头集合作为列数 { string[] names = dic.Key.ToString().Split('@'); string colname = dic.Value.ToString(); string name = names[0]; bool isTrue = false;//是否基础数据 if (names.Length == 2) { isTrue = bool.Parse(names[1]); } var info = properties.Where(x => x.Name == name).FirstOrDefault(); object value = info == null ? null : info.GetValue(lists[65535 * (idx - 1) + j], null); string colvalue = value == null ? "" : value.ToString(); //if (isTrue)//获取基础数据 //{ // colvalue = HtmlExtensions.GetBasicObjectNameByValue(colvalue); //} if (!h) { if ((!name.Equals("IsGroup"))) { ICell cell = headerRow.CreateCell(i); cell.CellStyle = headStyle; cell.SetCellValue(colname); if (value != null) { Type t = value.GetType(); string strt = t.Name; if (t.Name == "Nullable`1") { strt = t.GetGenericArguments()[0].Name; } switch (strt) { case "Decimal": case "Double": dataRow.CreateCell(i).SetCellValue(Double.Parse(value.ToString())); break; case "Int": dataRow.CreateCell(i).SetCellValue(int.Parse(value.ToString())); break; case "Float": dataRow.CreateCell(i).SetCellValue(float.Parse(value.ToString())); break; default: dataRow.CreateCell(i).SetCellValue(colvalue); break; } } else { dataRow.CreateCell(i).SetCellValue(colvalue); } } } else { if ((!name.Equals("IsGroup"))) { if (value != null) { Type t = value.GetType(); string strt = t.Name; if (t.Name == "Nullable`1") { strt = t.GetGenericArguments()[0].Name; } switch (strt) { case "Decimal": case "Double": dataRow.CreateCell(i).SetCellValue(Double.Parse(value.ToString())); break; case "Int": dataRow.CreateCell(i).SetCellValue(int.Parse(value.ToString())); break; case "Float": dataRow.CreateCell(i).SetCellValue(float.Parse(value.ToString())); break; default: dataRow.CreateCell(i).SetCellValue(colvalue); break; } } else { dataRow.CreateCell(i).SetCellValue(colvalue); } } } //========================对特定标志行设置颜色======================== if (name.Equals("IsGroup") && colvalue.Equals("Y")) { for (int m = 0; m < i; m++) { dataRow.GetCell(m).CellStyle = GroupStyle; } } //================================end================================= i++; } h = true; } } workbook.Write(ms); ms.Flush(); ms.Position = 0; sheet = null; headerRow = null; workbook = null; using (FileStream fs = new FileStream(fileName, FileMode.Create, FileAccess.Write)) { byte[] data = ms.ToArray(); fs.Write(data, 0, data.Length); fs.Flush(); } ms.Close(); ms.Dispose(); }
public MemoryStream GetExcelPackage() { using (var fs = new MemoryStream()) { var workbook = new HSSFWorkbook(); //var img = Image.FromFile(System.IO.Directory.GetCurrentDirectory() + "/gitea-sm.png"); //var stream = new MemoryStream();// img.Size //img.Save(stream, System.Drawing.Imaging.ImageFormat.Png); //FileStream stream = new FileStream(System.IO.Directory.GetCurrentDirectory() + "/gitea-sm.png", FileMode.Open, FileAccess.Read); //var arr = new byte[stream.Length]; //stream.Read(arr, 0, arr.Length); //var arr = File.ReadAllBytes(System.IO.Directory.GetCurrentDirectory() + "/gitea-sm.png"); // workbook.AddPicture(arr, PictureType.PNG); //stream.Close(); LoadStyle(workbook); var sheet = workbook.CreateSheet(SheetName); //添加图片 var data = File.ReadAllBytes(System.IO.Directory.GetCurrentDirectory() + "/gitea-sm.png"); var pictureIndex = workbook.AddPicture(data, PictureType.PNG); var helper = workbook.GetCreationHelper(); var drawing = sheet.CreateDrawingPatriarch(); var anchor = helper.CreateClientAnchor(); anchor.Col1 = 0; //0 index based column anchor.Row1 = 0; //0 index based row var picture = drawing.CreatePicture(anchor, pictureIndex); picture.Resize(1, 2); sheet.DefaultColumnWidth = 4; sheet.DisplayGridlines = false; SetCell(workbook, sheet, 2, 2, 22, 29, $"発行年月日 {PublishDate.ToString("yyyy年MM月dd日")}", _style12); SetCell(workbook, sheet, 5, 11, 1, 13, $"{ToCompany} 御中", _style10); SetCell(workbook, sheet, 5, 5, 17, 29, FromCompany, _style12); SetCell(workbook, sheet, 6, 6, 17, 29, FromDepartment, _style11); ////设置标题 SetCell(workbook, sheet, 16, 17, 0, 30, Title, _style16); //设置周期 SetCell(workbook, sheet, 20, 20, 1, 12, Period.ToString("yyyy年MM月度"), _style14); #region 代理商信息 SetCell(workbook, sheet, 22, 23, 1, 4, "代理店コード", _styleTopLeft); SetCell(workbook, sheet, 22, 23, 5, 13, AgentMessage.AgentCode, _styleTopRight); SetCell(workbook, sheet, 24, 25, 1, 4, "振込日", _styleLeft); SetCell(workbook, sheet, 24, 25, 5, 13, AgentMessage.DemittanceDate?.ToString("yyyy年MM月dd日"), _styleRight); SetCell(workbook, sheet, 26, 27, 1, 4, "振込金額", _styleBottomLeft); SetCell(workbook, sheet, 26, 27, 5, 13, AgentMessage.DemittanceAmount.ToString(), _styleBottomRight); #endregion #region 代理商帐户信息 SetCell(workbook, sheet, 22, 23, 17, 20, "金融機関名", _styleTopLeft); SetCell(workbook, sheet, 22, 23, 21, 29, AgentAccount.BankName, _styleTopRight); SetCell(workbook, sheet, 24, 25, 17, 20, "支店名", _styleLeft); SetCell(workbook, sheet, 24, 25, 21, 29, AgentAccount.BranchBankName, _styleRight); SetCell(workbook, sheet, 26, 27, 17, 20, "口座番号", _styleLeft); SetCell(workbook, sheet, 26, 27, 21, 22, AgentAccount.AccountMode, _dataStyle); SetCell(workbook, sheet, 26, 27, 23, 29, AgentAccount.Account, _styleRight); SetCell(workbook, sheet, 28, 29, 17, 20, "口座名義", _styleBottomLeft); SetCell(workbook, sheet, 28, 29, 21, 29, AgentAccount.AccountOwner, _styleBottomRight); #endregion #region 列表 HSSFPalette palette = workbook.GetCustomPalette(); //调色板实例 palette.SetColorAtIndex(11, 252, 228, 214); _colotStyle.FillForegroundColor = 11; SetCell(workbook, sheet, 32, 32, 1, 1, null, _colotStyle); SetCell(workbook, sheet, 32, 32, 2, 4, "コード", _colotStyle); SetCell(workbook, sheet, 32, 32, 5, 9, "加盟店", _colotStyle); SetCell(workbook, sheet, 32, 32, 10, 14, "対象期間", _colotStyle); SetCell(workbook, sheet, 32, 32, 15, 18, "決済利用額", _colotStyle); SetCell(workbook, sheet, 32, 32, 19, 21, "手数料率", _colotStyle); SetCell(workbook, sheet, 32, 32, 22, 25, "手数料額", _colotStyle); SetCell(workbook, sheet, 32, 32, 26, 29, "備考", _colotStyle); var rowIndex = 33; var sn = 1; var total = 0m; foreach (var item in Data) { total += item.RateAmount; SetCell(workbook, sheet, rowIndex, rowIndex, 1, 1, sn++.ToString(), _dataStyle); SetCell(workbook, sheet, rowIndex, rowIndex, 2, 4, item.Code, _dataStyle); SetCell(workbook, sheet, rowIndex, rowIndex, 5, 9, item.Name, _dataStyle); SetCell(workbook, sheet, rowIndex, rowIndex, 10, 14, item.TimeSection, _dataStyle); SetCell(workbook, sheet, rowIndex, rowIndex, 15, 18, item.Amount.ToString(), _dataStyle); SetCell(workbook, sheet, rowIndex, rowIndex, 19, 21, item.Rate.ToString(), _dataStyle); SetCell(workbook, sheet, rowIndex, rowIndex, 22, 25, item.RateAmount.ToString(), _dataStyle); SetCell(workbook, sheet, rowIndex, rowIndex, 26, 29, item.Memo, _dataStyle); rowIndex++; } SetCell(workbook, sheet, rowIndex, rowIndex, 1, 1, "計", _styleTotal); SetCell(workbook, sheet, rowIndex, rowIndex, 2, 4, "", _styleTotal); SetCell(workbook, sheet, rowIndex, rowIndex, 5, 9, "", _styleTotal); SetCell(workbook, sheet, rowIndex, rowIndex, 10, 14, "税抜き", _styleTotal); SetCell(workbook, sheet, rowIndex, rowIndex, 15, 18, "", _styleTotal); SetCell(workbook, sheet, rowIndex, rowIndex, 19, 21, "", _styleTotal); SetCell(workbook, sheet, rowIndex, rowIndex, 22, 25, total.ToString(), _styleTotal); SetCell(workbook, sheet, rowIndex, rowIndex, 26, 29, "", _styleTotal); #endregion workbook.Write(fs); return(fs); } }
//public IActionResult LoadExcel(IFormFile file) //{ // IExcelService _IExcelService = new ExcelService(); // try // { // var rows = _IExcelService.ExcelToList<ImportExcelModel>(file.OpenReadStream(), file.FileName); // return Content("导入成功!"); // } // catch (Exception e) { return Content("导入失败"); } //} #endregion #region Excel导出 public static byte[] ExportToExcel <T>(List <T> entities, ExcelModel model) { HSSFWorkbook workbook = new HSSFWorkbook(); HSSFPalette palette = workbook.GetCustomPalette(); HSSFColor hssFColor; byte red, green, bule; if (model.DataFields == null || model.DataFields.Length == 0) { model.DataFields = model.ColumnNames; } #region 标题 // 标题字体 IFont titleFont = workbook.CreateFont(); var titleColor = model.TitleRow.CellStyle.Font.Color; red = titleColor[0]; green = titleColor[1]; bule = titleColor[2]; palette.SetColorAtIndex(8, red, green, bule); hssFColor = palette.FindColor(red, green, bule); titleFont.Color = hssFColor.Indexed; titleFont.FontHeightInPoints = model.TitleRow.CellStyle.Font.FontHeightInPoints; // 标题前景色 var titleForegroundColor = model.TitleRow.CellStyle.FillForegroundColor; red = titleForegroundColor[0]; green = titleForegroundColor[1]; bule = titleForegroundColor[2]; palette.SetColorAtIndex(9, red, green, bule); hssFColor = palette.FindColor(red, green, bule); // 标题 ICellStyle titleStyle = workbook.CreateCellStyle(); titleStyle.SetFont(titleFont); titleStyle.FillPattern = FillPattern.SolidForeground; titleStyle.FillForegroundColor = hssFColor.Indexed; titleStyle.Alignment = HorizontalAlignment.Center; titleStyle.VerticalAlignment = VerticalAlignment.Center; ISheet sheet = workbook.CreateSheet("Sheet1"); IRow row = sheet.CreateRow(0); row.HeightInPoints = model.DataRow.HeightInPoints; ICell cell; for (int i = 0; i < model.ColumnNames.Length; i++) { cell = row.CreateCell(i); cell.CellStyle = titleStyle; cell.SetCellValue(model.ColumnNames[i]); } #endregion if (entities.Count > 0) { ICellStyle cellStyle = workbook.CreateCellStyle(); IFont cellFont = workbook.CreateFont(); cellFont.FontHeightInPoints = model.DataRow.CellStyle.Font.FontHeightInPoints; cellStyle.SetFont(cellFont); cellStyle.VerticalAlignment = VerticalAlignment.Center; for (int i = 0; i < entities.Count; i++) { row = sheet.CreateRow(i + 1); row.HeightInPoints = model.DataRow.HeightInPoints; object entity = entities[i]; for (int j = 0; j < model.DataFields.Length; j++) { // 数据行 object cellValue = entity.GetType().GetProperty(model.DataFields[j]).GetValue(entity); cell = row.CreateCell(j); cell.CellStyle = cellStyle; cell.SetCellValue(Convert.ToString(cellValue)); } } // 调整列宽 for (int i = 0; i <= entities.Count; i++) { sheet.AutoSizeColumn(i); } for (int columnNum = 0; columnNum <= model.ColumnNames.Length; columnNum++) { int columnWidth = sheet.GetColumnWidth(columnNum) / 256; for (int rowNum = 1; rowNum <= sheet.LastRowNum; rowNum++) { IRow currentRow; if (sheet.GetRow(rowNum) == null) { currentRow = sheet.CreateRow(rowNum); } else { currentRow = sheet.GetRow(rowNum); } if (currentRow.GetCell(columnNum) != null) { ICell currentCell = currentRow.GetCell(columnNum); int length = Encoding.Default.GetBytes(currentCell.ToString()).Length; if (columnWidth < length) { columnWidth = length; } } } columnWidth = Math.Min(columnWidth, 255); sheet.SetColumnWidth(columnNum, columnWidth * 256); } } using (MemoryStream ms = new MemoryStream()) { workbook.Write(ms); return(ms.GetBuffer()); } }
public MemoryStream GetExcelPackage() { using (var fs = new MemoryStream()) { var workbook = new HSSFWorkbook(); LoadStyle(workbook); var sheet = workbook.CreateSheet(SheetName); sheet.DefaultColumnWidth = 4; sheet.DisplayGridlines = false; SetCell(workbook, sheet, 2, 2, 22, 29, $"発行年月日 {PublishDate.ToString("yyyy年MM月dd日")}", _style12); SetCell(workbook, sheet, 5, 11, 1, 13, $"{ToCompany} 御中", _style10); SetCell(workbook, sheet, 5, 5, 17, 29, FromCompany, _style12); SetCell(workbook, sheet, 6, 6, 17, 29, FromDepartment, _style11); ////设置标题 SetCell(workbook, sheet, 16, 17, 0, 30, Title, _style16); //设置周期 SetCell(workbook, sheet, 20, 20, 1, 12, Period.ToString("yyyy年MM月度"), _style14); #region 代理商信息 SetCell(workbook, sheet, 22, 23, 1, 4, "代理店コード", _styleTopLeft); SetCell(workbook, sheet, 22, 23, 5, 13, AgentMessage.AgentCode, _styleTopRight); SetCell(workbook, sheet, 24, 25, 1, 4, "振込日", _styleLeft); SetCell(workbook, sheet, 24, 25, 5, 13, AgentMessage.DemittanceDate?.ToString("yyyy年MM月dd日"), _styleRight); SetCell(workbook, sheet, 26, 27, 1, 4, "振込金額", _styleBottomLeft); SetCell(workbook, sheet, 26, 27, 5, 13, AgentMessage.DemittanceAmount.ToString(), _styleBottomRight); #endregion #region 代理商帐户信息 SetCell(workbook, sheet, 22, 23, 17, 20, "金融機関名", _styleTopLeft); SetCell(workbook, sheet, 22, 23, 21, 29, AgentAccount.BankName, _styleTopRight); SetCell(workbook, sheet, 24, 25, 17, 20, "支店名", _styleLeft); SetCell(workbook, sheet, 24, 25, 21, 29, AgentAccount.BranchBankName, _styleRight); SetCell(workbook, sheet, 26, 27, 17, 20, "口座番号", _styleLeft); SetCell(workbook, sheet, 26, 27, 21, 22, AgentAccount.AccountMode, _dataStyle); SetCell(workbook, sheet, 26, 27, 23, 29, AgentAccount.Account, _styleRight); SetCell(workbook, sheet, 28, 29, 17, 20, "口座名義", _styleBottomLeft); SetCell(workbook, sheet, 28, 29, 21, 29, AgentAccount.AccountOwner, _styleBottomRight); #endregion #region 列表 HSSFPalette palette = workbook.GetCustomPalette(); //调色板实例 palette.SetColorAtIndex(11, 252, 228, 214); _colotStyle.FillForegroundColor = 11; SetCell(workbook, sheet, 32, 32, 1, 1, null, _colotStyle); SetCell(workbook, sheet, 32, 32, 2, 4, "コード", _colotStyle); SetCell(workbook, sheet, 32, 32, 5, 9, "加盟店", _colotStyle); SetCell(workbook, sheet, 32, 32, 10, 14, "対象期間", _colotStyle); SetCell(workbook, sheet, 32, 32, 15, 18, "決済利用額", _colotStyle); SetCell(workbook, sheet, 32, 32, 19, 21, "手数料率", _colotStyle); SetCell(workbook, sheet, 32, 32, 22, 25, "手数料額", _colotStyle); SetCell(workbook, sheet, 32, 32, 26, 29, "備考", _colotStyle); var rowIndex = 33; var sn = 1; var total = 0m; foreach (var item in Data) { total += item.RateAmount; SetCell(workbook, sheet, rowIndex, rowIndex, 1, 1, sn++.ToString(), _dataStyle); SetCell(workbook, sheet, rowIndex, rowIndex, 2, 4, item.Code, _dataStyle); SetCell(workbook, sheet, rowIndex, rowIndex, 5, 9, item.Name, _dataStyle); SetCell(workbook, sheet, rowIndex, rowIndex, 10, 14, item.TimeSection, _dataStyle); SetCell(workbook, sheet, rowIndex, rowIndex, 15, 18, item.Amount.ToString(), _dataStyle); SetCell(workbook, sheet, rowIndex, rowIndex, 19, 21, item.Rate.ToString(), _dataStyle); SetCell(workbook, sheet, rowIndex, rowIndex, 22, 25, item.RateAmount.ToString(), _dataStyle); SetCell(workbook, sheet, rowIndex, rowIndex, 26, 29, item.Memo, _dataStyle); rowIndex++; } SetCell(workbook, sheet, rowIndex, rowIndex, 1, 1, "計", _styleTotal); SetCell(workbook, sheet, rowIndex, rowIndex, 2, 4, "", _styleTotal); SetCell(workbook, sheet, rowIndex, rowIndex, 5, 9, "", _styleTotal); SetCell(workbook, sheet, rowIndex, rowIndex, 10, 14, "税抜き", _styleTotal); SetCell(workbook, sheet, rowIndex, rowIndex, 15, 18, "", _styleTotal); SetCell(workbook, sheet, rowIndex, rowIndex, 19, 21, "", _styleTotal); SetCell(workbook, sheet, rowIndex, rowIndex, 22, 25, total.ToString(), _styleTotal); SetCell(workbook, sheet, rowIndex, rowIndex, 26, 29, "", _styleTotal); #endregion workbook.Write(fs); return(fs); } }
private static void ApplyOptions(HSSFWorkbook wk, bool useTitle, int splitCount, ExcelOptions option) { try { #region 单元格样式 if (option != null && option.CellStyle != null && wk != null) { var cellStyle = wk.CreateCellStyle(); var cellStylex = wk.CreateCellStyle(); cellStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center; cellStyle.VerticalAlignment = NPOI.SS.UserModel.VerticalAlignment.Center; if (option.CellStyle.FontHeight.HasValue) { var font = wk.CreateFont(); font.FontHeight = option.CellStyle.FontHeight.Value * 100; font.FontName = "宋体"; cellStyle.SetFont(font); } if (option.CellStyle.UseBorder) { cellStyle.BorderBottom = BorderStyle.Thin; cellStyle.BorderTop = BorderStyle.Thin; cellStyle.BorderRight = BorderStyle.Thin; } cellStylex.CloneStyleFrom(cellStyle); if (option.CellStyle.UseSkipColor) { HSSFPalette palette = wk.GetCustomPalette(); //调色板实例 palette.SetColorAtIndex((short)8, (byte)184, (byte)204, (byte)228); var color = palette.FindColor((byte)184, (byte)204, (byte)228); cellStylex.FillBackgroundColor = color.Indexed; cellStylex.FillPattern = FillPattern.SolidForeground; } for (int i = 0; i < wk.NumberOfSheets; i++) { var sheet = wk.GetSheetAt(i); if (sheet != null) { for (int j = sheet.FirstRowNum; j <= sheet.LastRowNum; j++) { var row = sheet.GetRow(j); if (row != null) { for (int k = row.FirstCellNum; k <= row.LastCellNum; k++) { var cell = row.GetCell(k); if (cell != null) { if (j % 2 == 0) { cell.CellStyle = cellStyle; } else { cell.CellStyle = cellStylex; } } } if (option.AutoSizeColumn) { sheet.AutoSizeColumn(j); } } } } } } #endregion #region 合并区域参数 if (option != null && option.MergedRegions != null && wk != null) { //不允许区域相交 if (option.IsValidMergedRegions) { foreach (var mg in option.MergedRegions) { //先确定属于那个Excel var startSheetIndex = (mg.FirstRow + 1) / (splitCount - (useTitle ? 1 : 0)) + ((mg.FirstRow + 1) % (splitCount - (useTitle ? 1 : 0)) == 0 ? 0 : 1) - 1; var endSheetIndex = (mg.LastRow + 1) / (splitCount - (useTitle ? 1 : 0)) + ((mg.LastRow + 1) % (splitCount - (useTitle ? 1 : 0)) == 0 ? 0 : 1) - 1; var firstRow = 0; var lastRow = 0; //合并区域在同一Sheet if (startSheetIndex == endSheetIndex) { firstRow = mg.FirstRow - (startSheetIndex * (splitCount - (useTitle ? 1 : 0))) + (useTitle ? 1 : 0); lastRow = mg.LastRow - (endSheetIndex * (splitCount - (useTitle ? 1 : 0))) + (useTitle ? 1 : 0); wk.GetSheetAt(startSheetIndex).AddMergedRegion(new CellRangeAddress(firstRow, lastRow, mg.FirstCol, mg.LastCol)); } else //if (endSheetIndex - startSheetIndex > 1) { firstRow = mg.FirstRow - (startSheetIndex * (splitCount - (useTitle ? 1 : 0))) + (useTitle ? 1 : 0); lastRow = wk.GetSheetAt(startSheetIndex).LastRowNum; wk.GetSheetAt(startSheetIndex).AddMergedRegion(new CellRangeAddress(firstRow, lastRow, mg.FirstCol, mg.LastCol)); for (int i = startSheetIndex + 1; i <= endSheetIndex; i++) { firstRow = wk.GetSheetAt(i).FirstRowNum + (useTitle ? 1 : 0); lastRow = i == endSheetIndex ? (mg.LastRow - (endSheetIndex * (splitCount - (useTitle ? 1 : 0))) + (useTitle ? 1 : 0)) : wk.GetSheetAt(i).LastRowNum; wk.GetSheetAt(i).AddMergedRegion(new CellRangeAddress(firstRow, lastRow, mg.FirstCol, mg.LastCol)); } } } } } #endregion } catch { } }
public static void ListToExcel(string fileName, string templetName, List <RecordMode> dt, string titleName, string sheetName) { FileStream fs1 = new FileStream(templetName, FileMode.Open, FileAccess.Read); IWorkbook workBook = new XSSFWorkbook(fs1); ISheet sheet = workBook.GetSheet(sheetName); List <Color> colorList = new List <Color>(); colorList.Add(Color.FromArgb(255, 0, 0)); //红色 colorList.Add(Color.FromArgb(255, 255, 255)); //白色 HSSFWorkbook hssfWorkbook = new HSSFWorkbook(); HSSFPalette palette = hssfWorkbook.GetCustomPalette(); palette.SetColorAtIndex(999, colorList[0].R, colorList[0].G, colorList[0].B); palette.SetColorAtIndex(998, colorList[1].R, colorList[1].G, colorList[1].B); ////第一行 //IRow row0 = sheet.GetRow(0); //ICell cellTitle = row0.GetCell(0); //cellTitle.SetCellValue(titleName); //第二行 IRow row1 = sheet.GetRow(0); for (int j = 0; j < 8; j++) { ICell cell = row1.GetCell(j); cell.SetCellValue(cell.ToString()); } for (int i = 0; i < dt.Count; i++) { IRow rowi = sheet.CreateRow(i + 1); for (int j = 0; j < 8; j++) { //创建单元格 ICell cell = rowi.CreateCell(j); //给单元格赋值 cell.SetCellValue(dt[i][j].ToString()); //给单元格设置样式 ICellStyle colorStyle = workBook.CreateCellStyle(); colorStyle.FillPattern = FillPattern.SolidForeground; //cell.CellStyle.BorderBottom = BorderStyle.Thin; //cell.CellStyle.BorderLeft = BorderStyle.Thin; if (dt[i].isYC == true && (j == 4 || j == 5)) { var v1 = palette.FindColor(colorList[0].R, colorList[0].G, colorList[0].B); if (v1 == null) { throw new Exception("Color is not in Palette"); } colorStyle.FillForegroundColor = v1.Indexed; cell.CellStyle = colorStyle; //cell.CellStyle.BorderDiagonalColor = IndexedColors.Yellow.Index; //cell.CellStyle.FillPattern = FillPattern.SolidForeground; //cell.CellStyle.FillForegroundColor = HSSFColor.Yellow.Index; } else { var v1 = palette.FindColor(colorList[1].R, colorList[1].G, colorList[1].B); if (v1 == null) { throw new Exception("Color is not in Palette"); } colorStyle.FillForegroundColor = v1.Indexed; cell.CellStyle = colorStyle; //cell.CellStyle.BorderDiagonalColor = IndexedColors.Yellow.Index; //cell.CellStyle.FillPattern = FillPattern.SolidForeground; //cell.CellStyle.FillForegroundColor = HSSFColor.White.Index; } } } using (FileStream fs = new FileStream(fileName, FileMode.Create, FileAccess.Write)) { workBook.Write(fs); } }