public void ExportExcelDouble(DataTable dt) { try { //创建一个工作簿 IWorkbook workbook = new HSSFWorkbook(); //创建一个 sheet 表 ISheet sheet = workbook.CreateSheet(dt.TableName); //创建第一行 IRow rowFirst = sheet.CreateRow(0); //创建第二行 IRow rowSecond = sheet.CreateRow(1); //创建一个单元格 ICell cell = null; //创建单元格样式 ICellStyle cellStyle = workbook.CreateCellStyle(); cellStyle.VerticalAlignment = VerticalAlignment.Justify; //垂直对齐(默认应该为center,如果center无效则用justify) cellStyle.Alignment = HorizontalAlignment.Center; //水平对齐 //创建格式 IDataFormat dataFormat = workbook.CreateDataFormat(); //设置为文本格式,也可以为 text,即 dataFormat.GetFormat("text"); cellStyle.DataFormat = dataFormat.GetFormat("@"); sheet.AddMergedRegion(new CellRangeAddress(0, 0, 0, 2)); sheet.AddMergedRegion(new CellRangeAddress(0, 0, 3, 5)); sheet.AddMergedRegion(new CellRangeAddress(0, 0, 6, 8)); sheet.AddMergedRegion(new CellRangeAddress(0, 0, 10, 12)); rowFirst.CreateCell(0).SetCellValue("考试内容"); rowFirst.Cells[0].CellStyle = cellStyle; rowFirst.CreateCell(3).SetCellValue("考试要求"); rowFirst.Cells[1].CellStyle = cellStyle; rowFirst.CreateCell(6).SetCellValue("难度值"); rowFirst.Cells[2].CellStyle = cellStyle; rowFirst.CreateCell(9).SetCellValue("题型"); rowFirst.Cells[3].CellStyle = cellStyle; rowFirst.CreateCell(10).SetCellValue("题目来源"); rowFirst.Cells[4].CellStyle = cellStyle; //设置列名 foreach (DataColumn col in dt.Columns) { //创建单元格并设置单元格内容 rowSecond.CreateCell(col.Ordinal).SetCellValue(col.Caption); //设置单元格格式 rowSecond.Cells[col.Ordinal].CellStyle = cellStyle; } //写入数据 for (int i = 0; i < dt.Rows.Count; i++) { //跳过第一行,第一行为列名 IRow row = sheet.CreateRow(i + 2); for (int j = 0; j < dt.Columns.Count; j++) { cell = row.CreateCell(j); cell.SetCellValue(dt.Rows[i][j].ToString()); cell.CellStyle = cellStyle; } } //设置导出文件路径 string path = HttpContext.Current.Server.MapPath("/ImportExcel/"); //设置新建文件路径及名称 string savePath = path + DateTime.Now.ToString("yyyyMMddHHmmss") + ".xls"; //创建文件 FileStream file = new FileStream(savePath, FileMode.CreateNew, FileAccess.Write); //创建一个 IO 流 MemoryStream ms = new MemoryStream(); //写入到流 workbook.Write(ms); //转换为字节数组 byte[] bytes = ms.ToArray(); file.Write(bytes, 0, bytes.Length); file.Flush(); //还可以调用下面的方法,把流输出到浏览器下载 OutputClient(bytes); //释放资源 bytes = null; ms.Close(); ms.Dispose(); file.Close(); file.Dispose(); workbook.Close(); sheet = null; workbook = null; } catch (Exception ex) { } }
/// <summary> /// DataTable导出到Excel的MemoryStream /// </summary> /// <param name="dtSource">源DataTable</param> /// <param name="strHeaderText">表头文本</param> /// <param name="columnNames">列名</param> public static MemoryStream Export(DataTable dtSource, string strHeaderText, string[] columnNames) { if (columnNames != null && columnNames.Length != dtSource.Columns.Count) { throw new ArgumentException("参数不正确:columnNames,数组元素的个数需要和数据源列的数量相同!"); } HSSFWorkbook workbook = new HSSFWorkbook(); ISheet sheet = workbook.CreateSheet(); #region 右击文件 属性信息 { DocumentSummaryInformation dsi = PropertySetFactory.CreateDocumentSummaryInformation(); dsi.Company = "上海驰亚防伪科技有限公司"; workbook.DocumentSummaryInformation = dsi; SummaryInformation si = PropertySetFactory.CreateSummaryInformation(); si.Author = "技术部"; si.Title = strHeaderText; si.CreateDateTime = DateTime.Now; workbook.SummaryInformation = si; } #endregion ICellStyle dateStyle = workbook.CreateCellStyle(); IDataFormat format = 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++) { 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 = workbook.CreateSheet(); } #region 表头及样式 { IRow headerRow = sheet.CreateRow(0); headerRow.HeightInPoints = 25; headerRow.CreateCell(0).SetCellValue(strHeaderText); ICellStyle headStyle = workbook.CreateCellStyle(); headStyle.Alignment = HorizontalAlignment.Center; IFont font = workbook.CreateFont(); 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)); } #endregion #region 列头及样式 { IRow headerRow = sheet.CreateRow(1); ICellStyle headStyle = workbook.CreateCellStyle(); headStyle.Alignment = HorizontalAlignment.Center; IFont font = workbook.CreateFont(); font.FontHeightInPoints = 10; font.Boldweight = 700; headStyle.SetFont(font); if (columnNames == null) { 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) >= 40000) { sheet.SetColumnWidth(column.Ordinal, (arrColWidth[column.Ordinal] + 1) * 40000); } else { sheet.SetColumnWidth(column.Ordinal, (arrColWidth[column.Ordinal] + 1) * 256); } } } else { foreach (DataColumn column in dtSource.Columns) { headerRow.CreateCell(column.Ordinal).SetCellValue(columnNames[column.Ordinal]); headerRow.GetCell(column.Ordinal).CellStyle = headStyle; //设置列宽 if (((arrColWidth[column.Ordinal] + 1) * 256) >= 40000) { sheet.SetColumnWidth(column.Ordinal, 40000); } else { sheet.SetColumnWidth(column.Ordinal, (arrColWidth[column.Ordinal] + 1) * 256); } } } } #endregion rowIndex = 2; } #endregion #region 填充内容 IRow dataRow = sheet.CreateRow(rowIndex); foreach (DataColumn column in dtSource.Columns) { ICell newCell = 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++; } using (MemoryStream ms = new MemoryStream()) { workbook.Write(ms); ms.Flush(); ms.Position = 0; //sheet.Dispose(); //workbook.Dispose();//一般只用写这一个就OK了,他会遍历并释放所有资源,但当前版本有问题所以只释放sheet return(ms); } }
private void generate_my_data() { ISheet sheet0 = hssfworkbook.CreateSheet("Ruch spraw"); DataView view = (DataView)dane_do_tabeli_1.Select(DataSourceSelectArguments.Empty); DataTable table = view.ToTable(); IRow row0 = sheet0.CreateRow(0); table.TableName = "Załatwienia"; table.Columns.Remove("id_"); row0.CreateCell(0).SetCellValue("Opis"); row0.CreateCell(1).SetCellValue("Sprawy według repetoriów i wykazów"); var crs = new NPOI.SS.Util.CellRangeAddress(0, 1, 0, 0); sheet0.AddMergedRegion(crs); crs = new NPOI.SS.Util.CellRangeAddress(0, 0, 1, 8); sheet0.AddMergedRegion(crs); row0 = sheet0.CreateRow(1); row0.CreateCell(1).SetCellValue("C"); row0.CreateCell(2).SetCellValue("CG-G"); row0.CreateCell(3).SetCellValue("Ns"); row0.CreateCell(4).SetCellValue("Nc"); row0.CreateCell(5).SetCellValue("Co"); row0.CreateCell(6).SetCellValue("Cps"); row0.CreateCell(7).SetCellValue("WSC"); row0.CreateCell(8).SetCellValue("Łącznie"); int rol = 2; foreach (DataRow rowik in table.Rows) { row0 = sheet0.CreateRow(rol); for (int i = 0; i < 9; i++) { try { int ji = int.Parse(rowik[i].ToString().Trim()); ICellStyle cellStyle = hssfworkbook.CreateCellStyle(); cellStyle.DataFormat = HSSFDataFormat.GetBuiltinFormat("0"); row0.CreateCell(i).SetCellValue(ji); row0.Cells[i].CellStyle = cellStyle; } catch (Exception) { row0.CreateCell(i).SetCellValue(rowik[i].ToString().Trim()); } } rol++; }// end foreach // druga tabela view = (DataView)statystyki.Select(DataSourceSelectArguments.Empty); table = view.ToTable(); table = view.ToTable(); table.TableName = "Załatwienia"; table.Columns.Remove("ident"); table.Columns.Remove("sesja"); table.Columns.Remove("id_sedziego"); table.Columns.Remove("id_tabeli"); table.Columns.Remove("id_dzialu"); //table.Columns.Remove("d_13"); table.Columns.Remove("d_14"); table.Columns.Remove("d_15"); table.Columns.Remove("d_16"); table.Columns.Remove("d_17"); table.Columns.Remove("d_18"); table.Columns.Remove("d_19"); table.Columns.Remove("d_20"); table.Columns.Remove("d_21"); table.Columns.Remove("d_22"); // //robienie int ro = 2; //----------------- IDataFormat format = hssfworkbook.CreateDataFormat(); //----------------- ISheet sheet1 = hssfworkbook.CreateSheet("Załatwienia"); IRow row2 = sheet1.CreateRow(0); row2.CreateCell(0).SetCellValue("L.p."); crs = new NPOI.SS.Util.CellRangeAddress(0, 1, 0, 0); sheet1.AddMergedRegion(crs); row2.CreateCell(1).SetCellValue("Nazwisko"); crs = new NPOI.SS.Util.CellRangeAddress(0, 1, 1, 1); sheet1.AddMergedRegion(crs); row2.CreateCell(2).SetCellValue("Imię"); crs = new NPOI.SS.Util.CellRangeAddress(0, 1, 2, 2); sheet1.AddMergedRegion(crs); row2.CreateCell(3).SetCellValue("Funkcja"); crs = new NPOI.SS.Util.CellRangeAddress(0, 1, 3, 3); sheet1.AddMergedRegion(crs); row2.CreateCell(4).SetCellValue("Stanowisko"); crs = new NPOI.SS.Util.CellRangeAddress(0, 1, 4, 4); sheet1.AddMergedRegion(crs); row2.CreateCell(5).SetCellValue("Liczba sesji"); crs = new NPOI.SS.Util.CellRangeAddress(0, 0, 5, 6); sheet1.AddMergedRegion(crs); row2.CreateCell(7).SetCellValue("Załatwienia"); crs = new NPOI.SS.Util.CellRangeAddress(0, 0, 7, 14); sheet1.AddMergedRegion(crs); row2.CreateCell(15).SetCellValue("Il. sporządzonych uzasadnień"); crs = new NPOI.SS.Util.CellRangeAddress(0, 1, 15, 15); sheet1.AddMergedRegion(crs); row2.CreateCell(16).SetCellValue("Nieobecności"); crs = new NPOI.SS.Util.CellRangeAddress(0, 0, 16, 17); sheet1.AddMergedRegion(crs); row2 = sheet1.CreateRow(1); row2.CreateCell(5).SetCellValue("rozprawy"); row2.CreateCell(6).SetCellValue("posiedzenia"); row2.CreateCell(7).SetCellValue("C"); row2.CreateCell(8).SetCellValue("C-GC"); row2.CreateCell(9).SetCellValue("Ns"); row2.CreateCell(10).SetCellValue("Nc"); row2.CreateCell(11).SetCellValue("Co"); row2.CreateCell(12).SetCellValue("Cps"); row2.CreateCell(13).SetCellValue("WSC"); row2.CreateCell(14).SetCellValue("Razem"); row2.CreateCell(16).SetCellValue("Urlopy"); row2.CreateCell(17).SetCellValue("Zwolnienia"); foreach (DataRow rowik in table.Rows) { row2 = sheet1.CreateRow(ro); for (int i = 0; i < rowik.ItemArray.Length; i++) { try { int ji = int.Parse(rowik[i].ToString().Trim()); ICellStyle cellStyle = hssfworkbook.CreateCellStyle(); cellStyle.DataFormat = HSSFDataFormat.GetBuiltinFormat("0"); row2.CreateCell(i).SetCellValue(ji); row2.Cells[i].CellStyle = cellStyle; } catch (Exception) { row2.CreateCell(i).SetCellValue(rowik[i].ToString().Trim()); } } ro++; }// end foreach // trzeci sheet view = (DataView)tabela_3.Select(DataSourceSelectArguments.Empty); table = view.ToTable(); table.Columns.Remove("ident"); table.Columns.Remove("sesja"); table.Columns.Remove("id_sedziego"); table.Columns.Remove("id_tabeli"); table.Columns.Remove("id_dzialu"); table.Columns.Remove("d_06"); table.Columns.Remove("d_11"); table.Columns.Remove("d_12"); table.Columns.Remove("d_13"); table.Columns.Remove("d_14"); table.Columns.Remove("d_15"); table.Columns.Remove("d_16"); table.Columns.Remove("d_17"); table.Columns.Remove("d_18"); table.Columns.Remove("d_19"); table.Columns.Remove("d_20"); table.Columns.Remove("d_21"); table.Columns.Remove("d_22"); sheet1.AutoSizeColumn(0, true); sheet1.AutoSizeColumn(1, true); ISheet sheet2 = hssfworkbook.CreateSheet("Wyznaczenia"); row2 = sheet2.CreateRow(0); row2.CreateCell(0).SetCellValue("L.p."); row2.CreateCell(1).SetCellValue("Nazwisko"); row2.CreateCell(2).SetCellValue("Imię"); row2.CreateCell(3).SetCellValue("Funkcja"); row2.CreateCell(4).SetCellValue("Stanowisko"); crs = new NPOI.SS.Util.CellRangeAddress(0, 1, 0, 0); sheet2.AddMergedRegion(crs); crs = new NPOI.SS.Util.CellRangeAddress(0, 1, 1, 1); sheet2.AddMergedRegion(crs); crs = new NPOI.SS.Util.CellRangeAddress(0, 1, 2, 2); sheet2.AddMergedRegion(crs); crs = new NPOI.SS.Util.CellRangeAddress(0, 1, 3, 3); sheet2.AddMergedRegion(crs); crs = new NPOI.SS.Util.CellRangeAddress(0, 1, 4, 4); sheet2.AddMergedRegion(crs); row2.CreateCell(5).SetCellValue("Wyznaczenia"); crs = new NPOI.SS.Util.CellRangeAddress(0, 0, 5, 12); sheet2.AddMergedRegion(crs); row2 = sheet2.CreateRow(1); row2.CreateCell(5).SetCellValue("C"); row2.CreateCell(6).SetCellValue("C-GC"); row2.CreateCell(7).SetCellValue("Ns"); row2.CreateCell(8).SetCellValue("Nc"); row2.CreateCell(9).SetCellValue("Co"); row2.CreateCell(10).SetCellValue("Cps"); row2.CreateCell(11).SetCellValue("WSC"); row2.CreateCell(12).SetCellValue("Razem"); row2.CreateCell(13).SetCellValue("Odroczenia liczba spraw odroczonych"); ro = 2; foreach (DataRow rowik in table.Rows) { row2 = sheet2.CreateRow(ro); for (int i = 0; i < rowik.ItemArray.Length; i++) { try { int ji = int.Parse(rowik[i].ToString().Trim()); ICellStyle cellStyle = hssfworkbook.CreateCellStyle(); cellStyle.DataFormat = HSSFDataFormat.GetBuiltinFormat("0"); row2.CreateCell(i).SetCellValue(ji); row2.Cells[i].CellStyle = cellStyle; } catch (Exception) { row2.CreateCell(i).SetCellValue(rowik[i].ToString().Trim()); } } ro++; }// end foreach // czwarty sheet view = (DataView)tabela_4.Select(DataSourceSelectArguments.Empty); table = view.ToTable(); table.Columns.Remove("ident"); table.Columns.Remove("sesja"); table.Columns.Remove("id_sedziego"); // table.Columns.Remove("id_tabeli"); // table.Columns.Remove("id_dzialu"); table.Columns.Remove("d_09"); table.Columns.Remove("d_06"); table.Columns.Remove("d_11"); table.Columns.Remove("d_12"); table.Columns.Remove("d_13"); table.Columns.Remove("d_14"); table.Columns.Remove("d_15"); table.Columns.Remove("d_16"); table.Columns.Remove("d_17"); table.Columns.Remove("d_18"); table.Columns.Remove("d_19"); table.Columns.Remove("d_20"); table.Columns.Remove("d_21"); table.Columns.Remove("d_22"); ISheet sheet3 = hssfworkbook.CreateSheet("Stan referatów sędziów"); row2 = sheet3.CreateRow(0); row2.CreateCell(0).SetCellValue("L.p."); row2.CreateCell(1).SetCellValue("Nazwisko"); row2.CreateCell(2).SetCellValue("Imię"); row2.CreateCell(3).SetCellValue("Funkcja"); row2.CreateCell(4).SetCellValue("Stanowisko"); crs = new NPOI.SS.Util.CellRangeAddress(0, 1, 0, 0); sheet3.AddMergedRegion(crs); crs = new NPOI.SS.Util.CellRangeAddress(0, 1, 1, 1); sheet3.AddMergedRegion(crs); crs = new NPOI.SS.Util.CellRangeAddress(0, 1, 2, 2); sheet3.AddMergedRegion(crs); crs = new NPOI.SS.Util.CellRangeAddress(0, 1, 3, 3); sheet3.AddMergedRegion(crs); crs = new NPOI.SS.Util.CellRangeAddress(0, 1, 4, 4); sheet3.AddMergedRegion(crs); row2.CreateCell(5).SetCellValue("Pozostało w referatach spraw kategorii"); crs = new NPOI.SS.Util.CellRangeAddress(0, 0, 5, 12); sheet3.AddMergedRegion(crs); row2 = sheet3.CreateRow(1); row2.CreateCell(5).SetCellValue("C"); row2.CreateCell(6).SetCellValue("C-GC"); row2.CreateCell(7).SetCellValue("Ns"); row2.CreateCell(8).SetCellValue("Nc"); row2.CreateCell(9).SetCellValue("Co"); row2.CreateCell(10).SetCellValue("Cps"); row2.CreateCell(11).SetCellValue("WSC"); row2.CreateCell(12).SetCellValue("Razem"); // row2.CreateCell(12).SetCellValue("Odroczenia liczba spraw odroczonych"); ro = 2; foreach (DataRow rowik in table.Rows) { row2 = sheet3.CreateRow(ro); for (int i = 0; i < rowik.ItemArray.Length; i++) { try { int ji = int.Parse(rowik[i].ToString().Trim()); ICellStyle cellStyle = hssfworkbook.CreateCellStyle(); cellStyle.DataFormat = HSSFDataFormat.GetBuiltinFormat("0"); row2.CreateCell(i).SetCellValue(ji); row2.Cells[i].CellStyle = cellStyle; } catch (Exception) { row2.CreateCell(i).SetCellValue(rowik[i].ToString().Trim()); } } ro++; }// end foreach }
public void Report(string FileName) { DivideByFactory(); int pos = 0; //Export to excel HSSFWorkbook hssfworkbook = new HSSFWorkbook(); ////create a entry of DocumentSummaryInformation DocumentSummaryInformation dsi = PropertySetFactory.CreateDocumentSummaryInformation(); dsi.Company = "NPOI Team"; hssfworkbook.DocumentSummaryInformation = dsi; ////create a entry of SummaryInformation SummaryInformation si = PropertySetFactory.CreateSummaryInformation(); si.Subject = "NPOI SDK Example"; hssfworkbook.SummaryInformation = si; #region Create fonts and styles HSSFFont HeaderF1 = hssfworkbook.CreateFont(); HeaderF1.FontHeightInPoints = 11; HeaderF1.Boldweight = 11 * 256; HeaderF1.FontName = "Calibri"; HSSFFont HeaderF2 = hssfworkbook.CreateFont(); HeaderF2.FontHeightInPoints = 10; HeaderF2.Boldweight = 10 * 256; HeaderF2.FontName = "Calibri"; HSSFFont HeaderF3 = hssfworkbook.CreateFont(); HeaderF3.FontHeightInPoints = 9; HeaderF3.Boldweight = 9 * 256; HeaderF3.FontName = "Calibri"; HSSFFont SimpleF = hssfworkbook.CreateFont(); SimpleF.FontHeightInPoints = 10; SimpleF.FontName = "Calibri"; HSSFCellStyle SimpleCS = hssfworkbook.CreateCellStyle(); SimpleCS.BorderBottom = HSSFCellStyle.BORDER_THIN; SimpleCS.BottomBorderColor = HSSFColor.BLACK.index; SimpleCS.BorderLeft = HSSFCellStyle.BORDER_THIN; SimpleCS.LeftBorderColor = HSSFColor.BLACK.index; SimpleCS.BorderRight = HSSFCellStyle.BORDER_THIN; SimpleCS.RightBorderColor = HSSFColor.BLACK.index; SimpleCS.BorderTop = HSSFCellStyle.BORDER_THIN; SimpleCS.TopBorderColor = HSSFColor.BLACK.index; SimpleCS.SetFont(SimpleF); HSSFCellStyle CountDecCS = hssfworkbook.CreateCellStyle(); CountDecCS.DataFormat = hssfworkbook.CreateDataFormat().GetFormat("### ### ##0.000"); CountDecCS.BorderBottom = HSSFCellStyle.BORDER_THIN; CountDecCS.BottomBorderColor = HSSFColor.BLACK.index; CountDecCS.BorderLeft = HSSFCellStyle.BORDER_THIN; CountDecCS.LeftBorderColor = HSSFColor.BLACK.index; CountDecCS.BorderRight = HSSFCellStyle.BORDER_THIN; CountDecCS.RightBorderColor = HSSFColor.BLACK.index; CountDecCS.BorderTop = HSSFCellStyle.BORDER_THIN; CountDecCS.TopBorderColor = HSSFColor.BLACK.index; CountDecCS.SetFont(SimpleF); HSSFCellStyle CountCS = hssfworkbook.CreateCellStyle(); CountCS.DataFormat = hssfworkbook.CreateDataFormat().GetFormat("### ### ##0"); CountCS.BorderBottom = HSSFCellStyle.BORDER_THIN; CountCS.BottomBorderColor = HSSFColor.BLACK.index; CountCS.BorderLeft = HSSFCellStyle.BORDER_THIN; CountCS.LeftBorderColor = HSSFColor.BLACK.index; CountCS.BorderRight = HSSFCellStyle.BORDER_THIN; CountCS.RightBorderColor = HSSFColor.BLACK.index; CountCS.BorderTop = HSSFCellStyle.BORDER_THIN; CountCS.TopBorderColor = HSSFColor.BLACK.index; CountCS.SetFont(SimpleF); HSSFCellStyle SimpleHeaderCS = hssfworkbook.CreateCellStyle(); SimpleHeaderCS.BorderBottom = HSSFCellStyle.BORDER_MEDIUM; SimpleHeaderCS.BottomBorderColor = HSSFColor.BLACK.index; SimpleHeaderCS.BorderLeft = HSSFCellStyle.BORDER_MEDIUM; SimpleHeaderCS.LeftBorderColor = HSSFColor.BLACK.index; SimpleHeaderCS.BorderRight = HSSFCellStyle.BORDER_MEDIUM; SimpleHeaderCS.RightBorderColor = HSSFColor.BLACK.index; SimpleHeaderCS.BorderTop = HSSFCellStyle.BORDER_MEDIUM; SimpleHeaderCS.TopBorderColor = HSSFColor.BLACK.index; //SimpleHeaderCS.WrapText = true; SimpleHeaderCS.SetFont(HeaderF3); #endregion Create fonts and styles HSSFCell Cell1; if (FrontsProfilDT.Rows.Count > 0) { HSSFSheet sheet1 = hssfworkbook.CreateSheet("Фасады, Профиль"); sheet1.PrintSetup.PaperSize = (short)PaperSizeType.A4; sheet1.SetMargin(HSSFSheet.LeftMargin, (double).12); sheet1.SetMargin(HSSFSheet.RightMargin, (double).07); sheet1.SetMargin(HSSFSheet.TopMargin, (double).20); sheet1.SetMargin(HSSFSheet.BottomMargin, (double).20); pos += 2; int ColIndex = 0; Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++); Cell1.SetCellValue("Клиент"); Cell1.CellStyle = SimpleHeaderCS; Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++); Cell1.SetCellValue("Клиент ЗОВ"); Cell1.CellStyle = SimpleHeaderCS; Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++); Cell1.SetCellValue("№ заказа"); Cell1.CellStyle = SimpleHeaderCS; Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++); Cell1.SetCellValue("Дата упаковки"); Cell1.CellStyle = SimpleHeaderCS; Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++); Cell1.SetCellValue("Бухг.наим."); Cell1.CellStyle = SimpleHeaderCS; Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++); Cell1.SetCellValue("Инв.номер"); Cell1.CellStyle = SimpleHeaderCS; Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++); Cell1.SetCellValue("Фасад"); Cell1.CellStyle = SimpleHeaderCS; Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++); Cell1.SetCellValue("Цвет"); Cell1.CellStyle = SimpleHeaderCS; Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++); Cell1.SetCellValue("Вставка"); Cell1.CellStyle = SimpleHeaderCS; Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++); Cell1.SetCellValue("Цвет наполнителя"); Cell1.CellStyle = SimpleHeaderCS; Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++); Cell1.SetCellValue("Вставка-2"); Cell1.CellStyle = SimpleHeaderCS; Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++); Cell1.SetCellValue("Цвет наполнителя-2"); Cell1.CellStyle = SimpleHeaderCS; Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++); Cell1.SetCellValue("Кол-во"); Cell1.CellStyle = SimpleHeaderCS; Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++); Cell1.SetCellValue("Квадратура"); Cell1.CellStyle = SimpleHeaderCS; Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++); Cell1.SetCellValue("Ед.изм."); Cell1.CellStyle = SimpleHeaderCS; Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++); Cell1.SetCellValue("ID упаковки"); Cell1.CellStyle = SimpleHeaderCS; Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++); Cell1.SetCellValue("№ упаковки"); Cell1.CellStyle = SimpleHeaderCS; pos++; for (int i = 0; i < FrontsProfilDT.Rows.Count; i++) { ColIndex = 0; Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++); Cell1.SetCellValue(FrontsProfilDT.Rows[i]["ClientName"].ToString()); Cell1.CellStyle = SimpleCS; Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++); Cell1.SetCellValue(FrontsProfilDT.Rows[i]["ZOVClientName"].ToString()); Cell1.CellStyle = SimpleCS; Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++); Cell1.SetCellValue(Convert.ToInt32(FrontsProfilDT.Rows[i]["OrderNumber"])); Cell1.CellStyle = CountCS; Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++); Cell1.SetCellValue(FrontsProfilDT.Rows[i]["PackingDateTime"].ToString()); Cell1.CellStyle = SimpleCS; Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++); Cell1.SetCellValue(FrontsProfilDT.Rows[i]["AccountingName"].ToString()); Cell1.CellStyle = SimpleCS; Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++); Cell1.SetCellValue(FrontsProfilDT.Rows[i]["InvNumber"].ToString()); Cell1.CellStyle = SimpleCS; Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++); Cell1.SetCellValue(FrontsProfilDT.Rows[i]["TechStoreName"].ToString()); Cell1.CellStyle = SimpleCS; Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++); Cell1.SetCellValue(FrontsProfilDT.Rows[i]["Expr35"].ToString()); Cell1.CellStyle = SimpleCS; Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++); Cell1.SetCellValue(FrontsProfilDT.Rows[i]["Expr36"].ToString()); Cell1.CellStyle = SimpleCS; Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++); Cell1.SetCellValue(FrontsProfilDT.Rows[i]["Expr37"].ToString()); Cell1.CellStyle = SimpleCS; Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++); Cell1.SetCellValue(FrontsProfilDT.Rows[i]["Expr38"].ToString()); Cell1.CellStyle = SimpleCS; Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++); Cell1.SetCellValue(FrontsProfilDT.Rows[i]["Expr1"].ToString()); Cell1.CellStyle = SimpleCS; Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++); Cell1.SetCellValue(Convert.ToInt32(FrontsProfilDT.Rows[i]["Count"])); Cell1.CellStyle = CountCS; Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++); Cell1.SetCellValue(Convert.ToDouble(FrontsProfilDT.Rows[i]["Square"])); Cell1.CellStyle = CountDecCS; Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++); Cell1.SetCellValue(FrontsProfilDT.Rows[i]["Measure"].ToString()); Cell1.CellStyle = SimpleCS; Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++); Cell1.SetCellValue(Convert.ToInt32(FrontsProfilDT.Rows[i]["PackageID"])); Cell1.CellStyle = CountCS; Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++); Cell1.SetCellValue(Convert.ToInt32(FrontsProfilDT.Rows[i]["PackNumber"])); Cell1.CellStyle = CountCS; pos++; } } pos = 0; if (FrontsTPSDT.Rows.Count > 0) { HSSFSheet sheet1 = hssfworkbook.CreateSheet("Фасады, ТПС"); sheet1.PrintSetup.PaperSize = (short)PaperSizeType.A4; sheet1.SetMargin(HSSFSheet.LeftMargin, (double).12); sheet1.SetMargin(HSSFSheet.RightMargin, (double).07); sheet1.SetMargin(HSSFSheet.TopMargin, (double).20); sheet1.SetMargin(HSSFSheet.BottomMargin, (double).20); pos += 2; int ColIndex = 0; Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++); Cell1.SetCellValue("Клиент"); Cell1.CellStyle = SimpleHeaderCS; Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++); Cell1.SetCellValue("Клиент ЗОВ"); Cell1.CellStyle = SimpleHeaderCS; Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++); Cell1.SetCellValue("№ заказа"); Cell1.CellStyle = SimpleHeaderCS; Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++); Cell1.SetCellValue("Дата упаковки"); Cell1.CellStyle = SimpleHeaderCS; Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++); Cell1.SetCellValue("Бухг.наим."); Cell1.CellStyle = SimpleHeaderCS; Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++); Cell1.SetCellValue("Инв.номер"); Cell1.CellStyle = SimpleHeaderCS; Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++); Cell1.SetCellValue("Фасад"); Cell1.CellStyle = SimpleHeaderCS; Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++); Cell1.SetCellValue("Цвет"); Cell1.CellStyle = SimpleHeaderCS; Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++); Cell1.SetCellValue("Вставка"); Cell1.CellStyle = SimpleHeaderCS; Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++); Cell1.SetCellValue("Цвет наполнителя"); Cell1.CellStyle = SimpleHeaderCS; Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++); Cell1.SetCellValue("Вставка-2"); Cell1.CellStyle = SimpleHeaderCS; Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++); Cell1.SetCellValue("Цвет наполнителя-2"); Cell1.CellStyle = SimpleHeaderCS; Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++); Cell1.SetCellValue("Кол-во"); Cell1.CellStyle = SimpleHeaderCS; Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++); Cell1.SetCellValue("Квадратура"); Cell1.CellStyle = SimpleHeaderCS; Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++); Cell1.SetCellValue("Ед.изм."); Cell1.CellStyle = SimpleHeaderCS; Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++); Cell1.SetCellValue("ID упаковки"); Cell1.CellStyle = SimpleHeaderCS; Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++); Cell1.SetCellValue("№ упаковки"); Cell1.CellStyle = SimpleHeaderCS; pos++; for (int i = 0; i < FrontsTPSDT.Rows.Count; i++) { ColIndex = 0; Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++); Cell1.SetCellValue(FrontsTPSDT.Rows[i]["ClientName"].ToString()); Cell1.CellStyle = SimpleCS; Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++); Cell1.SetCellValue(FrontsTPSDT.Rows[i]["ZOVClientName"].ToString()); Cell1.CellStyle = SimpleCS; Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++); Cell1.SetCellValue(Convert.ToInt32(FrontsTPSDT.Rows[i]["OrderNumber"])); Cell1.CellStyle = CountCS; Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++); Cell1.SetCellValue(FrontsTPSDT.Rows[i]["PackingDateTime"].ToString()); Cell1.CellStyle = SimpleCS; Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++); Cell1.SetCellValue(FrontsTPSDT.Rows[i]["AccountingName"].ToString()); Cell1.CellStyle = SimpleCS; Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++); Cell1.SetCellValue(FrontsTPSDT.Rows[i]["InvNumber"].ToString()); Cell1.CellStyle = SimpleCS; Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++); Cell1.SetCellValue(FrontsTPSDT.Rows[i]["TechStoreName"].ToString()); Cell1.CellStyle = SimpleCS; Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++); Cell1.SetCellValue(FrontsTPSDT.Rows[i]["Expr35"].ToString()); Cell1.CellStyle = SimpleCS; Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++); Cell1.SetCellValue(FrontsTPSDT.Rows[i]["Expr36"].ToString()); Cell1.CellStyle = SimpleCS; Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++); Cell1.SetCellValue(FrontsTPSDT.Rows[i]["Expr37"].ToString()); Cell1.CellStyle = SimpleCS; Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++); Cell1.SetCellValue(FrontsTPSDT.Rows[i]["Expr38"].ToString()); Cell1.CellStyle = SimpleCS; Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++); Cell1.SetCellValue(FrontsTPSDT.Rows[i]["Expr1"].ToString()); Cell1.CellStyle = SimpleCS; Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++); Cell1.SetCellValue(Convert.ToInt32(FrontsTPSDT.Rows[i]["Count"])); Cell1.CellStyle = CountCS; Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++); Cell1.SetCellValue(Convert.ToDouble(FrontsTPSDT.Rows[i]["Square"])); Cell1.CellStyle = CountDecCS; Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++); Cell1.SetCellValue(FrontsTPSDT.Rows[i]["Measure"].ToString()); Cell1.CellStyle = SimpleCS; Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++); Cell1.SetCellValue(Convert.ToInt32(FrontsTPSDT.Rows[i]["PackageID"])); Cell1.CellStyle = CountCS; Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++); Cell1.SetCellValue(Convert.ToInt32(FrontsTPSDT.Rows[i]["PackNumber"])); Cell1.CellStyle = CountCS; pos++; } } pos = 0; if (DecorProfilDT.Rows.Count > 0) { HSSFSheet sheet1 = hssfworkbook.CreateSheet("Декор, Профиль"); sheet1.PrintSetup.PaperSize = (short)PaperSizeType.A4; sheet1.SetMargin(HSSFSheet.LeftMargin, (double).12); sheet1.SetMargin(HSSFSheet.RightMargin, (double).07); sheet1.SetMargin(HSSFSheet.TopMargin, (double).20); sheet1.SetMargin(HSSFSheet.BottomMargin, (double).20); pos += 2; int ColIndex = 0; Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++); Cell1.SetCellValue("Клиент"); Cell1.CellStyle = SimpleHeaderCS; Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++); Cell1.SetCellValue("Клиент ЗОВ"); Cell1.CellStyle = SimpleHeaderCS; Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++); Cell1.SetCellValue("№ заказа"); Cell1.CellStyle = SimpleHeaderCS; Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++); Cell1.SetCellValue("Дата упаковки"); Cell1.CellStyle = SimpleHeaderCS; Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++); Cell1.SetCellValue("Бухг.наим."); Cell1.CellStyle = SimpleHeaderCS; Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++); Cell1.SetCellValue("Инв.номер"); Cell1.CellStyle = SimpleHeaderCS; Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++); Cell1.SetCellValue("Артикул"); Cell1.CellStyle = SimpleHeaderCS; Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++); Cell1.SetCellValue("Цвет"); Cell1.CellStyle = SimpleHeaderCS; Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++); Cell1.SetCellValue("Длина"); Cell1.CellStyle = SimpleHeaderCS; Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++); Cell1.SetCellValue("Высота"); Cell1.CellStyle = SimpleHeaderCS; Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++); Cell1.SetCellValue("Ширина"); Cell1.CellStyle = SimpleHeaderCS; Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++); Cell1.SetCellValue("Кол-во"); Cell1.CellStyle = SimpleHeaderCS; Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++); Cell1.SetCellValue("Ед.изм."); Cell1.CellStyle = SimpleHeaderCS; Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++); Cell1.SetCellValue("ID упаковки"); Cell1.CellStyle = SimpleHeaderCS; Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++); Cell1.SetCellValue("№ упаковки"); Cell1.CellStyle = SimpleHeaderCS; pos++; for (int i = 0; i < DecorProfilDT.Rows.Count; i++) { ColIndex = 0; Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++); Cell1.SetCellValue(DecorProfilDT.Rows[i]["ClientName"].ToString()); Cell1.CellStyle = SimpleCS; Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++); Cell1.SetCellValue(DecorProfilDT.Rows[i]["ZOVClientName"].ToString()); Cell1.CellStyle = SimpleCS; Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++); Cell1.SetCellValue(Convert.ToInt32(DecorProfilDT.Rows[i]["OrderNumber"])); Cell1.CellStyle = CountCS; Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++); Cell1.SetCellValue(DecorProfilDT.Rows[i]["PackingDateTime"].ToString()); Cell1.CellStyle = SimpleCS; Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++); Cell1.SetCellValue(DecorProfilDT.Rows[i]["AccountingName"].ToString()); Cell1.CellStyle = SimpleCS; Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++); Cell1.SetCellValue(DecorProfilDT.Rows[i]["InvNumber"].ToString()); Cell1.CellStyle = SimpleCS; Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++); Cell1.SetCellValue(DecorProfilDT.Rows[i]["TechStoreName"].ToString()); Cell1.CellStyle = SimpleCS; Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++); Cell1.SetCellValue(DecorProfilDT.Rows[i]["Expr35"].ToString()); Cell1.CellStyle = SimpleCS; Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++); Cell1.SetCellValue(Convert.ToInt32(DecorProfilDT.Rows[i]["Length"])); Cell1.CellStyle = CountCS; Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++); Cell1.SetCellValue(Convert.ToInt32(DecorProfilDT.Rows[i]["Height"])); Cell1.CellStyle = CountCS; Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++); Cell1.SetCellValue(Convert.ToInt32(DecorProfilDT.Rows[i]["Width"])); Cell1.CellStyle = CountCS; Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++); Cell1.SetCellValue(Convert.ToInt32(DecorProfilDT.Rows[i]["Count"])); Cell1.CellStyle = CountCS; Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++); Cell1.SetCellValue(DecorProfilDT.Rows[i]["Measure"].ToString()); Cell1.CellStyle = SimpleCS; Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++); Cell1.SetCellValue(Convert.ToInt32(DecorProfilDT.Rows[i]["PackageID"])); Cell1.CellStyle = CountCS; Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++); Cell1.SetCellValue(Convert.ToInt32(DecorProfilDT.Rows[i]["PackNumber"])); Cell1.CellStyle = CountCS; pos++; } } pos = 0; if (DecorTPSDT.Rows.Count > 0) { HSSFSheet sheet1 = hssfworkbook.CreateSheet("Декор, ТПС"); sheet1.PrintSetup.PaperSize = (short)PaperSizeType.A4; sheet1.SetMargin(HSSFSheet.LeftMargin, (double).12); sheet1.SetMargin(HSSFSheet.RightMargin, (double).07); sheet1.SetMargin(HSSFSheet.TopMargin, (double).20); sheet1.SetMargin(HSSFSheet.BottomMargin, (double).20); pos += 2; int ColIndex = 0; Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++); Cell1.SetCellValue("Клиент"); Cell1.CellStyle = SimpleHeaderCS; Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++); Cell1.SetCellValue("Клиент ЗОВ"); Cell1.CellStyle = SimpleHeaderCS; Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++); Cell1.SetCellValue("№ заказа"); Cell1.CellStyle = SimpleHeaderCS; Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++); Cell1.SetCellValue("Дата упаковки"); Cell1.CellStyle = SimpleHeaderCS; Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++); Cell1.SetCellValue("Бухг.наим."); Cell1.CellStyle = SimpleHeaderCS; Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++); Cell1.SetCellValue("Инв.номер"); Cell1.CellStyle = SimpleHeaderCS; Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++); Cell1.SetCellValue("Артикул"); Cell1.CellStyle = SimpleHeaderCS; Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++); Cell1.SetCellValue("Цвет"); Cell1.CellStyle = SimpleHeaderCS; Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++); Cell1.SetCellValue("Длина"); Cell1.CellStyle = SimpleHeaderCS; Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++); Cell1.SetCellValue("Высота"); Cell1.CellStyle = SimpleHeaderCS; Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++); Cell1.SetCellValue("Ширина"); Cell1.CellStyle = SimpleHeaderCS; Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++); Cell1.SetCellValue("Кол-во"); Cell1.CellStyle = SimpleHeaderCS; Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++); Cell1.SetCellValue("Ед.изм."); Cell1.CellStyle = SimpleHeaderCS; Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++); Cell1.SetCellValue("ID упаковки"); Cell1.CellStyle = SimpleHeaderCS; Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++); Cell1.SetCellValue("№ упаковки"); Cell1.CellStyle = SimpleHeaderCS; pos++; for (int i = 0; i < DecorTPSDT.Rows.Count; i++) { ColIndex = 0; Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++); Cell1.SetCellValue(DecorTPSDT.Rows[i]["ClientName"].ToString()); Cell1.CellStyle = SimpleCS; Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++); Cell1.SetCellValue(DecorTPSDT.Rows[i]["ZOVClientName"].ToString()); Cell1.CellStyle = SimpleCS; Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++); Cell1.SetCellValue(Convert.ToInt32(DecorTPSDT.Rows[i]["OrderNumber"])); Cell1.CellStyle = CountCS; Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++); Cell1.SetCellValue(DecorTPSDT.Rows[i]["PackingDateTime"].ToString()); Cell1.CellStyle = SimpleCS; Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++); Cell1.SetCellValue(DecorTPSDT.Rows[i]["AccountingName"].ToString()); Cell1.CellStyle = SimpleCS; Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++); Cell1.SetCellValue(DecorTPSDT.Rows[i]["InvNumber"].ToString()); Cell1.CellStyle = SimpleCS; Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++); Cell1.SetCellValue(DecorTPSDT.Rows[i]["TechStoreName"].ToString()); Cell1.CellStyle = SimpleCS; Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++); Cell1.SetCellValue(DecorTPSDT.Rows[i]["Expr35"].ToString()); Cell1.CellStyle = SimpleCS; Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++); Cell1.SetCellValue(Convert.ToInt32(DecorTPSDT.Rows[i]["Length"])); Cell1.CellStyle = CountCS; Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++); Cell1.SetCellValue(Convert.ToInt32(DecorTPSDT.Rows[i]["Height"])); Cell1.CellStyle = CountCS; Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++); Cell1.SetCellValue(Convert.ToInt32(DecorTPSDT.Rows[i]["Width"])); Cell1.CellStyle = CountCS; Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++); Cell1.SetCellValue(Convert.ToInt32(DecorTPSDT.Rows[i]["Count"])); Cell1.CellStyle = CountCS; Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++); Cell1.SetCellValue(DecorTPSDT.Rows[i]["Measure"].ToString()); Cell1.CellStyle = SimpleCS; Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++); Cell1.SetCellValue(Convert.ToInt32(DecorTPSDT.Rows[i]["PackageID"])); Cell1.CellStyle = CountCS; Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++); Cell1.SetCellValue(Convert.ToInt32(DecorTPSDT.Rows[i]["PackNumber"])); Cell1.CellStyle = CountCS; pos++; } } string tempFolder = System.Environment.GetEnvironmentVariable("TEMP"); FileInfo file = new FileInfo(tempFolder + @"\" + FileName + ".xls"); int j = 1; while (file.Exists == true) { file = new FileInfo(tempFolder + @"\" + FileName + "(" + j++ + ").xls"); } FileStream NewFile = new FileStream(file.FullName, FileMode.Create); hssfworkbook.Write(NewFile); NewFile.Close(); System.Diagnostics.Process.Start(file.FullName); ClearReport(); }
/// <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> /// DataTable导出到Excel的MemoryStream Export() /// </summary> /// <param name="dtSource">DataTable数据源</param> /// <param name="excelConfig">导出设置包含文件名、标题、列设置</param> public static MemoryStream ExportMemoryStream(DataTable dtSource, ExcelConfig excelConfig) { int colint = 0; for (int i = 0; i < dtSource.Columns.Count;) { DataColumn column = dtSource.Columns[i]; if (excelConfig.ColumnEntity[colint].Column != column.ColumnName) { dtSource.Columns.Remove(column.ColumnName); } else { i++; if (colint < excelConfig.ColumnEntity.Count - 1) { colint++; } } } HSSFWorkbook workbook = new HSSFWorkbook(); ISheet sheet = 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 #region 设置标题样式 ICellStyle headStyle = workbook.CreateCellStyle(); int[] arrColWidth = new int[dtSource.Columns.Count]; string[] arrColName = new string[dtSource.Columns.Count]; //列名 ICellStyle[] arryColumStyle = new ICellStyle[dtSource.Columns.Count]; //样式表 headStyle.Alignment = HorizontalAlignment.Center; // ------------------ if (excelConfig.Background != new Color()) { if (excelConfig.Background != new Color()) { headStyle.FillPattern = FillPattern.SolidForeground; headStyle.FillForegroundColor = GetXLColour(workbook, excelConfig.Background); } } IFont font = workbook.CreateFont(); font.FontHeightInPoints = excelConfig.TitlePoint; if (excelConfig.ForeColor != new Color()) { font.Color = GetXLColour(workbook, excelConfig.ForeColor); } font.Boldweight = 700; headStyle.SetFont(font); #endregion #region 列头及样式 ICellStyle cHeadStyle = workbook.CreateCellStyle(); cHeadStyle.Alignment = HorizontalAlignment.Center; // ------------------ IFont cfont = workbook.CreateFont(); cfont.FontHeightInPoints = excelConfig.HeadPoint; cHeadStyle.SetFont(cfont); #endregion #region 设置内容单元格样式 foreach (DataColumn item in dtSource.Columns) { ICellStyle columnStyle = workbook.CreateCellStyle(); columnStyle.Alignment = HorizontalAlignment.Center; arrColWidth[item.Ordinal] = Encoding.GetEncoding(936).GetBytes(item.ColumnName.ToString()).Length; arrColName[item.Ordinal] = item.ColumnName.ToString(); if (excelConfig.ColumnEntity != null) { ColumnEntity columnentity = excelConfig.ColumnEntity.Find(t => t.Column == item.ColumnName); if (columnentity != null) { arrColName[item.Ordinal] = columnentity.ExcelColumn; if (columnentity.Width != 0) { arrColWidth[item.Ordinal] = columnentity.Width; } if (columnentity.Background != new Color()) { if (columnentity.Background != new Color()) { columnStyle.FillPattern = FillPattern.SolidForeground; columnStyle.FillForegroundColor = GetXLColour(workbook, columnentity.Background); } } if (columnentity.Font != null || columnentity.Point != 0 || columnentity.ForeColor != new Color()) { IFont columnFont = workbook.CreateFont(); columnFont.FontHeightInPoints = 10; if (columnentity.Font != null) { columnFont.FontName = columnentity.Font; } if (columnentity.Point != 0) { columnFont.FontHeightInPoints = columnentity.Point; } if (columnentity.ForeColor != new Color()) { columnFont.Color = GetXLColour(workbook, columnentity.ForeColor); } columnStyle.SetFont(font); } columnStyle.Alignment = getAlignment(columnentity.Alignment); } } arryColumStyle[item.Ordinal] = columnStyle; } if (excelConfig.IsAllSizeColumn) { #region 根据列中最长列的长度取得列宽 for (int i = 0; i < dtSource.Rows.Count; i++) { for (int j = 0; j < dtSource.Columns.Count; j++) { if (arrColWidth[j] != 0) { int intTemp = Encoding.GetEncoding(936).GetBytes(dtSource.Rows[i][j].ToString()).Length; if (intTemp > arrColWidth[j]) { arrColWidth[j] = intTemp; } } } } #endregion } #endregion int rowIndex = 0; #region 表头及样式 if (excelConfig.Title != null) { IRow headerRow = sheet.CreateRow(rowIndex); rowIndex++; if (excelConfig.TitleHeight != 0) { headerRow.Height = (short)(excelConfig.TitleHeight * 20); } headerRow.HeightInPoints = 25; headerRow.CreateCell(0).SetCellValue(excelConfig.Title); headerRow.GetCell(0).CellStyle = headStyle; sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(0, 0, 0, dtSource.Columns.Count - 1)); // ------------------ } #endregion #region 列头及样式 { IRow headerRow = sheet.CreateRow(rowIndex); rowIndex++; #region 如果设置了列标题就按列标题定义列头,没定义直接按字段名输出 foreach (DataColumn column in dtSource.Columns) { headerRow.CreateCell(column.Ordinal).SetCellValue(arrColName[column.Ordinal]); headerRow.GetCell(column.Ordinal).CellStyle = cHeadStyle; //设置列宽 sheet.SetColumnWidth(column.Ordinal, (arrColWidth[column.Ordinal] + 1) * 256); } #endregion } #endregion ICellStyle dateStyle = workbook.CreateCellStyle(); IDataFormat format = workbook.CreateDataFormat(); dateStyle.DataFormat = format.GetFormat("yyyy-mm-dd"); foreach (DataRow row in dtSource.Rows) { #region 新建表,填充表头,填充列头,样式 if (rowIndex == 65535) { sheet = workbook.CreateSheet(); rowIndex = 0; #region 表头及样式 { if (excelConfig.Title != null) { IRow headerRow = sheet.CreateRow(rowIndex); rowIndex++; if (excelConfig.TitleHeight != 0) { headerRow.Height = (short)(excelConfig.TitleHeight * 20); } headerRow.HeightInPoints = 25; headerRow.CreateCell(0).SetCellValue(excelConfig.Title); headerRow.GetCell(0).CellStyle = headStyle; sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(0, 0, 0, dtSource.Columns.Count - 1)); // ------------------ } } #endregion #region 列头及样式 { IRow headerRow = sheet.CreateRow(rowIndex); rowIndex++; #region 如果设置了列标题就按列标题定义列头,没定义直接按字段名输出 foreach (DataColumn column in dtSource.Columns) { headerRow.CreateCell(column.Ordinal).SetCellValue(arrColName[column.Ordinal]); headerRow.GetCell(column.Ordinal).CellStyle = cHeadStyle; //设置列宽 sheet.SetColumnWidth(column.Ordinal, (arrColWidth[column.Ordinal] + 1) * 256); } #endregion } #endregion } #endregion #region 填充内容 IRow dataRow = sheet.CreateRow(rowIndex); foreach (DataColumn column in dtSource.Columns) { ICell newCell = dataRow.CreateCell(column.Ordinal); newCell.CellStyle = arryColumStyle[column.Ordinal]; string drValue = row[column].ToString(); SetCell(newCell, dateStyle, column.DataType, drValue); } #endregion rowIndex++; } using (MemoryStream ms = new MemoryStream()) { workbook.Write(ms); ms.Flush(); ms.Position = 0; return(ms); } }
/********************************************************分割线往下方法不用与导入导出*********************************************************/ #region DataGridViewToExcel(DataGridView myDgv, string strHeaderText) DataTable导出到Excel的MemoryStream /// <summary> /// DataTable导出到Excel的MemoryStream /// </summary> /// <param name="myDgv">DataGridView控件名称</param> /// <param name="strHeaderText">第一行标题头</param> /// <param name="titleNames">列名称数组</param> /// <returns>内存流</returns> private static MemoryStream DataGridViewToExcel(DataGridView myDgv, string strHeaderText = null, string[] titleNames = null) { 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).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 表头及样式 { if (!string.IsNullOrEmpty(strHeaderText)) { 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; font.FontName = "宋体"; headStyle.SetFont(font); headerRow.GetCell(0).CellStyle = headStyle; // sheet.AddMergedRegion(new Region(0, 0, 0, dtSource.Columns.Count - 1)); //headerRow.Dispose(); rowIndex++; } } #endregion #region 列头及样式 { HSSFRow headerRow = (HSSFRow)sheet.CreateRow(rowIndex); HSSFCellStyle headStyle = (HSSFCellStyle)workbook.CreateCellStyle(); // headStyle.Alignment = CellHorizontalAlignment.CENTER; HSSFFont font = (HSSFFont)workbook.CreateFont(); font.FontHeightInPoints = 14; font.Boldweight = 500; font.FontName = "宋体"; headStyle.SetFont(font); if (titleNames != null) { if (titleNames.Length > 0) { for (int i = 0; i < titleNames.Length; i++) { headerRow.CreateCell(i).SetCellValue(titleNames[i]); headerRow.GetCell(i).CellStyle = headStyle; //设置列宽 sheet.SetColumnWidth(i, (arrColWidth[i] + 1) * 100); } } } else { 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) * 100); } } rowIndex++; // headerRow.Dispose(); } #endregion } #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> /// List导出到Excel的MemoryStream /// </summary> /// <param name="list">数据源</param> /// <param name="sHeaderText">表头文本</param> /// <param name="columns">需要导出的属性</param> private MemoryStream CreateExportMemoryStream(List <T> list, string sHeaderText, string[] columns) { HSSFWorkbook workbook = new HSSFWorkbook(); ISheet sheet = workbook.CreateSheet(); Type type = typeof(T); PropertyInfo[] properties = ReflectionHelper.GetProperties(type, columns); ICellStyle dateStyle = workbook.CreateCellStyle(); IDataFormat format = workbook.CreateDataFormat(); dateStyle.DataFormat = format.GetFormat("yyyy-MM-dd"); //单元格填充循环外设定单元格格式,避免4000行异常 ICellStyle contentStyle = workbook.CreateCellStyle(); contentStyle.Alignment = HorizontalAlignment.Left; #region 取得每列的列宽(最大宽度) int[] arrColWidth = new int[properties.Length]; for (int columnIndex = 0; columnIndex < properties.Length; columnIndex++) { //GBK对应的code page是CP936 arrColWidth[columnIndex] = properties[columnIndex].Name.Length; } #endregion for (int rowIndex = 0; rowIndex < list.Count; rowIndex++) { #region 新建表,填充表头,填充列头,样式 if (rowIndex == 65535 || rowIndex == 0) { if (rowIndex != 0) { sheet = workbook.CreateSheet(); } #region 表头及样式 { IRow headerRow = sheet.CreateRow(0); headerRow.HeightInPoints = 25; headerRow.CreateCell(0).SetCellValue(sHeaderText); ICellStyle headStyle = workbook.CreateCellStyle(); headStyle.Alignment = HorizontalAlignment.Center; IFont font = workbook.CreateFont(); font.FontHeightInPoints = 20; font.Boldweight = 700; headStyle.SetFont(font); headerRow.GetCell(0).CellStyle = headStyle; sheet.AddMergedRegion(new CellRangeAddress(0, 0, 0, properties.Length - 1)); } #endregion #region 列头及样式 { IRow headerRow = sheet.CreateRow(1); ICellStyle headStyle = workbook.CreateCellStyle(); headStyle.Alignment = HorizontalAlignment.Center; IFont font = workbook.CreateFont(); font.FontHeightInPoints = 10; font.Boldweight = 700; headStyle.SetFont(font); for (int columnIndex = 0; columnIndex < properties.Length; columnIndex++) { // 类属性如果有Description就用Description当做列名 DescriptionAttribute customAttribute = (DescriptionAttribute)Attribute.GetCustomAttribute(properties[columnIndex], typeof(DescriptionAttribute)); string description = properties[columnIndex].Name; if (customAttribute != null) { description = customAttribute.Description; } headerRow.CreateCell(columnIndex).SetCellValue(description); headerRow.GetCell(columnIndex).CellStyle = headStyle; //根据表头设置列宽 sheet.SetColumnWidth(columnIndex, (arrColWidth[columnIndex] + 1) * 256); } } #endregion } #endregion #region 填充内容 IRow dataRow = sheet.CreateRow(rowIndex + 2); // 前面2行已被占用 for (int columnIndex = 0; columnIndex < properties.Length; columnIndex++) { ICell newCell = dataRow.CreateCell(columnIndex); newCell.CellStyle = contentStyle; string drValue = properties[columnIndex].GetValue(list[rowIndex], null).ParseToString(); //根据单元格内容设定列宽 int length = Math.Min(253, Encoding.UTF8.GetBytes(drValue).Length + 1) * 256; if (sheet.GetColumnWidth(columnIndex) < length && !drValue.IsEmpty()) { sheet.SetColumnWidth(columnIndex, length); } switch (properties[columnIndex].PropertyType.ToString()) { case "System.String": newCell.SetCellValue(drValue); break; case "System.DateTime": case "System.Nullable`1[System.DateTime]": newCell.SetCellValue(drValue.ParseToDateTime()); newCell.CellStyle = dateStyle; //格式化显示 break; case "System.Boolean": case "System.Nullable`1[System.Boolean]": newCell.SetCellValue(drValue.ParseToBool()); break; case "System.Byte": case "System.Nullable`1[System.Byte]": case "System.Int16": case "System.Nullable`1[System.Int16]": case "System.Int32": case "System.Nullable`1[System.Int32]": newCell.SetCellValue(drValue.ParseToInt()); break; case "System.Int64": case "System.Nullable`1[System.Int64]": newCell.SetCellValue(drValue.ParseToString()); break; case "System.Double": case "System.Nullable`1[System.Double]": newCell.SetCellValue(drValue.ParseToDouble()); break; case "System.Single": case "System.Nullable`1[System.Single]": newCell.SetCellValue(drValue.ParseToDouble()); break; case "System.Decimal": case "System.Nullable`1[System.Decimal]": newCell.SetCellValue(drValue.ParseToDouble()); break; case "System.DBNull": newCell.SetCellValue(string.Empty); break; default: newCell.SetCellValue(string.Empty); break; } } #endregion } using (MemoryStream ms = new MemoryStream()) { workbook.Write(ms); workbook.Close(); ms.Flush(); ms.Position = 0; return(ms); } }
/// <summary> /// DataTable导出到Excel的MemoryStream Export() /// </summary> /// <param name="dtSource">DataTable数据源</param> /// <param name="excelConfig">导出设置包含文件名、标题、列设置</param> public static MemoryStream ExportMemoryStream(List <T> lists, ExcelConfig excelConfig) { HSSFWorkbook workbook = new HSSFWorkbook(); ISheet sheet = workbook.CreateSheet(); Type type = typeof(T); PropertyInfo[] properties = type.GetProperties(); #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 #region 设置标题样式 ICellStyle headStyle = workbook.CreateCellStyle(); int[] arrColWidth = new int[properties.Length]; string[] arrColName = new string[properties.Length]; //列名 ICellStyle[] arryColumStyle = new ICellStyle[properties.Length]; //样式表 headStyle.Alignment = HorizontalAlignment.Center; // ------------------ if (excelConfig.Background != new Color()) { if (excelConfig.Background != new Color()) { headStyle.FillPattern = FillPattern.SolidForeground; headStyle.FillForegroundColor = GetXLColour(workbook, excelConfig.Background); } } IFont font = workbook.CreateFont(); font.FontHeightInPoints = excelConfig.TitlePoint; if (excelConfig.ForeColor != new Color()) { font.Color = GetXLColour(workbook, excelConfig.ForeColor); } font.Boldweight = 700; headStyle.SetFont(font); #endregion #region 列头及样式 ICellStyle cHeadStyle = workbook.CreateCellStyle(); cHeadStyle.Alignment = HorizontalAlignment.Center; // ------------------ IFont cfont = workbook.CreateFont(); cfont.FontHeightInPoints = excelConfig.HeadPoint; cHeadStyle.SetFont(cfont); #endregion #region 设置内容单元格样式 int i = 0; foreach (PropertyInfo column in properties) { ICellStyle columnStyle = workbook.CreateCellStyle(); columnStyle.Alignment = HorizontalAlignment.Center; arrColWidth[i] = Encoding.GetEncoding(936).GetBytes(column.Name).Length; arrColName[i] = column.Name; if (excelConfig.ColumnEntity != null) { ColumnEntity columnentity = excelConfig.ColumnEntity.Find(t => t.Column == column.Name); if (columnentity != null) { arrColName[i] = columnentity.ExcelColumn; if (columnentity.Width != 0) { arrColWidth[i] = columnentity.Width; } if (columnentity.Background != new Color()) { if (columnentity.Background != new Color()) { columnStyle.FillPattern = FillPattern.SolidForeground; columnStyle.FillForegroundColor = GetXLColour(workbook, columnentity.Background); } } if (columnentity.Font != null || columnentity.Point != 0 || columnentity.ForeColor != new Color()) { IFont columnFont = workbook.CreateFont(); columnFont.FontHeightInPoints = 10; if (columnentity.Font != null) { columnFont.FontName = columnentity.Font; } if (columnentity.Point != 0) { columnFont.FontHeightInPoints = columnentity.Point; } if (columnentity.ForeColor != new Color()) { columnFont.Color = GetXLColour(workbook, columnentity.ForeColor); } columnStyle.SetFont(font); } } } arryColumStyle[i] = columnStyle; i++; } #endregion #region 填充数据 #endregion ICellStyle dateStyle = workbook.CreateCellStyle(); IDataFormat format = workbook.CreateDataFormat(); dateStyle.DataFormat = format.GetFormat("yyyy-mm-dd"); int rowIndex = 0; foreach (T item in lists) { #region 新建表,填充表头,填充列头,样式 if (rowIndex == 65535 || rowIndex == 0) { if (rowIndex != 0) { sheet = workbook.CreateSheet(); } #region 表头及样式 { if (excelConfig.Title != null) { IRow headerRow = sheet.CreateRow(0); if (excelConfig.TitleHeight != 0) { headerRow.Height = (short)(excelConfig.TitleHeight * 20); } headerRow.HeightInPoints = 25; headerRow.CreateCell(0).SetCellValue(excelConfig.Title); headerRow.GetCell(0).CellStyle = headStyle; sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(0, 0, 0, lists.Count - 1)); // ------------------ } } #endregion #region 列头及样式 { IRow headerRow = sheet.CreateRow(1); #region 如果设置了列标题就按列标题定义列头,没定义直接按字段名输出 int headIndex = 0; foreach (PropertyInfo column in properties) { headerRow.CreateCell(headIndex).SetCellValue(arrColName[headIndex]); headerRow.GetCell(headIndex).CellStyle = cHeadStyle; //设置列宽 sheet.SetColumnWidth(headIndex, (arrColWidth[headIndex] + 1) * 256); headIndex++; } #endregion } #endregion rowIndex = 2; } #endregion #region 填充内容 IRow dataRow = sheet.CreateRow(rowIndex); int ordinal = 0; foreach (PropertyInfo column in properties) { ICell newCell = dataRow.CreateCell(ordinal); newCell.CellStyle = arryColumStyle[ordinal]; string drValue = column.GetValue(item, null) == null ? "" : column.GetValue(item, null).ToString(); SetCell(newCell, dateStyle, column.PropertyType, drValue); ordinal++; } #endregion rowIndex++; } using (MemoryStream ms = new MemoryStream()) { workbook.Write(ms); ms.Flush(); ms.Position = 0; return(ms); } }
static void ExportToExcel(object sender, ExportExcelEventArgs e) { var workBook = new HSSFWorkbook(); var sheet = workBook.CreateSheet("exported-data"); if (e.Cannelled) { return; } var titleStyle1 = CreateTitleStyle(workBook); var dataStyle = CreateDataStyle(workBook); var dataStyle1 = workBook.CreateCellStyle(); // 文本。 dataStyle1.CloneStyleFrom(dataStyle); var dataStyle2 = workBook.CreateCellStyle(); // 整数。 dataStyle2.CloneStyleFrom(dataStyle); dataStyle2.DataFormat = workBook.CreateDataFormat().GetFormat("0"); dataStyle2.Alignment = HorizontalAlignment.Right; var dataStyle3 = workBook.CreateCellStyle(); // 整数金额。 dataStyle3.CloneStyleFrom(dataStyle); dataStyle3.DataFormat = workBook.CreateDataFormat().GetFormat("#,##0"); dataStyle3.Alignment = HorizontalAlignment.Right; var dataStyle4 = workBook.CreateCellStyle(); // 金额。 dataStyle4.CloneStyleFrom(dataStyle); dataStyle4.DataFormat = workBook.CreateDataFormat().GetFormat("#,##0.00"); dataStyle4.Alignment = HorizontalAlignment.Right; var dataStyle5 = workBook.CreateCellStyle(); // 日期。 dataStyle5.CloneStyleFrom(dataStyle); dataStyle5.DataFormat = workBook.CreateDataFormat().GetFormat("yyyy/MM/dd"); var dataStyle6 = workBook.CreateCellStyle(); // 日期时间。 dataStyle6.CloneStyleFrom(dataStyle); dataStyle6.DataFormat = workBook.CreateDataFormat().GetFormat("yyyy/MM/dd HH:mm:ss"); var dataStyle7 = workBook.CreateCellStyle(); // 百分比 dataStyle7.CloneStyleFrom(dataStyle); dataStyle7.DataFormat = workBook.CreateDataFormat().GetFormat("0.00%"); var dataStyle8 = workBook.CreateCellStyle(); // 是否。 dataStyle8.CloneStyleFrom(dataStyle); // 创建空白行 sheet.CreateRow(0); if (e.Cannelled) { return; } // 创建标题行。 var titleRow = sheet.CreateRow(1); for (var i = 0; i < e.Columns.Count; ++i) { if (e.Cannelled) { return; } var colIndex = i + 1; // Excel表格中的列序号。 var col = e.Columns[i]; var cell = titleRow.CreateCell(colIndex); cell.SetCellValue(col.Title); cell.CellStyle = titleStyle1; sheet.SetColumnWidth(colIndex, col.Width * 50 /* 1/5 of character width */); } var total = e.Binding.DataTable.Count; for (int j = 0; j < total; ++j) { if (e.Cannelled) { return; } var rowIndex = j + 2; // Excel表格中的行序号。 var dataRow = sheet.CreateRow(rowIndex); for (int i = 0; i < e.Columns.Count; ++i) { if (e.Cannelled) { return; } var colIndex = i + 1; // Excel表格中的列序号。 var col = e.Columns[i]; var cell = dataRow.CreateCell(colIndex); var value = e.Binding.GetCellValue(j, col.DataKey); if (value == null || string.IsNullOrWhiteSpace(value.ToString())) { continue; } switch (col.Type) { case MyGridColumnType.Boolean: cell.SetCellType(NPOI.SS.UserModel.CellType.String); cell.SetCellValue(value + ""); cell.CellStyle = dataStyle8; break; case MyGridColumnType.Date: cell.SetCellType(NPOI.SS.UserModel.CellType.String); cell.SetCellValue(Convert.ToDateTime(value)); cell.CellStyle = dataStyle5; break; case MyGridColumnType.DateTime: cell.SetCellType(NPOI.SS.UserModel.CellType.String); cell.SetCellValue(Convert.ToDateTime(value)); cell.CellStyle = dataStyle6; break; case MyGridColumnType.Money: cell.SetCellType(NPOI.SS.UserModel.CellType.Numeric); cell.SetCellValue(Convert.ToDouble(value)); cell.CellStyle = dataStyle4; break; case MyGridColumnType.IntMoney: cell.SetCellType(NPOI.SS.UserModel.CellType.Numeric); cell.SetCellValue(Convert.ToInt64(value)); cell.CellStyle = dataStyle3; break; case MyGridColumnType.Percent: cell.SetCellType(NPOI.SS.UserModel.CellType.Numeric); cell.SetCellValue(Convert.ToDouble(value)); cell.CellStyle = dataStyle7; break; case MyGridColumnType.Number: cell.SetCellType(NPOI.SS.UserModel.CellType.Numeric); cell.SetCellValue(Convert.ToInt64(value)); cell.CellStyle = dataStyle2; break; default: cell.SetCellType(NPOI.SS.UserModel.CellType.String); cell.SetCellValue(Convert.ToString(value)); cell.CellStyle = dataStyle1; break; } } e.UpdateProgress(j + 1); } if (e.Cannelled) { return; } using (var fs = File.OpenWrite(e.FileName)) { e.UpdateProgress(total, "保存文件..."); workBook.Write(fs); } e.UpdateProgress(total, "已保存"); } // end of Export.
/// <summary> /// DataTable导出到Excel的MemoryStream /// </summary> /// <param name="dtSource"></param> /// <param name="dateFormat"></param> /// <returns></returns> private static MemoryStream DataTableToExcel(DataTable dtSource, string dateFormat) { HSSFWorkbook workbook = new HSSFWorkbook(); HSSFSheet sheet = (HSSFSheet)workbook.CreateSheet(); HSSFCellStyle dateStyle = (HSSFCellStyle)workbook.CreateCellStyle(); HSSFDataFormat format = (HSSFDataFormat)workbook.CreateDataFormat(); dateStyle.DataFormat = format.GetFormat(dateFormat); //取得列宽 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 == 65535 || rowIndex == 0) { if (rowIndex != 0) { sheet = (HSSFSheet)workbook.CreateSheet(); } #region 列头及样式 { HSSFRow headerRow = (HSSFRow)sheet.CreateRow(0); HSSFCellStyle headStyle = (HSSFCellStyle)workbook.CreateCellStyle(); HSSFFont font = (HSSFFont)workbook.CreateFont(); headStyle.Alignment = HorizontalAlignment.Center; //水平居中 headStyle.VerticalAlignment = VerticalAlignment.Center; //垂直居中 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); } } #endregion rowIndex = 1; } #endregion #region 填充内容 HSSFRow dataRow = (HSSFRow)sheet.CreateRow(rowIndex); foreach (DataColumn column in dtSource.Columns) { HSSFCell newCell = (HSSFCell)dataRow.CreateCell(column.Ordinal); newCell.CellStyle.Alignment = HorizontalAlignment.Center; newCell.CellStyle.VerticalAlignment = VerticalAlignment.Center;//垂直居中 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; } } #endregion rowIndex++; } using (MemoryStream ms = new MemoryStream()) { workbook.Write(ms); ms.Flush(); ms.Position = 0; //sheet.Dispose(); return(ms); } }
/// <summary> /// DataTable导出到Excel的MemoryStream /// </summary> /// <param name="dtSource">源DataTable</param> /// <param name="html">表头html结构</param> /// <param name="dateFormat">时间格式(默认:yyyy-MM-dd)</param> private static MemoryStream DataTableToComplexExcel(DataTable dtSource, string html, string dateFormat) { HSSFWorkbook workbook = new HSSFWorkbook(); HSSFSheet sheet = (HSSFSheet)workbook.CreateSheet(); HSSFCellStyle dateStyle = (HSSFCellStyle)workbook.CreateCellStyle(); HSSFDataFormat format = (HSSFDataFormat)workbook.CreateDataFormat(); dateStyle.DataFormat = format.GetFormat(dateFormat); //设置单元格样式及字体 HSSFCellStyle cellStyle = (HSSFCellStyle)workbook.CreateCellStyle(); HSSFFont font = (HSSFFont)workbook.CreateFont(); font.FontHeightInPoints = 10; //字体大小 font.Boldweight = 700; //字体粗细 cellStyle.SetFont(font); cellStyle.Alignment = HorizontalAlignment.Center; //水平居中 cellStyle.VerticalAlignment = VerticalAlignment.Center; //垂直居中 //取得列宽 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 == 65535 || rowIndex == 0) { if (rowIndex != 0) { sheet = (HSSFSheet)workbook.CreateSheet(); } #region 列头及样式 { //获取页面html并对tr进行筛选 MatchCollection rowCollection = Regex.Matches(html, @"<tr[^>]*>[\s\S]*?<\/tr>", RegexOptions.IgnoreCase | RegexOptions.ExplicitCapture); //写在tr循环中 for (int i = 0; i < rowCollection.Count; i++) { HSSFRow rowTitle = (HSSFRow)sheet.CreateRow(i); string rowContent = rowCollection[i].Value; //对td进行筛选 MatchCollection columnCollection = Regex.Matches(rowContent, @"<td[^>]*>[\s\S]*?<\/td>", RegexOptions.IgnoreCase | RegexOptions.ExplicitCapture); //遍历td for (int j = 0; j < columnCollection.Count; j++) { var match = Regex.Match(columnCollection[j].Value, "<td.*?rowspan=\"(?<row>.*?)\".*?colspan=\"(?<col>.*?)\".*?row=\"(?<row1>.*?)\".*?col=\"(?<col1>.*?)\".*?class=\"(?<class>.*?)\">(?<value>.*?)<\\/td>", RegexOptions.IgnoreCase | RegexOptions.ExplicitCapture); if (match.Success) { int rowspan = Convert.ToInt32(match.Groups["row"].Value); //表格跨行 int colspan = Convert.ToInt32(match.Groups["col"].Value); //表格跨列 int rowNo = Convert.ToInt32(match.Groups["row1"].Value); //所在行 int colNo = Convert.ToInt32(match.Groups["col1"].Value); //所在列 string value = match.Groups["value"].Value; if (colspan == 1) //判断是否跨列 { var cell = rowTitle.CreateCell(colNo); //创建列 cell.SetCellValue(value); //设置列的值 cell.CellStyle = cellStyle; if (value.Length > 0) { int width = value.Length * 25 / 6; if (width > 255) { width = 250; } sheet.SetColumnWidth(colNo, width * 256); } } //判断是否跨行、跨列 if (rowspan > 1 || colspan > 1) { int firstRow = 0, lastRow = 0, firstCol = 0, lastCol = 0; if (rowspan > 1)//跨行 { firstRow = rowNo; lastRow = firstRow + rowspan - 1; } else { firstRow = lastRow = i; } if (colspan > 1)//跨列 { firstCol = colNo; int cols = colNo + colspan; for (; colNo < cols; colNo++) { var cell = rowTitle.CreateCell(colNo); cell.SetCellValue(value); cell.CellStyle = cellStyle; } lastCol = colNo - 1; } else { firstCol = lastCol = colNo; } //设置起始行数,结束行数,起始列数,结束列数 sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(firstRow, lastRow, firstCol, lastCol)); } } } } rowIndex = rowCollection.Count; } #endregion } #endregion #region 填充内容 HSSFRow dataRow = (HSSFRow)sheet.CreateRow(rowIndex); foreach (DataColumn column in dtSource.Columns) { HSSFCell newCell = (HSSFCell)dataRow.CreateCell(column.Ordinal); newCell.CellStyle.Alignment = HorizontalAlignment.Center; //水平居中; newCell.CellStyle.VerticalAlignment = VerticalAlignment.Center; //垂直居中 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; } } #endregion rowIndex++; } using (MemoryStream ms = new MemoryStream()) { workbook.Write(ms); ms.Flush(); ms.Position = 0; //sheet.Dispose(); return(ms); } }
public ActionResult ExportData(Pagination pagination, string queryJson) { try { pagination.page = 1; pagination.rows = 100000000; pagination.p_kid = "v_info.userid"; pagination.p_fields = @""; pagination.p_tablename = "v_userinfo v_info left join base_department d on v_info.departmentid = d.departmentid left join (select * from HJB_PERSONSET where MODULETYPE = 1) t on v_info.userid = t.userid"; pagination.conditionJson = " Account!='System'"; pagination.sidx = "DEPTSORTss,v_info.DEPTSORT asc,v_info.deptcode asc,v_info.userid"; pagination.sord = "desc"; var data = hikinoutlogbll.GetTableUserRole(pagination, queryJson); var queryParam = queryJson.ToJObject(); foreach (DataRow dr in data.Rows) { if (dr["nature"].ToString() == "专业" || dr["nature"].ToString() == "班组") { //DataTable dt = departmentBLL.GetDataTable(string.Format("select fullname from base_department where encode=(select encode from base_department t where instr('{0}',encode)=1 and nature='{1}' and organizeid='{2}') or encode='{0}' order by deptcode", dr["DEPARTMENTCODE"], "部门", dr["organizeid"])); //if (dt.Rows.Count > 0) //{ // string name = ""; // foreach (DataRow dr1 in dt.Rows) // { // name += dr1["fullname"].ToString() + "/"; // } // dr["dutyname"] = name.TrimEnd('/'); //} dr["dutyname"] = dr["dutyname"] + "(" + dr["DEPTNAME"] + ")"; } if (dr["userid"] != null) { string sTime = queryParam["sTime"].ToString(); string eTime = queryParam["eTime"].ToString(); string userId = dr["userid"].ToString(); string sql = string.Format(@"select COUNT(a.USERID) as num from(select DISTINCT userid, inout, devicename, CREATEDATE from bis_hikinoutlog) a left join V_USERINFO b on a.USERID = b.USERID where b.DEPTNAME is not null and a.INOUT = 0 and a.CREATEDATE >= to_date('{0}', 'yyyy-MM-dd') and a.CREATEDATE <= to_date('{1}', 'yyyy-MM-dd') and(case when(b.ISEPIBOLY = '否' and b.DEPTTYPE is null and length(b.deptcode) > 20) then(select fullname from base_department d where d.deptcode = substr(b.deptcode, 1, 20)) else b.DEPTNAME end) = (select case when v.nature = '班组' then v.parentname else v.DEPTNAME end as bmname from v_userinfo v where v.USERID = '{2}')" , sTime, eTime, userId); DataTable dt = operticketmanagerbll.GetDataTable(sql); if (dt.Rows.Count > 0) { dr["bmname"] = dr["bmname"] + "(进门总次数:" + dt.Rows[0]["num"].ToString(); } string sql2 = string.Format(@"select COUNT(a.USERID) as num from(select DISTINCT userid, inout, devicename, CREATEDATE from bis_hikinoutlog) a left join V_USERINFO b on a.USERID = b.USERID where b.DEPTNAME is not null and a.INOUT = 1 and a.CREATEDATE >= to_date('{0}', 'yyyy-MM-dd') and a.CREATEDATE <= to_date('{1}', 'yyyy-MM-dd') and(case when(b.ISEPIBOLY = '否' and b.DEPTTYPE is null and length(b.deptcode) > 20) then(select fullname from base_department d where d.deptcode = substr(b.deptcode, 1, 20)) else b.DEPTNAME end) = (select case when v.nature = '班组' then v.parentname else v.DEPTNAME end as bmname from v_userinfo v where v.USERID = '{2}')" , sTime, eTime, userId); DataTable dt2 = operticketmanagerbll.GetDataTable(sql2); if (dt2.Rows.Count > 0) { dr["bmname"] = dr["bmname"] + "出门总次数:" + dt2.Rows[0]["num"].ToString() + ")"; } } } //设置导出格式 //ExcelConfig excelconfig = new ExcelConfig(); //excelconfig.Title = "人员进出门禁数据信息"; //excelconfig.TitleFont = "微软雅黑"; //excelconfig.TitlePoint = 25; //excelconfig.FileName = "人员进出门禁数据导出.xls"; //excelconfig.IsAllSizeColumn = true; ////每一列的设置,没有设置的列信息,系统将按datatable中的列名导出 //List<ColumnEntity> listColumnEntity = new List<ColumnEntity>(); //excelconfig.ColumnEntity = listColumnEntity; //ColumnEntity columnentity = new ColumnEntity(); //excelconfig.ColumnEntity.Add(new ColumnEntity() { Column = "bmname".ToLower(), ExcelColumn = "单位(部门)" }); //excelconfig.ColumnEntity.Add(new ColumnEntity() { Column = "realname".ToLower(), ExcelColumn = "姓名" }); //excelconfig.ColumnEntity.Add(new ColumnEntity() { Column = "dutyname".ToLower(), ExcelColumn = "岗位名称" }); //excelconfig.ColumnEntity.Add(new ColumnEntity() { Column = "intnum".ToLower(), ExcelColumn = "进门次数(次)" }); //excelconfig.ColumnEntity.Add(new ColumnEntity() { Column = "outnum".ToLower(), ExcelColumn = "出门次数(次)" }); ////调用导出方法 //ExcelHelper.ExcelDownload(data, excelconfig); //导出excel string title = "人员进出门禁数据信息"; HSSFWorkbook workbook = new HSSFWorkbook();//创建Workbook对象 HSSFSheet sheet = workbook.CreateSheet("Sheet1") as HSSFSheet; sheet.DefaultRowHeight = 24 * 20; int column = data.Columns.Count; int indexRow = 0; //标题 if (!string.IsNullOrEmpty(title)) { IRow headerRow = sheet.CreateRow(indexRow); headerRow.HeightInPoints = 30; headerRow.CreateCell(0).SetCellValue(title); //合并单元格 CellRangeAddress region = new CellRangeAddress(0, 0, 0, 4); sheet.AddMergedRegion(region); ICellStyle cellstyle = workbook.CreateCellStyle(); cellstyle.VerticalAlignment = VerticalAlignment.Center; cellstyle.Alignment = HorizontalAlignment.Center; IFont font = workbook.CreateFont(); font.FontHeightInPoints = 25; font.FontName = "微软雅黑"; font.Boldweight = (short)FontBoldWeight.Bold; cellstyle.SetFont(font); var cell = sheet.GetRow(0).GetCell(0); cell.CellStyle = cellstyle; HSSFRegionUtil.SetBorderBottom(BorderStyle.Thin, region, sheet, workbook); //下边框 HSSFRegionUtil.SetBorderLeft(BorderStyle.Thin, region, sheet, workbook); //左边框 HSSFRegionUtil.SetBorderRight(BorderStyle.Thin, region, sheet, workbook); //右边框 HSSFRegionUtil.SetBorderTop(BorderStyle.Thin, region, sheet, workbook); //上边框 indexRow++; } //列头样式 ICellStyle headerStyle = workbook.CreateCellStyle(); headerStyle.Alignment = HorizontalAlignment.Center; headerStyle.VerticalAlignment = VerticalAlignment.Center; headerStyle.BorderBottom = BorderStyle.Thin; headerStyle.BorderLeft = BorderStyle.Thin; headerStyle.BorderRight = BorderStyle.Thin; headerStyle.BorderTop = BorderStyle.Thin; IFont headerFont = workbook.CreateFont(); //headerFont.FontHeightInPoints = 4; headerFont.FontName = "宋体"; headerFont.Boldweight = (short)FontBoldWeight.Bold; headerStyle.SetFont(headerFont); IRow row1 = sheet.CreateRow(indexRow); row1.CreateCell(0).SetCellValue("单位(部门)"); row1.GetCell(0).CellStyle = headerStyle; row1.CreateCell(1).SetCellValue("姓名"); row1.GetCell(1).CellStyle = headerStyle; row1.CreateCell(2).SetCellValue("岗位名称"); row1.GetCell(2).CellStyle = headerStyle; row1.CreateCell(3).SetCellValue("进门次数(次)"); row1.GetCell(3).CellStyle = headerStyle; row1.CreateCell(4).SetCellValue("出门次数(次)"); row1.GetCell(4).CellStyle = headerStyle; //普通单元格样式 ICellStyle bodyStyle = workbook.CreateCellStyle(); bodyStyle.Alignment = HorizontalAlignment.Center; bodyStyle.VerticalAlignment = VerticalAlignment.Center; IFont font1 = workbook.CreateFont(); font1.Color = HSSFColor.Black.Index; //font1.Boldweight = 25; //font1.FontHeightInPoints = 12; bodyStyle.FillForegroundColor = HSSFColor.White.Index; bodyStyle.SetFont(font1); //设置格式 IDataFormat format = workbook.CreateDataFormat(); //填充数据 for (int i = 0; i < data.Rows.Count; i++) { indexRow++; IRow rowTemp = sheet.CreateRow(indexRow); //rowTemp.Height = 62 * 20; rowTemp.CreateCell(0).SetCellValue(data.Rows[i]["bmname"].ToString()); rowTemp.CreateCell(1).SetCellValue(data.Rows[i]["realname"].ToString()); rowTemp.CreateCell(2).SetCellValue(data.Rows[i]["dutyname"].ToString()); rowTemp.CreateCell(3).SetCellValue(data.Rows[i]["intnum"].ToString()); rowTemp.CreateCell(4).SetCellValue(data.Rows[i]["outnum"].ToString()); rowTemp.GetCell(0).CellStyle = bodyStyle; rowTemp.GetCell(1).CellStyle = bodyStyle; rowTemp.GetCell(2).CellStyle = bodyStyle; rowTemp.GetCell(3).CellStyle = bodyStyle; rowTemp.GetCell(4).CellStyle = bodyStyle; } sheet.AutoSizeColumn(0); sheet.AutoSizeColumn(1); sheet.AutoSizeColumn(2); sheet.AutoSizeColumn(3); sheet.AutoSizeColumn(4); //合并单元格 MergeCells(sheet, data, 0, 0, 0); MemoryStream ms = new MemoryStream(); workbook.Write(ms); ms.Seek(0, SeekOrigin.Begin); return(File(ms, "application/vnd.ms-excel", title + ".xls")); } catch (Exception ex) { } return(Success("导出成功。")); }
/// <summary> /// Excel导出文件流(服务项目调用) /// </summary> /// <param name="dtSource">DataTable数据源</param> /// <param name="excelHeadTitle">文件第一行标题</param> public static MemoryStream GetExcelStream(DataTable dtSource, string excelHeadTitle = "") { HSSFWorkbook workbook = new HSSFWorkbook(); ExcelInformation information = new ExcelInformation(); ISheet sheet = workbook.CreateSheet(); #region 右击文件 属性信息 { DocumentSummaryInformation dsi = PropertySetFactory.CreateDocumentSummaryInformation(); dsi.Company = information.Company; workbook.DocumentSummaryInformation = dsi; SummaryInformation si = PropertySetFactory.CreateSummaryInformation(); si.Author = information.Author; //填加xls文件作者信息 si.ApplicationName = information.ApplicationName; //填加xls文件创建程序信息 si.LastAuthor = information.LastAuthor; //填加xls文件最后保存者信息 si.Comments = information.Comments; //填加xls文件作者信息 si.Title = information.Title; //填加xls文件标题信息 si.Subject = information.Subject; //填加文件主题信息 si.CreateDateTime = DateTime.Now; workbook.SummaryInformation = si; } #endregion ICellStyle dateStyle = workbook.CreateCellStyle(); IDataFormat format = workbook.CreateDataFormat(); dateStyle.DataFormat = format.GetFormat("yyyy-MM-dd HH:mm:ss"); //取得列宽 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 == 65535 || rowIndex == 0) { if (rowIndex != 0) { sheet = workbook.CreateSheet(); } #region 表头及样式 if (!string.IsNullOrWhiteSpace(excelHeadTitle)) { IRow headerRow = sheet.CreateRow(0); headerRow.HeightInPoints = 25; headerRow.CreateCell(0).SetCellValue(excelHeadTitle); ICellStyle headStyle = workbook.CreateCellStyle(); headStyle.Alignment = HorizontalAlignment.Center; IFont font = workbook.CreateFont(); font.FontHeightInPoints = 20; font.IsBold = true; headStyle.SetFont(font); headerRow.GetCell(0).CellStyle = headStyle; sheet.AddMergedRegion(new CellRangeAddress(0, 0, 0, dtSource.Columns.Count - 1)); } #endregion #region 列头及样式 { IRow headerRow = string.IsNullOrWhiteSpace(excelHeadTitle) ? sheet.CreateRow(0) : sheet.CreateRow(1); ICellStyle headStyle = workbook.CreateCellStyle(); headStyle.Alignment = HorizontalAlignment.Center; IFont font = workbook.CreateFont(); font.FontHeightInPoints = 10; font.IsBold = true; //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; var arr = (arrColWidth[column.Ordinal] + 1) * 256; //限定宽度 if (arrColWidth[column.Ordinal] > 100) { arrColWidth[column.Ordinal] = 100; } //设置列宽 sheet.SetColumnWidth(column.Ordinal, (arrColWidth[column.Ordinal] + 1) * 256); } } #endregion rowIndex = string.IsNullOrWhiteSpace(excelHeadTitle) ? 1 : 2; } #endregion #region 填充内容 IRow dataRow = sheet.CreateRow(rowIndex); foreach (DataColumn column in dtSource.Columns) { ICell newCell = dataRow.CreateCell(column.Ordinal); string drValue = row[column].ToString(); switch (column.DataType.BaseType.FullName) { case "System.Enum": //枚举类型 int intV = 0; int.TryParse(drValue, out intV); newCell.SetCellValue(intV); continue; } 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": //long intV = 0; //long.TryParse(drValue, out intV); newCell.SetCellValue(drValue); 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(); return(ms); } }
/// <summary> /// 生成模板 /// </summary> /// <param name="displayName">文件名</param> /// <returns>生成的模版文件</returns> public byte[] GenerateTemplate(out string displayName) { HSSFWorkbook workbook = new HSSFWorkbook(); InitExcelData(); CreateDataTable(); //add by dufei SetTemplateDataValus(); //add by dufei if (!string.IsNullOrEmpty(FileDisplayName)) { displayName = FileDisplayName + "_" + DateTime.Now.ToString("yyyy-MM-dd") + "_" + DateTime.Now.ToString("hh^mm^ss") + ".xls"; } else { displayName = this.GetType().Name + "_" + DateTime.Now.ToString("yyyy-MM-dd") + "_" + DateTime.Now.ToString("hh^mm^ss") + ".xls"; } //模板sheet页 HSSFSheet sheet = (HSSFSheet)workbook.CreateSheet(); workbook.SetSheetName(0, string.IsNullOrEmpty(FileDisplayName) ? this.GetType().Name : FileDisplayName); HSSFRow row = (HSSFRow)sheet.CreateRow(0); row.HeightInPoints = 20; HSSFSheet enumSheet = (HSSFSheet)workbook.CreateSheet(); HSSFRow enumSheetRow1 = (HSSFRow)enumSheet.CreateRow(0); enumSheetRow1.CreateCell(0).SetCellValue("是"); enumSheetRow1.CreateCell(1).SetCellValue("否"); enumSheetRow1.CreateCell(2).SetCellValue(this.GetType().Name); //为模板添加标记,必要时可添加版本号 HSSFSheet dataSheet = (HSSFSheet)workbook.CreateSheet(); #region 设置excel模板列头 //默认灰色 var headerStyle = GetCellStyle(workbook); headerStyle.IsLocked = true; //黄色 var yellowStyle = GetCellStyle(workbook, BackgroudColorEnum.Yellow); yellowStyle.IsLocked = true; //红色 var redStyle = GetCellStyle(workbook, BackgroudColorEnum.Red); redStyle.IsLocked = true; //取得所有ExcelPropety var propetys = this.GetType().GetFields().Where(x => x.FieldType == typeof(ExcelPropety)).ToList(); int _currentColunmIndex = 0; bool IsProtect = false; for (int porpetyIndex = 0; porpetyIndex < propetys.Count(); porpetyIndex++) { ExcelPropety excelPropety = (ExcelPropety)propetys[porpetyIndex].GetValue(this); ColumnDataType dateType = excelPropety.DataType; if (excelPropety.ReadOnly) { IsProtect = true; } //给必填项加星号 string colName = excelPropety.IsNullAble ? excelPropety.ColumnName : excelPropety.ColumnName + "*"; row.CreateCell(_currentColunmIndex).SetCellValue(colName); //修改列头样式 switch (excelPropety.BackgroudColor) { case BackgroudColorEnum.Yellow: row.Cells[_currentColunmIndex].CellStyle = yellowStyle; break; case BackgroudColorEnum.Red: row.Cells[_currentColunmIndex].CellStyle = redStyle; break; default: row.Cells[_currentColunmIndex].CellStyle = headerStyle; break; } var dataStyle = workbook.CreateCellStyle(); var dataFormat = workbook.CreateDataFormat(); if (dateType == ColumnDataType.Dynamic) { int dynamicColCount = excelPropety.DynamicColumns.Count(); for (int dynamicColIndex = 0; dynamicColIndex < dynamicColCount; dynamicColIndex++) { var dynamicCol = excelPropety.DynamicColumns.ToList()[dynamicColIndex]; string dynamicColName = excelPropety.IsNullAble ? dynamicCol.ColumnName : dynamicCol.ColumnName + "*"; row.CreateCell(_currentColunmIndex).SetCellValue(dynamicColName); row.Cells[_currentColunmIndex].CellStyle = headerStyle; if (dynamicCol.ReadOnly) { IsProtect = true; } //设定列宽 if (excelPropety.CharCount > 0) { sheet.SetColumnWidth(_currentColunmIndex, excelPropety.CharCount * 256); dataStyle.WrapText = true; } else { sheet.AutoSizeColumn(_currentColunmIndex); } //设置单元格样式及数据类型 dataStyle.IsLocked = excelPropety.ReadOnly; dynamicCol.SetColumnFormat(dynamicCol.DataType, _currentColunmIndex, sheet, dataSheet, dataStyle, dataFormat); _currentColunmIndex++; } } else { //设定列宽 if (excelPropety.CharCount > 0) { sheet.SetColumnWidth(_currentColunmIndex, excelPropety.CharCount * 256); dataStyle.WrapText = true; } else { sheet.AutoSizeColumn(_currentColunmIndex); } //设置是否锁定 dataStyle.IsLocked = excelPropety.ReadOnly; //设置单元格样式及数据类型 excelPropety.SetColumnFormat(dateType, _currentColunmIndex, sheet, dataSheet, dataStyle, dataFormat); _currentColunmIndex++; } } #endregion #region 添加模版数据 add by dufei if (TemplateDataTable.Rows.Count > 0) { for (int i = 0; i < TemplateDataTable.Rows.Count; i++) { DataRow tableRow = TemplateDataTable.Rows[i]; HSSFRow dataRow = (HSSFRow)sheet.CreateRow(1 + i); for (int porpetyIndex = 0; porpetyIndex < propetys.Count(); porpetyIndex++) { string colName = propetys[porpetyIndex].Name; tableRow[colName].ToString(); dataRow.CreateCell(porpetyIndex).SetCellValue(tableRow[colName].ToString()); } } } #endregion //冻结行 sheet.CreateFreezePane(0, 1, 0, 1); //锁定excel if (IsProtect) { sheet.ProtectSheet("password"); } workbook.SetSheetHidden(1, true); workbook.SetSheetHidden(2, true); MemoryStream ms = new MemoryStream(); workbook.Write(ms); return(ms.ToArray()); }
private void generate_my_data() { ISheet sheet0 = hssfworkbook.CreateSheet("Ruch spraw");//* DataView view = (DataView)dane_do_tabeli_1.Select(DataSourceSelectArguments.Empty); DataTable table = view.ToTable(); DataTable dT = (DataTable)Session["header_01"]; table.TableName = "Załatwienia"; table.Columns.Remove("id_"); table.Columns.Remove("id_tabeli"); table.Columns.Remove("d_17"); table.Columns.Remove("d_18"); table.Columns.Remove("d_19"); table.Columns.Remove("d_20"); var crs = new NPOI.SS.Util.CellRangeAddress(0, 0, 0, 0); IRow row0 = sheet0.CreateRow(0); #region tabela1 foreach (DataRow dR in getData(dT, "Column1=3")) { string text = dR[1].ToString().Trim(); int exc1 = int.Parse(dR[4].ToString().Trim()); int exc2 = int.Parse(dR[5].ToString().Trim()); int exc3 = int.Parse(dR[6].ToString().Trim()); int exc4 = int.Parse(dR[7].ToString().Trim()); row0.CreateCell(exc3).SetCellValue(text); if ((exc1 != exc2) || (exc3 != exc4)) { crs = new NPOI.SS.Util.CellRangeAddress(exc1, exc2, exc3, exc4); sheet0.AddMergedRegion(crs); } } row0 = sheet0.CreateRow(1); foreach (DataRow dR in getData(dT, "Column1=2")) { string text = dR[1].ToString().Trim(); int exc1 = int.Parse(dR[4].ToString().Trim()); int exc2 = int.Parse(dR[5].ToString().Trim()); int exc3 = int.Parse(dR[6].ToString().Trim()); int exc4 = int.Parse(dR[7].ToString().Trim()); row0.CreateCell(exc3).SetCellValue(text); if ((exc1 != exc2) || (exc3 != exc4)) { crs = new NPOI.SS.Util.CellRangeAddress(exc1 + 1, exc2 + 1, exc3, exc4); sheet0.AddMergedRegion(crs); } } row0 = sheet0.CreateRow(2); foreach (DataRow dR in getData(dT, "Column1=1")) { string text = dR[1].ToString().Trim(); int exc1 = int.Parse(dR[4].ToString().Trim()); int exc2 = int.Parse(dR[5].ToString().Trim()); int exc3 = int.Parse(dR[6].ToString().Trim()); int exc4 = int.Parse(dR[7].ToString().Trim()); row0.CreateCell(exc3).SetCellValue(text); if ((exc1 != exc2) || (exc3 != exc4)) { crs = new NPOI.SS.Util.CellRangeAddress(exc1 + 2, exc2 + 2, exc3, exc4); sheet0.AddMergedRegion(crs); } } int rol = 3; foreach (DataRow rowik in table.Rows) { row0 = sheet0.CreateRow(rol); for (int i = 0; i < rowik.ItemArray.Length; i++) { try { int ji = int.Parse(rowik[i].ToString().Trim()); ICellStyle cellStyle = hssfworkbook.CreateCellStyle(); cellStyle.DataFormat = HSSFDataFormat.GetBuiltinFormat("0"); row0.CreateCell(i).SetCellValue(ji); row0.Cells[i].CellStyle = cellStyle; } catch (Exception) { row0.CreateCell(i).SetCellValue(rowik[i].ToString().Trim()); } } rol++; }// end foreach #endregion tabela1 #region drugi arkusz // druga tabela view = (DataView)dane_do_tabeli_2.Select(DataSourceSelectArguments.Empty); table = view.ToTable(); table.TableName = "Załatwienia"; table.Columns.Remove("ident"); table.Columns.Remove("sesja"); table.Columns.Remove("id_sedziego"); table.Columns.Remove("id_tabeli"); table.Columns.Remove("id_dzialu"); table.Columns.Remove("d_12"); table.Columns.Remove("d_13"); table.Columns.Remove("d_14"); table.Columns.Remove("d_15"); table.Columns.Remove("d_16"); table.Columns.Remove("d_17"); table.Columns.Remove("d_18"); table.Columns.Remove("d_19"); table.Columns.Remove("d_20"); table.Columns.Remove("d_21"); table.Columns.Remove("d_22"); // //robienie int ro = 2; //----------------- IDataFormat format = hssfworkbook.CreateDataFormat(); ISheet sheet1 = hssfworkbook.CreateSheet("Załatwienia"); IRow row2 = sheet1.CreateRow(0); dT.Clear(); dT = (DataTable)Session["header_02"]; //=========== foreach (DataRow dR in getData(dT, "Column1=3")) { string text = dR[1].ToString().Trim(); int exc1 = int.Parse(dR[4].ToString().Trim()); int exc2 = int.Parse(dR[5].ToString().Trim()); int exc3 = int.Parse(dR[6].ToString().Trim()); int exc4 = int.Parse(dR[7].ToString().Trim()); row2.CreateCell(exc3).SetCellValue(text); if ((exc1 != exc2) || (exc3 != exc4)) { crs = new NPOI.SS.Util.CellRangeAddress(exc1, exc2, exc3, exc4); sheet0.AddMergedRegion(crs); } } row2 = sheet0.CreateRow(1); foreach (DataRow dR in getData(dT, "Column1=2")) { string text = dR[1].ToString().Trim(); int exc1 = int.Parse(dR[4].ToString().Trim()); int exc2 = int.Parse(dR[5].ToString().Trim()); int exc3 = int.Parse(dR[6].ToString().Trim()); int exc4 = int.Parse(dR[7].ToString().Trim()); row2.CreateCell(exc3).SetCellValue(text); if ((exc1 != exc2) || (exc3 != exc4)) { crs = new NPOI.SS.Util.CellRangeAddress(exc1 + 1, exc2 + 1, exc3, exc4); sheet0.AddMergedRegion(crs); } } row2 = sheet0.CreateRow(2); foreach (DataRow dR in getData(dT, "Column1=1")) { string text = dR[1].ToString().Trim(); int exc1 = int.Parse(dR[4].ToString().Trim()); int exc2 = int.Parse(dR[5].ToString().Trim()); int exc3 = int.Parse(dR[6].ToString().Trim()); int exc4 = int.Parse(dR[7].ToString().Trim()); row2.CreateCell(exc3).SetCellValue(text); if ((exc1 != exc2) || (exc3 != exc4)) { crs = new NPOI.SS.Util.CellRangeAddress(exc1 + 2, exc2 + 2, exc3, exc4); sheet0.AddMergedRegion(crs); } } rol = 3; foreach (DataRow rowik in table.Rows) { row2 = sheet0.CreateRow(rol); for (int i = 0; i < rowik.ItemArray.Length; i++) { try { int ji = int.Parse(rowik[i].ToString().Trim()); ICellStyle cellStyle = hssfworkbook.CreateCellStyle(); cellStyle.DataFormat = HSSFDataFormat.GetBuiltinFormat("0"); row2.CreateCell(i).SetCellValue(ji); row2.Cells[i].CellStyle = cellStyle; } catch (Exception) { row2.CreateCell(i).SetCellValue(rowik[i].ToString().Trim()); } } rol++; }// end foreach foreach (DataRow rowik in table.Rows) { row2 = sheet1.CreateRow(ro); for (int i = 0; i < rowik.ItemArray.Length; i++) { try { int ji = int.Parse(rowik[i].ToString().Trim()); ICellStyle cellStyle = hssfworkbook.CreateCellStyle(); cellStyle.DataFormat = HSSFDataFormat.GetBuiltinFormat("0"); row2.CreateCell(i).SetCellValue(ji); row2.Cells[i].CellStyle = cellStyle; } catch (Exception) { row2.CreateCell(i).SetCellValue(rowik[i].ToString().Trim()); } } ro++; }// end foreach #endregion drugi arkusz // trzeci sheet view = (DataView)tabela_3.Select(DataSourceSelectArguments.Empty); table = view.ToTable(); table.Columns.Remove("ident"); table.Columns.Remove("sesja"); table.Columns.Remove("id_sedziego"); table.Columns.Remove("id_tabeli"); table.Columns.Remove("id_dzialu"); table.Columns.Remove("d_10"); table.Columns.Remove("d_11"); table.Columns.Remove("d_12"); table.Columns.Remove("d_13"); table.Columns.Remove("d_14"); table.Columns.Remove("d_15"); table.Columns.Remove("d_16"); table.Columns.Remove("d_17"); table.Columns.Remove("d_18"); table.Columns.Remove("d_19"); table.Columns.Remove("d_20"); table.Columns.Remove("d_21"); table.Columns.Remove("d_22"); sheet1.AutoSizeColumn(0, true); sheet1.AutoSizeColumn(1, true); ISheet sheet2 = hssfworkbook.CreateSheet("Wyznaczenia"); row2 = sheet2.CreateRow(0); row2.CreateCell(0).SetCellValue("L.p."); row2.CreateCell(1).SetCellValue("Nazwisko"); row2.CreateCell(2).SetCellValue("Imię"); row2.CreateCell(3).SetCellValue("Funkcja"); row2.CreateCell(4).SetCellValue("Stanowisko"); crs = new NPOI.SS.Util.CellRangeAddress(0, 1, 0, 0); sheet2.AddMergedRegion(crs); crs = new NPOI.SS.Util.CellRangeAddress(0, 1, 1, 1); sheet2.AddMergedRegion(crs); crs = new NPOI.SS.Util.CellRangeAddress(0, 1, 2, 2); sheet2.AddMergedRegion(crs); crs = new NPOI.SS.Util.CellRangeAddress(0, 1, 3, 3); sheet2.AddMergedRegion(crs); crs = new NPOI.SS.Util.CellRangeAddress(0, 1, 4, 4); sheet2.AddMergedRegion(crs); row2.CreateCell(5).SetCellValue("Wyznaczenia"); crs = new NPOI.SS.Util.CellRangeAddress(0, 0, 5, 12); sheet2.AddMergedRegion(crs); row2 = sheet2.CreateRow(1); row2.CreateCell(5).SetCellValue("GU bez ''of''"); row2.CreateCell(6).SetCellValue("C-GC"); row2.CreateCell(7).SetCellValue("GU ''of''"); row2.CreateCell(8).SetCellValue("GU Razem"); row2.CreateCell(9).SetCellValue("GUp bez '''of'"); row2.CreateCell(10).SetCellValue("GUp ''of''"); row2.CreateCell(11).SetCellValue("WSC"); row2.CreateCell(12).SetCellValue("Razem"); row2.CreateCell(13).SetCellValue("Odroczenia liczba spraw odroczonych"); ro = 2; foreach (DataRow rowik in table.Rows) { row2 = sheet2.CreateRow(ro); for (int i = 0; i < rowik.ItemArray.Length; i++) { try { int ji = int.Parse(rowik[i].ToString().Trim()); ICellStyle cellStyle = hssfworkbook.CreateCellStyle(); cellStyle.DataFormat = HSSFDataFormat.GetBuiltinFormat("0"); row2.CreateCell(i).SetCellValue(ji); row2.Cells[i].CellStyle = cellStyle; } catch (Exception) { row2.CreateCell(i).SetCellValue(rowik[i].ToString().Trim()); } } ro++; }// end foreach // czwarty sheet view = (DataView)tabela_4.Select(DataSourceSelectArguments.Empty); table = view.ToTable(); table.Columns.Remove("ident"); table.Columns.Remove("sesja"); table.Columns.Remove("id_sedziego"); table.Columns.Remove("id_tabeli"); //table.Columns.Remove("id_dzialu"); table.Columns.Remove("d_09"); table.Columns.Remove("d_10"); table.Columns.Remove("d_11"); table.Columns.Remove("d_12"); table.Columns.Remove("d_13"); table.Columns.Remove("d_14"); table.Columns.Remove("d_15"); table.Columns.Remove("d_16"); table.Columns.Remove("d_17"); table.Columns.Remove("d_18"); table.Columns.Remove("d_19"); table.Columns.Remove("d_20"); table.Columns.Remove("d_21"); table.Columns.Remove("d_22"); ISheet sheet3 = hssfworkbook.CreateSheet("Stan referatów sędziów"); row2 = sheet3.CreateRow(0); row2.CreateCell(0).SetCellValue("L.p."); row2.CreateCell(1).SetCellValue("Nazwisko"); row2.CreateCell(2).SetCellValue("Imię"); row2.CreateCell(3).SetCellValue("Funkcja"); row2.CreateCell(4).SetCellValue("Stanowisko"); crs = new NPOI.SS.Util.CellRangeAddress(0, 1, 0, 0); sheet3.AddMergedRegion(crs); crs = new NPOI.SS.Util.CellRangeAddress(0, 1, 1, 1); sheet3.AddMergedRegion(crs); crs = new NPOI.SS.Util.CellRangeAddress(0, 1, 2, 2); sheet3.AddMergedRegion(crs); crs = new NPOI.SS.Util.CellRangeAddress(0, 1, 3, 3); sheet3.AddMergedRegion(crs); crs = new NPOI.SS.Util.CellRangeAddress(0, 1, 4, 4); sheet3.AddMergedRegion(crs); row2.CreateCell(5).SetCellValue("Pozostało w referatach spraw kategorii"); crs = new NPOI.SS.Util.CellRangeAddress(0, 0, 5, 12); sheet3.AddMergedRegion(crs); row2 = sheet3.CreateRow(1); row2.CreateCell(5).SetCellValue("GU bez ''of''"); row2.CreateCell(6).SetCellValue("C-GC"); row2.CreateCell(7).SetCellValue("GU ''of''"); row2.CreateCell(8).SetCellValue("GU Razem"); row2.CreateCell(9).SetCellValue("GUp bez '''of'"); row2.CreateCell(10).SetCellValue("GUp ''of''"); row2.CreateCell(11).SetCellValue("WSC"); row2.CreateCell(12).SetCellValue("Razem"); // row2.CreateCell(12).SetCellValue("Odroczenia liczba spraw odroczonych"); ro = 2; foreach (DataRow rowik in table.Rows) { row2 = sheet3.CreateRow(ro); for (int i = 0; i < rowik.ItemArray.Length; i++) { try { int ji = int.Parse(rowik[i].ToString().Trim()); ICellStyle cellStyle = hssfworkbook.CreateCellStyle(); cellStyle.DataFormat = HSSFDataFormat.GetBuiltinFormat("0"); row2.CreateCell(i).SetCellValue(ji); row2.Cells[i].CellStyle = cellStyle; } catch (Exception) { row2.CreateCell(i).SetCellValue(rowik[i].ToString().Trim()); } } ro++; }// end foreach }
/// <summary> /// 设置日期格式 /// </summary> /// <param name="workbook"></param> /// <returns></returns> private static short SetDateFormat(this HSSFWorkbook workbook) { HSSFDataFormat format = workbook.CreateDataFormat(); return(format.GetFormat("yyyy-mm-dd")); }
/// <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.ToCstTime(); 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>DataSet导出到Excel的MemoryStream</summary> /// <param name="ds">源DataSet</param> /// <param name="strHeaderTexts">表格头文本值集合</param> /// <param name="sheetCombineColIndexs">每个表格的要垂直合并的列的序号如:{"0,1","2"}表示表1的第0和1列进行合并,表2的第2列进行合并</param> /// <returns></returns> public static MemoryStream ExportDS(DataSet ds, List <string> strHeaderTexts, List <string> sheetCombineColIndexs) { HSSFWorkbook workbook = new HSSFWorkbook(); HSSFSheet sheet = null; #region 右击文件 属性信息 { DocumentSummaryInformation dsi = PropertySetFactory.CreateDocumentSummaryInformation(); dsi.Company = "http://www.jack.com/"; workbook.DocumentSummaryInformation = dsi; SummaryInformation si = PropertySetFactory.CreateSummaryInformation(); si.Author = "Jack"; //填加xls文件作者信息 si.ApplicationName = "jackExcel"; //填加xls文件创建程序信息 si.LastAuthor = "Jack"; //填加xls文件最后保存者信息 si.Comments = "Jack导出的excel"; //填加xls文件作者信息 si.Title = "Jack导出的excel"; //填加xls文件标题信息 si.Subject = "Jack导出的excel"; //填加文件主题信息 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 HH:mm:ss.fff"); for (int i = 0; i < ds.Tables.Count; i++) { #region 填充单个sheet int contentRowStartIndex = 0;//表格内容的内容数据起始行,用于合并同列多行之间的合并 sheet = workbook.CreateSheet(ds.Tables[i].TableName.StartsWith("Table") ? "Sheet" + (i + 1).ToString() : ds.Tables[i].TableName) as HSSFSheet; sheet.DefaultRowHeight = 22 * 20; DataTable dtSource = ds.Tables[i]; //取得列宽 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 ii = 0; ii < dtSource.Rows.Count; ii++) { //遍历数据内容,根据每一列的数据最大长度设置列宽 for (int j = 0; j < dtSource.Columns.Count; j++) { int intTemp = Encoding.GetEncoding(936).GetBytes(dtSource.Rows[ii][j].ToString()).Length; if (intTemp > arrColWidth[j]) { arrColWidth[j] = intTemp; } } } #region 填充表头,列头,数据内容 int rowIndex = 0; foreach (DataRow row in dtSource.Rows) { #region 新建表,填充表头,填充列头,样式 if (rowIndex == 0) { #region 表头及样式 if (strHeaderTexts != null && strHeaderTexts.Count - 1 >= i) { if (!string.IsNullOrWhiteSpace(strHeaderTexts[i])) { HSSFRow headerRow = sheet.CreateRow(0) as HSSFRow; headerRow.HeightInPoints = 25; headerRow.CreateCell(0).SetCellValue(strHeaderTexts[i]); HSSFCellStyle headStyle = workbook.CreateCellStyle() as HSSFCellStyle; headStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center; HSSFFont font = workbook.CreateFont() as HSSFFont; font.FontHeightInPoints = 15; font.Boldweight = 700; headStyle.SetFont(font); headerRow.GetCell(0).CellStyle = headStyle; sheet.AddMergedRegion(new Region(0, 0, 0, dtSource.Columns.Count - 1)); rowIndex++; //headerRow.Dispose(); } } #endregion #region 列头及样式 { HSSFRow headerRow = sheet.CreateRow(rowIndex) as HSSFRow; HSSFCellStyle headStyle = workbook.CreateCellStyle() as HSSFCellStyle; headStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center; headStyle.VerticalAlignment = NPOI.SS.UserModel.VerticalAlignment.Center; headerRow.HeightInPoints = 23; HSSFFont font = workbook.CreateFont() as HSSFFont; font.FontHeightInPoints = 11; 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; //设置列宽,这里我多加了10个字符的长度 sheet.SetColumnWidth(column.Ordinal, (arrColWidth[column.Ordinal] + 10) * 256); } rowIndex++; contentRowStartIndex = rowIndex;//记住数据内容的起始行 //headerRow.Dispose(); } #endregion } #endregion #region 填充内容 HSSFRow dataRow = sheet.CreateRow(rowIndex) as HSSFRow; dataRow.HeightInPoints = 22; 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++; } #endregion Hashtable ht = new Hashtable(); #region 列中多行之间的合并 if (sheetCombineColIndexs != null && sheetCombineColIndexs.Count > i) { List <int> combineColIndexs = new List <int>(); string[] strarr = sheetCombineColIndexs[i].Split(new char[] { ',' }, StringSplitOptions.RemoveEmptyEntries); foreach (var item in strarr) { combineColIndexs.Add(int.Parse(item)); } for (int j = contentRowStartIndex; j < rowIndex; j++) { for (int jj = 0; jj < combineColIndexs.Count; jj++) { int cloIndex = combineColIndexs[jj]; if (j == contentRowStartIndex) { Entry entry = new Entry(); entry.startIndex = contentRowStartIndex; object o = GetCellValue(sheet.GetRow(contentRowStartIndex).Cells[cloIndex]); entry.combineValue = o == null ? "" : o.ToString(); ht.Add(cloIndex, entry); continue; } object obj = GetCellValue(sheet.GetRow(j).Cells[cloIndex]); string value = obj == null ? "" : obj.ToString(); Entry en = (Entry)ht[cloIndex]; if (en.combineValue != value) { //如果发生不相等的情况则满足合并条件(最少是2行)就会合并 if (en.startIndex + 1 < j) { sheet.AddMergedRegion(new Region(en.startIndex, cloIndex, j - 1, cloIndex)); ICell cell = sheet.GetRow(en.startIndex).Cells[cloIndex]; ICellStyle cellstyle = workbook.CreateCellStyle(); //设置垂直居中格式 cellstyle.VerticalAlignment = VerticalAlignment.Center; //垂直居中 cell.CellStyle = cellstyle; } en.combineValue = value; en.startIndex = j; } else { //如果相等了,再判断是不是最后一行,如果是最后一行也要合并 if (j == rowIndex - 1) { sheet.AddMergedRegion(new Region(en.startIndex, cloIndex, j, cloIndex)); ICell cell = sheet.GetRow(en.startIndex).Cells[cloIndex]; ICellStyle cellstyle = workbook.CreateCellStyle(); //设置垂直居中格式 cellstyle.VerticalAlignment = VerticalAlignment.Center; //垂直居中 cell.CellStyle = cellstyle; } } } } } #endregion #endregion } 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> public static MemoryStream NpoiExport(DataTable dtSource, string strHeaderText) { HSSFWorkbook workbook = new HSSFWorkbook(); ISheet sheet = workbook.CreateSheet(); ICellStyle dateStyle = workbook.CreateCellStyle(); IDataFormat format = workbook.CreateDataFormat(); dateStyle.DataFormat = format.GetFormat("yyyy-MM-dd"); #region 取得每列的列宽(最大宽度) int[] arrColWidth = new int[dtSource.Columns.Count]; foreach (DataColumn item in dtSource.Columns) { //GBK对应的code page是CP936 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; } } } #endregion int rowIndex = 0; foreach (DataRow row in dtSource.Rows) { #region 新建表,填充表头,填充列头,样式 if (rowIndex == 65535 || rowIndex == 0) { if (rowIndex != 0) { sheet = workbook.CreateSheet(); } #region 表头及样式 { IRow headerRow = sheet.CreateRow(0); headerRow.HeightInPoints = 25; headerRow.CreateCell(0).SetCellValue(strHeaderText); ICellStyle headStyle = workbook.CreateCellStyle(); headStyle.Alignment = HorizontalAlignment.Center; IFont font = workbook.CreateFont(); font.FontHeightInPoints = 12; font.Boldweight = 700; headStyle.SetFont(font); headerRow.GetCell(0).CellStyle = headStyle; sheet.AddMergedRegion(new CellRangeAddress(0, 0, 0, dtSource.Columns.Count - 1)); } #endregion #region 列头及样式 { IRow headerRow = sheet.CreateRow(1); ICellStyle headStyle = workbook.CreateCellStyle(); headStyle.Alignment = HorizontalAlignment.Center; IFont font = 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; //设置列宽 sheet.SetColumnWidth(column.Ordinal, (arrColWidth[column.Ordinal] + 1) * 256); } } #endregion rowIndex = 2; } #endregion #region 填充内容 ICellStyle contentStyle = workbook.CreateCellStyle(); contentStyle.Alignment = HorizontalAlignment.Left; IRow dataRow = sheet.CreateRow(rowIndex); foreach (DataColumn column in dtSource.Columns) { ICell newCell = dataRow.CreateCell(column.Ordinal); newCell.CellStyle = contentStyle; 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++; } using (MemoryStream ms = new MemoryStream()) { workbook.Write(ms); ms.Flush(); ms.Position = 0; //sheet.Dispose(); //workbook.Dispose();//一般只用写这一个就OK了,他会遍历并释放所有资源,但当前版本有问题所以只释放sheet return(ms); } }
/// <summary> /// 由DataSet导出Excel(带有格式) /// </summary> /// <param name="sourceTable">要导出数据的DataTable</param> /// <param name="sheetName">工作表名称</param> /// <returns>Excel工作表</returns> private static Stream ExportDataSetToFormatExcel(DataSet sourceDs, string sheetName) { HSSFWorkbook workbook = new HSSFWorkbook(); MemoryStream ms = new MemoryStream(); string[] sheetNames = sheetName.Split(new string[] { ";" }, StringSplitOptions.RemoveEmptyEntries); //分割符 for (int i = 0; i < sheetNames.Length; i++) { HSSFSheet sheet = (HSSFSheet)workbook.CreateSheet(sheetNames[i]); var dateStyle = workbook.CreateCellStyle(); var format = workbook.CreateDataFormat(); dateStyle.DataFormat = format.GetFormat("yyyy-mm-dd"); //取得列宽 int[] arrColWidth = new int[sourceDs.Tables[i].Columns.Count]; foreach (DataColumn item in sourceDs.Tables[i].Columns) { arrColWidth[item.Ordinal] = Encoding.GetEncoding(936).GetBytes(item.ColumnName.ToString()).Length; } for (int k = 0; k < sourceDs.Tables[i].Rows.Count; k++) { for (int j = 0; j < sourceDs.Tables[i].Columns.Count; j++) { int intTemp = Encoding.GetEncoding(936).GetBytes(sourceDs.Tables[i].Rows[k][j].ToString()).Length; if (intTemp > arrColWidth[j]) { arrColWidth[j] = intTemp; } } } int rowIndex = 0; int sheetnum = 1; foreach (DataRow row in sourceDs.Tables[i].Rows) { #region 创建表头 if (rowIndex == 65535 || rowIndex == 0) { if (rowIndex != 0) { sheetnum++; sheet = (HSSFSheet)workbook.CreateSheet(sheetNames[i] + "-" + sheetnum.ToString()); } var headerRow = sheet.CreateRow(0); var headStyle = workbook.CreateCellStyle(); headStyle.Alignment = HorizontalAlignment.CENTER; var font = workbook.CreateFont(); font.FontHeightInPoints = 10; font.Boldweight = 700; headStyle.SetFont(font); //设置边框 headStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.THIN; headStyle.BottomBorderColor = HSSFColor.BLACK.index; headStyle.BorderLeft = NPOI.SS.UserModel.BorderStyle.THIN; headStyle.LeftBorderColor = HSSFColor.BLACK.index; headStyle.BorderRight = NPOI.SS.UserModel.BorderStyle.THIN; headStyle.RightBorderColor = HSSFColor.BLACK.index; headStyle.BorderTop = NPOI.SS.UserModel.BorderStyle.THIN; headStyle.TopBorderColor = HSSFColor.BLACK.index; //设置背景色 //headStyle.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.LIME.index; //headStyle.FillPattern = NPOI.SS.UserModel.FillPatternType.BIG_SPOTS; //headStyle.FillBackgroundColor = NPOI.HSSF.Util.HSSFColor.LIGHT_GREEN.index; foreach (DataColumn column in sourceDs.Tables[i].Columns) { headerRow.CreateCell(column.Ordinal).SetCellValue(column.ColumnName); headerRow.GetCell(column.Ordinal).CellStyle = headStyle; //设置列宽 sheet.SetColumnWidth(column.Ordinal, (arrColWidth[column.Ordinal] + 1) * 256); } rowIndex = 1; } #endregion #region 创建内容 var dataRow = sheet.CreateRow(rowIndex); foreach (DataColumn column in sourceDs.Tables[i].Columns) { var newCell = dataRow.CreateCell(column.Ordinal); string drValue = row[column].ToString(); switch (column.DataType.ToString()) { case "System.String": //字符串类型 newCell.SetCellValue(drValue); break; case "System.DateTime": //日期类型 case "MySql.Data.Types.MySqlDateTime": //MySql类型 if (drValue == "0000/0/0 0:00:00" || String.IsNullOrEmpty(drValue)) { //当时间为空,防止生成的execl 中是一串“#######”号,所有赋值为空字符串 newCell.SetCellValue(""); } else { 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++; } //设置首行首列冻结 //第一个参数表示要冻结的列数 //第二个参数表示要冻结的行数 //第三个参数表示右边区域可见的首列序号,从1开始计算 //第四个参数表示下边区域可见的首行序号,也是从1开始计算 //sheet.CreateFreezePane(1, 1, 0,0); } workbook.Write(ms); ms.Flush(); ms.Position = 0; workbook = null; 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 = 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 = 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> /// 带格式化的 /// </summary> /// <param name="dtSource">数据源</param> /// <param name="sheetName">创建的Sheet名称</param> /// <returns></returns> private static Stream ExportDataTableToFormatExcel(DataTable dtSource, string sheetName) { HSSFWorkbook workbook = new HSSFWorkbook(); var sheet = workbook.CreateSheet(sheetName); var dateStyle = workbook.CreateCellStyle(); var format = workbook.CreateDataFormat(); dateStyle.DataFormat = format.GetFormat("yyyy-MM-dd HH:mm:ss"); //取得列宽 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 sheetnum = 1; foreach (DataRow row in dtSource.Rows) { #region 新建表,填充表头,填充列头,样式 if (rowIndex == 65535 || rowIndex == 0) { if (rowIndex != 0) { sheetnum++; sheet = workbook.CreateSheet(sheetName + "-" + sheetnum.ToString()); } #region 列头及样式 { var headerRow = sheet.CreateRow(0); var headStyle = workbook.CreateCellStyle(); headStyle.Alignment = HorizontalAlignment.CENTER; var font = workbook.CreateFont(); font.FontHeightInPoints = 10; font.Boldweight = 700; headStyle.SetFont(font); //设置背景色 headStyle.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.WHITE.index; headStyle.FillBackgroundColor = NPOI.HSSF.Util.HSSFColor.RED.index; 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); } } #endregion rowIndex = 1; } #endregion #region 填充内容 var dataRow = sheet.CreateRow(rowIndex); foreach (DataColumn column in dtSource.Columns) { var newCell = dataRow.CreateCell(column.Ordinal); string drValue = row[column].ToString(); switch (column.DataType.ToString()) { case "System.String": //字符串类型 newCell.SetCellValue(drValue); break; case "System.DateTime": //日期类型 case "MySql.Data.Types.MySqlDateTime": //MySql类型 if (drValue == "0000/0/0 0:00:00" || String.IsNullOrEmpty(drValue)) { //当时间为空,防止生成的execl 中是一串“#######”号,所有赋值为空字符串 newCell.SetCellValue(""); } else { 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; return(ms); } }
public ActionResult ExportData(string state) { try { var curuser = OperatorProvider.Provider.Current(); string sql = @"select case when b.NATURE = '班组' then b.PARENTNAME else b.DEPTNAME END as DEPTNAME,ID,a.userid,b.REALNAME,b.gender,b.dutyname,a.devicename,TO_CHAR(a.CREATEDATE,'yyyy-mm-dd hh24:mi:ss') as datetime,case when (length(b.deptcode)>20) then (select d.SORTCODE from base_department d where d.deptcode = substr(b.deptcode,1,20)) else b.DEPTSORT end as DEPTSORTss from bis_hikinoutlog a left join V_USERINFO b on a.userid = b.userid left join(select* from HJB_PERSONSET where MODULETYPE = 0) t on a.userid = t.userid where REALNAME is not NULL and a.inout = 0 and not exists(select 1 from bis_hikinoutlog d where d.userid = a.userid and d.CREATEDATE + 0 > a.CREATEDATE + 0) "; //判断当前登陆用户是什么级别 if (!curuser.RoleName.IsEmpty()) { string RoleName = curuser.RoleName.ToString(); if (ViewBag.IsAppointAccount != 1) { if (RoleName.Contains("承包商级用户")) { //承包商级用户只可查看本单位门禁数据 sql += string.Format(" and (t.ISREFER is NULL or t.userid = '{0}')", curuser.UserId); sql += string.Format(@" and b.ROLENAME like '%{0}%' and b.DEPTNAME = (select case when a.nature = '班组' then a.parentname else a.DEPTNAME end as bmname from v_userinfo a where a.USERID = '{1}')", RoleName, curuser.UserId); } } else if (RoleName.Contains("厂级部门用户") || RoleName.Contains("安全管理员") || RoleName.Contains("公司领导") || RoleName.Contains("公司管理员") || RoleName.Contains("公司级用户") || RoleName.Contains("超级管理员") || curuser.UserId.Contains("1521c21a-62c9-4aa1-9093-a8bda503ea89")) { //此级别的用户可查看所有数据 } else { sql += string.Format(" and (t.ISREFER is NULL or t.userid = '{0}')", curuser.UserId); if (state == "0") { sql += string.Format(@" and (b.DEPTNAME = (select case when a.nature = '班组' then a.parentname else a.DEPTNAME end as bmname from v_userinfo a where a.USERID = '{0}') or b.PARENTNAME = (select case when a.nature = '班组' then a.parentname else a.DEPTNAME end as bmname from v_userinfo a where a.USERID = '{0}'))", curuser.UserId); } } } if (state == "0") { sql += " and DEPTTYPE is NULL ORDER BY DEPTSORTss, b.deptsort,b.DEPTCODE,b.userid desc"; } else { sql += " and DEPTTYPE is not NULL ORDER BY DEPTTYPE,DEPTSORTss,b.deptsort,b.DEPTCODE,b.userid desc"; } DataTable data = operticketmanagerbll.GetDataTable(sql); //导出excel string title = "在厂人员统计信息"; HSSFWorkbook workbook = new HSSFWorkbook();//创建Workbook对象 HSSFSheet sheet = workbook.CreateSheet("Sheet1") as HSSFSheet; sheet.DefaultRowHeight = 24 * 20; int column = data.Columns.Count; int indexRow = 0; //标题 if (!string.IsNullOrEmpty(title)) { IRow headerRow = sheet.CreateRow(indexRow); headerRow.HeightInPoints = 30; headerRow.CreateCell(0).SetCellValue(title); //合并单元格 CellRangeAddress region = new CellRangeAddress(0, 0, 0, 5); sheet.AddMergedRegion(region); ICellStyle cellstyle = workbook.CreateCellStyle(); cellstyle.VerticalAlignment = VerticalAlignment.Center; cellstyle.Alignment = HorizontalAlignment.Center; IFont font = workbook.CreateFont(); font.FontHeightInPoints = 25; font.FontName = "微软雅黑"; font.Boldweight = (short)FontBoldWeight.Bold; cellstyle.SetFont(font); var cell = sheet.GetRow(0).GetCell(0); cell.CellStyle = cellstyle; HSSFRegionUtil.SetBorderBottom(BorderStyle.Thin, region, sheet, workbook); //下边框 HSSFRegionUtil.SetBorderLeft(BorderStyle.Thin, region, sheet, workbook); //左边框 HSSFRegionUtil.SetBorderRight(BorderStyle.Thin, region, sheet, workbook); //右边框 HSSFRegionUtil.SetBorderTop(BorderStyle.Thin, region, sheet, workbook); //上边框 indexRow++; } //列头样式 ICellStyle headerStyle = workbook.CreateCellStyle(); headerStyle.Alignment = HorizontalAlignment.Center; headerStyle.VerticalAlignment = VerticalAlignment.Center; headerStyle.BorderBottom = BorderStyle.Thin; headerStyle.BorderLeft = BorderStyle.Thin; headerStyle.BorderRight = BorderStyle.Thin; headerStyle.BorderTop = BorderStyle.Thin; IFont headerFont = workbook.CreateFont(); //headerFont.FontHeightInPoints = 4; headerFont.FontName = "宋体"; headerFont.Boldweight = (short)FontBoldWeight.Bold; headerStyle.SetFont(headerFont); IRow row1 = sheet.CreateRow(indexRow); row1.CreateCell(0).SetCellValue("部门名称"); row1.GetCell(0).CellStyle = headerStyle; row1.CreateCell(1).SetCellValue("姓名"); row1.GetCell(1).CellStyle = headerStyle; row1.CreateCell(2).SetCellValue("性别"); row1.GetCell(2).CellStyle = headerStyle; row1.CreateCell(3).SetCellValue("岗位名称"); row1.GetCell(3).CellStyle = headerStyle; row1.CreateCell(4).SetCellValue("门禁通道名称"); row1.GetCell(4).CellStyle = headerStyle; row1.CreateCell(5).SetCellValue("进厂时间"); row1.GetCell(5).CellStyle = headerStyle; //普通单元格样式 ICellStyle bodyStyle = workbook.CreateCellStyle(); bodyStyle.Alignment = HorizontalAlignment.Center; bodyStyle.VerticalAlignment = VerticalAlignment.Center; IFont font1 = workbook.CreateFont(); font1.Color = HSSFColor.Black.Index; //font1.Boldweight = 25; //font1.FontHeightInPoints = 12; bodyStyle.FillForegroundColor = HSSFColor.White.Index; bodyStyle.SetFont(font1); //设置格式 IDataFormat format = workbook.CreateDataFormat(); //填充数据 for (int i = 0; i < data.Rows.Count; i++) { indexRow++; IRow rowTemp = sheet.CreateRow(indexRow); //rowTemp.Height = 62 * 20; rowTemp.CreateCell(0).SetCellValue(data.Rows[i]["deptname"].ToString()); rowTemp.CreateCell(1).SetCellValue(data.Rows[i]["realname"].ToString()); rowTemp.CreateCell(2).SetCellValue(data.Rows[i]["gender"].ToString()); rowTemp.CreateCell(3).SetCellValue(data.Rows[i]["dutyname"].ToString()); rowTemp.CreateCell(4).SetCellValue(data.Rows[i]["devicename"].ToString()); rowTemp.CreateCell(5).SetCellValue(data.Rows[i]["datetime"].ToString()); rowTemp.GetCell(0).CellStyle = bodyStyle; rowTemp.GetCell(1).CellStyle = bodyStyle; rowTemp.GetCell(2).CellStyle = bodyStyle; rowTemp.GetCell(3).CellStyle = bodyStyle; rowTemp.GetCell(4).CellStyle = bodyStyle; rowTemp.GetCell(5).CellStyle = bodyStyle; } sheet.AutoSizeColumn(0); sheet.AutoSizeColumn(1); sheet.AutoSizeColumn(2); sheet.AutoSizeColumn(3); sheet.AutoSizeColumn(4); sheet.AutoSizeColumn(5); //合并单元格 MergeCells(sheet, data, 0, 0, 0); MemoryStream ms = new MemoryStream(); workbook.Write(ms); ms.Seek(0, SeekOrigin.Begin); return(File(ms, "application/vnd.ms-excel", title + ".xls")); } catch (Exception ex) { } return(Success("导出成功。")); //设置导出格式 //ExcelConfig excelconfig = new ExcelConfig(); //excelconfig.Title = (state == "0" ? "内部" : "外部") + "人员统计信息"; //excelconfig.TitleFont = "微软雅黑"; //excelconfig.TitlePoint = 25; //excelconfig.FileName = "实时在厂人员统计导出.xls"; //excelconfig.IsAllSizeColumn = true; ////每一列的设置,没有设置的列信息,系统将按datatable中的列名导出 //List<ColumnEntity> listColumnEntity = new List<ColumnEntity>(); //excelconfig.ColumnEntity = listColumnEntity; //ColumnEntity columnentity = new ColumnEntity(); //excelconfig.ColumnEntity.Add(new ColumnEntity() { Column = "deptname".ToLower(), ExcelColumn = "部门名称" }); //excelconfig.ColumnEntity.Add(new ColumnEntity() { Column = "realname".ToLower(), ExcelColumn = "姓名" }); //excelconfig.ColumnEntity.Add(new ColumnEntity() { Column = "gender".ToLower(), ExcelColumn = "性别" }); //excelconfig.ColumnEntity.Add(new ColumnEntity() { Column = "dutyname".ToLower(), ExcelColumn = "岗位名称" }); //excelconfig.ColumnEntity.Add(new ColumnEntity() { Column = "devicename".ToLower(), ExcelColumn = "门禁通道名称" }); //excelconfig.ColumnEntity.Add(new ColumnEntity() { Column = "datetime".ToLower(), ExcelColumn = "进厂时间" }); ////调用导出方法 //ExcelHelper.ExcelDownload(data, excelconfig); }
public void SetUp() { // One or more test methods depends on the american culture. System.Threading.Thread.CurrentThread.CurrentCulture = System.Globalization.CultureInfo.CreateSpecificCulture("en-US"); // create the formatter to Test formatter = new HSSFDataFormatter(); // create a workbook to Test with wb = new HSSFWorkbook(); ISheet sheet = wb.CreateSheet(); IDataFormat format = wb.CreateDataFormat(); // create a row and Put some cells in it IRow row = sheet.CreateRow(0); // date value for July 8 1901 1:19 PM double dateNum = 555.555; // date value for July 8 1901 11:23 AM double timeNum = 555.47431; //valid date formats -- all should have "Jul" in output String[] goodDatePatterns = { "[$-F800]dddd\\,\\ mmmm\\ dd\\,\\ yyyy", "mmm/d/yy\\ h:mm PM;@", "mmmm/d/yy\\ h:mm;@", "mmmm/d;@", "mmmm/d/yy;@", "mmm/dd/yy;@", "[$-409]d\\-mmm;@", "[$-409]d\\-mmm\\-yy;@", "[$-409]dd\\-mmm\\-yy;@", "[$-409]mmm\\-yy;@", "[$-409]mmmm\\-yy;@", "[$-409]mmmm\\ d\\,\\ yyyy;@", "[$-409]mmm/d/yy\\ h:mm:ss;@", "[$-409]mmmm/d/yy\\ h:mm:ss am;@", "[$-409]mmmmm;@", "[$-409]mmmmm\\-yy;@", "mmmm/d/yyyy;@", "[$-409]d\\-mmm\\-yyyy;@" }; //valid time formats - all should have 11:23 in output String[] goodTimePatterns = { "HH:MM", "HH:MM:SS", "HH:MM;HH:MM;HH:MM", // This is fun - blue if positive time, // red if negative time or green for zero! "[BLUE]HH:MM;[RED]HH:MM;[GREEN]HH:MM", "yyyy-mm-dd hh:mm", "yyyy-mm-dd hh:mm:ss", }; // valid number formats String[] goodNumPatterns = { "#,##0.0000", "#,##0;[Red]#,##0", "(#,##0.00_);(#,##0.00)", "($#,##0.00_);[Red]($#,##0.00)", "$#,##0.00", "[$-809]#,##0.00", // international format "[$-2]#,##0.00", // international format "0000.00000%", "0.000E+00", "0.00E+00", "[BLACK]0.00;[COLOR 5]##.##", }; // invalid date formats -- will throw exception in DecimalFormat ctor String[] badNumPatterns = { "#,#$'#0.0000", "'#','#ABC#0;##,##0", "000 '123 4'5'6 000", "#''0#0'1#10L16EE" }; // create cells with good date patterns for (int i = 0; i < goodDatePatterns.Length; i++) { ICell cell = row.CreateCell(i); cell.SetCellValue(dateNum); ICellStyle cellStyle = wb.CreateCellStyle(); cellStyle.DataFormat = (/*setter*/ format.GetFormat(goodDatePatterns[i])); cell.CellStyle = (/*setter*/ cellStyle); } row = sheet.CreateRow(1); // create cells with time patterns for (int i = 0; i < goodTimePatterns.Length; i++) { ICell cell = row.CreateCell(i); cell.SetCellValue(timeNum); ICellStyle cellStyle = wb.CreateCellStyle(); cellStyle.DataFormat = (/*setter*/ format.GetFormat(goodTimePatterns[i])); cell.CellStyle = (/*setter*/ cellStyle); } row = sheet.CreateRow(2); // create cells with num patterns for (int i = 0; i < goodNumPatterns.Length; i++) { ICell cell = row.CreateCell(i); cell.SetCellValue(-1234567890.12345); ICellStyle cellStyle = wb.CreateCellStyle(); cellStyle.DataFormat = (/*setter*/ format.GetFormat(goodNumPatterns[i])); cell.CellStyle = (/*setter*/ cellStyle); } row = sheet.CreateRow(3); // create cells with bad num patterns for (int i = 0; i < badNumPatterns.Length; i++) { ICell cell = row.CreateCell(i); cell.SetCellValue(1234567890.12345); ICellStyle cellStyle = wb.CreateCellStyle(); cellStyle.DataFormat = (/*setter*/ format.GetFormat(badNumPatterns[i])); cell.CellStyle = (/*setter*/ cellStyle); } // Built in formats { // Zip + 4 format row = sheet.CreateRow(4); ICell cell = row.CreateCell(0); cell.SetCellValue(123456789); ICellStyle cellStyle = wb.CreateCellStyle(); cellStyle.DataFormat = (/*setter*/ format.GetFormat("00000-0000")); cell.CellStyle = (/*setter*/ cellStyle); } { // Phone number format row = sheet.CreateRow(5); ICell cell = row.CreateCell(0); cell.SetCellValue(5551234567D); ICellStyle cellStyle = wb.CreateCellStyle(); cellStyle.DataFormat = (/*setter*/ format.GetFormat("[<=9999999]###-####;(###) ###-####")); cell.CellStyle = (/*setter*/ cellStyle); } { // SSN format row = sheet.CreateRow(6); ICell cell = row.CreateCell(0); cell.SetCellValue(444551234); ICellStyle cellStyle = wb.CreateCellStyle(); cellStyle.DataFormat = (/*setter*/ format.GetFormat("000-00-0000")); cell.CellStyle = (/*setter*/ cellStyle); } { // formula cell row = sheet.CreateRow(7); ICell cell = row.CreateCell(0); cell.SetCellType(CellType.Formula); cell.CellFormula = (/*setter*/ "SUM(12.25,12.25)/100"); ICellStyle cellStyle = wb.CreateCellStyle(); cellStyle.DataFormat = (/*setter*/ format.GetFormat("##.00%;")); cell.CellStyle = (/*setter*/ cellStyle); } }
/// <summary> /// 获取Excel内存数据 /// </summary> /// <param name="dataSource">数据源</param> /// <param name="sheetName">Excel名字</param> /// <returns></returns> private static MemoryStream GetExcelData(DataTable dataSource, string sheetName) { if (dataSource == null) { throw new ArgumentException("dataSource is null"); } if (string.IsNullOrEmpty(sheetName)) { sheetName = "UnKown"; } //打开Excel对象 HSSFWorkbook _book = new HSSFWorkbook(); DocumentSummaryInformation _dom_summary_info = PropertySetFactory.CreateDocumentSummaryInformation(); _dom_summary_info.Company = ""; _book.DocumentSummaryInformation = _dom_summary_info; SummaryInformation _summary_info = PropertySetFactory.CreateSummaryInformation(); _summary_info.Subject = ""; _book.SummaryInformation = _summary_info; //Excel的Sheet对象 ISheet _sheet = _book.CreateSheet(sheetName); DataColumnCollection _columns = dataSource.Columns; DataRowCollection _rows = dataSource.Rows; IRow _row = _sheet.CreateRow(0); // 用于格式化单元格的数据 HSSFDataFormat format = (HSSFDataFormat)_book.CreateDataFormat(); // 设置列头字体 HSSFFont font = (HSSFFont)_book.CreateFont(); font.FontHeightInPoints = 18; //字体高度 //font.Color = (short)FontColor.RED; font.FontName = "隶书"; //字体 font.Boldweight = (short)FontBoldWeight.Bold; //font.IsItalic = true; //是否使用斜体 //font.IsStrikeout = true; //是否使用划线 //设置文本字体 HSSFFont fontContent = (HSSFFont)_book.CreateFont(); fontContent.FontHeightInPoints = 10; fontContent.FontName = "宋体"; // 设置单元格类型 HSSFCellStyle cellStyle = (HSSFCellStyle)_book.CreateCellStyle(); cellStyle.SetFont(font); cellStyle.Alignment = HorizontalAlignment.Center; //水平布局:居中 cellStyle.WrapText = false; // 添加单元格注释 // 创建HSSFPatriarch对象,HSSFPatriarch是所有注释的容器. HSSFPatriarch patr = (HSSFPatriarch)_sheet.CreateDrawingPatriarch(); // 定义注释的大小和位置,详见文档 HSSFComment comment = patr.CreateComment(new HSSFClientAnchor(0, 0, 0, 0, (short)4, 2, (short)6, 5)); // 设置注释内容 comment.String = new HSSFRichTextString("列标题"); // 设置注释作者. 当鼠标移动到单元格上是可以在状态栏中看到该内容. comment.Author = "天蓝海"; //set date format ICellStyle cellStyleDate = _book.CreateCellStyle(); cellStyleDate.SetFont(fontContent); cellStyleDate.Alignment = HorizontalAlignment.Center; //水平布局:居中 cellStyleDate.DataFormat = format.GetFormat("yyyy-m-d HH:MM:SS"); //数据格式 ICellStyle cellStyleNumber = _book.CreateCellStyle(); cellStyleNumber.SetFont(fontContent); cellStyleNumber.Alignment = HorizontalAlignment.Center; //水平布局:居中 cellStyleNumber.DataFormat = format.GetFormat("0.00"); //生成列名,根据DataTable的列名 for (int i = 0; i < _columns.Count; i++) { // 创建单元格 ICell cell = _row.CreateCell(i); HSSFRichTextString hssfString = new HSSFRichTextString(_columns[i].ColumnName); cell.SetCellValue(hssfString); //设置单元格内容 cell.CellStyle = cellStyle; //设置单元格样式 cell.SetCellType(CellType.String); //指定单元格格式:数值、公式或字符串 cell.CellComment = comment; //添加注释 } //填充数据 for (int j = 0; j < _rows.Count; j++) { _row = _sheet.CreateRow(j + 1); for (int k = 0; k < _columns.Count; k++) { ICell cell = _row.CreateCell(k); switch (_columns[k].DataType.ToString()) { case "System.String": HSSFRichTextString hssfString = new HSSFRichTextString(_rows[j][k].ToString()); cell.SetCellValue(hssfString); cell.SetCellType(CellType.String); break; case "System.Decimal": cell.SetCellValue((double)(decimal)_rows[j][k]); cell.CellStyle = cellStyleNumber; break; case "System.DateTime": cell.SetCellValue((DateTime)_rows[j][k]); cell.CellStyle = cellStyleDate; break; } //自动换行 if (_rows[j][k].ToString().Contains("\r\n")) { cell.CellStyle.WrapText = true; } } } //设置自动列宽 for (int i = 0; i < _columns.Count; i++) { _sheet.AutoSizeColumn(i); } //保存excel文档 _sheet.ForceFormulaRecalculation = true; MemoryStream _stream = new MemoryStream(); _book.Write(_stream); return(_stream); }
/// <summary> /// 輸出Excel(重複不顯示) /// </summary> /// <param name="FileName">輸出檔名</param> /// <param name="sheetName">Sheet名稱</param> /// <param name="Datas">填入之資料</param> /// <param name="mappings">填入之資料</param> public static HSSFWorkbook GenerateExcel(DataTable dt, List <tblExcelMapping> mappings) { string ExcelName = mappings[0].ExcelName; // 取得代碼轉換資料 using (vwCodeMappingRepository rep = new vwCodeMappingRepository()) { codeMap = rep.query("", "EXPORT", "EXCEL", ExcelName, ""); } HSSFWorkbook book = new HSSFWorkbook(); IEnumerable <string> sheetNames = mappings.Select(x => x.SheetName).Distinct(); foreach (var name in sheetNames) { ISheet sheet; sheet = (HSSFSheet)book.GetSheet(name); if (sheet == null) { sheet = (HSSFSheet)book.CreateSheet(name); } sheet.DisplayZeros = true; List <tblExcelMapping> Columns = mappings.Where(x => x.SheetName.Equals(name, StringComparison.OrdinalIgnoreCase)).OrderBy(x => x.X).ToList(); #region 表頭 IRow headerrow = sheet.CreateRow(0); ICellStyle style = book.CreateCellStyle(); style.Alignment = HorizontalAlignment.Center; style.VerticalAlignment = VerticalAlignment.Center; for (int i = 0; i < Columns.Count(); i++) { int X = Columns[i].X - 1; ICell cell = headerrow.CreateCell(X); cell.CellStyle = style; cell.SetCellValue(Columns[i].ColumnName); } #endregion #region 填入內容 for (int i = 0; i < Columns.Count(); i++) { int X = Columns[i].X - 1; ICellStyle dataStyle = book.CreateCellStyle(); dataStyle.Alignment = HorizontalAlignment.Center; dataStyle.VerticalAlignment = VerticalAlignment.Center; if (!string.IsNullOrEmpty(Columns[i].NewLineChar)) { dataStyle.WrapText = true; // 有設定換行字元時,啟動自動換行 } if (Columns[i].DataType == "Date") { //顯示日期格式 HSSFDataFormat dateFormat = (HSSFDataFormat)book.CreateDataFormat(); dataStyle.DataFormat = dateFormat.GetFormat("yyyy-MM-dd"); } if (Columns[i].DataType == "DateTime") { ////顯示日期格式 HSSFDataFormat dateFormat = (HSSFDataFormat)book.CreateDataFormat(); dataStyle.DataFormat = dateFormat.GetFormat("yyyy-MM-dd HH:mm:ss"); } else if (Columns[i].DataType == "Integer") { ////顯示有逗號區分的數值資料 HSSFDataFormat numericformat = (HSSFDataFormat)book.CreateDataFormat(); dataStyle.DataFormat = numericformat.GetFormat("###,##0"); } else if (Columns[i].DataType == "Decimal") { ////顯示有逗號區分的小數資料 HSSFDataFormat decimalFormat = (HSSFDataFormat)book.CreateDataFormat(); dataStyle.DataFormat = decimalFormat.GetFormat("###,##0.0000"); } int fixRow = -1; for (int row = 0; row < dt.Rows.Count; row++) { IRow dataRow; if (i == 0) { dataRow = sheet.CreateRow(row + 1); } else { dataRow = sheet.GetRow(row + 1); } string value = string.Empty; if (!string.IsNullOrEmpty(Columns[i].FieldName) || !string.IsNullOrEmpty(Columns[i].DefaultValue)) { if (string.IsNullOrEmpty(Columns[i].FieldName)) { value = Columns[i].DefaultValue; } else { value = (string.IsNullOrEmpty(dt.Rows[row][Columns[i].FieldName].ToString())) ? Columns[i].DefaultValue : dt.Rows[row][Columns[i].FieldName].ToString(); } // 代碼轉換 if (codeMap.Where(x => x.FieldName.Equals(Columns[i].ColumnName, StringComparison.OrdinalIgnoreCase)).Count() > 0 && codeMap.Where(x => x.BeforeValue.Equals(value, StringComparison.OrdinalIgnoreCase)).Count() > 0) { vwCodeMapping map = codeMap.Find(x => x.FieldName.Equals(Columns[i].ColumnName, StringComparison.OrdinalIgnoreCase) && x.BeforeValue.Equals(value, StringComparison.OrdinalIgnoreCase)); if (map != null) { value = map.AfterValue; } } if (!string.IsNullOrEmpty(Columns[i].NewLineChar)) { value = value.Replace(Columns[i].NewLineChar, "\n"); } } // 沒有指定 Field 時,代入 Default Value if (string.IsNullOrEmpty(Columns[i].FieldName)) { ICell cell = dataRow.CreateCell(X); cell.CellStyle = dataStyle; SetCellValue(ref cell, Columns[i].DataType, value); } // 判斷是否和上筆資料一致 else if (row == 0 || Columns[i].CanRepeat || (dt.Rows[row][Columns[i].FieldName].ToString() != dt.Rows[row - 1][Columns[i].FieldName].ToString())) { ICell cell = dataRow.CreateCell(X); cell.CellStyle = dataStyle; SetCellValue(ref cell, Columns[i].DataType, value); } } sheet.AutoSizeColumn(X); } #endregion } return(book); }
public void ExportExcel(DataTable dt) { try { //创建一个工作簿 IWorkbook workbook = new HSSFWorkbook(); //创建一个 sheet 表 ISheet sheet = workbook.CreateSheet(dt.TableName); //创建一行 IRow rowH = sheet.CreateRow(0); //创建一个单元格 ICell cell = null; //创建单元格样式 ICellStyle cellStyle = workbook.CreateCellStyle(); //创建格式 IDataFormat dataFormat = workbook.CreateDataFormat(); //设置为文本格式,也可以为 text,即 dataFormat.GetFormat("text"); cellStyle.DataFormat = dataFormat.GetFormat("@"); //设置列名 foreach (DataColumn col in dt.Columns) { //创建单元格并设置单元格内容 rowH.CreateCell(col.Ordinal).SetCellValue(col.Caption); //设置单元格格式 rowH.Cells[col.Ordinal].CellStyle = cellStyle; } //写入数据 for (int i = 0; i < dt.Rows.Count; i++) { //跳过第一行,第一行为列名 IRow row = sheet.CreateRow(i + 1); for (int j = 0; j < dt.Columns.Count; j++) { cell = row.CreateCell(j); cell.SetCellValue(dt.Rows[i][j].ToString()); cell.CellStyle = cellStyle; } } //设置导出文件路径 string path = HttpContext.Current.Server.MapPath("/ImportExcel/"); //设置新建文件路径及名称 string savePath = path + DateTime.Now.ToString("yyyyMMddHHmmss") + ".xls"; //创建文件 FileStream file = new FileStream(savePath, FileMode.CreateNew, FileAccess.Write); //创建一个 IO 流 MemoryStream ms = new MemoryStream(); //写入到流 workbook.Write(ms); //转换为字节数组 byte[] bytes = ms.ToArray(); file.Write(bytes, 0, bytes.Length); file.Flush(); //还可以调用下面的方法,把流输出到浏览器下载 OutputClient(bytes); //释放资源 bytes = null; ms.Close(); ms.Dispose(); file.Close(); file.Dispose(); workbook.Close(); sheet = null; workbook = null; } catch (Exception ex) { } }
/// <summary> /// DataTable导出到Excel的MemoryStream Export() /// </summary> /// <param Name="dtSource">DataTable数据源</param> /// <param Name="strHeaderText">Excel表头文本(例如:信息工程学院2014-2015学年第一学期教学检查听课安排)</param> private MemoryStream Export(List <ExportExcelModel> dtSource, string strHeaderText) { HSSFWorkbook workbook = new HSSFWorkbook(); ISheet sheet = workbook.CreateSheet(); { DocumentSummaryInformation dsi = PropertySetFactory.CreateDocumentSummaryInformation(); dsi.Company = "破晓技术团队"; 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; } ICellStyle dateStyle = workbook.CreateCellStyle(); IDataFormat format = workbook.CreateDataFormat(); dateStyle.DataFormat = format.GetFormat("yyyy-mm-dd"); int rowIndex = 0; int j = 0; foreach (ExportExcelModel row in dtSource) { // 新建表,填充表头,填充列头,样式 if (rowIndex == 65535 || rowIndex == 0) { if (rowIndex != 0) { sheet = workbook.CreateSheet(); } // 表头及样式 { IRow headerRow = sheet.CreateRow(0); headerRow.HeightInPoints = 25; headerRow.CreateCell(0).SetCellValue(strHeaderText); ICellStyle headStyle = workbook.CreateCellStyle(); headStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center; // ------------------ IFont font = workbook.CreateFont(); font.FontHeightInPoints = 20; font.Boldweight = 800; headStyle.SetFont(font); headerRow.GetCell(0).CellStyle = headStyle; sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(0, 0, 0, dcs.Count - 1)); // ------------------ } // { IRow headerRow = sheet.CreateRow(1); ICellStyle headStyle = workbook.CreateCellStyle(); headStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center; // ------------------ IFont font = workbook.CreateFont(); font.FontHeightInPoints = 20; font.Boldweight = 800; font.FontName = "宋体"; headStyle.SetFont(font); int index = 0; foreach (string column in dcs) { headerRow.CreateCell(index).SetCellValue(column); headerRow.GetCell(index).CellStyle = headStyle; sheet.AutoSizeColumn(index); //设置列宽 //sheet.SetColumnWidth(index, (arrColWidth[index] + 1) * 256); //sheet.SetColumnWi index++; } } rowIndex = 2; } //填充内容 IRow dataRow = sheet.CreateRow(rowIndex); int columnIndex = 0; //!!!!!!!!!!!!!!!!!!!!!!!如果没有职称可能会异常 DistinctSupervisor(dtSource[j].supervisors, ListSupervisor);//去督导员的职称 for (int i = 0; i < dcs.Count; i++) { ICell newCell = dataRow.CreateCell(columnIndex); columnIndex++; if (i != dcs.Count - 1) { //注意这个在导出的时候加了“\t” 的目的就是避免导出的数据显示为科学计数法。可以放在每行的首尾。 switch (i) { case 0: //序号 newCell.SetCellValue((j + 1).ToString() + "\t"); break; case 1: //课程 newCell.SetCellValue(dtSource[j].classname.ToString() + "\t"); break; case 2: //授课内容 newCell.SetCellValue(dtSource[j].classcontent.ToString() + "\t"); break; case 3: //授课方式 newCell.SetCellValue(dtSource[j].classtype.ToString() + "\t"); break; case 4: //专业 newCell.SetCellValue(dtSource[j].major.ToString() + "\t"); break; case 5: //教室 newCell.SetCellValue(dtSource[j].classroom.ToString() + "\t"); break; case 6: //教师 newCell.SetCellValue(dtSource[j].teachername.ToString() + "\t"); break; case 7: //周次 newCell.SetCellValue(dtSource[j].week.ToString() + "\t"); break; case 8: //听课时间 newCell.SetCellValue(dtSource[j].time + "\t"); break; case 9: //听课人员安排 newCell.SetCellValue(FormatSupervisor(ListSupervisor) + "\t"); break; case 10: //分数 newCell.SetCellValue(" " + "\t"); break; } } else { //申报 newCell.SetCellValue(" "); } } j++; rowIndex++; } adjustcolum(sheet); //调整列宽 AddBorder(sheet, workbook); //加边框 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="headerTextList">表头摘要信息</param> public MemoryStream Export(DataTable dtSource, List<String> headerTextList) { HSSFWorkbook workbook = new HSSFWorkbook(); ISheet sheet = workbook.CreateSheet("sheet1"); //设置Excel文件属性信息 SetFileProperty(workbook); 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++) { 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 = workbook.CreateSheet(); } #region 表头及样式 for (int i = 0; i < headerTextList.Count; i++) { HSSFRow headerRow = (HSSFRow)sheet.CreateRow(i); headerRow.HeightInPoints = 18; headerRow.CreateCell(0).SetCellValue(headerTextList[i]); HSSFCellStyle headerStyle = (HSSFCellStyle)workbook.CreateCellStyle(); headerStyle.Alignment = HorizontalAlignment.Left; HSSFFont font = (HSSFFont)workbook.CreateFont(); font.FontHeightInPoints = 14; //font.Boldweight = 700; headerStyle.SetFont(font); headerRow.GetCell(0).CellStyle = headerStyle; sheet.AddMergedRegion(new CellRangeAddress(0, 0, 0, dtSource.Columns.Count - 1)); } #endregion #region 列头及样式 { HSSFRow headerRow = (HSSFRow)sheet.CreateRow(headerTextList.Count); HSSFCellStyle headStyle = (HSSFCellStyle)workbook.CreateCellStyle(); headStyle.Alignment = 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; //设置列宽 sheet.SetColumnWidth(column.Ordinal, (arrColWidth[column.Ordinal] + 1) * 256); } } #endregion rowIndex = headerTextList.Count + 1; } #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++; } using (MemoryStream ms = new MemoryStream()) { workbook.Write(ms); ms.Flush(); ms.Position = 0; return ms; } }
/// <summary> /// 创建Sheet表 /// </summary> private static ISheet WorkbookCreateSheet <T>(List <T> dataSource, string xlsSheetName, List <FileFieldMapping> FieldMapList, HSSFWorkbook workbook) where T : class, new() { if (xlsSheetName == string.Empty) { xlsSheetName = "Sheet1"; } ISheet sheet = workbook.CreateSheet(xlsSheetName); ICellStyle cellSytleDate = workbook.CreateCellStyle(); IDataFormat format = workbook.CreateDataFormat(); cellSytleDate.DataFormat = format.GetFormat("yyyy年mm月dd日"); #region 填充列头区域 IRow rowHeader = sheet.CreateRow(0); //设置表头样式 ICellStyle headStyle = workbook.CreateCellStyle(); headStyle.Alignment = HorizontalAlignment.Center; IFont cellFontHeader = workbook.CreateFont(); cellFontHeader.Boldweight = 700; cellFontHeader.FontHeightInPoints = 12; headStyle.SetFont(cellFontHeader); int forEachindex = 0; if (FieldMapList == null || FieldMapList.Count < 1) { Type t = dataSource[0].GetType(); PropertyInfo[] pis = t.GetProperties(); for (int colIndex = 0; colIndex < pis.Length; colIndex++) { ICell cell = rowHeader.CreateCell(colIndex); cell.SetCellValue(pis[colIndex].Name); cell.CellStyle = headStyle; } } else { FieldMapList.ForEach(e => { ICell cell = rowHeader.CreateCell(forEachindex); cell.SetCellValue(e.FieldDiscretion); cell.CellStyle = headStyle; forEachindex++; }); } #endregion 填充列头区域 #region 对所需字段依数 填充内容区域 for (int rowIndex = 0; rowIndex < dataSource.Count; rowIndex++) { IRow rowContent = sheet.CreateRow(rowIndex + 1); T entity = dataSource[rowIndex]; Type tentity = entity.GetType(); PropertyInfo[] tpis = tentity.GetProperties(); int colIndex = 0; if (FieldMapList == null || FieldMapList.Count < 1) { for (int Index = 0; Index < tpis.Length; Index++) { FillIcell <T>(cellSytleDate, rowContent, entity, tpis, Index, Index); } } else { FieldMapList.ForEach(e => { //添加项次序号 if (e.FieldDiscretion == "项次") { ICell cellContent = rowContent.CreateCell(colIndex); cellContent.SetCellValue((rowIndex + 1).ToString()); colIndex++; } else { for (int tipsIndex = 0; tipsIndex < tpis.Length; tipsIndex++) { //如不是所需字段 跳过 if (e.FieldName == tpis[tipsIndex].Name) { FillIcell <T>(cellSytleDate, rowContent, entity, tpis, tipsIndex, colIndex); colIndex++; break; } } } }); } } #endregion 填充内容区域 return(sheet); }