TAdvGridExcelIO read/write .xlsx

I am wondering if TAdvGridExcelIO support read/write .xlsx.

If yes, how to set it up?

Thanks in advance.

TAdvGridExcelIO is designed for XLS.
For XLSX we have TMS Grid Filters bridge to TMS Flexcel

https://www.tmssoftware.com/site/advgridfilters.asp

Hi I have AdvstringGrid installed as a part of my TMS component pack. When I installed TMS Grid Filters, it said that "TMS Advstringgrid is not installed....". Is it anything I missed? Thanks!

Is this TMS VCL UI Pack?
The latest TMS Grid Filters is adapted to work with TMS VCL UI Pack.

Hi,


I have TMS VCL UI Pack installed and removed my old TMS Pack. The grid filters works well to export grid to .xlsx file!

I have another question: how to export multiple grids to multiple sheets in the same .xlsx file?

Here is my code:
//export the first string grid
AdvGridExcelExport->AdvStringGrid=MyAdvStringGrid1;
AdvGridExcelExport->Export(MyXLSFileName, "Sheet1");
//switch to a different string grid
AdvGridExcelExport->AdvStringGrid=MyAdvStringGrid2;
AdvGridExcelExport->Export(MyXLSFileName, "Sheet2");

For some reason, only the second the grid is exported. I use the same logic for GridExcelIO component. It was ok. I think I must have something wrong when I set up AdvGridExcelExport. Please help!
Thanks in advance!

Gene.

Hi,

The logic in AdvGridExcelExport is indeed a little different. The original AdvExcelIO wasn't originally designed to save to multiple sheets,  so we had to come out with many ugly workarounds, which weren't very intuitive or performant (each ->Export has to open the existing file, and save it to disk. If you have to export 100 sheets, there is a lot of opening and saving the same file)

AdvGridExcelIO is built around a trimmed version of FlexCel 3 engine, while in AdvGridExcelExport we use the real FlexCel engine. This means that you can use all of FlexCel objects together with AdvGridExcelExport and this makes a lot of stuff much simpler (like for example if you wanted to password protect the file, or do any manual customizations to the output).

Basically, for everything more advanced than just saving a grid or AdvWorkbook to a file,  you should use the FlexCel objects directly. This is how you could do this for this example:



  //create a file with 2 sheets
  std::unique_ptr<TXlsFile> xls(new TXlsFile(2, TExcelFileFormat::v2019, true));
  //select first sheet
  xls->ActiveSheet = 1;
  xls->SheetName = "Sheet1";
  AdvGridExcelExport->AdvStringGrid = MyAdvStringGrid1;
  AdvGridExcelExport->Export(xls.get(), TInsertInSheet::Clear);


  //select second sheet
  xls->ActiveSheet = 2;
  xls->SheetName = "Sheet2";
  AdvGridExcelExport->AdvStringGrid = MyAdvStringGrid2;
  AdvGridExcelExport->Export(xls.get(), TInsertInSheet::Clear);


  //finaly save the full file with all the sheets.
  xls->Save("test.xlsx");


As said, the main advantage here is that you have a full TXlsFile object, which you can use to customize the output as much as you like, using xls->Command() before saving. Remember also that FlexCel comes with an APIMate tool (search for it in the start menu) that will give you the commands you need to do to create some specific task. There is a button in APIMate to return C++ code too.

Forgot to say, in the .h file, you need to:



#include "VCL.FlexCel.Core.hpp"
#include "FlexCel.XlsAdapter.hpp"

 I do have FlexCel. If I use FlexCel, I have to manually write out all cells again. is there a way I can export AdvStringGrid with FlexCel?


I am using both export and Import. I would like to take the easiest approach if there is one.

Thanks!

Hi,

No, you don't have to manually write out all the cells, that's the idea.
The code I posted above does what you were doing in with AdvExceIO: Exports grid1 to sheet1 and grid2 to sheet2, and it is about the same number of lines of code.

The call:

AdvGridExcelExport->Export(xls.get(), TInsertInSheet::Clear);


Is the one that writes out the cells. Conceptually, this is doing the same as before, only that before you could only export to a file. I pseudocode, when you wrote in AdvGridExcelIO:


AdvGridExcelExport->Export(MyXLSFileName, "Sheet1");


internally that would do, using a v3 of FlexCel:

  XlsFilev3* xls{...}
  xls->Open(MyXLSFileName);
  xls->AddSheet();
  xls->ActiveSheet = LastSheet;
  ExportToSheet(grid);
  xls->Save(MyXlsFileName);


It is just that you didn't had control over the XlsFile object. Everytime you added a sheet with that method, you would read and existing file, add a sheet, export the sheet and save it, which resulted in lots of extra opening and saving.

Now, as AdvGridExcelExport relies in you having a real version of FlexCel installed, we can give you control over the XlsFile object itself, and you can export to that object instead of a file. But the component is doing the same work as before, taking care of manually writing all cells to the file.