static void Main(string[] args) { IWorkbook workbook = new XSSFWorkbook(); ISheet sheet = workbook.CreateSheet("Sheet1"); //increase the width of Column A sheet.SetColumnWidth(0, 5000); //create the format instance IDataFormat format = workbook.CreateDataFormat(); // Create a row and put some cells in it. Rows are 0 based. ICell cell = sheet.CreateRow(0).CreateCell(0); //number format with 2 digits after the decimal point - "1.20" SetValueAndFormat(workbook, cell, 1.2, HSSFDataFormat.GetBuiltinFormat("0.00")); //RMB currency format with comma - "¥20,000" ICell cell2 = sheet.CreateRow(1).CreateCell(0); SetValueAndFormat(workbook, cell2, 20000, format.GetFormat("¥#,##0")); //scentific number format - "3.15E+00" ICell cell3 = sheet.CreateRow(2).CreateCell(0); SetValueAndFormat(workbook, cell3, 3.151234, format.GetFormat("0.00E+00")); //percent format, 2 digits after the decimal point - "99.33%" ICell cell4 = sheet.CreateRow(3).CreateCell(0); SetValueAndFormat(workbook, cell4, 0.99333, format.GetFormat("0.00%")); //phone number format - "021-65881234" ICell cell5 = sheet.CreateRow(4).CreateCell(0); SetValueAndFormat(workbook, cell5, 02165881234, format.GetFormat("000-00000000")); //Chinese capitalized character number - 壹贰叁 元 ICell cell6 = sheet.CreateRow(5).CreateCell(0); SetValueAndFormat(workbook, cell6, 123, format.GetFormat("[DbNum2][$-804]0 元")); //Chinese date string ICell cell7 = sheet.CreateRow(6).CreateCell(0); SetValueAndFormat(workbook, cell7, new DateTime(2004, 5, 6), format.GetFormat("yyyy年m月d日")); cell7.SetCellValue(new DateTime(2004, 5, 6)); //Chinese date string ICell cell8 = sheet.CreateRow(7).CreateCell(0); SetValueAndFormat(workbook, cell8, new DateTime(2005, 11, 6), format.GetFormat("yyyy年m月d日")); //formula value with datetime style ICell cell9 = sheet.CreateRow(8).CreateCell(0); cell9.CellFormula = "DateValue(\"2005-11-11\")+TIMEVALUE(\"11:11:11\")"; ICellStyle cellStyle9 = workbook.CreateCellStyle(); cellStyle9.DataFormat = HSSFDataFormat.GetBuiltinFormat("m/d/yy h:mm"); cell9.CellStyle = cellStyle9; //display current time ICell cell10 = sheet.CreateRow(9).CreateCell(0); SetValueAndFormat(workbook, cell10, DateTime.Now, format.GetFormat("[$-409]h:mm:ss AM/PM;@")); FileStream sw = File.Create("test.xlsx"); workbook.Write(sw); sw.Close(); }
public void TestIdentifyDateFormats() { // First up, try with a few built in date formats short[] builtins = new short[] { 0x0e, 0x0f, 0x10, 0x16, 0x2d, 0x2e }; for (int i = 0; i < builtins.Length; i++) { String formatStr = HSSFDataFormat.GetBuiltinFormat(builtins[i]); Assert.IsTrue(DateUtil.IsInternalDateFormat(builtins[i])); Assert.IsTrue(DateUtil.IsADateFormat(builtins[i], formatStr)); } // Now try a few built-in non date formats builtins = new short[] { 0x01, 0x02, 0x17, 0x1f, 0x30 }; for (int i = 0; i < builtins.Length; i++) { String formatStr = HSSFDataFormat.GetBuiltinFormat(builtins[i]); Assert.IsFalse(DateUtil.IsInternalDateFormat(builtins[i])); Assert.IsFalse(DateUtil.IsADateFormat(builtins[i], formatStr)); } // Now for some non-internal ones // These come after the real ones int numBuiltins = HSSFDataFormat.NumberOfBuiltinBuiltinFormats; Assert.IsTrue(numBuiltins < 60); short formatId = 60; Assert.IsFalse(DateUtil.IsInternalDateFormat(formatId)); // Valid ones first String[] formats = new String[] { "yyyy-mm-dd", "yyyy/mm/dd", "yy/mm/dd", "yy/mmm/dd", "dd/mm/yy", "dd/mm/yyyy", "dd/mmm/yy", "dd-mm-yy", "dd-mm-yyyy", "DD-MM-YY", "DD-mm-YYYY", "dd\\-mm\\-yy", // Sometimes escaped "dd.mm.yyyy", "dd\\.mm\\.yyyy", "dd\\ mm\\.yyyy AM", "dd\\ mm\\.yyyy pm", "dd\\ mm\\.yyyy\\-dd", "[h]:mm:ss", //"mm/dd/yy", "\"mm\"/\"dd\"/\"yy\"", "mm/dd/yy", "\\\"mm\\\"/\\\"dd\\\"/\\\"yy\\\"", "mm/dd/yy \"\\\"some\\\" string\"", "m\\/d\\/yyyy", // These crazy ones are valid "yyyy-mm-dd;@", "yyyy/mm/dd;@", "dd-mm-yy;@", "dd-mm-yyyy;@", // These even crazier ones are also valid // (who knows what they mean though...) "[$-F800]dddd\\,\\ mmm\\ dd\\,\\ yyyy", "[$-F900]ddd/mm/yyy", // These ones specify colours, who knew that was allowed? "[BLACK]dddd/mm/yy", "[yeLLow]yyyy-mm-dd" }; for (int i = 0; i < formats.Length; i++) { Assert.IsTrue( DateUtil.IsADateFormat(formatId, formats[i]) , formats[i] + " is a date format" ); } // Then time based ones too formats = new String[] { "yyyy-mm-dd hh:mm:ss", "yyyy/mm/dd HH:MM:SS", "mm/dd HH:MM", "yy/mmm/dd SS", "mm/dd HH:MM AM", "mm/dd HH:MM am", "mm/dd HH:MM PM", "mm/dd HH:MM pm" //"m/d/yy h:mm AM/PM" }; for (int i = 0; i < formats.Length; i++) { Assert.IsTrue( DateUtil.IsADateFormat(formatId, formats[i]), formats[i] + " is a datetime format" ); } // Then invalid ones formats = new String[] { "yyyy*mm*dd", "0.0", "0.000", "0%", "0.0%", "[]Foo", "[BLACK]0.00%", "", null }; for (int i = 0; i < formats.Length; i++) { Assert.IsFalse( DateUtil.IsADateFormat(formatId, formats[i]), formats[i] + " is not a date or datetime format" ); } // And these are ones we probably shouldn't allow, // but would need a better regexp formats = new String[] { //"yyyy:mm:dd", "yyyy:mm:dd", "\"mm\"/\"dd\"/\"yy\"", }; for (int i = 0; i < formats.Length; i++) { // Assert.IsFalse( DateUtil.IsADateFormat(formatId, formats[i]) ); } }
public static bool ToExcel(string savePath, string SheetName, string HeadName, IList listJob, string className, string note = "") { try { DataTable table = ToDataTable(listJob, className, note); List <int> dateCellIndex = new List <int>();//日期格式列 bool header = true; //创建工作薄 HSSFWorkbook wk = new HSSFWorkbook(); //创建一个名称为mySheet的表 ISheet tb = wk.CreateSheet(SheetName); #region 表头样式 ICellStyle headStyle = wk.CreateCellStyle(); headStyle.FillBackgroundColor = NPOI.HSSF.Util.HSSFColor.Yellow.Index; headStyle.Alignment = HorizontalAlignment.Center; IFont font = wk.CreateFont(); font.FontName = "宋体"; font.FontHeightInPoints = 12; font.Boldweight = 700; headStyle.SetFont(font); #endregion #region 行样式 ICellStyle stylerow = wk.CreateCellStyle(); stylerow.Alignment = HorizontalAlignment.Center; IFont fontrow = wk.CreateFont(); fontrow.FontName = "宋体"; fontrow.FontHeightInPoints = 12; stylerow.SetFont(fontrow); stylerow.WrapText = true; #endregion #region 日期行样式 ICellStyle dateStyle = wk.CreateCellStyle(); IDataFormat format = wk.CreateDataFormat(); dateStyle.DataFormat = format.GetFormat("yyyy-MM-dd HH:mm:ss"); stylerow.WrapText = true; #endregion #region 数字样式 ICellStyle numStyle = wk.CreateCellStyle(); numStyle.DataFormat = HSSFDataFormat.GetBuiltinFormat("0.00"); //IDataFormat numformat = wk.CreateDataFormat(); //numStyle.DataFormat = numformat.GetFormat("0.00"); #endregion Dictionary <string, string> excelDataMap = ReadXml(className, false, note); //合并标题行 /// <param name="sheet">要合并单元格所在的sheet</param> /// <param name="rowstart">开始行的索引</param> /// <param name="rowend">结束行的索引</param> /// <param name="colstart">开始列的索引</param> /// <param name="colend">结束列的索引</param> tb.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(0, 0, 0, excelDataMap.Keys.Count)); IRow rowhead = tb.CreateRow(0); //创建一行 rowhead.HeightInPoints = 25; //行高 ICell cellhead = rowhead.GetCell(0, MissingCellPolicy.CREATE_NULL_AS_BLANK); //在行中创建单元格 cellhead.CellStyle = headStyle; cellhead.SetCellValue(HeadName); //写入表头 //SetCellRangeAddress(tb, 0, 0, 1, 20); int rowIndex = 1; if (header) { IRow row = tb.CreateRow(rowIndex); //创建一行 rowIndex++; row.HeightInPoints = 25; //行高 int cellIndex = 0; //开始列索引 #region 弃用 //for (int i = 0; i < table.Columns.Count; i++) //{ // if (!table.Columns[i].ToString().EndsWith("_wennull")) // { // if (table.Columns[i].DataType.ToString() == "System.DateTime")//日期型 // { // tb.SetColumnWidth(cellIndex, 20 * 256);//行宽10个汉字 // dateCellIndex.Add(cellIndex); // } // else // { // tb.SetColumnWidth(cellIndex, 16 * 256);//行宽8个汉字 // } // ICell cell = row.GetCell(cellIndex, MissingCellPolicy.CREATE_NULL_AS_BLANK); //在行中创建单元格 // cell.CellStyle = headStyle; // cell.SetCellValue(table.Columns[i].ToString());//循环往第二行的单元格中添加数据 // cellIndex++; // } //} #endregion foreach (var item in excelDataMap) { string columsName = item.Value; if (table.Columns[columsName].DataType.ToString() == "System.DateTime") //日期型 { tb.SetColumnWidth(cellIndex, 20 * 256); //行宽10个汉字 dateCellIndex.Add(cellIndex); } else { tb.SetColumnWidth(cellIndex, 16 * 256); //行宽8个汉字 } ICell cell = row.GetCell(cellIndex, MissingCellPolicy.CREATE_NULL_AS_BLANK); //在行中创建单元格 cell.CellStyle = headStyle; cell.SetCellValue(table.Columns[columsName].ToString()); //循环往第二行的单元格中添加数据 cellIndex++; } } for (int i = 0; i < table.Rows.Count; i++) { IRow row = tb.CreateRow(rowIndex + i); //创建一行 row.HeightInPoints = 20; //行高 int cellIndex = 0; //开始列索引 #region 弃用 //for (int c = 0; c < table.Columns.Count; c++) //{ // if (!table.Columns[c].ToString().EndsWith("_wennull")) // { // ICell cell = row.GetCell(cellIndex, MissingCellPolicy.CREATE_NULL_AS_BLANK); //在行中创建单元格 // if (table.Columns[c].DataType.ToString() == "System.DateTime")//日期型 // { // string value = table.Rows[i][c].ToString(); // if (value != "") // { // cell.SetCellValue(Convert.ToDateTime(value).ToString("yyyy-MM-dd HH:mm:ss"));//循环往第二行的单元格中添加数据 // } // cell.CellStyle = dateStyle; // } // else // { // cell.SetCellValue(table.Rows[i][c].ToString());//循环往第二行的单元格中添加数据 // cell.CellStyle = stylerow; // } // cellIndex++; // } //} #endregion foreach (var item in excelDataMap) { string columsName = item.Value; ICell cell = row.GetCell(cellIndex, MissingCellPolicy.CREATE_NULL_AS_BLANK); //在行中创建单元格 if (table.Columns[columsName].DataType.ToString() == "System.DateTime") //日期型 { string value = table.Rows[i][columsName].ToString(); if (value != "") { cell.SetCellValue(Convert.ToDateTime(value).ToString("yyyy-MM-dd HH:mm:ss"));//循环往第二行的单元格中添加数据 } cell.CellStyle = dateStyle; } else if (table.Columns[columsName].DataType.ToString() == "System.Decimal") { string value = table.Rows[i][columsName].ToString(); if (value != "") { cell.SetCellValue(Convert.ToDouble(value));//循环往第二行的单元格中添加数据 } //cell.CellStyle = numStyle;//数字格式化,不启用 } else { cell.SetCellValue(table.Rows[i][columsName].ToString());//循环往第二行的单元格中添加数据 cell.CellStyle = stylerow; } cellIndex++; } } //foreach (int cellIndex in dateCellIndex) //{ // for (int y = table.Rows.Count; y < 500; y++)//至少设置500行格式 // { // IRow row = tb.CreateRow(rowIndex + y);//创建一行 // row.HeightInPoints = 20; //行高 // ICell cell = row.GetCell(cellIndex, MissingCellPolicy.CREATE_NULL_AS_BLANK); //在行中创建单元格 // //cell.SetCellValue("");//循环往第二行的单元格中添加数据 // cell.CellStyle = dateStyle; // } //} using (FileStream fs = File.OpenWrite(savePath)) //打开一个xls文件,如果没有则自行创建,如果存在myxls.xls文件则在创建是不要打开该文件! { wk.Write(fs); //向打开的这个xls文件中写入mySheet表并保存。 } return(true); } catch (Exception) { return(false); } }
/// <summary> /// DataTable导出到Excel的MemoryStream /// xls office 2003格式 /// </summary> /// <param name="dtSource">源DataTable</param> /// <param name="strHeaderText">表头文本</param> static MemoryStream ExportDT(DataTable dtSource, string strHeaderText, string sheetName) { IWorkbook workbook = new HSSFWorkbook(); HSSFSheet sheet; if (string.IsNullOrEmpty(sheetName)) { sheet = workbook.CreateSheet() as HSSFSheet; } else { sheet = workbook.CreateSheet(sheetName) as HSSFSheet; } #region 右击文件 属性信息 //{ // DocumentSummaryInformation dsi = PropertySetFactory.CreateDocumentSummaryInformation(); // dsi.Company = "http://www.yongfa365.com/"; // workbook.DocumentSummaryInformation = dsi; // SummaryInformation si = PropertySetFactory.CreateSummaryInformation(); // si.Author = "柳永法"; //填加xls文件作者信息 // si.ApplicationName = "NPOI测试程序"; //填加xls文件创建程序信息 // si.LastAuthor = "柳永法2"; //填加xls文件最后保存者信息 // si.Comments = "说明信息"; //填加xls文件作者信息 // si.Title = "NPOI测试"; //填加xls文件标题信息 // si.Subject = "NPOI测试Demo"; //填加文件主题信息 // si.CreateDateTime = DateTime.Now; // workbook.SummaryInformation = si; //} #endregion HSSFCellStyle dateStyle = workbook.CreateCellStyle() as HSSFCellStyle; HSSFDataFormat format = workbook.CreateDataFormat() as HSSFDataFormat; dateStyle.DataFormat = format.GetFormat("yyyy-mm-dd"); //取得列宽 int[] arrColWidth = new int[dtSource.Columns.Count]; foreach (DataColumn item in dtSource.Columns) { arrColWidth[item.Ordinal] = Encoding.UTF8.GetBytes(item.ColumnName.ToString()).Length; } for (int i = 0; i < dtSource.Rows.Count; i++) { for (int j = 0; j < dtSource.Columns.Count; j++) { int intTemp = Encoding.UTF8.GetBytes(dtSource.Rows[i][j].ToString()).Length; if (intTemp > arrColWidth[j]) { arrColWidth[j] = intTemp; } } } int rowIndex = 0; foreach (DataRow row in dtSource.Rows) { #region 新建表,填充表头,填充列头,样式 if (rowIndex % 65535 == 0) { if (rowIndex != 0) { sheet = workbook.CreateSheet() as HSSFSheet; } #region 表头及样式 if (!string.IsNullOrEmpty(strHeaderText)) { HSSFRow headerRow = sheet.CreateRow(rowIndex++) as HSSFRow; headerRow.HeightInPoints = 25; headerRow.CreateCell(0).SetCellValue(strHeaderText); HSSFCellStyle headStyle = workbook.CreateCellStyle() as HSSFCellStyle; headStyle.Alignment = Npoi.Core.SS.UserModel.HorizontalAlignment.Center; HSSFFont font = workbook.CreateFont() as HSSFFont; font.FontHeightInPoints = 20; font.Boldweight = 700; headStyle.SetFont(font); headerRow.GetCell(0).CellStyle = headStyle; sheet.AddMergedRegion(new CellRangeAddress(0, 0, 0, dtSource.Columns.Count - 1)); //headerRow.Dispose(); } #endregion #region 列头及样式 { HSSFRow headerRow = sheet.CreateRow(rowIndex++) as HSSFRow; HSSFCellStyle headStyle = workbook.CreateCellStyle() as HSSFCellStyle; headStyle.Alignment = Npoi.Core.SS.UserModel.HorizontalAlignment.Center; HSSFFont font = workbook.CreateFont() as HSSFFont; font.FontHeightInPoints = 10; font.Boldweight = 700; headStyle.SetFont(font); foreach (DataColumn column in dtSource.Columns) { headerRow.CreateCell(column.Ordinal).SetCellValue(column.ColumnName); headerRow.GetCell(column.Ordinal).CellStyle = headStyle; //设置列宽 sheet.SetColumnWidth(column.Ordinal, (arrColWidth[column.Ordinal] + 1) * 256); } //headerRow.Dispose(); } #endregion } #endregion #region 填充内容 HSSFRow dataRow = sheet.CreateRow(rowIndex) as HSSFRow; foreach (DataColumn column in dtSource.Columns) { HSSFCell newCell = dataRow.CreateCell(column.Ordinal) as HSSFCell; string drValue = row[column].ToString(); switch (column.DataType.ToString()) { case "System.String": //字符串类型 double result; if (isNumeric(drValue, out result)) { double.TryParse(drValue, out result); newCell.SetCellValue(result); break; } else { newCell.SetCellValue(drValue); break; } case "System.DateTime": //日期类型 DateTime dateV; DateTime.TryParse(drValue, out dateV); newCell.SetCellValue(dateV); newCell.CellStyle = dateStyle; //格式化显示 break; case "System.Boolean": //布尔型 bool boolV = false; bool.TryParse(drValue, out boolV); newCell.SetCellValue(boolV); break; case "System.Int16": //整型 case "System.Int32": case "System.Int64": case "System.Byte": int intV = 0; int.TryParse(drValue, out intV); newCell.SetCellValue(intV); break; case "System.Decimal": //浮点型 case "System.Double": double doubV = 0; double.TryParse(drValue, out doubV); newCell.SetCellValue(doubV); break; case "System.DBNull": //空值处理 newCell.SetCellValue(""); break; default: newCell.SetCellValue(""); break; } } #endregion rowIndex++; } using (MemoryStream ms = new MemoryStream()) { workbook.Write(ms); ms.Flush(); ms.Position = 0; //sheet.Dispose(); //workbook.Dispose(); return(ms); } }
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 static void GridToExcelByNPOI(string sheetname, string lotid, string date, string[] summ1, string[] summ2, string[] summ3, DataGridView gv, string strExcelFileName, bool summflag, int mergeColIndex = -1) { HSSFWorkbook workbook = new HSSFWorkbook(); try { ISheet sheet = workbook.CreateSheet(sheetname); //汇总栏 ICellStyle SummStyle = workbook.CreateCellStyle(); SummStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center; SummStyle.VerticalAlignment = NPOI.SS.UserModel.VerticalAlignment.Center; //字体 NPOI.SS.UserModel.IFont summfont = workbook.CreateFont(); summfont.Boldweight = (short)FontBoldWeight.Bold; summfont.Color = NPOI.HSSF.Util.HSSFColor.DarkBlue.Index; SummStyle.SetFont(summfont); SummStyle.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.DarkBlue.Index; SummStyle.FillBackgroundColor = NPOI.HSSF.Util.HSSFColor.Lavender.Index; //Lot ID IRow summRow_1 = sheet.CreateRow(0); summRow_1.Height = 20 * 20; ICell cell_1 = summRow_1.CreateCell(0); cell_1.SetCellValue("Lot ID:"); cell_1.CellStyle = SummStyle; ICell cell_2 = summRow_1.CreateCell(1); cell_2.SetCellValue(lotid); cell_2.CellStyle = SummStyle; ICell cell_3 = summRow_1.CreateCell(2); cell_3.SetCellValue("Date:"); cell_3.CellStyle = SummStyle; ICell cell_4 = summRow_1.CreateCell(3); cell_4.SetCellValue(date); cell_4.CellStyle = SummStyle; ICell cell_5 = summRow_1.CreateCell(4); cell_5.SetCellValue("Create Date:"); cell_5.CellStyle = SummStyle; ICell cell_6 = summRow_1.CreateCell(5); cell_6.SetCellValue(DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss")); cell_6.CellStyle = SummStyle; if (summflag) { //汇总 IRow summRow_2 = sheet.CreateRow(1); summRow_2.Height = 20 * 20; ICell cell_21 = summRow_2.CreateCell(0); cell_21.SetCellValue(summ1[0]); cell_21.CellStyle = SummStyle; ICell cell_22 = summRow_2.CreateCell(1); cell_22.SetCellValue(summ1[1]); cell_22.CellStyle = SummStyle; ICell cell_23 = summRow_2.CreateCell(2); cell_23.SetCellValue(summ1[2]); cell_23.CellStyle = SummStyle; ICell cell_24 = summRow_2.CreateCell(3); cell_24.SetCellValue(summ1[3]); cell_24.CellStyle = SummStyle; IRow summRow_3 = sheet.CreateRow(2); summRow_3.Height = 20 * 20; ICell cell_31 = summRow_3.CreateCell(0); cell_31.SetCellValue(summ2[0]); cell_31.CellStyle = SummStyle; ICell cell_32 = summRow_3.CreateCell(1); cell_32.SetCellValue(summ2[1]); cell_32.CellStyle = SummStyle; ICell cell_33 = summRow_3.CreateCell(2); cell_33.SetCellValue(summ2[2]); cell_33.CellStyle = SummStyle; ICell cell_34 = summRow_3.CreateCell(3); cell_34.SetCellValue(summ2[3]); cell_34.CellStyle = SummStyle; IRow summRow_4 = sheet.CreateRow(3); summRow_4.Height = 20 * 20; ICell cell_41 = summRow_4.CreateCell(0); cell_41.SetCellValue(summ3[0]); cell_41.CellStyle = SummStyle; ICell cell_42 = summRow_4.CreateCell(1); cell_42.SetCellValue(summ3[1]); cell_42.CellStyle = SummStyle; if (summ3.Length > 2) { ICell cell_43 = summRow_4.CreateCell(2); cell_43.SetCellValue(summ3[2]); cell_43.CellStyle = SummStyle; ICell cell_44 = summRow_4.CreateCell(3); cell_44.SetCellValue(summ3[3]); cell_44.CellStyle = SummStyle; } } //表格头 ICellStyle HeadercellStyle = workbook.CreateCellStyle(); HeadercellStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin; HeadercellStyle.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin; HeadercellStyle.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin; HeadercellStyle.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin; HeadercellStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center; HeadercellStyle.VerticalAlignment = NPOI.SS.UserModel.VerticalAlignment.Center; //字体 NPOI.SS.UserModel.IFont headerfont = workbook.CreateFont(); headerfont.Boldweight = (short)FontBoldWeight.Bold; HeadercellStyle.SetFont(headerfont); //HeadercellStyle.FillBackgroundColorColor HeadercellStyle.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.Black.Index; HeadercellStyle.FillBackgroundColor = NPOI.HSSF.Util.HSSFColor.Lavender.Index; //用column name 作为列名 int icolIndex = 0; int ridx = (summflag ? 4 : 1); IRow headerRow = sheet.CreateRow(ridx); headerRow.Height = 20 * 20; foreach (DataGridViewColumn item in gv.Columns) { if (!item.Visible) { continue; } ICell cell = headerRow.CreateCell(icolIndex); sheet.SetColumnWidth(icolIndex, item.Width * 50); cell.SetCellValue(item.HeaderText); cell.CellStyle = HeadercellStyle; icolIndex++; } ICellStyle cellStyle = workbook.CreateCellStyle(); //为避免日期格式被Excel自动替换,所以设定 format 为 『@』 表示一率当成text來看 cellStyle.DataFormat = HSSFDataFormat.GetBuiltinFormat("@"); cellStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin; cellStyle.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin; cellStyle.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin; cellStyle.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin; NPOI.SS.UserModel.IFont cellfont = workbook.CreateFont(); cellfont.Boldweight = (short)FontBoldWeight.Normal; cellStyle.SetFont(cellfont); //建立内容行 int iRowIndex = (summflag ? 5 : 2); int iCellIndex = 0; //merge params string mergeColValue = string.Empty; int mergeFirstRow = iRowIndex; int mergeLastRow = iRowIndex; if (gv.Rows.Count > 0) { foreach (DataGridViewRow Rowitem in gv.Rows) { IRow DataRow = sheet.CreateRow(iRowIndex); foreach (DataGridViewColumn Colitem in gv.Columns) { if (Colitem.Visible) { var colValue = Rowitem.Cells[Colitem.Name].FormattedValue.ToString(); if (iCellIndex == mergeColIndex) { if (mergeColValue != colValue && iRowIndex != mergeFirstRow) { sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(mergeFirstRow, iRowIndex - 1, mergeColIndex, mergeColIndex)); mergeFirstRow = iRowIndex; } mergeColValue = colValue; cellStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center; cellStyle.VerticalAlignment = VerticalAlignment.Center; } else { cellStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.General; cellStyle.VerticalAlignment = VerticalAlignment.None; } ICell cell = DataRow.CreateCell(iCellIndex); cell.SetCellValue(colValue); cell.CellStyle = cellStyle; iCellIndex++; } } iCellIndex = 0; iRowIndex++; } if (mergeColIndex != -1) { sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(mergeFirstRow, iRowIndex - 1, mergeColIndex, mergeColIndex)); } ////自适应列宽度 //for (int i = 0; i < icolIndex; i++) //{ // sheet.AutoSizeColumn(i); //} } //写Excel FileStream file = new FileStream(strExcelFileName, FileMode.OpenOrCreate); workbook.Write(file); file.Flush(); file.Close(); } catch (Exception ex) { throw ex; } finally { workbook = null; } }
public Excel ExportReportToExcel(string basePath, string buildId, string energyCode, string[] regionIds, string date, string type) { type = type.ToUpper(); string templatePath = basePath + "/DayReportTemplate.xls"; string reportType = " 日报(" + date + ")"; if (type == "MM") { reportType = " 月报(" + date + ")"; templatePath = basePath + "/MonthReportTemplate.xls"; } else if (type == "YY") { reportType = " 年报(" + date + ")"; templatePath = basePath + "/YearReportTemplate.xls"; } else if (type == "DD") { reportType = " 日报(" + date + ")"; templatePath = basePath + "/DayReportTemplate.xls"; } //根据模板生成Excel FileStream file = new FileStream(templatePath, FileMode.Open, FileAccess.Read); HSSFWorkbook book = new HSSFWorkbook(file); HSSFSheet sheet = (HSSFSheet)book.GetSheet("Sheet1"); //创建单元格格式:数字型单元格默认取小数点后两位 ICellStyle style = book.CreateCellStyle(); style.DataFormat = HSSFDataFormat.GetBuiltinFormat("0.00"); //获取当前建筑名 IHomeDbContext homeContext = new HomeDbContext(); BuildInfo build = homeContext.GetBuildById(buildId); //获取能源类型 EnergyItemDict energyItem = reportContext.GetUnitByEnergyCode(energyCode); //string[] circuitIds = circuits.Split(','); List <ReportValue> data = context.GetReportValueList(energyCode, regionIds, date, type); var totalList = data.GroupBy(d => new { d.Time }) .Select(group => new ReportValue { Id = "Total", Name = "总计", Time = group.Key.Time, Value = group.Sum(p => p.Value) }).ToList(); data.AddRange(totalList); //设置Excel标题 sheet.GetRow(0).GetCell(0).SetCellValue(build.BuildName + " 区域用能 " + reportType); //设置Excel中报表的能源类别 sheet.GetRow(1).GetCell(1).SetCellValue(energyItem.EnergyItemName); //设置Excel中报表的能源单位 sheet.GetRow(1).GetCell(5).SetCellValue(energyItem.EnergyItemUnit); //根据传入circuitIds填充excel int rowId = 0; for (int i = 0; i < regionIds.Length + 1; i++) { //使用lamda表达式筛选List中Id与传入的Id对应的仪表:一次填充一行Excel List <ReportValue> current; if (i == regionIds.Length) { current = data.FindAll(p => p.Id == "Total"); } else { current = data.FindAll(p => p.Id == regionIds[i]); } if (current.Count > 0) { IRow row = sheet.CreateRow(rowId + 3); row.CreateCell(0).SetCellValue(current[0].Name); decimal total = 0; switch (type) { case "DD": foreach (var item in current) { //遍历筛选出列表中当前回路中每个小时的数据,填充到Excel中 //当前行的第一列已经填充回路名称,根据时间设置向右偏移一位,将对应数据写入到当前单元格,并设置格式 if (item.Time != null) { DateTime time = Convert.ToDateTime(item.Time); row.CreateCell(time.Hour + 1).SetCellValue((double)item.Value); row.GetCell(time.Hour + 1).CellStyle = style; total += Convert.ToDecimal(item.Value); } } row.CreateCell(25).SetCellValue((double)total); row.GetCell(25).CellStyle = style; break; case "MM": foreach (var item in current) { if (item.Time != null) { DateTime time = Convert.ToDateTime(item.Time); row.CreateCell(time.Day).SetCellValue((double)item.Value); row.GetCell(time.Day).CellStyle = style; total += Convert.ToDecimal(item.Value); } } row.CreateCell(32).SetCellValue((double)total); row.GetCell(32).CellStyle = style; break; case "YY": foreach (var item in current) { if (item.Time != null) { DateTime time = Convert.ToDateTime(item.Time); row.CreateCell(time.Month).SetCellValue((double)item.Value); row.GetCell(time.Month).CellStyle = style; total += Convert.ToDecimal(item.Value); } } row.CreateCell(13).SetCellValue((double)total); row.GetCell(13).CellStyle = style; break; } rowId++; } } Excel excel = new Excel(); excel.Name = build.BuildName + "区域用能" + reportType + "[" + Guid.NewGuid().ToString("N") + "].xls"; using (MemoryStream stream = new MemoryStream()) { book.Write(stream); stream.Seek(0, SeekOrigin.Begin); excel.Data = stream.ToArray(); } return(excel); }
/// <summary> /// DataTable导出到Excel的MemoryStream /// </summary> /// <param name="dtSource">源DataTable</param> /// <param name="strHeaderText">表头文本</param> /// <Author> 2010-5-8 22:21:41</Author> public static MemoryStream Export(DataTable dtSource, string strHeaderText, Dictionary <string, string> columnNames, Dictionary <string, string> dataformats) { HSSFWorkbook workbook = new HSSFWorkbook(); HSSFSheet sheet = (HSSFSheet)workbook.CreateSheet(); #region 右击文件 属性信息 { DocumentSummaryInformation dsi = PropertySetFactory.CreateDocumentSummaryInformation(); dsi.Company = ""; workbook.DocumentSummaryInformation = dsi; SummaryInformation si = PropertySetFactory.CreateSummaryInformation(); si.Author = "kakake"; //填加xls文件作者信息 si.ApplicationName = ""; //填加xls文件创建程序信息 si.LastAuthor = ""; //填加xls文件最后保存者信息 si.Comments = "说明信息"; //填加xls文件作者信息 si.Title = ""; //填加xls文件标题信息 si.Subject = ""; //填加文件主题信息 si.CreateDateTime = DateTime.Now; workbook.SummaryInformation = si; } #endregion HSSFCellStyle dateStyle = (HSSFCellStyle)workbook.CreateCellStyle(); HSSFDataFormat format = (HSSFDataFormat)workbook.CreateDataFormat(); dateStyle.DataFormat = format.GetFormat("yyyy-mm-dd"); HSSFCellStyle customStyle = (HSSFCellStyle)workbook.CreateCellStyle(); HSSFDataFormat customformat = (HSSFDataFormat)workbook.CreateDataFormat(); //取得列宽 int[] arrColWidth = new int[dtSource.Columns.Count]; foreach (DataColumn item in dtSource.Columns) { arrColWidth[item.Ordinal] = Encoding.GetEncoding(936).GetBytes(item.ColumnName.ToString()).Length; } for (int i = 0; i < dtSource.Rows.Count; i++) { for (int j = 0; j < dtSource.Columns.Count; j++) { int intTemp = Encoding.GetEncoding(936).GetBytes(dtSource.Rows[i][j].ToString()).Length; if (intTemp > arrColWidth[j]) { arrColWidth[j] = intTemp; } } } int rowIndex = 0; int index = 0; foreach (DataRow row in dtSource.Rows) { #region 新建表,填充表头,填充列头,样式 if (rowIndex == 65535 || rowIndex == 0) { if (rowIndex != 0) { sheet = (HSSFSheet)workbook.CreateSheet(); } #region 表头及样式 { HSSFRow headerRow = (HSSFRow)sheet.CreateRow(0); headerRow.HeightInPoints = 25; headerRow.CreateCell(0).SetCellValue(strHeaderText); HSSFCellStyle headStyle = (HSSFCellStyle)workbook.CreateCellStyle(); headStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.CENTER; HSSFFont font = (HSSFFont)workbook.CreateFont(); font.FontHeightInPoints = 20; font.Boldweight = 700; headStyle.SetFont(font); headerRow.GetCell(0).CellStyle = headStyle; if (columnNames.Count > 0) { sheet.AddMergedRegion(new Region(0, 0, 0, columnNames.Count - 1)); } else { sheet.AddMergedRegion(new Region(0, 0, 0, dtSource.Columns.Count - 1)); } //headerRow.Dispose(); } #endregion #region 列头及样式 { HSSFRow headerRow = (HSSFRow)sheet.CreateRow(1); HSSFCellStyle headStyle = (HSSFCellStyle)workbook.CreateCellStyle(); headStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.CENTER; HSSFFont font = (HSSFFont)workbook.CreateFont(); font.FontHeightInPoints = 10; font.Boldweight = 700; headStyle.SetFont(font); index = 0; foreach (DataColumn column in dtSource.Columns) { if (columnNames.Count > 0) { if (columnNames.ContainsKey(column.ColumnName)) { headerRow.CreateCell(index).SetCellValue(columnNames[column.ColumnName]); headerRow.GetCell(index).CellStyle = headStyle; //设置列宽 sheet.SetColumnWidth(index, (Encoding.GetEncoding(936).GetBytes(columnNames[column.ColumnName]).Length + 1) * 256); index++; } } else { headerRow.CreateCell(column.Ordinal).SetCellValue(column.ColumnName); headerRow.GetCell(column.Ordinal).CellStyle = headStyle; //设置列宽 sheet.SetColumnWidth(column.Ordinal, (arrColWidth[column.Ordinal] + 1) * 256); } } //headerRow.Dispose(); } #endregion rowIndex = 2; } #endregion #region 填充内容 index = 0; HSSFRow dataRow = (HSSFRow)sheet.CreateRow(rowIndex); foreach (DataColumn column in dtSource.Columns) { if (columnNames.Count > 0) { if (columnNames.ContainsKey(column.ColumnName)) { HSSFCell newCell = (HSSFCell)dataRow.CreateCell(index); string drValue = row[column].ToString(); switch (column.DataType.ToString()) { case "System.String": //字符串类型 newCell.SetCellValue(drValue); break; case "System.DateTime": //日期类型 DateTime dateV; DateTime.TryParse(drValue, out dateV); newCell.SetCellValue(dateV); newCell.CellStyle = dateStyle; //格式化显示 break; case "System.Boolean": //布尔型 bool boolV = false; bool.TryParse(drValue, out boolV); newCell.SetCellValue(boolV); break; case "System.Int16": //整型 case "System.Int32": case "System.Int64": case "System.Byte": int intV = 0; int.TryParse(drValue, out intV); newCell.SetCellValue(intV); break; case "System.Decimal": //浮点型 case "System.Double": double doubV = 0; double.TryParse(drValue, out doubV); newCell.SetCellValue(doubV); //HSSFCellStyle celldoubleStyle = (HSSFCellStyle)workbook.CreateCellStyle(); //int pos = Convert.ToString(doubV).Length - Convert.ToString(doubV).IndexOf('.') - 1; //if (pos == 4) //{ // celldoubleStyle.DataFormat = HSSFDataFormat.GetBuiltinFormat("0.0000"); //} //else if (pos == 2) //{ // celldoubleStyle.DataFormat = HSSFDataFormat.GetBuiltinFormat("0.00"); //} //newCell.CellStyle = celldoubleStyle; break; case "System.DBNull": //空值处理 newCell.SetCellValue(""); break; default: newCell.SetCellValue(""); break; } if (dataformats.ContainsKey(column.ColumnName)) { customStyle.DataFormat = customformat.GetFormat(dataformats[column.ColumnName]); newCell.CellStyle = customStyle; } index++; } } else { HSSFCell newCell = (HSSFCell)dataRow.CreateCell(column.Ordinal); string drValue = row[column].ToString(); switch (column.DataType.ToString()) { case "System.String": //字符串类型 newCell.SetCellValue(drValue); break; case "System.DateTime": //日期类型 DateTime dateV; DateTime.TryParse(drValue, out dateV); newCell.SetCellValue(dateV); newCell.CellStyle = dateStyle; //格式化显示 break; case "System.Boolean": //布尔型 bool boolV = false; bool.TryParse(drValue, out boolV); newCell.SetCellValue(boolV); break; case "System.Int16": //整型 case "System.Int32": case "System.Int64": case "System.Byte": int intV = 0; int.TryParse(drValue, out intV); newCell.SetCellValue(intV); break; case "System.Decimal": //浮点型 case "System.Double": double doubV = 0; double.TryParse(drValue, out doubV); newCell.SetCellValue(doubV); //HSSFCellStyle celldoubleStyle = (HSSFCellStyle)workbook.CreateCellStyle(); //int pos = Convert.ToString(doubV).Length - Convert.ToString(doubV).IndexOf('.') - 1; //if (pos == 4) //{ // celldoubleStyle.DataFormat = HSSFDataFormat.GetBuiltinFormat("0.0000"); //} //else if (pos == 2) //{ // celldoubleStyle.DataFormat = HSSFDataFormat.GetBuiltinFormat("0.00"); //} //newCell.CellStyle = celldoubleStyle; break; case "System.DBNull": //空值处理 newCell.SetCellValue(""); break; default: newCell.SetCellValue(""); break; } if (dataformats.ContainsKey(column.ColumnName)) { customStyle.DataFormat = customformat.GetFormat(dataformats[column.ColumnName]); newCell.CellStyle = customStyle; } } } #endregion rowIndex++; } using (MemoryStream ms = new MemoryStream()) { workbook.Write(ms); ms.Flush(); ms.Position = 0; //sheet.Workbook.Dispose(); //workbook.Dispose();//一般只用写这一个就OK了,他会遍历并释放所有资源,但当前版本有问题所以只释放 sheet return(ms); } }
/// <summary> /// 执行导出操作 /// </summary> /// <typeparam name="T"></typeparam> /// <param name="list"></param> public byte[] Export <T>(List <T> list) { //写入表格头 WriteHead(); //写入数据 ICellStyle cellStyle = _workbook.CreateCellStyle(); cellStyle.DataFormat = HSSFDataFormat.GetBuiltinFormat("@");//为避免日期格式被Excel自动替换,所以设定 format 为 『@』 表示一率当成text來看 cellStyle.BorderBottom = BorderStyle.Thin; cellStyle.BorderLeft = BorderStyle.Thin; cellStyle.BorderRight = BorderStyle.Thin; cellStyle.BorderTop = BorderStyle.Thin; cellStyle.VerticalAlignment = VerticalAlignment.Center; cellStyle.Alignment = HorizontalAlignment.Center; IFont cellFont = _workbook.CreateFont(); cellFont.Boldweight = (short)FontBoldWeight.Normal; cellStyle.SetFont(cellFont); //建立行内容,从1开始 int rowInex = 1; foreach (var rowItem in list) { //创建行 IRow row = _sheet.CreateRow(rowInex); row.HeightInPoints = 25; int cellIndex = 0; foreach (var cellItem in this.Fields) { //创建单元格 ICell cell = row.CreateCell(cellIndex); //反射获取属性的值 PropertyInfo info = rowItem.GetType().GetProperty(cellItem.Key); if (info == null) { cell.SetCellValue($"'{cellItem.Key}'属性不存在"); } else { object value = info.GetValue(rowItem); if (value != null && value.ToString() != "0001/1/1 0:00:00") { cell.SetCellValue(value.ToString()); } } cell.CellStyle = cellStyle; cellIndex++; } //进入下一次循环 rowInex++; } byte[] resultByte = null; //保存 using (MemoryStream ms = new MemoryStream()) { _workbook.Write(ms); resultByte = ms.ToArray(); ms.Dispose(); } return(resultByte); }
public static void DtToExcelByNPOI(DataTable dt, string strExcelFileName) { try { HSSFWorkbook workbook = new HSSFWorkbook(); ISheet sheet = workbook.CreateSheet("Sheet1"); ICellStyle HeadercellStyle = workbook.CreateCellStyle(); HeadercellStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin; HeadercellStyle.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin; HeadercellStyle.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin; HeadercellStyle.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin; HeadercellStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center; //字体 NPOI.SS.UserModel.IFont headerfont = workbook.CreateFont(); headerfont.Boldweight = (short)FontBoldWeight.Bold; HeadercellStyle.SetFont(headerfont); //用column name 作为列名 int icolIndex = 0; IRow headerRow = sheet.CreateRow(0); foreach (DataColumn item in dt.Columns) { ICell cell = headerRow.CreateCell(icolIndex); cell.SetCellValue(item.ColumnName); cell.CellStyle = HeadercellStyle; icolIndex++; } ICellStyle cellStyle = workbook.CreateCellStyle(); //为避免日期格式被Excel自动替换,所以设定 format 为 『@』 表示一率当成text來看 cellStyle.DataFormat = HSSFDataFormat.GetBuiltinFormat("@"); cellStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin; cellStyle.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin; cellStyle.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin; cellStyle.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin; NPOI.SS.UserModel.IFont cellfont = workbook.CreateFont(); cellfont.Boldweight = (short)FontBoldWeight.Normal; cellStyle.SetFont(cellfont); //建立内容行 int iRowIndex = 1; int iCellIndex = 0; foreach (DataRow Rowitem in dt.Rows) { IRow DataRow = sheet.CreateRow(iRowIndex); foreach (DataColumn Colitem in dt.Columns) { ICell cell = DataRow.CreateCell(iCellIndex); cell.SetCellValue(Rowitem[Colitem].ToString()); cell.CellStyle = cellStyle; iCellIndex++; } iCellIndex = 0; iRowIndex++; } //自适应列宽度 for (int i = 0; i < icolIndex; i++) { sheet.AutoSizeColumn(i); } //写Excel FileStream file = new FileStream(strExcelFileName, FileMode.OpenOrCreate); workbook.Write(file); file.Flush(); file.Close(); } catch (Exception ex) { } }
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; } }
private static void CreateSheets(HSSFWorkbook workbook, DataTable dt, string sheetName) { ISheet sheet = workbook.CreateSheet(sheetName); ICellStyle HeadercellStyle = workbook.CreateCellStyle(); HeadercellStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin; HeadercellStyle.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin; HeadercellStyle.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin; HeadercellStyle.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin; HeadercellStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center; //字体 NPOI.SS.UserModel.IFont headerfont = workbook.CreateFont(); headerfont.Boldweight = (short)FontBoldWeight.Bold; HeadercellStyle.SetFont(headerfont); //用column name 作为列名 int icolIndex = 0; IRow headerRow = sheet.CreateRow(0); foreach (DataColumn item in dt.Columns) { ICell cell = headerRow.CreateCell(icolIndex); cell.SetCellValue(item.ColumnName); cell.CellStyle = HeadercellStyle; icolIndex++; } ICellStyle cellStyle = workbook.CreateCellStyle(); //为避免日期格式被Excel自动替换,所以设定 format 为 『@』 表示一率当成text來看 cellStyle.DataFormat = HSSFDataFormat.GetBuiltinFormat("@"); cellStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin; cellStyle.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin; cellStyle.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin; cellStyle.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin; NPOI.SS.UserModel.IFont cellfont = workbook.CreateFont(); cellfont.Boldweight = (short)FontBoldWeight.Normal; cellStyle.SetFont(cellfont); //建立内容行 int iRowIndex = 1; int iCellIndex = 0; foreach (DataRow Rowitem in dt.Rows) { IRow DataRow = sheet.CreateRow(iRowIndex); foreach (DataColumn Colitem in dt.Columns) { ICell cell = DataRow.CreateCell(iCellIndex); cell.SetCellValue(Rowitem[Colitem].ToString()); cell.CellStyle = cellStyle; iCellIndex++; } iCellIndex = 0; iRowIndex++; } //自适应列宽度 for (int i = 0; i < icolIndex; i++) { sheet.AutoSizeColumn(i); } }
private void HSSFAnswer(HSSFWorkbook hssfworkbook, DataTable dt, ref int iCurrentRow) { ISheet sheet = hssfworkbook.GetSheetAt(0); int iRow = 8, iColumn = 0; IRow row; ICell cell; ICellStyle style = GetNormalStyle(hssfworkbook); IFont font = style.GetFont(hssfworkbook); font.FontHeightInPoints = 11; font.FontName = "Times New Roman"; //style.VerticalAlignment = VerticalAlignment.Bottom; style.Alignment = HorizontalAlignment.General; style.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin; style.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin; foreach (DataRow dr in dt.Rows) { ICellStyle styleCell1 = GetNormalStyle(hssfworkbook); font = styleCell1.GetFont(hssfworkbook); font.FontHeightInPoints = 11; font.FontName = "Times New Roman"; //style.VerticalAlignment = VerticalAlignment.Bottom; styleCell1.Alignment = HorizontalAlignment.General; styleCell1.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin; styleCell1.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin; row = sheet.CreateRow(iRow++); iColumn = 0; cell = row.CreateCell(iColumn++, CellType.Numeric); cell.CellStyle = styleCell1; cell.CellStyle.Alignment = HorizontalAlignment.Center; cell.CellStyle.VerticalAlignment = VerticalAlignment.Justify; cell.CellStyle.BorderLeft = NPOI.SS.UserModel.BorderStyle.Double; ICellStyle styleTxt = GetNormalStyle(hssfworkbook); if (Convert.ToInt32(dr[0]) == 1001 || Convert.ToInt32(dr[0]) == 1002) { //cell.SetCellValue(" "); //string value = dr[0].ToString();// +"."; //cell.SetCellValue(Convert.ToInt32(dr[0])); CellRangeAddress cellrange = new CellRangeAddress(iRow - 1, iRow - 1, 0, 1); MergeAreaBorder(hssfworkbook, sheet, cellrange, dr[1].ToString()); sheet.AddMergedRegion(cellrange); iColumn++; } else { cell.SetCellValue(Convert.ToInt32(dr[0])); cell = row.CreateCell(iColumn++, CellType.String); styleTxt.Alignment = HorizontalAlignment.General; styleTxt.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin; styleTxt.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin; cell.CellStyle = styleTxt; cell.SetCellValue(dr[1].ToString()); } for (; iColumn < dt.Columns.Count - 1; iColumn++) { cell = row.CreateCell(iColumn, CellType.Numeric); style.Alignment = HorizontalAlignment.Center; style.VerticalAlignment = VerticalAlignment.Justify; cell.CellStyle = style; if (Convert.ToInt32(dr[0]) == 1001 || Convert.ToInt32(dr[0]) == 1002) { styleTxt = GetHeaderStyle(hssfworkbook); styleTxt.BorderBottom = NPOI.SS.UserModel.BorderStyle.Double; styleTxt.BorderTop = NPOI.SS.UserModel.BorderStyle.Double; styleTxt.GetFont(hssfworkbook).Boldweight = (short)FontBoldWeight.Normal; //styleTxt.DataFormat = 0xa; cell.CellStyle = styleTxt; if (Convert.ToInt32(dr[0]) == 1002) { HSSFDataFormat formatPercent = (NPOI.HSSF.UserModel.HSSFDataFormat)hssfworkbook.CreateDataFormat(); cell.CellStyle.DataFormat = formatPercent.GetFormat("0.0%"); } } cell.SetCellValue(Convert.ToDouble(dr[iColumn])); } cell = row.CreateCell(iColumn); styleTxt = GetHeaderStyle(hssfworkbook); styleTxt.GetFont(hssfworkbook).Boldweight = (short)FontBoldWeight.Normal; styleTxt.BorderBottom = NPOI.SS.UserModel.BorderStyle.Double; styleTxt.BorderTop = NPOI.SS.UserModel.BorderStyle.Double; styleTxt.BorderLeft = NPOI.SS.UserModel.BorderStyle.Double; styleTxt.BorderRight = NPOI.SS.UserModel.BorderStyle.Double; cell.CellStyle = styleTxt; if (Convert.ToInt32(dr[0]) == 1002) { HSSFDataFormat formatPercent = (NPOI.HSSF.UserModel.HSSFDataFormat)hssfworkbook.CreateDataFormat(); cell.CellStyle.DataFormat = formatPercent.GetFormat("0.0%"); cell.SetCellValue(Convert.ToDouble(dr[iColumn])); } else { cell.SetCellValue(Convert.ToDouble(dr[iColumn]) * 100); } } iCurrentRow = iRow; }
/// <summary>DataTable导出到Excel的MemoryStream</summary> static MemoryStream ExportDT(DataTable dtSource, string strHeaderText, string[] str) { HSSFWorkbook workbook = new HSSFWorkbook(); HSSFSheet sheet = workbook.CreateSheet() as HSSFSheet; HSSFCellStyle dateStyle = workbook.CreateCellStyle() as HSSFCellStyle; HSSFDataFormat format = workbook.CreateDataFormat() as HSSFDataFormat; dateStyle.DataFormat = format.GetFormat("yyyy-mm-dd"); //取得列宽 int[] arrColWidth = new int[dtSource.Columns.Count]; foreach (DataColumn item in dtSource.Columns) { arrColWidth[item.Ordinal] = Encoding.GetEncoding(936).GetBytes(item.ColumnName.ToString()).Length; } for (int i = 0; i < dtSource.Rows.Count; i++) { for (int j = 0; j < dtSource.Columns.Count; j++) { int intTemp = Encoding.GetEncoding(936).GetBytes(dtSource.Rows[i][j].ToString()).Length; if (intTemp > arrColWidth[j]) { arrColWidth[j] = intTemp; } } } int rowIndex = 0; foreach (DataRow row in dtSource.Rows) { #region 新建表,填充表头,填充列头,样式 if (rowIndex == 0) { if (rowIndex != 0) { sheet = workbook.CreateSheet() as HSSFSheet; } #region 表头及样式 { HSSFRow headerRow = sheet.CreateRow(0) as HSSFRow; headerRow.HeightInPoints = 25; headerRow.CreateCell(0).SetCellValue(strHeaderText); HSSFCellStyle headStyle = workbook.CreateCellStyle() as HSSFCellStyle; headStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.CENTER; HSSFFont font = workbook.CreateFont() as HSSFFont; font.FontName = str[5]; //[5] font.FontHeightInPoints = Convert.ToInt16(str[0]); //[0] font.Boldweight = Convert.ToInt16(str[1]); //[1] headStyle.SetFont(font); headerRow.GetCell(0).CellStyle = headStyle; sheet.AddMergedRegion(new Region(0, 0, 0, dtSource.Columns.Count - 1)); //headerRow.Dispose(); } #endregion #region 列头及样式 { HSSFRow headerRow = sheet.CreateRow(1) as HSSFRow; HSSFCellStyle headStyle = workbook.CreateCellStyle() as HSSFCellStyle; headStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.CENTER; HSSFFont font = workbook.CreateFont() as HSSFFont; font.FontName = str[6]; //[6] font.FontHeightInPoints = Convert.ToInt16(str[2]); //[2] font.Boldweight = Convert.ToInt16(str[3]); //[3] headStyle.SetFont(font); foreach (DataColumn column in dtSource.Columns) { headerRow.CreateCell(column.Ordinal).SetCellValue(column.ColumnName); headerRow.GetCell(column.Ordinal).CellStyle = headStyle; //设置列宽 sheet.SetColumnWidth(column.Ordinal, (arrColWidth[column.Ordinal] + 1) * Convert.ToInt32(str[4])); //[4] } //headerRow.Dispose(); } #endregion rowIndex = 2; } #endregion #region 填充内容 HSSFRow dataRow = sheet.CreateRow(rowIndex) as HSSFRow; foreach (DataColumn column in dtSource.Columns) { HSSFCell newCell = dataRow.CreateCell(column.Ordinal) as HSSFCell; string drValue = row[column].ToString(); switch (column.DataType.ToString()) { case "System.String": //字符串类型 string result = drValue; newCell.SetCellValue(result); break; case "System.DateTime": //日期类型 DateTime dateV; DateTime.TryParse(drValue, out dateV); newCell.SetCellValue(dateV); newCell.CellStyle = dateStyle; //格式化显示 break; case "System.Boolean": //布尔型 bool boolV = false; bool.TryParse(drValue, out boolV); newCell.SetCellValue(boolV); break; case "System.Int16": //整型 case "System.Int32": case "System.Int64": case "System.Byte": int intV = 0; int.TryParse(drValue, out intV); newCell.SetCellValue(intV); break; case "System.Decimal": //浮点型 case "System.Double": double doubV = 0; double.TryParse(drValue, out doubV); newCell.SetCellValue(doubV); break; case "System.DBNull": //空值处理 newCell.SetCellValue(""); break; default: newCell.SetCellValue(""); break; } } #endregion rowIndex++; } MemoryStream ms = new MemoryStream(); workbook.Write(ms); ms.Flush(); ms.Position = 0; //sheet; //workbook.Dispose(); return(ms); }
/// <summary> /// 创建工作表 /// </summary> /// <param name="workBook"></param> /// <param name="dt"></param> /// <param name="name"></param> /// <param name="header"></param> /// <returns></returns> private static HSSFSheet CreateSheet(HSSFWorkbook workBook, DataTable dt, string name = "sheet1", string header = "") { HSSFSheet sheet = (HSSFSheet)workBook.CreateSheet(name); HSSFCellStyle dateStyle = (HSSFCellStyle)workBook.CreateCellStyle(); HSSFDataFormat format = (HSSFDataFormat)workBook.CreateDataFormat(); dateStyle.DataFormat = format.GetFormat("yyyy-mm-dd"); //取得列宽 int[] arrColWidth = new int[dt.Columns.Count]; foreach (DataColumn item in dt.Columns) { arrColWidth[item.Ordinal] = Encoding.GetEncoding(936).GetBytes(item.ColumnName.ToString()).Length; } for (int i = 0; i < dt.Rows.Count; i++) { for (int j = 0; j < dt.Columns.Count; j++) { int intTemp = Encoding.GetEncoding(936).GetBytes(dt.Rows[i][j].ToString()).Length; if (intTemp > arrColWidth[j]) { arrColWidth[j] = intTemp; } } } int rowIndex = 0; foreach (DataRow row in dt.Rows) { #region 新建表,填充表头,填充列头,样式 if (rowIndex == 65535 || rowIndex == 0) { if (rowIndex != 0) { sheet = (HSSFSheet)workBook.CreateSheet(); } #region 表头及样式 { if (!string.IsNullOrEmpty(header)) { HSSFRow headerRow = (HSSFRow)sheet.CreateRow(0); headerRow.HeightInPoints = 25; headerRow.CreateCell(0).SetCellValue(header); HSSFCellStyle headStyle = (HSSFCellStyle)workBook.CreateCellStyle(); // headStyle.Alignment = CellHorizontalAlignment.CENTER; HSSFFont font = (HSSFFont)workBook.CreateFont(); font.FontHeightInPoints = 20; font.Boldweight = 700; font.FontName = "微软雅黑"; headStyle.SetFont(font); headerRow.GetCell(0).CellStyle = headStyle; // sheet.AddMergedRegion(new Region(0, 0, 0, dtSource.Columns.Count - 1)); //headerRow.Dispose(); } } #endregion #region 列头及样式 { HSSFRow headerRow = (HSSFRow)sheet.CreateRow(1); HSSFCellStyle headStyle = (HSSFCellStyle)workBook.CreateCellStyle(); //headStyle.Alignment = CellHorizontalAlignment.CENTER; HSSFFont font = (HSSFFont)workBook.CreateFont(); font.FontHeightInPoints = 10; font.Boldweight = 700; font.FontName = "微软雅黑"; headStyle.SetFont(font); foreach (DataColumn column in dt.Columns) { headerRow.CreateCell(column.Ordinal).SetCellValue(column.ColumnName); headerRow.GetCell(column.Ordinal).CellStyle = headStyle; //设置列宽 sheet.SetColumnWidth(column.Ordinal, (arrColWidth[column.Ordinal] + 1) * 256); } // headerRow.Dispose(); } #endregion rowIndex = 2; } #endregion #region 填充内容 HSSFRow dataRow = (HSSFRow)sheet.CreateRow(rowIndex); HSSFCellStyle cellStyle = (HSSFCellStyle)workBook.CreateCellStyle(); HSSFFont cellFont = (HSSFFont)workBook.CreateFont(); cellFont.FontName = "微软雅黑"; cellStyle.SetFont(cellFont); foreach (DataColumn column in dt.Columns) { HSSFCell newCell = (HSSFCell)dataRow.CreateCell(column.Ordinal); string drValue = row[column].ToString(); switch (column.DataType.ToString()) { case "System.String": //字符串类型 newCell.SetCellValue(drValue); break; case "System.DateTime": //日期类型 System.DateTime dateV; System.DateTime.TryParse(drValue, out dateV); newCell.SetCellValue(dateV); newCell.CellStyle = dateStyle; //格式化显示 break; case "System.Boolean": //布尔型 bool boolV = false; bool.TryParse(drValue, out boolV); newCell.SetCellValue(boolV); break; case "System.Int16": //整型 case "System.Int32": case "System.Int64": case "System.Byte": int intV = 0; int.TryParse(drValue, out intV); newCell.SetCellValue(intV); break; case "System.Decimal": //浮点型 case "System.Double": double doubV = 0; double.TryParse(drValue, out doubV); newCell.SetCellValue(doubV); break; case "System.DBNull": //空值处理 newCell.SetCellValue(""); break; default: newCell.SetCellValue(""); break; } newCell.CellStyle = cellStyle; } #endregion rowIndex++; } return(sheet); }
static ICellStyle Getcellstyle(IWorkbook wb, stylexls str = stylexls.默认) { ICellStyle cellStyle = wb.CreateCellStyle(); //定义几种字体 //也可以一种字体,写一些公共属性,然后在下面需要时加特殊的 IFont font12 = wb.CreateFont(); font12.FontHeightInPoints = 10; font12.FontName = "微软雅黑"; IFont font = wb.CreateFont(); font.FontName = "微软雅黑"; //font.Underline = 1;下划线 IFont fontcolorblue = wb.CreateFont(); fontcolorblue.Color = HSSFColor.OliveGreen.Black.Index; fontcolorblue.IsItalic = true;//下划线 fontcolorblue.FontName = "微软雅黑"; //边框 cellStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin; cellStyle.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin; cellStyle.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin; cellStyle.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin; //边框颜色 cellStyle.BottomBorderColor = HSSFColor.OliveGreen.Black.Index; cellStyle.TopBorderColor = HSSFColor.OliveGreen.Black.Index; //背景图形,我没有用到过。感觉很丑 //cellStyle.FillBackgroundColor = HSSFColor.OLIVE_GREEN.BLUE.index; //cellStyle.FillForegroundColor = HSSFColor.OLIVE_GREEN.BLUE.index; cellStyle.FillForegroundColor = HSSFColor.White.Index; // cellStyle.FillPattern = FillPatternType.NO_FILL; cellStyle.FillBackgroundColor = HSSFColor.Maroon.Index; //水平对齐 cellStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Left; //垂直对齐 cellStyle.VerticalAlignment = VerticalAlignment.Center; //自动换行 cellStyle.WrapText = true; //缩进;当设置为1时,前面留的空白太大了。希旺官网改进。或者是我设置的不对 cellStyle.Indention = 0; return(cellStyle); //上面基本都是设共公的设置 //下面列出了常用的字段类型 switch (str) { case stylexls.头: // cellStyle.FillPattern = FillPatternType.LEAST_DOTS; cellStyle.SetFont(font12); break; case stylexls.时间: IDataFormat datastyle = wb.CreateDataFormat(); cellStyle.DataFormat = datastyle.GetFormat("yyyy/mm/dd"); cellStyle.SetFont(font); break; case stylexls.数字: cellStyle.DataFormat = HSSFDataFormat.GetBuiltinFormat("0.00"); cellStyle.SetFont(font); break; case stylexls.钱: IDataFormat format = wb.CreateDataFormat(); cellStyle.DataFormat = format.GetFormat("¥#,##0"); cellStyle.SetFont(font); break; case stylexls.url: fontcolorblue.Underline = FontUnderlineType.Single; cellStyle.SetFont(fontcolorblue); break; case stylexls.百分比: cellStyle.DataFormat = HSSFDataFormat.GetBuiltinFormat("0.00%"); cellStyle.SetFont(font); break; case stylexls.中文大写: IDataFormat format1 = wb.CreateDataFormat(); cellStyle.DataFormat = format1.GetFormat("[DbNum2][$-804]0"); cellStyle.SetFont(font); break; case stylexls.科学计数法: cellStyle.DataFormat = HSSFDataFormat.GetBuiltinFormat("0.00E+00"); cellStyle.SetFont(font); break; case stylexls.默认: cellStyle.SetFont(font); break; } return(cellStyle); }
public static void GridToExcelLotYield(string sheetname, string lotid, string date, string[] summ1, string[] summ2, string[] summ3, DataGridView gv, string strExcelFileName, bool summflag, string[] summ4 = null, DataGridView gv1 = null) { HSSFWorkbook workbook = new HSSFWorkbook(); try { var rIndex = 0; ISheet sheet = workbook.CreateSheet(sheetname); //汇总栏 ICellStyle SummStyle = workbook.CreateCellStyle(); SummStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center; SummStyle.VerticalAlignment = NPOI.SS.UserModel.VerticalAlignment.Center; //字体 NPOI.SS.UserModel.IFont summfont = workbook.CreateFont(); summfont.Boldweight = (short)FontBoldWeight.Bold; summfont.Color = NPOI.HSSF.Util.HSSFColor.DarkBlue.Index; SummStyle.SetFont(summfont); SummStyle.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.DarkBlue.Index; SummStyle.FillBackgroundColor = NPOI.HSSF.Util.HSSFColor.Lavender.Index; //Lot ID IRow summRow_1 = sheet.CreateRow(0); summRow_1.Height = 20 * 20; if (summ4 == null) { ICell cell_1 = summRow_1.CreateCell(0); cell_1.SetCellValue("Lot ID:"); cell_1.CellStyle = SummStyle; ICell cell_2 = summRow_1.CreateCell(1); cell_2.SetCellValue(lotid); cell_2.CellStyle = SummStyle; ICell cell_3 = summRow_1.CreateCell(2); cell_3.SetCellValue("Date:"); cell_3.CellStyle = SummStyle; ICell cell_4 = summRow_1.CreateCell(3); cell_4.SetCellValue(date); cell_4.CellStyle = SummStyle; } if (summflag) { //汇总 IRow summRow_2 = sheet.CreateRow(1); summRow_2.Height = 20 * 20; ICell cell_21 = summRow_2.CreateCell(0); cell_21.SetCellValue(summ1[0]); cell_21.CellStyle = SummStyle; ICell cell_22 = summRow_2.CreateCell(1); cell_22.SetCellValue(summ1[1]); cell_22.CellStyle = SummStyle; ICell cell_23 = summRow_2.CreateCell(2); cell_23.SetCellValue(summ1[2]); cell_23.CellStyle = SummStyle; ICell cell_24 = summRow_2.CreateCell(3); cell_24.SetCellValue(summ1[3]); cell_24.CellStyle = SummStyle; ICell cell_25 = summRow_2.CreateCell(4); cell_25.SetCellValue("Create Date:"); cell_25.CellStyle = SummStyle; ICell cell_26 = summRow_2.CreateCell(5); cell_26.SetCellValue(DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss")); cell_26.CellStyle = SummStyle; IRow summRow_3 = sheet.CreateRow(2); summRow_3.Height = 20 * 20; ICell cell_31 = summRow_3.CreateCell(0); cell_31.SetCellValue(summ2[0]); cell_31.CellStyle = SummStyle; ICell cell_32 = summRow_3.CreateCell(1); cell_32.SetCellValue(summ2[1]); cell_32.CellStyle = SummStyle; ICell cell_33 = summRow_3.CreateCell(2); cell_33.SetCellValue(summ2[2]); cell_33.CellStyle = SummStyle; ICell cell_34 = summRow_3.CreateCell(3); cell_34.SetCellValue(summ2[3]); cell_34.CellStyle = SummStyle; IRow summRow_4 = sheet.CreateRow(3); summRow_4.Height = 20 * 20; ICell cell_41 = summRow_4.CreateCell(0); cell_41.SetCellValue(summ3[0]); cell_41.CellStyle = SummStyle; ICell cell_42 = summRow_4.CreateCell(1); cell_42.SetCellValue(summ3[1]); cell_42.CellStyle = SummStyle; if (summ3.Length > 2) { ICell cell_43 = summRow_4.CreateCell(2); cell_43.SetCellValue(summ3[2]); cell_43.CellStyle = SummStyle; ICell cell_44 = summRow_4.CreateCell(3); cell_44.SetCellValue(summ3[3]); cell_44.CellStyle = SummStyle; } if (summ4 != null) { IRow r = sheet.CreateRow(4); r.Height = 20 * 20; IRow r4 = null; for (int i = 0; i < summ4.Length; i++) { var cIndex = 1; if (i % 2 == 0) { r4 = sheet.CreateRow(5 + rIndex); r4.Height = 20 * 20; cIndex = 0; rIndex++; } ICell c = r4.CreateCell(cIndex); c.SetCellValue(summ4[i]); c.CellStyle = SummStyle; } rIndex++; } } //表格头 ICellStyle HeadercellStyle = workbook.CreateCellStyle(); HeadercellStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin; HeadercellStyle.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin; HeadercellStyle.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin; HeadercellStyle.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin; HeadercellStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center; HeadercellStyle.VerticalAlignment = NPOI.SS.UserModel.VerticalAlignment.Center; //字体 NPOI.SS.UserModel.IFont headerfont = workbook.CreateFont(); headerfont.Boldweight = (short)FontBoldWeight.Bold; HeadercellStyle.SetFont(headerfont); //HeadercellStyle.FillBackgroundColorColor HeadercellStyle.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.Black.Index; HeadercellStyle.FillBackgroundColor = NPOI.HSSF.Util.HSSFColor.Lavender.Index; var waferId = string.Empty; int tDefects = 0, badDie = 0; var yieldLoss = 0.00; if (gv1 != null) { int icolIndex1 = 0; int ridx1 = (summflag ? 4 + rIndex : 1 + rIndex); IRow r = sheet.CreateRow(ridx1); r.Height = 20 * 20; rIndex++; ridx1++; IRow headerRow1 = sheet.CreateRow(ridx1); //rIndex++; headerRow1.Height = 20 * 20; foreach (DataGridViewColumn item in gv1.Columns) { if (!item.Visible) { continue; } ICell cell = headerRow1.CreateCell(icolIndex1); sheet.SetColumnWidth(icolIndex1, item.Width * 50); cell.SetCellValue(item.HeaderText); cell.CellStyle = HeadercellStyle; icolIndex1++; } ICellStyle cellStyle1 = workbook.CreateCellStyle(); //为避免日期格式被Excel自动替换,所以设定 format 为 『@』 表示一率当成text來看 cellStyle1.DataFormat = HSSFDataFormat.GetBuiltinFormat("@"); cellStyle1.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin; cellStyle1.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin; cellStyle1.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin; cellStyle1.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin; NPOI.SS.UserModel.IFont cellfont1 = workbook.CreateFont(); cellfont1.Boldweight = (short)FontBoldWeight.Normal; cellStyle1.SetFont(cellfont1); //建立内容行 int iRowIndex1 = (summflag ? 5 + rIndex : 2 + rIndex); int iCellIndex1 = 0; if (gv1.Rows.Count > 0) { foreach (DataGridViewRow Rowitem in gv1.Rows) { IRow DataRow = sheet.CreateRow(iRowIndex1); foreach (DataGridViewColumn Colitem in gv1.Columns) { if (Colitem.Visible) { ICell cell = DataRow.CreateCell(iCellIndex1); cell.SetCellValue(Rowitem.Cells[Colitem.Name].FormattedValue.ToString()); cell.CellStyle = cellStyle1; iCellIndex1++; if (Colitem.Name == "SumYieldLoss") { yieldLoss += Convert.ToDouble(Rowitem.Cells[Colitem.Name].Value); } } } iCellIndex1 = 0; iRowIndex1++; rIndex++; } IRow DataRows = sheet.CreateRow(iRowIndex1); foreach (DataGridViewColumn Colitem in gv1.Columns) { if (Colitem.Visible) { ICell cell = DataRows.CreateCell(iCellIndex1); if (Colitem.Name == "SumYieldLoss") { cell.SetCellValue(string.Format("{0}/{1}", yieldLoss, 100 - yieldLoss)); } cell.CellStyle = cellStyle1; iCellIndex1++; } } iCellIndex1 = 0; iRowIndex1++; rIndex++; } rIndex++; } //用column name 作为列名 int icolIndex = 0; int ridx = (summflag ? 4 + rIndex : 1 + rIndex); IRow r2 = sheet.CreateRow(ridx); r2.Height = 20 * 20; rIndex++; ridx++; IRow headerRow = sheet.CreateRow(ridx); //rIndex++; headerRow.Height = 20 * 20; foreach (DataGridViewColumn item in gv.Columns) { if (!item.Visible) { continue; } ICell cell = headerRow.CreateCell(icolIndex); sheet.SetColumnWidth(icolIndex, item.Width * 50); cell.SetCellValue(item.HeaderText); cell.CellStyle = HeadercellStyle; icolIndex++; } ICellStyle cellStyle = workbook.CreateCellStyle(); //为避免日期格式被Excel自动替换,所以设定 format 为 『@』 表示一率当成text來看 cellStyle.DataFormat = HSSFDataFormat.GetBuiltinFormat("@"); cellStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin; cellStyle.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin; cellStyle.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin; cellStyle.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin; NPOI.SS.UserModel.IFont cellfont = workbook.CreateFont(); cellfont.Boldweight = (short)FontBoldWeight.Normal; cellStyle.SetFont(cellfont); ICellStyle sumStyle = workbook.CreateCellStyle(); //为避免日期格式被Excel自动替换,所以设定 format 为 『@』 表示一率当成text來看 sumStyle.DataFormat = HSSFDataFormat.GetBuiltinFormat("@"); sumStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin; sumStyle.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin; sumStyle.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin; sumStyle.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin; NPOI.SS.UserModel.IFont sumfont = workbook.CreateFont(); sumfont.Boldweight = (short)FontBoldWeight.Bold; sumStyle.SetFont(sumfont); //建立内容行 int iRowIndex = (summflag ? 5 + rIndex : 2 + rIndex); int iCellIndex = 0; int rcnt = 0; yieldLoss = 0; if (gv.Rows.Count > 0) { foreach (DataGridViewRow Rowitem in gv.Rows) { if (!string.IsNullOrEmpty(waferId) && waferId != Rowitem.Cells["WaferId"].FormattedValue.ToString()) { IRow DataRows = sheet.CreateRow(iRowIndex); foreach (DataGridViewColumn Colitem in gv.Columns) { if (Colitem.Visible) { ICell cell = DataRows.CreateCell(iCellIndex); if (Colitem.Name != "Code" && Colitem.Name != "Category" && Colitem.Name != "Pareto") { if (Colitem.Name == "TotalDefects") { cell.SetCellValue(tDefects); } else if (Colitem.Name == "DieQuantity") { cell.SetCellValue(badDie); } else if (Colitem.Name == "YieldLoss") { cell.SetCellValue(string.Format("{0}/{1}", yieldLoss, 100 - yieldLoss)); } else if (Colitem.Name == "WaferId") { cell.SetCellValue(waferId); } else { cell.SetCellValue(Rowitem.Cells[Colitem.Name].FormattedValue.ToString()); } } cell.CellStyle = sumStyle; iCellIndex++; } } tDefects = 0; badDie = 0; yieldLoss = 0; iCellIndex = 0; iRowIndex++; rIndex++; sheet.CreateRow(iRowIndex); iRowIndex++; rIndex++; } IRow DataRow = sheet.CreateRow(iRowIndex); foreach (DataGridViewColumn Colitem in gv.Columns) { if (Colitem.Visible) { ICell cell = DataRow.CreateCell(iCellIndex); cell.SetCellValue(Rowitem.Cells[Colitem.Name].FormattedValue.ToString()); cell.CellStyle = cellStyle; iCellIndex++; if (Colitem.Name == "TotalDefects") { tDefects += Convert.ToInt16(Rowitem.Cells[Colitem.Name].Value); } else if (Colitem.Name == "DieQuantity") { badDie += Convert.ToInt16(Rowitem.Cells[Colitem.Name].Value); } else if (Colitem.Name == "YieldLoss") { yieldLoss += Convert.ToDouble(Rowitem.Cells[Colitem.Name].Value); } } } iCellIndex = 0; iRowIndex++; rIndex++; rcnt++; waferId = Rowitem.Cells["WaferId"].FormattedValue.ToString(); if (!string.IsNullOrEmpty(waferId) && rcnt == gv.Rows.Count) { IRow DataRows = sheet.CreateRow(iRowIndex); foreach (DataGridViewColumn Colitem in gv.Columns) { if (Colitem.Visible) { ICell cell = DataRows.CreateCell(iCellIndex); if (Colitem.Name != "Code" && Colitem.Name != "Category" && Colitem.Name != "Pareto") { if (Colitem.Name == "TotalDefects") { cell.SetCellValue(tDefects); } else if (Colitem.Name == "DieQuantity") { cell.SetCellValue(badDie); } else if (Colitem.Name == "YieldLoss") { cell.SetCellValue(string.Format("{0}/{1}", yieldLoss, 100 - yieldLoss)); } else if (Colitem.Name == "WaferId") { cell.SetCellValue(waferId); } else { cell.SetCellValue(Rowitem.Cells[Colitem.Name].FormattedValue.ToString()); } } cell.CellStyle = sumStyle; iCellIndex++; } } tDefects = 0; badDie = 0; yieldLoss = 0; iCellIndex = 0; iRowIndex++; rIndex++; IRow drSum = sheet.CreateRow(iRowIndex); drSum.Height = 20 * 20; iRowIndex++; rIndex++; } } ////自适应列宽度 //for (int i = 0; i < icolIndex; i++) //{ // sheet.AutoSizeColumn(i); //} } //写Excel FileStream file = new FileStream(strExcelFileName, FileMode.OpenOrCreate); workbook.Write(file); file.Flush(); file.Close(); } catch (Exception ex) { throw ex; } finally { workbook = null; } }
/// <summary> /// 定义单元格常用到样式 /// </summary> /// <param name="wb"></param> /// <param name="st"></param> /// <returns></returns> static ICellStyle CreateCellStyle(IWorkbook wb, StyleType st = StyleType.normal) { ICellStyle cellStyle = wb.CreateCellStyle(); //为避免日期格式被Excel自动替换,所以设定 format 为 『@』 表示一率当成text來看 cellStyle.DataFormat = HSSFDataFormat.GetBuiltinFormat("@"); cellStyle.BorderBottom = BorderStyle.Thin; cellStyle.BorderLeft = BorderStyle.Thin; cellStyle.BorderRight = BorderStyle.Thin; cellStyle.BorderTop = BorderStyle.Thin; //水平垂直居中 cellStyle.Alignment = HorizontalAlignment.Center; cellStyle.VerticalAlignment = VerticalAlignment.Center; //背景颜色 cellStyle.FillForegroundColor = 9; cellStyle.FillPattern = FillPattern.SolidForeground; //默认 IFont font = wb.CreateFont(); font.FontHeightInPoints = 10; font.Color = 8; switch (st) { case StyleType.normal: break; case StyleType.headCenter: font.FontHeightInPoints = 10; font.IsBold = true; break; case StyleType.headLeft: font.FontHeightInPoints = 10; font.IsBold = true; cellStyle.Alignment = HorizontalAlignment.Left; break; case StyleType.whiteGreen: font.Color = 9; cellStyle.FillForegroundColor = 17; cellStyle.FillPattern = FillPattern.SolidForeground; break; case StyleType.whiteOrange: font.Color = 9; cellStyle.FillForegroundColor = 51; cellStyle.FillPattern = FillPattern.SolidForeground; break; case StyleType.blue: font.Color = 12; break; case StyleType.green: font.Color = 17; break; case StyleType.blankGray: font.Color = 8; cellStyle.FillForegroundColor = 22; cellStyle.FillPattern = FillPattern.SolidForeground; break; case StyleType.bold: font.IsBold = true; break; case StyleType.headNoBorder: font.FontHeightInPoints = 10; font.IsBold = true; break; } cellStyle.SetFont(font); return(cellStyle); }
/// <summary> /// DataGridView导出到Excel的MemoryStream /// </summary> /// <param name="myDgv">源DataTable</param> /// <param name="strHeaderText">表头文本</param> public static MemoryStream DataGridViewToExcel(DataGridView myDgv, string strHeaderText) { HSSFWorkbook workbook = new HSSFWorkbook(); HSSFSheet sheet = (HSSFSheet)workbook.CreateSheet(); #region 右击文件 属性信息 { DocumentSummaryInformation dsi = PropertySetFactory.CreateDocumentSummaryInformation(); dsi.Company = "NPOI"; workbook.DocumentSummaryInformation = dsi; SummaryInformation si = PropertySetFactory.CreateSummaryInformation(); si.Author = "文件作者信息"; //填加xls文件作者信息 si.ApplicationName = "创建程序信息"; //填加xls文件创建程序信息 si.LastAuthor = "最后保存者信息"; //填加xls文件最后保存者信息 si.Comments = "作者信息"; //填加xls文件作者信息 si.Title = "标题信息"; //填加xls文件标题信息 si.Subject = "主题信息"; //填加文件主题信息 si.CreateDateTime = System.DateTime.Now; workbook.SummaryInformation = si; } #endregion HSSFCellStyle dateStyle = (HSSFCellStyle)workbook.CreateCellStyle(); HSSFDataFormat format = (HSSFDataFormat)workbook.CreateDataFormat(); dateStyle.DataFormat = format.GetFormat("yyyy-mm-dd"); //取得列宽 int[] arrColWidth = new int[myDgv.Columns.Count]; foreach (DataGridViewColumn item in myDgv.Columns) { arrColWidth[item.Index] = Encoding.GetEncoding(936).GetBytes(item.HeaderText.ToString()).Length; } for (int i = 0; i < myDgv.Rows.Count; i++) { for (int j = 0; j < myDgv.Columns.Count; j++) { int intTemp = Encoding.GetEncoding(936).GetBytes(myDgv.Rows[i].Cells[j].ToString()).Length; if (intTemp > arrColWidth[j]) { arrColWidth[j] = intTemp; } } } int rowIndex = 0; foreach (DataGridViewRow row in myDgv.Rows) { #region 新建表,填充表头,填充列头,样式 if (rowIndex == 65535 || rowIndex == 0) { if (rowIndex != 0) { sheet = (HSSFSheet)workbook.CreateSheet(); } #region 表头及样式 { HSSFRow headerRow = (HSSFRow)sheet.CreateRow(0); headerRow.HeightInPoints = 25; headerRow.CreateCell(0).SetCellValue(strHeaderText); HSSFCellStyle headStyle = (HSSFCellStyle)workbook.CreateCellStyle(); // headStyle.Alignment = CellHorizontalAlignment.CENTER; HSSFFont font = (HSSFFont)workbook.CreateFont(); font.FontHeightInPoints = 20; font.Boldweight = 700; headStyle.SetFont(font); headerRow.GetCell(0).CellStyle = headStyle; // sheet.AddMergedRegion(new Region(0, 0, 0, dtSource.Columns.Count - 1)); //headerRow.Dispose(); } #endregion #region 列头及样式 { HSSFRow headerRow = (HSSFRow)sheet.CreateRow(1); HSSFCellStyle headStyle = (HSSFCellStyle)workbook.CreateCellStyle(); //headStyle.Alignment = CellHorizontalAlignment.CENTER; HSSFFont font = (HSSFFont)workbook.CreateFont(); font.FontHeightInPoints = 10; font.Boldweight = 700; headStyle.SetFont(font); foreach (DataGridViewColumn column in myDgv.Columns) { headerRow.CreateCell(column.Index).SetCellValue(column.HeaderText); headerRow.GetCell(column.Index).CellStyle = headStyle; //设置列宽 sheet.SetColumnWidth(column.Index, (arrColWidth[column.Index] + 1) * 256); } // headerRow.Dispose(); } #endregion rowIndex = 2; } #endregion #region 填充内容 HSSFRow dataRow = (HSSFRow)sheet.CreateRow(rowIndex); if (row.Index > 0) { foreach (DataGridViewColumn column in myDgv.Columns) { HSSFCell newCell = (HSSFCell)dataRow.CreateCell(column.Index); string drValue = myDgv[column.Index, row.Index - 1].Value.ToString(); switch (column.ValueType.ToString()) { case "System.String": //字符串类型 newCell.SetCellValue(drValue); break; case "System.DateTime": //日期类型 System.DateTime dateV; System.DateTime.TryParse(drValue, out dateV); newCell.SetCellValue(dateV); newCell.CellStyle = dateStyle; //格式化显示 break; case "System.Boolean": //布尔型 bool boolV = false; bool.TryParse(drValue, out boolV); newCell.SetCellValue(boolV); break; case "System.Int16": //整型 case "System.Int32": case "System.Int64": case "System.Byte": int intV = 0; int.TryParse(drValue, out intV); newCell.SetCellValue(intV); break; case "System.Decimal": //浮点型 case "System.Double": double doubV = 0; double.TryParse(drValue, out doubV); newCell.SetCellValue(doubV); break; case "System.DBNull": //空值处理 newCell.SetCellValue(""); break; default: newCell.SetCellValue(""); break; } } } else { rowIndex--; } #endregion rowIndex++; } using (MemoryStream ms = new MemoryStream()) { workbook.Write(ms); ms.Flush(); ms.Position = 0; //sheet.Dispose(); //workbook.Dispose();//一般只用写这一个就OK了,他会遍历并释放所有资源,但当前版本有问题所以只释放sheet return(ms); } }
/// <summary> /// Exports to excel. /// </summary> /// <returns></returns> public ActionResult ExportToExcel(int?showInActive) { var workbook = new HSSFWorkbook(); var sheet = workbook.CreateSheet("DashboardIndicatorsData"); var format = workbook.CreateDataFormat(); sheet.CreateFreezePane(0, 1, 0, 1); sheet.SetAutoFilter(CellRangeAddress.ValueOf("A1:L1")); // Add header labels var rowIndex = 0; var row = sheet.CreateRow(rowIndex); row.CreateCell(0).SetCellValue("Indicator Number"); row.CreateCell(1).SetCellValue("Dashboard"); row.CreateCell(2).SetCellValue("Description"); row.CreateCell(3).SetCellValue("Defination"); row.CreateCell(4).SetCellValue("Sub Category 1"); row.CreateCell(5).SetCellValue("Sub Category 2"); row.CreateCell(6).SetCellValue("Format Type"); row.CreateCell(7).SetCellValue("Decimal Numbers"); row.CreateCell(8).SetCellValue("Ferquency Type"); row.CreateCell(9).SetCellValue("OwnerShip"); row.CreateCell(10).SetCellValue("Facility"); row.CreateCell(11).SetCellValue("SortOrder"); rowIndex++; using (var bal = new DashboardIndicatorsBal()) { var indicatorsData = bal.GetDashboardIndicatorsListActiveInActive(Helpers.GetSysAdminCorporateID(), showInActive); indicatorsData = indicatorsData.OrderBy(x => Convert.ToInt32(x.IndicatorNumber)).ToList(); var cellStyle = workbook.CreateCellStyle(); cellStyle.DataFormat = HSSFDataFormat.GetBuiltinFormat("0.00"); foreach (var item in indicatorsData) { row = sheet.CreateRow(rowIndex); row.CreateCell(0).SetCellType(CellType.Numeric); row.CreateCell(0).CellStyle = cellStyle; row.CreateCell(0).SetCellValue(Convert.ToDouble(item.IndicatorNumber)); row.CreateCell(1).SetCellValue(item.Dashboard); row.CreateCell(2).SetCellValue(item.Description); row.CreateCell(3).SetCellValue(item.Defination); row.CreateCell(4).SetCellValue(item.SubCategoryFirst); row.CreateCell(5).SetCellValue(item.SubCategorySecond); row.CreateCell(6).SetCellValue(item.FormatTypeStr); row.CreateCell(7).SetCellType(CellType.Numeric); row.CreateCell(7).CellStyle = cellStyle; row.CreateCell(7).SetCellValue(item.DecimalNumbers); row.CreateCell(8).SetCellValue(item.FerquencyTypeStr); row.CreateCell(9).SetCellValue(item.OwnerShip); row.CreateCell(10).SetCellValue(item.FacilityNameStr); row.CreateCell(11).SetCellValue(Convert.ToInt32(item.SortOrder)); rowIndex++; } } using (var exportData = new MemoryStream()) { var currentDateTime = Helpers.GetInvariantCultureDateTime(); var cookie = new HttpCookie("Downloaded", "True"); Response.Cookies.Add(cookie); workbook.Write(exportData); var saveAsFileName = string.Format("DashboardIndicatorsData-{0:d}.xls", currentDateTime).Replace("/", "-"); return(File(exportData.ToArray(), "application/vnd.ms-excel", string.Format("{0}", saveAsFileName))); } }
/// <summary> /// 导出数据行 /// </summary> /// <param name="dtSource"></param> /// <param name="drSource"></param> /// <param name="currentExcelRow"></param> /// <param name="excelSheet"></param> /// <param name="excelWorkBook"></param> protected static void InsertCell(DataTable dtSource, DataRow drSource, HSSFRow currentExcelRow, HSSFSheet excelSheet, HSSFWorkbook excelWorkBook) { for (int cellIndex = 0; cellIndex < ListColumnsName.Count; cellIndex++) { //列名称 string columnsName = ListColumnsName.GetKey(cellIndex).ToString(); HSSFCell newCell = null; System.Type rowType = drSource[columnsName].GetType(); string drValue = drSource[columnsName].ToString().Trim(); switch (rowType.ToString()) { case "System.String": //字符串类型 drValue = drValue.Replace("&", "&"); drValue = drValue.Replace(">", ">"); drValue = drValue.Replace("<", "<"); newCell = (HSSFCell)currentExcelRow.CreateCell(cellIndex); newCell.SetCellValue(drValue); break; case "System.DateTime": //日期类型 DateTime dateV; DateTime.TryParse(drValue, out dateV); newCell = (HSSFCell)currentExcelRow.CreateCell(cellIndex); newCell.SetCellValue(dateV); //格式化显示 HSSFCellStyle cellStyle = (HSSFCellStyle)excelWorkBook.CreateCellStyle(); HSSFDataFormat format = (HSSFDataFormat)excelWorkBook.CreateDataFormat(); cellStyle.DataFormat = format.GetFormat("yyyy-mm-dd hh:mm:ss"); newCell.CellStyle = cellStyle; break; case "System.Boolean": //布尔型 bool boolV = false; bool.TryParse(drValue, out boolV); newCell = (HSSFCell)currentExcelRow.CreateCell(cellIndex); newCell.SetCellValue(boolV); break; case "System.Int16": //整型 case "System.Int32": case "System.Int64": case "System.Byte": int intV = 0; int.TryParse(drValue, out intV); newCell = (HSSFCell)currentExcelRow.CreateCell(cellIndex); newCell.SetCellValue(intV.ToString()); break; case "System.Decimal": //浮点型 case "System.Double": double doubV = 0; double.TryParse(drValue, out doubV); newCell = (HSSFCell)currentExcelRow.CreateCell(cellIndex); newCell.SetCellValue(doubV); break; case "System.DBNull": //空值处理 newCell = (HSSFCell)currentExcelRow.CreateCell(cellIndex); newCell.SetCellValue(""); break; default: throw (new Exception(rowType.ToString() + ":类型数据无法处理!")); } } }
public void ExportToExcel(int page, int size, int store, int supplier, string stockCode, string stockName, int status, string fd, string td, string enable) { // Get the data to report on var masters = _service.ListCondition(page, size, store, supplier, stockCode, stockName, status, fd, td, enable); //var requisitionDetails = _service.ListConditionDetailExcel(page, size, store, mrf, stockCode, stockName, status, // fd, td, enable); // Create a new workbook var workbook = new HSSFWorkbook(); #region Cell Styles #region HeaderLabel Cell Style var headerLabelCellStyle = workbook.CreateCellStyle(); headerLabelCellStyle.Alignment = HorizontalAlignment.CENTER; headerLabelCellStyle.BorderBottom = CellBorderType.THIN; var headerLabelFont = workbook.CreateFont(); headerLabelFont.Boldweight = (short)FontBoldWeight.BOLD; headerLabelCellStyle.SetFont(headerLabelFont); #endregion #region RightAligned Cell Style var rightAlignedCellStyle = workbook.CreateCellStyle(); rightAlignedCellStyle.Alignment = HorizontalAlignment.RIGHT; #endregion #region Currency Cell Style var currencyCellStyle = workbook.CreateCellStyle(); currencyCellStyle.Alignment = HorizontalAlignment.RIGHT; var formatId = HSSFDataFormat.GetBuiltinFormat("$#,##0.00"); if (formatId == -1) { var newDataFormat = workbook.CreateDataFormat(); currencyCellStyle.DataFormat = newDataFormat.GetFormat("$#,##0.00"); } else { currencyCellStyle.DataFormat = formatId; } #endregion #region Detail Subtotal Style var detailSubtotalCellStyle = workbook.CreateCellStyle(); detailSubtotalCellStyle.BorderTop = CellBorderType.THIN; detailSubtotalCellStyle.BorderBottom = CellBorderType.THIN; var detailSubtotalFont = workbook.CreateFont(); detailSubtotalFont.Boldweight = (short)FontBoldWeight.BOLD; detailSubtotalCellStyle.SetFont(detailSubtotalFont); #endregion #region Detail Currency Subtotal Style var detailCurrencySubtotalCellStyle = workbook.CreateCellStyle(); detailCurrencySubtotalCellStyle.BorderTop = CellBorderType.THIN; detailCurrencySubtotalCellStyle.BorderBottom = CellBorderType.THIN; var detailCurrencySubtotalFont = workbook.CreateFont(); detailCurrencySubtotalFont.Boldweight = (short)FontBoldWeight.BOLD; detailCurrencySubtotalCellStyle.SetFont(detailCurrencySubtotalFont); formatId = HSSFDataFormat.GetBuiltinFormat("$#,##0.00"); if (formatId == -1) { var newDataFormat = workbook.CreateDataFormat(); detailCurrencySubtotalCellStyle.DataFormat = newDataFormat.GetFormat("$#,##0.00"); } else { detailCurrencySubtotalCellStyle.DataFormat = formatId; } #endregion #endregion #region Master sheet var sheet = workbook.CreateSheet("Price"); // Add header labels var rowIndex = 0; // Undestand as row in excel. row + 3 = xuong 3 row. var row = sheet.CreateRow(rowIndex); var cell = row.CreateCell(0); cell.SetCellValue("Store"); cell.CellStyle = headerLabelCellStyle; cell = row.CreateCell(1); cell.SetCellValue("Supplier"); cell.CellStyle = headerLabelCellStyle; cell = row.CreateCell(2); cell.SetCellValue("Stock Code"); cell.CellStyle = headerLabelCellStyle; cell = row.CreateCell(3); cell.SetCellValue("Stock Name"); cell.CellStyle = headerLabelCellStyle; cell = row.CreateCell(4); cell.SetCellValue("Price"); cell.CellStyle = headerLabelCellStyle; cell = row.CreateCell(5); cell.SetCellValue("Currency"); cell.CellStyle = headerLabelCellStyle; cell = row.CreateCell(6); cell.SetCellValue("Status"); cell.CellStyle = headerLabelCellStyle; cell = row.CreateCell(7); cell.SetCellValue("Start Date"); cell.CellStyle = headerLabelCellStyle; cell = row.CreateCell(8); cell.SetCellValue("End Date"); cell.CellStyle = headerLabelCellStyle; cell = row.CreateCell(9); cell.SetCellValue("Created Date"); cell.CellStyle = headerLabelCellStyle; cell = row.CreateCell(10); cell.SetCellValue("Created By"); cell.CellStyle = headerLabelCellStyle; cell = row.CreateCell(11); cell.SetCellValue("Modified Date"); cell.CellStyle = headerLabelCellStyle; cell = row.CreateCell(12); cell.SetCellValue("Modified By"); cell.CellStyle = headerLabelCellStyle; rowIndex++; // Add data rows foreach (var master in masters) { row = sheet.CreateRow(rowIndex); row.CreateCell(0).SetCellValue(master.Store); row.CreateCell(1).SetCellValue(master.Supplier); row.CreateCell(2).SetCellValue(master.Stock_Code); row.CreateCell(3).SetCellValue(master.Stock_Name); row.CreateCell(4).SetCellValue(master.Price.ToString()); row.CreateCell(5).SetCellValue(master.Currency); row.CreateCell(6).SetCellValue(master.Status); row.CreateCell(7).SetCellValue(master.Start != null ? master.Start.Value.ToString("dd/MM/yyyy") : master.Start.ToString()); row.CreateCell(8).SetCellValue(master.End != null ? master.End.Value.ToString("dd/MM/yyyy") : master.End.ToString()); row.CreateCell(9).SetCellValue(master.Created_Date != null ? master.Created_Date.Value.ToString("dd/MM/yyyy") : master.Created_Date.ToString()); row.CreateCell(10).SetCellValue(master.Created_By); row.CreateCell(11).SetCellValue(master.Modified_Date != null ? master.Modified_Date.Value.ToString("dd/MM/yyyy") : master.Modified_Date.ToString()); row.CreateCell(12).SetCellValue(master.Modified_By); rowIndex++; } // Auto-size each column for (var i = 0; i < sheet.GetRow(0).LastCellNum; i++) { sheet.AutoSizeColumn(i); // Bump up with auto-sized column width to account for bold headers sheet.SetColumnWidth(i, sheet.GetColumnWidth(i) + 1024); } // Add row indicating date/time report was generated... sheet.CreateRow(rowIndex + 1).CreateCell(0).SetCellValue("Report generated on " + DateTime.Now.ToString("dd/MM/yyyy")); #endregion // Save the Excel spreadsheet to a MemoryStream and return it to the client using (var exportData = new MemoryStream()) { workbook.Write(exportData); var saveAsFileName = string.Format("Price-{0}.xls", DateTime.Now.ToString("ddMMyyyyHHmmss")).Replace("/", "-"); Response.ContentType = "application/vnd.ms-excel"; Response.AddHeader("Content-Disposition", string.Format("attachment;filename={0}", saveAsFileName)); Response.Clear(); Response.BinaryWrite(exportData.GetBuffer()); Response.End(); } }
/// <summary> /// 设置Excel单元格样式(标题),数据格式 /// </summary> /// <param name="dateType">数据类型</param> /// <param name="porpetyIndex">单元格索引</param> /// <param name="sheet">Sheet页</param> /// <param name="dataSheet">数据Sheet页</param> /// <param name="dataStyle">样式</param> /// <param name="dataFormat">格式</param> public void SetColumnFormat(ColumnDataType dateType, int porpetyIndex, ISheet sheet, ISheet dataSheet, ICellStyle dataStyle, IDataFormat dataFormat) { XSSFDataValidationHelper dvHelper = new XSSFDataValidationHelper((XSSFSheet)sheet); CellRangeAddressList CellRangeList = new CellRangeAddressList(1, 1048576 - 1, porpetyIndex, porpetyIndex); //超过1048576最大行数,打开Excel会报错 XSSFDataValidationConstraint dvConstraint = null; XSSFDataValidation dataValidation = null; switch (dateType) { case ColumnDataType.Date: case ColumnDataType.DateTime: //因为DateTime类型,添加Validation报错,所以去掉 dataStyle.DataFormat = dataFormat.GetFormat("yyyy-MM-dd HH:mm:ss"); break; case ColumnDataType.Number: this.MinValueOrLength = string.IsNullOrEmpty(this.MinValueOrLength) ? long.MinValue.ToString() : this.MinValueOrLength; this.MaxValuseOrLength = string.IsNullOrEmpty(this.MaxValuseOrLength) ? long.MaxValue.ToString() : this.MaxValuseOrLength; dvConstraint = (XSSFDataValidationConstraint)dvHelper.CreateNumericConstraint(ValidationType.INTEGER, OperatorType.BETWEEN, this.MinValueOrLength, this.MaxValuseOrLength); dataValidation = (XSSFDataValidation)dvHelper.CreateValidation(dvConstraint, CellRangeList); dataValidation.CreateErrorBox(CoreProgram.Callerlocalizer?["Error"], CoreProgram.Callerlocalizer?["PleaseInputNumber"]); dataStyle.DataFormat = dataFormat.GetFormat("0"); dataValidation.CreatePromptBox(CoreProgram.Callerlocalizer?["PleaseInputNumberFormat"], CoreProgram.Callerlocalizer?["DataRange", MinValueOrLength, MaxValuseOrLength]); break; case ColumnDataType.Float: this.MinValueOrLength = string.IsNullOrEmpty(this.MinValueOrLength) ? decimal.MinValue.ToString() : this.MinValueOrLength; this.MaxValuseOrLength = string.IsNullOrEmpty(this.MaxValuseOrLength) ? decimal.MaxValue.ToString() : this.MaxValuseOrLength; dvConstraint = (XSSFDataValidationConstraint)dvHelper.CreateNumericConstraint(ValidationType.DECIMAL, OperatorType.BETWEEN, this.MinValueOrLength, this.MaxValuseOrLength); dataValidation = (XSSFDataValidation)dvHelper.CreateValidation(dvConstraint, CellRangeList); dataValidation.CreateErrorBox(CoreProgram.Callerlocalizer?["Error"], CoreProgram.Callerlocalizer?["PleaseInputDecimal"]); dataStyle.DataFormat = HSSFDataFormat.GetBuiltinFormat("0.00"); dataValidation.CreatePromptBox(CoreProgram.Callerlocalizer?["PleaseInputDecimalFormat"], CoreProgram.Callerlocalizer?["DataRange", MinValueOrLength, MaxValuseOrLength]); break; case ColumnDataType.Bool: dvConstraint = (XSSFDataValidationConstraint)dvHelper.CreateFormulaListConstraint("Sheet1!$A$1:$B$1"); dataValidation = (XSSFDataValidation)dvHelper.CreateValidation(dvConstraint, CellRangeList); dataValidation.CreateErrorBox(CoreProgram.Callerlocalizer?["Error"], CoreProgram.Callerlocalizer?["PleaseInputExistData"]); dataValidation.CreatePromptBox(CoreProgram.Callerlocalizer?["ComboBox"], CoreProgram.Callerlocalizer?["PleaseInputExistData"]); break; case ColumnDataType.Text: this.MinValueOrLength = string.IsNullOrEmpty(this.MinValueOrLength) ? "0" : this.MinValueOrLength; this.MaxValuseOrLength = string.IsNullOrEmpty(this.MaxValuseOrLength) ? "2000" : this.MaxValuseOrLength; dvConstraint = (XSSFDataValidationConstraint)dvHelper.CreateNumericConstraint(ValidationType.TEXT_LENGTH, OperatorType.BETWEEN, this.MinValueOrLength, this.MaxValuseOrLength); dataValidation = (XSSFDataValidation)dvHelper.CreateValidation(dvConstraint, CellRangeList); dataValidation.CreateErrorBox(CoreProgram.Callerlocalizer?["Error"], CoreProgram.Callerlocalizer?["WrongTextLength"]); dataStyle.DataFormat = dataFormat.GetFormat("@"); dataValidation.CreatePromptBox(CoreProgram.Callerlocalizer?["PleaseInputText"], CoreProgram.Callerlocalizer?["DataRange", MinValueOrLength, MaxValuseOrLength]); break; case ColumnDataType.ComboBox: case ColumnDataType.Enum: int count = this.ListItems.Count() == 0 ? 1 : this.ListItems.Count(); string cloIndex = ""; if (porpetyIndex > 25) { cloIndex += Convert.ToChar((int)(Math.Floor(porpetyIndex / 26d)) - 1 + 65); } cloIndex += Convert.ToChar(65 + porpetyIndex % 26).ToString(); IName range = sheet.Workbook.CreateName(); range.RefersToFormula = "Sheet2!$" + cloIndex + "$1:$" + cloIndex + "$" + count; range.NameName = "dicRange" + porpetyIndex; dvConstraint = (XSSFDataValidationConstraint)dvHelper.CreateFormulaListConstraint("dicRange" + porpetyIndex); dataValidation = (XSSFDataValidation)dvHelper.CreateValidation(dvConstraint, CellRangeList); dataValidation.CreateErrorBox(CoreProgram.Callerlocalizer?["Error"], CoreProgram.Callerlocalizer?["PleaseInputExistData"]); var listItemsTemp = this.ListItems.ToList(); for (int rowIndex = 0; rowIndex < this.ListItems.Count(); rowIndex++) { IRow dataSheetRow = dataSheet.GetRow(rowIndex); if (dataSheetRow == null) { dataSheetRow = dataSheet.CreateRow(rowIndex); } dataSheetRow.CreateCell(porpetyIndex).SetCellValue(listItemsTemp[rowIndex].Text); dataStyle.DataFormat = dataFormat.GetFormat("@"); dataSheetRow.Cells.Where(x => x.ColumnIndex == porpetyIndex).FirstOrDefault().CellStyle = dataStyle; } dataValidation.CreatePromptBox(CoreProgram.Callerlocalizer?["ComboBox"], CoreProgram.Callerlocalizer?["PleaseInputExistData"]); break; default: dvConstraint = (XSSFDataValidationConstraint)dvHelper.CreateNumericConstraint(ValidationType.TEXT_LENGTH, OperatorType.BETWEEN, this.MinValueOrLength, this.MaxValuseOrLength); dataValidation = (XSSFDataValidation)dvHelper.CreateValidation(dvConstraint, CellRangeList); dataValidation.CreateErrorBox(CoreProgram.Callerlocalizer?["Error"], CoreProgram.Callerlocalizer?["WrongTextLength"]); dataStyle.DataFormat = HSSFDataFormat.GetBuiltinFormat("@"); break; } if (dataValidation == null) { return; } if (!this.IsNullAble) { dataValidation.EmptyCellAllowed = false; } sheet.SetDefaultColumnStyle(porpetyIndex, dataStyle); dataValidation.ShowErrorBox = true; sheet.AddValidationData(dataValidation); }
static ICellStyle Getcellstyle(IWorkbook wb, stylexls str) { ICellStyle cellStyle = wb.CreateCellStyle(); //定义几种字体 //也可以一种字体,写一些公共属性,然后在下面需要时加特殊的 IFont font12 = wb.CreateFont(); font12.FontHeightInPoints = 10; font12.FontName = "微软雅黑"; IFont font = wb.CreateFont(); font.FontName = "微软雅黑"; //font.Underline = 1;下划线 IFont fontcolorblue = wb.CreateFont(); fontcolorblue.Color = HSSFColor.OLIVE_GREEN.BLUE.index; fontcolorblue.IsItalic = true;//下划线 fontcolorblue.FontName = "微软雅黑"; //边框 cellStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.DOTTED; cellStyle.BorderLeft = NPOI.SS.UserModel.BorderStyle.HAIR; cellStyle.BorderRight = NPOI.SS.UserModel.BorderStyle.HAIR; cellStyle.BorderTop = NPOI.SS.UserModel.BorderStyle.DOTTED; //边框颜色 cellStyle.BottomBorderColor = HSSFColor.OLIVE_GREEN.BLUE.index; cellStyle.TopBorderColor = HSSFColor.OLIVE_GREEN.BLUE.index; //背景图形,我没有用到过。感觉很丑 //cellStyle.FillBackgroundColor = HSSFColor.OLIVE_GREEN.BLUE.index; //cellStyle.FillForegroundColor = HSSFColor.OLIVE_GREEN.BLUE.index; cellStyle.FillForegroundColor = HSSFColor.WHITE.index; // cellStyle.FillPattern = FillPatternType.NO_FILL; cellStyle.FillBackgroundColor = HSSFColor.BLUE.index; //水平对齐 cellStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.LEFT; //垂直对齐 cellStyle.VerticalAlignment = VerticalAlignment.CENTER; //自动换行 cellStyle.WrapText = true; //缩进;当设置为1时,前面留的空白太大了。希旺官网改进。或者是我设置的不对 cellStyle.Indention = 0; //上面基本都是设共公的设置 //下面列出了常用的字段类型 switch (str) { case stylexls.header: // cellStyle.FillPattern = FillPatternType.LEAST_DOTS; cellStyle.SetFont(font12); break; case stylexls.time: IDataFormat datastyle = wb.CreateDataFormat(); cellStyle.DataFormat = datastyle.GetFormat("yyyy/mm/dd"); cellStyle.SetFont(font); break; case stylexls.number: cellStyle.DataFormat = HSSFDataFormat.GetBuiltinFormat("0.00"); cellStyle.SetFont(font); break; case stylexls.money: IDataFormat format = wb.CreateDataFormat(); cellStyle.DataFormat = format.GetFormat("¥#,##0"); cellStyle.SetFont(font); break; case stylexls.url: fontcolorblue.Underline = 1; cellStyle.SetFont(fontcolorblue); break; case stylexls.percent: cellStyle.DataFormat = HSSFDataFormat.GetBuiltinFormat("0.00%"); cellStyle.SetFont(font); break; case stylexls.CHupper: IDataFormat format1 = wb.CreateDataFormat(); cellStyle.DataFormat = format1.GetFormat("[DbNum2][$-804]0"); cellStyle.SetFont(font); break; case stylexls.Tnumber: cellStyle.DataFormat = HSSFDataFormat.GetBuiltinFormat("0.00E+00"); cellStyle.SetFont(font); break; case stylexls.defaults: cellStyle.SetFont(font); break; } return(cellStyle); }
MemoryStream Export(bool isPrivate) { HSSFWorkbook workBook = new HSSFWorkbook(); HSSFSheet sheet = (HSSFSheet)workBook.CreateSheet(); #region 右击文件属性信息 { DocumentSummaryInformation dsi = PropertySetFactory.CreateDocumentSummaryInformation(); dsi.Company = "DuGu"; workBook.DocumentSummaryInformation = dsi; SummaryInformation si = PropertySetFactory.CreateSummaryInformation(); si.Author = "DuGu"; si.ApplicationName = "DuGu_NPOI_" + AppInfo.GetName(); si.LastAuthor = "DuGu"; si.Comments = "DuGu"; si.Title = "DuGu"; si.Subject = "DemarcateResults"; si.CreateDateTime = DateTime.Now; workBook.SummaryInformation = si; } #endregion HSSFCellStyle dateStyle = (HSSFCellStyle)workBook.CreateCellStyle(); HSSFDataFormat format = (HSSFDataFormat)workBook.CreateDataFormat(); dateStyle.DataFormat = format.GetFormat("yyyy-mm-dd"); int[] arrColWidth = { 20, 20, 20, 20, 20, 20, 20, 20 }; #region 表头样式 HSSFRow headerRow = (HSSFRow)sheet.CreateRow(0); headerRow.HeightInPoints = 25; headerRow.CreateCell(0).SetCellValue(strHeaderText); HSSFCellStyle headStyle = (HSSFCellStyle)workBook.CreateCellStyle(); headStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center; HSSFFont font = (HSSFFont)workBook.CreateFont(); font.FontHeightInPoints = 25; font.Boldweight = 400; headStyle.SetFont(font); headerRow.GetCell(0).CellStyle = headStyle; sheet.AddMergedRegion(new NPOI.SS.Util.Region(0, 0, 0, ColumnCount - 1)); #endregion headerRow = (HSSFRow)sheet.CreateRow(1); headStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center; font.FontHeightInPoints = 25; font.Boldweight = 400; headStyle.SetFont(font); ExportOptions exportOptions = new ExportOptions(); for (int i = 0; i < ColumnCount; ++i) { headerRow.CreateCell(i).SetCellValue(exportOptions.TableString[i]); headerRow.GetCell(i).CellStyle = headStyle; sheet.SetColumnWidth(i, (arrColWidth[i] + 1) * 256); } for (int i = 0; i < 12; ++i) { headerRow = (HSSFRow)sheet.CreateRow(i + 2); headStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center; font.FontHeightInPoints = 15; font.Boldweight = 400; headStyle.SetFont(font); headerRow.CreateCell(0).SetCellValue(i + 1); headerRow.GetCell(0).CellStyle = headStyle; if (demaMotionArray != null) { for (int j = 1; j < ColumnCount; ++j) { headerRow.CreateCell(j).SetCellValue(demaMotionArray[i].Para[j - 1].ToString()); headerRow.GetCell(j).CellStyle = headStyle; sheet.SetColumnWidth(j, (arrColWidth[j] + 1) * 256); } } if (demaMotionList != null) { for (int j = 1; j < ColumnCount; ++j) { headerRow.CreateCell(j).SetCellValue(demaMotionList[i].Para[j - 1].ToString()); headerRow.GetCell(j).CellStyle = headStyle; sheet.SetColumnWidth(j, (arrColWidth[j] + 1) * 256); } } } using (MemoryStream ms = new MemoryStream()) { workBook.Write(ms); ms.Flush(); ms.Position = 0; return(ms); } }
/// <summary> /// DataTable导出到Excel的MemoryStream /// </summary> /// <param name="dtSource">源DataTable</param> /// <param name="strHeaderText">表头文本</param> private static MemoryStream ExportDT(DataTable dtSource, string strHeaderText) { HSSFWorkbook workbook = new HSSFWorkbook(); HSSFSheet sheet = (HSSFSheet)workbook.CreateSheet(); #region 右击文件 属性信息 { DocumentSummaryInformation dsi = PropertySetFactory.CreateDocumentSummaryInformation(); dsi.Company = "NPOI"; workbook.DocumentSummaryInformation = dsi; SummaryInformation si = PropertySetFactory.CreateSummaryInformation(); si.Author = "文件作者信息"; //填加xls文件作者信息 si.ApplicationName = "创建程序信息"; //填加xls文件创建程序信息 si.LastAuthor = "最后保存者信息"; //填加xls文件最后保存者信息 si.Comments = "作者信息"; //填加xls文件作者信息 si.Title = "标题信息"; //填加xls文件标题信息 si.Subject = "主题信息"; //填加文件主题信息 si.CreateDateTime = DateTime.Now; workbook.SummaryInformation = si; } #endregion HSSFCellStyle dateStyle = (HSSFCellStyle)workbook.CreateCellStyle(); HSSFDataFormat format = (HSSFDataFormat)workbook.CreateDataFormat(); dateStyle.DataFormat = format.GetFormat("yyyy-mm-dd"); //取得列宽 int[] arrColWidth = new int[dtSource.Columns.Count]; foreach (DataColumn item in dtSource.Columns) { arrColWidth[item.Ordinal] = Encoding.GetEncoding(936).GetBytes(item.ColumnName.ToString()).Length; } for (int i = 0; i < dtSource.Rows.Count; i++) { for (int j = 0; j < dtSource.Columns.Count; j++) { if (dtSource.Rows[i][j] == null) { continue; } int intTemp = Encoding.GetEncoding(936).GetBytes(dtSource.Rows[i][j].ToString()).Length; if (intTemp > arrColWidth[j]) { arrColWidth[j] = intTemp; } } } int rowIndex = 0; foreach (DataRow row in dtSource.Rows) { #region 新建表,填充表头,填充列头,样式 if (rowIndex == 65535 || rowIndex == 0) { if (rowIndex != 0) { sheet = (HSSFSheet)workbook.CreateSheet(); } #region 表头及样式 { HSSFRow headerRow = (HSSFRow)sheet.CreateRow(0); headerRow.HeightInPoints = 25; headerRow.CreateCell(0).SetCellValue(strHeaderText); HSSFCellStyle headStyle = (HSSFCellStyle)workbook.CreateCellStyle(); headStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center; HSSFFont font = (HSSFFont)workbook.CreateFont(); font.FontHeightInPoints = 20; font.Boldweight = 700; headStyle.SetFont(font); headerRow.GetCell(0).CellStyle = headStyle; sheet.AddMergedRegion(new NPOI.SS.Util.Region(0, 0, 0, dtSource.Columns.Count - 1)); //headerRow.Dispose(); } #endregion #region 列头及样式 { HSSFRow headerRow = (HSSFRow)sheet.CreateRow(1); HSSFCellStyle headStyle = (HSSFCellStyle)workbook.CreateCellStyle(); headStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center; HSSFFont font = (HSSFFont)workbook.CreateFont(); font.FontHeightInPoints = 10; font.Boldweight = 700; headStyle.SetFont(font); foreach (DataColumn column in dtSource.Columns) { headerRow.CreateCell(column.Ordinal).SetCellValue(column.ColumnName); headerRow.GetCell(column.Ordinal).CellStyle = headStyle; if ((arrColWidth[column.Ordinal] + 1) * 256 >= 10000) { sheet.SetColumnWidth(column.Ordinal, 10000); } else { //设置列宽 sheet.SetColumnWidth(column.Ordinal, (arrColWidth[column.Ordinal] + 1) * 256); } } //headerRow.Dispose(); } #endregion rowIndex = 2; } #endregion #region 填充内容 HSSFRow dataRow = (HSSFRow)sheet.CreateRow(rowIndex); foreach (DataColumn column in dtSource.Columns) { HSSFCell newCell = (HSSFCell)dataRow.CreateCell(column.Ordinal); string drValue = row[column].ToString(); switch (column.DataType.ToString()) { case "System.String": //字符串类型 newCell.SetCellValue(drValue); break; case "System.DateTime": //日期类型 DateTime dateV; DateTime.TryParse(drValue, out dateV); newCell.SetCellValue(dateV); newCell.CellStyle = dateStyle; //格式化显示 break; case "System.Boolean": //布尔型 bool boolV = false; bool.TryParse(drValue, out boolV); newCell.SetCellValue(boolV); break; case "System.Int16": //整型 case "System.Int32": case "System.Int64": case "System.Byte": int intV = 0; int.TryParse(drValue, out intV); newCell.SetCellValue(intV); break; case "System.Decimal": //浮点型 case "System.Double": double doubV = 0; double.TryParse(drValue, out doubV); newCell.SetCellValue(doubV); break; case "System.DBNull": //空值处理 newCell.SetCellValue(""); break; default: newCell.SetCellValue(""); break; } } #endregion rowIndex++; } MemoryStream ms = new MemoryStream(); workbook.Write(ms); ms.Flush(); ms.Position = 0; return(ms); }
/// <summary> /// 按ListColumnsName列名序列,在Excel的指定Sheet中写入行 /// </summary> /// <param name="workbook">工作簿</param> /// <param name="sheet">要写入数据的Sheet</param> /// <param name="rowIndex">要写入的行索引</param> /// <param name="tableRow">要写入的DataRow</param> private static void CreateRow(HSSFWorkbook workbook, HSSFSheet sheet, int rowIndex, DataRow tableRow) { HSSFRow dataRow = sheet.CreateRow(rowIndex) as HSSFRow; for (int cellIndex = 0; cellIndex < ListColumnsName.Count; cellIndex++) { //列名称 string columnsName = ListColumnsName.GetKey(cellIndex).ToString(); HSSFCell newCell = dataRow.CreateCell(cellIndex) as HSSFCell; System.Type rowType = tableRow[columnsName].GetType(); string drValue = tableRow[columnsName].ToString().Trim(); switch (rowType.ToString()) { case "System.String": //字符串类型 newCell.SetCellValue(drValue); break; case "System.DateTime": //日期类型 DateTime dateV; DateTime.TryParse(drValue, out dateV); newCell.SetCellValue(dateV); //格式化显示 HSSFCellStyle dateStyle = workbook.CreateCellStyle() as HSSFCellStyle; HSSFDataFormat format = workbook.CreateDataFormat() as HSSFDataFormat; dateStyle.DataFormat = format.GetFormat("yyyy-MM-dd HH:mm:ss"); newCell.CellStyle = dateStyle; break; case "System.Boolean": //布尔型 bool boolV = false; bool.TryParse(drValue, out boolV); newCell.SetCellValue(boolV); break; case "System.Int16": //整型 case "System.Int32": case "System.Int64": case "System.Byte": int intV = 0; int.TryParse(drValue, out intV); newCell.SetCellValue(intV); break; case "System.Decimal": //浮点型 case "System.Double": double doubV = 0; double.TryParse(drValue, out doubV); newCell.SetCellValue(doubV); break; case "System.DBNull": //空值处理 newCell.SetCellValue(""); break; default: newCell.SetCellValue(""); break; } } }
public StringBuilder readXls(string file, EERRDataAndMethods eerr, XSSFWorkbook twb, bool applyRate, Double rate) { StringBuilder retVal = new StringBuilder(""); HSSFWorkbook wb; wb = new HSSFWorkbook(new FileStream(file, FileMode.Open)); ISheet sheet = wb.GetSheetAt(0); IRow r = sheet.GetRow(0); ICell c = r.GetCell(0); string company = c.StringCellValue; XSSFSheet sh = (XSSFSheet)twb.GetSheet("Estado resultado"); string acct = ""; string acctDesc = ""; for (int i = 1; i < sheet.LastRowNum; i++) { r = sheet.GetRow(i); if (r != null) { c = r.GetCell(0); if (c != null) { if (c.CellType == CellType.String && (c.StringCellValue).StartsWith(C_STR_IN_ACCOUNT)) { acct = c.StringCellValue; acct = acct.Substring(C_STR_IN_ACCOUNT.Length).Trim(); int pos = acct.IndexOf(' '); acctDesc = acct.Substring(pos + 1).Trim(); acct = acct.Substring(0, pos).Trim(); Console.WriteLine("Account: " + acct + " " + acctDesc); } else if (acct.Length > 0 && r.LastCellNum >= 16) { //"Estado" 1,"Empresa" 2,"Agrupacion" 3,"Marca," 4,"EERR" 5,"Detalle EERR" 6,"Cuenta" 7,"Desc Cuenta" 8, //"Mes" 9,"Fecha" 10,"# Compte" 11,"Tipo;Glosa" 12,"Area" 13,"C.Costo" 14,"Item" 15,"Desc Item" 16, "F.Efec" 17, //"Analisis" 18,"Refer" 19,"Fch Ref" 20,"Fch Vto" 21,"DEBE" 22,"HABER" 23,"SALDO" 24,"Sucursal" 25 string s = ""; XSSFRow row = (XSSFRow)sh.CreateRow(sh.LastRowNum + 1); XSSFCell cell = (XSSFCell)row.CreateCell(C_OUT_STAT - 1); cell.SetCellValue(C_DATA_STATUS); cell = (XSSFCell)row.CreateCell(C_OUT_CIA - 1); cell.SetCellValue(company); c = r.GetCell(C_IN_AREA - 1); cell = (XSSFCell)row.CreateCell(C_OUT_DESC_AREA - 1); cell.SetCellValue(eerr.getAgrupacion(c.ToString())); cell = (XSSFCell)row.CreateCell(C_OUT_BRAND - 1); cell.SetCellValue(eerr.getBrand(getCellValue(c))); cell = (XSSFCell)row.CreateCell(C_OUT_DET_EERR - 1); cell.SetCellValue(eerr.getLinea(acct)); cell = (XSSFCell)row.CreateCell(C_OUT_ACCT_NUM - 1); cell.SetCellValue(acct); cell = (XSSFCell)row.CreateCell(C_OUT_ACCT_DESC - 1); cell.SetCellValue(acctDesc); c = r.GetCell(C_IN_DATE - 1); cell = (XSSFCell)row.CreateCell(C_OUT_DATE - 1); cell.SetCellValue(getCellDateValue(c)); cell = (XSSFCell)row.CreateCell(C_OUT_MONTH - 1); cell.SetCellValue(getMonth(c)); c = r.GetCell(C_IN_COMPTE - 1); cell = (XSSFCell)row.CreateCell(C_OUT_COMPTE - 1); cell.SetCellValue(getCellValue(c)); c = r.GetCell(C_IN_TYPE - 1); cell = (XSSFCell)row.CreateCell(C_OUT_TYPE - 1); cell.SetCellValue(getCellValue(c)); c = r.GetCell(C_IN_COMMENT - 1); cell = (XSSFCell)row.CreateCell(C_OUT_COMMENT - 1); cell.SetCellValue(getCellValue(c)); c = r.GetCell(C_IN_AREA - 1); cell = (XSSFCell)row.CreateCell(C_OUT_AREA - 1); cell.SetCellValue(getCellValue(c)); c = r.GetCell(C_IN_COST_CENTER - 1); cell = (XSSFCell)row.CreateCell(C_OUT_COST_CENT - 1); cell.SetCellValue(getCellValue(c)); c = r.GetCell(C_IN_ITEM - 1); cell = (XSSFCell)row.CreateCell(C_OUT_ITEM - 1); cell.SetCellValue(getCellValue(c)); cell = (XSSFCell)row.CreateCell(C_OUT_ITEM_DESC - 1); cell.SetCellValue(eerr.getItem(getCellValue(c))); c = r.GetCell(C_IN_EFF_DATE - 1); cell = (XSSFCell)row.CreateCell(C_OUT_EFF_DATE - 1); cell.SetCellValue(getCellDateValue(c)); c = r.GetCell(C_IN_ANALISYS_DATE - 1); cell = (XSSFCell)row.CreateCell(C_OUT_ANALYSIS_DATE - 1); cell.SetCellValue(getCellValue(c)); c = r.GetCell(C_IN_REFERENCE - 1); cell = (XSSFCell)row.CreateCell(C_OUT_REF - 1); cell.SetCellValue(getCellValue(c)); c = r.GetCell(C_IN_REF_DATE - 1); cell = (XSSFCell)row.CreateCell(C_OUT_REF_DATE - 1); cell.SetCellValue(getCellDateValue(c)); c = r.GetCell(C_IN_EXP_DATE - 1); cell = (XSSFCell)row.CreateCell(C_OUT_EXP_DATE - 1); cell.SetCellValue(getCellDateValue(c)); XSSFCell deb = null; short doubleFormat = HSSFDataFormat.GetBuiltinFormat("#,##0"); //wb.CreateDataFormat().GetFormat("#,##0"); double v = 0; c = r.GetCell(C_IN_DEBIT - 1); cell = (XSSFCell)row.CreateCell(C_OUT_DEBIT - 1); deb = cell; if (c != null) { s = c.ToString(); if (!string.IsNullOrEmpty(s) && Double.TryParse(s, out v)) { //cell = (XSSFCell)row.CreateCell(C_OUT_DEBIT - 1); cell.SetCellValue((applyRate?rate:1) * v); cell.SetCellType(CellType.Numeric); cell.CellStyle.DataFormat = doubleFormat; } } XSSFCell cred = null; c = r.GetCell(C_IN_CREDIT - 1); cell = (XSSFCell)row.CreateCell(C_OUT_CREDIT - 1); cred = cell; if (c != null) { s = c.ToString(); if (!string.IsNullOrEmpty(s) && Double.TryParse(s, out v)) { //cell = (XSSFCell)row.CreateCell(C_OUT_CREDIT - 1); cell.SetCellValue((applyRate ? rate : 1) * v); cell.SetCellType(CellType.Numeric); cell.CellStyle.DataFormat = doubleFormat; } } c = r.GetCell(C_IN_BALANCE - 1); if (c != null) { s = c.ToString(); if (!string.IsNullOrEmpty(s) && Double.TryParse(s, out v)) { cell = (XSSFCell)row.CreateCell(C_OUT_BALANCE - 1); cell.SetCellValue((applyRate ? rate : 1) * v); cell.SetCellType(CellType.Formula); cell.SetCellFormula(String.Format("{0}{1}-{2}{3}", C_COL_DEBIT, cell.Row.RowNum + 1, C_COL_CREDIT, cell.Row.RowNum + 1)); cell.CellStyle.DataFormat = doubleFormat; } } c = r.GetCell(C_IN_BRANCH - 1); if (c != null) { s = c.ToString(); if (!string.IsNullOrEmpty(s)) { cell = (XSSFCell)row.CreateCell(C_OUT_BRANCH - 1); cell.SetCellValue(eerr.getSucursal(s)); } } } } } } return(retVal); }
private HSSFCellStyle PhaseCellStyle(CellStyle paramCellStyle) { HSSFCellStyle style = null; //try //{ style = (HSSFCellStyle)_workbook.CreateCellStyle(); HSSFFont font = (HSSFFont)_workbook.CreateFont(); font.FontName = paramCellStyle.FontName.ToString(); font.FontHeightInPoints = paramCellStyle.FontHeightInPoints; if (paramCellStyle.FontBoldFlag) { font.Boldweight = 700; } style.SetFont(font); //设置水平位置 switch (paramCellStyle.HorizontalAlignment.ToString()) { case "Center": style.Alignment = HorizontalAlignment.Center; break; case "Left": style.Alignment = HorizontalAlignment.Left; break; case "Right": style.Alignment = HorizontalAlignment.Right; break; default: style.Alignment = HorizontalAlignment.Center; break; } //设置垂直位置 switch (paramCellStyle.VerticalAlignment.ToString()) { case "Center": style.VerticalAlignment = VerticalAlignment.Center; break; case "Top": style.VerticalAlignment = VerticalAlignment.Top; break; case "Botton": style.VerticalAlignment = VerticalAlignment.Bottom; break; default: style.VerticalAlignment = VerticalAlignment.Center; break; } //设置表格线样式宽度 switch (paramCellStyle.BorderStyle.ToString()) { case "Thin": style.BorderBottom = BorderStyle.Thin; style.BorderLeft = BorderStyle.Thin; style.BorderRight = BorderStyle.Thin; style.BorderTop = BorderStyle.Thin; break; default: break; } //设置表格线颜色 switch (paramCellStyle.BorderStyle.ToString()) { case "Black": style.BottomBorderColor = NPOI.HSSF.Util.HSSFColor.Black.Index; style.TopBorderColor = NPOI.HSSF.Util.HSSFColor.Black.Index; style.LeftBorderColor = NPOI.HSSF.Util.HSSFColor.Black.Index; style.RightBorderColor = NPOI.HSSF.Util.HSSFColor.Black.Index; break; default: break; } //设置单元格是否换行 #region 设置为文本 style.WrapText = paramCellStyle.WrapText; HSSFDataFormat format = (HSSFDataFormat)_workbook.GetCreationHelper().CreateDataFormat(); short index = format.GetFormat("@"); style.DataFormat = index; #endregion //} //catch(Exception ex) //{ // string ss = ex.Message; // return null; //} return(style); }
//Datatable导出Excel public static void GridToExcelByNPOI(DataTable dt, string strExcelFileName, string title) { HSSFWorkbook workbook = new HSSFWorkbook(); try { ISheet sheet = workbook.CreateSheet("Sheet1"); ICellStyle HeadercellStyle = workbook.CreateCellStyle(); HeadercellStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin; HeadercellStyle.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin; HeadercellStyle.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin; HeadercellStyle.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin; HeadercellStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center; //字体 NPOI.SS.UserModel.IFont headerfont = workbook.CreateFont(); headerfont.Boldweight = (short)FontBoldWeight.Bold; HeadercellStyle.SetFont(headerfont); //标题头 int icolIndex = 0; CellRangeAddress region = new CellRangeAddress(0, 0, 0, dt.Columns.Count > 0 ? dt.Columns.Count - 1 : 0); sheet.AddMergedRegion(region); ((HSSFSheet)sheet).SetEnclosedBorderOfRegion(region, BorderStyle.Thin, NPOI.HSSF.Util.HSSFColor.Black.Index); IRow headerRow = sheet.CreateRow(0); headerRow.HeightInPoints = 20; NPOI.SS.UserModel.ICell celltitle = headerRow.CreateCell(0); celltitle.SetCellValue(title); celltitle.CellStyle = HeadercellStyle; //用column name 作为列名 IRow headerRow1 = sheet.CreateRow(1); foreach (DataColumn item in dt.Columns) { NPOI.SS.UserModel.ICell cell1 = headerRow1.CreateCell(icolIndex); cell1.SetCellValue(item.ColumnName); cell1.CellStyle = HeadercellStyle; icolIndex++; } ICellStyle cellStyle = workbook.CreateCellStyle(); //为避免日期格式被Excel自动替换,所以设定 format 为 『@』 表示一率当成text來看 cellStyle.DataFormat = HSSFDataFormat.GetBuiltinFormat("@"); cellStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin; cellStyle.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin; cellStyle.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin; cellStyle.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin; NPOI.SS.UserModel.IFont cellfont = workbook.CreateFont(); cellfont.Boldweight = (short)FontBoldWeight.Normal; cellStyle.SetFont(cellfont); //建立内容行 int iRowIndex = 2; int iCellIndex = 0; foreach (DataRow Rowitem in dt.Rows) { IRow DataRow = sheet.CreateRow(iRowIndex); foreach (DataColumn Colitem in dt.Columns) { NPOI.SS.UserModel.ICell cell = DataRow.CreateCell(iCellIndex); cell.SetCellValue(Rowitem[Colitem].ToString()); cell.CellStyle = cellStyle; iCellIndex++; } iCellIndex = 0; iRowIndex++; } //自适应列宽度 for (int i = 0; i < icolIndex; i++) { sheet.AutoSizeColumn(i); } //写Excel FileStream file = new FileStream(strExcelFileName, FileMode.OpenOrCreate); workbook.Write(file); file.Flush(); file.Close(); } catch (Exception ex) { string s = ex.Message; } finally { workbook = null; } }