Web Applications

This is a sample how you can return a spreadsheet from your web server without access to the file system...

 

        private void DumpExcel(DataTable tbl)
        {
            using (ExcelPackage pck = new ExcelPackage())
            {
                //Create the worksheet
                ExcelWorksheet ws = pck.Workbook.Worksheets.Add("Demo");

                //Load the datatable into the sheet, starting from cell A1. Print the column names on row 1
                ws.Cells["A1"].LoadFromDataTable(tbl, true);

                //Format the header for column 1-3
                using (ExcelRange rng = ws.Cells["A1:C1"])
                {
                    rng.Style.Font.Bold = true;
                    rng.Style.Fill.PatternType = ExcelFillStyle.Solid;                      //Set Pattern for the background to Solid
                    rng.Style.Fill.BackgroundColor.SetColor(Color.FromArgb(79, 129, 189));  //Set color to dark blue
                    rng.Style.Font.Color.SetColor(Color.White);
                }

                //Example how to Format Column 1 as numeric 
                using (ExcelRange col = ws.Cells[2, 1, 2 + tbl.Rows.Count, 1])
                {
                    col.Style.Numberformat.Format = "#,##0.00";
                    col.Style.HorizontalAlignment = ExcelHorizontalAlignment.Right;
                }

                //Write it back to the client
                Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
                Response.AddHeader("content-disposition", "attachment;  filename=ExcelDemo.xlsx");
                Response.BinaryWrite(pck.GetAsByteArray());
            }
        }

 

Last edited Jan 2, 2011 at 6:32 PM by JanKallman, version 2

Comments

sandeepsingampall Jan 25 at 9:01 AM 
Its not working for me it is saying package does not contain a constructor that takes 0 arguments

sheikhzeeshan Oct 28, 2013 at 9:45 AM 
i want to merge multiple excel streams into one. is it possible with EPPlus

alsnow Jun 19, 2013 at 3:03 PM 
If you get "Unreachable error" use both Clear() and End()
Response.Clear();
Response.AddHeader("content-disposition", "attachment; filename=file.xlsx");
Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
Response.BinaryWrite(pck.GetAsByteArray());
Response.End();

tleck Jan 9, 2013 at 9:00 AM 
How to save ExcelPackage? How could i create new excel document?

chrissalas Apr 26, 2012 at 9:28 PM 
Where does the data.Length value come from?

miyonet Mar 7, 2012 at 2:48 PM 
i can only get the excel file as stream(System.IO.Stream)..
Can we get the excel file as a stream by this package..

rbsaroj Nov 26, 2011 at 2:43 PM 
can i use it without putting the response.end because this disable my page. and user have to refresh the page. which is not acceptable by end user. please suggest.

rslygh Sep 20, 2011 at 3:13 PM 
This code worked to create an xlsx file, but I kept getting an error in Excel 2007 telling me that the spreadsheet was corrupt and needed to be repaired, after which it would open the spreadsheet. To avoid the error I added Response.End(); to the end of the code above and it works properly after that. Thanks for taking care of the hard part!

poul Sep 12, 2011 at 11:00 AM 
if used:
...
ws.Cells["A1"].LoadFromCollection<string>(collection);
...
throw an exception: System.Reflection.TargetParameterCountException: Parameter count mismatch.

how to avoid this?

creoman Jul 14, 2011 at 10:06 AM 
Response.Clear(); doesn't help.
To fix the problem add one more header:
Response.AddHeader("Content-Length", data.Length.ToString());
Please note, that headers case are important

clukic Jun 30, 2011 at 2:09 PM 
If you receive the error "content unreadable" try adding the line
Response.Clear();
before writing the headers to the client.