def createSpreadsheet(recordset, dst_dir_path, dst_file_name):
dst_dir_path = os.path.realpath(dst_dir_path)
logger.debug("opening new workbook at %s" %
(os.path.join(dst_dir_path, dst_file_name)))
if not os.path.exists(dst_dir_path):
logger.error("Destination path %s does not exist. Error: %s" %
(dst_dir_path))
raise Exception("Destination path %s does not exist." % (dst_dir_path))
# Start some Excel magic
wb = Workbook()
ws0 = wb.add_sheet('Forecast')
ws0.set_panes_frozen(True)
ws0.set_horz_split_pos(1)
logger.debug("Workbook created with name 'Forecast'")
#0 = Black, 1 = White, 2 = Red, 3 = Green, 4 = Blue, 5 = Yellow, 6 = Magenta, 7 = Cyan, 16 = Maroon, 17 = Dark Green, 18 = Dark Blue, 19 = Dark Yellow , almost brown), 20 = Dark Magenta, 21 = Teal, 22 = Light Gray, 23 = Dark Gray
#Create the default font
defaultFont = Font()
defaultFont.name = 'Arial'
defaultFont.bold = False
defaultFont.size = 10
#create the default Pattern
defaultPattern = Pattern()
#create the default style
defaultStyle = XFStyle()
defaultStyle.font = defaultFont
#Create the failure font
failureFont = Font()
failureFont.name = 'Arial'
failureFont.bold = True
failureFont.size = 10
failureFont.colour = 'red'
#create the failure Pattern
failurePattern = Pattern()
failurePattern.pattern = Pattern.SOLID_PATTERN
failurePattern.pattern_fore_colour = 2
#Create the failure style
#failureStyle = XFStyle()
#easyxf( 'font: colour red, bold True, size 10, name Arial;')
#failureStyle.font = failureFont
#failureStyle.pattern = failurePattern
failureStyle = easyxf('font: bold 1, name Arial , height 200, color red;')
#create the failure Pattern
runningPattern = Pattern()
runningPattern.pattern = Pattern.SOLID_PATTERN
runningPattern.pattern_fore_colour = 3
#Create a running style
runningStyle = XFStyle()
runningStyle.font = defaultFont
runningStyle.pattern = runningPattern
# Grey background for the header row
headerPattern = Pattern()
headerPattern.pattern = Pattern.SOLID_PATTERN
headerPattern.pattern_fore_colour = 22
# Bold Fonts for the header row
headerFont = Font()
headerFont.name = 'Arial'
headerFont.bold = True
headerFont.size = 10
# style and write field labels
headerStyle = XFStyle()
headerStyle.font = headerFont
headerStyle.pattern = headerPattern
logger.debug("Writing data to worksheet.")
row_number = 1
col_width_dict = dict()
for i in range(8):
col_width_dict[i] = 0
for record in recordset:
currentStyle = defaultStyle
# if str(record[4]).upper() == "FAILURE":
# logger.debug( "Status %s is in FAILURE" % ( str(record[5] ) ) )
# currentStyle = failureStyle
# elif str(record[4] ).upper() == "RUNNING":
# logger.debug( "Status %s is in RUNNING" % ( str(record[5] ) ) )
# currentStyle = runningStyle
column = 0
for field in record: #i.e. for each field in the record
logger.debug("Writing data field %s to worksheet" % (str(field)))
if field:
ws0.write(
row_number, column, str(field),
currentStyle) #write excel cell from the cursor at row 1
else:
ws0.write(
row_number, column, "",
currentStyle) #write excel cell from the cursor at row 1
if len(str(field)) > col_width_dict[column]:
#only redefine the column width if we need it to be bigger
col_width_dict[column] = len(str(field))
ws0.col(column).width = len(str(field)) * 256
ws0.col(column).width = col_width_dict[column] * 256
column = column + 1 #increment the column to get the next field
row_number += 1
logger.debug("Writing header row to worksheet.")
ws0.write(0, 0, 'DAY', headerStyle)
ws0.col(0).width = 10 * 256
ws0.write(0, 1, 'JOB NAME', headerStyle)
ws0.write(0, 2, 'UC', headerStyle)
ws0.col(2).width = 17 * 256
ws0.write(0, 3, 'START TIME', headerStyle)
ws0.write(0, 4, 'PERIODICALLY', headerStyle)
ws0.col(4).width = 16 * 256
ws0.write(0, 5, 'STATUS', headerStyle)
ws0.col(5).width = 11 * 256
logger.debug("Writing excel file %s" %
(os.path.join(dst_dir_path, dst_file_name)))
wb.save(os.path.join(dst_dir_path, dst_file_name))
return 0
