I am facing an issue while trying to stream out an excel file which I generated with EPPlus OfficeOpenXml package.

My WCF service contact as below,

[WebInvoke(UriTemplate = "getengagementfeedfinalizedata?id={workspaceId}&s={startDate}&e={endDate}", Method = "POST", BodyStyle = WebMessageBodyStyle.Bare, ResponseFormat = WebMessageFormat.Json, RequestFormat = WebMessageFormat.Json)]
Stream GetEngagementFeedFinalizeData(int workspaceId, string startDate, string endDate, Stream data);

Corresponding method is as below,

    public Stream GetEngagementFeedFinalizeData(int workspaceId, string startDate, string endDate, Stream data)
    {
        try
        {
            string contentType = "application/vnd.ms-excel; charset=utf-8;";
            string extension = "xls";
            string fileName = "report-"   DateTime.Now.Ticks.ToString();
            string contentDisposition = string.Format("attachment; filename={0}.{1}", fileName, extension);

            WebOperationContext.Current.OutgoingResponse.ContentType = contentType;
            WebOperationContext.Current.OutgoingResponse.Headers.Set("Content-Disposition", contentDisposition);
            
            //Some business logic and fetching data from db which later I used to
            //fill a dataTable to generate an excel file. Respective DataTable is
            //assigned to a variable called "feedFinalizeDataTable"

            feedFinalizeDataTable.TableName = "Summary";

            DataSet dataSet = new DataSet();
            dataSet.Tables.Add(feedFinalizeDataTable);

            using (ExcelPackage excelPackage = new ExcelPackage())
            {
                foreach (DataTable dt in dataSet.Tables)
                {
                    ExcelWorksheet sheet = excelPackage.Workbook.Worksheets.Add(dt.TableName);
                    sheet.Cells["A1"].LoadFromDataTable(dt, true);

                    var cellFont = sheet.Cells[1, 1, 1, dt.Columns.Count].Style.Font;
                    cellFont.Bold = true;
                }

                excelPackage.Save();
                var bin = excelPackage.GetAsByteArray();

                return new MemoryStream(bin);
            }
        }
        catch (Exception ex)
        {
            Logger.Instance.Write(LogLevel.ERROR, "DashboardService.GetEngagementFeedFinalizeData", ex);

            throw;
        }
    }

This method is released without any error. But, the downloaded excel file is full of characters like PK�UU�H��3.

I thought this might be related to some encoding problems. I changed the contentType with octet-stream and remove charset=utf-8 from its decleration, but didn't solve the problem.

I know that there is no problem with the file I generated because when I save the file to a directory like this,

        using (ExcelPackage excel = new ExcelPackage())
        {
            foreach (DataTable dt in dataset.Tables)
            {
                ExcelWorksheet sheet = excel.Workbook.Worksheets.Add(dt.TableName);
                sheet.Cells["A1"].LoadFromDataTable(dt, true);

                var cellFont = sheet.Cells[1, 1, 1, dt.Columns.Count].Style.Font;
                cellFont.Bold = true;
            }

            var path = System.IO.Path.Combine(System.AppDomain.CurrentDomain.BaseDirectory);
            var filePath = path   "\\"   "New.xls";
            
            excel.SaveAs(new System.IO.FileInfo(filePath));
        }

The saved excel file is readable with the correct data set I have fetched from the db. So, it seems I cannot stream it properly.

There are also other problems caught my eye: The exported excel file's (I'm talking about the broken one with unreadable characters) worksheet name isn't the name I assigned via this line,

ExcelWorksheet sheet = excel.Workbook.Worksheets.Add(dt.TableName);

But rather, it bears the file name which I set on the content header,

string fileName = "report-"   DateTime.Now.Ticks.ToString();
WebOperationContext.Current.OutgoingResponse.Headers.Set("Content-Disposition", contentDisposition);

This also makes me worried because I gonna add another worksheet to this workbook later on. It seems like I'm overriding some of the excel file properties while trying to stream it out, but cannot figure what they are.

Thanks in advance!

CodePudding user response：

A couple of changes you can make:

 string contentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
 string extension = "xlsx";

You should use "xslx" wherever you are using "xls". You also don't have to call excelPackage.Save();.

CodePudding user response：

First, you can try to print log information through the breakpoint to check the specific error.

In addition, you can send the exported file to others to view. If the same problem occurs, it means that the file itself is faulty. Otherwise, it means that the problem is your machine.

Finally, change the format of the exported file to XLSX or CSV.