Class: Ld::Excel

Inherits:
Object
  • Object
show all
Defined in:
lib/ld.rb,
lib/ld/excel.rb

Instance Attribute Summary collapse

Class Method Summary collapse

Instance Method Summary collapse

Constructor Details

#initialize(path = nil) ⇒ Excel

构造函数,如果未传path则是创建一个新的excel, 如果传了path,则打开这个excel,不过打开前会验证后缀与是否存在



20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
 
# File 'lib/ld/excel.rb', line 20

def initialize(path = nil)
  if path!=nil
    if path.match(/.xls$/)!=nil
      if File::exist? path
        @excel = Spreadsheet.open path
        @path = path
        puts "打开文件:  #{path}"
      else
        raise "文件不存在:  #{path}"
      end
    else
      raise "只能打开.xls结尾的文件"
    end
  else
    @excel = Spreadsheet::Workbook.new
    puts "创建新的Excel实例"
  end
  @sheets = {}
  @sheet = nil
end

Instance Attribute Details

#basename ⇒ Object

Returns the value of attribute basename.



5
6
7
 
# File 'lib/ld/excel.rb', line 5

def basename
  @basename
end

#bean ⇒ Object

Returns the value of attribute bean.



5
6
7
 
# File 'lib/ld/excel.rb', line 5

def bean
  @bean
end

#beans ⇒ Object

Returns the value of attribute beans.



5
6
7
 
# File 'lib/ld/excel.rb', line 5

def beans
  @beans
end

#excel ⇒ Object

Returns the value of attribute excel.



5
6
7
 
# File 'lib/ld/excel.rb', line 5

def excel
  @excel
end

#format ⇒ Object

Returns the value of attribute format.



6
7
8
 
# File 'lib/ld/excel.rb', line 6

def format
  @format
end

#mappings ⇒ Object

Returns the value of attribute mappings.



5
6
7
 
# File 'lib/ld/excel.rb', line 5

def mappings
  @mappings
end

#path ⇒ Object

Returns the value of attribute path.



5
6
7
 
# File 'lib/ld/excel.rb', line 5

def path
  @path
end

#scope_arrs ⇒ Object

Returns the value of attribute scope_arrs.



5
6
7
 
# File 'lib/ld/excel.rb', line 5

def scope_arrs
  @scope_arrs
end

#sheet ⇒ Object

Returns the value of attribute sheet.



5
6
7
 
# File 'lib/ld/excel.rb', line 5

def sheet
  @sheet
end

#sheets ⇒ Object

Returns the value of attribute sheets.



5
6
7
 
# File 'lib/ld/excel.rb', line 5

def sheets
  @sheets
end

Class Method Details

.create(path, sheets) ⇒ Object 



566
567
568
569
570
571
572
 
# File 'lib/ld/excel.rb', line 566

def self.create(path, sheets)
  e = Excel.new
  sheets.each do |sheet|
    e.write_sheet sheet
  end
  e.save path
end

.open(path) ⇒ Object 



41
42
43
 
# File 'lib/ld/excel.rb', line 41

def self.open(path)
  self.new(path)
end

.write_excel(arrs_list, excel_name) ⇒ Object 



558
559
560
561
562
563
564
 
# File 'lib/ld/excel.rb', line 558

def self.write_excel(arrs_list,excel_name)
  new_excel = Excel.new
  arrs_list.each_with_index do |arrs,i|
    new_excel.write_sheet arrs,'sheet'+(i+1).to_s,:red
  end
  new_excel.save_to_complete excel_name
end

.write_excel2(arrs, excel_name) ⇒ Object 



574
575
576
577
578
579
580
581
 
# File 'lib/ld/excel.rb', line 574

def self.write_excel2(arrs,excel_name)
  new_excel = Excel.new
  new_excel.write_sheet arrs,'sheet1',:red
  new_excel.save_to excel_name
rescue
  puts $!
  puts $@
end

Instance Method Details

#ab_to(a, b) ⇒ Object 



160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
 
# File 'lib/ld/excel.rb', line 160

def ab_to a, b
  type = nil
  if is_number?(a) == true and is_number?(b) == true
    type = 'y'
    case a.to_i <=> b.to_i
      when 1
        return [type, (b..a).to_a]
      when -1
        return [type, (a..b).to_a]
      when 0
        return [type, [a]]
    end
  elsif is_number?(a) == false and is_number?(b) == false
    type = 'x'
    case a <=> b
      when 1
        return [type, (b..a).to_a]
      when -1
        return [type, (a..b).to_a]
      when 0
        return [type, [a]]
    end
  else
    raise "解析excel配置范围时,':'两边必须要么都是字母,要么都是数字!"
  end
end

#flush ⇒ Object 



129
130
131
 
# File 'lib/ld/excel.rb', line 129

def flush
  Excel.new self.path
end

#generate_map(address_str) ⇒ Object

用坐标解析一个excel scope



237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
 
# File 'lib/ld/excel.rb', line 237

def generate_map address_str
  map = {:x => [], :y => []}
  config = parse_address address_str
  if config[:scope]
    if config[:scope].include? ':'
      # map初始化
      arr = config[:scope].split(':')
      if config[:scope].scan(/[0-9]+/).join == ''
        map_adds(map, ab_to(arr[0].scan(/[A-Z]+/).join, arr[1].scan(/[A-Z]+/).join))
      elsif config[:scope].scan(/[A-Z]+/).join == ''
        map_adds(map, ab_to(arr[0].scan(/[0-9]+/).join, arr[1].scan(/[0-9]+/).join))
      else
        map_adds(map, ab_to(arr[0].scan(/[0-9]+/).join, arr[1].scan(/[0-9]+/).join))
        map_adds(map, ab_to(arr[0].scan(/[A-Z]+/).join, arr[1].scan(/[A-Z]+/).join))
      end
      # map 添加
      if config[:add_str]
        config[:add_str].split(',').each do |add|
          if add.include? ":"
            map_adds(map, ab_to(add.split(':')[0], add.split(':')[1]))
          else
            map_add map, add
          end
        end
      end
      # map 减小
      if config[:min_str]
        config[:min_str].split(',').each do |min|
          if min.include? ":"
            map_mins(map, ab_to(min.split(':')[0], min.split(':')[1]))
          else
            map_min map, min
          end
        end
      end
    else
      raise "scope 没有 ':' 无法解析"
    end
  else
    raise "scope == nil"
  end
  map[:x].uniq!
  map[:y].uniq!
  arrs = []
  map[:y].each do |y|
    rows = []
    map[:x].each do |x|
      rows << ["#{x}_#{y}", ZIMU[x], y.to_i - 1]
    end
    arrs << rows
  end
  return arrs
rescue
  puts "生成map时发生错误: #{$!}"
  puts $@
end

#get_examples ⇒ Object

获取第一列的所有单元格格式颜色arr



506
507
508
509
510
511
512
 
# File 'lib/ld/excel.rb', line 506

def get_examples
  @sheet.row(4).formats[0].pattern_fg_color == :red
  cols = excel.sheet.column(0)
  cols.each do |col|
    puts col
  end
end

#get_sheets ⇒ Object

获取所有页



57
58
59
60
61
 
# File 'lib/ld/excel.rb', line 57

def get_sheets
  @sheets = @excel.worksheets
  puts "返回 #{@sheets.size} 页"
  self
end

#info ⇒ Object

打印信息



492
493
494
495
496
497
498
499
500
501
502
503
 
# File 'lib/ld/excel.rb', line 492

def info
  if File.exist?(@path)
    @sheets ||= @excel.worksheets
    @sheets.each_with_index do |sheet,i|
      puts "第#{i}个sheet,name:#{sheet.name}"
    end
    puts "Excel文件size:#{File.size @path},sheet数:#{@sheets.size},文件path:#{@path}"
  else
    puts "不存在的文件,#{@path}"
  end
  self
end

#is_number?(str) ⇒ Boolean

Returns:

  • (Boolean) 


229
230
231
232
233
234
 
# File 'lib/ld/excel.rb', line 229

def is_number? str
  if str.to_i.to_s == str.to_s
    return true
  end
  false
end

#jiang(arr) ⇒ Object

排序



386
387
388
389
390
391
392
393
394
395
396
397
 
# File 'lib/ld/excel.rb', line 386

def jiang arr
  0.upto(arr.size - 2) do |i|
    (i+1).upto(arr.size - 1) do |j|
      if arr[i] < arr[j]
        arr[i] = arr[i] + arr[j]
        arr[j] = arr[i] - arr[j]
        arr[i] = arr[i] - arr[j]
      end
    end
  end
  arr
end

#map_add(map, add) ⇒ Object 



200
201
202
203
204
205
206
 
# File 'lib/ld/excel.rb', line 200

def map_add map, add
  if is_number? add
    map[:y] << add
  else
    map[:x] << add
  end
end

#map_adds(map, adds) ⇒ Object 



187
188
189
190
191
192
193
194
195
196
197
198
 
# File 'lib/ld/excel.rb', line 187

def map_adds map, adds
  case adds[0]
    when 'x'
      adds[1].each do |add|
        map[:x] << add
      end
    when 'y'
      adds[1].each do |add|
        map[:y] << add
      end
  end
end

#map_min(map, min) ⇒ Object 



221
222
223
224
225
226
227
 
# File 'lib/ld/excel.rb', line 221

def map_min map, min
  if is_number? min
    map[:y].delete min
  else
    map[:x].delete min
  end
end

#map_mins(map, mins) ⇒ Object 



208
209
210
211
212
213
214
215
216
217
218
219
 
# File 'lib/ld/excel.rb', line 208

def map_mins map, mins
  case mins[0]
    when 'x'
      mins[1].each do |min|
        map[:x].delete min
      end
    when 'y'
      mins[1].each do |min|
        map[:y].delete min
      end
  end
end

#new_sheet(sheet_name) ⇒ Object

创建新页(先查有没有该页)



64
65
66
67
68
 
# File 'lib/ld/excel.rb', line 64

def new_sheet(sheet_name)
  @sheet = @excel.create_worksheet(:name => sheet_name)
  # puts "创建了一页    #{sheet_name}"
  self
end

#open_new ⇒ Object

刷新excel中的sheet



85
86
87
 
# File 'lib/ld/excel.rb', line 85

def open_new
  excel_new = Excel.open @path
end

#open_sheet(sheet_name) ⇒ Object

获取一页



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

def open_sheet sheet_name
  @sheet = @excel.worksheet sheet_name
  if @sheet == nil
    raise "未找到 sheet #{sheet_name}"
  else
    # puts "sheet #{sheet_name}"
  end
  self
end

#parse_address(address) ⇒ Object

解析范围配置



296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
 
# File 'lib/ld/excel.rb', line 296

def parse_address address
  hash = {}
  if address
    address.upcase!
  else
    raise "address 为 nil"
  end
  if address.split('+').size > 2
    raise "'+'号只能有1个"
  end
  if address.split('-').size > 2
    raise "'-'号只能有1个"
  end
  if address.include?('+')
    a = address.split('+')[0]
    b = address.split('+')[1]
    if a.include?('-')
      hash.store :scope, a.split('-')[0]
      hash.store :min_str, a.split('-')[1]
      hash.store :add_str, b
    else
      hash.store :scope, a
      if b.include?('-')
        hash.store :min_str, b.split('-')[1]
        hash.store :add_str, b.split('-')[0]
      else
        hash.store :add_str, b
      end
    end
  else
    if address.include?('-')
      hash.store :scope, address.split('-')[0]
      hash.store :min_str, address.split('-')[1]
    else
      hash.store :scope, address
    end
  end
  hash
end

#parse_del_to_hash(address, scope) ⇒ Object 



133
134
135
136
137
138
139
140
141
142
143
144
145
 
# File 'lib/ld/excel.rb', line 133

def parse_del_to_hash address, scope
  arr = address.split '-'
  arr = arr[1..arr.size-1]
  start_row_num = scope.scan(/\d/).join[0..1]# 首行行号
  location = parse_location(scope.split(':')[0])
  hash = {}
  del_rows = []
  address.each do |del_row_num|# 去除行行号
    rows << del_row_num.to_i - start_row_num.to_i# 去除行行号 - 首行行号 = 数组角标位置
  end
  hash.store(:rows,jiang(rows))
  hash
end

#parse_location(location) ⇒ Object

解析一个excel location



148
149
150
151
152
153
154
155
156
157
158
 
# File 'lib/ld/excel.rb', line 148

def parse_location location
  if location and location.class == String
    location.upcase!
    {
        :x => ZIMU[location.scan(/[A-Z]+/).join].to_i,
        :y => (location.scan(/[0-9]+/).join.to_i - 1)
    }
  else
    ms.puts_fail "location为空或不是String类型,无法解析"
  end
end

#read_location(location, parse = true) ⇒ Object

读一个单元格



71
72
73
74
75
 
# File 'lib/ld/excel.rb', line 71

def read_location(location,parse = true)
  l = parse_location(location)
  unit = read_unit_by_xy(l[:r],l[:c],parse)
  # puts ""
end

#read_location_list_arr(location_list_arr_str) ⇒ Object

读很多个location链,返回二维数组



90
91
92
93
94
95
96
97
 
# File 'lib/ld/excel.rb', line 90

def read_location_list_arr(location_list_arr_str)
  units_arr = []
  location_list_arr_str.split(',').each do |location_list|
    units = read_location_list(location_list)
    units_arr << units
  end
  units_arr
end

#read_map(arrs, simple = true) ⇒ Object 



369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
 
# File 'lib/ld/excel.rb', line 369

def read_map arrs, simple = true
  @scope_arrs = []
  arrs.each do |arr|
    rows = []
    arr.each do |a|
      if simple
        rows << read_unit_by_xy(a[1], a[2], true)
      else
        rows << {:index => a[0], :value => read_unit_by_xy(a[1], a[2], true)}
      end
    end
    @scope_arrs << rows
  end
  @scope_arrs
end

#read_sheet_location(location, parse = true) ⇒ Object

读一个单元格2



78
79
80
81
 
# File 'lib/ld/excel.rb', line 78

def read_sheet_location(location,parse = true)
  open_sheet location.split('?')[0]
  read_location location.split('?')[1]
end

#read_sheet_locations(locations_config, parse = true) ⇒ Object

读取一个location链 ,返回一维数组



100
101
102
103
104
105
106
107
108
109
 
# File 'lib/ld/excel.rb', line 100

def read_sheet_locations(locations_config,parse = true)
  unit_list = []
  open_sheet locations_config.split('?')[0]
  locations_config.split('?')[1].split('.').each do |location|
    l = parse_location(location)
    unit = read_unit_by_xy(l[:r],l[:c],parse)
    unit_list << unit
  end
  unit_list
end

#read_sheet_scope(full_scope, simple = true, filter_nil = false) ⇒ Object

先打开一个sheet页,再读scope范围数据params?b13:m27-g.j.k.(14:18)



338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
 
# File 'lib/ld/excel.rb', line 338

def read_sheet_scope full_scope, simple = true, filter_nil = false
  if full_scope.include?('?')
    sheet_name = full_scope.split('?')[0]
    if sheet_name
      open_sheet sheet_name
    else
      raise "sheetname为nil"
    end
    address_str = full_scope.split('?')[1]
    map = generate_map address_str
    data_arrs = read_map map, simple
    if data_arrs.size == 0
      puts "没有任何内容的区域!  #{full_scope}"
    else
      puts "#{full_scope}"
    end
    # 除去不完整数据
    if filter_nil == true
      (data_arrs.size - 1).downto(0) do |i|
        arr = data_arrs[i]
        if arr[0] == nil or arr[1] == nil
          data_arrs.delete_at i
        end
      end
    end
    return data_arrs
  else
    raise "缺少?,需要在'?'左边指定sheet的名称"
  end
end

#read_unit_by_xy(x, y, parse) ⇒ Object

通过x,y坐标获取unit内容



118
119
120
121
122
123
124
125
126
127
 
# File 'lib/ld/excel.rb', line 118

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

#reload ⇒ Object

重新加载文件数据



515
516
517
518
519
520
521
522
523
524
525
526
527
528
529
530
531
 
# File 'lib/ld/excel.rb', line 515

def reload
  if @path!=nil
    if File.exist? @path
      @excel = Spreadsheet.open @path
      @sheets = @excel.worksheets
      @sheet = nil
      @basename = nil
      puts "reload成功    #{@path}"
    else
      raise "#{path},这个文件不存在,无法reload"
    end

  else
    raise "@path==nil,无法reload"
  end
  self
end

#save(path = nil) ⇒ Object

保存文件



443
444
445
446
447
448
449
450
451
452
453
454
455
456
457
458
459
460
461
462
 
# File 'lib/ld/excel.rb', line 443

def save(path = nil)
  flag = 1
  if path==nil
    if File.exist? @path
      @excel.write @path
      puts "保存覆盖了一个同名文件   #{@path}"
    else
      @excel.write @path
      puts "保存到:     #{@path}"
    end
  else
    @excel.write path
    if File.exist? path
      puts "保存到:    #{path}"
    else
      raise "保存失败!"
    end
  end
  self
end

#save_to(path_name) ⇒ Object 



482
483
484
485
486
487
488
489
 
# File 'lib/ld/excel.rb', line 482

def save_to(path_name)
  if path_name.match(/.xls$/)!=nil
    save(path_name)
  else
    raise "要以.xls结尾"
  end
  self
end

#save_to_complete(basename) ⇒ Object 



473
474
475
476
477
478
479
480
 
# File 'lib/ld/excel.rb', line 473

def save_to_complete(basename)
  if basename.match(/.xls$/)!=nil
    save(@@base_path + "complete/" + basename)
  else
    raise "要以.xls结尾"
  end
  self
end

#save_to_source(basename) ⇒ Object 



464
465
466
467
468
469
470
471
 
# File 'lib/ld/excel.rb', line 464

def save_to_source(basename)
  if basename.match(/.xls$/)!=nil
    save(@@base_path + "source/" + basename)
  else
    raise "要以.xls结尾"
  end
  self
end

#set_default_format(font_color = :black) ⇒ Object 



533
534
535
536
537
538
539
540
541
542
543
544
545
546
547
548
549
 
# File 'lib/ld/excel.rb', line 533

def set_default_format(font_color = :black)
  if @sheet!=nil
    @format = Spreadsheet::Format.new(
        :color => :blue,
        :weight => :bold,
        :size => 11
    )
    @format.font.color = font_color
    @format.font.name = "微软雅黑"
    @format.font.size = 11
    @sheet.default_format = @format
    # puts "设置默认格式成功  #{@sheet.name}"
  else
    raise "@sheet==nil,无法获取默认格式,无法设置默认格式"
  end
  @format
end

#sheng(arr) ⇒ Object 



399
400
401
402
403
404
405
406
407
408
409
410
 
# File 'lib/ld/excel.rb', line 399

def sheng arr
  0.upto(arr.size - 2) do |i|
    (i+1).upto(arr.size - 1) do |j|
      if arr[i] > arr[j]
        arr[i] = arr[i] + arr[j]
        arr[j] = arr[i] - arr[j]
        arr[i] = arr[i] - arr[j]
      end
    end
  end
  arr
end

#write_arr_to_point(arr, rank = '|', point = "a1") ⇒ Object

将一维数组写到表中,可写成列,也可以写成行



424
425
426
427
428
429
430
431
432
433
434
435
436
437
438
439
440
 
# File 'lib/ld/excel.rb', line 424

def write_arr_to_point(arr, rank = '|', point = "a1")
  l = parse_location(point)
  if rank == '|' or rank == 'col'
    arr.each_with_index do |data,r|
      # 坚写,行动列不动
      write_unit_by_xy(l[:r]+r,l[:c],data)
    end
  elsif rank == '-' or rank == 'row'
    arr.each_with_index do |data,c|
      # 横写,列动行不动
      write_unit_by_xy(l[:r],l[:c]+c,data)
    end
  else
    raise "横写rank |  竖写rank -   无法识别#{rank}"
  end
  self
end

#write_arrs_to_point(arrs, point = "a1") ⇒ Object

将二维数组写到表中



413
414
415
416
417
418
419
420
421
 
# File 'lib/ld/excel.rb', line 413

def write_arrs_to_point(arrs,point = "a1")
  l = parse_location(point)
  arrs.each_with_index do |arr,r|
    arr.each_with_index do |data,c|
      write_unit_by_xy(r+l[:x],c+l[:y],data)
    end
  end
  self
end

#write_sheet(sheet) ⇒ Object 



551
552
553
554
555
556
 
# File 'lib/ld/excel.rb', line 551

def write_sheet(sheet)
  new_sheet sheet[:name]
  set_default_format(sheet[:color].nil? ? 'black' : sheet[:color])
  write_arrs_to_point(sheet[:arrs], sheet[:point].nil? ? "a1" : sheet[:point])
  sheet
end

#write_unit_by_xy(x, y, unit) ⇒ Object

通过xy坐标往unit写内容



112
113
114
115
 
# File 'lib/ld/excel.rb', line 112

def write_unit_by_xy x, y, unit
  unit = unit.to_s if unit.class == Array
  @sheet.row(x)[y] = unit
end