[Solved] [Base] Import Calc/Excel from Clipboard to Base

[laggat]

Hello!

I can do the following manually:
1. Copy some Cells from Excel or Calc
2. Switch to my OO-Base - to the Table View
3. Press STRG+V (some Import-Wizzard is opened)
4. Choose some settings in this dialog and save a new Table.

I want to do something similar automatically using a script:
The user should...
1. Copy some Cells from Excel or Calc (same as before)
2. Switch to my Form in OO-Base
3. Press "Import clipboard"
4. My script should save a new Table with the name "ImportedData", adding an ID-Column (without any user interaction)

Is this possible in Basic? How to do that (code examples preferred )?

Thanks in advance
Florian Lagg (http://www.lagg.at/)

[QuazzieEvil]

[Personally] I would suggeset to read the target file and a Statement, Prepared Statement or RowSet service to import the data. The assumption is that you have a file (spreadhsheet) from which you want to import at least one sheet.

Possible steps:

1) Use a file picker to allows the user to select the file
2) get file URL from file picker
3) Now there are two options. You can either connect to the spreadhsheet as a database, or open it and cycle through its rows and columns

Code: Select all

      Cell=Document.Sheets.getbyIndex(Col,Row)
      strContent=Cell.String REM NOW YOU CAN PASS THIS VALUE TO THE DATABASE. CYCLE THROUG THE RESEST OF THE CELLS IN SIMILAR FASHION
     

Now sure if this approach will be feasible for your particular use. I will provide more details on it if you can use this approach or if you need them.

[Villeroy]

Code: Select all

oSel = thisCOmponent.getCurrentSelection()
If oSel.supportsService("com.sun.star.sheet.SheetCellRange")then
  data()= oSel.getDataArray()
  call processData(oDatabaseTable, data())
else
  Msgbox "No single range selected"
endif

Sub processData(oTBL, data())
REM have a lot of programming fun in clunky Basic!
End Sub

data() is a nested array like this:
[ (A1,B1,C1) ; (A2,B2,C2) ; (A3,B3,C3) ]
Since we are talking about spreadsheets, every single value can be one of:
- String
- Double (all dates, boolean, integers, percent, scientific numbers etc. are formatted doubles)
- Basic Null (for error values)
- empty cells are represented as empty strings ""

Now you have to map every single value to the respective data type of the matching database field.
Good luck.

[laggat]

Thanks a lot guys.
Havn't tried it now, but it should work.