require('luacom')
local iconv = require('luaiconv')
local iconvGbk2Utf8 = iconv.new("utf-8", "gbk")
function gbk2utf8(str)
return iconvGbk2Utf8:iconv(str)
end
local iconvUtf82Gbk = iconv.new("gbk", "utf-8")
function utf82GBK(str)
return iconvUtf82Gbk:iconv(str)
end
function IsFileExists(path)
local file = io.open(path, "rb")
if file then file:close() end
return file ~= nil
end
-- 获取excel内容
function GetContent( strFilePath )
local excel = luacom.CreateObject("Excel.Application")
if excel == nil then
excel = luacom.CreateObject("Ket.Appplication")
end
assert(excel ~= nil, "Neither Excel nor WPS is installed. Please install and try again.")
excel.Visible = false
assert(IsFileExists(strFilePath),"\""..strFilePath.."\",No Such File! ")
strFilePath = gbk2utf8(strFilePath)
local book = excel.WorkBooks:Open(strFilePath,0,1) --ReadOnly open
assert(book ~= nil, "Open "..strFilePath.." Failed")
local sheet = book.ActiveSheet
assert(sheet ~= nil, "empty sheet!")
local nColCount =sheet.UsedRange.Columns.Count
local nRowCount =sheet.UsedRange.Rows.Count
local tbExcelData = {}
for nRow = 1, nRowCount do
local tbRowData = {}
for nCol = 1, nColCount do
local str = tostring(sheet.Cells(nRow,nCol).Value2)
str = utf82GBK(str)
str = string.gsub(str,"\n","")
if str == "nil" then
str = ""
end
table.insert(tbRowData,str)
end
table.insert(tbExcelData,tbRowData)
end
--close File
excel.DisplayAlerts = false
book:Close()
CloseWindow(excel.hWnd) --获取excel窗口句柄,通过excel句柄关闭进程
--[[
book:Close()
sheet = nil
book = nil
excel:Quit()
excel = nil
collectgarbage()
]]--
return tbExcelData,nRowCount,nColCount
end