Class: GenSheet

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

Instance Method Summary collapse

Constructor Details

#initialize(roo) ⇒ GenSheet

Returns a new instance of GenSheet.



163
164
165
 
# File 'lib/GenSheet.rb', line 163

def initialize(roo)
  @roo = roo
end

Instance Method Details

#pre_sheet_ods(tables, sheets) ⇒ Object 



132
133
134
135
136
137
138
139
140
 
# File 'lib/GenSheet.rb', line 132

def pre_sheet_ods(tables, sheets)
  @roo.each_with_pagename do |name, sheet|
    # シート作成
    tables << (@outbook_ods.table name)
    sheets << sheet.clone
  end

  set_sheet_ods(tables, sheets)
end

#pre_sheet_xls(outsheets, originalsheets, names) ⇒ Object 



39
40
41
42
43
44
45
 
# File 'lib/GenSheet.rb', line 39

def pre_sheet_xls(outsheets, originalsheets, names)
  @roo.each_with_pagename do |name, sheet|
    outsheets << @outbook_xls.add_worksheet(name)
    originalsheets << sheet.clone
    names << name
  end
end

#set_cell_ods(row, ob_row) ⇒ Object 



152
153
154
155
156
157
158
159
160
161
 
# File 'lib/GenSheet.rb', line 152

def set_cell_ods(row, ob_row)
  row.each_with_index do |cell, x|
    # スタイル作成
    @outbook_ods.style 'font-style', :family => :cell do
      #property :text, 'font-weight' => 'bold', 'color' => '#ff0000'
    end
    # セル作成、スタイル適用
    ob_cell = ob_row.cell(cell, :style => 'font-style')
  end
end

#set_cell_xls(outsheet, cell, x, y, font, merge) ⇒ Object 



76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
 
# File 'lib/GenSheet.rb', line 76

def set_cell_xls(outsheet, cell, x, y, font, merge)
  # cellが空の場合は何もしない
  return if cell == nil

  # 各フォントフォーマットのセット
  format = set_format(font)

  # mergeしてあるセルの場合フォーマット追加
  for i in 0..merge.size - 1
    if y == merge[i][0] && x == merge[i][2]
      set_mergedcell_format(format)
    end
  end

  outsheet.write(y, x, cell, format)
end

#set_format(font) ⇒ Object 



93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
 
# File 'lib/GenSheet.rb', line 93

def set_format(font)
  format = @outbook_xls.add_format(
    #:bottom => 1,      #
    #:top => 1,         # border
    #:left => 1,        #
    #:right => 1,       #
    :color => font.instance_variable_get('@color'),
    :italic => font.instance_variable_get('@italic') ? 1 : 0,
    :font => font.instance_variable_get('@name'),
    :outline => font.instance_variable_get('@outline'),
    :shadow => font.instance_variable_get('@shadow'),
    :size => font.instance_variable_get('@size'),
    :strikeout => font.instance_variable_get('@strikeout'),
    :underline => font..instance_variable_get('@underline') == :none ? 0 : 1,  # アンダーラインの種類は4つだけどとりあえず
    :bold => font.instance_variable_get('@weight') > 400 ? 1 : 0              # weightが普通だと400、boldだと700になるようなのでとりあえず
    #:encoding => font.encoding,                    #
    #:escapement => font.escapement,                # fontにまとめて入っていたけど
    #:family => font.family,                        # どれに対応するのか..
    #:previous_fast_key => font.previous_fast_key,  #
  )

  return format
end

#set_mergedcell(outsheet, merge) ⇒ Object 



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

def set_mergedcell(outsheet, merge)
  format = @outbook_xls.add_format(:align => 'merge')
  for j in 0..merge.size - 1
    outsheet.merge_range(merge[j][0], merge[j][2], merge[j][1], merge[j][3], '', format)
  end
end

#set_mergedcell_format(format) ⇒ Object 



117
118
119
120
 
# File 'lib/GenSheet.rb', line 117

def set_mergedcell_format(format)
  format.set_align('center')
  format.set_valign('vcenter')
end

#set_mergedcell_sheet(worksheets, outsheets, merges) ⇒ Object 



47
48
49
50
51
52
53
54
 
# File 'lib/GenSheet.rb', line 47

def set_mergedcell_sheet(worksheets, outsheets, merges)
  for i in 0..worksheets.size - 1
    merges << worksheets[i].instance_variable_get('@merged_cells')
    if merges[i] != nil && merges[i] != []
      set_mergedcell(outsheets[i], merges[i])
    end
  end
end

#set_sheet_ods(tables, sheets) ⇒ Object 



142
143
144
145
146
147
148
149
150
 
# File 'lib/GenSheet.rb', line 142

def set_sheet_ods(tables, sheets)
  for i in 0..sheets.size - 1
    sheets[i].each_with_index do |row, y|
      # 行作成
      ob_row = tables[i].row
      set_cell_ods(row, ob_row)
    end
  end
end

#set_sheet_xls(outsheets, originalsheets, names, merges) ⇒ Object 



63
64
65
66
67
68
69
70
71
72
73
74
 
# File 'lib/GenSheet.rb', line 63

def set_sheet_xls(outsheets, originalsheets, names, merges)
  #@getformat = @formats[(x + 1) * (y + 1) - 1]   # fontもborderも入ってるけどもインデックスがわからない
  fonts = @roo.instance_variable_get('@fonts')
  for i in 0..originalsheets.size - 1
    originalsheets[i].each_with_index do |row, y|
      row.each_with_index do |cell, x|
        font = fonts[names[i]][[y + 1, x + 1]]
        set_cell_xls(outsheets[i], cell, x, y, font, merges[i])
      end
    end
  end
end

#to_ods(filename) ⇒ Object 



122
123
124
125
126
127
128
129
130
 
# File 'lib/GenSheet.rb', line 122

def to_ods(filename)
  @outbook_ods = ODF::SpreadSheet.new

  tables = Array.new
  sheets = Array.new
  pre_sheet_ods(tables, sheets)

  @outbook_ods.write_to filename
end

#to_xls(filename) ⇒ Object 



9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
 
# File 'lib/GenSheet.rb', line 9

def to_xls(filename)
  @outbook_xls = WriteExcel.new(filename)

  workbook = @roo.instance_variable_get('@workbook')
  formats = workbook.instance_variable_get('@formats')
  worksheets = workbook.instance_variable_get('@worksheets')

  # 出力シート準備、元シート分解
  outsheets = Array.new
  originalsheets = Array.new
  names = Array.new
  pre_sheet_xls(outsheets, originalsheets, names)

  # mergeしてあるセルのセット
  merges = Array.new
  set_mergedcell_sheet(worksheets, outsheets, merges)

  # シートのセット
  set_sheet_xls(outsheets, originalsheets, names, merges)

  # # 出力
  # for i in 0..originalsheets.size - 1
    # originalsheets[i].parse do |row|
      # puts row
    # end
  # end

  @outbook_xls.close
end