Writing an Excel file in EPPlus

Question

I have been stuck on this for days and despite all of the help out there  none of these solutions have been working for me  What I want to do is create an excel file using the EPPlus library with some basic data in it that I am pulling from a stored procedure  This is the code that I have in my ExportDocument cs file    public static ExcelPackage CreateExcelDocument int    arr        String path     D  temp testsheet3 xlsx         FileInfo newFile   null        if   File Exists path      testsheet2 xlsx        newFile   new FileInfo path      testsheet2 xlsx        else         return newFile        using  ExcelPackage package   new ExcelPackage                  ExcelWorksheet ws   package Workbook Worksheets Add  testsheet            ws Cells  B1   Value    Number of Used Agencies           ws Cells  C1   Value    Active Agencies           ws Cells  D1   Value    Inactive Agencies           ws Cells  E1   Value    Total Hours Volunteered           ws Cells  B1 E1   Style Font Bold   true           int x   2          char pos    B           foreach  object o in arr                        String str   pos   x ToString                ws Cells str  Value   o ToString                if  pos  gt   E                                 pos    B                   x                              pos                      package Save            return package            All the commented code is different things that I have found on the internet to try  Note that this a school organization and we are not using MVC  I am then using the code behind file to pull this method like this    protected void GenerateReport Object o  EventArgs e        Session  reportSession     ddReport SelectedItem Value ToString        int    arr   new int  ReportRepository GetAgencyCounts   Count       ReportRepository GetAgencyCounts   CopyTo arr  0        ExcelPackage pck   ExportDocument CreateExcelDocument arr         try               byte    data   ExportDocument CreateExcelDocument arr  GetAsByteArray            Response Clear            Response Buffer   true          Response BinaryWrite data           Response AddHeader  content-length   data Length ToString             Response ContentType    application vnd openxmlformats-officedocument spreadsheetml sheet           Response Flush            Response Close            Response End              catch  Exception ex               var stream   new MemoryStream        pck SaveAs stream        String filename    myfile xlsx       String contentType    application vnd openxmlformats-officedocument spreadsheetml sheet       var cd   new System Net Mime ContentDisposition               Inline   false          FileName   filename            Response AppendHeader  Content-Disposition   cd ToString         stream Position   0       return File stream  contentType  filename            Response ContentType    application vnd openxmlformats-officedocument spreadsheetml sheet       Response AppendHeader  Content-Disposition    attachment  filename     file Name       Response TransmitFile Path GetFullPath file Name        Response Flush        Response Close             Response ClearHeaders        Response BinaryWrite pck GetAsByteArray         Response ContentType    application vnd openxmlformats-officedocument spreadsheetml sheet       Response AddHeader  content-disposition    attachment   filename Sample2 xlsx        Response Flush        Response Close          Again note that all the commented code is things that I have found from various sources that have not worked   So I am not getting any errors but when I click the button on my application to execute the code behind method  nothing is happening  It is loading and it runs through but there are no files created  nothing is opening up  This is the first time I have ever used EPPlus and I am not wholly familiar with exporting things to excel programmatically so I feel lost here   Are there any suggestions that you guys have  I would be happy to clarify any points that I have not hit upon fully as well

User · Answer

It s best if you worked with DataSets and or DataTables  Once you have that  ideally straight from your stored procedure with proper column names for headers  you can use the following method   ws Cells LoadFromDataTable  lt DATATABLE HERE gt   true  OfficeOpenXml Table TableStyles Light8       which will produce a beautiful excelsheet with a nice table   Now to serve your file  assuming you have an ExcelPackage object as in your code above called pck    Response Clear     Response ContentType    application vnd openxmlformats-officedocument spreadsheetml sheet   Response AddHeader  Content-Disposition    attachment filename     sFilename    Response BinaryWrite pck GetAsByteArray     Response End

User · Answer

Have you looked at the samples provided with EPPlus   This one shows you how to create a file http   epplus codeplex com wikipage title ContentSheetExample  This one shows you how to use it to stream back a file http   epplus codeplex com wikipage title WebapplicationExample  This is how we use the package to generate a file   var newFile   new FileInfo ExportFileName   using  ExcelPackage xlPackage   new ExcelPackage newFile                                   do work here                                 xlPackage Save

User · Answer

If you have a collection of objects that you load using stored procedure you can also use LoadFromCollection   using  ExcelPackage package   new ExcelPackage file         ExcelWorksheet worksheet   package Workbook Worksheets Add  test         worksheet Cells  A1   LoadFromCollection myColl  true  OfficeOpenXml Table TableStyles Medium1        package Save

[c#] Writing an Excel file in EPPlus

Examples related to c#

Examples related to epplus