Generating an Excel file in ASP.NET [closed]

问题:

I am about to add a section to an ASP.NET app (VB.NET codebehind) that will allow a user to get data returned to them as an Excel file, which I will generate based on database data. While there are several ways of doing this, each has its own drawbacks. How would you return the data? I\'m looking for something that\'s as clean and straightforward as possible.

回答1:

CSV

Pros:

• Simple

Cons:

• It may not work in other locales or in different Excel configurations (i.e. List separator)
• Can\'t apply formatting, formulas, etc

HTML

Pros:

• Still pretty Simple
• Supports simple formating and formulas

Cons:

• You have to name the file as xls and Excel may warn you about opening a non native Excel file
• One worksheet per workbook

OpenXML (Office 2007 .XLSX)

Pros:

• Native Excel format
• Supports all Excel features
• Do not require an install copy of Excel
• Can generate Pivot tables
• Can be generated using open source project EPPlus

Cons:

• Limited compatibility outside Excel 2007 (shouldn\'t be a problem nowadays)
• Complicated unless you\'re using a third party component

SpreadSheetML (open format XML)

Pros:

• Simple compared to native Excel formats
• Supports most Excel features: formating, styles, formulas, multiple sheets per workbook
• Excel does not need to be installed to use it
• No third party libraries needed - just write out your xml
• Documents can be opened by Excel XP/2003/2007

Cons:

• Lack of good documentation
• Not supported in older versions of Excel (pre-2000)
• Write-only, in that once you open it and make changes from Excel it\'s converted to native Excel.

XLS (generated by third party component)

Pros:

• Generate native Excel file with all the formating, formulas, etc.

Cons:

• Cost money
• Add dependencies

COM Interop

Pros:

• Uses native Microsoft libraries
• Read support for native documents

Cons:

• Very slow
• Dependency/version matching issues
• Concurrency/data integrity issues for web use when reading
• Very slow
• Scaling issues for web use (different from concurrency): need to create many instances of heavy Excel app on the server
• Requires Windows
• Did I mention that it\'s slow?

回答2:

You can output the data as html table cells, stick a .xls or .xlsx extension on it, and Excel will open it as if it were a native document. You can even do some limited formatting and formula calculations this way, so it\'s much more powerful than CSV. Also, outputting an html table ought to be pretty easy to do from a web platform like ASP.Net ;)

If you need multiple worksheets or named worksheets within your Excel Workbook, you can do something similar via an XML schema called SpreadSheetML. This is not the new format that shipped with Office 2007, but something completely different that works as far back as Excel 2000. The easiest way to explain how it works is with an example:

<?xml version=\"1.0\"?>
<?mso-application progid=\"Excel.Sheet\"?> 
<Workbook xmlns=\"urn:schemas-microsoft-com:office:spreadsheet\"
        xmlns:o=\"urn:schemas-microsoft-com:office:office\"
        xmlns:x=\"urn:schemas-microsoft-com:office:excel\"
        xmlns:ss=\"urn:schemas-microsoft-com:office:spreadsheet\"
        xmlns:html=\"http://www.w3.org/TR/REC-html40\">
<DocumentProperties xmlns=\"urn:schemas-microsoft-com:office:office\">
      <Author>Your_name_here</Author>
      <LastAuthor>Your_name_here</LastAuthor>
      <Created>20080625</Created>
      <Company>ABC Inc</Company>
      <Version>10.2625</Version>
</DocumentProperties>
<ExcelWorkbook xmlns=\"urn:schemas-microsoft-com:office:excel\">
        <WindowHeight>6135</WindowHeight>
        <WindowWidth>8445</WindowWidth>
        <WindowTopX>240</WindowTopX>
        <WindowTopY>120</WindowTopY>
        <ProtectStructure>False</ProtectStructure>
        <ProtectWindows>False</ProtectWindows>
</ExcelWorkbook>

<Styles>
      <Style ss:ID=\"Default\" ss:Name=\"Normal\">
            <Alignment ss:Vertical=\"Bottom\" />
            <Borders />
            <Font />
            <Interior />
            <NumberFormat />
            <Protection />
      </Style>
</Styles>

<Worksheet ss:Name=\"Sample Sheet 1\">
<Table ss:ExpandedColumnCount=\"2\" x:FullColumns=\"1\" x:FullRows=\"1\" ID=\"Table1\">
<Column ss:Width=\"150\" />
<Column ss:Width=\"200\" />
<Row>
      <Cell><Data ss:Type=\"Number\">1</Data></Cell>
      <Cell><Data ss:Type=\"Number\">2</Data></Cell>
</Row>
<Row>
      <Cell><Data ss:Type=\"Number\">3</Data></Cell>
      <Cell><Data ss:Type=\"Number\">4</Data></Cell>
</Row>
<Row>
      <Cell><Data ss:Type=\"Number\">5</Data></Cell>
      <Cell><Data ss:Type=\"Number\">6</Data></Cell>
</Row>
<Row>
      <Cell><Data ss:Type=\"Number\">7</Data></Cell>
      <Cell><Data ss:Type=\"Number\">8</Data></Cell>
</Row>
</Table>
</Worksheet>

<Worksheet ss:Name=\"Sample Sheet 2\">
<Table ss:ExpandedColumnCount=\"2\" x:FullColumns=\"1\" x:FullRows=\"1\" ID=\"Table2\">
<Column ss:Width=\"150\" />
<Column ss:Width=\"200\" />
<Row>
      <Cell><Data ss:Type=\"String\">A</Data></Cell>
      <Cell><Data ss:Type=\"String\">B</Data></Cell>
</Row>
<Row>
      <Cell><Data ss:Type=\"String\">C</Data></Cell>
      <Cell><Data ss:Type=\"String\">D</Data></Cell>
</Row>
<Row>
      <Cell><Data ss:Type=\"String\">E</Data></Cell>
      <Cell><Data ss:Type=\"String\">F</Data></Cell>
</Row>
<Row>
      <Cell><Data ss:Type=\"String\">G</Data></Cell>
      <Cell><Data ss:Type=\"String\">H</Data></Cell>
</Row>
</Table>
</Worksheet>
</Workbook> 

回答3:

If coming from a DataTable:

public static void DataTabletoXLS(DataTable DT, string fileName)
{
    HttpContext.Current.Response.Clear();
    HttpContext.Current.Response.Charset = \"utf-16\";
    HttpContext.Current.Response.ContentEncoding = System.Text.Encoding.GetEncoding(\"windows-1250\");
    HttpContext.Current.Response.AddHeader(\"content-disposition\", string.Format(\"attachment; filename={0}.xls\", fileName));
    HttpContext.Current.Response.ContentType = \"application/ms-excel\";

    string tab = \"\";
    foreach (DataColumn dc in DT.Columns)
    {
        HttpContext.Current.Response.Write(tab + dc.ColumnName.Replace(\"\\n\", \"\").Replace(\"\\t\", \"\"));
        tab = \"\\t\";
    }
    HttpContext.Current.Response.Write(\"\\n\");

    int i;
    foreach (DataRow dr in DT.Rows)
    {
        tab = \"\";
        for (i = 0; i < DT.Columns.Count; i++)
        {
            HttpContext.Current.Response.Write(tab + dr[i].ToString().Replace(\"\\n\", \"\").Replace(\"\\t\", \"\"));
            tab = \"\\t\";
        }
        HttpContext.Current.Response.Write(\"\\n\");
    }
    HttpContext.Current.Response.End();
}

From a Gridview:

public static void GridviewtoXLS(GridView gv, string fileName)
{
    int DirtyBit = 0;
    int PageSize = 0;
    if (gv.AllowPaging == true)
    {
        DirtyBit = 1;
        PageSize = gv.PageSize;
        gv.AllowPaging = false;
        gv.DataBind();
    }

    HttpContext.Current.Response.Clear();
    HttpContext.Current.Response.Charset = \"utf-8\";
    HttpContext.Current.Response.ContentEncoding = System.Text.Encoding.GetEncoding(\"windows-1250\");
    HttpContext.Current.Response.AddHeader(
        \"content-disposition\", string.Format(\"attachment; filename={0}.xls\", fileName));
    HttpContext.Current.Response.ContentType = \"application/ms-excel\";

    using (StringWriter sw = new StringWriter())
    using (HtmlTextWriter htw = new HtmlTextWriter(sw))
    {
        //  Create a table to contain the grid
        Table table = new Table();

        //  include the gridline settings
        table.GridLines = gv.GridLines;

        //  add the header row to the table
        if (gv.HeaderRow != null)
        {
            Utilities.Export.PrepareControlForExport(gv.HeaderRow);
            table.Rows.Add(gv.HeaderRow);
        }

        //  add each of the data rows to the table
        foreach (GridViewRow row in gv.Rows)
        {
            Utilities.Export.PrepareControlForExport(row);
            table.Rows.Add(row);
        }

        //  add the footer row to the table
        if (gv.FooterRow != null)
        {
            Utilities.Export.PrepareControlForExport(gv.FooterRow);
            table.Rows.Add(gv.FooterRow);
        }

        //  render the table into the htmlwriter
        table.RenderControl(htw);

        //  render the htmlwriter into the response
        HttpContext.Current.Response.Write(sw.ToString().Replace(\"£\", \"\"));
        HttpContext.Current.Response.End();
    }

    if (DirtyBit == 1)
    {
        gv.PageSize = PageSize;
        gv.AllowPaging = true;
        gv.DataBind();
    }
}

private static void PrepareControlForExport(Control control)
{
    for (int i = 0; i < control.Controls.Count; i++)
    {
        Control current = control.Controls[i];
        if (current is LinkButton)
        {
            control.Controls.Remove(current);
            control.Controls.AddAt(i, new LiteralControl((current as LinkButton).Text));
        }
        else if (current is ImageButton)
        {
            control.Controls.Remove(current);
            control.Controls.AddAt(i, new LiteralControl((current as ImageButton).AlternateText));
        }
        else if (current is HyperLink)
        {
            control.Controls.Remove(current);
            control.Controls.AddAt(i, new LiteralControl((current as HyperLink).Text));
        }
        else if (current is DropDownList)
        {
            control.Controls.Remove(current);
            control.Controls.AddAt(i, new LiteralControl((current as DropDownList).SelectedItem.Text));
        }
        else if (current is CheckBox)
        {
            control.Controls.Remove(current);
            control.Controls.AddAt(i, new LiteralControl((current as CheckBox).Checked ? \"True\" : \"False\"));
        }

        if (current.HasControls())
        {
            Utilities.Export.PrepareControlForExport(current);
        }
    }
}

回答4:

This is a free wrapper around SpreadML--it works great.

http://www.carlosag.net/Tools/ExcelXmlWriter/

回答5:

Based on the answers given, and consultation with coworkers, it appears that the best solution is to generate either an XML file or HTML tables and push it down as an attachment. The one change recommended by my co-workers is that the data (i.e. the HTML tables) can be written directly to the Response object, thus eliminating the need to write out a file, which can be troublesome due to permissions problems, I/O contention, and ensuring that scheduled purging occurs.

Here\'s a snippet of the code... I haven\'t checked this yet, and I haven\'t supplied all the called code, but I think it represents the idea well.

    Dim uiTable As HtmlTable = GetUiTable(groupedSumData)

    Response.Clear()

    Response.ContentType = \"application/vnd.ms-excel\"
    Response.AddHeader(\"Content-Disposition\", String.Format(\"inline; filename=OSSummery{0:ddmmssf}.xls\", DateTime.Now))

    Dim writer As New System.IO.StringWriter()
    Dim htmlWriter As New HtmlTextWriter(writer)
    uiTable.RenderControl(htmlWriter)
    Response.Write(writer.ToString)

    Response.End()

回答6:

since Excel understands HTML you can just write the data out as an HTML table to a temp file with an .xls extension, get the FileInfo for the file, and blow it back using

Response.Clear();
Response.AddHeader(\"Content-Disposition\", \"attachment; filename=\" + fi.Name);
Response.AddHeader(\"Content-Length\", fi.Length.ToString());
Response.ContentType = \"application/octet-stream\";
Response.WriteFile(fi.FullName);
Response.End();

if you wanted to avoid the temp file, you could write to an in-memory stream and write the bytes back instead of using WriteFile

if the content-length header is omitted you could just write the html back directly, but this may not work correctly all the time in all browsers

回答7:

I personally prefer the XML method. I\'ll return the data from the database in a Dataset, save it to XMl, then I create an xslt file that contains a transformation rule that will format a proper document, and a simple XML transform will finish the job up. The best part of about this you can format cells, do conditional formatting, setup headers and footers, and even set print ranges.

回答8:

I\'ve done this a couple of times and each time the easiest way was to simply return a CSV (Comma Separated Value) file. Excel imports it perfectly, and it\'s relatively fast to do.

回答9:

we export data from a datagrid to excel all the time. Converting it to HTML then writing to an excel file

Response.ContentType = \"application/vnd.ms-excel\"
    Response.Charset = \"\"
    Response.AddHeader(\"content-disposition\", \"fileattachment;filename=YOURFILENAME.xls\")
    Me.EnableViewState = False
    Dim sw As System.IO.StringWriter = New System.IO.StringWriter
    Dim hw As HtmlTextWriter = New HtmlTextWriter(sw)
    ClearControls(grid)
    grid.RenderControl(hw)
    Response.Write(sw.ToString())
    Response.End()

The only gotcha with this method was that a lot of our grids had buttons or links in them so you need this too:

\'needed to export grid to excel to remove link button control and represent as text
Private Sub ClearControls(ByVal control As Control)
    Dim i As Integer
    For i = control.Controls.Count - 1 To 0 Step -1
        ClearControls(control.Controls(i))
    Next i

    If TypeOf control Is System.Web.UI.WebControls.Image Then
        control.Parent.Controls.Remove(control)
    End If

    If (Not TypeOf control Is TableCell) Then
        If Not (control.GetType().GetProperty(\"SelectedItem\") Is Nothing) Then
            Dim literal As New LiteralControl
            control.Parent.Controls.Add(literal)
            Try
                literal.Text = CStr(control.GetType().GetProperty(\"SelectedItem\").GetValue(control, Nothing))
            Catch
            End Try
            control.Parent.Controls.Remove(control)
        Else
            If Not (control.GetType().GetProperty(\"Text\") Is Nothing) Then
                Dim literal As New LiteralControl
                control.Parent.Controls.Add(literal)
                literal.Text = CStr(control.GetType().GetProperty(\"Text\").GetValue(control, Nothing))
                control.Parent.Controls.Remove(control)
            End If
        End If
    End If
    Return
End Sub

I found that somewhere, it works well.

回答10:

I recommend free opensource excel generation libruary which is based on OpenXML

It helped me several months ago.

回答11:

Here\'s a report that pulls from a stored procedure. The results are exported to Excel. It uses ADO instead of ADO.NET and the reason why is this line

oSheet.Cells(2, 1).copyfromrecordset(rst1)

It does most of the work and isn\'t available in ado.net.

‘Calls stored proc in SQL Server 2000 and puts data in Excel and ‘formats it

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click

        Dim cnn As ADODB.Connection
        cnn = New ADODB.Connection
        cnn.Open(\"Provider=SQLOLEDB;data source=xxxxxxx;\" & _
          \"database=xxxxxxxx;Trusted_Connection=yes;\")

        Dim cmd As New ADODB.Command


        cmd.ActiveConnection = cnn


        cmd.CommandText = \"[sp_TomTepley]\"
        cmd.CommandType = ADODB.CommandTypeEnum.adCmdStoredProc
        cmd.CommandTimeout = 0
        cmd.Parameters.Refresh()


        Dim rst1 As ADODB.Recordset
        rst1 = New ADODB.Recordset
        rst1.Open(cmd)

        Dim oXL As New Excel.Application
        Dim oWB As Excel.Workbook
        Dim oSheet As Excel.Worksheet

        \'oXL = CreateObject(\"excel.application\")
        oXL.Visible = True
        oWB = oXL.Workbooks.Add
        oSheet = oWB.ActiveSheet

        Dim Column As Integer
        Column = 1

        Dim fld As ADODB.Field
        For Each fld In rst1.Fields

            oXL.Workbooks(1).Worksheets(1).Cells(1, Column).Value = fld.Name
            oXL.Workbooks(1).Worksheets(1).cells(1, Column).Interior.ColorIndex = 15
            Column = Column + 1

        Next fld

        oXL.Workbooks(1).Worksheets(1).name = \"Tom Tepley Report\"
        oSheet.Cells(2, 1).copyfromrecordset(rst1)
        oXL.Workbooks(1).Worksheets(1).Cells.EntireColumn.AutoFit()


        oXL.Visible = True
        oXL.UserControl = True

        rst1 = Nothing

        cnn.Close()
        Beep()

    End Sub

回答12:

If you fill a GridView with data you can use this function to get the HTML formatted data, but indicating the browser it\'s an excel file.

 Public Sub ExportToExcel(ByVal fileName As String, ByVal gv As GridView)

        HttpContext.Current.Response.Clear()
        HttpContext.Current.Response.AddHeader(\"content-disposition\", String.Format(\"attachment; filename={0}\", fileName))
        HttpContext.Current.Response.ContentType = \"application/ms-excel\"

        Dim sw As StringWriter = New StringWriter
        Dim htw As HtmlTextWriter = New HtmlTextWriter(sw)
        Dim table As Table = New Table

        table.GridLines = gv.GridLines

        If (Not (gv.HeaderRow) Is Nothing) Then
            PrepareControlForExport(gv.HeaderRow)
            table.Rows.Add(gv.HeaderRow)
        End If

        For Each row As GridViewRow In gv.Rows
            PrepareControlForExport(row)
            table.Rows.Add(row)
        Next

        If (Not (gv.FooterRow) Is Nothing) Then
            PrepareControlForExport(gv.FooterRow)
            table.Rows.Add(gv.FooterRow)
        End If

        table.RenderControl(htw)

        HttpContext.Current.Response.Write(sw.ToString)
        HttpContext.Current.Response.End()

    End Sub


    Private Sub PrepareControlForExport(ByVal control As Control)

        Dim i As Integer = 0

        Do While (i < control.Controls.Count)

            Dim current As Control = control.Controls(i)

            If (TypeOf current Is LinkButton) Then
                control.Controls.Remove(current)
                control.Controls.AddAt(i, New LiteralControl(CType(current, LinkButton).Text))

            ElseIf (TypeOf current Is ImageButton) Then
                control.Controls.Remove(current)
                control.Controls.AddAt(i, New LiteralControl(CType(current, ImageButton).AlternateText))

            ElseIf (TypeOf current Is HyperLink) Then
                control.Controls.Remove(current)
                control.Controls.AddAt(i, New LiteralControl(CType(current, HyperLink).Text))

            ElseIf (TypeOf current Is DropDownList) Then
                control.Controls.Remove(current)
                control.Controls.AddAt(i, New LiteralControl(CType(current, DropDownList).SelectedItem.Text))

            ElseIf (TypeOf current Is CheckBox) Then
                control.Controls.Remove(current)
                control.Controls.AddAt(i, New LiteralControl(CType(current, CheckBox).Checked))

            End If

            If current.HasControls Then
                PrepareControlForExport(current)
            End If

            i = i + 1

        Loop

    End Sub

回答13:

Just avoid COM Interop via Microsoft.Office.Interop namespace. It is so damn slow and unreliable and unscalable. Not applicable for masochists.

回答14:

You can create nicely formatted Excel files using this library, quite easily: http://officehelper.codeplex.com/documentation.

Microsoft Office does not need to be installed on the webserver!

回答15:

CSV is easiest way. Most of the time it is linked to Excel. Otherwise you have to use the automation APIs or XML format. The APIs and XML are not that hard to use.

Information about generating XML for Excel

回答16:

I either go the CSV route (as described above), or more often these days, I use Infragistics NetAdvantage to generate the file. (The very vast majority of the time where Infragistics is in play, we\'re just exporting an existing UltraWebGrid, which is essentially a one-LOC solution unless extra formatting tweaks are needed. We could manually generate an Excel/BIFF file as well, but there\'s rarely a need to.)

回答17:

man, in .net i guess you could have a component that could do that, but in classic asp I have already done it creating an html table and changing the mime tipe of the page to vnd/msexcel. I guess that if you use a gridview and change the mime type maybe it should work, because the gridview is an html table.

回答18:

The only bulletproof way of avoiding the \"It looks like these numbers are stored as text\" green triangles is to use the Open XML format. It is worth using it, just to avoid the inevitable green triangles.

回答19:

The best method i\'ve seen for excel reports is to write out the data in XML with a XML extension and stream it to clients with the correct content type. (application/xls)

This works for any report which requires basic formating, and allows you to compare against existing spreadsheets by using text comparison tools.

回答20:

Assuming this is for an intranet, where you can set permissions and mandate IE, you can generate the workbook client side with JScript/VBScript driving Excel. This gives you native Excel formatting, without the hassle of trying to automate Excel on the server.

I\'m not sure I\'d really recommend this approach anymore except in fairly niche scenarios, but it was fairly common during the classic ASP heydays.

回答21:

You can of course always go for a third party components. Personally, I have had a good experience with Spire.XLS http://www.e-iceblue.com/xls/xlsintro.htm

The component is pretty easy to use within your application:

        Workbook workbook = new Workbook();

        //Load workbook from disk.
        workbook.LoadFromFile(@\"Data\\EditSheetSample.xls\");
        //Initailize worksheet
        Worksheet sheet = workbook.Worksheets[0];

        //Writes string
        sheet.Range[\"B1\"].Text = \"Hello,World!\";
        //Writes number
        sheet.Range[\"B2\"].NumberValue = 1234.5678;
        //Writes date
        sheet.Range[\"B3\"].DateTimeValue = System.DateTime.Now;
        //Writes formula
        sheet.Range[\"B4\"].Formula = \"=1111*11111\";

        workbook.SaveToFile(\"Sample.xls\");

回答22:

One of the problems I\'ve ran across using one of the solutions suggested above which are similar to this answer is that if you push the content out as an attachment (what I\'ve found to be the cleanest solution for non-ms browsers), then open it in Excel 2000-2003, its type is an \"Excel Web Page\" and not a native Excel document.

Then you have to explain to users how to use \"Save as type\" from within Excel to convert it to an Excel document. This is a pain if users need to edit this document and then re-upload it to your site.

My recommendation is to use CSV. It\'s simple and if users do open it from within Excel, Excel at least prompts them to save it in its native format.

回答23:

I would just create a CSV file based on the data, because I see that as the cleanest, and Excel has good support for it. But if you require a more flexible format, I\'m sure there\'s some 3rd party tools for generating real excel files.

回答24:

Here\'s a solution the streams the datatable out as a CSV. Fast, clean, and easy, and it handles commas in the input.

public static void ExportToExcel(DataTable data, HttpResponse response, string fileName)
{
    response.Charset = \"utf-8\";
    response.ContentEncoding = System.Text.Encoding.GetEncoding(\"windows-1250\");
    response.Cache.SetCacheability(HttpCacheability.NoCache);
    response.ContentType = \"text/csv\";
    response.AddHeader(\"Content-Disposition\", \"attachment; filename=\" + fileName);

    for (int i = 0; i < data.Columns.Count; i++)
    {
       response.Write(data.Columns[i].ColumnName);
       response.Write(i == data.Columns.Count - 1 ? \"\\n\" : \",\");
    }        
    foreach (DataRow row in data.Rows)
    {
        for (int i = 0; i < data.Columns.Count; i++)
        {
            response.Write(String.Format(\"\\\"{0}\\\"\", row[i].ToString()));
            response.Write(i == data.Columns.Count - 1 ? \"\\n\" : \",\");
        }
    }

    response.End();
}

回答25:

just created a function to export from a web form C# to excel hope it helps others

    public void ExportFileFromSPData(string filename, DataTable dt)
    {
        HttpResponse response = HttpContext.Current.Response;

        //clean up the response.object
        response.Clear();
        response.Buffer = true;
        response.Charset = \"\";

        // set the response mime type for html so you can see what are you printing 
        //response.ContentType = \"text/html\";
        //response.AddHeader(\"Content-Disposition\", \"attachment;filename=test.html\");

        // set the response mime type for excel
        response.ContentType = \"application/vnd.ms-excel\";
        response.AddHeader(\"Content-Disposition\", \"attachment;filename=\\\"\" + filename + \"\\\"\");
        response.ContentEncoding = System.Text.Encoding.UTF8;
        response.BinaryWrite(System.Text.Encoding.UTF8.GetPreamble());

        //style to format numbers to string
        string style = @\"<style> .text { mso-number-format:\\@; } </style>\";
        response.Write(style);

        // create a string writer
        using (StringWriter sw = new StringWriter())
        {
            using (HtmlTextWriter htw = new HtmlTextWriter(sw))
            {
                // instantiate a datagrid
                GridView dg = new GridView();
                dg.DataSource = dt;
                dg.DataBind();

                foreach (GridViewRow datarow in dg.Rows)
                {
                    //format specific cell to be text 
                    //to avoid 1.232323+E29 to get 1232312312312312124124
                    datarow.Cells[0].Attributes.Add(\"class\", \"text\");
                }

                dg.RenderControl(htw);
                response.Write(sw.ToString());
                response.End();
            }
        }
     }

回答26:

If you have to use Excel instead of a CSV file you will need to use OLE automation on an Excel instance one the server. The easiest way to do this is to have a template file and programatically fill it in with the data. You save it to another file.

Tips:

• Don\'t do it interactively. Have the user kick off the process and then post a page with the link to the file. This mitigates potential performance issues while the spreadsheet is generated.
• Use the template as I described before. It makes it easier to modify it.
• Make sure that Excel is set to not pop up dialogs. On a web server this will hang the whole excel instance.
• Keep the Excel instance on a separate server, preferably behind a firewall, so it is not exposed as a potential security hole.
• Keep an eye on resource usage. Generating a spreadhseet over the OLE automation interface (the PIA\'s are just shims over this) is a fairly heavyweight process. If you need to scale this to high data volumes you may need to be somewhat clever with your architecture.

Some of the \'use mime-types to trick excel into opening HTML table\' approaches would work if you don\'t mind the format of the file being a bit basic. These approaches also fob the CPU heavy work off onto the client. If you want fine-graned control over the format of the spreadsheet you will probably have to use Excel itself to generate the file as described above.