def write(self, r, c, label='', *args, **kwargs):
self.sheet.write(r, c, label, *args, **kwargs)
width = int(arial10.fitwidth(label))
if width > self.widths.get(c, 0):
self.widths[c] = width
self.sheet.col(c).width = width
def write(self, r, c, label="", style=pycel.Style.default_style):
self.sheet.write(r, c, label, style)
width = arial10.fitwidth(label)
if style is not None and style.font is not None and style.font.bold:
width *= 1.062 # jnm fudge factor
if width > self.widths.get(c, 0):
self.widths[c] = width
self.sheet.col(c).width = width
def write(self, r, c, label="", style=pycel.Style.default_style):
self.sheet.write(r, c, label, style)
width = arial10.fitwidth(label)
if style is not None and style.font is not None and style.font.bold:
width *= 1.062 # jnm fudge factor
if width > self.widths.get(c, 0):
self.widths[c] = width
self.sheet.col(c).width = width
def write(self, r, c, label='', *args, **kwargs):
self.sheet.write(r, c, label, *args, **kwargs)
width = int(arial10.fitwidth(label))
if width > self.widths.get(c, 0):
self.widths[c] = width
self.sheet.set_column(
c, int(width)
) #modified this as per XlsxWriter https://xlsxwriter.readthedocs.io/working_with_pandas.html
def __write_row(self, data, style):
self.__current_row_counter += 1
for index, item in enumerate(data):
item = str(item)
self.ws.write(self.__current_row_counter, index, item, style)
width = int(arial10.fitwidth(item))
if width > self.__widths.get(index, 0):
self.__widths[index] = width
self.ws.col(index).width = width
def write(self, r, c, label, *args, **kwargs):
self.sheet.write(r, c, label, *args, **kwargs)
offset = 0
if type(label) == float:
offset = 262.637 * 2 + 146.015
width = arial10.fitwidth(str(label)) + offset
if width > self.widths.get(c, 0):
self.widths[c] = width
self.sheet.col(c).width = int(width)
def write(self, r, c, label='', style=None):
if isinstance(label, datetime) and not style:
style = xlwt.XFStyle()
style.num_format_str = 'YYYY-MM-DD hh:mm:ss'
if style:
self.sheet.write(r, c, label, style)
else:
self.sheet.write(r, c, label)
width = arial10.fitwidth(style and style.num_format_str or str(label))
if width > self.widths.get(c, 0):
self.widths[c] = width
self.sheet.col(c).width = int(width)
def write(self, r, c, label='', *args, **kwargs):
self.sheet.write(r, c, label, *args, **kwargs)
self.sheet.row(r).collapse = True
bold = False
if args:
style = args[0]
bold = str(style.font.bold) in ('1', 'true', 'True')
width = arial10.fitwidth(label, bold)
if width > self.widths.get(c, 0):
self.widths[c] = width
self.sheet.col(c).width = width
height = arial10.fitheight(label, bold)
if height > self.heights.get(r, 0):
self.heights[r] = height
self.sheet.row(r).height = height
def write(self, r, c, label='', *args, **kwargs):
self.sheet.write(r, c, label, *args, **kwargs)
self.sheet.row(r).collapse = True
bold = False
if args:
style = args[0]
bold = str(style.font.bold) in ('1', 'true', 'True')
width = int(arial10.fitwidth(label, bold))
if width > self.widths.get(c, 0):
self.widths[c] = width
self.sheet.col(c).width = width
height = int(arial10.fitheight(label, bold))
if height > self.heights.get(r, 0):
self.heights[r] = height
self.sheet.row(r).height = height
def write(self, r, c, label=u'', style=None):
if not style:
style = XFStyle()
if isinstance(label, datetime.datetime):
_saved_format = style.num_format_str
style.num_format_str = 'dd/mm/yyyy hh:mm:ss'
self.sheet.write(r, c, label, style)
style.num_format_str = _saved_format
elif isinstance(label, datetime.date):
_saved_format = style.num_format_str
style.num_format_str = 'dd/mm/yyyy'
self.sheet.write(r, c, label, style)
style.num_format_str = _saved_format
# elif isinstance(label, (float, Decimal)):
# _saved_format = style.num_format_str
# style.num_format_str = '#,##0.00'
# self.sheet.write(r, c, label, style)
# style.num_format_str = _saved_format
else:
self.sheet.write(r, c, label, style)
self.sheet.row(r).collapse = True
unicode_label = unicode(label)
bold = str(style.font.bold) in ('1', 'true', 'True')
width = min(int(arial10.fitwidth(unicode_label, bold)), MAX_COLUMN_WIDTH)
if width > self.widths.get(c, 0):
self.widths[c] = width
self.sheet.col(c).width = width
height = int(arial10.fitheight(unicode_label, bold))
if height > self.heights.get(r, 0):
self.heights[r] = height
self.sheet.row(r).height = height
def buildexcel(file_dest):
# prepare an empty workbook
workbook = xlsxwriter.Workbook(file_dest,
{'default_date_format': 'YYYY-mm-dd'})
header_row_format = workbook.add_format({
'bg_color': '#C2C2D6',
'bottom': 3,
'top': 3,
'text_wrap': True,
'font_name': 'Arial',
'font_size': 10
})
data_row_format = workbook.add_format({
'font_name': 'Arial',
'font_size': 10
})
# work on each URL, as one new worksheet
for url, csvy in webscraping():
print(url)
lines = csvy.split("\n")
# Extract and parse the CSVY metadata part
fields_def_yaml = [
ln[1:] for ln in itertools.takewhile(lambda s: s[0] == '#', lines)
]
if not fields_def_yaml:
# the metadata is not in comment, look for limiters instead
fields_def_yaml = [
ln for ln in itertools.takewhile(
lambda s: not s.startswith('---'), lines[1:])
]
else:
fields_def_yaml = fields_def_yaml[1:-1]
fields_def = yaml.load("\n".join(fields_def_yaml))
assert ('fields' in fields_def)
assert (all('name' in f for f in fields_def['fields']))
fields = {f['name']: f for f in fields_def['fields']}
for name in fields:
if 'labels' in fields[name]:
labeltext = "\n".join([
str(int(x2)) + " : " + str(x1)
for x1, x2 in sorted(fields[name]['labels'].items(),
key=lambda x: x[1])
])
fields[name]['labels'] = labeltext
# Extract and parse the CSVY data part
csvdata = list(
csv.reader([ln + '\n' for ln in lines[len(fields_def_yaml) + 2:]]))
# Create worksheet and set title
worksheet = workbook.add_worksheet(
deduce_name(url)) # new sheet at end
# Header rows: label, labels, name
header = csvdata[0]
if len(header) < len(csvdata[1]):
# in case header row is shorter, align to right
header = [None] * (len(csvdata[1]) - len(header)) + header
option = [fields.get(h, {}).get('labels', ' ') for h in header]
label = [
breaklabel(fields.get(h, {}).get('label', ' ')) for h in header
]
worksheet.write_row(0, 0, header, header_row_format)
worksheet.write_row(1, 0, option, header_row_format)
worksheet.write_row(2, 0, label, header_row_format)
# Data rows
for j, row in enumerate(csvdata[1:]):
for i, col in enumerate(row):
if header[i] == 'date':
row[i] = datetime.datetime.strptime(col, "%Y%m%d").date()
elif col.isdigit():
row[i] = int(col)
else:
try:
row[i] = float(col)
except:
row[i] = None if col == 'NA' else col
worksheet.write_row(3 + j, 0, row, data_row_format)
# Add autofilter
ignore_col = ['weight', 'caseid', None]
maxrow = len(csvdata) + 2
minrow = 2
mincol = min(i for i, c in enumerate(header) if c not in ignore_col)
maxcol = max(i for i, c in enumerate(header) if c not in ignore_col)
worksheet.autofilter(minrow, mincol, maxrow, maxcol)
# Adjust column width: usually to fit the option text
colwidths = []
for c in range(len(header)):
columndata = ["00000", header[c], option[c]] + [
str(row[c]) for row in csvdata[1:] if len(row) > c
]
width = max(arial10.fitwidth(x) for x in columndata if x)
worksheet.set_column(c, c, width / 256.0)
colwidths.append(width)
# Adjust row height for option text row
height = max(arial10.fitheight(x) for x in option if x)
worksheet.set_row(1, height / 18.0)
# Adjust row height for option text row
height = max(arial10.fitheight(x) for x in label if x)
wrapped_add_height = max(
arial10.fitheight("\n" * math.ceil(arial10.fitwidth(l) / w - 1))
for l, w in zip(label, colwidths))
worksheet.set_row(2, (height + wrapped_add_height) / 18.0)
# Freeze the 3 header rows
worksheet.freeze_panes(3, 0)
# save to disk
workbook.close()
pstyle = xlwt.easyxf("font: color green; align: horiz center")
sstyle = xlwt.easyxf("font: color blue; align: horiz center")
fstart = 2
estart = 2
ID, F, E, S, P = 0, 1, 2, 3, 4
for k, line in enumerate(sys.stdin,1):
lines = line.split("\t")
ws = wb.add_sheet(str(k), cell_overwrite_ok=True)
ws.set_panes_frozen(True) # frozen headings instead of split panes
ws.set_horz_split_pos(estart-1)
ws.set_vert_split_pos(fstart-1)
ws.write(0, 0, "ID="+lines[ID])
ws.write(0, fstart-1, "NULL")
ws.write(estart-1, 0, "NULL")
ws.col(fstart-1).width = int(math.ceil(arial10.fitwidth("NULL")))
for j, fj in enumerate(lines[F].split(), fstart):
ws.write(0, j, fj)
ws.col(j).width = int(math.ceil(arial10.fitwidth(fj)))
for i, ei in enumerate(lines[E].split(), estart):
ws.write(i, 0, ei)
if len(lines) > P: # possible
for ji in lines[P].split():
col, row = map(int, ji.split("-"))
ws.write(row+estart, col+fstart, "P", pstyle)
if len(lines) > S: # sure (overwrite possible)
for ji in lines[S].split():
col, row = map(int, ji.split("-"))
ws.write(row+estart, col+fstart, "S", sstyle)
wb.save(sys.argv[1])
def write(self, r, c, label='', *args, **kwargs):
self.sheet.write(r, c, label, *args, **kwargs)
width = arial10.fitwidth(label)
if width > self.widths.get(c, 0):
self.widths[c] = int(width)
self.sheet.col(c).width = int(width)
def write(self, r, c, label="", *args, **kwargs):
self.sheet.write(r, c, label, *args, **kwargs)
width = int(arial10.fitwidth(str(label)))
if width > self.widths.get(c, 0):
self.widths[c] = width
self.sheet.col(c).width = width
def CreateExcelSpreadsheet(table, output_excel, use_alias=True):
"""Exports table to excel
Required:
table -- input table
output_excel -- output excel table (.xlsx, .xls)
Optional:
use_alias -- use field alias name for column headers. Default is True
"""
# build field dict
fieldNames = [(f.name, f.aliasName) for f in arcpy.ListFields(table) if f.type != 'Geometry']
fields = [f[1] if use_alias in ('true', True) else f[0] for f in fieldNames]
widths = {i: arial10.fitwidth(f) + 1024 for i,f in enumerate(fields)}
# get field values *Changed from type dict to list
with arcpy.da.SearchCursor(table, [f[0] for f in fieldNames]) as rows:
values = [r for r in rows]
# Create spreadsheet
wb = Workbook()
ws = wb.add_sheet('Sheet 1')
cols = len(fields)
rows = len(values) + 1
# set styles
#***************************************************************************
borders = Borders()
borders.left = Borders.THIN
borders.right = Borders.THIN
borders.top = Borders.THIN
borders.bottom = Borders.THIN
style = XFStyle()
style.borders = borders
# headers
fntHeaders = Font()
fntHeaders.bold = True
fntHeaders.height = 220
styleHeaders = XFStyle()
styleHeaders.font = fntHeaders
styleHeaders.borders = borders
# for date fields
styleDate = XFStyle()
styleDate.borders = borders
styleDate.num_format_str = 'MM/DD/YYYY'
#***************************************************************************
# write headers and freeze panes
for ci,field in enumerate(fields):
ws.write(0, ci, field, styleHeaders)
# freeze headers
ws.set_panes_frozen(True)
ws.set_horz_split_pos(1)
# fill in values
start = 1
for vals in values:
for i, value in enumerate(vals):
ws.write(start, i, value, styleDate if isinstance(value, datetime.datetime) else style)
v_width = arial10.fitwidth(str(value).strip())
if v_width > widths[i]:
widths[i] = v_width
start += 1
if not start % 1000:
ws.flush_row_data()
# autofit column widths
for ci,width in widths.iteritems():
ws.col(ci).width = int(width + 256) # just a little more padding
# save workbook
wb.save(output_excel)
del wb
out_url = '/'.join([PATH_URL, os.path.basename(out_file)])
arcpy.SetParameter(2, out_url)
arcpy.AddMessage(out_url)
return out_url
if out is None: # for wb.encoding
out = xlwt.Workbook(encoding=wb.encoding)
if k % 10 == 0:
sys.stderr.write(".")
if k % (10*80) == 0:
sys.stderr.write(linesep)
for ws in wb.sheets():
wsout = out.add_sheet(ws.name)
wsout.set_panes_frozen(True) # frozen headings instead of split panes
wsout.set_horz_split_pos(1)
wsout.set_vert_split_pos(1)
for row in range(ws.nrows):
for col in range(ws.ncols):
c = ws.cell(row,col).value
if type(c) != type(u''):
c = unicode("{0}".format(c), "utf-8")
if row == 0 and col == 0 and not c:
#wsout.write(0,0, u"ID="+i.strip(), istyle)
continue
if row == 0 and col > 0:
wsout.col(col).width = \
int(math.ceil(arial10.fitwidth(c.encode("utf-8"))))
if c == 'S':
wsout.write(row,col, c, sstyle)
elif c == 'P':
wsout.write(row,col, c, pstyle)
else:
wsout.write(row,col, c)
out.save(sys.argv[1])
'''
The below function finds the max number of characters for indexes: 1,2 and 7
We can use the answer to solve how long the width of the cells need to be using the arial10 module
'''
def get_longest_name(a_list):
return max((name for name in a_list), key=len, default='')
longest_index_1 = get_longest_name(index_product_code)
longest_index_2 = get_longest_name(index_description)
longest_index_7 = get_longest_name(index_PO_num)
'''
Using the arial10 module, we will find the correct fit of inexes 1,2 and 5
'''
ws.col(1).width = int(arial10.fitwidth(longest_index_1))
ws.col(2).width = int(arial10.fitwidth(longest_index_2))
a = int(arial10.fitwidth(longest_index_7))
'''
for index 7 below, I am subtracting the 2 merged cell lengths and adding a little bit extra room that is RELATED to the initial value, because arial10 is smaller than the font I'm using
'''
ws.col(7).width = (a - 6200) + int(a*0.1)
wb.save(workbook_to_extract + ' - Extract.xls')
print("\nExtract Completed.")
print("My program took", time.time() - start_time, "to run")
