This project has moved. For the latest updates, please go here.

Having trouble creating an excel spreadsheet

Mar 28, 2013 at 2:41 AM
I have been stuck on this for days and despite all of the help out there, none of these solutions have been working for me. What I want to do is create an excel file using the epplus library with some basic data in it that I am pulling from a stored procedure. This is the code that I have in my ExportDocument.cs file:
public static ExcelPackage CreateExcelDocument(int [] arr)
    {
        String path = @"D:\temp\testsheet3.xlsx";
        //FileInfo newFile = null;
        /*if (!File.Exists(path + "\\testsheet2.xlsx"))
            newFile = new FileInfo(path + "\\testsheet2.xlsx");
        else
            return newFile;*/
        using (ExcelPackage package = new ExcelPackage())
        {
            ExcelWorksheet ws = package.Workbook.Worksheets.Add("testsheet");

            ws.Cells["B1"].Value = "Number of Used Agencies";
            ws.Cells["C1"].Value = "Active Agencies";
            ws.Cells["D1"].Value = "Inactive Agencies";
            ws.Cells["E1"].Value = "Total Hours Volunteered";
            ws.Cells["B1:E1"].Style.Font.Bold = true;

            int x = 2;
            char pos = 'B';
            foreach (object o in arr)
            {
                String str = pos + x.ToString();
                ws.Cells[str].Value = o.ToString();
                if (pos > 'E')
                {
                    pos = 'B';
                    x++;
                }

                pos++;
            }
            //newFile.Create();
            //newFile.MoveTo(@"C:/testSheet.xlsx");
            //package.SaveAs(newFile);
            package.Save();
            /*Stream stream = File.Create(path);
            package.SaveAs(stream);
            stream.Close();*/

            //byte[] data = File.ReadAllBytes(path);
            //byte[] bin = package.GetAsByteArray();
            //String path = Path.GetTempPath();
            //File.WriteAllBytes(path, bin);
            //File.WriteAllBytes(path + "testsheet.xlsx", bin);
            //HttpContext.Current.Response.Write("<script>alert('"+ temp + "');</script>");
            //System.Diagnostics.Process.Start(path + "\\testsheet.xlsx");

            return package;
        }

    }
All the commented code is different things that I have found on the internet to try. Note that this a school organization and we are not using MVC. I am then using the codebehind file to pull this method like this:
protected void GenerateReport(Object o, EventArgs e)
    {
        //NamingContainer.FindControl("ReportsControl").Visible = false;
        //NamingContainer.FindControl("ReportsGenerateControl").Visible = true;
        Session["reportSession"] = ddReport.SelectedItem.Value.ToString();


        int [] arr = new int [ReportRepository.GetAgencyCounts().Count];
        ReportRepository.GetAgencyCounts().CopyTo(arr, 0);

        ExcelPackage pck = ExportDocument.CreateExcelDocument(arr);
        /*try
        {
            byte [] data = ExportDocument.CreateExcelDocument(arr).GetAsByteArray();
            Response.Clear();
            Response.Buffer = true;
            Response.BinaryWrite(data);
            Response.AddHeader("content-length", data.Length.ToString());
            Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";

            Response.Flush();
            Response.Close();
            Response.End();
        }
        catch (Exception ex) { }*/

        /*var stream = new MemoryStream();
        pck.SaveAs(stream);

        String filename = "myfile.xlsx";
        String contentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
        var cd = new System.Net.Mime.ContentDisposition
        {
            Inline = false,
            FileName = filename
        };
        Response.AppendHeader("Content-Disposition", cd.ToString());
        stream.Position = 0;

        return File(stream, contentType, filename);*/

        /*Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
        Response.AppendHeader("Content-Disposition", "attachment; filename=" + file.Name);
        Response.TransmitFile(Path.GetFullPath(file.Name));
        Response.Flush();
        Response.Close();*/

        /*Response.ClearHeaders();
        Response.BinaryWrite(pck.GetAsByteArray());
        Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
        Response.AddHeader("content-disposition", "attachment;  filename=Sample2.xlsx");
        Response.Flush();
        Response.Close();*/
    }
Again note that all the commented code is things that I have found from various sources that have not worked.

So I am not getting any errors but when I click the button on my application to execute the codebehind method, nothing is happening. It is loading and it runs through but there are no files created, nothing is opening up. This is the first time I have ever used epplus and I am not wholly familiar with exporting things to excel programmatically so I feel lost here.

Are there any suggestions that you guys have? I would be happy to clarify any points that I have not hit upon fully as well.

Thanks