在工作中,使用ASP.net(C#)从页面上导出Excel文件。要求是复杂表头
所以就试了几种方法,列出来供大家参考:
第一种方法呢,主要针对简单的Excel的导出,只有一行列名和数据。
在页面上,有一个导出的按钮。下面只写出按钮事件的代码:
- try
- {
- System.Data.DataTable table = bindData("Trdate desc");
- //将DataTable中添加一行,合并所有列
- String[] arr = new String[10] { "卡印刷号", "流水号", "商户名称", "商户号", "POS编号", "日期时间", "发生额", "操作员", "类型", "备注" };
- for (int i = 0; i < table.Columns.Count; i++)
- {
- table.Columns[i].ColumnName = arr[i].ToString();
- }
- DataView dv1 = table.DefaultView;
- dv1.Sort = "Trdate desc";//将DataTable排序,按POS编号(分店)排序,日期时间按倒序排列
- table = dv1.ToTable();
- ExcelHelper.DataTable1Excel(table);
- }
- catch
- {
- Response.Write("<script >function window.onload() {alert('导出失败!');}</script>");
- }
- public static void DataTable1Excel(System.Data.DataTable dtData)
- {
- try
- {
- GridView gvExport = null;
- HttpContext curContext = HttpContext.Current;
- StringWriter strWriter = null;
- HtmlTextWriter htmlWriter = null;
- if (dtData != null)
- {
- curContext.Response.ContentType = "application/vnd.ms-excel";
- curContext.Response.ContentEncoding = System.Text.Encoding.GetEncoding("gb2312");
- curContext.Response.Charset = "utf-8";
- strWriter = new StringWriter();
- htmlWriter = new HtmlTextWriter(strWriter);
- gvExport = new GridView();
- gvExport.DataSource = dtData.DefaultView;
- gvExport.AllowPaging = false;
- gvExport.DataBind();
- gvExport.RenderControl(htmlWriter);
- curContext.Response.Write("<meta http-equiv=\"Content-Type\" content=\"text/html;charset=gb2312\"/>" + strWriter.ToString());
- curContext.Response.End();
- }
- }
- catch (Exception e)
- {
- throw e;
- }
- }
方法2:需要引入MyXls的引用。org.in2bits.MyXls
- try
- {
- org.in2bits.MyXls.XlsDocument tz = new org.in2bits.MyXls.XlsDocument();
- tz.FileName = DateTime.Now.ToString().Replace("-", "").Replace(":", "").Replace(" ", "") + ".xls";//excel文件名称
- org.in2bits.MyXls.Worksheet sheet = tz.Workbook.Worksheets.AddNamed("36524充值明细");//Excel工作表名称
- org.in2bits.MyXls.Cells cells = sheet.Cells;
- int colnum = GridView_Data.Columns.Count; //获取gridview列数
- for (int i = 0; i < colnum; i++)
- {
- cells.AddValueCell(1, (i + 1), GridView_Data.Columns[i].HeaderText);//导出gridView列名
- }
- DataTable table = bindData("Trdate desc");
- for (int i = 0; i < table.Rows.Count; i++)
- {
- for (int j = 0; j < colnum; j++)
- {
- cells.AddValueCell((i + 2), (j + 1), table.Rows[i][j].ToString());
- }
- }
- tz.Send();//把写好的excel文件输出到客户端
- }
- catch { }
方法3:-------------------复杂表头
- DataTable table = bindData("Trdate desc");
- String[] arr = new String[10] { "卡印刷号", "流水号", "商户名称", "商户号", "POS编号", "日期时间", "发生额", "操作员", "类型", "备注" };
- for (int i = 0; i < table.Columns.Count; i++)
- {
- table.Columns[i].ColumnName = arr[i].ToString();
- }
- DataView dv1 = table.DefaultView;
- dv1.Sort = "Trdate desc";//将DataTable排序,按POS编号(分店)排序,日期时间按倒序排列
- table = dv1.ToTable();
- string summary = LabelSumLS.Text;
- exportBu36524czmx("充值明细", summary, table);
- #region 导出Excel--方法三
- protected void exportBu36524czmx(string titleName,string summary,DataTable table)
- {
- try
- {
- string temp_path = Environment.GetFolderPath(Environment.SpecialFolder.Desktop);//生成的文件存放路径
- ExcelOperate excelOperate = new ExcelOperate();
- //建立一个Excel.Application的新进程
- Microsoft.Office.Interop.Excel.Application app = new Microsoft.Office.Interop.Excel.Application();
- if (app == null)
- {
- return;
- }
- app.Visible = false;
- app.UserControl = true;
- Microsoft.Office.Interop.Excel.Workbooks workbooks = app.Workbooks;
- Microsoft.Office.Interop.Excel._Workbook workbook = workbooks.Add(Microsoft.Office.Interop.Excel.XlWBATemplate.xlWBATWorksheet);//这里的Add方法里的参数就相当于继承了一个空模板(暂这样理解吧)
- Microsoft.Office.Interop.Excel.Sheets sheets = workbook.Worksheets;
- Microsoft.Office.Interop.Excel._Worksheet worksheet = (Microsoft.Office.Interop.Excel._Worksheet)sheets.get_Item(1);
- if (worksheet == null)
- {
- return;
- }
- worksheet.Rows.NumberFormatLocal = "@"; //设置所有单元格为文本格式
- //第一行的标题
- worksheet.get_Range(worksheet.Cells[1, 1], worksheet.Cells[1, table.Columns.Count]).Merge(Missing.Value); //横向合并
- worksheet.get_Range(worksheet.Cells[1, 1], worksheet.Cells[1, 1]).Value2 = titleName;
- //设置格式
- excelOperate.SetHAlignCenter(worksheet, worksheet.Cells[1, 1], worksheet.Cells[1, 1]);//居中
- excelOperate.SetFontSize(worksheet, worksheet.Cells[1, 1], worksheet.Cells[1, 1], 12);//字体大小
- worksheet.get_Range(worksheet.Cells[1, 1], worksheet.Cells[1, table.Columns.Count]).Borders.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Black);//黑色连续边框
- //第二行的综述
- worksheet.get_Range(worksheet.Cells[2, 1], worksheet.Cells[2, table.Columns.Count]).Merge(Missing.Value); //横向合并
- worksheet.get_Range(worksheet.Cells[2, 1], worksheet.Cells[2, 1]).Value2 = summary;
- //设置格式
- excelOperate.SetHAlignCenter(worksheet, worksheet.Cells[2, 1], worksheet.Cells[2, 1]);//居中
- excelOperate.SetFontSize(worksheet, worksheet.Cells[2, 1], worksheet.Cells[2, 1], 9);//字体大小
- worksheet.get_Range(worksheet.Cells[2, 1], worksheet.Cells[1, table.Columns.Count]).Borders.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Black);//黑色连续边框
- for (int i = 0; i < table.Columns.Count; i++)
- {
- worksheet.Cells[3, i + 1] = table.Columns[i].ColumnName.ToString();
- excelOperate.SetFontSize(worksheet, worksheet.Cells[3, i + 1], worksheet.Cells[3, i + 1], 9);//字体大小
- excelOperate.SetBold(worksheet, worksheet.Cells[3, i + 1], worksheet.Cells[3, i + 1]); //黑体
- }
- worksheet.get_Range(worksheet.Cells[3, 1], worksheet.Cells[3, table.Columns.Count]).Borders.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Black);
- for (int i = 0; i < table.Rows.Count; i++)
- {
- for (int j = 0; j < table.Columns.Count; j++)
- {
- string data = table.Rows[i][j].ToString();
- worksheet.Cells[4 + i, j + 1] = data;
- excelOperate.SetFontSize(worksheet, worksheet.Cells[4 + i, j + 1], worksheet.Cells[4 + i, j + 1], 9);//字体大小
- //if (j == 6)
- //{
- // worksheet.Cells[4 + i, j + 1].
- //}
- }
- worksheet.get_Range(worksheet.Cells[4 + i, 1], worksheet.Cells[4 + i, 10]).Borders.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Black);//设置边框颜色,不然打印预览,会非常不雅观
- }
- worksheet.Name = titleName;
- worksheet.Columns.EntireColumn.AutoFit();//列宽自适应
- String tick = DateTime.Now.ToString().Replace("-", "").Replace(":", "").Replace(" ", "") + ".xls";//excel文件名称
- String save_path = temp_path + "\\" + tick + ".xls";
- workbook.SaveAs(save_path, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value);
- excelOperate.Dispose(worksheet, workbook, app);//关闭Excel进程
- Response.Write("<script >function window.onload() {alert('导出Excel文件到桌面成功!');}</script>");
- }
- catch
- {
- Response.Write("<script >function window.onload() {alert('导出失败!');}</script>");
- }
- }
- #endregion
结果:
方法4------------------------复杂表头,和方法三的结果一样。---------------------------我最后采用的第4种方法。
- string excelTile = Label1.Text;
- string summary = LabelSumLS.Text;
- TableCell[] header = new TableCell[12];
- for (int i = 0; i < header.Length; i++)
- {
- header[i] = new TableHeaderCell();
- }
- //第一行表头
- header[0].ColumnSpan = 10;
- header[0].Text = excelTile + "</th></tr><tr>";
- //第二行表头
- header[1].ColumnSpan = 10;
- header[1].Text = summary + "</th></tr><tr>";
- String[] arr = new String[10] { "卡印刷号", "流水号", "商户名称", "商户号", "POS编号", "日期时间", "发生额", "操作员", "类型", "备注" };
- for (int i = 0; i < arr.Length; i++)
- {
- header[i + 2].Text = arr[i];
- if (i + 2 == 11)
- {
- header[i + 2].Text = arr[i] + "</th>";
- }
- }
- DataTable dt = bindData("Trdate desc");//根据检索条件查询出DataTable
- Dictionary<int, int> mergeCellNums = new Dictionary<int, int>();
- for (int i = 0; i < dt.Columns.Count; i++)
- {
- mergeCellNums.Add(i, 2);
- }
- Common.DataTable2Excel(dt, header, excelTile + DateTime.Now.ToString("yyyyMMdd"), mergeCellNums, 0);
Common类的代码如下:
- using System;
- using System.Collections.Generic;
- using System.Web;
- using System.Configuration;
- using System.Data;
- using System.Data.Common;
- using System.Data.OleDb;
- using System.Web.UI.WebControls;
- using System.Text.RegularExpressions;
- namespace ContractSystem.WebUI.DataAnalyse
- {
- /// <summary>
- /// Common 的摘要说明
- /// 作者:李伟波
- /// 时间:2012-10-18
- /// </summary>
- public class Common
- {
- public Common()
- {
- //
- //TODO: 在此处添加构造函数逻辑
- //
- }
- /// <summary>
- /// 描述:把DataTable内容导出excel并返回客户端
- /// </summary>
- /// <param name="dtData"></param>
- /// <param name="header"></param>
- /// <param name="fileName"></param>
- /// <param name="mergeCellNums">要合并的列索引字典 格式:列索引-合并模式(合并模式 1 合并相同项、2 合并空项、3 合并相同项及空项)</param>
- /// <param name="mergeKey">作为合并项的标记列索引</param>
- public static void DataTable2Excel(System.Data.DataTable dtData, TableCell[] header, string fileName, Dictionary<int, int> mergeCellNums, int mergeKey)
- {
- System.Web.UI.WebControls.GridView gvExport = null;
- // 当前对话
- System.Web.HttpContext curContext = System.Web.HttpContext.Current;
- // IO用于导出并返回excel文件
- System.IO.StringWriter strWriter = null;
- System.Web.UI.HtmlTextWriter htmlWriter = null;
- if (dtData != null)
- {
- // 设置编码和附件格式
- curContext.Response.ContentType = "application/vnd.ms-excel";
- curContext.Response.ContentEncoding = System.Text.Encoding.GetEncoding("gb2312");
- curContext.Response.Charset = "gb2312";
- if (!string.IsNullOrEmpty(fileName))
- {
- //处理中文名乱码问题
- fileName = System.Web.HttpUtility.UrlEncode(fileName, System.Text.Encoding.UTF8);
- curContext.Response.AppendHeader("Content-Disposition", ("attachment;filename=" + (fileName.ToLower().EndsWith(".xls") ? fileName : fileName + ".xls")));
- }
- // 导出excel文件
- strWriter = new System.IO.StringWriter();
- htmlWriter = new System.Web.UI.HtmlTextWriter(strWriter);
- // 重新定义一个无分页的GridView
- gvExport = new System.Web.UI.WebControls.GridView();
- gvExport.DataSource = dtData.DefaultView;
- gvExport.AllowPaging = false;
- //优化导出数据显示,如身份证、12-1等显示异常问题
- gvExport.RowDataBound += new System.Web.UI.WebControls.GridViewRowEventHandler(dgExport_RowDataBound);
- gvExport.DataBind();
- //处理表头
- if (header != null && header.Length > 0)
- {
- gvExport.HeaderRow.Cells.Clear();
- gvExport.HeaderRow.Cells.AddRange(header);
- }
- //合并单元格
- if (mergeCellNums != null && mergeCellNums.Count > 0)
- {
- foreach (int cellNum in mergeCellNums.Keys)
- {
- MergeRows(gvExport, cellNum, mergeCellNums[cellNum], mergeKey);
- }
- }
- // 返回客户端
- gvExport.RenderControl(htmlWriter);
- curContext.Response.Clear();
- curContext.Response.Write("<meta http-equiv=\"content-type\" content=\"application/ms-excel; charset=gb2312\"/>" + strWriter.ToString());
- curContext.Response.End();
- }
- }
- /// <summary>
- /// 描述:行绑定事件
- /// </summary>
- /// <param name="sender"></param>
- /// <param name="e"></param>
- protected static void dgExport_RowDataBound(object sender, GridViewRowEventArgs e)
- {
- if (e.Row.RowType == DataControlRowType.DataRow)
- {
- foreach (TableCell cell in e.Row.Cells)
- {
- //优化导出数据显示,如大数据的字符串:卡号等显示异常问题
- if (Regex.IsMatch(cell.Text.Trim(), @"^\d{12,}$"))
- {
- cell.Attributes.Add("style", "vnd.ms-excel.numberformat:@");
- }
- }
- }
- }
- /// <summary>
- /// 描述:合并GridView列中相同的行
- /// </summary>
- /// <param name="gvExport">GridView对象</param>
- /// <param name="cellNum">需要合并的列</param>
- /// <param name="mergeMode">合并模式 1 合并相同项、2 合并空项、3 合并相同项及空项</param>
- /// <param name="mergeKey">作为合并项的标记列索引</param>
- public static void MergeRows(GridView gvExport, int cellNum, int mergeMode, int? mergeKey)
- {
- int i = 0, rowSpanNum = 1;
- //System.Drawing.Color alterColor = System.Drawing.Color.LightGray;
- while (i < gvExport.Rows.Count - 1)
- {
- GridViewRow gvr = gvExport.Rows[i];
- for (++i; i < gvExport.Rows.Count; i++)
- {
- GridViewRow gvrNext = gvExport.Rows[i];
- if ((!mergeKey.HasValue || (mergeKey.HasValue && (gvr.Cells[mergeKey.Value].Text.Equals(gvrNext.Cells[mergeKey.Value].Text) || " ".Equals(gvrNext.Cells[mergeKey.Value].Text)))) && ((mergeMode == 1 && gvr.Cells[cellNum].Text == gvrNext.Cells[cellNum].Text) || (mergeMode == 2 && " ".Equals(gvrNext.Cells[cellNum].Text.Trim())) || (mergeMode == 3 && (gvr.Cells[cellNum].Text == gvrNext.Cells[cellNum].Text || " ".Equals(gvrNext.Cells[cellNum].Text.Trim())))))
- {
- gvrNext.Cells[cellNum].Visible = false;
- rowSpanNum++;
- gvrNext.BackColor = gvr.BackColor;
- }
- else
- {
- gvr.Cells[cellNum].RowSpan = rowSpanNum;
- rowSpanNum = 1;
- //间隔行加底色,便于阅读
- //if (mergeKey.HasValue && cellNum == mergeKey.Value)
- //{
- // if (alterColor == System.Drawing.Color.White)
- // {
- // gvr.BackColor = System.Drawing.Color.LightGray;
- // alterColor = System.Drawing.Color.LightGray;
- // }
- // else
- // {
- // alterColor = System.Drawing.Color.White;
- // }
- //}
- break;
- }
- if (i == gvExport.Rows.Count - 1)
- {
- gvr.Cells[cellNum].RowSpan = rowSpanNum;
- //if (mergeKey.HasValue && cellNum == mergeKey.Value)
- //{
- // if (alterColor == System.Drawing.Color.White)
- // gvr.BackColor = System.Drawing.Color.LightGray;
- //}
- }
- }
- }
- }
- }
- }
第4中方法参考链接如下:{我的程序中,将行间隔的颜色去掉了,在Common中注释掉的语句是每间隔一行,将该行的背景置灰} http://blog.csdn.net/ranbolwb/article/details/8083983