I'm trying to supply a .xlsx file from a grid, I think most of the hard work is done. I'm picking up a template file and filling it with data.
I' getting an error 'file not found' on Response.WriteFile.
by the looks of the example (linked below) this should just be the intended file name, but I imagine this needs to be a path to the file?, so do I need to save my 'myDoc' object to the server and then provide the path in the Reponse.WriteFile.
It doesn't seem like that is what is meant by the example.
the code i'm using is a modified version of : http://technet.weblineindia.com/web/export-data-to-excel-using-openxml-sdk/
due to using sharepoint to store the template file I just had to create a filestream rather than supply the URL to the file.
here is my code:
// Create cell reference array
string[] CellReferenceArray = new string[] { "A", "B", "C", "D", "E" };
//Open your saved excel file that you have created using template file.
using (SpreadsheetDocument myDoc = SpreadsheetDocument.Open(file.OpenBinaryStream(), true))
{
// Create reference of main Workbook part, which contains all reference.
WorkbookPart objworkbook = myDoc.WorkbookPart;
// Create style sheet object that will be used for applying styling.
Stylesheet objstyleSheet = objworkbook.WorkbookStylesPart.Stylesheet;
// pick up first worksheet
WorksheetPart objworksheet = objworkbook.WorksheetParts.First();
// will be used in end while creating sheet data
string objorigninalSheetId = objworkbook.GetIdOfPart(objworksheet);
WorksheetPart objreplacementPart = objworkbook.AddNewPart<WorksheetPart>();
string objreplacementPartId = objworkbook.GetIdOfPart(objreplacementPart);
// Create object reader to read from excel file.
OpenXmlReader objreader = OpenXmlReader.Create(objworksheet);
// create writer object to write in excel sheet.
OpenXmlWriter objOpenXmwriter = OpenXmlWriter.Create(objreplacementPart);
int i = 1;
Row r = new Row();
Cell c = new Cell();
Columns col1 = new Columns();
UInt32 index = 0;
while (objreader.Read())
{
if (objreader.ElementType == typeof(SheetData))
{
if (objreader.IsEndElement)
continue;
objOpenXmwriter.WriteStartElement(new SheetData());
objOpenXmwriter.WriteStartElement(r);
// Loop to insert header
foreach (DataColumn colHead in YoutdTName.Columns)
{
c = new Cell
{
DataType = CellValues.String,
CellReference = CellReferenceArray[i] + Convert.ToString(index)
};
CellValue v1 = new CellValue(colHead.ColumnName.ToString());
c.Append(v1);
objOpenXmwriter.WriteElement(c);
i += 1;
}
objOpenXmwriter.WriteEndElement();
index += 1;
//Loop to insert datatable row in excel
foreach (DataRow dr in YoutdTName.Rows)
{
objOpenXmwriter.WriteStartElement(r);
i = 1;
foreach (DataColumn col in YoutdTName.Columns)
{
c = new Cell
{
DataType = CellValues.String,
CellReference = CellReferenceArray[i] + Convert.ToString(index)
};
CellValue v1 = new CellValue(dr[col].ToString());
c.AppendChild(v1);
objOpenXmwriter.WriteElement(c);
i += 1;
}
objOpenXmwriter.WriteEndElement();
index += 1;
}
objOpenXmwriter.WriteEndElement();
}
}
//close all objects
objreader.Close();
objOpenXmwriter.Close();
Sheet sheet = objworkbook.Workbook.Descendants<Sheet>().Where(s => s.Id.Value.Equals(objorigninalSheetId)).First();
sheet.Id.Value = objreplacementPartId;
objworkbook.DeletePart(objworksheet);
}
Response.AddHeader("Content-Disposition", "inline;filename=YourExcelfileName.xlxs");
Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
Response.WriteFile("YourExcelfileName.xlxs");
Response.Flush();
Response.End();
}
Use HttpResponse.BinaryWrite instead and take the underlying stream from your SpreadsheetDocument-instance .
http://msdn.microsoft.com/en-us/library/system.web.httpresponse.binarywrite(v=vs.110).aspx