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

106

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

107

108

var sheet = workbook.getSheetByPosition(0);

109

110

// iterate through the rows

111

var rows = sheet.Rows();

112

// ignore the 3 first rows

113

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

114

115

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

116

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

117

118

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)

125

126

127

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.

138

139

140

= Writing Excel files =

141

142

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.

143

144

== Example of an Excel file writing code ==

145

146

147

importLibrary("sysExcelUtils");

148

149

// load the excel file

150

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