Export to XLS

VCL TMS VCL UI Pack
1

I'm trying to export 100 000 rows from TAdvStringGrid to XLS file. If I try using

StringGrid->SaveToXLS("export.xls", true);

i get exception: "EOleSysError with message 'Operation unavailable" . After that, MS Excel starts and I get a warning saying that export.xls is in a different format then specified by the file.

Then I tried using TAdvGridExcelIO but then I aslo get an exception saying List bound(65536). I guess it's limited to that number of rows. Also, I don't see the option to stop opening MS Excel automatically after I exported data.

What to do?

2

Using C++ Builder 2010, Win7X64 Pro, Office 2007

3

SaveToXLS() exports to the .XLS file format.

The XLS file format itself has a limitation of 65535 rows, hence we can't export more than the XLS filel format allows.
4

Is there support for .xlsx format?

5

Using grid.SaveToXLS() / grid.LoadFromXLS() you can now already use .XLSX if you have Excel 2007 or Excel 2010

installed on the machine. Native XLSX integration will come later this year. 

6

Thanks. I'll try it.

7

Ok. I've done it using SaveToXLS method (with default file extension = '.xlsx') but I still get  "EOleSysError with message 'Operation unavailable" exception each time I call it. Could you look how to deal with it?

Also, maybe you should add extra parameter in SaveToXLS method: bool OpenDocument or something like that since I don't need excel document to be automatically opened after I've exported data from StringGrid.

Thanks.

8

Do you have a working version of Excel 2007 or Excel 2010 installed on your machine?
Maybe your install of Excel is also damaged. I'd suggest to try this on another machine.

9

Office 2007 installed. Tried on two computers with win7 (x86 and x64) and winxp (32bit). Each time there is an exception.

10

Press Continue and the XLSX file should be created.
This is a normal & by design exception that is gracefully handled when your app runs outside a debugger.
When you run from the IDE, your IDE will by default show this exception, even when it is handled.

Try to run your app outside the IDE.
11

I know it works outside IDE. Just didn't know how serious it is or will it cause problems in certain conditions.

I still hope you will update TAdvGridExcelIO to support xslx.

Thanks. 

12

As explained, it is a fully handled normal exception.
We're working on a solution to import/export to XLSX in the near future.

13

When using TAdvGridExcelIO with GridStartRow = 0 it removes all grid lines for the header row? Also, is it possible to set cell text alignment to Center (default = Top)?

14

I found the "problem". If grid's Look property is set to glWin7 then it removes grid lines for header row.

15

If you want to keep the settings as defined in TAdvStringGrid, please set AdvGridExcelIO.Options.ImportCellProperties = false