| View previous topic :: View next topic |
| Author |
Message |
theholycow
General User
Joined: 01 Sep 2005
Posts: 14
|
 Posted: Mon Mar 13, 2006 11:52 am Post subject: Apply template to imported csv |
 |
|
I've searched, and have found a few times that this question has been asked, and never answered in a way that would be useful for me. I need to take CSV data of a ledger and print it in a format that my boss can read without his glasses.
This means that some columns need big fonts, some need small fonts, rows need to be two or three times the height of the text they contain, and the rows need horizontal borders. Additionally, the column widths guessed by OOo are not good for me; some columns don't need to be completely displayed, some need extra width, and I can't exceed one landscape page width on letter-sized paper. Oh yeah, and the margins need to be changed from default.
As much as possible needs to be done automatically, which is why I would like to use a template. I'm going to load the data, print the result, and exit OOo all from inside a macro (the same macro, in fact, that opens QuickBooks Pro and prints a report to a CSV).
Is there a decent way to do this? Do I just need to spend hours painstakingly creating a macro, and if so, should I use an OOo macro or the OS-level macro processor I'm using for QuickBooks Pro (I'm using AutoHotKey)? Should I give up entirely on using a spreadsheet program for this and write a program to process the file? |
|
| Back to top |
|
 |
noranthon
Super User
Joined: 07 Jul 2005
Posts: 3323
|
| Posted: Mon Mar 13, 2006 8:04 pm Post subject: |
|
|
I use several macros that do similar things. I've cobbled this together from various sources. If you want to use this, you'll have to change names and paths etc to suit.
BTW, not used or tested.
Last edited by noranthon on Wed Jun 13, 2007 8:35 pm; edited 1 time in total |
|
| Back to top |
|
|
theholycow
General User
Joined: 01 Sep 2005
Posts: 14
|
| Posted: Tue Mar 14, 2006 7:38 am Post subject: |
|
|
I just tested it and it works great. Thanks! Your code is as educational as it is functional.
If I understand correctly, the lines
| Code: | lBottom = oCursor.RangeAddress.EndRow
lRight = oCursor.RangeAddress.EndColumn |
automagically locate the lower-right cell (similar to how print ranges are automagically defined). Am I correct?
I added this:
| Code: | Dim printOptions(0) as new com.sun.star.beans.PropertyValue
oExport.Close(true)
oDoc.print(printoptions())
oDoc.Close( False ) |
The declaration is, of course, at the top with all the other declarations. The rest is at the bottom.
It closes the CSV, prints the new file to the default printer with the default options, and attempts to close the new file without saving. oDoc.print() alone causes an error; based on some googling, it seemed to need some options in that format, so I passed it an empty, zero-length array of that data type, and it no longer fails at Odoc.print(). Is my kludge the correct way to print?
Then, on Odoc.Close, I get this error:
| Quote: | BASIC runtime error
An exception occured
Type: com.sun.star.util.CloseVetoException
Message: Controller disagree .... |
I've googled but failed to find out how to close without saving.
Where can I find an introductory reference to this kind of stuff? |
|
| Back to top |
|
|
noranthon
Super User
Joined: 07 Jul 2005
Posts: 3323
|
| Posted: Tue Mar 14, 2006 11:51 pm Post subject: |
|
|
Don't really know how to describe the cell at endcolumn and endrow of the used area. OO calls it file end. Endcolumn is the last column of a range, endrow the last row. First column and row are StartColumn and StartRow.
There's also a method .createCursorByRange. I have used that where only defining part of the used area but have not fathomed the differences (if any).
I do not print from OO and I've never tried to close a document without saving changes. oDoc.close( true ) certainly closes an unmodified document. The material below should provide answers.
A good starting point is Andrew Pitonyak's document (there's also a Writer version) and website. Links to other documents and sites are on the API site. You should get the Xray Tool and the SDK for v2. There's also a HowTo and a manual. |
|
| Back to top |
|
|
bigfish6874
General User
Joined: 03 Dec 2006
Posts: 10
|
| Posted: Sun Dec 10, 2006 6:36 pm Post subject: Opening CSV file and putting it in an OpenCalc Template |
|
|
| noranthon wrote: | I use several macros that do similar things. I've cobbled this together from various sources. If you want to use this, you'll have to change names and paths etc to suit.
| Code: | Sub ConvertCsvData
'Puts data from csv file into new spreadsheet document created from other than default template
Dim oCell as Object, oCursor as Object, oDoc as Object, oExport as Object, _
oSheet1 as Object, oSheet2 as Object, oSource as Object, oTarget as Object
Dim sExport as String, sTemplate as String
Dim lBottom as Long, lCols as Long, lRight as Long, lRows as Long
Dim aCsvProps( 1 ) as new com.sun.star.beans.PropertyValue, _
aTemplate( 0 ) as new com.sun.star.beans.PropertyValue, aData
'Define the file export.csv
sExport = "file:///~/Reports/export.csv"
aCsvProps(0).name = "Filtername"
aCsvProps(0).Value = "Text - txt - csv(StarCalc)"
aCsvProps(1).name = "FilterData"
aCsvProps(1).Value = "Any"
'Open .csv file.
If NOT FileExists( sExport ) Then
Msgbox( "No file named " & sExport ) : Exit sub
End If
oExport = StarDesktop.loadComponentFromURL( sExport, "_blank", 8, aCsvProps() )
'Create a new file from named template
sTemplate = "file:///~/.openoffice.org2/user/template/NewSS1.ots"
aTemplate( 0 ).Name = "AsTemplate"
aTemplate( 0 ).Value = true
oDoc = StarDesktop.LoadComponentFromUrl( sTemplate, "_blank" , 0, aTemplate() )
'Define the range from which data is to be taken
oSheet1 = oExport.Sheets.getByIndex( 0 )
oCell = oSheet1.getCellByPosition( 0, 0 ) ' cell A1
oCursor = oSheet1.createCursor( oCell )
oCursor.gotoEndOfUsedArea( True )
lBottom = oCursor.RangeAddress.EndRow
lRight = oCursor.RangeAddress.EndColumn
oSource = oSheet1.getCellRangeByPosition( 0, 0, lRight, lBottom )
'Define the range to which data is to be copied
'Target range will need to be formatted before or after copying (hence use of special template)
lCols = oSource.Columns.getCount - 1
lRows = oSource.Rows.getCount - 1
oSheet2 = oDoc.Sheets.getByIndex( 0 )
oTarget = oSheet2.getCellRangeByPosition( 0, 0, lCols, lRows ) 'Begins at A1
'Copy the data
aData = oSource.getDataArray
oTarget.setDataArray( aData )
End Sub |
BTW, not used or tested.  |
I modified the code to point to the correct file paths. When I run it in OpenOffice 2.1, I got a runtime error in the line "oTarget.setDataArray (aData)". The error message window says "BASIC Runtime error.
An exception occured. Type: com.sun.star.uno.RuntimeException
Message:."
What could be wrong with the code. Pls advise. Thanks. |
|
| Back to top |
|
|
noranthon
Super User
Joined: 07 Jul 2005
Posts: 3323
|
| Posted: Sun Dec 10, 2006 7:53 pm Post subject: |
|
|
The dimensions of the source and target ranges must be equal. That is the most likely source of error.
_________________
search forum by month |
|
| Back to top |
|
|
bigfish6874
General User
Joined: 03 Dec 2006
Posts: 10
|
| Posted: Sun Dec 10, 2006 8:22 pm Post subject: |
|
|
| noranthon wrote: | | The dimensions of the source and target ranges must be equal. That is the most likely source of error. |
Thanks. I am new to this scrpting. I am wondering what should be the dimension for the variable aData. If you look at the code, it looks incomplete. Please advise. Thanks. |
|
| Back to top |
|
|
Villeroy
Super User
Joined: 04 Oct 2004
Posts: 7649
Location: Germany
|
| Posted: Mon Dec 11, 2006 5:09 am Post subject: |
|
|
Read only access without any scripting:
Save your csv-table in a dedicated directory. You may save other related csv in the same directory.
File>New>Database...
Wizzard:
[x] "Connect to existing database"
type: Text
Point to the directory having the csv
Save the resulting OOo-database wherever you like. You can use your csv-directory as well.
Open a calc document
Hit F4
Browse to your new datasource and drag the table to some cell.
You'll get an import-range, linked to the datasource-table.
Menu:Data>Define Range...
Pick "Import1" (the named database range pointing to the imported data)
Button "More Options"
[x] "Keep Formatting" (of sheet-cells)
[x] "Insert/Remove Cells" (resize the import range and all adjacent formulae when this import range is refreshed)
Button "Apply"
Button "OK"
Apply some styles.
Save your spreadsheet.
You can edit the imported data. But this will not change the csv. You may overwrite the original csv-file by saving the edited sheet or you may open the csv-file as a separate document and edit/copy/paste that one.
Possibly you want to supply a hyperlink, opening the original csv.
_________________
XUbuntu 9.04, OOo 3.1.1(Sun), Sun Java 1.5.0_06 |
|
| Back to top |
|
|
|
|
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum
|
Powered by phpBB © 2001, 2005 phpBB Group
|
