稍微复杂一些的代码
require “luacom” excel = luacom.CreateObject(“Excel.Application”) local book = excel.Workbooks:Add() local sheet = book.Worksheets(1) excel.Visible = true
– 適当な値を書き込む for row=1,30 do for col=1,30 do sheet.Cells(row, col).Value2 = math.floor(math.random() * 100) end end – 値を調べて50以上のものを黄色でマークする local range = sheet:Range(“A1″) for row=1,30 do
for col=1,30 do local v = sheet.Cells(row, col).Value2 if v > 50 then local cell = range:Offset(row-1, col-1) cell:Select() excel.Selection.Interior.Color = 65535 end end
end
excel.DisplayAlerts = false — 終了確認を出さないようにする
excel:Quit() excel = nil
如果想给excel加个图表该怎么做？
require “luacom” excel = luacom.CreateObject(“Excel.Application”) local book = excel.Workbooks:Add() local sheet = book.Worksheets(1) excel.Visible = true
for row=1,30 do sheet.Cells(row, 1).Value2 = math.floor(math.random() * 100) end
local chart = excel.Charts:Add() chart.ChartType = 4 — xlLine local range = sheet:Range(“A1:A30″)
chart:SetSourceData(range)
如果想调用windows api，可以用下面的代码
require “alien”
MessageBox = alien.User32.MessageBoxA MessageBox:types{ret = ‘long’, abi = ’stdcall’, ‘long’, ’string’, ’string’, ‘long’ }
MessageBox(0, “title for test”, “LUA call windows api”, 0)
如何实现回调函数呢？下面的例子展示了回调。
require ‘alien’ –声明了两个函数EnumWindows和GetClassName EnumWindows = alien.user32.EnumWindows EnumWindows:types {“callback”, “pointer”, abi=”stdcall”}
GetClassName = alien.user32.GetClassNameA GetClassName:types {“long”, “pointer”, “int”, abi=”stdcall” }
local buf = alien.buffer(512)
– 会被EnumWindows反复调用，传入windows的handle local function enum_func(hwnd, p)
GetClassName(hwnd, buf, 511) print (hwnd..”:”..tostring(buf)) return 1 end local callback_func = alien.callback( enum_func, {“int”, “pointer”, abi=”stdcall”})
EnumWindows(callback_func, nil)
其中函数原型是
BOOL EnumWindows(WNDENUMPROC lpEnumFunc , LPARAM lParam ); int GetClassName(HWND hWnd , LPTSTR lpClassName , int nMaxCount ); 其中EnumWindows第一个参数的原型为，这个函数是客户调用时候传入，EnumWindows用它返回 BOOL CALLBACK EnumWindowsProc(HWND hwnd , LPARAM lParam ); 其他复杂的使用方法可以参考alien的文档。这些代码都来自www.hakkaku.net/articles/20090615-459
利用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个函数 代码如下：
require( ' luacom' ) --新建Excel文件 function _ExcelBookNew(Visible) local oExcel = luacom.CreateObject( " Excel.Application" ) if oExcel == 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 oExcel = luacom.CreateObject( " Excel.Application" ) if oExcel == 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 oExcel == 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 oExcel == 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 oExcel == 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 oExcel == 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 OverWrite == nil then OverWrite = 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 oExcel == 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 oExcel == 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 oExcel == 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))