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;
}