OpenOffice.org Forum at OOoForum.orgThe OpenOffice.org Forum
 
 [Home]   [FAQ]   [Search]   [Memberlist]   [Usergroups]   [Register
 [Profile]   [Log in to check your private messages]   [Log in

Question: How would I add or delete Sheets using Macro...

 
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Calc
View previous topic :: View next topic  
Author Message
r_vinoya
Guest





PostPosted: Tue Dec 02, 2003 9:44 pm    Post subject: Question: How would I add or delete Sheets using Macro... Reply with quote

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 #Smile
Back to top
DannyB
Moderator
Moderator


Joined: 02 Apr 2003
Posts: 3991
Location: Lawrence, Kansas, USA

PostPosted: Wed Dec 03, 2003 7:39 am    Post subject: Reply with quote

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
View user's profile Send private message
DannyB
Moderator
Moderator


Joined: 02 Apr 2003
Posts: 3991
Location: Lawrence, Kansas, USA

PostPosted: Wed Dec 03, 2003 8:22 am    Post subject: Reply with quote

Also see this....

http://www.oooforum.org/forum/viewtopic.php?t=3613
_________________
Want to make OOo Drawings like the colored flower design to the left?
Back to top
View user's profile Send private message
r_vinoya
Guest





PostPosted: Wed Dec 03, 2003 8:38 pm    Post subject: thanks Reply with quote

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
Super User


Joined: 09 Sep 2003
Posts: 3211
Location: Troyes France

PostPosted: Wed Dec 03, 2003 11:46 pm    Post subject: Reply with quote

See http://www.oooforum.org/forum/viewtopic.php?t=4188 for formating chart.
I have tested chart with OOoBASIC wihtout success for XYChart but with no problem with LineChart.
This is good documented in the sun documentation
I hope to remember to this thread this evening when I come back at home and give you some code.
:
_________________
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
View user's profile Send private message Visit poster's website
SergeM
Super User
Super User


Joined: 09 Sep 2003
Posts: 3211
Location: Troyes France

PostPosted: Thu Dec 04, 2003 12:06 am    Post subject: Reply with quote

See the sun documentation at
http://docs.sun.com/db/doc/817-1826-10
there is a chapter on charts.
_________________
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
View user's profile Send private message Visit poster's website
SergeM
Super User
Super User


Joined: 09 Sep 2003
Posts: 3211
Location: Troyes France

PostPosted: Thu Dec 04, 2003 12:43 pm    Post subject: Reply with quote

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
View user's profile Send private message Visit poster's website
r_vinoya
Super User
Super User


Joined: 03 Dec 2003
Posts: 619
Location: Somewhere in the Philippines

PostPosted: Thu Dec 04, 2003 5:04 pm    Post subject: follow-up question on charts Reply with quote

Thanks for the code Very Happy ...I haven't tried it yet... there is some trouble with my PC. Sad

Anyway...I have another question(s):

Question How would you refer/change Axis titles (x-axis/y-axis)?
(ex. for subtitle... code: oChart.SubTitle.string="Subtitle")

Question How would you refer/change axis number format? (changing axis decimal places/number format)

Next question: Even if not using Macro...
Question Is there way to add another data series in Charts? How?
_________________
# : - )
Back to top
View user's profile Send private message
SergeM
Super User
Super User


Joined: 09 Sep 2003
Posts: 3211
Location: Troyes France

PostPosted: Sat Dec 06, 2003 7:39 am    Post subject: Reply with quote

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
View user's profile Send private message Visit poster's website
Display posts from previous:   
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Calc All times are GMT - 8 Hours
Page 1 of 1

 
Jump to:  
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