Hello, 

Is it possible to export data from datagrid egl to an excel file by clicking a button in a richui handler?

Thanks 

I don't have a way to generate an excel on the frontend (rui), but I have encapsulated some parts of Apache POI library to generate excel files.

I have immitated the ease of operation from a datagrid. (The function accepts an array of type any and an array of columns and returns the excel file)

If you could find a way to do it in javascript, you might be able to do it in the frontend.

Kind regards,

Bram

Is there a sample code  to understand Apache POI library and how you generated excel files?

Thanks

Due to lack of time I'll just paste the code bits I currently use. It might be too complex, because we store the generated files on our as400 ifs and add references to the database. Retrieving the file is a separate process.

    function createExcelFromArray(arr any[] in, columns ExcelColumnRec[] in, filename string in, omschrijving string in, headerString String? in, laatsteRij String[] in) returns(decimal(8))        wb Workbook = new HSSFWorkbook();        createHelper CreationHelper = wb.getCreationHelper();        sheet Sheet = wb.createSheet("Pag 1");        rowCounter int = 0;         row Row = sheet.createRow(rowCounter);        if (headerString != null)            headerCs CellStyle = wb.createCellStyle();            headerCs.setAlignment(CellStyle.ALIGN_CENTER);             c Cell = row.createCell(0);                 c.setCellStyle(headerCs);                 c.setCellValue(createHelper.createRichTextString(headerString));                sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, columns.getSize() - 1));                 rowCounter += 1;                 row = sheet.createRow(rowCounter);         end                csDate CellStyle = wb.createCellStyle();        csDate.setDataFormat(createHelper.createDataFormat().getFormat("m/d/yy"));        csWrap CellStyle = wb.createCellStyle();        csWrap.setWrapText(true);                 for(i int from 1 to columns.getSize())            c Cell = row.createCell(i - 1);            c.setCellValue(createHelper.createRichTextString(columns[i].title));        end        for(i int from 1 to arr.getSize())                rowCounter += 1;             row = sheet.createRow(rowCounter);            for(j int from 1 to columns.getSize())                c Cell = row.createCell(j - 1);                waarde any = getCellValue(i, columns[j].name, arr);                 strval string;                 try                        strval = waarde;                                onException(exception AnyException)                                                                        end                case                    when(waarde isa date)                        c.setCellValue((waarde as date) + 1);                        c.setCellStyle(csDate);                    when(waarde isa date?)                        if((waarde as date?) == null)                            c.setCellValue(createHelper.createRichTextString(""));                        else                            c.setCellValue((waarde as date) + 1);                            c.setCellStyle(csDate);                        end                    when(waarde isa String[])                        sArr String[] = waarde as String[];                         s String;                         for (z int from 1 to sArr.getSize())                                if (z == 1)                                        s += sArr[z];                                else                                        s += "\n" + sArr[z];                                end                        end                        c.setCellValue(createHelper.createRichTextString(s));                        c.setCellStyle(csWrap);                     when(waarde isa string)                        s string = waarde;                        c.setCellValue(createHelper.createRichTextString(waarde));                    when(strval is numeric)                        c.setCellValue(waarde as int);                    otherwise                                ispunt boolean = false;                                 try                                                        q decimal(10,4) = waarde as decimal(10,4) + 1;                                    c.setCellValue(waarde as decimal(10,4));                                                onException(exception AnyException)                                                end                end             end        end        if (laatsteRij.getSize() != 0)                rowCounter += 1;             row = sheet.createRow(rowCounter);            for (i int from 1 to laatsteRij.getSize())                c Cell = row.createCell(i - 1);                s string = laatsteRij[i];                c.setCellValue(createHelper.createRichTextString(s));            end        end                for (i int from 1 to columns.getSize())                sheet.autoSizeColumn(i-1);         end                f File = new File(filename);        SysLib.writeStdout(f.getCanonicalPath());        fos FileOutputStream = new FileOutputStream(f);        wb.write(fos);        fos.java_close();                pdfFile File = new File(f.getCanonicalPath());        bestandTypeRec UIBestandTypeRec{};        bestandTypeRec = BestandLib.getBestandType(0); // geentype        bestand UIBestandExtRec{bestandrec = new UIBestandRec{DatumWijz = datetimelib.currentTimeStamp(), omschr = omschrijving, origBestandNaam = filename, extensie = "xls"}, types =[bestandTypeRec                ]};        BestandLib.addBestand(bestand);        fDest File;        if(GeneralOperations.getHostname() == "AS40071.MOL.LOCAL")            fDest = new File(FileBasicOperations.movePathToQNC(bestand.bestandrec.pad));        else            fDest = new File(bestand.bestandrec.pad);        end        FileBasicOperations.copyFile(pdfFile, fDest);        FileBasicOperations.removeFile(pdfFile);        return(bestand.bestandrec.id);    end

This requires the following classes from Apache Poi

• Cell
• CellRangeAddress
• CellStyle
• CreationHelper
• HSSFWorkbook
• Row
• Sheet
• Workbook

to be created as external types like this:

externaltype Cell type JavaObject{packageName = "org.apache.poi.ss.usermodel", javaName = "Cell"}    function setCellValue(value boolean in);         function setCellValue(text RichTextString in);        function setCellValue(value float in);                 function setCellStyle(style CellStyle in);         end

Externaltype CellRangeAddress extends CellRangeAddressBase type JavaObject{packageName = "org.apache.poi.ss.util", javaName = "CellRangeAddress"}    constructor(firstRow int in, lastRow int in, firstCol int in, lastCol int in); end

Externaltype CellStyle type JavaObject{packageName = "org.apache.poi.ss.usermodel", javaName = "CellStyle"}            static ALIGN_CENTER smallint;                 function setDataFormat(format smallint in);        function setWrapText(wrapped boolean in);        function setAlignment(alignment smallint in);   end

externaltype CreationHelper type JavaObject{packageName = "org.apache.poi.ss.usermodel ", javaName = "CreationHelper"}            function createRichTextString(text String in) returns (RichTextString);        function createDataFormat() returns (DataFormat); end

externaltype HSSFWorkbook extends POIDocument, Workbook type JavaObject{packageName = "org.apache.poi.hssf.usermodel", javaName = "HSSFWorkbook"}    constructor();         end

externaltype Row type JavaObject{packageName = "org.apache.poi.ss.usermodel", javaName = "Row"}    function createCell(column int in) returns (HSSFCell); end

externalType Sheet type JavaObject{packageName = "org.apache.poi.ss.usermodel", javaName = "Sheet"}     function createRow(rowNum int in) returns (HSSFRow);        function addMergedRegion(region CellRangeAddress in);        function autoSizeColumn(column int in);  end

externaltype Workbook type JavaObject{packageName = "org.apache.poi.ss.usermodel", javaName = "Workbook"}    function getCreationHelper() returns (CreationHelper);        function createSheet(name String in) returns (HSSFSheet);        function write(outStream OutputStream in);              function createCellStyle() returns (CellStyle); end

ExternalType OutputStream extends Object type JavaObject{packageName = "java.io", javaName = "OutputStream"}    function flush();        function java_close(){JavaName = "close"};end

ExternalType FileOutputStream extends OutputStream type JavaObject{packageName = "java.io", javaName = "FileOutputStream"}    constructor(pad String in);        constructor(f File in);end

This is a quick example, just let me know if I can help refine it or translate some of the variable names.

Kind regards,

Bram

and of course you will need the apache poi jar in your project.

Could you explain me the input arguments of this function:

    function createExcelFromArray(arr any[] in, columns ExcelColumnRec[] in, filename string in, omschrijving string in, headerString String? in, laatsteRij String[] in) ?

And which file I should download from this site https://poi.apache.org/download.html#POI-3.11?

Thanks

Hi Michael,

arr is an array of records (cast to type any). this is similar to the data parameter of the DataGrid

columns is an array of ExcelColumnRec's, similar to the DataGridColumn on a DataGrid. It specifies which field in the record (from the first parameter) is to be put in this column.

filename is used to define the name of the generated file.

omschrijving is Dutch for description. I believe it is only used for adding the file to the database, but has no real impact on the generation of the file.

headerString is a text put at the top of the excel file. it is put in a cell spanning all columns.

laatsterij is Dutch for lastRow. it is an array of texts to put on the last row of the excel file. I use this for displaying totals.

Kind regards,

Bram

Thanks a lot!

Sorry but which file I should download from this site https://poi.apache.org/download.html#POI-3.11?

Thanks

the bin zipped files contain the packaged jar files required.

the src zipped files would require you to build the jars yourself. --> I recommend the bin file

You can choose .zip or .tar.gz

I believe .zip wil be easiest.

Kind regards,

Bram

Hi Bram,

what do you mean by:

This requires the following classes from Apache Poi

• Cell
• CellRangeAddress
• CellStyle
• CreationHelper
• HSSFWorkbook
• Row
• Sheet
• Workbook

to be created as external types like this:

I have copied your external types in to my project and added the poi jar to my java buildpath..

But i get the error 'can not be resolved', please take a look at the screenshot.

Hi Marcel,

I forgot to mention the dependency classes, which also have to be coded as an externaltype.

I have added the sources in attachment

Thank you very much Bram!!!

But what about the type 'File' ?

Kind regards!

Sorry, it's an externaltype dependency from another package. It's not so easy to spot when everything is set up.

Here's the code:

package com.molcy.webservices.externaltypes.java.io;externalType File extends Object type JavaObject{packageName = "java.io", javaName = "File"}    constructor(pad String in);        function exists() returns (boolean);        function getCanonicalPath() returns (String);        function length() returns (bigint);        function getName() returns (String);end

Please let me know if I missed something.

Hi Bram,

thank you for your help!

Only the "getCellValue" function and the "FileBasicOperations" externaltype is missing.

Thanks for being patient with me. Here are the missing bits.

This is in the same file as the excel function:

    private function getCellValue(row int in, fieldName string in, data any[] in) returns(any)        object any = data[row];        if(object == null)            return("");        end        velden String[] = getTokens(fieldName, ".");                try                        veldnaam String = velden[1];                        b any = data[row][velden[1]];                        for (i int from 2 to velden.getSize())                                b = b[velden[i]];                                veldnaam = velden[i];                        end                        case                                 when (b isa String[])                                         return (b as String[]);                                 otherwise                                        return (b);                        end                        //veldwaarde String = b as String;                        //return (b);                onException(exception AnyException)                        SysLib.writeStdout(exception.message);                         return ("");                end        end                private function getTokens(source String in, delimiter String in) returns (String[])                max int = strLib.byteLen(source);                i int = 1;                 tokens String[0];                 token String? = "";                 // end processing when the getNextToken function returns a null                while(i < max && token != null)                   token = StrLib.getNextToken(source, i, delimiter);                   if (token != null)                              tokens.appendElement(token);                   end                end                return (tokens);         end

FileBasicOperations is a custom Externaltype (meaning I created the java code, not a library, so you need to add the file below in EGL, and the next under the "Java Resouces/src"

package com.molcy.webservices.externaltypes.java.io;externalType FileBasicOperations type JavaObject{packageName = "com.molcy.fileoperations", javaName = "FileBasicOperations"}            static function copyFile(fSource File in, fDest File in);                static function removeFile(rFile File in);                static function movePathToQNC(winPad String in) returns (String);                static function MovePathToWindows(as400Pad String in) returns (String);                static function movePathToTmp(winPad String in) returns (String);                static function getExtension(pad String in) returns (String);                static function getFilePathsInFolder(pad String in) returns (String[]);                static function zipIt(zipPad String in, bestandpaden String[] in, namen String[] in);end

package com.molcy.fileoperations;import java.io.File;import java.io.FileInputStream;import java.io.FileNotFoundException;import java.io.FileOutputStream;import java.io.IOException;import java.io.InputStream;import java.io.OutputStream;import java.util.ArrayList;import java.util.List;import java.util.zip.ZipEntry;import java.util.zip.ZipOutputStream;import org.apache.commons.io.FileUtils;import org.apache.commons.io.FilenameUtils;import org.apache.commons.io.filefilter.TrueFileFilter;public class FileBasicOperations {            public final static void copyFile (File fSource, File fDest){                try{                        InputStream in = new FileInputStream(fSource);                                          //For Append the file.                        //  OutputStream out = new FileOutputStream(f2,true);                        //For Overwrite the file.                        OutputStream out = new FileOutputStream(fDest);                        System.out.println("starting copy");                        byte[] buf = new byte[1024];                        int len;                        while ((len = in.read(buf)) > 0){                                out.write(buf, 0, len);                        }                        System.out.println("copy finished");                        in.close();                        out.close();                        //System.out.println("File copied.");                } catch(FileNotFoundException ex){                        System.out.println(ex.getMessage() + " in the specified directory.");                        //System.exit(0);                } catch(IOException e){                        System.out.println(e.getMessage());                  }                    }                public final static void removeFile(File rFile){                rFile.delete();        }                                                public static String movePathToQNC(String origPath) {                return origPath.replace("\\", "/").replace("//mol.local/Intern/Documenten/", "/QNTC/Molcy15/");        }                public static String MovePathToWindows(String origPath) {                return (origPath.replace("/QNTC/Molcy15/", "//mol.local/Intern/Documenten/").replace("/", "\\"));        }                public static String movePathToTmp(String origPath) {                return origPath.replace("\\", "/").replace("//mol.local/Intern/Documenten/", "/tmp/");        }        public static String getExtension(String path){                        return (FilenameUtils.getExtension(path));                /*                int dotInd = path.lastIndexOf('.');        String extension = (dotInd > 0 && dotInd < path.length()) ? path.substring(dotInd + 1) : null;                return (extension);                */        }                    public static List<String> getFilePathsInFolder(String rootpath){                List<String> retval = new ArrayList<String>();                File rootDir = new File(rootpath);                List<File> bestanden = (List<File>) FileUtils.listFiles(rootDir, TrueFileFilter.INSTANCE, TrueFileFilter.INSTANCE);                for (File f : bestanden){                        try {                                retval.add(f.getCanonicalPath());                        } catch (Exception e) {                                System.out.println("Fout bij opzoek van pad voor een van de bestanden");                        }                }                return retval;        }                public static void zipIt(String zipPad, List<String> bestandPaden, List<String> namen) {                byte[] buffer = new byte[1024];                try {                        FileOutputStream fos = new FileOutputStream(zipPad);                        ZipOutputStream zos = new ZipOutputStream(fos);                        //System.out.println("Output to Zip : " + zipPad);                        //int i;                         for (int i = 0; i<bestandPaden.size(); i++){                        //for (String file : bestandPaden) {                                //System.out.println("File Added : " + namen);                                try {                                        //String extension = FilenameUtils.getExtension(bestandPaden.get(i));                                        ZipEntry ze = new ZipEntry(namen.get(i));// + "." + extension);                                        //System.out.println("adding from file " + bestandPaden.get(i) + " to " + namen.get(i));                                        zos.putNextEntry(ze);                                        FileInputStream in = new FileInputStream(bestandPaden.get(i));                                        int len;                                        while ((len = in.read(buffer)) > 0) {                                                zos.write(buffer, 0, len);                                        }                                        in.close();                                } catch (Exception e) {                                        System.out.println("Fout bij toevoegen van bestand " + bestandPaden.get(i) + " " + e.getMessage());                                }                        }                        zos.closeEntry();                        zos.close();                        System.out.println("Done");                } catch (IOException ex) {                        ex.printStackTrace();                }        }                        }

The FileBasicOperations contains some other functions as well (like zipping a bunch of files)

I tried to quickly scan the code for other required files. Give me a shout if I missed something.

Kind regards,

Bram

Hi,

tank you for your time.

i think it's complete now.

But which type is the file which contains the excel and the getcellvalue function?!

Kind regards!

Where do you call function  createExcelFromArray in project?

And where should I put all external types and where I should define java classes like class FileBasicOperations? I m a little confused. 

I should create a new package in the service project and put all egl files there?

Thanks.

Hi Michael and Marcel,

I realize my posts above might be confusing. I didn't have the time to create a complete working example, so I tried to post some code snippets.

The urgent problems at work are fixed now, and with two holidays in Belgium, I've made some time to create a separate project for you to try.

I've also removed the code to store the file in a database (which simplifies things a bit). You will still need to find a way to get the file from the webserver to the end-user. Let me know if you need help with this.

I hope this creates some clarity. Let me know if you want some more details.

Kind regards,

Bram

Just some small comment. I've had a lot of difficulties posting on this forum the last couple of days.

Today I had some problems uploading the project. I therefore removed the FDA7.jar from the lib folder to reduce the file size.

Please copy the fda7.jar from another web project into the webcontent/web-inf/lib folder.

Bram,

I want to thank you for all your contributions to the forum and in helping other customers.  The sharing you and others do is invaluable.

Mark

I created a service project from your zip file but I don't know how can I test it. Could you give more info, please? And is it the right way to create it as service project ?

I get these errors in egl genetarion results:

I found that you have to define filePath variable but what should I put?

Thanks 

Hi Michael,

I would select file - import , and then select the root directory where you extracted the zip file.

I had to remove the web.xml file and the generated sources (everything under eglgen/javasource folder) when I wanted to deploy on tomcat instead of websphere liberty profile.

The filepath is the path to where you would like to store the file. eg. c:\test.xls for windows or /tmp/test.xls on linux.

Kind regards,

Bram

I tested the ExcelService.wsdl file with Web service explorer but i get that error when I put the path:

IWAB0135E An unexpected error has occurred.404Not Found

What web server did you run your program on and on what port is it running?

The wsdl uses port 10 000, because that's where our web server runs on.

I use Websphere server and I 've changed port to mine.

I developed and tested the project on Tomcat.

Have you changed the target runtime of the project to point to your websphere server?

Have you removed the web.xml and the generated Java sources, and then cleaned?

Kind regards,

Bram

How can I change the target runtime of the project ?

right-click the project, then under target runtime select the appropriate server runtime.

After I test it with web services explorer and put  c:\test.xls what will be the output? I should see something in this file ?

And what about the value of status ? 

There should be an excel file in the location you spicified.

Is your websphere running on windows?

I don't know what you mean by "the value of status".

I forgot the screenshot

This status here. (see the screenshot)

PS: Maybe it is wrong to create OfficeServicesWeb. tell me if it will cause me trouble.  And I m running on Windows. Thanks

This means the service is not running on the address http://localhost:9082/OfficeServices.
The service did not run.

There could be a lot of reasons why it didn't.

Have you got any experience with web services through EGL?

How can I configure the address?

Where is websphere running and on what port?

Did you run the application on websphere?

9082 is my profile and i 'm running the project on websphere.

Did you get any errors in the generation log or build log?

Is the server running on the same machine as your rbd installation?

Yes generation completed for OfficeServices with no errors and yes for the server. How can I see the build log?

Sorry, I meant Deployment log.

what do you see when you got to the following url with your web browser:

http://localhost:9082/OfficeServices/services

It should display a list of available web services and their functions. If it doesn't display then the service is not running on your web server. That should first be corrected. Perhaps the web server logs will give you more information.

Do I have to deploy this service project to see the results? I only generate the service project. and link(http://localhost:9082/OfficeServices/services)

  gives me that :

SRVE0255E: A WebGroup/Virtual Host to handle /OfficeServices/services has not been defined.

SRVE0255E: A WebGroup/Virtual Host to handle localhost:9082 has not been defined.

IBM WebSphere Application Server

if I must deploy the project I have to create a web file project, right?

If you clean the project it should generate entires in the deployment log.

I don't think it is necessary to deploy the project.

It should however be run on a webserver, so right-clicking the project and selecting "run on server" is required.

As what type do I run the project?

It should be a web project

Error 404: com.ibm.ws.webcontainer.servlet.exception.NoTargetForURIException: No target servlet configured for uri: /OfficeServices/services

I get this error now.

I'm afraid I can't really solve this via the forum. The problem you are having is with getting the service running on your Websphere installation. Perhaps you could try tomcat and the project as I supplied it in a separate workspace.

Have you used webservices before on your websphere installation?

Is there a useful guide to understand the whole process for the websphere server?

I only found this:

http://www-01.ibm.com/support/knowledgecenter/SSMQ79_9.1.1/com.ibm.egl.richui.sql.tutorial.doc/topics/egl_richui_sql_abstract.html

but it's for tomcat.

The process for Websphere is very similar.

Perhaps somebody from IBM can point you to a tutorial that's more suited for your needs.

kind regards,

Bram

Thanks for the guide.

Do you have any idea about these errors in the screenshot?

Thanks

It gives an error on your class.

Is it generated under EGLGen/JavaSource?