Generating Excel and Word Documents (X Evolution 3)

Scope

Languages: Java, .NET
Interfaces: Web, Win

Word Document data type only applies to Java generator. .Net implementation is deprecated (GXoffice) 

Introduction

Since GeneXus 9.0 provides the possibility of generating Excel documents without the need to interact with Office.
Java generator, using SUN virtual machine, has another way to generate Excel, which does not need to interact with MS Office.

This also applies to Word files, and it is quite useful in Java web applications, where the use of gxoffice is not recommended on account of scalability reasons. .Net generator does not support WordDocument.

Description

ExcelDocument

The implemented feature consists of generating Excel documents without the need to interact with MS Excel. It's another way to generate Excel files. The main advantage it provides is the ability to generate Excel documents in environments such as Web Applications, without having MS Excel installed in the server.

To avoid using automation with Excel (this means that the application doesn´t connect to MS Office); you must do the following:

1.&ExcelDoc.UseAutomation = 0 // &ExcelDoc is ExcelDocument type: http://www.gxtechnical.com/gxdlsp/pub/genexus_8.0_help_system/data_types/exceldocument.htm
2. Also, put the poi.jar file in the classpath (WinApp) or in WEB-INF\lib (WebApp)
 

The UseAutomation property is not necessary  since Genexus X 

Examples

In the following example, we generate an excel document using POI.
&excel is ExcelDocument Type.

Note: Place the "UseAutomation" before the "Open" command line.

Event "Excel"
   &ExcelDocument.UseAutomation = 0   //this property it is not neccesary since Genexus X version, see note bellow
   &ExcelDocument.Open(&File)  

   If &ExcelDocument.ErrCode <> 0 
       msg(&ExcelDocument.ErrDescription) 
   Else 
       &ExcelDocument.Clear()  
    
       &ExcelDocument.Cells(1,1).Text = 'Customers list'  
       &ExcelDocument.Cells(1,1).Bold = 1 
       &ExcelDocument.Cells(1,1).Color = 54 
       &ExcelDocument.Cells(1,1).Size = 10 
        
       &ExcelDocument.Cells(3,1).Text = 'Customer Id' 
       &ExcelDocument.Cells(3,1).Italic = 1 
       &ExcelDocument.Cells(3,1).Bold = 1 
    
       &row = 4 
       &col = 1 
       For Each Line in Grid 
    
               &ExcelDocument.Cells(&row,&col).Number = CustomerId 
               &ExcelDocument.Cells(&row,&col).Color = 3 
               &row += 1    
              
       Endfor  
       &ExcelDocument.Save()  
   Endif
EndEvent 
 

Note : (**) UseAutomation property is deprecated since GeneXus X version, for more information please refer here

.NET

.NET generates only Excel files, 

There are several possible implementations: 

•     EPPLUS: Since GeneXus X Evolution 1 Upgrade 8 and Genexus X X Evolution 2, a new implementation for XLSX file exists. Genexus uses this implementation when xlsx file extension is configured (which is recomendable). Check here for more information. 
•     Apache/Jakarta POI: The necessary files can be downloaded from here; it applies when using .Net framework 2.0 version. You need to copy the Jakarta DLL files or ExcelLite files to your kbase DATA00x\bin directory. You also need Microsoft J# Redistributable Package. Deprecated as since GeneXus 15.
•     ExcelLite DLL: The necessary file - GemBox.Spreadsheet.dll - is available after installing GemBox's spreadsheet software
•     Gxoffice: It uses a Gxoffice2.dll that must be registered (COM component). This implementation is deprecated.

See more details here

Java

Since GeneXus Xev2 Upgrade 2 a new implementation for XLSX file was done. Check here for more information.

Managing excel documents common issues

WordDocument 

The implemented feature consists of being able to generate Word documents interacting with OpenOffice instead of MSOffice (32 bit DLL implementation). The advantage is that, in addition to avoiding the need to have MSOffice installed in the server and use only a JRE 32 bit VM, OpenOffice has its server that listens to client orders, therefore it does not need to be in the same server where the application runs.

To use OpenOffice instead of MSOffice, you must do the following:
1. &WordDoc.UseMSOffice = 0 //&WordDoc is WordDocument Type: http://www.gxtechnical.com/gxdlsp/pub/genexus_8.0_help_system/data_types/WordDocument.htm
2. You have to include the following jar files in the WEB-INF\lib directory of your application in Tomcat:

unoil.jar
ridl.jar
juh.jar
jurt.jar
sandbox.jar

They are under the OpenOffice installation, under program\classes folder.

3. Besides, the OpenOffice server must be operating. It is started as follows:

<OOffice_path>\soffice -accept="socket,host=<ServerName>,port=8100;urp;StarOffice.ServiceManager"

More information...
SAC #18847