Blog

All Blog Posts | Next Post | Previous Post

Small XLSX component to deal with Excel files in TMS Web Core applications

Tuesday, August 25, 2020

Many times a quick and easy way is needed to export some basic data from a grid into an Excel file. We all know that Excel has lots of various features and covering the offered functionalities would require a huge library that we probably don't need for smaller tasks. We wanted to create something for these smaller tasks that covers reading, editing and writing Excel files locally in the client application while avoiding the complexities of a huge library.

TWebXLSX

TWebXLSX is a new free non-visual component as a wrapper around the open-source ExcelJS JavaScript library. Keep in mind, that ExcelJS only supports XLSX format.

Loading an excel workbook is as easy as calling TWebXLSX.Load with an array buffer as a parameter. In case of a TWebFilePicker, loading an XLSX file looks like this:

procedure TForm1.WebFilePicker1Change(Sender: TObject);
begin
  if WebFilePicker1.Files.Count > 0 then
    WebFilePicker1.Files.Items[0].GetFileAsArrayBuffer;
end;

procedure TForm1.WebFilePicker1GetFileAsArrayBuffer(Sender: TObject;
  AFileIndex: Integer; ABuffer: TJSArrayBufferRecord);
begin
  WebXLSX1.Load(ABuffer);
end;

You can save the workbook by calling TWebXLSX.Save with the filename as a parameter, which will then download the file to the device of the user.

ExcelJS is a workbook manager and it does not provide a visual element to display the data that it contains. Because of this, we added a Grid property where you can assign a TWebStringGrid or a TWebTableControl depending on your needs. TWebXLSX will load the data from the first sheet of the workbook into the grid automatically. If you want to change between the different sheets, you can use the TWebXLSX.ActiveSheet string property to define the active sheet name. If you don't know what sheet names are available, you can loop through the TWebXLSX.SheetNames property to find them out.

procedure TForm1.WebXLSX1WorkbookLoaded(Sender: TObject);
var
  I: Integer;
begin
  for I := 0 to WebXLSX1.SheetNameCount - 1 do
    WebListBox1.Items.Add(WebXLSX1.SheetNames[I]);
end;

When you drop a TWebXLSX component onto the form, it creates an empty workbook. Sheets to this empty workbook can be added with TWebXLSX.AddNewSheet('sheetname'). Sheets can also be removed by calling TWebXLSX.RemoveSheet('sheename').
Sometimes a workbook contains sheets that don't have any data. You can detect if a sheet has any rows by calling TWebXLSX.IsEmptySheet('sheetname').

When it comes to saving a workbook, you might want to apply some basic cell formatting. For this you can implement the OnSaveCell event which is triggered for each cell when the active sheet is saved (e.g. changing sheets or saving the workbook). With this event, you have direct access to the underlying ExcelJS cell object. Check out the styling documentation of ExcelJS to see which style settings are supported. You can also check the core libexceljs.pas file to see which properties we mapped already. If something that you'd like to use is missing, you can always extend it yourself!

procedure TForm1.WebXLSX1SaveCell(Sender: TObject; ARow, AColumn: Integer;
  AContent: string; var ACell: TJSExcelJSCellRecord);
begin
  if ARow = 0 then
    ACell.cell.style.alignment.horizontal := 'center';
end;

Would you like to customize your TWebStringGrid or TWebTableControl to reflect what's in your Excel file? You can also do that within the limits of the ExcelJS library by implementing the OnLoadCell event. Similarly to the OnSaveCell event, you have access to the underlying ExcelJS cell object that you can use to detect different cell types and styling. If you are not sure how to customize your grid, then take a look at our TMS WEB Core 1.5 Rimini tips & tricks blog.

And last but not least, you can use the OnWorkbookLoaded event when a workbook has finished loading, the OnSheetLoaded event when a sheet has finished loading into the grid and the OnNewSheetAdded event when a new sheet is added to the workbook.

Are you interested to see the TWebXLSX component in action? Check out our demo by clicking the button below!

As mentioned at the beginning of the artice, TWebXLSX is a free component that we are releasing as part of our TMS WEB Core Partner program and you can download it from here!

To install, open, compile & install the package from the "Component Library Source" folder. This will install the design-time TWebXLSX component.
For use at runtime, make sure that the "Core Source" folder is in your TMS WEB Core specific library path that you can set via IDE Tools, Options, TMS Web, Library path.

Tunde Keller

This blog post has received 9 comments.

1. Tuesday, August 25, 2020 at 11:39:56 PM

Thanks a lot, this is absolutely a very useful component.

Hennekens Stephan

2. Monday, June 21, 2021 at 8:48:18 PM

Hi Guys, just downloaded this component and tried to install by right clicking and choosing install on the DPROJ but get the error Error: Could not find include file ''TXLSX.RES.inc'' - the file doesn''t appear in the archive?

Wynne Mark

3. Monday, June 21, 2021 at 9:08:14 PM

I see nowhere a reference to TXLS.RES.inc. I can compile and install this component without any issue.
Please contact technical support with exact details.

Bruno Fierens

4. Monday, January 24, 2022 at 6:32:03 PM

Indeed a nice enhancement.

But do I understand this right, it is only intended to use on Windows, not on Linux or MacOS ( I can not add an other platform to the prject)?

Harry Stahl

5. Monday, January 24, 2022 at 9:51:14 PM

This is for TMS WEB Core web client applications and these web client applications can run on Windows, Linux and macOS in browsers installed on these operating systems.

Bruno Fierens

6. Tuesday, March 15, 2022 at 6:55:39 PM

Hi Bruno,
The component don''t work on Delphi 11 Alexandria.
Please can you provide an update?
On my system we updated TMSWEBCorePkgLibDXE13 to TMSWEBCorePkgLibDXE14 but when we try to install it raises an access violation error.

Dor Bujor Padureanu

7. Thursday, March 17, 2022 at 10:21:31 AM

I retested this here with Delphi 11 Alexandria and I cannot see a problem.
Opened ExcelJSXLSX.dpk, changed reference in requires to TMSWEBCorePkgLibDXE14 and then compiled & installed this package without any issue.

Bruno Fierens

8. Wednesday, February 14, 2024 at 9:19:51 PM

Hi, is this still current with Web Core v2.4.x.x? I am getting numerous isssues trying to install with Delphi 10.3.3 Rio, I get:
* [dcc32 Error] ExcelJSXLSX.dpk(36): E2200 Package ''TMSWEBCorePkgLibDXE12'' already contains unit ''WEBLib.XLSX''
* [dcc32 Error] ExcelJSXLSX.dpk(37): E2200 Package ''TMSWEBCorePkgLibDXE12'' already contains unit ''libexceljs''

When I go exploring, I find a very different WEBLib.XLSX.pas in Web Core\Core Source. Can you tell me what I need to do to get ExcelJS to work in Web Core now?

Berends Richard

9. Thursday, February 15, 2024 at 8:27:09 AM

TWebXLSX is now integrated directly into TMS WEB Core. A separate install is no longer needed.

Bruno Fierens

All Blog Posts | Next Post | Previous Post

Explore All Products

Blog

Small XLSX component to deal with Excel files in TMS Web Core applications

Tuesday, August 25, 2020

TWebXLSX

This blog post has received 9 comments.

Add a new comment

Blog Search