REM  *****  BASIC  *****
 
 Sub kakeibo_tutorial
 Dim oDoc As Object, oSheet As Object
 Dim oRange As Object
 Dim aHeader() As String
 Dim oCell As Object
 Dim i As Integer
 Dim oBorderLine1 As New com.sun.star.table.BorderLine
 Dim oBorderLine2 As New com.sun.star.table.BorderLine
 Dim oBorderLine3 As New com.sun.star.table.BorderLine
 Dim oTableBorder As New com.sun.star.table.TableBorder
 Dim oColumn As Object
 Dim oLocale As New com.sun.star.lang.Locale
 Dim nKey As Long
 Dim oNumberFormats As Object
 Dim sFormat As String
 Dim oStyleFamilies As Object
 Dim oCellStyles As Object
 Dim oSunStyle As Object, oSatStyle As Object, oNextMonth As Object
 Dim aNewStyles(2) As Object
 Dim sNewStyleName(2) As String
 Dim oConditionalFormat As Object
 Dim aCondition1(2) As New com.sun.star.beans.PropertyValue
 Dim aCondition2(2) As New com.sun.star.beans.PropertyValue
 Dim aCondition3(2) As New com.sun.star.beans.PropertyValue
 
   
   oDoc = ThisComponent
   oSheet = oDoc.getSheets().getByIndex(0)
   
   oRange = oSheet.getCellRangeByName("A1:T50")
   
   oRange.CellBackColor = RGB(230,230,255) 'Blue gray
   
   aHeader() = Array( "収入", "住居費", "光熱費", "消耗品", _
      "教育費", "食費", "嗜好品", "教養娯楽", "衣服", _
      "交通費", "その他", "支出計", "残高" )
      
   For i = 0 To 12
     oSheet.getCellByPosition( 4 + i, 2 ).String = aHeader(i)
   Next i
   oRange = oSheet.getCellRangeByPosition( 4, 2, 14, 2 ) 'Range "E3:O3"
   With oRange
     .RotateAngle = 4500
     .HoriJustify = com.sun.star.table.CellHoriJustify.CENTER
   End With
   
   With oBorderLine1
     .Color = RGB(230, 230, 255)
     .OuterLineWidth = 2 '0.05 pt
   End With
   
   With oTableBorder
     .IsLeftLineValid = True
     .IsRightLineValid = True
     .IsVerticalLineValid = True
     .LeftLine = oBorderLine1
     .RightLine = oBorderLine1
     .VerticalLine = oBorderLine1
   End With
 
 
   With oSheet
     .getCellRangeByPosition( 4, 2, 14, 2 ).CharColor = RGB(230,230,255)
     .getCellRangeByPosition( 4, 2, 14, 2 ).TableBorder = oTableBorder
     .getCellByPosition( 4, 2 ).CellBackColor = RGB( 92, 133, 38 ) 'Green 2
     .getCellRangeByPosition( 5, 2, 14, 2 ).CellBackColor = _
         RGB( 0, 74, 74 ) 'Turquoise 8
     .getCellRangeByPosition( 15, 2, 16, 2 ).HoriJustify = _
         com.sun.star.table.CellHoriJustify.RIGHT
   End With
   oColumn = oSheet.getCellRangeByPosition(0,0,1,0).getColumns()
   oColumn.Width = 1000
   
   oCell = oSheet.getCellRangeByName("C3")
   oCell.Value = Month( Now )
   sFormat = "0月分"
   oNumberFormats = oDoc.NumberFormats
   nKey = oNumberFormats.queryKey( sFormat, oLocale, false )
   If nKey <> -1 Then
     oCell.NumberFormat = nKey
   Else
     nKey = oNumberFormats.addNew( sFormat, oLocale )
     oCell.NumberFormat = nKey
   End If
   oCell.CharHeight = 24
   oCell.getColumns().OptimalWidth = true
   oCell.HoriJustify = com.sun.star.table.CellHoriJustify.CENTER
   
   oCell = oSheet.getCellRangeByName( "C5" )
   oCell.Formula = "=DATE(" & YEAR( NOW ) & ";C3;1)"
   oSheet.getCellRangeByName( "C6" ).Formula = "=C5+1"
   
   oRange = oSheet.getCellRangeByName("C6:C35")
   oRange.fillAuto( com.sun.star.sheet.FillDirection.TO_BOTTOM, 1 )
   
   oRange = oSheet.getCellRangeByName("C5:C35")
   nKey = -1
   sFormat = "M月D日(AAA)"
   nKey = oNumberFormats.queryKey( sFormat, oLocale, false )
   If nKey <> -1 Then
     oRange.NumberFormat = nKey
   Else
     nKey = oNumberFormats.addNew( sFormat, oLocale )
     oRange.NumberFormat = nKey
   End If
   oRange.CharHeight = 11
   oRange.HoriJustify = com.sun.star.table.CellHoriJustify.CENTER
   
   oStyleFamilies = oDoc.StyleFamilies
   oCellStyles = oStyleFamilies.getByName("CellStyles")
   sNewStyleName() = Array( "土曜日", "日曜日", "翌月" )
   aNewStyles() = Array( oSatStyle, oSunStyle, oNextMonth )
   For i = 0 To 2
     If NOT oCellStyles.hasByName(sNewStyleName(i)) Then
       aNewStyles(i) = oDoc.createInstance("com.sun.star.style.CellStyle")
       oCellStyles.insertByName( sNewStyleName(i), aNewStyles(i) )
     Else
       oCellStyles.removeByName( sNewStyleName(i) )
       aNewStyles(i) = oDoc.createInstance("com.sun.star.style.CellStyle")
       oCellStyles.insertByName( sNewStyleName(i), aNewStyles(i) )
     End If
   Next i
   oSatStyle.CellBackColor = RGB( 153, 204, 255 )
   oSunStyle.CellBackColor = RGB( 255, 0, 255 )
   With oNextMonth
     .CharColor = RGB(230,230,255)
     .CellBackColor = RGB(230,230,255)
   End With
   
   oConditionalFormat = oRange.ConditionalFormat
   
   aCondition1(0).Name = "Operator"
   aCondition1(0).Value = com.sun.star.sheet.ConditionOperator.GREATER
   aCondition1(1).Name = "Formula1"
   aCondition1(1).Value = "EOMONTH($Sheet1.$C$5;0)"
   aCondition1(2).Name = "StyleName"
   aCondition1(2).Value = sNewStyleName(2)
   
   aCondition2(0).Name = "Operator"
   aCondition2(0).Value = com.sun.star.sheet.ConditionOperator.FORMULA
   aCondition2(1).Name = "Formula1"
   aCondition2(1).Value = "WEEKDAY(A1)=7"
   aCondition2(2).Name = "StyleName"
   aCondition2(2).Value = sNewStyleName(0)
   
   aCondition3(0).Name = "Operator"
   aCondition3(0).Value = com.sun.star.sheet.ConditionOperator.FORMULA
   aCondition3(1).Name = "Formula1"
   aCondition3(1).Value = "WEEKDAY(A1)=1"
   aCondition3(2).Name = "StyleName"
   aCondition3(2).Value = sNewStyleName(1)
   
   With oConditionalFormat
     .clear()
     .addNew( aCondition1() )
     .addNew( aCondition2() )
     .addNew( aCondition3() )
   End With
   oRange.ConditionalFormat = oConditionalFormat
   
   With oSheet
     .getCellRangeByName( "C4" ).String = "前月より"
     .getCellRangeByName( "C37" ).String = "合計"
     .getCellRangeByName( "C4:C37" ).HoriJustify = _
       com.sun.star.table.CellHoriJustify.CENTER
     .getCellRangeByName( "P5" ).Formula = "=SUM(F5;O5)"
     .getCellRangeByName( "Q5" ).Formula = "=Q4+E5-P5"
     .getCellRangeByName( "E37" ).Formula = "=SUM(E5;E35)"
   End With
   oRange = oSheet.getCellRangeByName("P5:P35")
   oRange.fillAuto( com.sun.star.sheet.FillDirection.TO_BOTTOM, 1 )
   oRange = oSheet.getCellRangeByName("Q5:Q35")
   oRange.fillAuto( com.sun.star.sheet.FillDirection.TO_BOTTOM, 1 )
   oRange = oSheet.getCellRangeByName("E37:P37")
   oRange.fillAuto( com.sun.star.sheet.FillDirection.TO_RIGHT, 1 )
   oRange = oSheet.getCellRangeByName("E4:Q37")
   
   nKey = oNumberFormats.queryKey( "#,##0", oLocale, false )
   If nKey <> -1 Then
     oRange.NumberFormat = nKey
   Else
     nKey = oNumberFormats.addNew( "#,##0", oLocale )
     oRange.NumberFormat = nKey
   End If
   
   With oBorderLine2 'white line
     .Color = RGB(255, 255, 255) 'white
     .OuterLineWidth = 141 '4 pt
   End With
   
   With oBorderLine3 'gray line
     .Color = RGB(153, 153, 153) 'gray 40%
     .OuterLineWidth = 141 '4 pt
   End With
   
   oTableBorder = createUnoStruct("com.sun.star.table.TableBorder")
   With oTableBorder
     .IsLeftLineValid = true
     .IsTopLineValid = true
     .IsRightLineValid = true
     .IsBottomLineValid = true
     .LeftLine = oBorderLine2
     .TopLine = oBorderLine2
     .RightLine = oBorderLine3
     .BottomLine = oBorderLine3
   End With
   oSheet.getCellRangeByName( "B2:R38" ).TableBorder = oTableBorder
   
   With oTableBorder
     .LeftLine = oBorderLine3
     .TopLine = oBorderLine3
     .RightLine = oBorderLine2
     .BottomLine = oBorderLine2
   End With
   oSheet.getCellRangeByName( "E5:O35" ).TableBorder = oTableBorder
 End Sub

Reload   New Lower page making Edit Freeze Diff Upload Copy Rename   Front page List of pages Search Recent changes Backup   Help   RSS of recent changes