def write_row(self, array):
"""
write a row into the file
"""
for i in range(0, len(array)):
value = array[i]
style = None
tmp_array = []
if isinstance(value, datetime.datetime):
tmp_array = [
value.year, value.month, value.day,
value.hour, value.minute, value.second
]
value = xlrd.xldate.xldate_from_datetime_tuple(tmp_array, 0)
style = XFStyle()
style.num_format_str = DEFAULT_DATETIME_FORMAT
elif isinstance(value, datetime.date):
tmp_array = [value.year, value.month, value.day]
value = xlrd.xldate.xldate_from_date_tuple(tmp_array, 0)
style = XFStyle()
style.num_format_str = DEFAULT_DATE_FORMAT
elif isinstance(value, datetime.time):
tmp_array = [value.hour, value.minute, value.second]
value = xlrd.xldate.xldate_from_time_tuple(tmp_array)
style = XFStyle()
style.num_format_str = DEFAULT_TIME_FORMAT
if style:
self.native_sheet.write(self.current_row, i, value, style)
else:
self.native_sheet.write(self.current_row, i, value)
self.current_row += 1
def output(file_or_stream, sheetname, headers, values, encoding = 'utf8', footer_text = None, footer_link = None):
import xlwt
book = xlwt.Workbook(encoding)
sh = book.add_sheet(sheetname)
datestyle = XFStyle()
datestyle.num_format_str = 'DD/MM/YYYY'
timestyle = XFStyle()
timestyle.num_format_str = 'HH:MM:SS'
header_font = Font()
header_font.bold = True
al = Alignment()
al.horz = Alignment.HORZ_CENTER
header_style = XFStyle()
header_style.font = header_font
header_style.alignment = al
for i,header in enumerate(headers):
sh.write(0, i, header, header_style)
sh.set_panes_frozen(True) # frozen headings instead of split panes
sh.set_horz_split_pos(1) # in general, freeze after last heading row
sh.set_remove_splits(True) # if user does unfreeze, don't leave a split there
for j, row in enumerate(values):
for i, value in enumerate(row):
if value.__class__ == date:
sh.write(j+1, i, value, datestyle)
elif value.__class__ == time:
sh.write(j+1, i, value, timestyle)
else:
sh.write(j+1, i, value)
if footer_link and footer_text:
link_font = Font()
link_font.name = 'Verdana'
link_font.colour_index = 4
link_font.height = 20*8
al = Alignment()
al.horz = Alignment.HORZ_CENTER
al.vert = Alignment.VERT_BOTTOM
link_style = XFStyle()
link_style.font = link_font
link_style.alignment = al
row = len(values) + 1
sh.write_merge(row, row, 0, len(headers)-1, Formula('HYPERLINK("' + footer_link + '";"' + footer_text + '")'), link_style)
book.save(file_or_stream)
def exportToExcel(self,objectProject):
book = Workbook();
sheet1 = book.add_sheet('Sheet 1')
if( objectProject):
i=0;
row1 = sheet1.row(i) ;
row1.write(0, ('ประเภท').decode('UTF8') );
row1.write(1, ('ชื่อโครงการ').decode('UTF8'));
row1.write(2, ('รายละเอืยด').decode('UTF8') );
row1.write(3, ('งบประมาณรวม').decode('UTF8') );
row1.write(4, ('งบประมาณ').decode('UTF8') );
row1.write(5, ('เงินบำรุง').decode('UTF8') );
row1.write(6, ('งบประมาณอื่น').decode('UTF8') );
row1.write(7, ('งบประมาณอื่นจาก').decode('UTF8') );
row1.write(8, ('ผู้รับผิดชอบ').decode('UTF8') );
row1.write(9, ('กลุ่ม').decode('UTF8') );
row1.write(10, ('หน่วย/งาน').decode('UTF8') );
i=i+1;
style = XFStyle();
style.num_format_str = '#,##0.00';
for value in objectProject:
row1 = sheet1.row(i) ;
row1.write(0, value.get('project_type').decode('UTF8') );
row1.write(1, value.get('project_name').decode('UTF8') );
row1.write(2, value.get('detail').decode('UTF8') );
row1.write(3, value.get('allBudget') ,style );
row1.write(4, value.get('project_budget' ) ,style );
row1.write(5, value.get('maintenance_funds_budget'),style );
row1.write(6, value.get('budget_other') ,style );
if(value.get('budget_other_from')):
row1.write(7, value.get('budget_other_from').decode('UTF8') );
if(value.get('user_name')):
row1.write(8, value.get('user_name').decode('UTF8') );
row1.write(9, value.get('division').decode('UTF8') );
row1.write(10, value.get('section').decode('UTF8') );
i=i+1;
dirTempFile = gettempdir() + _os.sep + str('simple.xls');
book.save(dirTempFile);
#book.save(TemporaryFile());
return dirTempFile;
def writeErrorRec(record):
"""
Exception report spreadsheet output format same as input format and
assumes first column is of type Date - adds an extra, last column
for error message.
"""
dateFmt = XFStyle()
dateFmt.num_format_str='MM-DD-YYYY'
defaultFmt = XFStyle()
global erroutRow
for colnum in range(0, len(headers)):
erroutSheet.write(erroutRow, colnum, record[headers[colnum][0]],
tern(headers[colnum][0]==xlrd.XL_CELL_DATE, dateFmt, defaultFmt))
# add extra column for error message
erroutSheet.row(erroutRow).write(len(headers), record['Error'])
erroutRow += 1
# flush every 200 rows...
#if ((erroutRow % 200) + 1 == 200):
# since, xlwt uses aggressive caching, we must
# write each row manually, or the data may be lost upon exception
erroutSheet.flush_row_data()
errorsWorkbook.save('errors.xls')
def get_style( font_specs=dict(),
border_specs = dict(),
pattern = None,
num_format_str = None, ):
style_key = ( freeze(font_specs),
freeze(border_specs),
pattern,
num_format_str )
try:
return styles[style_key]
except KeyError:
style = XFStyle()
style.font = Font()
for key, value in font_specs.items():
setattr( style.font, key, value )
style.borders = Borders()
for key, value in border_specs.items():
setattr( style.borders, key, value )
if pattern:
style.pattern = pattern
if num_format_str:
style.num_format_str = num_format_str
styles[ style_key ] = style
return style
def data_amend_report(workbook, gl, ibm, nc_sp, pvs_sp, fm_sp):
# Sheet 1
sheet = workbook.get_sheet(0)
# Download hyperlink:
sheet.write(1, 0, Formula('HYPERLINK("{}")'.format(settings.IBM_DATA_AMEND_URI)))
# Padded zeroes number format
pad2, pad3, pad4 = XFStyle(), XFStyle(), XFStyle()
pad2.num_format_str = '00'
pad3.num_format_str = '000'
pad4.num_format_str = '0000'
current_row = 3
code_id = ''
for row, data in enumerate(gl, current_row):
# Only insert GLPivDownload objects with a matching IBMData object.
if ibm.filter(ibmIdentifier=data.codeID).exists():
# We have to aggregate all the GLPivotDownload objects with
# matching codeID values, and insert one row with total
# ytdActual and fyBudget values.
if code_id != data.codeID: # Reached the next codeID value.
current_row += 1 # Advance one row.
code_id = data.codeID
i = ibm.get(ibmIdentifier=data.codeID)
sheet.write(current_row, 0, data.codeID)
sheet.write(current_row, 1, int(data.costCentre), pad3)
sheet.write(current_row, 2, data.account, pad2)
sheet.write(current_row, 3, data.service, pad2)
sheet.write(current_row, 4, data.activity, pad3)
try:
sheet.write(current_row, 5, int(data.project), pad4)
except ValueError:
sheet.write(current_row, 5, data.project, pad4)
try:
sheet.write(current_row, 6, int(data.job), pad3)
except ValueError:
sheet.write(current_row, 6, data.job, pad3)
sheet.write(current_row, 7, data.jobName)
sheet.write(current_row, 8, data.activityName)
sheet.write(current_row, 9, data.projNameNo)
sheet.write(current_row, 10, i.budgetArea)
sheet.write(current_row, 11, i.projectSponsor)
sheet.write(current_row, 14, i.regionalSpecificInfo)
sheet.write(current_row, 15, i.servicePriorityID)
sheet.write(current_row, 18, i.annualWPInfo)
sheet.write(current_row, 19, data.mPRACategory)
ytd = gl.filter(codeID=code_id).aggregate(Sum('ytdActual'))
fy = gl.filter(codeID=code_id).aggregate(Sum('fybudget'))
sheet.write(current_row, 20, ytd['ytdActual__sum'])
sheet.write(current_row, 21, fy['fybudget__sum'])
# Insert the footer row formulae and '#END OF INPUT'
sheet.write(current_row+2, 0, '#END OF INPUT')
sheet.write(current_row+2, 20, Formula('SUM({}:{})'.format(cellname(4, 20), cellname(current_row, 20))))
sheet.write(current_row+2, 21, Formula('SUM({}:{})'.format(cellname(4, 21), cellname(current_row, 21))))
# Sheet 2 - Service priority checkboxes.
sheet = workbook.get_sheet(1)
write_service_priorities(sheet, nc_sp, pvs_sp, fm_sp)
def __init__(self, output=None, sheetname='Dieses Sheet'):
self.book = Workbook()
self.sheet = self.book.add_sheet(sheetname)
self.rownum = 0
self.output = output
datestyle = XFStyle()
datestyle.num_format_str = 'YYYY-MM-DD'
intstyle = XFStyle()
intstyle.num_format_str = '0'
floatstyle = XFStyle()
floatstyle.num_format_str = '0.00'
self.styles = {
'date': datestyle,
'int': intstyle,
'float': floatstyle,
}
def export_to_xls(self):
# TODO: take care of all the special cases
wb = Workbook()
ws = wb.add_sheet("Sheet 1")
objects = self.ordered_query(request)
form = self.edit_form_class()
DATE_STYLE = XFStyle()
DATE_STYLE.num_format_str = "DD/MM/YYYY"
col_names = ['id']
for field in form:
if isinstance(field, ModelFieldList):
continue
if hasattr(self.managed_class, field.name):
col_names.append(field.name)
for c, col_name in enumerate(col_names):
ws.write(0, c, col_name)
for r, obj in enumerate(objects):
for c, col_name in enumerate(col_names):
style = None
value = obj.display_value(col_name)
if isinstance(value, Entity):
value = value.name
elif isinstance(value, list):
if all(isinstance(x, basestring) for x in value):
value = "; ".join(value)
elif all(isinstance(x, Entity) for x in value):
value = "; ".join([x.name for x in value])
else:
raise Exception("I don't know how to export column {}".format(col_name))
elif isinstance(value, date):
style = DATE_STYLE
if style:
ws.write(r + 1, c, value, style)
else:
ws.write(r + 1, c, value)
fd = StringIO.StringIO()
wb.save(fd)
debug = request.args.get('debug_sql')
if debug:
# useful only in DEBUG mode, to get the debug toolbar in browser
return '<html><body>Exported</body></html>'
response = make_response(fd.getvalue())
response.headers['content-type'] = 'application/ms-excel'
filename = "%s-%s.xls" % (self.managed_class.__name__,
strftime("%d:%m:%Y-%H:%M:%S", gmtime()))
response.headers['content-disposition'] = 'attachment;filename="%s"' % filename
return response
def write_row(self, array):
"""
write a row into the file
"""
for i in range(0, len(array)):
value = array[i]
style = None
tmp_array = []
if isinstance(value, datetime.date) or isinstance(value, datetime.datetime):
tmp_array = [value.year, value.month, value.day]
value = xlrd.xldate.xldate_from_date_tuple(tmp_array, 0)
style = XFStyle()
style.num_format_str = "DD/MM/YY"
elif isinstance(value, datetime.time):
tmp_array = [value.hour, value.minute, value.second]
value = xlrd.xldate.xldate_from_time_tuple(tmp_array)
style = XFStyle()
style.num_format_str = "HH:MM:SS"
if style:
self.ws.write(self.current_row, i, value, style)
else:
self.ws.write(self.current_row, i, value)
self.current_row += 1
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 exportToExcel(self,objectBooks):
book = Workbook();
sheet1 = book.add_sheet('Sheet 1')
if( objectBooks):
i=0;
row1 = sheet1.row(i) ;
row1.write(0, ('เลขทะเบียน').decode('UTF8') );
row1.write(1, ('ที่').decode('UTF8'));
row1.write(2, ('ลงวันที่').decode('UTF8') );
row1.write(3, ('จาก').decode('UTF8') );
row1.write(4, ('ถึง').decode('UTF8') );
row1.write(5, ('เรื่อง').decode('UTF8') );
row1.write(6, ('การปฏิบัติ').decode('UTF8') );
row1.write(7, ('หมายเหตุ').decode('UTF8') );
i=i+1;
style = XFStyle();
style.num_format_str = 'D-MMM-YY';
for value in objectBooks:
row1 = sheet1.row(i) ;
row1.write(0, value.get('book_number').decode('UTF8') );
row1.write(1, value.get('book_at').decode('UTF8') );
row1.write(2, value.get('book_recive') ,style );
row1.write(3, value.get('book_from').decode('UTF8') );
row1.write(4, value.get('book_to' ).decode('UTF8') );
row1.write(5, value.get('book_detail').decode('UTF8') );
row1.write(6, value.get('book_operations').decode('UTF8') );
row1.write(7, value.get('book_remark').decode('UTF8') );
#row1.write(7, value.get('book_type_name').decode('UTF8') );
i=i+1;
dirTempFile = gettempdir() + _os.sep + str('books.xls');
book.save(dirTempFile);
#book.save(TemporaryFile());
return dirTempFile;
def _make_irradiation_line(self, sheet, row, irradname, cols):
dvc = self.dvc
chron = dvc.get_chronology(irradname)
_, prod = dvc.meta_repo.get_production(irradname, 'A')
sheet.write(row, 0, irradname)
for i, (label, key, fmt) in enumerate(cols[1:]):
style = XFStyle()
if key == 'duration':
v = chron.duration
elif key in INTERFERENCE_KEYS:
v = getattr(prod, key)
style.num_format_str = fmt
sheet.write(row, i + 1, v, style)
def save_xls(path,data):
"""
Save formated data to xls file
"""
w = Workbook()
ws = w.add_sheet('Sheet 1')
for i in range(len(data)):
for j in range(len(data[i])):
if j==4 and i>0:
continue
ws.write(i, j, data[i][j])
#rewrite date with apropriate format
date_format = XFStyle()
date_format.num_format_str = 'mm/dd/yyyy'
for i in range(1,len(data)):
ws.write(i, 4, data[i][4], date_format)
w.save(path)
def estiloCeldasSeparadas():
fnt = Font()
fnt.bold=True
#fnt.colour_index=2
borders = Borders()
borders.left = Borders.MEDIUM
borders.right = Borders.MEDIUM
borders.top = Borders.MEDIUM
borders.bottom = Borders.MEDIUM
pattern = Pattern()
pattern.pattern = Pattern.SOLID_PATTERN
pattern.pattern_fore_colour = 0x18#0x99
style = XFStyle()
style.num_format_str='YYYY-MM-DD'
style.font = fnt
style.borders = borders
style.pattern = pattern
return style
def render_excel(filename, col_title_list, data_row_list):
import StringIO
output = StringIO.StringIO()
export_wb = Workbook()
export_sheet = export_wb.add_sheet('Export')
col_idx = 0
for col_title in col_title_list:
export_sheet.write(0, col_idx, col_title)
col_idx += 1
row_idx = 1
for row_item_list in data_row_list:
col_idx = 0
for current_value in row_item_list:
if current_value:
current_value_is_date = False
if isinstance(current_value, datetime.datetime):
current_value = xlrd.xldate.xldate_from_datetime_tuple((current_value.year, current_value.month, \
current_value.day, current_value.hour, current_value.minute, \
current_value.second), 0)
current_value_is_date = True
elif isinstance(current_value, datetime.date):
current_value = xlrd.xldate.xldate_from_date_tuple((current_value.year, current_value.month, \
current_value.day), 0)
current_value_is_date = True
elif isinstance(current_value, datetime.time):
current_value = xlrd.xldate.xldate_from_time_tuple((current_value.hour, current_value.minute, \
current_value.second))
current_value_is_date = True
elif isinstance(current_value, models.Model):
current_value = str(current_value)
if current_value_is_date:
s = XFStyle()
s.num_format_str = 'M/D/YY'
export_sheet.write(row_idx, col_idx, current_value, s)
else:
export_sheet.write(row_idx, col_idx, current_value)
col_idx += 1
row_idx += 1
export_wb.save(output)
output.seek(0)
response = HttpResponse(output.getvalue())
response['Content-Type'] = 'application/vnd.ms-excel'
response['Content-Disposition'] = 'attachment; filename='+filename
return response
def toExcel(self, frames):
import StringIO
from xlwt import Workbook, XFStyle
# Need a file handle to save to
f = StringIO.StringIO()
frames = self.flattenFeature(frames)
if len(frames) > 0:
keys = frames[0].keys()
# Construct a workbook with one sheet
wb = Workbook()
s = wb.add_sheet("frames")
# Create the style for date/time
dateStyle = XFStyle()
dateStyle.num_format_str = "MM/DD/YYYY HH:MM:SS"
# Add the header/field labels
r = s.row(0)
for i, name in enumerate(keys):
r.write(i, name)
# Write the data
for i, frame in enumerate(frames):
print type(frame["capturetime"])
for j, name in enumerate(keys):
try:
if type(frame[name]) == datetime:
s.write(i + 1, j, frame[name], dateStyle)
else:
s.write(i + 1, j, str(frame[name]))
except KeyError:
pass
# Save the the string IO and grab the string data
wb.save(f)
output = f.getvalue()
f.close()
return output
def render_excel_to_file(filename, col_title_list, data_row_list):
output = StringIO.StringIO()
export_wb = Workbook()
export_sheet = export_wb.add_sheet('Export')
col_idx = 0
s = XFStyle()
s.num_format_str = 'M/D/YY'
for col_title in col_title_list:
export_sheet.write(0, col_idx, col_title)
col_idx += 1
row_idx = 1
for row_item_list in data_row_list:
col_idx = 0
for current_value in row_item_list:
if current_value:
current_value_is_date = False
if isinstance(current_value, datetime.datetime):
current_value = xlrd.xldate.xldate_from_datetime_tuple((current_value.year, current_value.month, \
current_value.day, current_value.hour, current_value.minute, \
current_value.second), 0)
current_value_is_date = True
elif isinstance(current_value, datetime.date):
current_value = xlrd.xldate.xldate_from_date_tuple((current_value.year, current_value.month, \
current_value.day), 0)
current_value_is_date = True
elif isinstance(current_value, datetime.time):
current_value = xlrd.xldate.xldate_from_time_tuple((current_value.hour, current_value.minute, \
current_value.second))
current_value_is_date = True
elif isinstance(current_value, models.Model):
current_value = str(current_value)
if current_value_is_date:
"""
s = XFStyle()
s.num_format_str = 'M/D/YY'
"""
export_sheet.write(row_idx, col_idx, current_value, s)
else:
export_sheet.write(row_idx, col_idx, current_value)
col_idx += 1
row_idx += 1
export_wb.save(filename)
def creaHojaDeTraduccion(excel, nombreHoja="traduccion"):
hoja=excel.creaHoja(nombreHoja)
hoja.col(0).width=90*256
hoja.col(1).width=20*256
fnt = Font()
fnt.bold=True
#fnt.colour_index=2
borders = Borders()
borders.left = Borders.MEDIUM
borders.right = Borders.MEDIUM
borders.top = Borders.MEDIUM
borders.bottom = Borders.MEDIUM
pattern = Pattern()
pattern.pattern = Pattern.SOLID_PATTERN
pattern.pattern_fore_colour = 0x18#0x99
style = XFStyle()
style.num_format_str='YYYY-MM-DD'
style.font = fnt
style.borders = borders
style.pattern = pattern
hoja.write(0,0,"Original Text",style)
hoja.write(0,1,"Translation",style)
return hoja
def write_resultset_to_xls_book(book,sheetname,results,**kwargs):
logging.debug('MAKE SHEET %s',sheetname)
sheet1 = book.add_sheet(sheetname)
names = None
if kwargs.has_key('names'):
names = kwargs['names']
if names is None:
names=[ "FIELD%03d" % (i+1) for i in range(len(results[0])) ]
for i,n in enumerate(names):
sheet1.write(0,i,n)
style = XFStyle()
style.num_format_str = "0.0"
for i,t in enumerate(results):
for j,v in enumerate(t):
if np.isnan(v):
sheet1.write(j+1,i,None)
else:
sheet1.write(j+1,i,v,style)
def write_hash_to_xls_book(book,sheetname,h,**kwargs):
sheet1 = book.add_sheet(sheetname)
sheet1.write(0,0,' / ')
for i,d in enumerate(h.keys()):
sheet1.write(i+1,0,str(d))
all_keys = list()
for k in h.values():
kk=k.keys()
all_keys.extend(kk)
all_keys=set(all_keys)
for i,d in enumerate(sorted(all_keys)):
sheet1.write(0,i+1,str(d))
style = XFStyle()
style.num_format_str = "0.0"
for i,t in enumerate(h.values()):
for j,k in enumerate(sorted(all_keys)):
if t.has_key(k):
sheet1.write(i+1,j+1,t[k],style)
else:
sheet1.write(i+1,j+1,None)
def code_update_report(workbook_ro, workbook, gl, gl_codeids, nc_sp, pvs_sp, fm_sp, ibm):
"""This report reads from the readonly workbook in order to perform some
cell processing.
"""
# Sheet 1
sheet = workbook.get_sheet(0)
sheet_ro = workbook_ro.get_sheet(0)
# Download hyperlink:
bigfont = easyxf('font: bold 1,height 360;') # Font height is in "twips" (1/20 of a point)
url = Formula('HYPERLINK("{}")'.format(settings.IBM_CODE_UPDATER_URI))
sheet.write(1, 0, url, bigfont)
# Padded zeroes number format
pad2, pad3, pad4 = XFStyle(), XFStyle(), XFStyle()
pad2.num_format_str = '00'
pad3.num_format_str = '000'
pad4.num_format_str = '0000'
# For each of the GL code IDs, take a subset of the query
# and insert values as required.
row = 4
max_col_idx = 21 # Start at column V.
for codeID in gl_codeids:
gl_pivs = gl.filter(codeID=codeID)
g = gl_pivs[0]
# Fill the non-resource columns.
sheet.write(row, 0, g.codeID)
sheet.write(row, 1, int(g.costCentre), pad3)
sheet.write(row, 2, g.account, pad2)
sheet.write(row, 3, g.service, pad2)
sheet.write(row, 4, g.activity, pad3)
try:
sheet.write(row, 5, int(g.project), pad4)
except ValueError:
sheet.write(row, 5, g.project, pad4)
try:
sheet.write(row, 6, int(g.job), pad3)
except ValueError:
sheet.write(row, 6, g.job, pad3)
sheet.write(row, 7, g.jobName)
sheet.write(row, 8, g.activityName)
sheet.write(row, 9, g.projNameNo)
sheet.write(row, 19, g.mPRACategory)
# Write the SUM formula.
sheet.write(row, 20, Formula('ROUND(SUM(V{}:GP{}), 0)'.format(row + 1, row + 1)))
# First, find the maximum column index in the template headers (row 4).
blank_cell = False
while not blank_cell:
if not sheet_ro.cell_value(3, max_col_idx): # Blank cell
blank_cell = True
else:
max_col_idx += 1
# Write ytdActual values for matching resource columns.
# Find the correct cell index of a matching resource code.
# If no match found, use the '0000' column (the first).
for gl_piv in gl_pivs:
resource_idx = 21 # Column V, '0000'
match_resource_code = False
for i in range(resource_idx, max_col_idx + 1):
if sheet_ro.cell_value(3, i) and int(sheet_ro.cell_value(3, i)) == gl_piv.resource:
match_resource_code = True
break
resource_idx += 1
if not match_resource_code: # No match was found.
resource_idx = 21 # Insert the ytdActual into column V.
# Write the ytdActual to the sheet.
sheet.write(row, resource_idx, gl_piv.ytdActual)
row += 1 # Advance one row, to the next Code ID.
row += 1
# Insert the footer row formulae and '#END OF INPUT'
sheet.write(row, 0, '#END OF INPUT')
sheet.write(row, 20, Formula('ROUND(SUM(V{}:GP{}), 0)'.format(row + 1, row + 1)))
for i in range(21, max_col_idx):
# For cell V:<end> in the footer row, insert a SUM formula.
sheet.write(row, i, Formula('ROUND(SUM({}:{}), 0)'.format(cellname(4, i), cellname(row - 1, i))))
# Sheet 2: Service priority checkboxes.
sheet = workbook.get_sheet(1)
write_service_priorities(sheet, nc_sp, pvs_sp, fm_sp)
# Sheet 3: Budget area & project sponsor lookup data.
# This is a list of unique budgetArea and projectSponsor values, written in
# as reference data for macros.
sheet = workbook.get_sheet(2)
write_budget_areas(sheet, ibm)
write_project_sponsors(sheet, ibm)
write_regional_spec_info(sheet, ibm)
# Select the first sheet.
sheet = workbook.get_sheet(0)
rb = open_workbook(mintPath,formatting_info=True)
r_sheet = rb.sheet_by_index(0)
wb = copy(rb)
w_sheet = wb.get_sheet(0)
num_rows = r_sheet.nrows
strRows = str(num_rows + 1)
StrRowsMinOne = str(num_rows)
logger.info('Number of Rows: ' + str(num_rows))
logger.info('Date to insert: ' + time.strftime("%m/%d/%Y"))
myDate = time.strftime("%m/%d/%Y")
dateStyle = XFStyle()
dateStyle.num_format_str='M/D/YY'
currencyStyle = XFStyle()
currencyStyle.num_format_str = '$#,##0.00'
#row, col, text
w_sheet.write(num_rows,0,datetime.datetime.now(),dateStyle)
for i in range (len(newBalances)):
column = int(i+1)
newBalance = newBalances[i]
w_sheet.write(num_rows,column,newBalance,currencyStyle)
#Total Assets
column = column + 1
w_sheet.write(num_rows,column,totalAsset,currencyStyle)
from datetime import date
from xlwt import Workbook, XFStyle, Borders, Pattern, Font
fnt = Font()
fnt.name = 'Arial'
borders = Borders()
borders.left = Borders.THICK
borders.right = Borders.THICK
borders.top = Borders.THICK
borders.bottom = Borders.THICK
pattern = Pattern()
pattern.pattern = Pattern.SOLID_PATTERN
pattern.pattern_fore_colour = 0x0A
style = XFStyle()
style.num_format_str = 'YYYY-MM-DD'
style.font = fnt
style.borders = borders
style.pattern = pattern
book = Workbook()
sheet = book.add_sheet('A Date')
sheet.write(1, 1, date(2009, 3, 18), style)
book.save('date.xls')
def exportToExcel(self,objectProject):
book = Workbook();
sheet1 = book.add_sheet('Sheet 1')
sheet1.col(1).width = 256*20;
sheet1.col(2).width = 256*80;
sheet1.col(3).width = 256*10;
sheet1.col(4).width = 256*20;
default_book_style = book.default_style
default_book_style.font.height = 20 * 36 # 36pt
fnt = Font()
fnt.name = 'Arial'
fnt.colour_index = 4
fnt.bold = True
borders = Borders()
borders.left = Borders.THIN
borders.right = Borders.THIN
borders.top = Borders.THIN
borders.bottom = Borders.THIN
pattern = Pattern();
pattern.pattern = Pattern.SOLID_PATTERN
pattern.pattern_fore_colour = 23
algn1 = Alignment();
algn1.wrap = 1;
#algn1.horz = Alignment.HORZ_CENTER
#algn1.vert = Alignment.VERT_TOP
alignHeader = Alignment();
alignHeader.horz = Alignment.HORZ_CENTER;
alignTop = Alignment();
alignTop.vert = Alignment.VERT_TOP
print "export";
if( objectProject):
i=0;
print "start" ;
styleHead = XFStyle();
styleHead.font = fnt;
styleHead.borders = borders;
styleHead.pattern = pattern;
styleHead.alignment = alignHeader;
row1 = sheet1.row(i) ;
row1.write(0, ('risk id').decode('UTF8'),styleHead );
sheet1.write_merge(i, i, 1, 2, ('รายละเอียด').decode('UTF8') ,styleHead );
# row1.write(1, ('รายละเอียด').decode('UTF8'));
row1.write(3, ('วันที่รายงาน').decode('UTF8'), styleHead );
row1.write(4, ('หน่วยที่รายงาน').decode('UTF8'), styleHead );
i=i+1;
style1 = XFStyle();
style1.alignment = algn1;
#style0 = xlwt.easyxf('font: name Times New Roman size 20, color-index black, bold on')
for value in objectProject:
row1 = sheet1.row(i) ;
styleRowDetail = XFStyle();
styleRowDetail.borders = borders;
styleRowDetail.alignment = alignTop;
StyleRowDetailWrap = styleRowDetail ;
StyleRowDetailWrap.alignment = algn1;
styleDate = XFStyle()
styleDate.num_format_str = 'DD-MM-YYYY' ; #'D-MMM-YY';
styleDate.borders = borders;
row1.write(0, value.get('risk_management_id'),styleRowDetail );
#row1.write(1, value.get('risk_detail').decode('UTF8') , style1);
sheet1.write_merge(i, i, 1, 2, value.get('risk_detail').decode('UTF8') , StyleRowDetailWrap );
row1.write(3, value.get('report_date') ,styleDate);
row1.write(4, value.get('report').decode('UTF8') ,styleRowDetail );
i=i+1;
row1 = sheet1.row(i) ;
row1.write(0," " );
row1.write(1,('หน่วยที่เกี่ยวข้อง').decode('UTF8') ,styleHead );
sheet1.write_merge(i, i, 2, 3,('รายละเอียดการตอบ').decode('UTF8') , styleHead );
i=i+1;
for sub in value.get('response') :
row1 = sheet1.row(i) ;
row1.write(0," " );
row1.write(1,sub.get('risk_team').decode('UTF8') , styleRowDetail );
sheet1.write_merge(i, i, 2, 3,sub.get('result').decode('UTF8') , StyleRowDetailWrap );
i=i+1;
dirTempFile = gettempdir() + _os.sep + str('simple.xls');
print dirTempFile;
book.save(dirTempFile);
def render_excel(filename, title_list, data_list, file_extension='.xls'):
if file_extension == '.csv':
response = HttpResponse(content_type='text/csv')
response[
'Content-Disposition'] = 'attachment; filename="%s"' % filename
csv_writer = csv.writer(response)
csv_writer.writerow(title_list)
for row_item_list in data_list:
for i in range(0, len(row_item_list)):
if row_item_list[i]:
if isinstance(row_item_list[i], datetime.datetime):
row_item_list[i] = row_item_list[i].strftime(
'%Y-%m-%d %H:%M:%S')
elif isinstance(row_item_list[i], datetime.date):
row_item_list[i] = row_item_list[i].strftime(
'%Y-%m-%d')
elif isinstance(row_item_list[i], datetime.time):
row_item_list[i] = row_item_list[i].strftime(
'%H:%M:%S')
csv_writer.writerow(row_item_list)
else:
from io import BytesIO
output = BytesIO()
export_wb = Workbook()
export_sheet = export_wb.add_sheet('Sheet1')
col_idx = 0
for col_title in title_list:
export_sheet.write(0, col_idx, "%s" % col_title)
col_idx += 1
row_idx = 1
for row_item_list in data_list:
col_idx = 0
for cell_value in row_item_list:
if cell_value:
cell_value_is_date = False
if isinstance(cell_value, datetime.datetime):
cell_value = xlrd.xldate.xldate_from_datetime_tuple(
(cell_value.year, cell_value.month, cell_value.day,
cell_value.hour, cell_value.minute,
cell_value.second), 0)
cell_value_is_date = True
elif isinstance(cell_value, datetime.date):
cell_value = xlrd.xldate.xldate_from_date_tuple(
(cell_value.year, cell_value.month,
cell_value.day), 0)
cell_value_is_date = True
elif isinstance(cell_value, datetime.time):
cell_value = xlrd.xldate.xldate_from_time_tuple(
(cell_value.hour, cell_value.minute,
cell_value.second))
cell_value_is_date = True
elif isinstance(cell_value, models.Model):
cell_value = str(cell_value)
if cell_value_is_date:
s = XFStyle()
s.num_format_str = 'M/D/YY'
export_sheet.write(row_idx, col_idx, cell_value, s)
else:
export_sheet.write(row_idx, col_idx, cell_value)
col_idx += 1
row_idx += 1
export_wb.save(output)
output.seek(0)
str_out = output.getvalue()
response = HttpResponse(str_out)
response['Content-Type'] = 'application/vnd.ms-excel'
response[
'Content-Disposition'] = 'attachment; filename="%s"' % filename
return response
def do_export(self):
"""
Does actual export. Called from a celery task as it may require a lot of API calls to grab all messages.
"""
from xlwt import Workbook, XFStyle
book = Workbook()
date_style = XFStyle()
date_style.num_format_str = 'DD-MM-YYYY HH:MM:SS'
base_fields = ["Time", "Message ID", "Flagged", "Labels", "Text", "Contact"]
contact_fields = self.org.get_contact_fields()
all_fields = base_fields + contact_fields
label_map = {l.name: l for l in Label.get_all(self.org)}
client = self.org.get_temba_client()
search = self.get_search()
# fetch all messages to be exported
messages = Message.search(self.org, search, None)
# extract all unique contacts in those messages
contact_uuids = set()
for msg in messages:
contact_uuids.add(msg.contact)
# fetch all contacts in batches of 25 and organize by UUID
contacts_by_uuid = {}
for uuid_chunk in chunks(list(contact_uuids), 25):
for contact in client.get_contacts(uuids=uuid_chunk):
contacts_by_uuid[contact.uuid] = contact
def add_sheet(num):
sheet = book.add_sheet(unicode(_("Messages %d" % num)))
for col in range(len(all_fields)):
field = all_fields[col]
sheet.write(0, col, unicode(field))
return sheet
# even if there are no messages - still add a sheet
if not messages:
add_sheet(1)
else:
sheet_number = 1
for msg_chunk in chunks(messages, 65535):
current_sheet = add_sheet(sheet_number)
row = 1
for msg in msg_chunk:
created_on = msg.created_on.astimezone(pytz.utc).replace(tzinfo=None)
flagged = SYSTEM_LABEL_FLAGGED in msg.labels
labels = ', '.join([label_map[l_name].name for l_name in msg.labels if l_name in label_map])
contact = contacts_by_uuid.get(msg.contact, None) # contact may no longer exist in RapidPro
current_sheet.write(row, 0, created_on, date_style)
current_sheet.write(row, 1, msg.id)
current_sheet.write(row, 2, 'Yes' if flagged else 'No')
current_sheet.write(row, 3, labels)
current_sheet.write(row, 4, msg.text)
current_sheet.write(row, 5, msg.contact)
for cf in range(len(contact_fields)):
if contact:
contact_field = contact_fields[cf]
current_sheet.write(row, 6 + cf, contact.fields.get(contact_field, None))
else:
current_sheet.write(row, 6 + cf, None)
row += 1
sheet_number += 1
temp = NamedTemporaryFile(delete=True)
book.save(temp)
temp.flush()
filename = 'orgs/%d/message_exports/%s.xls' % (self.org.id, random_string(20))
default_storage.save(filename, File(temp))
self.filename = filename
self.save(update_fields=('filename',))
subject = "Your messages export is ready"
download_url = settings.SITE_HOST_PATTERN % self.org.subdomain + reverse('cases.messageexport_read', args=[self.pk])
send_email(self.created_by.username, subject, 'cases/email/message_export', dict(link=download_url))
# force a gc
import gc
gc.collect()
#!/usr/bin/env python
# encoding: utf-8
"""
huTools/structured_xls.py - csv.py compatible Excel Export.
Created by Maximillian Dornseif on 2014-02-24.
Copyright (c) 2014, 2015 HUDORA. All rights reserved.
"""
import datetime
from cStringIO import StringIO
from xlwt import Workbook, XFStyle
datestyle = XFStyle()
datestyle.num_format_str = 'YYYY-MM-DD'
class XLSwriter(object):
"""csv-Modul kompatibles Interface zum Erzeugen von Excel Dateien.
Es muss allerdings save() oder getvalue() aufgerufen werden, um die XLS
Datei zu erzeugen."""
def __init__(self, output=None, sheetname='Dieses Sheet'):
self.book = Workbook()
self.sheet = self.book.add_sheet(sheetname)
self.rownum = 0
self.output = output
def writerow(self, row):
"""Eine Zeile schreiben. Row ist eine Liste von Werten."""
def main():
# Here we are reading the raw data csv file
re_style = ReportStyles()
csv_file = pd.read_csv('google.csv')
# Declaring list variables
month = []
year = []
Stock = []
# assigning values to variable
stock = csv_file['Stock']
# looping to extract months and year from date
for index in csv_file.index:
#assiging all the date to a variable
dates = pd.DatetimeIndex(csv_file['date'])
#appending years to a list
year.append(dates[index].year)
#appending months to a list
month.append(dates[index].month)
#appending stock to a list
Stock.append(stock[index])
#creating a data dictonary
data = {'Stock': Stock, 'Month': month, 'Year': year}
#creating a pandas dataframe from dict
frame = pd.DataFrame(data)
#Merging two data frames into one common data frame
csv_file = pd.merge(csv_file, frame, on='Stock')
#pivoting the data
piv = csv_file.pivot_table(['Open', 'High'],
rows='Month',
cols='Year',
margins=True,
aggfunc='count')
#writing pivot table to an excel
piv.to_excel('temp.xls')
book = open_workbook('temp.xls')
#reading the first sheet from excel
sheet0 = book.sheet_by_index(0)
col_cnt = sheet0.ncols
row_cnt = sheet0.nrows
pd1 = pd.read_excel(io='temp.xls', sheetname='Sheet1')
pd2 = pd.read_excel(io='temp.xls', sheetname='Sheet1')
writer = ExcelWriter('temp1.xls')
pd1.to_excel(writer, 'Sheet1', startcol=0, startrow=2)
pd2.to_excel(writer, 'Sheet1', startcol=(col_cnt + 2), startrow=2)
writer.save()
book = open_workbook('temp1.xls')
#reading the first sheet from excel
sheet0 = book.sheet_by_index(0)
col_cnt1 = sheet0.ncols
row_cnt1 = sheet0.nrows
currency = XFStyle()
currency.borders = re_style.borders_light()
currency.alignment = re_style.align_hor_right()
currency.num_format_str = "[$$-409]#,##0.00;-[$$-409]#,##0.00"
headings = XFStyle()
headings.borders = re_style.borders_light()
headings.alignment = re_style.align_hor_center()
headings.font = re_style.text_bold()
no_borders = XFStyle()
no_borders.borders = re_style.no_borders()
wb = Workbook()
ws = wb.add_sheet('Sample_Report', cell_overwrite_ok=True)
for row in range(row_cnt1):
for col in range(col_cnt1):
val = sheet0.cell_value(row, col)
if row < 2:
ws.row(row).write(col, val, no_borders)
elif col == (col_cnt + 2):
ws.row(row).write(col, val, headings)
# elif col > col_cnt and col < (col_cnt+3):
# ws.row(row).write(col, val, no_borders)
elif row > 4 and col > 0:
ws.row(row).write(col, val, currency)
elif row > 4 and col > (col_cnt + 3):
ws.row(row).write(col, val, currency)
else:
ws.row(row).write(col, val, headings)
wb.save('Report-1.xls')
#
# Excel styles:
#
from xlwt import Workbook, easyxf, Borders, Formula, XFStyle
hit_style1 = easyxf('pattern: pattern solid, fore_colour orange;')
hit_style2 = easyxf('pattern: pattern solid, fore_colour yellow;')
hit_style3 = easyxf('pattern: pattern solid, fore_colour yellow;')
stripe_style1 = easyxf('pattern: pattern solid, fore_colour white;')
stripe_style2 = easyxf('pattern: pattern solid, fore_colour white;')
header_style = easyxf('font: bold True;')
wrap_style = easyxf('align: wrap 1;')
center_style = easyxf('alignment: horizontal center;')
stats_style = XFStyle()
stats_style.num_format_str = "0.0000"
#
#
#
book = Workbook()
for seed in seed_cbiopath:
if seed not in ref2seed:
print "\n. I can't find the taxa", seed, "in your MSA. I'm skipping it!"
continue
mu_data = read_cbio_file(seed_cbiopath[seed])
if mu_data.__len__() <= 0:
print "\n. I didn't find any data in", seed_cbiopath[seed]
def exportToExcel(self, objectProject):
book = Workbook()
sheet1 = book.add_sheet('Sheet 1')
if (objectProject):
i = 0
row1 = sheet1.row(i)
row1.write(0, ('ประเภท').decode('UTF8'))
row1.write(1, ('ชื่อโครงการ').decode('UTF8'))
row1.write(2, ('รายละเอืยด').decode('UTF8'))
row1.write(3, ('งบประมาณรวม').decode('UTF8'))
row1.write(4, ('งบประมาณ').decode('UTF8'))
row1.write(5, ('เงินบำรุง').decode('UTF8'))
row1.write(6, ('งบประมาณอื่น').decode('UTF8'))
row1.write(7, ('งบประมาณอื่นจาก').decode('UTF8'))
row1.write(8, ('ผู้รับผิดชอบ').decode('UTF8'))
row1.write(9, ('กลุ่ม').decode('UTF8'))
row1.write(10, ('หน่วย/งาน').decode('UTF8'))
i = i + 1
style = XFStyle()
style.num_format_str = '#,##0.00'
for value in objectProject:
row1 = sheet1.row(i)
row1.write(0,
value.get('project_type').decode('UTF8'))
row1.write(1,
value.get('project_name').decode('UTF8'))
row1.write(2,
value.get('detail').decode('UTF8'))
row1.write(3, value.get('allBudget'), style)
row1.write(4, value.get('project_budget'), style)
row1.write(5, value.get('maintenance_funds_budget'), style)
row1.write(6, value.get('budget_other'), style)
if (value.get('budget_other_from')):
row1.write(7,
value.get('budget_other_from').decode('UTF8'))
if (value.get('user_name')):
row1.write(8,
value.get('user_name').decode('UTF8'))
row1.write(9,
value.get('division').decode('UTF8'))
row1.write(10,
value.get('section').decode('UTF8'))
i = i + 1
dirTempFile = gettempdir() + _os.sep + str('simple.xls')
book.save(dirTempFile)
#book.save(TemporaryFile());
return dirTempFile
from pathlib import Path
from typing import List, Dict, Union
from xlrd import open_workbook
from xlwt import XFStyle, Workbook
from hed_utils.support import log
FLOAT_FMT = XFStyle()
FLOAT_FMT.num_format_str = "##0.00"
@log.call(skip_args=["sheets"])
def write_sheets(sheets: Dict[str, List[Dict[str, Union[int, float, str]]]],
file: str):
""" Writes multiple sheets data to a file.
The column names for each sheet are the keys of the first dict record.
Automatically suffixes the file name with .xlsx if missing
Arguments:
sheets(dict) A dict with format { "sheet name": [ sheet records as dicts ] }
file(str) The output destination file
Returns:
dst_path(str) Absolute path to the output file
"""
dst_path = str(
def write_cursor_to_excel(curs, filename, sheetTitle):
"""write_cursor_to_excel
curs: a cursor for an open connection to an oracle database
filename: name of the XLS file to create
sheetTitle: name of the sheet to create
"""
# create style for header row - bold font, thin border below
fnt = Font()
fnt.bold = True
borders = Borders()
borders.bottom = Borders.THIN
hdrstyle = XFStyle()
hdrstyle.font = fnt
hdrstyle.borders = borders
# create a date format style for any date columns, if any
datestyle = XFStyle()
datestyle.num_format_str = 'DD/MM/YYYY'
# create the workbook. (compression: try to reduce the number of repeated styles)
wb = Workbook(style_compression=2)
# the workbook will have just one sheet
sh = wb.add_sheet(sheetTitle)
# write the header line, based on the cursor description
c = 0
colWidth = []
for col in curs.description:
#col[0] is the column name
#col[1] is the column data type
sh.write(0, c, col[0], hdrstyle)
colWidth.append(1) # arbitrary min cell width
if col[1] == cx_Oracle.DATETIME:
colWidth[-1] = len(datestyle.num_format_str)
if colWidth[-1] < len(col[0]):
colWidth[-1] = len(col[0])
c += 1
# write the songs, one to each row
r = 1
for song in curs:
row = sh.row(r)
for c in range(len(song)):
if song[c]:
if curs.description[c][1] == cx_Oracle.DATETIME:
row.write(c, song[c], datestyle)
else:
if colWidth[c] < len(str(song[c])):
colWidth[c] = len(str(song[c]))
row.write(c, song[c])
r += 1
for c in range(len(colWidth)):
sh.col(c).width = colWidth[c] * 350
# freeze the header row
sh.panes_frozen = True
sh.vert_split_pos = 0
sh.horz_split_pos = 1
wb.save(filename)
def write(folder,key, all_ID, all_score, all_correct, all_answer):
letters = ['A', 'B', 'C', 'D', 'E', 'X', 'M']
num_students = len(all_ID)
num_problems = len(key)
book = Workbook()
sheet1 = book.add_sheet('Score Summary')
sheet1.col(0).width = 3000
sheet1.col(1).width = 3000
sheet1.col(2).width = 3000
sheet1.col(3).width = 500
sheet1.col(4).width = 5000
sheet1.col(5).width = 3000
sheet1.col(6).width = 3000
sheet1.write(0, 0, 'Student ID') # A1
sheet1.write(0, 1, 'Raw Score') # B1
sheet1.write(0, 2, 'Percentage') # B1
pstyle = XFStyle()
pstyle.num_format_str = '0.00%'
dstyle = XFStyle()
dstyle.num_format_str = '0.00'
for j in range(num_students):
ID = all_ID[j]
score = all_score[j]
sheet1.row(j+1).set_cell_number(0, ID)
sheet1.row(j+1).set_cell_number(1, score)
sheet1.row(j+1).set_cell_number(2, score/float(num_problems), pstyle)
sheet1.write(0, 4, 'Test Score Statistics')
sheet1.write(0, 5, 'Raw Score')
sheet1.write(0, 6, 'Percentage')
sheet1.write(1, 4, 'Mean')
sheet1.write(2, 4, 'Median')
sheet1.write(3, 4, 'Max')
sheet1.write(4, 4, 'Min')
sheet1.write(5, 4, 'Standard Deviation')
mean_score = 'AVERAGE(B%d:B%d)' % (2, 1+num_students)
median_score = 'MEDIAN(B%d:B%d)' % (2, 1+num_students)
max_score = 'MAX(B%d:B%d)' % (2, 1+num_students)
min_score = 'MIN(B%d:B%d)' % (2, 1+num_students)
stdev = 'STDEV(B%d:B%d)' % (2, 1+num_students)
sheet1.write(1, 5, Formula(mean_score), dstyle)
sheet1.write(2, 5, Formula(median_score), dstyle)
sheet1.write(3, 5, Formula(max_score), dstyle)
sheet1.write(4, 5, Formula(min_score), dstyle)
sheet1.write(5, 5, Formula(stdev), dstyle)
mean_score = 'AVERAGE(C%d:C%d)' % (2, 1+num_students)
median_score = 'MEDIAN(C%d:C%d)' % (2, 1+num_students)
max_score = 'MAX(C%d:C%d)' % (2, 1+num_students)
min_score = 'MIN(C%d:C%d)' % (2, 1+num_students)
stdev = 'STDEV(C%d:C%d)' % (2, 1+num_students)
sheet1.write(1, 6, Formula(mean_score), pstyle)
sheet1.write(2, 6, Formula(median_score), pstyle)
sheet1.write(3, 6, Formula(max_score), pstyle)
sheet1.write(4, 6, Formula(min_score), pstyle)
sheet1.write(5, 6, Formula(stdev), pstyle)
col_totals = [sum(x) for x in zip(*all_correct)]
sheet2 = book.add_sheet('Test Diagnostics')
sheet2.col(0).width = 3000
sheet2.col(1).width = 3000
sheet2.col(2).width = 2000
sheet2.col(3).width = 500
sheet2.col(4).width = 5000
sheet2.col(5).width = 5000
sheet2.write(0, 0, 'Problem') # A1
sheet2.write(0, 1, '% Correct') # B1
for i in range(len(key)):
sheet2.row(i+1).set_cell_number(0, i+1)
sheet2.row(i+1).set_cell_number(1, col_totals[i]/float(num_students), pstyle)
# SHEET 3
sheet3 = book.add_sheet('Answer Summary')
sheet3.col(0).width = 3000
sheet3.col(1).width = 2000
sheet3.write(0, 0, 'Problem #') # A1
sheet3.write(0, 1, 'Key') # A1
for j in range(len(all_ID)):
sheet3.col(j+2).width = 2000
ID = all_ID[j]
sheet3.row(0).set_cell_number(j+2, ID)
for i in range(len(key)):
sheet3.write(i+1, j+2, letters[all_answer[j][i]])
for i in range(len(key)):
sheet3.row(i+1).set_cell_number(0, i+1)
sheet3.write(i+1, 1, letters[key[i]])
fout=os.path.join(folder,'grades.xls')
book.save(fout)
book.save(TemporaryFile())
def render_excel(filename, title_list, data_list, file_extension=".xls"):
if file_extension == ".csv":
str_out = ",".join(title_list)
for row_item_list in data_list:
for i in range(0, len(row_item_list)):
if row_item_list[i]:
if isinstance(row_item_list[i], datetime.datetime):
row_item_list[i] = row_item_list[i].strftime("%Y-%m-%d %H:%M:%S")
elif isinstance(row_item_list[i], datetime.date):
row_item_list[i] = row_item_list[i].strftime("%Y-%m-%d")
elif isinstance(row_item_list[i], datetime.time):
row_item_list[i] = row_item_list[i].strftime("%H:%M:%S")
str_out += ",".join(row_item_list)
content_type = "application/text"
else:
import StringIO
output = StringIO.StringIO()
export_wb = Workbook()
export_sheet = export_wb.add_sheet("Sheet1")
col_idx = 0
for col_title in title_list:
export_sheet.write(0, col_idx, "%s" % col_title)
col_idx += 1
row_idx = 1
for row_item_list in data_list:
col_idx = 0
for cell_value in row_item_list:
if cell_value:
cell_value_is_date = False
if isinstance(cell_value, datetime.datetime):
cell_value = xlrd.xldate.xldate_from_datetime_tuple(
(
cell_value.year,
cell_value.month,
cell_value.day,
cell_value.hour,
cell_value.minute,
cell_value.second,
),
0,
)
cell_value_is_date = True
elif isinstance(cell_value, datetime.date):
cell_value = xlrd.xldate.xldate_from_date_tuple(
(cell_value.year, cell_value.month, cell_value.day), 0
)
cell_value_is_date = True
elif isinstance(cell_value, datetime.time):
cell_value = xlrd.xldate.xldate_from_time_tuple(
(cell_value.hour, cell_value.minute, cell_value.second)
)
cell_value_is_date = True
elif isinstance(cell_value, models.Model):
cell_value = str(cell_value)
if cell_value_is_date:
s = XFStyle()
s.num_format_str = "M/D/YY"
export_sheet.write(row_idx, col_idx, cell_value, s)
else:
export_sheet.write(row_idx, col_idx, cell_value)
col_idx += 1
row_idx += 1
export_wb.save(output)
output.seek(0)
str_out = output.getvalue()
content_type = "application/vnd.ms-excel"
response = HttpResponse(str_out)
response["Content-Type"] = content_type
response["Content-Disposition"] = "attachment; filename=" + filename
return response
def export_bitacora(request, lenguage, trip_list):
email_template = '/email/0/'
error_list = 'Empty List'
if int(lenguage) == 1:
email_template = '/email/1/'
error_list = 'Lista vacía'
if not Validator.validate_view(request):
return redirect(email_template)
if len(trip_list) > 0:
user_id = request.session['id']
response = HttpResponse(content_type='application/ms-excel')
response[
'Content-Disposition'] = 'attachment; filename="bitacora.xls"'
wb = xlwt.Workbook(encoding='utf-8')
ws = wb.add_sheet('Trailers')
row_num = 0
rows_trip = Trip.objects.filter(trip_id__in=trip_list).filter(
user_id=user_id).values_list('assigned_id', 'vehicle_no',
'trailer_no', 'date',
'start_time', 'end_time',
'origin', 'destiny', 'trip_type')
date_format = XFStyle()
date_format.num_format_str = 'M/D/YY'
for row_trip in rows_trip:
row_num += 1
columns_trip = [
'Assigned Id', 'Vehicle No', 'Trailer No', 'Date',
'Start Time', 'End Time', 'Origin', 'Destiny', 'Trip Type'
]
columns_driver = ['Name', 'Lastname']
if int(lenguage) == 1:
columns_trip = [
'Id Asignado', 'No. de Vehiculo', 'No. de Caja',
'Fecha', 'Tiempo de salida', 'Tiempo de llegada',
'Origen', 'Destino', 'Tipo de Viaje'
]
columns_driver = ['Nombre', 'Apellido']
row_num = XlsGenerator.create_headers(columns_trip, ws,
row_num)
row_num = XlsGenerator.create_content(ws, row_num, row_trip,
xlwt.XFStyle())
row_num = XlsGenerator.create_headers(columns_driver, ws,
row_num)
rows_driver = Driver.objects.filter(
assigned_id=row_trip[0]).filter(
user_id=user_id).values_list('name', 'last_name')
for row_driver in rows_driver:
row_num = XlsGenerator.create_content(
ws, row_num, row_driver, xlwt.XFStyle())
wb.save(response)
return response
return render(request, 'hound-eng/error.html', {'error': error_list})
def exportReport1ToExcel(self,objectProject):
book = Workbook();
sheet1 = book.add_sheet('Sheet 1');
sheet1.col(1).width = 256*20;
sheet1.col(2).width = 256*80;
sheet1.col(3).width = 256*10;
sheet1.col(4).width = 256*10;
sheet1.col(5).width = 256*20;
sheet1.col(6).width = 256*20;
borders = Borders()
borders.left = Borders.THIN
borders.right = Borders.THIN
borders.top = Borders.THIN
borders.bottom = Borders.THIN
pattern = Pattern();
pattern.pattern = Pattern.SOLID_PATTERN
pattern.pattern_fore_colour = 23
wrap = Alignment();
wrap.wrap = 1;
wrap.vert = Alignment.VERT_TOP
alignHeader = Alignment();
alignHeader.horz = Alignment.HORZ_CENTER;
alignTop = Alignment();
alignTop.vert = Alignment.VERT_TOP
fnt = Font()
fnt.name = 'Arial'
fnt.colour_index = 4
fnt.bold = True
styleWrap = XFStyle();
styleWrap.alignment = wrap;
styleHead = XFStyle();
styleHead.font = fnt;
styleHead.borders = borders;
styleHead.pattern = pattern;
styleHead.alignment = alignHeader;
styleRowDetail = XFStyle();
styleRowDetail.borders = borders;
styleRowDetail.alignment = alignTop;
styleDate = XFStyle()
styleDate.num_format_str = 'DD-MM-YYYY' ; #'D-MMM-YY';
styleDate.borders = borders;
styleDate.alignment = alignTop;
StyleRowDetailWrap = styleRowDetail ;
StyleRowDetailWrap.alignment = wrap;
if( objectProject):
i=0;
row1 = sheet1.row(i) ;
row1.write(0, ('ลำดับที่').decode('UTF8') ,styleHead);
#sheet1.write_merge(i, i, 1, 2, ('รายละเอียด').decode('UTF8') );
row1.write(1, ('เลขความเสี่ยง').decode('UTF8'),styleHead );
row1.write(2, ('อุบัติการณ์/ภาวะไม่พึงประสงค์').decode('UTF8'),styleHead);
row1.write(3, ('วันที่รายงาน').decode('UTF8'),styleHead );
row1.write(4, ('ความรุนแรง').decode('UTF8'),styleHead );
row1.write(5, ('ด้าน/โปรแกรม').decode('UTF8') ,styleHead);
row1.write(6, ('หน่วยที่รายงาน').decode('UTF8') ,styleHead);
i=i+1;
for value in objectProject:
row1 = sheet1.row(i) ;
row1.write(0, value.get('row') ,styleRowDetail );
row1.write(1, str(value.get('risk_id')).decode('UTF8'),styleRowDetail );
row1.write(2, value.get('detail').decode('UTF8'),StyleRowDetailWrap );
row1.write(3, value.get('report_date') ,styleDate );
row1.write(4, value.get('level').decode('UTF8') ,styleRowDetail );
row1.write(5, value.get('pro').decode('UTF8') ,styleRowDetail );
row1.write(6, value.get('reporter').decode('UTF8') ,styleRowDetail );
i=i+1;
row2 = sheet1.row(i) ;
row2.write(2, ('รายละเอียด').decode('UTF8'),styleHead);
row2.write(3, ('หน่วยที่ตอบ').decode('UTF8'),styleHead );
row2.write(4, ('ระยะเวลาตอบ').decode('UTF8'),styleHead );
i=i+1;
for resp in value.get('responsible'):
row2 = sheet1.row(i) ;
row2.write(2, str(resp.get('detail')).decode('UTF8'),StyleRowDetailWrap);
row2.write(3, str(resp.get('service_name')).decode('UTF8'),styleRowDetail );
row2.write(4, str(resp.get('report_date')).decode('UTF8'),styleRowDetail );
i=i+1;
dirTempFile = gettempdir() + _os.sep + str('simpleReport1.xls');
book.save(dirTempFile);
return dirTempFile;
def query(input,output,args):
jql = input.read().replace('\n','')
headers= {
'Accept' : 'application/json',
'Content-Type' : 'application/json',
}
data={
'jql': '%s'%jql,
'startAt' : 0,
'maxResults' : 1000
}
if args.verbose:
json.dump(data,sys.stderr,indent=4)
sys.stderr.write('\n')
password = Passwords(args.hostname,'jira',args.username).password
auth=HTTPBasicAuth(args.username,password)
path='rest/api/2/search'
url='https://%s/%s'%(args.hostname,path)
format=' '.join(
map(
lambda x :
x['format'],
keys
)
)
response = requests.post(
url,
headers=headers,
data=json.dumps(data),
auth=auth
)
if args.verbose:
sys.stderr.write('%s\n'%response)
sys.stderr.write('%s\n'%response.json())
if args.xlsO:
workbook = Workbook()
sheet = workbook.add_sheet('Query')
for i in range(len(keys)):
sheet.col(i).width = 256*keys[i]['width']
sheet.write(0,i,keys[i]['name'])
if args.textO:
output.write('%s\n'%format.format(
*map(
lambda x :
x['name'],
keys
)
))
dividers = {
1 : 'Minutes',
60*60 : 'Hours',
60*60*8 : 'Days',
}
dts = '%Y-%m-%dT%H:%M:%S'
if response:
rows = 0
js = response.json()
if args.jsonO:
json.dump(js,output,indent=4)
else:
for issue in js['issues']:
rows = rows + 1
cols = values(issue)
if args.xlsO:
for i in range(len(cols)):
val = cols[i]
if keys[i]['path'] == 'key':
val = Formula('HYPERLINK("https://%s/browse/%s";"%s")'%(args.hostname,val,val))
if 'style' in keys[i].keys():
style = XFStyle()
style.num_format_str = keys[i]['style']
try:
val = val.rstrip('.000+1000')
val = datetime.strptime(val,dts)
sheet.write(rows,i,val,style)
except:
None
else:
sheet.write(rows,i,val)
if args.textO:
output.write('%s\n'%format.format(*cols))
srows=rows
for divider in sorted(dividers.keys()):
rows = rows + 1
if args.xlsO:
sheet.write(rows,0,dividers[divider])
for i in range(len(keys)):
if 'sum' not in keys[i].keys():
continue
col = chr(ord('A')+i)
formula = 'subtotal(9;%s%d:%s%d)/%d'%(
col,2,col,srows,divider
)
#print i,rows,col,formula
sheet.write(rows,i,Formula(formula))
if args.textO:
output.write('%s\n'%format.format(
*map(
lambda x :
x['sum']/divider if 'sum' in x.keys() else '',
keys
)
))
if args.xlsO:
workbook.save(output)
return
def exportReport5ToExcel(self,objectProject):
book = Workbook();
sheet1 = book.add_sheet('Sheet 1');
sheet1.col(1).width = 256*80;
sheet1.col(2).width = 256*10;
sheet1.col(3).width = 256*20;
borders = Borders()
borders.left = Borders.THIN
borders.right = Borders.THIN
borders.top = Borders.THIN
borders.bottom = Borders.THIN
pattern = Pattern();
pattern.pattern = Pattern.SOLID_PATTERN
pattern.pattern_fore_colour = 23
wrap = Alignment();
wrap.wrap = 1;
wrap.vert = Alignment.VERT_TOP
alignHeader = Alignment();
alignHeader.horz = Alignment.HORZ_CENTER;
alignTop = Alignment();
alignTop.vert = Alignment.VERT_TOP
fnt = Font()
fnt.name = 'Arial'
fnt.colour_index = 4
fnt.bold = True
styleWrap = XFStyle();
styleWrap.alignment = wrap;
styleHead = XFStyle();
styleHead.font = fnt;
styleHead.borders = borders;
styleHead.pattern = pattern;
styleHead.alignment = alignHeader;
styleRowDetail = XFStyle();
styleRowDetail.borders = borders;
styleRowDetail.alignment = alignTop;
styleDate = XFStyle()
styleDate.num_format_str = 'DD-MM-YYYY' ; #'D-MMM-YY';
styleDate.borders = borders;
styleDate.alignment = alignTop;
StyleRowDetailWrap = styleRowDetail ;
StyleRowDetailWrap.alignment = wrap;
if( objectProject):
i=0;
row1 = sheet1.row(i) ;
row1.write(0, ('risk id').decode('UTF8'),styleHead );
#sheet1.write_merge(i, i, 1, 2, ('รายละเอียด').decode('UTF8') );
row1.write(1, ('รายละเอียด').decode('UTF8'),styleHead);
row1.write(2, ('วันที่รายงาน').decode('UTF8'),styleHead );
row1.write(3, ('หน่วยที่รายงาน').decode('UTF8') ,styleHead);
i=i+1;
for value in objectProject:
row1 = sheet1.row(i) ;
row1.write(0, value.get('risk_management_id') ,styleRowDetail );
row1.write(1, value.get('risk_detail').decode('UTF8'),StyleRowDetailWrap );
#sheet1.write_merge(i, i, 1, 2, value.get('risk_detail').decode('UTF8') , StyleRowDetailWrap );
row1.write(2, value.get('report_date') ,styleDate );
row1.write(3, value.get('report').decode('UTF8') ,styleRowDetail );
i=i+1;
for sub in value.get('response') :
row1 = sheet1.row(i) ;
row1.write(0," " );
text = "(" + sub.get('risk_team').decode('UTF8') + " ) " + sub.get('result').decode('UTF8');
row1.write(1, text ,StyleRowDetailWrap );
i=i+1;
dirTempFile = gettempdir() + _os.sep + str('simpleReport5.xls');
book.save(dirTempFile);
return dirTempFile;
def render_excel(filename, title_list, data_list, file_extension='.xls'):
if file_extension == '.csv':
response = HttpResponse(content_type='text/csv')
response['Content-Disposition'] = 'attachment; filename="%s"' % filename
csv_writer = csv.writer(response)
csv_writer.writerow(title_list)
for row_item_list in data_list:
for i in range(0, len(row_item_list)):
if row_item_list[i]:
if isinstance(row_item_list[i], datetime.datetime):
row_item_list[i] = row_item_list[i].strftime(
'%Y-%m-%d %H:%M:%S'
)
elif isinstance(row_item_list[i], datetime.date):
row_item_list[i] = row_item_list[i].strftime(
'%Y-%m-%d'
)
elif isinstance(row_item_list[i], datetime.time):
row_item_list[i] = row_item_list[i].strftime(
'%H:%M:%S'
)
csv_writer.writerow(row_item_list)
else:
from io import BytesIO
output = BytesIO()
export_wb = Workbook()
export_sheet = export_wb.add_sheet('Sheet1')
col_idx = 0
for col_title in title_list:
export_sheet.write(0, col_idx, "%s" % col_title)
col_idx += 1
row_idx = 1
for row_item_list in data_list:
col_idx = 0
for cell_value in row_item_list:
if cell_value:
cell_value_is_date = False
if isinstance(cell_value, datetime.datetime):
cell_value = xlrd.xldate.xldate_from_datetime_tuple((
cell_value.year, cell_value.month,
cell_value.day, cell_value.hour,
cell_value.minute,
cell_value.second), 0)
cell_value_is_date = True
elif isinstance(cell_value, datetime.date):
cell_value = xlrd.xldate.xldate_from_date_tuple((
cell_value.year,
cell_value.month,
cell_value.day), 0)
cell_value_is_date = True
elif isinstance(cell_value, datetime.time):
cell_value = xlrd.xldate.xldate_from_time_tuple((
cell_value.hour,
cell_value.minute,
cell_value.second))
cell_value_is_date = True
elif isinstance(cell_value, models.Model):
cell_value = str(cell_value)
if cell_value_is_date:
s = XFStyle()
s.num_format_str = 'M/D/YY'
export_sheet.write(row_idx, col_idx, cell_value, s)
else:
export_sheet.write(row_idx, col_idx, cell_value)
col_idx += 1
row_idx += 1
export_wb.save(output)
output.seek(0)
str_out = output.getvalue()
response = HttpResponse(str_out)
response['Content-Type'] = 'application/vnd.ms-excel'
response['Content-Disposition'] = 'attachment; filename="%s"' % filename
return response
def service_priority_report(workbook, gl, ibm, nc_sp, pvs_sp, fm_sp):
# Sheet 1
sheet = workbook.get_sheet(0)
# Download hyperlink:
bigfont = easyxf('font: bold 1,height 360;') # Font height is in "twips" (1/20 of a point)
sheet.write(1, 0, Formula('HYPERLINK("{}")'.format(settings.IBM_SERVICE_PRIORITY_URI)), bigfont)
# Padded zeroes number format
pad2, pad3, pad4 = XFStyle(), XFStyle(), XFStyle()
pad2.num_format_str = '00'
pad3.num_format_str = '000'
pad4.num_format_str = '0000'
current_row = 3
code_id = ''
for row, data in enumerate(gl, current_row):
# Only insert GLPivDownload objects with a matching IBMData object.
if ibm.filter(ibmIdentifier=data.codeID):
# We have to aggregate all the GLPivotDownload objects with
# matching codeID values, and insert one row with total
# ytdActual and fyBudget values.
if code_id != data.codeID: # Reached the next codeID value.
code_id = data.codeID
i = ibm.get(ibmIdentifier=data.codeID)
current_row += 1 # Advance one row.
sheet.write(current_row, 0, data.codeID)
sheet.write(current_row, 1, int(data.costCentre), pad3)
sheet.write(current_row, 2, data.account, pad2)
sheet.write(current_row, 3, data.service, pad2)
sheet.write(current_row, 4, data.activity)
try:
sheet.write(current_row, 5, int(data.project), pad4)
except ValueError:
sheet.write(current_row, 5, data.project, pad4)
try:
sheet.write(current_row, 6, int(data.job), pad3)
except ValueError:
sheet.write(current_row, 6, data.job, pad3)
sheet.write(current_row, 7, data.jobName)
sheet.write(current_row, 8, data.activityName)
sheet.write(current_row, 9, data.projNameNo)
sheet.write(current_row, 10, i.budgetArea)
sheet.write(current_row, 11, i.projectSponsor)
sheet.write(current_row, 12, i.regionalSpecificInfo)
sheet.write(current_row, 13, i.servicePriorityID)
sheet.write(current_row, 14, i.annualWPInfo)
sheet.write(current_row, 15, data.mPRACategory)
ytd = gl.filter(codeID=code_id).aggregate(Sum('ytdActual'))
fy = gl.filter(codeID=code_id).aggregate(Sum('fybudget'))
sheet.write(current_row, 16, ytd['ytdActual__sum'])
sheet.write(current_row, 17, fy['fybudget__sum'])
# Insert the footer row formulae and '#END OF INPUT'
sheet.write(current_row + 2, 0, '#END OF INPUT')
sheet.write(current_row + 2, 16, Formula('SUM({}:{})'.format(cellname(4, 16), cellname(current_row, 16))))
sheet.write(current_row + 2, 17, Formula('SUM({}:{})'.format(cellname(4, 17), cellname(current_row, 17))))
# Sheet 2 - Service priority checkboxes.
sheet = workbook.get_sheet(1)
write_service_priorities(sheet, nc_sp, pvs_sp, fm_sp)
def code_update_report(workbook_ro, workbook, gl, gl_codeids, nc_sp, pvs_sp, fm_sp, ibm):
"""This report reads from the readonly workbook in order to perform some
cell processing.
"""
# Sheet 1
sheet = workbook.get_sheet(0)
sheet_ro = workbook_ro.get_sheet(0)
# Download hyperlink:
bigfont = easyxf('font: bold 1,height 360;') # Font height is in "twips" (1/20 of a point)
url = Formula('HYPERLINK("{}")'.format(settings.IBM_CODE_UPDATER_URI))
sheet.write(1, 0, url, bigfont)
# Padded zeroes number format
pad2, pad3, pad4 = XFStyle(), XFStyle(), XFStyle()
pad2.num_format_str = '00'
pad3.num_format_str = '000'
pad4.num_format_str = '0000'
# For each of the GL code IDs, take a subset of the query
# and insert values as required.
row = 4
max_col_idx = 21 # Start at column V.
for codeID in gl_codeids:
gl_pivs = gl.filter(codeID=codeID)
g = gl_pivs[0]
# Fill the non-resource columns.
sheet.write(row, 0, g.codeID)
sheet.write(row, 1, int(g.costCentre), pad3)
sheet.write(row, 2, g.account, pad2)
sheet.write(row, 3, g.service, pad2)
sheet.write(row, 4, g.activity, pad3)
try:
sheet.write(row, 5, int(g.project), pad4)
except ValueError:
sheet.write(row, 5, g.project, pad4)
try:
sheet.write(row, 6, int(g.job), pad3)
except ValueError:
sheet.write(row, 6, g.job, pad3)
sheet.write(row, 7, g.jobName)
sheet.write(row, 8, g.activityName)
sheet.write(row, 9, g.projNameNo)
sheet.write(row, 19, g.mPRACategory)
# Write the SUM formula.
sheet.write(row, 20, Formula('ROUND(SUM(V{}:GP{}), 0)'.format(row+1, row+1)))
# First, find the maximum column index in the template headers (row 4).
blank_cell = False
while not blank_cell:
if not sheet_ro.cell_value(3, max_col_idx): # Blank cell
blank_cell = True
else:
max_col_idx += 1
# Write ytdActual values for matching resource columns.
# Find the correct cell index of a matching resource code.
# If no match found, use the '0000' column (the first).
for gl_piv in gl_pivs:
resource_idx = 21 # Column V, '0000'
match_resource_code = False
for i in range(resource_idx, max_col_idx + 1):
if sheet_ro.cell_value(3, i) and int(sheet_ro.cell_value(3, i)) == gl_piv.resource:
match_resource_code = True
break
resource_idx += 1
if not match_resource_code: # No match was found.
resource_idx = 21 # Insert the ytdActual into column V.
# Write the ytdActual to the sheet.
sheet.write(row, resource_idx, gl_piv.ytdActual)
row += 1 # Advance one row, to the next Code ID.
row += 1
# Insert the footer row formulae and '#END OF INPUT'
sheet.write(row, 0, '#END OF INPUT')
sheet.write(row, 20, Formula('ROUND(SUM(V{}:GP{}), 0)'.format(row+1, row+1)))
for i in range(21, max_col_idx):
# For cell V:<end> in the footer row, insert a SUM formula.
sheet.write(row, i, Formula('ROUND(SUM({}:{}), 0)'.format(cellname(4, i), cellname(row-1, i))))
# Sheet 2: Service priority checkboxes.
sheet = workbook.get_sheet(1)
write_service_priorities(sheet, nc_sp, pvs_sp, fm_sp)
# Sheet 3: Budget area & project sponsor lookup data.
# This is a list of unique budgetArea and projectSponsor values, written in
# as reference data for macros.
sheet = workbook.get_sheet(2)
write_budget_areas(sheet, ibm)
write_project_sponsors(sheet, ibm)
write_regional_spec_info(sheet, ibm)
# Select the first sheet.
sheet = workbook.get_sheet(0)
# style for right justified and wrapped styleHeadersRight = XFStyle() styleHeadersRight.font = defaultFont styleHeadersRight.alignment.wrap = True styleHeadersRight.alignment = alignRight # blank line underline = XFStyle() uBorders = Borders() uBorders.bottom = Borders.THIN underline.borders = uBorders # date format styleDate = XFStyle() styleDate.num_format_str = 'MM/DD/YYYY' styleDate.font = defaultFont styleDate.alignment = alignLeft # currency format styleCurrency = XFStyle() styleCurrency.num_format_str = '#,##0.00' styleCurrency.font = defaultFont styleCurrency.alignment = alignRight # style for section-township-range zfillStyle = XFStyle() zfillStyle.num_format_str = '00' zfillStyle.font = defaultFont zfillStyle.alignment = alignLeft
