Excel download library in asp.net?

Question

Do we have any free 3rd party library that we can use in asp.net for download Excel sheets? Please provide me some.

Answer 1

Microsoft provides the Interop for programmatic creation of excel documents. You may also choose to buy Telerik controls which offers a gridview like control with save functionality that will save to Excel formats. I haven't done this in years so the details aren't crystal clear, but you can contact their support for more information.

Answer 2

I use and like NPOI: http://npoi.codeplex.com/

Answer 3

You may not need a third-party tool just to retrieve data from a database and export it to an Excel file. This code will generate a single worksheet Excel file from a DataView using HTML to make it look pretty.

You'll need to retrieve the data in to a DataSet dstExcel containing one DataTable which has the data you want in the Excel spreadsheet.

A button click event on the web page calls: GenerateExcelFile(dstExcel, fileName );

Here is the code-behind:

private void GenerateExcelFile(DataSet dst, string fileName)
    {
        // Clear any current output from the buffer.
        Response.Clear();

        // "Content-Disposition" & "attachment;filename=" are used to specify the default filename for the downloaded file.
      Response.AddHeader("Content-Disposition", "attachment;filename=" + fileName);
        Response.ContentType = "application/vnd.ms-excel";

        DataView dvw = dst.Tables[0].DefaultView;

        if ((dvw != null) && (dvw.Table.Rows.Count > 0))
        {
            // We're exporting an HTML table.
            Table tbl = ConvertDataViewToHTMLTable(dvw);

            StringWriter sw = new StringWriter();
            HtmlTextWriter hw = new HtmlTextWriter(sw);
            tbl.RenderControl(hw);
            Response.Write(sw.ToString());
            Response.End();
        }
    }


    private Table ConvertDataViewToHTMLTable(DataView dvw)
    {
        Table tbl = new Table();
        TableRow trw;
        TableCell tcl;
        Label lbl;
        DataColumn col;

        tbl.BorderColor = Color.Black;
        tbl.BorderWidth = Unit.Pixel(1);
        tbl.BorderStyle = BorderStyle.Solid;

        // Begin with a table row containing column names.
        trw = new TableRow();

        for (int i = 0; i < dvw.Table.Columns.Count; i++)
        {
            col = dvw.Table.Columns[i];

            // Add column name.
            lbl = new Label();
            lbl.Text = col.ColumnName;

            tcl = new TableCell();
            tcl.Controls.Add(lbl);

            tcl.BackColor = Color.MediumSeaGreen;
            tcl.ForeColor = Color.PaleGoldenrod;
            tcl.HorizontalAlign = HorizontalAlign.Center;
            tcl.Style["Font"] = "Tahoma";
            tcl.Style["Font-Weight"] = "Bold";

            trw.Controls.Add(tcl);
        }

        tbl.Controls.Add(trw);

        // Add records containg row data.
        DataRow row;
        for (int i = 0; i < dvw.Table.Rows.Count; i++)
        {
            row = dvw.Table.Rows[i];

            trw = new TableRow();

            for (int j = 0; j < dvw.Table.Columns.Count; j++)
            {
                col = dvw.Table.Columns[j];

                lbl = new Label();
                lbl.Text = row[col.ColumnName].ToString();

                tcl = new TableCell();
                tcl.Controls.Add(lbl);
                tcl.BorderColor = Color.LightGray;
                tcl.BorderWidth = Unit.Pixel(1);
                tcl.Style["Font"] = "Tahoma";

                trw.Controls.Add(tcl);

            }
            tbl.Controls.Add(trw);
        }

        return tbl;
    }

Answer 4

You can use the Report Definition Language. My method was a bit of hack but it does the job and without the need of third party assemblies. Drag a report viewer control (http://msdn.microsoft.com/en-us/library/ms251670(VS.80).aspx) on to your designer. Go to the markup and wrap the report viewer in a div element with the style.display attribute set to none.

You can then programmatically generate the Excel file (in my example I outputted a PDF and needed to perform impersonation because the outfile was written to a Doc server):

            Dim aa As AliasAccount = New AliasAccount("ASP.NET", "Uiop_4567")

            ' Begin Impersonation
            aa.BeginImpersonation()
            iRetVal = ExportCoverLetter()
            If iRetVal = 0 Then



                Dim bytes() As Byte = report.Render( _
                            "PDF", Nothing, sMimeType, sEncoding, sFileNameExtension, _
                            sStreamids, warnings)

                Using fs As FileStream = New FileStream(sApplicationPath, FileMode.Create)
                    fs.Write(bytes, 0, bytes.Length)
                End Using


                ' End of Impersonation
                aa.EndImpersonation()

                ' success
                Return 0
            Else
                Return -1
            End If

Once the file is generated you need to output it somehow. I used a AjaxControlKit.Modal Pop Up Extender. I tied the TargetControlID property to a hidden button and set the PopUpControlID to a asp panel which display a link saying View PDF:

       <asp:ImageButton ID="btnSearch2" runat="server" 
           ImageUrl="~/images/buttons/btnSearch.jpg" 
            AlternateText="Search" style="display:none;"/>    


    <asp:Panel ID="panSendPDFToClient" runat="server" Height="100px" Width="300px" BorderStyle="Solid" CssClass="ModalPopup" style="display:none;background-color:White">
         <table width="100%">
         <tr><td align = "center" style="width: 497px"><asp:Label style="width:inherit; text-align:center; color:Black; font-weight:bold;"  ID = "hlbl" Text = "Please click View PDF to download file." runat= "server"></asp:Label></td></tr>
         <tr>
             <td  class="formtext" style="height: 23px; width: 497px;" align="center" valign="middle">
                            <a id="lkSendPdfToClient" runat="server" 
                            href="sendpdftoclient.aspx" 
                            target="_blank" style ="font-size:12pt;" onclick="return lkSendPdfToClient_onclick()"
                            >View PDF</a>
                            <br />
           </td>
        </tr>  
        <tr><td align = "center" style="width: 497px"><asp:Button ID="btnCancel" runat="server" Text="Cancel" /></td></tr>         
   </table>
   <br/>

</asp:Panel>

When the user clicks View PDF it pops up a new window. The Document Path can be passed in via query string or session. In the new window obtain the Document Path, clear the HTTPContext buffer, change the content type and attach the file.

Private Function SendPDFToClient(ByVal sPDFfilePath As String, ByVal sFileName As String, ByRef oReturnException As System.Exception) As Integer
        ' variables
        Dim oMethodBase As MethodBase = MethodBase.GetCurrentMethod()
        Dim METHOD_NAME As String = oMethodBase.DeclaringType.ToString() & "." & oMethodBase.Name.ToString()


        Try


            Dim aa As AliasAccount = New AliasAccount("ASP.NET", "Uiop_4567")

            ' Begin Impersonation
            aa.BeginImpersonation()


            Dim fs As System.IO.FileStream

            Dim br As System.IO.BinaryReader

            fs = New System.IO.FileStream(sPDFfilePath, System.IO.FileMode.Open)

            br = New System.IO.BinaryReader(fs)


            Dim oByteArray As Byte() = br.ReadBytes(fs.Length - 1)

            br.Close()


            ' End of Impersonation
            aa.EndImpersonation()





            lblHiddenFileExist.Text = "File Exist=" & System.IO.File.Exists(sPDFfilePath) & ControlChars.NewLine _
                                        & " ByteArray Length=" & oByteArray.Length().ToString()


            HttpContext.Current.Response.Buffer = True
            HttpContext.Current.Response.Clear()
            HttpContext.Current.Response.ClearContent()
            HttpContext.Current.Response.ClearHeaders()
            HttpContext.Current.Response.ContentType = "application/pdf"
            HttpContext.Current.Response.AddHeader("Content-Disposition", "attachment;filename=" & sFileName)
            HttpContext.Current.Response.BinaryWrite(oByteArray)
            HttpContext.Current.Response.Flush()
            HttpContext.Current.Response.Close()



            ' success
            Return 0
        Catch exThreadAbort As Threading.ThreadAbortException
            ' do nothing
        Catch ex As Exception
            ' failure
            ex.Data.Add("METHOD_NAME", METHOD_NAME)
            oReturnException = ex
            Return -1
        End Try
    End Function

The end result is a window prompting the user if they want to save or open the document. Hope this helps.

Answer 5

EPPlus is really good for creating Excel files on the server.