###############################################################################
#
# Worksheet - A class for writing Excel Worksheets.
#
# Copyright 2013, John McNamara, jmcnamara@cpan.org
#
import re
import sys
from warnings import warn
COL_NAMES = {}
range_parts = re.compile(r'(\$?)([A-Z]{1,3})(\$?)(\d+)')
def xl_rowcol_to_cell(row, col, row_abs=0, col_abs=0):
"""
TODO. Add Utility.py docs.
"""
row += 1 # Change to 1-index.
row_abs = '$' if row_abs else ''
col_abs = '$' if col_abs else ''
col_str = xl_col_to_name(col, col_abs)
return col_str + row_abs + str(row)
def xl_rowcol_to_cell_fast(row, col):
"""
Optimised version of the xl_rowcol_to_cell function.
"""
if col in COL_NAMES:
col_str = COL_NAMES[col]
else:
col_str = xl_col_to_name(col)
COL_NAMES[col] = col_str
return col_str + str(row + 1)
def xl_col_to_name(col_num, col_abs=0):
"""
TODO. Add Utility.py docs.
"""
col_num += 1 # Change to 1-index.
col_str = ''
col_abs = '$' if col_abs else ''
while col_num:
# Set remainder from 1 .. 26
remainder = col_num % 26
if remainder == 0:
remainder = 26
# Convert the remainder to a character.
col_letter = chr(ord('A') + remainder - 1)
# Accumulate the column letters, right to left.
col_str = col_letter + col_str
# Get the next order of magnitude.
col_num = int((col_num - 1) / 26)
return col_abs + col_str
def xl_cell_to_rowcol(cell_str):
"""
TODO. Add Utility.py docs.
"""
if not cell_str:
return (0, 0)
match = range_parts.match(cell_str)
col_str = match.group(2)
row_str = match.group(4)
# Convert base26 column string to number.
expn = 0
col = 0
for char in reversed(col_str):
col += (ord(char) - ord('A') + 1) * (26 ** expn)
expn += 1
# Convert 1-index to zero-index
row = int(row_str) - 1
col -= 1
return row, col
def xl_cell_to_rowcol_abs(cell_str):
"""
TODO. Add Utility.py docs.
"""
if not cell_str:
return (0, 0, 0, 0)
match = range_parts.match(cell_str)
col_abs = match.group(1)
col_str = match.group(2)
row_abs = match.group(3)
row_str = match.group(4)
if col_abs:
col_abs = 1
else:
col_abs = 0
if row_abs:
row_abs = 1
else:
row_abs = 0
# Convert base26 column string to number.
expn = 0
col = 0
for char in reversed(col_str):
col += (ord(char) - ord('A') + 1) * (26 ** expn)
expn += 1
# Convert 1-index to zero-index
row = int(row_str) - 1
col -= 1
return row, col, row_abs, col_abs
def xl_range(first_row, first_col, last_row, last_col):
"""
TODO. Add Utility.py docs.
"""
range1 = xl_rowcol_to_cell(first_row, first_col)
range2 = xl_rowcol_to_cell(last_row, last_col)
return range1 + ':' + range2
def xl_color(color):
# Used in conjunction with the XlsxWriter *color() methods to convert
# a colour name into an RGB formatted string. These colours are for
# backward compatibility with older versions of Excel.
named_colors = {
'black': '#000000',
'blue': '#0000FF',
'brown': '#800000',
'cyan': '#00FFFF',
'gray': '#808080',
'green': '#008000',
'lime': '#00FF00',
'magenta': '#FF00FF',
'navy': '#000080',
'orange': '#FF6600',
'pink': '#FF00FF',
'purple': '#800080',
'red': '#FF0000',
'silver': '#C0C0C0',
'white': '#FFFFFF',
'yellow': '#FFFF00',
}
if color in named_colors:
color = named_colors[color]
if not re.match('#[0-9a-fA-F]{6}', color):
warn("Color '%s' isn't a valid Excel color" % color)
# Convert the RGB color to the Excel ARGB format.
return "FF" + color.lstrip('#').upper()
def get_sparkline_style(style_id):
styles = [
{'series': {'theme': "4", 'tint': "-0.499984740745262"},
'negative': {'theme': "5"},
'markers': {'theme': "4", 'tint': "-0.499984740745262"},
'first': {'theme': "4", 'tint': "0.39997558519241921"},
'last': {'theme': "4", 'tint': "0.39997558519241921"},
'high': {'theme': "4"},
'low': {'theme': "4"},
}, # 0
{'series': {'theme': "4", 'tint': "-0.499984740745262"},
'negative': {'theme': "5"},
'markers': {'theme': "4", 'tint': "-0.499984740745262"},
'first': {'theme': "4", 'tint': "0.39997558519241921"},
'last': {'theme': "4", 'tint': "0.39997558519241921"},
'high': {'theme': "4"},
'low': {'theme': "4"},
}, # 1
{'series': {'theme': "5", 'tint': "-0.499984740745262"},
'negative': {'theme': "6"},
'markers': {'theme': "5", 'tint': "-0.499984740745262"},
'first': {'theme': "5", 'tint': "0.39997558519241921"},
'last': {'theme': "5", 'tint': "0.39997558519241921"},
'high': {'theme': "5"},
'low': {'theme': "5"},
}, # 2
{'series': {'theme': "6", 'tint': "-0.499984740745262"},
'negative': {'theme': "7"},
'markers': {'theme': "6", 'tint': "-0.499984740745262"},
'first': {'theme': "6", 'tint': "0.39997558519241921"},
'last': {'theme': "6", 'tint': "0.39997558519241921"},
'high': {'theme': "6"},
'low': {'theme': "6"},
}, # 3
{'series': {'theme': "7", 'tint': "-0.499984740745262"},
'negative': {'theme': "8"},
'markers': {'theme': "7", 'tint': "-0.499984740745262"},
'first': {'theme': "7", 'tint': "0.39997558519241921"},
'last': {'theme': "7", 'tint': "0.39997558519241921"},
'high': {'theme': "7"},
'low': {'theme': "7"},
}, # 4
{'series': {'theme': "8", 'tint': "-0.499984740745262"},
'negative': {'theme': "9"},
'markers': {'theme': "8", 'tint': "-0.499984740745262"},
'first': {'theme': "8", 'tint': "0.39997558519241921"},
'last': {'theme': "8", 'tint': "0.39997558519241921"},
'high': {'theme': "8"},
'low': {'theme': "8"},
}, # 5
{'series': {'theme': "9", 'tint': "-0.499984740745262"},
'negative': {'theme': "4"},
'markers': {'theme': "9", 'tint': "-0.499984740745262"},
'first': {'theme': "9", 'tint': "0.39997558519241921"},
'last': {'theme': "9", 'tint': "0.39997558519241921"},
'high': {'theme': "9"},
'low': {'theme': "9"},
}, # 6
{'series': {'theme': "4", 'tint': "-0.249977111117893"},
'negative': {'theme': "5"},
'markers': {'theme': "5", 'tint': "-0.249977111117893"},
'first': {'theme': "5", 'tint': "-0.249977111117893"},
'last': {'theme': "5", 'tint': "-0.249977111117893"},
'high': {'theme': "5", 'tint': "-0.249977111117893"},
'low': {'theme': "5", 'tint': "-0.249977111117893"},
}, # 7
{'series': {'theme': "5", 'tint': "-0.249977111117893"},
'negative': {'theme': "6"},
'markers': {'theme': "6", 'tint': "-0.249977111117893"},
'first': {'theme': "6", 'tint': "-0.249977111117893"},
'last': {'theme': "6", 'tint': "-0.249977111117893"},
'high': {'theme': "6", 'tint': "-0.249977111117893"},
'low': {'theme': "6", 'tint': "-0.249977111117893"},
}, # 8
{'series': {'theme': "6", 'tint': "-0.249977111117893"},
'negative': {'theme': "7"},
'markers': {'theme': "7", 'tint': "-0.249977111117893"},
'first': {'theme': "7", 'tint': "-0.249977111117893"},
'last': {'theme': "7", 'tint': "-0.249977111117893"},
'high': {'theme': "7", 'tint': "-0.249977111117893"},
'low': {'theme': "7", 'tint': "-0.249977111117893"},
}, # 9
{'series': {'theme': "7", 'tint': "-0.249977111117893"},
'negative': {'theme': "8"},
'markers': {'theme': "8", 'tint': "-0.249977111117893"},
'first': {'theme': "8", 'tint': "-0.249977111117893"},
'last': {'theme': "8", 'tint': "-0.249977111117893"},
'high': {'theme': "8", 'tint': "-0.249977111117893"},
'low': {'theme': "8", 'tint': "-0.249977111117893"},
}, # 10
{'series': {'theme': "8", 'tint': "-0.249977111117893"},
'negative': {'theme': "9"},
'markers': {'theme': "9", 'tint': "-0.249977111117893"},
'first': {'theme': "9", 'tint': "-0.249977111117893"},
'last': {'theme': "9", 'tint': "-0.249977111117893"},
'high': {'theme': "9", 'tint': "-0.249977111117893"},
'low': {'theme': "9", 'tint': "-0.249977111117893"},
}, # 11
{'series': {'theme': "9", 'tint': "-0.249977111117893"},
'negative': {'theme': "4"},
'markers': {'theme': "4", 'tint': "-0.249977111117893"},
'first': {'theme': "4", 'tint': "-0.249977111117893"},
'last': {'theme': "4", 'tint': "-0.249977111117893"},
'high': {'theme': "4", 'tint': "-0.249977111117893"},
'low': {'theme': "4", 'tint': "-0.249977111117893"},
}, # 12
{'series': {'theme': "4"},
'negative': {'theme': "5"},
'markers': {'theme': "4", 'tint': "-0.249977111117893"},
'first': {'theme': "4", 'tint': "-0.249977111117893"},
'last': {'theme': "4", 'tint': "-0.249977111117893"},
'high': {'theme': "4", 'tint': "-0.249977111117893"},
'low': {'theme': "4", 'tint': "-0.249977111117893"},
}, # 13
{'series': {'theme': "5"},
'negative': {'theme': "6"},
'markers': {'theme': "5", 'tint': "-0.249977111117893"},
'first': {'theme': "5", 'tint': "-0.249977111117893"},
'last': {'theme': "5", 'tint': "-0.249977111117893"},
'high': {'theme': "5", 'tint': "-0.249977111117893"},
'low': {'theme': "5", 'tint': "-0.249977111117893"},
}, # 14
{'series': {'theme': "6"},
'negative': {'theme': "7"},
'markers': {'theme': "6", 'tint': "-0.249977111117893"},
'first': {'theme': "6", 'tint': "-0.249977111117893"},
'last': {'theme': "6", 'tint': "-0.249977111117893"},
'high': {'theme': "6", 'tint': "-0.249977111117893"},
'low': {'theme': "6", 'tint': "-0.249977111117893"},
}, # 15
{'series': {'theme': "7"},
'negative': {'theme': "8"},
'markers': {'theme': "7", 'tint': "-0.249977111117893"},
'first': {'theme': "7", 'tint': "-0.249977111117893"},
'last': {'theme': "7", 'tint': "-0.249977111117893"},
'high': {'theme': "7", 'tint': "-0.249977111117893"},
'low': {'theme': "7", 'tint': "-0.249977111117893"},
}, # 16
{'series': {'theme': "8"},
'negative': {'theme': "9"},
'markers': {'theme': "8", 'tint': "-0.249977111117893"},
'first': {'theme': "8", 'tint': "-0.249977111117893"},
'last': {'theme': "8", 'tint': "-0.249977111117893"},
'high': {'theme': "8", 'tint': "-0.249977111117893"},
'low': {'theme': "8", 'tint': "-0.249977111117893"},
}, # 17
{'series': {'theme': "9"},
'negative': {'theme': "4"},
'markers': {'theme': "9", 'tint': "-0.249977111117893"},
'first': {'theme': "9", 'tint': "-0.249977111117893"},
'last': {'theme': "9", 'tint': "-0.249977111117893"},
'high': {'theme': "9", 'tint': "-0.249977111117893"},
'low': {'theme': "9", 'tint': "-0.249977111117893"},
}, # 18
{'series': {'theme': "4", 'tint': "0.39997558519241921"},
'negative': {'theme': "0", 'tint': "-0.499984740745262"},
'markers': {'theme': "4", 'tint': "0.79998168889431442"},
'first': {'theme': "4", 'tint': "-0.249977111117893"},
'last': {'theme': "4", 'tint': "-0.249977111117893"},
'high': {'theme': "4", 'tint': "-0.499984740745262"},
'low': {'theme': "4", 'tint': "-0.499984740745262"},
}, # 19
{'series': {'theme': "5", 'tint': "0.39997558519241921"},
'negative': {'theme': "0", 'tint': "-0.499984740745262"},
'markers': {'theme': "5", 'tint': "0.79998168889431442"},
'first': {'theme': "5", 'tint': "-0.249977111117893"},
'last': {'theme': "5", 'tint': "-0.249977111117893"},
'high': {'theme': "5", 'tint': "-0.499984740745262"},
'low': {'theme': "5", 'tint': "-0.499984740745262"},
}, # 20
{'series': {'theme': "6", 'tint': "0.39997558519241921"},
'negative': {'theme': "0", 'tint': "-0.499984740745262"},
'markers': {'theme': "6", 'tint': "0.79998168889431442"},
'first': {'theme': "6", 'tint': "-0.249977111117893"},
'last': {'theme': "6", 'tint': "-0.249977111117893"},
'high': {'theme': "6", 'tint': "-0.499984740745262"},
'low': {'theme': "6", 'tint': "-0.499984740745262"},
}, # 21
{'series': {'theme': "7", 'tint': "0.39997558519241921"},
'negative': {'theme': "0", 'tint': "-0.499984740745262"},
'markers': {'theme': "7", 'tint': "0.79998168889431442"},
'first': {'theme': "7", 'tint': "-0.249977111117893"},
'last': {'theme': "7", 'tint': "-0.249977111117893"},
'high': {'theme': "7", 'tint': "-0.499984740745262"},
'low': {'theme': "7", 'tint': "-0.499984740745262"},
}, # 22
{'series': {'theme': "8", 'tint': "0.39997558519241921"},
'negative': {'theme': "0", 'tint': "-0.499984740745262"},
'markers': {'theme': "8", 'tint': "0.79998168889431442"},
'first': {'theme': "8", 'tint': "-0.249977111117893"},
'last': {'theme': "8", 'tint': "-0.249977111117893"},
'high': {'theme': "8", 'tint': "-0.499984740745262"},
'low': {'theme': "8", 'tint': "-0.499984740745262"},
}, # 23
{'series': {'theme': "9", 'tint': "0.39997558519241921"},
'negative': {'theme': "0", 'tint': "-0.499984740745262"},
'markers': {'theme': "9", 'tint': "0.79998168889431442"},
'first': {'theme': "9", 'tint': "-0.249977111117893"},
'last': {'theme': "9", 'tint': "-0.249977111117893"},
'high': {'theme': "9", 'tint': "-0.499984740745262"},
'low': {'theme': "9", 'tint': "-0.499984740745262"},
}, # 24
{'series': {'theme': "1", 'tint': "0.499984740745262"},
'negative': {'theme': "1", 'tint': "0.249977111117893"},
'markers': {'theme': "1", 'tint': "0.249977111117893"},
'first': {'theme': "1", 'tint': "0.249977111117893"},
'last': {'theme': "1", 'tint': "0.249977111117893"},
'high': {'theme': "1", 'tint': "0.249977111117893"},
'low': {'theme': "1", 'tint': "0.249977111117893"},
}, # 25
{'series': {'theme': "1", 'tint': "0.34998626667073579"},
'negative': {'theme': "0", 'tint': "-0.249977111117893"},
'markers': {'theme': "0", 'tint': "-0.249977111117893"},
'first': {'theme': "0", 'tint': "-0.249977111117893"},
'last': {'theme': "0", 'tint': "-0.249977111117893"},
'high': {'theme': "0", 'tint': "-0.249977111117893"},
'low': {'theme': "0", 'tint': "-0.249977111117893"},
}, # 26
{'series': {'rgb': "FF323232"},
'negative': {'rgb': "FFD00000"},
'markers': {'rgb': "FFD00000"},
'first': {'rgb': "FFD00000"},
'last': {'rgb': "FFD00000"},
'high': {'rgb': "FFD00000"},
'low': {'rgb': "FFD00000"},
}, # 27
{'series': {'rgb': "FF000000"},
'negative': {'rgb': "FF0070C0"},
'markers': {'rgb': "FF0070C0"},
'first': {'rgb': "FF0070C0"},
'last': {'rgb': "FF0070C0"},
'high': {'rgb': "FF0070C0"},
'low': {'rgb': "FF0070C0"},
}, # 28
{'series': {'rgb': "FF376092"},
'negative': {'rgb': "FFD00000"},
'markers': {'rgb': "FFD00000"},
'first': {'rgb': "FFD00000"},
'last': {'rgb': "FFD00000"},
'high': {'rgb': "FFD00000"},
'low': {'rgb': "FFD00000"},
}, # 29
{'series': {'rgb': "FF0070C0"},
'negative': {'rgb': "FF000000"},
'markers': {'rgb': "FF000000"},
'first': {'rgb': "FF000000"},
'last': {'rgb': "FF000000"},
'high': {'rgb': "FF000000"},
'low': {'rgb': "FF000000"},
}, # 30
{'series': {'rgb': "FF5F5F5F"},
'negative': {'rgb': "FFFFB620"},
'markers': {'rgb': "FFD70077"},
'first': {'rgb': "FF5687C2"},
'last': {'rgb': "FF359CEB"},
'high': {'rgb': "FF56BE79"},
'low': {'rgb': "FFFF5055"},
}, # 31
{'series': {'rgb': "FF5687C2"},
'negative': {'rgb': "FFFFB620"},
'markers': {'rgb': "FFD70077"},
'first': {'rgb': "FF777777"},
'last': {'rgb': "FF359CEB"},
'high': {'rgb': "FF56BE79"},
'low': {'rgb': "FFFF5055"},
}, # 32
{'series': {'rgb': "FFC6EFCE"},
'negative': {'rgb': "FFFFC7CE"},
'markers': {'rgb': "FF8CADD6"},
'first': {'rgb': "FFFFDC47"},
'last': {'rgb': "FFFFEB9C"},
'high': {'rgb': "FF60D276"},
'low': {'rgb': "FFFF5367"},
}, # 33
{'series': {'rgb': "FF00B050"},
'negative': {'rgb': "FFFF0000"},
'markers': {'rgb': "FF0070C0"},
'first': {'rgb': "FFFFC000"},
'last': {'rgb': "FFFFC000"},
'high': {'rgb': "FF00B050"},
'low': {'rgb': "FFFF0000"},
}, # 34
{'series': {'theme': "3"},
'negative': {'theme': "9"},
'markers': {'theme': "8"},
'first': {'theme': "4"},
'last': {'theme': "5"},
'high': {'theme': "6"},
'low': {'theme': "7"},
}, # 35
{'series': {'theme': "1"},
'negative': {'theme': "9"},
'markers': {'theme': "8"},
'first': {'theme': "4"},
'last': {'theme': "5"},
'high': {'theme': "6"},
'low': {'theme': "7"},
}, # 36
]
return styles[style_id]
def encode_utf8(string):
"""
Encode any strings passed by the user to UTF8 in Python 2.
"""
if string is None or sys.version_info >= (3, 0):
return string
else:
return string.encode('utf-8')