Tabbladen samenvoegen (LibreOffice Basic): verschil tussen versies
k (Jeroen Strompf heeft pagina Tabbladen Calc samenvoegen hernoemd naar Tabbladen samenvoegen (LibreOffice Basic)) |
|
(geen verschil)
|
Versie van 3 nov 2019 11:04
Debugging
https://forum.openoffice.org/en/forum/viewtopic.php?f=20&t=86186
getDataArray()
Get an array from the contents of the cell range:
sequence< sequence< any > > getDataArray() ''' Bronnen ''' * https://api.libreoffice.org/docs/idl/ref/interfacecom_1_1sun_1_1star_1_1sheet_1_1XCellRangeData.html#abde435a89989ab90d13779c1ec49ca00 == copyByName() == <code>copyByName</code> is een method van ''sheets'': <pre> void copyByName ( [in] string aName, [in] string aCopy, [in] short nDestination ) * aName: Name of the sheet to be copied * aCopy: Name of the copied sheet * nDestination: Numerical index of the new sheet
Voorbeeld
Sub CopySheet Dim oCurrentController As Object Dim oActiveSheet As Object Dim oSheets As Object oCurrentController = ThisComponent.getCurrentController() oActiveSheet = oCurrentController.getActiveSheet() oSheets = ThisComponent.getSheets() If oSheets.hasByName( oActiveSheet.Name & "Copy" ) Then MsgBox "Sheet name " & oActiveSheet.Name & "Copy, already exists" Else oSheets.copyByName(oActiveSheet.Name, oActiveSheet.Name & "Copy", oActiveSheet.RangeAddress.Sheet + 1) End If End Sub
Bronnen
Casus (okt. 2016)
Ik wil een rekenblad met zo'n 30 tabbladen importeren in MySQL. Daarbij wil ik die tabbladen samenvoegen, waarbij de naam van het tabblad als extra kolom wordt opgevoerd. Het liefst wordt dat geaggregeerde tabblad volautomatisch geëxporteerd in een passend formaat (TSV, etc.).
Gezochte functionaliteiten:
- Voeg een nieuw tabblad toe
- Voeg een nieuwe kolom toe aan alle tabbladen
- Vul de naam van het tabblad in, in die nieuwe kolommen
- Voeg de inhoud van alle tabbladen in, op het nieuw-aangemaakte tabblad.
De code hieronder, vervult deze subfunctionaliteiten. Er is geen code die alles samenvoegt.
Voeg een nieuw tabblad toe
function insert_sheet() ' ====================================================== ' Insert sheet 'Aggregate' (if not exists) ' ====================================================== dim doc as object dim sheet as object doc = ThisComponent if ThisComponent.sheets.hasByName("Aggregate") then ' Do nothing: Sheet "Aggregate" bestaat al else sheet = doc.createInstance("com.sun.star.sheet.Spreadsheet") doc.sheets.insertByName("Aggregate",sheet) end if end function
Voeg een kolom toe
function insert_column() ' ====================================================== ' Insert column on current sheet ' ====================================================== ' dim doc as object dim sheet as object dim new_column as object doc = Thiscomponent sheet = doc.Sheets(0) ' Select first sheet (counting starts at 0) sheet.Columns.insertByIndex(0,1) ' At position 0, insert 1 column end function
Get number of sheets
function get_number_of_sheets() ' ====================================================== ' Get the number of sheets in the current file ' ====================================================== ' ' In dit geval niet gewerkt met tussenliggende variableen ' Op zich handig, zo'n abstractielaag, maar hier overkill ' print ThisComponent.sheets.count end function
Insert column on each sheet
function insert_column_on_each_sheet() ' ' ====================================================== ' Insert column on each sheet ' ====================================================== ' for i=0 to ThisComponent.sheets.count-1 Thiscomponent.sheets(i).columns.insertbyindex(0,1) next end function
Get number of rows
function get_number_of_rows() ' ' ====================================================== ' Get number of rows ' ====================================================== ' ' * Dat gaat alleen indirect, mbv. een cursor & gotEndOfUsedArea ' * Base=0. Dus het getal is 1 cijfer te laag ' dim o_cursor as object o_cursor = ThisComponent.Sheets(0).createCursor() o_cursor.gotoEndOfUsedArea(false) print o_cursor.getRangeAddress().EndRow end function
Get sheet name
function get_sheet_name() ' ====================================================== ' Get sheet name ' ====================================================== ' get_sheet_name = thiscomponent.getcurrentcontroller.activesheet.GetName end function
Insert sheet name on each row
function insert_sheet_name_on_each_row() ' ====================================================== ' Insert sheet name on each row in new column ' ====================================================== dim o_cursor as object dim s_sheetname as string dim i_rows as integer ' Get number of rows » i_rows ' =================================== ' o_cursor = ThisComponent.Sheets(0).createCursor() o_cursor.gotoEndOfUsedArea(false) i_rows = o_cursor.getRangeAddress().EndRow+1 ' Sheet name » s_sheetname ' =================================== ' s_sheetname = ThisComponent.sheets(0).Getname ' Insert column ' ============= ' Thiscomponent.sheets(0).columns.insertbyindex(0,1) ' Insert sheet name on each row ' ============================ for i = 0 to i_rows-1 ThisComponent.sheets(0).getCellByPosition(0,i).String=s_sheetname next ' Debug ' ===== ' ' print s_sheetname & " " & i_rows end function
Copy & paste cell ranges
Zie aparte hoofdstuk voor details:
function copy_and_paste_copyrange_3() ' ' =============================================================================================================== ' copyRange + Range - Dit is 'm! ' =============================================================================================================== cell_org=ThisComponent.sheets(0).getCellRangeByPosition(0,0,0,2) cell_dest=ThisComponent.sheets(0).getCellByPosition(0,3) ThisComponent.sheets(0).copyRange _ ( _ cell_dest.CellAddress, _ cell_org.RangeAddress _ ) end function
Casus (okt. 2019)
Het is drie jaar later, en ik wil hetzelfde truukje doen. Ik heb in de tussenliggende jaren nix meer gedaan op dit gebied. Dus hoe gaat dat ook al weer?
De code hieronder is een beginnetje. De echte truuk - Kopiëren van de ene sheet naar de andere, is nog niet gelukt. Dat kostte nu te veel tijd.
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' ' Insert sheet name on each row in new column on all sheets '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' ' ' This function doesn't call any other own functions - This is all ' function insert_sheet_name_on_each_row_on_all_sheets() ' dim o_cursor as object dim s_sheetname as string dim i_rows as integer dim i_sheets as integer ' Loop through all sheets ''''''''''''''''''''''''''''''''''''' ' for sheet_counter = 0 to ThisComponent.sheets.count-1 ' Get number of rows » i_rows ''''''''''''''''''''''''''''''''''''' ' o_cursor = ThisComponent.Sheets(sheet_counter).createCursor() o_cursor.gotoEndOfUsedArea(false) i_rows = o_cursor.getRangeAddress().EndRow+1 ' Sheet name » s_sheetname ''''''''''''''''''''''''''''''''''''' ' s_sheetname = ThisComponent.sheets(sheet_counter).Getname ' Insert column ''''''''''''''''''''''''''''''''''''' ' Thiscomponent.sheets(sheet_counter).columns.insertbyindex(0,1) ' Insert sheet name on each row ''''''''''''''''''''''''''''''''''''' for i = 0 to i_rows-1 ThisComponent.sheets(sheet_counter).getCellByPosition(0,i).String=s_sheetname next next end function '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' ' Insert sheet 'Aggregate' (if not exists) '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' ' function insert_sheet() dim doc as object dim sheet as object doc = ThisComponent if ThisComponent.sheets.hasByName("Aggregate") then ' Do nothing: Sheet "Aggregate" bestaat al ' MsgBox "Sheet 'Aggregate' already exists" else sheet = doc.createInstance("com.sun.star.sheet.Spreadsheet") doc.sheets.insertByName("Aggregate",sheet) end if end function
Zie ook
Bronnen
Algemeen
- http://www.debugpoint.com/libreoffice-basic-macro-tutorial-index/ - Hoopvol
- https://wiki.documentfoundation.org/Development/How_to_debug - Geen Immediate Windows zoals in VBA
Code
- LibreOffice 3.4 Basic Programmer's Guide, p. 106 [1]
- http://openoffice3.web.fc2.com/OOoBasic_Calc.html#OOoCCA01a - Diverse voorbeelden. Ook goed voor je Japans
- http://www.openoffice.org/api/docs/common/ref/com/sun/star/sheet/XCellRangeMovement.html#copyRange - XCellRangeMovement API-calls
- https://www.prahladyeri.com/blog/2016/02/ten-libreoffice-macro-recipes.html
Copy & paste cell ranges
- https://www.openoffice.org/api/docs/common/ref/com/sun/star/sheet/XCellRangeData.html - getDataArray & setDataArray
- https://wiki.openoffice.org/wiki/Documentation/DevGuide/Spreadsheets/Data_Array
- https://wiki.openoffice.org/wiki/Documentation/DevGuide/Spreadsheets/Cell_Ranges
- http://www.openoffice.org/api/docs/common/ref/com/sun/star/sheet/SheetCellRange.html
- https://forum.openoffice.org/en/forum/viewtopic.php?f=20&t=33756 - copyRange, voorbeeld-code
- https://forum.openoffice.org/en/forum/viewtopic.php?f=25&t=16413
- https://ask.libreoffice.org/en/question/129574/calc-copy-range-of-cells-from-all-sheets-into-new-sheet/ - Indrukwekkend
- https://ask.libreoffice.org/en/question/187542/macro-to-copy-current-sheet-and-insert-it-after-current-sheet/