在工作中,使用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>");
}
ExcelHelper类中:
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:-------------------复杂表头