How can I download my Template Excel .xlxs using OpenXML in .NET 3.5

1.2k views Asked by At

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:

enter image description here

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 :)

0

There are 0 answers