lib/aio/base/toolkit/excel_wps.rb in aio_elin-1.1.1 vs lib/aio/base/toolkit/excel_wps.rb in aio_elin-1.1.2
- old
+ new
@@ -1,438 +1,437 @@
module Aio::Base::Toolkit
-module ExcelWps
- class WorkBook
+ module ExcelWps
+ class WorkBook
- include Aio::Ui::Verbose
+ include Aio::Ui::Verbose
- @@worksheets_name = []
- def initialize(encoding = "utf-8")
+ @@worksheets_name = []
+ def initialize(encoding = "utf-8")
- if Aio::Base::Toolkit::OS.windows?
- require "win32ole"
- else
- debugger
- print_error "只有Windows系统才能使用Excel模块"
- exit 0
- end
+ if Aio::Base::Toolkit::OS.windows?
+ require "win32ole"
+ else
+ print_error "只有Windows系统才能使用Excel模块"
+ exit 0
+ end
- @excel = WIN32OLE.new("excel.Application")
- @excel.visible = false
- @workbook = @excel.workbooks.add
- @encoding = encoding
- create_style
- end
+ @excel = WIN32OLE.new("excel.Application")
+ @excel.visible = false
+ @workbook = @excel.workbooks.add
+ @encoding = encoding
+ create_style
+ end
- # 警告提示开关
- def display_alerts=(bool)
- @excel.DisplayAlerts = bool
- end
+ # 警告提示开关
+ def display_alerts=(bool)
+ @excel.DisplayAlerts = bool
+ end
- def add_worksheet(name)
- while @@worksheets_name.include?(name)
- name += "1"
- end
- @@worksheets_name << name
- worksheet = @workbook.worksheets.add
- worksheet.activate
+ def add_worksheet(name)
+ while @@worksheets_name.include?(name)
+ name += "1"
+ end
+ @@worksheets_name << name
+ worksheet = @workbook.worksheets.add
+ worksheet.activate
- # 在同一进程中多次打开会出现name的问题, 所以干脆全部使用sheet
- # worksheet.name = name
- worksheet.name = "sheet"
- return WorkSheet.new(worksheet)
- end
+ # 在同一进程中多次打开会出现name的问题, 所以干脆全部使用sheet
+ # worksheet.name = name
+ worksheet.name = "sheet"
+ return WorkSheet.new(worksheet)
+ end
- def create_style
- sty = @workbook.styles.add("NormalStyle")
- self.class.normal_style(sty)
+ def create_style
+ sty = @workbook.styles.add("NormalStyle")
+ self.class.normal_style(sty)
- sty = @workbook.styles.add("BoldStyle")
- self.class.bold_style(sty)
+ sty = @workbook.styles.add("BoldStyle")
+ self.class.bold_style(sty)
- sty = @workbook.styles.add("TitleStyle")
- self.class.title_style(sty)
- end
+ sty = @workbook.styles.add("TitleStyle")
+ self.class.title_style(sty)
+ end
- def self.normal_style(sty)
- sty.font.size = 9
- sty.borders(7).linestyle = 1
- sty.borders(8).linestyle = 1
- sty.borders(9).linestyle = 1
- sty.borders(10).linestyle = 1
- sty.HorizontalAlignment = -4108
- end
+ def self.normal_style(sty)
+ sty.font.size = 9
+ sty.borders(7).linestyle = 1
+ sty.borders(8).linestyle = 1
+ sty.borders(9).linestyle = 1
+ sty.borders(10).linestyle = 1
+ sty.HorizontalAlignment = -4108
+ end
- def self.bold_style(sty)
- sty.font.size = 9
- sty.font.bold = true
- sty.borders(7).linestyle = 1
- sty.borders(8).linestyle = 1
- sty.borders(9).linestyle = 1
- sty.borders(10).linestyle = 1
- sty.HorizontalAlignment = -4108
- end
+ def self.bold_style(sty)
+ sty.font.size = 9
+ sty.font.bold = true
+ sty.borders(7).linestyle = 1
+ sty.borders(8).linestyle = 1
+ sty.borders(9).linestyle = 1
+ sty.borders(10).linestyle = 1
+ sty.HorizontalAlignment = -4108
+ end
- def self.title_style(sty)
- sty.font.size = 20
- sty.font.bold = true
- sty.borders(7).linestyle = 1
- sty.borders(8).linestyle = 1
- sty.borders(9).linestyle = 1
- sty.borders(10).linestyle = 1
- sty.HorizontalAlignment = -4108
- end
-
- def show
- @excel.visible = true
- end
+ def self.title_style(sty)
+ sty.font.size = 20
+ sty.font.bold = true
+ sty.borders(7).linestyle = 1
+ sty.borders(8).linestyle = 1
+ sty.borders(9).linestyle = 1
+ sty.borders(10).linestyle = 1
+ sty.HorizontalAlignment = -4108
+ end
- def save(path)
- @workbook.saveas(path)
- end
+ def show
+ @excel.visible = true
+ end
- def close
- @workbook.close
- @excel.quit
- end
- end # class WorkBook
+ def save(path)
+ @workbook.saveas(path)
+ end
- class WorkSheet
- IMAGE_ROW_NUM = 56
- @@worksheets_name = []
- def initialize(worksheet)
- @row_count = 1
- @worksheet = worksheet
- @nil_space = []
- end
+ def close
+ @workbook.close
+ @excel.quit
+ end
+ end # class WorkBook
- # 增加一个空行
- def add_space_line(n=1)
- return if n < 1
- @row_count += n
- end
+ class WorkSheet
+ IMAGE_ROW_NUM = 56
+ @@worksheets_name = []
+ def initialize(worksheet)
+ @row_count = 1
+ @worksheet = worksheet
+ @nil_space = []
+ end
- # 对列进行合并
- def merge(range1, range2)
- @worksheet.range("#{range1}:#{range2}").merge
- end
+ # 增加一个空行
+ def add_space_line(n=1)
+ return if n < 1
+ @row_count += n
+ end
- # 产生 ::Range 类
- def range(str)
- @worksheet.range(str)
- end
+ # 对列进行合并
+ def merge(range1, range2)
+ @worksheet.range("#{range1}:#{range2}").merge
+ end
- # 添加标题行
- def add_title(name)
- add_row.add_cell(name, false, "BoldStyle")
- end
+ # 产生 ::Range 类
+ def range(str)
+ @worksheet.range(str)
+ end
- # 设置列的宽度
- def width(col, width)
- @worksheet.Columns("#{col}:#{col}").ColumnWidth = width
- end
+ # 添加标题行
+ def add_title(name)
+ add_row.add_cell(name, false, "BoldStyle")
+ end
- def height(height)
- @row_height = height
- end
-
- # 增加Row类
- def add_row(&block)
- @current_row = Row.new(@worksheet, @row_count)
- @current_row.height = @row_height if @row_height
- @row_count += 1
- yield @current_row if block
- return @current_row
- end
+ # 设置列的宽度
+ def width(col, width)
+ @worksheet.Columns("#{col}:#{col}").ColumnWidth = width
+ end
- # 返回此时的Row类
- def current_row
- return @current_row
- end
+ def height(height)
+ @row_height = height
+ end
- # 返回此时的行索引
- def current_row_id
- return @current_row.row_id
- end
+ # 增加Row类
+ def add_row(&block)
+ @current_row = Row.new(@worksheet, @row_count)
+ @current_row.height = @row_height if @row_height
+ @row_count += 1
+ yield @current_row if block
+ return @current_row
+ end
- # 添加图像
- def add_image(image_path)
- return unless File.exist?(image_path)
- add_space_line
- add_row
- cell_name = current_row.first_cell
- @worksheet.Range(cell_name).Select
- @worksheet.Pictures.Insert(image_path)
- add_space_line IMAGE_ROW_NUM
- end
+ # 返回此时的Row类
+ def current_row
+ return @current_row
+ end
- # 判断是否有垂直分页符存在
- def has_pagebreak?
- @worksheet.VPageBreaks.count > 0
- end
+ # 返回此时的行索引
+ def current_row_id
+ return @current_row.row_id
+ end
- # 对列修改分页符
- def pagebreak(col)
- @worksheet.VPageBreaks(1).Location = @worksheet.columns(col)
- end
+ # 添加图像
+ def add_image(image_path)
+ return unless File.exist?(image_path)
+ add_space_line
+ add_row
+ cell_name = current_row.first_cell
+ @worksheet.Range(cell_name).Select
+ @worksheet.Pictures.Insert(image_path)
+ add_space_line IMAGE_ROW_NUM
+ end
- # 返回::WorkSheet
- def worksheet
- @worksheet
- end
+ # 判断是否有垂直分页符存在
+ def has_pagebreak?
+ @worksheet.VPageBreaks.count > 0
+ end
- # 添加图表
- def add_chart(&block)
- ch = @worksheet.Shapes.AddChart
- active = ch.chart
+ # 对列修改分页符
+ def pagebreak(col)
+ @worksheet.VPageBreaks(1).Location = @worksheet.columns(col)
+ end
- # 占位符
- block.call(Chart.new(active))
+ # 返回::WorkSheet
+ def worksheet
+ @worksheet
+ end
- ch.copy
- end
- end # SheetWork Class
+ # 添加图表
+ def add_chart(&block)
+ ch = @worksheet.Shapes.AddChart
+ active = ch.chart
- class Row
- FILL_TYPE = 4
+ # 占位符
+ block.call(Chart.new(active))
- attr_reader :row_id
+ ch.copy
+ end
+ end # SheetWork Class
- @@cell_map = ("A".."Z").to_a
- def initialize(worksheet, row_id)
- @row_id = row_id
- @cell_count = 0
- @worksheet = worksheet
- @nil_space = []
- end
-
- # 此时的单元格
- def curent_cell
- return cell_name(@cell_count)
- end
+ class Row
+ FILL_TYPE = 4
- # 第一个单元格
- def first_cell
- return cell_name(0)
- end
+ attr_reader :row_id
- # 设置行高
- def height=(height)
- @worksheet.rows(@row_id).RowHeight = height
- end
+ @@cell_map = ("A".."Z").to_a
+ def initialize(worksheet, row_id)
+ @row_id = row_id
+ @cell_count = 0
+ @worksheet = worksheet
+ @nil_space = []
+ end
- # 增加单元格
- def add_cell(value, auto_fit = false, style = "NormalStyle")
- range = @worksheet.Range(cell_name(@cell_count))
- range.Value = value.to_s
- range.Style = style
- range.Columns.AutoFit if auto_fit
- @cell_count += 1
- while(@nil_space.include?(to_letter(@cell_count))) do
- range = @worksheet.Range(cell_name(@cell_count))
- range.Value = ""
- range.Style = style
- @cell_count += 1
- end
-
- end
+ # 此时的单元格
+ def curent_cell
+ return cell_name(@cell_count)
+ end
- # 通过索引变成对应的字母
- def to_letter(index)
- @@cell_map.at(index)
- end
+ # 第一个单元格
+ def first_cell
+ return cell_name(0)
+ end
- # 特别注意,由于Toolkit中加入了Array,String的模块
- # 所以判断的时候特别注意要是
- def << (arr)
- case arr
- when ::Array
- arr.size.times do |t|
- add_cell(arr[t])
- end
- when ::String
- add_cell(arr)
- end
- end
+ # 设置行高
+ def height=(height)
+ @worksheet.rows(@row_id).RowHeight = height
+ end
- # 获得单元格的名字(通过索引)
- def cell_name(index)
- second = index % 26
- first = (index - second) / 26
- if first == 0
- return @@cell_map[second] + @row_id.to_s
- end
- first -= 1
- return @@cell_map[first] + @@cell_map[second] + @row_id.to_s
- end
+ # 增加单元格
+ def add_cell(value, auto_fit = false, style = "NormalStyle")
+ range = @worksheet.Range(cell_name(@cell_count))
+ range.Value = value.to_s
+ range.Style = style
+ range.Columns.AutoFit if auto_fit
+ @cell_count += 1
+ while(@nil_space.include?(to_letter(@cell_count))) do
+ range = @worksheet.Range(cell_name(@cell_count))
+ range.Value = ""
+ range.Style = style
+ @cell_count += 1
+ end
- # 获得单元格的名字(通过字母)
- def cell_name!(letter)
- return letter + @row_id.to_s
- end
+ end
- def set_cell(index, value, auto_fit = false, style = "NormalStyle")
- range = @worksheet.Range(cell_name(index))
- range.Value = value.to_s
- range.Style = style
- range.Columns.AutoFit if auto_fit
- end
+ # 通过索引变成对应的字母
+ def to_letter(index)
+ @@cell_map.at(index)
+ end
- # 设置单元格风格
- def set_style(letter, style = "NormalStyle")
- range = @worksheet.range(cell_name!(letter))
- range.Style = style
- end
+ # 特别注意,由于Toolkit中加入了Array,String的模块
+ # 所以判断的时候特别注意要是
+ def <<(arr)
+ case arr
+ when ::Array
+ arr.size.times do |t|
+ add_cell(arr[t])
+ end
+ when ::String
+ add_cell(arr)
+ end
+ end
- # 合并一行中的单元格
- def merge(idx_begin, idx_end)
- cell_begin = "#{idx_begin}#{@row_id}"
- cell_end = "#{idx_end}#{@row_id}"
- range = @worksheet.Range("#{cell_begin}:#{cell_end}")
- range.merge
- tmp = ((idx_begin.upcase)..(idx_end.upcase)).to_a
- tmp.shift
- @nil_space = (@nil_space | tmp).sort
- end
+ # 获得单元格的名字(通过索引)
+ def cell_name(index)
+ second = index % 26
+ first = (index - second) / 26
+ if first == 0
+ return @@cell_map[second] + @row_id.to_s
+ end
+ first -= 1
+ return @@cell_map[first] + @@cell_map[second] + @row_id.to_s
+ end
- # 开启自动换行
- def wraptext(letter)
- range = @worksheet.range(cell_name!(letter))
- range.WrapText = true
- end
+ # 获得单元格的名字(通过字母)
+ def cell_name!(letter)
+ return letter + @row_id.to_s
+ end
- # 对此时的行的下方下分页符
- def pagebreak
- @worksheet.rows(@row_id+1).PageBreak = 1
- end
+ def set_cell(index, value, auto_fit = false, style = "NormalStyle")
+ range = @worksheet.Range(cell_name(index))
+ range.Value = value.to_s
+ range.Style = style
+ range.Columns.AutoFit if auto_fit
+ end
- # 返回此时的::Row类
- def real_row
- @worksheet.rows(@row_id)
- end
-
- alias :style :set_style
- end
+ # 设置单元格风格
+ def set_style(letter, style = "NormalStyle")
+ range = @worksheet.range(cell_name!(letter))
+ range.Style = style
+ end
- # 图表
- class Chart
+ # 合并一行中的单元格
+ def merge(idx_begin, idx_end)
+ cell_begin = "#{idx_begin}#{@row_id}"
+ cell_end = "#{idx_end}#{@row_id}"
+ range = @worksheet.Range("#{cell_begin}:#{cell_end}")
+ range.merge
+ tmp = ((idx_begin.upcase)..(idx_end.upcase)).to_a
+ tmp.shift
+ @nil_space = (@nil_space | tmp).sort
+ end
- # 图形类型
- ColumnClustered = 51 # 簇状柱形图
- ColumnStacked = 52 # 堆积柱状图
- Doughnut = -4120 # 圆环图
- Line = 4 # 折线图
- Pie = 5 # 饼图
- BarClustered = 57 # 簇状条形图
+ # 开启自动换行
+ def wraptext(letter)
+ range = @worksheet.range(cell_name!(letter))
+ range.WrapText = true
+ end
- # 标签数据
- DataLabelsShowLabel = 4 # 数据点所属的分类
- DataLabelsShowLabelAndPercent = 5 # 占比百分比以及所属分类,仅饼图
- DataLabelsShowPercent = 3 # 百分比, 仅饼图
- DataLabelsShowValue = 2 # 默认值
+ # 对此时的行的下方下分页符
+ def pagebreak
+ @worksheet.rows(@row_id+1).PageBreak = 1
+ end
+ # 返回此时的::Row类
+ def real_row
+ @worksheet.rows(@row_id)
+ end
- def initialize(active)
- @chart = active
- end
+ alias :style :set_style
+ end
- def chart_work
- @chart
- end
+ # 图表
+ class Chart
- # 标签选项
- def now_table
- #@chart.SeriesCollection(n).DataLabels
- num = @chart.SeriesCollection.Count
- num.times do |t|
- t += 1
- yield @chart.SeriesCollection(t).DataLabels
- end
- end
+ # 图形类型
+ ColumnClustered = 51 # 簇状柱形图
+ ColumnStacked = 52 # 堆积柱状图
+ Doughnut = -4120 # 圆环图
+ Line = 4 # 折线图
+ Pie = 5 # 饼图
+ BarClustered = 57 # 簇状条形图
- # 修改标题
- def title=(name)
- @chart.HasTitle = true
- @chart.ChartTitle.Characters.Text = name
- end
+ # 标签数据
+ DataLabelsShowLabel = 4 # 数据点所属的分类
+ DataLabelsShowLabelAndPercent = 5 # 占比百分比以及所属分类,仅饼图
+ DataLabelsShowPercent = 3 # 百分比, 仅饼图
+ DataLabelsShowValue = 2 # 默认值
- # 这是原数据地址, 以每列数据作为一个系列
- def source=(range)
- @chart.SetSourceData(range, 2)
- end
- # 更改图形类型
- def type=(c_type)
- @chart.ChartType = c_type
- end
+ def initialize(active)
+ @chart = active
+ end
- # 设置X轴名称, 只用于条形图
- def axes_x=(name)
- @chart.Axes(1,1).HasTitle = true
- @chart.Axes(1,1).AxisTitle.Characters.Text = name
- end
+ def chart_work
+ @chart
+ end
- # 设置Y轴名称, 只用于条形图
- def axes_y=(name)
- @chart.Axes(2,1).HasTitle = true
- @chart.Axes(2,1).AxisTitle.Characters.Text = name
- end
+ # 标签选项
+ def now_table
+ #@chart.SeriesCollection(n).DataLabels
+ num = @chart.SeriesCollection.Count
+ num.times do |t|
+ t += 1
+ yield @chart.SeriesCollection(t).DataLabels
+ end
+ end
- # 设置坐标轴格式, 刻度单位
- def axes_unit(int)
- @chart.Axes(2).MajorUnit = int
- @chart.Axes(2).MinorUnit = int
- end
-
- # 修改样式
- # 通过录制宏可以查看样式编号
- # 条形图中203 比较好看
- # 饼图中 251, 254 比较好看
- def style=(int)
- @chart.ChartStyle = int
- data_label
- end
+ # 修改标题
+ def title=(name)
+ @chart.HasTitle = true
+ @chart.ChartTitle.Characters.Text = name
+ end
- # 添加饼图的百分比
- def data_label(type=DataLabelsShowLabelAndPercent)
+ # 这是原数据地址, 以每列数据作为一个系列
+ def source=(range)
+ @chart.SetSourceData(range, 2)
+ end
- # 应用标签选项
- @chart.ApplyDataLabels(type)
+ # 更改图形类型
+ def type=(c_type)
+ @chart.ChartType = c_type
+ end
- # 取消标签选项的系列名称
- now = @chart.SeriesCollection(1).DataLabels
- now.ShowSeriesName = false
+ # 设置X轴名称, 只用于条形图
+ def axes_x=(name)
+ @chart.Axes(1,1).HasTitle = true
+ @chart.Axes(1,1).AxisTitle.Characters.Text = name
+ end
- # 将图例放到右边
- now = @chart.Legend
- now.Position = -4152
- end
+ # 设置Y轴名称, 只用于条形图
+ def axes_y=(name)
+ @chart.Axes(2,1).HasTitle = true
+ @chart.Axes(2,1).AxisTitle.Characters.Text = name
+ end
- # 标签系列名称开关
- def series_name=(bool)
- now_table do |n|
- n.ShowSeriesName = bool
- end
- end
+ # 设置坐标轴格式, 刻度单位
+ def axes_unit(int)
+ @chart.Axes(2).MajorUnit = int
+ @chart.Axes(2).MinorUnit = int
+ end
- # 标签类别名称开关
- def category_name=(bool)
- now_table do |n|
- n.ShowCategoryName = bool
- end
- end
+ # 修改样式
+ # 通过录制宏可以查看样式编号
+ # 条形图中203 比较好看
+ # 饼图中 251, 254 比较好看
+ def style=(int)
+ @chart.ChartStyle = int
+ data_label
+ end
- # 标签值开关
- def value=(bool)
- now_table do |n|
- n.ShowValue = bool
- n.ShowLegendKey = 0 unless bool
- n.Separator = " " unless bool
- end
- end
-
- end
+ # 添加饼图的百分比
+ def data_label(type=DataLabelsShowLabelAndPercent)
+ # 应用标签选项
+ @chart.ApplyDataLabels(type)
+
+ # 取消标签选项的系列名称
+ now = @chart.SeriesCollection(1).DataLabels
+ now.ShowSeriesName = false
+
+ # 将图例放到右边
+ now = @chart.Legend
+ now.Position = -4152
+ end
+
+ # 标签系列名称开关
+ def series_name=(bool)
+ now_table do |n|
+ n.ShowSeriesName = bool
+ end
+ end
+
+ # 标签类别名称开关
+ def category_name=(bool)
+ now_table do |n|
+ n.ShowCategoryName = bool
+ end
+ end
+
+ # 标签值开关
+ def value=(bool)
+ now_table do |n|
+ n.ShowValue = bool
+ n.ShowLegendKey = 0 unless bool
+ n.Separator = " " unless bool
+ end
+ end
+
+ end
+
+ end
end
-end
-
+