Exported Excel File Contains Unreadable Characters through .Net WCF

Question

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!

Alameda

Answer 1

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();.

Worsted

Answer 2

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.

Bolan

unfortunatelly problem is caused by none of these. Its about the way of Epplus encoding when we use .GetAsByteArray() method. Its definitely not using UTF-8 when converting to bytearray. This is my main problem. I'm now trying to save the file to a directory and then stream the saved file, but the same problem persists – Alameda

Is the problem solved? Is there any other error message according to the method. – Bolan

Thank you for your interest but there was no error message. As I have stated before, code is released without any exception. And, is the problem resolved? Yes and no. I have tried everything I know but the file returned from my WCF end-point was always corrupted. So, I moved everything about excel creation to my MVC controller, and bingo.. File returned from MVC controller isn't corrupted. My MVC controller also returned a MemoryStream in the end, didn't do anything different compared to above code sample, but still couldn't able to figure out the problem on my WCF end-point – Alameda