public void TestHSSFCellToStringWithDataFormat() { HSSFWorkbook wb = new HSSFWorkbook(); ICell cell = wb.CreateSheet("Sheet1").CreateRow(0).CreateCell(0); cell.SetCellValue(new DateTime(2009, 8, 20)); NPOI.SS.UserModel.ICellStyle cellStyle = wb.CreateCellStyle(); cellStyle.DataFormat = HSSFDataFormat.GetBuiltinFormat("m/d/yy"); cell.CellStyle = cellStyle; Assert.AreEqual("8/20/09", cell.ToString()); NPOI.SS.UserModel.ICellStyle cellStyle2 = wb.CreateCellStyle(); IDataFormat format = wb.CreateDataFormat(); cellStyle2.DataFormat = format.GetFormat("YYYY-mm/dd"); cell.CellStyle = cellStyle2; Assert.AreEqual("2009-08/20", cell.ToString()); }
public void TestCloneStyleDiffWB() { HSSFWorkbook wbOrig = new HSSFWorkbook(); IFont fnt = wbOrig.CreateFont(); fnt.FontName=("TestingFont"); Assert.AreEqual(5, wbOrig.NumberOfFonts); IDataFormat fmt = wbOrig.CreateDataFormat(); fmt.GetFormat("MadeUpOne"); fmt.GetFormat("MadeUpTwo"); NPOI.SS.UserModel.ICellStyle orig = wbOrig.CreateCellStyle(); orig.Alignment = (HorizontalAlignment.Right); orig.SetFont(fnt); orig.DataFormat=(fmt.GetFormat("Test##")); Assert.AreEqual(HorizontalAlignment.Right, orig.Alignment); Assert.AreEqual(fnt,orig.GetFont(wbOrig)); Assert.AreEqual(fmt.GetFormat("Test##") , orig.DataFormat); // Now a style on another workbook HSSFWorkbook wbClone = new HSSFWorkbook(); Assert.AreEqual(4, wbClone.NumberOfFonts); IDataFormat fmtClone = wbClone.CreateDataFormat(); NPOI.SS.UserModel.ICellStyle clone = wbClone.CreateCellStyle(); Assert.AreEqual(4, wbClone.NumberOfFonts); Assert.AreNotEqual(HorizontalAlignment.Right,clone.Alignment); Assert.AreNotEqual("TestingFont", clone.GetFont(wbClone).FontName); clone.CloneStyleFrom(orig); Assert.AreEqual(HorizontalAlignment.Right, clone.Alignment); Assert.AreEqual("TestingFont" ,clone.GetFont(wbClone).FontName); Assert.AreEqual(fmtClone.GetFormat("Test##"),clone.DataFormat); Assert.AreNotEqual(fmtClone.GetFormat("Test##") , fmt.GetFormat("Test##")); Assert.AreEqual(5, wbClone.NumberOfFonts); }
private void FillRows(HSSFWorkbook workbook, ISheet workSheet, List<Cell> cells, dynamic rows) { var dateStyle = workbook.CreateCellStyle(); var format = workbook.CreateDataFormat(); dateStyle.DataFormat = format.GetFormat("yyyy-mm-dd"); var currentRow = 1; if (rows == null) { return; } foreach (var row in rows) { var workRow = workSheet.CreateRow(currentRow); var currentCol = 0; var type = row.GetType(); foreach (var cell in cells) { var workCell = workRow.CreateCell(currentCol); SetCellValue(workbook, workCell, type, row, cell); currentCol++; } currentRow++; } }
private static void SetCellValue(HSSFWorkbook workbook, ICell workCell, Type type, dynamic row, Cell cell) { var value = type.GetProperty(cell.Field).GetValue(row); if (value == null) { return; } if (value is DateTime) { workCell.SetCellValue((DateTime)value); } else if (value is int) { workCell.SetCellValue((int)value); } else if (value is double) { workCell.SetCellValue((double)value); } else { workCell.SetCellValue(value.ToString()); } if (!string.IsNullOrWhiteSpace(cell.Format)) { var cellStyle = workbook.CreateCellStyle(); var format = workbook.CreateDataFormat(); cellStyle.DataFormat = format.GetFormat(cell.Format); workCell.CellStyle = cellStyle; } }
private void btnOutput_Click(object sender, EventArgs e) { List <MODEL.Classes> lists = cm.GetAllClass(false); /* * 1.创建出工作薄 * 2.为这个工作薄创建出工作表 * 3.为这个表创建出行 * 4.为这个行创建出每一列(单元格cell)--添加数据 * 5.文件的写入 */ //创建一个工作薄 NPOI.HSSF.UserModel.HSSFWorkbook workbook = new NPOI.HSSF.UserModel.HSSFWorkbook(); //创建一张工作表 ISheet sheet1 = workbook.CreateSheet("sh1"); // workbook.CreateSheet("Sh1"); ///NPOI.HSSF. .SS .UserModel.HSSFSheet sheet1 = workbook.CreateSheet(); //.CreateSheet("sh1"); ////workbook.CreateSheet("sh1"); // workbook.CreateSheet("sh1"); //需要导出的数据在集合中,每一个对象对应着后期表的一行数据 for (int i = 0; i < lists.Count; i++) { //创建行 IRow row = sheet1.CreateRow(i); //创建第一个单元格 ICell cell1 = row.CreateCell(0); cell1.SetCellValue(lists[i].CID); //创建第2个单元格 ICell cell2 = row.CreateCell(1); cell2.SetCellValue(lists[i].CName); //创建第3个单元格 ICell cell3 = row.CreateCell(2); cell3.SetCellValue(lists[i].CCount); //创建第4个单元格 ICell cell4 = row.CreateCell(3); cell4.SetCellValue(lists[i].CImg); //创建第5个单元格 ICell cell5 = row.CreateCell(4); cell5.SetCellValue(lists[i].CIsDel); //创建第6个单元格 ICell cell6 = row.CreateCell(5); //cell6.SetCellValue(lists[i].CAddTime.ToString("yyyy年MM月dd日")); //日期值会被当成double cell6.SetCellValue(lists[i].CAddTime); //如何修改日期类型的格式 ICellStyle cs = workbook.CreateCellStyle(); IDataFormat df = workbook.CreateDataFormat(); cs.DataFormat = df.GetFormat("yyyy年MM月dd日"); cell6.CellStyle = cs; } using (FileStream fs = new FileStream("aa.xls", FileMode.Create)) { workbook.Write(fs); MessageBox.Show("ok"); } }
public void SetUp() { wb = new HSSFWorkbook(); ISheet sheet = wb.CreateSheet("new sheet"); style = wb.CreateCellStyle(); IDataFormat fmt = wb.CreateDataFormat(); style.DataFormat=(fmt.GetFormat("hh:mm:ss")); cell11 = sheet.CreateRow(0).CreateCell(0); form = new DataFormatter(); Evaluator = new HSSFFormulaEvaluator(wb); }
/// <summary> /// 设置样式 /// </summary> private static void SetExcelValue(DataTable dt, string sheetname, NPOI.HSSF.UserModel.HSSFWorkbook book, NPOI.SS.UserModel.ICellStyle style) { NPOI.SS.UserModel.ISheet sheet = book.CreateSheet(sheetname); NPOI.SS.UserModel.IRow row = sheet.CreateRow(0); NPOI.SS.UserModel.ICell cell = null; NPOI.SS.UserModel.ICell newCell = null; for (int i = 0; i < dt.Columns.Count; i++) { cell = row.CreateCell(i); cell.CellStyle = style; cell.SetCellValue(dt.Columns[i].ColumnName); } for (int i = 0; i < dt.Rows.Count; i++) { NPOI.SS.UserModel.IRow row2 = sheet.CreateRow(i + 1); for (int j = 0; j < dt.Columns.Count; j++) { string strColDataType = dt.Columns[j].DataType.ToString(); if (strColDataType.Equals("System.Int32")) { int intValue = 0; int.TryParse(dt.Rows[i][j].ToString(), out intValue); row2.CreateCell(j).SetCellValue(intValue); } else if (strColDataType.Equals("System.String")) { row2.CreateCell(j).SetCellValue(dt.Rows[i][j].ToString()); } else if (strColDataType.Equals("System.Double")) { double dblValue = 0; double.TryParse(dt.Rows[i][j].ToString(), out dblValue); row2.CreateCell(j).SetCellValue(dblValue); } else if (strColDataType.Equals("System.DateTime")) { DateTime dateV; DateTime.TryParse(dt.Rows[i][j].ToString(), out dateV); newCell = row2.CreateCell(j); newCell.SetCellValue(dateV); //格式化显示 HSSFCellStyle cellStyle = (HSSFCellStyle)book.CreateCellStyle(); HSSFDataFormat format = (HSSFDataFormat)book.CreateDataFormat(); cellStyle.DataFormat = format.GetFormat("yyyy-m-d"); newCell.CellStyle = cellStyle; } } } }
/// <summary> /// 加载Excel模板样式(xls) /// </summary> /// <param name="file"></param> private static void LoadExcelStyleForXLS(string file) { using (FileStream fs = new FileStream(file, FileMode.Open, FileAccess.Read)) { XLS.HSSFWorkbook hssfworkbook = new XLS.HSSFWorkbook(fs); ISheet s = hssfworkbook.GetSheetAt(0); for (int i = s.FirstRowNum; i <= s.LastRowNum; i++) { ICell c0 = s.GetRow(i).GetCell(0); ICell c1 = s.GetRow(i).GetCell(1); CellStyles.Add(c0.StringCellValue, c1.CellStyle); if (c0.StringCellValue == "DateTime") { IDataFormat format = hssfworkbook.CreateDataFormat(); CellStyles["DateTime"].DataFormat = format.GetFormat(c1.StringCellValue); } } } }
private void btnExportToExcel_Click(object sender, RoutedEventArgs e) { SaveFileDialog sdfExport = new SaveFileDialog(); sdfExport.Filter = "Excel文件|*.xls"; if (sdfExport.ShowDialog() != true) { return; } string filename = sdfExport.FileName; HSSFWorkbook workbook = new HSSFWorkbook(); ISheet sheet = workbook.CreateSheet("员工数据"); IRow rowHeader = sheet.CreateRow(0);//表头行 rowHeader.CreateCell(0, CellType.STRING).SetCellValue("姓名"); rowHeader.CreateCell(1, CellType.STRING).SetCellValue("工号"); rowHeader.CreateCell(2, CellType.STRING).SetCellValue("入职日期"); //把查询结果导出到Excel Employee[] employees = (Employee[])datagrid.ItemsSource; for (int i = 0; i < employees.Length; i++) { Employee employee = employees[i]; IRow row = sheet.CreateRow(i + 1); row.CreateCell(0, CellType.STRING).SetCellValue(employee.Name); row.CreateCell(1, CellType.STRING).SetCellValue(employee.Number); ICellStyle styledate = workbook.CreateCellStyle(); IDataFormat format = workbook.CreateDataFormat(); //格式具体有哪些请看单元格右键中的格式,有说明 styledate.DataFormat = format.GetFormat("yyyy\"年\"m\"月\"d\"日\""); ICell cellInDate = row.CreateCell(2, CellType.NUMERIC); cellInDate.CellStyle = styledate; cellInDate.SetCellValue(employee.InDate); } using (Stream stream = File.OpenWrite(filename)) { workbook.Write(stream); } }
public XlsModel(string sheetName) { SheetName = sheetName; // Create a new workbook and a sheet named "User Accounts" _workbook = new HSSFWorkbook(); _sheet = _workbook.CreateSheet(SheetName); var boldFont = _workbook.CreateFont(); boldFont.FontHeightInPoints = 10; boldFont.FontName = "Arial"; boldFont.Boldweight = (short) FontBoldWeight.Bold; _dateStyle = _workbook.CreateCellStyle(); _dateStyle.DataFormat = _workbook.CreateDataFormat().GetFormat("dd/mm/yyyy"); _nullStyle = _workbook.CreateCellStyle(); _nullStyle.FillForegroundColor = HSSFColor.Grey40Percent.Index; _nullStyle.FillPattern = FillPattern.SolidForeground; _headStyle = _workbook.CreateCellStyle(); _headStyle.SetFont(boldFont); }
/// <summary> /// DataTable导出到Excel的MemoryStream Export() /// </summary> /// <param Name="dtSource">DataTable数据源</param> /// <param Name="strHeaderText">Excel表头文本(例如:车辆列表)</param> public MemoryStream Export(DataTable dtSource, string strHeaderText) { 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 ICellStyle dateStyle = workbook.CreateCellStyle(); IDataFormat format = workbook.CreateDataFormat(); dateStyle.DataFormat = format.GetFormat("yyyy-mm-dd"); int rowIndex = 0; int j = 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 = 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)); // ------------------ } #endregion #region 列头及样式 { 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++; } } #endregion rowIndex = 2; } #endregion #region 填充内容 IRow dataRow = sheet.CreateRow(rowIndex); int columnIndex = 0; DistinctSupervisor(dtSource.Rows[j][6].ToString(), 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.Rows[j][8].ToString() + "\t"); break; case 2: newCell.SetCellValue(dtSource.Rows[j][9].ToString() + "\t"); break; case 3: newCell.SetCellValue(dtSource.Rows[j][10].ToString() + "\t"); break; case 4: newCell.SetCellValue(dtSource.Rows[j][11].ToString() + "\t"); break; case 5: newCell.SetCellValue(dtSource.Rows[j][7].ToString() + "\t"); break; case 6: newCell.SetCellValue(dtSource.Rows[j][2].ToString() + "\t"); break; case 7: newCell.SetCellValue(dtSource.Rows[j][3].ToString() + "\t"); break; case 8: //听课时间 newCell.SetCellValue( CalendarTools.getdata(Common.Year, Convert.ToInt32(dtSource.Rows[j][3]), Convert.ToInt32(dtSource.Rows[j][4]) - CalendarTools.weekdays(CalendarTools.CaculateWeekDay(Common.Year, Common.Month, Common.Day)), Common.Month, Common.Day).ToLongDateString() + " " + addseparator(Convert.ToInt32(dtSource.Rows[j][5])) + "节" + "\t"); break; case 9: newCell.SetCellValue(FormatSupervisor(ListSupervisor) + "\t"); break; case 10: newCell.SetCellValue(dtSource.Rows[j][12].ToString() + "\t"); break; } } else { newCell.SetCellValue(" "); } } #endregion j++; rowIndex++; } adjustcolum(sheet);//调整列宽 AddBorder(sheet,workbook);//加边框 using (MemoryStream ms = new MemoryStream()) { workbook.Write(ms); ms.Flush(); ms.Position = 0; return ms; } }
public ExportEquipmentCategoryColumnTemplateCommand.Result Handle(ExportEquipmentCategoryColumnTemplateCommand command) { var category = _repository.Get <EquipmentCategory>(command.CategoryId); var columns = category.Columns; string fileName = $"{category.Name}设备分类模板-" + DateTime.Now.ToString("yyyyMMddHHmmssfff") + ".xls"; // 文件名称 string urlPath = "Attachments/CategoryTemplate/" + fileName; // 文件下载的URL地址,供给前台下载 string filePath = HttpContext.Current.Server.MapPath("\\" + urlPath); // 文件路径 string directoryName = Path.GetDirectoryName(filePath); if (!Directory.Exists(directoryName)) { if (directoryName != null) { Directory.CreateDirectory(directoryName); } } //创建Excel文件的对象 NPOI.HSSF.UserModel.HSSFWorkbook book = new NPOI.HSSF.UserModel.HSSFWorkbook(); //添加一个sheet NPOI.SS.UserModel.ISheet sheet1 = book.CreateSheet("Sheet1"); //给sheet1添加第一行的头部标题 NPOI.SS.UserModel.IRow rowHeader = sheet1.CreateRow(0); rowHeader.CreateCell(0).SetCellValue("ID"); // 表头列名 rowHeader.CreateCell(1).SetCellValue("设备分类"); // 表头列名 rowHeader.CreateCell(2).SetCellValue("生产厂商"); rowHeader.CreateCell(3).SetCellValue("批次"); rowHeader.CreateCell(4).SetCellValue("产品小类"); rowHeader.CreateCell(5).SetCellValue("产品名称"); rowHeader.CreateCell(6).SetCellValue("产品编码"); rowHeader.CreateCell(7).SetCellValue("规格型号"); rowHeader.CreateCell(8).SetCellValue("材质"); rowHeader.CreateCell(9).SetCellValue("技术人员"); rowHeader.CreateCell(10).SetCellValue("物资人员"); rowHeader.CreateCell(11).SetCellValue("领料人"); rowHeader.CreateCell(12).SetCellValue("出厂日期"); rowHeader.CreateCell(13).SetCellValue("检测人员"); rowHeader.CreateCell(14).SetCellValue("检测结果"); rowHeader.CreateCell(15).SetCellValue("产品执行标准"); rowHeader.CreateCell(16).SetCellValue("安装位置"); var dateIndexes = new List <int>(); for (int i = 0; i < columns.Count; i++) { var cellStartIndex = i + 17; rowHeader.CreateCell(cellStartIndex).SetCellValue(columns[i].ColumnName); // 表头列名 if (columns[i].ColumnType == EquipmentCategoryColumnType.日期.ToString()) { dateIndexes.Add(cellStartIndex); } } IRow row1 = sheet1.CreateRow(1); row1.CreateCell(0).SetCellValue(category.Id); row1.CreateCell(1).SetCellValue(category.Name); foreach (var dateIndex in dateIndexes) { var cell = row1.CreateCell(dateIndex); //设置单元格格式 HSSFCellStyle style = (HSSFCellStyle)book.CreateCellStyle(); HSSFDataFormat format = (HSSFDataFormat)book.CreateDataFormat(); style.DataFormat = format.GetFormat("yyyy年mm月dd日"); cell.CellStyle = style; } // 4.生成文件 FileStream file = new FileStream(filePath, FileMode.Create); book.Write(file); file.Close(); return(new ExportEquipmentCategoryColumnTemplateCommand.Result { IsSucceed = true, UrlPath = urlPath, }); }
public void TestGetDataFormatUniqueIndex() { HSSFWorkbook wb = new HSSFWorkbook(); IDataFormat format = wb.CreateDataFormat(); short formatidx1 = format.GetFormat("YYYY-mm/dd"); short formatidx2 = format.GetFormat("YYYY-mm/dd"); Assert.AreEqual(formatidx1, formatidx2); short formatidx3 = format.GetFormat("000000.000"); Assert.AreNotEqual(formatidx1, formatidx3); }
public void ExportToExcel(int page, int size, int store, string mrf, string stockCode, string stockName, string status, string fd, string td, string enable) { // Get the data to report on var requisitionMasters = _service.ListCondition(page, size, store, mrf, stockCode, stockName, status, fd, td, enable); var requisitionDetails = _service.ListConditionDetailExcel(page, size, store, mrf, stockCode, stockName, status, fd, td, enable).OrderByDescending(i=>i.MRF); // Create a new workbook var workbook = new HSSFWorkbook(); #region Cell Styles #region HeaderLabel Cell Style var headerLabelCellStyle = workbook.CreateCellStyle(); headerLabelCellStyle.Alignment = HorizontalAlignment.CENTER; headerLabelCellStyle.BorderBottom = CellBorderType.THIN; var headerLabelFont = workbook.CreateFont(); headerLabelFont.Boldweight = (short)FontBoldWeight.BOLD; headerLabelCellStyle.SetFont(headerLabelFont); #endregion #region RightAligned Cell Style var rightAlignedCellStyle = workbook.CreateCellStyle(); rightAlignedCellStyle.Alignment = HorizontalAlignment.RIGHT; #endregion #region Currency Cell Style var currencyCellStyle = workbook.CreateCellStyle(); currencyCellStyle.Alignment = HorizontalAlignment.RIGHT; var formatId = HSSFDataFormat.GetBuiltinFormat("$#,##0.00"); if (formatId == -1) { var newDataFormat = workbook.CreateDataFormat(); currencyCellStyle.DataFormat = newDataFormat.GetFormat("$#,##0.00"); } else currencyCellStyle.DataFormat = formatId; #endregion #region Detail Subtotal Style var detailSubtotalCellStyle = workbook.CreateCellStyle(); detailSubtotalCellStyle.BorderTop = CellBorderType.THIN; detailSubtotalCellStyle.BorderBottom = CellBorderType.THIN; var detailSubtotalFont = workbook.CreateFont(); detailSubtotalFont.Boldweight = (short)FontBoldWeight.BOLD; detailSubtotalCellStyle.SetFont(detailSubtotalFont); #endregion #region Detail Currency Subtotal Style var detailCurrencySubtotalCellStyle = workbook.CreateCellStyle(); detailCurrencySubtotalCellStyle.BorderTop = CellBorderType.THIN; detailCurrencySubtotalCellStyle.BorderBottom = CellBorderType.THIN; var detailCurrencySubtotalFont = workbook.CreateFont(); detailCurrencySubtotalFont.Boldweight = (short)FontBoldWeight.BOLD; detailCurrencySubtotalCellStyle.SetFont(detailCurrencySubtotalFont); formatId = HSSFDataFormat.GetBuiltinFormat("$#,##0.00"); if (formatId == -1) { var newDataFormat = workbook.CreateDataFormat(); detailCurrencySubtotalCellStyle.DataFormat = newDataFormat.GetFormat("$#,##0.00"); } else detailCurrencySubtotalCellStyle.DataFormat = formatId; #endregion #endregion #region Requisition sheet var sheet = workbook.CreateSheet("Requistion"); // Add header labels var rowIndex = 0; // Undestand as row in excel. row + 3 = xuong 3 row. var row = sheet.CreateRow(rowIndex); var cell = row.CreateCell(0); cell.SetCellValue("MRF"); cell.CellStyle = headerLabelCellStyle; cell = row.CreateCell(1); cell.SetCellValue("Status"); cell.CellStyle = headerLabelCellStyle; cell = row.CreateCell(2); cell.SetCellValue("From"); cell.CellStyle = headerLabelCellStyle; cell = row.CreateCell(3); cell.SetCellValue("Deliver Date"); cell.CellStyle = headerLabelCellStyle; cell = row.CreateCell(4); cell.SetCellValue("Location"); cell.CellStyle = headerLabelCellStyle; cell = row.CreateCell(5); cell.SetCellValue("Project Code"); cell.CellStyle = headerLabelCellStyle; cell = row.CreateCell(6); cell.SetCellValue("Project Name"); cell.CellStyle = headerLabelCellStyle; cell = row.CreateCell(7); cell.SetCellValue("Created Date"); cell.CellStyle = headerLabelCellStyle; cell = row.CreateCell(8); cell.SetCellValue("Created By"); cell.CellStyle = headerLabelCellStyle; cell = row.CreateCell(9); cell.SetCellValue("Modified Date"); cell.CellStyle = headerLabelCellStyle; cell = row.CreateCell(10); cell.SetCellValue("Modified By"); cell.CellStyle = headerLabelCellStyle; rowIndex++; // Add data rows foreach (var requisition in requisitionMasters) { row = sheet.CreateRow(rowIndex); row.CreateCell(0).SetCellValue(requisition.MRF); row.CreateCell(1).SetCellValue(requisition.Status); row.CreateCell(2).SetCellValue(requisition.From); row.CreateCell(3).SetCellValue(requisition.Deliver_Date != null ? requisition.Deliver_Date.Value.ToString("dd/MM/yyyy") : requisition.Deliver_Date.ToString()); row.CreateCell(4).SetCellValue(requisition.Location); row.CreateCell(5).SetCellValue(requisition.Project_Code); row.CreateCell(6).SetCellValue(requisition.Project_Name); row.CreateCell(7).SetCellValue(requisition.Created_Date != null ? requisition.Created_Date.Value.ToString("dd/MM/yyyy") : requisition.Created_Date.ToString()); row.CreateCell(8).SetCellValue(requisition.Created_By); row.CreateCell(9).SetCellValue(requisition.Modified_Date != null ? requisition.Modified_Date.Value.ToString("dd/MM/yyyy") : requisition.Modified_Date.ToString()); row.CreateCell(10).SetCellValue(requisition.Modified_By); rowIndex++; } // Auto-size each column for (var i = 0; i < sheet.GetRow(0).LastCellNum; i++) { sheet.AutoSizeColumn(i); // Bump up with auto-sized column width to account for bold headers sheet.SetColumnWidth(i, sheet.GetColumnWidth(i) + 1024); } // Add row indicating date/time report was generated... sheet.CreateRow(rowIndex + 1).CreateCell(0).SetCellValue("Report generated on " + DateTime.Now.ToString("dd/MM/yyyy")); #endregion #region Detail sheet sheet = workbook.CreateSheet("Details"); #region Add header labels rowIndex = 0; row = sheet.CreateRow(rowIndex); cell = row.CreateCell(0); cell.SetCellValue("MRF"); cell.CellStyle = headerLabelCellStyle; cell = row.CreateCell(1); cell.SetCellValue("Stock Code"); cell.CellStyle = headerLabelCellStyle; cell = row.CreateCell(2); cell.SetCellValue("Stock Name"); cell.CellStyle = headerLabelCellStyle; cell = row.CreateCell(3); cell.SetCellValue("Stock Type"); cell.CellStyle = headerLabelCellStyle; cell = row.CreateCell(4); cell.SetCellValue("Quantity"); cell.CellStyle = headerLabelCellStyle; cell = row.CreateCell(5); cell.SetCellValue("To be Purchased"); cell.CellStyle = headerLabelCellStyle; cell = row.CreateCell(6); cell.SetCellValue("Follow Up Required"); cell.CellStyle = headerLabelCellStyle; cell = row.CreateCell(7); cell.SetCellValue("Purchased"); cell.CellStyle = headerLabelCellStyle; cell = row.CreateCell(8); cell.SetCellValue("Sent"); cell.CellStyle = headerLabelCellStyle; cell = row.CreateCell(9); cell.SetCellValue("Status"); cell.CellStyle = headerLabelCellStyle; cell = row.CreateCell(10); cell.SetCellValue("Date Assign"); cell.CellStyle = headerLabelCellStyle; cell = row.CreateCell(11); cell.SetCellValue("Remark"); cell.CellStyle = headerLabelCellStyle; cell = row.CreateCell(12); cell.SetCellValue("Unit"); cell.CellStyle = headerLabelCellStyle; cell = row.CreateCell(13); cell.SetCellValue("Category"); cell.CellStyle = headerLabelCellStyle; cell = row.CreateCell(14); cell.SetCellValue("Part No"); cell.CellStyle = headerLabelCellStyle; cell = row.CreateCell(15); cell.SetCellValue("Ral No"); cell.CellStyle = headerLabelCellStyle; cell = row.CreateCell(16); cell.SetCellValue("Color"); cell.CellStyle = headerLabelCellStyle; rowIndex++; #endregion // Add data rows var lastProductName = string.Empty; // For sum in excel // var startRowIndexForProductDetails = 1; foreach (var detail in requisitionDetails) { // Show a summary row for each new product var productNameToShow = string.Empty; if (string.Compare(detail.MRF, lastProductName) != 0) { if (!string.IsNullOrEmpty(lastProductName)) { // Add the subtotal row // AddSubtotalRow(sheet, startRowIndexForProductDetails, rowIndex, detailSubtotalCellStyle, detailCurrencySubtotalCellStyle); // rowIndex += 3; } productNameToShow = detail.MRF; lastProductName = detail.MRF; // startRowIndexForProductDetails = rowIndex; } row = sheet.CreateRow(rowIndex); row.CreateCell(0).SetCellValue(productNameToShow); row.CreateCell(1).SetCellValue(detail.Stock_Code); row.CreateCell(2).SetCellValue(detail.Stock_Name); row.CreateCell(3).SetCellValue(detail.Type); row.CreateCell(4).SetCellValue(detail.Quantity.ToString()); row.CreateCell(5).SetCellValue(detail.Quantity_PE.ToString()); row.CreateCell(6).SetCellValue(detail.FollowUpRequired.ToString()); row.CreateCell(7).SetCellValue(detail.Purchased.ToString()); row.CreateCell(8).SetCellValue(detail.Sent.ToString()); row.CreateCell(9).SetCellValue(detail.Status); row.CreateCell(10).SetCellValue(detail.Created_Date != null ? detail.Created_Date.Value.ToString("dd/MM/yyyy") : detail.Created_Date.ToString()); row.CreateCell(11).SetCellValue(detail.Remark); row.CreateCell(12).SetCellValue(detail.Unit); row.CreateCell(13).SetCellValue(detail.Category); row.CreateCell(14).SetCellValue(detail.Part_No); row.CreateCell(15).SetCellValue(detail.Ral_No); row.CreateCell(16).SetCellValue(detail.Color); //if (productDetail.Order.OrderDate.HasValue) //{ // cell = row.CreateCell(2); // cell.SetCellValue(productDetail.Order.OrderDate.Value.ToShortDateString()); // cell.CellStyle = rightAlignedCellStyle; //} //cell = row.CreateCell(3); //cell.SetCellType(CellType.NUMERIC); //cell.SetCellValue((double)productDetail.UnitPrice); //cell.CellStyle = currencyCellStyle; //cell = row.CreateCell(5); //cell.SetCellType(CellType.NUMERIC); //cell.SetCellValue((double)productDetail.UnitPrice * productDetail.Quantity); //cell.CellStyle = currencyCellStyle; rowIndex++; } // Add the subtotal row for the last product // AddSubtotalRow(sheet, startRowIndexForProductDetails, rowIndex, detailSubtotalCellStyle, detailCurrencySubtotalCellStyle); // rowIndex += 2; // Auto-size each column for (var i = 0; i < sheet.GetRow(0).LastCellNum; i++) { sheet.AutoSizeColumn(i); // Bump up with auto-sized column width to account for bold headers sheet.SetColumnWidth(i, sheet.GetColumnWidth(i) + 1024); } // Add row indicating date/time report was generated... // sheet.CreateRow(rowIndex + 1).CreateCell(0).SetCellValue("Report generated on " + DateTime.Now.ToString("dd/MM/yyyy")); #endregion // Save the Excel spreadsheet to a MemoryStream and return it to the client using (var exportData = new MemoryStream()) { workbook.Write(exportData); var saveAsFileName = string.Format("Requisition-{0}.xls", DateTime.Now.ToString("ddMMyyyyHHmmss")).Replace("/", "-"); Response.ContentType = "application/vnd.ms-excel"; Response.AddHeader("Content-Disposition", string.Format("attachment;filename={0}", saveAsFileName)); Response.Clear(); Response.BinaryWrite(exportData.GetBuffer()); Response.End(); } }
/// </summary> /// <param name="dt"> 数据源</param> /// <returns>stream</returns> public static Stream StreamData(DataTable dt, string sheetname, string strHeaderText) { HSSFWorkbook workbook = new HSSFWorkbook(); MemoryStream ms = new MemoryStream(); HSSFSheet sheet = null; HSSFCellStyle dateStyle = (HSSFCellStyle)workbook.CreateCellStyle(); HSSFDataFormat format = (HSSFDataFormat)workbook.CreateDataFormat(); dateStyle.DataFormat = format.GetFormat("yyyy-mm-dd"); //取得列宽 int[] arrColWidth = new int[dt.Columns.Count]; foreach (DataColumn item in dt.Columns) { arrColWidth[item.Ordinal] = Encoding.GetEncoding(936).GetBytes(item.ColumnName.ToString()).Length; } for (int i = 0; i < dt.Rows.Count; i++) { for (int j = 0; j < dt.Columns.Count; j++) { int intTemp = Encoding.GetEncoding(936).GetBytes(dt.Rows[i][j].ToString()).Length; if (intTemp > arrColWidth[j]) { arrColWidth[j] = intTemp; } } } sheet = (HSSFSheet)workbook.CreateSheet(sheetname); #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 Region(0, 0, 0, dt.Columns.Count - 1)); headerRow = null; //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 dt.Columns) { headerRow.CreateCell(column.Ordinal).SetCellValue(column.ColumnName); //遍历datatable,将datatable的列名赋予sheet headerRow.GetCell(column.Ordinal).CellStyle = headStyle; //设置列宽 sheet.SetColumnWidth(column.Ordinal, (arrColWidth[column.Ordinal] + 1) * 256); } headerRow = null; } #endregion int index = 2; //表头和列头已经占用一行,所以从2开始 foreach (DataRow row in dt.Rows) { HSSFRow datarow = (HSSFRow)sheet.CreateRow(index); foreach (DataColumn column in dt.Columns) { // row.CreateCell(column.Ordinal).SetCellValue(datarow[column].ToString()); //遍历并将值放入sheet HSSFCell newCell = (HSSFCell)datarow.CreateCell(column.Ordinal); // 实例化cell string drValue = row[column].ToString(); if (drValue == null || drValue == "") { newCell.SetCellValue(""); continue; } switch (column.DataType.ToString()) { case "System.String"://字符串类型 case "System.DateTime"://日期类型 newCell.SetCellValue(drValue); 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": case "System.Float": case "System.Single": double doubV = 0; double.TryParse(drValue, out doubV); newCell.SetCellValue(doubV); break; case "System.DBNull"://空值处理 newCell.SetCellValue(""); break; default: newCell.SetCellValue(""); break; } } index++; } workbook.Write(ms); ms.Flush(); ms.Position = 0; //headerrow = null; //workbook = null; workbook.Dispose(); return ms; }
/// <summary> /// /// </summary> /// <param name="table"></param> /// <param name="headerText"></param> /// <param name="sheetName"></param> /// <param name="columnName"></param> /// <param name="columnTitle"></param> /// <returns></returns> public static HSSFWorkbook GenerateData(DataTable table, string headerText, string sheetName, string[] columnName, string[] columnTitle) { HSSFWorkbook hssfworkbook = new HSSFWorkbook(); ISheet sheet = hssfworkbook.CreateSheet(sheetName); #region 设置文件属性信息 //创建一个文档摘要信息实体。 DocumentSummaryInformation dsi = PropertySetFactory.CreateDocumentSummaryInformation(); dsi.Company = "Weilog Team"; //公司名称 hssfworkbook.DocumentSummaryInformation = dsi; //创建一个摘要信息实体。 SummaryInformation si = PropertySetFactory.CreateSummaryInformation(); si.Author = " Weilog 系统"; si.Title = headerText; si.Subject = headerText; si.CreateDateTime = DateTime.Now; hssfworkbook.SummaryInformation = si; #endregion ICellStyle dateStyle = hssfworkbook.CreateCellStyle(); IDataFormat format = hssfworkbook.CreateDataFormat(); dateStyle.DataFormat = format.GetFormat("yyyy-mm-dd"); #region 取得列宽 int[] colWidth = new int[columnName.Length]; for (int i = 0; i < columnName.Length; i++) { colWidth[i] = Encoding.GetEncoding(936).GetBytes(columnTitle[i]).Length; } for (int i = 0; i < table.Rows.Count; i++) { for (int j = 0; j < columnName.Length; j++) { int intTemp = Encoding.GetEncoding(936).GetBytes(table.Rows[i][columnName[j]].ToString()).Length; if (intTemp > colWidth[j]) { colWidth[j] = intTemp; } } } #endregion int rowIndex = 0; foreach (DataRow row in table.Rows) { #region 新建表,填充表头,填充列头,样式 if (rowIndex == 65535 || rowIndex == 0) { if (rowIndex != 0) { sheet = hssfworkbook.CreateSheet(sheetName + ((int)rowIndex / 65535).ToString()); } #region 表头及样式 //if (!string.IsNullOrEmpty(headerText)) { IRow headerRow = sheet.CreateRow(0); headerRow.HeightInPoints = 25; headerRow.CreateCell(0).SetCellValue(headerText); ICellStyle headStyle = hssfworkbook.CreateCellStyle(); headStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center; IFont font = hssfworkbook.CreateFont(); font.FontHeightInPoints = 20; font.Boldweight = 700; headStyle.SetFont(font); headerRow.GetCell(0).CellStyle = headStyle; //sheet.AddMergedRegion(new Region(0, 0, 0, dtSource.Columns.Count - 1)); sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(0, 0, 0, table.Columns.Count - 1)); } #endregion #region 列头及样式 { //HSSFRow headerRow = sheet.CreateRow(1); IRow headerRow; //if (!string.IsNullOrEmpty(headerText)) //{ // headerRow = sheet.CreateRow(0); //} //else //{ headerRow = sheet.CreateRow(1); //} ICellStyle headStyle = hssfworkbook.CreateCellStyle(); headStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center; IFont font = hssfworkbook.CreateFont(); font.FontHeightInPoints = 10; font.Boldweight = 700; headStyle.SetFont(font); for (int i = 0; i < columnName.Length; i++) { headerRow.CreateCell(i).SetCellValue(columnTitle[i]); headerRow.GetCell(i).CellStyle = headStyle; //设置列宽 if ((colWidth[i] + 1) * 256 > 30000) { sheet.SetColumnWidth(i, 10000); } else { sheet.SetColumnWidth(i, (colWidth[i] + 1) * 256); } } /* 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 //if (!string.IsNullOrEmpty(headerText)) //{ // rowIndex = 1; //} //else //{ rowIndex = 2; //} } #endregion #region 填充数据 IRow dataRow = sheet.CreateRow(rowIndex); for (int i = 0; i < columnName.Length; i++) { ICell newCell = dataRow.CreateCell(i); string drValue = row[columnName[i]].ToString(); switch (table.Columns[columnName[i]].DataType.ToString()) { case "System.String"://字符串类型 if (drValue.ToUpper() == "TRUE") newCell.SetCellValue("是"); else if (drValue.ToUpper() == "FALSE") newCell.SetCellValue("否"); 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); if (boolV) newCell.SetCellValue("是"); else newCell.SetCellValue("否"); 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++; } return hssfworkbook; }
/// <summary> /// Adjusts the column width to fit the contents. /// This Process can be relatively slow on large sheets, so this should /// normally only be called once per column, at the end of your /// Processing. /// You can specify whether the content of merged cells should be considered or ignored. /// Default is to ignore merged cells. /// </summary> /// <param name="column">the column index</param> /// <param name="useMergedCells">whether to use the contents of merged cells when calculating the width of the column</param> public void AutoSizeColumn(int column, bool useMergedCells) { /** * Excel measures columns in Units of 1/256th of a Char width * but the docs say nothing about what particular Char is used. * '0' looks to be a good choice. */ char defaultChar = '0'; /** * This is the multiple that the font height is scaled by when determining the * boundary of rotated text. */ double fontHeightMultiple = 2.0; //FontRenderContext frc = new FontRenderContext(null, true, true); HSSFWorkbook wb = new HSSFWorkbook(book); NPOI.SS.UserModel.Font defaultFont = wb.GetFontAt((short)0); //str = new AttributedString("" + defaultChar); //CopyAttributes(defaultFont, str, 0, 1); //layout = new TextLayout(str.GetEnumerator(), frc); System.Drawing.Font font = HSSFFont2Font((HSSFFont)defaultFont); int defaultCharWidth = TextRenderer.MeasureText("" + new String(defaultChar, 1), font).Width; double width = -1; bool skipthisrow = false; for (IEnumerator it = rows.Values.GetEnumerator(); it.MoveNext(); ) { HSSFRow row = (HSSFRow)it.Current; NPOI.SS.UserModel.Cell cell = (HSSFCell)row.GetCell(column); if (cell == null) continue; int colspan = 1; for (int i = 0; i < NumMergedRegions; i++) { NPOI.SS.Util.CellRangeAddress region = GetMergedRegion(i); if (ContainsCell(region, row.RowNum, column)) { if (!useMergedCells) { // If we're not using merged cells, skip this one and move on to the next. skipthisrow = true; } cell = row.GetCell(region.FirstColumn); colspan = 1 + region.LastColumn - region.FirstColumn; } } if (skipthisrow) { continue; } NPOI.SS.UserModel.CellStyle style = cell.CellStyle; NPOI.SS.UserModel.Font font1 = wb.GetFontAt(style.FontIndex); if (cell.CellType == NPOI.SS.UserModel.CellType.STRING) { HSSFRichTextString rt = (HSSFRichTextString)cell.RichStringCellValue; String[] lines = rt.String.Split(new char[] { '\n' }); for (int k = 0; k < lines.Length; k++) { String txt = lines[k] + defaultChar; //str = new AttributedString(txt); //copyAttributes(font1, str, 0, txt.Length); if (rt.NumFormattingRuns > 0) { for (int j = 0; j < lines[k].Length; j++) { int idx = rt.GetFontAtIndex(j); if (idx != 0) { NPOI.SS.UserModel.Font fnt = wb.GetFontAt((short)idx); //copyAttributes(fnt, str, j, j + 1); } } } //layout = new TextLayout(str.GetEnumerator(), frc); if (style.Rotation != 0) { /* * Transform the text using a scale so that it's height is increased by a multiple of the leading, * and then rotate the text before computing the bounds. The scale results in some whitespace around * the Unrotated top and bottom of the text that normally wouldn't be present if Unscaled, but * is Added by the standard Excel autosize. */ double angle = style.Rotation * 2.0 * Math.PI / 360.0; //Tony Qu //TODO:: text rotated width measure //AffineTransform trans = new AffineTransform(); //trans.concatenate(AffineTransform.GetRotateInstance(style.Rotation*2.0*Math.PI/360.0)); //trans.concatenate( //AffineTransform.GetScaleInstance(1, fontHeightMultiple) //); width = Math.Max(width, ((TextRenderer.MeasureText(txt, font).Width * 1.0 / colspan) / defaultCharWidth) + cell.CellStyle.Indention); throw new NotImplementedException(); } else { //width = Math.Max(width, ((TextRenderer.MeasureText(txt, font).Width / colspan) / defaultCharWidth) + cell.CellStyle.Indention); width = Math.Max(width, (TextRenderer.MeasureText(txt, font).Width * 1.0 / colspan / defaultCharWidth) * 2 + cell.CellStyle.Indention); } } } else { String sval = null; if (cell.CellType == NPOI.SS.UserModel.CellType.NUMERIC) { NPOI.SS.UserModel.DataFormat dataformat = wb.CreateDataFormat(); short idx = style.DataFormat; String format = "General"; if (idx >= 0) { format = dataformat.GetFormat(idx).Replace("\"", ""); } double value = cell.NumericCellValue; try { if ("General".Equals(format)) sval = "" + value; else { sval = value.ToString("F"); } } catch (Exception) { sval = "" + value; } } else if (cell.CellType == NPOI.SS.UserModel.CellType.BOOLEAN) { sval = cell.BooleanCellValue.ToString(); } if (sval != null) { String txt = sval + defaultChar; //str = new AttributedString(txt); //copyAttributes(font, str, 0, txt.Length); //layout = new TextLayout(str.GetEnumerator(), frc); if (style.Rotation != 0) { /* * Transform the text using a scale so that it's height is increased by a multiple of the leading, * and then rotate the text before computing the bounds. The scale results in some whitespace around * the Unrotated top and bottom of the text that normally wouldn't be present if Unscaled, but * is Added by the standard Excel autosize. */ //Tony Qu //TODO:: text rotated width measure //AffineTransform trans = new AffineTransform(); //trans.concatenate(AffineTransform.GetRotateInstance(style.Rotation*2.0*Math.PI/360.0)); //trans.concatenate( //AffineTransform.GetScaleInstance(1, fontHeightMultiple) //); //width = Math.Max(width, ((layout.GetOutline(trans).GetBounds().Width / colspan) / defaultCharWidth) + cell.CellStyle.Indention); throw new NotImplementedException(); } else { //width = Math.Max(width, ((TextRenderer.MeasureText(txt, font).Width / colspan) / defaultCharWidth) + cell.CellStyle.Indention); width = Math.Max(width, (TextRenderer.MeasureText(txt, font).Width * 1.0 / colspan / defaultCharWidth) * 2 + cell.CellStyle.Indention); } } } } if (width != -1) { if (width > short.MaxValue) { //width can be bigger that Short.MAX_VALUE! width = short.MaxValue; } _sheet.SetColumnWidth(column, (short)(width * 256)); } }
/// <summary> /// DataTable导出到Excel的MemoryStream /// </summary> /// <param name="dtSource">源DataTable</param> /// <param name="strHeaderText">表头文本</param> public static MemoryStream Export(DataTable dtSource, string strHeaderText = "统计数据", string strImageFileName = "") { IWorkbook workbook = new HSSFWorkbook(); #region 填充图片 if (strImageFileName != "") { ISheet sheet1 = workbook.CreateSheet("图标"); byte[] bytes = System.IO.File.ReadAllBytes(strImageFileName); int pictureIdx = workbook.AddPicture(bytes, PictureType.JPEG); // Create the drawing patriarch. This is the top level container for all shapes. HSSFPatriarch patriarch = (HSSFPatriarch)sheet1.CreateDrawingPatriarch(); //add a picture HSSFClientAnchor anchor = new HSSFClientAnchor(0, 0, 0, 0, 0, 0, 20, 20); HSSFPicture pict = (HSSFPicture)patriarch.CreatePicture(anchor, pictureIdx); pict.Resize(); //rowIndex += 20; } #endregion if (strHeaderText == "") { strHeaderText = "统计数据"; } ISheet sheet = workbook.CreateSheet(strHeaderText); 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; arrColWidth[item.Ordinal] = arrColWidth[item.Ordinal] > 50 ? 50 : arrColWidth[item.Ordinal]; } 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] && intTemp < 50) { arrColWidth[j] = intTemp; } } } int rowIndex = 0; foreach (DataRow row in dtSource.Rows) { #region 新建表,填充表头,填充列头,样式 if (rowIndex == 65535 || rowIndex == 0) { if (rowIndex != 0) { sheet = workbook.CreateSheet(); } if (strHeaderText != "") { #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 NPOI.SS.Util.CellRangeAddress(0, 0, 0, dtSource.Columns.Count - 1)); //headerRow.Dispose(); } #endregion rowIndex = 1; } else { rowIndex = 0; } #region 列头及样式 { IRow headerRow = sheet.CreateRow(rowIndex); 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); } //headerRow.Dispose(); } #endregion rowIndex++; } #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; } }
/// <summary> /// 创建工作表 /// </summary> /// <param name="workBook"></param> /// <param name="dt"></param> /// <param name="name"></param> /// <param name="header"></param> /// <returns></returns> private static HSSFSheet CreateSheet(HSSFWorkbook workBook, DataTable dt, string name = "sheet1", string header = "") { HSSFSheet sheet = (HSSFSheet)workBook.CreateSheet(name); HSSFCellStyle dateStyle = (HSSFCellStyle)workBook.CreateCellStyle(); HSSFDataFormat format = (HSSFDataFormat)workBook.CreateDataFormat(); dateStyle.DataFormat = format.GetFormat("yyyy-mm-dd"); //取得列宽 int[] arrColWidth = new int[dt.Columns.Count]; foreach (DataColumn item in dt.Columns) { arrColWidth[item.Ordinal] = Encoding.GetEncoding(936).GetBytes(item.ColumnName.ToString()).Length; } for (int i = 0; i < dt.Rows.Count; i++) { for (int j = 0; j < dt.Columns.Count; j++) { int intTemp = Encoding.GetEncoding(936).GetBytes(dt.Rows[i][j].ToString()).Length; if (intTemp > arrColWidth[j]) { arrColWidth[j] = intTemp; } } } int rowIndex = 0; foreach (DataRow row in dt.Rows) { #region 新建表,填充表头,填充列头,样式 if (rowIndex == 65535 || rowIndex == 0) { if (rowIndex != 0) { sheet = (HSSFSheet)workBook.CreateSheet(); } #region 表头及样式 { if (!string.IsNullOrEmpty(header)) { HSSFRow headerRow = (HSSFRow)sheet.CreateRow(0); headerRow.HeightInPoints = 25; headerRow.CreateCell(0).SetCellValue(header); HSSFCellStyle headStyle = (HSSFCellStyle)workBook.CreateCellStyle(); // headStyle.Alignment = CellHorizontalAlignment.CENTER; HSSFFont font = (HSSFFont)workBook.CreateFont(); font.FontHeightInPoints = 20; font.Boldweight = 700; font.FontName = "微软雅黑"; headStyle.SetFont(font); headerRow.GetCell(0).CellStyle = headStyle; // sheet.AddMergedRegion(new Region(0, 0, 0, dtSource.Columns.Count - 1)); //headerRow.Dispose(); } } #endregion #region 列头及样式 { HSSFRow headerRow = (HSSFRow)sheet.CreateRow(1); HSSFCellStyle headStyle = (HSSFCellStyle)workBook.CreateCellStyle(); //headStyle.Alignment = CellHorizontalAlignment.CENTER; HSSFFont font = (HSSFFont)workBook.CreateFont(); font.FontHeightInPoints = 10; font.Boldweight = 700; font.FontName = "微软雅黑"; headStyle.SetFont(font); foreach (DataColumn column in dt.Columns) { headerRow.CreateCell(column.Ordinal).SetCellValue(column.ColumnName); headerRow.GetCell(column.Ordinal).CellStyle = headStyle; //设置列宽 sheet.SetColumnWidth(column.Ordinal, (arrColWidth[column.Ordinal] + 1) * 256); } // headerRow.Dispose(); } #endregion rowIndex = 2; } #endregion #region 填充内容 HSSFRow dataRow = (HSSFRow)sheet.CreateRow(rowIndex); HSSFCellStyle cellStyle = (HSSFCellStyle)workBook.CreateCellStyle(); HSSFFont cellFont = (HSSFFont)workBook.CreateFont(); cellFont.FontName = "微软雅黑"; cellStyle.SetFont(cellFont); foreach (DataColumn column in dt.Columns) { HSSFCell newCell = (HSSFCell)dataRow.CreateCell(column.Ordinal); string drValue = row[column].ToString(); switch (column.DataType.ToString()) { case "System.String"://字符串类型 newCell.SetCellValue(drValue); break; case "System.DateTime"://日期类型 System.DateTime dateV; System.DateTime.TryParse(drValue, out dateV); newCell.SetCellValue(dateV); newCell.CellStyle = dateStyle;//格式化显示 break; case "System.Boolean"://布尔型 bool boolV = false; bool.TryParse(drValue, out boolV); newCell.SetCellValue(boolV); break; case "System.Int16"://整型 case "System.Int32": case "System.Int64": case "System.Byte": int intV = 0; int.TryParse(drValue, out intV); newCell.SetCellValue(intV); break; case "System.Decimal"://浮点型 case "System.Double": double doubV = 0; double.TryParse(drValue, out doubV); newCell.SetCellValue(doubV); break; case "System.DBNull"://空值处理 newCell.SetCellValue(""); break; default: newCell.SetCellValue(""); break; } newCell.CellStyle = cellStyle; } #endregion rowIndex++; } return sheet; }
/// <summary> /// Create an Excel document with a nice breakdown of Trello. /// </summary> /// <returns></returns> public static byte[] ToExcel() { List<Board> _boards = ParseBoardData(); // New workbook var workbook = new HSSFWorkbook(); foreach (Board _boardInfo in _boards) { JavaScriptSerializer _worker = new JavaScriptSerializer(); _worker.MaxJsonLength = int.MaxValue; File.WriteAllText(HttpContext.Current.Server.MapPath("") + @"\output\" + _boardInfo.name.ToString() + ".json", _worker.Serialize(_boardInfo)); // Create a sheet var sheet = workbook.CreateSheet(_boardInfo.name.ToString()); // Kill everything not supposed to be in there. var rowIndex = 0; #region HEADING STYLE HSSFFont _headingFont = (HSSFFont)workbook.CreateFont(); _headingFont.Boldweight = (short)FontBoldWeight.Bold; _headingFont.Color = HSSFColor.Grey80Percent.Index; //_headingFont.FontHeight = 280; HSSFCellStyle _headingStyle = (HSSFCellStyle)workbook.CreateCellStyle(); _headingStyle = (HSSFCellStyle)workbook.CreateCellStyle(); _headingStyle.VerticalAlignment = VerticalAlignment.Center; _headingStyle.SetFont(_headingFont); #endregion // Create the heading var row = sheet.CreateRow(rowIndex); row.Height = 350; HSSFCell cell = (HSSFCell)row.CreateCell(0); cell.SetCellValue("Card name"); cell.CellStyle = _headingStyle; cell = (HSSFCell)row.CreateCell(1); cell.SetCellValue("Date created"); cell.CellStyle = _headingStyle; cell = (HSSFCell)row.CreateCell(2); cell.SetCellValue("Pending"); cell.CellStyle = _headingStyle; cell = (HSSFCell)row.CreateCell(3); cell.SetCellValue("In Progress"); cell.CellStyle = _headingStyle; cell = (HSSFCell)row.CreateCell(4); cell.SetCellValue("In Testing"); cell.CellStyle = _headingStyle; cell = (HSSFCell)row.CreateCell(5); cell.SetCellValue("Released"); cell.CellStyle = _headingStyle; cell = (HSSFCell)row.CreateCell(6); cell.SetCellValue("On Hold"); cell.CellStyle = _headingStyle; cell = (HSSFCell)row.CreateCell(7); cell.SetCellValue("Description"); cell.CellStyle = _headingStyle; // Next row rowIndex++; #region ROW STYLE HSSFCellStyle _rowStyle = (HSSFCellStyle)workbook.CreateCellStyle(); _rowStyle.VerticalAlignment = VerticalAlignment.Center; IDataFormat _dataFormat = workbook.CreateDataFormat(); #endregion if (_boardInfo.Lists != null) { foreach (Board.List _listData in _boardInfo.Lists) { if (_listData.Cards != null) { foreach (Board.List.Card _cardData in _listData.Cards) { // Add the row row = sheet.CreateRow(rowIndex); row.Height = 350; // Card name cell = (HSSFCell)row.CreateCell(0); cell.SetCellValue(_cardData.name.ToString()); cell.CellStyle = _rowStyle; // Created cell = (HSSFCell)row.CreateCell(1); DateTime _startDate = new DateTime(1970, 01, 01); cell.SetCellValue(_startDate.AddSeconds(int.Parse(_cardData.id.ToString().Substring(0, 8), System.Globalization.NumberStyles.HexNumber)).ToString("dd/MM/yyyy HH:mm:ss")); cell.CellStyle = _rowStyle; // Actions foreach (Board.List.Card.Action _actionData in _cardData.Actions) { if (_actionData.data != null) { if (_actionData.data.listAfter != null && _actionData.data.listAfter.name.ToString().ToLower().Contains("pending")) { cell = (HSSFCell)row.CreateCell(2); cell.SetCellValue(DateTime.Parse(_actionData.date.ToString()).ToString("dd/MM/yyyy HH:mm:ss")); cell.CellStyle = _rowStyle; } else if (_actionData.data.listAfter != null && _actionData.data.listAfter.name.ToString().ToLower().Contains("progress")) { cell = (HSSFCell)row.CreateCell(3); cell.SetCellValue(DateTime.Parse(_actionData.date.ToString()).ToString("dd/MM/yyyy HH:mm:ss")); cell.CellStyle = _rowStyle; } else if (_actionData.data.listAfter != null && _actionData.data.listAfter.name.ToString().ToLower().Contains("testing")) { cell = (HSSFCell)row.CreateCell(4); cell.SetCellValue(DateTime.Parse(_actionData.date.ToString()).ToString("dd/MM/yyyy HH:mm:ss")); cell.CellStyle = _rowStyle; } else if (_actionData.data.listAfter != null && _actionData.data.listAfter.name.ToString().ToLower().Contains("release")) { cell = (HSSFCell)row.CreateCell(5); cell.SetCellValue(DateTime.Parse(_actionData.date.ToString()).ToString("dd/MM/yyyy HH:mm:ss")); cell.CellStyle = _rowStyle; } else if (_actionData.data.listAfter != null && _actionData.data.listAfter.name.ToString().ToLower().Contains("hold")) { cell = (HSSFCell)row.CreateCell(6); cell.SetCellValue(DateTime.Parse(_actionData.date.ToString()).ToString("dd/MM/yyyy HH:mm:ss")); cell.CellStyle = _rowStyle; } } } // Description cell = (HSSFCell)row.CreateCell(7); cell.SetCellValue(_cardData.desc.ToString()); cell.CellStyle = _rowStyle; // Next row. rowIndex++; } } } // Size it sheet.AutoSizeColumn(0); sheet.AutoSizeColumn(1); sheet.AutoSizeColumn(2); sheet.AutoSizeColumn(3); sheet.AutoSizeColumn(4); sheet.AutoSizeColumn(5); sheet.AutoSizeColumn(6); sheet.AutoSizeColumn(7); sheet.CreateFreezePane(0, 1); } } #region OUTPUT // Save the Excel spreadsheet to a MemoryStream and return it to the client using (var exportData = new MemoryStream()) { workbook.Write(exportData); return exportData.GetBuffer(); } #endregion }
/// <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; }
public void TestWriteDataFormat() { string filepath = TempFile.GetTempFilePath("TestWriteSheetSimple", ".xls"); FileStream out1 = new FileStream(filepath, FileMode.OpenOrCreate); HSSFWorkbook wb = new HSSFWorkbook(); NPOI.SS.UserModel.ISheet s = wb.CreateSheet(); IRow r = null; ICell c = null; IDataFormat format = wb.CreateDataFormat(); NPOI.SS.UserModel.ICellStyle cs = wb.CreateCellStyle(); short df = format.GetFormat("0.0"); cs.DataFormat=(df); r = s.CreateRow(0); c = r.CreateCell(0); c.CellStyle=(cs); c.SetCellValue(1.25); wb.Write(out1); out1.Close(); FileStream stream = new FileStream(filepath,FileMode.OpenOrCreate); POIFSFileSystem fs = new POIFSFileSystem(stream); HSSFWorkbook workbook = new HSSFWorkbook(fs); NPOI.SS.UserModel.ISheet sheet = workbook.GetSheetAt(0); ICell cell = sheet.GetRow(0).GetCell(0); format = workbook.CreateDataFormat(); Assert.AreEqual(1.25, cell.NumericCellValue, 1e-10); Assert.AreEqual(format.GetFormat(df), "0.0"); Assert.AreEqual(format, workbook.CreateDataFormat()); stream.Close(); }
/// <summary> /// DataTable导出到Excel的MemoryStream /// </summary> /// <param name="dtSource">源DataTable</param> /// <param name="strHeaderText">表头文本</param> public static MemoryStream Export(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++) { int intTemp = Encoding.GetEncoding(936).GetBytes(dtSource.Rows[i][j].ToString()).Length; if (intTemp > arrColWidth[j]) { arrColWidth[j] = intTemp; } } } int rowIndex = 0; foreach (DataRow row in dtSource.Rows) { #region 新建表,填充表头,填充列头,样式 if (rowIndex == 65535 || rowIndex == 0) { if (rowIndex != 0) { sheet = (HSSFSheet)workbook.CreateSheet(); } #region 表头及样式 { HSSFRow headerRow = (HSSFRow)sheet.CreateRow(0); headerRow.HeightInPoints = 25; headerRow.CreateCell(0).SetCellValue(strHeaderText); HSSFCellStyle headStyle = (HSSFCellStyle)workbook.CreateCellStyle(); headStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center; HSSFFont font = (HSSFFont)workbook.CreateFont(); font.FontHeightInPoints = 20; font.Boldweight = 700; headStyle.SetFont(font); headerRow.GetCell(0).CellStyle = headStyle; sheet.AddMergedRegion(new Region(0, 0, 0, dtSource.Columns.Count - 1)); //headerRow. } #endregion #region 列头及样式 { HSSFRow headerRow = (HSSFRow)sheet.CreateRow(1); HSSFCellStyle headStyle = (HSSFCellStyle)workbook.CreateCellStyle(); headStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center; HSSFFont font = (HSSFFont)workbook.CreateFont(); font.FontHeightInPoints = 10; font.Boldweight = 700; headStyle.SetFont(font); foreach (DataColumn column in dtSource.Columns) { headerRow.CreateCell(column.Ordinal).SetCellValue(column.ColumnName); headerRow.GetCell(column.Ordinal).CellStyle = headStyle; //设置列宽 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++; } using (MemoryStream ms = new MemoryStream()) { workbook.Write(ms); ms.Flush(); ms.Position = 0; return ms; } }
private void ConvertStyle(HSSFWorkbook workbook, string name) { ExcelStyle cf = ExcelStyles[name]; Font font = workbook.CreateFont(); CellStyle style = workbook.CreateCellStyle(); style.FillForegroundColor = GetColor(cf.ForegroundColor); style.FillPattern = GetPatternType(cf.ForegroundPattern); font.Boldweight = (short)GetBoldWeight(cf.Bold); if (!string.IsNullOrEmpty(cf.FontName)) font.FontName = cf.FontName; font.FontHeightInPoints = cf.FontHeight; style.SetFont(font); if (cf.DataFormat != string.Empty) { DataFormat format = workbook.CreateDataFormat(); short formatIndex = format.GetFormat(cf.DataFormat); style.DataFormat = formatIndex; } this.CellStyles.Add(name, style); }
public void Export2Excel(string fileName, DataSet data) { MemoryStream ms = new MemoryStream(); HSSFWorkbook hssfworkbook = new HSSFWorkbook(); HSSFSheet sheet = (HSSFSheet)hssfworkbook.CreateSheet(); #region 文件属性 DocumentSummaryInformation dsi = PropertySetFactory.CreateDocumentSummaryInformation(); dsi.Company = "xi"; hssfworkbook.DocumentSummaryInformation = dsi; SummaryInformation si = PropertySetFactory.CreateSummaryInformation(); si.Author = "xi"; si.ApplicationName = "xi"; si.LastAuthor = "xi"; si.CreateDateTime = DateTime.Now; hssfworkbook.SummaryInformation = si; #endregion #region Excel单元格格式 int rowIndex = 0; HSSFRow headRow = null; HSSFRow titleRow = null; HSSFRow dataRow = null; HSSFCell cell = null; HSSFCellStyle headStyle = (HSSFCellStyle)hssfworkbook.CreateCellStyle(); headStyle.Alignment = HorizontalAlignment.CENTER; headStyle.VerticalAlignment = VerticalAlignment.CENTER; HSSFFont headfont = (HSSFFont)hssfworkbook.CreateFont(); headStyle.SetFont(headfont); HSSFCellStyle titleStyle = (HSSFCellStyle)hssfworkbook.CreateCellStyle(); titleStyle.Alignment = HorizontalAlignment.CENTER; HSSFFont titlefont = (HSSFFont)hssfworkbook.CreateFont(); headStyle.SetFont(titlefont); HSSFCellStyle cellDateStyle = hssfworkbook.CreateCellStyle() as HSSFCellStyle; HSSFDataFormat cellDateFormat = hssfworkbook.CreateDataFormat() as HSSFDataFormat; if ("Y" == "Y") cellDateStyle.DataFormat = cellDateFormat.GetFormat("yyyy-MM-dd HH:mm"); else cellDateStyle.DataFormat = cellDateFormat.GetFormat("yyyy-MM-dd"); //数量小数格式化字符串 HSSFCellStyle cellNumStyle = hssfworkbook.CreateCellStyle() as HSSFCellStyle; HSSFDataFormat cellNumFormat = hssfworkbook.CreateDataFormat() as HSSFDataFormat; string formatValue = "0"; string formatStr = string.Empty; switch (formatValue) { #region 格式化 case "0": formatStr = "0"; break; case "1": formatStr = "0.0"; break; case "2": formatStr = "0.00"; break; case "3": formatStr = "0.000"; break; case "4": formatStr = "0.0000"; break; case "5": formatStr = "0.00000"; break; default: formatStr = "{0:0}"; break; #endregion } if (formatStr == "0" || formatStr == "0.00") { cellNumStyle.DataFormat = HSSFDataFormat.GetBuiltinFormat(formatStr); } else { cellNumStyle.DataFormat = cellNumFormat.GetFormat(formatStr); } //日期格式字符串 string dateFormat = "yyyy-MM-dd"; if ("Y" == "Y") { dateFormat += " " + "HH:mm"; } #endregion foreach (DataRow row in data.Tables[0].Rows) { #region 新建sheet 填写表头 列头 if (rowIndex == 65535 || rowIndex == 0) { if (rowIndex != 0) { sheet = (HSSFSheet)hssfworkbook.CreateSheet(); } #region 列头 titleRow = (HSSFRow)sheet.CreateRow(rowIndex); for (int i = 0; i < data.Tables[0].Columns.Count; i++) { titleRow.CreateCell(i).SetCellValue(data.Tables[0].Columns[i].ColumnName); titleRow.GetCell(i).CellStyle = titleStyle; } rowIndex++; #endregion } #endregion #region 明细 dataRow = (HSSFRow)sheet.CreateRow(rowIndex); for (int i = 0; i < data.Tables[0].Columns.Count; i++) { cell = (HSSFCell)dataRow.CreateCell(i); string value = row[data.Tables[0].Columns[i].ColumnName].ToString(); switch (data.Tables[0].Columns[i].DataType.FullName) { case "System.String": cell.SetCellValue(value); break; case "System.DateTime": DateTime datevalue; DateTime.TryParse(value, out datevalue); if (datevalue != new DateTime()) { cell.CellStyle = cellDateStyle; cell.SetCellValue(DateTime.Parse(datevalue.ToString(dateFormat))); } else { cell.SetCellValue(""); } break; case "System.Boolean": bool boolvalue = false; bool.TryParse(value, out boolvalue); cell.SetCellValue(boolvalue); break; case "System.Int16": Int16 int16 = 0; Int16.TryParse(value, out int16); cell.SetCellValue(int16); break; case "System.Int32": Int32 int32 = 0; Int32.TryParse(value, out int32); cell.SetCellValue(int32); break; case "System.Int64": Int64 int64 = 0; Int64.TryParse(value, out int64); cell.SetCellValue(int64); break; case "System.Byte": int intV = 0; int.TryParse(value, out intV); cell.SetCellValue(intV); break; case "System.Decimal": double decV = 0; double.TryParse(value, out decV); cell.SetCellValue(decV); cell.CellStyle = cellNumStyle; break; case "System.Single": float floatV = 0; float.TryParse(value, out floatV); cell.SetCellValue(floatV); cell.CellStyle = cellNumStyle; break; case "System.Double": double doubV = 0; double.TryParse(value, out doubV); cell.SetCellValue(doubV); cell.CellStyle = cellNumStyle; break; case "System.DBNull": cell.SetCellValue(""); break; default: cell.SetCellValue(value); break; } } #endregion rowIndex++; } hssfworkbook.Write(ms); ms.Flush(); ms.Position = 0; Response.Clear(); Response.ContentType = "application/vnd.ms-excel"; Response.Charset = "GB2312"; HttpContext.Response.AppendHeader("Content-Disposition", "attachment;filename=" + fileName); HttpContext.Response.BinaryWrite(ms.ToArray()); HttpContext.Response.End(); ms.Close(); sheet = null; hssfworkbook = null; ms = null; }
private void btnExportToExcel_Click(object sender, RoutedEventArgs e) { SaveFileDialog sfd = new SaveFileDialog(); sfd.Filter = "excel文件|*.xls"; if (sfd.ShowDialog() != true) { return; } string fileName = sfd.FileName; HSSFWorkbook workbook = new HSSFWorkbook(); ISheet sheet = workbook.CreateSheet("员工信息表"); IRow rowHeader = sheet.CreateRow(0); rowHeader.CreateCell(0, CellType.STRING).SetCellValue("姓名"); rowHeader.CreateCell(1, CellType.STRING).SetCellValue("工号"); rowHeader.CreateCell(2, CellType.STRING).SetCellValue("入职时间"); rowHeader.CreateCell(3, CellType.STRING).SetCellValue("学历"); rowHeader.CreateCell(4, CellType.STRING).SetCellValue("毕业院校"); rowHeader.CreateCell(5, CellType.STRING).SetCellValue("基本工资"); rowHeader.CreateCell(6, CellType.STRING).SetCellValue("部门"); rowHeader.CreateCell(7, CellType.STRING).SetCellValue("职位"); rowHeader.CreateCell(8, CellType.STRING).SetCellValue("合同签订日期"); rowHeader.CreateCell(9, CellType.STRING).SetCellValue("合同到期日期"); Employee[] employees = (Employee[])datagrid.ItemsSource; ICellStyle cellStyle = workbook.CreateCellStyle(); IDataFormat dataFormat = workbook.CreateDataFormat(); cellStyle.DataFormat = dataFormat.GetFormat("yyyy\"年\"m\"月\"d\"日\""); for (int i = 0; i < employees.Length; i++) { Employee employee = employees[i]; IRow row = sheet.CreateRow(i + 1); row.CreateCell(0, CellType.STRING).SetCellValue(employee.Name); row.CreateCell(1, CellType.STRING).SetCellValue(employee.Number); ICell dateCell = row.CreateCell(2, CellType.NUMERIC); dateCell.CellStyle = cellStyle; dateCell.SetCellValue(employee.InDate); row.CreateCell(3, CellType.STRING).SetCellValue(IdNameDAL.GetEducationNameById(employee.EducationId)); row.CreateCell(4, CellType.STRING).SetCellValue(employee.School); row.CreateCell(5, CellType.STRING).SetCellValue(employee.BaseSalary); row.CreateCell(6, CellType.STRING).SetCellValue(DepartmentDAL.GetById(employee.DepartmentId).Name); row.CreateCell(7, CellType.STRING).SetCellValue(employee.Position); ICell contractBegindateCell = row.CreateCell(8, CellType.NUMERIC); contractBegindateCell.CellStyle = cellStyle; contractBegindateCell.SetCellValue(employee.ContractStartDay); ICell contractEnddateCell = row.CreateCell(9, CellType.NUMERIC); contractEnddateCell.CellStyle = cellStyle; contractEnddateCell.SetCellValue(employee.ContractEndDay); } using (Stream stream = File.OpenWrite(fileName)) { workbook.Write(stream); } }
/// <summary> /// 导出数据行 /// </summary> /// <param name="dtSource"></param> /// <param name="drSource"></param> /// <param name="currentExcelRow"></param> /// <param name="excelSheet"></param> /// <param name="excelWorkBook"></param> protected static void InsertCell(DataTable dtSource, DataRow drSource, HSSFRow currentExcelRow, HSSFSheet excelSheet, HSSFWorkbook excelWorkBook) { for (int cellIndex = 0; cellIndex < ListColumnsName.Count; cellIndex++) { //列名称 string columnsName = ListColumnsName.GetKey(cellIndex).ToString(); HSSFCell newCell = null; System.Type rowType = drSource[columnsName].GetType(); string drValue = drSource[columnsName].ToString().Trim(); switch (rowType.ToString()) { case "System.String"://字符串类型 drValue = drValue.Replace("&", "&"); drValue = drValue.Replace(">", ">"); drValue = drValue.Replace("<", "<"); newCell = currentExcelRow.CreateCell(cellIndex); newCell.SetCellValue(drValue); break; case "System.DateTime"://日期类型 DateTime dateV; DateTime.TryParse(drValue, out dateV); newCell = currentExcelRow.CreateCell(cellIndex); newCell.SetCellValue(dateV); //格式化显示 HSSFCellStyle cellStyle = excelWorkBook.CreateCellStyle(); HSSFDataFormat format = excelWorkBook.CreateDataFormat(); cellStyle.DataFormat = format.GetFormat("yyyy-mm-dd hh:mm:ss"); newCell.CellStyle = cellStyle; break; case "System.Boolean"://布尔型 bool boolV = false; bool.TryParse(drValue, out boolV); newCell = currentExcelRow.CreateCell(cellIndex); newCell.SetCellValue(boolV); break; case "System.Int16"://整型 case "System.Int32": case "System.Int64": case "System.Byte": int intV = 0; int.TryParse(drValue, out intV); newCell = currentExcelRow.CreateCell(cellIndex); newCell.SetCellValue(intV.ToString()); break; case "System.Decimal"://浮点型 case "System.Double": double doubV = 0; double.TryParse(drValue, out doubV); newCell = currentExcelRow.CreateCell(cellIndex); newCell.SetCellValue(doubV); break; case "System.DBNull"://空值处理 newCell = currentExcelRow.CreateCell(cellIndex); newCell.SetCellValue(""); break; default: throw (new Exception(rowType.ToString() + ":类型数据无法处理!")); } } }
public void ExportToExcel(int page, int size, int store, int poType, string status, string po, int supplier, string srv, string stockCode, string stockName, string fd, string td, string enable) { // Get the data to report on var masters = _service.ListCondition(page, size, store, poType, status, po, supplier, srv, stockCode, stockName, fd, td, enable); var details = _service.ListConditionDetailExcel(page, size, store, poType, status, po, supplier, srv, stockCode, stockName, fd, td, enable); // Create a new workbook var workbook = new HSSFWorkbook(); #region Cell Styles #region HeaderLabel Cell Style var headerLabelCellStyle = workbook.CreateCellStyle(); headerLabelCellStyle.Alignment = HorizontalAlignment.CENTER; headerLabelCellStyle.BorderBottom = CellBorderType.THIN; var headerLabelFont = workbook.CreateFont(); headerLabelFont.Boldweight = (short)FontBoldWeight.BOLD; headerLabelCellStyle.SetFont(headerLabelFont); #endregion #region RightAligned Cell Style var rightAlignedCellStyle = workbook.CreateCellStyle(); rightAlignedCellStyle.Alignment = HorizontalAlignment.RIGHT; #endregion #region Currency Cell Style var currencyCellStyle = workbook.CreateCellStyle(); currencyCellStyle.Alignment = HorizontalAlignment.RIGHT; var formatId = HSSFDataFormat.GetBuiltinFormat("$#,##0.00"); if (formatId == -1) { var newDataFormat = workbook.CreateDataFormat(); currencyCellStyle.DataFormat = newDataFormat.GetFormat("$#,##0.00"); } else currencyCellStyle.DataFormat = formatId; #endregion #region Detail Subtotal Style var detailSubtotalCellStyle = workbook.CreateCellStyle(); detailSubtotalCellStyle.BorderTop = CellBorderType.THIN; detailSubtotalCellStyle.BorderBottom = CellBorderType.THIN; var detailSubtotalFont = workbook.CreateFont(); detailSubtotalFont.Boldweight = (short)FontBoldWeight.BOLD; detailSubtotalCellStyle.SetFont(detailSubtotalFont); #endregion #region Detail Currency Subtotal Style var detailCurrencySubtotalCellStyle = workbook.CreateCellStyle(); detailCurrencySubtotalCellStyle.BorderTop = CellBorderType.THIN; detailCurrencySubtotalCellStyle.BorderBottom = CellBorderType.THIN; var detailCurrencySubtotalFont = workbook.CreateFont(); detailCurrencySubtotalFont.Boldweight = (short)FontBoldWeight.BOLD; detailCurrencySubtotalCellStyle.SetFont(detailCurrencySubtotalFont); formatId = HSSFDataFormat.GetBuiltinFormat("$#,##0.00"); if (formatId == -1) { var newDataFormat = workbook.CreateDataFormat(); detailCurrencySubtotalCellStyle.DataFormat = newDataFormat.GetFormat("$#,##0.00"); } else detailCurrencySubtotalCellStyle.DataFormat = formatId; #endregion #endregion #region Master sheet var sheet = workbook.CreateSheet("Main"); // Add header labels var rowIndex = 0; // Undestand as row in excel. row + 3 = xuong 3 row. var row = sheet.CreateRow(rowIndex); var cell = row.CreateCell(0); cell.SetCellValue("No"); cell.CellStyle = headerLabelCellStyle; cell = row.CreateCell(1); cell.SetCellValue("PE Code"); cell.CellStyle = headerLabelCellStyle; cell = row.CreateCell(2); cell.SetCellValue("Supplier"); cell.CellStyle = headerLabelCellStyle; cell = row.CreateCell(3); cell.SetCellValue("PE Date"); cell.CellStyle = headerLabelCellStyle; cell = row.CreateCell(4); cell.SetCellValue("PE Total"); cell.CellStyle = headerLabelCellStyle; cell = row.CreateCell(5); cell.SetCellValue("Location"); cell.CellStyle = headerLabelCellStyle; cell = row.CreateCell(6); cell.SetCellValue("PE Type"); cell.CellStyle = headerLabelCellStyle; cell = row.CreateCell(7); cell.SetCellValue("Currency"); cell.CellStyle = headerLabelCellStyle; cell = row.CreateCell(8); cell.SetCellValue("PE Status"); cell.CellStyle = headerLabelCellStyle; rowIndex++; // Add data rows foreach (var item in masters) { row = sheet.CreateRow(rowIndex); row.CreateCell(0).SetCellValue(item.No.ToString()); row.CreateCell(1).SetCellValue(item.PE_Code); row.CreateCell(2).SetCellValue(item.Supplier); row.CreateCell(3).SetCellValue(item.PE_Date.ToString("dd/MM/yyyy")); row.CreateCell(4).SetCellValue(item.PE_Total.ToString()); row.CreateCell(5).SetCellValue(item.PE_Location); row.CreateCell(6).SetCellValue(item.PE_Type); row.CreateCell(7).SetCellValue(item.Currency); row.CreateCell(8).SetCellValue(item.PE_Status); rowIndex++; } // Auto-size each column for (var i = 0; i < sheet.GetRow(0).LastCellNum; i++) { sheet.AutoSizeColumn(i); // Bump up with auto-sized column width to account for bold headers sheet.SetColumnWidth(i, sheet.GetColumnWidth(i) + 1024); } // Add row indicating date/time report was generated... sheet.CreateRow(rowIndex + 1).CreateCell(0).SetCellValue("Report generated on " + DateTime.Now.ToString("dd/MM/yyyy")); #endregion #region Detail sheet sheet = workbook.CreateSheet("Details"); #region Add header labels rowIndex = 0; row = sheet.CreateRow(rowIndex); cell = row.CreateCell(0); cell.SetCellValue("PE Code"); cell.CellStyle = headerLabelCellStyle; cell = row.CreateCell(1); cell.SetCellValue("Stock Code"); cell.CellStyle = headerLabelCellStyle; cell = row.CreateCell(2); cell.SetCellValue("Stock Name"); cell.CellStyle = headerLabelCellStyle; cell = row.CreateCell(3); cell.SetCellValue("Qty Order"); cell.CellStyle = headerLabelCellStyle; cell = row.CreateCell(4); cell.SetCellValue("Qty Receive"); cell.CellStyle = headerLabelCellStyle; cell = row.CreateCell(5); cell.SetCellValue("Qty Pending"); cell.CellStyle = headerLabelCellStyle; cell = row.CreateCell(6); cell.SetCellValue("Invoice No"); cell.CellStyle = headerLabelCellStyle; cell = row.CreateCell(7); cell.SetCellValue("Invoice Date"); cell.CellStyle = headerLabelCellStyle; cell = row.CreateCell(8); cell.SetCellValue("MRF"); cell.CellStyle = headerLabelCellStyle; cell = row.CreateCell(9); cell.SetCellValue("SRV"); cell.CellStyle = headerLabelCellStyle; cell = row.CreateCell(10); cell.SetCellValue("Stock Type"); cell.CellStyle = headerLabelCellStyle; cell = row.CreateCell(11); cell.SetCellValue("Stock Unit"); cell.CellStyle = headerLabelCellStyle; cell = row.CreateCell(12); cell.SetCellValue("Stock Category"); cell.CellStyle = headerLabelCellStyle; cell = row.CreateCell(13); cell.SetCellValue("Part No"); cell.CellStyle = headerLabelCellStyle; cell = row.CreateCell(14); cell.SetCellValue("Ral No"); cell.CellStyle = headerLabelCellStyle; cell = row.CreateCell(15); cell.SetCellValue("Color"); cell.CellStyle = headerLabelCellStyle; cell = row.CreateCell(16); cell.SetCellValue("Description"); cell.CellStyle = headerLabelCellStyle; rowIndex++; #endregion // Add data rows var lastProductName = string.Empty; // For sum in excel // var startRowIndexForProductDetails = 1; foreach (var detail in details) { // Show a summary row for each new product //var productNameToShow = string.Empty; //if (String.CompareOrdinal(detail.PE_Code, lastProductName) != 0) //{ // if (!string.IsNullOrEmpty(lastProductName)) // { // // Add the subtotal row // // AddSubtotalRow(sheet, startRowIndexForProductDetails, rowIndex, detailSubtotalCellStyle, detailCurrencySubtotalCellStyle); // // rowIndex += 3; // } // productNameToShow = detail.PE_Code; // lastProductName = detail.PE_Code; // // startRowIndexForProductDetails = rowIndex; //} row = sheet.CreateRow(rowIndex); //row.CreateCell(0).SetCellValue(productNameToShow); row.CreateCell(0).SetCellValue(detail.PE_Code); row.CreateCell(1).SetCellValue(detail.Stock_Code); row.CreateCell(2).SetCellValue(detail.Stock_Name); row.CreateCell(3).SetCellValue(detail.Qty_Total.ToString(CultureInfo.InvariantCulture)); row.CreateCell(4).SetCellValue(detail.Qty_Received.ToString(CultureInfo.InvariantCulture)); row.CreateCell(5).SetCellValue(detail.Qty_Pending.ToString(CultureInfo.InvariantCulture)); row.CreateCell(6).SetCellValue(detail.InvoiceNo); row.CreateCell(7).SetCellValue(detail.InvoiceDate.ToString("dd/MM/yyyy")); row.CreateCell(8).SetCellValue(detail.MRF); row.CreateCell(9).SetCellValue(detail.SRV); row.CreateCell(10).SetCellValue(detail.Type); row.CreateCell(11).SetCellValue(detail.Unit); row.CreateCell(12).SetCellValue(detail.Category); row.CreateCell(13).SetCellValue(detail.PartNo); row.CreateCell(14).SetCellValue(detail.RalNo); row.CreateCell(15).SetCellValue(detail.Color); row.CreateCell(16).SetCellValue(detail.Description); rowIndex++; } // Add the subtotal row for the last product // AddSubtotalRow(sheet, startRowIndexForProductDetails, rowIndex, detailSubtotalCellStyle, detailCurrencySubtotalCellStyle); // rowIndex += 2; // Auto-size each column for (var i = 0; i < sheet.GetRow(0).LastCellNum; i++) { sheet.AutoSizeColumn(i); // Bump up with auto-sized column width to account for bold headers sheet.SetColumnWidth(i, sheet.GetColumnWidth(i) + 1024); } // Add row indicating date/time report was generated... // sheet.CreateRow(rowIndex + 1).CreateCell(0).SetCellValue("Report generated on " + DateTime.Now.ToString("dd/MM/yyyy")); #endregion // Save the Excel spreadsheet to a MemoryStream and return it to the client using (var exportData = new MemoryStream()) { workbook.Write(exportData); var saveAsFileName = string.Format("StockIn-{0}.xls", DateTime.Now.ToString("ddMMyyyyHHmmss")).Replace("/", "-"); Response.ContentType = "application/vnd.ms-excel"; Response.AddHeader("Content-Disposition", string.Format("attachment;filename={0}", saveAsFileName)); Response.Clear(); Response.BinaryWrite(exportData.GetBuffer()); Response.End(); } }
/// <summary> /// DataTable导出到Excel的MemoryStream /// </summary> /// <param name="dtSource">源DataTable</param> /// <param name="listColumn"> </param> /// <param name="strHeaderText">表头文本</param> /// <param name="defaultColumnWidth">默认列宽度 </param> public static MemoryStream Export(DataTable dtSource, List<Column> listColumn, string strHeaderText, int defaultColumnWidth) { #region 门卫代码 if (listColumn == null) listColumn = new List<Column>(); #endregion var workbook = new HSSFWorkbook(); ISheet sheet = workbook.CreateSheet(); #region 右击文件 属性信息 { DocumentSummaryInformation dsi = PropertySetFactory.CreateDocumentSummaryInformation(); dsi.Company = "云学时代"; workbook.DocumentSummaryInformation = dsi; SummaryInformation si = PropertySetFactory.CreateSummaryInformation(); si.Author = "云学时代"; //填加xls文件作者信息 si.ApplicationName = strHeaderText; //填加xls文件创建程序信息 si.LastAuthor = "云学时代"; //填加xls文件最后保存者信息 si.Comments = "云学时代"; //填加xls文件作者信息 si.Title = strHeaderText; //填加xls文件标题信息 si.Subject = "云学时代" + strHeaderText; //填加文件主题信息 si.CreateDateTime = DateTime.Now; workbook.SummaryInformation = si; } #endregion #region 标题样式 ICellStyle titleCellStyle = workbook.CreateCellStyle(); titleCellStyle.Alignment = HorizontalAlignment.Center; titleCellStyle.FillForegroundColor = HSSFColor.BlueGrey.Index; titleCellStyle.FillPattern = NPOI.SS.UserModel.FillPattern.SparseDots; //titleCellStyle.FillBackgroundColor = HSSFColor.BLUE_GREY.index; titleCellStyle.FillBackgroundColor = HSSFColor.BlueGrey.Index; IFont titleFont = workbook.CreateFont(); titleFont.FontHeightInPoints = 18; titleFont.Boldweight = 700; titleFont.FontName = "黑体"; titleCellStyle.SetFont(titleFont); #endregion #region 表头样式 ICellStyle headCellStyle = workbook.CreateCellStyle(); headCellStyle.Alignment = HorizontalAlignment.Center; //headCellStyle.FillForegroundColor = HSSFColor.LIGHT_YELLOW.index; headCellStyle.FillForegroundColor = HSSFColor.LightYellow.Index; //headCellStyle.FillPattern = FillPatternType.SPARSE_DOTS; headCellStyle.FillPattern = NPOI.SS.UserModel.FillPattern.SparseDots; //headCellStyle.FillBackgroundColor = HSSFColor.LIGHT_YELLOW.index; headCellStyle.FillBackgroundColor = HSSFColor.LightYellow.Index; IFont headfont = workbook.CreateFont(); headfont.FontHeightInPoints = 10; headfont.Boldweight = 700; headfont.FontName = "宋体"; headCellStyle.SetFont(headfont); #endregion #region 单元格样式 ICellStyle cellStyle = workbook.CreateCellStyle(); cellStyle.Alignment = HorizontalAlignment.Center; IFont cellfont = workbook.CreateFont(); cellfont.FontHeightInPoints = 10; cellfont.Boldweight = 400; cellfont.FontName = "宋体"; cellStyle.SetFont(cellfont); #endregion #region 日期样式 ICellStyle dateStyle = workbook.CreateCellStyle(); //dateStyle.Alignment=HorizontalAlignment.LEFT; dateStyle.Alignment = HorizontalAlignment.Left; IFont dateFont = workbook.CreateFont(); dateFont.FontHeightInPoints = 10; dateFont.Boldweight = 400; dateFont.FontName = "宋体"; dateStyle.SetFont(dateFont); IDataFormat format = workbook.CreateDataFormat(); dateStyle.DataFormat = format.GetFormat("yyyy-mm-dd"); #endregion #region 取得列宽 //var arrColWidth = new int[dtSource.Columns.Count]; //foreach (DataColumn item in dtSource.Columns) //{ // arrColWidth[item.Ordinal] = Encoding.GetEncoding(936).GetBytes(item.ColumnName).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; // } // } //} for (int i = 0; i < dtSource.Columns.Count; i++) { var firstOrDefault = listColumn.FirstOrDefault(a => a.Key == dtSource.Columns[i].ColumnName); if (firstOrDefault != null) { sheet.SetColumnWidth(i, firstOrDefault.Width); } else { sheet.SetColumnWidth(i, defaultColumnWidth); } } #endregion int rowIndex = 0; foreach (DataRow row in dtSource.Rows) { #region 新建表,填充表头,填充列头,样式 if (rowIndex == 65535 || rowIndex == 0) { if (rowIndex != 0) { sheet = workbook.CreateSheet(); } #region 表头及样式 IRow titleRow = sheet.CreateRow(0); titleRow.HeightInPoints = 22; titleRow.CreateCell(0).SetCellValue(strHeaderText); titleRow.GetCell(0).CellStyle = titleCellStyle; sheet.AddMergedRegion(new CellRangeAddress(0, 0, 0, dtSource.Columns.Count - 1)); #endregion #region 列头及样式 IRow headerRow = sheet.CreateRow(1); foreach (DataColumn column in dtSource.Columns) { headerRow.CreateCell(column.Ordinal).SetCellValue(column.ColumnName); headerRow.GetCell(column.Ordinal).CellStyle = headCellStyle; } #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(); newCell.CellStyle = cellStyle; //格式化显示 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; bool.TryParse(drValue, out boolV); newCell.SetCellValue(boolV); break; case "System.Int16": //整型 case "System.Int32": case "System.Int64": case "System.Byte": int intV; int.TryParse(drValue, out intV); newCell.SetCellValue(intV); break; case "System.Decimal": //浮点型 case "System.Double": double doubV; double.TryParse(drValue, out doubV); newCell.SetCellValue(doubV); break; case "System.DBNull": //空值处理 newCell.SetCellValue(""); break; default: newCell.SetCellValue(""); break; } } #endregion rowIndex++; } //using (var ms = new MemoryStream()) //{ // workbook.Write(ms); // ms.Flush(); // ms.Position = 0; // return ms; //} var ms = new MemoryStream(); workbook.Write(ms); ms.Flush(); ms.Position = 0; return ms; }
private bool AutoCheckSL() { HSSFWorkbook cb = new HSSFWorkbook(); ISheet cs = cb.CreateSheet(); ICellStyle style = cb.CreateCellStyle(); IDataFormat format = cb.CreateDataFormat(); style.DataFormat = format.GetFormat("N2"); style.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center; ICellStyle style_all = cb.CreateCellStyle(); style_all.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center; IRow hr = cs.CreateRow(0); hr.CreateCell(0).SetCellValue("条码"); hr.CreateCell(1).SetCellValue("库存"); hr.CreateCell(2).SetCellValue("盘点"); hr.CreateCell(3).SetCellValue("售价"); hr.CreateCell(4).SetCellValue("品名"); hr.CreateCell(5).SetCellValue("进价"); for (int m = 0; m < 6; m++) { hr.GetCell(m).CellStyle = style_all; } cs.SetColumnWidth(0, 16 * 256); cs.SetColumnWidth(1, 8 * 256); cs.SetColumnWidth(2, 8 * 256); cs.SetColumnWidth(3, 10 * 256); cs.SetColumnWidth(4, 24 * 256); cs.SetColumnWidth(5, 10 * 256); int total = 1; FileStream fs = new FileStream(this.textBox_pd.Text, FileMode.Open, FileAccess.Read); HSSFWorkbook book = new HSSFWorkbook(fs); ISheet sheet = book.GetSheetAt(0); string tm; for (int i = 0; i < sheet.LastRowNum + 1; i++) { IRow row = sheet.GetRow(i); ICell cell = row.GetCell(0); switch (cell.CellType) { case CellType.Numeric: tm = cell.NumericCellValue.ToString(); break; case CellType.String: tm = cell.StringCellValue; break; default: return false; } string s = "select kc.tm,kc.sl,pd.sl,goods.sj,goods.pm,goods.jj from kc join pd on(kc.tm=pd.tm) join goods on(kc.tm=goods.tm) where pd.tm='" + tm + "'"; Form_main.Command.CommandText = s; MySqlDataReader dr = Form_main.Command.ExecuteReader(); dr.Read(); if (dr.GetInt32(1) == dr.GetInt32(2)) { this.toolStripStatusLabel1.Text = "正在核对商品:" + tm + " OK"; Application.DoEvents(); } else { IRow r = cs.CreateRow(total); r.CreateCell(0).SetCellType(CellType.String); r.GetCell(0).CellStyle = style_all; r.GetCell(0).SetCellValue(dr.GetString(0));//tm r.CreateCell(1).SetCellType(CellType.Numeric); r.GetCell(1).CellStyle = style_all; r.GetCell(1).SetCellValue(dr.GetInt32(1));//kc-sl r.CreateCell(2).SetCellType(CellType.Numeric); r.GetCell(2).CellStyle = style_all; r.GetCell(2).SetCellValue(dr.GetInt32(2));//pd-sl r.CreateCell(3).SetCellType(CellType.Numeric);//sj r.GetCell(3).CellStyle = style; r.GetCell(3).SetCellValue(dr.GetFloat(3)); r.CreateCell(4).SetCellType(CellType.String);//pm r.GetCell(4).CellStyle = style_all; r.GetCell(4).SetCellValue(dr.GetString(4)); r.CreateCell(5).SetCellType(CellType.Numeric); r.GetCell(5).CellStyle = style; r.GetCell(5).SetCellValue(dr.GetFloat(5)); total++; this.toolStripStatusLabel1.Text = "正在核对商品:" + tm + " Error"; Application.DoEvents(); } dr.Close(); } if (total > 1) { MessageBox.Show("核对完成,共核对" + (sheet.LastRowNum + 1) + "件商品,其中不匹配商品" + cs.LastRowNum + "件需要保存", "提示", MessageBoxButtons.OK, MessageBoxIcon.Information); SaveFileDialog sfd = new SaveFileDialog(); sfd.Title = "请选择保存的文件名及路径(核对中产生的不同数据)"; sfd.AddExtension = true; sfd.Filter = "电子表格文件(*.xls)|*.xls"; if (sfd.ShowDialog(this) == DialogResult.OK) { fs = new FileStream(sfd.FileName, FileMode.Create, FileAccess.Write); cb.Write(fs); fs.Close(); MessageBox.Show("数量不匹配条码已保存至" + sfd.FileName); } else { MessageBox.Show("找到不匹配数据未能保存", "提示", MessageBoxButtons.OK, MessageBoxIcon.Information); } } else { MessageBox.Show("核对完成,共" + (sheet.LastRowNum + 1) + "件商品,全部与电脑库存相符!", "提示", MessageBoxButtons.OK, MessageBoxIcon.Information); } this.textBox_kc.Clear(); this.textBox_pd.Clear(); this.toolStripStatusLabel1.Text = "就绪"; return true; }
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); } }
public void ExportToExcel(int page, int size, int store, string department, string user, string enable) { // Get the data to report on var masters = _service.ListCondition(page, size, store, department, user, enable); // Create a new workbook var workbook = new HSSFWorkbook(); #region Cell Styles #region HeaderLabel Cell Style var headerLabelCellStyle = workbook.CreateCellStyle(); headerLabelCellStyle.Alignment = HorizontalAlignment.CENTER; headerLabelCellStyle.BorderBottom = CellBorderType.THIN; var headerLabelFont = workbook.CreateFont(); headerLabelFont.Boldweight = (short)FontBoldWeight.BOLD; headerLabelCellStyle.SetFont(headerLabelFont); #endregion #region RightAligned Cell Style var rightAlignedCellStyle = workbook.CreateCellStyle(); rightAlignedCellStyle.Alignment = HorizontalAlignment.RIGHT; #endregion #region Currency Cell Style var currencyCellStyle = workbook.CreateCellStyle(); currencyCellStyle.Alignment = HorizontalAlignment.RIGHT; var formatId = HSSFDataFormat.GetBuiltinFormat("$#,##0.00"); if (formatId == -1) { var newDataFormat = workbook.CreateDataFormat(); currencyCellStyle.DataFormat = newDataFormat.GetFormat("$#,##0.00"); } else currencyCellStyle.DataFormat = formatId; #endregion #region Detail Subtotal Style var detailSubtotalCellStyle = workbook.CreateCellStyle(); detailSubtotalCellStyle.BorderTop = CellBorderType.THIN; detailSubtotalCellStyle.BorderBottom = CellBorderType.THIN; var detailSubtotalFont = workbook.CreateFont(); detailSubtotalFont.Boldweight = (short)FontBoldWeight.BOLD; detailSubtotalCellStyle.SetFont(detailSubtotalFont); #endregion #region Detail Currency Subtotal Style var detailCurrencySubtotalCellStyle = workbook.CreateCellStyle(); detailCurrencySubtotalCellStyle.BorderTop = CellBorderType.THIN; detailCurrencySubtotalCellStyle.BorderBottom = CellBorderType.THIN; var detailCurrencySubtotalFont = workbook.CreateFont(); detailCurrencySubtotalFont.Boldweight = (short)FontBoldWeight.BOLD; detailCurrencySubtotalCellStyle.SetFont(detailCurrencySubtotalFont); formatId = HSSFDataFormat.GetBuiltinFormat("$#,##0.00"); if (formatId == -1) { var newDataFormat = workbook.CreateDataFormat(); detailCurrencySubtotalCellStyle.DataFormat = newDataFormat.GetFormat("$#,##0.00"); } else detailCurrencySubtotalCellStyle.DataFormat = formatId; #endregion #endregion #region Master sheet var sheet = workbook.CreateSheet("User"); // Add header labels var rowIndex = 0; // Undestand as row in excel. row + 3 = xuong 3 row. var row = sheet.CreateRow(rowIndex); var cell = row.CreateCell(0); cell.SetCellValue("User Id"); cell.CellStyle = headerLabelCellStyle; cell = row.CreateCell(1); cell.SetCellValue("User Name"); cell.CellStyle = headerLabelCellStyle; cell = row.CreateCell(2); cell.SetCellValue("First Name"); cell.CellStyle = headerLabelCellStyle; cell = row.CreateCell(3); cell.SetCellValue("Last Name"); cell.CellStyle = headerLabelCellStyle; cell = row.CreateCell(4); cell.SetCellValue("Email"); cell.CellStyle = headerLabelCellStyle; cell = row.CreateCell(5); cell.SetCellValue("Tel."); cell.CellStyle = headerLabelCellStyle; cell = row.CreateCell(6); cell.SetCellValue("Mobile"); cell.CellStyle = headerLabelCellStyle; cell = row.CreateCell(7); cell.SetCellValue("Department"); cell.CellStyle = headerLabelCellStyle; cell = row.CreateCell(8); cell.SetCellValue("Right User"); cell.CellStyle = headerLabelCellStyle; cell = row.CreateCell(9); cell.SetCellValue("Right Store"); cell.CellStyle = headerLabelCellStyle; cell = row.CreateCell(10); cell.SetCellValue("Right Project"); cell.CellStyle = headerLabelCellStyle; cell = row.CreateCell(11); cell.SetCellValue("Right Stock"); cell.CellStyle = headerLabelCellStyle; cell = row.CreateCell(12); cell.SetCellValue("Right Stock Out"); cell.CellStyle = headerLabelCellStyle; cell = row.CreateCell(13); cell.SetCellValue("Right Stock In"); cell.CellStyle = headerLabelCellStyle; cell = row.CreateCell(14); cell.SetCellValue("Right Stock Return"); cell.CellStyle = headerLabelCellStyle; cell = row.CreateCell(15); cell.SetCellValue("Right Stock-Reactive"); cell.CellStyle = headerLabelCellStyle; cell = row.CreateCell(16); cell.SetCellValue("Right Service"); cell.CellStyle = headerLabelCellStyle; cell = row.CreateCell(17); cell.SetCellValue("Right Requisition"); cell.CellStyle = headerLabelCellStyle; cell = row.CreateCell(18); cell.SetCellValue("Right PE"); cell.CellStyle = headerLabelCellStyle; cell = row.CreateCell(19); cell.SetCellValue("Right Accounting"); cell.CellStyle = headerLabelCellStyle; cell = row.CreateCell(20); cell.SetCellValue("Right Supplier"); cell.CellStyle = headerLabelCellStyle; cell = row.CreateCell(21); cell.SetCellValue("Right Price"); cell.CellStyle = headerLabelCellStyle; cell = row.CreateCell(22); cell.SetCellValue("Created Date"); cell.CellStyle = headerLabelCellStyle; cell = row.CreateCell(23); cell.SetCellValue("Created By"); cell.CellStyle = headerLabelCellStyle; cell = row.CreateCell(24); cell.SetCellValue("Modified Date"); cell.CellStyle = headerLabelCellStyle; cell = row.CreateCell(25); cell.SetCellValue("Modified By"); cell.CellStyle = headerLabelCellStyle; rowIndex++; // Add data rows foreach (var master in masters) { row = sheet.CreateRow(rowIndex); row.CreateCell(0).SetCellValue(master.Id.ToString()); row.CreateCell(1).SetCellValue(master.UserName); row.CreateCell(2).SetCellValue(master.FirstName); row.CreateCell(3).SetCellValue(master.LastName); row.CreateCell(4).SetCellValue(master.Email); row.CreateCell(5).SetCellValue(master.Telephone); row.CreateCell(6).SetCellValue(master.Mobile); row.CreateCell(7).SetCellValue(master.Department); row.CreateCell(8).SetCellValue(Constants.Action(master.UserR)); row.CreateCell(9).SetCellValue(Constants.Action(master.StoreR)); row.CreateCell(10).SetCellValue(Constants.Action(master.ProjectR)); row.CreateCell(11).SetCellValue(Constants.Action(master.StockR)); row.CreateCell(12).SetCellValue(Constants.Action(master.StockOutR)); row.CreateCell(13).SetCellValue(Constants.Action(master.StockInR)); row.CreateCell(14).SetCellValue(Constants.Action(master.StockReturnR)); row.CreateCell(15).SetCellValue(Constants.Action(master.ReActiveStockR)); row.CreateCell(16).SetCellValue(Constants.Action(master.StockServiceR)); row.CreateCell(17).SetCellValue(Constants.Action(master.RequisitionR)); row.CreateCell(18).SetCellValue(Constants.Action(master.PER)); row.CreateCell(19).SetCellValue(Constants.Action(master.AccountingR)); row.CreateCell(20).SetCellValue(Constants.Action(master.SupplierR)); row.CreateCell(21).SetCellValue(Constants.Action(master.PriceR)); row.CreateCell(22).SetCellValue(master.Created != null ? master.Created.Value.ToString("dd/MM/yyyy") : master.Created.ToString()); row.CreateCell(23).SetCellValue(master.CreatedBy.ToString()); row.CreateCell(24).SetCellValue(master.Modified != null ? master.Modified.Value.ToString("dd/MM/yyyy") : master.Modified.ToString()); row.CreateCell(25).SetCellValue(master.ModifiedBy.ToString()); rowIndex++; } // Auto-size each column for (var i = 0; i < sheet.GetRow(0).LastCellNum; i++) { sheet.AutoSizeColumn(i); // Bump up with auto-sized column width to account for bold headers sheet.SetColumnWidth(i, sheet.GetColumnWidth(i) + 1024); } // Add row indicating date/time report was generated... sheet.CreateRow(rowIndex + 1).CreateCell(0).SetCellValue("Report generated on " + DateTime.Now.ToString("dd/MM/yyyy")); #endregion // Save the Excel spreadsheet to a MemoryStream and return it to the client using (var exportData = new MemoryStream()) { workbook.Write(exportData); var saveAsFileName = string.Format("User-{0}.xls", DateTime.Now.ToString("ddMMyyyyHHmmss")).Replace("/", "-"); Response.ContentType = "application/vnd.ms-excel"; Response.AddHeader("Content-Disposition", string.Format("attachment;filename={0}", saveAsFileName)); Response.Clear(); Response.BinaryWrite(exportData.GetBuffer()); Response.End(); } }
public void ExportToExcel(int page, int size, int supplierType, int supplierId, string supplierName, string stockCode, string stockName, int country, int market, string enable) { // Get the data to report on var masters = _service.ListCondition(page, size, supplierType, supplierId, supplierName, stockCode, stockName, country, market, enable); var details = _service.ListConditionDetailExcel(page, size, supplierType, supplierId, supplierName, stockCode, stockName, country, market, enable); // Create a new workbook var workbook = new HSSFWorkbook(); #region Cell Styles #region HeaderLabel Cell Style var headerLabelCellStyle = workbook.CreateCellStyle(); headerLabelCellStyle.Alignment = HorizontalAlignment.CENTER; headerLabelCellStyle.BorderBottom = CellBorderType.THIN; var headerLabelFont = workbook.CreateFont(); headerLabelFont.Boldweight = (short)FontBoldWeight.BOLD; headerLabelCellStyle.SetFont(headerLabelFont); #endregion #region RightAligned Cell Style var rightAlignedCellStyle = workbook.CreateCellStyle(); rightAlignedCellStyle.Alignment = HorizontalAlignment.RIGHT; #endregion #region Currency Cell Style var currencyCellStyle = workbook.CreateCellStyle(); currencyCellStyle.Alignment = HorizontalAlignment.RIGHT; var formatId = HSSFDataFormat.GetBuiltinFormat("$#,##0.00"); if (formatId == -1) { var newDataFormat = workbook.CreateDataFormat(); currencyCellStyle.DataFormat = newDataFormat.GetFormat("$#,##0.00"); } else currencyCellStyle.DataFormat = formatId; #endregion #region Detail Subtotal Style var detailSubtotalCellStyle = workbook.CreateCellStyle(); detailSubtotalCellStyle.BorderTop = CellBorderType.THIN; detailSubtotalCellStyle.BorderBottom = CellBorderType.THIN; var detailSubtotalFont = workbook.CreateFont(); detailSubtotalFont.Boldweight = (short)FontBoldWeight.BOLD; detailSubtotalCellStyle.SetFont(detailSubtotalFont); #endregion #region Detail Currency Subtotal Style var detailCurrencySubtotalCellStyle = workbook.CreateCellStyle(); detailCurrencySubtotalCellStyle.BorderTop = CellBorderType.THIN; detailCurrencySubtotalCellStyle.BorderBottom = CellBorderType.THIN; var detailCurrencySubtotalFont = workbook.CreateFont(); detailCurrencySubtotalFont.Boldweight = (short)FontBoldWeight.BOLD; detailCurrencySubtotalCellStyle.SetFont(detailCurrencySubtotalFont); formatId = HSSFDataFormat.GetBuiltinFormat("$#,##0.00"); if (formatId == -1) { var newDataFormat = workbook.CreateDataFormat(); detailCurrencySubtotalCellStyle.DataFormat = newDataFormat.GetFormat("$#,##0.00"); } else detailCurrencySubtotalCellStyle.DataFormat = formatId; #endregion #endregion #region Master sheet var sheet = workbook.CreateSheet("Supplier"); // Add header labels var rowIndex = 0; // Undestand as row in excel. row + 3 = xuong 3 row. var row = sheet.CreateRow(rowIndex); var cell = row.CreateCell(0); cell.SetCellValue("No"); cell.CellStyle = headerLabelCellStyle; cell = row.CreateCell(1); cell.SetCellValue("Supplier Id"); cell.CellStyle = headerLabelCellStyle; cell = row.CreateCell(2); cell.SetCellValue("Supplier Name"); cell.CellStyle = headerLabelCellStyle; cell = row.CreateCell(3); cell.SetCellValue("Type"); cell.CellStyle = headerLabelCellStyle; cell = row.CreateCell(4); cell.SetCellValue("Address"); cell.CellStyle = headerLabelCellStyle; cell = row.CreateCell(5); cell.SetCellValue("City"); cell.CellStyle = headerLabelCellStyle; cell = row.CreateCell(6); cell.SetCellValue("Country"); cell.CellStyle = headerLabelCellStyle; cell = row.CreateCell(7); cell.SetCellValue("Phone 1"); cell.CellStyle = headerLabelCellStyle; cell = row.CreateCell(8); cell.SetCellValue("Phone 2"); cell.CellStyle = headerLabelCellStyle; cell = row.CreateCell(9); cell.SetCellValue("Mobile"); cell.CellStyle = headerLabelCellStyle; cell = row.CreateCell(10); cell.SetCellValue("Fax"); cell.CellStyle = headerLabelCellStyle; cell = row.CreateCell(11); cell.SetCellValue("Email"); cell.CellStyle = headerLabelCellStyle; cell = row.CreateCell(12); cell.SetCellValue("Contact"); cell.CellStyle = headerLabelCellStyle; cell = row.CreateCell(13); cell.SetCellValue("Created Date"); cell.CellStyle = headerLabelCellStyle; cell = row.CreateCell(14); cell.SetCellValue("Created By"); cell.CellStyle = headerLabelCellStyle; cell = row.CreateCell(15); cell.SetCellValue("Modified Date"); cell.CellStyle = headerLabelCellStyle; cell = row.CreateCell(16); cell.SetCellValue("Modified By"); cell.CellStyle = headerLabelCellStyle; rowIndex++; // Add data rows var no = 1; foreach (var master in masters) { row = sheet.CreateRow(rowIndex); row.CreateCell(0).SetCellValue(no); row.CreateCell(1).SetCellValue(master.Id); row.CreateCell(2).SetCellValue(master.Name); row.CreateCell(3).SetCellValue(master.Type); row.CreateCell(4).SetCellValue(master.Address); row.CreateCell(5).SetCellValue(master.City); row.CreateCell(6).SetCellValue(master.Country); row.CreateCell(7).SetCellValue(master.Phone); row.CreateCell(8).SetCellValue(master.Phone_2); row.CreateCell(9).SetCellValue(master.Mobile); row.CreateCell(10).SetCellValue(master.Fax); row.CreateCell(11).SetCellValue(master.Email); row.CreateCell(12).SetCellValue(master.Contact); row.CreateCell(13).SetCellValue(master.Created_Date != null ? master.Created_Date.Value.ToString("dd/MM/yyyy") : master.Created_Date.ToString()); row.CreateCell(14).SetCellValue(master.Created_By); row.CreateCell(15).SetCellValue(master.Modified_Date != null ? master.Modified_Date.Value.ToString("dd/MM/yyyy") : master.Modified_Date.ToString()); row.CreateCell(16).SetCellValue(master.Modified_By); rowIndex++; no++; } // Auto-size each column for (var i = 0; i < sheet.GetRow(0).LastCellNum; i++) { sheet.AutoSizeColumn(i); // Bump up with auto-sized column width to account for bold headers sheet.SetColumnWidth(i, sheet.GetColumnWidth(i) + 1024); } // Add row indicating date/time report was generated... sheet.CreateRow(rowIndex + 1).CreateCell(0).SetCellValue("Report generated on " + DateTime.Now.ToString("dd/MM/yyyy")); #endregion #region Detail sheet sheet = workbook.CreateSheet("Product"); #region Add header labels rowIndex = 0; row = sheet.CreateRow(rowIndex); cell = row.CreateCell(0); cell.SetCellValue("Supplier"); cell.CellStyle = headerLabelCellStyle; cell = row.CreateCell(1); cell.SetCellValue("Stock Code"); cell.CellStyle = headerLabelCellStyle; cell = row.CreateCell(2); cell.SetCellValue("Stock Name"); cell.CellStyle = headerLabelCellStyle; cell = row.CreateCell(3); cell.SetCellValue("Stock Type"); cell.CellStyle = headerLabelCellStyle; cell = row.CreateCell(4); cell.SetCellValue("Unit"); cell.CellStyle = headerLabelCellStyle; cell = row.CreateCell(5); cell.SetCellValue("Category"); cell.CellStyle = headerLabelCellStyle; cell = row.CreateCell(6); cell.SetCellValue("Part No"); cell.CellStyle = headerLabelCellStyle; cell = row.CreateCell(7); cell.SetCellValue("Ral No"); cell.CellStyle = headerLabelCellStyle; cell = row.CreateCell(8); cell.SetCellValue("Color"); cell.CellStyle = headerLabelCellStyle; rowIndex++; #endregion // Add data rows var lastProductName = string.Empty; // For sum in excel // var startRowIndexForProductDetails = 1; foreach (var detail in details) { // Show a summary row for each new product var productNameToShow = string.Empty; if (string.Compare(detail.Supplier_Name, lastProductName) != 0) { if (!string.IsNullOrEmpty(lastProductName)) { // Add the subtotal row // AddSubtotalRow(sheet, startRowIndexForProductDetails, rowIndex, detailSubtotalCellStyle, detailCurrencySubtotalCellStyle); // rowIndex += 3; } productNameToShow = detail.Supplier_Name; lastProductName = detail.Supplier_Name; // startRowIndexForProductDetails = rowIndex; } row = sheet.CreateRow(rowIndex); row.CreateCell(0).SetCellValue(productNameToShow); row.CreateCell(1).SetCellValue(detail.Stock_Code); row.CreateCell(2).SetCellValue(detail.Stock_Name); row.CreateCell(3).SetCellValue(detail.Type); row.CreateCell(4).SetCellValue(detail.Unit); row.CreateCell(5).SetCellValue(detail.Category); row.CreateCell(6).SetCellValue(detail.Part_No); row.CreateCell(7).SetCellValue(detail.Ral_No); row.CreateCell(8).SetCellValue(detail.Color); rowIndex++; } // Add the subtotal row for the last product // AddSubtotalRow(sheet, startRowIndexForProductDetails, rowIndex, detailSubtotalCellStyle, detailCurrencySubtotalCellStyle); // rowIndex += 2; // Auto-size each column for (var i = 0; i < sheet.GetRow(0).LastCellNum; i++) { sheet.AutoSizeColumn(i); // Bump up with auto-sized column width to account for bold headers sheet.SetColumnWidth(i, sheet.GetColumnWidth(i) + 1024); } // Add row indicating date/time report was generated... // sheet.CreateRow(rowIndex + 1).CreateCell(0).SetCellValue("Report generated on " + DateTime.Now.ToString("dd/MM/yyyy")); #endregion // Save the Excel spreadsheet to a MemoryStream and return it to the client using (var exportData = new MemoryStream()) { workbook.Write(exportData); var saveAsFileName = string.Format("Supplier-{0}.xls", DateTime.Now.ToString("ddMMyyyyHHmmss")).Replace("/", "-"); Response.ContentType = "application/vnd.ms-excel"; Response.AddHeader("Content-Disposition", string.Format("attachment;filename={0}", saveAsFileName)); Response.Clear(); Response.BinaryWrite(exportData.GetBuffer()); Response.End(); } }
/// <summary> /// DataTable������Excel��MemoryStream /// </summary> /// <param name="dtSource">ԴDataTable</param> /// <param name="strHeaderText">��ͷ�ı�</param> public static MemoryStream Export(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++) { 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 = CellHorizontalAlignment.CENTER; HSSFFont font = (HSSFFont)workbook.CreateFont(); font.FontHeightInPoints = 20; font.Boldweight = 700; headStyle.SetFont(font); headerRow.GetCell(0).CellStyle = headStyle; sheet.AddMergedRegion(new Region(0, 0, 0, dtSource.Columns.Count - 1)); //headerRow.Dispose(); } #endregion #region ��ͷ����ʽ { HSSFRow headerRow = (HSSFRow)sheet.CreateRow(1); HSSFCellStyle headStyle = (HSSFCellStyle)workbook.CreateCellStyle(); //headStyle.Alignment = CellHorizontalAlignment.CENTER; HSSFFont font = (HSSFFont)workbook.CreateFont(); font.FontHeightInPoints = 10; font.Boldweight = 700; headStyle.SetFont(font); foreach (DataColumn column in dtSource.Columns) { headerRow.CreateCell(column.Ordinal).SetCellValue(column.ColumnName); headerRow.GetCell(column.Ordinal).CellStyle = headStyle; //�����п� sheet.SetColumnWidth(column.Ordinal, (arrColWidth[column.Ordinal] + 1) * 256); } //headerRow.Dispose(); } #endregion 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++; } using (MemoryStream ms = new MemoryStream()) { workbook.Write(ms); ms.Flush(); ms.Position = 0; //sheet.(); //workbook.Dispose();//һ��ֻ��д��һ����OK�ˣ�����������ͷ�������Դ������ǰ�汾����������ֻ�ͷ�sheet return ms; } }