<commit_before>#     simple_xls.py: write simple Excel spreadsheets
#     Copyright (C) University of Manchester 2013-4 <NAME>
#
########################################################################
#
# simple_xls.py
#
#########################################################################

__version__ = "0.0.7"

"""
simple_xls.py

Simple spreadsheet module intended to provide a nicer programmatic interface
to Excel spreadsheet generation.

It is currently built on top of SpreadSheet.py, which itself uses the xlwt,
xlrd and xlutils modules. In future the relevant parts may be rewritten to
remove the dependence on Spreadsheet.py and call the appropriate xl* classes
and functions directly.

Example usage
-------------

Start by making a workbook, represented by an XLSWorkBook object:

>>> wb = XLSWorkBook("Test")

Then add worksheets to this:

>>> wb.add_work_sheet('test')
>>> wb.add_work_sheet('data',"My Data")

Worksheets have an id and an optional title. Ids must be unique and can
be used to fetch the XLSWorkSheet object that represent the worksheet:

>>> data = wb.worksheet['data']

Cells can be addressed directly using various notations:

>>> data['A1'] = "Column 1"
>>> data['A']['1'] = "Updated value"
>>> data['AZ']['3'] = "Another value"

The extent of the sheet is defined by the outermost populated rows and
columns

>>> data.last_column # outermost populated column
>>> data.last_row    # outermost populated row

There are various other methods for returning the next row or column; see
the documentation for the XLSWorkSheet class.

Data can be added cell-wise (i.e. referencing individual cells as above),
row-wise, column-wise and block-wise.

Column-wise operations include inserting a column (shifting columns above
it along one to make space):

>>> data.insert_column('B',data=['hello','goodbye','whatev'])

Append a column (writing data to the first empty column at the end of
the sheet):

>>> data.append_column(data=['hello','goodbye','whatev'])

Write data to a column, overwriting any existing values:

>>> data.write_column(data=['hello','goodbye','whatev'])

Data can be specified as a list, text or as a single value which is
repeated for each cell (i.e. a "fill" value).

Similar row-wise operations also exist:

>>> data.insert_row(4,data=['Dozy','Beaky','Mick','Titch'])
>>> data.append_row(data=['Dozy','Beaky','Mick','Titch'])
>>> data.write_row(4,data=['Dozy','Beaky','Mick','Titch'])

Block-wise data can be added via a tab and newline-delimited string:

>>> data.insert_block_data("This\tis\t\tsome\n\trandom\n\tdata")
>>> data.insert_block_data("This\tis\t\tsome\n\tMORE\trandom\n\tdata",
...                        col='M',row=7)

Formulae can be specified by prefixing a '=' symbol to the start of the
cell contents, e.g.:

>>> data['A3'] = '=A1+A2'

'?' and '#' are special characters that can be used to indicate 'current
row' and 'current column' respectively, e.g.:

>>> data.fill_column('A','=B?+C?') # evaluates to 'B1+C1' (A1), 'B2+C2' (A2) etc

Styling and formatting information can be associated with a cell, either
when adding column, row or block data or by using the 'set_style' method.
In each case the styling information is passed via an XLSStyle object, e.g.

>>> data.set_style(XLSStyle(number_format=NumberFormats.PERCENTAGE),'A3')

The workbook can be saved to file:

>>> wb.save_as_xls('test.xls')

Alternatively the contents of a sheet (or a subset) can be rendered as text:

>>> data.render_as_text(include_columns_and_rows=True,
...                     eval_formulae=True,
...                     include_styles=True)
>>> data.render_as_text(start='B1',end='C6',include_columns_and_rows=True)

"""

#######################################################################
# Import modules that this module depends on
#######################################################################
 
import re
from collections import Iterator
import logging
import Spreadsheet
import bcf_utils

#######################################################################
# Constants
#######################################################################

# Value to assign to failed evaluations
BAD_REF="## !REF ##"

# Number formats
class NumberFormats:
    THOUSAND_SEPARATOR=0
    PERCENTAGE=1

#######################################################################
# Class definitions
#######################################################################

class XLSWorkBook:
    """Class for creating an Excel (xls) spreadsheet

    An XLSWorkBook instance provides an interface to creating an
    Excel spreadsheet.

    It consists of a collection of XLSWorkSheet objects, each
    of which represents a sheet in the workbook.

    Sheets are created and appended using the add_work_sheet
    method:

    >>> xls = XLSWorkBook()
    >>> sheet = xls('example')
    <commit_msg>share/simple_xls.py 0.0.8: add some limits as constants in the 'Limits' class.<commit_after>#     simple_xls.py: write simple Excel spreadsheets
#     Copyright (C) University of Manchester 2013-4 <NAME>
#
########################################################################
#
# simple_xls.py
#
#########################################################################

__version__ = "0.0.8"

"""
simple_xls.py

Simple spreadsheet module intended to provide a nicer programmatic interface
to Excel spreadsheet generation.

It is currently built on top of SpreadSheet.py, which itself uses the xlwt,
xlrd and xlutils modules. In future the relevant parts may be rewritten to
remove the dependence on Spreadsheet.py and call the appropriate xl* classes
and functions directly.

Example usage
-------------

Start by making a workbook, represented by an XLSWorkBook object:

>>> wb = XLSWorkBook("Test")

Then add worksheets to this:

>>> wb.add_work_sheet('test')
>>> wb.add_work_sheet('data',"My Data")

Worksheets have an id and an optional title. Ids must be unique and can
be used to fetch the XLSWorkSheet object that represent the worksheet:

>>> data = wb.worksheet['data']

Cells can be addressed directly using various notations:

>>> data['A1'] = "Column 1"
>>> data['A']['1'] = "Updated value"
>>> data['AZ']['3'] = "Another value"

The extent of the sheet is defined by the outermost populated rows and
columns

>>> data.last_column # outermost populated column
>>> data.last_row    # outermost populated row

There are various other methods for returning the next row or column; see
the documentation for the XLSWorkSheet class.

Data can be added cell-wise (i.e. referencing individual cells as above),
row-wise, column-wise and block-wise.

Column-wise operations include inserting a column (shifting columns above
it along one to make space):

>>> data.insert_column('B',data=['hello','goodbye','whatev'])

Append a column (writing data to the first empty column at the end of
the sheet):

>>> data.append_column(data=['hello','goodbye','whatev'])

Write data to a column, overwriting any existing values:

>>> data.write_column(data=['hello','goodbye','whatev'])

Data can be specified as a list, text or as a single value which is
repeated for each cell (i.e. a "fill" value).

Similar row-wise operations also exist:

>>> data.insert_row(4,data=['Dozy','Beaky','Mick','Titch'])
>>> data.append_row(data=['Dozy','Beaky','Mick','Titch'])
>>> data.write_row(4,data=['Dozy','Beaky','Mick','Titch'])

Block-wise data can be added via a tab and newline-delimited string:

>>> data.insert_block_data("This\tis\t\tsome\n\trandom\n\tdata")
>>> data.insert_block_data("This\tis\t\tsome\n\tMORE\trandom\n\tdata",
...                        col='M',row=7)

Formulae can be specified by prefixing a '=' symbol to the start of the
cell contents, e.g.:

>>> data['A3'] = '=A1+A2'

'?' and '#' are special characters that can be used to indicate 'current
row' and 'current column' respectively, e.g.:

>>> data.fill_column('A','=B?+C?') # evaluates to 'B1+C1' (A1), 'B2+C2' (A2) etc

Styling and formatting information can be associated with a cell, either
when adding column, row or block data or by using the 'set_style' method.
In each case the styling information is passed via an XLSStyle object, e.g.

>>> data.set_style(XLSStyle(number_format=NumberFormats.PERCENTAGE),'A3')

The workbook can be saved to file:

>>> wb.save_as_xls('test.xls')

Alternatively the contents of a sheet (or a subset) can be rendered as text:

>>> data.render_as_text(include_columns_and_rows=True,
...                     eval_formulae=True,
...                     include_styles=True)
>>> data.render_as_text(start='B1',end='C6',include_columns_and_rows=True)

"""

#######################################################################
# Import modules that this module depends on
#######################################################################
 
import re
from collections import Iterator
import logging
import Spreadsheet
import bcf_utils

#######################################################################
# Constants
#######################################################################

# Value to assign to failed evaluations
BAD_REF="## !REF ##"

# Number formats
class NumberFormats:
    THOUSAND_SEPARATOR=0
    PERCENTAGE=1

# Spreadsheet limits
class Limits:
    MAX_LEN_WORKSHEET_TITLE = 31 # Max worksheet title length
    MAX_LEN_WORKSHEET_CELL_VALUE = 250 # Maximum no. of characters in cell
    MAX_NUMBER_ROWS_PER_WORKSHEET = 65536 # Max number of rows per worksheet

#######################################################################
# Class definitions
#######################################################################

class XLSWorkBook:
    """Class for creating an Excel (xls) spreadsheet

    An XLSWorkBook instance provides an interface to creating an
    Excel spreadsheet.

    It consists of a collection of XLSWorkSheet objects, each
    of which represents a sheet in the workbook.

    Sheets are created and appended using the add_work_sheet
    method:

    >>> xls = XLSWorkBook()
    >>> sheet = xls('example')
    
{'fraction_non_alphanumeric': 0.21472025531066122, 'fraction_numerical': 0.0070808816196270075, 'mean_word_length': 4.994022713687985, 'pattern_counts': {'":': 0, '<': 5, '<?xml version=': 0, '>': 155, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'dir': 'git-commits-cleaned', 'id': '615418', 'n_tokens_mistral': 2936, 'n_tokens_neox': 2788, 'n_words': 984}