class Ld::Sheet

Constants

ABSCISSA

Attributes

excel[RW]
sheet[RW]

Public Class Methods

create(excel, name) click to toggle source

作用 创建一个sheet

# File lib/ld/excel/sheet.rb, line 177
def self.create excel, name
  self.new excel, name, 'new'
end
new(excel, name, type = 'new') click to toggle source
# File lib/ld/excel/sheet.rb, line 11
def initialize excel, name, type = 'new'
  raise "sheet name is nil" if !name
  @excel = excel
  @name = name
  case type
    when 'new'
      @sheet = excel.create_worksheet :name => name
      @point = 'a1'
      @headings = nil
      @rows = []
    when 'open'
      @sheet = excel.worksheet name
      raise "sheet '#{name}' not found!" if !@sheet
  end
  @format = @sheet.default_format
end
open(excel, name) click to toggle source

作用 打开一个sheet

# File lib/ld/excel/sheet.rb, line 172
def self.open excel, name
  self.new excel, name, 'open'
end

Public Instance Methods

delete_maps(rows, cols, deletes) click to toggle source

作用 少读一些行或列

# File lib/ld/excel/sheet.rb, line 117
def delete_maps rows, cols, deletes
  raise "deletes 参数只能是 String" if deletes.class != String
  del_arr = deletes.split(',').map do |del|
    if del.match(/:/)
      raise "del params syntax error! \n'#{del}'" if del.split(':').size > 2
      a, b = del.split(':')
      (a..b).to_a
    else
      del
    end
  end
  del_arr.flatten.each do |del|
    if is_row? del
      rows.delete del.to_i
    else
      cols.delete del.upcase
    end
  end
end
insert_maps(rows, cols, inserts) click to toggle source

作用 多读一些行或列

# File lib/ld/excel/sheet.rb, line 96
def insert_maps rows, cols, inserts
  raise "inserts 参数只能是 String" if inserts.class != String
  insert_arr = inserts.split(',').map do |insert|
    if insert.match(/:/)
      raise "insert params syntax error! \n'#{insert}'" if insert.split(':').size > 2
      a, b = insert.split(':')
      (a..b).to_a
    else
      insert
    end
  end
  insert_arr.flatten.each do |insert|
    if is_row? insert
      rows << insert.to_i
    else
      cols << insert.upcase
    end
  end
end
insert_row(row) click to toggle source

作用 在当前sheet的主体内容末尾添加一行数据(传入一维数组),但不写入(只有调用Ld::Excel的实例方法save才会写入io)

# File lib/ld/excel/sheet.rb, line 226
def insert_row row
  raise 'insert_row 传入的必须是一个数组' if row.class != Array
  @rows << row
end
is_row?(row) click to toggle source

作用 判断要添加或要移除的是一行还是一列

# File lib/ld/excel/sheet.rb, line 164
def is_row? row
  if row.to_i.to_s == row.to_s
    return true
  end
  false
end
parse_point(point) click to toggle source

作用 解析一个字符串坐标(如‘A1’)返回x,y坐标(‘A1’返回)

# File lib/ld/excel/sheet.rb, line 199
def parse_point point
  raise "无法解析excel坐标,坐标需要是String,不能是#{point.class.to_s}" if point.class != String
  point.upcase!
  characters = point.scan(/[A-Z]+/)
  raise "parse point error! \n'#{point}'" if characters.size != 1
  numbers = point.scan(/[0-9]+/)
  raise "parse point error! \n'#{point}'" if numbers.size != 1
  {:character => characters[0], :number => numbers[0].to_i}
end
parse_string_scope(scope) click to toggle source

作用 解析范围参数

# File lib/ld/excel/sheet.rb, line 41
def parse_string_scope scope
  PARAMETER_ERROR.hint_and_raise :scope, "'+' or '-' 只能存在1个" if scope.split('+').size > 2 or scope.split('-').size > 2
  hash = {}
  scope.upcase!
  if scope.include? '+'
    hash[:scope], other = scope.split('+')
    if other.include? '-'
      hash[:insert], hash[:delete] = other.split('-')
    else
      hash[:insert] = other
    end
  else
    if scope.include? '-'
      hash[:scope], hash[:delete] = scope.split('-')
    else
      hash[:scope] = scope
    end
  end
  hash
end
read(scope, show_location = false) click to toggle source

作用 读sheet页数据,返回二维数组

# File lib/ld/excel/sheet.rb, line 29
def read scope, show_location = false
  raise "scope params is nil" if !scope
  map = read_scope_to_map scope
  read_arrs map, show_location
end
read_arrs(map_arrs, show_location) click to toggle source

作用 读二维数据(使用maps)

# File lib/ld/excel/sheet.rb, line 138
def read_arrs map_arrs, show_location
  map_arrs.map do |map_arr|
    map_arr.map do |map|
      value = read_unit_by_xy map[:col], map[:row], true
      if show_location
        {map[:location] => value}
      else
        value
      end
    end
  end
end
read_row(i) click to toggle source

作用 读取一行

# File lib/ld/excel/sheet.rb, line 36
def read_row i
  @sheet.row(i-1).to_a
end
read_scope_to_map(scope) click to toggle source

作用 使用范围参数构建maps(预读)

# File lib/ld/excel/sheet.rb, line 63
def read_scope_to_map scope
  scope = parse_string_scope scope if scope.class == String
  PARAMETER_ERROR.hint_and_raise :scope, "缺少scope参数,或':',或':'存在多个" if !scope[:scope] or !scope[:scope].match(/:/) or scope[:scope].split(':').size > 2
  a, b = scope[:scope].split(':').map{|point| parse_point point}
  cols = (a[:character]..b[:character]).to_a
  rows = (a[:number]..b[:number]).to_a
  insert_maps rows, cols, scope[:insert].upcase if scope[:insert]
  delete_maps rows, cols, scope[:delete].upcase if scope[:delete]

  if scope[:delete]
    raise "delete 参数只能是 String" if scope[:delete].class != String
  end
  rows = rows.uniq.sort
  cols = cols.uniq.sort
  maps = rows.map do |row|
    cols.map do |col|
      col_i = ABSCISSA[col]
      raise "不存在这个列 \n'#{col}'" if !col_i
      {
          location:"#{col}#{row}",
          row:row - 1,
          col:col_i
      }
    end
  end
  # 调试
  # maps.each do |arr|
  #   puts arr.map{|a| "#{a[:location]}(#{a[:row]}_#{a[:col]})"}.to_s
  # end
  maps
end
read_unit_by_xy(x, y, parse) click to toggle source

作用 通过x,y坐标获取一个单元格的内容

# File lib/ld/excel/sheet.rb, line 152
def read_unit_by_xy x, y, parse
  # puts "x: #{x}\ty: #{y}"
  unit = @sheet.row(y)[x]
  if unit.instance_of? Spreadsheet::Formula
    if parse
      return unit.value
    end
  end
  return unit
end
save() click to toggle source

作用 将数据写入sheet

# File lib/ld/excel/sheet.rb, line 182
def save
  point = parse_point @point
  raise '保存sheet必须要有内容,请 set_rows' if !@rows
  raise '保存sheet必须要有name,请 set_rows' if !@name
  @rows.unshift @headings if @headings
  @sheet.default_format = @format
  @rows.each_with_index do |row, r|
    row.each_with_index do |unit, c|
      row = point[:number] + r - 1
      col = ABSCISSA[point[:character]] + c
      write_unit_by_xy row, col, unit
    end
  end
  self
end
set_color(color) click to toggle source

作用 设置当前sheet页的字体颜色

# File lib/ld/excel/sheet.rb, line 241
def set_color color
  @format.font.color = color
end
set_font(font) click to toggle source

作用 设置当前sheet页的字体

# File lib/ld/excel/sheet.rb, line 252
def set_font font
  @format.font.name = font
end
set_font_size(size) click to toggle source

作用 设置当前sheet页的字体大小

# File lib/ld/excel/sheet.rb, line 246
def set_font_size size
  raise 'size 必须是一个整数' if size.class != Fixnum
  @format.font.size  = size
end
set_format(hash) click to toggle source
# File lib/ld/excel/sheet.rb, line 266
def set_format hash
  set_color hash[:color]
  set_font_size hash[:font_size]
  set_font hash[:font]
end
set_headings(headings) click to toggle source

作用 在当前sheet的主体内容顶上方添加一个表头(传入二维数组),但不写入(只有调用Ld::Excel的实例方法save才会写入io)

# File lib/ld/excel/sheet.rb, line 216
def set_headings headings
  if headings
    raise 'headings 必须是一个数组' if headings.class != Array
    @headings = headings
  else
    @headings = nil
  end
end
set_point(point) click to toggle source

作用 设置当前sheet页的字体颜色

# File lib/ld/excel/sheet.rb, line 262
def set_point point
  @point = point
end
set_rows(rows) click to toggle source

作用 在当前sheet中添加主体数据(传入二维数组),但不写入(只有调用Ld::Excel的实例方法save才会写入io)

# File lib/ld/excel/sheet.rb, line 210
def set_rows rows
  raise '必须是一个数组且是一个二维数组' if rows.class != Array && rows.first.class != Array
  @rows = rows
end
set_weight(weight) click to toggle source

作用 设置当前sheet页的单元格宽度(暂时无效)

# File lib/ld/excel/sheet.rb, line 257
def set_weight weight
  @format
end
write_unit_by_xy(x, y, unit) click to toggle source

作用 通过x,y坐标往一个单元格中写入数据,但不写入(只有调用Ld::Excel的实例方法save才会写入io)

# File lib/ld/excel/sheet.rb, line 232
def write_unit_by_xy x, y, unit
  if unit.class == Array
    unit = unit.to_s
    puts '提示: 有一个单元格的内容是Array, 它被当成字符串写入'
  end
  @sheet.row(x)[y] = unit
end