[SOLVED] How to read and parse excel files in Lua?

How to read and parse excel files in Lua?

I want to read a xls file and then parse it. How can i do it with Lua? Thanks.

Solution

This isn't a duplicate, since he actually just wants to read from an excel file and parse the data - not manipulate an excel object.

Whenever I had to do this, I used luasql using ADO. at the most basic level, something like this is what you'd use to open a db connection and read excel data from it if you know how many fields are going to be in each row of your query.

function rows(connection, sql_stmt)
    local cursor = assert(connection:execute(sql_stmt))
    return function() 
        return cursor:fetch()
    end
end

local fpath = "path/to/file.xlxs"
local constr = "Provider=Microsoft.ACE.OLEDB.12.0;"..
               "Data Source=\""..fpath.."\";".. 
               "Extended Properties=\"Excel 12.0 Xml;HDR=YES\"";
                   
local env = assert(luasql.ado())
local con = assert(env:connect(constr))

-- the name of the worksheet needs to be surrounded by brackets, and end
-- with a '$' sign.
local query = "SELECT * FROM \[name_of_worksheet$\]"

-- you can do this if you know how many fields you get back specifically.
for field1, field2, field3, ... in rows(con, query) do      
    -- handle any parsing of data from a row, here.
end

con:close()
env:close()

if you don't know how many fields you're getting back, you can do something like this:

local fpath = "path/to/file.xlxs"
local constr = "Provider=Microsoft.ACE.OLEDB.12.0;"..
               "Data Source=\""..fpath.."\";"..
               "Extended Properties=\"Excel 12.0 Xml;HDR=YES\"";
                   
local env = assert(luasql.ado())
local con = assert(env:connect(constr))

-- the name of the worksheet needs to be surrounded by brackets, and end
-- with a '$' sign.
local query = "SELECT * FROM \[name_of_worksheet$\]"


-- if you didn't know how many fields you might get back per row, i 
-- believe you can do this to iterate over each row...
local cursor = con:execute(query)
local t = {}
local results = cursor:fetch(t)

while results ~= nil do
    for k,v in pairs(results) do
        -- check each field in the row here
    end
    results = con:fetch(t)
end

cursor:close()
con:close()
env:close()

make sure to close your cursors/connections/environments when you're done with them. also, you'll want to verify that the connection string above will work with your version of excel. the easiest way to determine which connection string you need is to go to www.connectionstrings.com and browse for the correct one.