|
#!/usr/bin/env ruby
|
|
# encoding: utf-8
|
|
|
|
require 'win32ole'
|
|
|
|
class Member
|
|
def full_path xls_name
|
|
File.expand_path(xls_name.encode('gb18030')).encode('utf-8').gsub('/', "\\")
|
|
end
|
|
|
|
def book excel, txt_name, xls_name
|
|
workbook = excel.Workbooks.Add
|
|
begin
|
|
worksheet = workbook.worksheets(1)
|
|
worksheet.Name = '成员表'
|
|
worksheet.Rows.RowHeight = 20
|
|
worksheet.Range("A2").select
|
|
begin
|
|
excel.ActiveWindow.FreezePanes = true
|
|
rescue
|
|
end
|
|
worksheet.Cells.Font.Size = 9
|
|
widths = [3, 3, 3, 5.63, 7.00, 3.75, 9, 3.75, 3.75, 5.63, 3.75, 3.75, 3.75, 3.75]
|
|
titles = %w{组 号 CXO 上车点 昵称 性别 手机号码 区 街道 社区 总数 本线 对讲 奖金}
|
|
worksheet.Range("G:G").NumberFormat = '@'
|
|
('A'..'N').each_with_index do |column, index|
|
|
worksheet.Range("#{column}1").ColumnWidth = widths[index]
|
|
worksheet.Range("#{column}1").Value = titles[index]
|
|
end
|
|
line = 1
|
|
File.open(txt_name, 'r:gb18030') do |fil|
|
|
fil.each_line do |row|
|
|
line += 1
|
|
columns = row.chomp.split("\t")
|
|
('A'..'N').each_with_index do |column, index|
|
|
worksheet.Range("#{column}#{line}").Value = columns[index]
|
|
end
|
|
end
|
|
end
|
|
worksheet.Range("A1:N#{line}").HorizontalAlignment = 1
|
|
worksheet.Range("A1:N#{line}").VerticalAlignment = 2 # (1~5)
|
|
worksheet.Range("A1:N#{line}").Borders.Weight = 2
|
|
worksheet.Range("A1:N#{line}").Borders.ColorIndex = 1
|
|
worksheet.Range("A1:N#{line}").Borders.LineStyle = 1 # (1~13)
|
|
worksheet.Range("A1:N1").Interior.ColorIndex = 39
|
|
worksheet.Range("A2:N#{line}").Interior.ColorIndex = 2
|
|
workbook.saveas(full_path(xls_name))
|
|
ensure
|
|
workbook.close
|
|
end
|
|
end
|
|
|
|
def main txt_name, xls_name
|
|
WIN32OLE.codepage = WIN32OLE::CP_UTF8
|
|
excel = WIN32OLE::new('ket.Application') # http://bbs.wps.cn/thread-22427901-1-1.html
|
|
begin
|
|
# excel.AskToUpdateLinks WPS不认识
|
|
excel.Visible = excel.DisplayAlerts = excel.ScreenUpdating = false
|
|
excel.SheetsInNewWorkbook = 1
|
|
File.delete(xls_name) if File.exist?(xls_name)
|
|
book excel, txt_name, xls_name
|
|
ensure
|
|
excel.Quit
|
|
end
|
|
end
|
|
end
|
|
|
|
if $0 == __FILE__
|
|
Dir.chdir 'C:/K/Hike'
|
|
Member.new.main('simple.txt', 'simple.xls')
|
|
end
|