| Class | Rods |
| In: |
lib/rods.rb
|
| Parent: | Object |
| 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" |
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).
# File lib/rods.rb, line 2384
2384: def initialize(file,languageArray=["de","DE","€","EUR"])
2385: die("Contructor: second parameter is not an array") unless(languageArray.class.to_s == "Array")
2386: die("Contructor: wrong size of languageArray ... expected 4") unless(languageArray.size == 4)
2387: languageArray.each{ |element|
2388: die("Constructor: element #{element} is not a string") unless (element.class.to_s == "String")
2389: }
2390: @contentText
2391: @language=languageArray[0]
2392: @country=languageArray[1]
2393: @currencySymbol=languageArray[2]
2394: @currencySymbolInternal=languageArray[3]
2395: @spreadSheet
2396: @stylesText
2397: @metaText
2398: @officeMeta
2399: @manifestText
2400: @manifestRoot
2401: @settingsText
2402: @officeSettings
2403: @currentTableName # Name der aktuellen Tabelle
2404: @tables=Hash.new() # Hash der Tabellen und ihrer Eigenschaften
2405: @numTables # Anzahl der Tabellen
2406: @officeStyles
2407: @autoStyles
2408: @floatStyle="myFloat"
2409: @dateStyle="myDate"
2410: @stringStyle="myString"
2411: @currencyStyle="myCurrency"
2412: @percentStyle="myPercent"
2413: @timeStyle="myTime"
2414: @styleCounter=0
2415: @myFile # (ggf. qualifizierter) Dateiname der eingelesenen Datei
2416: #---------------------------------------------------------------
2417: # Hash-Tabelle der geschriebenen Styles
2418: #---------------------------------------------------------------
2419: @styleArchive=Hash.new()
2420: #---------------------------------------------------------------
2421: # Farbpalette
2422: #---------------------------------------------------------------
2423: @palette={"black" => "#000000",
2424: "blue" => "#000080",
2425: "green" => "#008000",
2426: "turquoise" => "#008080",
2427: "red" => "#800000",
2428: "magenta" => "#800080",
2429: "brown" => "#808000",
2430: "grey" => "#808080",
2431: "lightgrey" => "#c0c0c0",
2432: "lightblue" => "#0000ff",
2433: "lightgreen" => "#00ff00",
2434: "lightturquoise" => "#00ffff",
2435: "lightred" => "#ff0000",
2436: "lightmagenta" => "#ff00ff",
2437: "yellow" => "#ffff00",
2438: "white" => "#ffffff",
2439: "grey30" => "#b3b3b3",
2440: "grey20" => "#cccccc",
2441: "grey10" => "#e6e6e6",
2442: "red1" => "#ff3366",
2443: "red2" => "#dc2300",
2444: "red3" => "#b84700",
2445: "red4" => "#ff3333",
2446: "red5" => "#eb613d",
2447: "red6" => "#b84747",
2448: "red7" => "#b80047",
2449: "red8" => "#99284c",
2450: "magenta1" => "#94006b",
2451: "magenta2" => "#94476b",
2452: "magenta3" => "#944794",
2453: "magenta4" => "#9966cc",
2454: "magenta5" => "#6b4794",
2455: "magenta6" => "#6b2394",
2456: "magenta7" => "#6b0094",
2457: "magenta8" => "#5e11a6",
2458: "blue1" => "#280099",
2459: "blue2" => "#4700b8",
2460: "blue3" => "#2300dc",
2461: "blue4" => "#2323dc",
2462: "blue5" => "#0047ff",
2463: "blue6" => "#0099ff",
2464: "blue7" => "#00b8ff",
2465: "blue8" => "#99ccff",
2466: "turquoise1" => "#00dcff",
2467: "turquoise2" => "#00cccc",
2468: "turquoise3" => "#23b8dc",
2469: "turquoise4" => "#47b8b8",
2470: "turquoise5" => "#33a3a3",
2471: "turquoise6" => "#198a8a",
2472: "turquoise7" => "#006b6b",
2473: "turquoise8" => "#004a4a",
2474: "green1" => "#355e00",
2475: "green2" => "#5c8526",
2476: "green3" => "#7da647",
2477: "green4" => "#94bd5e",
2478: "green5" => "#00ae00",
2479: "green6" => "#33cc66",
2480: "yellow1" => "#e6ff00",
2481: "yellow2" => "#ffff99",
2482: "yellow3" => "#ffff66",
2483: "yellow4" => "#e6e64c",
2484: "yellow5" => "#cccc00",
2485: "yellow6" => "#b3b300",
2486: "yellow7" => "#808019",
2487: "yellow8" => "#666600",
2488: "brown1" => "#4c1900",
2489: "brown2" => "#663300",
2490: "brown3" => "#804c19",
2491: "brown4" => "#996633",
2492: "orange1" => "#cc6633",
2493: "orange2" => "#ff6633",
2494: "orange3" => "#ff9966",
2495: "orange4" => "#ffcc99",
2496: "purple" => "#9999ff",
2497: "bordeaux" => "#993366",
2498: "paleyellow" => "#ffffcc",
2499: "palegreen" => "#ccffff",
2500: "darkpurple" => "#660066",
2501: "salmon" => "#ff8080"
2502: }
2503: @fixedStyles=["myTable", "myRow", "myColumn", "myFloatFormat", "myFloat", "myTimeFormat",
2504: "myTime", "myPercentFormat", "myPercent", "myString", "myDateFormat",
2505: "myDate", "myDateFormatDay", "myDateDay", "myCurrencyFormatPositive",
2506: "myCurrencyFormat", "myCurrency", "myCommentParagraph", "myCommentText",
2507: "myCommentGraphics"]
2508: open(file)
2509: end
Delets the cell at the given indices
mySheet.deleteCell(7,9)
# File lib/rods.rb, line 2917
2917: def deleteCell(rowInd,colInd)
2918: die("deleteCell: index #{rowInd} is not a Fixnum/Integer") unless (rowInd.class.to_s == "Fixnum")
2919: die("deleteCell: invalid index #{rowInd}") unless (rowInd > 0)
2920: die("deleteCell: index #{colInd} is not a Fixnum/Integer") unless (colInd.class.to_s == "Fixnum")
2921: die("deleteCell: invalid index #{colInd}") unless (colInd > 0)
2922: tell("deleteCell: deleting cell at #{rowInd}:#{colInd}")
2923: row=getRow(rowInd)
2924: deleteCellFromRow(row,colInd)
2925: end
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
# File lib/rods.rb, line 2855
2855: def deleteCell2(cell)
2856: die("deleteCell2: cell #{cell} is not a REXML::Element") unless (cell.class.to_s == "REXML::Element")
2857: #-------------------------------------------------------------------
2858: # Entweder Wiederholungszahl dekrementieren oder Zelle loeschen
2859: #-------------------------------------------------------------------
2860: repetitions=cell.attributes["table:number-columuns-repeated"]
2861: if(repetitions && repetitions.to_i > 1)
2862: cell.attributes["table:number-columns-repeated"]=(repetitions.to_i-1).to_s
2863: tell("deleteCell2: decrementing empty cells")
2864: else
2865: row=cell.elements["ancestor::table:table-row"]
2866: unless (row)
2867: die("deleteCell2: internal error: Could not extract parent-row of cell #{cell}")
2868: end
2869: row.elements.delete(cell)
2870: tell("deleteCell2: deleting non-empty cell")
2871: end
2872: end
Delets the cell to the right of the given cell
cell=mySheet.writeGetCell(4,7,"date","16.01.2011") mySheet.deleteCellAfter(cell)
# File lib/rods.rb, line 2776
2776: def deleteCellAfter(cell)
2777: die("deleteCellAfter: cell #{cell} is not a REXML::Element") unless (cell.class.to_s == "REXML::Element")
2778: #--------------------------------------------------------
2779: # Entweder Wiederholungsattribut der aktuellen Zelle
2780: # dekrementieren oder ggf. Wiederholungsattribut der
2781: # Folgezelle dekrementieren oder selbige loeschen
2782: #--------------------------------------------------------
2783: repetitions=cell.attributes["table:number-columns-repeated"]
2784: if(repetitions && repetitions.to_i > 1)
2785: cell.attributes["table:number-columns-repeated"]=(repetitions.to_i-1).to_s
2786: else
2787: nextCell=cell.next_sibling
2788: die("deleteCellAfter: cell is already last cell in row") unless (nextCell)
2789: nextRepetitions=nextCell.attributes["table:number-columns-repeated"]
2790: if(nextRepetitions && nextRepetitions.to_i > 1)
2791: nextCell.attributes["table:number-columns-repeated"]=(nextRepetitions.to_i-1).to_s
2792: else
2793: row=cell.elements["ancestor::table:table-row"]
2794: unless (row)
2795: die("deleteCellAfter: internal error: Could not extract parent-row of cell #{cell}")
2796: end
2797: row.elements.delete(nextCell)
2798: end
2799: end
2800: end
Delets the cell to the left of the given cell
cell=mySheet.writeGetCell(4,7,"formula:currency","=A1+B2") mySheet.deleteCellBefore(cell)
# File lib/rods.rb, line 2957
2957: def deleteCellBefore(cell)
2958: die("deleteCellBefore: cell #{cell} is not a REXML::Element") unless (cell.class.to_s == "REXML::Element")
2959: #--------------------------------------------------------
2960: # Entweder Wiederholungsattribut der vorherigen Zelle
2961: # dekrementieren oder selbige loeschen
2962: #--------------------------------------------------------
2963: previousCell=cell.previous_sibling
2964: die("deleteCellBefore: cell is already first cell in row") unless (previousCell)
2965: previousRepetitions=previousCell.attributes["table:number-columns-repeated"]
2966: if(previousRepetitions && previousRepetitions.to_i > 1)
2967: previousCell.attributes["table:number-columns-repeated"]=(previousRepetitions.to_i-1).to_s
2968: else
2969: row=cell.elements["ancestor::table:table-row"]
2970: unless (row)
2971: die("deleteCellBefore: internal error: Could not extract parent-row of cell #{cell}")
2972: end
2973: row.elements.delete(previousCell)
2974: end
2975: end
Delets the cell at the given index in the given row
row=mySheet.getRow(8) mySheet.deleteCell(row,9)
# File lib/rods.rb, line 2838
2838: def deleteCellFromRow(row,colInd)
2839: die("deleteCell: row #{row} is not a REXML::Element") unless (row.class.to_s == "REXML::Element")
2840: die("deleteCell: index #{colInd} is not a Fixnum/Integer") unless (colInd.class.to_s == "Fixnum")
2841: die("deleteCell: invalid index #{colInd}") unless (colInd > 0)
2842: cell=getCellFromRow(row,colInd+1)
2843: deleteCellBefore(cell)
2844: end
Deletes the column at the given index
mySheet.deleteColumn(8)
# File lib/rods.rb, line 3097
3097: def deleteColumn(colInd)
3098: die("deleteColumn: index #{colInd} is not a Fixnum/Integer") unless (colInd.class.to_s == "Fixnum")
3099: die("deleteColumn: invalid index #{colInd}") unless (colInd > 0)
3100: currentWidth=@tables[@currentTableName][WIDTH]
3101: die("deleteColumn: column-index #{colInd} is outside valid range/current table width") if (colInd > currentWidth)
3102: #-------------------------------------------------------------------
3103: # Entweder Wiederholungsattribut der fraglichen Spalte dekrementieren
3104: # oder selbige loeschen
3105: #-------------------------------------------------------------------
3106: currentTable=@tables[@currentTableName][NODE]
3107: column=getChildByIndex(currentTable,COLUMN,colInd)
3108: repetitions=column.attributes["table:number-columns-repeated"]
3109: if(repetitions && repetitions.to_i > 1)
3110: column.attributes["table:number-columns-repeated"]=(repetitions.to_i-1).to_s
3111: else
3112: table=column.elements["ancestor::table:table"]
3113: unless (table)
3114: die("deleteColumn: internal error: Could not extract parent-table of column #{column}")
3115: end
3116: table.elements.delete(column)
3117: end
3118: #-----------------------------------------------
3119: # Fuer alle existierenden Zeilen neue Zelle an
3120: # Spaltenposition einfuegen und dabei implizit
3121: # Tabellenbreite aktualisieren
3122: #-----------------------------------------------
3123: row=getRow(1)
3124: deleteCellFromRow(row,colInd)
3125: i=1
3126: while(row=getNextExistentRow(row)) # fuer alle Zeilen ab der zweiten
3127: deleteCellFromRow(row,colInd)
3128: i+=1
3129: end
3130: end
Delets the row at the given index
mySheet.deleteRow(7)
# File lib/rods.rb, line 2906
2906: def deleteRow(rowInd)
2907: die("deleteRow: index #{rowInd} is not a Fixnum/Integer") unless (rowInd.class.to_s == "Fixnum")
2908: die("deleteRow: invalid index #{rowInd}") unless (rowInd > 0)
2909: row=getRow(rowInd+1)
2910: deleteRowAbove(row)
2911: end
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
# File lib/rods.rb, line 2883
2883: def deleteRow2(row)
2884: die("deleteRow2: row #{row} is not a REXML::Element") unless (row.class.to_s == "REXML::Element")
2885: #-------------------------------------------------------------------
2886: # Entweder Wiederholungszahl dekrementieren oder Zeile loeschen
2887: #-------------------------------------------------------------------
2888: repetitions=row.attributes["table:number-rows-repeated"]
2889: if(repetitions && repetitions.to_i > 1)
2890: row.attributes["table:number-rows-repeated"]=(repetitions.to_i-1).to_s
2891: tell("deleteRow2: decrementing empty rows")
2892: else
2893: table=row.elements["ancestor::table:table"]
2894: unless (table)
2895: die("deleteRow2: internal error: Could not extract parent-table of row #{row}")
2896: end
2897: table.elements.delete(row)
2898: tell("deleteRow2: deleting non-empty row")
2899: end
2900: end
Delets the row above the given row
row=mySheet.getRow(5) mySheet.deleteRowAbove(row)
# File lib/rods.rb, line 2932
2932: def deleteRowAbove(row)
2933: die("deleteRowAbove: row #{row} is not a REXML::Element") unless (row.class.to_s == "REXML::Element")
2934: #--------------------------------------------------------
2935: # Entweder Wiederholungsattribut der vorherigen Zeile
2936: # dekrementieren oder selbige loeschen
2937: #--------------------------------------------------------
2938: previousRow=row.previous_sibling
2939: die("deleteRowAbove: row is already first row in row") unless (previousRow)
2940: previousRepetitions=previousRow.attributes["table:number-rows-repeated"]
2941: if(previousRepetitions && previousRepetitions.to_i > 1)
2942: previousRow.attributes["table:number-rows-repeated"]=(previousRepetitions.to_i-1).to_s
2943: else
2944: table=row.elements["ancestor::table:table"]
2945: unless (table)
2946: die("deleteRowAbove: internal error: Could not extract parent-table of row #{row}")
2947: end
2948: table.elements.delete(previousRow)
2949: end
2950: end
Delets the row below the given row
row=mySheet.getRow(11) mySheet.deleteRowBelow(row)
# File lib/rods.rb, line 2807
2807: def deleteRowBelow(row)
2808: die("deleteRowBelow: row #{row} is not a REXML::Element") unless (row.class.to_s == "REXML::Element")
2809: #--------------------------------------------------------
2810: # Entweder Wiederholungsattribut der aktuellen Zeile
2811: # dekrementieren oder ggf. Wiederholungsattribut der
2812: # Folgezeile dekrementieren oder selbige loeschen
2813: #--------------------------------------------------------
2814: repetitions=row.attributes["table:number-rows-repeated"]
2815: if(repetitions && repetitions.to_i > 1)
2816: row.attributes["table:number-rows-repeated"]=(repetitions.to_i-1).to_s
2817: else
2818: nextRow=row.next_sibling
2819: die("deleteRowBelow: row #{row} is already last row in table") unless (nextRow)
2820: nextRepetitions=nextRow.attributes["table:number-rows-repeated"]
2821: if(nextRepetitions && nextRepetitions.to_i > 1)
2822: nextRow.attributes["table:number-rows-repeated"]=(nextRepetitions.to_i-1).to_s
2823: else
2824: table=row.elements["ancestor::table:table"]
2825: unless (table)
2826: die("deleteRowBelow: internal error: Could not extract parent-table of row #{row}")
2827: end
2828: table.elements.delete(nextRow)
2829: end
2830: end
2831: end
Deletes the table of the given name and updates the internal table-administration.
mySheet.deleteTable("Tabelle2")
# File lib/rods.rb, line 565
565: def deleteTable(tableName)
566: die("deleteTable: table '#{tableName}' cannot be deleted as it is the current table !") if (tableName == @currentTableName)
567: #----------------------------------------------------
568: # Tabellenname gueltig ?
569: #----------------------------------------------------
570: if(@tables.has_key?(tableName))
571: #--------------------------------------------------
572: # Loeschung in XML-Tree
573: #--------------------------------------------------
574: node=@tables[tableName][NODE]
575: @spreadSheet.elements.delete(node)
576: #--------------------------------------------------
577: # Loeschung in Tabellen-Hash
578: #--------------------------------------------------
579: @tables.delete(tableName)
580: @numTables-=1
581: tell("deleteTable: deleting table #{tableName}")
582: else
583: die("deleteTable: invalid table-name/not existing table: '#{tableName}'")
584: end
585: end
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)
# File lib/rods.rb, line 237
237: def getCell(rowInd,colInd)
238: row=getRow(rowInd)
239: return getChildByIndex(row,CELL,colInd)
240: end
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.
# File lib/rods.rb, line 228
228: def getCellFromRow(row,colInd)
229: return getChildByIndex(row,CELL,colInd)
230: end
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(’\d{1}[.,]\d{2}’)
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]")
}
# File lib/rods.rb, line 2611
2611: def getCellsAndIndicesFor(content)
2612: die("getCellsAndIndicesFor: 'content' is not of typ String") unless (content.class.to_s == "String")
2613: result=Array.new()
2614: i=0
2615: tell("getCellsAndIndicesFor: Searching for cells with content '#{content}'")
2616: #----------------------------------------------------------------
2617: # Alle Text-Nodes suchen
2618: #----------------------------------------------------------------
2619: @spreadSheet.elements.each("//table:table-cell/text:p"){ |textNode|
2620: text=textNode.text
2621: #---------------------------------------------------------
2622: # Zelle gefunden ?
2623: #
2624: # 'content' darf regulaerer Ausdruck sein, muss dann jedoch
2625: # in einfachen Hochkommata uebergeben werden
2626: #---------------------------------------------------------
2627: if(text && (text.match(/#{content}/)))
2628: result[i]=Hash.new()
2629: tell("getCellsAndIndicesFor: '#{content}' matched '#{text}'")
2630: #-----------------------------------------------------
2631: # Zelle und Zellenindex ermitteln
2632: #-----------------------------------------------------
2633: cell=textNode.elements["ancestor::table:table-cell"]
2634: unless (cell)
2635: die("getCellsAndIndicesFor: internal error: Could not extract parent-cell of textNode with #{content}")
2636: end
2637: colIndex=getIndex(cell)
2638: #-----------------------------------------------------
2639: # Zeile und Zeilenindex ermitteln
2640: #-----------------------------------------------------
2641: row=textNode.elements["ancestor::table:table-row"]
2642: unless (row)
2643: die("getCellsAndIndicesFor: internal error: Could not extract parent-row of textNode with #{content}")
2644: end
2645: rowIndex=getIndex(row)
2646: result[i][:cell]=cell
2647: result[i][:row]=rowIndex
2648: result[i][:col]=colIndex
2649: tell("getCellsAndIndicesFor: Indices #{rowIndex} #{colIndex}")
2650: i+=1
2651: end
2652: }
2653: return result
2654: end
internal: Wrapper for getIndexAndOrNumber(node,BOTH)
# File lib/rods.rb, line 2673
2673: def getIndexAndNumber(node)
2674: return getIndexAndOrNumber(node,BOTH)
2675: end
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 !
# File lib/rods.rb, line 2557
2557: def getNextExistentCell(cell)
2558: return cell.next_sibling
2559: end
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 !
# File lib/rods.rb, line 2575
2575: def getNextExistentRow(row)
2576: return row.next_sibling
2577: end
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 !
# File lib/rods.rb, line 2566
2566: def getPreviousExistentCell(cell)
2567: return cell.previous_sibling
2568: end
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 !
# File lib/rods.rb, line 2539
2539: def getPreviousExistentRow(row)
2540: #----------------------------------------------------------------------
2541: # Cave: table:table-row und table:table-column sind Siblings !!!!
2542: # Letztere duerfen jedoch NICHT zurueckgegeben werden
2543: #----------------------------------------------------------------------
2544: previousSibling=row.previous_sibling
2545: if(previousSibling && previousSibling.elements["self::table:table-row"])
2546: return previousSibling
2547: else
2548: return nil
2549: end
2550: end
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}
}
# File lib/rods.rb, line 253
253: def getRow(rowInd)
254: currentTable=@tables[@currentTableName][NODE]
255: return getChildByIndex(currentTable,ROW,rowInd)
256: end
Inserts and returns a cell at the given index, thereby shifting existing cells.
cell=mySheet.insertCell(4,17)
# File lib/rods.rb, line 3044
3044: def insertCell(rowInd,colInd)
3045: die("insertCell: index #{rowInd} is not a Fixnum/Integer") unless (rowInd.class.to_s == "Fixnum")
3046: die("insertCell: invalid index #{rowInd}") unless (rowInd > 0)
3047: die("insertCell: index #{colInd} is not a Fixnum/Integer") unless (colInd.class.to_s == "Fixnum")
3048: die("insertCell: invalid index #{colInd}") unless (colInd > 0)
3049: tell("insertCell: inserting new cell at #{rowInd}:#{colInd}")
3050: cell=getCell(rowInd,colInd)
3051: return insertCellBefore(cell)
3052: end
Inserts a new cell after the given cell thereby shifting existing cells
cell=mySheet.getCell(4,7) mySheet.insertCellAfter(cell)
# File lib/rods.rb, line 3001
3001: def insertCellAfter(cell)
3002: die("insertCellAfter: cell #{cell} is not a REXML::Element") unless (cell.class.to_s == "REXML::Element")
3003: newCell=createCell(1)
3004: cell.next_sibling=newCell
3005: #-----------------------------------------------------------------------
3006: # Cave: etwaige Wiederholungen uebertragen
3007: #-----------------------------------------------------------------------
3008: repetitions=cell.attributes["table:number-columns-repeated"]
3009: if(repetitions)
3010: cell.attributes.delete("table:number-columns-repeated")
3011: newCell.next_sibling=createCell(repetitions.to_i)
3012: end
3013: #-----------------------------------------
3014: # bisherige Tabellenbreite ueberschritten ?
3015: #-----------------------------------------
3016: lengthOfRow=getNumberOfSiblings(cell)
3017: if(lengthOfRow > @tables[@currentTableName][WIDTH])
3018: @tables[@currentTableName][WIDTH]=lengthOfRow
3019: @tables[@currentTableName][WIDTHEXCEEDED]=true
3020: tell("insertCellAfter: new table width: #{lengthOfRow}")
3021: end
3022: return newCell
3023: end
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
# File lib/rods.rb, line 2981
2981: def insertCellBefore(cell)
2982: die("insertCellBefore: cell #{cell} is not a REXML::Element") unless (cell.class.to_s == "REXML::Element")
2983: newCell=createCell(1)
2984: cell.previous_sibling=newCell
2985: #-----------------------------------------
2986: # bisherige Tabellenbreite überschritten ?
2987: #-----------------------------------------
2988: lengthOfRow=getNumberOfSiblings(cell)
2989: if(lengthOfRow > @tables[@currentTableName][WIDTH])
2990: @tables[@currentTableName][WIDTH]=lengthOfRow
2991: @tables[@currentTableName][WIDTHEXCEEDED]=true
2992: tell("insertCellBefore: new table width: #{lengthOfRow}")
2993: end
2994: return newCell
2995: end
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)
# File lib/rods.rb, line 3031
3031: def insertCellFromRow(row,colInd)
3032: die("insertCell: row #{row} is not a REXML::Element") unless (row.class.to_s == "REXML::Element")
3033: die("insertCell: index #{colInd} is not a Fixnum/Integer") unless (colInd.class.to_s == "Fixnum")
3034: die("insertCell: invalid index #{colInd}") unless (colInd > 0)
3035: tell("insertCell: inserting new cell in column:#{colInd}")
3036: cell=getCellFromRow(row,colInd)
3037: return insertCellBefore(cell)
3038: end
Inserts a column at the given index, thereby shifting existing columns
mySheet.insertColumn(1) # inserts column before former column 1
# File lib/rods.rb, line 3135
3135: def insertColumn(colInd)
3136: die("insertColumn: index #{colInd} is not a Fixnum/Integer") unless (colInd.class.to_s == "Fixnum")
3137: die("insertColumn: invalid index #{colInd}") unless (colInd > 0)
3138: tell("insertColumn: inserting new column")
3139: currentTable=@tables[@currentTableName][NODE]
3140: #-----------------------------------------------
3141: # Neuer Spalteneintrag im Header mit impliziter
3142: # Aktualisierung der Tabellenbreite
3143: #-----------------------------------------------
3144: column=getChildByIndex(currentTable,COLUMN,colInd)
3145: insertColumnBeforeInHeader(column)
3146: #-----------------------------------------------
3147: # Fuer alle existierenden Zeilen neue Zelle an
3148: # Spaltenposition einfuegen und dabei implizit
3149: # Tabellenbreite aktualisieren
3150: #-----------------------------------------------
3151: row=getRow(1)
3152: cell=getChildByIndex(row,CELL,colInd)
3153: insertCellBefore(cell)
3154: i=1
3155: while(row=getNextExistentRow(row)) # fuer alle Zeilen ab der zweiten
3156: cell=getChildByIndex(row,CELL,colInd)
3157: insertCellBefore(cell)
3158: i+=1
3159: end
3160: end
Inserts and returns a row at the given index, thereby shifting existing rows
row=mySheet.insertRow(1) # inserts row above former row 1
# File lib/rods.rb, line 3057
3057: def insertRow(rowInd)
3058: die("insertRow: invalid rowInd #{rowInd}") unless (rowInd > 0)
3059: die("insertRow: rowInd #{rowInd} is not a Fixnum/Integer") unless (rowInd.class.to_s == "Fixnum")
3060: tell("insertRow: inserting new row")
3061: row=getRow(rowInd)
3062: return insertRowAbove(row)
3063: end
Inserts a new row below the given row thereby shifting existing rows
row=mySheet.getRow(8) mySheet.insertRowBelow(row)
# File lib/rods.rb, line 3079
3079: def insertRowBelow(row)
3080: newRow=createRow(1)
3081: row.next_sibling=newRow
3082: #-----------------------------------------------------------------------
3083: # Cave: etwaige Wiederholungen uebertragen
3084: #-----------------------------------------------------------------------
3085: repetitions=row.attributes["table:number-rows-repeated"]
3086: if(repetitions)
3087: row.attributes.delete("table:number-rows-repeated")
3088: newRow.next_sibling=createRow(repetitions.to_i)
3089: end
3090: return newRow
3091: end
Inserts a table of the given name at the end of the spreadsheet and updates the internal table-administration.
mySheet.insertTable("example")
# File lib/rods.rb, line 536
536: def insertTable(tableName)
537: die("insertTable: table '#{tableName}' already exists") if (@tables.has_key?(tableName))
538: #---------------------------------------------------------------------------
539: # XML-Tree schreiben
540: #---------------------------------------------------------------------------
541: newTable=writeXml(@spreadSheet,{TAG => "table:table",
542: "table:name" => tableName,
543: "table:print" => "false",
544: "table:style-name" => "myTable",
545: "child1" => {TAG => "table:table-column",
546: "table:style" => "myColumn",
547: "table:default-cell-style-name" => "Default"},
548: "child2" => {TAG => "table:table-row",
549: "table:style-name" => "myRow",
550: "child3" => {TAG => "table:table-cell"}}})
551: #---------------------------------------------------------------------------
552: # Tabellen-Hash aktualisieren
553: #---------------------------------------------------------------------------
554: @tables[tableName]=Hash.new()
555: @tables[tableName][NODE]=newTable
556: @tables[tableName][WIDTH]=getTableWidth(newTable)
557: @tables[tableName][WIDTHEXCEEDED]=false
558: @numTables+=1
559: end
Inserts a table of the given name after the given spreadsheet and updates the internal table-administration.
mySheet.insertTableAfter("table1","table2")
# File lib/rods.rb, line 481
481: def insertTableAfter(relativeTableName,tableName)
482: insertTableBeforeAfter(relativeTableName,tableName,AFTER)
483: end
Inserts a table of the given name before the given spreadsheet and updates the internal table-administration.
mySheet.insertTableBefore("table2","table1")
# File lib/rods.rb, line 473
473: def insertTableBefore(relativeTableName,tableName)
474: insertTableBeforeAfter(relativeTableName,tableName,BEFORE)
475: end
Helper-Tool: Prints all styles of content.xml in indented ASCII-notation
mySheet.printAutoStyles()
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
# File lib/rods.rb, line 2141
2141: def printAutoStyles()
2142: printStyles(@autoStyles," ")
2143: end
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’.
# File lib/rods.rb, line 2520
2520: def printColorMap()
2521: puts("printColorMap: convenience color-mappings")
2522: puts("-----------------------------------------")
2523: @palette.each{ |key,value|
2524: puts(" #{key} -> #{value}")
2525: }
2526: puts("You can use the convenience keys in 'setAttribute' and 'writeStyleAbbr'")
2527: puts("for the attributes")
2528: puts(" border,border-bottom, border-top, border-left, border-right")
2529: puts(" background-color")
2530: puts(" color")
2531: end
Helper-Tool: Prints all styles of styles.xml in indented ASCII-notation
mySheet.printOfficeStyles()
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"
# File lib/rods.rb, line 2119
2119: def printOfficeStyles()
2120: printStyles(@officeStyles," ")
2121: end
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
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)
}
# File lib/rods.rb, line 839
839: def readCell(rowInd,colInd)
840: #------------------------------------------------------------------
841: # Fuer alle Zeilen
842: #------------------------------------------------------------------
843: i=0
844: j=0
845: #------------------------------------------------------------------
846: # Zelle mit Indizes suchen
847: #------------------------------------------------------------------
848: currentTable=@tables[@currentTableName][NODE]
849: currentTable.elements.each("table:table-row"){ |row|
850: i=i+1
851: j=0
852: repetition=row.attributes["table:number-rows-repeated"]
853: #-------------------------------------------
854: # Zeilenwiederholungen addieren
855: #-------------------------------------------
856: if(repetition)
857: i=i+(repetition.to_i-1)
858: end
859: #-------------------------------------------
860: # Falls Zeilenindex uebersprungen oder erreicht
861: #-------------------------------------------
862: if(i >= rowInd)
863: return readCellFromRow(row,colInd)
864: end
865: }
866: #--------------------------------------------
867: # ausserhalb bisheriger Zeilen
868: #--------------------------------------------
869: return nil,nil
870: end
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
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")
# File lib/rods.rb, line 782
782: def readCellFromRow(row,colInd)
783: j=0
784: #------------------------------------------------------------------
785: # Fuer alle Spalten
786: #------------------------------------------------------------------
787: row.elements.each("table:table-cell"){ |cell|
788: j=j+1
789: #-------------------------------------------
790: # Spaltenwiederholungen addieren
791: #-------------------------------------------
792: repetition=cell.attributes["table:number-columns-repeated"]
793: if(repetition)
794: j=j+(repetition.to_i-1)
795: end
796: #-------------------------------------------
797: # Falls Spaltenindex uebersprungen oder erreicht
798: #-------------------------------------------
799: if(j >= colInd)
800: #-------------------------------------------
801: # Zelltext und Datentyp zurueckgeben
802: # ggf. Waehrungssymbol abschneiden
803: #-------------------------------------------
804: textElement=cell.elements["text:p"]
805: if(! textElement)
806: return nil,nil
807: else
808: text=textElement.text
809: if(! text)
810: text=""
811: end
812: type=cell.attributes["office:value-type"]
813: if(! type)
814: type="string"
815: end
816: text=normalizeText(text,type)
817: return text,type
818: end
819: end
820: }
821: #----------------------------------------------
822: # ausserhalb bisheriger Spalten
823: #----------------------------------------------
824: return nil,nil
825: end
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
# File lib/rods.rb, line 439
439: def renameTable(oldName,newName)
440: die("renameTable: table '#{oldName}' does not exist") unless (@tables.has_key?(oldName))
441: #------------------------------------------------------
442: # XML-Tree anpassen
443: #------------------------------------------------------
444: node=@tables[oldName][NODE]
445: node.attributes["table:name"]=newName
446: #------------------------------------------------------
447: # Tabellen-Hash anpassen
448: #------------------------------------------------------
449: @tables[newName]=@tables[oldName]
450: @tables.delete(oldName)
451: if(oldName == @currentTableName)
452: @currentTableName=newName
453: tell("renameTable: renaming table (which is current table !) '#{oldName}' to '#{newName}'")
454: else
455: tell("renameTable: renaming table '#{oldName}' to '#{newName}'")
456: end
457: end
Saves the file associated with the current RODS-object.
mySheet.save()
# File lib/rods.rb, line 2337
2337: def save()
2338: die("save: internal error: @myFile is not set -> cannot save file") unless (@myFile && (! @myFile.empty?))
2339: die("save: this should not happen: file #{@myFile} is missing") unless (File.exists?(@myFile))
2340: tell("save: saving as file #{@myFile}")
2341: Zip::ZipFile.open(@myFile){ |zipfile|
2342: finalize(zipfile)
2343: }
2344: end
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")
# File lib/rods.rb, line 2350
2350: def saveAs(newFile)
2351: die("saveAs: file #{newFile} does not have valid ending '*.ods'") unless (newFile.match(/\.ods$/))
2352: if(File.exists?(newFile))
2353: tell("saveAs: file #{newFile} exists -> deleting")
2354: File.delete(newFile)
2355: end
2356: #--------------------------------------------------------
2357: # Datei anlegen
2358: #--------------------------------------------------------
2359: tell("saveAs: saving as file #{newFile}")
2360: Zip::ZipFile.open(newFile,true){ |zipfile|
2361: ["Configurations2","META-INF","Thumbnails"].each{ |dir|
2362: zipfile.mkdir(dir)
2363: zipfile.file.chmod(0755,dir)
2364: }
2365: ["accelerator","floater","images","menubar","popupmenu","progressbar","statusbar","toolbar"].each{ |dir|
2366: subDir="Configurations2/"+dir
2367: zipfile.mkdir(subDir)
2368: zipfile.file.chmod(0755,subDir)
2369: }
2370: finalize(zipfile)
2371: }
2372: end
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" })
}
# File lib/rods.rb, line 1273
1273: def setAttributes(cell,attributes)
1274: die("setAttributes: cell #{cell} is not a REXML::Element") unless (cell.class.to_s == "REXML::Element")
1275: die("setAttributes: hash #{attributes} is not a hash") unless (attributes.class.to_s == "Hash")
1276: #----------------------------------------------------------------------
1277: # Flag, ob neue Attribute und deren Auspraegungen bereits im aktuellen
1278: # style vorhanden sind
1279: #----------------------------------------------------------------------
1280: containsMatchingAttributes=TRUE
1281: #-----------------------------------------------------------------------
1282: # Attribut-Hash, welcher "convenience"-Werte enthalten kann (und wird ;-)
1283: # zunaechst normieren
1284: #-----------------------------------------------------------------------
1285: attributes=normStyleHash(attributes)
1286: die("setAttributes: attribute style:name not allowed in attribute-list as automatically generated") if (attributes.has_key?("style:name"))
1287: #------------------------------------------------------------------
1288: # Falls Zelle bereits style zugewiesen hat
1289: #------------------------------------------------------------------
1290: currentStyleName=cell.attributes["table:style-name"]
1291: if(currentStyleName)
1292: #---------------------------------------------------------------
1293: # style suchen (lassen)
1294: #---------------------------------------------------------------
1295: file,currentStyle=getStyle(currentStyleName)
1296: #-----------------------------------------------------------------------
1297: # Pruefung, ob oben gefundener style die neuen Attribute und deren Werte
1298: # bereits enthaelt.
1299: # Falls auch nur ein Attribut nicht oder nicht mit dem richtigen Wert
1300: # vorhanden ist, muss ein neuer style erstellt werden.
1301: # Grundannahme: Ein Open-Document-Style-Attribut kann per se immer nur in einem bestimmten Typ
1302: # Knoten vorkommen und muss daher nicht naeher qualifiziert werden !
1303: #-----------------------------------------------------------------------
1304: attributes.each{ |attribute,value|
1305: currentValue=currentStyle.attributes[attribute]
1306: #-------------------------------------------------
1307: # Attribut in Context-Node nicht gefunden ?
1308: #-------------------------------------------------
1309: if(! currentValue) # nilClass
1310: tell("setAttributes: #{currentStyleName}: #{attribute} not in Top-Node")
1311: #-----------------------------------------------------------
1312: # Attribut mit passendem Wert dann in Kind-Element vorhanden ?
1313: #-----------------------------------------------------------
1314: if(currentStyle.elements["*[@#{attribute} = '#{value}']"])
1315: tell("setAttributes: #{currentStyleName}: #{attribute}/#{value} matching in Sub-Node")
1316: #-----------------------------------------------------------
1317: # andernfalls Komplettabbruch der Pruefschleife aller Attribute und Flag setzen
1318: # => neuer style muss erzeugt werden
1319: #-----------------------------------------------------------
1320: else
1321: tell("setAttributes: #{currentStyleName}: #{attribute}/#{value} not matching in Sub-Node")
1322: containsMatchingAttributes=FALSE
1323: break
1324: end
1325: #--------------------------------------------------
1326: # Attribut in Context-Node gefunden
1327: #--------------------------------------------------
1328: else
1329: #--------------------------------------------------
1330: # Passt der Wert des gefundenen Attributes bereits ?
1331: #--------------------------------------------------
1332: if (currentValue == value)
1333: tell("setAttributes: #{currentStyleName}: #{attribute}/#{value} matching in Top-Node")
1334: #-------------------------------------------------
1335: # bei unpassendem Wert Flag setzen
1336: #-------------------------------------------------
1337: else
1338: tell("setAttributes: #{currentStyleName}: #{attribute}/#{value} not matching with #{currentValue} in Top-Node")
1339: containsMatchingAttributes=FALSE
1340: end
1341: end
1342: }
1343: #--------------------------------------------------------
1344: # Wurden alle Attribut-Wertepaare gefunden, d.h. kann
1345: # bisheriger style weiterverwendet werden ?
1346: #--------------------------------------------------------
1347: if(containsMatchingAttributes)
1348: tell("setAttributes: #{currentStyleName}: all attributes/values matching -> keeping current style")
1349: #-------------------------------------------------------
1350: # nein => passenden Style in Archiv suchen oder klonen und anpassen
1351: #-------------------------------------------------------
1352: else
1353: getAppropriateStyle(cell,currentStyle,attributes)
1354: end
1355: #------------------------------------------------------------------------
1356: # Zelle hatte noch gar keinen style zugewiesen
1357: #------------------------------------------------------------------------
1358: else
1359: #----------------------------------------------------------------------
1360: # Da style fehlt, ggf. aus office:value-type bestmoeglichen style ermitteln
1361: #----------------------------------------------------------------------
1362: valueType=cell.attributes["office:value-type"]
1363: if(valueType)
1364: case valueType
1365: when "string" then currentStyleName="myString"
1366: when "percentage" then currentStyleName="myPercentage"
1367: when "currency" then currentStyleName="myCurrency"
1368: when "float" then currentStyleName="myFloat"
1369: when "date" then currentStyleName="myDate"
1370: when "time" then currentStyleName="myTime"
1371: else
1372: die("setAttributes: unknown office:value-type #{valueType} found in #{cell}")
1373: end
1374: else
1375: #-----------------------------------------
1376: # 'myString' als Default
1377: #-----------------------------------------
1378: currentStyleName="myString"
1379: end
1380: #-------------------------------------------------------
1381: # passenden Style in Archiv suchen oder klonen und anpassen
1382: #-------------------------------------------------------
1383: file,currentStyle=getStyle(currentStyleName)
1384: getAppropriateStyle(cell,currentStyle,attributes)
1385: end
1386: end
Sets the table of the given name as the default-table for all subsequent operations.
mySheet.setCurrentTable("example")
# File lib/rods.rb, line 463
463: def setCurrentTable(tableName)
464: die("setCurrentTable: table '#{tableName}' does not exist") unless (@tables.has_key?(tableName))
465: @currentTableName=tableName
466: tell("setCurrentTable: setting #{tableName} as current table")
467: end
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
Example
mySheet.setDateFormat("myDateDay") # RODS' default format for display of weekday
mySheet.setDateFormat("myDate") # RODS' default format for date ("12.01.2011" German format)
# File lib/rods.rb, line 55
55: def setDateFormat(formatName)
56: case formatName
57: when "myDate" then @dateStyle="myDate"
58: when "myDateDay" then @dateStyle="myDateDay"
59: else die("setDateFormat: invalid format-name #{format}")
60: end
61: end
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 !
# File lib/rods.rb, line 2252
2252: def setStyle(cell,styleName)
2253: #-----------------------------------------------------------------------
2254: # Ist Style gueltig, d.h. in content.xml vorhanden ?
2255: #-----------------------------------------------------------------------
2256: die("setStyle: style \'#{styleName}\' does not exist") unless (@autoStyles.elements["*[@style:name = '#{styleName}']"])
2257: cell.attributes['table:style-name']=styleName
2258: end
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
# File lib/rods.rb, line 185
185: def writeCell(rowInd,colInd,type,text)
186: cell=getCell(rowInd,colInd)
187: writeText(cell,type,text)
188: end
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")
# File lib/rods.rb, line 211
211: def writeCellFromRow(row,colInd,type,text)
212: cell=getCellFromRow(row,colInd)
213: writeText(cell,type,text)
214: end
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)")
# File lib/rods.rb, line 2267
2267: def writeComment(cell,comment)
2268: die("writeComment: cell #{cell} is not a REXML::Element") unless (cell.class.to_s == "REXML::Element")
2269: die("writeComment: comment #{comment} is not a string") unless (comment.class.to_s == "String")
2270: #--------------------------------------------
2271: # Ggf. alten Kommentar loeschen
2272: #--------------------------------------------
2273: cell.elements.delete("office:annotation")
2274: writeXml(cell,{TAG => "office:annotation",
2275: "svg:x" => "4.119cm",
2276: "draw:caption-point-x" => "-0.61cm",
2277: "svg:y" => "0cm",
2278: "draw:caption-point-y" => "0.011cm",
2279: "draw:text-style-name" => "myCommentParagraph",
2280: "svg:height" => "0.596cm",
2281: "draw:style-name" => "myCommentGraphics",
2282: "svg:width" => "2.899cm",
2283: "child1" => {TAG => "dc:date",
2284: TEXT => "2010-01-01T00:00:00"
2285: },
2286: "child2" => {TAG => "text:p",
2287: "text:style-name" => "myCommentParagraph",
2288: TEXT => comment
2289: }
2290: })
2291: end
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"})
# File lib/rods.rb, line 171
171: def writeGetCell(rowInd,colInd,type,text)
172: cell=getCell(rowInd,colInd)
173: writeText(cell,type,text)
174: return cell
175: end
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")
# File lib/rods.rb, line 197
197: def writeGetCellFromRow(row,colInd,type,text)
198: cell=getCellFromRow(row,colInd)
199: writeText(cell,type,text)
200: return cell
201: end
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"})
# File lib/rods.rb, line 1625
1625: def writeStyleAbbr(attributes)
1626: writeStyle(normStyleHash(attributes))
1627: end
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")
# File lib/rods.rb, line 1032
1032: def writeText(cell,type,text)
1033: #------------------------------------------
1034: # Zunaechst ggf. stoerende Attribute löschen
1035: #------------------------------------------
1036: cell.attributes.each{ |attribute,value|
1037: cell.attributes.delete(attribute)
1038: }
1039: #-------------------------------------------
1040: # Typabhaengig diverse Attribute der Zelle setzen
1041: #-------------------------------------------
1042: # String
1043: #-------------------------------------------
1044: if(type == "string")
1045: cell.attributes["office:value-type"]="string"
1046: cell.attributes["table:style-name"]=@stringStyle
1047: #-------------------------------------------
1048: # Float
1049: #-------------------------------------------
1050: elsif(type == "float")
1051: cell.attributes["office:value-type"]="float"
1052: #-----------------------------------------------------
1053: # Dezimaltrenner von "," in "." aendern
1054: #-----------------------------------------------------
1055: internalText=text.sub(/,/,".")
1056: cell.attributes["office:value"]=internalText
1057: cell.attributes["table:style-name"]=@floatStyle
1058: #-------------------------------------------
1059: # Formula
1060: # Cave: Zahlformat 1,25 muss geaendert werden in 1.25
1061: # In der reinen Textdarstellung der Zellenformel verwendet
1062: # OpenOffice das laenderspezifische Trennzeichen; im Attributwert
1063: # der Formel muss jedoch das englische Format mit '.' stehen !
1064: # Waehrend dies bei interaktiver Eingabe der Formel transparent
1065: # gewandelt (jedoch stets mit laenderspezifischem Trennzeichen angezeigt) wird,
1066: # muss hier explizit "Hand angelegt" werden. Der Unterschied ist dann lediglich
1067: # in der XML-Darstellung (des Attributwertes) zu sehen, NICHT in der interaktiven
1068: # Anzeige unter OpenOffice.
1069: # Als Fuellwert wird stehts "0" gesetzt; beim Oeffnen der Datei mit OpenOffice
1070: # wird dann der richtige Wert errechnet und geschrieben.
1071: #-------------------------------------------
1072: elsif(type.match(/^formula/))
1073: #---------------------------------------------
1074: # Formel fuer interne Darstellung aufbereiten
1075: #---------------------------------------------
1076: cell.attributes["table:formula"]=internalizeFormula(text)
1077: #---------------------------------------------
1078: # Zellformatierung bestimmen
1079: #---------------------------------------------
1080: case type
1081: when "formula","formula:float"
1082: cell.attributes["office:value-type"]="float"
1083: cell.attributes["office:value"]=0
1084: cell.attributes["table:style-name"]=@floatStyle
1085: when "formula:time"
1086: cell.attributes["office:value-type"]="time"
1087: cell.attributes["office:time-value"]="PT00H00M00S"
1088: cell.attributes["table:style-name"]=@timeStyle
1089: # cell.attributes["table:style-name"]=""
1090: when "formula:date"
1091: cell.attributes["office:value-type"]="date"
1092: cell.attributes["office:date-value"]="0"
1093: cell.attributes["table:style-name"]=@dateStyle
1094: when "formula:currency"
1095: cell.attributes["office:value-type"]="currency"
1096: #-----------------------------------------------------
1097: # Dezimaltrenner von "," in "." aendern
1098: #-----------------------------------------------------
1099: internalText="0.0"
1100: cell.attributes["office:value"]=internalText
1101: cell.attributes["office:currency"]=@currencySymbolInternal
1102: cell.attributes["table:style-name"]=@currencyStyle
1103: else die("writeText: invalid type of formula #{type}")
1104: end
1105: text="0"
1106: #-------------------------------------------
1107: # Percent
1108: #-------------------------------------------
1109: elsif(type == "percent")
1110: cell.attributes["office:value-type"]="percentage"
1111: cell.attributes["office:value"]=percent2PercentVal(text)
1112: cell.attributes["table:style-name"]=@percentStyle
1113: text=text+" %"
1114: #-------------------------------------------
1115: # Currency
1116: #-------------------------------------------
1117: elsif(type == "currency")
1118: cell.attributes["office:value-type"]="currency"
1119: #-----------------------------------------------------
1120: # Dezimaltrenner von "," in "." aendern und
1121: # Waehrungs-Symbol hintanstellen
1122: #-----------------------------------------------------
1123: internalText=text.sub(/,/,".")
1124: text=text+" "+@currencySymbol
1125: cell.attributes["office:value"]=internalText
1126: cell.attributes["office:currency"]=@currencySymbolInternal
1127: cell.attributes["table:style-name"]=@currencyStyle
1128: #-------------------------------------------
1129: # Date
1130: #-------------------------------------------
1131: elsif(type == "date")
1132: cell.attributes["office:value-type"]="date"
1133: cell.attributes["table:style-name"]=@dateStyle
1134: cell.attributes["office:date-value"]=date2DateVal(text)
1135: #-------------------------------------------
1136: # Time (im Format 13:37)
1137: #-------------------------------------------
1138: elsif(type == "time")
1139: cell.attributes["office:value-type"]="time"
1140: cell.attributes["table:style-name"]=@timeStyle
1141: cell.attributes["office:time-value"]=time2TimeVal(text)
1142: else
1143: puts("Wrong type #{type}: Doing nothing")
1144: end
1145: #-------------------------------------------
1146: # Text setzen
1147: #-------------------------------------------
1148: # Textelement bereits vorhanden ?
1149: #-------------------------------------------
1150: if(cell.elements["text:p"])
1151: cell.elements["text:p"].text=text
1152: #-------------------------------------------
1153: # nicht vorhanden (Leerzelle) -> neu anlegen
1154: #-------------------------------------------
1155: else
1156: newElement=cell.add_element("text:p")
1157: newElement.text=text
1158: end
1159: end