Lua read xlsx example

xlsx_read.lua

require('zip')
require('xml_parse')

local excel = zip_open('excel/test.xlsx')
if not excel then return end

local flag
flag = excel:find('xl/workbook.xml')
if flag then
  local str = excel:read('*a')
  local workbook = xml_parse({ string = str })
  local sheets = workbook.tags['workbook'].tags['sheets'].children
end

flag = excel:find('xl/sharedStrings.xml')
local shared_strs = {}
if flag then
  local str = excel:read('*a')
  local sst = xml_parse({ string = str })
  for i, v in ipairs(sst.tags['sst'].children) do
    if v.tags['t'] then
      shared_strs[tostring(i - 1)] = v.tags['t'].cdata
    elseif v.children[1] then
      local cache = {}
      for j, r in ipairs(v.children) do
        if r.tag == 'phoneticPr' then break end
        cache[j] = r.tags['t'].cdata
      end
      shared_strs[tostring(i - 1)] = table.concat(cache)
    else
      shared_strs[tostring(i - 1)] = ''
    end
  end
end

flag = excel:find('xl/styles.xml')
local nf_defs = {
  ['14'] = 'date', ['15'] = 'date', ['16'] = 'date', ['17'] = 'date', ['27'] = 'date', 
  ['28'] = 'date', ['29'] = 'date', ['30'] = 'date', ['31'] = 'date', ['36'] = 'date', 
  ['50'] = 'date', ['51'] = 'date', ['52'] = 'date', ['53'] = 'date', ['54'] = 'date', 
  ['57'] = 'date', ['58'] = 'date', 
  ['18'] = 'time', ['19'] = 'time', ['20'] = 'time', ['21'] = 'time', ['22'] = 'datetime', 
  ['32'] = 'time', ['33'] = 'time', ['34'] = 'time', ['35'] = 'time', ['55'] = 'time',
  ['56'] = 'time',
  ['9'] = 'percent', ['10'] = 'percent'
}
local nf_s = {}
if flag then
  local str = excel:read('*a')
  local styles = xml_parse({ string = str })
  local stylesheet = styles.tags['styleSheet']
  if stylesheet.tags['numFmts'] then
    for _, nf in ipairs(stylesheet.tags['numFmts'].children) do
      local fmt_id = nf.attrs['numFmtId']
      local fmt_code = nf.attrs['formatCode']
      if fmt_code:match('yy') or fmt_code:match('mmm') then
        nf_defs[fmt_id] = 'date'
      end
      if fmt_code:match('ss') then
        if nf_defs[fmt_id] then
          nf_defs[fmt_id] = 'datetime'
        else
          nf_defs[fmt_id] = 'time'
        end
      end
    end
  end
  if stylesheet.tags['cellXfs'] then
    for i, nf_link in ipairs(stylesheet.tags['cellXfs'].children) do
      nf_s[tostring(i - 1)] = nf_defs[nf_link.attrs['numFmtId']]
    end
  end
end

local floor = math.floor
local DAYSIZE = 86400
local function toYMD_1900(ord)
  if ord >= 0 then
    if ord == 0 then
      return { year=1900, month=1, day=0 }
    elseif ord == 60 then
      return { year=1900, month=2, day=29 }
    elseif ord < 60 then
      return { year=1900, month=(ord < 32 and 1 or 2), day=((ord - 1) % 31) + 1 }
    end
  end
  local l = ord + 68569 + 2415019;
  local n = floor((4 * l) / 146097);
  l = l - floor((146097 * n + 3) / 4);
  local i = floor((4000 * (l + 1)) / 1461001);
  l = l - floor((1461 * i) / 4) + 31;
  local j = floor((80 * l) / 2447);
  local nDay = l - floor((2447 * j) / 80);
  l = floor(j / 11);
  local nMonth = j + 2 - (12 * l);
  local nYear = 100 * (n - 49) + i + l;
  return { year=nYear, month=nMonth, day=nDay }
end

local function date_from_serial(value)
  local date = floor(value)
  local t = DAYSIZE * (value - date)
  local time = floor(t) -- in seconds
  -- date "epsilon" correction
  if (t - time) > 0.9999 then
    time = time + 1;
    if time == DAYSIZE then
      time = 0;
      date = date + 1;
    end
  end
  -- serial date/time to gregorian calendar
  local x = (time < 0) and (DAYSIZE + time) or time;
  local date_t = toYMD_1900(date);
  date_t.hh = floor((x / 60) / 60) % 60;
  date_t.mm = floor(x / 60) % 60;
  date_t.ss = floor(x) % 60;
  return date_t
end

flag = excel:find('xl/worksheets/sheet1.xml')
if flag then
  local str = excel:read('*a')
  local sheet = xml_parse({ string = str })
  local s_data = sheet.tags['worksheet'].tags['sheetData']
  
  for _, row_node in ipairs(s_data.children) do
    local row_num = tonumber(row_node.attrs['r'])
    for _, col_node in ipairs(row_node.children) do
      local cell_id = col_node.attrs['r']
      local col_type = col_node.attrs['t']
      
      local data = col_node.children[1] and col_node.children[1].cdata
      if col_type == 's' then
        data = shared_strs[data]
      elseif col_type == 'b' then
        data = data == '1'
      else
        local num_fmt_no = col_node.attrs['s']
        if num_fmt_no then
          if nf_s[num_fmt_no] == 'date' then
            data = os.date('%Y-%m-%d', os.time(date_from_serial(tonumber(data))))
          elseif nf_s[num_fmt_no] == 'datetime' then
            data = os.date('%c', os.time(date_from_serial(tonumber(data))))
          elseif nf_s[num_fmt_no] == 'time' then
            local time_t = date_from_serial(tonumber(data))
            data = string.format('%02d:%02d:%02d', time_t.hh, time_t.mm, time_t.ss)
          elseif nf_s[num_fmt_no] == 'percent' then
            data = (data * 100) .. '%'
          end
        end
      end
      print(cell_id, data)
    end
  end
end