File format error after open downloaded excel file

Question

In my app, I am trying to download excel file from byte array content in mvc. After file downloaded, when I open that downloaded file I am getting error.

"The file you're trying to open 'XXXX.xls' is in a different format than specified by the file extension. Verify that the file is not corrupted and is from a trusted source before opening the file. Do you want to open the file now?"

after click on yes in above error I am getting another error

Excel found unreadable content in 'XXXX.xls'. Do you want to recover the contents of this workbook? If you trust the source of this workbook, click Yes.

Again when I am click on yes in above error message I am getting first error message again.

"The file you're trying to open 'XXXX.xls' is in a different format than specified by the file extension. Verify that the file is not corrupted and is from a trusted source before opening the file. Do you want to open the file now?"

After click on yes in above error message, excel opens a repair popup showing message inside it. The message is

Repaired Records: Format from /xl/styles.xml part (Styles)

Here is my controller code

 [HttpPost, FileDownload]
        public FileContentResult GetReport(DateTime StartDate, DateTime EndDate, int ReportType)
        {
            var reportData = new Model().GetReport(StartDate, EndDate, ReportType);

            string fileName = "Report " + (TimeZoneUtil.ConvertUtcDateTimeToESTDateTime(DateTime.UtcNow).ToString("yyyy:MM:dd:hh:mm:ss")) + ".xls";

            return File(reportData, MimeMapping.GetMimeMapping(fileName), fileName);

        }

I am calling this method in view using jQuery File Download Plugin and my code is

  var dataToSend = { "StartDate": $("#dtpreportstartdate").val(), "EndDate": $("#dtpreportenddate").val(), "ReportType": value };
                        $.fileDownload(GetBaseUrl() + "Dashboard/GetReport",
                        {
                            preparingMessageHtml: "success message",
                            failMessageHtml: "Error message",
                            httpMethod: "POST",
                            data: dataToSend
                        });

Below is my method to get excel content

 public byte[] CreateReportFile(List<BGClass> BGRows)
            {
                MemoryStream ms = new MemoryStream();
                SpreadsheetDocument xl = SpreadsheetDocument.Create(ms, SpreadsheetDocumentType.Workbook);
                WorkbookPart wbp = xl.AddWorkbookPart();
                WorksheetPart wsp = wbp.AddNewPart<WorksheetPart>();
                Workbook wb = new Workbook();
                FileVersion fv = new FileVersion();
                fv.ApplicationName = "Microsoft Office Excel";
                Worksheet ws = new Worksheet();
                SheetData sd = new SheetData();
                AddStyleSheet(ref xl);


                Row headerRow = new Row();
                Cell CreatedDateHeaderCell = new Cell() { StyleIndex = Convert.ToUInt32(1) };
                CreatedDateHeaderCell.DataType = CellValues.String;
                CreatedDateHeaderCell.CellValue = new CellValue("Created Date");
                headerRow.Append(CreatedDateHeaderCell);

                Cell BackgroundNameHeaderCell = new Cell() { StyleIndex = Convert.ToUInt32(1) };
                BackgroundNameHeaderCell.DataType = CellValues.String;
                BackgroundNameHeaderCell.CellValue = new CellValue("Bg Name");
                headerRow.Append(BackgroundNameHeaderCell);

                sd.Append(headerRow);

                foreach (BGClass reportRow in BGRows)
                {
                    Row dataRow = new Row();

                    Cell CreatedDateDataCell = new Cell();
                    CreatedDateDataCell.DataType = CellValues.String;
                    CreatedDateDataCell.CellValue = new CellValue(TimeZoneHelper.ConvertUtcDateTimeToESTDateTime(reportRow.CreatedDate).ToString());
                    dataRow.Append(CreatedDateDataCell);

                    Cell BackgroundNameDataCell = new Cell();
                    BackgroundNameDataCell.DataType = CellValues.String;
                    BackgroundNameDataCell.CellValue = new CellValue(reportRow.BackgroundName);
                    dataRow.Append(BackgroundNameDataCell);

                }

                ws.Append(sd);
                wsp.Worksheet = ws;
                wsp.Worksheet.Save();
                Sheets sheets = new Sheets();
                Sheet sheet = new Sheet();
                sheet.Name = "Report";
                sheet.SheetId = 1;
                sheet.Id = wbp.GetIdOfPart(wsp);
                sheets.Append(sheet);
                wb.Append(fv);
                wb.Append(sheets);

                xl.WorkbookPart.Workbook = wb;
                xl.WorkbookPart.Workbook.Save();
                xl.Close();

                return ms.ToArray();
            }

What is wrong with the code? Why I am getting excel error while opening a file? I tried lots of blog to change MIME type, but nothing work for me. Any idea?

Answer 1

I solved the problem after did some research. I am applying style to header row in excel using function AddStyleSheet(ref xl);. Problem occurs in that method I missing few parameters while applying style to cell.

My old method is

private WorkbookStylesPart AddStyleSheet(ref SpreadsheetDocument spreadsheet)
        {
            WorkbookStylesPart stylesheet = spreadsheet.WorkbookPart.AddNewPart<WorkbookStylesPart>();
            Stylesheet workbookstylesheet = new Stylesheet();

            Font fontBold = new Font(new FontName() { Val = "Arial" }); // Default font
            Font defaultFont = new Font(new FontName() { Val = "Arial" }); // Bold font

            Bold bold = new Bold();
            defaultFont.Append(bold);

            Fonts fonts = new Fonts();      // <APENDING Fonts>
            fonts.Append(fontBold);
            fonts.Append(defaultFont);

            //// <Fills>
            //Fill fill0 = new Fill();        // Default fill
            //Fills fills = new Fills();      // <APENDING Fills>
            //fills.Append(fill0);

            // <Borders>
            //Border border0 = new Border();     // Defualt border
            //Borders borders = new Borders();    // <APENDING Borders>
            //borders.Append(border0);

            // <CellFormats>
            CellFormat cellformat0 = new CellFormat() { FontId = 0, FillId = 0, BorderId = 0 }; // Default style : Mandatory | Style ID =0
            CellFormat cellformat1 = new CellFormat() { FontId = 1 };  // Style with Bold text ; Style ID = 1

            // <APENDING CellFormats>
            CellFormats cellformats = new CellFormats();
            cellformats.Append(cellformat0);
            cellformats.Append(cellformat1);

            // Append FONTS, FILLS , BORDERS & CellFormats to stylesheet <Preserve the ORDER>
            workbookstylesheet.Append(fonts);
            //workbookstylesheet.Append(fills);
            //workbookstylesheet.Append(borders);
            workbookstylesheet.Append(cellformats);

            // Finalize
            stylesheet.Stylesheet = workbookstylesheet;
            stylesheet.Stylesheet.Save();

            return stylesheet;
        }

Here in this function I commented Fill and border section as I don't need it. But if you don't use it while applying style index it will give you "unreachable content" error, which I was facing.

So I changed my method and add Fill and border section to style. Here is my updated method.

 private WorkbookStylesPart AddStyleSheet(ref SpreadsheetDocument spreadsheet)
    {
        WorkbookStylesPart stylesheet = spreadsheet.WorkbookPart.AddNewPart<WorkbookStylesPart>();
        Stylesheet workbookstylesheet = new Stylesheet(
            new Fonts(
                new Font(                                                               // Index 0 – The default font.
                    new FontSize() { Val = 11 },
                    new Color() { Rgb = new HexBinaryValue() { Value = "000000" } },
                    new FontName() { Val = "Arial" }),
                new Font(                                                               // Index 1 – The bold font.
                    new Bold(),
                    new FontSize() { Val = 11 },
                    new Color() { Rgb = new HexBinaryValue() { Value = "000000" } },
                    new FontName() { Val = "Arial" })
            ),
            new Fills(
                new Fill(                                                           // Index 0 – The default fill.
                    new PatternFill() { PatternType = PatternValues.None })
            ),
            new Borders(
                new Border(                                                         // Index 0 – The default border.
                    new LeftBorder(),
                    new RightBorder(),
                    new TopBorder(),
                    new BottomBorder(),
                    new DiagonalBorder())
            ),
            new CellFormats(
                new CellFormat() { FontId = 0, FillId = 0, BorderId = 0 },                          // Index 0 – The default cell style.  If a cell does not have a style index applied it will use this style combination instead
                new CellFormat() { FontId = 1, FillId = 0, BorderId = 0 }                           // Index 1 – Bold 
            )
        );


        stylesheet.Stylesheet = workbookstylesheet;
        stylesheet.Stylesheet.Save();

        return stylesheet;
    }

For ref link https://blogs.msdn.microsoft.com/chrisquon/2009/11/30/stylizing-your-excel-worksheets-with-open-xml-2-0/.

Answer 2

Can I suggest something ?

Why not use a free C# library, like mine (link below), which you can pass your List<> variable to, and it'll create a perfectly working .xlsx file for you.

CodeProject: Export to Excel, in C#

One line of code, and this problem goes away:

public void CreateReportFile(List<BGClass> BGRows)
{
    CreateExcelFile.CreateExcelDocument(BGRows, "SomeFilename.xlsx");
}

All C# source code is provided free of charge.

Answer 3

You're using SpreadsheetDocument.Create()from the OpenXML SDK.

This indicates that you're writing an XLSX file, yet you serve the file with an XLS extension and according MIME type.

Change the file extension to .xlsx, indicating the XML format.