midanaf / excellibrary

Automatically exported from code.google.com/p/excellibrary
0 stars 0 forks source link

Invalid or corrupt file (unreadable content) when opening in Excel 2010 #102

Open GoogleCodeExporter opened 8 years ago

GoogleCodeExporter commented 8 years ago
Workbook wb = new Workbook();
Worksheet sheet = new Worksheet("Sheet1");

var headerCell = new Cell("Pools Report", CellFormat.General);
sheet.Cells[0, 0] = headerCell;

wb.Worksheets.Add(sheet);
wb.Save(filename);

I've attached the output file below. I'm very confused by this because I'm 
purposely creating the most simple spreadsheet possible just to test the lib. 

I do not have a lower version of Excel to test opening, it's possible only 2010 
has an issue opening it.

Original issue reported on code.google.com by motd...@gmail.com on 15 Jul 2011 at 11:57

Attachments:

GoogleCodeExporter commented 8 years ago
I forgot to say this, but I can use the lib to correctly open the file, I can 
enumerate the cells, and read the correct value from the cell ("Pools Report").

Original comment by motd...@gmail.com on 15 Jul 2011 at 11:57

GoogleCodeExporter commented 8 years ago
[deleted comment]
GoogleCodeExporter commented 8 years ago
I had the same problem. The reason that is happening is because the file is too 
small. You can add some white space to bring the size of the file up. Here is 
what I did after all my data was entered.

 for (int i = 0; i < 150; i++)
{
   col = 1;
   for (int j = 0; j < 10; j++)
   {
      myWorkSheet.Cells[row, col] = new Cell(" ");
      col++;
   }
      row++;
}

Hope that solves your problem.

Al

Original comment by mwang...@gmail.com on 18 Jul 2011 at 7:51

GoogleCodeExporter commented 8 years ago
How strange, that does indeed solve the problem. Thanks!

Original comment by motd...@gmail.com on 18 Jul 2011 at 8:32

GoogleCodeExporter commented 8 years ago
Thanks for the fix. This needs to get changed!

Original comment by Eric.Yar...@gmail.com on 2 Aug 2011 at 5:33

GoogleCodeExporter commented 8 years ago
I am also seeing this issue in Excel 2007

Original comment by wdelge...@gmail.com on 4 Aug 2011 at 10:23

GoogleCodeExporter commented 8 years ago
There is a 4096 byte boundary in:

WriteStreamData(string[] streamPath, byte[] data)
     if (entry.StreamLength < Header.MinimumStreamSize)...
...which results in a call to:

WriteShortStreamData(entry.FirstSectorID, data);

Somewhere down here produces bad output which cannot be read by Excel. Padding 
your data works, but is a bit ugly. A proper fix is required.

Also, it seems to me that in workbook.cs, this method should have a 'using':
public void SaveToStream(Stream stream)
{
     CompoundDocument doc = CompoundDocument.CreateFromStream(stream);
     using (MemoryStream mstream = new MemoryStream())
     {
         WorkbookEncoder.Encode(this, mstream);
         doc.WriteStreamData(new string[] { "Workbook" }, mstream.ToArray());
         doc.Save();
     }
}

Original comment by clements...@gmail.com on 6 Sep 2011 at 6:31

GoogleCodeExporter commented 8 years ago
Seeing this as well using the sample in ExcelLibrary.WinForm.  Pretty 
disappointing that the sample won't even work.

Original comment by StevenVi...@hotmail.com on 20 Sep 2011 at 7:30

GoogleCodeExporter commented 8 years ago
shabby.

Original comment by dom.bark...@gmail.com on 27 Oct 2011 at 5:56

GoogleCodeExporter commented 8 years ago
This lib really does have potential; Im having issues with my columns all 
having a size of ~8 pixels. They are there (with above pad fix) but they are 
all squished. Anyone out there experience this?

Original comment by marlons...@gmail.com on 26 Feb 2012 at 4:58

GoogleCodeExporter commented 8 years ago
Thanks to Comment 3 by mwang...@gmail.com, Jul 18, 2011
i also had this strange problem and used your padding suggestion and it worked 
for me thanks
i have changed it a bit to  add according to your table current row count and 
column count you need your output excel file to be somthing like more than 6 kb 
in size.
It can be that if you have few columns you need to add more rows. I have used 
it with 22 columns. 

            var minRows = 10;
            if (dt.Rows.Count < minRows)
            {
                for (int col = 0; col < dt.Columns.Count; col++)
                {

                    for (int row = dt.Rows.Count + 1; row < minRows ; row++)
                    {
                        worksheet.Cells[row, col] = new Cell(" ");
                    }
                }
            }
Eitan

Original comment by eavi...@gmail.com on 19 Mar 2012 at 8:34

GoogleCodeExporter commented 8 years ago
[deleted comment]
GoogleCodeExporter commented 8 years ago
I agree, padding the file with empty cells does cure the problem. But I didn't 
need to do this for excel 2007, only for excel 2003

using vs2010

Original comment by phil.s.c...@gmail.com on 7 Sep 2012 at 1:26

GoogleCodeExporter commented 8 years ago
Issue 122 has been merged into this issue.

Original comment by jetcat on 1 Mar 2013 at 11:00

GoogleCodeExporter commented 8 years ago
Great library, thanks for the fix :).

Original comment by jelleoos...@gmail.com on 4 Feb 2014 at 6:51

GoogleCodeExporter commented 8 years ago
Following on from comment #7 above, page 18 of this PDF: 

http://www.digitalpreservation.gov/formats/digformatspecs/Excel97-2007BinaryFile
Format(xls)Specification.pdf

...suggests that a "Simple Save" can't have a stream less than 4096 bytes long. 
 I'm not sure if what this library does is a simple save, but the following 
workaround does seem to fix the problem.

The fix: If the data to be written is less than 4096 bytes, then pad it up to 
that.  This then avoids saving to the short stream, like adding empty cells 
does.

Add this to the start of the function CompoundDocument.WriteStreamData(...):

            if (data.Length < 4096) // perhaps Header.MinimumStreamSize instead?
            {
                byte[] data2 = new byte[4096];        // new padded array
                Array.Copy(data, data2, data.Length); // copy shorter data across
                data = data2;                         // replace with new larger array
            }

This makes Excel 2007 open the file without error, and it doesn't add a number 
of blank cells to the workbook to do it.

Someone more familiar with the XLS file format than me will have to say whether 
or not this is a good/correct fix to the problem.

Original comment by stu...@gmail.com on 21 Aug 2014 at 2:33

GoogleCodeExporter commented 8 years ago
I'm getting corrupt file errors in Excel 2010, and my files are in the 
neighborhood of 500kb.

Original comment by thadarkl...@gmail.com on 30 Sep 2014 at 4:24