xls2lua.py

#! /usr/bin/env python
# -*- coding: utf-8 -*
# author: zfengzhen

import xlrd
import os.path
import time

SCRIPT_HEAD = "-- this file is generated by program!\n\
-- don't change it manaully.\n\
-- source file: %s\n\
-- created at: %s\n\
\n\
\n\
"
SCRIPT_END = "\n\
for i=1, #(%s.all_type) do\n\
	local item = %s.all_type[i]\n\
	for j=1, #item do\n\
		item[j].__index = item[j]\n\
		if j < #item then\n\
			setmetatable(item[j+1], item[j])\n\
		end\n\
	end\n\
end\n\
\n\
\n\
"
def make_table(filename):
	if not os.path.isfile(filename):
		raise NameError, "%s is	not	a valid	filename" % filename
	book_xlrd = xlrd.open_workbook(filename,formatting_info=True)

	excel = {}
	excel = {}
	excel["filename"] = filename
	excel["data"] = {}
	excel["meta"] = {}
	for sheet in book_xlrd.sheets():
		sheet_name = sheet.name.replace(" ", "_")
		excel["data"][sheet_name] = {}
		excel["meta"][sheet_name] = {}

		# 必须大于2行2列
		if sheet.ncols <= 2 or sheet.nrows <= 2:
			return {}, -1, "sheet[" + sheet_name + "]" + " columns and rows must > 2"

		# 解析标题
		title = {}
		col_idx = 0
		for col_idx in xrange(sheet.ncols):
			value = sheet.cell_value(0, col_idx)
			vtype = sheet.cell_type(0, col_idx)
			if vtype != 1:
				return {}, -1, "title columns[" + str(col_idx) + "] must be string"
			title[col_idx] = str(value).replace(" ", "_")

		if title[0] != "id":
			return {}, -1, "sheet[" + sheet_name + "]" + " first column title must be [id]"
		elif title[1] != "name":
			return {}, -1, "sheet[" + sheet_name + "]" + " second column title must be [name]"
 
		excel["meta"][sheet_name]["title"] = title

		# 类型解析
		type_dict = {}
		col_idx = 0
		for col_idx in xrange(sheet.ncols):
			value = sheet.cell_value(1, col_idx)
			vtype = sheet.cell_type(1, col_idx)
			type_dict[col_idx] = str(value)
			if (type_dict[col_idx].lower() != "int" \
				and type_dict[col_idx].lower() != "string" \
				and type_dict[col_idx].lower() != "boolean"):
				return {}, -1, "sheet[" + sheet_name + "]" + \
					" row[" + row_idx + "] column[" + col_idx + \
					"] type must be [int] or [string] or [boolean]"

		if type_dict[0].lower() != "int":
			return {}, -1,"sheet[" + sheet_name + "]" + " first column type must be [int]"
		elif type_dict[1].lower() != "string":
			return {}, -1, "sheet[" + sheet_name + "]" + " second column type must be [string]"

		excel["meta"][sheet_name]["type"] = type_dict
        # 第3行为解释数据

		# 数据解析
		row_idx = 3
		for row_idx in xrange(3, sheet.nrows):
			item_name = sheet.cell_value(row_idx, 1)
			if (item_name != None and item_name != ""):
				excel["data"][sheet_name][item_name] = {}
				excel["meta"][sheet_name][item_name] = {}
				excel["meta"][sheet_name][item_name]["index"] = 0

		row_idx = 3
		pre_item_id = -1
		pre_item_name = ""
		pre_row = {}
		# 数据从第二行开始
		for row_idx in xrange(3, sheet.nrows):
			row = {}

			# 名字,如果没有采用上一行数据
			item_name = sheet.cell_value(row_idx, 1)
			item_name_type = sheet.cell_type(row_idx, 1)	
			if (item_name == None or item_name == "" or item_name_type == 0):
				item_name = pre_item_name
			else:
				pre_item_name = item_name

			# id, 如果没有采用上一行数据
			item_id = sheet.cell_value(row_idx, 0)
			item_id_type = sheet.cell_type(row_idx, 0)	
			if (item_id == None or item_id_type == 0):
				item_id = pre_item_id
			else:
				pre_item_id = item_id

			col_idx = 0
			for col_idx in xrange(sheet.ncols):
				value = sheet.cell_value(row_idx, col_idx)
				vtype = sheet.cell_type(row_idx, col_idx)
				# 本行有数据
				v = None
				if type_dict[col_idx].lower() == "int" and vtype == 2:
					v = int(value)
				elif type_dict[col_idx].lower() == "string" and vtype == 1:
					v = str(value)
				elif type_dict[col_idx].lower() == "boolean" and vtype == 4:
					if str(value).lower() == "true":
						v = "true"	
					else:
						v = "false"

				# 如果值为空,采用上一行数据
				if v is not None and value != "":
					row[col_idx] = v
				#elif item_id != pre_item_id:
				#	pre_item_id = item_id
				#elif (item_id == pre_item_id):
				#	row[col_idx] = pre_row[col_idx]

			if row:
				item_idx = excel["meta"][sheet_name][item_name]["index"]
				excel["data"][sheet_name][item_name][item_idx] = row
				excel["meta"][sheet_name][item_name]["index"] = item_idx + 1
				pre_row = row

	return excel, 0 , "ok"

def format_str(v):
	s = ("%s"%(v)).encode("gbk")
	if s[-1] == "]":
		s = "%s "%(s)
	return s

def write_to_lua_script(excel, output_path):
	for (sheet_name, sheet) in excel["data"].items():
		outfp = open(output_path + "/" + sheet_name + ".lua", 'w')
		create_time = time.strftime("%a %b %d %H:%M:%S %Y", time.gmtime(time.time()))
		outfp.write(SCRIPT_HEAD % (excel["filename"], create_time)) 
		outfp.write("local " + sheet_name + " = {}\n")
		outfp.write("\n\n")
		
		title = excel["meta"][sheet_name]["title"]
		type_dict= excel["meta"][sheet_name]["type"]

		# 写入所有的类型
		outfp.write(sheet_name + ".type_map = {}\n")
		outfp.write("local type_map = " + sheet_name + ".type_map\n")
		for (item_name, item) in sheet.items():
			# 第一个数据会存在
			outfp.write("type_map[" + format_str(item[0][0]) + "] = \"" \
				+ format_str(item[0][1]).replace(" ", "_")  + "s\"\n")
			outfp.write("type_map[\"" + format_str(item[0][1]).replace(" ", "_") + "s\"] = " \
				+ format_str(item[0][0]) + "\n")

		outfp.write("\n\n")
		
		for (item_name, item) in sheet.items():
			# 写入数据
			real_item_name = format_str(item_name).replace(" ", "_") + "s"
			for (row_idx, row) in item.items():
				if row_idx == 0:
					outfp.write(sheet_name + "." + real_item_name + " = {}\n")
					outfp.write("local " + real_item_name + " = " + sheet_name + "." + real_item_name + "\n\n")
				outfp.write(real_item_name + "[" + str(row_idx+1) + "] = {\n")
				for (col_idx, field)in row.items():
					if type_dict[col_idx] == "int":
						outfp.write("\t" + str(title[col_idx]) + " = " + str(row[col_idx]) + ",\n")
					elif type_dict[col_idx] == "string":
						outfp.write("\t" + str(title[col_idx]) + " = \"" + str(row[col_idx]) + "\",\n")
					elif type_dict[col_idx] == "boolean":
						outfp.write("\t" + str(title[col_idx]) + " = " + str(row[col_idx]) + ",\n")
				outfp.write("}\n\n")

		outfp.write(sheet_name + ".all_type= {}\n")
		outfp.write("local all_type = " + sheet_name + ".all_type\n")
		index = 1
		for (item_name, item) in sheet.items():
			outfp.write("all_type[" + str(index) + "] = " \
				+ format_str(item_name).replace(" ", "_")  + "s\n")
			index = index + 1
			

		outfp.write("\n\n")
		outfp.write(SCRIPT_END % (sheet_name, sheet_name)) 
		outfp.write("return " + sheet_name)
		outfp.write("\n\n")
		outfp.close()

def main():
	import sys
	if len(sys.argv) < 3:
		sys.exit('''usage: xls2lua.py excel_name output_path)''')
	filename = sys.argv[1]
	output_path = sys.argv[2]
	t, ret, errstr = make_table(filename)
	if ret != 0:
		print "error: " + errstr
	else:
		print "res:"
		print(t)
		print "success!!!"
	write_to_lua_script(t, output_path)

if __name__=="__main__":
	main()