private void btnExcelExport_Click(object sender, EventArgs e) { ManagerInfoBll miBll = new ManagerInfoBll(); var list = miBll.GetList(); dataGridView1.DataSource = list; //创建Excel工作薄 XSSFWorkbook workbook = new XSSFWorkbook(); //创建单元格样式 ICellStyle cellTitleStyle = workbook.CreateCellStyle(); //设置单元格居中显示 cellTitleStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center; //创建字体 IFont font = workbook.CreateFont(); //设置字体加粗显示 font.IsBold = true; cellTitleStyle.SetFont(font); //创建Excel工作表 ISheet sheet = workbook.CreateSheet("管理员"); //创建Excel行 IRow row = sheet.CreateRow(0); //创建Excel单元格 NPOI.SS.UserModel.ICell cell = row.CreateCell(0); //设置单元格值 cell.SetCellValue("管理员管理"); //设置单元格合并 sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(0, 0, 0, 3)); cell.CellStyle = cellTitleStyle; for (int i = 0; i < list.Count; i++) { IRow rowDate = sheet.CreateRow(i + 1); Type t = list[i].GetType(); int count = 0; foreach (PropertyInfo pi in t.GetProperties()) { object value = pi.GetValue(list[i]); string name = pi.Name; NPOI.SS.UserModel.ICell cellDate = rowDate.CreateCell(count); if (i == 0) { cellDate.SetCellValue(name); } else { cellDate.SetCellValue(value.ToString()); } sheet.AutoSizeColumn(count); count++; } } using (FileStream fs = new FileStream(@"C:\Users\Saber\Desktop\Demo.xlsx", FileMode.OpenOrCreate)) { workbook.Write(fs); } }
/// <summary> /// 根据DataTable创建一个Excel的WorkBook并返回 /// </summary> /// <param name="dt">数据表</param> /// <returns>XSSFWorkbook对象</returns> public XSSFWorkbook CreateExcelByDataTable(DataTable dt) { //创建一个工作博 XSSFWorkbook wk = new XSSFWorkbook(); //创建以个Sheet ISheet tb = wk.CreateSheet("Sheet1"); //创建表头(在第0行) IRow row = tb.CreateRow(0); #region 根据Datable表头创建Excel表头 for (int i = 0; i < dt.Columns.Count; i++) { //创建单元格 NPOI.SS.UserModel.ICell cell = row.CreateCell(i); //cell.CellStyle.FillBackgroundColor = NPOI.HSSF.Util.HSSFColor.DarkRed.Index; //cell.CellStyle.FillForegroundColor= NPOI.HSSF.Util.HSSFColor.Yellow.Index; cell.SetCellValue(dt.Columns[i].ColumnName); tb.AutoSizeColumn(i);//自动调整宽度,貌似对中文支持不好 SetBorder(cell); } #endregion #region 根据DataTable内容创建Excel内容 for (int i = 0; i < dt.Rows.Count; i++) { IRow rows = tb.CreateRow(i + 1); for (int j = 0; j < dt.Columns.Count; j++) { NPOI.SS.UserModel.ICell cell = rows.CreateCell(j); cell.SetCellValue(dt.Rows[i][j].ToString()); SetBorder(cell); } } #endregion return wk; }
/// <summary> /// 赋值单元格 /// </summary> /// <param name="sheet"></param> /// <param name="list"></param> private static void SetPurchaseOrder(ISheet sheet, List <TemplateMode> list) { try { foreach (var item in list) { IRow row = null; ICell cell = null; row = sheet.GetRow(item.row); if (row == null) { row = sheet.CreateRow(item.row); } cell = row.GetCell(item.cell); if (cell == null) { cell = row.CreateCell(item.cell); } cell.SetCellValue(item.value); } } catch (Exception) { throw; } }
/// <summary> /// 根据DataTable创建一个Excel的WorkBook并返回 /// </summary> /// <param name="dt">数据表</param> /// <param name="columnName">表头</param> /// <param name="SheetName">表名称</param> /// <returns>XSSFWorkbook对象</returns> public XSSFWorkbook CreateExcelByDataTable(DataTable dt, string[] columnName, string SheetName = "Sheet1") { //创建一个工作博 XSSFWorkbook wk = new XSSFWorkbook(); //创建以个Sheet ISheet tb = wk.CreateSheet(SheetName); //创建表头(在第0行) IRow row = tb.CreateRow(0); #region 表头根据参数 for (int i = 0; i < columnName.Length; i++) { //创建单元格 NPOI.SS.UserModel.ICell cell = row.CreateCell(i); cell.SetCellValue(columnName[i]); cell.CellStyle.FillBackgroundColor = NPOI.HSSF.Util.HSSFColor.DarkBlue.Index; SetBorder(cell); } #endregion #region 根据DataTable内容创建Excel内容 for (int i = 0; i < dt.Rows.Count; i++) { IRow rows = tb.CreateRow(i); for (int j = 0; j < dt.Columns.Count; j++) { if (j >= columnName.Length) break; NPOI.SS.UserModel.ICell cell = rows.CreateCell(j); cell.SetCellValue(dt.Rows[i][j].ToString()); SetBorder(cell); } } #endregion return wk; }
void WriteExcel(ref NPOI.SS.UserModel.IWorkbook book, DataTable dt) { NPOI.SS.UserModel.ISheet sheet = book.CreateSheet("Sheet1"); // 添加表头 NPOI.SS.UserModel.IRow row = sheet.CreateRow(0); int index = 0; foreach (DataColumn item in dt.Columns) { NPOI.SS.UserModel.ICell cell = row.CreateCell(index); cell.SetCellType(NPOI.SS.UserModel.CellType.String); cell.SetCellValue(item.Caption); index++; } // 添加数据 for (int i = 0; i < dt.Rows.Count; i++) { index = 0; row = sheet.CreateRow(i + 1); foreach (DataColumn item in dt.Columns) { NPOI.SS.UserModel.ICell cell = row.CreateCell(index); cell.SetCellType(NPOI.SS.UserModel.CellType.String); cell.SetCellValue(dt.Rows[i][item].ToString()); index++; } } }
/// <summary> /// render datatable to workbook stream /// </summary> /// <param name="sourceTable">datatable</param> /// <returns>workbook stream</returns> public static Stream RenderDataTableToExcel(DataTable sourceTable) { HSSFWorkbook workbook = new HSSFWorkbook(); MemoryStream ms = new MemoryStream(); HSSFSheet sheet = (HSSFSheet)workbook.CreateSheet(); HSSFRow headerRow = (HSSFRow)sheet.CreateRow(0); //設定 Header Style HSSFCellStyle headerStyle = (HSSFCellStyle)workbook.CreateCellStyle(); headerStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center; headerStyle.VerticalAlignment = NPOI.SS.UserModel.VerticalAlignment.Center; headerStyle.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.LightBlue.Index; headerStyle.FillPattern = NPOI.SS.UserModel.FillPattern.SolidForeground; headerStyle.BorderTop = headerStyle.BorderLeft; headerStyle.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin; headerStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin; HSSFFont headerFont = (HSSFFont)workbook.CreateFont(); headerFont.Color = HSSFColor.White.Index; headerFont.Boldweight = 1; headerStyle.SetFont(headerFont); // handling header. for (int i = 0; i < sourceTable.Columns.Count; i++) { DataColumn column = sourceTable.Columns[i]; NPOI.SS.UserModel.ICell headerCell = headerRow.CreateCell(column.Ordinal); headerCell.SetCellValue(column.ColumnName); headerCell.CellStyle = headerStyle; } // handling value. int rowIndex = 1; foreach (DataRow row in sourceTable.Rows) { HSSFRow dataRow = (HSSFRow)sheet.CreateRow(rowIndex); foreach (DataColumn column in sourceTable.Columns) { dataRow.CreateCell(column.Ordinal).SetCellValue(row[column].ToString()); } rowIndex++; } workbook.Write(ms); ms.Flush(); ms.Position = 0; sheet = null; headerRow = null; workbook = null; return(ms); }
/// <summary> /// 读取模板,根据List(list(string))"创建一个Excel的WorkBook并返回 /// </summary> /// <param name="dt">数据表</param> /// <param name="columnName">表头</param> /// <param name="columnWidth">列宽度*256</param> /// <param name="ModUrl">模板地址</param> /// <param name="startRowIndex">起始行,首行=0,默认0</param> /// <param name="RemoveOtherRows">是否移除起始行后的数据,默认=是</param> /// <param name="SheetName">表名称</param> /// <returns>XSSFWorkbook对象</returns> public XSSFWorkbook CreateExcelByList_Mod(System.Collections.Generic.List <System.Collections.Generic.List <object> > dt, string[] columnName, int[] columnWidth, string ModUrl, int startRowIndex = 0, bool RemoveOtherRows = true, string SheetName = "Sheet1") { startRowIndex = startRowIndex >= 0 ? startRowIndex : 0; string ExcelModePath = NodeBase + ModUrl; if (!File.Exists(ExcelModePath)) { return(null); } //创建一个工作博 XSSFWorkbook wk = new XSSFWorkbook(); using (FileStream fileExcelMod = new FileStream(ExcelModePath, FileMode.Open, FileAccess.Read)) { wk = new XSSFWorkbook(fileExcelMod); fileExcelMod.Close(); } if (wk == null) { return(null); } //创建一个Sheet ISheet tb = wk.GetSheetAt(0); #region 移除起始行后的所有行 if (RemoveOtherRows && tb.LastRowNum > startRowIndex + 1) { for (int rmr = tb.LastRowNum - 1; rmr > startRowIndex; rmr--) { tb.ShiftRows(rmr, rmr + 1, -1); } } #endregion //设置单元格宽度 //创建表头(在第0行) #region 根据List<List<object>>内容创建Excel内容 for (int i = 0; i < dt.Count; i++) { IRow rows = tb.CreateRow(i + startRowIndex); for (int j = 0; j < dt[i].Count; j++) { NPOI.SS.UserModel.ICell cell = rows.CreateCell(j); cell.SetCellValue(dt[i][j].ToString()); SetBorder(cell); } } #endregion return(wk); }
private static void SetCell(ICell newCell, ICellStyle dateStyle, Type dataType, string drValue) { switch (dataType.ToString()) { case "System.String": //字符串类型 newCell.SetCellValue(drValue); break; case "System.DateTime": //日期类型 System.DateTime dateV; if (System.DateTime.TryParse(drValue, out dateV)) { newCell.SetCellValue(dateV); } else { newCell.SetCellValue(""); } newCell.CellStyle = dateStyle; //格式化显示 break; case "System.Boolean": //布尔型 bool boolV = false; bool.TryParse(drValue, out boolV); newCell.SetCellValue(boolV); break; case "System.Int16": //整型 case "System.Int32": case "System.Int64": case "System.Byte": int intV = 0; int.TryParse(drValue, out intV); newCell.SetCellValue(intV); break; case "System.Decimal": //浮点型 case "System.Double": double doubV = 0; double.TryParse(drValue, out doubV); newCell.SetCellValue(doubV); break; case "System.DBNull": //空值处理 newCell.SetCellValue(""); break; default: newCell.SetCellValue(""); break; } }
/// <summary> /// Creates a cell, gives it a value, and applies a style if provided /// </summary> /// <param name="row">the row to Create the cell in</param> /// <param name="column">the column index to Create the cell in</param> /// <param name="value">The value of the cell</param> /// <param name="style">If the style is not null, then Set</param> /// <returns>A new HSSFCell</returns> public static NPOI.SS.UserModel.ICell CreateCell(NPOI.SS.UserModel.IRow row, int column, String value, HSSFCellStyle style) { NPOI.SS.UserModel.ICell cell = GetCell(row, column); cell.SetCellValue(new HSSFRichTextString(value)); if (style != null) { cell.CellStyle = (style); } return(cell); }
private static void CopyCell(NPOI.SS.UserModel.ICell sCell, NPOI.SS.UserModel.ICell dCell, List <ICellStyle> dCellStyles, List <IFont> dFonts) { ICellStyle currCellStyle = dCell.Sheet.Workbook.FindStyle(sCell.Sheet.Workbook, sCell.CellStyle, dCellStyles, dFonts); if (currCellStyle == null) { currCellStyle = dCell.Sheet.Workbook.CreateCellStyle().CopyStyle(sCell.CellStyle, dCell.Sheet.Workbook, sCell.Sheet.Workbook, dCellStyles, dFonts); } dCell.CellStyle = currCellStyle; switch (sCell.CellType) { case CellType.String: dCell.SetCellValue(sCell.StringCellValue); break; case CellType.Numeric: dCell.SetCellValue(sCell.NumericCellValue); break; case CellType.Blank: dCell.SetCellType(CellType.Blank); break; case CellType.Boolean: dCell.SetCellValue(sCell.BooleanCellValue); break; case CellType.Error: dCell.SetCellValue(sCell.ErrorCellValue); break; case CellType.Formula: dCell.SetCellValue(sCell.CellFormula); break; default: break; } }
//protected void Button1_Click(object sender, EventArgs e) //{ // using (MemoryStream ms = Export()) // { // Response.ContentType = "application/vnd.ms-word"; // Response.ContentEncoding = Encoding.UTF8; // Response.Charset = ""; // Response.AppendHeader("Content-Disposition", "attachment;filename=" + HttpUtility.UrlEncode("123.doc", Encoding.UTF8)); // Response.BinaryWrite(Export().GetBuffer()); // Response.End(); // } //} //private void ExportExcel(string fileName) //{ // //byte[] byteArray; // //if (Request.Browser.Browser == "IE") // // eFilePath = HttpUtility.UrlEncode(eFilePath); // //using (FileStream fs = new FileStream(eFilePath, FileMode.Open)) // //{ // // byteArray = new byte[fs.Length]; // // fs.Read(byteArray, 0, byteArray.Length); // //} // //Response.Buffer = false; // //Response.Clear(); // //Response.ContentType = "application/vnd.openxmlformats-officedocument.wordprocessingml.document; name=" + eFilePath; // //Response.AddHeader("content-disposition", "attachment;filename=" + fileName + "_" + DateTime.Now.ToString("yyyyMMdd_HHmmss") + ".xlsx"); // //Response.BinaryWrite(byteArray); // //Response.End(); //} public static MemoryStream ExportExcel(string templateFileName) { string filePath = templateFileName; XSSFWorkbook workBook = null; XSSFSheet sheet1 = null; using (FileStream fs = File.OpenRead(filePath)) { workBook = new XSSFWorkbook(fs); sheet1 = (XSSFSheet)workBook.GetSheet("Sheet1"); //添加或修改WorkSheet里的数据 System.Data.DataTable dt = new System.Data.DataTable(); //dt = DbHelperMySQLnew.Query("select * from t_jb_info where id='" + id + "'").Tables[0]; //if (dt.Rows.Count > 0) //{ // if (!string.IsNullOrEmpty(dt.Rows[0]["blrq"].ToString())) // { //sheet.GetRow(2).GetCell(1).SetCellValue("56565"); //sheet.GetRow(2).GetCell(2).SetCellValue("hahaha"); //sheet.GetRow(2).GetCell(3).SetCellValue(DateTime.Now.ToString()); // } //} // 创建新增行 for (var i = 1; i <= 10; i++) { IRow row1 = sheet1.CreateRow(i); for (var j = 0; j < 10; j++) { //新建单元格 NPOI.SS.UserModel.ICell cell = row1.CreateCell(j); // 单元格赋值 cell.SetCellValue(""); } } sheet1.GetRow(1).GetCell(0).SetCellValue("56565"); sheet1.GetRow(1).GetCell(1).SetCellValue("hahaha"); sheet1.GetRow(1).GetCell(2).SetCellValue(DateTime.Now.ToString()); sheet1.ForceFormulaRecalculation = true; } using (MemoryStream ms = new MemoryStream()) { workBook.Write(ms); return(ms); } }
public static void DataTableToExcel(string sheetName, DataTable dt, ExcelExt excelExt, Stream outStream) { try { NPOI.SS.UserModel.IWorkbook book = null; if (excelExt == ExcelExt.Xls) { book = new NPOI.HSSF.UserModel.HSSFWorkbook(); } else { book = new NPOI.XSSF.UserModel.XSSFWorkbook(); } NPOI.SS.UserModel.ISheet sheet = book.CreateSheet(sheetName); // 添加表头 NPOI.SS.UserModel.IRow row = sheet.CreateRow(0); int index = 0; foreach (DataColumn item in dt.Columns) { NPOI.SS.UserModel.ICell cell = row.CreateCell(index); cell.SetCellType(NPOI.SS.UserModel.CellType.String); cell.SetCellValue(item.ColumnName); index++; } // 添加数据 int num = dt.Rows.Count; for (int i = 0; i < num; i++) { index = 0; row = sheet.CreateRow(i + 1); foreach (DataColumn item in dt.Columns) { NPOI.SS.UserModel.ICell cell = row.CreateCell(index); cell.SetCellType(NPOI.SS.UserModel.CellType.String); cell.SetCellValue(dt.Rows[i][item].ToString()); index++; } } book.Write(outStream); book = null; } catch (Exception ex) { Console.WriteLine(ex); } }
/// <summary> /// 作用:填写时间: /// 作者:汪建龙 /// 编写时间:2017年3月8日15:48:22 /// </summary> /// <param name="sheet"></param> private static void WriteTime(ref ISheet sheet) { IRow row = sheet.GetRow(0); if (row == null) { row = sheet.CreateRow(0); } NPOI.SS.UserModel.ICell cell = row.GetCell(3); if (cell == null) { cell = row.CreateCell(3); } cell.SetCellValue(DateTime.Now.ToString("yyyy-MM-dd")); }
/// <summary> /// 根据List(list(string))"创建一个Excel的WorkBook并返回 /// </summary> /// <param name="dt">数据表</param> /// <param name="columnName">表头</param> /// <param name="columnWidth">列宽度*256</param> /// <param name="SheetName">表名称</param> /// <returns>XSSFWorkbook对象</returns> public XSSFWorkbook CreateExcelByList(System.Collections.Generic.List <System.Collections.Generic.List <object> > dt, string[] columnName, int[] columnWidth, string SheetName = "Sheet1") { //创建一个工作博 XSSFWorkbook wk = new XSSFWorkbook(); //创建以个Sheet ISheet tb = wk.CreateSheet(SheetName); //设置单元格宽度 for (int i = 0; i < columnWidth.Length; i++) { tb.SetColumnWidth(i, columnWidth[i] * 256); } //创建表头(在第0行) IRow row = tb.CreateRow(0); #region 表头根据参数 for (int i = 0; i < columnName.Length; i++) { //创建单元格 NPOI.SS.UserModel.ICell cell = row.CreateCell(i); cell.SetCellValue(columnName[i]); //背景 //cell.CellStyle.FillForegroundColor = NPOI.XSSF.Util.XSSFColor.DarkTeal.Index; //cell.CellStyle.FillPattern = FillPattern.SolidForeground; //边框颜色 //cell.CellStyle.TopBorderColor = NPOI.XSSF.Util.XSSFColor.OliveGreen.Index; //cell.CellStyle.LeftBorderColor = NPOI.XSSF.Util.XSSFColor.OliveGreen.Index; //cell.CellStyle.BottomBorderColor = NPOI.XSSF.Util.XSSFColor.OliveGreen.Index; //cell.CellStyle.RightBorderColor = NPOI.XSSF.Util.XSSFColor.OliveGreen.Index; cell.CellStyle.IsLocked = true; SetBorder(cell); } #endregion #region 根据List<List<object>>内容创建Excel内容 for (int i = 0; i < dt.Count; i++) { IRow rows = tb.CreateRow(i + 1); for (int j = 0; j < dt[i].Count; j++) { NPOI.SS.UserModel.ICell cell = rows.CreateCell(j); cell.SetCellValue(dt[i][j].ToString()); SetBorder(cell); } } #endregion return(wk); }
/// <summary> /// 將多個DataTable寫入ExcelFile並以Stream輸出. /// </summary> /// <param name="templateFileName">範本檔檔名</param> /// <param name="sourceTables">source table.</param> /// <param name="startRows">指定由範本檔第幾列開始附加資料</param> /// <returns></returns> public static Stream RenderDataTableToExcelSheet(string templateFileName, DataTable[] sourceTables, int[] startRows) { HSSFWorkbook workbook = FileToWorkBook(templateFileName);; MemoryStream ms = new MemoryStream();; HSSFSheet sheet = null; for (int idx = 0; idx < sourceTables.Length; idx++) { sheet = (HSSFSheet)workbook.GetSheetAt(idx); DataTable sourceTable = sourceTables[idx]; HSSFCellStyle cellStyle = (HSSFCellStyle)workbook.CreateCellStyle(); cellStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Left; cellStyle.VerticalAlignment = NPOI.SS.UserModel.VerticalAlignment.Center; cellStyle.BorderTop = cellStyle.BorderLeft; cellStyle.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin; cellStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin; cellStyle.WrapText = true; // handling value. int rowIndex = startRows[idx]; foreach (DataRow row in sourceTable.Rows) { HSSFRow dataRow = (HSSFRow)sheet.CreateRow(rowIndex); foreach (DataColumn column in sourceTable.Columns) { NPOI.SS.UserModel.ICell cell = dataRow.CreateCell(column.Ordinal); cell.SetCellValue(row[column].ToString()); cell.CellStyle = cellStyle; } rowIndex++; } sheet = null; } workbook.Write(ms); ms.Flush(); ms.Position = 0; workbook = null; return(ms); }
public void ExportExcel(string fileName, GridView dgv) { string saveFileName = DateTime.Now.ToString();; SaveFileDialog saveDialog = new SaveFileDialog(); saveDialog.DefaultExt = "xls"; saveDialog.Filter = "Excel文件|*.xls"; saveDialog.FileName = fileName; saveDialog.ShowDialog(); saveFileName = saveDialog.FileName; HSSFWorkbook workbook = new HSSFWorkbook(); MemoryStream ms = new MemoryStream(); NPOI.SS.UserModel.ISheet sheet = workbook.CreateSheet("Sheet1"); int rowCount = dgv.Rows.Count; int colCount = dgv.Columns.Count; for (int i = 0; i < rowCount; i++) { NPOI.SS.UserModel.IRow dataRow = sheet.CreateRow(i); for (int j = 0; j < colCount; j++) { if (dgv.Columns[j].Visible && dgv.Rows[i].Cells[j] != null) { NPOI.SS.UserModel.ICell cell = dataRow.CreateCell(j); cell.SetCellValue(dgv.Rows[i].Cells[j].ToString()); } } } workbook.Write(ms); FileStream file = new FileStream(saveFileName, FileMode.Create); workbook.Write(file); file.Close(); workbook = null; ms.Close(); ms.Dispose(); MessageBox.Show(fileName + " 保存成功", "提示", MessageBoxButtons.OK); }
/// <summary> /// 创建excel表头 /// </summary> /// <param name="dgv"></param> /// <param name="excelSheet"></param> protected static void CreateHeader(NPOI.SS.UserModel.ISheet excelSheet, HSSFWorkbook excelWorkbook) { int cellIndex = 0; NPOI.SS.UserModel.IRow row1 = excelSheet.CreateRow(0); NPOI.SS.UserModel.ICellStyle cellStyle = excelWorkbook.CreateCellStyle(); cellStyle.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.WHITE.index; //cellStyle.FillPattern = HSSFCellStyle.SQUARES; cellStyle.FillBackgroundColor = NPOI.HSSF.Util.HSSFColor.BLACK.index; //循环导出列 foreach (System.Collections.DictionaryEntry de in _listColumnsName) { NPOI.SS.UserModel.ICell cell = row1.CreateCell(cellIndex); cell.CellStyle = cellStyle; string cellName = de.Value.ToString().Trim(); cell.SetCellValue(cellName); excelSheet.SetColumnWidth(cellIndex, 15 * 256); cellIndex++; } }
private void button1_Click(object sender, EventArgs e) { IWorkbook xssfwb; string numer; string nrSprawy; using (FileStream file = new FileStream(sciezka + "ListaNumerow.xlsx", FileMode.Open, FileAccess.Read)) { xssfwb = new XSSFWorkbook(file); } ISheet sheet = xssfwb.GetSheet("Arkusz1"); for (int row = 1; row <= 5; row++) //sheet.LastRowNum; row++) { if (sheet.GetRow(row) != null) { // MessageBox.Show(string.Format("Row {0} = {1}", row, sheet.GetRow(row).GetCell(6).StringCellValue)); IRow wiersz = sheet.GetRow(row); numer = wiersz.GetCell(0).StringCellValue; nrSprawy = wiersz.GetCell(1).StringCellValue.Replace('/', '_'); ZapiszPNG(numer); zapiszPlikWord(nrSprawy, numer); NPOI.SS.UserModel.ICell cell = wiersz.GetCell(9) ?? wiersz.CreateCell(9, CellType.String); cell.SetCellValue("Zapisano"); } } using (FileStream file = new FileStream(sciezka + "ListaNumerow.xlsx", FileMode.Create, FileAccess.Write)) { xssfwb.Write(file); } }
/** * Called to update the embedded Excel workbook. As the format and structire * of the workbook are known in advance, all this code attempts to do is * write a new value into the first cell on the first row of the first * worksheet. Prior to executing this method, that cell will contain the * value 1. * * @throws org.apache.poi.openxml4j.exceptions.OpenXML4JException * Rather * than use the specific classes (HSSF/XSSF) to handle the embedded * workbook this method uses those defeined in the SS stream. As * a result, it might be the case that a SpreadsheetML file is * opened for processing, throwing this exception if that file is * invalid. * @throws java.io.IOException Thrown if a problem occurs in the underlying * file system. */ public void UpdateEmbeddedDoc1() { IWorkbook workbook = null; ISheet sheet = null; IRow row = null; NPOI.SS.UserModel.ICell cell = null; PackagePart pPart = null; IEnumerator <PackagePart> pIter = null; List <PackagePart> embeddedDocs = this.doc.GetAllEmbedds(); if (embeddedDocs != null && embeddedDocs.Count != 0) { pIter = embeddedDocs.GetEnumerator(); while (pIter.MoveNext()) { pPart = pIter.Current; if (pPart.PartName.Extension.Equals(BINARY_EXTENSION) || pPart.PartName.Extension.Equals(OPENXML_EXTENSION)) { // Get an InputStream from the pacage part and pass that // to the create method of the WorkbookFactory class. Update // the resulting Workbook and then stream that out again // using an OutputStream obtained from the same PackagePart. workbook = WorkbookFactory.Create(pPart.GetInputStream()); sheet = workbook.GetSheetAt(SHEET_NUM); row = sheet.GetRow(ROW_NUM); cell = row.GetCell(CELL_NUM); cell.SetCellValue(NEW_VALUE); workbook.Write(pPart.GetOutputStream()); } } // Finally, write the newly modified Word document out to file. string file = Path.GetFileNameWithoutExtension(this.docFile) + "tmp" + Path.GetExtension(this.docFile); this.doc.Write(new FileStream(file, FileMode.CreateNew)); } }
/// <summary> /// 將DataTable轉成Stream輸出. /// </summary> /// <param name="sourceTable">The source table.</param> /// <param name="templateFileName">範本檔檔名</param> /// <param name="startRows">指定由範本檔第幾列開始附加資料</param> /// <returns></returns> public static Stream RenderDataTableToExcel(DataTable sourceTable, string templateFileName, int startRows) { HSSFWorkbook workbook = FileToWorkBook(templateFileName); MemoryStream ms = new MemoryStream(); HSSFSheet sheet = (HSSFSheet)workbook.GetSheetAt(0); HSSFCellStyle cellStyle = (HSSFCellStyle)workbook.CreateCellStyle(); cellStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Left; cellStyle.VerticalAlignment = NPOI.SS.UserModel.VerticalAlignment.Center; cellStyle.WrapText = true; // handling value. int rowIndex = startRows; foreach (DataRow row in sourceTable.Rows) { HSSFRow dataRow = (HSSFRow)sheet.CreateRow(rowIndex); foreach (DataColumn column in sourceTable.Columns) { NPOI.SS.UserModel.ICell cell = dataRow.CreateCell(column.Ordinal); cell.SetCellValue(row[column].ToString()); cell.CellStyle = cellStyle; } rowIndex++; } workbook.Write(ms); ms.Flush(); ms.Position = 0; sheet = null; workbook = null; return(ms); }
/// <summary> /// return File(bs, "application/vnd.ms-excel"); /// </summary> /// <param name="tb"></param> /// <returns></returns> public static byte[] DataTableToExcel(DataTable tb) { if (tb == null) { return(null); } using (var ms = new MemoryStream()) { var workbook = new NPOI.XSSF.UserModel.XSSFWorkbook(); var sheet = workbook.CreateSheet(ValidateHelper.IsPlumpString(tb.TableName) ? tb.TableName : "sheet"); var style = GetStyle(workbook, NPOI.HSSF.Util.HSSFColor.Red.Index, NPOI.HSSF.Util.HSSFColor.White.Index); NPOI.SS.UserModel.IRow row = null; NPOI.SS.UserModel.ICell cell = null; for (int i = 0; i < tb.Rows.Count; ++i) { row = sheet.CreateRow(i); for (int j = 0; j < tb.Columns.Count; ++j) { cell = row.CreateCell(j); cell.SetCellValue(ConvertHelper.GetString(tb.Rows[i][j])); cell.CellStyle = style; } } workbook.Write(ms); workbook.Clear(); tb.Clear(); var bs = ms.ToArray(); return(bs); } }
/// <summary> /// DataTable导出到Excel的MemoryStream /// </summary> /// <param name="dtSource">源DataTable</param> /// <param name="strHeaderText">表头文本</param> public static MemoryStream Export(DataTable dtSource, string strHeaderText, string passaord = null) { HSSFWorkbook workbook = new HSSFWorkbook(); NPOI.SS.UserModel.ISheet sheet = workbook.CreateSheet(); if (string.IsNullOrEmpty(passaord) == false) { sheet.ProtectSheet(passaord); } #region 右击文件 属性信息 { DocumentSummaryInformation dsi = PropertySetFactory.CreateDocumentSummaryInformation(); dsi.Company = "NPOI"; workbook.DocumentSummaryInformation = dsi; SummaryInformation si = PropertySetFactory.CreateSummaryInformation(); si.Author = "文件作者信息"; //填加xls文件作者信息 si.ApplicationName = "创建程序信息"; //填加xls文件创建程序信息 si.LastAuthor = "最后保存者信息"; //填加xls文件最后保存者信息 si.Comments = "作者信息"; //填加xls文件作者信息 si.Title = "标题信息"; //填加xls文件标题信息 si.Subject = "主题信息"; //填加文件主题信息 si.CreateDateTime = DateTime.Now; workbook.SummaryInformation = si; } #endregion NPOI.SS.UserModel.ICellStyle dateStyle = workbook.CreateCellStyle(); NPOI.SS.UserModel.IDataFormat format = workbook.CreateDataFormat(); dateStyle.DataFormat = format.GetFormat("yyyy-mm-dd"); //取得列宽 int[] arrColWidth = new int[dtSource.Columns.Count]; foreach (DataColumn item in dtSource.Columns) { arrColWidth[item.Ordinal] = Encoding.GetEncoding(936).GetBytes(item.ColumnName.ToString()).Length; } for (int i = 0; i < dtSource.Rows.Count; i++) { for (int j = 0; j < dtSource.Columns.Count; j++) { int intTemp = Encoding.GetEncoding(936).GetBytes(dtSource.Rows[i][j].ToString()).Length; if (intTemp > arrColWidth[j]) { arrColWidth[j] = intTemp; } } } int rowIndex = 0; foreach (DataRow row in dtSource.Rows) { #region 新建表,填充表头,填充列头,样式 if (rowIndex == 65535 || rowIndex == 0) { if (rowIndex != 0) { sheet = workbook.CreateSheet(); if (string.IsNullOrEmpty(passaord) == false) { sheet.ProtectSheet(passaord); } } #region 表头及样式 { if (string.IsNullOrEmpty(strHeaderText) == false) { NPOI.SS.UserModel.IRow headerRow = sheet.CreateRow(0); headerRow.HeightInPoints = 25; headerRow.CreateCell(0).SetCellValue(strHeaderText); NPOI.SS.UserModel.ICellStyle headStyle = workbook.CreateCellStyle(); headStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center; NPOI.SS.UserModel.IFont font = workbook.CreateFont(); font.FontHeightInPoints = 20; font.Boldweight = 700; headStyle.SetFont(font); headerRow.GetCell(0).CellStyle = headStyle; sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(0, 0, 0, dtSource.Columns.Count - 1)); rowIndex += 1; } //headerRow.Dispose(); } #endregion #region 列头及样式 { NPOI.SS.UserModel.IRow headerRow = sheet.CreateRow(rowIndex); NPOI.SS.UserModel.ICellStyle headStyle = workbook.CreateCellStyle(); headStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center; NPOI.SS.UserModel.IFont font = workbook.CreateFont(); font.FontHeightInPoints = 10; font.Boldweight = 700; headStyle.SetFont(font); foreach (DataColumn column in dtSource.Columns) { headerRow.CreateCell(column.Ordinal).SetCellValue(column.ColumnName); headerRow.GetCell(column.Ordinal).CellStyle = headStyle; //设置列宽 sheet.SetColumnWidth(column.Ordinal, (arrColWidth[column.Ordinal] + 1) * 256); } //headerRow.Dispose(); rowIndex += 1; } #endregion } #endregion #region 填充内容 NPOI.SS.UserModel.IRow dataRow = sheet.CreateRow(rowIndex); foreach (DataColumn column in dtSource.Columns) { NPOI.SS.UserModel.ICell newCell = dataRow.CreateCell(column.Ordinal); string drValue = row[column].ToString(); switch (column.DataType.ToString()) { case "System.String": //字符串类型 newCell.SetCellValue(drValue); break; case "System.DateTime": //日期类型 DateTime dateV; DateTime.TryParse(drValue, out dateV); newCell.SetCellValue(dateV); newCell.CellStyle = dateStyle; //格式化显示 break; case "System.Boolean": //布尔型 bool boolV = false; bool.TryParse(drValue, out boolV); newCell.SetCellValue(boolV); break; case "System.Int16": //整型 case "System.Int32": case "System.Int64": case "System.Byte": int intV = 0; int.TryParse(drValue, out intV); newCell.SetCellValue(intV); break; case "System.Decimal": //浮点型 case "System.Double": double doubV = 0; double.TryParse(drValue, out doubV); newCell.SetCellValue(doubV); break; case "System.DBNull": //空值处理 newCell.SetCellValue(""); break; default: newCell.SetCellValue(""); break; } } #endregion rowIndex++; } using (MemoryStream ms = new MemoryStream()) { workbook.Write(ms); ms.Flush(); ms.Position = 0; // sheet.Dispose(); //workbook.Dispose();//一般只用写这一个就OK了,他会遍历并释放所有资源,但当前版本有问题所以只释放sheet return(ms); } }
/// <summary> /// DataTable导出到Excel的MemoryStream xlsx /// </summary> /// <param name="dtSource">源DataTable</param> /// <param name="strHeaderText">表头文本</param> /// <param name="strFileName">文件名</param> public static MemoryStream ExportXlsx(IEnumerable <DataTable> dataTables, string strHeaderText, string passaord = null) { XSSFWorkbook workbook = new XSSFWorkbook(); int i = 0; NPOI.SS.UserModel.ICellStyle dateStyle = workbook.CreateCellStyle(); NPOI.SS.UserModel.IDataFormat format = workbook.CreateDataFormat(); dateStyle.DataFormat = format.GetFormat("yyyy-mm-dd"); #region 右击文件 属性信息 { // DocumentSummaryInformation dsi = PropertySetFactory.CreateDocumentSummaryInformation(); // dsi.Company = "NPOI"; // SummaryInformation si = PropertySetFactory.CreateSummaryInformation(); // si.Author = "文件作者信息"; //填加xlsx文件作者信息 // si.ApplicationName = "创建程序信息"; //填加xlsx文件创建程序信息 // si.LastAuthor = "最后保存者信息"; //填加xls文件最后保存者信息 // si.Comments = "作者信息"; //填加xls文件作者信息 // si.Title = "标题信息"; //填加xls文件标题信息 // si.Subject = "主题信息";//填加文件主题信息 // si.CreateDateTime = DateTime.Now; // workbook.GetProperties().CustomProperties.AddProperty("Company", "NPOI"); // if (!workbook.GetProperties().CustomProperties.Contains("Company")) // workbook.GetProperties().CustomProperties.AddProperty("Company", dsi.Company); } #endregion foreach (DataTable dt in dataTables) { string sheetName = string.IsNullOrEmpty(dt.TableName) ? "Sheet " + (++i).ToString() : dt.TableName; ISheet sheet = workbook.CreateSheet(sheetName); if (string.IsNullOrEmpty(passaord) == false) { sheet.ProtectSheet(passaord); } int rowIndex = 0; #region 表头及样式 { if (string.IsNullOrEmpty(strHeaderText) == false) { NPOI.SS.UserModel.IRow headerRow = sheet.CreateRow(rowIndex); headerRow.HeightInPoints = 25; headerRow.CreateCell(0).SetCellValue(strHeaderText); NPOI.SS.UserModel.ICellStyle headStyle = workbook.CreateCellStyle(); headStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center; NPOI.SS.UserModel.IFont font = workbook.CreateFont(); font.FontHeightInPoints = 20; font.Boldweight = 700; headStyle.SetFont(font); headerRow.GetCell(0).CellStyle = headStyle; sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(0, 0, 0, dt.Columns.Count - 1)); rowIndex += 1; } //headerRow.Dispose(); } #endregion #region 列头及样式 { //取得列宽 int[] arrColWidth = new int[dt.Columns.Count]; foreach (DataColumn item in dt.Columns) { arrColWidth[item.Ordinal] = Encoding.GetEncoding(936).GetBytes(item.ColumnName.ToString()).Length; } for (int k = 0; k < dt.Rows.Count; k++) { for (int j = 0; j < dt.Columns.Count; j++) { int intTemp = Encoding.GetEncoding(936).GetBytes(dt.Rows[k][j].ToString()).Length; if (intTemp > arrColWidth[j]) { arrColWidth[j] = intTemp; } } } NPOI.SS.UserModel.IRow headerRow = sheet.CreateRow(rowIndex); NPOI.SS.UserModel.ICellStyle headStyle = workbook.CreateCellStyle(); headStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center; NPOI.SS.UserModel.IFont font = workbook.CreateFont(); font.FontHeightInPoints = 10; font.Boldweight = 700; headStyle.SetFont(font); foreach (DataColumn column in dt.Columns) { headerRow.CreateCell(column.Ordinal).SetCellValue(column.ColumnName); headerRow.GetCell(column.Ordinal).CellStyle = headStyle; //设置列宽 sheet.SetColumnWidth(column.Ordinal, (arrColWidth[column.Ordinal] + 1) * 256); } //headerRow.Dispose(); rowIndex += 1; } #endregion //#region 表头 //for (int j = 0; j < dt.Columns.Count; j++) //{ // string columnName = string.IsNullOrEmpty(dt.Columns[j].ColumnName) // ? "Column " + j.ToString() // : dt.Columns[j].ColumnName; // headerRow.CreateCell(j).SetCellValue(columnName); //} //#endregion #region 内容 for (int a = 0; a < dt.Rows.Count; a++) { DataRow dr = dt.Rows[a]; IRow row = sheet.CreateRow(a + rowIndex); for (int b = 0; b < dt.Columns.Count; b++) { row.CreateCell(b).SetCellValue(dr[b] != DBNull.Value ? dr[b].ToString() : string.Empty); DataColumn dc = dt.Columns[b]; NPOI.SS.UserModel.ICell newCell = row.CreateCell(dc.Ordinal); string drValue = dr[b].ToString(); switch (dc.DataType.ToString()) { case "System.String": //字符串类型 newCell.SetCellValue(drValue); break; case "System.DateTime": //日期类型 DateTime dateV; DateTime.TryParse(drValue, out dateV); newCell.SetCellValue(dateV); newCell.CellStyle = dateStyle; //格式化显示 break; case "System.Boolean": //布尔型 bool boolV = false; bool.TryParse(drValue, out boolV); newCell.SetCellValue(boolV); break; case "System.Int16": //整型 case "System.Int32": case "System.Int64": case "System.Byte": int intV = 0; int.TryParse(drValue, out intV); newCell.SetCellValue(intV); break; case "System.Decimal": //浮点型 case "System.Double": double doubV = 0; double.TryParse(drValue, out doubV); newCell.SetCellValue(doubV); break; case "System.DBNull": //空值处理 newCell.SetCellValue(""); break; default: newCell.SetCellValue(""); break; } } } #endregion } using (MemoryStream ms = new MemoryStream()) { workbook.Write(ms); ms.Flush(); // ms.Position = 0; // sheet.Dispose(); //workbook.Dispose();//一般只用写这一个就OK了,他会遍历并释放所有资源,但当前版本有问题所以只释放sheet return(ms); } }
//Datatable导出Excel public static void GridToExcelByNPOI(DataTable dt, string strExcelFileName, string title) { HSSFWorkbook workbook = new HSSFWorkbook(); try { ISheet sheet = workbook.CreateSheet("Sheet1"); ICellStyle HeadercellStyle = workbook.CreateCellStyle(); HeadercellStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin; HeadercellStyle.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin; HeadercellStyle.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin; HeadercellStyle.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin; HeadercellStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center; //字体 NPOI.SS.UserModel.IFont headerfont = workbook.CreateFont(); headerfont.Boldweight = (short)FontBoldWeight.Bold; HeadercellStyle.SetFont(headerfont); //标题头 int icolIndex = 0; CellRangeAddress region = new CellRangeAddress(0, 0, 0, dt.Columns.Count > 0 ? dt.Columns.Count - 1 : 0); sheet.AddMergedRegion(region); ((HSSFSheet)sheet).SetEnclosedBorderOfRegion(region, BorderStyle.Thin, NPOI.HSSF.Util.HSSFColor.Black.Index); IRow headerRow = sheet.CreateRow(0); headerRow.HeightInPoints = 20; NPOI.SS.UserModel.ICell celltitle = headerRow.CreateCell(0); celltitle.SetCellValue(title); celltitle.CellStyle = HeadercellStyle; //用column name 作为列名 IRow headerRow1 = sheet.CreateRow(1); foreach (DataColumn item in dt.Columns) { NPOI.SS.UserModel.ICell cell1 = headerRow1.CreateCell(icolIndex); cell1.SetCellValue(item.ColumnName); cell1.CellStyle = HeadercellStyle; icolIndex++; } ICellStyle cellStyle = workbook.CreateCellStyle(); //为避免日期格式被Excel自动替换,所以设定 format 为 『@』 表示一率当成text來看 cellStyle.DataFormat = HSSFDataFormat.GetBuiltinFormat("@"); cellStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin; cellStyle.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin; cellStyle.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin; cellStyle.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin; NPOI.SS.UserModel.IFont cellfont = workbook.CreateFont(); cellfont.Boldweight = (short)FontBoldWeight.Normal; cellStyle.SetFont(cellfont); //建立内容行 int iRowIndex = 2; int iCellIndex = 0; foreach (DataRow Rowitem in dt.Rows) { IRow DataRow = sheet.CreateRow(iRowIndex); foreach (DataColumn Colitem in dt.Columns) { NPOI.SS.UserModel.ICell cell = DataRow.CreateCell(iCellIndex); cell.SetCellValue(Rowitem[Colitem].ToString()); cell.CellStyle = cellStyle; iCellIndex++; } iCellIndex = 0; iRowIndex++; } //自适应列宽度 for (int i = 0; i < icolIndex; i++) { sheet.AutoSizeColumn(i); } //写Excel FileStream file = new FileStream(strExcelFileName, FileMode.OpenOrCreate); workbook.Write(file); file.Flush(); file.Close(); } catch (Exception ex) { string s = ex.Message; } finally { workbook = null; } }
/// <summary> /// 向指定的行和列的单元格写值 /// </summary> /// <param name="rowIndex">行索引</param> /// <param name="columnIndex">列索引</param> /// <param name="value">值</param> /// <returns></returns> private bool setData(int rowIndex, int columnIndex, string value) { rowIndex--; columnIndex--; NPOI.SS.UserModel.IRow row = null; if (xlSheet.GetRow(rowIndex) != null) { row = xlSheet.GetRow(rowIndex); } else { row = xlSheet.CreateRow(rowIndex); } /* * if (xlSheet.LastRowNum == 0) * { * row = xlSheet.CreateRow(0); * } * else * { * if (xlSheet.LastRowNum >= rowIndex) * { * if (xlSheet.GetRow(rowIndex) != null) * { * row = xlSheet.GetRow(rowIndex); * } * else * { * row = xlSheet.CreateRow(rowIndex); * } * } * else * { * row = xlSheet.CreateRow(rowIndex); * } * } * */ NPOI.SS.UserModel.ICell cell = null; if (row.GetCell(columnIndex) != null) { cell = row.GetCell(columnIndex); } else { cell = row.CreateCell(columnIndex); } /* * if (row.LastCellNum >= columnIndex) * { * if (row.GetCell(columnIndex) != null) * { * cell = row.GetCell(columnIndex); * } * else * { * cell = row.CreateCell(columnIndex); * } * * } * else * { * cell = row.CreateCell(columnIndex); * } * */ cell.SetCellValue(value); return(true); }
/// <summary> /// MLR將DataTable轉成Stream輸出. /// </summary> /// <param name="sourceTable">The source table.</param> /// <param name="templateFileName">範本檔檔名</param> /// <param name="startRows">指定由範本檔第幾列開始附加資料</param> /// <param name="tableId">MLR TableID</param> /// <returns></returns> public static Stream RenderDataTableToExcelForMLR(DataTable sourceTable, string templateFileName, int startRows, string tableId) { HSSFWorkbook workbook = FileToWorkBook(templateFileName); MemoryStream ms = new MemoryStream(); HSSFSheet sheet = (HSSFSheet)workbook.GetSheetAt(0); HSSFCellStyle cellStyle = (HSSFCellStyle)workbook.CreateCellStyle(); cellStyle.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin; cellStyle.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin; cellStyle.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin; cellStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin; cellStyle.WrapText = true; //Setting TableId HSSFRow fRow = (HSSFRow)sheet.CreateRow(0); NPOI.SS.UserModel.ICell firstCell = fRow.CreateCell(0); firstCell.SetCellValue(tableId); firstCell.CellStyle = cellStyle; // handling value. int rowIndex = startRows; foreach (DataRow row in sourceTable.Rows) { HSSFRow dataRow = (HSSFRow)sheet.CreateRow(rowIndex); foreach (DataColumn column in sourceTable.Columns) { NPOI.SS.UserModel.ICell cell = dataRow.CreateCell(column.Ordinal); cell.SetCellValue(row[column].ToString()); cell.CellStyle = cellStyle; } rowIndex++; } ISheet sheet1 = (ISheet)workbook.GetSheetAt(0); List <string> merged = new List <string>(); for (int i = 0; i < sourceTable.Rows.Count; i++) { string seq = sourceTable.Rows[i]["MASK_TOOLING_SEQUENCE"].ToString(); var query = sourceTable.Select(string.Format("MASK_TOOLING_SEQUENCE = '{0}'", seq)); int count = query.Count(); if (count > 1) { if (!merged.Contains(seq)) { merged.Add(seq); int firstRow = startRows + i; int lastRow = startRows + i + count - 1; sheet1.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(firstRow, lastRow, 0, 0)); sheet1.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(firstRow, lastRow, 3, 3)); sheet1.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(firstRow, lastRow, 8, 8)); } } } workbook.Write(ms); ms.Flush(); ms.Position = 0; sheet = null; workbook = null; return(ms); }
public void CreateExcel() { IWorkbook workbook = new XSSFWorkbook(); try { ISheet sheet = workbook.CreateSheet(SheetName); ICellStyle cellStyle = workbook.CreateCellStyle(); IDataFormat format = workbook.CreateDataFormat(); cellStyle.DataFormat = format.GetFormat("yyyy-mm-dd"); var RowIter = 0; var ColIter = 0; // Header Line - Food var row = sheet.CreateRow(RowIter); ColIter = 0; row.CreateCell(ColIter).SetCellValue("Food"); RowIter++; // Data Lines - Food row = sheet.CreateRow(RowIter); ColIter = 0; dicListDate.TryGetValue("Food", out List <string> ListDate); foreach (var val in ListDate) { DateTime t = ParseStringToDateTime(val); NPOI.SS.UserModel.ICell cell = row.CreateCell(ColIter); cell.SetCellValue(t); cell.CellStyle = cellStyle; ColIter++; } RowIter++; row = sheet.CreateRow(RowIter); ColIter = 0; dicListFpi.TryGetValue("Food", out List <double> ListFpi); foreach (var val in ListFpi) { row.CreateCell(ColIter).SetCellValue(val); ColIter++; } RowIter++; foreach (KeyValuePair <string, List <double> > entry in dicListFpi.OrderBy(o => o.Key).ToDictionary(o => o.Key, p => p.Value)) { if (entry.Key == "Food") { continue; } // Header Line - Except Food row = sheet.CreateRow(RowIter); ColIter = 0; row.CreateCell(ColIter).SetCellValue(entry.Key); RowIter++; // Data Lines - Except Food row = sheet.CreateRow(RowIter); ColIter = 0; foreach (var val in entry.Value) { DateTime t = ParseStringToDateTime(dicListDate[entry.Key][ColIter]); NPOI.SS.UserModel.ICell cell = row.CreateCell(ColIter); cell.SetCellValue(t); cell.CellStyle = cellStyle; //row.CreateCell(ColIter).SetCellValue(t); ColIter++; } RowIter++; row = sheet.CreateRow(RowIter); ColIter = 0; foreach (var val in entry.Value) { row.CreateCell(ColIter).SetCellValue(val); ColIter++; } RowIter++; } // Save Workbook FileInfo FI = new FileInfo(FilePath); FI.Directory.Create(); // If the directory already exists, this method does nothing. FileStream file = new FileStream(FilePath, FileMode.Create); workbook.Write(file); file.Close(); } catch { throw; } finally { workbook.Close(); } }
public static void ExportCommonGridtoExcel(System.Windows.Forms.DataGridView grid, string fileName) { if (grid == null || grid.DataSource == null || grid.Rows.Count == 0 || grid.Columns.Count == 0 || string.IsNullOrEmpty(fileName)) { return; } try { HSSFWorkbook workbook = new HSSFWorkbook(); MemoryStream ms = new MemoryStream(); NPOI.SS.UserModel.ISheet sheet = workbook.CreateSheet("Sheet1"); int colCount = 0; NPOI.SS.UserModel.IRow headerRow = sheet.CreateRow(0); for (int i = 0; i < grid.Columns.Count; i++) { if (grid.Columns[i].Visible) { NPOI.SS.UserModel.ICell cell = headerRow.CreateCell(colCount++, NPOI.SS.UserModel.CellType.String); cell.SetCellValue(grid.Columns[i].HeaderText); } } int rowCount = 1; for (int i = 0; i < grid.Rows.Count; i++) { colCount = 0; if (grid.Rows[i].Visible) { NPOI.SS.UserModel.IRow dataRow = sheet.CreateRow(rowCount++); for (int j = 0; j < grid.Columns.Count; j++) { if (grid.Columns[j].Visible) { NPOI.SS.UserModel.ICell cell = dataRow.CreateCell(colCount++, NPOI.SS.UserModel.CellType.String); if (grid.Rows[i].Cells[j] != null && grid.Rows[i].Cells[j].Value != null) { cell.SetCellValue(grid.Rows[i].Cells[j].Value.ToString()); } else { cell.SetCellValue(string.Empty); } } } } } for (int i = 0; i < grid.Columns.Count; i++) { if (grid.Columns[i].Visible) { sheet.AutoSizeColumn(i); } } workbook.Write(ms); FileStream file = new FileStream(fileName, FileMode.Create); workbook.Write(file); file.Close(); workbook = null; ms.Close(); ms.Dispose(); } catch (Exception) { throw; } }
/// <summary> /// 导出数据行 /// </summary> /// <param name="dtSource"></param> /// <param name="drSource"></param> /// <param name="currentExcelRow"></param> /// <param name="excelSheet"></param> /// <param name="excelWorkBook"></param> protected static void InsertCell(DataTable dtSource, DataRow drSource, NPOI.SS.UserModel.IRow currentExcelRow, NPOI.SS.UserModel.ISheet excelSheet, HSSFWorkbook excelWorkBook, NPOI.SS.UserModel.ICellStyle cellStyle_DateTime) { for (int cellIndex = 0; cellIndex < _listColumnsName.Count; cellIndex++) { //列名称 string columnsName = _listColumnsName.GetKey(cellIndex).ToString(); NPOI.SS.UserModel.ICell newCell = null; System.Type rowType = drSource[columnsName].GetType(); string drValue = drSource[columnsName].ToString().Trim(); switch (rowType.ToString()) { case "System.String": //字符串类型 drValue = drValue.Replace("&", "&"); drValue = drValue.Replace(">", ">"); drValue = drValue.Replace("<", "<"); newCell = currentExcelRow.CreateCell(cellIndex); newCell.SetCellValue(drValue); break; case "System.DateTime": //日期类型 DateTime dateV; DateTime.TryParse(drValue, out dateV); newCell = currentExcelRow.CreateCell(cellIndex); newCell.SetCellValue(dateV); //格式化显示 newCell.CellStyle = cellStyle_DateTime; break; case "System.Boolean": //布尔型 bool boolV = false; bool.TryParse(drValue, out boolV); newCell = currentExcelRow.CreateCell(cellIndex); newCell.SetCellValue(boolV); break; case "System.Int16": //整型 case "System.Int32": case "System.Int64": case "System.Byte": int intV = 0; int.TryParse(drValue, out intV); newCell = currentExcelRow.CreateCell(cellIndex); newCell.SetCellValue(intV.ToString()); break; case "System.Decimal": //浮点型 case "System.Double": double doubV = 0; double.TryParse(drValue, out doubV); newCell = currentExcelRow.CreateCell(cellIndex); newCell.SetCellValue(doubV); break; case "System.DBNull": //空值处理 newCell = currentExcelRow.CreateCell(cellIndex); newCell.SetCellValue(""); break; case "System.Guid": //空值处理 newCell = currentExcelRow.CreateCell(cellIndex); newCell.SetCellValue(drValue); break; default: throw (new Exception(rowType.ToString() + ":类型数据无法处理!")); } } }
public static void ExportCommonGridtoExcel(System.Windows.Forms.DataGridView grid, string sheetName, out string fileName) { fileName = ""; if (grid == null || grid.DataSource == null || grid.Rows.Count == 0 || grid.Columns.Count == 0) { return; } System.Windows.Forms.SaveFileDialog sfDialog = new System.Windows.Forms.SaveFileDialog(); sfDialog.Filter = "Excel files(*.xls)|*.xls"; sfDialog.FilterIndex = 1; sfDialog.DefaultExt = ".xls"; //sfDialog.InitialDirectory = Environment.CurrentDirectory; sfDialog.RestoreDirectory = true; if (sfDialog.ShowDialog() != System.Windows.Forms.DialogResult.OK) { return; } try { HSSFWorkbook workbook = new HSSFWorkbook(); MemoryStream ms = new MemoryStream(); NPOI.SS.UserModel.ISheet sheet = workbook.CreateSheet(string.IsNullOrEmpty(sheetName) ? "Sheet1" : sheetName); int colCount = 0; NPOI.SS.UserModel.IRow headerRow = sheet.CreateRow(0); for (int i = 0; i < grid.Columns.Count; i++) { if (grid.Columns[i].Visible) { NPOI.SS.UserModel.ICell cell = headerRow.CreateCell(colCount++, NPOI.SS.UserModel.CellType.String); cell.SetCellValue(grid.Columns[i].HeaderText); } } int rowCount = 1; for (int i = 0; i < grid.Rows.Count; i++) { colCount = 0; if (grid.Rows[i].Visible) { NPOI.SS.UserModel.IRow dataRow = sheet.CreateRow(rowCount++); for (int j = 0; j < grid.Columns.Count; j++) { if (grid.Columns[j].Visible) { NPOI.SS.UserModel.ICell cell = dataRow.CreateCell(colCount++, NPOI.SS.UserModel.CellType.String); if (grid.Rows[i].Cells[j] != null && grid.Rows[i].Cells[j].Value != null) { cell.SetCellValue(grid.Rows[i].Cells[j].Value.ToString()); } else { cell.SetCellValue(string.Empty); } } } } } for (int i = 0; i < grid.Columns.Count; i++) { if (grid.Columns[i].Visible) { sheet.AutoSizeColumn(i); } } workbook.Write(ms); FileStream file = new FileStream(sfDialog.FileName, FileMode.Create); workbook.Write(file); file.Close(); workbook = null; ms.Close(); ms.Dispose(); fileName = sfDialog.FileName; } catch (Exception) { throw; } }