一个很实用的将DataTable数据导出到excel的示例

[sleman]

如下代码，求救在asp.net中如何将导出的EXCEL让用户自己选择路径，而不是将路径写死？
保存路径如下面红色代码部分。




#region 导出为EXCEL

/// <summary>
/// 将DataTable或DataSet导出为Excel
/// 修改 2012/11/6
/// </summary>
/// <param name="dt">需要导出的DataTable,DataSet或GridView</param>
/// <param name="AbosultedFilePath">Excel的绝对路径</param>
/// <returns>是否成功</returns>
public static bool ExportToExcel(System.Data.DataTable dt, string AbosultedFilePath)
{
if (dt == null)
return false;
// 创建Excel应用程序对象
Microsoft.Office.Interop.Excel.Application excApp = new Microsoft.Office.Interop.Excel.Application();
Microsoft.Office.Interop.Excel.Workbook workBook = excApp.Workbooks.Add(Microsoft.Office.Interop.Excel.XlWBATemplate.xlWBATWorksheet);
Microsoft.Office.Interop.Excel.Worksheet workSeet = workBook.Worksheets[1]; //取得sheet1
Microsoft.Office.Interop.Excel.Range range = null;
int tableCount = dt.Rows.Count;
int rowRead = 0;
float percent = 0;
for (int i = 0; i < dt.Columns.Count; i++)
{
workSeet.Cells[1, i + 1] = dt.Columns[i].ColumnName;

//设置标题的样式
range = (Microsoft.Office.Interop.Excel.Range)workSeet.Cells[1, i + 1];
range.Font.Bold = true; //粗体
range.Font.Size = "12";//字体大小
range.Font.Name = "宋体";
range.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter; //居中
range.BorderAround(Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous, Microsoft.Office.Interop.Excel.XlBorderWeight.xlThin, Microsoft.Office.Interop.Excel.XlColorIndex.xlColorIndexAutomatic, null); //背景色
range.EntireColumn.AutoFit(); //自动设置列宽
range.EntireRow.AutoFit(); //自动设置行高
}
for (int r = 0; r < dt.Rows.Count; r++)
{
for (int c = 0; c < dt.Columns.Count; c++)
{
//写入内容
workSeet.Cells[r + 2, c + 1] = "'" + dt.Rows[r][c].ToString();
//设置样式
range = (Microsoft.Office.Interop.Excel.Range)workSeet.Cells[r + 2, c + 1];
range.Font.Size = 9; //字体大小
range.BorderAround(Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous, Microsoft.Office.Interop.Excel.XlBorderWeight.xlThin, Microsoft.Office.Interop.Excel.XlColorIndex.xlColorIndexAutomatic, null); //加边框
range.EntireColumn.AutoFit(); //自动调整列宽
}
rowRead++;
percent = ((float)(100 * rowRead)) / tableCount;
System.Windows.Forms.Application.DoEvents();
}
range.Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlInsideHorizontal].Weight = Microsoft.Office.Interop.Excel.XlBorderWeight.xlThin;
if (dt.Columns.Count > 1)
{
range.Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlInsideVertical].Weight = Microsoft.Office.Interop.Excel.XlBorderWeight.xlThin;
}
try
{
workBook.Saved = true;
workBook.SaveCopyAs(AbosultedFilePath); }
catch { }
workBook.Close();
if (excApp != null)
{
excApp.Workbooks.Close();
excApp.Quit();
int generation = System.GC.GetGeneration(excApp);
System.Runtime.InteropServices.Marshal.ReleaseComObject(excApp);
excApp = null;
System.GC.Collect(generation);
}
GC.Collect(); //强行销毁
#region 强行杀死最近打开的Excel进程
System.Diagnostics.Process[] excelProc = System.Diagnostics.Process.GetProcessesByName("EXCEL");
System.DateTime startTime = new DateTime();
int m, killID = 0;
for (m = 0; m < excelProc.Length; m++)
{
if (startTime < excelProc[m].StartTime)
{
startTime = excelProc[m].StartTime;
killID = m;
}
}
if (excelProc[killID].HasExited == false)
{
excelProc[killID].Kill();
}
#endregion
return true;
}
#endregion

[hch126163]

先分清楚 服务器段和客户端

[sleman]

然后呢？请写实现代码！～

[hch126163]

设置响应头是 2进制文件 客户端自己会下载，下载是，自己会选择 保存路径

[q107770540]

导出前，弹出个框让用户选择导出路径，然后再传给方法： ExportToExcel ASP。NET提供了现成的控件

[q107770540]

private void SaveFileDialog() { //string localFilePath, fileNameExt, newFileName, FilePath; SaveFileDialog saveFileDialog1 = new SaveFileDialog(); //设置文件类型 saveFileDialog1.Filter = " excel files(*.xls)|*.xls"; //设置默认文件类型显示顺序 saveFileDialog1.FilterIndex = 2; //保存对话框是否记忆上次打开的目录 saveFileDialog1.RestoreDirectory = true; //点了保存按钮进入 if (saveFileDialog1.ShowDialog() == DialogResult.OK) { DataTable dt=(DataTable)this.DataGridView.DataSource; ExportToExcel(dt,saveFileDialog1.FileName); } }

[sleman]

楼上的哥们，你那是winfrom吧！

[残月照我心]

EXcel的导入和导出例子（用之前要先添加引用Microsoft.Office.Interop.Excel）:

<%@ Page Language="C#" AutoEventWireup="true" CodeFile="Default.aspx.cs" Inherits="_Default" %>

<!DOCTYPE html>

<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server"  id="Head1">
<meta http-equiv="Content-Type" content="text/html; charset=utf-8"/>
    <title></title>
</head>
<body>  
    <form id="form1" runat="server">
    <div>
    <input id="File1" type="file" runat="server" />
        <asp:Button ID="Button1" runat="server" Text="导入" OnClick="btnUpload_Click" /><br />
                 <asp:Button ID="cmdOpen" runat="server" Text="在线打开" CommandName="open" OnCommand="Button_Click" /> 
            <asp:Button ID="cmdSave"
                runat="server" Text="本地保存" CommandName="save" OnCommand="Button_Click" />
            <asp:DropDownList ID="listType" runat="server">
                <asp:ListItem Value="excel">Excel</asp:ListItem>
                <asp:ListItem Value="word">Word</asp:ListItem>
            </asp:DropDownList> 
        <br />
        数据源:
        <asp:GridView ID="GridView1" runat="server">
    </asp:GridView>
    </div>
    </form>
</body>
</html>

using System;
using System.Data;
using System.Data.OleDb;
using System.Data.SqlClient;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Data.Sql;
using System.Reflection;
using System.IO;
using Microsoft.Office.Interop.Excel;

public partial class _Default : System.Web.UI.Page
{
    protected void Page_Load(object sender, EventArgs e)
    {
        //FillGridView();
    }
    /**/
    /// <summary>
    /// 实现excel中的数据导入
    /// </summary>
    /// <param name="sender"></param>
    /// <param name="e"></param>
    protected void btnUpload_Click(object sender, EventArgs e)
    {
        if (File1.Value == string.Empty || File1.Value == "")
        {
            Response.Write("<Script>alert('选择路径');</Script>");
        }
        else
        {
           
            FileInfo file = new FileInfo(File1.PostedFile.FileName.ToString());
            string sConnectionString;
            string extension = file.Extension;
            switch (extension)
            {
                case ".xls":
                    sConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + file + ";Extended Properties='Excel 8.0;HDR=Yes;IMEX=1;'";
                    break;
                case ".xlsx":
                    sConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + file + ";Extended Properties='Excel 12.0;HDR=Yes;IMEX=1;'";
                    break;
                default:
                    sConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + file + ";Extended Properties='Excel 8.0;HDR=Yes;IMEX=1;'";
                    break;
            }
            OleDbConnection objConn = new OleDbConnection(sConnectionString);
            objConn.Open();
            OleDbDataAdapter objAdapter1 = new OleDbDataAdapter("select * from [Sheet1$]", objConn);
            DataSet objDataset1 = new DataSet();
            objAdapter1.Fill(objDataset1, "XLData");
            GridView1.DataSource = objDataset1.Tables[0];
            GridView1.DataBind();
            objConn.Close();

        }
    }
    private void OutPut(string fileType, string strType)
    {
        Response.Clear();
        Response.Buffer = true;
        Response.Charset = "GB2312";
        Response.AppendHeader("Content-Disposition", fileType);
        Response.ContentType = strType;
        this.EnableViewState = false;
        System.IO.StringWriter swOut = new System.IO.StringWriter();
        HtmlTextWriter hTw = new HtmlTextWriter(swOut);
        GridView1.RenderControl(hTw);
        Response.Write(swOut.ToString());
        Response.End();
    }
    protected void Button_Click(object sender, CommandEventArgs e)
    {
        switch (e.CommandName)
        {
            case "save":
                switch (listType.SelectedValue)
                {
                    case "excel":
                        OutPut("attachment;filename=out.xls", "application/ms-excel");
                        break;
                    case "word":
                        OutPut("attachment;filename=out.doc", "application/ms-word");
                        break;
                }
                break;
            case "open":
                switch (listType.SelectedValue)
                {
                    case "excel":
                        OutPut("online;filename=out.xls", "application/ms-excel");
                        break;
                    case "word":
                        OutPut("online;filename=out.doc", "application/ms-word");
                        break;
                }
                break;
        }
    }
    public override void VerifyRenderingInServerForm(Control control)
    {
    }
}

[sleman]

、

引用 3 楼 hch126163 的回复:

设置响应头是 2进制文件 客户端自己会下载，下载是，自己会选择 保存路径

我也是想要这个结果，可是亲爱的你看见了。 这里是保存到一个路径，不是保存到流中。 workBook.SaveCopyAs(AbosultedFilePath);

[peng2739956]

不就是一个 DataTable 导出为EXCEl么 完全不需要用 工作簿。

 /// <summary>
    /// DataTable通过流导出Excel
    /// </summary>
    /// <param name="ds">数据源DataSet或者DataTable</param>
    /// <param name="columns">DataTable中列对应的列名(可以是中文),若为null则取DataTable中的字段名</param>
    /// <param name="fileName">保存文件名(例如：a.xls)</param>
    /// <returns></returns>
    public bool StreamExport(DataTable dt, ArrayList columns, string fileName, System.Web.UI.Page pages)
    {
        if (dt.Rows.Count > 65535) //总行数大于Excel的行数
        {
            throw new Exception("预导出的数据总行数大于excel的行数");
        }
        if (string.IsNullOrEmpty(fileName)) return false;
        StringBuilder content = new StringBuilder();
        StringBuilder strtitle = new StringBuilder();
        content.Append("<html xmlns:o='urn:schemas-microsoft-com:office:office' xmlns:x='urn:schemas-microsoft-com:office:excel' xmlns='http://www.w3.org/TR/REC-html40'>");
        content.Append("<head><title></title><meta http-equiv='Content-Type' content=\"text/html; charset=gb2312\">");
        //注意：[if gte mso 9]到[endif]之间的代码，用于显示Excel的网格线，若不想显示Excel的网格线，可以去掉此代码
        content.Append("<!--[if gte mso 9]>");
        content.Append("<xml>");
        content.Append(" <x:ExcelWorkbook>");
        content.Append("  <x:ExcelWorksheets>");
        content.Append("   <x:ExcelWorksheet>");
        content.Append("    <x:Name>Sheet1</x:Name>");
        content.Append("    <x:WorksheetOptions>");
        content.Append("      <x:Print>");
        content.Append("       <x:ValidPrinterInfo />");
        content.Append("      </x:Print>");
        content.Append("    </x:WorksheetOptions>");
        content.Append("   </x:ExcelWorksheet>");
        content.Append("  </x:ExcelWorksheets>");
        content.Append("</x:ExcelWorkbook>");
        content.Append("</xml>");
        content.Append("<![endif]-->");
        content.Append("</head><body><table style='border-collapse:collapse;table-layout:fixed;'><tr>");
        if (columns != null)
        {
            for (int i = 0; i < columns.Count; i++)
            {
                if (columns[i] != null && columns[i] != "")
                {
                    content.Append("<td><b>" + columns[i] + "</b></td>");
                }
                else
                {
                    content.Append("<td><b>" + dt.Columns[i].ColumnName + "</b></td>");
                }
            }
        }
        else
        {
            for (int j = 0; j < dt.Columns.Count; j++)
            {
                content.Append("<td><b>" + dt.Columns[j].ColumnName + "</b></td>");
            }
        }
        content.Append("</tr>\n");
        for (int j = 0; j < dt.Rows.Count; j++)
        {
            content.Append("<tr>");
            for (int k = 0; k < dt.Columns.Count; k++)
            {
                object obj = dt.Rows[j][k];
                Type type = obj.GetType();
                if (type.Name == "Int32" || type.Name == "Single" || type.Name == "Double" || type.Name == "Decimal")
                {
                    double d = obj == DBNull.Value ? 0.0d : Convert.ToDouble(obj);
                    if (type.Name == "Int32" || (d - Math.Truncate(d) == 0))
                        content.AppendFormat("<td style='vnd.ms-excel.numberformat:#,##0'>{0}</td>", obj);
                    else
                        content.AppendFormat("<td style='vnd.ms-excel.numberformat:#,##0.00'>{0}</td>", obj);
                }
                else
                    content.AppendFormat("<td style='vnd.ms-excel.numberformat:@'>{0}</td>", obj);
            }
            content.Append("</tr>\n");
        }
        content.Append("</table></body></html>");
        content.Replace(" ", "");
        pages.Response.Clear();
        pages.Response.Buffer = true;
        pages.Response.ContentType = "application/ms-excel";  //"application/ms-excel";
        pages.Response.Charset = "UTF-8";
        pages.Response.ContentEncoding = System.Text.Encoding.UTF7;
        fileName = System.Web.HttpUtility.UrlEncode(fileName, System.Text.Encoding.UTF8);
        pages.Response.AppendHeader("Content-Disposition", "attachment; filename=" + fileName);
        pages.Response.Write(content.ToString());
        //pages.Response.End();  //注意，若使用此代码结束响应可能会出现“由于代码已经过优化或者本机框架位于调用堆栈之上,无法计算表达式的值。”的异常。
        HttpContext.Current.ApplicationInstance.CompleteRequest(); //用此行代码代替上一行代码，则不会出现上面所说的异常。
        return true;
    }

[flyingpigs3]

贴个更简单的: using Acey.ExcelX; // public void ExportDataTableToExcel(DataTable dt) { IWorkbook workbook = ExcelxApplication.CreateWorkbook(); IWorksheet worksheet = workbook.Worksheets[0]; worksheet.ImportDataTable(dt, true, "A1"); workbook.SaveToHttpResponse(response, "book1.xls", false);//输出到网页 //或者 //workbook.SaveAs(@"c:\book1.xls", FileFormat.Excel97To2003);//保存到硬盘 } 原文:http://www.cnblogs.com/happyfish78/

[poiu710]

引用 9 楼 peng2739956 的回复:

不就是一个 DataTable 导出为EXCEl么 完全不需要用 工作簿。 C# code?12345678910111213141516171819202122232425262728293031323334353637383940414243444546474849505152535455565758596061626364656667686970717273747……

这个好使