Wiki source code of Excel Integration

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

1.1	1	= Excel Integration - Tables and API =
	2
	3	The Excel Export integration is achieved with several functions:
	4
	5	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 ==
	30
	31
	32
	33	Excel files can be read using the ExcelWorkbook API:
	34
	35	{{code language="javascript"}}
	36	var workbook = ExcelWorkbook.loadWorkbook(data.custFile.sysData, "ISO-8859-1");
	37	{{/code}}
	38
	39	Using this API, you can iterate over rows and columns.
	40
	41	For example:
	42
	43	{{code language="javascript"}}
	44	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();
	57	e.myProp1 = val1;
	58	e.myProp2 = val2;
	59	e.Save();
	60	}
	61	{{/code}}
	62
	63
	64
	65	== 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.
	68
	69	We need:
	70
	71	* 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
	85
	86	[[image:img.png]]
	87
	88
	89	add the file attachment property:
	90
	91	[[image:img2.png]]
	92
	93
	94
	95	(((
	96	(% 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
	98
	99	(((
	100	[[image:img3.png]]
	101	)))
	102	)))
	103
	104	The script for the operation is:
2.1	105	{{code language="javascript"}}var workbook = ExcelWorkbook.loadWorkbook(data.custFile.sysData, "ISO-8859-1");
1.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();
	118	e.myProp1 = val1;
	119	e.myProp2 = val2;
	120	e.Save();
	121	}{{/code}}
	122
	123	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:\\
	127
	128	[[image:img4.png]]
	129
	130	Wrap up of the entity:
	131
	132
	133	[[image:img5.png]]
	134
	135
	136	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	{{code language="javascript"}}
	146	importLibrary("sysExcelUtils");
	147
	148	// load the excel file
	149	var xls = sysExcelUtils.loadExcelTemplate("app.resa.presta", "ISO-8859-1");
	150	if(xls == null)
	151	throw "Unable to load excel template app.resa.presta";
	152
	153	var sheet = xls.getSheetByPosition(0);
	154
	155
	156	// lookup the data
	157	var flt = data.getFilter();
	158	flt.setPageSize(-1);
	159
	160	var count = flt.count();
	161	progress.total = count;
	162
	163	var line = 0;
	164	var results = flt.search();
	165	var fmt = new Array();
	166	for(var i=0; i<10; i++)
	167	fmt[i] = sheet.getCellFormat(i,1);
	168
	169
	170	while(results.hasNext()) {
	171	var presta = results.next();
	172	print(presta);
	173	line++;
	174	progress.current = line;
	175
	176	sheet.setCellValue(0,line,presta.rqStartTime, fmt[0]);
	177	sheet.setCellValue(1,line,presta.rqEndTime, fmt[1]);
	178	var room = presta.rqRoom;
	179	sheet.setCellValue(2,line,room == null ? null : room.sysLabel, fmt[2]);
	180	var resa = presta.rqRoomResa;
	181	sheet.setCellValue(3,line,resa == null ? null : resa.rqTitle, fmt[3]);
	182	sheet.setCellValue(4,line,resa == null ? null : resa.rqAttendeeNumber, fmt[4]);
	183	sheet.setCellValue(5,line,presta.rqQty, fmt[5]);
	184
	185	sheet.setCellValue(6,line, resa == null ? null : resa.rqDescription, fmt[7]);
	186	var cat = presta.rqCatalogCategory;
	187	sheet.setCellValue(7,line, cat == null ? null : cat.sysLabel, fmt[7]);
	188	var prod = presta.rqReference;
	189	sheet.setCellValue(8,line, prod == null ? null : prod.sysLabel, fmt[8]);
	190
	191
	192	}
	193	// generates the attachment
	194	data = xls.getWorkbookFile();
	195
	196
	197	{{/code}}
	198
	199
	200	= Excel API Reference =
2.1	201
	202	* Main API for Excel Read and Write:
2.2	203	[[ExcelWorkbook>>Main.JSExcelWorkbook]]
2.1	204
	205	* Manipulate writable workbooks:
2.2	206	[[ExcelWritableWorkbook>>Main.JSExcelWritableWorkbook]]
	207	[[ExcelWritableSheet>>Main.JSExcelWritableSheet]]
2.1	208
	209	* Manipulate readonly workbooks:
2.2	210	[[ExcelReadonlyWorkbook>>Main.JSExcelReadonlyWorkbook]]
	211	[[ExcelSheet>>Main.JSExcelSheet]]
	212	[[ExcelCell>>Main.JSExcelCell]]

Wiki source code of Excel Integration

Quick Links

My Recent Modifications