| View previous topic :: View next topic |
| Author |
Message |
r_vinoya
Guest
|
 Posted: Tue Dec 02, 2003 9:44 pm Post subject: Question: How would I add or delete Sheets using Macro... |
 |
|
How would you program Macro in OO...
in excel
sheets.add => ?
sheets.delete => ?
workbooks.open filename
I'm trying to migrate excel teemplates with macro. I will appreciate it very much when you answer my questions. Thank you very much.
r_vinoya from the Philippines # |
|
| Back to top |
|
 |
DannyB
Moderator
Joined: 02 Apr 2003
Posts: 3991
Location: Lawrence, Kansas, USA
|
| Posted: Wed Dec 03, 2003 7:39 am Post subject: |
|
|
Study this example. It should get you started.
| Code: | Sub Main
' Set oDoc to which spreadsheet document we're working with.
' Three examples of how to set the variable oDoc...
'-----
' In this case, work with the document that this macro is embedded into.
' oDoc = ThisComponent
'-----
'-----
' Use this line instead to create a NEW calc document.
oDoc = StarDesktop.loadComponentFromURL( "private:factory/scalc", "_blank", 0, Array() )
'-----
'-----
' Use this instead to open an EXISTING calc document.
' cFile = "C:\Documents and Settings\dbrewer\Desktop\MyCalc" ' Windows
' cFile = "/home/danny/Desktop/MyCalc.sxc" ' Linux
' cURL = ConvertToURL( cFile + ".sxc" )
' oDoc = StarDesktop.loadComponentFromURL( cURL, "_blank", 0, Array() )
'-----
' Here are two ways to get to a sheet.
' Note that these don't make the sheet *vislble*, merely give you
' access to its content within the program.
oSheet = oDoc.getSheets().getByIndex( 0 ) ' get the zero'th sheet
' oSheet = oDoc.getSheets().getByName( "Sheet3" ) ' get by name
'-----
' Put some sales figures onto the sheet.
oSheet.getCellByPosition( 0, 0 ).setFormula( "Month" )
oSheet.getCellByPosition( 1, 0 ).setFormula( "Sales" )
oSheet.getCellByPosition( 0, 1 ).setFormula( "Jan" )
oSheet.getCellByPosition( 0, 2 ).setFormula( "Feb" )
oSheet.getCellByPosition( 0, 3 ).setFormula( "Mar" )
oSheet.getCellByPosition( 0, 4 ).setFormula( "Apr" )
oSheet.getCellByPosition( 0, 5 ).setFormula( "May" )
oSheet.getCellByPosition( 0, 6 ).setFormula( "Jun" )
oSheet.getCellByPosition( 1, 1 ).setValue( 3826 )
oSheet.getCellByPosition( 1, 2 ).setValue( 3504 )
oSheet.getCellByPosition( 1, 3 ).setValue( 2961 )
oSheet.getCellByPosition( 1, 4 ).setValue( 2504 )
oSheet.getCellByPosition( 1, 5 ).setValue( 2102 )
oSheet.getCellByPosition( 1, 6 ).setValue( 1756 )
'-----
'-----
' Insert six more sheets.
nNumSheetsCurrently = oDoc.getSheets().getCount()
oDoc.getSheets().insertNewByName( "Fred", nNumSheetsCurrently+1 )
oDoc.getSheets().insertNewByName( "Joe", nNumSheetsCurrently+2 )
oDoc.getSheets().insertNewByName( "Bill", nNumSheetsCurrently+3 )
oDoc.getSheets().insertNewByName( "Sam", nNumSheetsCurrently+4 )
oDoc.getSheets().insertNewByName( "Tom", nNumSheetsCurrently+5 )
oDoc.getSheets().insertNewByName( "David", nNumSheetsCurrently+6 )
' Now find a sheet named "Sheet2" and get rid of it.
oDoc.getSheets().removeByName( "Sheet2" )
'-----
' Prepare the filename to save.
cFile = "C:\Documents and Settings\dbrewer\Desktop\MyCalc" ' Windows
' cFile = "/home/danny/Desktop/MyCalc.sxc" ' Linux
' Now save the spreadsheet.
cURL = ConvertToURL( cFile + ".sxc" )
oDoc.storeAsURL( cURL, Array() )
' Note the above used storAsUrl, the following use storeToUrl.
' Now save it in Excel format.
cURL = ConvertToURL( cFile + ".xls" )
oDoc.storeToURL( cURL, Array( MakePropertyValue( "FilterName", "MS Excel 97" ) ) )
' Now save a PDF.
cURL = ConvertToURL( cFile + ".pdf" )
oDoc.storeToURL( cURL, Array( MakePropertyValue( "FilterName", "calc_pdf_Export" ) ) )
' Now save as HTML.
cURL = ConvertToURL( cFile + ".html" )
oDoc.storeToURL( cURL, Array( MakePropertyValue( "FilterName", "HTML (StarCalc)" ) ) )
End Sub
'----------
' Create and return a new com.sun.star.beans.PropertyValue.
'
Function MakePropertyValue( Optional cName As String, Optional uValue ) As com.sun.star.beans.PropertyValue
Dim oPropertyValue As New 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
|
Be sure to change the pathnames of where the documents get saved.
The API documentation for spreadsheet related services is here...
http://api.openoffice.org/docs/common/ref/com/sun/star/sheet/module-ix.html
The spreadsheet document service is described here....
http://api.openoffice.org/docs/common/ref/com/sun/star/sheet/SpreadsheetDocument.html
Notice that it has an interface, among others, called XSpreadsheetDocument, which is described here...
http://api.openoffice.org/docs/common/ref/com/sun/star/sheet/XSpreadsheetDocument.html
Notice that XSpreadsheetDocument has a nifty method called getSheets()
http://api.openoffice.org/docs/common/ref/com/sun/star/sheet/XSpreadsheetDocument.html#getSheets
which returns an XSpreadsheets, and a note that says to See also: Spreadsheets.
XSpreadsheets is described here...
http://api.openoffice.org/docs/common/ref/com/sun/star/sheet/XSpreadsheets.html
Spreadsheets is described here...
http://api.openoffice.org/docs/common/ref/com/sun/star/sheet/Spreadsheets.html
You can see that Spreadsheets service includes the XSpreadsheets interface. The XSpreadsheets interface has methods...
insertNewByName()
moveByName()
copyByName()
and also inherits from XNameContainer
http://api.openoffice.org/docs/common/ref/com/sun/star/container/XNameContainer.html
which means you also have methods...
insertByName()
removeByName()
and this interface inherits from XNameReplace
http://api.openoffice.org/docs/common/ref/com/sun/star/container/XNameReplace.html
which means you also have methods....
replaceByName()
and XNameReplace inherits from XNameAccess
http://api.openoffice.org/docs/common/ref/com/sun/star/container/XNameAccess.html
which has methods
getByName()
getElementNames()
hasByName()
and XNameAccess inherits from XElementAccess, which has methods...
getElementType()
hasElements()
and XElementAccess inherits from XInterface, which has methods written in a language that I will not utter here.
Thus, when you call "getSheets()", as in my example above, it returns something called a "Spreadsheets" service, which has an "XSpreadsheets" interface, and has all of the above methods.
But wait! There's more! Now how much would you pay?
The Spreadsheets service also implements the XIndexAccess interface, which is where I got the getCount() method that I used in the above example.
The Developer's Guide is here...
http://api.openoffice.org/DevelopersGuide/DevelopersGuide.html
The Developer's Guide Table Of Contents is here...
http://api.openoffice.org/docs/DevelopersGuide/DevelopersGuide.htm
Good Luck.
_________________
Want to make OOo Drawings like the colored flower design to the left? |
|
| Back to top |
|
|
DannyB
Moderator
Joined: 02 Apr 2003
Posts: 3991
Location: Lawrence, Kansas, USA
|
|
| Back to top |
|
|
r_vinoya
Guest
|
| Posted: Wed Dec 03, 2003 8:38 pm Post subject: thanks |
|
|
Thank you very Much...DannyB.
Eh....
How about macros about formatting charts:
Ex. x-axis min/max, y-axis min/max, change chart datasource, something like that
Do you have any programs/reference on this? |
|
| Back to top |
|
|
SergeM
Super User
Joined: 09 Sep 2003
Posts: 3211
Location: Troyes France
|
|
| Back to top |
|
|
SergeM
Super User
Joined: 09 Sep 2003
Posts: 3211
Location: Troyes France
|
|
| Back to top |
|
|
SergeM
Super User
Joined: 09 Sep 2003
Posts: 3211
Location: Troyes France
|
| Posted: Thu Dec 04, 2003 12:43 pm Post subject: |
|
|
Here is an exemple of code.
I will post it in a new thread when I will have more deeply tested it...
What does this code do ?
Suppose for instance your x data in column A A2=1, A3=2, ... A13=12 (it's only an example) Suppose now your y data in column B For test I use the formula : =1+2*A2+3*A2*A2+4*A2*A2*A2 and drag it to B2:B13 which is clearly of order 3 (it's not important). and you want to determine y=ax^2+ bx + c (order 2) that best fit the data.
I run then my Sub :
Polyfit("A2:A13","B2:B13",0,2)
The number 0 is the index of the sheete where are your data.
The number 2 is the order of the polynom.
this code :
- create a new sheet called "Temp" if it doesn't exist
- copy the x and x^2 in it and then y
- compute the a, b and c
- compute the data with these coefficients
- create a chart with the two curves data and best fit
- print in the subtitle the equation of the best fit
I hote that this code will help you.
| Code: |
Function ReturnAbsoluteAddressAsString (x, y As INTEGER)
Dim nb,i As Integer
Dim AdrStr As String
AdrStr=""
nb= (x+1) / 26
For i=1 to nb
AdrStr = AdrStr+"A"
next
AdrStr = AdrStr+Chr((x mod 26)+65)
AdrStr = AdrStr+Cstr(y+1)
ReturnAbsoluteAddressAsString=AdrStr
End Function
Sub Polyfit(xGroupe,yGroupe As String,iSheetNo,Order as Integer)
Dim oScalcDocument,oSheetStart,oSheetTemp,oxGroup,oyGroup,oGroupLinest,oGroupTrend As Object
Dim oCellAddress As New com.sun.star.table.CellAddress
Dim FormulaStr,CellAddressByName, oxGroupName,oyGroupName, oGroupName As String
Dim i,x,y,j As Integer
Dim aRect As New com.sun.star.awt.Rectangle
Dim oCharts, oChart As Object
Dim mRangeAddress(2) As New com.sun.star.table.CellRangeAddress
oScalcDocument=ThisComponent
oSheetStart = oScalcDocument.Sheets(iSheetNo)
oxGroup=oSheetStart.getCellRangeByName(xGroupe)
oyGroup=oSheetStart.getCellRangeByName(yGroupe)
Dx= oxGroup.getColumns().count
Dy=oxGroup.GetRows().count
if Dx= oyGroup.getColumns().count and Dy=oyGroup.GetRows().count Then
'--- creation of temp sheet for calculus
if oSCalcDocument.Sheets.hasByName("Temp") Then
oSheetTemp = oSCalcDocument.Sheets.getByName("Temp")
else
oSheetTemp = oSCalcDocument.createInstance("com.sun.star.sheet.Spreadsheet")
oScalcDocument.Sheets.InsertByName("Temp",oSheetTemp)
end if
'----copy of x x^2 x^3 .....
For i =1 To Order
For x=0 To Dx-1
For y=0 To Dy-1 oCellAddress=oSCalcDocument.Sheets(iSheetNo).GetCellByPosition(x,y).GetCellAddress
CellAddressByName=oSCalcDocument.Sheets(iSheetNo).Name + "." + _
ReturnAbsoluteAddressAsString (oCellAddress.Column,oCellAddress.Row)
formulaStr= "=" + CellAddressByName
For j=2 To i
formulaStr = formulaStr + "*"+ CellAddressByName
Next
oSheetTemp.GetCellByPosition(x+(i-1)*Dx,y).formula = formulaStr
Next
Next
Next
'--- copy of y in x+(i-1)*Dx
For x=0 To Dx-1
For y=0 To Dy-1
oCellAddress=oyGroup.GetCellByPosition(x,y).GetCellAddress
formulaStr= "=" + oSCalcDocument.Sheets(iSheetNo).Name + "."+ _
ReturnAbsoluteAddressAsString (oCellAddress.Column,oCellAddress.Row)
oSheetTemp.GetCellByPosition(x+(i-1)*Dx,y).formula = formulaStr
Next
Next
'--- construction of a new oxGroup for linest and trend
x=Order*Dx -1
y=Dy-1
oxGroupName="A1:"+ ReturnAbsoluteAddressAsString(x,y)
oxGroup=oSheetTemp.getCellRangeByName(oxGroupName)
'--- construction of a new oyGroup for linest and trend
x=Order*Dx
oyGroupName= ReturnAbsoluteAddressAsString(x,0) + ":"
x=(Order+1)*Dx -1
y=Dy-1
oyGroupName= oyGroupName + ReturnAbsoluteAddressAsString(x,y)
oyGroup=oSheetTemp.getCellRangeByName(oyGroupName)
'--- construction of oGroupLinest for result of linest
'--- a line under data
oGroupName = ReturnAbsoluteAddressAsString(0,Dy+1)
oGroupName = oGroupName + ":" + ReturnAbsoluteAddressAsString(Order,Dy+1)
oGroupLinest = oSheetTemp.getCellRangeByName(oGroupName)
'---ready to compute linest now
StringFormula = "=LINEST(" + oyGroupName + ";" + oxGroupName + ";1;0)"
oGroupLinest.setArrayFormula(StringFormula)
'--- construction of oGroup for result of trend
oGroupName = ReturnAbsoluteAddressAsString((Order+2)*Dx -1,0) + ":"
oGroupName = oGroupName + ReturnAbsoluteAddressAsString((Order+3)*Dx -2,Dy-1)
oGroupTrend = oSheetTemp.getCellRangeByName(oGroupName)
'--- ready to compute trend now
StringFormula = "=TREND(" + oyGroupName + ";" + oxGroupName + ";" + oxGroupName + ";1)"
oGroupTrend.setArrayFormula(StringFormula)
'--- ready to show the graph now
'--- first prepare the recangle
aRect.X = 8000
aRect.Y = 1000
aRect.Width = 10000
aRect.Height = 10000
'--- second prepare the data
mRangeAddress(1)=oyGroup.GetRangeAddress()
mRangeAddress(2)=oGroupTrend.GetRangeAddress()
'--- we have now to reconstruct oxGroup
x = Dx -1
y = Dy-1
oxGroupName="A1:"+ ReturnAbsoluteAddressAsString(x,y)
oxGroup=oSheetTemp.getCellRangeByName(oxGroupName)
mRangeAddress(0)=oxGroup.GetrangeAddress()
oCharts = oScalcDocument.Sheets(iSheetNo).Charts
if oCharts.HasByName("LineDiagram") Then
oCharts.RemoveByname("LineDiagram")
end if
oCharts.addNewByName("LineDiagram",aRect,mRangeAddress(),TRUE, TRUE)
oChart = oCharts.getByName("LineDiagram").embeddedObject
oChart.diagram = oChart.createInstance("com.sun.star.chart.LineDiagram")
oChart.DataSourceLabelsInFirstColumn=TRUE
oChart.DataSourceLabelsInFirstRow=FALSE
oChart.HasMainTitle=True
oChart.Title.string = "Trend"
oChart.HasSubTitle=True
'--- Construction of the string for Subtitle
SubTitleString = "Polyfit : y="
For i=0 To Order
if (Order-i)>1 Then
SubTitleString = SubTitleString + oGroupLinest.getCellByPosition(i,0).string + "x^" + CStr(Order-i) + "+"
else
if (Order-i)=1 Then
SubTitleString = SubTitleString + oGroupLinest.getCellByPosition(i,0).string + "x" + "+"
else
SubTitleString = SubTitleString + oGroupLinest.getCellByPosition(i,0).string
end if
end if
Next
oChart.SubTitle.string = SubTitleString
Else
MsgBox("The two groups haven't the same size")
end if
End Sub
|
_________________
Linux & Windows OOo3.0
UNO & C++ : WIKI
http://wiki.services.openoffice.org/wiki/Using_Cpp_with_the_OOo_SDK
In French
http://wiki.services.openoffice.org/wiki/Documentation/FR/Cpp_Guide |
|
| Back to top |
|
|
r_vinoya
Super User
Joined: 03 Dec 2003
Posts: 619
Location: Somewhere in the Philippines
|
| Posted: Thu Dec 04, 2003 5:04 pm Post subject: follow-up question on charts |
|
|
Thanks for the code  ...I haven't tried it yet... there is some trouble with my PC. 
Anyway...I have another question(s):
 How would you refer/change Axis titles (x-axis/y-axis)?
(ex. for subtitle... code: oChart.SubTitle.string="Subtitle")
How would you refer/change axis number format? (changing axis decimal places/number format)
Next question: Even if not using Macro...
Is there way to add another data series in Charts? How?
_________________
# : - ) |
|
| Back to top |
|
|
SergeM
Super User
Joined: 09 Sep 2003
Posts: 3211
Location: Troyes France
|
| Posted: Sat Dec 06, 2003 7:39 am Post subject: |
|
|
A bug in my code : it only works with x data begining in the first line like "A1:A15". I have corrected it and will post it later...
Your first question
>>How would you refer/change Axis titles (x-axis/y-axis)?
>>(ex. for subtitle... code: oChart.SubTitle.string="Subtitle")
with hand :
Double click and then right click and autoformat and you can do what you want
You have to learn the difference between the two right click of question 1 and question 3 which are different
with programming :
| Code: |
Sub XAxisTitle()
Dim mychart,diagram
mychart=Thiscomponent.sheets(0).charts(0).embeddedobject
diagram = mychart.getdiagram
diagram.HasXAxisTitle = TRUE
diagram.XAxisTitle.string = "X data"
End Sub
|
works only if your chart is the first one (because of ...charts(0)...)
Second question
>>How would you refer/change axis number format? (changing axis decimal places/number format)
Not tested but i think
| Code: |
diagram.XAxis.Max=105
diagram.XAxis.Min=50
|
will works to change the range of data of your X axis.
For the third question
>>Is there way to add another data series in Charts? How?
Right click on the chart and choose
"change range of data" or something like that (I use a french version of OO)
You can add what you want. a quick test shows me that i only can add data in the same sheet. To confirm or infirm ?
_________________
Linux & Windows OOo3.0
UNO & C++ : WIKI
http://wiki.services.openoffice.org/wiki/Using_Cpp_with_the_OOo_SDK
In French
http://wiki.services.openoffice.org/wiki/Documentation/FR/Cpp_Guide |
|
| 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
|
