Hi,
I’m adding data to excel on windows. its so slow!
i think its because excel is recalculating everything after every insert
i know in excel you can turn the calculation off but i can’t see how to do this from xojo.
anyone got any ideas?
Hi,
I’m adding data to excel on windows. its so slow!
i think its because excel is recalculating everything after every insert
i know in excel you can turn the calculation off but i can’t see how to do this from xojo.
anyone got any ideas?
Wow, we were just discussing this.
I don’t think you can, but are you adding your data a cell at a time? If so, try doing it a row at a time instead.
hi,
i have managed to get the rows to add, but the numeric columns are all text and end up with a green triangle n the cell
any idea how to change the format of a column?
i tried activesheet.column(i).numberformat = “0000,00”
but it does nothing, not even an exception
Can you post the code you’re using to set the values of the row?
Hi Russ,
Good question. Here is how to turn Excel calculations to manual (off):
[code] Dim excel as new ExcelApplication
excel.Workbooks.Add
excel.Visible = True
excel.Application.Calculation = Office.xlManual[/code]
To turn the calculation back on, use this code:
excel.Application.Calculation = Office.xlAutomatic
To calculate automatically except for data tables, use this code:
excel.Application.Calculation = Office.xlSemiautomatic
Could you let me know if this helps increase the speed of Excel on your application?
Another tip to increase the speed of putting large amounts of data in excel is to have visible = false until all the data is added. This works for some and not for others.
Thanks
Hi Kem,
the code I’m using is below. i found that if i didn’t add the line
param.type = oleparameter.ParamTypeString
nothing was added to the sheet. of course this is the reason everything is text. but i can’t see how to do
it otherwise.
I did think maybe i would add the data as column ranges instead of rows, taking account of the field type in the recordset.
but that might end up with some very large arrays Vs. lots of small arrays. (not sure of the real world difference)
if rs <> nil then
Dim excel As New ExcelApplication
Dim book As ExcelWorkbook
Dim sheet As ExcelWorksheet
excel.Visible = false
book = excel.Workbooks.Add
'excel.ActiveSheet.Name = "Sheet1"
dim param as new oleparameter
param.type = oleparameter.ParamTypeString
dim RowArray() as variant
for column as integer = 1 to rs.FieldCount -1
Rowarray.Append rs.IdxField(column).Name
next
//Put the contents of your array into excel range with one command
param.ValueArray = RowArray
dim r as excelrange
r = excel.range(excel.activesheet.cells(StartRow,StartColumn),excel.activesheet.cells(startrow,rs.FieldCount-1))
r.value2 = param
// Make the headers Bold
r.Font.Bold = true
dim row as integer = startrow +1
while not rs.eof
redim RowArray(-1)
for column as integer = 1 to rs.fieldcount -1
Row.Append rs.IdxField(column).StringValue
next
param.ValueArray = RowArray
r = excel.range(excel.activesheet.cells(row,StartColumn),excel.activesheet.cells(row,rs.fieldcount-1))
r.value2 = param
rs.MoveNext
row = row + 1
wend
Hi Eugene,
thats great! i will try that too.
i found that adding by rows is very fast over doing it cell-by-cell
but i feel turning off calculation as well as visible=false will be the
whole answer to making it really fast. (faster the better)
Hi Russ,
I modified a few parts of the code and this code takes about 17 seconds on my Windows 8.1 machine. It creates 9 columns of string data, and adds 1000 integer values for each of the nine rows, for a total of 9000 data points. Not sure how many data points you are sending to Excel. I turned off a few things in excel to increase the speed and then turned them back on again at the end.
[code] Dim excel as new ExcelApplication
excel.Visible = false
excel.Workbooks.Add
excel.ActiveWindow.WindowState = Office.xlMinimized
excel.Application.Calculation = Office.xlManual
excel.ActiveSheet.Name = “MySheet”
Dim param1 as new OLEParameter
param1.Type = OLEParameter.ParamTypeString
'Add 10 strings to array
Dim RowArray() as Variant
Dim i as Integer
For i = 1 to 10
RowArray.Append “Header:” + CStr(i)
Next i
param1.ValueArray = RowArray
Dim r as ExcelRange
Dim StartRow, StartColumn as integer
StartRow = 1
StartColumn = 1
r = excel.range(excel.activesheet.cells(StartRow,StartColumn),excel.activesheet.cells(StartRow,UBound(RowArray)))
r.value2 = param1
r.Font.Bold = True
'Add data in a column array
Dim ColumnArray() as Integer
For i = 1 to 1000
ColumnArray.Append i
Next i
For i = 0 to UBound(ColumnArray)
excel.Range(“A2”).Offset(i,0).Value = ColumnArray(i)
excel.Range(“B2”).Offset(i,0).Value = ColumnArray(i)
excel.Range(“C2”).Offset(i,0).Value = ColumnArray(i)
excel.Range(“D2”).Offset(i,0).Value = ColumnArray(i)
excel.Range(“E2”).Offset(i,0).Value = ColumnArray(i)
excel.Range(“F2”).Offset(i,0).Value = ColumnArray(i)
excel.Range(“G2”).Offset(i,0).Value = ColumnArray(i)
excel.Range(“H2”).Offset(i,0).Value = ColumnArray(i)
excel.Range(“I2”).Offset(i,0).Value = ColumnArray(i)
Next i
excel.Application.Calculation = Office.xlAutomatic
excel.Visible = True
excel.ActiveWindow.WindowState = Office.xlMaximized
MsgBox “Done”[/code]
Hi Russ,
I tried code with a different OLEParameter, and was not able to make it work either.
param.type = oleparameter.ParamTypeString
I’ll try a few more things and will give you an update if I am successful.
Hi guys,
Eugene’s code where it starts to add data to each cell can actually be speeded up more like:
excel.range("A2:I1000").value=ColumnArray()
How faster would that be compared with 17 second, Eugene?
Hi Dennis,
Thanks for the code and it made a large difference in execution time! Less than 1 second.
Russ, how long does it take to pull the information from the database?
Here is the modified code with Dennis’ suggestion.
[code] Dim excel as new ExcelApplication
excel.Visible = false
excel.Workbooks.Add
excel.ActiveWindow.WindowState = Office.xlMinimized
excel.Application.Calculation = Office.xlManual
excel.ActiveSheet.Name = “MySheet”
Dim param1 as new OLEParameter
param1.Type = OLEParameter.ParamTypeString
'Add 10 strings to array
Dim RowArray() as Variant
Dim i as Integer
For i = 1 to 10
RowArray.Append “Header:” + CStr(i)
Next i
param1.ValueArray = RowArray
Dim r as ExcelRange
Dim StartRow, StartColumn as integer
StartRow = 1
StartColumn = 1
r = excel.range(excel.activesheet.cells(StartRow,StartColumn),excel.activesheet.cells(StartRow,UBound(RowArray)))
r.value2 = param1
r.Font.Bold = True
'Add data in a column array
Dim ColumnArray() as Integer
For i = 1 to 1000
ColumnArray.Append i
Next i
'Put array info into Excel
excel.Range(“A2:I1000”).Value = ColumnArray()
excel.Application.Calculation = Office.xlAutomatic
excel.Visible = True
excel.ActiveWindow.WindowState = Office.xlMaximized[/code]
Dennis,
Thanks for the code to pass an array. All I seem to be able to send to the excel file is the number 1. It is definitely fast, and how is the string data passed to Excel?
Thanks.
[code] Dim excel as new ExcelApplication
excel.Workbooks.Add
excel.Visible = True
'Make an array of 100
Dim ColumnArray() as String
Dim i as Integer
For i = 1 to 100
ColumnArray.Append(Cstr(i))
Next i
'Put array in first 100 rows - only the number 1
excel.Range(“A1:A100”).Value = ColumnArray
[/code]
hi, Sorry i forgot to follow the thread
this code seems to put the first value in the array in every row or am i missing something?
Russ,
You’re not missing anything. In order to show the values the array need to be transposed.
It would help if You can provide either the code in use to grab the data from the database or an example. Are You using ADO?
The following code snippet may help but it’s subject to the code in use:
'Put array info into Excel
excel.Range("A2:A1001").Value = excel.workWorksheetFunction.transpose(ColumnArray())
Hi Dennis.
well this is my code.
i tried = excel.worksheetfunction.transpose() and i got no errors, but no data either
if rs <> nil then
Dim excel as new ExcelApplication
excel.Visible = false
excel.Workbooks.Add
excel.ActiveWindow.WindowState = Office.xlMinimized
excel.Application.Calculation = Office.xlManual
excel.ActiveSheet.Name = "MySheet"
Dim param1 as new OLEParameter
param1.Type = OLEParameter.ParamTypeString
dim i as integer
'Add 10 strings to array
Dim RowArray() as Variant
for column as integer = 1 to rs.FieldCount -1
rowarray.Append rs.IdxField(column).Name
next
//Put the contents of your array into excel range with one command
param1.ValueArray = rowArray
dim r as excelrange
r = excel.range(excel.activesheet.cells(StartRow,StartColumn),excel.activesheet.cells(startrow,rowarray.ubound))
r.value2 = param1
// Make the headers Bold
r.Font.Bold = true
param1.ValueArray = RowArray
r = excel.range(excel.activesheet.cells(StartRow,StartColumn),excel.activesheet.cells(StartRow,UBound(RowArray)))
r.value2 = param1
r.Font.Bold = True
dim columnData() as ExcelVariantArray
for i = 1 to rs.FieldCount
dim va as new ExcelVariantArray
columnData.Append va
next
dim rowCount as integer
while not rs.eof
for column as integer = 1 to rs.FieldCount -1
columndata(column).data.append rs.IdxField(column).StringValue
next
rowcount = rowcount+1
rs.MoveNext
wend
'Put array info into Excel
for column as integer = 1 to rs.FieldCount -1
excel.Range(excel.activesheet.cells(2,column),excel.activesheet.cells(rowcount,column)).Value = columndata(column).data()
next
excel.Application.Calculation = Office.xlAutomatic
excel.Visible = True
excel.ActiveWindow.WindowState = Office.xlMaximized
end if
oh, its a recordset object from an odbcDatabase.
Thanks Russ. I will take a look on it. Can’t promise that I can get back to You today but asap.
Hi Dennis, I have the answer!!!
basically you cannot assign a 1D array to a column,
even if you only want a single column in excel, the array must be 2D
then it works perfectly, fills thousands of rows in next to no time
I’m very happy now. thanks for your help!
Russ,
Great news!
Yes, that’s true and therefore I used the Transpose function,
Could You have the kindness to provide the code solution?
Hi Russ,
Are you willing to share the solution for filling a range of cells, i get the next error:Unknown name, (failed on “ValueArray2D”)
TIA,
Andre