#! python3
#multiplicationTable.py - The program will take a command line numerical input, N, and create an NxN matrix in an excel file
import openpyxl, sys
from openpyxl.styles import Font, Style
#Get the input from the commandline
N = int(sys.argv[1])
#Open Workbook
wb = openpyxl.Workbook()
sheet = wb.get_active_sheet()
#create the font objects that make up the outter part of the table and the inner part
fontObj = Font(name='Times New Roman', bold='True')
styleObj = Style(font=fontObj)
#Create array to right out the square
for i in range(1, N + 1):
for j in range(1, N + 1):
sheet.cell(row=i + 1, column=j + 1).value = i * j
#TODO: Write out the "header" portions
for i in range(2, N + 2):
sheet.cell(row=i, column=1).value = i - 1
sheet.cell(row=1, column=i).value = i - 1
#Save and close workbook
wb.save('multiplicationTable.xlsx')
from base.models.enums.proposal_type import ProposalType
from base.models.learning_component_year import LearningComponentYear
from base.models.learning_unit_year import SQL_RECURSIVE_QUERY_EDUCATION_GROUP_TO_CLOSEST_TRAININGS
from osis_common.document import xls_build
XLS_DESCRIPTION = _('Learning units list')
XLS_FILENAME = _('LearningUnitsList')
WORKSHEET_TITLE = _('Learning units list')
TRANSFORMATION_AND_MODIFICATION_COLOR = Color('808000')
TRANSFORMATION_COLOR = Color('ff6600')
SUPPRESSION_COLOR = Color('ff0000')
MODIFICATION_COLOR = Color('0000ff')
CREATION_COLOR = Color('008000')
DEFAULT_LEGEND_STYLES = {
Style(fill=PatternFill(patternType='solid', fgColor=CREATION_COLOR)): ['A2'],
Style(fill=PatternFill(patternType='solid', fgColor=MODIFICATION_COLOR)): ['A3'],
Style(fill=PatternFill(patternType='solid', fgColor=SUPPRESSION_COLOR)): ['A4'],
Style(fill=PatternFill(patternType='solid', fgColor=TRANSFORMATION_COLOR)): ['A5'],
Style(fill=PatternFill(patternType='solid', fgColor=TRANSFORMATION_AND_MODIFICATION_COLOR)): ['A6'],
}
BOLD_FONT = Font(bold=True)
SPACES = ' '
HEADER_TEACHERS = _('List of teachers')
HEADER_PROGRAMS = _('Programs')
PROPOSAL_LINE_STYLES = {
ProposalType.CREATION.name: Style(font=Font(color=CREATION_COLOR), ),
ProposalType.MODIFICATION.name: Style(font=Font(color=MODIFICATION_COLOR), ),
ProposalType.SUPPRESSION.name: Style(font=Font(color=SUPPRESSION_COLOR), ),
ProposalType.TRANSFORMATION.name: Style(font=Font(color=TRANSFORMATION_COLOR), ),
ProposalType.TRANSFORMATION_AND_MODIFICATION.name: Style(font=Font(color=TRANSFORMATION_AND_MODIFICATION_COLOR), ),
'''
consolidated codes
'''
# setting font
from openpyxl.styles import Font, Style
wb = openpyxl.Workbook()
sheet = wb.get_sheet_by_name('Sheet')
italic24Font = Font(size=24, italic=True)
styleObj = Style(font=italic24Font)
sheet['A1'].style = styleObj
sheet['A1'] = 'Hello world!'
wb.save('styled.xlsx')
# formulas
import openpyxl
wb = openpyxl.Workbook()
sheet = wb.get_active_sheet()
sheet['A1'] = 200
sheet['A2'] = 300
sheet['A3'] = '=SUM(A1:A2)'
wb.save('writeFormula.xlsx')
import openpyxl
wbFormulas = openpyxl.load_workbook('writeFormula.xlsx')
sheet = wbFormulas.get_active_sheet()
sheet['A3'].value # =SUM(A1:A2)
wbDataOnly = openpyxl.load_workbook('writeFormula.xlsx', data_only=True)
sheet = wbDataOnly.get_active_sheet() >>> sheet['A3'].value # 500
# adjusting rows and columns
def render_xlsx(self, outfd, data):
BoldStyle = Style(font=Font(name='Calibri',
size=11,
bold=True,
italic=False,
vertAlign=None,
underline='none',
strike=False,
color='FFFFFFFF'),
fill=PatternFill(fill_type="solid",
start_color='FF000000',
end_color='FF000000'))
RedStyle = Style(font=Font(name='Calibri',
size=11,
bold=False,
italic=False,
vertAlign=None,
underline='none',
strike=False,
color='FF000000'),
border=Border(left=Side(border_style="thick",
color='FF000000'),
right=Side(border_style="thick",
color='FF000000'),
top=Side(border_style="thick",
color='FF000000'),
bottom=Side(border_style="thick",
color='FF000000'),
diagonal=Side(border_style="thick",
color='FF000000'),
diagonal_direction=0,
outline=Side(border_style="thick",
color='FF000000'),
vertical=Side(border_style="thick",
color='FF000000'),
horizontal=Side(border_style="thick",
color='FF000000')),
fill=PatternFill(start_color='FFFF0000',
end_color='FFFF0000',
fill_type='solid'))
GreenStyle = Style(font=Font(name='Calibri',
size=11,
bold=False,
italic=False,
vertAlign=None,
underline='none',
strike=False,
color='FF000000'),
fill=PatternFill(start_color="FF00FF00",
end_color="FF00FF00",
fill_type="solid"))
wb = Workbook(optimized_write=True)
ws = wb.create_sheet()
ws.title = "Psxview Output"
ws.append([
"Offset (P)", "Name", "PID", "pslist", "psscan", "thrdproc",
"pspcid", "csrss", "session", "deskthrd", "Exit Time"
])
total = 1
for offset, process, ps_sources in data:
incsrss = ps_sources['csrss'].has_key(offset)
insession = ps_sources['session'].has_key(offset)
indesktop = ps_sources['deskthrd'].has_key(offset)
inpspcid = ps_sources['pspcid'].has_key(offset)
inpslist = ps_sources['pslist'].has_key(offset)
inthread = ps_sources['thrdproc'].has_key(offset)
if self._config.APPLY_RULES:
if not incsrss:
if str(process.ImageFileName).lower() in [
"system", "smss.exe", "csrss.exe"
]:
incsrss = "Okay"
elif process.ExitTime > 0:
incsrss = "Okay"
if not insession:
if str(process.ImageFileName).lower() in [
"system", "smss.exe"
]:
insession = "Okay"
elif process.ExitTime > 0:
insession = "Okay"
if not indesktop:
if str(process.ImageFileName).lower() in [
"system", "smss.exe"
]:
indesktop = "Okay"
elif process.ExitTime > 0:
indesktop = "Okay"
if not inpspcid:
if process.ExitTime > 0:
inpspcid = "Okay"
if not inpslist:
if process.ExitTime > 0:
inpslist = "Okay"
if not inthread:
if process.ExitTime > 0:
inthread = "Okay"
ws.append([
hex(offset),
str(
utils.remove_unprintable(str(process.ImageFileName))
or ""),
str(process.UniqueProcessId),
str(inpslist),
str(ps_sources['psscan'].has_key(offset)),
str(inthread),
str(inpspcid),
str(incsrss),
str(insession),
str(indesktop),
str(process.ExitTime or '')
])
total += 1
wb.save(filename=self._config.OUTPUT_FILE)
wb = load_workbook(filename=self._config.OUTPUT_FILE)
ws = wb.get_sheet_by_name(name="Psxview Output")
for col in xrange(1, 12):
ws.cell("{0}{1}".format(get_column_letter(col),
1)).style = BoldStyle
for row in xrange(2, total + 1):
for col in xrange(4, 11):
if ws.cell("{0}{1}".format(get_column_letter(col),
row)).value == "False":
ws.cell("{0}{1}".format(get_column_letter(col),
row)).style = RedStyle
else:
ws.cell("{0}{1}".format(get_column_letter(col),
row)).style = GreenStyle
wb.save(filename=self._config.OUTPUT_FILE)
def addExpense():
global x, keyword, items
x += 1
fontCur = Font(name='Cooper Black', bold=False)
current = Style(font=fontCur)
Ex1 = random.randint(0, 4)
Ex2 = random.randint(5, 9)
Ex3 = random.randint(10, len(categories) - 3)
Name1 = random.randint(1, x - 1)
Name2 = random.randint(1, x - 1)
Name3 = random.randint(1, x - 1)
if x < 5:
print('What\'s the name of your expense? (Ex: eggs, clothes, vodka)')
else:
print('What\'s the name of your expense? (Ex: ' +
month['A' + str(Name1)].value + ', ' +
month['A' + str(Name2)].value + ', ' +
month['A' + str(Name3)].value + ')')
exp = input()
if exp.lower().startswith('correct'):
print('Correction initiated')
shortExit()
sys.exit()
if exp.lower() == 'no' or exp.lower() == 'done':
shortExit()
sys.exit()
if exp.lower().endswith('cashback'):
exp.split()
exp = exp[0]
print('How much cash back?')
autoAdd('cashback', 'Bank', float(input()))
items += 1
month['A' + str(x)] = exp
keyword = exp
val = checkCate()
print()
if val != '':
month['B' + str(x)] = val.title()
else:
print('Under which category? (Ex: ' + str(categories[Ex1]) + ', ' +
str(categories[Ex2]) + ', ' + str(categories[Ex3]) + ')')
print('(* at end to assign)')
cat = input()
if cat.endswith('*'):
addToCate()
cat = cat.strip('*')
print()
if cat == 'food':
print('Was it food from the grocery store? [Y or N]')
catF = input()
if catF == 'Y' or catF == 'y':
month['B' + str(x)] = 'Groceries'
else:
month['B' + str(x)] = 'Res'
elif cat == 'cvs':
print('Was it health related? (if for supplies then N) [Y or N]')
catC = input()
if catC == 'Y' or catC == 'y':
month['B' + str(x)] = 'Health'
else:
month['B' + str(x)] = 'Supplies'
else:
month['B' + str(x)] = cat.title()
print('Additional notes?')
if dNum < 10:
print('(if from previous month, put \'qq\' here)')
sub = input()
month['C' + str(x)] = sub
print()
while True:
print('Total cost? (Enter as x.xx)')
cost = input()
try:
month['D' + str(x)] = float(cost)
except ValueError:
print('Be careful! You have to enter a number here')
else:
break
if ['B' + str(x)] == 'Other Food' and float(cost) > float(25):
print('Did you pay for others\' food?')
pay = input()
if pay.lower().startswith('y'):
month['D' + str(x)].fill = PatternFill(start_color='6DC066',
fill_type='solid')
print()
for i in range(0, 5):
month[get_column_letter(i + 1) + str(x)].style = current
def categoryTotal():
ml = len(categories)
for g in range(1, mNum + 1):
mez = wb.get_sheet_by_name(TM[g])
mz = g + 1
z = mez.max_row
for i in range(ml - 2):
Sum[get_column_letter(mz + 8) + str(i + 2)] = 0
Sum['I' + str(i + 2)] = '=SUM(J' + str(
i + 2) + ':' + get_column_letter(mNum + 9) + str(i + 2) + ')'
y = i + 1
if y == 1: #Rent
ct = [
'Rent',
'Lease',
]
if y == 2: #Utilities
ct = [
'Util', 'Utility', 'Utilities', 'Water Bill',
'Electricity', 'Water', 'Bill'
]
if y == 3: #Phone
ct = ['Phone Bill', 'Phone']
if y == 4: #Apparel
ct = ['Apparel', 'Clothes', 'Shoes']
if y == 5: #Supplies
ct = ['Supplies', 'Office', 'Bathroom']
if y == 6: #Tech
ct = ['Technology', 'Tech'] #...
if y == 7: #Service
ct = ['Service', 'Services', 'Dry Cleaning']
if y == 8: #Health/Gym
ct = [
'Health', 'Gym', 'Medical', 'Med', 'Gnc', 'Vitamins',
'Health/Gym'
]
if y == 9: #Haircut
ct = ['Haircut', 'Hair', 'Fade', 'Cut', 'Lineup']
if y == 10: #Groceries
ct = ['Groceries', 'Grocery']
if y == 11: #Other Food
ct = [
'Other Food', 'Res', 'Snacks', 'Candy', 'Beer', 'Wine',
'Alcohol', 'Liquor', 'Coffee', 'Drink'
]
if y == 12: #Gas and Parking
ct = ['Gas', 'Parking', 'Gas & Parking']
if y == 13: #Insurance
ct = ['Insurance', 'Ins']
if y == 14: #Credit Card
ct = [
'Bank & Credit Card', 'Credit', 'Credit Card', 'Bank',
'Interest', 'Cc'
]
if y == 15: #Student Loans
ct = [
'Student', 'Loans', 'Student Loans', 'Student Loan',
'FAFSA'
]
if y == 16: #Vehicle Expense
ct = ['Car', 'Motorcycle', 'Engine']
if y == 17: #Entertainment
ct = ['Entertainment', 'Movies', 'Sport', 'Sports', 'Game']
if y == 18: #Subscriptions
ct = ['Sub', 'Subscriptions', 'Xxx', 'Recurring', 'Spotify']
if y == 19: #Travel
ct = ['Travel', 'Flight', 'Train', 'Plane', 'Airplane', 'Bus']
if y == 20: #Public Transportation
ct = [
'Public Transportation', 'Transportation', 'Uber', 'Marta',
'Cab'
]
if y == 21: #Seasonal/Special
ct = [
'Special', 'Seasonal', 'Graduation', 'Spring Break',
'Recital', 'Gift', 'Gifts'
]
for j in range(
2, (z + 1)
): #Updates all active categories #masterCate[categories[i]] instead of ct
if mez['B' +
str(j)].value in ct and mez['C' + str(j)].value != 'qq':
add = float(Sum[get_column_letter(mz + 8) +
str(i + 2)].value) + float(
mez['D' + str(j)].value)
Sum[get_column_letter(mz + 8) + str(i + 2)] = float(add)
if g != 1 and mez['B' + str(j)].value in ct and mez[
'C' + str(j)].value == 'qq':
add = float(Sum[get_column_letter(mz + 7) +
str(i + 2)].value) + float(
mez['D' + str(j)].value)
Sum[get_column_letter(mz + 7) + str(i + 2)] = float(add)
if mNum > 3:
Sum[get_column_letter(mNum + 10) +
str(i + 2)] = '=ROUND(AVERAGE(J' + str(
i + 2) + ':' + get_column_letter(mNum +
8) + str(i +
2) + '),2)'
Sum[get_column_letter(mNum + 11) +
str(i + 2)] = '=ROUND(AVERAGE(' + get_column_letter(
mNum + 6) + str(i + 2) + ':' + get_column_letter(
mNum + 8) + str(i + 2) + '),2)'
else:
Sum[get_column_letter(mNum + 10) + str(i + 2)] = '=\'[' + str(
int(year) -
1) + ' Monthly Expenses.xlsx]Summary\'!$V$' + str(i + 2)
Sum[get_column_letter(mNum + 11) + str(i + 2)] = '=\'[' + str(
int(year) -
1) + ' Monthly Expenses.xlsx]Summary\'!$W$' + str(i + 2)
#Below updates the 'other' category and total for month
Sum[get_column_letter(mz + 8) +
str(ml - 1)] = '=' + TM[g] + '!E' + str(
z) + '-SUM(Summary!' + get_column_letter(
mz + 8) + '2:' + get_column_letter(mz + 8) + str(ml -
2) + ')'
Sum[get_column_letter(mz + 8) +
str(ml + 1)] = '=SUM(' + get_column_letter(
mz + 8) + '2:' + get_column_letter(mz + 8) + str(ml - 1) + ')'
# Updating the year total and average
Sum['I' + str(ml - 1)] = '=SUM(J' + str(ml - 1) + ':' + get_column_letter(
mNum + 9) + str(ml - 1) + ')'
if mNum > 3:
Sum[get_column_letter(mNum + 10) +
str(ml + 1)] = '=ROUND(AVERAGE(J' + str(
ml + 1) + ':' + get_column_letter(mNum + 8) + str(ml +
1) + '),2)'
Sum[get_column_letter(mNum + 11) +
str(ml +
1)] = '=ROUND(AVERAGE(' + get_column_letter(mNum + 6) + str(
ml + 1) + ':' + get_column_letter(mNum +
8) + str(ml + 1) + '),2)'
else:
Sum[get_column_letter(mNum + 10) + str(
ml +
1)] = '=\'/Users/alexrogers823/Documents/Python Projects/[' + str(
int(year) -
1) + ' Monthly Expenses.xlsx]Summary\'!$V$' + str(ml + 1)
Sum[get_column_letter(mNum + 11) + str(
ml +
1)] = '=\'/Users/alexrogers823/Documents/Python Projects/[' + str(
int(year) -
1) + ' Monthly Expenses.xlsx]Summary\'!$W$' + str(ml + 1)
for r in range(len(categories)):
if Sum[get_column_letter(mNum + 12) +
str(r + 2)].value != None and Sum[
get_column_letter(mNum + 9) + str(r + 2)].value > Sum[
get_column_letter(mNum + 12) + str(r + 2)].value:
Sum[get_column_letter(mNum + 12) + str(r + 2)].style = Style(
font=Font(name='Calibri', color='FF0000'))
from base.business.learning_unit import CMS_LABEL_SPECIFICATIONS, get_achievements_group_by_language
from base.business.learning_unit_xls import annotate_qs
from base.business.xls import get_name_or_username
from base.models.person import get_user_interface_language
from base.models.teaching_material import TeachingMaterial
from base.views.learning_unit import get_specifications_context
from cms.enums.entity_name import LEARNING_UNIT_YEAR
from cms.models.text_label import TextLabel
from cms.models.translated_text import TranslatedText
from cms.models.translated_text_label import TranslatedTextLabel
from osis_common.document import xls_build
XLS_DESCRIPTION = _('Learning units list')
XLS_FILENAME = _('LearningUnitsList')
WORKSHEET_TITLE = _('Learning units list')
WRAP_TEXT_STYLE = Style(alignment=Alignment(wrapText=True, vertical="top"), )
CMS_ALLOWED_TAGS = []
def create_xls_educational_information_and_specifications(
user, learning_units, request):
titles = _get_titles()
working_sheet_data = prepare_xls_educational_information_and_specifications(
learning_units, request)
parameters = {
xls_build.DESCRIPTION: XLS_DESCRIPTION,
xls_build.USER: get_name_or_username(user),
xls_build.FILENAME: XLS_FILENAME,
import logging
import os
import PyVetCom
from datetime import date, timedelta
from openpyxl import load_workbook, Workbook
from openpyxl.cell import get_column_letter
from openpyxl.styles import Style, PatternFill, Border, Side, Alignment, Protection, Font
xlsxfile = 'weekly_recon.xlsx'
side_thin = Side(border_style='thin')
side_thick = Side(border_style='thick')
style_default_top = Style(font=Font(size=9),
border=Border(left=side_thin,
right=side_thin,
top=side_thick,
bottom=side_thin))
style_default_bottom = Style(font=Font(size=9),
border=Border(left=side_thin,
right=side_thin,
top=side_thin,
bottom=side_thick))
def setCell(ws, col, row, val, style):
cell = ws["%s%d" % (get_column_letter(col + 0), row)]
cell.value = val
cell.style = style
# coding=utf-8
from openpyxl import Workbook, load_workbook
from openpyxl.styles import Alignment, Style
import re
in_wb = load_workbook(filename='../data/first_term_schedule_v1.xlsx', read_only=True)
out_wb = Workbook()
in_ws = in_wb.active
out_ws = out_wb.active
in_ws.sheet_view.rightToLeft=True
out_ws.sheet_view.rightToLeft=True
pat = r"([\(]*[\w\s*/]+\))"
align_style = Style(alignment=Alignment(wrap_text=True))
ro = 0
for (ri,irow) in enumerate(in_ws.iter_rows('A1:I320')):
for (ci,icell) in enumerate(irow):
if icell.value is not None:
ivalue = icell.value
if ci == 0:
ro += 1
ocell = out_ws.cell(row=ro, column=ci+1)
val = ocell.value
if val is not None:
if type(ivalue) is unicode or ci+1 != 4:
if type(ivalue) is float:
ivalue = int(ivalue)
from openpyxl import Workbook from openpyxl.styles import Font, Color from openpyxl.styles import colors from openpyxl.styles import Style, PatternFill, Border, Side, Alignment, Protection, Font file_output = open(r'stock_number_list.txt','wt') outwb = Workbook() ws1 = outwb.create_sheet(0,u'sh_stock_data') ws2 = outwb.create_sheet(0,u'sz_stock_data') ft = Font(color=colors.RED) s1 = Style(number_format='0%') s2 = Style(number_format='0.00') ws1.cell(row=1,column=1).value=u'股票编号' ws1.cell(row=1,column=2).value=u'股票名称' ws1.cell(row=1,column=3).value=u'涨跌幅' ws1.cell(row=1,column=4).value=u'当前价格' ws2.cell(row=1,column=1).value=u'股票编号' ws2.cell(row=1,column=2).value=u'股票名称' ws2.cell(row=1,column=3).value=u'涨跌幅' ws2.cell(row=1,column=4).value=u'当前价格' ##ws1.write(0, 0, u'股票编号') ##ws1.write(0, 1, u'股票名称') ##ws1.write(0, 2, u'涨跌幅')
def __apply_style_to_cells(ws, color_style, row):
style_enrollment_state = Style(fill=PatternFill(patternType='solid',
fgColor=Color(color_style.lstrip("#"))))
ws.cell(row=row, column=FIRST_COL_LEGEND_ENROLLMENT_STATUS).style = style_enrollment_state
ws.cell(row=row, column=FIRST_COL_LEGEND_ENROLLMENT_STATUS+1).style = style_enrollment_state
def compare(s1, s2, w1n, w2n):
#rowrange = max(s1.get_highest_row(), s2.get_highest_row())
#colrange = max(s1.get_highest_column(), s2.get_highest_column())
#print rowrange,colrange
#eps = 10
rowrange = max(s1.max_row, s2.max_row)
colrange = max(s1.max_column, s2.max_column)
total_cells = rowrange * colrange
fill1_err, fill2_err, fill3_err = 0, 0, 0
diff_fill, unfill = 0, total_cells
#print rowrange,colrange,total_cells
#print col1,col2,col3,val1,val2
#pass
cfill_1 = Style(
fill=PatternFill(patternType='solid', fgColor=Color(coldic[col1])))
cfill_2 = Style(
fill=PatternFill(patternType='solid', fgColor=Color(coldic[col2])))
cfill_3 = Style(
fill=PatternFill(patternType='solid', fgColor=Color(coldic[col3])))
cfill_4 = Style(
fill=PatternFill(patternType='solid', fgColor=Color(coldic['Olive'])))
succ = "Pass"
e = 'Data Matches!'
try:
for i in xrange(1, rowrange + 1):
for j in xrange(1, colrange + 1):
#time.sleep(0.5)
x = s1.cell(row=i, column=j)
y = s2.cell(row=i, column=j)
xfor_code, yfor_code = x.number_format, y.number_format
a = x.value
b = y.value
nt = None
dt = datetime.datetime.today()
tlist = [type(int()), type(float()), type(long()), type(nt)]
numlist = [type(int()), type(float()), type(long())]
#xfor_code = s2.cell(row=i,column=j).number_format
if (type(a) not in numlist or x.number_format == '@') and (
type(b) not in numlist or y.number_format == '@'):
a, b = unicode(a).lower(), unicode(b).lower()
#print x.number_format+str('->')+y.number_format
#print a + str('->')+ b
#print str(type(a)) +str('->')+str(type(b))
if a != b:
diff_fill += 1
#unfill -= 1
#print a,b
#print type(a),type(b)
succ = "Fail"
e = "Data Mismatches"
comtxt = None
if type(a) in numlist and (type(b) == type(nt)
or type(b) == type(unicode())):
comtxt = str(w1n) + ": " + unicode(a) + str(
"\ndiff: ") + unicode(a)
elif type(b) in numlist and (type(a) == type(nt) or type(a)
== type(unicode())):
comtxt = str(w1n) + ": " + unicode(a) + str(
"\ndiff: ") + unicode(b)
elif type(a) in numlist and type(b) in numlist:
diff_val = float(abs(a - b))
diff_per = diff_val / abs(a) * 100 if a != 0 else b
if diff_per <= float(eps):
continue
#y.style = cfill_1 if diff_per <= float(val1) else cfill_2 if diff_per <= float(val2) else cfill_3
if diff_per <= float(val1):
y.style = cfill_1
fill1_err += 1
elif diff_per <= float(val2):
y.style = cfill_2
fill2_err += 1
else:
y.style = cfill_3
fill3_err += 1
if a < b:
comtxt = str(w1n) + ": " + unicode(a) + str(
"\ndiff: ") + unicode(b - a) + str(
"\n") + unicode(round(
diff_per, 4)) + unicode(" % inc. ")
elif a > b:
comtxt = str(w1n) + ": " + unicode(a) + str(
"\ndiff: ") + unicode(b - a) + str(
"\n") + unicode(round(
diff_per, 4)) + unicode(" % dec. ")
#comtxt = str(w1n)+": " + unicode(a)+ str("\ndiff: ")+ unicode(b-a)+ str("\ndiff %: ")+ unicode(diff_per)
elif type(a) == type(unicode()) and type(b) == type(
unicode()):
comtxt = str(w1n) + ": " + unicode(a)
y.style = cfill_4
fill3_err += colrange
#unfill -= colrange-1
comment = Comment(comtxt, w2n)
#y.style = cfill
y.comment = comment
y.number_format = yfor_code
else:
comtxt = None
print "nik"
print fill1_err, fill2_err, fill3_err, total_cells, unfill, diff_fill
except KeyboardInterrupt:
succ = "Abort"
e = "KeyboardInterrupt"
except Exception as e:
succ = "Abort"
#print [succ,e]
return [succ, e]
def do_excel(contracts, day_contract_profits, day_contracts, day_accountID,
day_times):
excel_file = u"log/test.xlsx"
report_wb = Workbook()
#在第一个sheet记录基本情况
report_ws_base = report_wb.worksheets[0]
report_ws_base.title = u"基本情况"
#基本情况
days = day_accountID.keys()
days.sort()
days_num = len(days)
index = 1
report_ws_base.cell(row=index, column=1).value = "测试机制：根据交易记录得到每日净值"
index += 1
report_ws_base.cell(
row=index, column=1).value = "测试数据时间范围：%u-%u-%u—%u-%u-%u" % (
days[0] / 10000, days[0] / 100 % 100, days[0] % 100,
days[days_num - 1] / 10000, days[days_num - 1] / 100 % 100,
days[days_num - 1] % 100)
#记录每日操作账号
index += 2
report_ws_base.cell(row=index, column=1).value = "操作账号情况："
index += 1
report_ws_base.cell(row=index, column=1).value = "日期"
report_ws_base.cell(row=index, column=2).value = "账号"
index += 1
for day in days:
new_style = Style(alignment=alignment.Alignment(horizontal='left'),
font=fonts.Font(bold=False))
report_ws_base.cell(row=index, column=1).value = day
report_ws_base.cell(row=index, column=1).style = new_style
set_accountID = day_accountID[day]
size = len(set_accountID)
line = ""
for i in xrange(0, size):
if 0 == i:
line += "%s" % set_accountID[i]
else:
line += ";%s" % set_accountID[i]
report_ws_base.cell(row=index, column=2).value = line
report_ws_base.cell(row=index, column=2).style = new_style
index += 1
#记录每日操作合约
index += 1
report_ws_base.cell(row=index, column=1).value = "操作合约情况："
index += 1
report_ws_base.cell(row=index, column=1).value = "日期"
report_ws_base.cell(row=index, column=2).value = "合约"
index += 1
days = day_contracts.keys()
days.sort()
for day in days:
report_ws_base.cell(row=index, column=1).value = day
report_ws_base.cell(row=index, column=1).style = new_style
set_contracts = day_contracts[day]
size = len(set_contracts)
line = ""
for i in xrange(0, size):
if 0 == i:
line += "%s" % set_contracts[i]
else:
line += ";%s" % set_contracts[i]
report_ws_base.cell(row=index, column=2).value = line
report_ws_base.cell(row=index, column=2).style = new_style
index += 1
#记录每日交易次数
index += 1
report_ws_base.cell(row=index, column=1).value = "每日交易次数："
index += 1
report_ws_base.cell(row=index, column=1).value = "日期"
report_ws_base.cell(row=index, column=2).value = "交易次数"
index += 1
days = day_times.keys()
days.sort()
for day in days:
report_ws_base.cell(row=index, column=1).value = day
report_ws_base.cell(row=index, column=1).style = new_style
report_ws_base.cell(row=index, column=2).value = day_times[day]
report_ws_base.cell(row=index, column=2).style = new_style
index += 1
#在第二个sheet记录每日净值
report_ws_profit = report_wb.create_sheet()
report_ws_profit.title = u"每日净值"
index_row = 1
index_column = 1
report_ws_profit.cell(row=index_row, column=index_column).value = "日期"
index_column += 1
for contract in contracts:
report_ws_profit.cell(row=index_row,
column=index_column).value = contract
index_column += 1
report_ws_profit.cell(row=index_row, column=index_column).value = "总净值"
index_row += 1
days = day_contract_profits.keys()
days.sort()
for day in days:
index_column = 1
report_ws_profit.cell(row=index_row, column=index_column).value = day
map_profit = day_contract_profits[day]
index_column += 1
total_profit = 0
for contract in contracts:
if map_profit.has_key(contract):
report_ws_profit.cell(
row=index_row,
column=index_column).value = map_profit[contract]
total_profit += map_profit[contract]
else:
report_ws_profit.cell(row=index_row,
column=index_column).value = 0
index_column += 1
report_ws_profit.cell(row=index_row,
column=index_column).value = total_profit
index_row += 1
report_wb.save(excel_file)
