[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

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...

[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

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.

[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

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.

[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

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.

[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

How are you creating the Excel file? We create an Excel XML file.

[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

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.

[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

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

[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

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

[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

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.www.acucorp.com/.../showthread.php
2.www.acucorp.com/.../showthread.php

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

[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

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.

[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

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

[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

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

[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

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

[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

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?

[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

Yes, your understanding is correct. You exactly got it.


Let me make it more clearly.
[ATTACH]380[/ATTACH]
The browser is IE8.0.
This screen shows a link(http:\\\\intranet\\excelfile.xls) to my created excel file(European format on server).

When I either open it direct from ie8; or right click and "Save as" to my local disk and open it again, the decimal separator will change from "COMMA" to "PERIOD"(North American Format).

But on the web server directory ".\\htdocs\\excelfile.xls", I open this file directly, it is still European format(COMMA as Decimal Separator).

[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

Yes, your understanding is correct. You exactly got it.


Let me make it more clearly.
[ATTACH]380[/ATTACH]
The browser is IE8.0.
This screen shows a link(http:\\\\intranet\\excelfile.xls) to my created excel file(European format on server).

When I either open it direct from ie8; or right click and "Save as" to my local disk and open it again, the decimal separator will change from "COMMA" to "PERIOD"(North American Format).

But on the web server directory ".\\htdocs\\excelfile.xls", I open this file directly, it is still European format(COMMA as Decimal Separator).

[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

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

Hi Mr. Gforseth,

I find your nice sample code "excelarray.cbl" in the forum.
http://www.acucorp.com/support/supported/customer_forum/showthread.php?t=163&highlight=safearrays

In the code, I see

      *Now, transfer all those 300 items at once
           MODIFY  olWrkSh
                   Range("A2")::Resize(100 , 3)::Value =
                   MyTable.


But my question is if I would like to read 300 items at once from EXCEL, would the code be sth like

           INQUIRE  olWrkSh
                   Range("A2")::Resize(100 , 3)::Value IN
                   MyTable.


I asked this question, because I found my 40,000 records also looks very slow when I tried to read them from Excel cell by cell.

:confused:

[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

Yes, that should do the trick.

[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

Unfortunately, I got "type mismatch" error.

[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

Unfortunately, I got "type mismatch" error.

[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

I recompiled and run "excelarray.cbl" successfully and got "BOOK1.xls".
[ATTACH]388[/ATTACH]

Then I did some changes in the code:

           PERFORM GEN-TABLE.
           CREATE  Application      OF Excel
                   HANDLE           IN olExcel.
[COLOR="Red"]          MODIFY olExcel @Workbooks::Open("C:\\Book1.xls")
           GIVING olWrkBk.[/COLOR]     
       *This is for training purposes only. If you don't want to see
      *Excel, remove this line.
           MODIFY  olExcel          @Visible = 1.
           INQUIRE olWrkBk          Worksheets::Item(1) IN
                   olWrkSh.
      *Set the column title, using cells A1, B1 and C2.
[COLOR="Blue"]      $     MODIFY  olWrkSh
      $             Range("A1")::Value = "Order ID"
      $             Range("B1")::Value = "Amount"
      $             Range("C1")::Value = "Tax".
      *Now, transfer all those 300 items at once
      $     MODIFY  olWrkSh[/COLOR]           
[COLOR="Red"]        INQUIRE olWrkSh
                   Range("A2")::Resize(100 , 3)::Value IN
                   MyTable.[/COLOR]           
            DESTROY olWrkSh.
      *Note that the SaveAs method of the WorkBook object in some versions
      *of Excel has a bug, it will claim that you pass too few parameters.
      *There are two ways to bypass this, either modify the number following
      *OPTIONAL to match the number of parameters minus 1, or preferably
      *provide parameters until the compiler gets happy.
      *Note however, the method SaveAs appears a number of times for various
      *objects, make sure you modify the correct one.
        [COLOR="Blue"]      $     MODIFY  olWrkBk
      $             SaveAs(BY NAME Filename "C:\\Book1.xls").
      $     MODIFY  olWrkBk          @Close( BY NAME SaveChanges 0 ).[/COLOR]
           DESTROY olWrkBk.
           MODIFY  olExcel          Quit().
      *This next line is very important, otherwise Excel will stick in
      *memory.
           DESTROY olExcel.
           GOBACK.

      *This section depends on the presens of the sample program rand.cbl
      *as provided in the sample directory of an ACUCOBOL-GT development
      *installation.
      *We here fill the variant table with data. Notice the mix of text
      *and numeric values, this is a convenient feature of the variant
      *data type.
       GEN-TABLE SECTION.
       GEN-TABLE-001.
           PERFORM VARYING          I FROM 1 BY 1 UNTIL I > 100
                   STRING           "ORD"
                                    I
                                    DELIMITED BY SIZE
                                    INTO MyData
                   CALL             "C$SETVARIANT" USING
                                    MyData
                                    MyItem(I, 1)
                                    END-CALL
                   MOVE 1 TO RND-VALUE
                   CALL             "C$SETVARIANT" USING
                                    RND-VALUE
                                    MyItem(I, 2)
                                    END-CALL
                   MULTIPLY         RND-VALUE BY 0.7 GIVING
                                    RND-VALUE
                   CALL             "C$SETVARIANT" USING
                                    RND-VALUE
                                    MyItem(I, 3)
                                    END-CALL
           END-PERFORM.
       GEN-TABLE-900.
       GEN-TABLE-EXIT.
           EXIT.



But unfortunately, I got "type mismatch" error?!
[ATTACH]390[/ATTACH]

[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

I just did a small test here, and yes, there is an error.
I recall this now, the problem relates to the "unknown" size of the array at the time data is restored. I seem to remember it was considered whether to address this or not, and it was turned down as there not much demand for the feature at the time.
The best thing I can suggest is to raise awareness of it, e.g. send in an enhancement request.
The work around is to inquire the value of each individual cell, which, granted, is not very effective.