Allow toxlsx() to overwrite add or replace a worksheet
# -*- coding: utf-8 -*-
from __future__ import absolute_import, print_function, division
-import locale
-
+from petl.compat import PY3
from petl.util.base import Table, data
from petl.io.sources import read_source_from_arg, write_source_from_arg
@@ -87,15 +86,52 @@ class XLSXView(Table):
pass
-def toxlsx(tbl, filename, sheet=None, write_header=True):
+def toxlsx(tbl, filename, sheet=None, write_header=True, mode="replace"):
"""
Write a table to a new Excel .xlsx file.
- """
+ N.B., the sheet name is case sensitive.
+ The `mode` argument controls how the file and sheet are treated:
+ - `replace`: This is the default. It either replaces or adds a
+ named sheet, or if no sheet name is provided, all sheets
+ (overwrites the entire file).
+ - `overwrite`: Always overwrites the file. This produces a file
+ with a single sheet.
+ - `add`: Adds a new sheet. Raises `ValueError` if a named sheet
+ already exists.
+ The `sheet` argument can be omitted in all cases. The new sheet
+ will then get a default name.
+ If the file does not exist, it will be created, unless `replace`
+ mode is used with a named sheet. In the latter case, the file
+ must exist and be a valid .xlsx file.
+ """
import openpyxl
+ if mode == "overwrite" or (mode == "replace" and sheet is None):
+ wb = openpyxl.Workbook(write_only=True)
+ ws = wb.create_sheet(title=sheet)
+ elif mode == "replace":
+ if PY3:
+ FileNotFound = FileNotFoundError
+ else:
+ FileNotFound = IOError
+ try:
+ wb = openpyxl.load_workbook(filename=filename, read_only=False)
+ except FileNotFound:
wb = openpyxl.Workbook(write_only=True)
+ try:
+ ws = wb[str(sheet)]
+ ws.delete_rows(1, ws.max_row)
+ except KeyError:
+ ws = wb.create_sheet(title=sheet)
+ elif mode == "add":
+ wb = openpyxl.load_workbook(filename=filename, read_only=False)
ws = wb.create_sheet(title=sheet)
+ # wb.create_sheet(title="foo") creates "foo1" if "foo" exists.
+ if sheet is not None and ws.title != sheet:
+ raise ValueError("Sheet %s already exists in file" % sheet)
+ else:
+ raise ValueError("Unknown mode '%s'" % mode)
if write_header:
rows = tbl
else:
