1*b1cdbd2cSJim Jagielski<?xml version="1.0" encoding="UTF-8"?> 2*b1cdbd2cSJim Jagielski<!DOCTYPE script:module PUBLIC "-//OpenOffice.org//DTD OfficeDocument 1.0//EN" "module.dtd"> 3*b1cdbd2cSJim Jagielski<!--*********************************************************** 4*b1cdbd2cSJim Jagielski * 5*b1cdbd2cSJim Jagielski * Licensed to the Apache Software Foundation (ASF) under one 6*b1cdbd2cSJim Jagielski * or more contributor license agreements. See the NOTICE file 7*b1cdbd2cSJim Jagielski * distributed with this work for additional information 8*b1cdbd2cSJim Jagielski * regarding copyright ownership. The ASF licenses this file 9*b1cdbd2cSJim Jagielski * to you under the Apache License, Version 2.0 (the 10*b1cdbd2cSJim Jagielski * "License"); you may not use this file except in compliance 11*b1cdbd2cSJim Jagielski * with the License. You may obtain a copy of the License at 12*b1cdbd2cSJim Jagielski * 13*b1cdbd2cSJim Jagielski * http://www.apache.org/licenses/LICENSE-2.0 14*b1cdbd2cSJim Jagielski * 15*b1cdbd2cSJim Jagielski * Unless required by applicable law or agreed to in writing, 16*b1cdbd2cSJim Jagielski * software distributed under the License is distributed on an 17*b1cdbd2cSJim Jagielski * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY 18*b1cdbd2cSJim Jagielski * KIND, either express or implied. See the License for the 19*b1cdbd2cSJim Jagielski * specific language governing permissions and limitations 20*b1cdbd2cSJim Jagielski * under the License. 21*b1cdbd2cSJim Jagielski * 22*b1cdbd2cSJim Jagielski ***********************************************************--> 23*b1cdbd2cSJim Jagielski<script:module xmlns:script="http://openoffice.org/2000/script" script:name="Soft" script:language="StarBasic">Option Explicit 24*b1cdbd2cSJim JagielskiREM ***** BASIC ***** 25*b1cdbd2cSJim Jagielski 26*b1cdbd2cSJim Jagielski 27*b1cdbd2cSJim JagielskiSub CreateStyleEnumeration() 28*b1cdbd2cSJim Jagielski EmptySelection() 29*b1cdbd2cSJim Jagielski EmptyListbox(DialogModel.lstSelection) 30*b1cdbd2cSJim Jagielski CurSheetName = oDocument.CurrentController.GetActiveSheet.Name 31*b1cdbd2cSJim Jagielski MakeStyleEnumeration(False) 32*b1cdbd2cSJim Jagielski DialogModel.lblSelection.Label = sTEMPLATES 33*b1cdbd2cSJim JagielskiEnd Sub 34*b1cdbd2cSJim Jagielski 35*b1cdbd2cSJim Jagielski 36*b1cdbd2cSJim JagielskiSub MakeStyleEnumeration(bAddToListbox as Boolean) 37*b1cdbd2cSJim JagielskiDim m as integer 38*b1cdbd2cSJim JagielskiDim aStyleFormat as Object 39*b1cdbd2cSJim JagielskiDim Stylename as String 40*b1cdbd2cSJim Jagielski StyleIndex = -1 41*b1cdbd2cSJim Jagielski oStyles = oDocument.StyleFamilies.GetbyIndex(0) 42*b1cdbd2cSJim Jagielski For m = 0 To oStyles.count-1 43*b1cdbd2cSJim Jagielski oStyle = oStyles.GetbyIndex(m) 44*b1cdbd2cSJim Jagielski StyleName = oStyle.Name 45*b1cdbd2cSJim Jagielski If CheckFormatType(oStyle) Then 46*b1cdbd2cSJim Jagielski If Not bAddToListBox Then 47*b1cdbd2cSJim Jagielski AddSingleItemToListbox(DialogModel.lstSelection, Stylename) 48*b1cdbd2cSJim Jagielski Else 49*b1cdbd2cSJim Jagielski SwitchNumberFormat(ostyle, oFormats, sEuroSign) 50*b1cdbd2cSJim Jagielski End If 51*b1cdbd2cSJim Jagielski StyleIndex = StyleIndex + 1 52*b1cdbd2cSJim Jagielski If StyleIndex > Ubound(StyleRangeAssignMentList()) Then 53*b1cdbd2cSJim Jagielski Redim Preserve StyleRangeAssignmentList(StyleIndex) 54*b1cdbd2cSJim Jagielski End If 55*b1cdbd2cSJim Jagielski StyleRangeAssignmentList(StyleIndex) = "<STYLENAME>" & Stylename & "</STYLENAME>" & _ 56*b1cdbd2cSJim Jagielski "<DEFINED>FALSE</DEFINED>" & "<RANGES></RANGES>" &_ 57*b1cdbd2cSJim Jagielski "<CELLCOUNT>0</CELLCOUNT>" &_ 58*b1cdbd2cSJim Jagielski "<SELECTED>FALSE</SELECTED>" 59*b1cdbd2cSJim Jagielski End If 60*b1cdbd2cSJim Jagielski Next m 61*b1cdbd2cSJim Jagielski If StyleIndex > -1 Then 62*b1cdbd2cSJim Jagielski Redim Preserve StyleRangeAssignmentList(StyleIndex) 63*b1cdbd2cSJim Jagielski Else 64*b1cdbd2cSJim Jagielski ReDim StyleRangeAssignmentList() 65*b1cdbd2cSJim Jagielski End If 66*b1cdbd2cSJim JagielskiEnd Sub 67*b1cdbd2cSJim Jagielski 68*b1cdbd2cSJim Jagielski 69*b1cdbd2cSJim JagielskiSub AssignRangestoStyle(StyleList(), SelList()) 70*b1cdbd2cSJim JagielskiDim i as Integer 71*b1cdbd2cSJim JagielskiDim n as integer 72*b1cdbd2cSJim JagielskiDim LastIndex as Integer 73*b1cdbd2cSJim JagielskiDim CurStyleName as String 74*b1cdbd2cSJim JagielskiDim AssignString as String 75*b1cdbd2cSJim Jagielski LastIndex = Ubound(StyleList()) 76*b1cdbd2cSJim Jagielski StatusValue = 0 77*b1cdbd2cSJim Jagielski SetStatusLineText(sStsRELRANGES) 78*b1cdbd2cSJim Jagielski For i = 0 To LastIndex 79*b1cdbd2cSJim Jagielski CurStyleName = StyleList(i) 80*b1cdbd2cSJim Jagielski n = PartStringInArray(StyleRangeAssignmentList(), CurStyleName, 0) 81*b1cdbd2cSJim Jagielski AssignString = StyleRangeAssignmentlist(n) 82*b1cdbd2cSJim Jagielski If IndexInArray(CurStyleName, SelList()) <> -1 Then 83*b1cdbd2cSJim Jagielski ' Style is selected 84*b1cdbd2cSJim Jagielski If FindPartString(AssignString, "<DEFINED>", "</DEFINED>", 1) = "FALSE" Then 85*b1cdbd2cSJim Jagielski AssignString = ReplaceString(AssignString, "<SELECTED>TRUE</SELECTED>", "<SELECTED>FALSE</SELECTED>") 86*b1cdbd2cSJim Jagielski AssignCellFormatRanges(n, AssignString, CurStyleName) 87*b1cdbd2cSJim Jagielski End If 88*b1cdbd2cSJim Jagielski Else 89*b1cdbd2cSJim Jagielski ' Style is not selected 90*b1cdbd2cSJim Jagielski If FindPartString(AssignString, "<SELECTED>", "</SELECTED>", 1) = "FALSE" Then 91*b1cdbd2cSJim Jagielski DeselectStyle(CurStyleName, n) 92*b1cdbd2cSJim Jagielski End If 93*b1cdbd2cSJim Jagielski End If 94*b1cdbd2cSJim Jagielski IncreaseStatusvalue(SBRELGET/(LastIndex+1)) 95*b1cdbd2cSJim Jagielski Next i 96*b1cdbd2cSJim JagielskiEnd Sub 97*b1cdbd2cSJim Jagielski 98*b1cdbd2cSJim Jagielski 99*b1cdbd2cSJim JagielskiSub AssignCellFormatRanges(n as Integer, AssignString as String, CurStyleName as String) 100*b1cdbd2cSJim JagielskiDim oRanges() as Object 101*b1cdbd2cSJim JagielskiDim oRange as Object 102*b1cdbd2cSJim JagielskiDim oRangeAddress 103*b1cdbd2cSJim JagielskiDim oSheet as Object 104*b1cdbd2cSJim JagielskiDim StyleCellCount as Long 105*b1cdbd2cSJim JagielskiDim i as Integer 106*b1cdbd2cSJim JagielskiDim MaxIndex as Integer 107*b1cdbd2cSJim JagielskiDim RangeString as String 108*b1cdbd2cSJim JagielskiDim SheetName as String 109*b1cdbd2cSJim JagielskiDim RangeName as String 110*b1cdbd2cSJim JagielskiDim CellCountString as String 111*b1cdbd2cSJim Jagielski StyleCellCount = 0 112*b1cdbd2cSJim Jagielski RangeString = "<RANGES>" 113*b1cdbd2cSJim Jagielski MaxIndex = oSheets.Count-1 114*b1cdbd2cSJim Jagielski For i = 0 To MaxIndex 115*b1cdbd2cSJim Jagielski oSheet = oSheets(i) 116*b1cdbd2cSJim Jagielski SheetName = oSheet.Name 117*b1cdbd2cSJim Jagielski oRanges = osheet.CellFormatRanges.CreateEnumeration 118*b1cdbd2cSJim Jagielski While oRanges.hasMoreElements 119*b1cdbd2cSJim Jagielski oRange = oRanges.NextElement 120*b1cdbd2cSJim Jagielski If oRange.getPropertyState("NumberFormat") = 1 Then 121*b1cdbd2cSJim Jagielski If oRange.CellStyle = CurStyleName Then 122*b1cdbd2cSJim Jagielski oRangeAddress = oRange.RangeAddress 123*b1cdbd2cSJim Jagielski RangeName = RetrieveRangeNamefromAddress(oRange) 124*b1cdbd2cSJim Jagielski RangeString = RangeString & RangeName & "," 125*b1cdbd2cSJim Jagielski StyleCellCount = StyleCellCount + CountRangeCells(oRange) 126*b1cdbd2cSJim Jagielski End If 127*b1cdbd2cSJim Jagielski End If 128*b1cdbd2cSJim Jagielski Wend 129*b1cdbd2cSJim Jagielski Next i 130*b1cdbd2cSJim Jagielski If StyleCellCount > 0 Then 131*b1cdbd2cSJim Jagielski TotCellCount = TotCellCount + StyleCellCount 132*b1cdbd2cSJim Jagielski RangeString = RTrimStr(RangeString,",") 133*b1cdbd2cSJim Jagielski RangeString = RangeString & "</RANGES>" 134*b1cdbd2cSJim Jagielski CellCountString = "<CELLCOUNT>" & StyleCellCount & "</CELLCOUNT" 135*b1cdbd2cSJim Jagielski AssignString = ReplaceString(AssignString, RangeString,"<RANGES></RANGES>") 136*b1cdbd2cSJim Jagielski AssignString = ReplaceString(AssignString, CellCountString,"<CELLCOUNT>0</CELLCOUNT>") 137*b1cdbd2cSJim Jagielski End If 138*b1cdbd2cSJim Jagielski AssignString = ReplaceString(AssignString, "<DEFINED>TRUE</DEFINED>", "<DEFINED>FALSE</DEFINED>") 139*b1cdbd2cSJim Jagielski StyleRangeAssignmentList(n) = AssignString 140*b1cdbd2cSJim JagielskiEnd Sub 141*b1cdbd2cSJim Jagielski 142*b1cdbd2cSJim Jagielski 143*b1cdbd2cSJim Jagielski' deletes a styletemplate from the Collection that selects the ranges 144*b1cdbd2cSJim JagielskiSub DeselectStyle(DeSelStyleName as String, n as Integer) 145*b1cdbd2cSJim JagielskiDim i as Integer 146*b1cdbd2cSJim JagielskiDim RangeName as String 147*b1cdbd2cSJim JagielskiDim SelectString as String 148*b1cdbd2cSJim JagielskiDim AssignString as String 149*b1cdbd2cSJim JagielskiDim StyleRangeList() as String 150*b1cdbd2cSJim JagielskiDim MaxIndex as Integer 151*b1cdbd2cSJim Jagielski SelectString ="<SELECTED>FALSE</SELECTED>" 152*b1cdbd2cSJim Jagielski AssignString = StyleRangeAssignmentList(n) 153*b1cdbd2cSJim Jagielski RangeString = FindPartString(AssignString,"<RANGES>","</RANGES>",1) 154*b1cdbd2cSJim Jagielski StyleRangeList() = ArrayoutofString(RangeString,",") 155*b1cdbd2cSJim Jagielski MaxIndex = Ubound(StyleRangeList()) 156*b1cdbd2cSJim Jagielski For i = 0 To MaxIndex 157*b1cdbd2cSJim Jagielski RangeName = StyleRangeList(i) 158*b1cdbd2cSJim Jagielski If oSelRanges.HasbyName(RangeName) Then 159*b1cdbd2cSJim Jagielski oSelRanges.RemovebyName(RangeName) 160*b1cdbd2cSJim Jagielski End If 161*b1cdbd2cSJim Jagielski Next i 162*b1cdbd2cSJim Jagielski AssignString = ReplaceString(AssignString, "<SELECTED>FALSE</SELECTED>", "<SELECTED>TRUE</SELECTED>") 163*b1cdbd2cSJim Jagielski StyleRangeAssignmentList(n) = AssignString 164*b1cdbd2cSJim JagielskiEnd Sub 165*b1cdbd2cSJim Jagielski 166*b1cdbd2cSJim Jagielski 167*b1cdbd2cSJim JagielskiFunction RetrieveRangeNamefromAddress(oRange as Object) as String 168*b1cdbd2cSJim JagielskiDim Rangename as String 169*b1cdbd2cSJim JagielskiDim oAddressRanges as Object 170*b1cdbd2cSJim Jagielski oAddressRanges = oDocument.createInstance("com.sun.star.sheet.SheetCellRanges") 171*b1cdbd2cSJim Jagielski oAddressRanges.InsertbyName("",oRange) 172*b1cdbd2cSJim Jagielski Rangename = oAddressRanges.RangeAddressesasString 173*b1cdbd2cSJim Jagielski' Msgbox "Adresse: " & oRangeAddress.StartColumn & " ; " & oRangeAddress.EndColumn & " ; " & oRangeAddress.StartRow & " ; " & oRangeAddress.EndRow & chr(13) & RangeName 174*b1cdbd2cSJim Jagielski' oAddressRanges.RemovebyName(RangeName) 175*b1cdbd2cSJim Jagielski RetrieveRangeNamefromAddress = Rangename 176*b1cdbd2cSJim JagielskiEnd Function 177*b1cdbd2cSJim Jagielski 178*b1cdbd2cSJim Jagielski 179*b1cdbd2cSJim Jagielski' creates a sheet object from an according sectionname 180*b1cdbd2cSJim JagielskiFunction RetrieveSheetoutofRangeName(TableText as String) 181*b1cdbd2cSJim JagielskiDim DescriptionList() as String 182*b1cdbd2cSJim JagielskiDim SheetName as String 183*b1cdbd2cSJim JagielskiDim MaxIndex as integer 184*b1cdbd2cSJim Jagielski ' find out in which sheet the range is 185*b1cdbd2cSJim Jagielski DescriptionList() = ArrayOutofString(TableText,".",MaxIndex) 186*b1cdbd2cSJim Jagielski SheetName = DescriptionList(0) 187*b1cdbd2cSJim Jagielski SheetName = DeleteStr(SheetName,"'") 188*b1cdbd2cSJim Jagielski ' set the viewcursor on this sheet 189*b1cdbd2cSJim Jagielski RetrieveSheetoutofRangeName = oSheets.GetbyName(SheetName) 190*b1cdbd2cSJim JagielskiEnd Function 191*b1cdbd2cSJim Jagielski 192*b1cdbd2cSJim Jagielski 193*b1cdbd2cSJim Jagielski' creates a rangeobject from an according rangename 194*b1cdbd2cSJim JagielskiFunction RetrieveRangeoutofRangeName(TableText as String) 195*b1cdbd2cSJim Jagielski oSheet = RetrieveSheetoutofRangeName(TableText) 196*b1cdbd2cSJim Jagielski oRange = oSheet.GetCellRangebyName(TableText) 197*b1cdbd2cSJim Jagielski RetrieveRangeoutofRangeName = oRange 198*b1cdbd2cSJim JagielskiEnd Function 199*b1cdbd2cSJim Jagielski 200*b1cdbd2cSJim Jagielski 201*b1cdbd2cSJim JagielskiSub ConvertTheSoftWay(StyleList(), bDeSelect as Boolean) 202*b1cdbd2cSJim JagielskiDim i as Integer 203*b1cdbd2cSJim JagielskiDim l as Integer 204*b1cdbd2cSJim JagielskiDim s as Integer 205*b1cdbd2cSJim JagielskiDim n as Integer 206*b1cdbd2cSJim JagielskiDim CurStyleName as String 207*b1cdbd2cSJim JagielskiDim RangeName as String 208*b1cdbd2cSJim JagielskiDim OldStatusValue as Integer 209*b1cdbd2cSJim JagielskiDim LastIndex as Integer 210*b1cdbd2cSJim JagielskiDim oSelListbox as Object 211*b1cdbd2cSJim JagielskiDim StyleRangeList() as String 212*b1cdbd2cSJim JagielskiDim MaxIndex as Integer 213*b1cdbd2cSJim Jagielski oSelListbox = DialogConvert.GetControl("lstSelection") 214*b1cdbd2cSJim Jagielski LastIndex = Ubound(StyleList()) 215*b1cdbd2cSJim Jagielski OldStatusValue = StatusValue 216*b1cdbd2cSJim Jagielski For i = 0 To LastIndex 217*b1cdbd2cSJim Jagielski CurStyleName = StyleList(i) 218*b1cdbd2cSJim Jagielski oStyle = oStyles.GetbyName(CurStyleName) 219*b1cdbd2cSJim Jagielski StyleRangeList() = GetAssignedRanges(CurStyleName, n) 220*b1cdbd2cSJim Jagielski MaxIndex = Ubound(StyleRangeList()) 221*b1cdbd2cSJim Jagielski For s = 0 To MaxIndex 222*b1cdbd2cSJim Jagielski RangeName = StyleRangeList(s) 223*b1cdbd2cSJim Jagielski oRange = RetrieveRangeoutofRangeName(RangeName) 224*b1cdbd2cSJim Jagielski If oRange.getPropertyState("NumberFormat") = 1 Then 225*b1cdbd2cSJim Jagielski ' Range is hard formatted 226*b1cdbd2cSJim Jagielski ConvertCellCurrencies(oRange) 227*b1cdbd2cSJim Jagielski CurCellCount = CountRangeCells(oRange) 228*b1cdbd2cSJim Jagielski End If 229*b1cdbd2cSJim Jagielski IncreaseStatusvalue((CurCellCount/TotCellCount)*(95-OldStatusValue)) 230*b1cdbd2cSJim Jagielski If bDeSelect Then 231*b1cdbd2cSJim Jagielski ' Note: On Problems see Bug #73157 232*b1cdbd2cSJim Jagielski If oSelRanges.HasbyName(RangeName) Then 233*b1cdbd2cSJim Jagielski oSelRanges.RemovebyName(RangeName) 234*b1cdbd2cSJim Jagielski oDocument.CurrentController.Select(oSelRanges) 235*b1cdbd2cSJim Jagielski End If 236*b1cdbd2cSJim Jagielski End If 237*b1cdbd2cSJim Jagielski Next s 238*b1cdbd2cSJim Jagielski SwitchNumberFormat(ostyle, oFormats, sEuroSign) 239*b1cdbd2cSJim Jagielski StyleRangeAssignmentList(n) = "" 240*b1cdbd2cSJim Jagielski l = GetItemPos(oSelListBox.Model, CurStyleName) 241*b1cdbd2cSJim Jagielski oSelListbox.RemoveItems(l,1) 242*b1cdbd2cSJim Jagielski Next 243*b1cdbd2cSJim JagielskiEnd Sub 244*b1cdbd2cSJim Jagielski 245*b1cdbd2cSJim Jagielski 246*b1cdbd2cSJim JagielskiFunction GetAssignedRanges(CurStyleName as String, n as Integer) 247*b1cdbd2cSJim JagielskiDim StyleRangeList() as String 248*b1cdbd2cSJim JagielskiDim RangeString as String 249*b1cdbd2cSJim JagielskiDim AssignString as String 250*b1cdbd2cSJim Jagielski n = PartStringInArray(StyleRangeAssignmentList(), CurStyleName, 0) 251*b1cdbd2cSJim Jagielski If n <> -1 Then 252*b1cdbd2cSJim Jagielski AssignString = StyleRangeAssignmentList(n) 253*b1cdbd2cSJim Jagielski RangeString = FindPartString(AssignString,"<RANGES>", "</RANGES>",1) 254*b1cdbd2cSJim Jagielski If RangeString <> "" Then 255*b1cdbd2cSJim Jagielski StyleRangeList() = ArrayoutofString(RangeString,",") 256*b1cdbd2cSJim Jagielski End If 257*b1cdbd2cSJim Jagielski End If 258*b1cdbd2cSJim Jagielski GetAssignedRanges() = StyleRangeList() 259*b1cdbd2cSJim JagielskiEnd Function</script:module> 260