def format_cell(self,cell,type = 'normal'): # my_font = Font(size = 8) my_font = Font(name='Calibri', size=8, bold=False, italic=False, vertAlign=None, underline='none', strike=False, color='FF000000' # color='FFBB00' ) if type == "error": my_font.color = 'F60' my_font.size = 11 cell.font = my_font
def parse_font(self, font_node):
"""Read individual font"""
font = Font()
fontSizeEl = font_node.find('{%s}sz' % SHEET_MAIN_NS)
if fontSizeEl is not None:
font.size = fontSizeEl.get('val')
fontNameEl = font_node.find('{%s}name' % SHEET_MAIN_NS)
if fontNameEl is not None:
font.name = fontNameEl.get('val')
bold = font_node.find('{%s}b' % SHEET_MAIN_NS)
if bold is not None:
font.bold = bool(bold.get('val', True))
italic = font_node.find('{%s}i' % SHEET_MAIN_NS)
if italic is not None:
font.italic = bool(italic.get('val', True))
underline = font_node.find('{%s}u' % SHEET_MAIN_NS)
if underline is not None:
font.underline = underline.get('val', 'single')
strikethrough = font_node.find('{%s}strike' % SHEET_MAIN_NS)
if strikethrough is not None:
font.strikethrough = True
color = font_node.find('{%s}color' % SHEET_MAIN_NS)
if color is not None:
font.color.index = self._get_relevant_color(color)
return font
def test_conditional_formatting_addDxfStyle(self):
cf = ConditionalFormatting()
fill = Fill()
fill.start_color.index = 'FFEE1111'
fill.end_color.index = 'FFEE1111'
fill.fill_type = Fill.FILL_SOLID
font = Font()
font.name = 'Arial'
font.size = 12
font.bold = True
font.underline = Font.UNDERLINE_SINGLE
borders = Borders()
borders.top.border_style = Border.BORDER_THIN
borders.top.color.index = Color.DARKYELLOW
borders.bottom.border_style = Border.BORDER_THIN
borders.bottom.color.index = Color.BLACK
dxfId = cf.addDxfStyle(self.workbook, font, borders, fill)
assert dxfId == 0
dxfId = cf.addDxfStyle(self.workbook, None, None, fill)
assert dxfId == 1
assert len(self.workbook.style_properties['dxf_list']) == 2
def parse_fonts(root, color_index):
"""Read in the fonts"""
font_list = []
fonts = root.find('{%s}fonts' % SHEET_MAIN_NS)
if fonts is not None:
font_nodes = fonts.findall('{%s}font' % SHEET_MAIN_NS)
for font_node in font_nodes:
font = Font()
fontSizeEl = font_node.find('{%s}sz' % SHEET_MAIN_NS)
if fontSizeEl is not None:
font.size = fontSizeEl.get('val')
fontNameEl = font_node.find('{%s}name' % SHEET_MAIN_NS)
if fontNameEl is not None:
font.name = fontNameEl.get('val')
font.bold = True if len(font_node.findall('{%s}b' % SHEET_MAIN_NS)) else False
font.italic = True if len(font_node.findall('{%s}i' % SHEET_MAIN_NS)) else False
if len(font_node.findall('{%s}u' % SHEET_MAIN_NS)):
underline = font_node.find('{%s}u' % SHEET_MAIN_NS).get('val')
font.underline = underline if underline else 'single'
color = font_node.find('{%s}color' % SHEET_MAIN_NS)
if color is not None:
if color.get('indexed') is not None and 0 <= int(color.get('indexed')) < len(color_index):
font.color.index = color_index[int(color.get('indexed'))]
elif color.get('theme') is not None:
if color.get('tint') is not None:
font.color.index = 'theme:%s:%s' % (color.get('theme'), color.get('tint'))
else:
font.color.index = 'theme:%s:' % color.get('theme') # prefix color with theme
elif color.get('rgb'):
font.color.index = color.get('rgb')
font_list.append(font)
return font_list
def test_conditional_formatting_setDxfStyle(self):
cf = ConditionalFormatting()
fill = Fill()
fill.start_color.index = 'FFEE1111'
fill.end_color.index = 'FFEE1111'
fill.fill_type = Fill.FILL_SOLID
font = Font()
font.name = 'Arial'
font.size = 12
font.bold = True
font.underline = Font.UNDERLINE_SINGLE
borders = Borders()
borders.top.border_style = Border.BORDER_THIN
borders.top.color.index = Color.DARKYELLOW
borders.bottom.border_style = Border.BORDER_THIN
borders.bottom.color.index = Color.BLACK
cf.add('C1:C10', FormulaRule(formula=['ISBLANK(C1)'], font=font, border=borders, fill=fill))
cf.add('D1:D10', FormulaRule(formula=['ISBLANK(D1)'], fill=fill))
cf.setDxfStyles(self.workbook)
assert len(self.workbook.style_properties['dxf_list']) == 2
assert self.workbook.style_properties['dxf_list'][0] == {'font': font, 'border': borders, 'fill': fill}
assert self.workbook.style_properties['dxf_list'][1] == {'fill': fill}
def format_worksheet(worksheet, scenario_note, imei, scenario_start_time,
rssi_power_list, rssi_average_list):
# FUNCTION FOR SETTING UP THE EXCEL WORKSHEET LAYOUT, FORMAT AND CHART
line_chart = LineChart()
line_chart.title = "RSSI Power Graph"
line_chart.y_axis.title = 'RSSI Level'
line_chart.x_axis.title = 'Data Count'
rssi_power_data = Reference(worksheet,
min_col=9,
min_row=8,
max_col=9,
max_row=len(rssi_power_list) + 7)
power_series = Series(rssi_power_data, title="Actual RSSI Power")
line_chart.append(power_series)
rssi_average_data = Reference(worksheet,
min_col=15,
min_row=8,
max_col=15,
max_row=len(rssi_average_list) + 7)
average_series = Series(rssi_average_data, title="Averaged RSSI Power")
line_chart.append(average_series)
worksheet.add_chart(line_chart, "Q6")
worksheet.merge_cells('A1:O1')
worksheet.merge_cells('A3:O3')
worksheet.merge_cells('A4:O4')
worksheet.merge_cells('A6:C6')
worksheet.merge_cells('E6:I6')
worksheet.merge_cells('K6:O6')
worksheet['A3'].fill = PatternFill(start_color="1e90ff",
end_color="1e90ff",
fill_type="solid")
worksheet['A6'].fill = PatternFill(start_color="1e90ff",
end_color="1e90ff",
fill_type="solid")
worksheet['A7'].fill = PatternFill(start_color="1e90ff",
end_color="1e90ff",
fill_type="solid")
worksheet['B7'].fill = PatternFill(start_color="1e90ff",
end_color="1e90ff",
fill_type="solid")
worksheet['C7'].fill = PatternFill(start_color="1e90ff",
end_color="1e90ff",
fill_type="solid")
worksheet['E6'].fill = PatternFill(start_color="1e90ff",
end_color="1e90ff",
fill_type="solid")
worksheet['E7'].fill = PatternFill(start_color="1e90ff",
end_color="1e90ff",
fill_type="solid")
worksheet['F7'].fill = PatternFill(start_color="1e90ff",
end_color="1e90ff",
fill_type="solid")
worksheet['G7'].fill = PatternFill(start_color="1e90ff",
end_color="1e90ff",
fill_type="solid")
worksheet['H7'].fill = PatternFill(start_color="1e90ff",
end_color="1e90ff",
fill_type="solid")
worksheet['I7'].fill = PatternFill(start_color="1e90ff",
end_color="1e90ff",
fill_type="solid")
worksheet['K6'].fill = PatternFill(start_color="1e90ff",
end_color="1e90ff",
fill_type="solid")
worksheet['K7'].fill = PatternFill(start_color="1e90ff",
end_color="1e90ff",
fill_type="solid")
worksheet['L7'].fill = PatternFill(start_color="1e90ff",
end_color="1e90ff",
fill_type="solid")
worksheet['M7'].fill = PatternFill(start_color="1e90ff",
end_color="1e90ff",
fill_type="solid")
worksheet['N7'].fill = PatternFill(start_color="1e90ff",
end_color="1e90ff",
fill_type="solid")
worksheet['O7'].fill = PatternFill(start_color="1e90ff",
end_color="1e90ff",
fill_type="solid")
worksheet['A1'].alignment = Alignment(horizontal='center')
worksheet['A3'].alignment = Alignment(horizontal='center')
worksheet['A4'].alignment = Alignment(horizontal='center')
worksheet['A6'].alignment = Alignment(horizontal='center')
worksheet['D6'].alignment = Alignment(horizontal='center')
worksheet['E6'].alignment = Alignment(horizontal='center')
worksheet['J6'].alignment = Alignment(horizontal='center')
worksheet['K6'].alignment = Alignment(horizontal='center')
worksheet['A3'].font = Font(bold=True)
worksheet['A6'].font = Font(bold=True)
worksheet['K6'].font = Font(bold=True)
worksheet['A1'].font = Font(bold=True)
worksheet['E6'].font = Font(bold=True)
worksheet['A1'] = 'RSSI PARSE RESULTS'
worksheet['A3'] = 'Scenario notes'
worksheet['A4'] = scenario_note
worksheet['A6'] = 'Test details'
worksheet['A7'] = 'Target IMEI'
worksheet['A8'] = imei
worksheet['B7'] = 'Start time'
worksheet['B8'] = scenario_start_time
worksheet['C7'] = 'Duration'
worksheet['E6'] = 'Actual RSSI Power'
worksheet['E7'] = 'Data count'
worksheet['E8'] = '=COUNT(I8:I1048576)'
worksheet['F7'] = 'Average'
worksheet['F8'] = '=AVERAGE(I8:I1048576)'
worksheet['G7'] = 'Max'
worksheet['G8'] = '=MAX(I8:I1048576)'
worksheet['H7'] = 'Min'
worksheet['H8'] = '=MIN(I8:I1048576)'
worksheet['I7'] = 'Data'
worksheet['K6'] = 'Averaged RSSI Power'
worksheet['K7'] = 'Data count'
worksheet['K8'] = '=COUNT(O8:O1048576)'
worksheet['L7'] = 'Average'
worksheet['L8'] = '=AVERAGE(O8:O1048576)'
worksheet['M7'] = 'Max'
worksheet['M8'] = '=MAX(O8:O1048576)'
worksheet['N7'] = 'Min'
worksheet['N8'] = '=MIN(O8:O1048576)'
worksheet['O7'] = 'Data'
# Master Sheet (Sheet0)
if found == 1:
if 'Sheet0' not in wb.sheetnames:
# head = []
#head = ['Name', 'PS Number', 'Email', 'Phone Number', 'Batch', 'Location', 'BU', 'XYZ']
ws = wb.create_sheet('Sheet0')
print("CREATING")
s = ws.max_row # variable to store max rows for sl num
for i in range(1, len(head)+1):
ws.cell(row=1, column=i).value = head[i - 1]
for i in range(1, len(head)+1):
clr = ws.cell(row=1, column=i)
clr.font = Font(bold=True)
for i in range(1, len(head)+1):
ws.cell(row=s + 1, column=i).value = data[i - 1]
wb.save(path)
else:
# ws = wb.get_sheet_by_name('Sheet0')
ws = wb['Sheet0']
s = ws.max_row
for i in range(1, len(head)+1):
ws.cell(row=s + 1, column=i).value = data[i - 1]
wb.save(path)
if found == 0:
print("DATA NOT FOUND")
from openpyxl.chart import BarChart3D, Series, Reference
def cabecera(listubicacion, lista, wb, tipo, empadronadores):
idemp = lista[1]
if tipo == 1:
scr = lista[2]
else:
scr = lista[-3:]
ccdd = listubicacion[0]
ccpp = listubicacion[1]
ccdi = listubicacion[2]
departamento = listubicacion[3]
provincia = listubicacion[4]
distrito = listubicacion[5]
if tipo == 1:
ws = wb.get_sheet_by_name(idemp)
else:
ws = wb.get_sheet_by_name(lista)
ws["D1"] = u'CENSOS NACIONALES: XII DE LA POBLACION, VII DE VIVIENDA Y III DE COMUNIDADES INDIGENAS'
ws['D1'].alignment = Alignment(horizontal="center", vertical="center")
ws["D1"].font = Font(bold=True)
ws.merge_cells('D1:N1')
ws["D2"] = u'III Censo de Comunidades Nativas y I Censo de Comunidades Campesinas'
ws['D2'].alignment = Alignment(horizontal="center", vertical="center")
ws["D2"].font = Font(bold=True)
ws.merge_cells('D2:N2')
ws["D4"] = u'PROGRAMACION DE RUTAS DE TRABAJO RURAL'
ws['D4'].alignment = Alignment(horizontal="center", vertical="center")
ws["D4"].font = Font(bold=True, size=12)
ws.merge_cells('D4:N4')
ws["A6"] = u'DEPARTAMENTO'
ws["A7"] = u'PROVINCIA'
ws["A8"] = u'DISTRITO'
ws["M6"] = u'EMPADRONADOR'
ws["M8"] = u'JEFE DE SECCION'
ws["A10"] = u'DIA'
ws["A11"] = u'AER'
ws["A12"] = u'ACTIVIDAD'
ws["A23"] = u'G.OPER.'
ws["A24"] = u'PASAJES'
ws["A25"] = u'V: VIAJE'
ws["A26"] = u'E: EMPADRONAMIENTO'
ws["A27"] = u'T: TRASLADO'
ws["A28"] = u'S: SUPERVISION'
ws["Q5"] = u'Doc.CPV.03.105'
ws["A25"].font = Font(bold=True)
ws["A26"].font = Font(bold=True)
ws["A27"].font = Font(bold=True)
ws["A28"].font = Font(bold=True)
ws["Q5"].font = Font(bold=True)
ws["Q5"].alignment = Alignment(horizontal="right", vertical="bottom")
ws["Q10"] = u'TOTAL'
diasCuerpo(ws)
for x in ['A10', 'A11', 'A12', 'A21', 'A22', 'Q8', 'Q10']:
ws[x].alignment = Alignment(horizontal="center", vertical="center")
colorCelda(ws)
formatocampos(ws)
infoubicacion = [ccdd, ccpp, ccdi, departamento, provincia, distrito]
for x in range(6, 9):
ws["C{}".format(x)] = infoubicacion[x - 6]
ws["D{}".format(x)] = infoubicacion[x - 3]
if tipo == 1:
emp = lista[0]
ws['O6'] = emp
ws.cell(row=6, column=17).border = thin_border
elif tipo == 2:
listaEmp = []
for i in empadronadores:
if i[0] == lista:
listaEmp.append(i[1])
else:
pass
ws["O6"] = '-'.join(listaEmp)
ws.cell(row=6, column=17).border = thin_border
del listaEmp
ws['O8'] = scr
ws.cell(row=8, column=17).border = thin_border
for x in range(13, 23):
ws["A{}".format(x)] = u'CC.PP.'
ws["A{}".format(x)].alignment = Alignment(horizontal="center",
vertical="center")
ws.merge_cells('Q10:Q12')
listaceldas = ['A6:B6', 'A7:B7', 'A8:B8', 'D6:H6', 'D7:H7',
'D8:H8'] # 'N6:O6', 'N8:O8', 'P6:Q6', 'P8:Q8'
# for x in ['M6', 'M8', 'O6', 'O8', 'C6', 'C7', 'C8']:
# ws[x].alignment = Alignment(horizontal="center", vertical="center")
for cells in listaceldas:
ws.merge_cells(cells)
set_border(cells, ws)
for cells in [6, 7, 8]:
ws.cell(row=cells, column=3).border = thin_border
for cells in ['D6:H6', 'D7:H7', 'D8:H8']:
ws.merge_cells(cells)
set_border(cells, ws)
cuerpo_border('A10:Q24', ws)
Author: <David Emenike>
'''
import argparse
import sys
import openpyxl
from openpyxl.utils import get_column_letter
from openpyxl.styles import Font
number = int(sys.argv[1])
cells = number + 1
wb = openpyxl.Workbook()
sheet = wb.active
make_bold = Font(bold=True)
while cells > 1:
# Write outer cels values and make them bold
sheet.cell(row=cells, column=1).value = cells - 1
sheet.cell(row=1, column=cells).value = cells - 1
sheet.cell(row=cells, column=1).font = make_bold
sheet.cell(row=1, column=cells).font = make_bold
cells -= 1
col_length = number + 1
count = 0
while count < number:
def load_weights(self, weightfile, layer_num, channel_bins_pow, \
vis=None, featureLayers=None, show=3, view="flat", \
dn_ref=None, cmp_precision=4, log=None):
#Open the weights file
fp = open(weightfile, "rb")
#The first 4 values are header information
# 1. Major version number
# 2. Minor Version Number
# 3. Subversion number
# 4. IMages seen
version = np.fromfile(fp, count=3, dtype=np.int32)
version = [int(i) for i in version]
if version[0] * 10 + version[1] >= 2 and version[0] < 1000 and version[
1] < 1000:
seen = np.fromfile(fp, count=1, dtype=np.int64)
else:
seen = np.fromfile(fp, count=1, dtype=np.int32)
self.header = torch.from_numpy(np.concatenate((version, seen), axis=0))
self.seen = int(seen)
#The rest of the values are the weights
# Let's load them up
ann_version = False
ANN_FORMAT = 1 << 12
if (ANN_FORMAT == version):
ann_version = True
if ann_version is True:
n_layers = np.fromfile(fp, dtype=np.int32, count=1)
fp.seek(4 * n_layers[0], 1)
else:
weights = np.fromfile(fp, dtype=np.float32)
show_weights = False
if vis and dn_ref is None:
show_weights = True
cmp_weights = False
if dn_ref is not None:
cmp_weights = True
nLayerCmp = 0
nErr = 0
nSkip = 0
ptr = 0
conv_num = 0
for i in range(len(self.module_list)):
module_type = self.blocks[i + 1]["type"]
if module_type == "convolutional" or module_type == "dilated_convolutional": #dilated_convolutional for deeplab
if (ann_version is True):
fp.seek(4 * 2, 1)
#print(str(i)+" "+str(self.blocks[i + 1]))
s_layer = "layer {} {}: ".format(i, module_type)
show_layer_weights = False
if show_weights and (featureLayers == None
or len(featureLayers) == 0 or
featureLayers.split().count(str(i)) > 0):
show_layer_weights = True
cmp_layer_weights = False
if cmp_weights and (featureLayers == None
or len(featureLayers) == 0 or
featureLayers.split().count(str(i)) > 0):
cmp_layer_weights = True
model = self.module_list[i]
#compare weights
model_ref = None
if cmp_layer_weights:
nLayerCmp += 1
model_ref = dn_ref.module_list[i]
try:
batch_normalize = int(self.blocks[i +
1]["batch_normalize"])
except:
batch_normalize = 0
conv = model[0]
#compare weights
conv_ref = None
if model_ref is not None:
conv_ref = model_ref[0]
if (batch_normalize > 0):
bn = model[1]
#compare weights
bn_ref = None
if model_ref is not None:
bn_ref = model_ref[1]
#Get the number of weights of Batch Norm Layer
num_bn_biases = bn.bias.numel()
#Load the weights
if (ann_version is True):
fp.seek(4, 1)
weights = np.fromfile(fp,
dtype=np.float32,
count=num_bn_biases)
ptr = 0
bn_biases = torch.from_numpy(weights[ptr:ptr +
num_bn_biases])
ptr += num_bn_biases
if (ann_version is True):
fp.seek(4, 1)
weights = np.fromfile(fp,
dtype=np.float32,
count=num_bn_biases)
ptr = 0
bn_weights = torch.from_numpy(weights[ptr:ptr +
num_bn_biases])
ptr += num_bn_biases
if (ann_version is True):
fp.seek(4, 1)
weights = np.fromfile(fp,
dtype=np.float32,
count=num_bn_biases)
ptr = 0
bn_running_mean = torch.from_numpy(weights[ptr:ptr +
num_bn_biases])
ptr += num_bn_biases
if (ann_version is True):
fp.seek(4, 1)
weights = np.fromfile(fp,
dtype=np.float32,
count=num_bn_biases)
ptr = 0
bn_running_var = torch.from_numpy(weights[ptr:ptr +
num_bn_biases])
ptr += num_bn_biases
#Cast the loaded weights into dims of model weights.
bn_biases = bn_biases.view_as(bn.bias.data)
bn_weights = bn_weights.view_as(bn.weight.data)
bn_running_mean = bn_running_mean.view_as(bn.running_mean)
bn_running_var = bn_running_var.view_as(bn.running_var)
#Copy the data to model
bn.bias.data.copy_(bn_biases)
bn.weight.data.copy_(bn_weights)
bn.running_mean.copy_(bn_running_mean)
bn.running_var.copy_(bn_running_var)
#compare weights
if bn_ref:
no_diff, is_skip = tensor_diff(vis, bn.bias,
bn_ref.bias,
cmp_precision,
s_layer + "bn bias",
log, 0)
if no_diff == False:
nErr += 1
if is_skip == True:
nSkip += 1
no_diff, is_skip = tensor_diff(vis, bn.weight,
bn_ref.weight,
cmp_precision,
s_layer + "bn weight",
log, 0)
if no_diff == False:
nErr += 1
if is_skip == True:
nSkip += 1
no_diff, is_skip = tensor_diff(vis, bn.running_mean,
bn_ref.running_mean,
cmp_precision,
s_layer + "bn mean",
log, 0)
if no_diff == False:
nErr += 1
if is_skip == True:
nSkip += 1
no_diff, is_skip = tensor_diff(vis, bn.running_var,
bn_ref.running_var,
cmp_precision,
s_layer + "bn var", log,
0)
if no_diff == False:
nErr += 1
if is_skip == True:
nSkip += 1
#if tensor_diff(vis, bn.bias, bn_ref.bias, cmp_precision, s_layer+"bn bias", log, 0) == False:
# nErr += 1
#if tensor_diff(vis, bn.weight, bn_ref.weight, cmp_precision, s_layer+"bn weight", log, 0) == False:
# nErr += 1
#if tensor_diff(vis, bn.running_mean, bn_ref.running_mean, cmp_precision, s_layer+"bn mean", log, 0) == False:
# nErr += 1
#if tensor_diff(vis, bn.running_var, bn_ref.running_var, cmp_precision, s_layer+"bn var", log, 0) == False:
# nErr += 1
if show_layer_weights == True:
self.visualizeWeights(vis, i, bn_biases, show, view,
s_layer + "bn bias", log)
self.visualizeWeights(vis, i, bn_weights, show, view,
s_layer + "bn weight", log)
self.visualizeWeights(vis, i, bn_running_mean, show,
view, s_layer + "bn mean", log)
self.visualizeWeights(vis, i, bn_running_var, show,
view, s_layer + "bn var", log)
else:
#Number of biases
num_biases = conv.bias.numel()
#Load the weights
if (ann_version is True):
fp.seek(4, 1)
weights = np.fromfile(fp,
dtype=np.float32,
count=num_biases)
ptr = 0
conv_biases = torch.from_numpy(weights[ptr:ptr +
num_biases])
ptr = ptr + num_biases
#reshape the loaded weights according to the dims of the model weights
conv_biases = conv_biases.view_as(conv.bias.data)
#Finally copy the data
conv.bias.data.copy_(conv_biases)
#compare weights
if conv_ref:
no_diff, is_skip = tensor_diff(vis, conv.bias,
conv_ref.bias,
cmp_precision,
s_layer + "conv bias",
log, 0)
if no_diff == False:
nErr += 1
if is_skip == True:
nSkip += 1
#if tensor_diff(vis, conv.bias, conv_ref.bias, cmp_precision, s_layer+"conv bias", log, 0) == False:
# nErr += 1
if show_layer_weights:
self.visualizeWeights(vis, i, conv_biases, show, view,
s_layer + "conv bias", log)
#Let us load the weights for the Convolutional layers
num_weights = conv.weight.numel()
#Do the same as above for weights
if (ann_version is True):
fp.seek(4, 1)
weights = np.fromfile(fp,
dtype=np.float32,
count=num_weights)
ptr = 0
conv_weights = torch.from_numpy(weights[ptr:ptr + num_weights])
ptr = ptr + num_weights
conv_weights = conv_weights.view_as(conv.weight.data)
conv_num += 1
# print("*****************************************************************")
# print(conv_num)
# print(i) # layer num 0~160, 161 is yolo
# print(conv_weights.shape[0])
# e.g. layer = 157, conv No.107 convweights.shape = [1024, 512, 3, 3]
if i == layer_num:
print(
"*****************************************************************"
)
print('conv_weights.shape: {}'.format(conv_weights.shape))
conv_batches = conv_weights.shape[0] # 1024
conv_channels = conv_weights.shape[1] # 512
conv_heights = conv_weights.shape[2] # 3
conv_widthes = conv_weights.shape[3] # 3
print("valid:", conv_batches, conv_channels, conv_heights,
conv_widthes)
channel_bins = pow(
2, channel_bins_pow
) # value is 16, means group_num = 1024/16 = 64
channel_bins_num = conv_batches / channel_bins # 64
print('channel_bins: {}'.format(channel_bins))
# create excel 157
filefullpath = 'output/layer' + str(i) + '.xlsx'
if os.path.exists(filefullpath):
os.remove(filefullpath)
wb = Workbook()
# ws = wb.create_sheet('layer' + str(i), i)
ws = wb.active
ws.freeze_panes = 'B2'
alignment_set = Alignment(horizontal='center',
vertical='center')
fill_set = PatternFill("solid", fgColor="CCCCCC")
border_set = Border(left=Side(style='thin',
color=colors.BLACK),
right=Side(style='thin',
color=colors.BLACK),
top=Side(style='thin',
color=colors.BLACK),
bottom=Side(style='thin',
color=colors.BLACK))
# write 'Channel\Batch' in (1,1)
ws.cell(row=1, column=1, value='Channel\Batch')
ws.cell(row=1, column=1).font = Font(bold=True)
ws.cell(row=1, column=1).alignment = alignment_set
ws.cell(row=1, column=1).fill = fill_set
ws.cell(row=1, column=1).border = border_set
# write 'Channel_Max' in (1, int(channel_bins_num)+2)
ws.cell(row=1,
column=int(channel_bins_num) + 2,
value='Channel_Max')
ws.cell(row=1, column=int(channel_bins_num) +
2).font = Font(bold=True)
ws.cell(row=1, column=int(channel_bins_num) +
2).alignment = Alignment(horizontal='center',
vertical='center')
ws.cell(row=1,
column=int(channel_bins_num) + 2).fill = fill_set
ws.cell(row=1, column=int(channel_bins_num) +
2).border = border_set
# write 'Total_Max' in (conv_channels+2, 1)
ws.cell(row=conv_channels + 2, column=1, value='Total_Max')
ws.cell(row=conv_channels + 2,
column=1).font = Font(bold=True)
ws.cell(row=conv_channels + 2,
column=1).alignment = Alignment(
horizontal='center', vertical='center')
ws.cell(row=conv_channels + 2, column=1).fill = fill_set
ws.cell(row=conv_channels + 2,
column=1).border = border_set
# write 'Sparsity' in (conv_channels+3, 1)
ws.cell(row=conv_channels + 3, column=1, value='Sparsity')
ws.cell(row=conv_channels + 3,
column=1).font = Font(bold=True)
ws.cell(row=conv_channels + 3,
column=1).alignment = Alignment(
horizontal='center', vertical='center')
ws.cell(row=conv_channels + 3, column=1).fill = fill_set
ws.cell(row=conv_channels + 3,
column=1).border = border_set
# write groups = 64 in row
for j in range(1, int(channel_bins_num) + 1):
ws.cell(row=1, column=j + 1, value=j)
ws.cell(row=1, column=j + 1).font = Font(bold=True)
ws.cell(row=1, column=j + 1).alignment = Alignment(
horizontal='center', vertical='center')
ws.cell(row=1, column=j + 1).fill = fill_set
ws.cell(row=1, column=j + 1).border = border_set
# write channels = 512 in column
for k in range(1, conv_channels + 1):
ws.cell(row=k + 1, column=1, value=k)
ws.cell(row=k + 1, column=1).font = Font(bold=True)
ws.cell(row=k + 1, column=1).alignment = Alignment(
horizontal='center', vertical='center')
ws.cell(row=k + 1, column=1).fill = fill_set
ws.cell(row=k + 1, column=1).border = border_set
# slice
channel_bin_nozero = []
channel_bins_max = []
total_max = []
nozero_len_total = 0
for conv_channel in range(conv_channels): # conv_channels
for conv_batch in range(conv_batches):
for conv_height in range(conv_heights):
for conv_width in range(conv_widthes):
conv_value = conv_weights[conv_batch,
conv_channel,
conv_height,
conv_width]
if conv_value != 0:
channel_bin_nozero.append(conv_value)
if (conv_batch + 1) % channel_bins == 0:
channel_bin_nozero_len = len(
channel_bin_nozero
) # channel bin nozero number
channel_bins_max.append(
channel_bin_nozero_len) # Channel_Max list
channel_bins_max_value = max(
channel_bins_max) # Channel_Max value
total_max.append(
channel_bins_max_value) # Total_Max list
nozero_len_total += channel_bin_nozero_len
ws.cell(
row=conv_channel + 2,
column=((conv_batch + 1) / channel_bins +
1),
value=channel_bin_nozero_len)
ws.cell(
row=conv_channel + 2,
column=((conv_batch + 1) / channel_bins +
1)).alignment = Alignment(
horizontal='center',
vertical='center')
ws.cell(row=conv_channel + 2,
column=channel_bins_num + 2,
value=channel_bins_max_value)
ws.cell(row=conv_channel + 2,
column=channel_bins_num +
2).alignment = Alignment(
horizontal='center',
vertical='center')
channel_bin_nozero.clear()
channel_bins_max.clear()
total_max_value = max(total_max) # Total_Max value
ws.cell(row=conv_channels + 2,
column=2,
value=total_max_value)
ws.cell(row=conv_channels + 2,
column=2).alignment = Alignment(
horizontal='center', vertical='center')
sparsity = (conv_batches * conv_channels * conv_widthes *
conv_heights - nozero_len_total) / (
conv_batches * conv_channels *
conv_widthes * conv_heights)
sparsity = "%.2f%%" % (sparsity * 100)
ws.cell(row=conv_channels + 3, column=2, value=sparsity)
ws.cell(row=conv_channels + 3,
column=2).alignment = Alignment(
horizontal='center', vertical='center')
print('nozero_len_total: ', nozero_len_total)
# print('sparsity: %.2f%%'%(sparsity*100))
print('sparsity: ', sparsity)
print('total_max: ', total_max_value)
# #create chart
# bar_chart = BarChart()
# # bar_chart.type = 'col'
# bar_chart.title = 'Weights Nozero Distribution'
# # bar_chart.style = 12
# bar_chart.x_axis.title = 'Channels'
# bar_chart.x_axis.delete = False
# bar_chart.y_axis.title = 'Channel bins max'
# bar_chart.y_axis.delete = False
# lables = Reference(ws, min_row=2, max_row=10+1, min_col=1)
# data = Reference(ws, min_row=1, max_row=10+1, min_col=channel_bins_num+2)
# bar_chart.add_data(data, titles_from_data=True)
# bar_chart.set_categories(lables)
# # bar_chart.shape = 0
# bar_chart.height = 7.5
# bar_chart.width = 15
# bar_chart.dLbls = label.DataLabelList()
# bar_chart.dLbls.showVal = True
# bar_chart.showVal = True
# ws.add_chart(bar_chart,'B13')
wb.save(filefullpath)
def writeexcel(box, cutoffDate):
wb = Workbook()
ws = wb.active
bld = Font(bold=True)
ws.title = "Lag Report"
# '"Boat","Fab Start","Fab End","Canvas Start","Lag 1","Canvas End","Paint Start","Lag 2","Paint End","Outfit Start","Lag 3","Outfit End"'
props = [ ['A', 'Boat', 10.2602040816327], ['B', 'Firstday\nFab', 12.6887755102041], ['C', 'Lastday\nFab', 12.6887755102041], \
['D', 'Firstday\nCanvas', 12.6887755102041], ['E', 'Lag', 10.2602040816327], ['F', 'Lastday\nCanvas', 12.6887755102041], \
['G', 'Firstday\nPaint', 12.6887755102041], ['H', 'Lag', 10.2602040816327], ['I', 'Lastday\nPaint', 12.6887755102041], \
['J', 'Firstday\nOutfitting', 12.6887755102041], ['K', 'Lag', 10.2602040816327], ['L', 'Lastday\nOutfitting', 12.6887755102041] ]
for col, text, width in props:
ws['%s1' % col] = text
ws.column_dimensions[col].width = width
currentCell = ws.cell(1, ord(col) - 64)
currentCell.alignment = Alignment(horizontal='center')
currentCell.font = bld
# set header row height
ws.row_dimensions[1].height = 35.05
row_index = 2
for boat in sorted(box, key=lambda k: k['outfitStart']):
if boat["outfitEnd"] != "":
column_index = 1
if datetime.datetime.strptime(boat["outfitStart"],
"%Y-%m-%d").date() > cutoffDate:
if boat["canvasStart"] == "":
# no canvas stage only output for paint and outfitting
lag1 = ""
lag2 = max(workdays.networkdays(datetime.datetime.strptime(boat["fabEnd"],"%Y-%m-%d").date(), \
datetime.datetime.strptime(boat["paintStart"],"%Y-%m-%d").date(), holidays) -2, 0)
else:
lag1 = max(workdays.networkdays(datetime.datetime.strptime(boat["fabEnd"],"%Y-%m-%d").date(), \
datetime.datetime.strptime(boat["canvasStart"],"%Y-%m-%d").date(), holidays) -2, 0)
lag2 = max(workdays.networkdays(datetime.datetime.strptime(boat["canvasEnd"],"%Y-%m-%d").date(), \
datetime.datetime.strptime(boat["paintStart"],"%Y-%m-%d").date(), holidays) -2, 0)
lag3 = max(workdays.networkdays(datetime.datetime.strptime(boat["paintEnd"],"%Y-%m-%d").date(), \
datetime.datetime.strptime(boat["outfitStart"],"%Y-%m-%d").date(), holidays) -2, 0)
ws["A%s" % (row_index)] = boat["job"]
ws["B%s" % (row_index)] = boat["fabStart"]
ws["C%s" % (row_index)] = boat["fabEnd"]
ws["D%s" % (row_index)] = boat["canvasStart"]
ws["E%s" % (row_index)] = lag1
ws["F%s" % (row_index)] = boat["canvasEnd"]
ws["G%s" % (row_index)] = boat["paintStart"]
ws["H%s" % (row_index)] = lag2
ws["I%s" % (row_index)] = boat["paintEnd"]
ws["J%s" % (row_index)] = boat["outfitStart"]
ws["K%s" % (row_index)] = lag3
ws["L%s" % (row_index)] = boat["outfitEnd"]
row_index += 1
greyFill = PatternFill(start_color='FFCCCCCC',
end_color='FFCCCCCC',
fill_type='solid')
ws.conditional_formatting.add(
'A2:L%s' % (row_index - 1),
FormulaRule(formula=['ISEVEN(ROW())'], stopIfTrue=True, fill=greyFill))
wb.save('/tmp/LagReport-%s.xlsx' % (datetime.date.today()))
if last_weekday in [5, 6]:
celula_fds(header_letter[str(last_weekday)], row, '')
else:
celula_vazia(header_letter[str(last_weekday)], row)
last_weekday += 1
mes = {}
mes['1'] = 'Janeiro'
mes['2'] = 'FEvereiro'
mes['3'] = 'Março'
mes['4'] = 'Abril'
mes['5'] = 'Maio'
mes['6'] = 'Junho'
mes['7'] = 'Julho'
mes['8'] = 'Agosto'
mes['9'] = 'Setembro'
mes['10'] = 'Outubro'
mes['11'] = 'Novembro'
mes['12'] = 'Dezembro'
fonte_def = Font(name='calibri', size=16, bold=True)
sheet['b2'].value = mes[str(month.dt.month)] + ' ' + str(month.dt.year)
sheet['b2'].font = fonte_def
excel_file = 'David Jorge - {}-{:02} {}.xlsx'.format(month.dt.year,
month.dt.month,
mes[str(month.dt.month)])
print('excel_file:', excel_file)
wb.save(excel_file)
console.open_in(excel_file)
def __call__(self):
lectures = self.request.db.query(models.Lecture)
lectures = lectures.filter(models.Lecture.is_visible)
w = self.w
# sheet Tutorials
worksheet_tutorials = w.active
worksheet_tutorials.title = 'Tutorials'
header = [
'Tutor FirstName', 'Tutor LastName', 'Tutor Email',
'Tutorial Information', 'Student Count', 'Tutorial Room', 'Time',
'Comments'
]
worksheet_tutorials.append(header)
worksheet_tutorials.row_dimensions[1].font = Font(bold=True)
row_index = 2
for lecture in lectures.all():
tutorial_list = []
lecture_name = lecture.name
for tutorial in lecture.tutorials:
vtutor = tutorial.tutor.name(
) if tutorial.tutor is not None else 'None'
vtutor_first_name = tutorial.tutor.first_name if tutorial.tutor is not None else 'None'
vtutor_last_name = tutorial.tutor.last_name if tutorial.tutor is not None else 'None'
vemail = tutorial.tutor.email if tutorial.tutor is not None else 'None'
vplace = tutorial.place
vtime = tutorial.time.__html__()
vcomment = tutorial.comment
vstudent = len(tutorial.students.all())
tutorial_item = (vtutor_first_name, vtutor_last_name, vemail,
lecture_name, vstudent, vplace, vtime,
vcomment)
tutorial_list.append(tutorial_item)
# add summary lecture
new_list = []
lecture_item = ('', lecture.lecturer, '', lecture_name,
lecture.lecture_students.count(), '',
lecture.term.__html__(), '')
new_list.append(lecture_item)
# sort by tutor fistName
tutorial_list = sorted(tutorial_list)
tutorial_index = 1
for item in tutorial_list:
new_item = (item[0], item[1], item[2],
item[3] + ' Uebungsgruppe: ' + str(tutorial_index),
item[4], item[5], item[6], item[7])
new_list.append(new_item)
tutorial_index = tutorial_index + 1
# add to sheet
for item in new_list:
for col, d in enumerate(item, 1):
worksheet_tutorials.cell(row=row_index,
column=col,
value=d)
row_index = row_index + 1
# set column width
for column_cells in worksheet_tutorials.columns:
max_length = max(len(str(cell.value)) for cell in column_cells)
worksheet_tutorials.column_dimensions[
column_cells[0].column].width = max_length * 1.2
return self.createResponse()
def fix_formatting(filename, patient_name='', case_name=''):
''' Helper function '''
def border_add(border, top=None, right=None, left=None, bottom=None):
if top is None:
top = border.top
if left is None:
left = border.left
if right is None:
right = border.right
if bottom is None:
bottom = border.bottom
return openpyxl.styles.Border(top=top,
left=left,
right=right,
bottom=bottom)
# print(filename)
assert filename.endswith('.xlsx')
thin = Side(border_style='thin')
medium = Side(border_style='medium')
df = pd.read_excel(filename)
wb = openpyxl.load_workbook(filename)
ws = wb.worksheets[0]
''' Insert header '''
ws.oddHeader.center.text = patient_name
''' By default make all cells horizontal='center', except for the first and last two columns
'''
cells = [
ws[c + str(r)] for c in ascii_uppercase[1:ws.max_column - 1]
for r in range(1, ws.max_row + 1)
]
for cell in cells:
cell.alignment = Alignment(horizontal='center')
''' Make first column bold and right aligned '''
cells = [ws['A' + str(r)] for r in range(3, ws.max_row - 1)]
for cell in cells:
# cell.font = Font(bold=True)
cell.alignment = Alignment(horizontal='right')
cell.border = border_add(cell.border, right=medium)
''' Make first two rows bold and left aligned '''
cells = [
ws[c + str(i)] for i in range(1, 3)
for c in ascii_uppercase[0:ws.max_column]
]
for cell in cells:
cell.font = Font(bold=True)
cell.alignment = Alignment(horizontal='left')
''' Get locations of 'Allele' '''
allele_ij = []
for i in df.index:
for j, v in enumerate(df.iloc[i]):
if v == 'Allele':
allele_ij.append([i, j])
''' Apply medium thickness based on which cells have the word 'Allele'
'''
for i, j in allele_ij:
for k in range(0, ws.max_column):
cell = ws[ascii_uppercase[k] + str(i + 3)]
cell.border = border_add(cell.border, bottom=medium)
for k in range(0, j, 2):
cell = ws[ascii_uppercase[k] + str(i + 2)]
cell.border = border_add(cell.border, right=medium)
cell = ws[ascii_uppercase[k] + str(i + 3)]
cell.border = border_add(cell.border, right=medium)
cell = ws[ascii_uppercase[k + j] + str(i + 2)]
cell.border = border_add(cell.border, right=medium)
cell = ws[ascii_uppercase[k + j] + str(i + 3)]
cell.border = border_add(cell.border, right=medium)
cell = ws[ascii_uppercase[2 * j] + str(i + 2)]
cell.border = border_add(cell.border, right=medium)
cell = ws[ascii_uppercase[2 * j] + str(i + 3)]
cell.border = border_add(cell.border, right=medium)
ws.sheet_view.view = 'pageLayout'
openpyxl.worksheet.worksheet.Worksheet.set_printer_settings(
ws, paper_size=1, orientation='landscape')
ws.page_margins.bottom = 0.5
ws.page_margins.top = 0.5
ws.page_margins.left = 0.5
ws.page_margins.right = 0.5
ws.page_margins.header = 0.1
ws.sheet_properties.pageSetUpPr.fitToPage = True
ws.page_setup.fitToHeight = False
ws.page_setup.fitToWidth = 1
wb.save(filename)
wb.close() # FileFormat = 56 is for .xls extension
def generate_excel(report, name, reporting_start_datetime_local,
reporting_end_datetime_local, period_type):
wb = Workbook()
ws = wb.active
# Row height
ws.row_dimensions[1].height = 102
for i in range(2, 2000 + 1):
ws.row_dimensions[i].height = 42
# Col width
ws.column_dimensions['A'].width = 1.5
ws.column_dimensions['B'].width = 25.0
for i in range(ord('C'), ord('L')):
ws.column_dimensions[chr(i)].width = 15.0
# Font
name_font = Font(name='Constantia', size=15, bold=True)
title_font = Font(name='宋体', size=15, bold=True)
data_font = Font(name='Franklin Gothic Book', size=11)
table_fill = PatternFill(fill_type='solid', fgColor='1F497D')
f_border = Border(left=Side(border_style='medium', color='00000000'),
right=Side(border_style='medium', color='00000000'),
bottom=Side(border_style='medium', color='00000000'),
top=Side(border_style='medium', color='00000000'))
b_border = Border(bottom=Side(border_style='medium', color='00000000'), )
b_c_alignment = Alignment(vertical='bottom',
horizontal='center',
text_rotation=0,
wrap_text=True,
shrink_to_fit=False,
indent=0)
c_c_alignment = Alignment(vertical='center',
horizontal='center',
text_rotation=0,
wrap_text=True,
shrink_to_fit=False,
indent=0)
b_r_alignment = Alignment(vertical='bottom',
horizontal='right',
text_rotation=0,
wrap_text=True,
shrink_to_fit=False,
indent=0)
c_r_alignment = Alignment(vertical='bottom',
horizontal='center',
text_rotation=0,
wrap_text=True,
shrink_to_fit=False,
indent=0)
# Img
img = Image("excelexporters/myems.png")
img.width = img.width * 0.85
img.height = img.height * 0.85
# img = Image("myems.png")
ws.add_image(img, 'B1')
# Title
ws.row_dimensions[3].height = 60
ws['B3'].font = name_font
ws['B3'].alignment = b_r_alignment
ws['B3'] = 'Name:'
ws['C3'].border = b_border
ws['C3'].alignment = b_c_alignment
ws['C3'].font = name_font
ws['C3'] = name
ws['D3'].font = name_font
ws['D3'].alignment = b_r_alignment
ws['D3'] = 'Period:'
ws['E3'].border = b_border
ws['E3'].alignment = b_c_alignment
ws['E3'].font = name_font
ws['E3'] = period_type
ws['F3'].font = name_font
ws['F3'].alignment = b_r_alignment
ws['F3'] = 'Date:'
ws['G3'].border = b_border
ws['G3'].alignment = b_c_alignment
ws['G3'].font = name_font
ws['G3'] = reporting_start_datetime_local + "__" + reporting_end_datetime_local
ws.merge_cells("G3:H3")
if "reporting_period" not in report.keys() or \
"names" not in report['reporting_period'].keys() or len(report['reporting_period']['names']) == 0:
filename = str(uuid.uuid4()) + '.xlsx'
wb.save(filename)
return filename
##################################
current_row_number = 6
reporting_period_data = report['reporting_period']
has_names_data_flag = True
if "names" not in reporting_period_data.keys() or \
reporting_period_data['names'] is None or \
len(reporting_period_data['names']) == 0:
has_names_data_flag = False
if has_names_data_flag:
ws['B' + str(current_row_number)].font = title_font
ws['B' + str(current_row_number)] = name + ' 报告期消耗'
current_row_number += 1
category = reporting_period_data['names']
ca_len = len(category)
ws.row_dimensions[current_row_number].height = 60
ws['B' + str(current_row_number)].fill = table_fill
ws['B' + str(current_row_number)].border = f_border
col = 'C'
for i in range(0, ca_len):
ws[col + str(current_row_number)].fill = table_fill
ws[col + str(current_row_number)].font = name_font
ws[col + str(current_row_number)].alignment = c_c_alignment
ws[col + str(current_row_number)].border = f_border
ws[col + str(current_row_number)] = \
reporting_period_data['names'][i] + " " + reporting_period_data['energy_category_names'][i] + \
" (" + reporting_period_data['units'][i] + ")"
col = chr(ord(col) + 1)
current_row_number += 1
ws['B' + str(current_row_number)].font = title_font
ws['B' + str(current_row_number)].alignment = c_c_alignment
ws['B' + str(current_row_number)].border = f_border
ws['B' + str(current_row_number)] = '消耗'
col = 'C'
for i in range(0, ca_len):
ws[col + str(current_row_number)].font = name_font
ws[col + str(current_row_number)].alignment = c_c_alignment
ws[col + str(current_row_number)].border = f_border
ws[col + str(current_row_number)] = round(
reporting_period_data['subtotals'][i], 2)
col = chr(ord(col) + 1)
current_row_number += 1
ws['B' + str(current_row_number)].font = title_font
ws['B' + str(current_row_number)].alignment = c_c_alignment
ws['B' + str(current_row_number)].border = f_border
ws['B' + str(current_row_number)] = '环比'
col = 'C'
for i in range(0, ca_len):
ws[col + str(current_row_number)].font = name_font
ws[col + str(current_row_number)].alignment = c_c_alignment
ws[col + str(current_row_number)].border = f_border
ws[col + str(current_row_number)] = str(
round(reporting_period_data['increment_rates'][i] * 100, 2)) + '%' \
if reporting_period_data['increment_rates'][i] is not None else '-'
col = chr(ord(col) + 1)
current_row_number += 2
category_dict = group_by_category(
reporting_period_data['energy_category_names'])
for category_dict_name, category_dict_values in category_dict.items():
ws['B' + str(current_row_number)].font = title_font
ws['B' + str(current_row_number)] = \
name + ' ' + category_dict_name + ' (' + reporting_period_data['units'][category_dict_values[0]] + \
') 分项消耗占比'
current_row_number += 1
table_start_row_number = current_row_number
ws['B' + str(current_row_number)].fill = table_fill
ws['B' + str(current_row_number)].border = f_border
ws['C' + str(current_row_number)].font = name_font
ws['C' + str(current_row_number)].fill = table_fill
ws['C' + str(current_row_number)].alignment = c_c_alignment
ws['C' + str(current_row_number)].border = f_border
ws['C' + str(current_row_number)] = '消耗'
current_row_number += 1
for i in category_dict_values:
ws['B' + str(current_row_number)].font = title_font
ws['B' + str(current_row_number)].alignment = c_c_alignment
ws['B' + str(current_row_number)].border = f_border
ws['B' + str(current_row_number)] = \
reporting_period_data['names'][i] + " (" + reporting_period_data['units'][i] + ")"
ws['C' + str(current_row_number)].font = name_font
ws['C' + str(current_row_number)].alignment = c_c_alignment
ws['C' + str(current_row_number)].border = f_border
ws['C' + str(current_row_number)] = round(
reporting_period_data['subtotals'][i], 3)
current_row_number += 1
table_end_row_number = current_row_number - 1
pie = PieChart()
pie.title = \
name + ' ' + category_dict_name + ' (' + reporting_period_data['units'][category_dict_values[0]] + \
') 分项消耗占比'
labels = Reference(ws,
min_col=2,
min_row=table_start_row_number + 1,
max_row=table_end_row_number)
pie_data = Reference(ws,
min_col=3,
min_row=table_start_row_number,
max_row=table_end_row_number)
pie.add_data(pie_data, titles_from_data=True)
pie.set_categories(labels)
pie.height = 6.6
pie.width = 9
s1 = pie.series[0]
s1.dLbls = DataLabelList()
s1.dLbls.showCatName = False
s1.dLbls.showVal = True
s1.dLbls.showPercent = True
ws.add_chart(pie, 'D' + str(table_start_row_number))
if len(category_dict_values) < 4:
current_row_number = current_row_number - len(
category_dict_values) + 4
current_row_number += 1
#####################################
has_values_data = True
has_timestamps_data = True
if 'values' not in reporting_period_data.keys() or \
reporting_period_data['values'] is None or \
len(reporting_period_data['values']) == 0:
has_values_data = False
if 'timestamps' not in reporting_period_data.keys() or \
reporting_period_data['timestamps'] is None or \
len(reporting_period_data['timestamps']) == 0 or \
len(reporting_period_data['timestamps'][0]) == 0:
has_timestamps_data = False
if has_values_data and has_timestamps_data:
ca_len = len(reporting_period_data['names'])
time = reporting_period_data['timestamps'][0]
ws['B' + str(current_row_number)].font = title_font
ws['B' + str(current_row_number)] = name + ' 详细数据'
current_row_number += 1
chart_start_row_number = current_row_number
current_row_number += ca_len * 6
table_start_row_number = current_row_number
ws.row_dimensions[current_row_number].height = 60
ws['B' + str(current_row_number)].fill = table_fill
ws['B' + str(current_row_number)].font = title_font
ws['B' + str(current_row_number)].alignment = c_c_alignment
ws['B' + str(current_row_number)].border = f_border
ws['B' + str(current_row_number)] = '日期时间'
col = 'C'
for i in range(0, ca_len):
ws[col + str(current_row_number)].fill = table_fill
ws[col + str(current_row_number)].font = title_font
ws[col + str(current_row_number)].alignment = c_c_alignment
ws[col + str(current_row_number)].border = f_border
ws[col + str(current_row_number)] = \
reporting_period_data['names'][i] + " (" + reporting_period_data['units'][i] + ")"
col = chr(ord(col) + 1)
current_row_number += 1
for i in range(0, len(time)):
ws['B' + str(current_row_number)].font = title_font
ws['B' + str(current_row_number)].alignment = c_c_alignment
ws['B' + str(current_row_number)].border = f_border
ws['B' + str(current_row_number)] = time[i]
col = 'C'
for j in range(0, ca_len):
ws[col + str(current_row_number)].font = title_font
ws[col + str(current_row_number)].alignment = c_c_alignment
ws[col + str(current_row_number)].border = f_border
ws[col + str(current_row_number)] = round(reporting_period_data['values'][j][i], 2) \
if reporting_period_data['values'][j][i] is not None else 0.00
col = chr(ord(col) + 1)
current_row_number += 1
table_end_row_number = current_row_number - 1
ws['B' + str(current_row_number)].font = title_font
ws['B' + str(current_row_number)].alignment = c_c_alignment
ws['B' + str(current_row_number)].border = f_border
ws['B' + str(current_row_number)] = '小计'
col = 'C'
for i in range(0, ca_len):
ws[col + str(current_row_number)].font = title_font
ws[col + str(current_row_number)].alignment = c_c_alignment
ws[col + str(current_row_number)].border = f_border
ws[col + str(current_row_number)] = round(
reporting_period_data['subtotals'][i], 2)
col = chr(ord(col) + 1)
current_row_number += 2
format_time_width_number = 1.0
min_len_number = 1.0
min_width_number = 11.0 # format_time_width_number * min_len_number + 4 and min_width_number > 11.0
if period_type == 'hourly':
format_time_width_number = 4.0
min_len_number = 2
min_width_number = 12.0
elif period_type == 'daily':
format_time_width_number = 2.5
min_len_number = 4
min_width_number = 14.0
elif period_type == 'monthly':
format_time_width_number = 2.1
min_len_number = 4
min_width_number = 12.4
elif period_type == 'yearly':
format_time_width_number = 1.5
min_len_number = 5
min_width_number = 11.5
for i in range(0, ca_len):
line = LineChart()
line.title = '报告期消耗 - ' + \
reporting_period_data['names'][i] + " (" + reporting_period_data['units'][i] + ")"
labels = Reference(ws,
min_col=2,
min_row=table_start_row_number + 1,
max_row=table_end_row_number)
line_data = Reference(ws,
min_col=3 + i,
min_row=table_start_row_number,
max_row=table_end_row_number)
line.add_data(line_data, titles_from_data=True)
line.set_categories(labels)
line_data = line.series[0]
line_data.marker.symbol = "circle"
line_data.smooth = True
line.x_axis.crosses = 'min'
line.height = 8.25
line.width = format_time_width_number * len(time) if len(
time) > min_len_number else min_width_number
if line.width > 24:
line.width = 24
line.dLbls = DataLabelList()
line.dLbls.dLblPos = 't'
line.dLbls.showVal = True
line.dLbls.showPercent = False
chart_col = 'B'
chart_cell = chart_col + str(chart_start_row_number)
chart_start_row_number += 6
ws.add_chart(line, chart_cell)
#####################################
has_associated_equipment_flag = True
if "associated_equipment" not in report.keys() or \
"energy_item_names" not in report['associated_equipment'].keys() or \
len(report['associated_equipment']["energy_item_names"]) == 0 \
or 'associated_equipment_names_array' not in report['associated_equipment'].keys() \
or report['associated_equipment']['associated_equipment_names_array'] is None \
or len(report['associated_equipment']['associated_equipment_names_array']) == 0 \
or len(report['associated_equipment']['associated_equipment_names_array'][0]) == 0:
has_associated_equipment_flag = False
if has_associated_equipment_flag:
associated_equipment = report['associated_equipment']
ws['B' + str(current_row_number)].font = title_font
ws['B' + str(current_row_number)] = name + ' 相关设备数据'
current_row_number += 1
table_start_row_number = current_row_number
ws.row_dimensions[current_row_number].height = 60
ws['B' + str(current_row_number)].fill = table_fill
ws['B' + str(current_row_number)].font = name_font
ws['B' + str(current_row_number)].alignment = c_c_alignment
ws['B' + str(current_row_number)].border = f_border
ws['B' + str(current_row_number)] = '相关设备'
ca_len = len(associated_equipment['energy_item_names'])
for i in range(0, ca_len):
row = chr(ord('C') + i)
ws[row + str(current_row_number)].fill = table_fill
ws[row + str(current_row_number)].font = name_font
ws[row + str(current_row_number)].alignment = c_c_alignment
ws[row + str(current_row_number)].border = f_border
ws[row + str(current_row_number)] = \
reporting_period_data['names'][i] + " (" + reporting_period_data['units'][i] + ")"
associated_equipment_len = len(
associated_equipment['associated_equipment_names_array'][0])
for i in range(0, associated_equipment_len):
current_row_number += 1
row = str(current_row_number)
ws['B' + row].font = title_font
ws['B' + row].alignment = c_c_alignment
ws['B' + row] = associated_equipment[
'associated_equipment_names_array'][0][i]
ws['B' + row].border = f_border
for j in range(0, ca_len):
col = chr(ord('C') + j)
ws[col + row].font = title_font
ws[col + row].alignment = c_c_alignment
ws[col + row] = round(
associated_equipment['subtotals_array'][j][i], 2)
ws[col + row].border = f_border
filename = str(uuid.uuid4()) + '.xlsx'
wb.save(filename)
return filename
def infer_schema(data,
fname,
output_root='',
sample_size=1.0,
type_threshold=0.5,
n_jobs=1,
base_schema=None):
"""
Infer data types for all columns for the input table
Parameters
----------
data: pandas DataFrame
data table to infer
fname: string
the output file name
output_root: string, default=''
the root directory for the output file
sample_size: int or float(<= 1.0), default=1.0
int: number of sample rows to infer the data type (useful for large tables)
float: sample size in percentage
type_threshold: float(<= 1.0), default=0.5
threshold for inferring data type
n_jobs: int, default=1
the number of jobs to run in parallel
base_schema: pandas DataFrame, default=None
data schema to base on
"""
# check sample_size
if sample_size > 1:
if int(sample_size) != sample_size:
raise ValueError(
'sample_size: only accept integer when it is > 1.0')
if sample_size > data.shape[0]:
print("sample_size: %d is larger than the data size: %d" %
(sample_size, data.shape[0]))
# check output_root
if output_root != '':
if not os.path.isdir(output_root):
raise ValueError('output_root: root not exists')
# check type_threshold
if (type_threshold <= 0) or (type_threshold > 1):
raise ValueError('type_threshold: should be in (0, 1]')
# check base_schema
if base_schema is not None:
if type(base_schema) != pd.core.frame.DataFrame:
raise ValueError('base_schema: only accept pandas DataFrame')
# open a new workbook to store all result
wb = openpyxl.Workbook()
ws = wb['Sheet']
ws.title = 'schema'
# calculate sample size
if sample_size <= 1.0:
sample_size = int(data.shape[0] * sample_size)
# dictionary to store dropna sample data values
data_dropna_sample_values = {}
for col in data.columns.values:
if len(data[col].dropna()) <= sample_size:
data_dropna_sample_values[col] = data[col].dropna().values
else:
data = data.sample(sample_size).reset_index(drop=True)
data_dropna_sample_values[col] = data[col].dropna().values
# use data_dropna_sample_values to infer data type for each column
_n_jobs = np.min([n_jobs, len(data.columns.values)])
type_infos = Parallel(n_jobs=_n_jobs)(delayed(_infer_dtype)(
data_dropna_sample_values[col], col, type_threshold)
for col in data.columns.values)
type_infos_df = pd.DataFrame(type_infos)[['column', 'type']]
# dtype mapping for basic stat calculation
data_types = {}
for col in data.columns.values:
data_types[col] = type_infos_df.loc[type_infos_df['column'] == col,
'type'].values[0]
# get basic statistic information for all columns
stat_infos = Parallel(n_jobs=_n_jobs)(delayed(_cal_column_stat)(
data_dropna_sample_values[col], col, data_types[col])
for col in data.columns.values)
stat_infos_df = pd.DataFrame(stat_infos)
# merge dtype infomation with stat information
full_infos_df = type_infos_df.merge(stat_infos_df, on='column', how='left')
# add include column
full_infos_df['include'] = 1
full_infos_df = full_infos_df[[
'column', 'type', 'include', 'sample_value', 'sample_num_uni',
'sample_uni_percentage', 'sample_min', 'sample_median', 'sample_max',
'sample_std'
]]
# if base_schema is provided, we can compare with base schema
if base_schema is not None:
base_schema = base_schema[['column', 'type', 'include']]
base_schema.columns = [
'base_%s' % (col) for col in base_schema.columns.values
]
full_infos_df = full_infos_df.merge(base_schema,
left_on='column',
right_on='base_column',
how='outer')
# compare with the base schema
full_infos_df['base_column'] = full_infos_df['base_column'].apply(
lambda x: 'column not in base table' if pd.isnull(x) else x)
full_infos_df['column'] = full_infos_df['column'].apply(
lambda x: 'column not in current table' if pd.isnull(x) else x)
# reorder the column
full_infos_df['include'] = base_schema['base_include']
full_infos_df = full_infos_df[[
'column', 'base_column', 'type', 'base_type', 'include',
'sample_value', 'sample_num_uni', 'sample_uni_percentage',
'sample_min', 'sample_median', 'sample_max', 'sample_std'
]]
# add data validation for type column
val_type = DataValidation(type="list",
formula1='"key,numeric,str,date"',
allow_blank=False)
ws.add_data_validation(val_type)
# add data validation for include column
val_include = DataValidation(type="list",
formula1='"0,1"',
allow_blank=False)
ws.add_data_validation(val_include)
# get col_name, excel column mapping
column_mapping = {}
for i, col in enumerate(full_infos_df.columns):
column_mapping[col] = xlsxwriter.utility.xl_col_to_name(i)
# write everything into the worksheet
for r_idx, r in enumerate(
dataframe_to_rows(full_infos_df, index=False, header=True)):
ws.append(r)
for cell_idx, cell in enumerate(
ws.iter_cols(max_col=ws.max_column,
min_row=ws.max_row,
max_row=ws.max_row)):
cell = cell[0]
if r_idx != 0:
val_type.add(ws['%s%d' % (column_mapping['type'], ws.max_row)])
val_include.add(ws['%s%d' %
(column_mapping['include'], ws.max_row)])
if cell_idx == 0:
cell.font = Font(bold=True)
else:
cell.style = 'Accent5'
# add conditional formating
red_fill = PatternFill(bgColor="FFC7CE")
red_font = Font(color="9C0006")
green_fill = PatternFill(bgColor="C6EFCE")
green_font = Font(color="006100")
blue_fill = PatternFill(bgColor="9ECAE1")
blue_font = Font(color="08306B")
orange_fill = PatternFill(bgColor="FDD0A2")
orange_font = Font(color="A63603")
purple_fill = PatternFill(bgColor="DADAEB")
purple_font = Font(color="3F007D")
# red highlight if there is any inconsistent between base and the target
if base_schema is not None:
col1 = column_mapping['column']
col2 = column_mapping['base_column']
ws.conditional_formatting.add(
'%s2:%s%d' % (col1, col1, ws.max_row),
FormulaRule(formula=['%s2<>%s2' % (col1, col2)],
stopIfTrue=True,
fill=red_fill,
font=red_font))
ws.conditional_formatting.add(
'%s2:%s%d' % (col2, col2, ws.max_row),
FormulaRule(formula=['%s2<>%s2' % (col1, col2)],
stopIfTrue=True,
fill=red_fill,
font=red_font))
col1 = column_mapping['type']
col2 = column_mapping['base_type']
ws.conditional_formatting.add(
'%s2:%s%d' % (col1, col1, ws.max_row),
FormulaRule(formula=['%s2<>%s2' % (col1, col2)],
stopIfTrue=True,
fill=red_fill,
font=red_font))
ws.conditional_formatting.add(
'%s2:%s%d' % (col2, col2, ws.max_row),
FormulaRule(formula=['%s2<>%s2' % (col1, col2)],
stopIfTrue=True,
fill=red_fill,
font=red_font))
# yellow highlight column type and include (which need to be modified)
ws['%s1' % (column_mapping['type'])].style = 'Neutral'
ws['%s1' % (column_mapping['include'])].style = 'Neutral'
# green highlight for the mkey type and red highlight for the error type
type_cols = [column_mapping['type']]
if 'base_type' in column_mapping.keys():
type_cols.append(column_mapping['base_type'])
for col in type_cols:
ws.conditional_formatting.add(
'%s2:%s%d' % (col, col, ws.max_row),
FormulaRule(formula=['%s2="error"' % (col)],
stopIfTrue=True,
fill=red_fill,
font=red_font))
ws.conditional_formatting.add(
'%s2:%s%d' % (col, col, ws.max_row),
FormulaRule(formula=['%s2="key"' % (col)],
stopIfTrue=True,
fill=green_fill,
font=green_font))
ws.conditional_formatting.add(
'%s2:%s%d' % (col, col, ws.max_row),
FormulaRule(formula=['%s2="numeric"' % (col)],
stopIfTrue=True,
fill=blue_fill,
font=blue_font))
ws.conditional_formatting.add(
'%s2:%s%d' % (col, col, ws.max_row),
FormulaRule(formula=['%s2="str"' % (col)],
stopIfTrue=True,
fill=orange_fill,
font=orange_font))
ws.conditional_formatting.add(
'%s2:%s%d' % (col, col, ws.max_row),
FormulaRule(formula=['%s2="date"' % (col)],
stopIfTrue=True,
fill=purple_fill,
font=purple_font))
# red highlight for include = 0
ws.conditional_formatting.add(
'%s2:%s%d' %
(column_mapping['include'], column_mapping['include'], ws.max_row),
FormulaRule(formula=['%s2=0' % (column_mapping['include'])],
stopIfTrue=True,
fill=red_fill,
font=red_font))
# red highlight for sample_num_uni = 0 or 1, only one unique value
ws.conditional_formatting.add(
'%s2:%s%d' % (column_mapping['sample_num_uni'],
column_mapping['sample_num_uni'], ws.max_row),
FormulaRule(formula=['%s2=0' % (column_mapping['sample_num_uni'])],
stopIfTrue=True,
fill=red_fill,
font=red_font))
ws.conditional_formatting.add(
'%s2:%s%d' % (column_mapping['sample_num_uni'],
column_mapping['sample_num_uni'], ws.max_row),
FormulaRule(formula=['%s2=1' % (column_mapping['sample_num_uni'])],
stopIfTrue=True,
fill=red_fill,
font=red_font))
# adjust the column format for the worksheet
_adjust_ws(ws=ws, row_height=20)
wb.save(filename=os.path.join(output_root, 'data_schema_%s.xlsx' %
(fname)))
from openpyxl.styles import Font, Border, Side, PatternFill, Alignment
wb = load_workbook("sample.xlsx")
ws = wb.active
a1 = ws["A1"]
b1 = ws["B1"]
c1 = ws["C1"]
# 열의 너비 설정
ws.column_dimensions["A"].width = 5
# 행의 높이 설정
ws.row_dimensions[1].height = 50
# 폰트 스타일 설정
a1.font = Font(color="FF0000", italic=True, bold=True)
b1.font = Font(color="CC33FF", name="Arial", strike=True)
c1.font = Font(color="0000FF", size=20, underline="single")
# 테두리 적용
thin_border = Border(left=Side(style="thin"),
right=Side(style="thin"),
top=Side(style="thin"),
bottom=Side(style="thin"))
a1.border = thin_border
b1.border = thin_border
c1.border = thin_border
# 90점 넘는 셀에 대해서 초록색으로 적용
for row in ws.rows:
for cell in row:
cellCheckBoundsXLeft = 40
cellCheckBoundsXRight = 40
cellCheckBoundsY = 48
#Holds given cell positions - if given - otherwise automatic detecting is used.
cellUniversalArray = []
#Used to create imaginary bounds for checking nucleus
nucleusCheckBoundsX = 5
nucleusCheckBoundsY = 8
#Spreadsheet initializtaion
spreadsheet = workbook.active
spreadsheet.title = "Cell Results"
BOLD_FONT = Font(bold=True)
scaler = setKind * 9
spreadsheet.cell(row=scaler + 1, column=1,
value="Cell Image:").font = BOLD_FONT
spreadsheet.cell(row=scaler + 4, column=1,
value="Cell Number:").font = BOLD_FONT
spreadsheet.cell(row=scaler + 5, column=1, value="Cell Area:").font = BOLD_FONT
spreadsheet.cell(row=scaler + 6, column=1,
value="Nuclei Count:").font = BOLD_FONT
spreadsheet.cell(row=scaler + 7, column=1,
value="Cell Width:").font = BOLD_FONT
spreadsheet.cell(row=scaler + 8, column=1,
value="Cell Height:").font = BOLD_FONT
scaler = 2 * 9
spreadsheet.cell(row=scaler + 1, column=1,
value="Cell Image:").font = BOLD_FONT
def get_xls(self):
day_set = self.get_day_set()
deals = self.get_deals()
filename = '{branch} {service} {day}.xlsx'.format(
# city=self.service.branch.city.name,
branch=self.service.branch.name,
service=self.service.name,
day=self.day_start.strftime('%Y%m%d'))
wb = Workbook()
ws = wb.active
ws.title = '%s %s' % (self.service.name,
self.day_start.strftime('%Y%m%d'))
title_text = '{day} {branch} {service}'.format(
# city=self.service.branch.city.name,
branch=self.service.branch.name,
service=self.service.name,
day=self.day_start.strftime('%d.%m.%Y'))
title = ws.cell(row=1, column=1, value=title_text)
title.alignment = Alignment(horizontal='left')
title.font = Font(name='Calibri', size=11, bold=True)
ws.merge_cells('A1:C1')
labels = [
'Время', 'Клиент', 'День рождения', 'Телефон', 'Email', 'Статус',
'Стоимость', 'Предоплата', 'Безнал', 'Правщик', 'Администратор',
'Комментарий'
]
# names = [i[1] for i in data['tabs'][tab_key]['labels']]
columns = []
for index, label in enumerate(labels):
cell = ws.cell(row=2, column=index + 1, value=label)
cell.font = Font(name='Calibri', size=11, bold=True)
columns.append(cell.column)
row = 3
# import ipdb; ipdb.set_trace()
for time_key in sorted(day_set['timing'].keys()):
item = day_set['timing'][time_key]
# time_key = time['key']
time_key_str = '%s%s' % (day_set['key'], time_key)
if time_key in day_set['groups']:
row += 1
_ = ws.cell(row=row,
column=1,
value=day_set['groups'][time_key]['name'])
_ = ws.cell(row=row,
column=2,
value=day_set['groups'][time_key]['persons'])
# has deals
if time_key in day_set['timing'] and 'deals' in item:
# print('item', item)
if item.get('empty'):
row += 1
_ = ws.cell(row=row, column=1, value=item['label'])
for deal_key in item['deals']:
try:
deal = deals[deal_key]
except KeyError:
continue
# row += 1
# deal_text = '{step} ({cost} / {paid}), правщик: {master}'.format(
# step=deal['step_label'],
# cost=deal['cost'],
# paid=deal['paid'],
# master=deal['master__full_name'],
# )
# _ = ws.cell(row=row, column=1, value='%s (%s)' % (deal['start_string'], deal['minutes']))
# _ = ws.cell(row=row, column=2, value=deal_text)
# ws.merge_cells('B{row}:C{row}'.format(row=row))
# if deal['comment']:
# row += 1
# _ = ws.cell(row=row, column=2, value=deal['comment'])
# ws.merge_cells('B{row}:C{row}'.format(row=row))
for index, person in enumerate(deal['persons']):
row += 1
person_text = '%s' % (person['full_name'])
if person['control']:
person_text = '(Контроль) ' + person_text
if index == 0:
_ = ws.cell(
row=row,
column=1,
value='%s (%s)' %
(deal['start_string'], deal['minutes']))
_ = ws.cell(row=row,
column=6,
value=deal['step_label'])
_ = ws.cell(row=row, column=7, value=deal['cost'])
_ = ws.cell(row=row, column=8, value=deal['paid'])
_ = ws.cell(row=row,
column=9,
value=deal['paid_non_cash'])
_ = ws.cell(row=row,
column=10,
value=deal['master__full_name'])
_ = ws.cell(row=row,
column=11,
value=deal['manager__full_name'])
_ = ws.cell(row=row,
column=12,
value=deal['comment'])
_ = ws.cell(row=row, column=2, value=person_text)
_ = ws.cell(row=row, column=3, value=person['age'])
_ = ws.cell(row=row, column=4, value=person['phone'])
# _ = ws.cell(row=row, column=5, value=person['emails'])
if 'empty_finish' in item:
row += 1
_ = ws.cell(row=row,
column=1,
value=item['empty_finish']['label'])
border = Border(bottom=Side(border_style='thin', color='000000'))
ws.conditional_formatting.add(
'A1:K%s' % row, FormulaRule(formula=['E1=0'], border=border))
ws.column_dimensions['A'].width = 13
ws.column_dimensions['B'].width = 30
ws.column_dimensions['C'].width = 10
# row += 2
# for cellObj in ws['%s2:%s%s' % (columns[0], columns[-1], items_count)]:
# for cell in cellObj:
# import ipdb; ipdb.set_trace()
# print(cell.coordinate, cell.column)
# ws.column_dimensions[cell.column].bestFit = True
# ws[cell.coordinate].alignment = Alignment(horizontal='left')
response = HttpResponse(save_virtual_workbook(wb),
content_type='application/vnd.ms-excel')
response['Content-Disposition'] = 'inline; filename=%s' % urlquote(
filename).lower()
return response
def get(self, request, *args, **kwargs):
query = TiempoMuertonDet.objects.all()
wb = Workbook()
ws = wb.active
ws.tittle = 'Tiempos Muertos'
#Establer el nombre del archivo
nombre_archivo = "Reporte Tiempos Muertosw.xlsx"
ws['B1'].alignment = Alignment(horizontal='left', vertical='center')
ws['B1'].border = Border(left=Side(border_style='thin'),
right=Side(border_style='thin'),
top=Side(border_style='thin'),
bottom=Side(border_style='thin'))
ws['B1'].fill = PatternFill(start_color='66FFCC',
end_color='66FFCC',
fill_type='solid')
ws['B1'].font = Font(name='calibri', size=12, bold=True)
ws['B1'] = 'Company'
ws.merge_cells('B1:F1')
ws['B2'].alignment = Alignment(horizontal='left', vertical='center')
ws['B2'].border = Border(left=Side(border_style='thin'),
right=Side(border_style='thin'),
top=Side(border_style='thin'),
bottom=Side(border_style='thin'))
ws['B2'].fill = PatternFill(start_color='66FFCC',
end_color='66FFCC',
fill_type='solid')
ws['B2'].font = Font(name='calibri', size=12, bold=True)
ws['B2'] = 'Department'
ws.merge_cells('B2:F2')
ws['B3'].alignment = Alignment(horizontal='left', vertical='center')
ws['B3'].border = Border(left=Side(border_style='thin'),
right=Side(border_style='thin'),
top=Side(border_style='thin'),
bottom=Side(border_style='thin'))
ws['B3'].fill = PatternFill(start_color='66FFCC',
end_color='66FFCC',
fill_type='solid')
ws['B3'].font = Font(name='calibri', size=12, bold=True)
ws['B3'] = 'Reporte de Tiempos Muertos'
ws.merge_cells('B3:F3')
ws.row_dimensions[1].height = 20
ws.row_dimensions[2].height = 20
ws.row_dimensions[3].height = 20
ws.column_dimensions['B'].width = 20
ws.column_dimensions['C'].width = 20
ws.column_dimensions['D'].width = 20
ws.column_dimensions['E'].width = 20
ws['B6'].alignment = Alignment(horizontal='center', vertical='center')
ws['B6'].border = Border(left=Side(border_style='thin'),
right=Side(border_style='thin'),
top=Side(border_style='thin'),
bottom=Side(border_style='thin'))
ws['B6'].fill = PatternFill(start_color='66CFCC',
end_color='66CFCC',
fill_type='solid')
ws['B6'].font = Font(name='calibri', size=11, bold=True)
ws['B6'] = 'Fecha'
ws['C6'].alignment = Alignment(horizontal='center', vertical='center')
ws['C6'].border = Border(left=Side(border_style='thin'),
right=Side(border_style='thin'),
top=Side(border_style='thin'),
bottom=Side(border_style='thin'))
ws['C6'].fill = PatternFill(start_color='66CFCC',
end_color='66CFCC',
fill_type='solid')
ws['C6'].font = Font(name='calibri', size=11, bold=True)
ws['C6'] = 'Planta'
controlador = 7
for q in query:
ws.cell(row=controlador,
column=4).alignment = Alignment(horizontal='center',
vertical='center')
ws.cell(row=controlador,
column=4).border = Border(left=Side(border_style='thin'),
right=Side(border_style='thin'),
top=Side(border_style='thin'),
bottom=Side(border_style='thin'))
ws.cell(row=controlador,
column=4).fill = PatternFill(start_color='66CFCC',
end_color='66CFCC',
fill_type='solid')
ws.cell(row=controlador, column=4).font = Font(name='calibri',
size=11,
bold=True)
ws.cell(row=controlador, column=4).value = str(q.causa)
ws.cell(row=controlador,
column=5).alignment = Alignment(horizontal='center',
vertical='center')
ws.cell(row=controlador,
column=5).border = Border(left=Side(border_style='thin'),
right=Side(border_style='thin'),
top=Side(border_style='thin'),
bottom=Side(border_style='thin'))
ws.cell(row=controlador,
column=5).fill = PatternFill(start_color='66CFCC',
end_color='66CFCC',
fill_type='solid')
ws.cell(row=controlador, column=5).font = Font(name='calibri',
size=11,
bold=True)
ws.cell(row=controlador, column=5).value = q.cantidad
ws.cell(row=controlador,
column=6).alignment = Alignment(horizontal='center',
vertical='center')
ws.cell(row=controlador,
column=6).border = Border(left=Side(border_style='thin'),
right=Side(border_style='thin'),
top=Side(border_style='thin'),
bottom=Side(border_style='thin'))
ws.cell(row=controlador,
column=6).fill = PatternFill(start_color='66CFCC',
end_color='66CFCC',
fill_type='solid')
ws.cell(row=controlador, column=6).font = Font(name='calibri',
size=11,
bold=True)
ws.cell(row=controlador, column=6).value = q.observaciones
controlador += 1
response = HttpResponse(content_type='application/ms-excel')
contenido = "attachment; filename = {0}".format(nombre_archivo)
response["Content-Disposition"] = contenido
wb.save(response)
return response
startdate = datetime.datetime.strptime(r['start'], '%Y-%m-%d')
finishdate = datetime.datetime.strptime(r['finish'], '%Y-%m-%d')
print(startdate.weekday())
les = requests.get("http://localhost/var/www/html/Raspisator/API/getLessons.php?project_id="+sys.argv[1])
les = json.loads(les.text)
les = {i['id'] : i for i in les}
print(sinfo)
grades = sinfo['grades']
teachers = sinfo['teachers']
subjects = sinfo['subjects']
wb = openpyxl.workbook.Workbook()
ws = wb.active
ws.merge_cells('A'+str(koef-1)+':'+str(unichr(ord('A')+len(grades)+1))+str(koef-1))
ws['A'+str(koef-1)] = "Расписание на " + str(startdate.date().day)+" "+months[(startdate).date().month-1] + " - " + str(finishdate.date().day)+" "+months[finishdate.date().month-1]
ws['A'+str(koef-1)].font = Font(color=colors.BLACK, bold=True)
ws['A'+str(koef-1)].alignment = Alignment(wrap_text = True, horizontal = 'center')
for i in range(len(a['grades'])):
#классы
ws[str(unichr(ord('A')+i+2))+str(koef)] = grades[a['grades'][i]]['grade_number'] + grades[a['grades'][i]]['grade_name']
t = ws[str(unichr(ord('A')+i+2))+str(koef)]
t.font = Font(color=colors.BLACK, bold=True)
ws.column_dimensions[str(unichr(ord('A')+i+2))].width=defwidth
#t.fill = PatternFill(bgColor=colors.RED, fill_type = "solid")
ws.column_dimensions['A'].width = 9
koef+=1
for i in range(len(a['table']['table'])):
for j in range(len(a['table']['table'][i])):
fields1 = list(fielddic)
fields2 = list(fielddic.values())
# print(fields1)
ws.append(fields1)
ws.append(fields2)
# 导出表内容
for rows in obj.objects.all().values_list(
): # 未排除自动增长的字段内容导出，结构没有，内容有（小BUG）
ws.append(list(rows))
ws.row_dimensions.group(1, 1, hidden=True)
i = i + 1
ws = wb.active
ws.title = 'Cover'
ws['A4'] = '填报单位：'
ws['A4'].font = Font(name='Arial', size=14, color=colors.RED, italic=True)
ws['A5'] = '填报期间:'
ws['A6'] = '币种：'
ws['A7'] = '版本号'
application_window = tk.Tk()
application_window.withdraw() # 隐藏消息框
# Build a list of tuples for each file type the file dialog should display
my_filetypes = [('xlsx files', '.xlsx')]
# localpath = filedialog.asksaveasfile()
# localpath = filedialog.askdirectory()
localpath = filedialog.asksaveasfilename(parent=application_window,
initialdir=os.getcwd(),
title="Please select a location:",
filetypes=my_filetypes)
# ch19_25.py
import openpyxl
from openpyxl.styles import Font
wb = openpyxl.Workbook() # 建立空白的活頁簿
ws = wb.get_active_sheet() # 獲得目前工作表
fontTitle1 = Font(name='微軟正黑體', size=24)
ws['A1'].font = fontTitle1
ws['A1'] = '明志科技大學'
fontTitle2 = Font(name='Old English Text MT', size=24, bold=True)
ws['A2'].font = fontTitle2
ws['A2'] = 'Ming-Chi Institute of Technology'
fontTitle3 = Font(size=24, bold=True, italic=True)
ws['A3'].font = fontTitle3
ws['A3'] = 'Ming-Chi Institute of Technology'
wb.save('out19_25.xlsx') # 將活頁簿儲存
def export_analysis_xls(title,
event_pk=None,
information_pks=None,
request_data=None):
# Create a spreadsheet and get active workbook
ew = ExcelWriter()
ws = ew.get_active()
ws.title = "Split Entries"
wsg = ew.wb.create_sheet("Grouped Entries")
# Create title row
titles = [
"Date of Lead Publication", "Imported By", "Date Imported", "URL",
"Lead Title", "Source", "Excerpt"
]
event = entry_models.Event.objects.get(pk=event_pk)
elements = json.loads(event.entry_template.elements)
sTypes = [
'date-input', 'scale', 'number-input', 'multiselect', 'organigram'
]
element_ids = []
geo_elements = []
for element in elements:
eType = element['type']
if eType in sTypes:
titles.append(element['label'])
elif eType == 'matrix1d':
titles.append([element['title'], 'Dimension', 'Sub-Dimension'])
elif eType == 'matrix2d':
titles.append([
element['title'], 'Dimension', 'Sub-Dimension', 'Sector',
'Subsector'
])
elif eType == 'number2d':
for row in element['rows']:
for column in element['columns']:
titles.append('{} - {}'.format(row['title'],
column['title']))
for row in element['rows']:
titles.append('{} matches'.format(row['title']))
elif eType == 'geolocations':
geo_elements.append(element['id'])
else:
continue
element_ids.append([element['id'], eType])
if event_pk:
countries = entry_models.Event.objects.get(pk=event_pk).countries.\
all().distinct()
else:
countries = entry_models.Country.objects.all().distinct()
for country in countries:
admin_levels = country.adminlevel_set.all()
for admin_level in admin_levels:
titles.append(admin_level.name)
titles.append('{} P-Code'.format(admin_level.name))
index = 0
for t in titles:
if isinstance(t, list):
for wswsg in [ws, wsg]:
wswsg.cell(row=1, column=index + 1).value = t[0]
wswsg.cell(row=1, column=index + 1).font = Font(bold=True)
wswsg.merge_cells(start_row=1,
end_row=1,
start_column=index + 1,
end_column=index + len(t) - 1)
for ele in t[1:]:
for wswsg in [ws, wsg]:
wswsg.cell(row=2, column=index + 1).value = ele
wswsg.cell(row=2, column=index + 1).font = Font(bold=True)
index = index + 1
else:
for wswsg in [ws, wsg]:
wswsg.cell(row=1, column=index + 1).value = t
wswsg.cell(row=1, column=index + 1).font = Font(bold=True)
index = index + 1
ew.auto_fit_cells_in_row(1, ws)
ew.auto_fit_cells_in_row(1, wsg)
# Add each information in each entry belonging to this event
informations = entry_models.EntryInformation.objects.filter(
~Q(entry__template=None), entry__lead__event__pk=event_pk).distinct()
if information_pks:
informations = informations.filter(pk__in=information_pks)
informations = analysis_filter(informations, request_data, elements)
grouped_rows = []
for i, info in enumerate(informations):
try:
rows = RowCollection(1)
lead_url = info.entry.lead.url
if Attachment.objects.filter(lead=info.entry.lead).count() > 0:
lead_url = info.entry.lead.attachment.upload.url
rows.add_values([
format_date(info.entry.lead.published_at),
info.entry.created_by,
format_date(info.entry.created_at.date()), lead_url,
info.entry.lead.name, info.entry.lead.source_name,
xstr(info.excerpt)
])
infoE = json.loads(info.elements)
for element_id, element_type in element_ids:
element = list_filter(infoE, 'id', element_id)
get_analysis_data(elements, element_id, element, element_type,
rows)
for country in countries:
admin_levels = country.adminlevel_set.all()
for admin_level in admin_levels:
selections = []
for map_selections in [
geoE for geoE in infoE
if geoE.get('id') in geo_elements
]:
for map_selection in map_selections.get('value', []):
map_selection_list = map_selection.split(':')
if len(map_selection_list) == 3:
map_selection_list.append('')
if len(map_selection_list) == 4:
m_iso3, m_admin, m_name, pcode = \
map_selection_list
if admin_level.level == int(m_admin):
selections.append([m_name, pcode])
if len(selections) == 0:
selections = [['', '']]
rows.permute_and_add_list(selections)
ew.append(rows.rows, ws)
grouped_rows.append(rows.group_rows)
except:
pass
ew.append(grouped_rows, wsg)
# ew.save_to('/tmp/text.xls') # REMOVE THIS
return ew.get_http_response(title)
def excel_helper(sheet, table_id, table, option):
"""
Create excel sheet.
:param option: 'value' or 'percent'
"""
# Write table id and title on the first row
sheet['A1'] = table_id
sheet['B1'] = table['title']
sheet['A1'].font = Font(bold=True)
sheet['B1'].font = Font(bold=True)
header = []
max_indent = 0
# get column headers
for column_id, column_info in table['columns'].items():
column_name_utf8 = column_info['name'].encode('utf-8')
indent = column_info['indent']
header.append((column_name_utf8, indent))
if indent > max_indent:
max_indent = indent
# Populate first column with headers
for i, col_tuple in enumerate(header):
header = col_tuple[0]
current_row = i + 4 # 1-based index, account for geographic headers
current_cell = sheet.cell(row=current_row, column=1)
current_cell.value = header
indent = col_tuple[1]
if indent is None:
logger.warn("Null indent for {} {}".format(table_id, header))
indent = 0
current_cell.alignment = Alignment(indent=indent, wrap_text=True)
# Resize column width
sheet.column_dimensions['A'].width = 50
# this SQL echoed in OGR export but no geom so copying instead of factoring out
# plus different binding when using SQLAlchemy
result = session(sql_url).execute(
"""SELECT full_geoid,display_name
FROM tiger2018.census_name_lookup
WHERE full_geoid IN :geoids
ORDER BY full_geoid""",
{'geoids': tuple(valid_geo_ids)}
)
geo_headers = []
any_zero_denominators = False
has_denominator_column = False
for i, (geoid, name) in enumerate(result):
geo_headers.append(name)
col_values = []
col_errors = []
for table_id, table in table_metadata.items():
table_estimates = data.get(geoid, {}).get(table_id, {}).get('estimate')
table_errors = data.get(geoid, {}).get(table_id, {}).get('error')
if option == 'value':
for column_id, column_info in table['columns'].items():
col_values.append(table_estimates.get(column_id, ''))
col_errors.append(table_errors.get(column_id, ''))
elif option == 'percent':
denominator_column_id = table.get('denominator_column_id')
if denominator_column_id:
has_denominator_column = True
base_estimate = data[geoid][table_id]['estimate'][denominator_column_id]
for column_id, column_info in table['columns'].items():
if base_estimate and column_id in table_estimates and column_id in table_errors:
col_values.append(table_estimates[column_id] / base_estimate)
col_errors.append(table_errors[column_id] / base_estimate)
else:
any_zero_denominators = True
col_values.append('*')
col_errors.append('')
else:
col_values.append('*')
col_errors.append('')
for j, value in enumerate(col_values):
col_num = (i + 1) * 2
row_num = j + 4
sheet.cell(row=row_num, column=col_num).value = value
sheet.cell(row=row_num, column=col_num + 1).value = col_errors[j]
if option == 'percent':
sheet.cell(row=row_num, column=col_num).number_format = '0.00%'
sheet.cell(row=row_num, column=col_num + 1).number_format = '0.00%'
if option == 'percent' and (any_zero_denominators or not has_denominator_column):
annotation_cell = sheet.cell(row=(row_num + 1), column=1)
annotation_cell.font = Font(italic=True)
if any_zero_denominators:
annotation_cell.value = "* Base value of zero; no percentage available"
elif not has_denominator_column:
annotation_cell.value = "* Percentage values not appropriate for this table"
else:
annotation_cell.value = "* Unexpected error. Please contact Census Reporter at https://censusreporter.uservoice.com/ and let us know the page from where you downloaded this data."
# Write geo headers
for i in range(len(geo_headers)):
current_col = (i + 1) * 2
current_cell = sheet.cell(row=2, column=current_col)
current_cell.value = geo_headers[i]
current_cell.alignment = Alignment(horizontal='center')
sheet.merge_cells(start_row=2, end_row=2, start_column=current_col, end_column=current_col + 1)
sheet.cell(row=3, column=current_col).value = "Value"
sheet.cell(row=3, column=current_col + 1).value = "Error"
def export_and_save(event_pk, filename):
# Create a spreadsheet and get active workbook
ew = ExcelWriter()
ws = ew.get_active()
ws.title = "Split Entries"
wsg = ew.wb.create_sheet("Grouped Entries")
# Create title row
titles = [
"Country",
"Date of Lead Publication",
"Date of Information",
"Created By",
"Date Imported",
"Lead Title",
"Source",
"Excerpt",
"Reliability",
"Severity",
"Demographic Groups",
"Specific Needs Groups",
"Affected Groups",
"Pillar",
"Subpillar",
"Sector",
"Subsector",
]
countries = entry_models.Event.objects.get(pk=event_pk).countries\
.all().distinct()
for country in countries:
admin_levels = country.adminlevel_set.all()
for i, admin_level in enumerate(admin_levels):
titles.append('Admin {}'.format(i))
for i, t in enumerate(titles):
ws.cell(row=1, column=i + 1).value = t
ws.cell(row=1, column=i + 1).font = Font(bold=True)
wsg.cell(row=1, column=i + 1).value = t
wsg.cell(row=1, column=i + 1).font = Font(bold=True)
ew.auto_fit_cells_in_row(1, ws)
ew.auto_fit_cells_in_row(1, wsg)
# Add each information in each entry belonging to this event
informations = entry_models.EntryInformation.objects.filter(
entry__lead__event__pk=event_pk, entry__template=None).distinct()
grouped_rows = []
for i, info in enumerate(informations):
try:
rows = RowCollection(1)
rows.permute_and_add(info.entry.lead.event.countries.all())
rows.add_values([
format_date(info.entry.lead.published_at),
format_date(info.date), info.entry.created_by,
format_date(info.entry.created_at.date()),
info.entry.lead.name, info.entry.lead.source_name,
xstr(info.excerpt), info.reliability.name, info.severity.name
])
# Column Name `Demographic Groups` Renamed to
# `Vulnerable Group` as specified in Issue #280
rows.permute_and_add(info.vulnerable_groups.all())
rows.permute_and_add(info.specific_needs_groups.all())
rows.permute_and_add(info.affected_groups.all())
attributes = []
if info.informationattribute_set.count() > 0:
for attr in info.informationattribute_set.all():
attr_data = [
attr.subpillar.pillar.name, attr.subpillar.name
]
if attr.sector:
attr_data.append(attr.sector.name)
if attr.subsectors.count() > 0:
for ss in attr.subsectors.all():
attributes.append(attr_data + [ss.name])
else:
attributes.append(attr_data + [''])
else:
attributes.append(attr_data + ['', ''])
else:
attributes.append(['', '', '', ''])
rows.permute_and_add_list(attributes)
for country in countries:
admin_levels = country.adminlevel_set.all()
for admin_level in admin_levels:
selections = []
for map_selection in info.map_selections.all():
if admin_level == map_selection.admin_level:
selections.append(map_selection.name)
rows.permute_and_add(selections)
ew.append(rows.rows, ws)
grouped_rows.append(rows.group_rows)
except:
pass
ew.append(grouped_rows, wsg)
ew.save_to(filename)
wb = Workbook()
ws = wb.active
# for troubleshooting
from collections import Counter
if sys.version[0] == '2':
reload(sys)
sys.setdefaultencoding("utf-8")
# sys.path.insert(0, "/var/www/cgi-bin/bank_recon.py")
os.chdir(".") # bound path to the current directory
# satisfy openpyxl requirements for highlighting cells
highlight = NamedStyle(name="highlight")
highlight.font = Font(bold=True, size=12)
bd = Side(style='thick', color="000000")
highlight.border = Border(left=bd, top=bd, right=bd, bottom=bd)
highlight.fill = PatternFill(fill_type='lightUp',
start_color='fff000',
end_color='6efdfd')
# satisfy openpyxl requirements for highlighting cells2
highlight2 = NamedStyle(name="highlight2")
highlight2.font = Font(bold=True, size=12)
bd = Side(style='thick', color="000000")
highlight2.border = Border(left=bd, top=bd, right=bd, bottom=bd)
highlight2.fill = PatternFill(fill_type='lightUp',
start_color='fff000',
end_color='fff000')
def generate_comparison_data_xlsx(op_name, mi100_trace_events, v100_trace_events, scope_to_config_map, xlsx_file):
mi100_data = extract_op_data(mi100_trace_events, op_name)
v100_data = extract_op_data(v100_trace_events, op_name)
mi100_scopes = sorted(mi100_data.keys())
v100_scopes = sorted(v100_data.keys())
if mi100_scopes != v100_scopes:
print("Data collection error : scopes do not match")
workbook = Workbook()
sheet = workbook.active
dark_green_font = Font(bold=True, color='008800')
light_green_fill = PatternFill('solid', fgColor='DDFFDD')
dark_red_font = Font(bold=True, color='880000')
light_red_fill = PatternFill('solid', fgColor='FFDDDD')
right_align = Alignment(horizontal="right")
header = ["Model Scope and Config",
"MI100(us)", "V100(us)", "V100/MI100",
"MI100 Kernel Name", "V100 Kernel Name"]
sheet.append(header)
for i in range(len(header)):
sheet.cell(row=sheet._current_row, column=i+1).font = Font(bold=True)
sheet.append([])
for scope in mi100_scopes:
config = scope_to_config_map[scope]
mi100_kernels = mi100_data[scope]
v100_kernels = v100_data[scope]
num_mi100_kernels = len(mi100_kernels)
num_v100_kernels = len(v100_kernels)
num_kernels = max(2, num_mi100_kernels, num_v100_kernels)
mi100_duration = 0
v100_duration = 0
for i in range(num_kernels):
mi100_kernel_name, mi100_kernel_duration = mi100_kernels[i] if i < num_mi100_kernels else (" ","")
v100_kernel_name, v100_kernel_duration = v100_kernels[i] if i < num_v100_kernels else (" ","")
line = [""]
if i == 0: line = [scope]
if i == 1: line = [config]
line.append(mi100_kernel_duration)
line.append(v100_kernel_duration)
line.append("")
line.append(mi100_kernel_name)
line.append(v100_kernel_name)
sheet.append(line)
mi100_duration += mi100_kernel_duration if mi100_kernel_duration else 0
v100_duration += v100_kernel_duration if v100_kernel_duration else 0
v100_mi100_ratio = v100_duration / mi100_duration
line = ["layer total"]
line.append(mi100_duration)
line.append(v100_duration)
line.append(v100_mi100_ratio)
sheet.append(line)
sheet.cell(row=sheet._current_row, column=1).alignment = right_align
sheet.cell(row=sheet._current_row, column=4).font = dark_red_font if v100_mi100_ratio < 1.0 else dark_green_font
sheet.cell(row=sheet._current_row, column=4).fill = light_red_fill if v100_mi100_ratio < 1.0 else light_green_fill
sheet.append([])
sheet.append([])
line = []
line.append("Total Kernel Time (us)")
line.append("=SUM(B2:B{})/2".format(sheet._current_row - 1))
line.append("=SUM(C2:C{})/2".format(sheet._current_row - 1))
sheet.append(line)
sheet.column_dimensions['A'].width = 90
sheet.column_dimensions['B'].width = 15
sheet.column_dimensions['C'].width = 15
sheet.column_dimensions['D'].width = 15
sheet.column_dimensions['E'].width = 50
sheet.column_dimensions['F'].width = 50
workbook.save(filename=xlsx_file)
def format_ft1(lista):
ft1 = Font(name='Calibri', size=9, italic=True)
for komorka in lista:
komorka.font = ft1
for j in range(1, 6):
sheet.cell(row=i, column=j).border = thin_border
wb.save(Name[0])
else:
print 'New Sheet Created ' + OS
SheetIndex = len(sheets)
wb.create_sheet(index=SheetIndex - 1, title=OS)
sheet = wb.get_sheet_by_name(OS)
ListFormat = [
'KB No.', 'Description', 'WDM Package Name', 'Affected software',
'Dependency'
]
sheet['A1'] = 'Security Patches JAN 2016-' + OS + ''
#------------------------Fonts and Styling------------------------------------------#
font1 = Font(bold=True)
BackColor = PatternFill(patternType='solid', fgColor=Color('FFFF00'))
BackColor2 = PatternFill(patternType='solid', fgColor=Color('528ED5'))
align = Alignment(horizontal='center')
sheet.merge_cells('A1:E1')
for i in range(1, 6):
sheet.cell(row=1, column=i).style = Style(alignment=align,
fill=BackColor2,
font=font1)
sheet.cell(row=2, column=i).style = Style(fill=BackColor, font=font1)
sheet.cell(row=2, column=i).value = ListFormat[i - 1]
#-----------------------------Adding Data to new sheet-------------------------------#
TotalKB = len(KBNos)
for j in range(3, TotalKB + 3):
sheet.cell(row=j, column=1).value = KBNos[j - 3]
def test_write_dxf(self):
redFill = Fill()
redFill.start_color.index = 'FFEE1111'
redFill.end_color.index = 'FFEE1111'
redFill.fill_type = Fill.FILL_SOLID
whiteFont = Font()
whiteFont.color.index = "FFFFFFFF"
whiteFont.bold = True
whiteFont.italic = True
whiteFont.underline = 'single'
whiteFont.strikethrough = True
blueBorder = Borders()
blueBorder.left.border_style = 'medium'
blueBorder.left.color = Color(Color.BLUE)
blueBorder.right.border_style = 'medium'
blueBorder.right.color = Color(Color.BLUE)
blueBorder.top.border_style = 'medium'
blueBorder.top.color = Color(Color.BLUE)
blueBorder.bottom.border_style = 'medium'
blueBorder.bottom.color = Color(Color.BLUE)
cf = ConditionalFormatting()
cf.add('A1:A2', FormulaRule(formula="[A1=1]", font=whiteFont, border=blueBorder, fill=redFill))
cf.setDxfStyles(self.workbook)
assert len(self.workbook.style_properties['dxf_list']) == 1
assert 'font' in self.workbook.style_properties['dxf_list'][0]
assert 'border' in self.workbook.style_properties['dxf_list'][0]
assert 'fill' in self.workbook.style_properties['dxf_list'][0]
w = StyleWriter(self.workbook)
w._write_dxfs()
xml = get_xml(w._root)
diff = compare_xml(xml, """
<styleSheet xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main">
<dxfs count="1">
<dxf>
<font>
<color rgb="FFFFFFFF" />
<b val="1" />
<i val="1" />
<u val="single" />
<strike />
</font>
<fill>
<patternFill patternType="solid">
<fgColor rgb="FFEE1111" />
<bgColor rgb="FFEE1111" />
</patternFill>
</fill>
<border>
<left style="medium">
<color rgb="FF0000FF"></color>
</left>
<right style="medium">
<color rgb="FF0000FF"></color>
</right>
<top style="medium">
<color rgb="FF0000FF"></color>
</top>
<bottom style="medium">
<color rgb="FF0000FF"></color>
</bottom>
</border>
</dxf>
</dxfs>
</styleSheet>
""")
assert diff is None, diff
def exportTable(matrix, fname, ext, title=None):
"""Save data to a file
Inputs
matrix: array with data to save
fname: name of file to save
ext: name of format to save
csv | ods | xls | xlsx
title: column title array, optional
"""
sheetTitle = QApplication.translate("pychemqt", "Table")
if fname.split(".")[-1] != ext:
fname += ".%s" % ext
# Format title
header = []
if title:
for ttl in title:
line = str(ttl).split(os.linesep)
if line[-1] != "[-]":
line[-1] = "["+line[-1]+"]"
header.append(" ".join(line))
if ext == "csv":
import csv
with open(fname, 'w') as csvfile:
writer = csv.DictWriter(csvfile, fieldnames=header)
writer.writeheader()
for row in matrix:
kw = {}
for ttl, value in zip(header, row):
kw[ttl] = value
writer.writerow(kw)
elif ext == "ods":
import ezodf
spreadsheet = ezodf.newdoc("ods", fname)
sheets = spreadsheet.sheets
sheet = ezodf.Table(sheetTitle)
sheets += sheet
sheet.reset(size=(len(matrix)+1, len(matrix[0])))
# Add Data
if title:
for i, ttl in enumerate(header):
sheet["%s%i" % (spreadsheetColumn(i), 1)].set_value(ttl)
for j, row in enumerate(matrix):
for i, data in enumerate(row):
sheet["%s%i" % (spreadsheetColumn(i), j+2)].set_value(data)
spreadsheet.save()
elif ext == "xls":
import xlwt
spreadsheet = xlwt.Workbook()
sheet = spreadsheet.add_sheet(sheetTitle)
font = xlwt.Font()
font.bold = True
style = xlwt.XFStyle()
style.font = font
# Add Data
if title:
for i, ttl in enumerate(header):
sheet.write(0, i, ttl, style)
for j, row in enumerate(matrix):
for i, data in enumerate(row):
sheet.write(j+1, i, data)
spreadsheet.save(fname)
elif ext == "xlsx":
import openpyxl
from openpyxl.styles import Font
spreadsheet = openpyxl.Workbook()
sheet = spreadsheet.active
sheet.title = sheetTitle
font1 = Font()
font1.size = 9
font1.bold = True
font2 = Font()
font2.size = 9
# Add Data
if title:
for i, ttl in enumerate(header):
sheet["%s%i" % (spreadsheetColumn(i), 1)] = ttl
sheet["%s%i" % (spreadsheetColumn(i), 1)].style.font = font1
for j, row in enumerate(matrix):
for i, data in enumerate(row):
sheet["%s%i" % (spreadsheetColumn(i), j+2)] = data
sheet["%s%i" % (spreadsheetColumn(i), j+2)].style.font = font2
spreadsheet.save(filename=fname)
else:
raise ValueError(QApplication.translate(
"pychemqt", "Unsopported format") + " " + ext)
#Create new sheet in Excel with template name if does not already exist
wb.create_sheet(list_template_index)
# Now that we have an Excel workbook with a sheet per template, we need to populate it. Try putting in just headers first
# Put column_headers into Excel and style. Only really want to do this once though... and probably have as separate function
for column_count, header in enumerate(header_list, 1):
#print "\nThe list index here is: %s\n" % list_template_index
sheet_selected = wb[list_template_index]
cell_pos = sheet_selected.cell(row=1,
column=column_count)
cell_pos.value = header
# Ensures data is centered within cell
cell_pos.alignment = Alignment(horizontal="center",
wrapText=True)
cell_pos.font = Font(bold=True)
#Now that headers are filled out in each template...need to add in data
for row_index, row in enumerate(table.rows):
single_row = [
table.cell(row_index, column).text_frame.text
for column in range(0, number_of_columns)
]
#print "\nRow %s in table is: %s\n" % (row_index, single_row)
#print "\nThe length of a single row is: %s\n" % len(single_row)
#Zip up row with header template only if same length
if row_index != 0 and len(single_row) == len(
header_list):
def export(self) -> str:
def cm_to_inch(cm: float) -> float:
return cm * 0.393701
def prepare(ws: openpyxl.worksheet.Worksheet) -> None:
ws.header_footer.setHeader(
'&L&"Calibri,Regular"&K000000&G&C&"Lao UI,Bold"&8Total Project Construction Pty. Ltd.&"Lao UI,Regular"&K000000_x000D_ACN 117 578 560 ABN 84 117 578 560_x000D_PO Box 313 HALL ACT_x000D_P: 02-6230 2455 F:02-6230 2488_x000D_E: [email protected]'
)
ws.header_footer.setFooter(
f'&L&"Arial,Italic"&9&K000000App. A - Contract Variations&R&"Arial,Italic"&9&K000000{self.name}'
)
ws.page_margins.top = cm_to_inch(3.4)
ws.page_margins.bottom = cm_to_inch(2)
ws.page_margins.left = cm_to_inch(1.2)
ws.page_margins.right = cm_to_inch(1.1)
fill = PatternFill(patternType='solid', fgColor=Color('D8E4BC'))
wb: openpyxl.Workbook = openpyxl.Workbook()
ws: openpyxl.worksheet.Worksheet = wb.active
ws.title = 'Claim - TOTAL'
prepare(ws)
if self.client is None:
client_name = ''
client_first_line_address = ''
client_second_line_address = ''
else:
client_name = self.client.name
client_first_line_address = self.client.first_line_address
client_second_line_address = self.client.second_line_address
if self.superintendent is None:
superintendent_name = ''
superintendent_first_line_address = ''
superintendent_second_line_address = ''
else:
superintendent_name = self.superintendent.name
superintendent_first_line_address = self.superintendent.first_line_address
superintendent_second_line_address = self.superintendent.second_line_address
ws['A1'].value = 'Client: '
ws['A1'].value += ' '.join([client_name, superintendent_name])
ws['A1'].font = Font(name='Lao UI', size=10, bold=True)
ws['A2'].value = ' '
ws['A2'].value += ' '.join(
[client_first_line_address, superintendent_first_line_address])
ws['A3'].value = ' '
ws['A3'].value += ' '.join(
[client_second_line_address, superintendent_second_line_address])
ws['C1'].value = f'Reference #: {arrow.now("Australia/Canberra").format("MM")}-{self.reference_number}'
ws['C1'].font = Font(name='Lao UI', size=10, bold=True)
ws['C3'].value = f'Date: {arrow.now("Australia/Canberra").format("DD/MM/YY")}'
ws['C3'].font = Font(name='Lao UI', size=10)
ws['A4'].value = 'PROGRESS CLAIM No.'
ws['A4'].font = Font(name='Lao UI', size=14)
ws['A4'].fill = fill
ws['B4'].value = f'Project No: {self.reference_number}'
ws['B4'].font = Font(name='Lao UI', size=10)
ws['C4'].value = f'{arrow.now("Australia/Canberra").format("MMMM")}'
ws['C4'].font = Font(name='Lao UI', size=10)
ws['B5'].value = 'Approval terms: '
ws['B5'].font = Font(name='Lao UI', size=10)
ws['A6'].value = f'Project: {self.name}'
ws['A6'].font = Font(name='Lao UI', size=11, bold=True)
ws['B6'].value = 'Contract'
ws['B6'].font = Font(name='Lao UI', size=10, bold=True)
ws['B6'].alignment = Alignment(horizontal='center')
ws['C6'].value = 'Completed'
ws['C6'].font = Font(name='Lao UI', size=10, bold=True)
ws['C6'].alignment = Alignment(horizontal='center')
ws['A7'].value = ''
ws['B7'].value = 'Value'
ws['B7'].font = Font(name='Lao UI', size=10, bold=True)
ws['B7'].alignment = Alignment(horizontal='center')
ws['C7'].value = 'To Date'
ws['C7'].font = Font(name='Lao UI', size=10, bold=True)
ws['C7'].alignment = Alignment(horizontal='center')
ws.merge_cells('D6:D7')
ws['D6'].value = '%'
ws['D6'].font = Font(name='Arial', size=12, bold=True)
ws['D6'].alignment = Alignment(horizontal='center', vertical='center')
for row in [6, 7]:
for column in 'ABCD':
cell = ws[f'{column}{row}']
cell.fill = fill
ws['A1'].border = Border(
top=Side(border_style='medium', color='FF000000'))
ws['B1'].border = Border(
top=Side(border_style='medium', color='FF000000'),
right=Side(border_style='medium', color='FF000000'),
)
ws['C1'].border = Border(
top=Side(border_style='medium', color='FF000000'))
ws['D1'].border = Border(
top=Side(border_style='medium', color='FF000000'))
ws['B2'].border = Border(
right=Side(border_style='medium', color='FF000000'))
ws['A3'].border = Border(
bottom=Side(border_style='medium', color='FF000000'))
ws['B3'].border = Border(
bottom=Side(border_style='medium', color='FF000000'),
right=Side(border_style='medium', color='FF000000'),
)
ws['A4'].border = Border(
top=Side(border_style='medium', color='FF000000'),
bottom=Side(border_style='medium', color='FF000000'),
)
ws['A6'].border = Border(
top=Side(border_style='medium', color='FF000000'),
bottom=Side(border_style='medium', color='FF000000'),
left=Side(border_style='medium', color='FF000000'),
right=Side(border_style='medium', color='FF000000'),
)
ws['B6'].border = Border(
top=Side(border_style='medium', color='FF000000'),
left=Side(border_style='medium', color='FF000000'),
right=Side(border_style='medium', color='FF000000'),
)
ws['C6'].border = Border(
top=Side(border_style='medium', color='FF000000'),
left=Side(border_style='medium', color='FF000000'),
right=Side(border_style='medium', color='FF000000'),
)
ws['D6'].border = Border(
top=Side(border_style='medium', color='FF000000'),
left=Side(border_style='medium', color='FF000000'),
right=Side(border_style='medium', color='FF000000'),
)
ws['A7'].border = Border(
top=Side(border_style='medium', color='FF000000'),
bottom=Side(border_style='medium', color='FF000000'),
left=Side(border_style='medium', color='FF000000'),
right=Side(border_style='medium', color='FF000000'),
)
ws['B7'].border = Border(
bottom=Side(border_style='medium', color='FF000000'),
left=Side(border_style='medium', color='FF000000'),
right=Side(border_style='medium', color='FF000000'),
)
ws['C7'].border = Border(
bottom=Side(border_style='medium', color='FF000000'),
left=Side(border_style='medium', color='FF000000'),
right=Side(border_style='medium', color='FF000000'),
)
ws['D7'].border = Border(
bottom=Side(border_style='medium', color='FF000000'),
left=Side(border_style='medium', color='FF000000'),
right=Side(border_style='medium', color='FF000000'),
)
row = 8
def _comp(p):
return p.id
self.progress_items.sort(key=_comp)
for progress_item in self.progress_items:
ws[f'A{row}'].value = progress_item.name
ws[f'B{row}'].value = progress_item.contract_value
ws[f'C{row}'].value = progress_item.completed_value
ws[f'D{row}'].value = f'= C{row}/B{row}'
# print(ws['B{}'.format(row)].number_format)
# print(ws['C{}'.format(row)].number_format)
row += 1
for irow in range(8, row):
for column in 'ABCD':
cell = ws[f'{column}{irow}']
cell.font = Font(name='Lao UI', size=9)
cell.border = Border(
left=Side(border_style='thin', color='FF000000'),
right=Side(border_style='thin', color='FF000000'),
)
if column == 'D':
cell.alignment = Alignment(vertical='center',
horizontal='center')
ws[f'A{row}'].value = 'TOTAL OF CONTRACT'
ws[f'B{row}'].value = f'=SUM(B{8}:B{row - 1})'
ws[f'C{row}'].value = f'=SUM(C{8}:C{row - 1})'
ws[f'D{row}'].value = f'=C{row}/B{row}'
for column in 'ABCD':
cell = ws[f'{column}{row}']
cell.font = Font(name='Lao UI', size=9, bold=True)
row += 1
ws[f'A{row}'].value = 'Variations - See Appendix A attached'
ws[f'B{row}'].value = r"='Appendix A'!D34"
ws[f'C{row}'].value = r"='Appendix A'!E34"
ws[f'D{row}'].value = f'=C{row}/B{row}'
for row in [row - 1, row]:
for column in 'ABCD':
cell = ws[f'{column}{row}']
cell.fill = fill
cell.border = Border(top=Side(border_style='thin',
color='FF000000'),
bottom=Side(border_style='thin',
color='FF000000'),
left=Side(border_style='thin',
color='FF000000'),
right=Side(border_style='thin',
color='FF000000'))
row += 1
ws[f'A{row}'].value = 'Totals Excluding GST'
ws[f'B{row}'].value = f'=B{row - 1} + B{row - 2}'
ws[f'C{row}'].value = f'=C{row - 1} + C{row - 2}'
ws[f'D{row}'].value = f'=C{row}/B{row}'
for column in 'BCD':
cell = ws[f'{column}{row}']
cell.fill = fill
cell.border = Border(top=Side(border_style='medium',
color='FF000000'),
bottom=Side(border_style='medium',
color='FF000000'),
left=Side(border_style='medium',
color='FF000000'),
right=Side(border_style='medium',
color='FF000000'))
row += 1
ws[f'A{row}'].value = 'Less paid to date'
ws[f'C{row}'].border = Border(left=Side(border_style='thin',
color='FF000000'),
right=Side(border_style='thin',
color='FF000000'))
row += 1
ws[f'A{row}'].value = 'Value of work completed this period'
ws[f'C{row}'].value = f'=C{row - 2} - C{row - 1}'
ws[f'C{row}'].border = Border(left=Side(border_style='thin',
color='FF000000'),
right=Side(border_style='thin',
color='FF000000'))
row += 1
ws[f'A{row}'].value = 'GST this period'
ws[f'C{row}'].value = f'=C{row - 1} * 10%'
ws[f'C{row}'].border = Border(left=Side(border_style='thin',
color='FF000000'),
right=Side(border_style='thin',
color='FF000000'))
for irow in range(row - 3, row + 1):
for column in 'ABCD':
cell = ws[f'{column}{irow}']
cell.font = Font(name='Lao UI', size=9)
row += 1
ws[f'A{row}'].value = 'TOTAL PAYABLE THIS CLAIM'
ws[f'A{row}'].font = Font(name='Lao UI', size=9, bold=True)
ws[f'C{row}'].value = f'=C{row - 2} + C{row - 1}'
ws[f'C{row}'].font = Font(name='Lao UI', size=9, bold=True)
ws[f'C{row}'].border = Border(top=Side(border_style='medium',
color='FF000000'),
bottom=Side(border_style='medium',
color='FF000000'),
left=Side(border_style='medium',
color='FF000000'),
right=Side(border_style='medium',
color='FF000000'))
for irow in range(8, row + 1):
ws[f'B{irow}'].number_format = r'_-"$"* #,##0.00_-;\\-"$"* #,##0.00_-;_-"$"* "-"??_-;_-@_-'
ws[f'C{irow}'].number_format = r'_-"$"* #,##0.00_-;\\-"$"* #,##0.00_-;_-"$"* "-"??_-;_-@_-'
ws[f'D{irow}'].number_format = r'0.00%'
ws.column_dimensions['A'].width = 40
ws.column_dimensions['B'].width = 15
ws.column_dimensions['C'].width = 15
ws.column_dimensions['D'].width = 8
ws.sheet_view.view = 'pageLayout'
ws: openpyxl.worksheet.Worksheet = wb.create_sheet()
ws.title = 'Appendix A'
prepare(ws)
ws.merge_cells('A1:D1')
ws['A1'].style.alignment.wrap_text = True
ws['A1'].value = f'{self.name}\nJOB #: {self.reference_number}'
ws['A1'].fill = fill
ws['A1'].font = Font(name='Lao UI', size=11, bold=True)
ws.merge_cells('A3:B3')
ws['A3'].value = "Appendix 'A' - Contract variations"
ws['A3'].font = Font(name='Lao UI', size=10, bold=True)
# ws['E3'].value = '=TODAY()'
ws['E3'].value = datetime.today()
ws['E3'].number_format = 'mm-dd-yy'
ws['E3'].font = Font(name='Lao UI', size=9)
ws['A5'].value = 'NO.'
ws['B5'].value = 'ITEM'
ws['C5'].value = 'PENDING'
ws['D5'].value = 'APPROVED'
ws['E5'].value = 'COMPLETED'
for cell in ['A5', 'B5', 'C5', 'D5', 'E5']:
ws[cell].font = Font(name='Lao UI', size=10, bold=True)
ws[cell].alignment = Alignment(horizontal='center')
ws[cell].fill = fill
ws[cell].border = Border(top=Side(border_style='medium',
color='FF000000'),
bottom=Side(border_style='medium',
color='FF000000'),
left=Side(border_style='medium',
color='FF000000'),
right=Side(border_style='medium',
color='FF000000'))
row = 6
self.variations.sort(key=lambda v: v.vid)
for variation in self.variations:
ws['A' + str(row)].value = row - 5
ws['B' + str(row)].value = variation.description
if variation.pending or variation.approved:
if variation.pending:
column = 'C'
else:
column = 'D'
ws[column + str(row)].value = variation.amount
elif variation.declined:
ws['B' + str(
row
)].value = f"{variation.description} (declined {variation.amount})"
ws['C' + str(row)].value = 0.0
if variation.completed:
ws['E' + str(row)].value = variation.amount
row += 1
while row < 34:
for column in ['A', 'B', 'C', 'D', 'E']:
cell = ws[column + str(row)]
cell.border = Border(left=Side(border_style='thin',
color='FF000000'),
right=Side(border_style='thin',
color='FF000000'))
row += 1
for index in range(6, row):
for column in ['A', 'B', 'C', 'D', 'E']:
cell = ws[f'{column}{index}']
cell.border = Border(left=Side(border_style='thin',
color='FF000000'),
right=Side(border_style='thin',
color='FF000000'))
if column == 'A':
cell.font = Font(name='Lao UI', size=10, bold=True)
cell.alignment = Alignment(vertical='center',
horizontal='center')
else:
cell.font = Font(name='Lao UI', size=9)
if column == 'B':
cell.alignment = Alignment(vertical='center',
horizontal='left')
else:
cell.alignment = Alignment(vertical='center')
cell.number_format = r'_-"$"* #,##0.00_-;\\-"$"* #,##0.00_-;_-"$"* "-"??_-;_-@_-'
ws.merge_cells(f'A{row}:B{row}')
ws[f'A{row}'].value = 'TOTALS'
ws[f'C{row}'].value = f'=SUM(C6:C{row - 1})'
ws[f'D{row}'].value = f'=SUM(D6:D{row - 1})'
ws[f'E{row}'].value = f'=SUM(E6:E{row - 1})'
for column in 'ABCDE':
cell = ws[f'{column}{row}']
cell.alignment = Alignment(vertical='center', horizontal='center')
cell.fill = fill
cell.border = Border(left=Side(border_style='medium',
color='FF000000'),
right=Side(border_style='medium',
color='FF000000'),
top=Side(border_style='medium',
color='FF000000'),
bottom=Side(border_style='medium',
color='FF000000'))
if column != 'A':
cell.number_format = r'_-"$"* #,##0.00_-;\\-"$"* #,##0.00_-;_-"$"* "-"??_-;_-@_-'
ws.row_dimensions[1].height = 30
for row in range(len(self.variations)):
ws.row_dimensions[6 + row].height = 30
ws.column_dimensions['A'].width = 5
ws.column_dimensions['B'].width = 40
ws.column_dimensions['C'].width = 13
ws.column_dimensions['D'].width = 13
ws.column_dimensions['E'].width = 13
ws.cell('A1').alignment = Alignment(wrapText=True)
ws.sheet_view.view = 'pageLayout'
for index, variation in enumerate(self.variations):
new_ws: openpyxl.worksheet.Worksheet = wb.create_sheet()
prepare(new_ws)
new_ws.title = f'V{index + 1}'
new_ws.merge_cells('A1:H1')
new_ws['A1'].value = 'CONTRACT VARIATION'
new_ws['A1'].fill = fill
new_ws['A1'].font = Font(name='Lao UI', size=16, bold=True)
new_ws['A1'].alignment = Alignment(vertical='center',
horizontal='center')
for column in 'ABCDEFGH':
new_ws[column + '1'].border = Border(
top=Side(border_style='medium', color='FF000000'),
bottom=Side(border_style='medium', color='FF000000'),
left=Side(border_style='medium', color='FF000000'),
right=Side(border_style='medium', color='FF000000'))
new_ws.merge_cells('B3:H3')
new_ws['B3'].value = f'PROJECT: {self.name}'
new_ws['B3'].fill = fill
new_ws['B3'].font = Font(name='Lao UI', size=12, bold=True)
for column in 'BCDEFGH':
new_ws[f'{column}3'].border = Border(
top=Side(border_style='thin', color='FF000000'),
bottom=Side(border_style='thin', color='FF000000'))
new_ws['B5'].value = 'VARIATION NO:'
new_ws['B5'].fill = fill
new_ws['C5'].fill = fill
new_ws['B5'].font = Font(name='Lao UI', size=12, bold=True)
new_ws.merge_cells('D5:E5')
new_ws['D5'].value = index + 1
new_ws['D5'].fill = fill
new_ws['D5'].font = Font(name='Lao UI', size=14, bold=True)
new_ws.merge_cells('G5:H5')
new_ws['G5'].value = f'TPC REF: {self.reference_number}'
new_ws['G5'].font = Font(name='Lao UI', size=14, bold=True)
new_ws['G5'].alignment = Alignment(vertical='center',
horizontal='left')
for column in 'BCDE':
cell = new_ws[column + str(5)]
cell.border = Border(top=Side(border_style='thin',
color='FF000000'),
bottom=Side(border_style='thin',
color='FF000000'))
for column in 'BCDEFGH':
cell = new_ws[column + str(6)]
cell.border = Border(
bottom=Side(border_style='medium', color='FF000000'))
row = 7
if len(variation.items) > 1:
new_ws.merge_cells(f'B{row}:G{row}')
new_ws[f'B{row}'].value = variation.description
new_ws[f'B{row}'].font = Font(name='Lao UI',
size=11,
bold=True)
new_ws[f'B{row}'].alignment = Alignment(vertical='center')
row += 1
for item in variation.items:
new_ws.merge_cells(f'B{row}:G{row}')
new_ws[f'B{row}'].value = item.description
new_ws[f'B{row}'].font = Font(name='Lao UI',
size=11,
bold=True)
new_ws[f'B{row}'].alignment = Alignment(vertical='center')
new_ws[f'H{row}'].value = item.amount
new_ws[f'H{row}'].font = Font(name='Lao UI',
size=11,
bold=True)
new_ws[f'H{row}'].alignment = Alignment(vertical='center')
new_ws[
f'H{row}'].number_format = r'_-"$"* #,##0.00_-;\\-"$"* #,##0.00_-;_-"$"* "-"??_-;_-@_-'
new_ws.row_dimensions[row].height = 40
row += 1
while row < 13:
new_ws[f'B{row}'].font = Font(name='Lao UI', size=11)
new_ws[f'H{row}'].font = Font(name='Lao UI', size=11)
row += 1
for column in 'BCDEFGH':
cell = new_ws[column + str(row - 1)]
cell.border = Border(
bottom=Side(border_style='thin', color='FF000000'))
new_ws['B' + str(row)].value = 'Value of work'
new_ws['H' + str(row)].value = f'=SUM(H7:H{row - 1})'
new_ws['B' + str(row)].font = Font(name='Lao UI', size=11)
new_ws['H' + str(row)].font = Font(name='Lao UI', size=11)
row += 1
new_ws['B' +
str(row)].value = f'Add OH/Profit {self.margin * 100}%'
new_ws['H' +
str(row)].value = f'=H{row - 1} * {self.margin * 100}%'
new_ws['B' + str(row)].font = Font(name='Lao UI', size=11)
new_ws['H' + str(row)].font = Font(name='Lao UI', size=11)
if self.admin_fee is not None and self.admin_fee != 0:
row += 1
new_ws['B' + str(row)].value = 'Fixed administration fee'
new_ws['H' + str(row)].value = self.admin_fee
new_ws['B' + str(row)].font = Font(name='Lao UI', size=11)
new_ws['H' + str(row)].font = Font(name='Lao UI', size=11)
for column in 'BCDEFGH':
new_ws[column + str(row)].border = Border(
bottom=Side(border_style='thin', color='FF000000'))
row += 1
new_ws['B' + str(row)].value = 'Subtotal'
if self.admin_fee is None:
new_ws['H' + str(row)] = f'=H{row - 2} + H{row - 1}'
else:
new_ws['H' +
str(row)] = f'=H{row - 3} + H{row - 2} + H{row - 1}'
new_ws['H' + str(row)].font = Font(name='Lao UI',
size=11,
bold=True)
row += 1
new_ws['B' + str(row)] = 'Add GST'
new_ws['H' + str(row)].value = f'=H{row - 1} * 0.1'
new_ws['H' + str(row)].font = Font(name='Lao UI',
size=11,
underline='singleAccounting')
for column in 'BCDEFGH':
new_ws[column + str(row)].border = Border(
bottom=Side(border_style='medium', color='FF000000'))
row += 1
new_ws.merge_cells(f'B{row}:C{row}')
for column in 'BCDEFGH':
new_ws[f'{column}{row}'].fill = fill
new_ws['B' + str(row)] = 'TOTAL'
new_ws['H' + str(row)].value = f'=H{row - 1} + H{row - 2}'
new_ws['H' + str(row)].font = Font(name='Lao UI',
size=11,
bold=True)
for idx in range(7, row + 1):
new_ws['H' + str(
idx
)].number_format = r'_-"$"* #,##0.00_-;\\-"$"* #,##0.00_-;_-"$"* "-"??_-;_-@_-'
row += 4
new_ws[f'B{row}'].value = 'Variation Prepared By:'
new_ws[f'G{row}'].value = 'Variation Prepared For:'
new_ws[f'B{row}'].font = Font(name='Lao UI', size=11)
new_ws[f'G{row}'].font = Font(name='Lao UI', size=11)
row += 5
new_ws[f'B{row}'].value = 'FOR'
new_ws[f'B{row}'].font = Font(name='Lao UI', size=11)
new_ws[f'B{row + 1}'].value = 'Total Project Construction Pty Ltd'
new_ws[f'B{row + 1}'].font = Font(name='Lao UI', size=11)
new_ws[f'G{row}'].value = 'FOR'
new_ws[f'G{row}'].font = Font(name='Lao UI', size=11)
new_ws[f'G{row + 1}'].value = variation.prepared_for
new_ws[f'G{row + 1}'].font = Font(name='Lao UI', size=11)
row += 3
new_ws[f'B{row}'].value = 'Date:'
# new_ws['C{}'.format(row)].value = '=TODAY()'
new_ws[f'C{row}'].value = datetime.today()
new_ws[f'C{row}'].number_format = 'mm-dd-yy'
new_ws[f'G{row}'].value = 'Date:'
new_ws[f'B{row}'].font = Font(name='Lao UI', size=11)
new_ws[f'C{row}'].font = Font(name='Lao UI', size=11)
new_ws[f'G{row}'].font = Font(name='Lao UI', size=11)
new_ws.row_dimensions[1].height = 60
new_ws.row_dimensions[3].height = 40
new_ws.column_dimensions['A'].width = 3
new_ws.column_dimensions['D'].width = 6
new_ws.column_dimensions['F'].width = 4
new_ws.column_dimensions['H'].width = 12
new_ws.sheet_view.view = 'pageLayout'
fn = self.name + '.xlsx'
wb.save('generated/' + fn)
return fn
def SetFormat(self,row,col,fmt):
pyws = self.pyws
font = None
color = None
align = None
fill = None
numFmt = None
border = None
c = pyws.cell(row=row,column=col)
#-------------------------------------------------------------------------
for i in fmt:
if (i == 'hAlign'):
if (not align): align = Alignment()
align.horizontal = alignType[fmt[i]]
elif (i == 'vAlign'):
if (not align): align = Alignment()
align.vertical = alignType[fmt[i]]
elif (i == 'tAlign'):
if (not align): align = Alignment()
align.text_rotation = fmt[i]
elif (i == 'wrap'):
if (not align): align = Alignment()
align.wrap_text = fmt[i]
elif (i == 'font'):
name = 'Calibri'
bold = False
size = 11
dict = fmt[i]
if ('emph' in dict):
if (dict['emph'] == 'B'):
bold = True
if ('size' in dict):
size = dict['size']
if (not font):
font = Font(name=name,size=size,bold=bold)
elif (i == 'border'):
dict = fmt[i]
color = None
style = None
if ('Color' in dict):
color = ColorTable[dict['Color']]
else:
color = ColorTable['Black']
if ('Style' in dict):
color = dict['Style']
if (c.border.top.style == None):
tSide = Side(color=color)
else:
tSide = c.border.top.copy()
if (c.border.bottom.style == None):
bSide = Side(color=color)
else:
bSide = c.border.bottom.copy()
if (c.border.left.style == None):
lSide = Side(color=color)
else:
lSide = c.border.left.copy()
if (c.border.right.style == None):
rSide = Side(color=color)
else:
rSide = c.border.right.copy()
if ((len(dict) ==1) and ('A' in dict)):
tSide.style = dict['A']
bSide.style = dict['A']
lSide.style = dict['A']
rSide.style = dict['A']
else:
for j in dict:
if (j == 'T'):
tSide.style = dict[j]
if (j == 'B'):
bSide.style = dict[j]
if (j == 'L'):
lSide.style = dict[j]
if (j == 'R'):
rSide.style = dict[j]
border = Border(left=lSide,right=rSide,top=tSide,bottom=bSide)
elif (i == 'fill'):
color = ColorTable[fmt[i]]
fill = PatternFill(start_color=color,end_color='FFFFFFFF',fill_type='solid')
elif (i == 'orient'):
pass
elif (i == 'numFmt'):
numFmt = fmt[i]
#-------------------------------------------------------------------------
if (font):
c.font = font.copy()
if (align):
c.alignment = align.copy()
if (border):
c.border = border.copy()
if (fill):
c.fill = fill.copy()
if (numFmt):
c.number_format = numFmt
def InsertCellValues(r,col,v,ws):
if(len(v)==5):
d=r[0]
e=r[1]
f=r[2]
ws[d+col[0]]=float(v[0])
ws[d+col[0]].font=Font(color='0000FF')
ws[d+col[0]].alignment=Alignment(horizontal='left')
ws[e+col[0]]=float(v[1])
ws[f+col[0]]=float(v[2])
ws[e+col[1]]=float(v[3])
ws[e+col[1]].font=Font(color='FF0000')
ws[f+col[1]]=float(v[4])
ws[f+col[1]].font=Font(color='FF0000')
totalTrips=float(v[1])+float(v[3])
totalAmount=float(v[2])+float(v[4])
elif(len(v)==4):
c=r[0]
d=r[1]
e=r[2]
f=r[3]
ws[c+col[0]]=float(v[0])
ws[d+col[0]]=float(v[1])
ws[e+col[0]]=float(v[2])
ws[f+col[0]]=float(v[3])
ws[c+col[0]].font=Font(color='0000FF')
ws[d+col[0]].font=Font(color='0000FF')
ws[e+col[0]].font=Font(color='0000FF')
ws[f+col[0]].font=Font(color='0000FF')
ws[d+col[0]].alignment=Alignment(horizontal='left')
totalTrips=float(v[2])
totalAmount=float(v[3])
elif(len(v)==7):
c=r[0]
d=r[1]
e=r[2]
f=r[3]
ws[c+col[0]]=v[0]
ws[c+col[0]].alignment=Alignment(horizontal='left')
ws[c+col[0]].font=Font(color='000000',bold=True,size=11)
ws[e+col[1]]=v[1]
ws[f+col[1]]=v[2]
ws[e+col[1]].alignment=Alignment(horizontal='center')
ws[e+col[1]].font=Font(color='000000',size=11)
ws[f+col[1]].alignment=Alignment(horizontal='center')
ws[f+col[1]].font=Font(color='000000',size=11)
ws[c+col[2]]=float(v[3])
ws[d+col[2]]=float(v[4])
ws[d+col[2]].font=Font(color='0000FF')
ws[d+col[2]].alignment=Alignment(horizontal='left')
ws[c+col[2]].font=Font(color='0000FF')
ws[e+col[2]]=float(v[5])
ws[f+col[2]]=float(v[6])
ws[e+col[2]].font=Font(color='0000FF')
ws[f+col[2]].font=Font(color='0000FF')
totalTrips=float(v[5])
totalAmount=float(v[6])
elif(len(v)==6):
c=r[0]
d=r[1]
e=r[2]
f=r[3]
ws[c+col[0]]=float(v[0])
ws[c+col[0]].font=Font(color='0000FF')
ws[d+col[0]]=float(v[1])
ws[d+col[0]].font=Font(color='0000FF')
ws[d+col[0]].alignment=Alignment(horizontal='left')
ws[e+col[0]]=float(v[2])
ws[e+col[0]].font=Font(color='0000FF')
ws[f+col[0]]=float(v[3])
ws[f+col[0]].font=Font(color='0000FF')
ws[e+col[1]]=float(v[4])
ws[e+col[1]].font=Font(color='FF0000')
ws[f+col[1]]=float(v[5])
ws[f+col[1]].font=Font(color='FF0000')
totalTrips=float(v[2])+float(v[4])
totalAmount=float(v[3])+float(v[5])
elif(len(v)==9):
c=r[0]
d=r[1]
e=r[2]
f=r[3]
ws[c+col[0]]=v[0]
ws[c+col[0]].alignment=Alignment(horizontal='left')
ws[c+col[0]].font=Font(color='000000',bold=True,size=11)
ws[e+col[1]]=v[1]
ws[f+col[1]]=v[2]
ws[e+col[1]].alignment=Alignment(horizontal='center')
ws[e+col[1]].font=Font(color='000000',size=11)
ws[f+col[1]].alignment=Alignment(horizontal='center')
ws[f+col[1]].font=Font(color='000000',size=11)
ws[c+col[2]]=float(v[3])
ws[c+col[2]].font=Font(color='0000FF')
ws[d+col[2]]=float(v[4])
ws[d+col[2]].font=Font(color='0000FF')
ws[d+col[2]].alignment=Alignment(horizontal='left')
ws[e+col[2]]=float(v[5])
ws[f+col[2]]=float(v[6])
ws[e+col[3]]=float(v[7])
ws[e+col[3]].font=Font(color='FF0000')
ws[f+col[3]]=float(v[8])
ws[f+col[3]].font=Font(color='FF0000')
totalTrips=float(v[5])+float(v[7])
totalAmount=float(v[6])+float(v[8])
return (totalTrips,totalAmount)
def exportTable(matrix, fname, format, title=None):
"""Save data to a file
Inputs
matrix: array with data to save
fname: name of file to save
format: name of format to save
csv | ods | xls | xlsx
title: column title array, optional
"""
sheetTitle = unicode(QApplication.translate("pychemqt", "Table"))
if fname.split(".")[-1] != format:
fname+=".%s" % format
# Format title
if title:
header = []
for ttl in title:
line = unicode(ttl).split(os.linesep)
if line[-1] != "[-]":
line[-1] = "["+line[-1]+"]"
header.append(" ".join(line))
c_newline=maketrans(os.linesep, " ")
if format == "csv":
import csv
with open(fname, "w") as archivo:
writer = csv.writer(archivo, delimiter='\t', quotechar='"', quoting=csv.QUOTE_NONE)
# Add Data
if title:
writer.writerow([ttl.translate(c_newline) for ttl in header])
c_float=maketrans(".", ",")
for row in matrix:
writer.writerow([str(data).translate(c_float) for data in row])
elif format == "ods":
import ezodf
spreadsheet = ezodf.newdoc("ods", fname)
sheets = spreadsheet.sheets
sheet=ezodf.Table(sheetTitle)
sheets+=sheet
sheet.reset(size=(len(matrix)+1, len(matrix[0])))
# Add Data
if title:
for i, ttl in enumerate(header):
sheet["%s%i"%(spreadsheetColumn(i), 1)].set_value(ttl)
for j, row in enumerate(matrix):
for i, data in enumerate(row):
sheet["%s%i"%(spreadsheetColumn(i), j+2)].set_value(data)
spreadsheet.save()
elif format == "xls":
import xlwt
spreadsheet = xlwt.Workbook()
sheet = spreadsheet.add_sheet(sheetTitle)
font = xlwt.Font()
font.bold = True
style = xlwt.XFStyle()
style.font = font
# Add Data
if title:
for i, ttl in enumerate(header):
sheet.write(0, i, ttl, style)
for j, row in enumerate(matrix):
for i, data in enumerate(row):
sheet.write(j+1, i, data)
spreadsheet.save(fname)
elif format == "xlsx":
import openpyxl
from openpyxl.styles import Style, Font
spreadsheet = openpyxl.Workbook()
sheet = spreadsheet.active
sheet.title = sheetTitle
font1 = Font()
font1.size = 9
font1.bold = True
font2 = Font()
font2.size = 9
# Add Data
if title:
for i, ttl in enumerate(header):
sheet["%s%i"%(spreadsheetColumn(i), 1)] = ttl
sheet["%s%i"%(spreadsheetColumn(i), 1)].style.font= font1
for j, row in enumerate(matrix):
for i, data in enumerate(row):
sheet["%s%i"%(spreadsheetColumn(i), j+2)] = data
sheet["%s%i"%(spreadsheetColumn(i), j+2)].style.font = font2
spreadsheet.save(filename=fname)
else:
raise ValueError(QApplication.translate(
"pychemqt", "Unsopported format") + " " + format)
if args["group"]:
grupos = get_group_ids(args["group"])
else:
grupos = get_group_ids()
for g in zapi.hostgroup.get(output="extend", groupids=grupos):
print("--> Grupo selecionado: " + g["name"])
# CRIANDO PLANILHA
wb = Workbook()
sheet = wb.active
sheet.title = "Eventos"
sheet["A1"] = "INICIO"
sheet["A1"].font = Font(sz=12, bold=True)
sheet["B1"] = "FIM"
sheet["B1"].font = Font(sz=12, bold=True)
sheet["C1"] = "HOST"
sheet["C1"].font = Font(sz=12, bold=True)
sheet["D1"] = "TRIGGER"
sheet["D1"].font = Font(sz=12, bold=True)
sheet["E1"] = "SEVERIDADE"
sheet["E1"].font = Font(sz=12, bold=True)
sheet["F1"] = "STATUS"
sheet["F1"].font = Font(sz=12, bold=True)
sheet["G1"] = "GRUPO"
sheet["G1"].font = Font(sz=12, bold=True)
sheet["H1"] = "APP"
sheet["H1"].font = Font(sz=12, bold=True)
sheet["I1"] = "ACK"
def parse_fonts(self):
"""Read in the fonts"""
fonts = self.root.findall('{%s}fonts/{%s}font' % (SHEET_MAIN_NS, SHEET_MAIN_NS))
for node in fonts:
yield Font.from_tree(node)
from openpyxl.styles import Font
from openpyxl.styles import Alignment
A1 = Font(
name="MS UI Gothic",
size=19.0,
extend=None,
underline=None,
vertAlign=None,
scheme=None,
charset=128,
family=3.0,
bold=False,
italic=False,
strike=None,
outline=None,
shadow=None,
condense=None,
)
A2 = Font(
name="MS PGothic",
size=10.0,
extend=None,
underline=None,
vertAlign=None,
scheme=None,
charset=128,
family=3.0,
bold=True,
italic=False,
