Friday, 25 January 2013 13:55

how to export to excel by openxml

Written by 
Rate this item
(0 votes)

exporting to data in excel is a very common practice
in business website.one can use asp.net Response methods
to export to excel which is easy to do using a gridview.but
contents not appear properly like there will be white back-
ground over excel rows and this way is not efficient too.

the solution is to use microsoft's openxml sdk.
following example shows how to do that -

here a new document opened from a template file generateddb2.xlsx first then
data added in 'A' column in excel file as a example -

at first add reference to DocumentFormat.OpenXml.dll and add these in code
behind file -
using DocumentFormat.OpenXml.Packaging;
using DocumentFormat.OpenXml.Spreadsheet;
using DocumentFormat.OpenXml;

using (SpreadsheetDocument myWorkbook = SpreadsheetDocument.Open
(Server.MapPath(subPath + "\\" + User_Name) + "\\" + "generateddb2.xlsx", true))
{
//Access the main Workbook part, which contains all references
WorkbookPart workbookPart = myWorkbook.WorkbookPart;
//Grab the first worksheet
WorksheetPart worksheetPart = workbookPart.WorksheetParts.First();
//SheetData will contain all the data
SheetData sdata = worksheetPart.Worksheet.GetFirstChild<SheetData>();

WorkbookStylesPart wbsp = workbookPart.WorkbookStylesPart;//<WorkbookStylesPart>();
wbsp.Stylesheet = CreateStylesheet();
wbsp.Stylesheet.Save();

string info = null;
List<string> tempoProIds = new List<string>();
DocumentFormat.OpenXml.Spreadsheet.Row contentRow1 = new DocumentFormat.OpenXml.Spreadsheet.Row();
sdata.AppendChild(contentRow1);
CreateContentHeaders(contentRow1, 1, "First Data");

worksheetPart.Worksheet.Save();
myWorkbook.Close();

WebClient client = new WebClient();
Byte[] buffer = client.DownloadData("generateddb2.xlsx");
Response.Clear();
Response.Buffer = true;
Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
Response.AddHeader("content-disposition", "attachment;filename=" + "Bids.xlsx"); //for opening save/open dialog
//Response.AddHeader("content-length", buffer.Length.ToString()); // for opening any file in browser
Response.Charset = "";
Response.Cache.SetCacheability(HttpCacheability.NoCache);
Response.BinaryWrite(buffer);
Response.End();
}
DocumentFormat.OpenXml.Spreadsheet.Row CreateContentHeaders(DocumentFormat.OpenXml.Spreadsheet.Row r, int index, string text)
{
r.RowIndex = (UInt32)index;
DocumentFormat.OpenXml.Spreadsheet.Cell c = new DocumentFormat.OpenXml.Spreadsheet.Cell();
c.DataType = CellValues.String;
c.CellValue = new DocumentFormat.OpenXml.Spreadsheet.CellValue(text);
c.StyleIndex = 5;

c.CellReference = "A" + index;
r.AppendChild(c);
return r;
}

Read 3939 times
Super User

Email This email address is being protected from spambots. You need JavaScript enabled to view it.
Login to post comments