ASP.NET 导出Excel 和csv

 public static string ExportTable(DataSet ds)
    {
        string data = "";
        //data = ds.DataSetName + "\n";
        foreach (DataTable tb in ds.Tables)
        {
            //data += tb.TableName + "\n";
            data += "<table cellspacing=\"0\" cellpadding=\"5\" rules=\"all\" border=\"1\">";
            //写出列名
            data += "<tr style=\"font-weight: bold; white-space: nowrap;\">";
            foreach (DataColumn column in tb.Columns)
            {
                data += "<td>" + column.ColumnName + "</td>";
            }
            data += "</tr>";
            //写出数据
            foreach (DataRow row in tb.Rows)
            {
                data += "<tr>";
                foreach (DataColumn column in tb.Columns)
                {
                    if (column.ColumnName.Equals("证件编号") || column.ColumnName.Equals("报名编号"))
                        data += "<td style=\"vnd.ms-excel.numberformat:@\">" + row[column].ToString() + "</td>";
                    else 
                        data += "<td>" + row[column].ToString() + "</td>";
                }
                data += "</tr>";
            }
            data += "</table>";
        }
        return data;
    }

    public static void ExportDsToXls(Page page, string sql)
    {
        ExportDsToXls(page, "FileName", sql);
    }
    public static void ExportDsToXls(Page page, string fileName, string sql)
    {
        DataSet ds = DBUtil.GetDataSet(sql);
        if (ds != null) ExportDsToXls(page, fileName, ds);
    }
    public static void ExportDsToXls(Page page, DataSet ds)
    {
        ExportDsToXls(page, "FileName", ds);
    }
    public static void ExportDsToXls(Page page, string fileName, DataSet ds)
    {
        page.Response.Clear();
        page.Response.Buffer = true;
        page.Response.Charset = "GB2312";
        //page.Response.Charset = "UTF-8";
        page.Response.AppendHeader("Content-Disposition", "attachment;filename=" + fileName + System.DateTime.Now.ToString("_yyMMdd_hhmm") + ".xls");
        page.Response.ContentEncoding = System.Text.Encoding.GetEncoding("GB2312");//设置输出流为简体中文
        page.Response.ContentType = "application/ms-excel";//设置输出文件类型为excel文件。 
        page.EnableViewState = false;
        page.Response.Write(ExportTable(ds));
        page.Response.End();
    }
//style="vnd.ms-excel.numberformat:@" 可以去除自动科学计数法的困扰 
//输出为Table，能够最大限度的减少字段中数据对生成的文件格式的影响，在这里我没有处理数据中含有HTML标签的情况 在页面后台中，这样使用就可以了：
    protected void lbtnToExcel_Click(object sender, EventArgs e)
    {
        string strWhere = BuildSearchWhereString(); 
        string strOrder = this.hidOrderString.Value; 
        string sql = "SELECT 报名编号, 证件编号, 姓名, 考区考点, 报考类别, " 
            + "行政区划名称 AS 行政区划, 单位名称 AS 工作单位, 毕业学校名称, 毕业专业名称 AS 毕业专业, 毕业年月, " 
            + "通讯地址, 性别"
            + " from [VW报名]"; 
        if (!string.IsNullOrEmpty(strWhere)) sql += " where " + strWhere; 
        if (!string.IsNullOrEmpty(strOrder)) sql += " order by " + strOrder; 
        else sql += " order by [报考类别]";
        PageExport.ExportDsToXls(this.Page, "BaoMing", sql);
        dataBind();
    }

 //修改了这个函数：在导出1k条数据时，估计速度提高上百倍，数据量越大越明显，原理很简单，StringBuilder的性能和“+”的性能的区别。

public static string ExportTable(DataSet ds) 
{ 
StringBuilder sb = new StringBuilder(); 
//data = ds.DataSetName + "\n"; 
int count = 0; 

foreach (DataTable tb in ds.Tables) 
{ 
//data += tb.TableName + "\n"; 
sb.AppendLine("<meta http-equiv=\"Content-Type\" content=\"text/html; charset=gb2312\">"); 
sb.AppendLine("<table cellspacing=\"0\" cellpadding=\"5\" rules=\"all\" border=\"1\">"); 
//写出列名 
sb.AppendLine("<tr style=\"font-weight: bold; white-space: nowrap;\">"); 
foreach (DataColumn column in tb.Columns) 
{ 
sb.AppendLine("<td>" + column.ColumnName + "</td>"); 
} 
sb.AppendLine("</tr>"); 

//写出数据 
foreach (DataRow row in tb.Rows) 
{ 
sb.Append("<tr>"); 
foreach (DataColumn column in tb.Columns) 
{ 
if (column.ColumnName.Equals("证件编号") || column.ColumnName.Equals("报名编号")) 
sb.Append("<td style=\"vnd.ms-excel.numberformat:@\">" + row[column].ToString() + "</td>"); 
else 
sb.Append("<td>" + row[column].ToString() + "</td>"); 
} 
sb.AppendLine("</tr>"); 
count++; 
} 
sb.AppendLine("</table>"); 
} 

return sb.ToString(); 
} 

 

评论

1 楼 hautbbs 2011-11-28  

谢谢分享！