lua(实现代码(1)):使用lua将excel中的配置数据生成sql文件

本程序主要用于游戏编程等相关项目中，产品人员使用excel设计数据之后直接将相关数据生成sql，可以导入数据库

excel格式及相关的使用方法，请下载数据包http://download.csdn.net/download/wentianyao/9401274

该库使用了luacom组件所以只能在windows使用

-- excel文件解析成sql文件
-- ws
-- 2015.5

------------------------------------------------------------------------
--配置部分
-- 生成sql目标文件的前缀
local FilePrefix = "conf_"
-- 识别excel中表的前缀
local TablePrefix = "conf_"

------------------------------------------------------------------------
-- 加载库文件
package.path=package.path..";..\\?.lua;..\\..\\scripts\\?.lua"
require("luacom")
require("json")
local lfs = require"lfs"    -- 加载文件处理库

------------------------------------------------------------------------
local exl2sql={}

-- 设置类型长度 语句配置
--                    数据类型        创建表的时候用(属性创建语句)               数据为空的时候的替换数据
exl2sql.STRINGTYPE ={["UINT11"] =     {"int(11) unsigned NOT NULL DEFAULT '0'", 0},
           ["INT11"] =     {"int(11) NOT NULL DEFAULT '0'", 0},
           ["INT32"] =     {"int(32) NOT NULL DEFAULT '0'", 0},
           ["INT64"] =     {"int(64) NOT NULL DEFAULT '0'", 0},
           ["STRING32"] =    {"varchar(32) DEFAULT NULL", "\'\'"},   
           ["STRING64"] =    {"varchar(64) DEFAULT NULL", "\'\'"},
           ["STRING128"] =   {"varchar(128) DEFAULT NULL", "\'\'"},
           ["STRING256"] =   {"varchar(256) DEFAULT NULL", "\'\'"},
           ["STRING512"] =   {"varchar(512) DEFAULT NULL", "\\'\''\'"},
           ["STRING1024"] =   {"varchar(1024) DEFAULT NULL", "\'\'"},
           ["STRING2048"] =   {"varchar(2048) DEFAULT NULL", "\'\'"},
           ["DATETIME"] =   {"datetime default '0000-00-00 00:00:00'", "\'\'"},
           ["JSON256"] =    {"varchar(256)  DEFAULT NULL", "\'\'"},
           ["JSON1024"] =    {"varchar(1024) DEFAULT NULL", "\'\'"},
           ["JSON2048"] =    {"varchar(2048) DEFAULT NULL", "\'\'"}}

-- 字符串的处理“^%s”表示匹配所有非s里面的数据，*表示匹配前一个字符串0次或多次,(.-)表示匹配.或者-，%s*表示0次或多次匹配s
function exl2sql.trim(s)
  return s:match "^%s*(.-)%s*$"
end

-- 将字符串s中的符合tMode的子串替换为tReplace
function exl2sql.escape(s)
    local tMode="[%z\'\"\\\26\b\n\r\t]"
    local tReplace={
        ['\0']='\\0',
        ['\'']='\\\'',
        ['\"']='\\\"',
        ['\\']='\\\\',
        ['\26']='\\z',
        ['\b']='\\b',
        ['\n']='\\n',
        ['\r']='\\r',
        ['\t']='\\t'
    }
    return string.gsub(s, tMode, tReplace)
end

 

-- 获取目标目录下的有所*.xlsx文件
-- 参数path:为纯路径
function exl2sql.GetAllXlsxFile(path)
  local FileList = {}
  local NoSuffixFileList = {} -- 无后缀的文件列表
   -- 循环遍历
   for ofile in lfs.dir(path) do
     if ofile ~= nil then
       -- 获取文件属性
      local f = path..'\\'..ofile
      --local f = ofile
      -- 拆分文件及后缀
      local ext = f:match(".+%.(%w+)$")
      -- 获取本目录下的所有的xlsx文件
      if ext == "xlsx" then
         -- 排除过度文件
         local filename = string.sub(ofile, 0, 2)
         if filename ~= "~$" then
         table.insert(FileList, f)
         local name = string.sub(f,0,f:match(".+()%.%w+$")).."sql"  -- 生成的文件名
         table.insert(NoSuffixFileList, name)    
        end
      end
     end  
  end
  return FileList, NoSuffixFileList
end

 

-- 获取文档中每个sheet信息
-- 参数：ExlObj表示创建的Excel对象，BookObj表示打开的excel对象
-- 返回值: 返回所有有效的sheet列表，否则返回nil
function exl2sql.GetSheetInfo(ExlObj, BookObj)
   -- 遍历文件中的sheet标签（目前暂定标签格式为TablePrefix指定),其他的不识别
   local SheetList = {} -- 目标文件所有的sheet信息
   
    -- 获取sheet文件数量
    local temp = BookObj.Sheets.Count
    -- 遍历所有的表名(excel中默认的起始编号是1)

    for i = 0,(temp - 1) do
       -- 获取该table的数据特征(列名，类型)
        local tSheet=BookObj.Sheets(i + 1) -- 获取当前的sheet文件
       local tabname = ExlObj.ActiveWorkbook.Sheets(i+1).Name  -- 获取sheet名
       -- 识别是不是要生成的TablePrefix指定的前缀表
       local TablePrefixLen = string.len(TablePrefix) -- 获取指定前缀的长度
       local PreTabeName = string.sub(tabname, 0, TablePrefixLen) -- 截取sheet名字的前n个用于识别是不是前缀
       
      -- 如果是要符合规范的sheet文件
       if PreTabeName == TablePrefix then 
          -- 获取sheet的属性（编号及名字）
          local tabattr = {TableIndx = nil, TableName = nil, ColList = {}}  -- 表的基础结构(表编号，sheet名字，表中所有有效列的信息)
          tabattr.TableIndx = i + 1  -- 当前表所在的sheet编号
          tabattr.TableName = tabname  -- sheet对应的将要生成的name
          tabattr.ColList = {}
          print("INFO_1: read sheet"..tabattr.TableIndx.." :name".. tabname.. " base info start...")

          -- 遍历列,获取列名(注:excel表中的必需第二行是列名)    
          local bBreak = true -- 跳出中断
          local ColNum = 1 -- 列编号
          while bBreak == true do
             local SheetInfo = {} -- 表的列信息(列编号，列名说明，列名，列类型)
             SheetInfo.ColIndex = ColNum
             SheetInfo.ColNameText = tostring(tSheet.Cells(1, ColNum).Value2) -- 获取列名备注
             SheetInfo.ColName = tostring(tSheet.Cells(2, ColNum).Value2) -- 获取列名
             SheetInfo.ColType = tostring(tSheet.Cells(3, ColNum).Value2) -- 获取目标列的数据类型
                  
             -- 索引结束
             if SheetInfo.ColName == "nil" then
                 print("INFO_2: the sheet: "..tabattr.TableName.." has check end...")
                bBreak = false  -- 循环中断
                break
             end
             
             if SheetInfo.ColType == "nil" then --缺少列类型
                   print("ERROR_1: this sheet: "..tabattr.TableName.." NO."..SheetInfo.ColIndex.." column have not Type!")
                   return nil --end
              else
                    -- 类型检查
                    if exl2sql.STRINGTYPE[SheetInfo.ColType] == nil then
                    print("ERROR_2: this sheet: "..tabattr.TableName.." NO."..SheetInfo.ColIndex.." column type is wrong!")
                    return nil
                    end
                   
                  print("INFO_3: NO."..ColNum.." column".." Name: "..SheetInfo.ColName.." Type:"..SheetInfo.ColType)
                  -- 将列信息添加到表信息中
                  table.insert(tabattr.ColList, SheetInfo)
                  ColNum = ColNum + 1  -- 列编号+1
             end
          end -- while bBreak do
          
          -- 提示信息
          if table.getn(tabattr.ColList) == 0 then
            print("ERROR_3: Have no any data from sheet"..ColNum.."!")
            else
            -- 表信息插入sheet列表
             table.insert(SheetList, tabattr)
          end
          
       end -- if PreTabeName == TablePrefix then
       
    end -- for i = 0,(temp - 1) do
   
    -- 检查SheetList是否为空
    if 0 >= table.getn(SheetList) then
      return nil
    end
   
    -- 返回sheet列表
    return SheetList
end

 

-- 根据列类型获取数据
-- 参数:ColType表示列类型,Data表示列原始数据
function exl2sql.GetDataByType(ColType, Data)
    -- 若读取的数据为空
  if Data == nil then
    local nildata = exl2sql.STRINGTYPE[ColType][2] -- 获取数据为空的时候的替换数据
    return nildata
  end
  
  -- 若有数据
  local bData = nil
  if ColType == "UINT11" or ColType == "INT11" or ColType == "INT32" or ColType == "INT64"then
     bData = tonumber(Data)
    else
       bData = exl2sql.trim(tostring(Data))
  end
  
  
  -- 特殊处理
  -- 特殊处理1，json类型处理规则
  local TypeSuf = string.sub(ColType, 0, 4) -- 获取类型前4个，识别是不是json
  if TypeSuf == "JSON" then
   bData = exl2sql.escape(json.encode(json.decode(bData)))
  end
  
  -- 对于字符串加"“”"处理
  if ColType == "UINT11" or ColType == "INT11" or ColType == "INT32" or ColType == "INT64"then
  else
       bData = "\""..bData.."\""
  end
  
  return bData
end

 

-- 执行sql的创建，包括表的创建和数据的插入
-- 参数：ExlObj表示创建的Excel对象，BookObj表示打开的excel对象,SheetObj表示已经检查过的有效的sheet,WriteOutFile输出的sql文件句柄
function exl2sql.CreateSql(ExlObj, BookObj, SheetObj, WriteOutFile) 
   -- 创建表的过程(获取执行语句)
   local tCreateSql = ""
   local tNameList = {} -- 列名列表
   local tNameType = {} -- 列类型
   for i, ColInfo in ipairs(SheetObj.ColList) do
       -- 根据类型获取语句
       local TypeString = exl2sql.STRINGTYPE[ColInfo.ColType][1] -- 获取对应的类型长度
      tCreateSql = tCreateSql .. "`"..ColInfo.ColName.."` "..TypeString
      table.insert(tNameList, ColInfo.ColName)
      table.insert(tNameType, ColInfo.ColType)
   end
  
  
   -- 获取数据同时进行数据检查
   -- 提供主键检查
   local tSheet=BookObj.Sheets(SheetObj.TableIndx) -- 获取当前的sheet文件
   -- 遍历所有数据（*目前从第4行开始）
   local bBreak = true
   local RowlIndex = 4 --遍历的数据行(目前是从第4行开始)
   local ColNumMax = table.getn(tNameList) -- 获取有多少数据列
   local RowKeyList = {} -- 所有主键值
   local RowDataList = {} -- 所有行数据
   while bBreak do -- 一直循环遍历所有数据
       local RowData = {} -- 行数据
        -- 获主键数据
        local keydata = tSheet.Cells(RowlIndex, 1).Value2
        if keydata == nil then  -- 认为数据结束
           bBreak = false
           break  -- 中断
         end
        
         -- 转换成有效数据
         local colKeydata = exl2sql.GetDataByType(tNameType[1], keydata)
        -- 主键检查
         if RowKeyList[colKeydata] ~= nil then -- 表示该主键已经被用过了
          -- 如果联系的两个值都为
          print("ERROR_4: in sheet"..SheetObj.TableName.." DOUBLE PRIMARY KEY value:"..colKeydata) return nil
         end  
         
       -- 插入主键
       RowKeyList[colKeydata] = 1 -- 表示该主键已经使用
       -- 插入第列数据
       table.insert(RowData, colKeydata)
      
       -- 其他数据
      for j = 2, ColNumMax do
          -- 获取列数据
          local coldata = exl2sql.GetDataByType(tNameType[j], tSheet.Cells(RowlIndex, j).Value2)
         table.insert(RowData, coldata)
      end
      
      -- 加入数据列
      table.insert(RowDataList, RowData)
  
      RowlIndex = RowlIndex + 1 -- 行递增
   end
  
  
   -- 插入注释
   WriteOutFile:write("\n\n-- -----------------------------\n-- "..SheetObj.TableName.."\n-- -------------------------------")
  
   -- 插入表头
   WriteOutFile:write("\nDROP TABLE IF EXISTS `"..SheetObj.TableName.."`; \nCREATE TABLE `"..SheetObj.TableName.."` (\n")
  
   -- 插入表尾(包括建立列，设置主键(默认第一列就是主键)，引擎类型，编码方式)
   WriteOutFile:write(tCreateSql.." PRIMARY KEY (`"..SheetObj.ColList[1].ColName.."`)\n) ENGINE=MyISAM DEFAULT CHARSET=utf8;\n\n")
  
   -- 循环插入数据的过程
   --WriteOutFile:write("insert into `"..SheetObj.TableName.."`(`"..table.concat(tNameList, "','").."`) values ("..table.concat (RowDataList, ", ")..";\n")
   for i, col in ipairs(RowDataList) do
   WriteOutFile:write("INSERT INTO `"..SheetObj.TableName.."` VALUES ("..table.concat(col, ", ")..");\n")
   end
  
 
   print("INFO_4: Table: ".. SheetObj.TableName.." create success ...")
  
   return 1
  
end

 

-- 打开excel表
-- 参数tExl:为excel读写句柄；file:为完整的文件路径及文件名；filename为生成的文件名
function exl2sql.OpenExcel(tExl, file, filename)
    -- 打印起始信息
    print("INFO:file: "..file.." to "..filename.." start...")

    if tExl == nil then print("ERROR_5: need lua library!") return end
    -- 加载目标文件
    local tBook=tExl.Workbooks:Open(file)
    if tBook == nil then print("ERROR_6: Excel file: "..file.." open fail!")  tExl.Application:quit() tExl=nil return end
   
    -- 获取excel中所有有效的sheet信息
    local sheetlist = exl2sql.GetSheetInfo(tExl, tBook)
    if sheetlist == nil then tBook:close()  tExl.Application:quit() tExl=nil return end
      
    -- 打开目标sql文件
    os.remove(filename) -- 先删除一遍
    local WriteOutFile = io.open(filename, "a")
    if WriteOutFile == nil then print("ERROR_7: Create sql file: "..filename.." fail!") tBook:close() tExl.Application:quit() tExl=nil return end 
    
    -- 插入文件编码方式
   -- WriteOutFile:write("set names \"utf8\";\n")
      WriteOutFile:write("\nSET FOREIGN_KEY_CHECKS=0;\n")
  
    -- 向sql文件中写入数据
    for i, v in ipairs(sheetlist)do
     local res = exl2sql.CreateSql(tExl, tBook, v, WriteOutFile)
     -- 如果数据写入失败，就删除sql文件
     if res == nil then
       WriteOutFile:close()
      print("ERROR_8: Sheet write fail !")
      os.remove(filename)
      -- 关闭excel文件
        tBook:close()
        return
     end
    end
   
    -- 关闭sql文件
    WriteOutFile:close()
   
    -- 关闭excel文件
    tBook:close()
    tExl.Application:quit()
    tExl=nil

   
    -- 打印
    print("INFO:file: "..file.." to "..filename.." end...success!")
   
    return
end

 

--程序入口
function exl2sql.MainStart()
    -- 创建读取文件类型
   local tExl=luacom.CreateObject("Excel.Application")
  
  -- 获取当前目录下的所有excel文件
  local AllFile, AllFileName = exl2sql.GetAllXlsxFile(lfs.currentdir());
  for i,ofile in ipairs(AllFile) do
    -- 获取单个文件的所有table属性
    local exlAllTable = exl2sql.OpenExcel(tExl, ofile, AllFileName[i])
    --if exlAllTable == nil then return end
    print("\n\n\n")
  end
 
   -- 关闭句柄
   tExl.Application:quit()
    tExl=nil
 
end

 

exl2sql.MainStart()