jmcnamara/xlsxwriter.lua

A lua module for creating Excel XLSX files.

xlsxwriter.lua's Issues

Error: too many open files

This is my nginx error.log

2016/11/15 17:26:20 [error] 999#0: *354064 lua entry thread aborted: runtime error: /usr/local/share/lua/5.1/xlsxwriter/packager.lua:120: /tmp/splice_1479201980915.xlsx: Too many open files
stack traceback:
coroutine 0:
        [C]: in function 'assert'
        /usr/local/share/lua/5.1/xlsxwriter/packager.lua:120: in function '_create_package'
        /usr/local/share/lua/5.1/xlsxwriter/workbook.lua:369: in function '_store_workbook'
        /usr/local/share/lua/5.1/xlsxwriter/workbook.lua:188: in function 'close'
        /usr/local/nginx/html/xlsx-service/splice.lua:57: in function 'xlsxSplice'
        content_by_lua:3: in function <content_by_lua:1>, client: x.x.x.x, server: localhost, request: "POST /xlsx-splice HTTP/1.1", host: "x.x.x.x"
2016/11/15 17:26:20 [crit] 999#0: *354064 open() "/usr/local//nginx/html/50x.html" failed (24: Too many open files), client: x.x.x.x, server: localhost, request: "POST /xlsx-splice HTTP/1.1", host: "x.x.x.x"

nginx -v
nginx version: openresty/1.5.12.1
lua -v
Lua 5.1.5  Copyright (C) 1994-2012 Lua.org, PUC-Rio

I reloaded nginx it works

Feature request: Add missing arguments to Format functions

Some functions of Format class miss the input argument, providing only default value:

• no argument, if not specified, true should be assumed. It should be implemented like set_hidden(value):
  • set_bold()
  • set_italic()
  • set_font_strikeout()
  • set_font_outline()
  • set_font_shadow()
  • set_center_across()
  • set_text_wrap()
  • set_shrink()
  • set_text_justlast()
  • set_font_condense()
  • set_font_extend()
  • others
• value argument provided, but why this time no default value allowed? It should be implemented like set_hidden(value):
  • set_underline(value)
  • set_font_script(value)
  • set_locked(value)
  • set_indent(value)

Problem with Packager

On Windows, starting with Win Vista, we found a privilege problem. Packager:_add_to_zip() uses the function "io.tmpfile()", which, in turn, uses the C-runtime function "tmpfile()". In Windows MSVCRT this ist defined as "create a temporary file on the root of the filesystem". Sadly most often this means, create a file directly unter "C:", which is not possible for normal users. So the xlswriter has to be run using administrative privileges.

Feature request: Add support for charts

This is a sweet project, charting would be great.

Vertical alignment does not work.

I test merge1.lua and I get vertical alignment as bottom not vcenter.
I also try set valign = "center" result is same.

Got build error, when installing it

I want to install lua-xlsxwriter to read/write excel. But I got the following error.

[~] luarocks install xlsxwriter 11:51:37
Installing https://luarocks.org/xlsxwriter-0.0.6-1.src.rock

Error: Build error: Failed installing xlsxwriter/styles.lua in /usr/local/lib/luarocks/rocks-5.3/xlsxwriter/0.0.6-1/lua/xlsxwriter/styles.lua: The source and destination are the same files

or

Error: Build error: Failed installing xlsxwriter/strict.lua in /usr/local/lib/luarocks/rocks-5.3/xlsxwriter/0.0.6-1/lua/xlsxwriter/strict.lua: The source and destination are the same files

I also tried to install it with lua-xlsxwriter

[~] luarocks install lua-xlsxwriter 11:15:56
Installing https://luarocks.org/lua-xlsxwriter-0.0.1-1.src.rock
Missing dependencies for lua-xlsxwriter 0.0.1-1:
struct (not installed)

lua-xlsxwriter 0.0.1-1 depends on struct (not installed)
Installing https://luarocks.org/struct-1.2-2.src.rock

env MACOSX_DEPLOYMENT_TARGET=10.8 gcc -O2 -fPIC -I/usr/local/opt/lua/include/lua5.3 -c struct.c -o struct.o
struct.c:181:27: warning: implicit declaration of function 'luaL_putchar' is
invalid in C99 [-Wimplicit-function-declaration]
while (toalign-- > 0) luaL_putchar(&b, '\0');
^
struct.c:318:37: error: array has incomplete element type
'const struct luaL_reg'
static const struct luaL_reg thislib[] = {
^
struct.c:318:21: note: forward declaration of 'struct luaL_reg'
static const struct luaL_reg thislib[] = {
^
struct.c:326:3: warning: implicit declaration of function 'luaL_register' is
invalid in C99 [-Wimplicit-function-declaration]
luaL_register(L, "struct", thislib);
^
2 warnings and 1 error generated.

Error: Failed installing dependency: https://luarocks.org/struct-1.2-2.src.rock - Build error: Failed compiling object struct.o

Create a xlsxwriter.lua compatible library based on the C library libxlsxwriter

Hi,

I am currently maintaining 4 version of the same Xlsx writing library in Perl, Python, C and Lua:

• https://github.com/jmcnamara/excel-writer-xlsx
• https://github.com/jmcnamara/XlsxWriter
• https://github.com/jmcnamara/libxlsxwriter
• https://github.com/jmcnamara/xlsxwriter.lua

The compatibility between the modules in terms of features is roughly 100%/100%/60%/30%.

The Lua version in particular has fallen behind and I don't have a lot of time to work on it. However, I am progressing the C library, albeit slowly.

If someone was interested they could add a Lua wrapper around the C library and use the xlsxwriter.lua docs and test framework. The API is would match the Python module.

This is mainly as a suggestion for anyone who is interested. I don't intend to write this. :-)

on lua 5.3 xlsxwriter not working

integer and float divided

http://www.lua.org/manual/5.3/manual.html#8.1

The conversion of a float to a string now adds a .0 suffix to the result if it looks like an integer. (For instance, the float 2.0 will be printed as 2.0, not as 2.) You should always use an explicit format when you need a specific format for numbers.

diff --git a/xlsxwriter/utility.lua b/xlsxwriter/utility.lua
index 9f1f636..50bfe1b 100644
--- a/xlsxwriter/utility.lua
+++ b/xlsxwriter/utility.lua
@@ -67,7 +67,8 @@
 -- Convert a zero indexed row and column cell reference to a A1 style string.
 --
 function Utility.rowcol_to_cell(row, col)
-  row = row + 1
+  row = math.modf(row + 1)
+  col = math.modf(col)
   local col_str = Utility.col_to_name_abs(col, false)
   return col_str .. row
 end
@@ -77,7 +78,8 @@
 -- with Excel absolute indexing.
 --
 function Utility.rowcol_to_cell_abs(row, col, row_abs, col_abs)
-  row = row + 1
+  row = math.modf(row + 1)
+  col = math.modf(col)
   row_abs = row_abs and "$" or ""
   local col_str = Utility.col_to_name_abs(col, col_abs)
   return col_str .. row_abs .. row


diff --git a/xlsxwriter/worksheet.lua b/xlsxwriter/worksheet.lua
index 236ed6e..c43c893 100644
--- a/xlsxwriter/worksheet.lua
+++ b/xlsxwriter/worksheet.lua
@@ -2301,7 +2301,7 @@
     height = self.default_row_height
   end

-  local attributes = {{["r"] = r + 1}}
+  local attributes = {{["r"] = math.modf(r + 1)}}

   -- Get the format index.
   if format then
@@ -2564,8 +2564,8 @@
   end

   local attributes = {
-    {["min"]   = firstcol + 1},
-    {["max"]   = lastcol  + 1},
+    {["min"]   = math.modf(firstcol + 1)},
+    {["max"]   = math.modf(lastcol  + 1)},
     {["width"] = width},
   }

and unpack removed from the global scope

diff --git a/xlsxwriter/strict.lua b/xlsxwriter/strict.lua
index f86bbdb..efe1486 100644
--- a/xlsxwriter/strict.lua
+++ b/xlsxwriter/strict.lua
@@ -38,7 +38,11 @@

 mt.__index = function (t, n)
   if not mt.__declared[n] and what() ~= "C" then
-    error("variable '"..n.."' is not declared", 2)
+    if n == "unpack" and t == _G and table[n] then
+      return rawget(table, n)
+    else
+      error("variable '"..n.."' is not declared", 2)
+    end
   end
   return rawget(t, n)
 end

i'm all not tested, but the situation has improved significantly

Please correct in the repository

Rich String Support - Request

Does lua plugin supports rich_string capability. ? Please add this will be helpful.

Error with xlsxwriter in openresty

Had the same error as in #18
After perform advice "remove the require "xlsxwriter.strict" line from all the xlsxwriter/*.lua files and see if that makes OpenResty happy?" - i have got error.

How can i fix the issue?

[string "etlua"]:34: module 'xlsxwriter.workbook' not found:
	no field package.preload['xlsxwriter.workbook']could not load `etlua` file
	no file '/usr/local/openresty/lualib/xlsxwriter/workbook.lua'
	no file '/usr/local/openresty/lualib/xlsxwriter/workbook/init.lua'
	no file './xlsxwriter/workbook.lua'
	no file '/usr/local/openresty/luajit/share/luajit-2.1.0-alpha/xlsxwriter/workbook.lua'
	no file '/usr/local/share/lua/5.1/xlsxwriter/workbook.lua'
	no file '/usr/local/share/lua/5.1/xlsxwriter/workbook/init.lua'
	no file '/usr/local/openresty/luajit/share/lua/5.1/xlsxwriter/workbook.lua'
	no file '/usr/local/openresty/luajit/share/lua/5.1/xlsxwriter/workbook/init.lua'
	no file '/usr/local/openresty/lualib/xlsxwriter/workbook.so'
	no file './xlsxwriter/workbook.so'
	no file '/usr/local/lib/lua/5.1/xlsxwriter/workbook.so'
	no file '/usr/local/openresty/luajit/lib/lua/5.1/xlsxwriter/workbook.so'
	no file '/usr/local/lib/lua/5.1/loadall.so'
	no file '/usr/local/openresty/lualib/xlsxwriter.so'
	no file './xlsxwriter.so'
	no file '/usr/local/lib/lua/5.1/xlsxwriter.so'
	no file '/usr/local/openresty/luajit/lib/lua/5.1/xlsxwriter.so'
	no file '/usr/local/lib/lua/5.1/loadall.so'

Traceback

stack traceback:
	[C]: in function 'require'
	[string "etlua"]:34: in function 'run'
	/usr/local/share/lua/5.1/lapis/etlua.lua:158: in function 'thing'
	/usr/local/share/lua/5.1/lapis/request.lua:89: in function 'write'
	/usr/local/share/lua/5.1/lapis/request.lua:213: in function 'render'
	/usr/local/share/lua/5.1/lapis/application.lua:136: in function </usr/local/share/lua/5.1/lapis/application.lua:135>
	[C]: in function 'xpcall'
	/usr/local/share/lua/5.1/lapis/application.lua:159: in function 'dispatch'
	/usr/local/share/lua/5.1/lapis/nginx.lua:214: in function 'serve'
	content_by_lua:2: in function <content_by_lua:1>

Versions: xlsxwriter/0.0.6-1, Lua 5.1.4, CentOS 6.8, lapis/1.5.1-1
Using xlsxwriter inside Lapis etlua-file:

<!-- views/index.etlua -->
<h1>Hello world</h1>
<p>Welcome to my page</p>

<%     local Workbook = require "xlsxwriter.workbook"
       local workbook  = Workbook:new("hello_world.xlsx")
       local worksheet = workbook:add_worksheet()
       workbook:close()   
%>

Feature request: Some suggestions to API

1. Inherite Formats

Base = w:add_format{...}
Derive = Base:derive{...}
-- if we change value in `Base` value also changes in `Derive`
-- If we set value in `Derive` then all changes in `Base` are ignore.

1. Clone Formats

Base = w:add_format{...}
Derive = Base:clone{...} -- independent

1. set method

format:set{align = 'center'}

1. functions should return self

format
  :set_align(...)
  :set_color(...)

sheet
  :write(...)
  :write(...)

luarocks install error Ubuntu 14.04 with utility.lua

luarock error when inflating xlsxwriter-0.0.6-1.rockspec, "can not stat 'xlsxwriter/utility.lua ': No such file or directory.

terminal line = sudo luarocks install xlsxwriter

build is aborted. I can install anything else including the dependencies just not this. Any idea's why?

zlib not found in ZipWriter dependency.

I search the zlib to install but only have a 'zlib1.dll' found with this name, but this file is in the 'clib' lua folder.

The API don't function in windows?

------------------- OUTPUT
C:\Program Files (x86)\Lua\luarocks3.3.1\src>lua C:\Users\GanGss\Documents@\LUA\xlsxwriter\xlsxwriter.lua
lua: ... Files (x86)\Lua\5.1\systree\share\lua\5.1\ZipWriter.lua:51: module 'zlib' not found:
No LuaRocks module found for zlib
no field package.preload['zlib']
no file 'C:\Program Files (x86)\Lua\5.1\systree\share\lua\5.1\zlib.lua'
no file 'C:\Program Files (x86)\Lua\luarocks3.3.1\src\lua\zlib.lua'
no file 'C:\Program Files (x86)\Lua\luarocks3.3.1\src\lua\zlib\init.lua'
no file 'C:\Program Files (x86)\Lua\luarocks3.3.1\src\zlib.lua'
no file 'C:\Program Files (x86)\Lua\luarocks3.3.1\src\zlib\init.lua'
no file 'C:\Program Files (x86)\Lua\luarocks3.3.1\src..\share\lua\5.4\zlib.lua'
no file 'C:\Program Files (x86)\Lua\luarocks3.3.1\src..\share\lua\5.4\zlib\init.lua'
no file '.\zlib.lua'
no file '.\zlib\init.lua'
no file 'C:\Program Files (x86)\Lua\5.1\lua\zlib.luac'
no file 'C:\Users\GanGss\AppData\Roaming/luarocks/share/lua/5.4/zlib.lua'
no file 'C:\Users\GanGss\AppData\Roaming/luarocks/share/lua/5.4/zlib/init.lua'
no file 'C:\Program Files (x86)\Lua\luarocks3.3.1\src\zlib.dll'
no file 'C:\Program Files (x86)\Lua\luarocks3.3.1\src..\lib\lua\5.4\zlib.dll'
no file 'C:\Program Files (x86)\Lua\luarocks3.3.1\src\loadall.dll'
no file '.\zlib.dll'
no file 'C:\Users\GanGss\AppData\Roaming/luarocks/lib/lua/5.4/zlib.dll'
stack traceback:
[C]: in function 'require'
... Files (x86)\Lua\5.1\systree\share\lua\5.1\ZipWriter.lua:51: in main chunk
[C]: in function 'require'
...6)\Lua\5.1\systree\share\lua\5.1\xlsxwriter\packager.lua:44: in main chunk
[C]: in function 'require'
...6)\Lua\5.1\systree\share\lua\5.1\xlsxwriter\workbook.lua:11: in main chunk
[C]: in function 'require'
C:\Users\GanGss\Documents@\LUA\xlsxwriter\xlsxwriter.lua:9: in main chunk
[C]: in ?

Does not create global function.

xmlwriter.lua define global functions : _escape_data _escape_attributes _format_attributes_unencoded _format_attributes.
Also worksheet.lua try use Utility._escape_data but there no such function.

Feature request: Insert images

Is it possible?
In documentation I found mantion in Known Issues and Bugs insert_image function.

Working with 'constant_memory'?

I want to generate an excel sheet with ~20.000 rows and 74 columns.
I took a look into the documentation "Working with Memory and Performance" and after reading in approximately 35 different forums, I have no idea how to use

for row in range(0, row_max):
for col in range(0, col_max):
worksheet.write(row, col, some_data)

What is "some_data"?

A list (of all values in the table) (1)? A list of all rows (as lists) (1)? A tuple of lists (1)? Or a special command (2)? How do I grab the inside of the cell of the specific number of the row and column at this point of the code? It is sadly not even slightly explained in the documentation.

(1) does not work
(2) no idea what command

Feature request: function protect()

Hi John,
first of all thanks for you work :) This feature request is about the function protect() which looks not implemented. According to the documentation and in order to have a cell protected the following lines should be executed:

locked = workbook:add_format()
locked:set_locked(true)

unlocked = workbook:add_format()
locked:set_locked(false)

-- Enable worksheet protection
worksheet:protect()

-- This cell cannot be edited.
worksheet:write("A1", "=1+2", locked)

-- This cell can be edited.
worksheet:write("A2", "=1+2", unlocked)

..but I've come across an error:

/usr/bin/lua: xxxxxxxxx: attempt to call method 'protect' (a boolean value)

Thanks a lot!

Error when using xlsxwriter in openresty

I use the following command to install xlsxwriter:

    git clone https://github.com/jmcnamara/xlsxwriter.lua.git
    cd xlsxwriter.lua
    sudo luarocks make

It installed successfully, but when I tried to use it, nginx report failure.

user root;
worker_processes  1;
error_log logs/error.log;
events {
    worker_connections 1024;
}
http {
    server {
        listen 80;
        location / {
            default_type text/html;
            content_by_lua_block {
                ngx.say(package.path);
                local test0 = require("luarocks.loader");
                local workbook = require("xlsxwriter.workbook");
            }
        }
    }
}

I find this in the /nginx/error.log:

2016/08/24 23:21:08 [notice] 2801#0: signal process started
2016/08/24 23:21:12 [error] 2802#0: *3 lua entry thread aborted: runtime error: ...l/openresty/luajit/share/lua/5.1/xlsxwriter/workbook.lua:8: variable 'require' is not declared
stack traceback:
coroutine 0:
    [C]: in function 'require'
    content_by_lua(nginx.conf:17):4: in function <content_by_lua(nginx.conf:17):1>, client: 127.0.0.1, server: , request: "GET / HTTP/1.1", host: "localhost"

It seems something wrong with /workbook.lua. I wonder how to fix it.

Thanks in advance.