在工作开发中,客户经常要求数据库中数据导出到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 }