FlexCelReport Xlsx over 65536 rows

Hi all,


I'm fairly new with using FlexCel.  I have a pretty simple scenario: 

We're currently using the FlexCelReport object, we invoke the AddTable() method to add a DataTable with over 65536 rows.  If I call the Run method, it throws an exception indicating that I cannot run over 65536 rows.  Since I have the source, I modified the Run method to take in a XlsAdapter to save with the XlsxFormat inside the FlexCelReport::Run(stream,stream) method.  Whenever I do that, it corrupts the Excel sheet.

Can someone point me to the right direction?

Thanks.


Hi Robert,


I've got your email in my queue to answer, I haven't been able yet since it requires that we make you a new kind of trial, and we are trying to make a new release for monday, and I haven't got the time to look at it.

About this particular question, I am not sure on what you modified in the sources, but it is just impossible to have an xls file with more than 65.536 rows. We can't do it, Excel can't do it. And the reason is simple, rows in xls are stored using 2 bytes, and in 2 bytes you can store at most 65536 values. If you try to store a bigger value into those 2 bytes, you'll just overflow the data. Same with columns, they are saved in 1 byte, so you can have at most 256 columns. 65536 rows and 256 columns is a limitation in the xls file format, there is nothing anyone can do about it. If you try to save an xls file in Excel 2013/10/7 with more than 65536 rows, it won't let you either.

So to save over 65536 rows, you need to save as xlsx. Sadly from what I read in the mail you have an older version without xlsx support, so you need the trial for that. And I am no sure why, you are getting the trial message, which doesn't let you run the report. I'll repeat what I said in my last mail. You shouldn't get any trial message when running inside the debugger. That is, when pressing F5 or Run in Visual studio. (not if you press "ctrl F5" or "run without debugging").

If for any reasone you still get the error inside the debugger, the alternatives I see are:
1)Please wait until mid next week after we release 6.3, and I'll make you a trial available that won't show the message.
2)If you want, you can just buy it and use the registered version, if you stumble in any problem we can fix, I'll make you a refund. We normally prefer that people tries the stuff before buying (it is simpler for us and for them), but in this case, a "buy and if you can't make it work we'll refund you the money" approach can be a good way to workaround your trial problems.
3)Lastly, a kind of a hack, but something you can try. The trial adds the message every 3 rows: so you can make your trial template not to have any tags rows multiple of 3, and it should work for your tests.

Some final notes in saving more than 65536 rows:
1)Note that while xlsx isn't limited to 65536 rows, it still is limited to 1,048,576 rows and 16,384 columns. For those technically interested, this means they store now the row in 4 bytes and the columns in 2. Actually 4 bytes would give us 2^32  values, and 1,048,576 is 2^20, but they are reserving the other bits. And xlsx is xml, so the file format itself isn't limited as xls is: you could write <row>incredibly big number</row> and it will still work. But Excel itself loads those rows in 4 bytes, so they limit it anyway. In the future they might increase the number of rows without changing the file format, as they had to do when they added 1 million rows.

2)1 million rows will also use a lot of memory. For the structure of a spreadsheet we need to keep in memory, and even when FlexCel 6 is very optimized in memory usage, it will still use a lot of memory (and so will Excel when it opens the file). So it will take some resources to get to the xlsx limit, but it works fine.

Let me know which one of the 3 options you prefer for the trial.

Hi Adrian,


I actually have a license.  I was finally able to track down our old license and discovered that we have one month left from tomorrow.

All of what I'm saying is relevant to the 6.2.1.0 version that I just downloaded.

I tried to save with the XLSX format and I receive a corrupted Excel file (at least according to Office 2013).  Now, I undoubtedly could be doing something wrong.  However, it really simply is using the FlexCelReport object with an excel template and attempting to persist more than 65,536 rows.  Would it be more helpful for me to provide the source code of what I'm attempting?

(I understand the limits and issues with generating a report more than 65000 rows, I would say in instances like this that I just have to give the client what they want ...=/).

I cannot express how helpful you've been with the process.  Thank you very much!

Robert, 

I am glad you could finally test it. Over 65535 rows support has been working fine for some time already, but there could always be an issue.
Can you email me the file you generated to adrian@tmssoftware.com so I can take a look? I might ask you for the code later, but to begin taking a look at the file might be enough to figure out what happened.

Adrian has helped me with the problem.  I was using a MemoryStream and was using GetBuffer().  So, from the documentation that I have problems reading (but from the MSDN docs):


"Note that the buffer contains allocated bytes which might be unused"

Saving this in the regular XLS file format works fine as Excel doesn't complain.  However, Excel 2013 will complain and repair the file.  It is a problem and has nothing to do with the FlexCel library it works fine.

Using .ToArray() as opposed to .GetBuffer() makes this work perfectly well.