b/s web页面数据导出到Excel表，求例子

[w3c112]

b/s web页面数据导出到Excel表，求例子

[chenhaoying]

#region 导出数据列表 private void ExportExcel(string datatableHtml, string fileName) { Response.Clear(); Response.Buffer = true; Response.Charset = "GB2312"; //Response.Charset = "UTF-8"; Response.AppendHeader("Content-Disposition", "attachment;filename=" + HttpUtility.UrlEncode(fileName, System.Text.Encoding.UTF8) + ".xls"); Response.ContentEncoding = System.Text.Encoding.GetEncoding("GB2312");//设置输出流为简体中文 Response.ContentType = "application/ms-excel";//设置输出文件类型为excel文件。 EnableViewState = false; Response.Write(datatableHtml); Response.End(); } #endregion

[w3c112]

引用 1 楼 chenhaoying 的回复:

#region 导出数据列表 private void ExportExcel(string datatableHtml, string fileName) { Response.Clear(); Response.Buffer = true; Response.Charset = "GB2312"; //Response.Charset = "UTF-8"; Response.AppendHeader("Content-Disposition", "attachment;filename=" + HttpUtility.UrlEncode(fileName, System.Text.Encoding.UTF8) + ".xls"); Response.ContentEncoding = System.Text.Encoding.GetEncoding("GB2312");//设置输出流为简体中文 Response.ContentType = "application/ms-excel";//设置输出文件类型为excel文件。 EnableViewState = false; Response.Write(datatableHtml); Response.End(); } #endregion

datatableHtml 这个是用来做什么的？

[LoserOrLeader]

楼主可以去搜下导出CSV，CSV格式打开也是EXCEL 但是导出CSV比较简单，其实呢掌握他们的原理的话都不难的（这是废话，哈哈！）

[by_封爱]

http://bbs.csdn.net/topics/390830774 不客气..

[Hertz_liu]

参考这个地址 http://www.it165.net/pro/html/201305/5775.html 它是使用HTML导出的

[我说我行就是行]

protected void ExportExcel(DataTable dt)
{
    if (dt == null||dt.Rows.Count==0) return;
    Microsoft.Office.Interop.Excel.Application xlApp = new Microsoft.Office.Interop.Excel.Application();
 
    if (xlApp == null)
    {
        return;
    }
    System.Globalization.CultureInfo CurrentCI = System.Threading.Thread.CurrentThread.CurrentCulture;
    System.Threading.Thread.CurrentThread.CurrentCulture = new System.Globalization.CultureInfo("en-US");
    Microsoft.Office.Interop.Excel.Workbooks workbooks = xlApp.Workbooks;
    Microsoft.Office.Interop.Excel.Workbook workbook = workbooks.Add(Microsoft.Office.Interop.Excel.XlWBATemplate.xlWBATWorksheet);
    Microsoft.Office.Interop.Excel.Worksheet worksheet = (Microsoft.Office.Interop.Excel.Worksheet)workbook.Worksheets[1];
    Microsoft.Office.Interop.Excel.Range range;
    long totalCount = dt.Rows.Count;
    long rowRead = 0;
    float percent = 0;
    for (int i = 0; i < dt.Columns.Count; i++)
    {
        worksheet.Cells[1, i + 1] = dt.Columns[i].ColumnName;
        range = (Microsoft.Office.Interop.Excel.Range)worksheet.Cells[1, i + 1];
        range.Interior.ColorIndex = 15;
        range.Font.Bold = true;
    }
    for (int r = 0; r < dt.Rows.Count; r++)
    {
        for (int i = 0; i < dt.Columns.Count; i++)
        {
            worksheet.Cells[r + 2, i + 1] = dt.Rows[r][i].ToString();
        }
        rowRead++;
        percent = ((float)(100 * rowRead)) / totalCount;
    }
    xlApp.Visible = true;
}

调用方法

public void GenerateExcel()
{
    DataTable dt = new DataTable();
    dt.Columns.Add("Name", typeof(string));
    dt.Columns.Add("Age", typeof(string));
    DataRow dr = dt.NewRow();
    dr["Name"] = "spring";
    dr["Age"] = "20";
    dt.Rows.Add(dr);
    dt.AcceptChanges();
    ExportExcel(dt);
}

测试OK，，你可以试下，，

[w3c112]

引用 4 楼 diaodiaop 的回复:

http://bbs.csdn.net/topics/390830774 不客气..

var dt = ExcelHelper.GetDataTable(Server.MapPath("~/xls_tmp/2003.xls")); 这句会提示未能找到路径“C:\Users\Daxia\Desktop\yixie\GuangDongRuralCreditCooperatives\WebSite\Center\eMBS\xls_down\201408040458196523.xls”的一部分。

[w3c112]

引用 7 楼 dingzongyinnihao 的回复:

protected void ExportExcel(DataTable dt)
{
    if (dt == null||dt.Rows.Count==0) return;
    Microsoft.Office.Interop.Excel.Application xlApp = new Microsoft.Office.Interop.Excel.Application();
 
    if (xlApp == null)
    {
        return;
    }
    System.Globalization.CultureInfo CurrentCI = System.Threading.Thread.CurrentThread.CurrentCulture;
    System.Threading.Thread.CurrentThread.CurrentCulture = new System.Globalization.CultureInfo("en-US");
    Microsoft.Office.Interop.Excel.Workbooks workbooks = xlApp.Workbooks;
    Microsoft.Office.Interop.Excel.Workbook workbook = workbooks.Add(Microsoft.Office.Interop.Excel.XlWBATemplate.xlWBATWorksheet);
    Microsoft.Office.Interop.Excel.Worksheet worksheet = (Microsoft.Office.Interop.Excel.Worksheet)workbook.Worksheets[1];
    Microsoft.Office.Interop.Excel.Range range;
    long totalCount = dt.Rows.Count;
    long rowRead = 0;
    float percent = 0;
    for (int i = 0; i < dt.Columns.Count; i++)
    {
        worksheet.Cells[1, i + 1] = dt.Columns[i].ColumnName;
        range = (Microsoft.Office.Interop.Excel.Range)worksheet.Cells[1, i + 1];
        range.Interior.ColorIndex = 15;
        range.Font.Bold = true;
    }
    for (int r = 0; r < dt.Rows.Count; r++)
    {
        for (int i = 0; i < dt.Columns.Count; i++)
        {
            worksheet.Cells[r + 2, i + 1] = dt.Rows[r][i].ToString();
        }
        rowRead++;
        percent = ((float)(100 * rowRead)) / totalCount;
    }
    xlApp.Visible = true;
}

调用方法

public void GenerateExcel()
{
    DataTable dt = new DataTable();
    dt.Columns.Add("Name", typeof(string));
    dt.Columns.Add("Age", typeof(string));
    DataRow dr = dt.NewRow();
    dr["Name"] = "spring";
    dr["Age"] = "20";
    dt.Rows.Add(dr);
    dt.AcceptChanges();
    ExportExcel(dt);
}

测试OK，，你可以试下，，

你这个代码 哪个是设置单元格宽度的

[w3c112]

引用 4 楼 diaodiaop 的回复:

http://bbs.csdn.net/topics/390830774 不客气..

那个路径怎么设置？存储的

[chenhaoying]

引用 2 楼 w3c112 的回复:

[quote=引用 1 楼 chenhaoying 的回复:] #region 导出数据列表 private void ExportExcel(string datatableHtml, string fileName) { Response.Clear(); Response.Buffer = true; Response.Charset = "GB2312"; //Response.Charset = "UTF-8"; Response.AppendHeader("Content-Disposition", "attachment;filename=" + HttpUtility.UrlEncode(fileName, System.Text.Encoding.UTF8) + ".xls"); Response.ContentEncoding = System.Text.Encoding.GetEncoding("GB2312");//设置输出流为简体中文 Response.ContentType = "application/ms-excel";//设置输出文件类型为excel文件。 EnableViewState = false; Response.Write(datatableHtml); Response.End(); } #endregion

datatableHtml 这个是用来做什么的？[/quote] 这个datatableHtml 就是你根据你获取的数据生成的html字符串，例如：<table><tr><td>数据</td></tr></table>

[飘零_枫]

引用 8 楼 w3c112 的回复:

[quote=引用 4 楼 diaodiaop 的回复:] http://bbs.csdn.net/topics/390830774 不客气..

var dt = ExcelHelper.GetDataTable(Server.MapPath("~/xls_tmp/2003.xls")); 这句会提示未能找到路径“C:\Users\Daxia\Desktop\yixie\GuangDongRuralCreditCooperatives\WebSite\Center\eMBS\xls_down\201408040458196523.xls”的一部分。 [/quote] 你在C:\Users\Daxia\Desktop\yixie\GuangDongRuralCreditCooperatives\WebSite\Center\eMBS\下建一个xls_down文档就OK。跟随提示找路径 少了补上就行，建项目的时候你设置的存放路径与你所下载的NOPI不在同一个地方。