Class: Rods

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

Constant Summary collapse

ROW = 
"row"
CELL = 
"cell"
COLUMN = 
"column"
TAG = 
"tag"
TEXT = 
"text"
CHILD = 
"child"
STYLES = 
"styles"
CONTENT = 
"content"
DUMMY = 
"dummy"
WIDTH = 
"width"
NODE = 
"node"
BEFORE = 
"before"
AFTER = 
"after"
INDEX = 
"index"
NUMBER = 
"number"
BOTH = 
"both"
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).



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
2500
2501
2502
2503
2504
2505
2506
2507
2508
2509
 
# File 'lib/rods.rb', line 2384

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

#deleteCell(rowInd, colInd) ⇒ Object

Delets the cell at the given indices

mySheet.deleteCell(7,9)



2917
2918
2919
2920
2921
2922
2923
2924
2925
 
# File 'lib/rods.rb', line 2917

def deleteCell(rowInd,colInd)
  die("deleteCell: index #{rowInd} is not a Fixnum/Integer") unless (rowInd.class.to_s == "Fixnum")
  die("deleteCell: invalid index #{rowInd}") unless (rowInd > 0)
  die("deleteCell: index #{colInd} is not a Fixnum/Integer") unless (colInd.class.to_s == "Fixnum")
  die("deleteCell: invalid index #{colInd}") unless (colInd > 0)
  tell("deleteCell: deleting cell at #{rowInd}:#{colInd}")
  row=getRow(rowInd)
  deleteCellFromRow(row,colInd)
end

#deleteCell2(cell) ⇒ Object

Delets the given cell.

‘cell’ is a REXML::Element as returned by getCell(cellInd).

startCell=mySheet.getCell(34,1) while(cell=mySheet.getNextExistentCell(startCell))

mySheet.deleteCell2(cell)

end



2855
2856
2857
2858
2859
2860
2861
2862
2863
2864
2865
2866
2867
2868
2869
2870
2871
2872
 
# File 'lib/rods.rb', line 2855

def deleteCell2(cell)
  die("deleteCell2: cell #{cell} is not a REXML::Element") unless (cell.class.to_s == "REXML::Element")
  #-------------------------------------------------------------------
  # Entweder Wiederholungszahl dekrementieren oder Zelle loeschen
  #-------------------------------------------------------------------
  repetitions=cell.attributes["table:number-columuns-repeated"]
  if(repetitions && repetitions.to_i > 1)
    cell.attributes["table:number-columns-repeated"]=(repetitions.to_i-1).to_s
    tell("deleteCell2: decrementing empty cells")
  else
    row=cell.elements["ancestor::table:table-row"]
    unless (row)
      die("deleteCell2: internal error: Could not extract parent-row of cell #{cell}") 
    end
    row.elements.delete(cell)
    tell("deleteCell2: deleting non-empty cell")
  end
end

#deleteCellAfter(cell) ⇒ Object

Delets the cell to the right of the given cell

cell=mySheet.writeGetCell(4,7,"date","16.01.2011")
mySheet.deleteCellAfter(cell)



2776
2777
2778
2779
2780
2781
2782
2783
2784
2785
2786
2787
2788
2789
2790
2791
2792
2793
2794
2795
2796
2797
2798
2799
2800
 
# File 'lib/rods.rb', line 2776

def deleteCellAfter(cell)
  die("deleteCellAfter: cell #{cell} is not a REXML::Element") unless (cell.class.to_s == "REXML::Element")
  #--------------------------------------------------------
  # Entweder Wiederholungsattribut der aktuellen Zelle
  # dekrementieren oder ggf. Wiederholungsattribut der
  # Folgezelle dekrementieren oder selbige loeschen
  #--------------------------------------------------------
  repetitions=cell.attributes["table:number-columns-repeated"]
  if(repetitions && repetitions.to_i > 1)
    cell.attributes["table:number-columns-repeated"]=(repetitions.to_i-1).to_s
  else
    nextCell=cell.next_sibling
    die("deleteCellAfter: cell is already last cell in row") unless (nextCell)
    nextRepetitions=nextCell.attributes["table:number-columns-repeated"]
    if(nextRepetitions && nextRepetitions.to_i > 1)
      nextCell.attributes["table:number-columns-repeated"]=(nextRepetitions.to_i-1).to_s
    else
      row=cell.elements["ancestor::table:table-row"]
      unless (row)
        die("deleteCellAfter: internal error: Could not extract parent-row of cell #{cell}") 
      end
      row.elements.delete(nextCell)
    end
  end
end

#deleteCellBefore(cell) ⇒ Object

Delets the cell to the left of the given cell

cell=mySheet.writeGetCell(4,7,"formula:currency","=A1+B2")
mySheet.deleteCellBefore(cell)



2957
2958
2959
2960
2961
2962
2963
2964
2965
2966
2967
2968
2969
2970
2971
2972
2973
2974
2975
 
# File 'lib/rods.rb', line 2957

def deleteCellBefore(cell)
  die("deleteCellBefore: cell #{cell} is not a REXML::Element") unless (cell.class.to_s == "REXML::Element")
  #--------------------------------------------------------
  # Entweder Wiederholungsattribut der vorherigen Zelle
  # dekrementieren oder selbige loeschen
  #--------------------------------------------------------
  previousCell=cell.previous_sibling
  die("deleteCellBefore: cell is already first cell in row") unless (previousCell)
  previousRepetitions=previousCell.attributes["table:number-columns-repeated"]
  if(previousRepetitions && previousRepetitions.to_i > 1)
    previousCell.attributes["table:number-columns-repeated"]=(previousRepetitions.to_i-1).to_s
  else
    row=cell.elements["ancestor::table:table-row"]
    unless (row)
      die("deleteCellBefore: internal error: Could not extract parent-row of cell #{cell}") 
    end
    row.elements.delete(previousCell)
  end
end

#deleteCellFromRow(row, colInd) ⇒ Object

Delets the cell at the given index in the given row

row=mySheet.getRow(8)
mySheet.deleteCell(row,9)



2838
2839
2840
2841
2842
2843
2844
 
# File 'lib/rods.rb', line 2838

def deleteCellFromRow(row,colInd)
  die("deleteCell: row #{row} is not a REXML::Element") unless (row.class.to_s == "REXML::Element")
  die("deleteCell: index #{colInd} is not a Fixnum/Integer") unless (colInd.class.to_s == "Fixnum")
  die("deleteCell: invalid index #{colInd}") unless (colInd > 0)
  cell=getCellFromRow(row,colInd+1)
  deleteCellBefore(cell)
end

#deleteColumn(colInd) ⇒ Object

Deletes the column at the given index

mySheet.deleteColumn(8)



3097
3098
3099
3100
3101
3102
3103
3104
3105
3106
3107
3108
3109
3110
3111
3112
3113
3114
3115
3116
3117
3118
3119
3120
3121
3122
3123
3124
3125
3126
3127
3128
3129
3130
 
# File 'lib/rods.rb', line 3097

def deleteColumn(colInd)
  die("deleteColumn: index #{colInd} is not a Fixnum/Integer") unless (colInd.class.to_s == "Fixnum")
  die("deleteColumn: invalid index #{colInd}") unless (colInd > 0)
  currentWidth=@tables[@currentTableName][WIDTH]
  die("deleteColumn: column-index #{colInd} is outside valid range/current table width") if (colInd > currentWidth)
  #-------------------------------------------------------------------
  # Entweder Wiederholungsattribut der fraglichen Spalte dekrementieren
  # oder selbige loeschen
  #-------------------------------------------------------------------
  currentTable=@tables[@currentTableName][NODE]
  column=getChildByIndex(currentTable,COLUMN,colInd)
  repetitions=column.attributes["table:number-columns-repeated"]
  if(repetitions && repetitions.to_i > 1)
    column.attributes["table:number-columns-repeated"]=(repetitions.to_i-1).to_s
  else
    table=column.elements["ancestor::table:table"]
    unless (table)
      die("deleteColumn: internal error: Could not extract parent-table of column #{column}") 
    end
    table.elements.delete(column)
  end
  #-----------------------------------------------
  # Fuer alle existierenden Zeilen neue Zelle an
  # Spaltenposition einfuegen und dabei implizit
  # Tabellenbreite aktualisieren
  #-----------------------------------------------
  row=getRow(1)
  deleteCellFromRow(row,colInd)
  i=1
  while(row=getNextExistentRow(row)) # fuer alle Zeilen ab der zweiten
    deleteCellFromRow(row,colInd)
    i+=1
  end 
end

#deleteRow(rowInd) ⇒ Object

Delets the row at the given index

mySheet.deleteRow(7)



2906
2907
2908
2909
2910
2911
 
# File 'lib/rods.rb', line 2906

def deleteRow(rowInd)
  die("deleteRow: index #{rowInd} is not a Fixnum/Integer") unless (rowInd.class.to_s == "Fixnum")
  die("deleteRow: invalid index #{rowInd}") unless (rowInd > 0)
  row=getRow(rowInd+1)
  deleteRowAbove(row)
end

#deleteRow2(row) ⇒ Object

Delets the given row.

‘row’ is a REXML::Element as returned by getRow(rowInd).

startRow=mySheet.getRow(12) while(row=mySheet.getNextExistentRow(startRow))

mySheet.deleteRow2(row)

end



2883
2884
2885
2886
2887
2888
2889
2890
2891
2892
2893
2894
2895
2896
2897
2898
2899
2900
 
# File 'lib/rods.rb', line 2883

def deleteRow2(row)
  die("deleteRow2: row #{row} is not a REXML::Element") unless (row.class.to_s == "REXML::Element")
  #-------------------------------------------------------------------
  # Entweder Wiederholungszahl dekrementieren oder Zeile loeschen
  #-------------------------------------------------------------------
  repetitions=row.attributes["table:number-rows-repeated"]
  if(repetitions && repetitions.to_i > 1)
    row.attributes["table:number-rows-repeated"]=(repetitions.to_i-1).to_s
    tell("deleteRow2: decrementing empty rows")
  else
    table=row.elements["ancestor::table:table"]
    unless (table)
      die("deleteRow2: internal error: Could not extract parent-table of row #{row}") 
    end
    table.elements.delete(row)
    tell("deleteRow2: deleting non-empty row")
  end
end

#deleteRowAbove(row) ⇒ Object

Delets the row above the given row

row=mySheet.getRow(5)
mySheet.deleteRowAbove(row)



2932
2933
2934
2935
2936
2937
2938
2939
2940
2941
2942
2943
2944
2945
2946
2947
2948
2949
2950
 
# File 'lib/rods.rb', line 2932

def deleteRowAbove(row)
  die("deleteRowAbove: row #{row} is not a REXML::Element") unless (row.class.to_s == "REXML::Element")
  #--------------------------------------------------------
  # Entweder Wiederholungsattribut der vorherigen Zeile
  # dekrementieren oder selbige loeschen
  #--------------------------------------------------------
  previousRow=row.previous_sibling
  die("deleteRowAbove: row is already first row in row") unless (previousRow)
  previousRepetitions=previousRow.attributes["table:number-rows-repeated"]
  if(previousRepetitions && previousRepetitions.to_i > 1)
    previousRow.attributes["table:number-rows-repeated"]=(previousRepetitions.to_i-1).to_s
  else
    table=row.elements["ancestor::table:table"]
    unless (table)
      die("deleteRowAbove: internal error: Could not extract parent-table of row #{row}") 
    end
    table.elements.delete(previousRow)
  end
end

#deleteRowBelow(row) ⇒ Object

Delets the row below the given row

row=mySheet.getRow(11)
mySheet.deleteRowBelow(row)



2807
2808
2809
2810
2811
2812
2813
2814
2815
2816
2817
2818
2819
2820
2821
2822
2823
2824
2825
2826
2827
2828
2829
2830
2831
 
# File 'lib/rods.rb', line 2807

def deleteRowBelow(row)
  die("deleteRowBelow: row #{row} is not a REXML::Element") unless (row.class.to_s == "REXML::Element")
  #--------------------------------------------------------
  # Entweder Wiederholungsattribut der aktuellen Zeile
  # dekrementieren oder ggf. Wiederholungsattribut der
  # Folgezeile dekrementieren oder selbige loeschen
  #--------------------------------------------------------
  repetitions=row.attributes["table:number-rows-repeated"]
  if(repetitions && repetitions.to_i > 1)
    row.attributes["table:number-rows-repeated"]=(repetitions.to_i-1).to_s
  else
    nextRow=row.next_sibling
    die("deleteRowBelow: row #{row} is already last row in table") unless (nextRow)
    nextRepetitions=nextRow.attributes["table:number-rows-repeated"]
    if(nextRepetitions && nextRepetitions.to_i > 1)
      nextRow.attributes["table:number-rows-repeated"]=(nextRepetitions.to_i-1).to_s
    else
      table=row.elements["ancestor::table:table"]
      unless (table)
        die("deleteRowBelow: internal error: Could not extract parent-table of row #{row}") 
      end
      table.elements.delete(nextRow)
    end
  end
end

#deleteTable(tableName) ⇒ Object

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

mySheet.deleteTable("Tabelle2")



565
566
567
568
569
570
571
572
573
574
575
576
577
578
579
580
581
582
583
584
585
 
# File 'lib/rods.rb', line 565

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)



237
238
239
240
 
# File 'lib/rods.rb', line 237

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.



228
229
230
 
# File 'lib/rods.rb', line 228

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

#getCellsAndIndicesFor(content) ⇒ Object

Finds all cells with content ‘content’ and returns them along with the indices of row and column as an array of hashes.

[{:cell => cell,
  :row  => rowIndex,
  :col  => colIndex},
 {:cell => cell,
  :row  => rowIndex,
  :col  => colIndex}]

Regular expressions for ‘content’ are allowed but must be enclosed in single (not double) quotes !

In case of no matches at all, an empty array is returned.

The following finds all occurences of a comma- or dot-separated number, consisting of 1 digit before and 2 digits behind the decimal-separator.

myArray=mySheet.getCellsAndIndicesFor(‘d1d2’)

Keep in mind that the content of a call with a formula is not the formula, but the current value of the computed result.

Also consider that you have to search for the external (i.e. visible) represenation of a cell’s content, not it’s internal computational value. For instance, when looking for a currency value of 1525 (that is shown as ‘1.525 EUR’), you’ll have to code

result=mySheet.getCellsAndIndicesFor('1[.,]525')
result.each{ |cellHash|
  puts("Found #{cellHash[:cell] on #{cellHash[:row] - #{cellHash[:col]")
}



2611
2612
2613
2614
2615
2616
2617
2618
2619
2620
2621
2622
2623
2624
2625
2626
2627
2628
2629
2630
2631
2632
2633
2634
2635
2636
2637
2638
2639
2640
2641
2642
2643
2644
2645
2646
2647
2648
2649
2650
2651
2652
2653
2654
 
# File 'lib/rods.rb', line 2611

def getCellsAndIndicesFor(content)
  die("getCellsAndIndicesFor: 'content' is not of typ String") unless (content.class.to_s == "String")
  result=Array.new()
  i=0
  tell("getCellsAndIndicesFor: Searching for cells with content '#{content}'")
  #----------------------------------------------------------------
  # Alle Text-Nodes suchen
  #----------------------------------------------------------------
  @spreadSheet.elements.each("//table:table-cell/text:p"){ |textNode|
    text=textNode.text
    #---------------------------------------------------------
    # Zelle gefunden ?
    #
    # 'content' darf regulaerer Ausdruck sein, muss dann jedoch
    # in einfachen Hochkommata uebergeben werden
    #---------------------------------------------------------
    if(text && (text.match(/#{content}/)))
      result[i]=Hash.new() 
      tell("getCellsAndIndicesFor: '#{content}' matched '#{text}'")
      #-----------------------------------------------------
      # Zelle und Zellenindex ermitteln
      #-----------------------------------------------------
      cell=textNode.elements["ancestor::table:table-cell"]
      unless (cell)
        die("getCellsAndIndicesFor: internal error: Could not extract parent-cell of textNode with #{content}") 
      end
      colIndex=getIndex(cell)
      #-----------------------------------------------------
      # Zeile und Zeilenindex ermitteln
      #-----------------------------------------------------
      row=textNode.elements["ancestor::table:table-row"]
      unless (row)
        die("getCellsAndIndicesFor: internal error: Could not extract parent-row of textNode with #{content}") 
      end
      rowIndex=getIndex(row)
      result[i][:cell]=cell
      result[i][:row]=rowIndex
      result[i][:col]=colIndex
      tell("getCellsAndIndicesFor: Indices #{rowIndex} #{colIndex}")
      i+=1
    end
  }
  return result
end

#getIndexAndNumber(node) ⇒ Object

internal: Wrapper for getIndexAndOrNumber(node,BOTH)



2673
2674
2675
 
# File 'lib/rods.rb', line 2673

def getIndexAndNumber(node)
  return getIndexAndOrNumber(node,BOTH)
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 !



2557
2558
2559
 
# File 'lib/rods.rb', line 2557

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 !



2575
2576
2577
 
# File 'lib/rods.rb', line 2575

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 !



2566
2567
2568
 
# File 'lib/rods.rb', line 2566

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 !



2539
2540
2541
2542
2543
2544
2545
2546
2547
2548
2549
2550
 
# File 'lib/rods.rb', line 2539

def getPreviousExistentRow(row)
  #----------------------------------------------------------------------
  # Cave: table:table-row und table:table-column sind Siblings !!!!
  # Letztere duerfen jedoch NICHT zurueckgegeben werden
  #----------------------------------------------------------------------
  previousSibling=row.previous_sibling
  if(previousSibling && previousSibling.elements["self::table:table-row"])
    return previousSibling
  else
    return nil
  end
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}
}



253
254
255
256
 
# File 'lib/rods.rb', line 253

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

#insertCell(rowInd, colInd) ⇒ Object

Inserts and returns a cell at the given index, thereby shifting existing cells.

cell=mySheet.insertCell(4,17)



3044
3045
3046
3047
3048
3049
3050
3051
3052
 
# File 'lib/rods.rb', line 3044

def insertCell(rowInd,colInd)
  die("insertCell: index #{rowInd} is not a Fixnum/Integer") unless (rowInd.class.to_s == "Fixnum")
  die("insertCell: invalid index #{rowInd}") unless (rowInd > 0)
  die("insertCell: index #{colInd} is not a Fixnum/Integer") unless (colInd.class.to_s == "Fixnum")
  die("insertCell: invalid index #{colInd}") unless (colInd > 0)
  tell("insertCell: inserting new cell at #{rowInd}:#{colInd}")
  cell=getCell(rowInd,colInd)
  return insertCellBefore(cell)
end

#insertCellAfter(cell) ⇒ Object

Inserts a new cell after the given cell thereby shifting existing cells

cell=mySheet.getCell(4,7)
mySheet.insertCellAfter(cell)



3001
3002
3003
3004
3005
3006
3007
3008
3009
3010
3011
3012
3013
3014
3015
3016
3017
3018
3019
3020
3021
3022
3023
 
# File 'lib/rods.rb', line 3001

def insertCellAfter(cell)
  die("insertCellAfter: cell #{cell} is not a REXML::Element") unless (cell.class.to_s == "REXML::Element")
  newCell=createCell(1)
  cell.next_sibling=newCell
  #-----------------------------------------------------------------------
  # Cave: etwaige Wiederholungen uebertragen
  #-----------------------------------------------------------------------
  repetitions=cell.attributes["table:number-columns-repeated"]
  if(repetitions)
    cell.attributes.delete("table:number-columns-repeated")
    newCell.next_sibling=createCell(repetitions.to_i)
  end
  #-----------------------------------------
  # bisherige Tabellenbreite ueberschritten ?
  #-----------------------------------------
  lengthOfRow=getNumberOfSiblings(cell)
  if(lengthOfRow > @tables[@currentTableName][WIDTH])
    @tables[@currentTableName][WIDTH]=lengthOfRow
    @tables[@currentTableName][WIDTHEXCEEDED]=true
    tell("insertCellAfter: new table width: #{lengthOfRow}")
  end
  return newCell
end

#insertCellBefore(cell) ⇒ Object

Inserts a new cell before the given cell thereby shifting existing cells

cell=mySheet.getCell(5,1)
mySheet.insertCellBefore(cell) # adds cell at beginning of row 5



2981
2982
2983
2984
2985
2986
2987
2988
2989
2990
2991
2992
2993
2994
2995
 
# File 'lib/rods.rb', line 2981

def insertCellBefore(cell)
  die("insertCellBefore: cell #{cell} is not a REXML::Element") unless (cell.class.to_s == "REXML::Element")
  newCell=createCell(1)
  cell.previous_sibling=newCell
  #-----------------------------------------
  # bisherige Tabellenbreite überschritten ?
  #-----------------------------------------
  lengthOfRow=getNumberOfSiblings(cell)
  if(lengthOfRow > @tables[@currentTableName][WIDTH])
    @tables[@currentTableName][WIDTH]=lengthOfRow
    @tables[@currentTableName][WIDTHEXCEEDED]=true
    tell("insertCellBefore: new table width: #{lengthOfRow}")
  end
  return newCell
end

#insertCellFromRow(row, colInd) ⇒ Object

Inserts and returns a cell at the given index in the given row, thereby shifting existing cells.

row=mySheet.getRow(5)
cell=mySheet.insertCellFromRow(row,17)



3031
3032
3033
3034
3035
3036
3037
3038
 
# File 'lib/rods.rb', line 3031

def insertCellFromRow(row,colInd)
  die("insertCell: row #{row} is not a REXML::Element") unless (row.class.to_s == "REXML::Element")
  die("insertCell: index #{colInd} is not a Fixnum/Integer") unless (colInd.class.to_s == "Fixnum")
  die("insertCell: invalid index #{colInd}") unless (colInd > 0)
  tell("insertCell: inserting new cell in column:#{colInd}")
  cell=getCellFromRow(row,colInd)
  return insertCellBefore(cell)
end

#insertColumn(colInd) ⇒ Object

Inserts a column at the given index, thereby shifting existing columns

mySheet.insertColumn(1) # inserts column before former column 1



3135
3136
3137
3138
3139
3140
3141
3142
3143
3144
3145
3146
3147
3148
3149
3150
3151
3152
3153
3154
3155
3156
3157
3158
3159
3160
 
# File 'lib/rods.rb', line 3135

def insertColumn(colInd)
  die("insertColumn: index #{colInd} is not a Fixnum/Integer") unless (colInd.class.to_s == "Fixnum")
  die("insertColumn: invalid index #{colInd}") unless (colInd > 0)
  tell("insertColumn: inserting new column")
  currentTable=@tables[@currentTableName][NODE]
  #-----------------------------------------------
  # Neuer Spalteneintrag im Header mit impliziter
  # Aktualisierung der Tabellenbreite
  #-----------------------------------------------
  column=getChildByIndex(currentTable,COLUMN,colInd)
  insertColumnBeforeInHeader(column)
  #-----------------------------------------------
  # Fuer alle existierenden Zeilen neue Zelle an
  # Spaltenposition einfuegen und dabei implizit
  # Tabellenbreite aktualisieren
  #-----------------------------------------------
  row=getRow(1)
  cell=getChildByIndex(row,CELL,colInd)
  insertCellBefore(cell)
  i=1
  while(row=getNextExistentRow(row)) # fuer alle Zeilen ab der zweiten
    cell=getChildByIndex(row,CELL,colInd)
    insertCellBefore(cell)
    i+=1
  end 
end

#insertRow(rowInd) ⇒ Object

Inserts and returns a row at the given index, thereby shifting existing rows

row=mySheet.insertRow(1) # inserts row above former row 1



3057
3058
3059
3060
3061
3062
3063
 
# File 'lib/rods.rb', line 3057

def insertRow(rowInd)
  die("insertRow: invalid rowInd #{rowInd}") unless (rowInd > 0)
  die("insertRow: rowInd #{rowInd} is not a Fixnum/Integer") unless (rowInd.class.to_s == "Fixnum")
  tell("insertRow: inserting new row")
  row=getRow(rowInd)
  return insertRowAbove(row)
end

#insertRowAbove(row) ⇒ Object

Inserts a new row above the given row thereby shifting existing rows

row=mySheet.getRow(1)
mySheet.insertRowAbove(row)



3069
3070
3071
3072
3073
 
# File 'lib/rods.rb', line 3069

def insertRowAbove(row)
  newRow=createRow(1)
  row.previous_sibling=newRow
  return newRow
end

#insertRowBelow(row) ⇒ Object

Inserts a new row below the given row thereby shifting existing rows

row=mySheet.getRow(8)
mySheet.insertRowBelow(row)



3079
3080
3081
3082
3083
3084
3085
3086
3087
3088
3089
3090
3091
 
# File 'lib/rods.rb', line 3079

def insertRowBelow(row)
  newRow=createRow(1)
  row.next_sibling=newRow
  #-----------------------------------------------------------------------
  # Cave: etwaige Wiederholungen uebertragen
  #-----------------------------------------------------------------------
  repetitions=row.attributes["table:number-rows-repeated"]
  if(repetitions)
    row.attributes.delete("table:number-rows-repeated")
    newRow.next_sibling=createRow(repetitions.to_i)
  end
  return newRow
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")



536
537
538
539
540
541
542
543
544
545
546
547
548
549
550
551
552
553
554
555
556
557
558
559
 
# File 'lib/rods.rb', line 536

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")



481
482
483
 
# File 'lib/rods.rb', line 481

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")



473
474
475
 
# File 'lib/rods.rb', line 473

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



2141
2142
2143
 
# File 'lib/rods.rb', line 2141

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’.



2520
2521
2522
2523
2524
2525
2526
2527
2528
2529
2530
2531
 
# File 'lib/rods.rb', line 2520

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"



2119
2120
2121
 
# File 'lib/rods.rb', line 2119

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)
}



839
840
841
842
843
844
845
846
847
848
849
850
851
852
853
854
855
856
857
858
859
860
861
862
863
864
865
866
867
868
869
870
 
# File 'lib/rods.rb', line 839

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 uebersprungen oder erreicht
    #-------------------------------------------
    if(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")



782
783
784
785
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
 
# File 'lib/rods.rb', line 782

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 uebersprungen oder erreicht
    #-------------------------------------------
    if(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



439
440
441
442
443
444
445
446
447
448
449
450
451
452
453
454
455
456
457
 
# File 'lib/rods.rb', line 439

def renameTable(oldName,newName)
  die("renameTable: table '#{oldName}' does not exist") unless (@tables.has_key?(oldName))
  #------------------------------------------------------
  # 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()



2337
2338
2339
2340
2341
2342
2343
2344
 
# File 'lib/rods.rb', line 2337

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")



2350
2351
2352
2353
2354
2355
2356
2357
2358
2359
2360
2361
2362
2363
2364
2365
2366
2367
2368
2369
2370
2371
2372
 
# File 'lib/rods.rb', line 2350

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" }) 
}



1273
1274
1275
1276
1277
1278
1279
1280
1281
1282
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
 
# File 'lib/rods.rb', line 1273

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")



463
464
465
466
467
 
# File 'lib/rods.rb', line 463

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)



55
56
57
58
59
60
61
 
# File 'lib/rods.rb', line 55

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 !



2252
2253
2254
2255
2256
2257
2258
 
# File 'lib/rods.rb', line 2252

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



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

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")



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

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)")



2267
2268
2269
2270
2271
2272
2273
2274
2275
2276
2277
2278
2279
2280
2281
2282
2283
2284
2285
2286
2287
2288
2289
2290
2291
 
# File 'lib/rods.rb', line 2267

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"})



171
172
173
174
175
 
# File 'lib/rods.rb', line 171

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")



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

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"})



1625
1626
1627
 
# File 'lib/rods.rb', line 1625

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(mySheet.getCell(17,39),"currency","14,37")

The example can of course be simplified by

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



1032
1033
1034
1035
1036
1037
1038
1039
1040
1041
1042
1043
1044
1045
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
 
# File 'lib/rods.rb', line 1032

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