Can anyone help me with my problem when downloading my Excel Template using open XML? I can open the template and I think I also write data to it but I can't download the file to see if data is reflected the cell/row that I want it to display. I called my method from code behind with EventHandler as a data type.
Here is my code on the method I'm talking about:
/// <summary>
/// Handles the ExtractJobsCalibrationForm event of the View control.
/// </summary>
/// <param name="sender">The source of the event.</param>
/// <param name="e">The <see cref="System.EventArgs"/> instance containing the event data.</param>
void View_ExtractJobsCalibrationForm(object sender, EventArgs e)
{
try
{
using (Data.DataContexts.IDataContext objContext = Data.DataContexts.DataContext.CreateDataContext())
{
GridViewRow row = (((ImageButton)((System.Web.UI.WebControls.GridViewCommandEventArgs)(e)).CommandSource).NamingContainer as GridViewRow);
IQueryable<Data.JobSummary> objJobs = objContext.Jobs.GetJobSummaries().Where(j => !j.IsDeleted);
IQueryable<Data.EquipmentSummary> objEquipment = objContext.Equipment.GetEquipmentSummaries();
//oXL = new Microsoft.Office.Interop.Excel.Application();
//oXL.Visible = true;
//oXL.DisplayAlerts = false;
string path = HttpContext.Current.Server.MapPath("/Reports/ExcelTemplate/Calibration_Form_ARS-FORM-CL1_Template.xlsx");
string destPath = System.IO.Path.Combine(System.IO.Path.GetTempPath(), string.Format(System.IO.Path.GetFileNameWithoutExtension(path) + "_{0}_{1}{2}", ((HyperLink)row.Cells[1].Controls[0]).Text, DateTime.UtcNow.ToString("yyyy-MM-dd"), ".xlsx"));
//Create a copy from the Template to save the data.
System.IO.File.Copy(path, destPath, true);
byte[] byteArray = File.ReadAllBytes(destPath);
using (MemoryStream ms = new MemoryStream())
{
ms.Write(byteArray, 0, (int)byteArray.Length);
using (SpreadsheetDocument doc = SpreadsheetDocument.Open(ms, true))
{
// Access the main Workbook part, which contains all references.
WorkbookPart workbookPart = doc.WorkbookPart;
// get sheet by name
Sheet sheet = workbookPart.Workbook.Descendants<Sheet>().Where(s => s.Name == "Pre calibration Check in").FirstOrDefault();
// get worksheetpart by sheet id
WorksheetPart worksheetPart = workbookPart.GetPartById(sheet.Id.Value) as WorksheetPart;
// The SheetData object will contain all the data.
SheetData sheetData = worksheetPart.Worksheet.GetFirstChild<SheetData>();
Cell cell = GetCell(worksheetPart.Worksheet, "B", 2);
cell.CellValue = new CellValue("10");
cell.DataType = new EnumValue<CellValues>(CellValues.Number);
// Save the worksheet.
worksheetPart.Worksheet.Save();
//doc.Close();
//Stream stream = File.Open(destPath, FileMode.Open, FileAccess.ReadWrite);
//stream.Close();
}
File.WriteAllBytes(destPath, ms.ToArray());
System.Web.HttpResponse response = System.Web.HttpContext.Current.Response;
response.Clear();
response.AppendHeader("Content-Type", "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
response.AppendHeader("Content-Disposition", "attachment;filename=Testing.xlsx;");
response.Flush();
response.BinaryWrite(ms.ToArray());
response.End();
}
}
}
catch (System.IO.IOException ioEx)
{
string openFileName = ioEx.Message.Split('\'')[1].Split('\\')[6];
//If file is already open, Quit the newly open excel that is with the same filename that is currently open.
//oXL.Quit();
//Display the message.
View.DisplayInformation(string.Format("File ({0}) is already open!", openFileName));
}
catch (Exception objException)
{
View.DisplayException(objException);
}
}
and when I run my application there's no file that was downloaded. I'm still stuck with this and don't know how can I make it work. Also, just to inform you that my first code, I'm using Excel Interop but I have a problem when deploying it to the Environment that's why I shift to OpenXML.
EDIT:
Just want to add the Response Header of the download:
as you can see it already has the Content-Disposition and the Content-Length of the file but still nothings happening or no download prompt is display upon clicking the export.
Thanks in advance :)
