| View previous topic :: View next topic |
| Author |
Message |
DannyB
Moderator
Joined: 02 Apr 2003
Posts: 4021
Location: Lawrence, Kansas, USA
|
 Posted: Wed Jun 09, 2004 11:58 am Post subject: Spreadsheet splitter (split each sheet into separate doc) |
 |
|
Here is a program that opens a spreadsheet (either SXC or XLS) and then splits it up so that each sheet is a separate document.
Each sheet is exported in several formats...
1. XLS (excel)
2. SXC (calc)
3. PDF (portable document format - adobe acrobat)
4. CSV (comma separated values)
For instance, if you call the macro with a url like this....
SplitSheets( "file:///C:/path/to/my/doc.xls" )
Then in the folder
C:\path\to\my
you end up with....
doc.xls (your original document)
doc - Sheet 1.csv
doc - Sheet 1.pdf
doc - Sheet 1.sxc
doc - Sheet 1.xls
doc - Sheet 2.csv
doc - Sheet 2.pdf
doc - Sheet 2.sxc
doc - Sheet 2.xls
doc - Sheet 3.csv
doc - Sheet 3.pdf
doc - Sheet 3.sxc
doc - Sheet 3.xls
(assuming the original document started out with 3 sheets)
Here is the code.
| Code: | Sub Main
cCalcDocToSplit = "C:\Documents and Settings\dbrewer\Desktop\Test\Test.sxc"
SplitSheets( cCalcDocToSplit )
End Sub
Sub SplitSheets( cCalcDocToSplit )
' Open the document to find out how many Sheets it has.
oDoc = StarDesktop.loadComponentFromURL( ConvertToURL( cCalcDocToSplit ), "_blank", 0, Array() )
nNumSheets = oDoc.getSheets().getCount()
' Now that we know how many Sheets it has, close it.
oDoc.close( True )
' Get the name of the document, but without a filename suffix.
cCalcDocToSplitNoSuffix = Left( cCalcDocToSplit, Len( cCalcDocToSplit ) - 4 )
' Now loop once for each Sheet.
nHighestSheetNumber = nNumSheets-1
For nSheetToSave = 0 To nHighestSheetNumber
' Open the document.
oDoc = StarDesktop.loadComponentFromURL( ConvertToURL( cCalcDocToSplit ), "_blank", 0, Array() )
' Delete all Sheets except the one we're interested in keeping
' on this loop.
DeleteAllSheetsExcept( oDoc, nSheetToSave )
' Prepare to save the document in multiple forms.
' First get the new filename to save it under.
cNewName = cCalcDocToSplitNoSuffix + " -- Sheet " + CSTR( nSheetToSave + 1 )
' Save the document as a new calc document.
oDoc.storeToURL( ConvertToURL( cNewName + ".sxc" ), _
Array() )
' Save it as a XLS.
oDoc.storeToUrl( ConvertToURL( cNewName + ".xls" ), _
Array( MakePropertyValue( "FilterName", "MS Excel 97" ) ) )
' Save it as a PDF.
oDoc.storeToUrl( ConvertToURL( cNewName + ".pdf" ), _
Array( MakePropertyValue( "FilterName", "calc_pdf_Export" ) ) )
' Save it as a CSV.
oDoc.storeToUrl( ConvertToURL( cNewName + ".csv" ), _
Array( MakePropertyValue( "FilterName", "Text - txt - csv (StarCalc)" ) ) )
' Close the document without saving it.
oDoc.close( True )
Next
End Sub
' Delete all Sheets of an Impress or Draw document,
' EXCEPT for a certian Sheet that we want to keep.
Function DeleteAllSheetsExcept( oDoc, nSheetToKeep )
nNumSheets = oDoc.getSheets().getCount()
nHighestSheetNumber = nNumSheets-1
' Delete the last Sheet, then the Sheet before that,
' then the Sheet before that, until we get to the
' Sheet to keep.
' This deletes all Sheets AFTER the Sheet to keep.
nSheetToDelete = nHighestSheetNumber
Do while nSheetToDelete > nSheetToKeep
' Get the Sheet.
oSheet = oDoc.getSheets().getByIndex( nSheetToDelete )
' Tell the document to remove it.
oDoc.getSheets().removeByName( oSheet.getName() )
nSheetToDelete = nSheetToDelete - 1
Loop
' Delete all the Sheets before the Sheet to keep.
For i = 0 To nSheetToKeep - 1
' Delete the first Sheet.
nSheetToDelete = 0
' Get the Sheet.
oSheet = oDoc.getSheets().getByIndex( nSheetToDelete )
' Tell the document to remove it.
oDoc.getSheets().removeByName( oSheet.getName() )
Next
End Function
Function MakePropertyValue( Optional cName As String, Optional uValue ) As com.sun.star.beans.PropertyValue
oPropertyValue = createUnoStruct( "com.sun.star.beans.PropertyValue" )
If Not IsMissing( cName ) Then
oPropertyValue.Name = cName
EndIf
If Not IsMissing( uValue ) Then
oPropertyValue.Value = uValue
EndIf
MakePropertyValue() = oPropertyValue
End Function
|
This program is remarkably similar to a program I wrote to split Impress slides....
Slide Splitter
http://www.oooforum.org/forum/viewtopic.php?t=7796
http://www.oooforum.org/forum/viewtopic.php?p=32470#32470
http://www.oooforum.org/forum/viewtopic.php?p=23018#23018
http://www.oooforum.org/forum/viewtopic.php?t=2664
http://www.oooforum.org/forum/viewtopic.php?p=9819#9819
The Impress slide splitter is available at OOoMacros.org.
_________________
Want to make OOo Drawings like the colored flower design to the left? |
|
| Back to top |
|
 |
DannyB
Moderator
Joined: 02 Apr 2003
Posts: 4021
Location: Lawrence, Kansas, USA
|
|
| Back to top |
|
|
qubodup
Newbie
Joined: 26 Oct 2009
Posts: 1
Location: Berlin, Germany
|
| Posted: Mon Oct 26, 2009 3:33 am Post subject: |
|
|
| Thank you for this snippet. I will try to use it in combination with the Combine Files macro to merge the sheets in a file. |
|
| 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
|
