Uroš Grum
Uroš Grum

Reputation: 138

Faster Cell input values LibreOffice Vba

I am trying to copy data (cca. 70*25*15 Cells) out of a txt file into libre office spreadsheet with a macro. Right now im accesing cells with this code :

Example:
Dim CellValue As Object

CellValue = ThisComponent.Sheets(1).getCellByPosition(i, j)
CellValue.String = "Whatever data i get from txt"

By running the above code and if text file have (70*25*15) cell entries, it needs 2min and 18 sec to copy all the data from text file to spreadsheet. Is there a better way to acess cells and speed up the process ?

Upvotes: 2

Views: 1665

Answers (1)

Axel Richter
Axel Richter

Reputation: 61860

You could set the DataArray of an object which implements XCellRangeData form an array of arrays.

Example:

Sub Test()

  aDataArray = array(array("A1", "B1", "C1"), array("A2", 2.2, 2.3), array("A3", 3.2, 3.3))

  oDoc = ThisComponent

  oSheet = oDoc.getSheets().getByIndex(1)

  oRange = oSheet.getCellRangeByName("A1:C3")

  'xray oRange

  oRange.setDataArray(aDataArray)

End Sub

https://www.openoffice.org/api/docs/common/ref/com/sun/star/sheet/XCellRangeData.html#setDataArray

Edit:

More complex example:

Sub Test()

  lRowCount = 1800
  lColCount = 40

  dim aDataArray() as variant
  dim aColumnArray() as variant

  redim aDataArray(lRowCount-1) 'the main array is for rows; 0-based so count-1

  for lRow = lbound(aDataArray) to ubound(aDataArray)

   redim aColumnArray(lColCount-1) 'this array is for column data

   for lCol = lbound(aColumnArray) to ubound(aColumnArray)
    'create some sample data
    select case lCol
     case 1
      aColumnArray(lCol) = CInt(int((10 * rnd()) + 1) 'integer in column 2
     case 2
      aColumnArray(lCol) = CDbl(1000 * rnd()) 'double in column 3, later formatted as currency     
     case 3
      aColumnArray(lCol) = CDbl(date + int(365*rnd())) 'date in column 4, must be double in the array, will be later formatted as date
     case 4
      aColumnArray(lCol) = CInt(lRow mod 2 = 0) 'boolean in column 5, must be integer in the array, will be later formatted as booleann
     case else
      aColumnArray(lCol) = "r" & lRow & "c" & lCol 'all other columns string
    end select
   next
   aDataArray(lRow) = aColumnArray
  next

  oDoc = ThisComponent

  oSheet = oDoc.getSheets().getByIndex(0)

  lStartRow = 1
  lStartCol = 0
  'make sure, the size of the range will exactly match the array size
  oRange = oSheet.getCellRangeByPosition(lStartCol, lStartRow, lStartCol+lColCount-1, lStartRow+lRowCount-1)

  oRange.setDataArray(aDataArray) 'now the data is in the sheet

  'this code is for formatting
  oLocale = new com.sun.star.lang.Locale 'create empty locale

  oNumberFormats = thiscomponent.getNumberFormats() 'get NumberFormats from Calc

  'format column 3 as currency   
  oRange = oSheet.getCellRangeByPosition(2, lStartRow, 2, lStartRow+lRowCount-1)
  lCurrencyFormat = oNumberFormats.getStandardFormat(com.sun.star.util.NumberFormat.CURRENCY, oLocale)  
  oRange.NumberFormat = lCurrencyFormat

  'format column 4 as date
  oRange = oSheet.getCellRangeByPosition(3, lStartRow, 3, lStartRow+lRowCount-1)
  lFormat = oNumberFormats.getStandardFormat(com.sun.star.util.NumberFormat.DATE, oLocale)  
  oRange.NumberFormat = lFormat

  'format column 5 as boolean
  oRange = oSheet.getCellRangeByPosition(4, lStartRow, 4, lStartRow+lRowCount-1)
  lFormat = oNumberFormats.getStandardFormat(com.sun.star.util.NumberFormat.LOGICAL, oLocale)  
  oRange.NumberFormat = lFormat

End Sub

Upvotes: 3

Related Questions