Excel Integration
Excel Integration - Tables and API
The Excel Export integration is achieved with several functions:
On every multicolumn table, you can find a small Excel button on the lower right. This will export the content of the table with the following rules:
- there is one excel spreadsheet
- all columns on the table are exported as columns on the spreadsheet (and only those)
- all rows on the tables - including those that are in subsequent pages are exported. If your table is large, this may result into a large Excel file.
For any different usage, you should use the Excel API for exporting the data you want with the format you want.
The Excel Import integration is provided by the Excel API for reading your Excel spreadsheet with its specific format. This requires a custom action and operation.
All Excel API are provided by the the Excel Bundle (read and write Excel Files).
Note: From version 2.2.491 , the Excel Bundle uses Apache POI 3.8. This provides additional support for:
- Microsoft Office 2007 Excel files (xlsx)
- More complex formatting of Excel spread sheets
Before version 2.2.491, the Excel bundles uses JExcelAPI. This library does not support the above functionalities.
Reading Excel files
ExcelWorkbook API
Excel files can be read using the ExcelWorkbook API:
Using this API, you can iterate over rows and columns.
For example:
var sheet = workbook.getSheetByPosition(0);
// iterate through the rows
var rows = sheet.Rows();
// ignore the 3 first rows
for(var row=3; row<rows; row++) {
var val1 = sheet.Cell(0,row).Value;
var val2 = sheet.Cell(1,row).Value;
var e = myEntity.New();
e.myProp1 = val1;
e.myProp2 = val2;
e.Save();
}
An complete example of Excel upload and read
Let's imagine we want to provide a small function to upload an Excel file and process it on the server.
We need:
- a small entity to handle the uploaded file and the custom action et operation
- an action to upload the file
- an operation to process the file
On this example we will:
- Create a small support entity to upload the file: one propery: file attachment
- Create an Import Operation
- Create an upload action
- Create a small excel file
- Upload your excel file
1 - Create a small support entity
add the file attachment property:
2 - Create an Import Operation
The script for the operation is:
var workbook = ExcelWorkbook.loadWorkbook(data.custFile.sysData, "ISO-8859-1");
var sheet = workbook.getSheetByPosition(0);
// iterate through the rows
var rows = sheet.Rows();
// ignore the 3 first rows
for(var row=3; row<rows; row++) {
var val1 = sheet.Cell(0,row).Value;
var val2 = sheet.Cell(1,row).Value;
var e = myEntity.New();
e.myProp1 = val1;
e.myProp2 = val2;
e.Save();
}
Note that you may have to adapt the name of the file property (custFile in our example)
3 - Create an upload action:
Wrap up of the entity:
Then you can test you Excel import with a small Excel file.
Writing Excel files
The Excel API can also be used to write Excel Files. You may also read an excel file for writing as a template and fill some values and save it for download.
Example of an Excel file writing code
// load the excel file
var xls = sysExcelUtils.loadExcelTemplate("app.resa.presta", "ISO-8859-1");
if(xls == null)
throw "Unable to load excel template app.resa.presta";
var sheet = xls.getSheetByPosition(0);
// lookup the data
var flt = data.getFilter();
flt.setPageSize(-1);
var count = flt.count();
progress.total = count;
var line = 0;
var results = flt.search();
var fmt = new Array();
for(var i=0; i<10; i++)
fmt[i] = sheet.getCellFormat(i,1);
while(results.hasNext()) {
var presta = results.next();
print(presta);
line++;
progress.current = line;
sheet.setCellValue(0,line,presta.rqStartTime, fmt[0]);
sheet.setCellValue(1,line,presta.rqEndTime, fmt[1]);
var room = presta.rqRoom;
sheet.setCellValue(2,line,room == null ? null : room.sysLabel, fmt[2]);
var resa = presta.rqRoomResa;
sheet.setCellValue(3,line,resa == null ? null : resa.rqTitle, fmt[3]);
sheet.setCellValue(4,line,resa == null ? null : resa.rqAttendeeNumber, fmt[4]);
sheet.setCellValue(5,line,presta.rqQty, fmt[5]);
sheet.setCellValue(6,line, resa == null ? null : resa.rqDescription, fmt[7]);
var cat = presta.rqCatalogCategory;
sheet.setCellValue(7,line, cat == null ? null : cat.sysLabel, fmt[7]);
var prod = presta.rqReference;
sheet.setCellValue(8,line, prod == null ? null : prod.sysLabel, fmt[8]);
}
// generates the attachment
data = xls.getWorkbookFile();
Excel API Reference
- Main API for Excel Read and Write:
ExcelWorkbook
- Manipulate writable workbooks:
ExcelWritableWorkbook
ExcelWritableSheet
- Manipulate readonly workbooks:
ExcelReadonlyWorkbook
ExcelSheet
ExcelCell