OpenXml Spreadsheet Output gives "General Error" opening in OpenOffice when containing a large number of rows

Question

Spreadsheet output of OpenXML works in Excel (and Google Docs) but throws a runtime error in OpenOffice 4.x...

Specific error is

General Error.

General input/output error.

with no further explanation. It, in practice, has only occurred for me if there were greater than 40 rows for the spreadsheet; however, there did not seems to be a specific number of rows that caused the issue.

I have already created a workaround for the issue. This post is just to share my horrible, horrible solution for those that just need something.

Answer 1

I suspect the cause might be in the Zip headers or part of the zip entries themselves and that their is either a bug in the library that writes the Zip output for the System.IO.Packaging namespace (which I assume that OpenXML uses) or that OpenOffice has a very simple zip reader. Maybe something with the central directory file offsets versus the compressed file size, but I did not bother to check as I had limited time.

I may investigate further one day, or if anyone knows a quick solution, then do let me know. The files are written using the examples found on MSDN and they do open correctly in Excel.

In the meantime, if anyone needs a band-aid to the issue, I am posting my quick fix here since I was unable to find one myself. It expects a byte array (perhaps dumped from MemorySteam or read a FileStream). It outputs another byte array.

Someone clever could have it accept a Stream, seek to 0 relative to Beginning, and then read from there, perhaps writing directly to another passed in stream. That would be an exercise to the reader, unless someone happens to post a response that does the same.

If anyone does have a better solution, I would not mind knowing.

Uses .NET 4.5

References System.IO.Compression

using System;
using System.IO;
using System.IO.Compression;

namespace redmasq {
   public static class ExcelFileFixExample {
        public static byte[] XLSXOpenOfficePackageFix(byte[] fileData) {
            using (MemoryStream ms = new MemoryStream(fileData, false)) {
                using (ZipArchive za = new ZipArchive(ms)) {
                    using (MemoryStream ms2 = new MemoryStream()) {
                        using (ZipArchive za2 = new ZipArchive(ms2, ZipArchiveMode.Create)) {
                            foreach (ZipArchiveEntry entry in za.Entries) {
                                ZipArchiveEntry zae = za2.CreateEntry(entry.FullName, System.IO.Compression.CompressionLevel.Optimal);
                                using (Stream src = entry.Open()) {
                                    using (Stream dest = zae.Open()) {
                                        src.CopyTo(dest);
                                    }
                                }
                            }
                        }
                        return ms2.ToArray();
                    }
                }
            }
        }
   }
}