Question

C#导出Excel单元格合并问题？？求大神

我想把相同部分的数据的单元格合并，请问应该怎么写？？ string[] BiaoTou = { "填写人", "填写时间", "标段名称", "作业时间", "作业人数", "天气", "作业位置", "作业类型", "数量", "人数", "内容" }; cells[2, 0].PutValue(BiaoTou[0]); cells[2, 1].PutValue(BiaoTou[1]); cells[2, 2].PutValue(BiaoTou[2]); cells[2, 3].PutValue(BiaoTou[3]); cells[2, 4].PutValue(BiaoTou[4]); cells[2, 5].PutValue(BiaoTou[5]); cells[2, 6].PutValue(BiaoTou[6]); cells[2, 7].PutValue(BiaoTou[7]); cells[2, 8].PutValue(BiaoTou[8]); cells[2, 9].PutValue(BiaoTou[9]); cells[2, 10].PutValue(BiaoTou[10]); for (int i = 0; i < dt.Rows.Count; i++) { string Creater = dt.Rows[i]["Creater"].ToString(); string CreateTime = dt.Rows[i]["CreateTime"].ToString(); string BiaoDuanName = dt.Rows[i]["BiaoDuanName"].ToString(); string ZuoYeTime = dt.Rows[i]["ZuoYeTime"].ToString(); string ZuoYeRenShu = dt.Rows[i]["ZuoYeRenShu"].ToString(); string TianQiName = dt.Rows[i]["TianQiName"].ToString(); cells[i + 3, 0].PutValue(Creater); cells[i + 3, 1].PutValue(CreateTime); cells[i + 3, 2].PutValue(BiaoDuanName); cells[i + 3, 3].PutValue(ZuoYeTime); cells[i + 3, 4].PutValue(ZuoYeRenShu); cells[i + 3, 5].PutValue(TianQiName); cells[i + 3, 6].PutValue(dt.Rows[i]["ZuoYeLeiXingName"]); cells[i + 3, 7].PutValue(dt.Rows[i]["ZuoYeWeiZhiName"]); cells[i + 3, 8].PutValue(dt.Rows[i]["ZuoYeCount"]); cells[i + 3, 9].PutValue(dt.Rows[i]["ZuoYeRenCount"]); cells[i + 3, 10].PutValue(dt.Rows[i]["ZuoYeContent"]); }

Answer 1

参考一下，使用NPOI类库，

1. 创建Excel ，组织好数据格式，行合并等等，导出到Excel文件即可。

   private static void createExcel()
           {
               //建立空白工作簿
               IWorkbook workbook = new HSSFWorkbook();
               //在工作簿中：建立空白工作表
               ISheet sheet = workbook.CreateSheet("表1矿山基本情况表（1）");
               //sheet.SetColumnWidth(0, 10 * 256);
               //在工作表中：建立行，参数为行号，从0计
               IRow row = sheet.CreateRow(0);
               //在行中：建立单元格，参数为列号，从0计
               ICell cell = row.CreateCell(0);
               //设置单元格内容
               cell.SetCellValue("表1 矿山基本情况表（1）");
               ICellStyle style = workbook.CreateCellStyle();
               //设置单元格的样式：水平对齐居中
               style.Alignment = HorizontalAlignment.Center;
               style.VerticalAlignment = VerticalAlignment.Center;
               //新建一个字体样式对象
               IFont font = workbook.CreateFont();
               font.FontName = "宋体";
               font.FontHeightInPoints = 14;
               //设置字体加粗样式
               //font.Boldweight = short.MaxValue;
               //使用SetFont方法将字体样式添加到单元格样式中 
               style.SetFont(font);
               //将新的样式赋给单元格
               cell.CellStyle = style;
   
               //设置单元格的高度
               row.Height = 30 * 20;
               //设置单元格的宽度
               sheet.SetColumnWidth(0, 30 * 256);
   
               //设置一个合并单元格区域，使用上下左右定义CellRangeAddress区域
               //CellRangeAddress四个参数为：起始行，结束行，起始列，结束列
               sheet.AddMergedRegion(new CellRangeAddress(0, 0, 0, 15));
   
               //创建header
   
               ICellStyle headerStyle = workbook.CreateCellStyle();
               //设置单元格的样式：水平对齐居中
               headerStyle.Alignment = HorizontalAlignment.Center;
               headerStyle.VerticalAlignment = VerticalAlignment.Center;
               //新建一个字体样式对象
               IFont heanderfont = workbook.CreateFont();
               heanderfont.FontName = "宋体";
               heanderfont.FontHeightInPoints = 11;
               //使用SetFont方法将字体样式添加到单元格样式中 
               headerStyle.SetFont(heanderfont);
   
               IRow header = sheet.CreateRow(1);
   
   
               ICell cellHeader = CreateHeaderRowCell(header, 0, "序号", headerStyle);
               cellHeader = CreateHeaderRowCell(header, 1, "矿山名称", headerStyle);
               cellHeader = CreateHeaderRowCell(header, 2, "采矿许可证号", headerStyle);
               cellHeader = CreateHeaderRowCell(header, 3, "矿山地址", headerStyle);
               cellHeader = CreateHeaderRowCell(header, 4, "矿山中心点坐标", headerStyle);
               //cellHeader = CreateHeaderRowCell(header, 5, "");
               cellHeader = CreateHeaderRowCell(header, 6, "矿区面积", headerStyle);
               cellHeader = CreateHeaderRowCell(header, 7, "拐点坐标", headerStyle);
               cellHeader = CreateHeaderRowCell(header, 8, "矿山规模", headerStyle);
               cellHeader = CreateHeaderRowCell(header, 9, "开采矿种", headerStyle);
               cellHeader = CreateHeaderRowCell(header, 10, "开采矿类", headerStyle);
               cellHeader = CreateHeaderRowCell(header, 11, "经济类型", headerStyle);
               cellHeader = CreateHeaderRowCell(header, 12, "开采方式", headerStyle);
               cellHeader = CreateHeaderRowCell(header, 13, "有效期起", headerStyle);
               cellHeader = CreateHeaderRowCell(header, 14, "有效期止", headerStyle);
   
               IRow header2 = sheet.CreateRow(2);
   
   
               cellHeader = CreateHeaderRowCell(header2, 4, "东经", headerStyle);
               cellHeader = CreateHeaderRowCell(header2, 5, "北纬", headerStyle);
               cellHeader = CreateHeaderRowCell(header2, 6, "（km²）", headerStyle);
               //合并
               sheet.AddMergedRegion(new CellRangeAddress(1, 2, 0, 0));//前3列合并
               sheet.AddMergedRegion(new CellRangeAddress(1, 2, 1, 1));
               sheet.AddMergedRegion(new CellRangeAddress(1, 2, 2, 2));
               sheet.AddMergedRegion(new CellRangeAddress(1, 2, 3, 3));
   
               sheet.AddMergedRegion(new CellRangeAddress(1, 1, 4, 5));
               //5，6，7列不合并
               sheet.AddMergedRegion(new CellRangeAddress(1, 2, 7, 7));
               sheet.AddMergedRegion(new CellRangeAddress(1, 2, 8, 8));
               sheet.AddMergedRegion(new CellRangeAddress(1, 2, 9, 9));
               sheet.AddMergedRegion(new CellRangeAddress(1, 2, 10, 10));
               sheet.AddMergedRegion(new CellRangeAddress(1, 2, 11, 11));
               sheet.AddMergedRegion(new CellRangeAddress(1, 2, 12, 12));
               sheet.AddMergedRegion(new CellRangeAddress(1, 2, 13, 13));
               sheet.AddMergedRegion(new CellRangeAddress(1, 2, 14, 14));
               int icolIndex = 15;
   
               //Datas
               int length = 10;
               for (int i = 1; i <= length; i++)
               {
                   IRow dRow = sheet.CreateRow(2 + i);
                   ICell cellRow = CreateHeaderRowCell(dRow, 0, i.ToString(), headerStyle);
               }
               int bottomIndex = 2 + length + 1;
               StringBuilder sb = new StringBuilder();
               sb.Append("填表单位（盖章）：          填表人：          审核人：       填表日期：    年    月    日");
   
               sheet.AddMergedRegion(new CellRangeAddress(bottomIndex, bottomIndex, 0, 15));
               IRow bottomRow = sheet.CreateRow(bottomIndex);
               ICell cellBottom = CreateHeaderRowCell(bottomRow, 0, sb.ToString(), style);
   
   
               for (int columnNum = 0; columnNum <= 14; columnNum++)
               {
                   int columnWidth = sheet.GetColumnWidth(columnNum) / 256;//获取当前列宽度  
                   for (int rowNum = 1; rowNum <= sheet.LastRowNum; rowNum++)//在这一列上循环行  
                   {
                       IRow currentRow = sheet.GetRow(rowNum);
                       ICell currentCell = currentRow.GetCell(columnNum);
                       int len = 0;
                       if (currentCell != null)
                       {
                           len = Encoding.UTF8.GetBytes(currentCell.ToString()).Length;//获取当前单元格的内容宽度  
                       }
                       if (columnWidth < len)
                       {
                           columnWidth = len;
                       }
                       //若当前单元格内容宽度大于列宽，则调整列宽为当前单元格宽度，后面的+1是我人为的将宽度增加一个字符  
                   }
                   //int width = (int)((columnWidth+0.8) * 256);
                   sheet.SetColumnWidth(columnNum, columnWidth * 256);
               }
               //自适应列宽度
               for (int i = 0; i < icolIndex; i++)
               {
                   if (i == 4 || i == 5)
                       continue;
                   sheet.AutoSizeColumn(i, false);
               }
               //sheet.AutoSizeColumn(0, true);
               //将工作簿写入文件
               using (FileStream fs = new FileStream("生成效果1.xls", FileMode.Create, FileAccess.Write))
               {
                   workbook.Write(fs);
               }
           }

2. 如果对于格式有严格要求，可以先加载Excel模板，向模板中填充数据，导出到excel文件即可。
   

   String fileName = @"表1.xlsx";
   FileStream file = new FileStream(fileName, FileMode.Open, FileAccess.Read);
   IWorkbook hssfworkbook = new XSSFWorkbook(file);
   ISheet ws = hssfworkbook.GetSheet("表1矿山基本情况表（1）");
   int totalIndex = 20;        // 每个类别的总行数
   int rowIndex = 3;       // 起始行
   int colmunNum = 15;//列数
   
   ICellStyle style = hssfworkbook.CreateCellStyle();
   //设置单元格的样式：水平对齐居中
   style.Alignment = HorizontalAlignment.Center;
   style.VerticalAlignment = VerticalAlignment.Center;
   //新建一个字体样式对象
   IFont font = hssfworkbook.CreateFont();
   font.FontName = "宋体";
   font.FontHeightInPoints = 14;
   //设置字体加粗样式
   //font.Boldweight = short.MaxValue;
   //使用SetFont方法将字体样式添加到单元格样式中
   style.SetFont(font);
   
   //datas
   ICellStyle headerStyle = hssfworkbook.CreateCellStyle();
   //设置单元格的样式：水平对齐居中
   headerStyle.Alignment = HorizontalAlignment.Center;
   headerStyle.VerticalAlignment = VerticalAlignment.Center;
   //新建一个字体样式对象
   IFont heanderfont = hssfworkbook.CreateFont();
   heanderfont.FontName = "宋体";
   heanderfont.FontHeightInPoints = 11;
   //使用SetFont方法将字体样式添加到单元格样式中
   headerStyle.SetFont(heanderfont);
   //Datas
   int datas_len = 10;
   for (int i = 1; i <= datas_len; i++)
   {
   IRow dRow = ws.CreateRow(2 + i);
   dRow.Height = 25 * 20;
   ICell cellRow = CreateHeaderRowCell(dRow, 0, i.ToString(), headerStyle);
   }
   //追加最后一行 合并
   int bottomIndex = 2 + datas_len + 1;
   StringBuilder sb = new StringBuilder();
   sb.Append("填表单位（盖章）：          填表人：          审核人：       填表日期：    年    月    日");
   
   ws.AddMergedRegion(new CellRangeAddress(bottomIndex, bottomIndex, 0, colmunNum-1));
   IRow bottomRow = ws.CreateRow(bottomIndex);
   ICell cellBottom = CreateHeaderRowCell(bottomRow, 0, sb.ToString(), style);
   //转存
   using (FileStream fs = new FileStream("生成效果2.xlsx", FileMode.Create, FileAccess.Write))
   {
   hssfworkbook.Write(fs);
   }
   Console.WriteLine("Finish");