def _get_cell_data(self, name, value, req, context, writer):
if name == 'id':
url = self.env.abs_href.ticket(value)
value = '#%d' % value
width = len(value)
value = Formula('HYPERLINK("%s",%s)' % (url, get_literal(value)))
return value, 'id', width, 1
if isinstance(value, datetime):
return value, '[datetime]', None, None
if value and name in ('reporter', 'owner'):
value = Chrome(self.env).format_author(req, value)
return value, name, None, None
if name == 'cc':
value = Chrome(self.env).format_emails(context, value)
return value, name, None, None
if name == 'milestone':
url = self.env.abs_href.milestone(value)
width, line = writer.get_metrics(value)
value = Formula('HYPERLINK("%s",%s)' % (url, get_literal(value)))
return value, name, width, line
return value, name, None, None
def addTopStudents(self, wb, data):
sheet = wb.get_sheet(SHEET_TOP)
start_row = 6
# compute best TOTAL ITEMS
base = 50.0 # round to nearest 50
items = int(base * math.ceil(float(data[0]["total"]) / base))
self.setHeaders(sheet, "AIEX TOP TEN PERFORMERS")
self.setSubheaders(sheet)
for rcnt, row in enumerate(data, start_row):
formula = Formula("M%s/N%s" % (rcnt + 1, rcnt + 1))
srow = sheet.row(rcnt)
srow.write(0, rcnt - start_row + 1, STYLE_DEFAULT)
srow.write(1, row["name"], STYLE_DEFAULT)
srow.write(2, row["student_num"], STYLE_DEFAULT)
srow.write(3, row["bsa_code"], STYLE_DEFAULT)
srow.write(4, row["part1"], STYLE_DEFAULT)
srow.write(5, row["part2"], STYLE_DEFAULT)
srow.write(6, row["part3"], STYLE_DEFAULT)
srow.write(7, row["part4"], STYLE_DEFAULT)
srow.write(8, row["part5"], STYLE_DEFAULT)
srow.write(9, row["part6"], STYLE_DEFAULT)
srow.write(10, row["part7"], STYLE_DEFAULT)
srow.write(11, row["part8"], STYLE_DEFAULT)
srow.write(12, row["part9"], STYLE_DEFAULT)
srow.write(13, row["total"], STYLE_DEFAULT)
srow.write(14, items, STYLE_DEFAULT)
srow.write(15, formula, STYLE_DECIMAL)
srow.write(16, 1, STYLE_PERFECT)
srow.write(17, formula, STYLE_PERCENTAGE)
def main(logfiles,dir_path):
if len(logfiles) != 0:
book = Workbook('utf-8')
# crate the cover sheet
sheet0 = book.add_sheet('Cover', cell_overwrite_ok=True)
row0 = sheet0.row(0)
row0.write(0, "ID")
row0.write(1, "FILE")
row0.write(2, "SHEET")
tall_style = Style.easyxf('font:height 72;')
row0.set_style(tall_style)
sheet0.col(1).width = 256 * 50
for item in logfiles:
print "processed file:" + os.path.split(item)[1]
new_row = sheet0.row(logfiles.index(item) + 1)
new_row.write(0, logfiles.index(item) + 1)
click = '#sheet%s!A1' % str(logfiles.index(item))
text = os.path.split(item)[1]
style = Style.easyxf('font: underline single,color blue;')
new_row.write(1, Formula('HYPERLINK("%s";"%s")' % (click, text)), style)
new_row.write(2, 'sheet' + str(logfiles.index(item)))
# creat other sheet
out_putlist = get_formatted_in_a_file(item)
Write_To_excel(book, 'sheet' + str(logfiles.index(item)), out_putlist)
save_path = os.path.join(dir_path, 'Aresult.xls')
book.save(save_path)
else:
print "the dirtory doesn't contains any log file"
def write_excle(self, excel_table):
self.xls = Workbook(encoding='utf-8')
self.sheet = self.xls.add_sheet("Sheet")
self.excel_table = excel_table
for row in range(0, len(excel_table)):
self.sheet.row(row).height = self.row_height
for col in range(0, len(excel_table[row])):
cell = excel_table[row][col]
if cell.type == EXCEL_DATA_TYPE_STR:
self.sheet.write_merge(row, row + cell.merge_row, col,
col + cell.merge_col, cell.data,
cell.style)
print cell.data
if str_len(cell.data) > 11:
min_width = self.col_width * (str_len(cell.data) / 12 +
1)
self.sheet.col(col).width = (
self.sheet.col(col).width
if self.sheet.col(col).width > min_width else
min_width)
if cell.type == EXCEL_DATA_TYPE_NUM:
self.sheet.write_merge(row, row + cell.merge_row, col,
col + cell.merge_col, cell.data,
cell.style)
if cell.type == EXCEL_DATA_TYPE_FORMULA:
self.sheet.write_merge(row, row + cell.merge_row, col,
col + cell.merge_col,
Formula(cell.data), cell.style)
return self.xls
def addPlayer(self, sheet, player, index):
indexClmn_l = 0
style = easyxf('font: underline single')
# for key in player.keys() :
for key in DPDataFormat.keyListPlayer:
if isnumber(player[key]):
try:
if player[key].find(".") != -1:
value_l = float(player[key])
else:
value_l = player[key].replace("\n",
" ").replace(" ", " ")
sheet.write(index, indexClmn_l, value_l)
except:
value_l = player[key]
sheet.write(index, indexClmn_l, value_l)
else:
if key == "html":
link = 'HYPERLINK("%s";"%s")' % (player[key],
player['Name'])
value_l = Formula(link)
sheet.write(index, indexClmn_l, value_l, style)
else:
value_l = filter(onlyascii, player[key])
sheet.write(index, indexClmn_l, value_l)
indexClmn_l += 1
def main(repo, developers):
basic_header = ['URL', 'Number', 'Title']
workbook = Workbook()
sheets = []
for developer in developers:
sheet_name = "{0} Estimates".format(developer)
sheet = workbook.add_sheet(sheet_name)
sheets.append(SheetInfo(sheet_name, developer, sheet))
consensus_sheet_name = "Consensus"
consensus_sheet_info = SheetInfo(consensus_sheet_name, None,
workbook.add_sheet(consensus_sheet_name))
sheets.append(consensus_sheet_info)
for sheet_info in sheets:
sheet = sheet_info.sheet
for column_index, column_header in enumerate(basic_header):
sheet.write(0, column_index, column_header)
if sheet_info.developer:
sheet.write(0, len(basic_header),
"{0} Estimate".format(sheet_info.developer))
sheet.write(0,
len(basic_header) + 1,
"{0} Notes".format(sheet_info.developer))
else:
# If it's the consensus sheet, then add two columns for
# each developer.
column_index = len(basic_header)
for developer in developers:
sheet.write(0, column_index, "{0} Estimate".format(developer))
sheet.write(0, column_index + 1, "{0} Notes".format(developer))
column_index += 2
sheet.write(0, column_index, "Consensus")
for i, issue in enumerate(get_unestimated_open_issues(repo)):
row_index = i + 1
for sheet_info in sheets:
for column_index in range(len(basic_header)):
sheet_info.sheet.write(row_index, column_index,
issue[column_index])
# In the Consensus sheet add a reference to each of the
# developer sheets' estimate and notes columns.
for i, sheet_info in enumerate(s for s in sheets if s.developer):
fmt = "'{sheet}'!{column}{row}"
for original_column_index in (len(basic_header) + i
for i in range(2)):
formula_text = fmt.format(
sheet=sheet_info.name,
column=column_number_to_letters(original_column_index + 1),
row=row_index + 1)
consensus_sheet_info.sheet.write(row_index,
original_column_index + 2 * i,
Formula(formula_text))
workbook.save('estimates.xls')
def create_remesas_info_xls(remesa_emitida, destinatarios):
importes = remesa_emitida.politica.array_cuotas
ctrlsum, no_cobrado, nboftxs = 0, 0, 0 # ctrlsum: Cantidad total, nboftxs: Número total de remesas
ruta = MEDIA_CONTABILIDAD + str(destinatarios[0]['ge'].ronda.entidad.code) + '/'
if not os.path.exists(ruta):
os.makedirs(ruta)
fichero_xls = '%s.xls' % (remesa_emitida.grupo)
wb = xlwt.Workbook()
wr = wb.add_sheet('Remesas')
wa = wb.add_sheet('Avisos')
fila_excel_remesas = 0
fila_excel_avisos = 0
estilo = xlwt.XFStyle()
font = xlwt.Font()
font.bold = True
estilo.font = font
wr.write(fila_excel_remesas, 0, 'Destinatario cobro (dbtrnm)', style=estilo)
wr.write(fila_excel_remesas, 1, 'Concepto (rmtinf)', style=estilo)
wr.write(fila_excel_remesas, 2, 'Cuenta bancaria (dbtriban)', style=estilo)
wr.write(fila_excel_remesas, 3, 'Couta (instdamt)', style=estilo)
for destinatario in destinatarios:
importe = sum(importes[:destinatario['num']])
try:
dbtrnm = destinatario['oa'].gauser.get_full_name()[:69]
rmtinf = '%s - %s (%s) - %s' % (remesa_emitida.politica.concepto,
remesa_emitida.politica.get_tipo_cobro_display(),
datetime.today().strftime('%d-%m-%Y'),
destinatario['texto'])
dbtriban = destinatario['oa'].debtor_account
instdamt = sum(importes[:destinatario['num']])
fila_excel_remesas += 1
wr.write(fila_excel_remesas, 0, dbtrnm)
wr.write(fila_excel_remesas, 1, rmtinf)
wr.write(fila_excel_remesas, 2, dbtriban)
wr.write(fila_excel_remesas, 3, instdamt)
ctrlsum += importe
nboftxs += 1
except Exception as e:
aviso1 = 'Error al crear la remesa para %s' % (destinatario['ge'].gauser.get_full_name())
existe_oa = 'Sí' if destinatario['oa'] else 'No'
aviso2 = '%s tiene una orden de adeudo directa firmada' % (existe_oa)
fila_excel_avisos += 1
wa.write(fila_excel_avisos, 0, aviso1)
wa.write(fila_excel_avisos, 5, aviso2)
wa.write(fila_excel_avisos, 10, 'Importe: %s' % (importe))
wa.write(fila_excel_avisos, 15, str(e))
no_cobrado += importe
fila_excel_remesas += 1
wr.write(fila_excel_remesas, 3, Formula("SUM(D2:D%s)" % (fila_excel_remesas)), style=estilo)
wr.col(0).width = 10000
wr.col(1).width = 15000
wr.col(2).width = 8000
wr.col(3).width = 5000
wb.save(ruta + fichero_xls)
return {'ctrlsum': ctrlsum, 'nboftxs': nboftxs}
def create_excel(traceFile):
book = Workbook()
mySheet = book.add_sheet('salam')
mySheet.write(0,1 , "PID")
mySheet.write(0,2 , "Arrival Time")
mySheet.write(0,3 , "Address")
mySheet.write(0,4 , "Size")
mySheet.write(0,5 , "Type")
with open(traceFile, 'r') as inF:
lineIndex = 1
for line in inF:
tolenizedLine = [token for token in line.split()]
mySheet.write(lineIndex,1,tokenizedLine[0])
mySheet.write(lineIndex,2,tokenizedLine[1])
mySheet.write(lineIndex,3,tokenizedLine[3])
mySheet.write(lineIndex,4,tokenizedLine[4])
mySheet.write(lineIndex,5,tokenizedLine[5])
lineIndex+=1
book.save(traceFile + '.xls')
book.save(TemporaryFile())
xf = glob.glob("*.xls")[0]
finalBook = Workbook()
finalSheet = finalBook.add_sheet('FINAL')
finalSheet.write(0,1 , "PID")
finalSheet.write(0,2 , "Arrival Time")
finalSheet.write(0,3 , "Address")
finalSheet.write(0,4 , "Size")
finalSheet.write(0,5 , "Type")
finalSheet.write(0,6 , "Interval")
curBook = open_workbook(xf)
curSheet = curBook.sheet_by_index(0)
pids = curSheet.col_values(1,1)
arrivalTimes = curSheet.col_values(2,1)
addresses = curSheet.col_values(3,1)
sizes = curSheet.col_values(4,1)
types = sizes = curSheet.col_values(5,1)
for i in range(len(pids)):
curSheet.write(i+1,1,pids[i])
curSheet.write(i+1,2,arrivalTimes[i])
curSheet.write(i+1,3,addresses[i])
curSheet.write(i+1,4,sizes[i])
curSheet.write(i+1,5,types[i])
for i in range(len(opsVals)):
opsSheet.write( i+1 ,6, Formula('SUB(C'+str(i+2)+':C'+str(i+1)+')' ))
finalBook.save('FINAL_BLKTRACE_EXCEL.xls')
finalBook.save(TemporaryFile())
for xf in xlFiles:
os.remove(xf)
def write_sitdsc(sitlineno=0, str_row=None, str_col=0, cross_col_no=2, csvlineno=None, height=None, style_head=None, style=None):
ws_sit.row(str_row).height_mismatch = 1
ws_sit.row(str_row).height = height
cont_list = lines[csvlineno].split(',')
ws_sit.write_merge(str_row, str_row, str_col, str_col + cross_col_no, cont_list[0], style_head)
for (i, content) in enumerate(cont_list[1:]):
if content == 'Non':
cellname = Utils.rowcol_to_cell(sitlineno, i + cross_col_no + 1)
ws_sit.write(str_row, i + cross_col_no + 1,
Formula("VLOOKUP(%s,sit_chn_dsc!$A$1:$B$1000,2,FALSE)" % cellname), style)
else:
ws_sit.write(str_row, i + cross_col_no + 1, content, style)
def reload_report(workbook, ibm, nc_sp, pvs_sp, fm_sp, gl):
# IBMData sheet
sheet = workbook.get_sheet(0)
# Define cell styles
data_xf = easyxf('border: left thin, right thin, top thin, bottom thin;')
pad2 = copy(data_xf)
pad2.num_format_str = '00'
pad3 = copy(data_xf)
pad3.num_format_str = '000'
pad4 = copy(data_xf)
pad4.num_format_str = '0000'
# Download hyperlink:
sheet.write(1, 0, Formula('HYPERLINK("{}")'.format(settings.IBM_RELOAD_URI)))
# Insert data:
for row, data in enumerate(ibm, 3):
sheet.write(row, 0, int(data.costCentre), pad3)
sheet.write(row, 1, data.account, pad2)
sheet.write(row, 2, data.service, pad2)
sheet.write(row, 3, data.activity, pad3)
try:
sheet.write(row, 4, int(data.project), pad4)
except ValueError:
sheet.write(row, 4, data.project, pad4)
try:
sheet.write(row, 5, int(data.job), pad3)
except ValueError:
sheet.write(row, 5, data.job, pad3)
sheet.write(row, 6, data.budgetArea, data_xf)
sheet.write(row, 7, data.projectSponsor, data_xf)
sheet.write(row, 8, data.regionalSpecificInfo, data_xf)
sheet.write(row, 9, data.servicePriorityID, data_xf)
sheet.write(row, 10, data.annualWPInfo, data_xf)
# Make some columns wider
sheet.col(6).width = 5000
sheet.col(7).width = 5000
sheet.col(8).width = 5000
sheet.col(9).width = 5000
sheet.col(10).width = 30000
# Sheet 2 - Service priority checkboxes.
sheet = workbook.get_sheet(1)
write_service_priorities(sheet, nc_sp, pvs_sp, fm_sp)
# Sheet 3 - GL Codes sheet
sheet = workbook.get_sheet(2)
for row, data in enumerate(gl):
sheet.write(row, 0, data.gLCode)
sheet.write(row, 1, data.shortCode)
sheet.write(row, 2, data.shortCodeName)
sheet.col(0).width = 7500
sheet.col(2).width = 12500
def hyperlink(self, row, col, title, link, style=None):
import urlparse
style = style or 'font: underline single,color 4'
style = self.style(style=style)
r = urlparse.urlparse(link)
if not r.scheme and self.domain:
link = self.domain + link
title = title.replace('"', '')
self.sh.write(row, col,
Formula('HYPERLINK("%s";"%s")' % (link, title)), style)
def write_total(ws, row, first_row):
row += 1
last_row = row
last_column = 1 + 13 * 1
for column in range(2, last_column + 1):
ws.write(
row, column,
Formula("SUM({column}{start}:{column}{end})".format(
column=COLUMN_NAMES[column],
start=first_row + 1,
end=last_row)), Style.currency_bold)
def generate_results(self):
self.results = self.wb.add_sheet(u'Totais')
self._build_header(self.results,
(u'Pessoa', u'Responsabilidade', u'Despesas pagas',
u'Interpagamentos feitos', u'Interpagamentos recebidos', u'Total'))
for person in Person.objects.all():
self._write_line(self.results,
(person.name,
Formula('Sumif(Responsabilidades!E2:E%d, Totais!A%d, Responsabilidades!F2:F%d)' %
(self.responsibilities.line,
self.results.line+1,
self.responsibilities.line),
),
Formula('Sumif(Despesas!E2:E%d, Totais!A%d, Despesas!F2:F%d)' %
(self.expenses.line,
self.results.line+1,
self.expenses.line),
),
Formula('Sumif(Interpagamentos!B2:B%d, Totais!A%d, Interpagamentos!D2:D%d)' %
(self.interpayments.line,
self.results.line+1,
self.interpayments.line),
),
Formula('Sumif(Interpagamentos!C2:C%d, Totais!A%d, Interpagamentos!D2:D%d)' %
(self.interpayments.line,
self.results.line+1,
self.interpayments.line),
),
Formula('-B%d + C%d + D%d - E%d' % ((self.results.line+1,)*4)),
)
)
def _get_cell_data(self, req, col, cell, row, writer):
value = cell['value']
if col == 'report':
url = self.env.abs_href.report(value)
width, line = writer.get_metrics(value)
value = Formula('HYPERLINK("%s",%s)' % (url, get_literal(value)))
return value, col, width, line
if col in ('ticket', 'id'):
value = '#%s' % cell['value']
url = get_resource_url(self.env, row['resource'],
self.env.abs_href)
width = len(value)
value = Formula('HYPERLINK("%s",%s)' % (url, get_literal(value)))
return value, 'id', width, 1
if col == 'milestone':
url = self.env.abs_href.milestone(value)
width, line = writer.get_metrics(value)
value = Formula('HYPERLINK("%s",%s)' % (url, get_literal(value)))
return value, col, width, line
if col == 'time':
if isinstance(value, basestring) and value.isdigit():
value = from_utimestamp(long(value))
return value, '[time]', None, None
elif col in ('date', 'created', 'modified'):
if isinstance(value, basestring) and value.isdigit():
value = from_utimestamp(long(value))
return value, '[date]', None, None
elif col == 'datetime':
if isinstance(value, basestring) and value.isdigit():
value = from_utimestamp(long(value))
return value, '[datetime]', None, None
width, line = writer.get_metrics(value)
return value, col, width, line
def writeExcel(self):
f = xlwt.Workbook(encoding='gbk')
sheet1 = f.add_sheet('Template', cell_overwrite_ok=True)
style1 = xlwt.XFStyle()
row = 0
col = 0
sheet1.write(row, col, self.titleExcel)
row += 1
sheet1.write(row, col, self.tips)
row += 2
sheet1.write(row, col, self.title)
i = 1
for t in self.title:
if i <= 8:
sheet1.write(row, col, t)
col += 1
else:
sheet1.write(row, col, Formula("A1*B1"))
if not os.path.exists(self.arg + '/Template.xls'):
f.save(self.arg + '/Template.xls')
else:
os.remove(self.arg + '/Template.xls')
f.save(self.arg + '/Template.xls')
def write_total_invoice_paid(ws, row, first_row):
row += 1
last_row = row
column = 2
last_column = 1 + 13 * 5
for i in range(1, last_column):
if column % 2 == 0:
border_currency = Style.last_col_currency_border_left
else:
border_currency = Style.currency_bold
if i == last_column:
border_currency = Style.last_col_currency_border_bold
ws.write(
row, column,
Formula("SUM({column}{start}:{column}{end})".format(
column=COLUMN_NAMES[column],
start=first_row + 1,
end=last_row)), border_currency)
column += 1
def _get_cell_data(self, req, col, cell, row, writer):
value = cell['value']
if col == 'report':
url = self.env.abs_href.report(value)
width, line = writer.get_metrics(value)
value = Formula('HYPERLINK("%s",%s)' % (url, get_literal(value)))
return value, col, width, line
if col in ('ticket', 'id'):
value = '#%s' % cell['value']
url = get_resource_url(self.env, row['resource'], self.env.abs_href)
width = len(value)
value = Formula('HYPERLINK("%s",%s)' % (url, get_literal(value)))
return value, 'id', width, 1
if col == 'milestone':
url = self.env.abs_href.milestone(value)
width, line = writer.get_metrics(value)
value = Formula('HYPERLINK("%s",%s)' % (url, get_literal(value)))
return value, col, width, line
if col == 'time':
if isinstance(value, basestring) and value.isdigit():
value = from_utimestamp(long(value))
return value, '[time]', None, None
elif col in ('date', 'created', 'modified'):
if isinstance(value, basestring) and value.isdigit():
value = from_utimestamp(long(value))
return value, '[date]', None, None
elif col == 'datetime':
if isinstance(value, basestring) and value.isdigit():
value = from_utimestamp(long(value))
return value, '[datetime]', None, None
width, line = writer.get_metrics(value)
return value, col, width, line
def code_update_report(workbook_ro, workbook, gl, gl_codeids, nc_sp, pvs_sp, fm_sp, ibm):
"""This report reads from the readonly workbook in order to perform some
cell processing.
"""
# Sheet 1
sheet = workbook.get_sheet(0)
sheet_ro = workbook_ro.get_sheet(0)
# Download hyperlink:
bigfont = easyxf('font: bold 1,height 360;') # Font height is in "twips" (1/20 of a point)
url = Formula('HYPERLINK("{}")'.format(settings.IBM_CODE_UPDATER_URI))
sheet.write(1, 0, url, bigfont)
# Padded zeroes number format
pad2, pad3, pad4 = XFStyle(), XFStyle(), XFStyle()
pad2.num_format_str = '00'
pad3.num_format_str = '000'
pad4.num_format_str = '0000'
# For each of the GL code IDs, take a subset of the query
# and insert values as required.
row = 4
max_col_idx = 21 # Start at column V.
for codeID in gl_codeids:
gl_pivs = gl.filter(codeID=codeID)
g = gl_pivs[0]
# Fill the non-resource columns.
sheet.write(row, 0, g.codeID)
sheet.write(row, 1, int(g.costCentre), pad3)
sheet.write(row, 2, g.account, pad2)
sheet.write(row, 3, g.service, pad2)
sheet.write(row, 4, g.activity, pad3)
try:
sheet.write(row, 5, int(g.project), pad4)
except ValueError:
sheet.write(row, 5, g.project, pad4)
try:
sheet.write(row, 6, int(g.job), pad3)
except ValueError:
sheet.write(row, 6, g.job, pad3)
sheet.write(row, 7, g.jobName)
sheet.write(row, 8, g.activityName)
sheet.write(row, 9, g.projNameNo)
sheet.write(row, 19, g.mPRACategory)
# Write the SUM formula.
sheet.write(row, 20, Formula('ROUND(SUM(V{}:GP{}), 0)'.format(row+1, row+1)))
# First, find the maximum column index in the template headers (row 4).
blank_cell = False
while not blank_cell:
if not sheet_ro.cell_value(3, max_col_idx): # Blank cell
blank_cell = True
else:
max_col_idx += 1
# Write ytdActual values for matching resource columns.
# Find the correct cell index of a matching resource code.
# If no match found, use the '0000' column (the first).
for gl_piv in gl_pivs:
resource_idx = 21 # Column V, '0000'
match_resource_code = False
for i in range(resource_idx, max_col_idx + 1):
if sheet_ro.cell_value(3, i) and int(sheet_ro.cell_value(3, i)) == gl_piv.resource:
match_resource_code = True
break
resource_idx += 1
if not match_resource_code: # No match was found.
resource_idx = 21 # Insert the ytdActual into column V.
# Write the ytdActual to the sheet.
sheet.write(row, resource_idx, gl_piv.ytdActual)
row += 1 # Advance one row, to the next Code ID.
row += 1
# Insert the footer row formulae and '#END OF INPUT'
sheet.write(row, 0, '#END OF INPUT')
sheet.write(row, 20, Formula('ROUND(SUM(V{}:GP{}), 0)'.format(row+1, row+1)))
for i in range(21, max_col_idx):
# For cell V:<end> in the footer row, insert a SUM formula.
sheet.write(row, i, Formula('ROUND(SUM({}:{}), 0)'.format(cellname(4, i), cellname(row-1, i))))
# Sheet 2: Service priority checkboxes.
sheet = workbook.get_sheet(1)
write_service_priorities(sheet, nc_sp, pvs_sp, fm_sp)
# Sheet 3: Budget area & project sponsor lookup data.
# This is a list of unique budgetArea and projectSponsor values, written in
# as reference data for macros.
sheet = workbook.get_sheet(2)
write_budget_areas(sheet, ibm)
write_project_sponsors(sheet, ibm)
write_regional_spec_info(sheet, ibm)
# Select the first sheet.
sheet = workbook.get_sheet(0)
return fileslList
except IOError, msg:
print msg
logfiles = []
logfiles = get_logfile_in_dir(
r'c:\arcgisserver\logs\JIANGMB.ESRICHINA.COM\server')
book = Workbook('utf-8')
#crate the cover sheet
sheet0 = book.add_sheet('Cover', cell_overwrite_ok=True)
row0 = sheet0.row(0)
row0.write(0, "ID")
row0.write(1, "FILE")
row0.write(2, "SHEET")
tall_style = Style.easyxf('font:height 72;')
row0.set_style(tall_style)
sheet0.col(1).width = 256 * 50
for item in logfiles:
new_row = sheet0.row(logfiles.index(item) + 1)
new_row.write(0, logfiles.index(item) + 1)
click = '#sheet%s!A1' % str(logfiles.index(item))
text = os.path.split(item)[1]
style = Style.easyxf('font: underline single,color blue;')
new_row.write(1, Formula('HYPERLINK("%s";"%s")' % (click, text)), style)
new_row.write(2, 'sheet' + str(logfiles.index(item)))
#creat other sheet
out_putlist = get_formatted_in_a_file(item)
Write_To_excel(book, 'sheet' + str(logfiles.index(item)), out_putlist)
import xlrd
from xlwt import Workbook, Formula
path = r"D:\Data\Python\export\fichier.xls"
classeur = Workbook()
# On ajoute une feuille au classeur
feuille = classeur.add_sheet("OCB")
feuille.write(0, 0, 1)
feuille.write(0, 1, 2)
feuille.write(0, 2, Formula('A1+B1'))
classeur.save(path)
print u"Fichier cree: {}".format(path)
'''
import glob, csv, xlwt, os
wb = xlwt.Workbook()
for filename in glob.glob("c:/xxx/*.csv"):
(f_path, f_name) = os.path.split(filename)
(f_short_name, f_extension) = os.path.splitext(f_name)
ws = wb.add_sheet(f_short_name)
spamReader = csv.reader(open(filename, 'rb'))
for rowx, row in enumerate(spamReader):
for colx, value in enumerate(row):
ws.write(rowx, colx, value)
def writeResults(C1,C2,C3,R2,R3,f,magCL,magOL,phaseOL,magvcoTF,PFDCPNoiseOut,
PrescalerNoiseOut,VCONoiseOut,R2NoiseOut,R3NoiseOut,XTALNoiseOut,SDNoiseOut,
TotalNoise,t,fT,lockTime_0p001Pcnt, lockTime_0p0001Pcnt, lockTime_0p00001Pcnt,
lockTime_0p000001Pcnt, f2, fInterpol, TotalNoiseV2HzInterpol, enteredKphi,
enteredKVCO, enteredPM, enteredLoopBW, enteredFout, enteredFref, enteredR,
enteredP, enteredT31, enteredGamma, noiseWorkbook,PFDCPNoise, XTALNoise,
PrescalerNoise, VCONoise, SDNoise):
book = Workbook()
whiteCell = easyxf("pattern: fore_colour white, pattern solid;")
parameter = easyxf('font: name Arial, bold True, height 280; alignment: horizontal center')
parameterValue = easyxf('font: name Arial, height 280;' 'borders: left thick, right thick;' 'alignment: horizontal center;' 'pattern: pattern solid, fore_colour white;', num_format_str='0.000E+00')
parameterValue2 = easyxf('font: name Arial, height 280;' 'borders: left thick, right thick;' 'alignment: horizontal center;' 'pattern: pattern solid, fore_colour white;', num_format_str='0.000')
parameterValueRed = easyxf('font: name Arial, bold True, height 280, colour red;' 'alignment: horizontal center', num_format_str='0.000E+00')
parameterValue2Red = easyxf('font: name Arial, bold True, height 280, colour red;' 'alignment: horizontal center', num_format_str='0.000')
parameterValue3 = easyxf('font: name Arial, height 280;' 'borders: left thick, right thick;' 'alignment: horizontal center;' 'pattern: pattern solid, fore_colour white;', num_format_str='0.000000%')
columnHeader = easyxf('font: name Arial, bold True, height 280; alignment: horizontal center')
notes = easyxf('font: name Arial, bold True, height 280; alignment: horizontal left;' "pattern: fore_colour white, pattern solid;")
notesRed = easyxf('font: name Arial, bold True, height 280, colour red; alignment: horizontal left;' "pattern: fore_colour white, pattern solid;")
link = easyxf('font: name Arial, bold True, italic True, height 240, underline single, colour red; alignment: horizontal left;' "pattern: fore_colour white, pattern solid;")
linkContact = easyxf('font: name Arial, bold True, italic True, height 240, underline single, colour black; alignment: horizontal left;' "pattern: fore_colour white, pattern solid;")
columnHeaderBorderL = easyxf('font: name Arial, bold True, height 280;' 'borders: left thick;' 'alignment: horizontal center;' 'pattern: pattern solid, fore_colour white;')
columnHeaderBorderLRed = easyxf('font: name Arial, bold True, height 280, colour red;' 'borders: left thick;' 'alignment: horizontal center;' 'pattern: pattern solid, fore_colour gray25;')
columnHeaderBorderBLRed = easyxf('font: name Arial, bold True, height 280, colour red;' 'borders: left thick, bottom thick;' 'alignment: horizontal center;' 'pattern: pattern solid, fore_colour gray25;')
columnHeaderBorderTL = easyxf('font: name Arial, bold True, height 280;' 'borders: left thick, top thick;' 'alignment: horizontal center;' 'pattern: pattern solid, fore_colour white;')
columnHeaderBorderBL = easyxf('font: name Arial, bold True, height 280;' 'borders: left thick, bottom thick;' 'alignment: horizontal center;' 'pattern: pattern solid, fore_colour white;')
columnHeaderBorderTLBR = easyxf('font: name Arial, bold True, height 280;' 'borders: left thick, top thick, bottom thick, right thick;' 'alignment: horizontal center;' 'pattern: pattern solid, fore_colour gray25;')
columnHeaderBorderTLBRAlignleft = easyxf('font: name Arial, bold True, height 280;' 'borders: left thick, top thick, bottom thick, right thick;' 'alignment: horizontal left;' 'pattern: pattern solid, fore_colour gray25;')
parameterValue2BorderBR = easyxf('font: name Arial, height 280;' 'borders: right thick, bottom thick;' 'alignment: horizontal center;' 'pattern: pattern solid, fore_colour gray25;', num_format_str='0.000')
parameterValue2BorderR = easyxf('font: name Arial, height 280;' 'borders: right thick;' 'alignment: horizontal center;' 'pattern: pattern solid, fore_colour gray25;', num_format_str='0.000')
parameterValueBorderR = easyxf('font: name Arial, height 280;' 'borders: right thick;' 'alignment: horizontal center;' 'pattern: pattern solid, fore_colour gray25;', num_format_str='0.000E+00')
parameterValueBorderBR = easyxf('font: name Arial, height 280;' 'borders: bottom thick, right thick;' 'alignment: horizontal center;' 'pattern: pattern solid, fore_colour gray25;', num_format_str='0.000E+00')
parameterValueBorderBRWhite = easyxf('font: name Arial, height 280;' 'borders: bottom thick, right thick, left thick;' 'alignment: horizontal center;' 'pattern: pattern solid, fore_colour white;', num_format_str='0.000E+00')
parameterValueBorderTR = easyxf('font: name Arial, height 280;' 'borders: top thick, right thick;' 'alignment: horizontal center;' 'pattern: pattern solid, fore_colour gray25;', num_format_str='0.000E+00')
redResult = easyxf('font: name Arial, bold True, height 280, colour red;' 'alignment: horizontal center')
#Write the Loop Parameters worksheet
sheetLoopParam = book.add_sheet('Loop Parameters')
for i in range(100):
sheetLoopParam.row(i).set_style(whiteCell)#Make everything white first
sheetLoopParam.col(0).width = 8000
sheetLoopParam.col(1).width = 5000
sheetLoopParam.insert_bitmap(os.path.abspath("PLL_diagram_Excel.bmp"), 0, 0)
sheetLoopParam.write(20,0,'Kphi',columnHeaderBorderTL)
sheetLoopParam.write(20,1,enteredKphi,parameterValueBorderTR)
sheetLoopParam.write(21,0,'KVCO',columnHeaderBorderL)
sheetLoopParam.write(21,1,enteredKVCO,parameterValueBorderR)
sheetLoopParam.write(22,0,'Phase Margin',columnHeaderBorderL)
sheetLoopParam.write(22,1,enteredPM,parameterValue2BorderR)
sheetLoopParam.write(23,0,'Loop Bandwidth',columnHeaderBorderL)
sheetLoopParam.write(23,1,enteredLoopBW,parameterValueBorderR)
sheetLoopParam.write(24,0,'Fout',columnHeaderBorderL)
sheetLoopParam.write(24,1,enteredFout,parameterValueBorderR)
sheetLoopParam.write(25,0,'Fref',columnHeaderBorderL)
sheetLoopParam.write(25,1,enteredFref,parameterValueBorderR)
sheetLoopParam.write(26,0,'R',columnHeaderBorderL)
sheetLoopParam.write(26,1,enteredR,parameterValue2BorderR)
sheetLoopParam.write(27,0,'P',columnHeaderBorderL)
sheetLoopParam.write(27,1,enteredP,parameterValue2BorderR)
sheetLoopParam.write(28,0,'T31',columnHeaderBorderL)
sheetLoopParam.write(28,1,enteredT31,parameterValue2BorderR)
sheetLoopParam.write(29,0,'Gamma',columnHeaderBorderBL)
sheetLoopParam.write(29,1,enteredGamma,parameterValue2BorderBR)
sheetLoopParam.write(32,0," References:",notes)
sheetLoopParam.write(33,0, Formula('HYPERLINK("http://www.ti.com/tool/pll_book";" PLL Performance Simulation and Design Handbook - 4th Edition Dean Banerjee. 2006.")'),link)
sheetLoopParam.write(35,0, Formula('HYPERLINK("mailto:[email protected]";" Contact")'),linkContact)
#Write the Noise Sources worksheet
if noiseWorkbook == "":
sheetLoopParam.write(37,0,"***WARNING: Empty noise file or an error occurred while reading the file. Using default noise data instead.***",notesRed)
f, XTALNoise, PFDCPNoise, PrescalerNoise, VCONoise, SDNoise = defaultNoise()
sheetNoiseSources = book.add_sheet('Noise Sources')
for i in range(100):
sheetNoiseSources.row(i).set_style(whiteCell)#Make everything white first
sheetNoiseSources.col(0).width = 6000
sheetNoiseSources.col(1).width = 8000
sheetNoiseSources.col(2).width = 8000
sheetNoiseSources.col(3).width = 10000
sheetNoiseSources.col(4).width = 8000
sheetNoiseSources.col(5).width = 10000
sheetNoiseSources.write(0,0,'Frequency (Hz)',columnHeaderBorderTLBR)
sheetNoiseSources.write(0,1,'XTAL Noise (dBc/Hz)',columnHeaderBorderTLBR)
sheetNoiseSources.write(0,2,'PFDCP Noise (dBc/Hz)',columnHeaderBorderTLBR)
sheetNoiseSources.write(0,3,'Prescaler Noise (dBc/Hz)',columnHeaderBorderTLBR)
sheetNoiseSources.write(0,4,'VCO Noise (dBc/Hz)',columnHeaderBorderTLBR)
sheetNoiseSources.write(0,5,'Sigma Delta Noise (dBc/Hz)',columnHeaderBorderTLBR)
for i in range(len(f)):
sheetNoiseSources.write(i+1,0,f[i],parameterValue)
sheetNoiseSources.write(i+1,1,XTALNoise[i],parameterValue2)
sheetNoiseSources.write(i+1,2,PFDCPNoise[i],parameterValue2)
sheetNoiseSources.write(i+1,3,PrescalerNoise[i],parameterValue2)
sheetNoiseSources.write(i+1,4,VCONoise[i],parameterValue2)
sheetNoiseSources.write(i+1,5,SDNoise[i],parameterValue2)
#Write Loop Filter Components worksheet:
sheetLoopFilter = book.add_sheet('Loop Filter Components')
for i in range(100):
sheetLoopFilter.row(i).set_style(whiteCell)#Make everything white first
sheetLoopFilter.col(0).width = 4000
sheetLoopFilter.col(1).width = 5000
sheetLoopFilter.write(0,0,' Loop Filter Components',columnHeaderBorderTLBRAlignleft)
sheetLoopFilter.write(0,1,None,columnHeaderBorderTLBRAlignleft)
sheetLoopFilter.write(1,0,'C1',columnHeaderBorderTL)
sheetLoopFilter.write(1,1,C1,parameterValue)
sheetLoopFilter.write(2,0,'C2',columnHeaderBorderL)
sheetLoopFilter.write(2,1,C2,parameterValue)
sheetLoopFilter.write(3,0,'C3',columnHeaderBorderL)
sheetLoopFilter.write(3,1,C3,parameterValue)
sheetLoopFilter.write(4,0,'R2',columnHeaderBorderL)
sheetLoopFilter.write(4,1,R2,parameterValue)
sheetLoopFilter.write(5,0,'R3',columnHeaderBorderBL)
sheetLoopFilter.write(5,1,R3,parameterValueBorderBRWhite)
#Write Loop Response worksheet:
sheetLoopResponse = book.add_sheet('Loop Response Data')
for i in range(100):
sheetLoopResponse.row(i).set_style(whiteCell)#Make everything white first
sheetLoopResponse.col(0).width = 6000
sheetLoopResponse.write(0,0,'Frequency (Hz)',columnHeaderBorderTLBR)
sheetLoopResponse.col(1).width = 15000
sheetLoopResponse.write(0,1,'Closed Loop Response Magnitude (dB)',columnHeaderBorderTLBR)
sheetLoopResponse.col(2).width = 14000
sheetLoopResponse.write(0,2,'Open Loop Response Magnitude (dB)',columnHeaderBorderTLBR)
sheetLoopResponse.col(3).width = 14000
sheetLoopResponse.write(0,3,'Open Loop Response Phase (dB)',columnHeaderBorderTLBR)
sheetLoopResponse.col(4).width = 14000
sheetLoopResponse.write(0,4,'VCO Transfer Function Magnitude (dB)',columnHeaderBorderTLBR)
for i in range(len(f)):
sheetLoopResponse.write(i+1,0,f[i],parameterValue)
sheetLoopResponse.write(i+1,1,magCL[i],parameterValue2)
sheetLoopResponse.write(i+1,2,magOL[i],parameterValue2)
sheetLoopResponse.write(i+1,3,phaseOL[i],parameterValue2)
sheetLoopResponse.write(i+1,4,magvcoTF[i],parameterValue2)
#Write Noise Results worksheet:
sheetPLLNoise = book.add_sheet('Output Noise Contributors')
for i in range(100):
sheetPLLNoise.row(i).set_style(whiteCell)#Make everything white first
sheetPLLNoise.col(0).width = 6000
sheetPLLNoise.write(0,0,'Frequency (Hz)',columnHeaderBorderTLBR)
sheetPLLNoise.col(1).width = 6000
sheetPLLNoise.write(0,1,'PFDCP (dBc/Hz)',columnHeaderBorderTLBR)
sheetPLLNoise.col(2).width = 7000
sheetPLLNoise.write(0,2,'Prescaler (dBc/Hz)',columnHeaderBorderTLBR)
sheetPLLNoise.col(3).width = 6000
sheetPLLNoise.write(0,3,'VCO (dBc/Hz)',columnHeaderBorderTLBR)
sheetPLLNoise.col(4).width = 6000
sheetPLLNoise.write(0,4,'R2 (dBc/Hz)',columnHeaderBorderTLBR)
sheetPLLNoise.col(5).width = 6000
sheetPLLNoise.write(0,5,'R3 (dBc/Hz)',columnHeaderBorderTLBR)
sheetPLLNoise.col(6).width = 6000
sheetPLLNoise.write(0,6,'XTAL (dBc/Hz)',columnHeaderBorderTLBR)
sheetPLLNoise.col(7).width = 8000
sheetPLLNoise.write(0,7,'Sigma Delta (dBc/Hz)',columnHeaderBorderTLBR)
sheetPLLNoise.col(8).width = 8000
sheetPLLNoise.write(0,8,'Total Noise (dBc/Hz)',columnHeaderBorderTLBR)
for i in range(len(f)):
sheetPLLNoise.write(i+1,0,f[i],parameterValue)
sheetPLLNoise.write(i+1,1,PFDCPNoiseOut[i],parameterValue2)
sheetPLLNoise.write(i+1,2,PrescalerNoiseOut[i],parameterValue2)
sheetPLLNoise.write(i+1,3,VCONoiseOut[i],parameterValue2)
sheetPLLNoise.write(i+1,4,R2NoiseOut[i],parameterValue2)
sheetPLLNoise.write(i+1,5,R3NoiseOut[i],parameterValue2)
sheetPLLNoise.write(i+1,6,XTALNoiseOut[i],parameterValue2)
sheetPLLNoise.write(i+1,7,SDNoiseOut[i],parameterValue2)
sheetPLLNoise.write(i+1,8,TotalNoise[i],parameterValue2)
#Write Time Response worksheet:
sheetPLLTime = book.add_sheet('Time Response')
for i in range(2050):
sheetPLLTime.row(i).set_style(whiteCell)#Make everything white first
sheetPLLTime.col(0).width = 5000
sheetPLLTime.write(0,0,'Time (s)',columnHeaderBorderTLBR)
sheetPLLTime.col(1).width = 9000
sheetPLLTime.write(0,1,'Output Frequency (Hz)',columnHeaderBorderTLBR)
for i in range(len(t)):
sheetPLLTime.write(i+1,0,t[i],parameterValue)
sheetPLLTime.write(i+1,1,fT[i],parameterValue)
#Write lock times
sheetLockTimes = book.add_sheet('Lock Times')
for i in range(100):
sheetLockTimes.row(i).set_style(whiteCell)#Make everything white first
sheetLockTimes.col(0).width = 11000
sheetLockTimes.write(0,0,'Locks within what % error',columnHeaderBorderTLBR)
sheetLockTimes.col(1).width = 11000
sheetLockTimes.write(0,1,'Locks within how many Hertz',columnHeaderBorderTLBR)
sheetLockTimes.col(2).width = 6000
sheetLockTimes.write(0,2,'Lock Time (s)',columnHeaderBorderTLBR)
sheetLockTimes.write(1,0,0.00001,parameterValue3)
sheetLockTimes.write(1,1,float(scientific(0.00001*f2)),parameterValue)
sheetLockTimes.write(1,2,lockTime_0p001Pcnt,parameterValue)
sheetLockTimes.write(2,0,0.000001,parameterValue3)
sheetLockTimes.write(2,1,float(scientific(0.000001*f2)),parameterValue)
sheetLockTimes.write(2,2,lockTime_0p0001Pcnt,parameterValue)
sheetLockTimes.write(3,0,0.0000001,parameterValue3)
sheetLockTimes.write(3,1,float(scientific(0.0000001*f2)),parameterValue)
sheetLockTimes.write(3,2,lockTime_0p00001Pcnt,parameterValue)
sheetLockTimes.write(4,0,0.00000001,parameterValue3)
sheetLockTimes.write(4,1,float(scientific(0.00000001*f2)),parameterValue)
sheetLockTimes.write(4,2,lockTime_0p000001Pcnt,parameterValue)
#Phase Error and Jitter worksheets
sheetphaseError = book.add_sheet('Phase Error')
for i in range(650):
sheetphaseError.row(i).set_style(whiteCell)#Make everything white first
sheetphaseError.col(0).width = 6000
sheetphaseError.write(0,0,'Frequency (Hz)',columnHeaderBorderTLBR)
sheetphaseError.col(1).width = 8000
sheetphaseError.write(0,1,'Total Noise (V2/Hz)',columnHeaderBorderTLBR)
sheetphaseError.col(2).width = 12500
sheetphaseError.write(0,2,'Frequency Error Integrand (V2*Hz)',columnHeaderBorderTLBR)
for i in range(len(fInterpol)):
sheetphaseError.write(i+1,0,fInterpol[i],parameterValue)
sheetphaseError.write(i+1,1,TotalNoiseV2HzInterpol[i],parameterValue)
sheetphaseError.write(i+1,2,fInterpol[i]*fInterpol[i]*TotalNoiseV2HzInterpol[i],parameterValue)
sheetphaseError.col(4).width = 12000
sheetphaseError.write(1,4,"Lower Integration Limit (Hz)", columnHeaderBorderTL)
sheetphaseError.write(2,4,"Upper Integration Limit (Hz)", columnHeaderBorderBL)
sheetphaseError.write(3,4,"RMS Phase Error (degrees)", columnHeaderBorderL)
sheetphaseError.write(4,4,"Jitter (s)", columnHeaderBorderL)
sheetphaseError.write(5,4,"RMS Frequency Error (Hz)", columnHeaderBorderBL)
sheetphaseError.col(5).width = 6000
sheetphaseError.write(1,5,1.7e3,parameterValueBorderTR)
sheetphaseError.write(2,5,200e3,parameterValueBorderBR)
sheetphaseError.write(7,4,"Enter lower and upper integration limits to calculate RMS Phase Error",notes)
sheetphaseError.write(8,4,"Required: (Upper Int. Limit) >= 20*(Lower Int. Limit)",notes)
sheetphaseError.write(9,4,"Calculations made using interpolated data",notes)
#x="(180/PI())*SQRT(2*((VLOOKUP(E3,A2:B32,1)-VLOOKUP(E2,A2:B32,1))/6)*(VLOOKUP(E2,A2:B32,2)+VLOOKUP(E3,A2:B32,2)+4*VLOOKUP(((VLOOKUP(E2,A2:B32,1)+VLOOKUP(E3,A2:B32,1))/2.0),A2:B32,2)))"
freqError="""SQRT(
2*((F3-F3/1.5)/6)*(VLOOKUP(F3,A2:C602,3) + VLOOKUP(F3/1.5,A2:C602,3) + 4*VLOOKUP((F3 + F3/1.5)/2,A2:C602,3))
+ 2*((F3/1.5-F3/2.25)/6)*(VLOOKUP(F3/1.5,A2:C602,3) + VLOOKUP(F3/2.25,A2:C602,3) + 4*VLOOKUP((F3/1.5 + F3/2.25)/2,A2:C602,3))
+ 2*((F3/2.25-F3/3.375)/6)*(VLOOKUP(F3/2.25,A2:C602,3) + VLOOKUP(F3/3.375,A2:C602,3) + 4*VLOOKUP((F3/2.25 + F3/3.375)/2,A2:C602,3))
+ 2*((F3/3.375-F3/5.0625)/6)*(VLOOKUP(F3/3.375,A2:C602,3) + VLOOKUP(F3/5.0625,A2:C602,3) + 4*VLOOKUP((F3/3.375 + F3/5.0625)/2,A2:C602,3))
+ 2*((F3/5.0625-F3/7.594)/6)*(VLOOKUP(F3/5.0625,A2:C602,3) + VLOOKUP(F3/7.594,A2:C602,3) + 4*VLOOKUP((F3/5.0625 + F3/7.594)/2,A2:C602,3))
+ 2*((F3/7.594-F3/11.39)/6)*(VLOOKUP(F3/7.594,A2:C602,3) + VLOOKUP(F3/11.39,A2:C602,3) + 4*VLOOKUP((F3/7.594 + F3/11.39)/2,A2:C602,3))
+ 2*((F3/11.39-F3/17.086)/6)*(VLOOKUP(F3/11.39,A2:C602,3) + VLOOKUP(F3/17.086,A2:C602,3) + 4*VLOOKUP((F3/11.39 + F3/17.086)/2,A2:C602,3))
+ 2*((F3/17.086-F2)/6)*(VLOOKUP(F3/17.086,A2:C602,3) + VLOOKUP(F2,A2:C602,3) + 4*VLOOKUP((F3/17.086 + F2)/2,A2:C602,3))
)"""#Take the Simpson integral over several intervals.
phaseError="""(180/PI())*SQRT(
2*((F2*1.5-F2)/6)*(VLOOKUP(F2*1.5,A2:C602,2) + VLOOKUP(F2,A2:C602,2) + 4*VLOOKUP((F2*1.5 + F2)/2,A2:C602,2))
+ 2*((F2*2.25-F2*1.5)/6)*(VLOOKUP(F2*2.25,A2:C602,2) + VLOOKUP(F2*1.5,A2:C602,2) + 4*VLOOKUP((F2*2.25 + F2*1.5)/2,A2:C602,2))
+ 2*((F2*3.375-F2*2.25)/6)*(VLOOKUP(F2*3.375,A2:C602,2) + VLOOKUP(F2*2.25,A2:C602,2) + 4*VLOOKUP((F2*3.375 + F2*2.25)/2,A2:C602,2))
+ 2*((F2*5.0625-F2*3.375)/6)*(VLOOKUP(F2*5.0625,A2:C602,2) + VLOOKUP(F2*3.375,A2:C602,2) + 4*VLOOKUP((F2*5.0625 + F2*3.375)/2,A2:C602,2))
+ 2*((F2*7.594-F2*5.0625)/6)*(VLOOKUP(F2*7.594,A2:C602,2) + VLOOKUP(F2*5.0625,A2:C602,2) + 4*VLOOKUP((F2*7.594 + F2*5.0625)/2,A2:C602,2))
+ 2*((F2*11.39-F2*7.594)/6)*(VLOOKUP(F2*11.39,A2:C602,2) + VLOOKUP(F2*7.594,A2:C602,2) + 4*VLOOKUP((F2*11.39 + F2*7.594)/2,A2:C602,2))
+ 2*((F2*17.086-F2*11.39)/6)*(VLOOKUP(F2*17.086,A2:C602,2) + VLOOKUP(F2*11.39,A2:C602,2) + 4*VLOOKUP((F2*17.086 + F2*11.39)/2,A2:C602,2))
+ 2*((F3-F2*17.086)/6)*(VLOOKUP(F3,A2:C602,2) + VLOOKUP(F2*17.086,A2:C602,2) + 4*VLOOKUP((F3 + F2*17.086)/2,A2:C602,2))
)"""#Take the Simpson integral over several intervals.
jitter="F4/360.0/'Loop Parameters'!B25"
y="(180/PI())*SQRT(2*((E3-E2)/6)*(VLOOKUP(E2,A2:B32,2)+VLOOKUP(E3,A2:B32,2)+4*VLOOKUP(((E3+E2)/2.0),A2:B32,2)))"
sheetphaseError.write(3,5,Formula(phaseError),parameterValue2BorderR)
sheetphaseError.write(4,5,Formula(jitter),parameterValueBorderR)
sheetphaseError.write(5,5,Formula(freqError),parameterValue2BorderBR)
return book
filepath.write(''' This is my assignment for Lab 9.
I know that it is easy to create textfiles in Python
I just need to relax and follow the yellow brick road''')
filepath.close()
# Create the following Excel Spreadsheet using Python.
from xlwt import Workbook, Formula, easyxf # xlwt is built in module in python used to work with excelfiles.
wb = Workbook()
sheet1 = wb.add_sheet('Sheet1') #add_sheet is used to add anew sheet in the excel file.
style1 = easyxf('pattern : pattern solid, fore_color yellow;') #easyfx is used to style cells in excel.
sheet1.write(0,0,'Column1') # to write the data in format of row, column, value.
sheet1.write(0,1,'Column2')
sheet1.write(0,2,'Column3')
wb.save('xlwt Assignment.xls')
# For Column1 values
for i in xrange(1,11):
sheet1.write(i,0,i)
sheet1.write(11,0,Formula('SUM(A2:A11)'),style1) #Formula is used to write fomula in excel.
wb.save('xlwt Assignment.xls')
# For column2 values
for i,j in zip(xrange(1,11),xrange( 100,200,10)):
sheet1.write(i,1,j)
sheet1.write(11,1,Formula('SUM(B2:B11)'),style1)
wb.save('xlwt Assignment.xls') # save is used to save the excel file.
# For column3 values
for i,j in zip(xrange(1,11),xrange(1500,15001,1500)):
sheet1.write(i,2,j)
sheet1.write(11,2,Formula('SUM(C2:C11)'),style1)
wb.save('xlwt Assignment.xls')
else:
col_style = style_num
# écriture
wc.write(irow+1, icol+1, str(value), col_style)
# - bottom
for icol in xrange(3): wc.write(irow+2, icol, '', style_bottom)
# Deuxième feuille
# - nom
wn = w.add_sheet('Testouille')
# - gros titre
wn.write_merge(0, 1, 0, 5, u"Ça c'est un gros titre", xls_style(o=1, s=500))
# - hyperlien
uri = "http://relay.vacumm.fr/~raynaud/pydoc"
urn = "Python Actimar"
wn.write_merge(3,3,1,10,Formula('HYPERLINK("%s";"%s")'%(uri, urn)),xls_style(c=4))
# - groupes de niveaux
wn.write(4, 1, 'level1')
wn.write(5, 1, 'level2')
wn.write(6, 1, 'level2')
wn.write(7, 1, 'level1')
wn.row(4).level = wn.row(7).level = 1
wn.row(5).level = wn.row(6).level = 2
# - dates
from datetime import datetime
wn.write(8, 0, 'Dates :')
wn.write(8, 1, datetime.now(), xls_style(fmt='h:mm:ss AM/PM'))
wn.write(8, 2, datetime.now(), xls_style(fmt='MMM-YY'))
# Écriture du document
def work_am():
p = 1
camera = cv2.VideoCapture(0)
path = "the_nhan_vien"
while True:
rb = xlrd.open_workbook('bang1.xlsx')
wb = copy(rb)
w_sheet = wb.get_sheet(0)
sheet = rb.sheet_by_index(0)
(grabbed, frame) = camera.read() # doc frame video
frame = imutils.resize(frame, width=500) #hinh thi kich thuoc webcam
frame_clone = frame.copy() #copy ra 1 ban de xu ly sao nay
# detect face
faces = detector.detectMultiScale(frame,
scaleFactor=1.1,
minNeighbors=5,
minSize=(30, 30),
flags=cv2.CASCADE_SCALE_IMAGE)
# Loop over the face bounding boxes
for (fX, fY, fW, fH) in faces:
roi = frame[fY:fY + fH, fX:fX + fW] # cat khuon mat
roi = cv2.cvtColor(roi,
cv2.COLOR_BGR2GRAY) # chuyen anh thanh anh xam
roi = cv2.resize(roi, (200, 200)) #resize khich thuoc
roi = roi.astype("float") / 255.0 # chuan hoa
roi = img_to_array(roi) # chuyen thanh mang
roi = np.expand_dims(
roi, axis=0
) # them 1 chieu vi dau vao CNN la (None,hight,with,dept)
predictions = model.predict(roi) # du doan
i = predictions.argmax(axis=1)[0] #lay chi so cao nhat
accur = "{:.2f}%".format(predictions[0][i] *
100) #tinh do chinh xac
print("do chinh xac: " + accur)
result = predictions[0][i]
if result > 0.8: # thay doi do chinh xac o day neu predict lon hon 80% thi hien ten nguoc lai hien unknown
text = "{}".format(name_class[i]) #lay ten class
# Show our detected faces along with smiling/not smiling labels
img1 = cv2.imread(
os.path.join(path, "{}".format(str(i) + ".png")),
cv2.IMREAD_COLOR)
winname = "anh_nhan_vien"
cv2.namedWindow(winname) # Create a named window
cv2.moveWindow(winname, 1000, 70) # Move it to (40,30)
cv2.imshow(winname, img1)
else:
text = "Unknown"
i = 10
img1 = cv2.imread(
os.path.join(path, "{}".format(str(i) + ".png")),
cv2.IMREAD_COLOR)
winname = "anh_nhan_vien"
cv2.namedWindow(winname) # Create a named window
cv2.moveWindow(winname, 1000, 70) # Move it to (40,30)
cv2.imshow(winname, img1)
#hien thi ten class
cv2.putText(frame_clone, text, (fX, fY - 10),
cv2.FONT_HERSHEY_SIMPLEX, 0.45, (0, 0, 255), 2)
# dong khung mat
cv2.rectangle(frame_clone, (fX, fY), (fX + fW, fY + fH),
(0, 0, 255), 2)
if cv2.waitKey(1) & 0xFF == ord("a"):
if i == 0:
a = 1 + sheet.cell_value(5, 3)
w_sheet.write(5, 3, a)
w_sheet.write(5, 5, Formula('IF(D6>=E6,E6,D6)'))
w_sheet.write(5, 7, Formula('266000*F6+G6'))
elif i == 1:
a = 1 + sheet.cell_value(6, 3)
w_sheet.write(6, 3, a)
w_sheet.write(6, 5, Formula('IF(D7>=E7,E7,D7)'))
w_sheet.write(6, 7, Formula('266000*F7+G7'))
elif i == 2:
a = 1 + sheet.cell_value(7, 3)
w_sheet.write(7, 3, a)
w_sheet.write(7, 5, Formula('IF(D8>=E8,E8,D8)'))
w_sheet.write(7, 7, Formula('266000*F8+G8'))
elif i == 3:
a = 1 + sheet.cell_value(8, 3)
w_sheet.write(8, 3, a)
w_sheet.write(8, 5, Formula('IF(D9>=E9,E9,D9)'))
w_sheet.write(8, 7, Formula('266000*F9+G9'))
elif i == 4:
a = 1 + sheet.cell_value(9, 3)
w_sheet.write(9, 3, a)
w_sheet.write(9, 5, Formula('IF(D10>=E10,E10,D10)'))
w_sheet.write(9, 7, Formula('266000*F10+G10'))
elif i == 5:
a = 1 + sheet.cell_value(10, 3)
w_sheet.write(10, 3, a)
w_sheet.write(10, 5, Formula('IF(D11>=E11,E11,D11)'))
w_sheet.write(10, 7, Formula('266000*F11+G11'))
elif i == 6:
a = 1 + sheet.cell_value(11, 3)
w_sheet.write(11, 3, a)
w_sheet.write(11, 5, Formula('IF(D12>=E12,E12,D12)'))
w_sheet.write(11, 7, Formula('266000*F12+G12'))
elif i == 7:
a = 1 + sheet.cell_value(12, 3)
w_sheet.write(12, 3, a)
w_sheet.write(12, 5, Formula('IF(D13>=E13,E13,D13)'))
w_sheet.write(12, 7, Formula('266000*F13+G13'))
elif i == 8:
a = 1 + sheet.cell_value(13, 3)
w_sheet.write(13, 3, a)
w_sheet.write(13, 5, Formula('IF(D14>=E14,E14,D14)'))
w_sheet.write(13, 7, Formula('266000*F14+G14'))
elif i == 9:
a = 1 + sheet.cell_value(14, 3)
w_sheet.write(14, 3, a)
w_sheet.write(14, 5, Formula('IF(D15>=E15,E15,D15)'))
w_sheet.write(14, 7, Formula('266000*F15+G15'))
wb.save('bang1.xlsx')
cv2.imshow("Face", frame_clone)
# If the 'q' key is pressed, stop the loop
#if cv2.waitKey(1) & 0xFF == ord("q"):
if p == 2:
break
# Cleanup the camera and close any open windows
camera.release()
cv2.destroyAllWindows()
from xlwt import Formula
SUM_WORKING_DAYS = Formula('COUNTA(C7:C37)&"日"')
SUM_WORKING_HOURS = Formula('TEXT(SUM(N7:N37), "[h]:mm")')
SUM_OVERTIME_HOURS = Formula('TEXT(SUM(O7:O37), "[h]:mm")')
# 日々の実働時間を計算するためのエクセル関数を作成
wh_formulas_display = []
wh_formulas_calc = []
for i in range(7, 38):
# TODO 時間を表示するのと計算を可能にするのがうまく両立できない
# 現状の対応：見た目用の時間（黒文字白背景）と計算用の時間（白文字白背景）を別に用意する
c = f'C{i}'
d = f'D{i}'
e = f'E{i}'
wh_formula_display = f'TEXT(IF(OR({c}="", {d}=""), "", {d}-{c}-{e}), "h:mm")'
wh_formulas_display.append(wh_formula_display)
f = f'F{i}'
wh_formula_calc = f'IF({f}="", 0, VALUE({f}))'
wh_formulas_calc.append(wh_formula_calc)
DISPLAY_WORKING_HOURS = [Formula(f) for f in wh_formulas_display]
CALC_WORKING_HOURS = [Formula(f) for f in wh_formulas_calc]
# 日々の残業時間を計算するためのエクセル関数を作成
oh_formulas_display = []
oh_formulas_calc = []
for i in range(7, 38):
# TODO 時間を表示するのと計算を可能にするのがうまく両立できない
def one2one(self):
sourceFile0=self.lineEdit_5.text()
sourceFile1=self.lineEdit_6.text()
print(sourceFile0)
print(sourceFile1)
if sourceFile0.strip()=="" or sourceFile1.strip()=="":
QMessageBox.critical(self,"注意","请选择数据读取表和写入表！")
return
else:
sourceFile0=sourceFile0.strip()
sourceFile1=sourceFile1.strip()
filenameOk0=sourceFile0.endswith(".xlsx") or sourceFile0.endswith(".xls")
filenameOk1=sourceFile1.endswith(".xlsx") or sourceFile1.endswith(".xls")
filenameOk=filenameOk0 and filenameOk1
if filenameOk is False:
QMessageBox.critical(self,"注意","必须选择.xls或者.xlsx格式EXCEL文件！")
return
columnName0=self.comboBox_0.currentText()
columnName1=self.comboBox_1.currentText()
columnName2=self.comboBox_2.currentText()
columnName3=self.comboBox_3.currentText()
columnName4=self.comboBox_4.currentText()
columnName5=self.comboBox_5.currentText()
print(columnName0,columnName1,columnName2,columnName3,columnName4,columnName5)
#--------------------------------------------------------
#建立SQLITE3数据库连接
conn = sqlite3.connect('advertisers.db')
cursor = conn.cursor()
sql='DROP TABLE IF EXISTS book0'
cursor.execute(sql)
conn.commit()
sql='CREATE TABLE IF NOT EXISTS book0(a varchar(128), b varchar(128), c varchar(128), d varchar(128), e varchar(128), f varchar(128), g varchar(128), h varchar(128), i varchar(128), j varchar(128), k varchar(128), l varchar(128), m varchar(128), n varchar(128), o varchar(128), p varchar(128), q varchar(128), r varchar(128), s varchar(128), t varchar(128), u varchar(128), v varchar(128), w varchar(128), x varchar(128), y varchar(128), z varchar(128))'
cursor.execute(sql)
conn.commit()
#将工作表数据存入SQLITE数据库表
args=[]
book0=open_workbook(sourceFile0)
for sheet in book0.sheets():
for i in range(1,sheet.nrows):
vArray=[]
for j in range(26):
if j<sheet.ncols:
v=str(sheet.cell(i,j).value).strip()
else:
v=''
vArray.append(v)
arg=tuple(vArray)
args.append(arg)
#批量执行sql数据插入
try:
sql = 'INSERT INTO book0(a,b,c,d,e,f,g,h,i,j,k,l,m,n,o,p,q,r,s,t,u,v,w,x,y,z) values(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)'
cursor.executemany(sql, args)
except Exception as e:
print(e)
finally:
cursor.close()
conn.commit()
conn.close()
self.statusbar.showMessage("数据读取表已经成功导入本地SQLITE数据库")
#-----------------------------------------------------------------------
#建立SQLITE3数据库连接
conn = sqlite3.connect('advertisers.db')
cursor = conn.cursor()
sql='DROP TABLE IF EXISTS book1'
cursor.execute(sql)
conn.commit()
sql='CREATE TABLE IF NOT EXISTS book1(a varchar(128), b varchar(128), c varchar(128), d varchar(128), e varchar(128), f varchar(128), g varchar(128), h varchar(128), i varchar(128), j varchar(128), k varchar(128), l varchar(128), m varchar(128), n varchar(128), o varchar(128), p varchar(128), q varchar(128), r varchar(128), s varchar(128), t varchar(128), u varchar(128), v varchar(128), w varchar(128), x varchar(128), y varchar(128), z varchar(128))'
cursor.execute(sql)
conn.commit()
#将工作表数据存入SQLITE数据库表
args=[]
book1=open_workbook(sourceFile1)
for sheet in book1.sheets():
for i in range(1,sheet.nrows):
vArray=[]
for j in range(26):
if j<sheet.ncols:
v=str(sheet.cell(i,j).value).strip()
else:
v=''
vArray.append(v)
arg=tuple(vArray)
args.append(arg)
#批量执行sql数据插入
try:
sql = 'INSERT INTO book1(a,b,c,d,e,f,g,h,i,j,k,l,m,n,o,p,q,r,s,t,u,v,w,x,y,z) values(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)'
cursor.executemany(sql, args)
except Exception as e:
print(e)
finally:
cursor.close()
conn.commit()
conn.close()
self.statusbar.showMessage("数据写入表已经成功导入本地SQLITE数据库")
#--------------------------------------------------------------------------------
#比较两个表
self.statusbar.showMessage("正在比对数据 请稍候")
#创建EXCEL表格准备写入数据
book = Workbook()
sheet= book.add_sheet('Sheet1',cell_overwrite_ok=True)
#单元格格式水蓝色
cellStyle=easyxf('font: name 宋体;pattern: pattern solid, fore_colour aqua')
#建立SQLITE3数据库连接
conn = sqlite3.connect('advertisers.db')
cursor = conn.cursor()
#通过联合多表UPDATE来导入已有广告主到新表中
#覆盖原数据
#sql="UPDATE book1 SET "+columnName3+"=(SELECT "+columnName2+" FROM book0 WHERE book0."+columnName0+"=book1."+columnName1+")"
#不覆盖原数据
sql="UPDATE book1 SET "+columnName3+"=(SELECT "+columnName2+" FROM book0 WHERE book0."+columnName0+"=book1."+columnName1+") WHERE book1."+columnName3+"=''"
print(sql)
cursor.execute(sql)
conn.commit
#覆盖原数据
#sql="UPDATE book1 SET "+columnName5+"=(SELECT "+columnName4+" FROM book0 WHERE book0."+columnName0+"=book1."+columnName1+")"
#不覆盖原数据
sql="UPDATE book1 SET "+columnName5+"=(SELECT "+columnName4+" FROM book0 WHERE book0."+columnName0+"=book1."+columnName1+") WHERE book1."+columnName5+"=''"
print(sql)
cursor.execute(sql)
conn.commit
#从本地数据库表中查询数据输出到EXCEL表
sql="SELECT * FROM book1 ORDER BY l DESC,j ASC,d ASC"
cursor.execute(sql)
conn.commit
rows = cursor.fetchall()
if rows:
rown=1
for row in rows:
for i in range(26):
if (i==3 or i==9):
if row[i]:
link='HYPERLINK("https://www.baidu.com/s?wd='+str(row[i]).replace("&","+")+'";"'+str(row[i])+'")'
#print(link)
sheet.write(rown,i,Formula(link),cellStyle)
else:
sheet.write(rown,i,"",cellStyle)
elif i==5:
link='HYPERLINK("https://www.baidu.com/s?wd=site:www.tianyancha.com '+str(row[5])+'";"'+str(row[5])+'")'
sheet.write(rown,i,Formula(link),cellStyle)
elif i==17:
link='HYPERLINK("https://translate.google.cn/#zh-CN/en/'+str(row[9])+'";"英译")'
sheet.write(rown,i,Formula(link),cellStyle)
elif i==18:
link='HYPERLINK("https://www.baidu.com/s?wd=site:www.tianyancha.com '+str(row[9])+'";"天眼查")'
sheet.write(rown,i,Formula(link),cellStyle)
else:
if row[i]:
sheet.write(rown,i,str(row[i]),cellStyle)
else:
sheet.write(rown,i,"",cellStyle)
rown=rown+1
self.statusbar.showMessage("共计写入"+str(rown)+"行")
#设置EXCEL表格格式
sheet.col(0).hidden=True
sheet.col(1).hidden=True
sheet.col(2).hidden=True
sheet.col(3).width=8000
sheet.col(4).hidden=True
sheet.col(5).width=3000
sheet.col(6).hidden=True
sheet.col(7).hidden=True
sheet.col(8).hidden=True
sheet.col(9).width=10000
sheet.col(10).hidden=True
sheet.col(11).width=10000
sheet.col(12).width=2000
sheet.col(13).width=2000
sheet.col(14).width=2000
sheet.col(15).width=2000
sheet.col(16).width=2000
sheet.col(17).width=2000
sheet.col(18).width=2000
savedFileName=re.sub(r'\.xlsx|\.xls','',sourceFile1)+'_ok.xls'
if os.path.exists(savedFileName):
t=time.localtime()
savedFileName=re.sub(r'\.xlsx|\.xls','',sourceFile1)+'_ok_'+str(t.tm_year)+'_'+str(t.tm_mon)+'_'+str(t.tm_mday)+'_'+str(t.tm_hour)+'_'+str(t.tm_min)+'_'+str(t.tm_sec)+'.xls'
book.save(savedFileName)
hyperlinker="<a href='file:///"+savedFileName+"' title='点击打开'>"+savedFileName+"</a>"
QMessageBox.information(self,"注意","处理完的广告主文件已经保存到<br/><br/>"+hyperlinker+"<br/>（点击链接打开文件）<br/><br/>")
#关闭SQLITE数据库
cursor.close()
conn.close()
def processAdvertisers(self):
targetFileName=self.lineEdit_4.text().strip()
#先判断文件选择是否正确
if targetFileName is "":
QMessageBox.critical(self,'注意', '请先选择要处理的新广告主EXCEL表!')
return
filenameOk=targetFileName.endswith(".xlsx") or targetFileName.endswith(".xls")
if filenameOk is False:
QMessageBox.critical(self,"注意","请输入正确的EXCEL文件名！")
return
print("当前选定要处理的广告主EXCEL表：",targetFileName)
#建立SQLITE3数据库连接
conn = sqlite3.connect('advertisers.db')
cursor = conn.cursor()
sql='DROP TABLE IF EXISTS workbook'
cursor.execute(sql)
conn.commit()
sql='CREATE TABLE IF NOT EXISTS workbook(a varchar(128), b varchar(128), c varchar(128), d varchar(128), e varchar(128), f varchar(128), g varchar(128), h varchar(128), i varchar(128), j varchar(128), k varchar(128), l varchar(128), m varchar(128), n varchar(128), o varchar(128), p varchar(128), q varchar(128), r varchar(128), s varchar(128), t varchar(128), u varchar(128), v varchar(128), w varchar(128), x varchar(128), y varchar(128), z varchar(128))'
cursor.execute(sql)
conn.commit()
#将工作表数据存入SQLITE数据库表
args=[]
book=open_workbook(targetFileName)
for sheet in book.sheets():
for i in range(1,sheet.nrows):
vArray=[]
for j in range(26):
if j<sheet.ncols:
v=str(sheet.cell(i,j).value).strip()
else:
v=""
vArray.append(v)
arg=tuple(vArray)
args.append(arg)
#批量执行sql数据插入
try:
sql = 'INSERT INTO workbook(a,b,c,d,e,f,g,h,i,j,k,l,m,n,o,p,q,r,s,t,u,v,w,x,y,z) values(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)'
cursor.executemany(sql, args)
except Exception as e:
print(e)
finally:
cursor.close()
conn.commit()
conn.close()
self.statusbar.showMessage("Excel表已经成功导入本地SQLITE数据库")
#--------------------------------------------------------------------------------
#测试是否成功
self.statusbar.showMessage("正在比对数据，请稍候...")
#创建EXCEL表格准备写入数据
book = Workbook()
sheet= book.add_sheet('Sheet1',cell_overwrite_ok=True)
#单元格格式水蓝色
cellStyle=easyxf('font: name 宋体;pattern: pattern solid, fore_colour aqua')
#建立SQLITE3数据库连接
conn = sqlite3.connect('advertisers.db')
cursor = conn.cursor()
#通过联合多表UPDATE来导入已有广告主到新表中
sql="UPDATE workbook SET j=(SELECT cname FROM advertiser WHERE bname=workbook.d) WHERE j=''"
cursor.execute(sql)
conn.commit
sql="UPDATE workbook SET l=(SELECT ename FROM advertiser WHERE cname=workbook.j) WHERE l=''"
cursor.execute(sql)
conn.commit
#从本地数据库表中查询数据输出到EXCEL表
sql="SELECT * FROM workbook ORDER BY l DESC,j ASC,d ASC"
cursor.execute(sql)
conn.commit
rows = cursor.fetchall()
if rows:
rown=1
for row in rows:
for i in range(26):
if (i==3 or i==9):
if row[i]:
link='HYPERLINK("https://www.baidu.com/s?wd='+str(row[i]).replace("&","+")+'";"'+str(row[i])+'")'
sheet.write(rown,i,Formula(link),cellStyle)
else:
sheet.write(rown,i,"",cellStyle)
elif i==5:
link='HYPERLINK("https://www.baidu.com/s?wd=site:www.tianyancha.com '+str(row[5])+'";"'+str(row[5])+'")'
sheet.write(rown,i,Formula(link),cellStyle)
elif i==17:
link='HYPERLINK("https://translate.google.cn/#zh-CN/en/'+str(row[9])+'";"英译")'
sheet.write(rown,i,Formula(link),cellStyle)
elif i==18:
link='HYPERLINK("https://www.baidu.com/s?wd=site:www.tianyancha.com '+str(row[9])+'";"天眼查")'
sheet.write(rown,i,Formula(link),cellStyle)
else:
if row[i]:
sheet.write(rown,i,str(row[i]),cellStyle)
else:
sheet.write(rown,i,"",cellStyle)
rown=rown+1
self.statusbar.showMessage("共计写入"+str(rown)+"行")
#设置EXCEL表格格式
sheet.col(0).hidden=True
sheet.col(1).hidden=True
sheet.col(2).hidden=True
sheet.col(3).width=8000
sheet.col(4).hidden=True
sheet.col(5).width=3000
sheet.col(6).hidden=True
sheet.col(7).hidden=True
sheet.col(8).hidden=True
sheet.col(9).width=10000
sheet.col(10).hidden=True
sheet.col(11).width=10000
sheet.col(12).width=2000
sheet.col(13).width=2000
sheet.col(14).width=2000
sheet.col(15).width=2000
sheet.col(16).width=2000
sheet.col(17).width=2000
sheet.col(18).width=2000
savedFileName=re.sub(r'\.xlsx|\.xls','',targetFileName)+'_ok.xls'
if os.path.exists(savedFileName):
t=time.localtime()
savedFileName=re.sub(r'\.xlsx|\.xls','',targetFileName)+'_ok_'+str(t.tm_year)+'_'+str(t.tm_mon)+'_'+str(t.tm_mday)+'_'+str(t.tm_hour)+'_'+str(t.tm_min)+'_'+str(t.tm_sec)+'.xls'
book.save(savedFileName)
hyperlinker="<a href='file:///"+savedFileName+"' title='点击打开'>"+savedFileName+"</a>"
QMessageBox.information(self,"注意","处理完的广告主文件已经保存到<br/><br/>"+hyperlinker+"<br/>（点击链接打开文件）<br/><br/>")
#关闭SQLITE数据库
cursor.close()
conn.close()
def write_sheet(sortedinput, sheetnum, header):
ws = wb.add_sheet('prion-scores-%d' % sheetnum)
header_style = easyxf('font: bold true, color black;')
url_style = easyxf('font: underline single, color blue;')
corescore_pos_style = easyxf(
'font: bold true; pattern: pattern solid, fore_color light_orange;')
corescore_zero_style = easyxf(
'pattern: pattern solid_fill, fore_color light_orange;')
llk_pos_style = easyxf(
'font: bold true; pattern: pattern solid, fore_color light_yellow;')
llk_zero_style = easyxf('pattern: pattern solid, fore_color light_yellow;')
rossprd_high_style = easyxf(
'font: bold true; pattern: pattern solid, fore_color light_turquoise;')
rossprd_low_style = easyxf(
'pattern: pattern solid, fore_color light_turquoise;')
shrink_col_style = easyxf(
'pattern: pattern solid, fore_color gray25; alignment: horizontal center, vertical center, wrap on;'
)
row_style = easyxf('alignment: horizontal center, vertical center;')
#default_style = easyxf('')
# append new columns to header
start_header = header[0:1]
rest = header[2:]
header = []
header.append("rank_by_corescore")
header.extend(start_header)
header.append("gene_name")
header.append("gene_aliases")
header.append("orf_status")
header.append("description")
# rearrange columns in rest: push lesser-used cols to end of list
for item in ["PRDstart", "PRDend", "PRDlen", "PROTlen", "COREaa", "STARTaa", "ENDaa", "PRDaa", "PRDscore", \
"MWstart", "MWend", "MWlen", "HMMall", "HMMvit", "FInumaa", "FImeanhydro", "FImeancharge", "FImeancombo", "FImaxrun"]:
move_to_back(rest, item)
# now add them back
header.extend(rest)
# initialize row number
row = 0
# write header
for col in range(len(header)):
ws.row(0).write(col, header[col], header_style)
# parse fields and generate output Excel file
for inputrow in sortedinput:
orf_id = inputrow['SEQid']
# if we are filtering, only print out specified hits
if include:
if orf_id in include:
if debug:
print "include:", orf_id, "rank:", inputrow[
"rank_by_corescore"]
else:
#print "skip:", orf_id, "rank:", inputrow["rank_by_corescore"]
continue
else:
if debug: print orf_id, "rank:", inputrow["rank_by_corescore"]
# loop through input cols
for col in range(len(header)):
# get key
key = header[col]
# note: offset row by one
if key == "rank_by_corescore":
# use the current row as the rank
value = inputrow[key]
if debug: print orf_id, value
ws.row(row + 1).write(col, value)
# hyperlink ORF name to SGD
elif key == "SEQid":
try:
value = inputrow[key]
formula = 'HYPERLINK("http://www.yeastgenome.org/cgi-bin/locus.fpl?locus=%s";"%s")' % (
value, value)
link = Formula(formula)
ws.row(row + 1).write(col, link, url_style)
except:
ws.row(row + 1).write(col, value)
# get human readable name from GFF3 file
elif key == "gene_name":
try:
common_name = orf_map[orf_id]['gene']
except (NameError, KeyError) as e:
# default back to using the ORF id
common_name = orf_id
ws.row(row + 1).write(col, common_name)
elif key == "gene_aliases":
try:
aliases = orf_map[orf_id]['Alias']
except (NameError, KeyError) as e:
# if no aliases leave blank
aliases = ""
if aliases == common_name:
aliases = ""
if aliases.startswith(common_name):
aliases = string.replace(aliases, common_name + ',', '')
# also remove URL quoting, if it exists
ws.row(row + 1).write(col, urllib.unquote(aliases))
elif key == "orf_status":
try:
orf_status = orf_map[orf_id]['orf_classification']
except (NameError, KeyError) as e:
orf_status = "NA"
ws.row(row + 1).write(col, orf_status)
elif key == "description":
try:
display = orf_map[orf_id]['display']
except (NameError, KeyError) as e:
display = orf_id
ws.row(row + 1).write(col, urllib.unquote(display),
shrink_col_style)
elif key == "COREscore":
value = inputrow[key]
if float(value) > 0:
ws.row(row + 1).write(col, value, corescore_pos_style)
else:
ws.row(row + 1).write(col, value, corescore_zero_style)
elif key == "LLR":
value = inputrow[key]
if float(value) > 0:
ws.row(row + 1).write(col, value, llk_pos_style)
else:
ws.row(row + 1).write(col, value, llk_zero_style)
elif key == "PAPAprd":
value = inputrow[key]
if float(value) > 0.05:
ws.row(row + 1).write(col, value, rossprd_high_style)
else:
ws.row(row + 1).write(col, value, rossprd_low_style)
elif key == "PAPAfi":
value = inputrow[key]
if float(value) < 0.0:
ws.row(row + 1).write(col, value, rossprd_high_style)
else:
ws.row(row + 1).write(col, value, rossprd_low_style)
else:
# get value at key
value = inputrow[key]
ws.row(row + 1).write(col, value)
ws.row(row + 1).set_style(row_style)
ws.row(row + 1).height_mismatch = 1
ws.row(row + 1).height = 600
row += 1
if not (allhits):
if row >= numhits:
break
# set column widths after everything generated
set_col_width("description", header, ws, 25)
set_col_width("LLR", header, ws, 5)
set_col_width("LLRstart", header, ws, 7)
set_col_width("LLRend", header, ws, 7)
set_col_width("LLRlen", header, ws, 6)
set_col_width("NLLR", header, ws, 6)
set_col_width("COREscore", header, ws, 8)
set_col_width("COREstart", header, ws, 8)
set_col_width("CORElen", header, ws, 8)
set_col_width("COREend", header, ws, 6)
for colname in ["PRDstart", "PRDend", "PRDend", "PRDlen", "PROTlen"]:
set_col_width(colname, header, ws, 8)
def produce_summary(self, cr, uid, travel, context=None):
number_format = _('#,##0.00 [$$-C0C];-#,##0.00 [$$-C0C]')
total_fnt = Font()
total_fnt.name = 'Calibri'
total_fnt.bold = True
total_fnt.height = 16 * 20 # font size 12
total_cell_l_style = XFStyle()
total_cell_l_style.alignment = Column.title_aln
total_cell_l_style.borders = Borders()
total_cell_l_style.borders.left = Borders.THICK
total_cell_l_style.borders.right = Borders.HAIR
total_cell_l_style.borders.top = Borders.THICK
total_cell_l_style.borders.bottom = Borders.THICK
total_cell_l_style.pattern = Column.title_ptn
total_cell_l_style.num_format_str = number_format
total_cell_l_style.font = Column.obj_fnt
total_cell_r_style = XFStyle()
total_cell_r_style.alignment = Column.title_aln
total_cell_r_style.borders = Borders()
total_cell_r_style.borders.left = Borders.HAIR
total_cell_r_style.borders.right = Borders.THICK
total_cell_r_style.borders.top = Borders.THICK
total_cell_r_style.borders.bottom = Borders.THICK
total_cell_r_style.pattern = Column.title_ptn
total_cell_r_style.num_format_str = number_format
total_cell_r_style.font = Column.obj_fnt
sub_total_cell_label = Cell(_(u'SOUS-TOTAL'), Column.title_fnt,
Column.title_aln,
total_cell_l_style.borders,
Column.title_ptn)
total_cell_label = Cell(_(u'TOTAL'), total_fnt, Column.title_aln,
total_cell_r_style.borders, Column.title_ptn,
number_format)
journeys = [i for i in travel.journey_ids]
w = Workbook()
ws = w.add_sheet(_('Travel Summary'))
ws.row(2).height = 0x0280
ws.row(3 + len(journeys)).height = 0x0140
ws.row(4 + len(journeys)).height = 0x0180
row = 0
row += 2
# Write headers
for i, col in enumerate(self._excel_columns):
ws.col(i).width = col.width
ws.write(row, i, col.text, col.style)
row += 1
for i, obj in enumerate(journeys):
ws.write(row + i, 0, i + 1, self._excel_columns[0].obj_style)
for j in xrange(1, len(self._excel_columns)):
ws.write(row + i, j, self._excel_columns[j].func(obj),
self._excel_columns[j].obj_style)
row += len(journeys)
rate_index = next(i for i, x in enumerate(self._excel_columns)
if x.text == _('TICKET RATE')) - 1
cost_index = next(i for i, x in enumerate(self._excel_columns)
if x.text == _('COSTS')) - 1
total_index = next(i for i, x in enumerate(self._excel_columns)
if x.text == _('TOTAL')) - 1
# Sub total label
ws.write_merge(row, row, 0, rate_index, sub_total_cell_label.text,
sub_total_cell_label.style)
# Sub totals
ws.write(
row, rate_index + 1,
Formula("SUM(%s%d:%s%d)" %
(chr(66 + rate_index), 4, chr(66 + rate_index), row)),
total_cell_l_style)
ws.write(
row, rate_index + 2,
Formula("SUM(%s%d:%s%d)" %
(chr(66 + cost_index), 4, chr(66 + cost_index), row)),
total_cell_r_style)
total_top_underline_style = XFStyle()
total_top_underline_style.borders = Borders()
total_top_underline_style.borders.top = Borders.THICK
total_top_underline_style.font = Column.obj_fnt
# Draw a line above total to close box
ws.write(row, total_index + 1, "", total_top_underline_style)
row += 1
# Total label
ws.write_merge(row, row, 0, rate_index, total_cell_label.text,
total_cell_label.style)
# Total
ws.write_merge(
row, row, rate_index + 1, total_index,
Formula("%s%d+%s%d" %
(chr(66 + rate_index), row, chr(66 + cost_index), row)),
total_cell_label.style)
return w
def data_amend_report(workbook, gl, ibm, nc_sp, pvs_sp, fm_sp, ibm_filtered):
# Sheet 1
sheet = workbook.get_sheet(0)
# Download hyperlink:
bigfont = easyxf('font: bold 1,height 360;') # Font height is in "twips" (1/20 of a point)
sheet.write(1, 0, Formula('HYPERLINK("{}")'.format(settings.IBM_DATA_AMEND_URI)), bigfont)
# Padded zeroes number format
pad2, pad3, pad4 = XFStyle(), XFStyle(), XFStyle()
pad2.num_format_str = '00'
pad3.num_format_str = '000'
pad4.num_format_str = '0000'
current_row = 3
code_id = ''
for row, data in enumerate(gl, current_row):
# Only insert GLPivDownload objects with a matching IBMData object.
if ibm.filter(ibmIdentifier=data.codeID).exists():
# We have to aggregate all the GLPivotDownload objects with
# matching codeID values, and insert one row with total
# ytdActual and fyBudget values.
if code_id != data.codeID: # Reached the next codeID value.
current_row += 1 # Advance one row.
code_id = data.codeID
i = ibm.get(ibmIdentifier=data.codeID)
sheet.write(current_row, 0, data.codeID)
sheet.write(current_row, 1, int(data.costCentre), pad3)
sheet.write(current_row, 2, data.account, pad2)
sheet.write(current_row, 3, data.service, pad2)
sheet.write(current_row, 4, data.activity, pad3)
try:
sheet.write(current_row, 5, int(data.project), pad4)
except ValueError:
sheet.write(current_row, 5, data.project, pad4)
try:
sheet.write(current_row, 6, int(data.job), pad3)
except ValueError:
sheet.write(current_row, 6, data.job, pad3)
sheet.write(current_row, 7, data.jobName)
sheet.write(current_row, 8, data.activityName)
sheet.write(current_row, 9, data.projNameNo)
sheet.write(current_row, 10, i.budgetArea)
sheet.write(current_row, 11, i.projectSponsor)
sheet.write(current_row, 14, i.regionalSpecificInfo)
sheet.write(current_row, 15, i.servicePriorityID)
sheet.write(current_row, 18, i.annualWPInfo)
sheet.write(current_row, 19, data.mPRACategory)
ytd = gl.filter(codeID=code_id).aggregate(Sum('ytdActual'))
fy = gl.filter(codeID=code_id).aggregate(Sum('fybudget'))
sheet.write(current_row, 20, ytd['ytdActual__sum'])
sheet.write(current_row, 21, fy['fybudget__sum'])
# Insert the footer row formulae and '#END OF INPUT'
sheet.write(current_row+2, 0, '#END OF INPUT')
sheet.write(current_row+2, 20, Formula('SUM({}:{})'.format(cellname(4, 20), cellname(current_row, 20))))
sheet.write(current_row+2, 21, Formula('SUM({}:{})'.format(cellname(4, 21), cellname(current_row, 21))))
# Sheet 2 - Service priority checkboxes.
sheet = workbook.get_sheet(1)
write_service_priorities(sheet, nc_sp, pvs_sp, fm_sp)
# Sheet 3: Budget area & project sponsor lookup data.
# This is a list of unique budgetArea and projectSponsor values, written in
# as reference data for macros.
sheet = workbook.get_sheet(2)
write_budget_areas(sheet, ibm_filtered)
write_project_sponsors(sheet, ibm_filtered)
# Select the first sheet.
sheet = workbook.get_sheet(0)
table = workbook.add_sheet('wahson', cell_overwrite_ok=True)
table.col(0).width = 5000
table.col(1).width = 10000
table.col(2).width = 7000
style = xlwt.XFStyle() # 初始化样式
font = xlwt.Font() # 为样式创建字体
font.bold = True
style.font = font # 为样式设置字体
table.write(0, 0, '型号', style)
table.write(0, 1, '型号产品链接', style)
table.write(0, 2, '类别', style)
link_style = easyxf('font: underline single')
i = 1
for item in items:
table.write(i, 0, item['name'])
table.write(
i, 1,
Formula('HYPERLINK("' + item['url'] + '";"' + item['url'] + '")'),
link_style)
table.write(i, 2, item['type'])
i = i + 1
workbook.save("d:/wahson.xls")
time.sleep(5)
browser.close()