根据excel文件，生成若干数据（xml）

generator.rb

#encoding: utf-8
require 'spreadsheet'
require 'rexml/document'
include REXML

class Generator
	attr_accessor :dataResult #array，获取每个xml所需数据
	attr_accessor :resultPath #string，生成的xml存储路径
	attr_accessor :excelPath #string，excel文件路径
	attr_accessor :templatePath #string，xml模板文件路径
	attr_accessor :sheetIndex #int，excel文件数据所在sheet
	attr_accessor :interface #string，当前接口名称，用于拼凑xml的名称和存储路径
	attr_accessor :normalLineNum #int，excel文件中正常数据行数，默认为第1行
	
	def initialize
		@dataResult = Array.new
		@@otherData = Hash.new
		@normalLineNum = 1
	end

	def traversalExcel
		begin
			Spreadsheet.client_encoding= "UTF-8" #config:standard xml is UTF-8, bank xml is GB2312

			data = Spreadsheet.open(@excelPath)
			sheets = data.worksheet(@sheetIndex)
			content = Array.new

			#将sheet转换成array
			sheets.each do |row|
				temp = Array.new
				for i in (0..row.size-1)
					temp.push row[i]
				end
				content.push temp
			end
			return content
		rescue
			puts "error:#{$!} at:#{$@}"
		end
	end

	def loadXPath
		begin
			content = traversalExcel()
			xpath = Array.new
			for j in (0..content[0].size-1)
				xpath.push content[0][j].to_s
			end
			return xpath
		rescue
			puts "error:#{$!} at:#{$@}"
		end
	end

	def loadNormalData
		begin
			content = traversalExcel()
			for i in (1..@normalLineNum)
				array = Array.new
				for j in (0..content[0].size-1)
					array.push content[i][j].to_s
				end
				@dataResult.push array
			end
		rescue
			puts "error:#{$!} at:#{$@}"
		end
	end

	def loadExceptionData(type)
		begin
			if type == "one"
				content = traversalExcel()
				#one exception data
				for je in (0..content[0].size-1)
					for i in (@normalLineNum+1..content.size-1)
						unless content[i][je].nil?
							array = Array.new
							array[je] = content[i][je]
							for jn in (0..content[0].size-1)
								unless je == jn
									array[jn] = content[1][jn]
								end
							end
							@dataResult.push array
						end
					end
				end
			elsif type == "multi"
				#multi exception data (2..n)
				for j in (1..content[0].size-1)
					array = Array.new
					for je in (0..j)
						array.push content[@normalLineNum+1][je].to_s #任取一行异常数据，此处取第一行异常数据
					end
					for jn in (j+1..content[0].size-1)
						array.push content[1][jn].to_s #任取一行正常数据，此处取第一行正常数据
					end
					@dataResult.push array
				end
			end
		rescue
			puts "error:#{$!} at:#{$@}"
		end
	end

	def traversalTemplate(template)
		begin
			if File.exist? template
				file = File.open(template)
				doc = Document.new file
				return doc
			else
				puts "ERROR: can\'t find #{template}. Please try again."
			end
		rescue
			puts "error:#{$!} at:#{$@}"
		end
	end

	def autoAddData(doc,xpath,data)
		begin
			for i in (0..xpath.size-1)
				doc.elements.each(xpath[i].to_s) do |e|
					e.add_text data[i] unless data[i] == "NULL" #NULL代表该字段为空值
				end
			end
			return doc
		rescue
			puts "error:#{$!} at:#{$@}"
		end
	end
	
	def manualAddData(doc) #除excel数据：固定值，随机值等
		begin
			unless @@otherData.nil?
				@@otherData.each do |k,v|
					doc.elements.each(k.to_s) do |e|
						if v.size == 1
							e.add_text v.first unless v.first == "NULL" #NULL代表该字段为空值
						else
							ke = Kernel.const_get(v[0]).new
							se = ke.send(v[1],*v[2..v.size-1])
							e.add_text se.to_s unless se.to_s == "NULL" #NULL代表该字段为空值
						end
					end
				end
			end
			return doc
		rescue
			puts "error:#{$!} at:#{$@}"
		end
	end
	
	def addData(xpath,*data) #除excel数据：固定值，随机值
		begin
			@@otherData[xpath.to_s] = *data
		rescue
			puts "error:#{$!} at:#{$@}"
		end
	end

	def write(doc)
		begin
			sleep 1 #由于程序运行过快导致时间戳精度不够，故人工减速
			filename = "#{@interface}-#{(Time.now).strftime('%Y%m%d%H%M%S')}.xml"
			filepath = "#{@resultPath}/#{filename}"
			unless File.exist? filepath
				file = File.new(filepath,"w+")
				file.puts doc.write
				file.close
			end
		rescue
			puts "error:#{$!} at:#{$@}"
		end
	end
	
	def run
		begin
			xpath = loadXPath
			@dataResult.each do |row|
				doc = traversalTemplate(@templatePath)
				doc = autoAddData(doc,xpath,row)
				doc = manualAddData(doc)
				write(doc)
			end
		rescue
			puts "error:#{$!} at:#{$@}"
		end
	end
end

run.rb

#encoding: utf-8
require 'dataGenerator'

g = Generator.new
g.sheetIndex = 0
g.interface = "testInterface"
g.excelPath = "excel.xls"
g.templatePath = "template.xml"
g.resultPath = "DataGenerator/"
g.normalLineNum = 2
g.loadExceptionData
g.loadNormalData
g.addData("Request/Test","123")
g.addData("Request/Details/Test","Object","rand",9999999999999999)
g.run

template.xml

<Request>
<Test></Test>
<Data></Data>
<ID></ID>
<Details>
<Test></Test>
<Comment></Comment>
</Details>
</Request>

说明

EXCEL模板
----------------------------------
第一行：XPATH，例如：Request/Data
第二行：各字段对应正常数据
……
第normalLineNum+1行：各字段对应异常数据
……