Loading csv files into different sheets

[Bombadillo]

Hi, I've started learning basic and I'm trying to write a macro that loads different csv files into different sheets of the same spreadsheet. The problem is that I don't know how to reproduce the "Insert Sheet->From File" behaviour. The only function I found is loadComponentFromURL, but it's not what I need (at least I think...). Can anyone help me, please?

P.S.: As csv files are generated by a C++ program I wrote, how difficult would it be to use OpenOffice API C++ Binding to create a spreadsheet directly from that program?

[uros]

Hi Bombadillo!

Try this solution to import two (or more) csv files in one Calc dokument:
http://www.oooforum.org/forum/viewtopic.php?t=13861&start=0&postdays=0&postorder=asc&highlight=csv

Calc document is XML document, I asume you can create it directly with C++. Sorry, I don't know anything about C++.

Uros

[Bombadillo]

Thank you very much uros . It works perfectly (and it's easier than I thought)!

[Danad]

Another way ( XSheetLinkable ) that works for me:

[uros]

Hi Danad!

Great job, much better then my way!

Uros

[Bombadillo]

I've tried Danad's solution and its quite faster (I have to load nearly a hundred of csv files), thank you very much Now a final question: as some sheets contain data from different files, is there a way to link external data into a sheet starting from a given position? I've tried to look at documentation but I haven't found anything, so for the moment I simply create a temporary sheet and then copy data from it (like in uros' code).

Bye!

[Danad]

Uros,
Thanks for the kind words.

Bombadillo,

Look for ( dev's guide ):
- method insertAtPosition of interface XAreaLinks
- property AreaLinks
- struct CellAddress

Code is almost the same as previous sample.

[Bombadillo]

Thank you very much Danad

[rufus]

Danad,
Could you explain more detail about inserting data at current position. I found the insertAtPosition method in Developers Guide, but i did not understand how to use it.
Thank you.

[Danad]

There's an old snip here:

http://www.oooforum.org/forum/viewtopic.phtml?t=23067&highlight=insertatposition

HTH

[rufus]

Danad,
I tried your code, but it works not correctly for me. I need to import CSV-file into Calc in "DOS/OS2 866 Cyrr" codepage at 6th row.

When i use this code:

sub ImportCSVIntoSheet
oDoc = thisComponent
' 1st sheet
oPlan = oDoc.getSheets().getByIndex(0)
' set arguments
sURL = "file:///C:/Zakaz/Zakaz_oo/zakaz.csv"
'sURL = ConvertToURL ( sURL )
sOrigem = ""
sFiltro = "Text - txt - csv (StarCalc)"
sOpc = "44,34,30,1,"
nModo = com.sun.star.sheet.SheetLinkMode.NORMAL
' link file
oPlan.link(sURL, sOrigem, sFiltro, sOpc, nModo)
' reset link
oPlan.setLinkMode(com.sun.star.sheet.SheetLinkMode.NONE)
end sub

it imports in correct codepage ("44,34,30,1," parameters are responsible for this), but at 1st row. But when i try this:

Sub ex_Area_Link
Dim oDoc As Object
Dim oPlan As Object
Dim oLinks As Object
Dim aPos As New com.sun.star.table.CellAddress
oDoc = ThisComponent
oPlan = oDoc.getSheets().getByIndex(0)
sURL = "file:///C:/Zakaz/Zakaz_oo/zakaz.csv"
sFonte = "Planilha2.A1:E5"
sFiltro = "Text - txt - csv (StarCalc)"
sOpcoes = "44,34,30,1,"
aPos.Sheet = 0
aPos.Column = 0
aPos.Row = 5
oLinks = oDoc.AreaLinks
oLinks.insertAtPosition(aPos, sURL, sFonte, sFiltro, sOpcoes)
oLinks.removeByIndex(oLinks.Count - 1)
End Sub

it imports at 6th row, BUT in wrong codepage, like this:

22040560942 à ¦¥­áª¨¥ Ž‚ˆƒŽ €­âà æ¨â 36
22040560946 à ¦¥­áª¨¥ Ž‚ˆƒŽ €­âà æ¨â 40
22040560948 à ¦¥­áª¨¥ Ž‚ˆƒŽ €­âà æ¨â 42
22040561940 à ¦¥­áª¨¥ Ž‚ˆƒŽ ¥¦¥¢ë© 34
22040561942 à ¦¥­áª¨¥ Ž‚ˆƒŽ ¥¦¥¢ë© 36

How can i fix it?

[Danad]

As you can see here (section 9.2.2):
http://api.openoffice.org/docs/DevelopersGuide/Spreadsheet/Spreadsheet.xhtml

the third token (30 in your case) refers to character set.

The only thing (AFAIK) documented is that a value 0 => to the System char set.

HTH

[rivella]