利用Luacom进行Excel文件的操作
主要参考了AutoIt3的UDF库的实现，将会持续更新
更新历史：
2008-10-28 增加_ExcelSheetList,_ExcelSheetActivate函数
2008-10-28 由于Luacom内部应该是支持UTF-8，所以通过C API写了一个Lua的扩展库，对字符串ANSI <-> Unicode <-> UTF-8 进行转换处理，从而可以正常打开中文文件名，详见
http://hi.baidu.com/nivrrex/blog/item/17c231adad9e8a0f4b36d6ca.html
对_ExcelBookSaveAs有轻微修改
2008-10-05 初次版本，(似乎有部分问题，比如打开中文文件名的问题)
实现了 _ExcelBookNew,_ExcelBookOpen,_ExcelWriteCell,_ExcelReadCell,_ExcelBookSave,_ExcelBookSaveAs, _ExcelBookClose 等7个函数
代码如下：
--新建Excel文件
function _ExcelBookNew(Visible)
local Excel = luacom.CreateObject("Excel.Application")
if Excel == nil then error("Object is not create") end
--处理是否可见
if tonumber(Visible) == nil then error("Visible is not a number") end
if Visible == nil then Visible = 1 end
if Visible > 1 then Visible = 1 end
if Visible < 0 then Visible = 0 end
oExcel.Visible = Visible
oExcel.WorkBooks:Add()
oExcel.ActiveWorkbook.Sheets(1):Select()
return oExcel
end
--打开已有的Excel文件
function _ExcelBookOpen(FilePath,Visible,ReadOnly)
local Excel = luacom.CreateObject("Excel.Application")
if Excel == nil then error("Object is not create") end
--查看文件是否存在
local t=io.open(FilePath,"r")
if t == nil then
--文件不存在时的处理
oExcel.Application:quit()
oExcel=nil
error("File is not exists")
else
t:close()
end
--处理是否可见ReadOnly
if Visible == nil then Visible = 1 end
if tonumber(Visible) == nil then error("Visible is not a number") end
if Visible > 1 then Visible = 1 end
if Visible < 0 then Visible = 0 end
--处理是否只读
if ReadOnly == nil then ReadOnly = 0 end
if tonumber(ReadOnly) == nil then error("ReadOnly is not a number") end
if ReadOnly > 1 then ReadOnly = 1 end
if ReadOnly < 0 then ReadOnly = 0 end
oExcel.Visible = Visible
--打开指定文件
oExcel.WorkBooks:Open(FilePath,nil,ReadOnly)
oExcel.ActiveWorkbook.Sheets(1):Select()
return oExcel
end
--写入Cells数据
function _ExcelWriteCell(oExcel,Value,Row,Column)
--验证参数
if Excel == nil then error("oExcel is not a object!") end
if tonumber(Row) == nil or Row < 1 then error("Row is not a valid number!") end
if tonumber(Column) == nil or Column < 1 then error("Column is not a valid number!") end
--对指定Cell位置赋值
oExcel.Activesheet.Cells(Row, Column).Value2 = Value
return 1
end
--读取Cells数据
function _ExcelReadCell(oExcel,Row,Column)
--验证参数
if Excel == nil then error("oExcel is not a object!") end
if tonumber(Row) == nil or Row < 1 then error("Row is not a valid number!") end
if tonumber(Column) == nil or Column < 1 then error("returnColumn is not a valid number!") end
--返回指定Cell位置值
return oExcel.Activesheet.Cells(Row, Column).Value2
end
--保存Excel文件
function _ExcelBookSave(oExcel, Alerts)
--验证参数
if Excel == nil then error("oExcel is not a object!") end
--处理是否提示
if Alerts == nil then Alerts = 0 end
if tonumber(Alerts) == nil then error("Alerts is not a number") end
if Alerts > 1 then Alerts = 1 end
if Alerts < 0 then Alerts = 0 end
oExcel.Application.DisplayAlerts = Alerts
oExcel.Application.ScreenUpdating = Alerts
--进行保存
oExcel.ActiveWorkBook:Save()
if not Alerts then
oExcel.Application.DisplayAlerts = 1
oExcel.Application.ScreenUpdating = 1
end
return 1
end
--另存Excel文件
function _ExcelBookSaveAs(oExcel,FilePath,Type,Alerts,OverWrite)
--验证参数
if Excel == nil then error("oExcel is not a object!") end
--处理保存文件类型
if Type == nil then Type = "xls" end
if Type == "xls" or Type == "csv" or Type == "txt" or Type == "template" or Type == "html" then
if Type == "xls" then Type = -4143 end -- xlWorkbookNormal
if Type == "csv" then Type = 6 end -- xlCSV
if Type == "txt" then Type = -4158 end -- xlCurrentPlatformText
if Type == "template" then Type = 17 end -- xlTemplate
if Type == "html" then Type = 44 end -- xlHtml
else
error("Type is not a valid type")
end
--处理是否提示
if Alerts == nil then Alerts = 0 end
if tonumber(Alerts) == nil then error("Alerts is not a number") end
if Alerts > 1 then Alerts = 1 end
if Alerts < 0 then Alerts = 0 end
oExcel.Application.DisplayAlerts = Alerts
oExcel.Application.ScreenUpdating = Alerts
--处理文件是否OverWrite
if verWrite == nil then verWrite = 0 end
--查看文件是否存在
local t=io.open(FilePath,"r")
--如果文件存在且OverWrite参数为0，返回错误
if not t == nil then
if not OverWrite then
t:close()
error("Can't overwrite the file!")
end
t:close()
os.remove(FilePath)
end
--保存文件
if FilePath == nil then error("FilePath is not valid !") end
--使用ActiveWorkBook时，在已经打开文件时，无法另存，所以使用WorkBookS(1)进行处理
oExcel.WorkBookS(1):SaveAs(FilePath,Type)
--继续处理Alerts参数，以便继续使用
if not Alerts then
oExcel.Application.DisplayAlerts = 1
oExcel.Application.ScreenUpdating = 1
end
return 1
end
--关闭Excel文件
function _ExcelBookClose(oExcel,Save,Alerts)
--验证参数
if Excel == nil then error("oExcel is not a object!") end
--处理是否保存
if Save == nil then Save = 1 end
if tonumber(Save) == nil then error("Save is not a number") end
if Save > 1 then Save = 1 end
if Save < 0 then Save = 0 end
--处理是否提示
if Alerts == nil then Alerts = 0 end
if tonumber(Alerts) == nil then error("Alerts is not a number") end
if Alerts > 1 then Alerts = 1 end
if Alerts < 0 then Alerts = 0 end
if Save == 1 then oExcel.ActiveWorkBook:save() end
oExcel.Application.DisplayAlerts = Alerts
oExcel.Application.ScreenUpdating = Alerts
oExcel.Application:Quit()
return 1
end
--列出所有Sheet
function _ExcelSheetList(oExcel)
--验证参数
if Excel == nil then error("oExcel is not a object!") end
local temp = oExcel.ActiveWorkbook.Sheets.Count
local tab = {}
tab[0] = temp
for i = 1,temp do
tab[i] = oExcel.ActiveWorkbook.Sheets(i).Name
end
--返回一个table，其中tab[0]为个数
return tab
end
--激活指定的sheet
function _ExcelSheetActivate(oExcel, vSheet)
local tab = {}
local found = 0
--验证参数
if Excel == nil then error("oExcel is not a object!") end
--设置默认sheet为1
if vSheet == nil then vSheet = 1 end
--如果提供参数为数字
if tonumber(vSheet) ~= nil then
if oExcel.ActiveWorkbook.Sheets.Count < tonumber(vSheet) then error("The sheet value is to biger!") end
--如果提供参数为字符
else
tab = _ExcelSheetList(oExcel)
for i = 1 , tab[0] do
if tab[i] == vSheet then found = 1 end
end
if found ~= 1 then error("Can't find the sheet") end
end
oExcel.ActiveWorkbook.Sheets(vSheet):Select ()
return 1
end
--参数基本做到了可以省略
require('Unicode')
b=assert(_ExcelBookOpen("c:\\d.xls"))
assert(_ExcelSheetActivate(b))
assert(_ExcelWriteCell(b,Unicode.a2u8("哈哈"),1,1))
assert(_ExcelBookSave(b,1))
assert(_ExcelBookClose(b))
b=assert(_ExcelBookNew(1))
tab=assert(_ExcelSheetList(b))
for i,v in pairs(tab) do
print(i,v)
end
assert(_ExcelSheetActivate(b,"Sheet2"))
--b=assert(_ExcelBookOpen("c:\\d.xls",1,0))
assert(_ExcelWriteCell(b,"haha",1,1))
assert(_ExcelBookSaveAs(b,"c:\\a","txt",0,0))
print(_ExcelReadCell(b,1,1))
assert(_ExcelBookClose(b))
