BlogAll Blog Posts | Next Post | Previous Post
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.
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:
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.
procedure TForm1.WebFilePicker1Change(Sender: TObject); begin if WebFilePicker1.Files.Count > 0 then WebFilePicker1.Files.Items.GetFileAsArrayBuffer; end; procedure TForm1.WebFilePicker1GetFileAsArrayBuffer(Sender: TObject; AFileIndex: Integer; ABuffer: TJSArrayBufferRecord); begin WebXLSX1.Load(ABuffer); end;
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.
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').
procedure TForm1.WebXLSX1WorkbookLoaded(Sender: TObject); var I: Integer; begin for I := 0 to WebXLSX1.SheetNameCount - 1 do WebListBox1.Items.Add(WebXLSX1.SheetNames[I]); end;
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!
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.
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;
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.
This blog post has received 7 comments.
All Blog Posts | Next Post | Previous Post