C#使用Npoi導出Excel並合並行列
在工作開發中,客戶經常要求數據庫中數據導出到Excel表格。以前方法是引用office相關組件,如果客戶沒有安裝office,功能就會遇到問題。
現在用Npoi導出Excel,導出表格是合並行列,如圖:
導出的要求:合計列要進行合並,序號一致的要合並。最後一行要合並列。
因為相同序號數量不是固定的,要動態算合並的行數。
合並行列接口:XXX.AddMergedRegion(new CellRangeAddress(開始行, 最後一行, 開始列, 最後一列));
隱藏指定:sheet.SetColumnHidden(cellIndex, true);
引用組件:
NPOI.dll;
NPOI.OOXML.dll;
NPOI.OpenXml4Net.dll;
NPOI.OpenXmlFormats.dll;
ICSharpCode.SharpZipLib.dll;
代碼如下:
/// <summary> /// /// </summary> /// <param name="dtSource">數據源</param> /// <param name="strFileName">保存路徑</param> /// <param name="dvXH">序號</param> public void Export(DataTable dtSource,string strFileName,DataView dvXH=null) { //創建工作簿 office2007以上 XSSFWorkbook workbook = new XSSFWorkbook(); //為工作簿創建工作表並命名 ISheet sheet = workbook.CreateSheet("商品表"); ICellStyle dateStyle = workbook.CreateCellStyle(); IDataFormat format = workbook.CreateDataFormat(); dateStyle.DataFormat = format.GetFormat("yyyy-mm-dd"); #region 表頭及樣式 int cellIndex = 0; IRow headerRow = sheet.CreateRow(0); for (int i = 0; i < dtSource.Columns.Count; i++) { #region MyRegion string ColumnsName = dtSource.Columns[i].ToString(); if (dtSource.Columns[i].ColumnName.EndsWith("XH")) { ColumnsName = "序號"; sheet.SetColumnWidth(cellIndex, 3000); //sheet.SetColumnHidden(cellIndex, true);隱藏指定列 } else if (dtSource.Columns[i].ColumnName.EndsWith("GoogName")) { ColumnsName = "商品名稱"; sheet.SetColumnWidth(cellIndex,10000);//設置列寬 } else if (dtSource.Columns[i].ColumnName.EndsWith("Num")) { ColumnsName = "數量"; sheet.SetColumnWidth(cellIndex, 5000); } else if (dtSource.Columns[i].ColumnName.EndsWith("Summation")) { ColumnsName = "合計(元)"; sheet.SetColumnWidth(cellIndex, 5000); } #endregion //設置行高 headerRow.HeightInPoints = 35; headerRow.CreateCell(cellIndex).SetCellValue(ColumnsName); ICellStyle headStyle = workbook.CreateCellStyle(); headStyle.WrapText = true; IFont font = workbook.CreateFont(); //字體大小 font.FontHeightInPoints = 12; font.Boldweight = 360; headStyle.SetFont(font); headerRow.GetCell(cellIndex).CellStyle = headStyle; cellIndex++; } #endregion int rowIndex = 1;//行數一定要從1行開始 int count = 1; int startRow = 1; DataView dvSource = dtSource.DefaultView; if (dvXH!=null) { foreach (DataRowView drv in dvXH) {//1-10.11-12,13-14,15-16 int rowcout = 0; dvSource.RowFilter = "XH='" + drv["XH"] + "'"; foreach (DataRowView row in dvSource) { #region 填充內容 IRow dataRow = sheet.CreateRow(rowIndex); //序號 ICell newCel0 = dataRow.CreateCell(0); ICellStyle style0 = workbook.CreateCellStyle(); style0.DataFormat = format.GetFormat("text"); newCel0.SetCellValue(row["XH"].ToString()); //標的名稱 ICell newCel2 = dataRow.CreateCell(1); ICellStyle style2 = workbook.CreateCellStyle(); style2.DataFormat = format.GetFormat("text"); newCel2.SetCellValue(row["GoogName"].ToString()); //標的數量 ICell newCel4 = dataRow.CreateCell(2); ICellStyle style4 = workbook.CreateCellStyle(); style4.DataFormat = format.GetFormat("text"); newCel4.SetCellValue(row["Num"].ToString()); //合計(元) ICell newCel8 = dataRow.CreateCell(3); ICellStyle style8 = workbook.CreateCellStyle(); style8.DataFormat = format.GetFormat("text"); newCel8.SetCellValue(row["Summation"].ToString()); #endregion rowIndex++; rowcout++; } if (count == 1) { //合並行數 sheet.AddMergedRegion(new CellRangeAddress(startRow, rowcout, 3, 3)); startRow = startRow + rowcout; } else { sheet.AddMergedRegion(new CellRangeAddress(startRow, startRow + rowcout - 1, 3, 3)); startRow = startRow + rowcout; } count++; } } else { #region MyRegion foreach (DataRowView row in dvSource) { #region 填充內容 IRow dataRow = sheet.CreateRow(rowIndex); //序號 ICell newCel0 = dataRow.CreateCell(0); ICellStyle style0 = workbook.CreateCellStyle(); style0.DataFormat = format.GetFormat("text"); newCel0.SetCellValue(row["XH"].ToString()); //商品名稱 ICell newCel1 = dataRow.CreateCell(1); ICellStyle style1 = workbook.CreateCellStyle(); style1.DataFormat = format.GetFormat("text"); newCel1.SetCellValue(row["GoogName"].ToString()); //數量 ICell newCel2 = dataRow.CreateCell(2); ICellStyle style2 = workbook.CreateCellStyle(); style2.DataFormat = format.GetFormat("text"); newCel2.SetCellValue(row["Num"].ToString()); //合計(元) ICell newCel3 = dataRow.CreateCell(3); ICellStyle style3 = workbook.CreateCellStyle(); style3.DataFormat = format.GetFormat("text"); newCel3.SetCellValue(row["Summation"].ToString()); #endregion rowIndex++; } #endregion } #region 拼接最後一行 IFont fontLast = workbook.CreateFont(); fontLast.FontHeightInPoints = 30; fontLast.Boldweight = 480; IRow dataRowLast = sheet.CreateRow(rowIndex); dataRowLast.HeightInPoints = 40; ICell newCelLast = dataRowLast.CreateCell(0); ICellStyle styleLast = workbook.CreateCellStyle(); styleLast.DataFormat = format.GetFormat("text"); styleLast.SetFont(fontLast); newCelLast.SetCellValue("制作人:張三"); sheet.AddMergedRegion(new CellRangeAddress(rowIndex, rowIndex, 0, 3)); #endregion MemoryStream stream = new MemoryStream(); workbook.Write(stream); var buf = stream.ToArray(); using (FileStream fs = new FileStream(strFileName, FileMode.Create, FileAccess.Write)) { fs.Write(buf, 0, buf.Length); fs.Flush(); } }
實際運用中,涉及到數據,方法中有很多校驗等操作,方法直觀可讀性不是太好,下面附上簡單導出的方法:
實際上導出Excel,總結有幾點:
1、引用相關組件
2、創建一個工作簿,創建工作表並命名;
3、設置表頭及樣式;
4、填充數據;
5、保存數據到指定位置;
/// <summary> /// 簡單導出數據 /// </summary> /// <param name="dtSource">數據源</param> /// <param name="strFileName">保存路徑</param> /// <param name="dvXH">序號</param> public void Export1(DataTable dtSource, string strFileName) { //創建工作簿 XSSFWorkbook workbook = new XSSFWorkbook(); //為工作簿創建工作表並命名 ISheet sheet = workbook.CreateSheet("商品表"); IDataFormat format = workbook.CreateDataFormat(); #region 表頭及樣式 int cellIndex = 0; IRow headerRow = sheet.CreateRow(0); for (int i = 0; i < dtSource.Columns.Count; i++) { //設置行高 headerRow.HeightInPoints = 35; headerRow.CreateCell(cellIndex).SetCellValue(dtSource.Columns[i].ToString()); ICellStyle headStyle = workbook.CreateCellStyle(); headStyle.WrapText = true; IFont font = workbook.CreateFont(); //字體大小 font.FontHeightInPoints = 12; font.Boldweight = 360; headStyle.SetFont(font); headerRow.GetCell(cellIndex).CellStyle = headStyle; cellIndex++; } #endregion #region 數據填充 int rowIndex = 1;//行數一定要從1行開始,因為上面已經創建瞭表頭為0行; DataView dvSource = dtSource.DefaultView; foreach (DataRow row in dtSource.Rows) { int ColumnIndex = 0; IRow dataRow = sheet.CreateRow(rowIndex); foreach (DataColumn column in dtSource.Columns) { //序號 ICell newCel0 = dataRow.CreateCell(ColumnIndex); ICellStyle style0 = workbook.CreateCellStyle(); style0.DataFormat = format.GetFormat("text");//數據類型 newCel0.SetCellValue(row[column.ColumnName].ToString()); ColumnIndex++; } rowIndex++; } #endregion #region 保存到指定位置 MemoryStream stream = new MemoryStream(); workbook.Write(stream); var buf = stream.ToArray(); using (FileStream fs = new FileStream(strFileName, FileMode.Create, FileAccess.Write)) { fs.Write(buf, 0, buf.Length); fs.Flush(); } #endregion }
以上就是本文的全部內容,希望對大傢的學習有所幫助,也希望大傢多多支持WalkonNet。
推薦閱讀:
- C#使用NPOI實現Excel導入導出功能
- C#使用NPOI對Excel數據進行導入導出
- C#通過NPOI導入導出數據EXCEL
- C#使用NPOI讀取excel轉為DataSet
- C#利用NPOI操作Excel(單元格設置)