Excel Integration (Main.Excel)

= 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:

6

7

* there is one excel spreadsheet

8

* all columns on the table are exported as columns on the spreadsheet (and only those)

9

* 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.

10

11

For any different usage, you should use the Excel API for exporting the data you want with the format you want.

12

13

14

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.

15

16

17

All Excel API are provided by the the Excel Bundle (read and write Excel Files).

18

19

Note: From version 2.2.491 , the Excel Bundle uses Apache POI 3.8. This provides additional support for:

20

21

* Microsoft Office 2007 Excel files (xlsx)

22

* More complex formatting of Excel spread sheets

23

24

Before version 2.2.491, the Excel bundles uses JExcelAPI. This library does not support the above functionalities.

25

26

27

= Reading Excel files =

28

29

== ExcelWorkbook API ==

Excel files can be read using the ExcelWorkbook API:

34

35

36

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

37

38

39

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

For example:

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

45

46

var sheet = workbook.getSheetByPosition(0);

47

48

// iterate through the rows

49

var rows = sheet.Rows();

50

// ignore the 3 first rows

51

for(var row=3; row<rows; row++) {

52

53

var val1 = sheet.Cell(0,row).Value;

54

var val2 = sheet.Cell(1,row).Value;

55

56

var e = myEntity.New();

e.myProp1 = val1;

e.myProp2 = val2;

e.Save();

}

== An complete example of Excel upload and read ==

66

67

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

72

* an action to upload the file

73

* an operation to process the file

74

75

On this example we will:

76

77

- Create a small support entity to upload the file: one propery: file attachment

78

- Create an Import Operation

79

- Create an upload action

80

- Create a small excel file

81

- Upload your excel file

82

83

84

1 - Create a small support entity

[[image:img.png]]

add the file attachment property:

[[image:img2.png]]

(((

(% style="margin-top: 1em; margin-right: 0px; margin-bottom: 1em; margin-left: 0px; padding-top: 0px; padding-right: 0px; padding-bottom: 0px; padding-left: 0px; border-top-width: 0px; border-right-width: 0px; border-bottom-width: 0px; border-left-width: 0px; border-style: initial; border-color: initial; outline-width: 0px; outline-style: initial; outline-color: initial; font-weight: inherit; font-style: inherit; font-size: 14px; font-family: sans-serif; line-height: 1.4em; position: static; border-collapse: collapse; " %)

97

2 - Create an Import Operation

(((

[[image:img3.png]]

)))

)))

The script for the operation is:

105

{{code language="javascript"}}var workbook = ExcelWorkbook.loadWorkbook(data.custFile.sysData, "ISO-8859-1");

106

107

var sheet = workbook.getSheetByPosition(0);

108

109

// iterate through the rows

110

var rows = sheet.Rows();

111

// ignore the 3 first rows

112

for(var row=3; row<rows; row++) {

113

114

var val1 = sheet.Cell(0,row).Value;

115

var val2 = sheet.Cell(1,row).Value;

116

117

var e = myEntity.New();

e.myProp1 = val1;

e.myProp2 = val2;

e.Save();

}{{/code}}

Note that you may have to adapt the name of the file property (custFile in our example)

124

125

126

3 - Create an upload action:\\

[[image:img4.png]]

Wrap up of the entity:

[[image:img5.png]]

Then you can test you Excel import with a small Excel file.

137

138

139

= Writing Excel files =

140

141

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.

142

143

== Example of an Excel file writing code ==

144

145

146

importLibrary("sysExcelUtils");

147

148

// load the excel file

149

var xls = sysExcelUtils.loadExcelTemplate("app.resa.presta", "ISO-8859-1");