# -*- coding: cp1252 -*-
import xlwt as xl
from time import *
import xlrd as rd
import xlutils.copy as cp

def add_sheet(mysheet,headers,values,title,comment):
#   mysheet.insert_bitmap('a bitmap file ', 0, 0, 560,0,1,0.2)
    al = xl.Alignment()
    al.horz = xl.Alignment.HORZ_CENTER
    al.vert = xl.Alignment.VERT_CENTER
    borders = xl.Borders()
    borders.left = 1
    borders.right = 1
    borders.top = 1
    borders.bottom = 1
    li = 1
#    #write Title
    title_font=xl.Font() #make a font object
    title_font.bold=True
    title_font.colour_index = 4
    title_font.underline=False
    title_style = xl.XFStyle(); title_style.font = title_font;
    title_style.borders=borders
    title_style.alignment = al
    mysheet.write_merge(li,li,0,len(headers)-1,title,title_style)
    li += 1
#    #write headers
    header_font=xl.Font() #make a font object
    header_font.bold=True
    header_font.underline=True
    #font needs to be style actually
    header_style = xl.XFStyle(); header_style.font = header_font
    header_style.borders=borders
    header_style.font.height = 150
    header_style.alignment.horz= xl.Alignment.HORZ_CENTER
    for col,value in enumerate(headers):
        mysheet.write(li,col,value,header_style)
    li += 1
#    #write txt
    bg_color = xl.Pattern()
    bg_color.pattern = bg_color.SOLID_PATTERN
    bg_color.pattern_fore_colour = 22
    txt_col_style = xl.XFStyle(); txt_col_style.alignment.horz = xl.Alignment.HORZ_LEFT; txt_col_style.borders = borders
    txt_col_style.font.height = 150
    txt_black = xl.XFStyle(); txt_black.alignment.horz = xl.Alignment.HORZ_LEFT; txt_black.borders = borders
    txt_black.pattern= bg_color
    for row_num,row_values in enumerate(values):
        row_num+=li #start at row 2
        for col,value in enumerate(headers):
            if len(row_values) > col :
                if row_values[col] == '-' :
                    mysheet.write(row_num,col,'',txt_black)
                else :
                    mysheet.write(row_num,col,row_values[col],txt_col_style)
#    #write comment
    li = row_num + 1
    comal = xl.Alignment()
    comal.horz = xl.Alignment.HORZ_LEFT
    comal.vert = xl.Alignment.VERT_TOP
    comal.wrap = 1
    comment_style = xl.XFStyle()
    comment_style.borders=borders
    comment_style.alignment = comal
    mysheet.write_merge(li,li+4,0,len(headers)-1,comment,comment_style)

def save_in_excel():
    mydoc = xl.Workbook()
    mydoc.encoding= 'cp1252'
    mysheet=[]
    for sheet_num,h_tabs in enumerate(headers):
        mysheet.append(0)
        mysheet[sheet_num]=mydoc.add_sheet(sheets[sheet_num])
        add_sheet(mysheet[sheet_num],headers[sheet_num],values[sheet_num],titles[sheet_num],comments[sheet_num])
    mydoc.save('#SAVEAS#')

titles=#TITLE#
sheets=#SHEET#
headers=#HEADER#
values=#DATA#
comments=#COMMENT#
save_in_excel()