module Worksheet

Excel 利用のための拡張モジュール ↑

目的 ↑

ole32を利用してExcelを操作する。

Constants

ColumnWidth
Formula
HorizontalAlignment
LineStyle
MergeCells
RowHeight
Value
VerticalAlignment
WrapText

Public Instance Methods

[](y,x) click to toggle source

セル参照 ↑

sheet[y,x]

# File excel.rb, line 23
def [] y,x
  cell = self.Cells.Item(y,x)
  if cell.MergeCells
    cell.MergeArea.Item(1,1).Value
  else
    cell.Value
  end
end
[]=(y,x,value) click to toggle source

セル代入 ↑

sheet[y,x] = xx

# File excel.rb, line 35
def []= y,x,value
  cell = self.Cells.Item(y,x)
  if cell.MergeCells
    cell.MergeArea.Item(1,1).Value = value
  else
    cell.Value = value
  end
end
add_picture(py,px,file,sh,sw) click to toggle source

画像の貼り付け　point位置指定 ↑

# File excel.rb, line 219
def add_picture(py,px,file,sh,sw)
  self.Shapes.AddPicture(file,false,true,px,py,0.75*sw,0.75*sh)
end
add_picture_at_cell(cy,cx,file,sh,sw) click to toggle source

画像の貼り付け　セル位置指定 ↑

# File excel.rb, line 225
def add_picture_at_cell(cy,cx,file,sh,sw)
  r = self.Range(r_str(cy,cx))
  self.Shapes.AddPicture(file,false,true,r.Left,r.Top,0.75*sw,0.75*sh)
end
box(y1,x1,y2,x2) click to toggle source

枠線を設定 ↑

# File excel.rb, line 151
def box(y1,x1,y2,x2)
  r = r_str(y1,x1)+':'+r_str(y2,x2)
  self.Range(r).Borders.LineStyle = 1
end
center(y1,x1,y2,x2) click to toggle source

中央揃え ↑

# File excel.rb, line 173
def center(y1,x1,y2,x2)
  r = r_str(y1,x1)+':'+r_str(y2,x2)
  self.Range(r).HorizontalAlignment = -4108
end
color(y,x) click to toggle source

セルの背景色 参照 ↑

sheet.color(y,x)

# File excel.rb, line 47
def color(y,x)
    self.Cells.Item(y,x).interior.colorindex
end
copy(y1,x1,y2,x2,y3,x3) click to toggle source

コピー ↑

# File excel.rb, line 198
def copy(y1,x1,y2,x2,y3,x3)
  r2 = r_str(y3,x3)
  r = r_str(y1,x1)+':'+r_str(y2,x2)
  self.Range(r2).Copy
  self.Range(r).PasteSpecial('Paste' => -4104)
end
delete_row(n,m) click to toggle source

行の削除 ↑

# File excel.rb, line 213
def delete_row(n,m)
  self.Range("#{n}:#{m}").Delete
end
font_color(y,x) click to toggle source

セルの文字色 参照 ↑

sheet.font_color(y,x)

# File excel.rb, line 69
def font_color(y,x)
    self.Cells.Item(y,x).Font.colorindex
end
format_copy(y1,x1,y2,x2,y3,x3) click to toggle source

範囲指定の式のコピー ↑

# File excel.rb, line 180
def format_copy(y1,x1,y2,x2,y3,x3)
  r2 = r_str(y3,x3)
  r = r_str(y1,x1)+':'+r_str(y2,x2)
  self.Range(r2).Copy
  self.Range(r).PasteSpecial('Paste' => -4122)
end
format_copy1(y1,x1,y2,x2) click to toggle source

セルの式のコピー ↑

# File excel.rb, line 189
def format_copy1(y1,x1,y2,x2)
  r2 = r_str(y2,x2)
  r = r_str(y1,x1)
  self.Range(r2).Copy
  self.Range(r).PasteSpecial('Paste' => -4122)
end
formula( y,x,f) click to toggle source

セルに式を設定 ↑

# File excel.rb, line 116
def formula( y,x,f)
  r = r_str(y,x)
  self.Range(r).Formula = f
end
get_formula( y,x) click to toggle source

セルに設定された式を参照 ↑

# File excel.rb, line 123
def get_formula( y,x)
  r = r_str(y,x)
  self.Range(r).Formula
end
group_column(x1,x2) click to toggle source

カラムのグループ化 ↑

# File excel.rb, line 137
def group_column(x1,x2)
  r = r_str(1,x1)+':'+r_str(1,x2)
  self.Range(r).Columns.Group
end
group_row(y1,y2) click to toggle source

行のグループ化 ↑

# File excel.rb, line 130
def group_row(y1,y2)
  r = r_str(y1,1)+':'+r_str(y2,1)
  self.Range(r).Rows.Group
end
insert_row(n) click to toggle source

行の挿入 ↑

# File excel.rb, line 207
def insert_row(n)
  self.Rows("#{n}:#{n}").Insert('Shift' => -4121)
end
merge(y1,x1,y2,x2) click to toggle source

指定範囲をマージ ↑

# File excel.rb, line 144
def merge(y1,x1,y2,x2)
  r = r_str(y1,x1)+':'+r_str(y2,x2)
  self.Range(r).MergeCells = true
end
r_str(y,x) click to toggle source

セル位置を指定する文字列作成 ↑

# File excel.rb, line 103
def r_str(y,x)
  self.Cells.Item(y,x).address('RowAbsolute'=>false,'ColumnAbsolute'=>false)
end
select( y,x) click to toggle source

セルを選択 ↑

# File excel.rb, line 109
def select( y,x)
  r = r_str(y,x)
  self.Range(r).select
end
set_color(y,x,color) click to toggle source

セルの背景色 設定 ↑

sheet.color(y,x,color)

# File excel.rb, line 54
def set_color(y,x,color)
    self.Cells.Item(y,x).interior.colorindex = color
end
set_font_color(y,x,color) click to toggle source

セルの文字色 設定 ↑

sheet.set_font_color(y,x,color)

# File excel.rb, line 76
def set_font_color(y,x,color)
    self.Cells.Item(y,x).Font.colorindex = color
end
set_height(y,x,height) click to toggle source

行の高さ設定 ↑

# File excel.rb, line 96
def set_height(y,x,height)
    self.Cells.Item(y,x).RowHeight = height
end
set_range_color(y1,x1,y2,x2,color) click to toggle source

セルの範囲への背景色 設定 ↑

set_range_color(y1,x1,y2,x2,color)

# File excel.rb, line 61
def set_range_color(y1,x1,y2,x2,color)
  r = r_str(y1,x1)+':'+r_str(y2,x2)
  self.Range(r).interior.colorindex = color
end
set_range_font_color(y1,x1,y2,x2,color) click to toggle source

セルの範囲への背景色 設定 ↑

set_range_font_color(y1,x1,y2,x2,color)

# File excel.rb, line 83
def set_range_font_color(y1,x1,y2,x2,color)
  r = r_str(y1,x1)+':'+r_str(y2,x2)
  self.Range(r).Font.colorindex = color
end
set_width(y,x,width) click to toggle source

カラム幅設定 ↑

# File excel.rb, line 90
def set_width(y,x,width)
    self.Cells.Item(y,x).ColumnWidth = width
end
v_top(y1,x1,y2,x2) click to toggle source

上付き ↑

# File excel.rb, line 166
def v_top(y1,x1,y2,x2)
  r = r_str(y1,x1)+':'+r_str(y2,x2)
  self.Range(r).VerticalAlignment = -4160
end
wrap(y1,x1,y2,x2) click to toggle source

文字列の折り返し指定 ↑

# File excel.rb, line 158
def wrap(y1,x1,y2,x2)
  r = r_str(y1,x1)+':'+r_str(y2,x2)
  self.Range(r).HorizontalAlignment = 1
  self.Range(r).WrapText = true
end