module Aio::Base::Toolkit
module ExcelOffice
	class WorkBook

		include Aio::Ui::Verbose

		@@worksheets_name = []
		def initialize(encoding = "utf-8")

			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

		# 切换到分页预览视图
		def window_pagebreak
			@excel.ActiveWindow.View = 2
		end

		# 切换到普通视图
		def window_normal
			@excel.ActiveWindow.View = 1
		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
			worksheet.name = name
			return WorkSheet.new(worksheet)
		end

		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("TitleStyle")
			self.class.title_style(sty)
		end

		def self.normal_style(sty)
			sty.font.size = 9
			sty.HorizontalAlignment = -4108
		end

		def self.bold_style(sty)
			sty.font.size = 9
			sty.font.bold = true
			sty.HorizontalAlignment = -4108
		end

		def self.title_style(sty)
			sty.font.size = 20
			sty.font.bold = true
			sty.HorizontalAlignment = -4108
		end
			
		def show
			@excel.visible = true
		end

		def save(path)
			path = Aio::Base::Toolkit::String.safe_path(path)
			@workbook.saveas(path)
		end

		def close
			@workbook.close
			@excel.quit
		end
	end		# class WorkBook

	class WorkSheet
		IMAGE_ROW_NUM = 56
		@@worksheets_name = []
		def initialize(worksheet)
			@row_count = 1
			@worksheet = worksheet
			@nil_space = []
		end

		# 增加一个空行
		def add_space_line(n=1)
			return if n < 1
			@row_count += n
		end

		# 对列进行合并
		def merge(range1, range2)
			@worksheet.range("#{range1}:#{range2}").merge
		end

		# 产生 ::Range 类
		def range(str)
			@worksheet.range(str)
		end

		# 添加标题行
		def add_title(name)
			add_row.add_cell(name, false, "BoldStyle")
		end

		# 设置列的宽度
		def width(col, width)
			@worksheet.Columns("#{col}:#{col}").ColumnWidth = width
		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
			@current_row.merge!
			begin
				@current_row.real_row.borders.linestyle = 1
			rescue Exception
				retry
			end
			return @current_row
		end

		# 返回此时的Row类
		def current_row
			return @current_row
		end

		# 返回此时的行索引
		def current_row_id
			return @current_row.row_id
		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

		# 判断是否有垂直分页符存在
		def has_pagebreak?
			@worksheet.VPageBreaks.count > 0 
		end

		# 对列修改分页符
		def pagebreak_left(num)
			#@worksheet.VPageBreaks(1).Location = @worksheet.columns(col)
			@worksheet.VPageBreaks(1).DragOff("-4161", num.to_i)
		end

		# 返回::WorkSheet
		def worksheet
			@worksheet
		end

		# 添加图表
		def add_chart(&block)
			ch = @worksheet.Shapes.AddChart
			active = ch.chart

			# 占位符
			block.call(Chart.new(active))

			ch.copy
		end
	end

	class Row
		FILL_TYPE = 4

		attr_reader :row_id

		@@cell_map = ("A".."Z").to_a
		def initialize(worksheet, row_id)
			@row_id = row_id
			@cell_count = 0
			@worksheet = worksheet
			@nil_space = []
			@merge_cell = []
		end
		
		# 此时的单元格
		def curent_cell
			return cell_name(@cell_count)
		end

		# 第一个单元格
		def first_cell
			return cell_name(0)
		end

		# 设置行高
		def height=(height)
			begin
				@worksheet.rows(@row_id).RowHeight = height
			rescue Exception
				retry
			end
		end

		# 增加单元格
		def add_cell(value, auto_fit = false, style = "NormalStyle")
			range = @worksheet.Range(cell_name(@cell_count))
			begin
				range.Value = value.to_s
				range.Style = style
			rescue Exception
				retry
			end
			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 to_letter(index)
			@@cell_map.at(index)
		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 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 cell_name!(letter)
			return letter + @row_id.to_s
		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 set_style(letter, style = "NormalStyle")
			range = @worksheet.range(cell_name!(letter))
			range.Style = style
		end

		# 合并一行中的单元格, 这里仅仅是记录
		def merge(idx_begin, idx_end)
			cell_begin = "#{idx_begin}#{@row_id}"
			cell_end = "#{idx_end}#{@row_id}"
			@merge_cell << [cell_begin, cell_end]
			tmp = ((idx_begin.upcase)..(idx_end.upcase)).to_a
			tmp.shift
			@nil_space = (@nil_space | tmp).sort
		end

		def merge!
			return if @merge_cell.empty?
			@merge_cell.each do |cell_begin, cell_end|
				range = @worksheet.Range("#{cell_begin}:#{cell_end}")
				range.merge
			end
		end

		# 开启自动换行
		def wraptext(letter)
			range = @worksheet.range(cell_name!(letter))
			range.WrapText = true
		end

		# 对此时的行的下方下分页符
		def pagebreak
			begin
				@worksheet.rows(@row_id+1).PageBreak = 1
			rescue Exception
				retry
			end
		end

		# 返回此时的::Row类
		def real_row
			#@worksheet.rows(@row_id)
			@worksheet.range("A#{@row_id}:#{cell_name(@cell_count - 1)}")
		end
			
		alias :style :set_style
	end

	# 图表
	class Chart

		# 图形类型
		ColumnClustered = 51 				# 簇状柱形图
		ColumnStacked = 52          # 堆积柱状图
		Doughnut = -4120						# 圆环图
		Line = 4										# 折线图
		Pie = 5											# 饼图
		BarClustered = 57						# 簇状条形图

		# 标签数据
		DataLabelsShowLabel = 4 		# 数据点所属的分类
		DataLabelsShowLabelAndPercent = 5 # 占比百分比以及所属分类,仅饼图
		DataLabelsShowPercent	= 3		# 百分比, 仅饼图
		DataLabelsShowValue		= 2		# 默认值


		def initialize(active)
			@chart = active
		end

		def chart_work
			@chart
		end

		# 修改标题
		def title=(name)
			@chart.HasTitle = true
			@chart.ChartTitle.Characters.Text = name
		end

		# 这是原数据地址, 按列生成数据
		def source=(range)
			@chart.SetSourceData(range, 2)
		end

		# 更改图形类型
		def type=(c_type)
			@chart.ChartType = c_type
		end

		# 设置X轴名称, 只用于条形图
		def axes_x=(name)
			@chart.Axes(1,1).HasTitle = true
			@chart.Axes(1,1).AxisTitle.Characters.Text = name
		end

		# 设置Y轴名称, 只用于条形图
		def axes_y=(name)
			@chart.Axes(2,1).HasTitle = true
			@chart.Axes(2,1).AxisTitle.Characters.Text = name
		end
			
		# 修改样式
		# 通过录制宏可以查看样式编号
		# 条形图中203 比较好看
		# 饼图中  251, 254 比较好看
		def style=(int)
			@chart.ChartStyle = int
			data_label
		end

		# 添加饼图的百分比
		def data_label(type=DataLabelsShowLabelAndPercent)

			# 应用标签选项
			@chart.ApplyDataLabels(type)

			# 取消标签选项的系列名称
			now = @chart.SeriesCollection(1).DataLabels
			now.ShowSeriesName = false

			# 将图例放到右边
			now = @chart.Legend
			now.Position = -4152
		end
	end

end
end