Highlighted
Absent Member.
Absent Member.
6376 views

[archive] How to improve the performance with Excel in acuCOBOL?

[Migrated content. Thread originally posted on 28 July 2009]

I have above 3800 records need to be output .
Originally, my code outputs them to a CSV file.
It only takes about 2 minutes.
Now I output them to an Excel file.
It takes me more than 10 minutes.

In fact, my production file have more 40,000 records.
And my program is a online real-time code, it has maximum 10 minutes limit to run.

I just wonder if there is some method to improve the Excel creation perfomance in acuCOBOL?

Does someone have experience?

Thanks
0 Likes
17 Replies
Highlighted
Absent Member.
Absent Member.

RE: [archive] How to improve the performance with Excel in acuCOBOL?

One thing that does improve performance somewhat is to wait until you have loaded all the data before opening the application.

Make sure you do this command last: MODIFY hExcelApp @VISIBLE = 1.

so add some sort of progress display window to keep users informed on how much longer it's going to take.

I think you can alos speed it up by passing in an array instead of manipulating each cell separately. I think tho with the shear number of records (40,000) I don't think you will ever get as fast as you are doing currently by building a CSV and opening that...
0 Likes
Highlighted
Absent Member.
Absent Member.

RE: [archive] How to improve the performance with Excel in acuCOBOL?

Hi there:

Why don't you keep creating the CSV file and then read it with EXCEL thru OLE. It must be some examples in the forum somewhere (OPEN and SAVEAS).

Good Luck.


CB52 from the Pampas.
0 Likes
Highlighted
Absent Member.
Absent Member.

RE: [archive] How to improve the performance with Excel in acuCOBOL?

Have you considered using Acu4GL ODBC - not enough details in your issue, but if you execute your programs on Windows, you can use a WRITE statement in COBOL and the data goes to a Data Source Name (DSN) where the DSN is connected to your Excel table.
0 Likes
Highlighted
Absent Member.
Absent Member.

RE: [archive] How to improve the performance with Excel in acuCOBOL?

How are you creating the Excel file? We create an Excel XML file.
0 Likes
Highlighted
Absent Member.
Absent Member.

RE: [archive] How to improve the performance with Excel in acuCOBOL?

we had also performance problems with direct output to excel.

To improve the import wie use the "old" csv and import this in excel.



       01  myArray.
           03  filler        occurs 7.
               05  myItems   usage handle of variant.


---

           inquire xls-sheet  @CELLS::@ITEM(1, "A") xls-range. | A1 start of import

           modify xls-sheet QueryTables::Add(
                  by name Connection  "TEXT;y:\muffrohr\seq-anof.txt" |csv-file
                  by name Destination xls-range
                                            )
                  giving xls-query.

           call "C$SETVARIANT" using 1, myItems(1). | Number
           call "C$SETVARIANT" using 2, myItems(2). | Text
           call "C$SETVARIANT" using 2, myItems(3). | Text
           call "C$SETVARIANT" using 2, myItems(4). | Text
           call "C$SETVARIANT" using 2, myItems(5). | Text
           call "C$SETVARIANT" using 4, myItems(6). | Date
           call "C$SETVARIANT" using 1, myItems(7). | Number

           modify xls-query @Name("SEQ-ANOF"),
                            @FieldNames(1),
                            @RowNumbers(0),
                            @FillAdjacentFormulas(0),
                            @PreserveFormatting(1),
                            @RefreshOnFileOpen(0),
                            @RefreshStyle(xlInsertDeleteCells),
                            @SavePassword(0),
                            @SaveData(1),
                            @AdjustColumnWidth(0),
                            @RefreshPeriod(0),
                            @TextFilePromptOnRefresh(0),
                            @TextFilePlatform(850),
                            @TextFileStartRow(1),
                            @TextFileParseType(xlDelimited),
                            @TextFileTextQualifier(xlTextQualifierNone),
                            @TextFileConsecutiveDelimiter(0),
                            @TextFileTabDelimiter(0),
                            @TextFileSemicolonDelimiter(1),
                            @TextFileCommaDelimiter(0),
                            @TextFileSpaceDelimiter(0),
                            @TextFileColumnDataTypes(myArray),
                            @TextFileTrailingMinusNumbers(1),
                            @Refresh(by name BackgroundQuery 0).

           modify  xls-query @Delete().
           destroy xls-query.



If we need to format the Import we use a little trick... we use a temp-row in the csv where we place a code which we inquire in excel to set the format.

Example:
The Grid in the Software:
http://www.cobolhilfe.de/ot/musoftware.jpg

The Export-CSV
http://www.cobolhilfe.de/ot/mucsv.jpg

In Excel
http://www.cobolhilfe.de/ot/muexcel.jpg

In the CSV the First Data-Field is the code for the Format, after all Formats are set, we delete in excel the first row.
0 Likes
Highlighted
Absent Member.
Absent Member.

RE: [archive] How to improve the performance with Excel in acuCOBOL?

If you export data to Excel cell by cell, this is very slow, using safearrays is way faster. Do you use safearrays?
0 Likes
Highlighted
Absent Member.
Absent Member.

RE: [archive] How to improve the performance with Excel in acuCOBOL?

Thanks all for your replies.

After research, I decided to try Frosti and gforseth suggestion.

1.create csv file "testexcel.000"
2.export it to excel file "testexcel.xls"

And Frosti's code is truely helpful.

I incorporated those codes into my code, and it works like a horse. It almost takes same time as before, but it could directly create excel files which I like to have.

I attached my test code here for your reference, it is not my production code(it is too much complicated parts in that code), and a csv file$&output excel file as well.


       IDENTIFICATION DIVISION.
       PROGRAM-ID. testexcel.
       AUTHOR.     Dan Maltes, AST.
       DATE-WRITTEN. 2002/12/15 - 12:00:00.
       DATE-COMPILED. 2002/12/15 - 12:00:00.
       REMARKS. Example of controlling excel 2002(office xp) through
           ole automation.The excel object model states that an
           Application object contains Workbooks which contains
           Worksheets which contains a Range(cells).
           Excel Object Model
            - Application
              + Workbooks(Workbook) collection
                + Worksheets(Worksheet) collection
                  - Range(cells)
           Note: You will need to generate your own excel.def file with
                 the AXDEFGEN utility.  This sample was tested with an
                 excel.def file generated for the
                 Microsoft Excel 10.0 Object Library(Ver 1.4)
                 which is part of MS Office XP.
                 You may need to make changes to this program for
                 compatibility with office 2000 and office 97.
           testexcel is modified by littlelittle on 2009/07/29.
           It may need some adjustment to run properly on your machine.
           My test environment:
             Microsoft Excel 11.0 Object Library(Ver 1.5),
             Excel 2003, acuCOBOL 6.10, Windows XP SP3.     
       ENVIRONMENT DIVISION.
       CONFIGURATION SECTION.
       SPECIAL-NAMES.
           copy "excel.def".
            .
       INPUT-OUTPUT SECTION.
050809 FILE-CONTROL.
050809 DATA DIVISION.
       FILE SECTION.
       WORKING-STORAGE SECTION.
050809 01  PROGID           PIC X(9)  VALUE "testexcel".
       77 EX-APP            HANDLE OF Application.
       77 EX-WORKBOOK       HANDLE OF Workbook.
       77 EX-WORKSHEET      HANDLE OF Worksheet.
       77 EX-RANGE          HANDLE OF Range.
       77 EX-QUERY          HANDLE OF QueryTable.
       77 CELL-RANGE        PIC X(04) VALUE SPACES.
       77 TXT-VALUE         PIC X(50) VALUE SPACES.
       77 NUM-VALUE         PIC 9(09) VALUE ZEROS.
       77 FORMULA-VALUE     PIC X(50) VALUE SPACES.
       77 WKBK-NAME         PIC X(100) VALUE SPACES.
052609 01 FILE-LOCATION     PIC X(21) VALUE "c:\testdata\filesabc\".
072909 01 FILE-CONNECTION   PIC X(39) VALUE
072909                      "TEXT;c:\testdata\filesabc\testexcel.000". |a csv file
072909 01  MYARRAY.
           03 FILLER OCCURS 21.
             05  MYITEMS USAGE HANDLE OF VARIANT.
       PROCEDURE DIVISION.
050809 DECLARATIVES.
050809 END DECLARATIVES.
       MAIN SECTION.
      * Create excel application object
           CREATE @Application OF @Excel HANDLE IN EX-APP.
      * Open existing sheet logic:
           MODIFY EX-APP @Visible = 1.
      * Add a new workbook, do not name it yet, will be done when saved.
           MODIFY EX-APP @Workbooks::Add() GIVING EX-WORKBOOK.
      * Can reference Sheet1 and change the name.
           INQUIRE EX-WORKBOOK @Worksheets::Item(1)
           IN EX-WORKSHEET.                   
050809     MODIFY EX-WORKSHEET @Name = "testexcel".
072909     INQUIRE EX-WORKSHEET @CELLS::@ITEM(1, "A")
072909     IN EX-RANGE.
072909     MODIFY EX-WORKSHEET @QUERYTABLES::ADD(
                  BY NAME CONNECTION  FILE-CONNECTION
                  BY NAME DESTINATION EX-RANGE )
                  GIVING EX-QUERY.
072909*1:NUMBER; 2:TEXT; 4:DATE; I have 21 fields in workbook.
           CALL "C$SETVARIANT" USING 2, MYITEMS(1). | TEXT
           CALL "C$SETVARIANT" USING 2, MYITEMS(2). | TEXT
           CALL "C$SETVARIANT" USING 2, MYITEMS(3). | TEXT
           CALL "C$SETVARIANT" USING 2, MYITEMS(4). | TEXT
           CALL "C$SETVARIANT" USING 2, MYITEMS(5). | TEXT
           CALL "C$SETVARIANT" USING 2, MYITEMS(6). | TEXT
           CALL "C$SETVARIANT" USING 2, MYITEMS(7). | TEXT
           CALL "C$SETVARIANT" USING 2, MYITEMS(8). | TEXT
           CALL "C$SETVARIANT" USING 1, MYITEMS(9). | NUMBER
           CALL "C$SETVARIANT" USING 2, MYITEMS(10). | TEXT
           CALL "C$SETVARIANT" USING 2, MYITEMS(11). | TEXT
           CALL "C$SETVARIANT" USING 1, MYITEMS(12). | NUMBER
           CALL "C$SETVARIANT" USING 1, MYITEMS(13). | NUMBER
           CALL "C$SETVARIANT" USING 1, MYITEMS(14). | NUMBER
           CALL "C$SETVARIANT" USING 2, MYITEMS(15). | TEXT
           CALL "C$SETVARIANT" USING 2, MYITEMS(16). | TEXT
           CALL "C$SETVARIANT" USING 2, MYITEMS(17). | TEXT
           CALL "C$SETVARIANT" USING 2, MYITEMS(18). | TEXT
           CALL "C$SETVARIANT" USING 2, MYITEMS(19). | TEXT
           CALL "C$SETVARIANT" USING 2, MYITEMS(20). | TEXT
           CALL "C$SETVARIANT" USING 2, MYITEMS(21). | TEXT
           MODIFY EX-QUERY @Name("QUERY-INV"),
                            @FieldNames(1),
                            @RowNumbers(0),
                            @FillAdjacentFormulas(0),
                            @PreserveFormatting(1),
                            @RefreshOnFileOpen(0),
                            @RefreshStyle(xlInsertDeleteCells),
                            @SavePassword(0),
                            @SaveData(1),
                            @AdjustColumnWidth(0),
                            @RefreshPeriod(0),
                            @TextFilePromptOnRefresh(0),
                            @TextFilePlatform(850),
                            @TextFileStartRow(1),
                            @TextFileParseType(xlDelimited),
                            @TextFileTextQualifier(xlTextQualifierNone),
                            @TextFileConsecutiveDelimiter(0),
                            @TextFileTabDelimiter(0),
                            @TextFileSemicolonDelimiter(1),
                            @TextFileCommaDelimiter(0),
                            @TextFileSpaceDelimiter(0),
                            @TextFileColumnDataTypes(myArray),
                            @TextFileTrailingMinusNumbers(1),
                            @Refresh(by name BackgroundQuery 0).
           DESTROY EX-RANGE. |this line is important, I ever forget it and Excel will remain in memory.
           MODIFY  EX-QUERY @Delete().
           DESTROY EX-QUERY.
      * Save workbook and close it
052609     STRING FILE-LOCATION "testexcel.xls"
050809     DELIMITED BY SIZE INTO WKBK-NAME
052609     END-STRING.
           MODIFY EX-WORKBOOK @SaveAs(         
               BY NAME @Filename WKBK-NAME,
               BY NAME @FileFormat @xlNormal,
               BY NAME @Password NULL,
               BY NAME @WriteResPassword NULL,
               BY NAME @ReadOnlyRecommended 0,
               BY NAME @CreateBackup 0
               BY NAME @AccessMode @xlNoChange).
           MODIFY EX-WORKBOOK @Close().
      * Cleanup and exit.  Handle destruction should be done in reverse
      * object heirarchy order or Excel may be left open in background
      * waiting for dangling handles to be destroyed.
      * Quit the Excel automation server before destroying its handle.
           DESTROY EX-WORKSHEET.
           DESTROY EX-WORKBOOK.
           MODIFY EX-APP @Quit().
           DESTROY EX-APP.
           EXIT PROGRAM.
           STOP RUN.


my conclusion:
1."CELL BY CELL UPDATE" is best for a number of cells value change;
2." open csv file, save as excel file " method is best for large quantity of records updating(value,style,format).

reference links which is most helpful to my problem:
1.http://www.acucorp.com/support/supported/customer_forum/showthread.php?t=1601
2.http://www.acucorp.com/support/supported/customer_forum/showthread.php?t=490

csv file:
[ATTACH]374[/ATTACH]

Additionally,
[ATTACH]372[/ATTACH]
My excel 2003 is English version and my international region is Canada.
My users could be in Europe.
The number fields in some case (see the red arrows) in the xlsfile.jpg need to be changed to "5.072,96"(european format) rather than "5,072.96"(north American format).
I ever tried to add these lines in my code:

072909     MODIFY EX-APP @DecimalSeparator = ",".
072909     MODIFY EX-APP @ThousandsSeparator = ".".
072909     MODIFY EX-APP @UseSystemSeparators(0).


But there is no luck, it still shows "5072.96".

Appricate for any suggestion on this issue.

LittleLittle
0 Likes
Highlighted
Absent Member.
Absent Member.

RE: [archive] How to improve the performance with Excel in acuCOBOL?

My guess is that when your spreadsheet is loaded, the number format is set and your setting decimalpoints etc is too late. You should select the entire spreadsheet and set the desired number format.
0 Likes
Highlighted
Absent Member.
Absent Member.

RE: [archive] How to improve the performance with Excel in acuCOBOL?

i tested it a little bit.
You must use the correct order.

I use German Windows and German Excel - so i tested to set the North American Format.


           modify xls-app @UseSystemSeparators = 0.
           modify xls-app @DecimalSeparator    = ".".
           modify xls-app @ThousandsSeparator  = ",".


your code ist correct...

If i use


           modify xls-app @UseSystemSeparators = 0.
           modify xls-app @DecimalSeparator    = ".".
           modify xls-app @ThousandsSeparator  = ",".

           inquire xls-sheet @CELLS::@RANGE("D1", "D1") xls-range.
           modify  xls-range @SELECT().
           modify  xls-range
                   [b]@ENTIRECOLUMN::@NUMBERFORMAT('#.##0,00').[/b]


i get "123456,00". wrong

in this order i must format the numbers in the American "Style" not in the German format.


           modify xls-app @UseSystemSeparators = 0.
           modify xls-app @DecimalSeparator    = ".".
           modify xls-app @ThousandsSeparator  = ",".

           inquire xls-sheet @CELLS::@RANGE("D1", "D1") xls-range.
           modify  xls-range @SELECT().
           modify  xls-range
                   [b]@ENTIRECOLUMN::@NUMBERFORMAT('#,##0.00')[/b].


i get 1,234,567.89 correct


If i format all in German...

           inquire xls-sheet @CELLS::@RANGE("D1", "D1") xls-range.
           modify  xls-range @SELECT().
           modify  xls-range
                   [b]@ENTIRECOLUMN::@NUMBERFORMAT('#.##0,00').[/b]


and then switch the App-Style


           modify xls-app @UseSystemSeparators = 0.
           modify xls-app @DecimalSeparator    = ".".
           modify xls-app @ThousandsSeparator  = ",".


it is also correct.


David
0 Likes
Highlighted
Absent Member.
Absent Member.

RE: [archive] How to improve the performance with Excel in acuCOBOL?

i tested it a little bit.
You must use the correct order.

I use German Windows and German Excel - so i tested to set the North American Format.


           modify xls-app @UseSystemSeparators = 0.
           modify xls-app @DecimalSeparator    = ".".
           modify xls-app @ThousandsSeparator  = ",".


your code ist correct...

If i use


           modify xls-app @UseSystemSeparators = 0.
           modify xls-app @DecimalSeparator    = ".".
           modify xls-app @ThousandsSeparator  = ",".

           inquire xls-sheet @CELLS::@RANGE("D1", "D1") xls-range.
           modify  xls-range @SELECT().
           modify  xls-range
                   [b]@ENTIRECOLUMN::@NUMBERFORMAT('#.##0,00').[/b]


i get "123456,00". wrong

in this order i must format the numbers in the American "Style" not in the German format.


           modify xls-app @UseSystemSeparators = 0.
           modify xls-app @DecimalSeparator    = ".".
           modify xls-app @ThousandsSeparator  = ",".

           inquire xls-sheet @CELLS::@RANGE("D1", "D1") xls-range.
           modify  xls-range @SELECT().
           modify  xls-range
                   [b]@ENTIRECOLUMN::@NUMBERFORMAT('#,##0.00')[/b].


i get 1,234,567.89 correct


If i format all in German...

           inquire xls-sheet @CELLS::@RANGE("D1", "D1") xls-range.
           modify  xls-range @SELECT().
           modify  xls-range
                   [b]@ENTIRECOLUMN::@NUMBERFORMAT('#.##0,00').[/b]


and then switch the App-Style


           modify xls-app @UseSystemSeparators = 0.
           modify xls-app @DecimalSeparator    = ".".
           modify xls-app @ThousandsSeparator  = ",".


it is also correct.


David


Hi David,

Thanks for your advice.

You must use the correct order.

Yes, this is important. I never think about that before you remind me.

I also like your codes:


           inquire xls-sheet @CELLS::@RANGE("D1", "D1") xls-range.
           modify  xls-range @SELECT().
           modify  xls-range
                   [b]@ENTIRECOLUMN::@NUMBERFORMAT('#,##0.00')[/b].


But finally I changed my mind, I duplicate my code to two copies. One copy is same as now, the other copy includes

000330 CONFIGURATION SECTION.
072909 SPECIAL-NAMES.
073109     DECIMAL-POINT IS COMMA.

And I change the number format to use COMMA as decimal separator in that copy.

So it will run one of them depending on the customer location(Europe or North America).

Now European version code will create csv file and excel file with "PERIOD" decimal separator.
North American version code will create csv file and excel file with "COMMA" decimal separator.
And I don't need to change excel decimal separator setting at all(I find it is a "nightmare" for my code to change the excel settings).


My issue is solved.


But another new issue is outstanding, and this is IE8.0 problem rather than acuCOBOL.

When I open my created excel file which has "COMMA" decimal separator for Europe directly from the server, it shows me correct numeric format such as "1.234.567,89",
but when I download same excel file through IE8.0(my online system provding the link for this downloadable excel file) and open it, the numeric format change to North American format again such as "1,234,567.89" .

If I use Firefox 3.5 to download it and open it, the numeric format won't change, still shows European format.

I "googled" about EXCEL and IE8.0 on this senario, but no luck.

I am not sure if you or someone else ever heard of this IE8.0 trick/defect?!

Mike
0 Likes
Highlighted
Absent Member.
Absent Member.

RE: [archive] How to improve the performance with Excel in acuCOBOL?

let me understand this.

you have the excel file on your webserver...

.\htdocs\excelfile.xls

and you open it direct from ie8
example http:\\intranet\excelfile.xls

then the format switch in ie8?
0 Likes
The opinions expressed above are the personal opinions of the authors, not of Micro Focus. By using this site, you accept the Terms of Use and Rules of Participation. Certain versions of content ("Material") accessible here may contain branding from Hewlett-Packard Company (now HP Inc.) and Hewlett Packard Enterprise Company. As of September 1, 2017, the Material is now offered by Micro Focus, a separately owned and operated company. Any reference to the HP and Hewlett Packard Enterprise/HPE marks is historical in nature, and the HP and Hewlett Packard Enterprise/HPE marks are the property of their respective owners.