Page 1 of 1
Data transfer to Excel
Posted: Fri Oct 28, 2011 5:16 pm
by 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
Re: Data transfer to Excel
Posted: Sat Oct 29, 2011 1:15 am
by ghosttie
If CSV is faster why not use CSV?
Re: Data transfer to Excel
Posted: Mon Oct 31, 2011 7:28 am
by 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.
Re: Data transfer to Excel
Posted: Mon Oct 31, 2011 8:31 am
by 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.
Re: Data transfer to Excel
Posted: Mon Oct 31, 2011 1:31 pm
by 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.