C# DataTable to Excel using ClosedXML

Question

I have looked through post after posts and cannot figure how to get this to work.

I have a c# controller which i want to return an excel file of the data query from database. My response which I capture from firebug seems to be in binary (see picture) but how do i get the popup asking me to save or open?

thanks

public void getData()
        {
            var sql = "select top 10 * from z_main";

            System.Data.DataTable dt = runSQLParamsReturnDT(sql, null, db);
            dt.TableName = "Detail Data";
            using (XLWorkbook wb = new XLWorkbook())
            {
                wb.Worksheets.Add(dt);
                wb.Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;
                wb.Style.Font.Bold = true;

                Response.Clear();
                Response.Buffer = true;
                Response.Charset = "";
                Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
                Response.AddHeader("content-disposition", "attachment;filename= EmployeeReport.xlsx");

                using (MemoryStream MyMemoryStream = new MemoryStream())
                {
                    wb.SaveAs(MyMemoryStream);
                    MyMemoryStream.WriteTo(Response.OutputStream);
                    Response.Flush();
                    Response.End();
                }
            }
        }

Have you tried to change ContentType to "application/vnd.ms-excel" — Samvel Petrosov, May 15 '17 at 18:55
Yes I have, Response.ContentType = "application/vnd.ms-excel"; still the same. — chungtinhlakho, May 15 '17 at 19:02
Add Response.ClearContent() and Response.ClearHeaders() before Response.Buffer=true; — Samvel Petrosov, May 15 '17 at 19:23
Change filename= EmployeeReport.xlsx to filename= \"EmployeeReport.xlsx\" — Samvel Petrosov, May 16 '17 at 03:54
Doesn't work, maybe i should add that I am doing an http.post from angular 2. The funny thing is, on the debug view from firebug, if I right and open in a new tab, it will ask me to download the excel — chungtinhlakho, May 17 '17 at 16:24
Also you can try to remove Response.End() and use HttpContext.Current.ApplicationInstance.CompleteRequest() — Samvel Petrosov, May 17 '17 at 16:31
As you can see http://stackoverflow.com/questions/43998006/how-to-add-button-for-downloading-excel-file-in-acumatica I have this problem in our system too, but to my happiness there is special operator for doing such things in that system — Samvel Petrosov, May 17 '17 at 16:33
PXRedirectToFileException cannot be found and I cannot seem to find what kind of library from google. — chungtinhlakho, May 17 '17 at 21:24
Yes , i know it's exception from the system i was working with — Samvel Petrosov, May 17 '17 at 21:26
any alternative since i can't use PXRedirectToFileException . — chungtinhlakho, May 17 '17 at 21:35

C# DataTable to Excel using ClosedXML

0 Answers0