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
set_font(font)
click to toggle source
set_font_size(size)
click to toggle source
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
set_point(point)
click to toggle source
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
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