C# excel导出时根据数据库的内容自动合并单元格

在前台页面有一个导出按钮btn2

在后台页面给它填充方法

 

protected void btn2_Click(object sender, EventArgs e) { int j = 0; ds = SqlHelper.ExecuteDataset(this.connStr, CommandType.Text, "select count(nf),nf from Table_1 group by nf;select * from Table_1 order by nf "); helper = new ExcelHelper(); if (ds != null) { for (int i = 0; i < ds.Tables[0].Rows.Count; i++) { if (i == 0) { helper.MergeCells(1,1, 1, Convert.ToInt32(ds.Tables[0].Rows[i][0].ToString()), 1, ds.Tables[0].Rows[i][1].ToString()); } else if (Convert.ToInt32(ds.Tables[0].Rows[i][0].ToString()) != 1) { helper.MergeCells(1, j + 1, 1, j + Convert.ToInt32(ds.Tables[0].Rows[i][0].ToString()), 1, ds.Tables[0].Rows[i][1].ToString()); } else { helper.MergeCells(1, j + 1, 1, j + 1, 1, ds.Tables[0].Rows[i][1].ToString()); } j += Convert.ToInt32(ds.Tables[0].Rows[i][0].ToString()); } helper.DataTableToExcel(ds.Tables[1], 1, 1); } else { Response.Write("<mce:script type="text/javascript"><!--alert('暂时没有数据')// --></mce:script>"); return; } }

 

其中，ExcelHelper是一个类文件，与该实现有关的方法如下：

 

/// <summary> /// 构造函数，新建一个工作簿 /// </summary> public ExcelHelper() { //创建一个Application对象并使其可见 beforeTime = DateTime.Now; app = new Excel.ApplicationClass(); app.Visible = true; afterTime = DateTime.Now; //新建一个WorkBook workBook = app.Workbooks.Add(Type.Missing); //得到WorkSheet对象 workSheet = (Excel.Worksheet)workBook.Sheets.get_Item(1); } #endregion

 

/// <summary> /// 合并单元格，并赋值，对指定WorkSheet操作 /// </summary> /// <param name="sheetIndex">WorkSheet索引</param> /// <param name="beginRowIndex">开始行索引</param> /// <param name="beginColumnIndex">开始列索引</param> /// <param name="endRowIndex">结束行索引</param> /// <param name="endColumnIndex">结束列索引</param> /// <param name="text">合并后Range的值</param> public void MergeCells(int sheetIndex, int beginRowIndex, int beginColumnIndex, int endRowIndex, int endColumnIndex, string text) { if (sheetIndex > this.WorkSheetCount) { this.KillExcelProcess(); throw new Exception("索引超出范围，WorkSheet索引不能大于WorkSheet数量！"); } workSheet = (Excel.Worksheet)workBook.Worksheets.get_Item(sheetIndex); range = workSheet.get_Range(workSheet.Cells[beginRowIndex, beginColumnIndex], workSheet.Cells[endRowIndex, endColumnIndex]); range.ClearContents(); //先把Range内容清除，合并才不会出错 range.MergeCells = true; range.Value2 = text; range.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter; range.VerticalAlignment = Excel.XlVAlign.xlVAlignCenter; } #endregion

 

/// <summary> /// 将DataTable数据写入Excel文件（不分页） /// </summary> /// <param name="dt">DataTable</param> /// <param name="top">表格数据起始行索引</param> /// <param name="left">表格数据起始列索引</param> public void DataTableToExcel(System.Data.DataTable dt, int top, int left) { int rowCount = dt.Rows.Count; //DataTable行数 int colCount = dt.Columns.Count; //DataTable列数 //利用二维数组批量写入 string[,] arr = new string[rowCount, colCount]; for (int j = 0; j < rowCount; j++) { for (int k = 0; k < colCount; k++) { arr[j, k] = dt.Rows[j][k].ToString(); } } range = (Excel.Range)workSheet.Cells[top, left]; range = range.get_Resize(rowCount, colCount); range.Value2 = arr; }

 

注明：以上有些代码是参考并修改其他网络上的。