Lua通过COM调用外部程序excel及调用windows api

最新推荐文章于 2024-08-15 17:15:08 发布

转载最新推荐文章于 2024-08-15 17:15:08 发布 · 2.6k 阅读

文章标签：

#excel #windows #lua #object #function #callback

LUA 专栏收录该内容

20 篇文章

订阅专栏

本文介绍使用Lua语言通过Luacom库操作Excel的方法，包括创建、读写单元格、图表生成及条件格式设置等。此外，还展示了如何利用Lua调用Windows API实现消息框显示及窗口枚举等功能。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

为了方便起见，最好安装lua for windows，里面已经包含了很多有用的第三方模块。

使用lua调用excel，然后往cell 里面填一些数据

require('luacom')
--Excelの起動
excel = luacom .CreateObject("Excel.Application")
excel.Visible = true --可視状態に
--ワークブックを追加
local book = excel .Workbooks:Add()
local sheet = book .Worksheets(1)
--適当な値を100個書き込む
for row = 1 ,100 do
sheet.Cells(row, 1).Value2 = math .floor(math.random() * 20)
end

稍微复杂一些的代码

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))