Note, in this mode a row of data is written and then discarded when a cell in a new row is added via one of the worksheet write_() methods. Therefore, once this mode is active, data should be written in sequential row order.
See memory_perf for more details.
When specifying a filename it is recommended that you use an .xlsx extension or Excel will generate a warning when opening the file.
workbook:add_worksheet()
add_worksheet([sheetname])
Add a new worksheet to a workbook:
param sheetname
Optional worksheet name, defaults to Sheet1, etc.
rtype
A worksheet <Worksheet> object.
The add_worksheet() method adds a new worksheet to a workbook.
At least one worksheet should be added to a new workbook. The Worksheet <worksheet> object is used to write data and configure a worksheet in the workbook.
The sheetname parameter is optional. If it is not specified the default Excel convention will be followed, i.e. Sheet1, Sheet2, etc.:
The worksheet name must be a valid Excel worksheet name, i.e. it cannot contain any of the characters [ ] : * ? / \ and it must be less than 32 characters.
In addition, you cannot use the same, case insensitive, sheetname for more than one worksheet.
workbook:add_format()
add_format([properties])
Create a new Format object to formats cells in worksheets.
paramionary properties
An optional table of format properties.
rtype
A Format <Format> object.
The add_format() method can be used to create new Format <Format> objects which are used to apply formatting to a cell. You can either define the properties at creation time via a table of property values or later via method calls:
format1 = workbook:add_format(props) -- Set properties at creation.
format2 = workbook:add_format() -- Set properties later.
See the format and working_with_formats sections for more details about Format properties and how to set them.
workbook:close()
close()
Close the Workbook object and write the XLSX file.
This should be done for every file.
workbook:close()
Currently, there is no implicit close().
workbook:set_properties()
set_properties()
Set the document properties such as Title, Author etc.
param properties
Dictionary of document properties.
The set_properties method can be used to set the document properties of the Excel file created by xlsxwriter. These properties are visible when you use the Office Button -> Prepare -> Properties option in Excel and are also available to external applications that read or index windows files.
The properties that can be set are:
title
subject
author
manager
company
category
keywords
comments
status
The properties are all optional and should be passed in table format as follows:
workbook:set_properties({
title = 'This is an example spreadsheet',
subject = 'With document properties',
author = 'John McNamara',
manager = 'Dr. Heinz Doofenshmirtz',
company = 'of Wolves',
category = 'Example spreadsheets',
keywords = 'Sample, Example, Properties',
comments = 'Created with Lua and the xlsxwriter module'})
See also ex_doc_properties.
workbook:define_name()
define_name()
Create a defined name in the workbook to use as a variable.
param name
The defined name.
param formula
The cell or range that the defined name refers to.
This method is used to defined a name that can be used to represent a value, a single cell or a range of cells in a workbook: These defined names can then be used in formulas:
Return a sequence of the worksheet objects in the workbook:
rtype
A sequence of worksheet <Worksheet> objects.
The worksheets() method returns a table/sequence of the worksheets in a workbook suitable for iteration with ipairs(). This is useful if you want to repeat an operation on each worksheet in a workbook:
for _, worksheet in ipairs(workbook:worksheets()) do
worksheet:write("A1", "Hello")
end