how to create and download excel document using asp.net
Solution 1
Starter kit
First i have downloaded the Open XML Format SDK 2.0.
It comes with 3 useful tools in :
C:\Program Files\Open XML Format SDK\V2.0\tools
-
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 "xl\worksheets" ).
The code
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.
Namespaces references
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:\inetpub\wwwroot\project.Web\Clients\Handlers\oxml-tpl\livreurs.xlsx", @"C:\inetpub\wwwroot\project.Web\Clients\Handlers\oxml-tpl\generated.xlsx", true);
// Open the copied template workbook.
using (SpreadsheetDocument myWorkbook = SpreadsheetDocument.Open(@"C:\inetpub\wwwroot\project.Web\Clients\Handlers\oxml-tpl\generated.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<SheetData>();
// Begining Row pointer
int index = 2;
// Database results
var query = from t in db.Clients select t;
// For each item in the database, add a Row to SheetData.
foreach (var item in query)
{
// Cell related variable
string Nom = item.Nom;
// New Row
Row row = new Row();
row.RowIndex = (UInt32)index;
// New Cell
Cell cell = new Cell();
cell.DataType = CellValues.InlineString;
// Column A1, 2, 3 ... and so on
cell.CellReference = "A"+index;
// Create Text object
Text t = new Text();
t.Text = Nom;
// Append Text to InlineString object
InlineString inlineString = new InlineString();
inlineString.AppendChild(t);
// Append InlineString to Cell
cell.AppendChild(inlineString);
// Append Cell to Row
row.AppendChild(cell);
// Append Row to SheetData
sheetData.AppendChild(row);
// increase row pointer
index++;
}
// save
worksheetPart.Worksheet.Save();
}
I havent finished yet, my second job is to auto download the spreadsheet after modification.
Finally, i redirect the user to my generated spredsheet (from my aspx)
context.Response.Redirect("Oxml-tpl/generated.xlsx");
Solution 2
just set Response.ContentType = "application/vnd.ms-excel" and your page will rendered as an excel sheet on the clients browser
Related videos on Youtube
Comments
-
belaz almost 2 years
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.
-
Neil N about 15 yearsbut for simplicity's sake, output any data you want in excel's "grid" in html tables
-
Yablargo almost 12 yearsThanks for the (borrowed!) in depth example. Needed to do a quick job regarding reading tons of excel. This did it for me with the least resistance
-
Srinivas over 8 yearsThanks Its working as expected. I have one doubt if i have more than 30 columns then how can i write to excel. for few columns we can write in foreach . Please suggest me.