Data transfer to Excel

[stevek]

We use a generic method to transfer report data direct to Excel based on passing each row of report data as a TAB delimited string and then using the Excel function putValue to populate each cell within each row. Unfortunately this methodology is very inefficient for large reports (e.g. over 100 rows and above). Creating the equivalent report as a CSV file is 10 times faster so it is obvious the Excel automation server side of things is the problem.

I need to speed the process up so have been toying with the idea of
(a) Transfer the TAB delimited string into Cell A of each row
(b) Use the Excel function textToColumns to automatically separate the TAB delimited string into the adjacent columns

Unfortunately, trying to make sense of the Excel function parameter requirements is a nightmare. The MSDN site makes reference to named space parameters like Excel.XlTextParsingType.xlDelimited but I have no idea how to relate this to the equivalent integer that Jade expects in the parameter string?

Has anyone got suggestions or maybe a better way to speed up the data transfer methodology to Excel?

Steve

[ghosttie]

If CSV is faster why not use CSV?

[torrie]

We use the same method as Ghostie, extracting to a csv file. If you wanted a formatted Excel file, then you can use a query table to load the csv file. Note, you will need to delete the oQuery and oRange objects when you're finished. They don't get cleaned up by default.

Code: Select all

oRange := zMyActiveWorksheet.range( pcStartRange & piRow.String, getColumnLabel(getColumnNoFromLabel( pcStartRange ) + piColumns) & piRow.String); oQuery := zMyActiveWorksheet.queryTables.add( "TEXT;" & psDataFile, oRange, null ); oQuery.fieldNames := true; oQuery.rowNumbers := false; oQuery.fillAdjacentFormulas := false; oQuery.preserveFormatting := true; oQuery.refreshOnFileOpen := false; oQuery.refreshStyle := Microsoft_Excel_11.XlInsertDeleteCells; oQuery.savePassword := false; oQuery.saveData := true; oQuery.adjustColumnWidth := true; oQuery.refreshPeriod := 0; oQuery.textFilePromptOnRefresh := false; oQuery.textFilePlatform := Microsoft_Excel_11.XlWindows; oQuery.textFileStartRow := 1; oQuery.textFileParseType := Microsoft_Excel_11.XlDelimited; oQuery.textFileConsecutiveDelimiter := false; oQuery.textFileTextQualifier := Microsoft_Excel_11.XlTextQualifierDoubleQuote; oQuery.textFileConsecutiveDelimiter := false; oQuery.textFileTabDelimiter := false; oQuery.textFileSemicolonDelimiter := false; oQuery.textFileCommaDelimiter := true; oQuery.textFileSpaceDelimiter := false; oQuery.textFileTrailingMinusNumbers := true; oQuery.refresh(false); oQuery.delete_;

If you're extracting oids, just be wary as Excel will (helpfully) drop any leading and trailing zeros.

[BeeJay]

If you want to avoid the CSV route, I found it much quicker to build up all the report data as Tab separated columns and CrLf separated rows in a single string and then copy that string to the clipboard and paste it into Excel in a single operation. For example, suppose you have the report data in a string called reportData and a reference to the Excel sheet into which you want to put the data in a variable called sheet:

Code: Select all

app.copyStringToClipboard( reportData ); range := sheet.range( 'A1', null ); sheet.paste( range, null );

Cheers,
BeeJay.

[stevek]

Thanks for all your replies.

Like Beejay, all our exported data is built as TAB delimited strings as standard to enable us to decide at last moment what format to use on output (CSV, TAB, XLS) - as selected by user. Will experiment with the various suggestions and see whichever works the fastest.