Source code for domdf_spreadsheet_tools

#!/usr/bin/env python
Tools for creating and formatting spreadsheets with Python and OpenPyXL.
#  Copyright 2018-2020 Dominic Davis-Foster <>
#  This program is free software; you can redistribute it and/or modify
#  it under the terms of the GNU Lesser General Public License as published by
#  the Free Software Foundation; either version 3 of the License, or
#  (at your option) any later version.
#  This program is distributed in the hope that it will be useful,
#  but WITHOUT ANY WARRANTY; without even the implied warranty of
#  GNU Lesser General Public License for more details.
#  You should have received a copy of the GNU Lesser General Public License
#  along with this program; if not, write to the Free Software
#  Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston,
#  MA 02110-1301, USA.

# stdlib
import csv
import locale
import os
import traceback
from typing import Any, Dict, List, Mapping, Optional, Union

# 3rd party
from domdf_python_tools.typing import PathLike
from openpyxl import Workbook, load_workbook  # type: ignore[import]
from openpyxl.styles import Alignment  # type: ignore[import]
from openpyxl.utils import get_column_letter  # type: ignore[import]
from openpyxl.worksheet.worksheet import Worksheet  # type: ignore[import]

__author__ = "Dominic Davis-Foster"
__copyright__ = "Copyright 2018-2020 Dominic Davis-Foster"
__license__ = "LGPLv3+"
__version__ = "0.1.6"
__email__ = ""

__all__ = ["append_to_xlsx", "format_header", "format_sheet", "make_column_property_dict", "col_width_from_cm"]

[docs]def append_to_xlsx( csv_input_file: PathLike, xlsx_output_file: PathLike, sheet_title: Optional[str] = None, separator: str = ',', overwrite: bool = False, to_floats: bool = False, encoding: str = "UTF-8" ) -> None: """ Add CSV file to xlsx file as a new worksheet. :param csv_input_file: filepath of CSV file. :param xlsx_output_file: filepath of xlsx file. :param sheet_title: Title of sheet to append. Default is the value of ``csv_input_file``. :no-default sheet_title: :param separator: Separator for reading CSV file. :param overwrite: Whether to overwrite the xlsx output file (i.e. create a new file containing just the new sheet). :param to_floats: Whether to read strings with thousand separators as floats. :param encoding: The encoding to read the file as. """ # Setup for reading strings with thousand separators as floats # From locale.setlocale(locale.LC_ALL, '') if sheet_title is None: sheet_title = os.path.splitext(os.path.basename(csv_input_file))[0] if overwrite: wb = Workbook() ws = wb.remove_sheet(ws) else: wb = load_workbook(xlsx_output_file) wb.create_sheet(sheet_title) ws = wb[sheet_title] with open(csv_input_file, encoding=encoding) as f: reader = csv.reader(f, delimiter=separator) for row in reader: try: if to_floats: row_buffer: List[Union[str, float]] = [] for cell in row: try: row_buffer.append(locale.atof(cell)) except BaseException: row_buffer.append(cell) ws.append(row_buffer) else: ws.append(row) except BaseException: traceback.print_exc() # print the error print(row)
[docs]def format_sheet( ws: Worksheet, number_format_list: Optional[Dict[str, str]] = None, width_list: Optional[Dict[str, float]] = None, alignment_list: Optional[Dict[str, str]] = None, ) -> None: """ Format columns of an xlsx worksheet. :param ws: The worksheet to format. :type ws: :class:`openpyxl.worksheet.worksheet.Worksheet`. :param number_format_list: dictionary of number format strings for each column letter. :param width_list: dictionary of widths for each column letter. :param alignment_list: dictionary of alignments (``left``, ``right``, or ``center``) for each column letter. """ # for row in ws.iter_rows("A1:{}{}".format(get_column_letter(ws.max_column), ws.max_row)): for row in ws[f"A1:{get_column_letter(ws.max_column)}{ws.max_row}"]: for cell in row: cell.alignment = Alignment(vertical="center", wrap_text=False) if number_format_list: for column in number_format_list: # for row in ws.iter_rows('{0}{1}:{0}{2}'.format(column, 3, ws.max_row)): for row in ws[f"{column}{3}:{column}{ws.max_row}"]: for cell in row: cell.number_format = number_format_list[column] for column_cells in ws.columns: length = max(len(str(cell.value)) for cell in column_cells) if length < 1: length = 1 ws.column_dimensions[get_column_letter(column_cells[0].column)].width = length # ws.column_dimensions[column_cells[0].column].bestFit = True if width_list: for column in width_list: if width_list[column] == 0: ws.column_dimensions[column].hidden = True else: ws.column_dimensions[column].width = width_list[column] if alignment_list: for column in alignment_list: # for row in ws.iter_rows("{0}{1}:{0}{2}".format(column, ws.min_row, ws.max_row)): for row in ws[f"{column}{ws.min_row}:{column}{ws.max_row}"]: for cell in row: cell.alignment = Alignment( horizontal=alignment_list[column], vertical="center", wrap_text=False, )
[docs]def format_header( ws: Worksheet, alignment_list: Dict[str, str], start_row: int = 1, end_row: int = 1, ) -> None: """ Format the alignment of the header rows of a worksheet. :param ws: The worksheet to format. :type ws: :class:`openpyxl.worksheet.worksheet.Worksheet`. :param alignment_list: dictionary of alignments (left, right, center) for each column letter. :param start_row: The row to start formatting on. :param end_row: The row to end formatting on. """ for column in alignment_list: # for row in ws.iter_rows("{0}{1}:{0}{2}".format(column, start_row, end_row)): for row in ws[f"{column}{start_row}:{column}{end_row}"]: for cell in row: cell.alignment = Alignment(horizontal=alignment_list[column], vertical="center", wrap_text=False)
[docs]def make_column_property_dict( indict: Mapping, outdict: Optional[Dict[str, Any]] = None, offset_dict: Optional[Mapping] = None, repeat: int = 1, length: int = 1, ) -> Dict[str, Any]: """ Generate property lists from integer values. :param indict: Property values to add to the property dict. :param outdict: Dictionary of properties for each column letter. :param offset_dict: :param repeat: :param length: .. TODO:: Finish this docstring; check usage in GunShotMatch """ if outdict is None: outdict = {} for index in indict: for offset in range(repeat): outdict[get_column_letter(int(index) + (length * offset))] = indict[index] if offset_dict: offset = repeat * length for index in offset_dict: outdict[get_column_letter(int(index) + offset)] = offset_dict[index] return outdict
[docs]def col_width_from_cm(width: float) -> float: """ Returns an estimate of the width of the column in pixels, given the size in centimeters. :param width: The width in centimeters. .. versionadded:: 0.2.0 """ # # width in px / 38 = width in cm # width in cm * 38 = width in px # 10 = 75px ~= 1.97cm return width / (1.97 / 10)