Is there anyway I can update a spreadsheet in the same manner as virtual mode e.g. without reading the contents of the whole file.
I have a spreadsheet that causes a out of memory exception when trying to load. I solved this problem by using the virtual mode to read the contents of the spreadsheet for display and I can access the comments etc but I cannot work out how to update a row/cell value without opening the file.
Thanks
Hi,
Virtual mode for writing is in the plans, but sadly we haven't got time yet to implement it. When implemented it will have some limitations, like for example it will work only with xlsx files (xls files are very difficult to write "as you go", as you need to update the file in many places, but then, xls files can only have 65536 rows so it shouldn't be a problem). Other limitation will be that it won't be able to recalculate, as the cells aren't in memory, and if you have a1000: =A2, we would have to fetch the value of A2 when we are already writing cell A1000.
But in the meantime until we implement it, we've reduced a lot the memory usage in FlexCel 6 (about 1/2 of the memory used in 5.7, but it depends on the specific file). I wonder if you could use that to load the file in memory.
If you are interested in testing it, please drop me an email to adrian@tmssoftware.com with your registration info and I will send you a v6 beta so you can try it.
Regards,
Adrian.
OK I didn't think you could so how do I open, read and update a 70mb spreadsheet with 24999 rows in it?
And yes I would like to beta test. THanks
25000 rows don't look as that much, we are testing with 700,000 row x 50 columns spreadsheets, and it is ok. But of course, it depends on the type of file you have.
To open, read and update you would do as always:
XlsFile xls = new XlsFile("myfile.xlsx", true);
xls.SetCellValue(1,1,"hello")
xls.Save("myresult.xlsx");
As said, FlexCel 6 should consume about half the memory than FlexCel 5 for a normal file, so if you were getting out of memory errors in 5, the file might load fine in 6.
For the betatest, please send me an email to adrian@tmssoftware.com with your registration info, I'll send you the latest.
I'll send the info but I'd like to see if I'm doing something wrong in v5.
Its a straight forward a file as you can get! 18500 rows * 79 cells. FlexCel and the APiMateseems not to be able to open it - I get memory expcetions?
Perhaps I am doing something wrong when I create the file?
Creating the file I use:
_xlsFile = new XlsFile(true);
_xlsFile.NewFile(1, TExcelFileFormat.v2010);
//do my stuff
_xlsFile.Save("test123.xlsx");
The do my stuff basically write a set of assorted data values. I can send you the file if you want?
ApiMate might return memory exceptions because it will convert the full file to text, and this will be a huge huge text file (with lots of "xls.SetCellValue(...)" strings).
It isn't designed for that, you should use APIMate with small files to investigate how things are done. Can you open the file with the "Custom Preview" demo? (Modules\25.Printing and Exporting\20.CustomPreview )
You can send me the file and I can take a look, but if it is 70 mb probably not by mail. You should upload it to a place like dropbox or skydrive (or some ftp server) and send me the link.
I've sent you an xlsx version of the file (8m) - I get the same problem so...
I got the problem in my code first using fileOpen and was using APIMAte and the Virtual Mode to consider my options. VIrtual mode is get and fast but I need to update the file!
Excel can open this file and any other of the large (10mb+) spreadsheets I have.
btw the email will be from gharrison at VIPR Solution . com.
Thanks for the email, I've gotten it and answered it.
As said there, I think this file should open fine even in v5, but not APIMate. APIMate is designed for small files, or it wil generate GBs of text and the textbox we use to display it will probably complain.
If you open this file with
XlsFile xls = new XlsFile("myfile.xlsx", true);
xls.SetCellValue(1,1,"whatever");
xls.Save("result.xlsx");
It should work fine, allow you to edit it and not use that much memory.