Class: Rods

Inherits:
Object
  • Object
show all
Defined in:
lib/rods.rb

Constant Summary collapse

ROW = 
"row"
CELL = 
"cell"
TAG = 
"tag"
TEXT = 
"text"
CHILD = 
"child"
STYLES = 
"styles"
CONTENT = 
"content"
DUMMY = 
"dummy"
WIDTH = 
"width"
NODE = 
"node"
BEFORE = 
"before"
AFTER = 
"after"
WIDTHEXCEEDED = 
"exceeded"

Instance Method Summary collapse

Constructor Details

#initialize(file, languageArray = ["de","DE","€","EUR"]) ⇒ Rods

Constructor: The given file has to have a *.ods-ending

mySheet=Rods.new("/home/heinz/Work/Template.ods") 
mySheet=Rods.new("/home/heinz/Work/Template.ods",["de,"DE","€","EUR"]) 
mySheet=Rods.new("/home/heinz/Work/Another.ods",["us","US","$","DOLLAR"])

“de”,“DE”,“€”,“EUR” are the default-settings for the language, country, external and internal currency-symbol. All these values merely affect currency-values and annotations (the latter though not visibly).



2374
2375
2376
2377
2378
2379
2380
2381
2382
2383
2384
2385
2386
2387
2388
2389
2390
2391
2392
2393
2394
2395
2396
2397
2398
2399
2400
2401
2402
2403
2404
2405
2406
2407
2408
2409
2410
2411
2412
2413
2414
2415
2416
2417
2418
2419
2420
2421
2422
2423
2424
2425
2426
2427
2428
2429
2430
2431
2432
2433
2434
2435
2436
2437
2438
2439
2440
2441
2442
2443
2444
2445
2446
2447
2448
2449
2450
2451
2452
2453
2454
2455
2456
2457
2458
2459
2460
2461
2462
2463
2464
2465
2466
2467
2468
2469
2470
2471
2472
2473
2474
2475
2476
2477
2478
2479
2480
2481
2482
2483
2484
2485
2486
2487
2488
2489
2490
2491
2492
2493
2494
2495
2496
2497
2498
2499
 
# File 'lib/rods.rb', line 2374

def initialize(file,languageArray=["de","DE","€","EUR"])
  die("Contructor: second parameter is not an array") unless(languageArray.class.to_s == "Array")
  die("Contructor: wrong size of languageArray ... expected 4") unless(languageArray.size == 4)
  languageArray.each{ |element|
    die("Constructor: element #{element} is not a string") unless (element.class.to_s == "String")
  }
  @contentText
  @language=languageArray[0]             
  @country=languageArray[1]             
  @currencySymbol=languageArray[2]     
  @currencySymbolInternal=languageArray[3] 
  @spreadSheet
  @stylesText
  @metaText
  @officeMeta
  @manifestText
  @manifestRoot
  @settingsText
  @officeSettings
  @currentTableName                # Name der aktuellen Tabelle
  @tables=Hash.new()               # Hash der Tabellen und ihrer Eigenschaften
  @numTables                       # Anzahl der Tabellen
  @officeStyles
  @autoStyles
  @floatStyle="myFloat"
  @dateStyle="myDate"  
  @stringStyle="myString"
  @currencyStyle="myCurrency"
  @percentStyle="myPercent"
  @timeStyle="myTime"
  @styleCounter=0
  @myFile # (ggf. qualifizierter) Dateiname der eingelesenen Datei
  #---------------------------------------------------------------
  # Hash-Tabelle der geschriebenen Styles
  #---------------------------------------------------------------
  @styleArchive=Hash.new()
  #---------------------------------------------------------------
  # Farbpalette
  #---------------------------------------------------------------
  @palette={"black" => "#000000",
            "blue" => "#000080",
            "green" => "#008000",
            "turquoise" => "#008080",
            "red" => "#800000",
            "magenta" => "#800080",
            "brown" => "#808000",
            "grey" => "#808080",
            "lightgrey" => "#c0c0c0",
            "lightblue" => "#0000ff",
            "lightgreen" => "#00ff00",
            "lightturquoise" => "#00ffff",
            "lightred" => "#ff0000",
            "lightmagenta" => "#ff00ff",
            "yellow" => "#ffff00",
            "white" => "#ffffff",
            "grey30" => "#b3b3b3",
            "grey20" => "#cccccc",
            "grey10" => "#e6e6e6",
            "red1" => "#ff3366",
            "red2" => "#dc2300",
            "red3" => "#b84700",
            "red4" => "#ff3333",
            "red5" => "#eb613d",
            "red6" => "#b84747",
            "red7" => "#b80047",
            "red8" => "#99284c",
            "magenta1" => "#94006b",
            "magenta2" => "#94476b",
            "magenta3" => "#944794",
            "magenta4" => "#9966cc",
            "magenta5" => "#6b4794",
            "magenta6" => "#6b2394",
            "magenta7" => "#6b0094",
            "magenta8" => "#5e11a6",
            "blue1" => "#280099",
            "blue2" => "#4700b8",
            "blue3" => "#2300dc",
            "blue4" => "#2323dc",
            "blue5" => "#0047ff",
            "blue6" => "#0099ff",
            "blue7" => "#00b8ff",
            "blue8" => "#99ccff",
            "turquoise1" => "#00dcff",
            "turquoise2" => "#00cccc",
            "turquoise3" => "#23b8dc",
            "turquoise4" => "#47b8b8",
            "turquoise5" => "#33a3a3",
            "turquoise6" => "#198a8a",
            "turquoise7" => "#006b6b",
            "turquoise8" => "#004a4a",
            "green1" => "#355e00",
            "green2" => "#5c8526",
            "green3" => "#7da647",
            "green4" => "#94bd5e",
            "green5" => "#00ae00",
            "green6" => "#33cc66",
            "yellow1" => "#e6ff00",
            "yellow2" => "#ffff99",
            "yellow3" => "#ffff66",
            "yellow4" => "#e6e64c",
            "yellow5" => "#cccc00",
            "yellow6" => "#b3b300",
            "yellow7" => "#808019",
            "yellow8" => "#666600",
            "brown1" => "#4c1900",
            "brown2" => "#663300",
            "brown3" => "#804c19",
            "brown4" => "#996633",
            "orange1" => "#cc6633",
            "orange2" => "#ff6633",
            "orange3" => "#ff9966",
            "orange4" => "#ffcc99",
            "purple" => "#9999ff",
            "bordeaux" => "#993366",
            "paleyellow" => "#ffffcc",
            "palegreen" => "#ccffff",
            "darkpurple" => "#660066",
            "salmon" => "#ff8080"
  }
  @fixedStyles=["myTable", "myRow", "myColumn", "myFloatFormat", "myFloat", "myTimeFormat", 
                "myTime", "myPercentFormat", "myPercent", "myString", "myDateFormat", 
                "myDate", "myDateFormatDay", "myDateDay", "myCurrencyFormatPositive", 
                "myCurrencyFormat", "myCurrency", "myCommentParagraph", "myCommentText", 
                "myCommentGraphics"]
  open(file)
end

Instance Method Details

#deleteTable(tableName) ⇒ Object

Deletes the table of the given name and updates the internal table-administration.

mySheet.deleteTable("Tabelle2")



569
570
571
572
573
574
575
576
577
578
579
580
581
582
583
584
585
586
587
588
589
 
# File 'lib/rods.rb', line 569

def deleteTable(tableName)
  die("deleteTable: table '#{tableName}' cannot be deleted as it is the current table !") if (tableName == @currentTableName)
  #----------------------------------------------------
  # Tabellenname gueltig ?
  #----------------------------------------------------
  if(@tables.has_key?(tableName))
    #--------------------------------------------------
    # Loeschung in XML-Tree
    #--------------------------------------------------
    node=@tables[tableName][NODE]
    @spreadSheet.elements.delete(node)
    #--------------------------------------------------
    # Loeschung in Tabellen-Hash
    #--------------------------------------------------
    @tables.delete(tableName)
    @numTables-=1
    tell("deleteTable: deleting table #{tableName}")
  else
    die("deleteTable: invalid table-name/not existing table: '#{tableName}'")
  end
end

#getCell(rowInd, colInd) ⇒ Object

Returns the cell at the given indices. Cell is a REXML::Element. The cell is created if it does not exist.

cell=mySheet.getCell(14,37)



250
251
252
253
 
# File 'lib/rods.rb', line 250

def getCell(rowInd,colInd)
  row=getRow(rowInd)
  return getChildByIndex(row,CELL,colInd)
end

#getCellFromRow(row, colInd) ⇒ Object

Returns the cell at the given index in the given row. Cell and row are REXML::Elements. The cell is created if it does not exist.

row=mySheet.getRow(15)
cell=mySheet.getCellFromRow(row,17) # 17th cell of 15th row

Looks a bit strange compared to

cell=mySheet.getCell(15,17)

but is considerably faster if you are operating on several cells of the same row as after locating the first cell of the row the XML-Parser can start from the node of the already found row instead of having to locate the row over and over again.



241
242
243
 
# File 'lib/rods.rb', line 241

def getCellFromRow(row,colInd)
  return getChildByIndex(row,CELL,colInd)
end

#getNextExistentCell(cell) ⇒ Object

Fast Routine to get the next cell, because XML-Parser does not have to start from top-node of row to find cell ! Returns next cell as a REXML::Element or nil if no element exists Cf. explanation in README !



2537
2538
2539
 
# File 'lib/rods.rb', line 2537

def getNextExistentCell(cell)
  return cell.next_sibling
end

#getNextExistentRow(row) ⇒ Object

Fast Routine to get the next row, because XML-Parser does not have to start from top-node of document to find row ! Returns next row as a REXML::Element or nil if no element exists Cf. explanation in README !



2555
2556
2557
 
# File 'lib/rods.rb', line 2555

def getNextExistentRow(row)
  return row.next_sibling
end

#getPreviousExistentCell(cell) ⇒ Object

Fast Routine to get the previous cell, because XML-Parser does not have to start from top-node of row to find cell ! Returns previous cell as a REXML::Element or nil if no element exists Cf. explanation in README !



2546
2547
2548
 
# File 'lib/rods.rb', line 2546

def getPreviousExistentCell(cell)
  return cell.previous_sibling
end

#getPreviousExistentRow(row) ⇒ Object

Fast Routine to get the previous row, because XML-Parser does not have to start from top-node of document to find row ! Returns previous row as a REXML::Element or nil if no element exists. Cf. explanation in README !



2528
2529
2530
 
# File 'lib/rods.rb', line 2528

def getPreviousExistentRow(row)
  return row.previous_sibling
end

#getRow(rowInd) ⇒ Object

Returns the row at the given index. Row is a REXML::Element. The row is created if it does not exist.

row=getRow(1)
1.upto(500){ |i|
  row=getRow(i) 
  text1,type1=readCellFromRow(row,3)  
  text2,type2=readCellFromRow(row,4) # XML-Parser can start from row-node instead of root-node !
  puts("Read #{text1} of #{type1} and #{text2} of #{type2}
}



266
267
268
269
 
# File 'lib/rods.rb', line 266

def getRow(rowInd)
  currentTable=@tables[@currentTableName][NODE]
  return getChildByIndex(currentTable,ROW,rowInd)
end

#insertTable(tableName) ⇒ Object

Inserts a table of the given name at the end of the spreadsheet and updates the internal table-administration.

mySheet.insertTable("example")



540
541
542
543
544
545
546
547
548
549
550
551
552
553
554
555
556
557
558
559
560
561
562
563
 
# File 'lib/rods.rb', line 540

def insertTable(tableName)
  die("insertTable: table '#{tableName}' already exists") if (@tables.has_key?(tableName))
  #---------------------------------------------------------------------------
  # XML-Tree schreiben
  #---------------------------------------------------------------------------
  newTable=writeXml(@spreadSheet,{TAG => "table:table",
                                  "table:name" => tableName,
                                  "table:print" => "false",
                                  "table:style-name" => "myTable",
                                  "child1" => {TAG => "table:table-column",
                                               "table:style" => "myColumn",
                                               "table:default-cell-style-name" => "Default"},
                                  "child2" => {TAG => "table:table-row",
                                               "table:style-name" => "myRow",
                                               "child3" => {TAG => "table:table-cell"}}})
  #---------------------------------------------------------------------------
  # Tabellen-Hash aktualisieren
  #---------------------------------------------------------------------------
  @tables[tableName]=Hash.new()
  @tables[tableName][NODE]=newTable
  @tables[tableName][WIDTH]=getTableWidth(newTable)
  @tables[tableName][WIDTHEXCEEDED]=false
  @numTables+=1
end

#insertTableAfter(relativeTableName, tableName) ⇒ Object

Inserts a table of the given name after the given spreadsheet and updates the internal table-administration.

mySheet.insertTableAfter("table1","table2")



485
486
487
 
# File 'lib/rods.rb', line 485

def insertTableAfter(relativeTableName,tableName)
  insertTableBeforeAfter(relativeTableName,tableName,AFTER)
end

#insertTableBefore(relativeTableName, tableName) ⇒ Object

Inserts a table of the given name before the given spreadsheet and updates the internal table-administration.

mySheet.insertTableBefore("table2","table1")



477
478
479
 
# File 'lib/rods.rb', line 477

def insertTableBefore(relativeTableName,tableName)
  insertTableBeforeAfter(relativeTableName,tableName,BEFORE)
end

#printAutoStyles ⇒ Object

Helper-Tool: Prints all styles of content.xml in indented ASCII-notation

mySheet.printAutoStyles()

  • Lines starting with ‘E’ are Element-Tags

  • Lines starting with ‘A’ are Attributes

  • Lines starting with ‘T’ are Element-Text

Sample output:

E: number:date-style
  A: style:name => "myDateFormat"
  A: number:automatic-order => "true"
  A: number:format-source => "language"
  E: number:day
  E: number:text
    T: "."
  E: number:month
  E: number:text
    T: "."
  E: number:year



2131
2132
2133
 
# File 'lib/rods.rb', line 2131

def printAutoStyles()
  printStyles(@autoStyles,"  ")
end

#printColorMap ⇒ Object

Helper-function: Print palette of implemented color-mappings

mySheet.printColorMap()

generates ouput like …

"lightturquoise" => "#00ffff",
"lightred" => "#ff0000",
"lightmagenta" => "#ff00ff",
"yellow" => "#ffff00",

you can use for ‘setAttributes’ and ‘writeStyleAbbr’.



2510
2511
2512
2513
2514
2515
2516
2517
2518
2519
2520
2521
 
# File 'lib/rods.rb', line 2510

def printColorMap()
  puts("printColorMap: convenience color-mappings")
  puts("-----------------------------------------")
  @palette.each{ |key,value|
    puts("  #{key} -> #{value}")
  }
  puts("You can use the convenience keys in 'setAttribute' and 'writeStyleAbbr'")
  puts("for the attributes")
  puts("  border,border-bottom, border-top, border-left, border-right")
  puts("  background-color")
  puts("  color")
end

#printOfficeStyles ⇒ Object

Helper-Tool: Prints all styles of styles.xml in indented ASCII-notation

mySheet.printOfficeStyles()

  • Lines starting with ‘E’ are Element-Tags

  • Lines starting with ‘A’ are Attributes

  • Lines starting with ‘T’ are Element-Text

Sample output:

E: style:style
  A: style:name => "myCommentGraphics"
  A: style:family => "graphic"
  E: style:graphic-properties
    A: fo:padding-right => "0.1cm"
    A: draw:marker-start-width => "0.2cm"
    A: draw:auto-grow-width => "false"
    A: draw:marker-start-center => "false"
    A: draw:shadow => "hidden"
    A: draw:shadow-offset-x => "0.1cm"
    A: draw:shadow-offset-y => "0.1cm"
    A: draw:marker-start => "Linienende_20_1"
    A: fo:padding-top => "0.1cm"
    A: draw:fill => "solid"
    A: draw:caption-escape-direction => "auto"
    A: fo:padding-left => "0.1cm"
    A: draw:fill-color => "#ffffcc"
    A: draw:auto-grow-height => "true"
    A: fo:padding-bottom => "0.1cm"



2109
2110
2111
 
# File 'lib/rods.rb', line 2109

def printOfficeStyles()
  printStyles(@officeStyles,"  ")
end

#readCell(rowInd, colInd) ⇒ Object

Returns the content and type of the cell at the given indices as strings. If the cell does not exist, nil is returned for text and type. Type is one of the following office:value-types

  • string, float, currency, time, date, percent, formula

The content of a formula is it’s last calculated result or 0 in case of a newly created cell. See annotations at ‘readCellFromRow’.

1.upto(10){ |i|
   text,type=readCell(i,i)
   writeCell(i,10-i,type,text)
}



848
849
850
851
852
853
854
855
856
857
858
859
860
861
862
863
864
865
866
867
868
869
870
871
872
873
874
875
876
877
878
879
880
881
882
883
884
 
# File 'lib/rods.rb', line 848

def readCell(rowInd,colInd)
  #------------------------------------------------------------------
  # Fuer alle Zeilen
  #------------------------------------------------------------------
  i=0
  j=0
  #------------------------------------------------------------------
  # Zelle mit Indizes suchen
  #------------------------------------------------------------------
  currentTable=@tables[@currentTableName][NODE]
  currentTable.elements.each("table:table-row"){ |row|
    i=i+1  
    j=0
    repetition=row.attributes["table:number-rows-repeated"]
    #-------------------------------------------
    # Zeilenwiederholungen addieren
    #-------------------------------------------
    if(repetition)
      i=i+(repetition.to_i-1)
    end
    #-------------------------------------------
    # Falls Zeilenindex schon uebersprungen
    #-------------------------------------------
    if(i > rowInd)
      return nil, nil
    #-------------------------------------------
    # Falls Zeilenindex erreicht
    #-------------------------------------------
    elsif(i == rowInd)
      return readCellFromRow(row,colInd)
    end
  }
  #--------------------------------------------
  # ausserhalb bisheriger Zeilen
  #--------------------------------------------
  return nil,nil
end

#readCellFromRow(row, colInd) ⇒ Object

Returns the content and type of the cell at the index in the given row as strings. Row is a REXML::Element. If the cell does not exist, nil is returned for text and type. Type is one of the following office:value-types

  • string, float, currency, time, date, percent, formula

The content of a formula is it’s last calculated result or 0 in case of a newly created cell ! The text is internally cleaned from currency-symbols and converted to a valid (English) float representation (but remains a string) in case of type “currency” or “float”.

amount=0.0
5.upto(8){ |i|
  row=mySheet.getRow(i)
  text,type=mySheet.readCellFromRow(row,i)
  mySheet.writeCellFromRow(row,9,type,(-1.0*text.to_f).to_s)
  if(type == "currency")
    amount+=text.to_f
  end
}
puts("Earned #{amount} bucks")



786
787
788
789
790
791
792
793
794
795
796
797
798
799
800
801
802
803
804
805
806
807
808
809
810
811
812
813
814
815
816
817
818
819
820
821
822
823
824
825
826
827
828
829
830
831
832
833
834
 
# File 'lib/rods.rb', line 786

def readCellFromRow(row,colInd)
  j=0
  #------------------------------------------------------------------
  # Fuer alle Spalten
  #------------------------------------------------------------------
  row.elements.each("table:table-cell"){ |cell|
    j=j+1
    #-------------------------------------------
    # Spaltenwiederholungen addieren
    #-------------------------------------------
    repetition=cell.attributes["table:number-columns-repeated"]
    if(repetition)
      j=j+(repetition.to_i-1)
    end
    #-------------------------------------------
    # Falls Spaltenindex schon uebersprungen
    #-------------------------------------------
    if(j > colInd)
      return nil, nil
    #-------------------------------------------
    # Falls Spaltenindex erreicht
    #-------------------------------------------
    elsif(j == colInd)
      #-------------------------------------------
      # Zelltext und Datentyp zurueckgeben
      # ggf. Waehrungssymbol abschneiden
      #-------------------------------------------
      textElement=cell.elements["text:p"]
      if(! textElement)
        return nil,nil
      else
        text=textElement.text
        if(! text)
   text=""
        end
        type=cell.attributes["office:value-type"]
        if(! type)
   type="string"
        end
        text=normalizeText(text,type)
        return text,type
      end
    end
  }
  #----------------------------------------------
  # ausserhalb bisheriger Spalten
  #----------------------------------------------
  return nil,nil
end

#renameTable(oldName, newName) ⇒ Object

Renames the table of the given name and updates the internal table-administration.

mySheet.renameTable("Tabelle1","not needed") # 'Tabelle1' is the default in a German environment



442
443
444
445
446
447
448
449
450
451
452
453
454
455
456
457
458
459
460
461
 
# File 'lib/rods.rb', line 442

def renameTable(oldName,newName)
  die("renameTable: table '#{oldName}' does not exist") unless (@tables.has_key?(oldName))
  # die("renameTable: table '#{oldName}' cannot be renamed as it is the current table !") if (oldName == @currentTableName)
  #------------------------------------------------------
  # XML-Tree anpassen
  #------------------------------------------------------
  node=@tables[oldName][NODE]
  node.attributes["table:name"]=newName
  #------------------------------------------------------
  # Tabellen-Hash anpassen
  #------------------------------------------------------
  @tables[newName]=@tables[oldName]
  @tables.delete(oldName)
  if(oldName == @currentTableName)
    @currentTableName=newName
    tell("renameTable: renaming table (which is current table !) '#{oldName}' to '#{newName}'")
  else
    tell("renameTable: renaming table '#{oldName}' to '#{newName}'")
  end
end

#save ⇒ Object

Saves the file associated with the current RODS-object.

mySheet.save()



2327
2328
2329
2330
2331
2332
2333
2334
 
# File 'lib/rods.rb', line 2327

def save()
  die("save: internal error: @myFile is not set -> cannot save file") unless (@myFile && (! @myFile.empty?))
  die("save: this should not happen: file #{@myFile} is missing") unless (File.exists?(@myFile))
  tell("save: saving as file #{@myFile}")
  Zip::ZipFile.open(@myFile){ |zipfile|
    finalize(zipfile) 
  } 
end

#saveAs(newFile) ⇒ Object

Saves the current content to a new destination/file. Caveat: Thumbnails are not created (these are normally part of the *.ods-zip-file).

mySheet.saveAs("/home/heinz/Work/Example.ods")



2340
2341
2342
2343
2344
2345
2346
2347
2348
2349
2350
2351
2352
2353
2354
2355
2356
2357
2358
2359
2360
2361
2362
 
# File 'lib/rods.rb', line 2340

def saveAs(newFile)
  die("saveAs: file #{newFile} does not have valid ending '*.ods'") unless (newFile.match(/\.ods$/))
  if(File.exists?(newFile))
    tell("saveAs: file #{newFile} exists -> deleting")
    File.delete(newFile)
  end
  #--------------------------------------------------------
  # Datei anlegen
  #--------------------------------------------------------
  tell("saveAs: saving as file #{newFile}")
  Zip::ZipFile.open(newFile,true){ |zipfile|
    ["Configurations2","META-INF","Thumbnails"].each{ |dir|
      zipfile.mkdir(dir)
      zipfile.file.chmod(0755,dir)
    }
    ["accelerator","floater","images","menubar","popupmenu","progressbar","statusbar","toolbar"].each{ |dir|
      subDir="Configurations2/"+dir
      zipfile.mkdir(subDir)
      zipfile.file.chmod(0755,subDir)
    }
    finalize(zipfile) 
  }
end

#setAttributes(cell, attributes) ⇒ Object

Merges style-attributes of given attribute-hash with current style of given cell. Checks, whether the resulting style already exists in the archive of created styles or creates and archives a new style. Applies the found or created style to cell. Cell is a REXML::Element.

mySheet.setAttributes(cell,{ "border-right" => "0.05cm solid magenta4",
                             "border-bottom" => "0.03cm solid lightgreen",
                             "border-top" => "0.08cm solid salmon",
                             "font-style" => "italic",
                             "font-weight" => "bold"})
mySheet.setAttributes(cell,{ "border" => "0.01cm solid turquoise", # turquoise frame
                             "text-align" => "center",             # center alignment
                             "background-color" => "yellow2",      # background-color
                             "color" => "blue"})                   # font-color
1.upto(7){ |row|
  cell=mySheet.getCell(row,5)
  mySheet.setAttributes(cell,{ "border-right" => "0.07cm solid green6" }) 
}



1283
1284
1285
1286
1287
1288
1289
1290
1291
1292
1293
1294
1295
1296
1297
1298
1299
1300
1301
1302
1303
1304
1305
1306
1307
1308
1309
1310
1311
1312
1313
1314
1315
1316
1317
1318
1319
1320
1321
1322
1323
1324
1325
1326
1327
1328
1329
1330
1331
1332
1333
1334
1335
1336
1337
1338
1339
1340
1341
1342
1343
1344
1345
1346
1347
1348
1349
1350
1351
1352
1353
1354
1355
1356
1357
1358
1359
1360
1361
1362
1363
1364
1365
1366
1367
1368
1369
1370
1371
1372
1373
1374
1375
1376
1377
1378
1379
1380
1381
1382
1383
1384
1385
1386
1387
1388
1389
1390
1391
1392
1393
1394
1395
1396
 
# File 'lib/rods.rb', line 1283

def setAttributes(cell,attributes)
  die("setAttributes: cell #{cell} is not a REXML::Element") unless (cell.class.to_s == "REXML::Element")
  die("setAttributes: hash #{attributes} is not a hash") unless (attributes.class.to_s == "Hash")
  #----------------------------------------------------------------------
  # Flag, ob neue Attribute und deren Auspraegungen bereits im aktuellen
  # style vorhanden sind
  #----------------------------------------------------------------------
  containsMatchingAttributes=TRUE
  #-----------------------------------------------------------------------
  # Attribut-Hash, welcher "convenience"-Werte enthalten kann (und wird ;-) 
  # zunaechst normieren
  #-----------------------------------------------------------------------
  attributes=normStyleHash(attributes)
  die("setAttributes: attribute style:name not allowed in attribute-list as automatically generated") if (attributes.has_key?("style:name"))
  #------------------------------------------------------------------
  # Falls Zelle bereits style zugewiesen hat
  #------------------------------------------------------------------
  currentStyleName=cell.attributes["table:style-name"]
  if(currentStyleName)
    #---------------------------------------------------------------
    # style suchen (lassen)
    #---------------------------------------------------------------
    file,currentStyle=getStyle(currentStyleName)
    #-----------------------------------------------------------------------
    # Pruefung, ob oben gefundener style die neuen Attribute und deren Werte
    # bereits enthaelt.
    # Falls auch nur ein Attribut nicht oder nicht mit dem richtigen Wert
    # vorhanden ist, muss ein neuer style erstellt werden.
    # Grundannahme: Ein Open-Document-Style-Attribut kann per se immer nur in einem bestimmten Typ
    # Knoten vorkommen und muss daher nicht naeher qualifiziert werden !
    #-----------------------------------------------------------------------
    attributes.each{ |attribute,value|
      currentValue=currentStyle.attributes[attribute]
      #-------------------------------------------------
      # Attribut in Context-Node nicht gefunden ?
      #-------------------------------------------------
      if(! currentValue)  # nilClass
        tell("setAttributes: #{currentStyleName}: #{attribute} not in Top-Node")
        #-----------------------------------------------------------
        # Attribut mit passendem Wert dann in Kind-Element vorhanden ?
        #-----------------------------------------------------------
        if(currentStyle.elements["*[@#{attribute} = '#{value}']"])
          tell("setAttributes: #{currentStyleName}: #{attribute}/#{value} matching in Sub-Node")
        #-----------------------------------------------------------
        # andernfalls Komplettabbruch der Pruefschleife aller Attribute und Flag setzen
        # => neuer style muss erzeugt werden
        #-----------------------------------------------------------
        else
          tell("setAttributes: #{currentStyleName}: #{attribute}/#{value} not matching in Sub-Node")
          containsMatchingAttributes=FALSE
          break
        end
      #--------------------------------------------------
      # Attribut in Context-Node gefunden
      #--------------------------------------------------
      else
        #--------------------------------------------------
        # Passt der Wert des gefundenen Attributes bereits ?
        #--------------------------------------------------
        if (currentValue == value)
          tell("setAttributes: #{currentStyleName}: #{attribute}/#{value} matching in Top-Node")
        #-------------------------------------------------
        # bei unpassendem Wert Flag setzen
        #-------------------------------------------------
        else
          tell("setAttributes: #{currentStyleName}: #{attribute}/#{value} not matching with #{currentValue} in Top-Node")
          containsMatchingAttributes=FALSE
        end
      end
    }
    #--------------------------------------------------------
    # Wurden alle Attribut-Wertepaare gefunden, d.h. kann 
    # bisheriger style weiterverwendet werden ?
    #--------------------------------------------------------
    if(containsMatchingAttributes)
      tell("setAttributes: #{currentStyleName}: all attributes/values matching -> keeping current style")
    #-------------------------------------------------------
    # nein => passenden Style in Archiv suchen oder klonen und anpassen
    #-------------------------------------------------------
    else
      getAppropriateStyle(cell,currentStyle,attributes)
    end
  #------------------------------------------------------------------------
  # Zelle hatte noch gar keinen style zugewiesen
  #------------------------------------------------------------------------
  else
    #----------------------------------------------------------------------
    # Da style fehlt, ggf. aus office:value-type bestmoeglichen style ermitteln
    #----------------------------------------------------------------------
    valueType=cell.attributes["office:value-type"]
    if(valueType)
      case valueType
        when "string" then currentStyleName="myString"
        when "percentage" then currentStyleName="myPercentage"
        when "currency" then currentStyleName="myCurrency"
        when "float" then currentStyleName="myFloat"
        when "date" then currentStyleName="myDate"
        when "time" then currentStyleName="myTime"
      else
        die("setAttributes: unknown office:value-type #{valueType} found in #{cell}")
      end
    else
      #-----------------------------------------
      # 'myString' als Default
      #-----------------------------------------
      currentStyleName="myString" 
    end
    #-------------------------------------------------------
    # passenden Style in Archiv suchen oder klonen und anpassen
    #-------------------------------------------------------
    file,currentStyle=getStyle(currentStyleName)
    getAppropriateStyle(cell,currentStyle,attributes)
  end
end

#setCurrentTable(tableName) ⇒ Object

Sets the table of the given name as the default-table for all subsequent operations.

mySheet.setCurrentTable("example")



467
468
469
470
471
 
# File 'lib/rods.rb', line 467

def setCurrentTable(tableName)
  die("setCurrentTable: table '#{tableName}' does not exist") unless (@tables.has_key?(tableName))
  @currentTableName=tableName
  tell("setCurrentTable: setting #{tableName} as current table")
end

#setDateFormat(formatName) ⇒ Object

Convenience-function to switch the default-style for the display of date-values. The switch is valid for all subsequently created cells with date-values.

Builtin valid values are

  • ‘myDate’

    • -> “02.01.2011” (German formatting)

  • ‘myDateDay’

    • -> “Su”

Example

mySheet.setDateFormat("myDateDay")  # RODS' default format for display of weekday
mySheet.setDateFormat("myDate")     # RODS' default format for date ("12.01.2011" German format)



92
93
94
95
96
97
98
 
# File 'lib/rods.rb', line 92

def setDateFormat(formatName)
  case formatName
    when "myDate" then @dateStyle="myDate"
    when "myDateDay" then @dateStyle="myDateDay"
    else die("setDateFormat: invalid format-name #{format}")
  end
end

#setStyle(cell, styleName) ⇒ Object

Applies style of given name to given cell and overwrites all previous style-settings of the latter including the former data-style !

mySheet.writeStyleAbbr({"name" => "myStrange",
                        "text-align" => "right",
                        "data-style-name" => "myCurrencyFormat" <- don't forget data-style !
                        "border-left" => "0.01cm solid grey4"})
mySheet.setStyle(cell,"myStrange") # <- style-name has to exist !



2242
2243
2244
2245
2246
2247
2248
 
# File 'lib/rods.rb', line 2242

def setStyle(cell,styleName)
  #-----------------------------------------------------------------------
  # Ist Style gueltig, d.h. in content.xml vorhanden ?
  #-----------------------------------------------------------------------
  die("setStyle: style \'#{styleName}\' does not exist") unless (@autoStyles.elements["*[@style:name = '#{styleName}']"])
  cell.attributes['table:style-name']=styleName
end

#writeCell(rowInd, colInd, type, text) ⇒ Object

Writes the given text to the cell with the given indices. Creates the cell if not existing. Formats the cell according to type.

mySheet.writeCell(1,1,"date","31.12.2010") # 1st row, 1st column
mySheet.writeCell(2,1,"formula:date","=A1+1") 
mySheet.writeCell(1,3,"time","13:37") # German time-format
mySheet.writeCell(1,4,"currency","19,99") # you could also use '.' as a decimal separator



198
199
200
201
 
# File 'lib/rods.rb', line 198

def writeCell(rowInd,colInd,type,text)
  cell=getCell(rowInd,colInd)
  writeText(cell,type,text)
end

#writeCellFromRow(row, colInd, type, text) ⇒ Object

Writes the given text to the cell with the given index in the given row. Row is a REXML::Element. Creates the cell if it does not exist. Formats the cell according to type.

row=mySheet.getRow(3)
mySheet.writeCellFromRow(row,1,"date","28.12.2010")
mySheet.writeCellFromRow(row,2,"formula:date","=A1+3")



224
225
226
227
 
# File 'lib/rods.rb', line 224

def writeCellFromRow(row,colInd,type,text)
  cell=getCellFromRow(row,colInd)
  writeText(cell,type,text)
end

#writeComment(cell, comment) ⇒ Object

Inserts an annotation field for the given cell. Caveat: When you make the annotation permanently visible in a subsequent OpenOffice.org-session, the annotation will always be displayed in the upper left corner of the sheet. The temporary display of the annotation is not affected however.

mySheet.writeComment(cell,"by Dr. Heinz Breinlinger (who else)")



2257
2258
2259
2260
2261
2262
2263
2264
2265
2266
2267
2268
2269
2270
2271
2272
2273
2274
2275
2276
2277
2278
2279
2280
2281
 
# File 'lib/rods.rb', line 2257

def writeComment(cell,comment)
  die("writeComment: cell #{cell} is not a REXML::Element") unless (cell.class.to_s == "REXML::Element")
  die("writeComment: comment #{comment} is not a string") unless (comment.class.to_s == "String")
  #--------------------------------------------
  # Ggf. alten Kommentar loeschen
  #--------------------------------------------
  cell.elements.delete("office:annotation")
  writeXml(cell,{TAG => "office:annotation",
                 "svg:x" => "4.119cm",
                 "draw:caption-point-x" => "-0.61cm",
                 "svg:y" => "0cm",
                 "draw:caption-point-y" => "0.011cm",
                 "draw:text-style-name" => "myCommentParagraph",
                 "svg:height" => "0.596cm",
                 "draw:style-name" => "myCommentGraphics",
                 "svg:width" => "2.899cm",
                 "child1" => {TAG => "dc:date",
                              TEXT => "2010-01-01T00:00:00"
                             },                    
                 "child2" => {TAG => "text:p",
                              "text:style-name" => "myCommentParagraph",
                              TEXT => comment
                             }
                })
end

#writeGetCell(rowInd, colInd, type, text) ⇒ Object

Writes the given text to the cell with the given indices. Creates the cell if not existing. Formats the cell according to type and returns the cell.

cell=mySheet.writeGetCell(3,3,"formula:time","=C2-C1")

This is useful for a subsequent call to 

mySheet.setAttributes(cell,{ "background-color" => "yellow3"})



184
185
186
187
188
 
# File 'lib/rods.rb', line 184

def writeGetCell(rowInd,colInd,type,text)
  cell=getCell(rowInd,colInd)
  writeText(cell,type,text)
  return cell
end

#writeGetCellFromRow(row, colInd, type, text) ⇒ Object

Writes the given text to the cell with the given index in the given row. Row is a REXML::Element. Creates the cell if not existing. Formats the cell according to type and returns the cell.

row=mySheet.getRow(17)
cell=mySheet.writeGetCellFromRow(row,4,"formula:currency","=B5*1,19")



210
211
212
213
214
 
# File 'lib/rods.rb', line 210

def writeGetCellFromRow(row,colInd,type,text)
  cell=getCellFromRow(row,colInd)
  writeText(cell,type,text)
  return cell
end

#writeStyleAbbr(attributes) ⇒ Object

Creates a new style out of the given attribute-hash with abbreviated and simplified syntax.

mySheet.writeStyleAbbr({"name" => "myNewPercentStyle",        # <- style-name to be applied to a cell
                        "margin-left" => "0.3cm",
                        "text-align" => "start",
                        "color" => "blue",
                        "border" => "0.01cm solid black",
                        "font-style" => "italic",
                        "data-style-name" => "myPercentFormat", # <- predefined RODS data-style
                        "font-weight" => "bold"})



1615
1616
1617
 
# File 'lib/rods.rb', line 1615

def writeStyleAbbr(attributes)
  writeStyle(normStyleHash(attributes))
end

#writeText(cell, type, text) ⇒ Object

Writes the given text-string to given cell and sets style of cell to corresponding type. Keep in mind: All values of tables are passed and retrieved as strings !

mySheet.writeText(getCell(17,39),"currency","14,37")

The example can of course be simplified by

mySheet.writeCell(17,39,"currency","14,37")



1046
1047
1048
1049
1050
1051
1052
1053
1054
1055
1056
1057
1058
1059
1060
1061
1062
1063
1064
1065
1066
1067
1068
1069
1070
1071
1072
1073
1074
1075
1076
1077
1078
1079
1080
1081
1082
1083
1084
1085
1086
1087
1088
1089
1090
1091
1092
1093
1094
1095
1096
1097
1098
1099
1100
1101
1102
1103
1104
1105
1106
1107
1108
1109
1110
1111
1112
1113
1114
1115
1116
1117
1118
1119
1120
1121
1122
1123
1124
1125
1126
1127
1128
1129
1130
1131
1132
1133
1134
1135
1136
1137
1138
1139
1140
1141
1142
1143
1144
1145
1146
1147
1148
1149
1150
1151
1152
1153
1154
1155
1156
1157
1158
1159
1160
1161
1162
1163
1164
1165
1166
1167
1168
1169
1170
1171
1172
1173
 
# File 'lib/rods.rb', line 1046

def writeText(cell,type,text)
  #------------------------------------------
  # Zunaechst ggf. stoerende Attribute löschen
  #------------------------------------------
  cell.attributes.each{ |attribute,value|
    cell.attributes.delete(attribute)
  }
  #-------------------------------------------
  # Typabhaengig diverse Attribute der Zelle setzen
  #-------------------------------------------
  # String
  #-------------------------------------------
  if(type == "string")
    cell.attributes["office:value-type"]="string"
    cell.attributes["table:style-name"]=@stringStyle
  #-------------------------------------------
  # Float
  #-------------------------------------------
  elsif(type == "float")
    cell.attributes["office:value-type"]="float"
    #-----------------------------------------------------
    # Dezimaltrenner von "," in "." aendern 
    #-----------------------------------------------------
    internalText=text.sub(/,/,".")   
    cell.attributes["office:value"]=internalText
    cell.attributes["table:style-name"]=@floatStyle
  #-------------------------------------------
  # Formula
  # Cave: Zahlformat 1,25 muss geaendert werden in 1.25
  #   In der reinen Textdarstellung der Zellenformel verwendet
  #   OpenOffice das laenderspezifische Trennzeichen; im Attributwert
  #   der Formel muss jedoch das englische Format mit '.' stehen !
  #   Waehrend dies bei interaktiver Eingabe der Formel transparent
  #   gewandelt (jedoch stets mit laenderspezifischem Trennzeichen angezeigt) wird,
  #   muss hier explizit "Hand angelegt" werden. Der Unterschied ist dann lediglich
  #   in der XML-Darstellung (des Attributwertes) zu sehen, NICHT in der interaktiven
  #   Anzeige unter OpenOffice.
  #   Als Fuellwert wird stehts "0" gesetzt; beim Oeffnen der Datei mit OpenOffice
  #   wird dann der richtige Wert errechnet und geschrieben.
  #-------------------------------------------
  elsif(type.match(/^formula/))
    #---------------------------------------------
    # Formel fuer interne Darstellung aufbereiten
    #---------------------------------------------
    cell.attributes["table:formula"]=internalizeFormula(text) 
    #---------------------------------------------
    # Zellformatierung bestimmen
    #---------------------------------------------
    case type
      when "formula","formula:float"
        cell.attributes["office:value-type"]="float"
        cell.attributes["office:value"]=0
        cell.attributes["table:style-name"]=@floatStyle
      when "formula:time"
        cell.attributes["office:value-type"]="time"
        cell.attributes["office:time-value"]="PT00H00M00S"
        cell.attributes["table:style-name"]=@timeStyle
        # cell.attributes["table:style-name"]=""
      when "formula:date"
        cell.attributes["office:value-type"]="date"
        cell.attributes["office:date-value"]="0"
        cell.attributes["table:style-name"]=@dateStyle
      when "formula:currency"
        cell.attributes["office:value-type"]="currency"
        #-----------------------------------------------------
        # Dezimaltrenner von "," in "." aendern
        #-----------------------------------------------------
        internalText="0.0"   
        cell.attributes["office:value"]=internalText
        cell.attributes["office:currency"]=@currencySymbolInternal
        cell.attributes["table:style-name"]=@currencyStyle
      else die("writeText: invalid type of formula #{type}")
    end
    text="0"
  #-------------------------------------------
  # Percent
  #-------------------------------------------
  elsif(type == "percent")
    cell.attributes["office:value-type"]="percentage"
    cell.attributes["office:value"]=percent2PercentVal(text)
    cell.attributes["table:style-name"]=@percentStyle
    text=text+" %"
  #-------------------------------------------
  # Currency
  #-------------------------------------------
  elsif(type == "currency")
    cell.attributes["office:value-type"]="currency"
    #-----------------------------------------------------
    # Dezimaltrenner von "," in "." aendern und
    # Waehrungs-Symbol hintanstellen
    #-----------------------------------------------------
    internalText=text.sub(/,/,".")   
    text=text+" "+@currencySymbol
    cell.attributes["office:value"]=internalText
    cell.attributes["office:currency"]=@currencySymbolInternal
    cell.attributes["table:style-name"]=@currencyStyle
  #-------------------------------------------
  # Date
  #-------------------------------------------
  elsif(type == "date")
    cell.attributes["office:value-type"]="date"
    cell.attributes["table:style-name"]=@dateStyle
    cell.attributes["office:date-value"]=date2DateVal(text)
  #-------------------------------------------
  # Time (im Format 13:37)
  #-------------------------------------------
  elsif(type == "time")
    cell.attributes["office:value-type"]="time"
    cell.attributes["table:style-name"]=@timeStyle
    cell.attributes["office:time-value"]=time2TimeVal(text)
  else
    puts("Wrong type #{type}: Doing nothing")
  end
  #-------------------------------------------
  # Text setzen
  #-------------------------------------------
  # Textelement bereits vorhanden ?
  #-------------------------------------------
  if(cell.elements["text:p"])
    cell.elements["text:p"].text=text
  #-------------------------------------------
  # nicht vorhanden (Leerzelle) -> neu anlegen
  #-------------------------------------------
  else
    newElement=cell.add_element("text:p")
    newElement.text=text
  end
end