Excel Integration

Last modified by Maurice Gasco on 2018/06/07 16:57

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:

var workbook = ExcelWorkbook.loadWorkbook(data.custFile.sysData, "ISO-8859-1");

Using this API, you can iterate over rows and columns.

For example:

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();
}

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

importLibrary("sysExcelUtils");

// 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

Tags:

Created by Pierre Dubois on 2011/10/07 10:22