How to create and download excel document using asp.net ?The purpose is to use xml, linq or whatever to send an excel document to a customer via a browser.Edit : Use caseThe customer load a gridview ( made with ajax framework ) in a browser, the gridview is directly linked to an sql database. I put a button ‘…
how to create and download excel document using asp.net
How to create and download excel document using asp.net ?
The purpose is to use xml, linq or whatever to send an excel document to a customer via a browser.
Edit : Use case
The customer load a gridview ( made with ajax framework ) in a browser, the gridview is directly linked to an sql database. I put a button ‘export to excel’ to let customer save this gridview data on his computer ansd i would like to launch a clean download of an excel.
The solutions proposed here are not clean, like send an html document and change the header to excel document etc, i’m searching a simple solution on codeplex right now, i will let you know.
First i have downloaded the Open XML Format SDK 2.0.
It comes with 3 useful tools in :
C:Program FilesOpen XML Format SDKV2.0tools
DocumentReflector.exe wich auto generate the c# to build a spreadsheet from the code.
OpenXmlClassesExplorer.exe display Ecma specification and the class documentation (using an MSDN style format).
OpenXmlDiff.exe graphically compare two Open XML files and search for errors.
I suggest anyone who begin to rename .xlsx to .zip, so you can see the XML files who drive our spreadsheet ( for the example our sheets are in “xlworksheets” ).
Disclaimer : I have stolen all the code from an MSDN technical article ;D
The following code use an *.xlsx template i made manually to be able to modify it.
using System.IO; using System.Xml; using DocumentFormat.OpenXml.Packaging; using DocumentFormat.OpenXml.Spreadsheet; using DocumentFormat.OpenXml; // Database object DataClassesDataContext db = new DataClassesDataContext(); // Make a copy of the template file. File.Copy(@”C:inetpubwwwrootproject.WebClientsHandlersoxml-tpllivreurs.xlsx”, @”C:inetpubwwwrootproject.WebClientsHandlersoxml-tplgenerated.xlsx”, true); // Open the copied template workbook. using (SpreadsheetDocument myWorkbook = SpreadsheetDocument.Open(@”C:inetpubwwwrootproject.WebClientsHandlersoxml-tplgenerated.xlsx”, true)) // Access the main Workbook part, which contains all references. WorkbookPart workbookPart = myWorkbook.WorkbookPart; // Get the first worksheet. WorksheetPart worksheetPart = workbookPart.WorksheetParts.ElementAt(2); // The SheetData object will contain all the data. SheetData sheetData = worksheetPart.Worksheet.GetFirstChild
Finally, i redirect the user to my generated spredsheet (from my ashx)
just set Response.ContentType = “application/vnd.ms-excel” and your page will rendered as an excel sheet on the clients browser
Sample code here
Kindly refer to the following good post:
Reading and Writing Excel Spreadsheets Using ADO.NET C# DbProviderFactory
There are quite a few ways of handling this, depending on how extensive the Excel functionality is. Binoj’s answer works if the Excel is just a spreadsheet and has no direct Excel functionality built in. The client can add functionality, concats, etc. These are “dumb” excel docs until the client does soemthing.
To create a more full featured Excel doc, you havve two basic choices that I can think of offhand.
Use either the office components (re: bad) to create an excel document, or a third party component, like SoftArtisan’s ExcelWriter. Great component, but there is a cost.
Use a control on the page that allows export to Excel. Most vendors of ASSP.NET controls have this functionality on their grids.
Option #1 allows you pretty much all functionality of Excel. Option #2 is a bit more limited, at least in the controls I have tried.
Good article on how top export to excel from Erika Ehrli http://blogs.msdn.com/erikaehrli/archive/2009/01/30/how-to-export-data-to-excel-from-an-asp-net-application-avoid-the-file-format-differ-prompt.aspx
For more info: how to create and download excel document using asp.net