excelparsingluacoronasdkxls

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.