Corrupt XLSX Document - Unit testing

[David]

Not sure that this actually qualifies as a bug, but it was unexpected behavior.  For unit testing I was creating an empty Workbook document and writing it to a file.  It turns out, that the file is corrupt unless it has a Worksheet added to it.  The XlsxFormatProvider does not issue any error and happily writes a 3k file, but Excel can't read it.  Excel throws a dialog "We found a problem with some content in the 'test.xlsx'.  Do you want us...".  Selecting Yes eventually comes back with a "The workbook cannot be opened or repaired by Microsoft Excel because it is corrupt." message.

Simply adding a worksheet:

workbook.Worksheets.Add()

will allow it to create a file that Excel will handle.  Again, not sure you could call this a bug, but it wasn't what I expected it to do.  It might be better if the XlsxFormatProvider threw an exception for trying to write out an empty file.  

At any rate, figured I throw this out there in case anyone else runs into this behavior.

[David]

Figured I'd post a little program that exhibits this behavior:

 

using System.IO;
using Telerik.Windows.Documents.Spreadsheet.FormatProviders.OpenXml.Xlsx;
using Telerik.Windows.Documents.Spreadsheet.Model;

namespace SSTest2
{
    class Program
    {
        static void Main(string[] args)
        {
                var workbook = new Workbook();
                var filename = @"C:\tmp\test.xlsx";
                var formatProvider = new XlsxFormatProvider();
                using (Stream output = new FileStream(filename, FileMode.Create))
                {
                    formatProvider.Export(workbook, output);
                }
        }
    }
}

[Tanya]

Hi David,

Thank you for bringing this to our attention. 

The reason for the behavior you are observing is that having a workbook without any worksheet is an invalid scenario according to the OOXML specification. I agree that exporting the file without any error can be confusing and that is why I created a task to prevent similar scenarios: SpreadProcessing: Exporting a workbook without a worksheet shouldn't be possible.

 

I updated your Telerik points in appreciation for reporting this.

Hope this is helpful.

Regards,
Tanya
Progress Telerik

[Zorro]

Hi David,
I recently faced the same error and I went ahead to click yes, believing that the inbuilt might fix the problem. After a proper research I understood that
On clicking ‘Yes’, any of these scenarios may occur:
Your Excel file might open but formatting may be lost, formulas replaced with values, and other such inconsistencies can crop up.
The error is followed by another error message. For instance, you may encounter “The file is corrupt and cannot be opened” error message.
here is a work around if the data is too important:
Try to open your '.xlsx' file by making it 'read-only'. Follow these steps:
In Excel, click File from the main menu.
In the screen that appears, select Save for new document or Save As for previously saved document.
From the ‘Save As’ dialog box, click Tools > General Options.
Click on the ‘read-only’ checkbox to make the document read-only, and then click OK. 
Open a new and blank '.xlsx' file and copy everything from the corrupt Excel file to this new file. Save this file and try to open it again.
Otherwise, you can refer to this link as an official Microsoft trouble-shooter. You can also refer to, or get help from any third party excel repair software. Here is a list of the top tools which are used to get rid of such corruption messages

[Zorro]

Top 10 Excel Recovery software - https://www.stellarinfo.com/blog/top-10-best-excel-recovery-software/

[David]

It wasn't an issue with data loss in this circumstance.  It was that the XlsxFormatProvider would write an invalid xlsx file without throwing an error.  My use case was simply a unit test for a method that emitted an xlsx document.  The integration test that tried to open that generated file failed.  I tried the built-in fix just to see what it would do.