public void AddDateRangetoExcelSheet(HSSFWorkbook workbook, ISheet sheet) { //Create a Title row var titleFont = workbook.CreateFont(); titleFont.Boldweight = (short)NPOI.SS.UserModel.FontBoldWeight.Bold; titleFont.FontHeightInPoints = 11; titleFont.Underline = NPOI.SS.UserModel.FontUnderlineType.Single; var titleStyle = workbook.CreateCellStyle(); titleStyle.SetFont(titleFont); var row = sheet.CreateRow(rowCount++); var cell = row.CreateCell(0); cell.CellStyle = titleStyle; cell.SetCellValue("Date Range"); row = sheet.CreateRow(rowCount++); cell = row.CreateCell(0); var value = string.Format("Start Date: {0}", StartDate.ToString("MM-dd-yyyy")); cell.SetCellValue(value); row = sheet.CreateRow(rowCount++); cell = row.CreateCell(0); value = string.Format("End Date: {0}", EndDate.ToString("MM-dd-yyyy")); cell.SetCellValue(value); }
public void TestDoesNoHarmIfNothingToDo() { HSSFWorkbook wb = new HSSFWorkbook(); IFont f = wb.CreateFont(); f.FontName = ("Testing"); NPOI.SS.UserModel.ICellStyle s = wb.CreateCellStyle(); s.SetFont(f); Assert.AreEqual(5, wb.NumberOfFonts); Assert.AreEqual(22, wb.NumCellStyles); // Optimise fonts HSSFOptimiser.OptimiseFonts(wb); Assert.AreEqual(5, wb.NumberOfFonts); Assert.AreEqual(22, wb.NumCellStyles); Assert.AreEqual(f, s.GetFont(wb)); // Optimise styles HSSFOptimiser.OptimiseCellStyles(wb); Assert.AreEqual(5, wb.NumberOfFonts); Assert.AreEqual(22, wb.NumCellStyles); Assert.AreEqual(f, s.GetFont(wb)); }
public static StyleContainer CreateStyles(HSSFWorkbook workbook) { var styles = new StyleContainer(); var h1Font = workbook.CreateFont(); h1Font.FontHeightInPoints = (short)24; h1Font.Boldweight = (short)NPOI.SS.UserModel.FontBoldWeight.BOLD; var h2Font = workbook.CreateFont(); h2Font.FontHeightInPoints = (short)16; h2Font.Boldweight = (short)NPOI.SS.UserModel.FontBoldWeight.BOLD; var h3Font = workbook.CreateFont(); h3Font.FontHeightInPoints = (short)12; h3Font.Boldweight = (short)NPOI.SS.UserModel.FontBoldWeight.BOLD; var titleFont = workbook.CreateFont(); titleFont.Boldweight = (short)NPOI.SS.UserModel.FontBoldWeight.BOLD; styles.RightAligned = workbook.CreateCellStyle(); styles.RightAligned.Alignment = NPOI.SS.UserModel.HorizontalAlignment.RIGHT; styles.Date = workbook.CreateCellStyle(); styles.Date.Alignment = NPOI.SS.UserModel.HorizontalAlignment.RIGHT; styles.Date.DataFormat = HSSFDataFormat.GetBuiltinFormat("m/d/yy h:mm"); styles.Currency = workbook.CreateCellStyle(); styles.Currency.Alignment = NPOI.SS.UserModel.HorizontalAlignment.RIGHT; //styles.Currency.DataFormat = HSSFDataFormat.GetBuiltinFormat("$#,##0.00"); styles.Currency.DataFormat = (short)7; styles.LeftAligned = workbook.CreateCellStyle(); styles.LeftAligned.Alignment = NPOI.SS.UserModel.HorizontalAlignment.LEFT; styles.TitleStyle = workbook.CreateCellStyle(); styles.TitleStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.CENTER; styles.TitleStyle.SetFont(titleFont); // Fonts are set into a style so create a new one to use. styles.HeaderStyle = workbook.CreateCellStyle(); styles.HeaderStyle.SetFont(h1Font); styles.HeaderStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.CENTER; // Fonts are set into a style so create a new one to use. styles.Header2Style = workbook.CreateCellStyle(); styles.Header2Style.SetFont(h2Font); // Fonts are set into a style so create a new one to use. styles.Header3Style = workbook.CreateCellStyle(); styles.Header3Style.SetFont(h3Font); return styles; }
/// <summary> /// 加边框 /// </summary> /// <param Name="rowindex">1开始</param> /// <param Name="cellIndex">1开始</param> public void AddBorder( ISheet sheet, HSSFWorkbook workbook) { ICellStyle styel = workbook.CreateCellStyle(); styel.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center; // ------------------ IFont font1 = workbook.CreateFont(); font1.FontHeightInPoints = 11; font1.Boldweight = 600; font1.FontName = "宋体"; styel.SetFont(font1); for (int rowindex=1;rowindex<sheet.LastRowNum+1;rowindex++) { for (int cellIndex =0; cellIndex < dcs.Count;cellIndex++ ) { sheet.GetRow(rowindex).RowStyle = styel; ICell cell = sheet.GetRow(rowindex ).GetCell(cellIndex ); HSSFCellStyle Style = workbook.CreateCellStyle() as HSSFCellStyle; Style.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center; Style.VerticalAlignment = VerticalAlignment.Center; Style.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin; Style.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin; Style.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin; Style.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin; Style.DataFormat = 0; Style.SetFont(font1); cell.CellStyle = Style; } } }
public WorkbookExcelRender() { _workbook = new HSSFWorkbook(); _isDisposed = false; OddCellStyle = _workbook.CreateCellStyle(); EvenCellStyle = _workbook.CreateCellStyle(); OddCellStyleCenterAligned = _workbook.CreateCellStyle(); EvenCellStyleCenterAligned = _workbook.CreateCellStyle(); _nextCellIndex = -1; RTFHelper = new RTFHelper() { NegFont = _workbook.CreateFont(), NormalFont = _workbook.CreateFont(), PosFont = _workbook.CreateFont(), NegFontCrossed = _workbook.CreateFont(), NormalFontCrossed = _workbook.CreateFont(), PosFontCrossed = _workbook.CreateFont(), Pallete = _workbook.GetCustomPalette(), RTFRenderer = new RtfTextRender() }; InitDefaultRTFHelper(); InitDefaultCellStyles(); }
public void TestWriteSheetFont() { HSSFWorkbook wb = new HSSFWorkbook(); NPOI.SS.UserModel.ISheet s = wb.CreateSheet(); IRow r = null; //ICell c = null; IFont fnt = wb.CreateFont(); NPOI.SS.UserModel.ICellStyle cs = wb.CreateCellStyle(); fnt.Color=(NPOI.HSSF.Util.HSSFColor.RED.index); fnt.Boldweight=(short)FontBoldWeight.BOLD; cs.SetFont(fnt); for (short rownum = (short)0; rownum < 100; rownum++) { r = s.CreateRow(rownum); r.RowStyle=(cs); r.CreateCell(0); } wb = HSSFTestDataSamples.WriteOutAndReadBack(wb); SanityChecker sanityChecker = new SanityChecker(); sanityChecker.CheckHSSFWorkbook(wb); Assert.AreEqual(99, s.LastRowNum, "LAST ROW == 99"); Assert.AreEqual(0, s.FirstRowNum, "FIRST ROW == 0"); }
public void NpoiExcel(DataTable dt, string title) { NPOI.HSSF.UserModel.HSSFWorkbook book = new NPOI.HSSF.UserModel.HSSFWorkbook(); NPOI.SS.UserModel.ISheet sheet = book.CreateSheet("Sheet1"); NPOI.SS.UserModel.IRow headerrow = sheet.CreateRow(0); ICellStyle style = book.CreateCellStyle(); style.Alignment = HorizontalAlignment.CENTER; style.VerticalAlignment = VerticalAlignment.CENTER; for (int i = 0; i < dt.Columns.Count; i++) { ICell cell = headerrow.CreateCell(i); cell.CellStyle = style; cell.SetCellValue(dt.Columns[i].ColumnName); } for (int I = 0; I <= dt.Rows.Count - 1; I++) { HSSFRow row2 = (HSSFRow)sheet.CreateRow(I + 1); for (int j = 0; j <= dt.Columns.Count - 1; j++) { string DgvValue = dt.Rows[I][j].ToString(); row2.CreateCell(j).SetCellValue(DgvValue); sheet.SetColumnWidth(j, 20 * 150); } } MemoryStream ms = new MemoryStream(); book.Write(ms); Response.AddHeader("Content-Disposition", string.Format("attachment; filename={0}.xls", HttpUtility.UrlEncode(title + "_" + DateTime.Now.ToString("yyyy-MM-dd"), System.Text.Encoding.UTF8))); Response.BinaryWrite(ms.ToArray()); Response.End(); book = null; ms.Close(); ms.Dispose(); }
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; } }
/// <summary> /// Creates the excel workbook. /// </summary> /// <param name="subject">The subject.</param> public void CreateWorkbook(string subject) { //Creating the excel workbook NPOI.HSSF.UserModel.HSSFWorkbook wb = new NPOI.HSSF.UserModel.HSSFWorkbook(); //Creating summary information to the document NPOI.HPSF.DocumentSummaryInformation dsi = NPOI.HPSF.PropertySetFactory.CreateDocumentSummaryInformation(); dsi.Company = "Thoris"; //Applying summary information to the document wb.DocumentSummaryInformation = dsi; //Creating summary information for the data NPOI.HPSF.SummaryInformation si = NPOI.HPSF.PropertySetFactory.CreateSummaryInformation(); si.Subject = subject; //Applying summary information to the data wb.SummaryInformation = si; _workbook = wb; //Creating the current styles Font font = _workbook.CreateFont(); font.Boldweight = (short)FontBoldWeight.BOLD; NPOI.SS.UserModel.CellStyle cellStyle = _workbook.CreateCellStyle(); cellStyle.SetFont(font); _headerStyle = CreateHeaderStyle(_workbook); _dataStyle = CreateStyle(_workbook, false); }
public void NpoiExcel(DataTable dt, string title, string s) { NPOI.HSSF.UserModel.HSSFWorkbook book = new NPOI.HSSF.UserModel.HSSFWorkbook(); NPOI.SS.UserModel.ISheet sheet = book.CreateSheet("Sheet1"); NPOI.SS.UserModel.IRow headerrow = sheet.CreateRow(0); ICellStyle style = book.CreateCellStyle(); style.Alignment = HorizontalAlignment.CENTER; style.VerticalAlignment = VerticalAlignment.CENTER; string[] ss = s.Split(','); for (int i = 0; i < ss.Length; i++) { ICell cell = headerrow.CreateCell(i); cell.CellStyle = style; cell.SetCellValue(ss[i]); } for (int I = 0; I <= dt.Rows.Count - 1; I++) { HSSFRow row2 = (HSSFRow)sheet.CreateRow(I + 1); for (int j = 0; j <= dt.Columns.Count - 1; j++) { //double a = 0; //string b = ""; //try //{ // a = Convert.ToDouble(dt.Rows[I][j].ToString()); // row2.CreateCell(j).SetCellValue(a); // sheet.SetColumnWidth(j, 20 * 150); //} //catch //{ // b = dt.Rows[I][j].ToString(); // row2.CreateCell(j).SetCellValue(b); // sheet.SetColumnWidth(j, 20 * 150); //} string DgvValue = dt.Rows[I][j].ToString(); if (j == 8 || j == 9) { row2.CreateCell(j).SetCellValue(Convert.ToDouble(DgvValue)); } else { row2.CreateCell(j).SetCellValue(DgvValue); } sheet.SetColumnWidth(j, 20 * 150); } } MemoryStream ms = new MemoryStream(); book.Write(ms); Response.AddHeader("Content-Disposition", string.Format("attachment; filename={0}.xls", HttpUtility.UrlEncode(title + "_" + DateTime.Now.ToString("yyyy-MM-dd"), System.Text.Encoding.UTF8))); Response.BinaryWrite(ms.ToArray()); Response.End(); book = null; ms.Close(); ms.Dispose(); }
//数据导出为Excel private void btnExport_Click(object sender, EventArgs e) { string sql = "select CC_AutoId,CC_CustomerName,CC_CellPhone,CC_Landline,CC_BuyDate,CC_CarNum,CC_BracketNum from T_Customers"; using (SqlDataReader reader = SqlHelper.ExecuteReader(sql, CommandType.Text)) { if (reader.HasRows) { IWorkbook wb = new HSSFWorkbook(); ISheet sheet = wb.CreateSheet("customers"); int rowIndex = 0;//手动处理行索引 while (reader.Read()) { int autoId = reader.GetInt32(0); string name = reader.GetString(1); string cellPhone = reader.GetString(2); //数据为空,那么就不能Get..,所以要在这里判断 string landLine = reader.IsDBNull(3) ? null : reader.GetString(3);//可为空 DateTime BuyDate = reader.GetDateTime(4); string carNum = reader.GetString(5); string bracketNum = reader.GetString(6); IRow row = sheet.CreateRow(rowIndex); rowIndex++; row.CreateCell(0).SetCellValue(autoId); row.CreateCell(1).SetCellValue(name); row.CreateCell(2).SetCellValue(cellPhone); //如果为空或Null,把Excel这个单元格设置为值为空 if (string.IsNullOrEmpty(landLine)) { row.CreateCell(3).SetCellType(CellType.BLANK); } else { row.CreateCell(3).SetCellValue(landLine); } //============设置日期格式======= //创建单元格样式对象 ICellStyle cellStyle = wb.CreateCellStyle(); //设置单元格样式对象属性 cellStyle.DataFormat = HSSFDataFormat.GetBuiltinFormat("m/d/yy h:mm"); ICell BuyDateRow = row.CreateCell(4); BuyDateRow.CellStyle = cellStyle; BuyDateRow.SetCellValue(BuyDate); //==============设置完成======== row.CreateCell(5).SetCellValue(carNum); row.CreateCell(6).SetCellValue(bracketNum); } using (FileStream fs = File.OpenWrite("Customers.xls")) { wb.Write(fs); MessageBox.Show("ok"); } } } }
public void AddListToExcelSheet(HSSFWorkbook workbook, ISheet sheet, string Title, Dictionary<string, bool> list) { //Create a Title row var titleFont = workbook.CreateFont(); titleFont.Boldweight = (short)NPOI.SS.UserModel.FontBoldWeight.Bold; titleFont.FontHeightInPoints = 11; titleFont.Underline = NPOI.SS.UserModel.FontUnderlineType.Single; var titleStyle = workbook.CreateCellStyle(); titleStyle.SetFont(titleFont); var row = sheet.CreateRow(rowCount++); row = sheet.CreateRow(rowCount++); var cell = row.CreateCell(0); cell.CellStyle = titleStyle; cell.SetCellValue(Title); foreach (var org in list) { if (org.Value == true) { row = sheet.CreateRow(rowCount++); cell = row.CreateCell(0); cell.SetCellValue(org.Key); } } }
public void NpoiExcel(System.Data.DataTable dt, string file) { try { double sheetCountdbl = double.Parse(dt.Rows.Count.ToString()) / 60000; int sheetCount = (int)(Math.Ceiling(sheetCountdbl)); NPOI.HSSF.UserModel.HSSFWorkbook book = new NPOI.HSSF.UserModel.HSSFWorkbook(); for (int c = 0; c < sheetCount; c++) { string sheetname = "Sheet" + (c + 1).ToString(); NPOI.SS.UserModel.ISheet sheet = book.CreateSheet(sheetname); NPOI.SS.UserModel.IRow headerrow = sheet.CreateRow(0); ICellStyle style = book.CreateCellStyle(); style.Alignment = HorizontalAlignment.Center; style.VerticalAlignment = VerticalAlignment.Center; for (int i = 0; i < dt.Columns.Count; i++) { ICell cell = headerrow.CreateCell(i); cell.CellStyle = style; cell.SetCellValue(dt.Columns[i].ColumnName); } int js = 0;//计数 //表内容 for (int i = c * 60000; i < dt.Rows.Count; i++) { if (js > 59999) { break; } IRow row = sheet.CreateRow(js + 1); for (int j = 0; j < dt.Columns.Count; j++) { ICell cell = row.CreateCell(j); cell.CellStyle = style; cell.SetCellValue(dt.Rows[i][j].ToString()); } js++; } } FileStream fs = File.Create(file); book.Write(fs); fs.Flush(); fs.Close(); } catch (System.Exception ex) { throw new Exception(ex.Message); } }
public void TestSetAlignment() { // Create initial cell with default style IWorkbook wkb = new HSSFWorkbook(); ISheet sheet = wkb.CreateSheet(); IRow row = sheet.CreateRow(0); ICell cell = row.CreateCell(0); // Create a new cell with default style ICell cell2 = row.CreateCell(1); // Init a new cell style cloned from the one of cell 0 cell2.CellStyle = wkb.CreateCellStyle(); cell2.CellStyle.CloneStyleFrom(cell.CellStyle); // At this time cell style index should be different Assert.AreNotEqual(cell.CellStyle.Index, cell2.CellStyle.Index); // Set an arbitraty cell style property to differentiate the two styles cell.CellStyle.Alignment = HorizontalAlignment.Right; // Try to make the same change so that CellUtil will get existing style CellUtil.SetAlignment(cell2, wkb, (short)HorizontalAlignment.Right); // Check that cell style has properly been set to HorizontalAlignment.Right Assert.AreEqual(cell2.CellStyle.Alignment, HorizontalAlignment.Right); // Check that cell style index are the same again Assert.AreEqual(cell.CellStyle.Index, cell2.CellStyle.Index); // Init a new cell style cloned from the one of cell 0 cell2.CellStyle = wkb.CreateCellStyle(); cell2.CellStyle.CloneStyleFrom(cell.CellStyle); // Set an arbitraty cell style property to differentiate the two styles cell.CellStyle.Alignment = HorizontalAlignment.Left; // Try to make different change so that CellUtil will get new style CellUtil.SetAlignment(cell2, wkb, (short)HorizontalAlignment.Center); // Check that cell style has alignement property set to HorizontalAlignment.Center Assert.AreEqual(cell2.CellStyle.Alignment, HorizontalAlignment.Center); // Check that cell style index are different Assert.AreNotEqual(cell.CellStyle.Index, cell2.CellStyle.Index); }
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"); } }
/// <summary> /// 导出内容到excel下载 /// </summary> /// <param name="sender"></param> /// <param name="e"></param> protected void ExportToExcel() { HSSFWorkbook workbook = new HSSFWorkbook(); var sheet = workbook.CreateSheet(); //创建一个单元格样式 var backgroundColorStyle = workbook.CreateCellStyle(); backgroundColorStyle.FillPattern = NPOI.SS.UserModel.FillPatternType.SOLID_FOREGROUND; backgroundColorStyle.FillForegroundColor = HSSFColor.BLUE.index; //创建表头行列及赋值样式 var headTitles = new[] { "StudentCode", "ClassCode", "RoomName" }; var headRow = sheet.CreateRow(0); for (var i = 0; i < 3; i++) { var cell = headRow.CreateCell(i); if (i == 0) cell.CellStyle = backgroundColorStyle; cell.SetCellValue(headTitles[i]); } //创建数据行列 for (int i = 1; i < this._lessenList.Count + 1; i++) { var lesson = this._lessenList[i - 1]; var row = sheet.CreateRow(i); if (lesson.StudentCode == "BJ02") row.Height = 50 * 20; //设置背景色 var cellStudentCode = row.CreateCell(0); cellStudentCode.CellStyle = backgroundColorStyle; cellStudentCode.SetCellValue(lesson.StudentCode); row.CreateCell(1).SetCellValue(lesson.ClassCode); row.CreateCell(2).SetCellValue(lesson.RoomName); } //数据写进内存流 MemoryStream memoryStream = new MemoryStream(); workbook.Write(memoryStream); //销毁对象 workbook = null; sheet = null; headRow = null; //将内存流以附件形式输出到客户端 var bytes = memoryStream.ToArray(); this._response.AddHeader("Content-Disposition", "attachment;filename=1.xls"); this._response.AddHeader("Content-Length", bytes.Length.ToString()); this._response.OutputStream.Write(bytes, 0, bytes.Length); this._response.Flush(); }
/// <summary> /// 导出excel /// </summary> /// <param name="response"></param> /// <param name="dt"></param> /// <param name="fileName"></param> /// <param name="sheetname"></param> public static void ExportExcel(HttpResponse response, DataTable dt, string fileName, string sheetname) { NPOI.HSSF.UserModel.HSSFWorkbook book = new NPOI.HSSF.UserModel.HSSFWorkbook(); 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.IFont font = book.CreateFont(); NPOI.SS.UserModel.ICellStyle style = book.CreateCellStyle(); font.Boldweight = (short)NPOI.SS.UserModel.FontBoldWeight.Bold; font.FontName = "微软雅黑"; style.SetFont(font); 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); } } } //写入到客户端 System.IO.MemoryStream ms = new System.IO.MemoryStream(); book.Write(ms); response.AddHeader("Content-Disposition", string.Format("attachment; filename=" + HttpUtility.UrlEncode(fileName, Encoding.UTF8) + DateTime.Now.ToShortDateString() + ".xls")); response.ContentType = "application/vnd.ms-excel"; //response.ContentEncoding = System.Text.Encoding.GetEncoding("gb2312"); response.BinaryWrite(ms.ToArray()); book = null; ms.Close(); ms.Dispose(); }
public ICellStyle CreateBorderCellStyle(HSSFWorkbook wb) { ICellStyle style = wb.CreateCellStyle(); style.Alignment = HorizontalAlignment.Center; style.VerticalAlignment = VerticalAlignment.Center; style.BorderTop = BorderStyle.Thin; style.BorderBottom = BorderStyle.Thin; style.BorderLeft = BorderStyle.Thin; style.BorderRight = BorderStyle.Thin; return style; }
protected void btnExportExcel_Click(object sender,EventArgs e) { HSSFWorkbook workBook = new HSSFWorkbook(); DocumentSummaryInformation dsi = PropertySetFactory.CreateDocumentSummaryInformation(); dsi.Company = "杭州农副产品物流网络科技有限公司"; workBook.DocumentSummaryInformation = dsi; SummaryInformation si = PropertySetFactory.CreateSummaryInformation(); si.Subject = "采购配送系统配送价格单页"; workBook.SummaryInformation = si; HSSFSheet sheet = workBook.CreateSheet("价格单页"); DataTable dt = logic.product.listWithPriceByBuyer(companyId); int rowIndex = 0; int colIndex = 0; HSSFRow newRow = null; HSSFCellStyle style = null; HSSFCell cell=null; foreach (DataRow row in dt.Rows) { newRow = sheet.CreateRow(rowIndex++); colIndex = 0; foreach (DataColumn col in dt.Columns) { cell=newRow.CreateCell(colIndex++); cell.SetCellValue(row[col].ToString()); if (rowIndex % 2 == 1) { style = workBook.CreateCellStyle(); style.FillBackgroundColor = NPOI.HSSF.Util.HSSFColor.GREY_80_PERCENT.index; style.FillPattern = HSSFCellStyle.SOLID_FOREGROUND; cell.CellStyle = style; } } } string filename = ddlCompanyList.SelectedItem.Text + ".xls"; Response.ContentType = "application/vnd.ms-excel"; Response.AddHeader("Content-Disposition", string.Format("attachment;filename={0}", HttpUtility.UrlEncode(filename, System.Text.Encoding.UTF8))); Response.Clear(); MemoryStream memoryStream = new MemoryStream(); workBook.Write(memoryStream); Response.BinaryWrite(memoryStream.GetBuffer()); Response.End(); }
/// <summary> /// 普通单元格样式 居中 /// </summary> /// <param name="book"></param> /// <returns>参见模型</returns> public ICellStyle getCellStyleCenter(NPOI.HSSF.UserModel.HSSFWorkbook book) { ICellStyle cellstyle = book.CreateCellStyle(); cellstyle.Alignment = HorizontalAlignment.Center; //水平居中 cellstyle.VerticalAlignment = VerticalAlignment.Center; //垂直居中 cellstyle.WrapText = true; //自动换行 IFont cellfont = book.CreateFont(); cellfont.FontHeightInPoints = 11; //11号字体 cellstyle.SetFont(cellfont); return(cellstyle); }
/// <summary> /// 标题单元格样式 /// </summary> /// <param name="book"></param> /// <returns>参见模型</returns> public ICellStyle getCellStyleTitle(NPOI.HSSF.UserModel.HSSFWorkbook book) { ICellStyle cellstyle = book.CreateCellStyle(); cellstyle.Alignment = HorizontalAlignment.Center; cellstyle.VerticalAlignment = VerticalAlignment.Center; IFont titlefont = book.CreateFont(); titlefont.FontHeightInPoints = 14; titlefont.Boldweight = (short)FontBoldWeight.Bold; //字体加粗 cellstyle.SetFont(titlefont); return(cellstyle); }
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; } } } }
public byte[] WriteExcel(IEnumerable data, string[] columns) { MemoryStream output = new MemoryStream(); HSSFWorkbook workbook = new HSSFWorkbook(); ISheet sheet = workbook.CreateSheet(); IFont headerFont = workbook.CreateFont(); headerFont.Boldweight = (short)FontBoldWeight.Bold; ICellStyle headerStyle = workbook.CreateCellStyle(); headerStyle.SetFont(headerFont); headerStyle.Alignment = HorizontalAlignment.Center; //(Optional) freeze the header row so it is not scrolled sheet.CreateFreezePane(0, 1, 0, 1); IEnumerator foo = data.GetEnumerator(); foo.MoveNext(); Type t = foo.Current.GetType(); IRow header = sheet.CreateRow(0); PropertyInfo[] properties = t.GetProperties(); int colIndex = 0; for (int i = 0; i < properties.Length; i++) { if (columns.Contains(properties[i].Name)) { ICell cell = header.CreateCell(colIndex); cell.CellStyle = headerStyle; cell.SetCellValue(properties[i].Name); colIndex++; } } int rowIndex = 0; foreach (object o in data) { colIndex = 0; IRow row = sheet.CreateRow(rowIndex + 1); for (int i = 0; i < properties.Length; i++) { if (columns.Contains(properties[i].Name)) { row.CreateCell(colIndex).SetCellValue(properties[i].GetValue(o, null).ToString()); colIndex++; } } rowIndex++; } workbook.Write(output); return output.ToArray(); }
/// <summary> /// 头部单元格样式 /// </summary> /// <param name="book"></param> /// <returns>参见模型</returns> public ICellStyle getCellStyleHead(NPOI.HSSF.UserModel.HSSFWorkbook book) { ICellStyle cellstyle = book.CreateCellStyle(); cellstyle.Alignment = HorizontalAlignment.Center; cellstyle.VerticalAlignment = VerticalAlignment.Center; cellstyle.WrapText = true; IFont cellheadfont = book.CreateFont(); cellheadfont.FontHeightInPoints = 11; cellheadfont.Boldweight = (short)FontBoldWeight.Bold; //字体加粗 cellstyle.SetFont(cellheadfont); return(cellstyle); }
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> /// 导出到Excel /// </summary> /// <param name="table"></param> /// <returns></returns> public bool ToExcel(DataTable table) { FileStream fs = new FileStream(this._filePath, FileMode.OpenOrCreate, FileAccess.ReadWrite); IWorkbook workBook = new HSSFWorkbook(); this._sheetName = this._sheetName.IsEmpty() ? "sheet1" : this._sheetName; ISheet sheet = workBook.CreateSheet(this._sheetName); //处理表格标题 IRow row = sheet.CreateRow(0); row.CreateCell(0).SetCellValue(this._title); sheet.AddMergedRegion(new CellRangeAddress(0, 0, 0, table.Columns.Count - 1)); row.Height = 500; ICellStyle cellStyle = workBook.CreateCellStyle(); IFont font = workBook.CreateFont(); font.FontName = "微软雅黑"; font.FontHeightInPoints = 17; cellStyle.SetFont(font); cellStyle.VerticalAlignment = VerticalAlignment.Center; cellStyle.Alignment = HorizontalAlignment.Center; row.Cells[0].CellStyle = cellStyle; //处理表格列头 row = sheet.CreateRow(1); for (int i = 0; i < table.Columns.Count; i++) { row.CreateCell(i).SetCellValue(table.Columns[i].ColumnName); row.Height = 350; sheet.AutoSizeColumn(i); } //处理数据内容 for (int i = 0; i < table.Rows.Count; i++) { row = sheet.CreateRow(2 + i); row.Height = 250; for (int j = 0; j < table.Columns.Count; j++) { row.CreateCell(j).SetCellValue(table.Rows[i][j].ToString()); sheet.SetColumnWidth(j, 256 * 15); } } //写入数据流 workBook.Write(fs); fs.Flush(); fs.Close(); return true; }
public void TestColorStyleCopy() { HSSFWorkbook bookA = new HSSFWorkbook(); HSSFWorkbook bookB = new HSSFWorkbook(); bookA.Workbook.CustomPalette.ClearColors(); bookA.Workbook.CustomPalette.SetColor(0x8, 12, 15, 255); //0x8 is blueish bookA.Workbook.CustomPalette.SetColor(0x9, 200, 200, 200); //0x9 is light gray bookB.Workbook.CustomPalette.ClearColors(); bookB.Workbook.CustomPalette.SetColor(0x8, 192, 168, 0); //Throw a color into the destination book so we can see color merge working. HSSFSheet sheetA = bookA.CreateSheet("Sheet A") as HSSFSheet; ICell cell = sheetA.CreateRow(0).CreateCell(0); cell.SetCellValue("I'm a stylish cell!"); IFont myFont = bookA.CreateFont(); myFont.FontName = "Times New Roman"; myFont.IsItalic = true; myFont.FontHeightInPoints = 12; myFont.Color = 0x8; ICellStyle myStyle = bookA.CreateCellStyle(); myStyle.SetFont(myFont); myStyle.FillForegroundColor = 0x9; cell.CellStyle = myStyle; HSSFSheet sheetB = bookB.CreateSheet("BookB Sheet") as HSSFSheet; ICell beeCell = sheetB.CreateRow(0).CreateCell(0); ICellStyle styleB = bookB.CreateCellStyle(); styleB.FillForegroundColor = 0x8; beeCell.CellStyle = styleB; beeCell.SetCellValue("Hello NPOI"); //Copy the sheet, and make sure the color, style, and font is correct sheetA.CopyTo(bookB, "Copied Sheet A", true, true); HSSFSheet theCopy = bookB.GetSheetAt(1) as HSSFSheet; ICell copiedCell = theCopy.GetRow(0).GetCell(0); //Check that the fill color got copied byte[] srcColor = bookA.Workbook.CustomPalette.GetColor(0x9); byte[] destColor = bookB.Workbook.CustomPalette.GetColor(copiedCell.CellStyle.FillForegroundColor); Assert.IsTrue(srcColor[0]==destColor[0] && srcColor[1]==destColor[1] && srcColor[2]==destColor[2]); //Check that the font color got copied srcColor = bookA.Workbook.CustomPalette.GetColor(0x8); destColor = bookB.Workbook.CustomPalette.GetColor(copiedCell.CellStyle.GetFont(bookB).Color); Assert.IsTrue(srcColor[0] == destColor[0] && srcColor[1] == destColor[1] && srcColor[2] == destColor[2]); //Check that the fill color of the cell originally in the destination book is still intact srcColor = bookB.Workbook.CustomPalette.GetColor(0x8); Assert.IsTrue(srcColor[0] == 192 && srcColor[1] == 168 && srcColor[2] == 0); //Check that the font made it over okay Assert.AreEqual(copiedCell.CellStyle.GetFont(bookB).FontName, myFont.FontName); }
public byte[] Create(List<string> columns, List<List<string>> rows, HttpServerUtilityBase server) { var workbook = new HSSFWorkbook(); var sheet = workbook.CreateSheet("Sheet1"); // create header style var headerFont = workbook.CreateFont(); headerFont.Boldweight = (short)FontBoldWeight.BOLD; var headerStyle = workbook.CreateCellStyle(); headerStyle.FillBackgroundColor = HSSFColor.GREY_40_PERCENT.index; headerStyle.FillForegroundColor = HSSFColor.GREY_40_PERCENT.index; headerStyle.FillPattern = FillPatternType.SOLID_FOREGROUND; headerStyle.SetFont(headerFont); // Getting the row... 0 is the first row. var dataRow = sheet.CreateRow(0); for (int i = 0; i < columns.Count; i++) { dataRow.CreateCell(i).SetCellValue(columns.ElementAt(i)); dataRow.GetCell(i).CellStyle = headerStyle; } var rowCount = 0; foreach (var a in rows) // gets the array that represents a row { rowCount++; dataRow = sheet.CreateRow(rowCount); for (var i = 0; i < a.Count; i++) // gets the individual cell { dataRow.CreateCell(i).SetCellValue(a[i]); } } // Forcing formula recalculation... sheet.ForceFormulaRecalculation = true; // adjust column widths for (int i = 0; i < columns.Count; i++) { sheet.AutoSizeColumn(i); } using (var ms = new MemoryStream()) { workbook.Write(ms); return ms.ToArray(); } }
public static ICellStyle CreateHeaderStyle(HSSFWorkbook wb, short foreColor, short backgroundColor) { var style = wb.CreateCellStyle(); style.FillForegroundColor = (short)17; var font = wb.CreateFont(); font.Color = foreColor; font.FontHeightInPoints = 12; style.SetFont(font); style.FillBackgroundColor = 16; style.FillForegroundColor = backgroundColor; style.FillPattern = FillPatternType.SOLID_FOREGROUND; return style; }
/// <summary> /// 导出Excel /// </summary> /// <param name="monthFlag"></param> public static void GetExcelReportItem(DataTable dt, string Name) { NPOI.HSSF.UserModel.HSSFWorkbook book = new NPOI.HSSF.UserModel.HSSFWorkbook(); NPOI.SS.UserModel.ISheet sheet1 = book.CreateSheet(Name); //开始行,小标从0开始 int StartRow = 1; //设置单元格样式 HSSFCellStyle style = (HSSFCellStyle)book.CreateCellStyle(); style.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin; style.BottomBorderColor = NPOI.HSSF.Util.HSSFColor.Black.Index; style.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin; //DASH_DOT_DOT style.LeftBorderColor = NPOI.HSSF.Util.HSSFColor.Black.Index; style.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin; //HAIR style.RightBorderColor = NPOI.HSSF.Util.HSSFColor.Black.Index; style.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin; //MEDIUM_DASHED style.TopBorderColor = NPOI.HSSF.Util.HSSFColor.Black.Index; #region 给excel数据源 //创建表头 IRow rowColumns = sheet1.CreateRow(0); rowColumns.HeightInPoints = 30; for (int c = 0; c < dt.Columns.Count; c++) { HSSFCell cell = (HSSFCell)rowColumns.CreateCell(c); cell.SetCellValue(dt.Columns[c].ColumnName); cell.CellStyle = style; } for (int i = 0; i < dt.Rows.Count; i++) { DataRow dr = dt.Rows[i]; //创建行 IRow row = sheet1.CreateRow(i + StartRow); row.HeightInPoints = 30; //填充数据 for (int j = 0; j < dt.Columns.Count; j++) { HSSFCell cell = (HSSFCell)row.CreateCell(j); cell.SetCellValue(dr[j].ToString()); cell.CellStyle = style; } } #endregion ImportExcel(book, string.Format("{0}" + Name, DateTime.Now.ToString("yyyy-MM-dd"))); }
/// <summary> /// 创建Excel标题行 /// </summary> /// <param name="workbook">工作簿对象</param> /// <param name="sheet">Excel单页</param> /// <param name="strHeaderText">标题行文本</param> /// <param name="dtSource">DataTable数据源</param> public void CreateRowHeader(HSSFWorkbook workbook, Sheet sheet, int startRowIndex, string strHeaderText, DataTable dtSource) { #region 表头及样式 { Row headerRow = sheet.CreateRow(startRowIndex); headerRow.HeightInPoints = 25; headerRow.CreateCell(0).SetCellValue(strHeaderText); CellStyle headStyle = workbook.CreateCellStyle(); headStyle.Alignment = HorizontalAlignment.CENTER; Font font = workbook.CreateFont(); font.FontHeightInPoints = 20; font.Boldweight = 700; headStyle.SetFont(font); headStyle.WrapText = true; headerRow.GetCell(0).CellStyle = headStyle; sheet.AddMergedRegion(new CellRangeAddress(0, 0, 0, dtSource.Columns.Count - 1)); } #endregion #region 列头及样式 { int[] arrColWidth = GetColumnWidth(dtSource); Row headerRow = sheet.CreateRow(startRowIndex+1); CellStyle headStyle = workbook.CreateCellStyle(); headStyle.Alignment = HorizontalAlignment.CENTER; Font font = workbook.CreateFont(); font.FontHeightInPoints = 10; font.Boldweight = 700; headStyle.SetFont(font); foreach (DataColumn column in dtSource.Columns) { headerRow.CreateCell(column.Ordinal).SetCellValue(column.ColumnName); headerRow.GetCell(column.Ordinal).CellStyle = headStyle; //设置列宽 sheet.SetColumnWidth(column.Ordinal, (arrColWidth[column.Ordinal] + 1) * 256); } } #endregion }
/// <summary> /// 导出Excel文件 /// </summary> /// <param name="dt"></param> /// <param name="path"></param> public static void ExportExcel(DataTable dt, string path) { hssfworkbook = new HSSFWorkbook(); DocumentSummaryInformation dsi = PropertySetFactory.CreateDocumentSummaryInformation(); hssfworkbook.DocumentSummaryInformation = dsi; SummaryInformation si = PropertySetFactory.CreateSummaryInformation(); hssfworkbook.SummaryInformation = si; ISheet sheet1 = hssfworkbook.CreateSheet(string.IsNullOrEmpty(dt.TableName) ? "Sheet1" : dt.TableName); IRow rowTitle = sheet1.CreateRow(0); ICell cellTitle; ICellStyle style = hssfworkbook.CreateCellStyle(); style.Alignment = HorizontalAlignment.CENTER; style.VerticalAlignment = VerticalAlignment.CENTER; for (int m = 0; m < dt.Columns.Count; m++) { cellTitle = rowTitle.CreateCell(m); cellTitle.CellStyle = style; cellTitle.SetCellValue(dt.Columns[m].ColumnName); } IRow row; ICell cell; if (dt.Rows.Count > 0) { for (int i = 0; i < dt.Rows.Count; i++) { row = sheet1.CreateRow(i + 1); for (int j = 0; j < dt.Columns.Count; j++) { cell = row.CreateCell(j); cell.CellStyle = style; string obj = dt.Rows[i][j].ToString(); cell.SetCellValue(obj); } } } var file = new FileStream(path, FileMode.Create); hssfworkbook.Write(file); file.Close(); }
/// <summary> /// 為資料建立成NPOI 的Workbook和Sheet /// </summary> /// <param name="sourceTable">要匯出的內容</param> /// <param name="workbook">The workbook.</param> /// <param name="sheet">The sheet.</param> /// <param name="headerRowIndex">標題欄的index起始位置</param> /// <param name="rowIndexStart">第一筆實際資料的起始位置</param> private void CreatWorksheetForSingleDataTable(DataTable sourceTable, ref HSSFWorkbook workbook, ref ISheet sheet, int headerRowIndex = 0, int rowIndexStart = 1) { IRow headerRow = sheet.CreateRow(headerRowIndex); // Set up the Header Coloumn cell style var headerCellStyle = workbook.CreateCellStyle(); headerCellStyle.BorderTop = BorderStyle.Thin; headerCellStyle.BorderBottom = BorderStyle.Thin; headerCellStyle.Alignment = HorizontalAlignment.Center; // Set up the Header Coloumn cell font style var headerCellFontStyle = workbook.CreateFont(); headerCellFontStyle.Boldweight = (short)FontBoldWeight.Bold; headerCellStyle.SetFont(headerCellFontStyle); // handling header. foreach (DataColumn column in sourceTable.Columns) { var headerCell = headerRow.CreateCell(column.Ordinal); headerCell.SetCellValue(column.ColumnName); headerCell.CellStyle = headerCellStyle; } // handling value. int rowIndex = rowIndexStart; foreach (DataRow row in sourceTable.Rows) { IRow dataRow = sheet.CreateRow(rowIndex); foreach (DataColumn column in sourceTable.Columns) { dataRow.CreateCell(column.Ordinal).SetCellValue(row[column].ToString()); } rowIndex++; } }
/// <summary> /// 将DataTable数据导出到Excel文件中(xls) /// </summary> /// <param name="dt"></param> /// <param name="file"></param> public static byte[] TableToExcelForXLS(DataTable dt, string file) { XLS.HSSFWorkbook hssfworkbook = new XLS.HSSFWorkbook(); ISheet sheet = hssfworkbook.CreateSheet("Data"); ICellStyle style = hssfworkbook.CreateCellStyle(); style.CloneStyleFrom(CellStyles[ExcelCellStyle.Style1]); //表头 IRow row = sheet.CreateRow(0); for (int i = 0; i < dt.Columns.Count; i++) { ICell cell = row.CreateCell(i); cell.SetCellValue(dt.Columns[i].ColumnName); cell.CellStyle = style; } //数据 for (int i = 0; i < dt.Rows.Count; i++) { IRow row1 = sheet.CreateRow(i + 1); for (int j = 0; j < dt.Columns.Count; j++) { ICell cell = row1.CreateCell(j); cell.SetCellValue(dt.Rows[i][j].ToString()); } } //转为字节数组 MemoryStream stream = new MemoryStream(); hssfworkbook.Write(stream); var buf = stream.ToArray(); return(buf); ////保存为Excel文件 //using (FileStream fs = new FileStream(file, FileMode.Create, FileAccess.Write)) //{ // fs.Write(buf, 0, buf.Length); // fs.Flush(); //} }
private static void CreateHeadRow(DataTable dt, HSSFWorkbook workbook, ISheet sheet) { ICellStyle titleStyle = workbook.CreateCellStyle(); var styleHeader = CreateHeaderStyle(workbook, HSSFColor.WHITE.index, HSSFColor.GREEN.index); IRow row = sheet.CreateRow(0); ICell c1 = row.CreateCell(0); ICell c2 = row.CreateCell(1); ICell c3 = row.CreateCell(2); int i = 0; foreach (DataColumn cell in dt.Columns) { ICell iCell = row.CreateCell(i); iCell.SetCellValue(cell.ColumnName); iCell.CellStyle = styleHeader; sheet.AutoSizeColumn(i); i++; } }
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); } }
/// <summary> /// 设置excel中单元格的样式 /// djin--2012-5-25 /// </summary> /// <param name="hssfworkbook"></param> /// <param name="options"></param> /// <returns></returns> public CellStyle setCellstyle(HSSFWorkbook hssfworkbook, string[] options) { CellStyle style3 = hssfworkbook.CreateCellStyle(); foreach (string s in options) { if (s == "Border") { style3.BorderBottom = CellBorderType.THIN; style3.BorderLeft = CellBorderType.THIN; style3.BorderRight = CellBorderType.THIN; style3.BorderTop = CellBorderType.THIN; } if (s == "BorderNone") { style3.BorderBottom = CellBorderType.NONE; style3.BorderLeft = CellBorderType.NONE; style3.BorderRight = CellBorderType.NONE; style3.BorderTop = CellBorderType.NONE; } if (s == "Left") { style3.VerticalAlignment = VerticalAlignment.CENTER; style3.Alignment = HorizontalAlignment.LEFT; } if (s == "Right") { style3.VerticalAlignment = VerticalAlignment.CENTER; style3.Alignment = HorizontalAlignment.RIGHT; } if (s == "DateTime") { DataFormat format = hssfworkbook.CreateDataFormat(); style3.DataFormat = format.GetFormat("yyyy-mm-dd"); } } Font font3 = hssfworkbook.CreateFont(); font3.FontHeightInPoints = 9; style3.SetFont(font3); return style3; }
public void TestWriteSheetFont() { string filepath = TempFile.GetTempFilePath("TestWriteSheetFont", ".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; IFont fnt = wb.CreateFont(); NPOI.SS.UserModel.ICellStyle cs = wb.CreateCellStyle(); fnt.Color=(NPOI.HSSF.Util.HSSFColor.Red.Index); fnt.Boldweight= (short)FontBoldWeight.Bold; cs.SetFont(fnt); for (short rownum = ( short ) 0; rownum < 100; rownum++) { r = s.CreateRow(rownum); // r.SetRowNum(( short ) rownum); for (short cellnum = ( short ) 0; cellnum < 50; cellnum += 2) { c = r.CreateCell(cellnum); c.SetCellValue(rownum * 10000 + cellnum + ((( double ) rownum / 1000) + (( double ) cellnum / 10000))); c = r.CreateCell(cellnum + 1); c.SetCellValue("TEST"); c.CellStyle = (cs); } } wb.Write(out1); out1.Close(); SanityChecker sanityChecker = new SanityChecker(); sanityChecker.CheckHSSFWorkbook(wb); Assert.AreEqual(99, s.LastRowNum, "LAST ROW == 99"); Assert.AreEqual(0, s.FirstRowNum, "FIRST ROW == 0"); // assert((s.LastRowNum == 99)); }
public void TestDoesNoHarmIfNothingToDo() { HSSFWorkbook wb = new HSSFWorkbook(); // New files start with 4 built in fonts, and 21 built in styles Assert.AreEqual(4, wb.NumberOfFonts); Assert.AreEqual(21, wb.NumCellStyles); // Create a test font and style, and use them IFont f = wb.CreateFont(); f.FontName = ("Testing"); NPOI.SS.UserModel.ICellStyle s = wb.CreateCellStyle(); s.SetFont(f); HSSFSheet sheet = (HSSFSheet)wb.CreateSheet(); HSSFRow row = (HSSFRow)sheet.CreateRow(0); row.CreateCell(0).CellStyle = (s); // Should have one more than the default of each Assert.AreEqual(5, wb.NumberOfFonts); Assert.AreEqual(22, wb.NumCellStyles); // Optimise fonts HSSFOptimiser.OptimiseFonts(wb); Assert.AreEqual(5, wb.NumberOfFonts); Assert.AreEqual(22, wb.NumCellStyles); Assert.AreEqual(f, s.GetFont(wb)); // Optimise styles HSSFOptimiser.OptimiseCellStyles(wb); Assert.AreEqual(5, wb.NumberOfFonts); Assert.AreEqual(22, wb.NumCellStyles); Assert.AreEqual(f, s.GetFont(wb)); }
private HSSFWorkbook NPOIHelper(List <ExportAllScoreViewModel> models) { //创建Excel文件的对象 NPOI.HSSF.UserModel.HSSFWorkbook book = new NPOI.HSSF.UserModel.HSSFWorkbook(); //添加一个sheet NPOI.SS.UserModel.ISheet sheet1 = book.CreateSheet("Sheet1"); ICellStyle cellstyle = book.CreateCellStyle(); cellstyle.VerticalAlignment = VerticalAlignment.Center; cellstyle.Alignment = HorizontalAlignment.Center; //sheet1.SetColumnWidth(0,100); //sheet1.SetColumnWidth(1, 100); //sheet1.SetColumnWidth(2, 100); //sheet1.SetColumnWidth(3, 100); //sheet1.SetColumnWidth(4, 100); //sheet1.SetColumnWidth(5, 100); //sheet1.SetColumnWidth(6, 100); #region [头部设计] //给sheet1添加第一行的头部标题 NPOI.SS.UserModel.IRow row1 = sheet1.CreateRow(0); row1.CreateCell(0).SetCellValue("序号"); row1.CreateCell(1).SetCellValue("作品名称"); row1.CreateCell(2).SetCellValue("作者姓名"); row1.CreateCell(3).SetCellValue("单位名称"); row1.CreateCell(4).SetCellValue("评审指标"); row1.CreateCell(8).SetCellValue("总分"); row1.CreateCell(9).SetCellValue("评审意见"); row1.Cells.ForEach(ce => { ce.CellStyle = cellstyle; }); //给sheet1添加第二行的头部标题 NPOI.SS.UserModel.IRow row2 = sheet1.CreateRow(1); row2.CreateCell(4).SetCellValue("教学设计(25)"); row2.CreateCell(5).SetCellValue("教学行为(25)"); row2.CreateCell(6).SetCellValue("教学效果(25)"); row2.CreateCell(7).SetCellValue("创新与实用(25)"); row2.Cells.ForEach(ce => { ce.CellStyle = cellstyle; }); sheet1.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(0, 1, 0, 0)); // 序号 sheet1.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(0, 1, 1, 1)); // 作品名称 sheet1.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(0, 1, 2, 2)); sheet1.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(0, 1, 3, 3)); sheet1.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(0, 0, 4, 7)); sheet1.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(0, 1, 8, 8)); sheet1.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(0, 1, 9, 9)); #endregion var i = 0; foreach (var item in models) { i++; NPOI.SS.UserModel.IRow rowtemp = sheet1.CreateRow(i + 1); rowtemp.CreateCell(0).SetCellValue(i); rowtemp.CreateCell(1).SetCellValue(item.ResourceName); rowtemp.CreateCell(2).SetCellValue(item.Author); rowtemp.CreateCell(3).SetCellValue(item.AuthorCompany); rowtemp.CreateCell(4).SetCellValue(item.Score1); rowtemp.CreateCell(5).SetCellValue(item.Score2); rowtemp.CreateCell(6).SetCellValue(item.Score3); rowtemp.CreateCell(7).SetCellValue(item.Score4); rowtemp.CreateCell(8).SetCellValue(item.Score); rowtemp.CreateCell(9).SetCellValue(item.Comment); } NPOI.SS.UserModel.IRow rowtemp2 = sheet1.CreateRow(i + 3); rowtemp2.CreateCell(1).SetCellValue("评审组:"); rowtemp2.CreateCell(2).SetCellValue(models.FirstOrDefault().GroupName); rowtemp2.CreateCell(4).SetCellValue("评审专家:"); rowtemp2.CreateCell(8).SetCellValue("评审时间:"); return(book); }
private static MemoryStream GetExcelMemoryStream(List <dynamic> orderItemList) { var first = orderItemList.FirstOrDefault(); //创建表格 NPOI.HSSF.UserModel.HSSFWorkbook book = new NPOI.HSSF.UserModel.HSSFWorkbook(); NPOI.SS.UserModel.ISheet sheet = book.CreateSheet(); #region 定义样式 //创建字体 var cellStyleFont = (HSSFFont)book.CreateFont(); cellStyleFont.IsBold = true; cellStyleFont.FontName = "宋体"; cellStyleFont.FontHeightInPoints = 10; //RGB自定义背景色 HSSFPalette palette = book.GetCustomPalette(); palette.SetColorAtIndex(HSSFColor.Pink.Index, (byte)220, (byte)220, (byte)220); // titleStyle HSSFCellStyle titleStyle = (HSSFCellStyle)book.CreateCellStyle(); //(自定义背景色)单元格背景颜色 和FillPattern必须一起设置 titleStyle.FillForegroundColor = HSSFColor.Pink.Index; titleStyle.FillPattern = FillPattern.SolidForeground; titleStyle.VerticalAlignment = VerticalAlignment.Center; //垂直居中 titleStyle.BorderBottom = BorderStyle.Thin; titleStyle.BorderLeft = BorderStyle.Thin; titleStyle.BorderRight = BorderStyle.Thin; titleStyle.BorderTop = BorderStyle.Thin; titleStyle.SetFont(cellStyleFont);//设置字体 //headStyle HSSFCellStyle headStyle = (HSSFCellStyle)book.CreateCellStyle(); headStyle.Alignment = HorizontalAlignment.Center; //水平居中 headStyle.BorderBottom = BorderStyle.Thin; headStyle.BorderLeft = BorderStyle.Thin; headStyle.BorderRight = BorderStyle.Thin; headStyle.BorderTop = BorderStyle.Thin; headStyle.SetFont(cellStyleFont); //borderStyle HSSFCellStyle borderStyle = (HSSFCellStyle)book.CreateCellStyle(); borderStyle.BorderBottom = BorderStyle.Thin; borderStyle.BorderLeft = BorderStyle.Thin; borderStyle.BorderRight = BorderStyle.Thin; borderStyle.BorderTop = BorderStyle.Thin; #endregion #region 表头 //设置单元格宽度 sheet.SetColumnWidth(0, 5000); //采购日期 sheet.SetColumnWidth(1, 5000); //门店名称 sheet.SetColumnWidth(2, 14000); //门店地址 sheet.SetColumnWidth(3, 4500); //采购数量 sheet.SetColumnWidth(4, 4500); //操作人 //表头 NPOI.SS.UserModel.IRow row = sheet.CreateRow(4); row.Height = 350; row.CreateCell(0).SetCellValue("采购日期"); row.CreateCell(1).SetCellValue("门店名称"); row.CreateCell(2).SetCellValue("门店地址"); row.CreateCell(3).SetCellValue("采购数量"); row.CreateCell(4).SetCellValue("操作人"); for (int i = 0; i < 5; i++) { row.Cells[i].CellStyle = titleStyle; } //合并单元格 CellRangeAddress region0 = new CellRangeAddress(0, 0, 0, 4); sheet.AddMergedRegion(region0); IRow row0 = sheet.CreateRow(0); row0.CreateCell(0).SetCellValue("冷链食品处理详情"); row0.Cells[0].CellStyle = headStyle; var row0Cell4 = row0.CreateCell(4); row0Cell4.CellStyle = headStyle; IRow row1 = sheet.CreateRow(1); row1.CreateCell(0).SetCellValue("处理商品批次码"); row1.CreateCell(1).SetCellValue(first.BatchNo.ToString()); row1.Cells[0].CellStyle = titleStyle; IRow row2 = sheet.CreateRow(2); row2.CreateCell(0).SetCellValue("处理日期"); row2.CreateCell(1).SetCellValue(first.NoticeTime?.ToString("yyyy.MM.dd")); row2.Cells[0].CellStyle = titleStyle; IRow row3 = sheet.CreateRow(3); row3.CreateCell(0).SetCellValue("处理人"); row3.CreateCell(1).SetCellValue(first.NoticeUserName.ToString()); row3.Cells[0].CellStyle = titleStyle; //设置黑色边框 for (int i = 1; i < 4; i++) { var iRow = sheet.GetRow(i); for (int j = 1; j < 5; j++) { var jCell = (j == 1) ? iRow.GetCell(j) : iRow.CreateCell(j); jCell.CellStyle = borderStyle; } } #endregion #region 循环数据 int index = 5; foreach (var item in orderItemList) { IRow itemRow = sheet.CreateRow(index); var rowCell0 = itemRow.CreateCell(0); rowCell0.SetCellValue((item.ImportTime ?? item.SubTime).ToString("yyyy.MM.dd")); rowCell0.CellStyle = borderStyle; var rowCell1 = itemRow.CreateCell(1); rowCell1.SetCellValue(item.TargetStoreName.ToString()); rowCell1.CellStyle = borderStyle; var rowCell2 = itemRow.CreateCell(2); rowCell2.SetCellValue(item.TargetStoreAddress.ToString()); rowCell2.CellStyle = borderStyle; var rowCell3 = itemRow.CreateCell(3); rowCell3.SetCellValue(item.Number?.ToString()); rowCell3.CellStyle = borderStyle; var rowCell4 = itemRow.CreateCell(4); rowCell4.SetCellValue(item.ImportUserNames.ToString()); rowCell4.CellStyle = borderStyle; index++; } #endregion MemoryStream ms = new MemoryStream(); book.Write(ms); return(ms); }
void ExportAll(object filePath) { this.Invoke(new Action(() => { })); lock (obj) { DataTable tables_1 = SqlHelper_1.GetTables(); DataTable tables_2 = SqlHelper_2.GetTables(); OrderedEnumerableRowCollection <DataRow> dataRows_1 = tables_1.AsEnumerable().OrderBy(a => a.Field <string>("name")); OrderedEnumerableRowCollection <DataRow> dataRows_2 = tables_2.AsEnumerable().OrderBy(a => a.Field <string>("name")); //创建Excel文件的对象 IWorkbook book = new NPOI.HSSF.UserModel.HSSFWorkbook(); #region 设置表头样式 //创建样式对象 ICellStyle style = book.CreateCellStyle(); //创建一个字体样式对象 IFont font = book.CreateFont(); font.Boldweight = short.MaxValue; font.FontHeightInPoints = 16; //水平居中 style.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center; //垂直居中 style.Alignment = NPOI.SS.UserModel.HorizontalAlignment.CenterSelection; //将字体样式赋给样式对象 style.SetFont(font); #endregion #region 设置内容样式 //创建样式对象 ICellStyle style2 = book.CreateCellStyle(); //水平居中 style2.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center; //垂直居中 style2.Alignment = NPOI.SS.UserModel.HorizontalAlignment.CenterSelection; #endregion foreach (DataRow row in dataRows_1) { string tablename = row["name"].ToString().Trim(); DataTable tableInfo_1 = SqlHelper_1.GetTableInfo(tablename); DataTable tableInfo_2 = SqlHelper_2.GetTableInfo(tablename); IEnumerable <DataRow> query = tableInfo_1.AsEnumerable().Except(tableInfo_2.AsEnumerable(), DataRowComparer.Default); if (query.Count() > 0) { DataTable changesTable = query.CopyToDataTable(); //添加一个sheet NPOI.SS.UserModel.ISheet sheet1 = book.CreateSheet(tablename); //给sheet1添加第一行的头部标题 NPOI.SS.UserModel.IRow row1 = sheet1.CreateRow(0); int c = 0; foreach (DataColumn item in changesTable.Columns) { //设置宽度 sheet1.SetColumnWidth(c, 40 * 150); ICell cell = row1.CreateCell(c); cell.SetCellValue(item.Caption); cell.CellStyle = style; c++; } //将数据逐步写入sheet1各个行 int k = 0; foreach (DataRow item in changesTable.Rows) { NPOI.SS.UserModel.IRow rowtemp = sheet1.CreateRow(k + 1); for (int i = 0; i < changesTable.Columns.Count; i++) { ICell cell = rowtemp.CreateCell(i); cell.SetCellValue(item[i].ToString()); cell.CellStyle = style2; } k++; } } } //写入文件 FileStream xlsfile = new FileStream(filePath.ToString(), FileMode.Create); book.Write(xlsfile); xlsfile.Dispose(); this.Invoke(new Action(() => { MessageBox.Show("导出成功,文件存放于桌面", "导出提示", MessageBoxButtons.OK, MessageBoxIcon.None); })); isFinish = true; } }
public static HSSFWorkbook Export <T>(List <T> Objs, string[] headerList) { //创建Excel文件的对象 NPOI.HSSF.UserModel.HSSFWorkbook book = new NPOI.HSSF.UserModel.HSSFWorkbook(); //添加一个sheet NPOI.SS.UserModel.ISheet sheet1 = book.CreateSheet("Sheet1"); ICellStyle style = book.CreateCellStyle(); style.Alignment = HorizontalAlignment.Center; style.WrapText = true; style.FillPattern = FillPattern.SolidForeground; style.FillForegroundColor = HSSFColor.Grey25Percent.Index; style.VerticalAlignment = VerticalAlignment.Center; style.BorderBottom = BorderStyle.Double; style.BorderLeft = BorderStyle.Double; style.BorderRight = BorderStyle.Double; style.BorderTop = BorderStyle.Double; style.BottomBorderColor = HSSFColor.Grey50Percent.Index; style.LeftBorderColor = HSSFColor.Grey50Percent.Index; style.RightBorderColor = HSSFColor.Grey50Percent.Index; style.TopBorderColor = HSSFColor.Grey50Percent.Index; IFont font = book.CreateFont(); font.FontHeightInPoints = 14; font.Boldweight = (short)NPOI.SS.UserModel.FontBoldWeight.Bold; font.FontName = "標楷體"; font.Color = HSSFColor.White.Index; style.SetFont(font);//HEAD 样式 //row.CreateCell(0).SetCellValue(getString(syscomment, "Company") + "\n" + getString(syscomment, "Report")); //貌似这里可以设置各种样式字体颜色背景等,但是不是很方便,这里就不设置了 //给sheet1添加第一行的头部标题 NPOI.SS.UserModel.IRow row1 = sheet1.CreateRow(0); row1.Height = 80 * 5; for (int i = 0; i < headerList.Length; i++) { row1.CreateCell(i).SetCellValue(headerList[i]); row1.GetCell(i).CellStyle = style; if (i == 0) { sheet1.SetColumnWidth(i, 22 * 256); } if (i > 0) { sheet1.SetColumnWidth(i, 30 * 256); } } int m = 0; //将数据逐步写入sheet1各个行 foreach (T model in Objs) { NPOI.SS.UserModel.IRow rowtemp = sheet1.CreateRow(m + 1); int j = 0; foreach (PropertyInfo propertyInfo in typeof(T).GetProperties()) { rowtemp.CreateCell(j).SetCellValue(double.Parse(propertyInfo.GetValue(model, null).ToString())); j++; } m++; } return(book); }
public static HSSFWorkbook Export(DataTable dt, string[] headerList, string[] headercode) { //创建Excel文件的对象 NPOI.HSSF.UserModel.HSSFWorkbook book = new NPOI.HSSF.UserModel.HSSFWorkbook(); //添加一个sheet NPOI.SS.UserModel.ISheet sheet1 = book.CreateSheet("Sheet1"); ICellStyle style = book.CreateCellStyle(); style.Alignment = HorizontalAlignment.Center; style.VerticalAlignment = VerticalAlignment.Center; style.WrapText = true; style.FillPattern = FillPattern.SolidForeground; style.FillForegroundColor = HSSFColor.Grey25Percent.Index; style.VerticalAlignment = VerticalAlignment.Center; style.BorderBottom = BorderStyle.Double; style.BorderLeft = BorderStyle.Double; style.BorderRight = BorderStyle.Double; style.BorderTop = BorderStyle.Double; style.BottomBorderColor = HSSFColor.Grey50Percent.Index; style.LeftBorderColor = HSSFColor.Grey50Percent.Index; style.RightBorderColor = HSSFColor.Grey50Percent.Index; style.TopBorderColor = HSSFColor.Grey50Percent.Index; IFont font = book.CreateFont(); font.FontHeightInPoints = 14; font.Boldweight = (short)NPOI.SS.UserModel.FontBoldWeight.Bold; font.FontName = "標楷體"; font.Color = HSSFColor.Black.Index; style.SetFont(font);//HEAD 样式 //貌似这里可以设置各种样式字体颜色背景等,但是不是很方便,这里就不设置了 //给sheet1添加第一行的头部标题 NPOI.SS.UserModel.IRow row1 = sheet1.CreateRow(0); row1.Height = 80 * 5; ICellStyle cellstyle = book.CreateCellStyle(); cellstyle.Alignment = HorizontalAlignment.Left; cellstyle.VerticalAlignment = VerticalAlignment.Top; cellstyle.WrapText = true; for (int i = 0; i < headerList.Length; i++) { row1.CreateCell(i).SetCellValue(headerList[i]); row1.GetCell(i).CellStyle = style; if (i == 0) { sheet1.SetColumnWidth(i, 22 * 256); } if (i > 0) { sheet1.SetColumnWidth(i, 30 * 256); } } //定义第二个工作簿防止内容溢出报错问题 NPOI.SS.UserModel.ISheet sheet2 = null; if (dt.Rows.Count > 65535) { sheet2 = book.CreateSheet("Sheet2"); //给sheet1添加第一行的头部标题 NPOI.SS.UserModel.IRow row11 = sheet2.CreateRow(0); row11.Height = 80 * 5; ICellStyle cellstyle1 = book.CreateCellStyle(); cellstyle1.Alignment = HorizontalAlignment.Left; cellstyle1.VerticalAlignment = VerticalAlignment.Top; cellstyle1.WrapText = true; for (int i = 0; i < headerList.Length; i++) { row11.CreateCell(i).SetCellValue(headerList[i]); row11.GetCell(i).CellStyle = style; if (i == 0) { sheet2.SetColumnWidth(i, 22 * 256); } if (i > 0) { sheet2.SetColumnWidth(i, 30 * 256); } } } int k = 0; //将数据逐步写入sheet1各个行 for (int i = 0; i < dt.Rows.Count; i++) { if (i > 65534) { NPOI.SS.UserModel.IRow rowtemp = sheet2.CreateRow(k); rowtemp.HeightInPoints = 65; for (int j = 0; j < headerList.Length; j++) { rowtemp.CreateCell(j).SetCellValue(dt.Rows[i][headercode[j]].ToString()); rowtemp.GetCell(j).CellStyle = cellstyle; } k++; } else { NPOI.SS.UserModel.IRow rowtemp = sheet1.CreateRow(i + 1); rowtemp.HeightInPoints = 65; for (int j = 0; j < headerList.Length; j++) { rowtemp.CreateCell(j).SetCellValue(dt.Rows[i][headercode[j]].ToString()); rowtemp.GetCell(j).CellStyle = cellstyle; } } } return(book); }
/// <summary> /// 导出问卷数据 /// </summary> /// <param name="list"></param> public string CreateExcel() { list = (from r in _db.tbl_record join emp in _db.tbl_employeeMK on new { EId = r.eId.Value } equals new { EId = emp.eId } join ans in ( (from a in _db.tbl_answer group a by new { a.rId } into g select new { arid = g.Key.rId.Value, answer1 = g.Max(p => (p.qId == 1 ? p.aContent : null)), answer2 = g.Max(p => (p.qId == 2 ? p.aContent : null)), answer3 = g.Max(p => (p.qId == 3 ? p.aContent : null)), answer4 = g.Max(p => (p.qId == 4 ? p.aContent : null)), answer5 = g.Max(p => (p.qId == 5 ? p.aContent : null)), answer6 = g.Max(p => (p.qId == 6 ? p.aContent : null)), answer7 = g.Max(p => (p.qId == 7 ? p.aContent : null)), answer8 = g.Max(p => (p.qId == 8 ? p.aContent : null)), answer9 = g.Max(p => (p.qId == 9 ? p.aContent : null)), answer10 = g.Max(p => (p.qId == 10 ? p.aContent : null)), answer11 = g.Max(p => (p.qId == 11 ? p.aContent : null)), answer12 = g.Max(p => (p.qId == 12 ? p.aContent : null)) }))on new { RId = r.rId } equals new { RId = ans.arid } orderby r.rId select new RecordModel { AnswerId = r.rId, AnswerName = emp.eName, AnswerNumber = emp.eNumber, AnswerPhone = emp.ePhone, AnswerDuration = r.rDuration.Value, AnswerTime = r.rEndtime.Value, AnswerAnswer1 = ans.answer1, AnswerAnswer2 = ans.answer2, AnswerAnswer3 = ans.answer3, AnswerAnswer4 = ans.answer4, AnswerAnswer5 = ans.answer5, AnswerAnswer6 = ans.answer6, AnswerAnswer7 = ans.answer7, AnswerAnswer8 = ans.answer8, AnswerAnswer9 = ans.answer9, AnswerAnswer10 = ans.answer10, AnswerAnswer11 = ans.answer11, AnswerAnswer12 = ans.answer12, } ).ToList(); if (list != null && list.Count > 0) // { try { NPOI.HSSF.UserModel.HSSFWorkbook book = new NPOI.HSSF.UserModel.HSSFWorkbook(); NPOI.SS.UserModel.ISheet sheet = book.CreateSheet("问卷数据下载"); // NPOI.SS.UserModel.IRow rowHeader = sheet.CreateRow(0); sheet.SetColumnWidth(0, 12 * 256); sheet.SetColumnWidth(1, 10 * 256); sheet.SetColumnWidth(2, 15 * 256); sheet.SetColumnWidth(3, 18 * 256); sheet.SetColumnWidth(4, 20 * 256); sheet.SetColumnWidth(5, 25 * 256); ICellStyle style = book.CreateCellStyle(); HSSFColor color = new HSSFColor.BLACK(); FillPatternType fillPattern = FillPatternType.SOLID_FOREGROUND;//灰色背景 HSSFColor backGround = new HSSFColor.GREY_25_PERCENT(); IFont font = ExportData.GetFontStyle(book, "宋体", color, 11); //设置单元格的样式:水平对齐居中 style = ExportData.GetCellStyle(book, font, fillPattern, backGround, HorizontalAlignment.CENTER, VerticalAlignment.CENTER); ICell cell1 = rowHeader.CreateCell(0); cell1.SetCellValue("姓名"); //将新的样式赋给单元格 cell1.CellStyle = style; ICell cell2 = rowHeader.CreateCell(1); cell2.SetCellValue("编号"); cell2.CellStyle = style; ICell cell3 = rowHeader.CreateCell(2); cell3.SetCellValue("手机号码"); cell3.CellStyle = style; ICell cell4 = rowHeader.CreateCell(3); cell4.SetCellValue("第1题"); cell4.CellStyle = style; ICell cell5 = rowHeader.CreateCell(4); cell5.SetCellValue("第2题"); cell5.CellStyle = style; ICell cell6 = rowHeader.CreateCell(5); cell6.SetCellValue("第3题"); cell6.CellStyle = style; ICell cell7 = rowHeader.CreateCell(6); cell7.SetCellValue("第4题"); cell7.CellStyle = style; ICell cell8 = rowHeader.CreateCell(7); cell8.SetCellValue("第5题"); cell8.CellStyle = style; ICell cell9 = rowHeader.CreateCell(8); cell9.SetCellValue("第6题"); cell9.CellStyle = style; ICell cell10 = rowHeader.CreateCell(9); cell10.SetCellValue("第7题"); cell10.CellStyle = style; ICell cell11 = rowHeader.CreateCell(10); cell11.SetCellValue("第8题"); cell11.CellStyle = style; ICell cell12 = rowHeader.CreateCell(11); cell12.SetCellValue("第9题"); cell12.CellStyle = style; ICell cell13 = rowHeader.CreateCell(12); cell13.SetCellValue("第10题"); cell13.CellStyle = style; ICell cell14 = rowHeader.CreateCell(13); cell14.SetCellValue("第11题"); cell14.CellStyle = style; ICell cell15 = rowHeader.CreateCell(14); cell15.SetCellValue("第12题"); cell15.CellStyle = style; ICellStyle cellStyle = book.CreateCellStyle(); cellStyle.Alignment = HorizontalAlignment.CENTER; cellStyle.VerticalAlignment = VerticalAlignment.CENTER; IFont rowfont = book.CreateFont(); rowfont.FontName = "宋体"; rowfont.FontHeightInPoints = (short)11; rowfont.Color = color.GetIndex(); cellStyle.SetFont(rowfont); // NPOI.SS.UserModel.IRow row = null; for (int i = 0; i < list.Count; i++) { RecordModel model = list[i]; row = sheet.CreateRow(i + 1); ICell rowcell1 = row.CreateCell(0); rowcell1.SetCellValue(model.AnswerName); rowcell1.CellStyle = cellStyle; ICell rowcell2 = row.CreateCell(1); rowcell2.SetCellValue(model.AnswerNumber); rowcell2.CellStyle = cellStyle; ICell rowcell3 = row.CreateCell(2); rowcell3.SetCellValue(model.AnswerPhone); rowcell3.CellStyle = cellStyle; ICell rowcell4 = row.CreateCell(3); rowcell4.SetCellValue(model.AnswerAnswer1); rowcell4.CellStyle = cellStyle; ICell rowcell5 = row.CreateCell(4); rowcell5.SetCellValue(model.AnswerAnswer2); rowcell5.CellStyle = cellStyle; ICell rowcell6 = row.CreateCell(5); rowcell6.SetCellValue(model.AnswerAnswer3); rowcell6.CellStyle = cellStyle; ICell rowcell7 = row.CreateCell(6); rowcell7.SetCellValue(model.AnswerAnswer4); rowcell7.CellStyle = cellStyle; ICell rowcell8 = row.CreateCell(7); rowcell8.SetCellValue(model.AnswerAnswer5); rowcell8.CellStyle = cellStyle; ICell rowcell9 = row.CreateCell(8); rowcell9.SetCellValue(model.AnswerAnswer6); rowcell9.CellStyle = cellStyle; ICell rowcell10 = row.CreateCell(9); rowcell10.SetCellValue(model.AnswerAnswer7); rowcell10.CellStyle = cellStyle; ICell rowcell11 = row.CreateCell(10); rowcell11.SetCellValue(model.AnswerAnswer8); rowcell11.CellStyle = cellStyle; ICell rowcell12 = row.CreateCell(11); rowcell12.SetCellValue(model.AnswerAnswer9); rowcell12.CellStyle = cellStyle; ICell rowcell13 = row.CreateCell(12); rowcell13.SetCellValue(model.AnswerAnswer10); rowcell13.CellStyle = cellStyle; ICell rowcell14 = row.CreateCell(13); rowcell14.SetCellValue(model.AnswerAnswer11); rowcell14.CellStyle = cellStyle; ICell rowcell15 = row.CreateCell(14); rowcell15.SetCellValue(model.AnswerAnswer12); rowcell15.CellStyle = cellStyle; } // using (System.IO.MemoryStream ms = new System.IO.MemoryStream()) { book.Write(ms); //string path = @"C:\Users\DNS\Desktop\导出参会人"; //string path = System.Environment.GetFolderPath(Environment.SpecialFolder.DesktopDirectory) + "\\数据导出Excel"; // path = System.Environment.GetFolderPath(Environment.SpecialFolder.Desktop) + "\\数据导出Excel"; string path = AppDomain.CurrentDomain.SetupInformation.ApplicationBase + "Data\\"; DirectoryInfo excelDir = new DirectoryInfo(path); //如果路径不存在就创建 if (!Directory.Exists(path)) { Directory.CreateDirectory(path); } string fileName = "mk_q" + "_" + DateTime.Now.ToString("yyyyMMddHHmmss") + ".xls"; //string strDate = DateTime.Now.ToString("yyyy-MM-dd-HH "); path = excelDir.FullName + fileName; if (File.Exists(path)) { File.Delete(path); } using (Stream localFile = new FileStream(path, FileMode.OpenOrCreate, FileAccess.ReadWrite)) { //ms.ToArray()转换为字节数组就是想要的图片源字节 localFile.Write(ms.ToArray(), 0, (int)ms.Length); } book = null; ms.Close(); ms.Dispose(); // string webAddress = System.Configuration.ConfigurationManager.AppSettings["mkDataPath"].ToString(); return(webAddress + fileName); } } catch (Exception) { //Page.ClientScript.RegisterStartupScript(Page.GetType(), "message", "<script language='javascript' defer>alert('导出格式错误');</script>"); return("Error1"); } } else { return("Error2"); } }
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 FileResult OrderToExecl() { //取查询条件 var orderNo = Request.Get("OrderNo"); var checkOrderNo = string.IsNullOrWhiteSpace(orderNo); var consignee = Request.Get("Consignee"); var checkConsignee = string.IsNullOrWhiteSpace(consignee); var memberName = Request.Get("MemberName"); var checkMemberName = string.IsNullOrWhiteSpace(memberName); var orderStatus = Request.Get("OrderStatus"); var checkOrderStatus = string.IsNullOrWhiteSpace(orderStatus); var orderStatusInt = orderStatus.To <int>(); var refundStatus = Request.Get("RefundStatus"); var checkRefundStatus = string.IsNullOrWhiteSpace(refundStatus); var refundStatusInt = refundStatus.To <int>(); var payStatus = Request.Get("PayStatus"); var checkPayStatus = string.IsNullOrWhiteSpace(payStatus); var payStatusInt = payStatus.To <int>(); var shippingStatus = Request.Get("ShippingStatus"); var checkShippingStatus = string.IsNullOrWhiteSpace(shippingStatus); var shippingStatusInt = shippingStatus.To <int>(); var paymentId = Request.Get("PaymentId"); var checkPaymentId = string.IsNullOrWhiteSpace(paymentId); var createTimeBegin = Request.Get("CreateTimeBegin"); var checkCreateTimeBegin = string.IsNullOrWhiteSpace(createTimeBegin); var createTimeBeginTime = createTimeBegin.To <DateTime>(); var createTimeEnd = Request.Get("CreateTimeEnd"); var checkCreateTimeEnd = string.IsNullOrWhiteSpace(createTimeEnd); var createTimeEndTime = createTimeEnd.To <DateTime>(); Expression <Func <Order, bool> > expression = l => (checkOrderNo || l.OrderNo.Contains(orderNo)) && (checkConsignee || l.Consignee.Contains(consignee)) && l.OrderStatus != OrderStatus.Deleted && (checkOrderStatus || (int)l.OrderStatus == orderStatusInt) && (checkRefundStatus || (int)l.RefundStatus == refundStatusInt) && (checkPayStatus || (int)l.PayStatus == payStatusInt) && (checkShippingStatus || (int)l.ShippingStatus == shippingStatusInt) && (checkCreateTimeBegin || l.CreateTime >= createTimeBeginTime) && (checkCreateTimeEnd || l.CreateTime <= createTimeEndTime) && (checkPaymentId || l.PaymentId.ToString() == paymentId) && (checkMemberName || l.MemberName.Contains(memberName)); var list = _orderService.GetList(expression).Select(o => new ViewModels.SimpleOderModel(o)); //创建Excel文件的对象 NPOI.HSSF.UserModel.HSSFWorkbook book = new NPOI.HSSF.UserModel.HSSFWorkbook(); //添加一个sheet NPOI.SS.UserModel.ISheet sheet1 = book.CreateSheet("Sheet1"); if (list.Any()) { //给sheet1添加第一行的头部标题 NPOI.SS.UserModel.IRow row1 = sheet1.CreateRow(0); row1.CreateCell(0).SetCellValue("订单号"); row1.CreateCell(1).SetCellValue("商品名称"); row1.CreateCell(2).SetCellValue("规格"); row1.CreateCell(3).SetCellValue("数量"); row1.CreateCell(4).SetCellValue("下单时间"); row1.CreateCell(5).SetCellValue("收货人"); row1.CreateCell(6).SetCellValue("收货地址"); row1.CreateCell(7).SetCellValue("联系电话"); row1.CreateCell(8).SetCellValue("商品总价"); row1.CreateCell(9).SetCellValue("物流费用"); row1.CreateCell(10).SetCellValue("积分折抵"); row1.CreateCell(11).SetCellValue("应付金额"); row1.CreateCell(12).SetCellValue("订单状态"); var i = 0; var cs = book.CreateCellStyle(); cs.WrapText = true; foreach (var item in list) { string n = ""; string m = ""; string k = ""; for (int j = 0; j < item.OrderGoods.Count; j++) { if (j == item.OrderGoods.Count - 1) { n += item.OrderGoods[j].GoodsName; m += item.OrderGoods[j].GoodsAttribute; k += item.OrderGoods[j].Quantity + item.OrderGoods[j].Unit; } else { n += item.OrderGoods[j].GoodsName + "\n"; m += item.OrderGoods[j].GoodsAttribute + "\n"; k += item.OrderGoods[j].Quantity + item.OrderGoods[j].Unit + "\n"; } } NPOI.SS.UserModel.IRow rowtemp = sheet1.CreateRow(i + 1); rowtemp.CreateCell(0).SetCellValue(item.OrderNo); rowtemp.CreateCell(1).SetCellValue(n); rowtemp.CreateCell(2).SetCellValue(m); rowtemp.CreateCell(3).SetCellValue(k); if (item.OrderGoods.Count > 1) { rowtemp.GetCell(1).CellStyle = cs; rowtemp.GetCell(2).CellStyle = cs; rowtemp.GetCell(3).CellStyle = cs; } rowtemp.CreateCell(4).SetCellValue(string.Format("{0:yyyy-MM-dd HH:mm:ss}", item.CreateTime)); rowtemp.CreateCell(5).SetCellValue(item.Consignee); rowtemp.CreateCell(6).SetCellValue(item.RegionName + item.Address); rowtemp.CreateCell(7).SetCellValue(item.Tel); rowtemp.CreateCell(8).SetCellValue(item.GoodsAmount.ToString("#0.00")); rowtemp.CreateCell(9).SetCellValue(item.ShippingFee.ToString("#0.00")); rowtemp.CreateCell(10).SetCellValue(item.IntegralMoney.ToString("#0.00")); rowtemp.CreateCell(11).SetCellValue(item.PayFee.ToString("#0.00")); var statusName = item.OrderStatus.Description(); if (item.RefundStatus > 0) { statusName += "(" + item.RefundStatus.Description() + ")"; } if (item.EvaluateStatus > 0) { statusName += "(" + item.EvaluateStatus.Description() + ")"; } rowtemp.CreateCell(12).SetCellValue(statusName); i++; } } // 写入到客户端 var ms = new MemoryStream(); book.Write(ms); ms.Seek(0, SeekOrigin.Begin); var dt = DateTime.Now; var dateTime = dt.ToString("yyMMddHHmmssfff"); var fileName = "订单列表" + dateTime + ".xls"; return(File(ms, "application/vnd.ms-excel", fileName)); }
public FileResult ExportList(AdminCredential User, int eid, int v) { var view = SystemSetService.View.GetViewItem(v).Data; NPOI.HSSF.UserModel.HSSFWorkbook workbook = new NPOI.HSSF.UserModel.HSSFWorkbook(); NPOI.SS.UserModel.ISheet sheet1 = workbook.CreateSheet(view.Title); NPOI.SS.UserModel.IRow row1 = sheet1.CreateRow(0); row1.Height = 430; ICellStyle style = workbook.CreateCellStyle(); // style.FillForegroundColor = (short)24;// NPOI.HSSF.Util.HSSFColor.LightGreen.Index; // style.FillPattern = FillPattern.SolidForeground; //style.BorderTop = BorderStyle.Thin; //style.BorderLeft = BorderStyle.Thin; //style.BorderRight = BorderStyle.Thin; //style.BorderBottom = BorderStyle.Thin; style.Alignment = HorizontalAlignment.Center; style.VerticalAlignment = VerticalAlignment.Center; // IFont font = workbook.CreateFont(); //创建一个字体样式对象 // font.FontHeightInPoints = 10;//字体大小 // font.FontName = "宋体"; //和excel里面的字体对应 // font.Boldweight = short.MaxValue;//字体加粗 // font.Color = NPOI.HSSF.Util.HSSFColor.White.Index; // style.SetFont(font); //将字体样式赋给样式对象 var FieldList = JsonConvert.DeserializeObject <List <ViewFieldModel> >(view.FieldList); int i = 0; foreach (var item in FieldList) { ICell cell = row1.CreateCell(i); cell.SetCellValue(item.Title); cell.CellStyle = style; i++; } style.Alignment = HorizontalAlignment.Left; DataTable dt = SystemSetService.View.GetViewExportData(view.Sql, new Pagination() { Page = 1, PageSize = 999999, vid = v, eid = eid }, User); //将数据逐步写入sheet1各个行 for (int z = 0; z < dt.Rows.Count; z++) { var row = dt.Rows[z]; NPOI.SS.UserModel.IRow rowtemp = sheet1.CreateRow(z + 1); int c = 0; foreach (var item in FieldList) { var _value = row[item.Field].ToString(); //if (ConvertHelper.IsDateTime(_value)) //{ // if (ConvertHelper.ToDouble(_value, 0) == 0) // { // _value = ConvertHelper.ToDateTime(_value).ToString("yyyy-MM-dd HH:mm:ss"); // } //} var cell = rowtemp.CreateCell(c++); cell.CellStyle = style; cell.SetCellValue(_value); } } // 写入到客户端 System.IO.MemoryStream ms = new System.IO.MemoryStream(); workbook.Write(ms); ms.Seek(0, SeekOrigin.Begin); return(File(ms, "application/vnd.ms-excel", view.Title + ".xls")); }
static public int Deport(System.Windows.Forms.DataGridView mView, int startIndex) { try { IWorkbook mWorkbook = new NPOI.HSSF.UserModel.HSSFWorkbook(); ISheet mSheet = mWorkbook.CreateSheet("Sheet1"); ICellStyle styleRight = mWorkbook.CreateCellStyle(); //ICellStyle NumberStyle = mWorkbook.CreateCellStyle(); //IDataFormat format = mWorkbook.CreateDataFormat(); styleRight.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center; styleRight.VerticalAlignment = NPOI.SS.UserModel.VerticalAlignment.Center; //NumberStyle.DataFormat = format.GetFormat("0.00"); ICell mCell; int i, j; int x = 0; //DataGridView mView = dgvReport; //添加标头 IRow mRow = mSheet.CreateRow(0); for (i = startIndex; i <= mView.Columns.Count - 1; i++) { if (mView.Columns[i].Visible == true) { mCell = mRow.CreateCell(x); x++; mCell.SetCellValue(mView.Columns[i].HeaderText); mCell.CellStyle = styleRight; } } //添加内容 for (i = 1; i <= mView.RowCount; i++) { mRow = mSheet.CreateRow(i); x = 0; for (j = startIndex; j <= mView.Columns.Count - 1; j++) { if (mView.Columns[j].Visible == true) { mCell = mRow.CreateCell(x); x++; if (Classes.PubFunc.IsNumber(Convert.ToString(mView.Rows[i - 1].Cells[j].Value)) == true) { mCell.SetCellValue(Convert.ToDouble(mView.Rows[i - 1].Cells[j].Value)); } else { mCell.SetCellValue(Convert.ToString(mView.Rows[i - 1].Cells[j].Value)); } mCell.CellStyle = styleRight; } } } //创建文件 SaveFileDialog mDialog = new SaveFileDialog(); mDialog.AddExtension = true; mDialog.DefaultExt = "xls"; mDialog.Filter = "Excel Worksheets(*.xls)|*.xls"; mDialog.InitialDirectory = System.Environment.CurrentDirectory; mDialog.FileName = "导出数据-" + DateTime.Now.ToString("yyyyMMddhhmmss"); if (mDialog.ShowDialog() == DialogResult.OK) { Stream mFile = mDialog.OpenFile(); mWorkbook.Write(mFile); mFile.Close(); MessageBox.Show("保存成功!", "提示"); } } catch (Exception ex) { MessageBox.Show(ex.Message.ToString()); } return(0); }
static public int InvoiceDeport(System.Windows.Forms.DataGridView mView) { try { IWorkbook mWorkbook = new NPOI.HSSF.UserModel.HSSFWorkbook(); ISheet mSheet = mWorkbook.CreateSheet("Sheet1"); ICellStyle styleRight = mWorkbook.CreateCellStyle(); //ICellStyle NumberStyle = mWorkbook.CreateCellStyle(); //IDataFormat format = mWorkbook.CreateDataFormat(); styleRight.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center; styleRight.VerticalAlignment = NPOI.SS.UserModel.VerticalAlignment.Center; styleRight.WrapText = true; ICell mCell; int i; //添加标头 IRow mRow = mSheet.CreateRow(0); int m = 0; mCell = mRow.CreateCell(m++); mCell.SetCellValue("公司名称"); mSheet.SetDefaultColumnStyle(m, styleRight);//设置样式 mCell = mRow.CreateCell(m++); mCell.SetCellValue("总金额"); mSheet.SetDefaultColumnStyle(m, styleRight); mCell = mRow.CreateCell(m++); mCell.SetCellValue("发票类型"); mSheet.SetDefaultColumnStyle(m, styleRight); mCell = mRow.CreateCell(m++); mCell.SetCellValue("开票日期"); mSheet.SetDefaultColumnStyle(m, styleRight); mCell = mRow.CreateCell(m++); mCell.SetCellValue("商品名称"); mSheet.SetDefaultColumnStyle(m, styleRight); mCell = mRow.CreateCell(m++); mCell.SetCellValue("单价"); mSheet.SetDefaultColumnStyle(m, styleRight); mCell = mRow.CreateCell(m++); mCell.SetCellValue("数量"); mSheet.SetDefaultColumnStyle(m, styleRight); mCell = mRow.CreateCell(m++); mCell.SetCellValue("金额"); mSheet.SetDefaultColumnStyle(m, styleRight); //添加内容 int mExcelRowCount = 1; for (i = 0; i <= mView.RowCount - 1; i++) { if (Convert.ToBoolean(mView.Rows[i].Cells["ColCombine"].Value) == true) { mRow = mSheet.CreateRow(mExcelRowCount++); string InvoiceContent = string.Empty; //公司名称 mCell = mRow.CreateCell(0); mCell.SetCellValue(Convert.ToString(mView.Rows[i].Cells["customerNameDataGridViewTextBoxColumn"].Value)); //总金额 mCell = mRow.CreateCell(1); mCell.SetCellValue(Convert.ToString(mView.Rows[i].Cells["invoiceMoneyDataGridViewTextBoxColumn"].Value)); //发票类型 mCell = mRow.CreateCell(2); mCell.SetCellValue(Convert.ToString(mView.Rows[i].Cells["invoiceTypeDataGridViewTextBoxColumn"].Value)); //开票日期 mCell = mRow.CreateCell(3); mCell.SetCellValue(Convert.ToString(mView.Rows[i].Cells["invoiceDateDataGridViewTextBoxColumn"].Value)); //开票内容 InvoiceContent = Convert.ToString(mView.Rows[i].Cells["invoiceContentDataGridViewTextBoxColumn"].Value); string[] contents = System.Text.RegularExpressions.Regex.Split(InvoiceContent, "!#!"); for (int n = 0; n < contents.Length - 3; n += 4) { mCell = mRow.CreateCell(4); mCell.SetCellValue(contents[n]); mCell = mRow.CreateCell(5); mCell.SetCellValue(contents[n + 1]); mCell = mRow.CreateCell(6); mCell.SetCellValue(contents[n + 2]); mCell = mRow.CreateCell(7); mCell.SetCellValue(contents[n + 3]); if (n < contents.Length - 7) { mRow = mSheet.CreateRow(mExcelRowCount++); } } mSheet.CreateRow(mExcelRowCount++); } } //创建文件 SaveFileDialog mDialog = new SaveFileDialog(); mDialog.AddExtension = true; mDialog.DefaultExt = "xls"; mDialog.Filter = "Excel Worksheets(*.xls)|*.xls"; mDialog.InitialDirectory = System.Environment.CurrentDirectory; mDialog.FileName = "开票-" + DateTime.Now.ToString("yyyyMMddhhmmss"); if (mDialog.ShowDialog() == DialogResult.OK) { Stream mFile = mDialog.OpenFile(); mWorkbook.Write(mFile); mFile.Close(); MessageBox.Show("保存成功!", "提示"); } } catch (Exception ex) { MessageBox.Show(ex.Message.ToString()); } return(0); }
public /*ActionResult*/ FileContentResult About() { NPOI.HSSF.UserModel.HSSFWorkbook book = new NPOI.HSSF.UserModel.HSSFWorkbook(); NPOI.SS.UserModel.ISheet sheet = book.CreateSheet(); #region 定义表头 //RGB自定义背景色 HSSFPalette palette = book.GetCustomPalette(); palette.SetColorAtIndex(HSSFColor.Pink.Index, (byte)54, (byte)96, (byte)146); HSSFCellStyle cellStyle = (HSSFCellStyle)book.CreateCellStyle(); ///(自定义背景色)单元格背景颜色 和FillPattern必须一起设置 cellStyle.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.Pink.Index; cellStyle.FillPattern = FillPattern.SolidForeground; cellStyle.Alignment = HorizontalAlignment.Center; //水平居中 cellStyle.VerticalAlignment = VerticalAlignment.Center; //垂直居中 //设置黑色边框 cellStyle.BorderBottom = BorderStyle.Thin; cellStyle.BorderLeft = BorderStyle.Thin; cellStyle.BorderRight = BorderStyle.Thin; cellStyle.BorderTop = BorderStyle.Thin; //创建字体 var cellStyleFont = (HSSFFont)book.CreateFont(); cellStyleFont.Color = HSSFColor.White.Index; cellStyleFont.IsBold = true; cellStyleFont.FontName = "宋体"; cellStyleFont.FontHeightInPoints = 11; cellStyle.SetFont(cellStyleFont); //设置单元格宽度 sheet.SetColumnWidth(0, 4000); //核销人 sheet.SetColumnWidth(1, 4000); //账号 sheet.SetColumnWidth(2, 5000); //核销时间 sheet.SetColumnWidth(3, 3500); sheet.SetColumnWidth(4, 3500); sheet.SetColumnWidth(5, 4000); sheet.SetColumnWidth(6, 7000); sheet.SetColumnWidth(7, 3500); //全面值 sheet.SetColumnWidth(8, 4500); //使用门槛 sheet.SetColumnWidth(9, 5000); //核销门店 sheet.SetColumnWidth(10, 10000); //appid sheet.SetColumnWidth(11, 3500); //核销地址 sheet.SetColumnWidth(12, 3500); //核销id // 第一行 NPOI.SS.UserModel.IRow row = sheet.CreateRow(0); row.Height = 350; row.CreateCell(0).SetCellValue("核销人"); row.CreateCell(1).SetCellValue("核销人账号"); row.CreateCell(2).SetCellValue("核销时间"); row.CreateCell(3).SetCellValue("订单金额"); row.CreateCell(4).SetCellValue("核销金额"); row.CreateCell(5).SetCellValue("核销券码"); row.CreateCell(6).SetCellValue("有效期"); row.CreateCell(7).SetCellValue("券面值"); row.CreateCell(8).SetCellValue("使用门槛"); row.CreateCell(9).SetCellValue("核销门店"); row.CreateCell(10).SetCellValue("核销门店APPID"); row.CreateCell(11).SetCellValue("核销地址"); row.CreateCell(12).SetCellValue("核销ID"); for (int i = 0; i < 13; i++) { //设置高度 row.Height = 400; row.Cells[i].CellStyle = cellStyle; } #endregion //合并单元格 /** * 第一个参数:从第几行开始合并 * 第二个参数:到第几行结束合并 * 第三个参数:从第几列开始合并 * 第四个参数:到第几列结束合并 **/ CellRangeAddress region = new CellRangeAddress(1, 2, 0, 0); sheet.AddMergedRegion(region); HSSFCellStyle cellStyleItem = (HSSFCellStyle)book.CreateCellStyle(); cellStyleItem.Alignment = HorizontalAlignment.Center; //水平居中 cellStyleItem.VerticalAlignment = VerticalAlignment.Center; //垂直居中 HSSFCellStyle cellStyleItem2 = (HSSFCellStyle)book.CreateCellStyle(); cellStyleItem2.VerticalAlignment = VerticalAlignment.Center; //垂直居中 for (int i = 1; i < 100; i++) { NPOI.SS.UserModel.IRow row2 = sheet.CreateRow(i); row2.CreateCell(0).SetCellValue("收银员小吴"); row2.CreateCell(1).SetCellValue("13587639864"); row2.CreateCell(2).SetCellValue(DateTime.Now.ToString("yyyy/MM/dd HH:mm")); row2.CreateCell(3).SetCellValue("1000"); row2.CreateCell(4).SetCellValue("180"); row2.CreateCell(5).SetCellValue("DJD84K6JFU"); row2.CreateCell(6).SetCellValue($"{DateTime.Now.ToString("yyyy-MM-dd")} ~ {DateTime.Now.AddDays(1).ToString("yyyy-MM-dd")}"); row2.CreateCell(7).SetCellValue("60"); row2.CreateCell(8).SetCellValue("满100元可用"); row2.CreateCell(9).SetCellValue("世纪新园中餐厅"); row2.CreateCell(10).SetCellValue(System.Guid.NewGuid().ToString()); var ignoreList = new List <int> { 5, 8, 9, 10 }; for (int j = 0; j < 11; j++) { if (ignoreList.Contains(j)) { continue; } row2.Cells[j].CellStyle = cellStyleItem; } } // 写入到客户端 System.IO.MemoryStream ms = new System.IO.MemoryStream(); book.Write(ms); //Response.ContentType = "application/octet-stream"; //Response.AppendHeader("content-disposition", $"attachment;filename={DateTime.Now.ToString("yyyyMMddHHmmssfff")}.xls;"); //Response.BinaryWrite(ms.ToArray()); //book = null; //ms.Close(); //ms.Dispose(); //return null; return(File(ms.ToArray(), "application/octet-stream", string.Format("冷链食品处理详情{0}.xls", DateTime.Now.ToString("yyyyMMddHHmmss")))); }
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> /// 根据模板创建新的文件 /// </summary> /// <param name="lst"></param> private static void CreatReport(List <Model> lst) { #region 加载xls文件 //模板文件路径 string path = @"C:\Users\1\Desktop\日报\实收款项明细表.xls"; FileStream fs_modle; using (fs_modle = new FileStream(path, FileMode.Open, FileAccess.ReadWrite)) { IWorkbook workbook_model = new NPOI.HSSF.UserModel.HSSFWorkbook(fs_modle); // fs_modle.Close(); ISheet sheet_model = workbook_model.GetSheetAt(0); IRow row_hj = sheet_model.GetRow(7);//合计行 //设置单元格时 ICellStyle style = workbook_model.CreateCellStyle(); style.BorderTop = BorderStyle.Thin; style.BorderBottom = BorderStyle.Thin; style.BorderLeft = BorderStyle.Thin; style.BorderRight = BorderStyle.Thin; style.WrapText = true; style.DataFormat = 4; style.VerticalAlignment = VerticalAlignment.Center; IRow r; for (int i = 6; i < lst.Count + 6; i++) { r = sheet_model.CreateRow(i); //创建一新行 r.HeightInPoints = 25; //设置行高 for (int J = 0; J < 14; J++) { switch (J) { case 0: r.CreateCell(J).SetCellValue(lst[i - 6].序号); break; case 1: r.CreateCell(J).SetCellValue(lst[i - 6].项目名称); break; case 2: r.CreateCell(J).SetCellValue(lst[i - 6].楼栋名称); break; case 3: r.CreateCell(J).SetCellValue(lst[i - 6].房号); break; case 4: r.CreateCell(J).SetCellValue(lst[i - 6].客户名称); break; case 5: r.CreateCell(J).SetCellValue(lst[i - 6].收款日期.ToString()); break; case 6: r.CreateCell(J).SetCellValue(lst[i - 6].票据类型); break; case 7: r.CreateCell(J).SetCellValue(lst[i - 6].票据编号); break; case 8: r.CreateCell(J).SetCellValue(lst[i - 6].款项类型); break; case 9: r.CreateCell(J).SetCellValue(lst[i - 6].款项名称); break; case 10: r.CreateCell(J).SetCellValue(lst[i - 6].金额); break; case 11: r.CreateCell(J).SetCellValue(lst[i - 6].支付方式); break; case 12: r.CreateCell(J).SetCellValue(lst[i - 6].银付方式); break; case 13: r.CreateCell(J).SetCellValue(lst[i - 6].摘要); break; default: break; } } var enu = r.GetEnumerator(); while (enu.MoveNext()) { enu.Current.CellStyle = style;//设置风格 } } //创建合计行 IRow r1 = sheet_model.CreateRow(lst.Count + 6); r1.HeightInPoints = 25;//设置行高 ICell cell; IFont font = workbook_model.CreateFont(); font.FontName = "宋体"; font.FontHeightInPoints = 10; for (int i = 0; i < 14; i++) { cell = r1.CreateCell(i); cell.CellStyle = style; cell.SetCellValue("--"); cell.CellStyle.Alignment = HorizontalAlignment.Center; cell.CellStyle.SetFont(font); } double JE = lst.Sum(t => t.金额); r1.GetCell(0).SetCellValue(" "); r1.GetCell(1).SetCellValue("合计"); r1.GetCell(10).SetCellValue(JE); //统计周期和统计时间 string ZQ = $"统计周期:{DateTime.Now.ToShortDateString()} 至 {DateTime.Now.ToShortDateString()}"; string DT = $"制表日期:{DateTime.Now.ToShortDateString()}"; sheet_model.GetRow(3).GetCell(0).SetCellValue(ZQ); sheet_model.GetRow(4).GetCell(0).SetCellValue(DT); string newFileFullPath = $@"{ Environment.GetFolderPath(Environment.SpecialFolder.DesktopDirectory)}"; string filePath = $"{newFileFullPath}\\日报\\日报表{DateTime.Now.ToShortDateString()}.xls"; try { using (FileStream fs = new FileStream(filePath, FileMode.OpenOrCreate, FileAccess.Write)) { workbook_model.Write(fs); } // fs.Dispose(); Console.WriteLine("文件创建成功!"); // workbook_model.Close(); lst.Clear(); } catch (Exception) { throw; } finally { Console.WriteLine(File.Exists(filePath)); Process.Start(@"C:\Program Files (x86)\Kingsoft\WPS Office\11.1.0.9912\office6\wps.exe", filePath); } } #endregion }