Hello
I am using VCL and version 3 with XLSAdapter, FlexCelImport and FlexCelGrid.
Can you please tell me what my options are ( or how to) to save or exporting sheets to an HTML file?
Thanks.< id="plugin0" ="application/x-dgnria" width="0" height="0" style=": ; : 1000">< name="tabId" value="66">
Hi,
Sadly FlexCel 3 doesn't export to html (or pdf) you will need to use FlexCel 5 for that.
If you are in a delphi version less than XE, we are working in a dll to allow you to access all the functionality of FlexCel 5 from older versions (including exporting to html)
Ok Thanks, I am using the following XE2, XLSAdapter, XLSTemplateStore, FlexcelImport and FlexcelGrid.With a lot of direct cell data read and write access from code.
Is it easy to upgrade to FlexCel 5 ? < id="plugin0" ="application/x-dgnria" width="0" height="0" style=": ; : 1000">< name="tabId" value="66">
You don't really need to upgrade unless you want to, you can keep using v3, and use v5 in parallel to do html/pdf exporting.
Before continuing, please make sure to read:
Now, to be more specific to your case:
1)Install FlexCel 5 (don't uninstall FlexCel 3)
2)If you have a FlexCel 3 less than 3.7, it would be a good idea to update FlexCel 3 too. You can get the latest from the registered page, below FlexCel 5.
3)With FlexCel 3.7 comes a new component, XlsxAdapter, which you can use instead of XlsAdapter. It is advisable to change all the XlsAdapters in your app by XlsxAdapters, since with an XlsxAdapter you can read/write both xls and xlsx files.
Note: For those reading this who don't know it: To replace XlsAdapter for XlsxAdapters, open the form/datamodule where they are, right click the form, choose "View as Text", search and replace xlsadapter by xlsxadapter, right click, choose "View as form" and save. The ie will pop up a message about the components being converted and this is all.
Up to here there were the things you needed to do to start using FlexCel 5. What's below is optional, and you can do it at your own peace, or not at all if you prefer it.
4)The template store is removed in FlexCel5, because it was kind of quirky. First it needed "refresh" everytime you changed a file (and I know myself I have shipped apps with not up to date template stores), and second, it will store the xls files in the form, making the forms quite big.
Template stores were needed in FlexCel 2 because we couldn't read from streams in the first versions, but they aren't longer needed and they haven't been for a long time. (also because the ide now has resource support built in).
So, while you can keep using template stores with XlsxAdapter, you could start thinking in replacing them.
To replace template stores, you would:
4a) add a new file with extension ".rc" to your project. Let's say you call it "templates.rc"
4b)Inside templates.rc include all your xls/xlsx files you want to embed in the exe. Add one line per file, like:
MYFILE1_XLS RCDATA "MyFile2.xls"
MYFILE2_XLS RCDATA "MyFile2.xls"
4c)That's it. Now to open the embedded file with FlexCelImport you would do:
procedure TForm6.Button1Click(Sender: TObject);
var
rs: TResourceStream;
begin
rs := TResourceStream.Create(HInstance, 'myfile1_xls', RT_RCDATA);
try
FlexCelImport1.LoadFromStream(rs);
finally
rs.Free;
end;
end;
you can of course create your own method "LoadTemplate" that does this so you don't have to save it every time. Embedding resources has the advantage over TemplateStore that if you modify the Excel file, save it, and recompile the app, the app will always use the latest, without needing to refresh.
5) Once you have all of this done, you might want to start replacing FlexCelImports by TXlsFile. TXlsFile is not a component but a class, but migration is nrmally very simple as all methods are similar.
You would use:
var
xls: TXlsFile;
begin
xls := TXlsFIle.Create(true);
try
xls.Open('file.xls');
xls.SetCellValue(1,1,'hello from FlexCel');
xls.Save('result.xls');
finally
xls.Free;
end;
the code in the middle of the try/finally (all the xls.method calls) are very similar in both FlexCelImport and TXlsFile. Some things changes, for example we don't use indexed properties anymore, and so FlexCelImport.CellValue[] property is now xls.GetCellValue and xls.SetCellValue methods. (for those wondering, we ahd to make this change because the indexed properties behaved very bad with C++ builder)
And, whenever you are in doubt with how a method translates from FlexCleImport to XlsFile, remember that you can call APIMate (start menu->TMS FlexCel->Tools->APIMate) on the file you created with FlexCelImport. Create the file as you are creating it right now, open it with APIMate, and it will tell you the FlexCel 5 code you need to create the same file.
6)Well, now that this is done, let's get back to business, how to export to html? In FlexCel 5 you can find a demo showing how to do it, but it would be something like:
procedure aToHtmlTest.TestAllowOverwrite;
var
xls: TXlsFile;
htm: TFlexCelHtmlExport;
begin
xls := TXlsFile.Create;
try
xls.Open('aFILE.xls');
htm := TFlexCelHtmlExport.Create(xls, true);
try
htm.ExportAllVisibleSheetsAsTabs('destfolder', 'pre2_', '_post.htm', '', '', TStandardSheetSelector.Create([TSheetSelectorPosition.Top]), true);
finally
FreeObj(htm);
end;
xls.Free
end;
There are many other options on how to export to pdf, please make sue to look at the demo.
If you have a FlexCelImport with XlsxAdapter instead of an XlsFile you want to export, you can use:
var
xls: TXlsFile;
begin
xls := (FlexCelImport1.GetWorkbook as TXlsxFile).GetTWorkbook;
And then use this v5 TXlsFile to export to html as above.
Well, this got long. Hope it helps, let me know about any doubts.
Hello Adrian
Thanks for the detailed explanation.
I am trying your sample code from your post here....
If you have a FlexCelImport with XlsxAdapter instead of an XlsFile you want to export, you can use:
var
xls: TXlsFile;
begin
xls := (FlexCelImport1.GetWorkbook as TXlsxFile).GetTWorkbook;
but i am running into an error...
[dcc32 Error] Unit1.pas(16673): E2010 Incompatible types: 'TXLSFile' and 'TXlsFile'
Any advice?
Thanks
Hi,
This is because there are 2 conflicting classes: TXLSFile (uppercase XLS) is defined in FlexCel 3, while TXlsFile (lowercase Xls) is defined in 5, causing a confusion on the compiler about which one you want to use.
This should be easy to fix. TXLSFile (upper) is defined in XlsAdapter.pas (from v3), but if you have replaced the TXlsAdapter by TXlsxAdapter, you don't need to use XlsAdapter anymore.
So just remove the XlsAdapter unit from your uses list.
If for any reason you need to keep using XlsAdapter and you can't remove it from your uses, you will have to fully qualify the names:
var
xls: FlexCel.XlsAdapter.TXlsFile;
begin
xls := (FlexCelImport1.GetWorkbook as TXlsxFile).GetTWorkbook;
Or you might also reorder the units so FlexCel.XlsAdapter (from v5) is used after XlsAdapter (from v3). Whatever you do, you just need to tell the compiler some way to use the TXlsFile from v5 and not TXLSFile from v3. As said, the easiest is just to remove uses XlsAdapter from your unit.