def print_account_totals(self, _xs, xlwt, ws, row_start_account,
row_position, current_account, _p):
cell_format = _xs['bold'] + _xs['fill'] + \
_xs['borders_all'] + _xs['wrap'] + _xs['top']
cell_style = xlwt.easyxf(cell_format)
cell_style_decimal = xlwt.easyxf(
cell_format + _xs['right'],
num_format_str=report_xls.decimal_format)
c_specs = [
('acc_title', 2, 0, 'text', current_account.name),
('code', 1, 0, 'text', current_account.code),
]
for column in range(3, 7):
# in case of one single comparison, the column 6 will contain
# percentages
if (_p.comparison_mode == 'single' and column == 6):
total_diff = rowcol_to_cell(row_position, column - 1)
total_balance = rowcol_to_cell(row_position, column - 2)
account_formula = 'Round(' + total_diff + \
'/' + total_balance + '*100;0)'
else:
account_start = rowcol_to_cell(row_start_account, column)
account_end = rowcol_to_cell(row_position - 1, column)
account_formula = 'Round(SUM(' + \
account_start + ':' + account_end + ');2)'
c_specs += [('total%s' % column, 1, 0, 'text', None,
account_formula, None, cell_style_decimal)]
row_data = self.xls_row_template(c_specs, [x[0] for x in c_specs])
row_position = self.xls_write_row(
ws, row_position, row_data, cell_style)
return row_position + 1
def writetoxls(list,fn):
wbook = xlwt.Workbook()
wsheet = wbook.add_sheet('sheet1')
cellxf_g = xlwt.easyxf('font: color green, bold true')
cellxf_r = xlwt.easyxf('font: color red, bold true')
cellxf_b = xlwt.easyxf("font: bold false")
for i in range(len(list)):
wsheet.write(i,0,xlwt.Formula('HYPERLINK("%s";"%s")'%(list[i][6].value,list[i][0].value)))
wsheet.write(i,1,list[i][1].value.decode('utf-8'))
wsheet.write(i,2,list[i][2].value)
wsheet.write(i,3,list[i][3].value.decode('utf-8'))
wsheet.write(i,4,list[i][4].value.decode('utf-8'))
wsheet.write(i,5,list[i][5].value)
wsheet.write(i,6,list[i][6].value.decode('utf-8'))
for j in range(7,len(list[i]),2):
if list[i][j+1].value > list[i][2].value:
cellxf = cellxf_g
elif list[i][j+1].value < list[i][2].value:
cellxf = cellxf_r
else:
cellxf = cellxf_b
wsheet.write(i,j,list[i][j].value,cellxf)
wsheet.write(i,j+1,list[i][j+1].value,cellxf)
wsheet.col(0).width = 13000
wsheet.col(2).width = 3000
wsheet.col(6).hidden = True
wbook.save(fn)
def render(self, request):
result = StringIO.StringIO()
header = dict(enumerate(get_flattened_field_names(self.fields)))
count = len(header)
book = xlwt.Workbook()
sheet = book.add_sheet('new')
styles = {
datetime.date: xlwt.easyxf(num_format_str='M/D/YY'),
int: xlwt.easyxf(num_format_str='#,##0'),
float: xlwt.easyxf(num_format_str='#,##0.00000'),
datetime.datetime: xlwt.easyxf(num_format_str='M/D/YY h:mm'),
unicode: xlwt.easyxf(),
}
styles['default'] = styles[unicode]
for i in range(0, count):
sheet.write(0, i, header[i])
for row_index, row_contents in enumerate(get_flattened_data(self.construct())):
for i in range(0, count):
val = row_contents.get(header[i], "")
val, key = (val, type(val)) if styles.has_key(type(val)) else detect_style(val) or (val, 'default')
sheet.write(row_index + 1, i, val, styles[key])
book.save(result)
return result.getvalue()
def show_color(sheet):
colNum = 6
width = 5000
height = 500
colors = ['aqua','black','blue','blue_gray','bright_green','brown','coral','cyan_ega','dark_blue','dark_blue_ega','dark_green','dark_green_ega','dark_purple','dark_red',
'dark_red_ega','dark_teal','dark_yellow','gold','gray_ega','gray25','gray40','gray50','gray80','green','ice_blue','indigo','ivory','lavender',
'light_blue','light_green','light_orange','light_turquoise','light_yellow','lime','magenta_ega','ocean_blue','olive_ega','olive_green','orange','pale_blue','periwinkle','pink',
'plum','purple_ega','red','rose','sea_green','silver_ega','sky_blue','tan','teal','teal_ega','turquoise','violet','white','yellow']
for colorIndex in range(len(colors)):
rowIndex = colorIndex / colNum
colIndex = colorIndex - rowIndex*colNum
sheet.col(colIndex).width = width
sheet.row(rowIndex).set_style(easyxf('font:height %s;'%height))
color = colors[colorIndex]
whiteStyle = easyxf('pattern:pattern solid, fore_colour %s;'
'align: vertical center, horizontal center;'
'font: bold true, colour white;' % color)
blackStyle = easyxf('pattern:pattern solid, fore_colour %s;'
'align: vertical center, horizontal center;'
'font: bold true, colour black;' % color)
if color == 'black':
sheet.write(rowIndex, colIndex, color, style = whiteStyle)
else:
sheet.write(rowIndex, colIndex, color, style = blackStyle)
def export_xlwt(model, values_list, fields):
import xlwt
from datetime import datetime, date
modelname = model._meta.verbose_name_plural.lower()
book = xlwt.Workbook(encoding='utf8')
sheet = book.add_sheet(modelname)
default_style = xlwt.Style.default_style
datetime_style = xlwt.easyxf(num_format_str='dd/mm/yyyy hh:mm')
date_style = xlwt.easyxf(num_format_str='dd/mm/yyyy')
for j, f in enumerate(fields):
sheet.write(0, j, fields[j])
for row, rowdata in enumerate(values_list):
for col, val in enumerate(rowdata):
if isinstance(val, datetime):
style = datetime_style
elif isinstance(val, date):
style = date_style
else:
style = default_style
sheet.write(row + 1, col, val, style=style)
response = HttpResponse(mimetype='application/vnd.ms-excel')
response['Content-Disposition'] = 'attachment; filename=%s.xls' % modelname
book.save(response)
return response
def write_data_1columnpertimestep(xlSheet, data, ts, startdate=None, format_string=''):
tmp = data.copy()
if numpy.ma.isMA(tmp): tmp = numpy.ma.filled(tmp,float(c.missing_value))
xlCol = 0
# write the data to the xl file
xlSheet.write(1,xlCol,'Day')
nrows = numpy.shape(tmp)[0]
ncols = numpy.shape(tmp)[1]
if startdate is None:
for j in range(nrows+1):
xlSheet.write(j+2,xlCol,j)
else:
d_xf = xlwt.easyxf(num_format_str='dd/mm/yyyy')
for j in range(nrows):
d = startdate + datetime.timedelta(days=j)
xlSheet.write(j+2,xlCol,d,d_xf)
xlCol = xlCol + 1
if len(format_string)!=0:
d_xf = xlwt.easyxf(num_format_str=format_string)
else:
d_xf = xlwt.easyxf()
for m in range(1,ncols+1):
xlSheet.write(1,xlCol,float(m)*ts/60)
for j in range(nrows):
xlSheet.write(j+2,xlCol,float(tmp[j,m-1]),d_xf)
xlCol = xlCol + 1
def visitor_excel(request):
book = xlwt.Workbook(encoding='utf8')
sheet = book.add_sheet('untitled')
default_style = xlwt.Style.default_style
datetime_style = xlwt.easyxf(num_format_str='mm/dd/yyyy hh:mm')
date_style = xlwt.easyxf(num_format_str='mm/dd/yyyy')
values_list = Visitor.objects.all().values_list()
for row, rowdata in enumerate(values_list):
for col, val in enumerate(rowdata):
if isinstance(val, datetime):
style = datetime_style
val=val.replace(tzinfo=None)
elif isinstance(val, date):
style = date_style
else:
style = default_style
sheet.write(row, col, val, style=style)
response = HttpResponse(mimetype='application/vnd.ms-excel')
response['Content-Disposition'] = 'attachment; filename=visitors.xls'
book.save(response)
return response
def get_xls_export(self, context):
results = self.get_results(context)
output = StringIO.StringIO()
export_header = (self.request.GET.get('export_xls_header', 'off') == 'on')
model_name = self.opts.verbose_name
book = xlwt.Workbook(encoding='utf8')
sheet = book.add_sheet(u"%s %s" % (_(u'Sheet'), force_unicode(model_name)))
styles = {'datetime': xlwt.easyxf(num_format_str='yyyy-mm-dd hh:mm:ss'),
'date': xlwt.easyxf(num_format_str='yyyy-mm-dd'),
'time': xlwt.easyxf(num_format_str='hh:mm:ss'),
'header': xlwt.easyxf('font: name Times New Roman, color-index red, bold on', num_format_str='#,##0.00'),
'default': xlwt.Style.default_style}
datas = [row.values() for row in results]
if export_header:
datas.insert(0, results[0].keys())
for rowx, row in enumerate(datas):
for colx, value in enumerate(row):
if export_header and rowx == 0:
cell_style = styles['header']
else:
if isinstance(value, datetime.datetime):
cell_style = styles['datetime']
elif isinstance(value, datetime.date):
cell_style = styles['date']
elif isinstance(value, datetime.time):
cell_style = styles['time']
else:
cell_style = styles['default']
sheet.write(rowx, colx, value, style=cell_style)
book.save(output)
output.seek(0)
return output.getvalue()
def add_to_excel(self, state):
""" adds content to the excel file based on the specific statistic type """
import xlwt
answers = state['answers']
ws = state['ws']
current_row = state['current_row']
translator = self.getSite().getPortalTranslations()
total, unanswered, per_row_and_choice = self.calculate(self.question, answers)
#define Excel styles
header_style = xlwt.easyxf('font: bold on; align: vert centre')
normal_style = xlwt.easyxf('align: vert centre')
#write cell elements similarly to the zpt-->html output
ws.write(current_row, 1, self.question.title, header_style)
current_row += 1
current_col = 2
for choice in self.question.choices:
ws.write(current_row, current_col, choice, header_style)
current_col += 1
ws.write(current_row, current_col, translator('Not answered'), header_style)
current_row += 1
for row in self.question.rows:
r = self.question.rows.index(row)
ws.write(current_row, 1, row, header_style)
current_col = 2
for statistics in per_row_and_choice[r]:
ws.write(current_row, current_col, '%u (%.2f%%)'
% (statistics[0], round(statistics[1], 2)), normal_style)
current_col += 1
ws.write(current_row, current_col, '%u (%.2f%%)'
% (unanswered[r][0], round(unanswered[r][1], 2)), normal_style)
current_row += 1
state['current_row'] = current_row
def add_toxicities_to_sheet(sheet, imported_rec):
sheet.write_merge(0, 0, 0, 1, 'Effective Concentrations')
sheet.write(1, 0, 'Index', xlwt.easyxf('font: underline on;'))
sheet.write(1, 1, 'Species', xlwt.easyxf('font: underline on;'))
sheet.write(1, 2, 'After 24H', xlwt.easyxf('font: underline on;'))
sheet.write(1, 3, 'After 48H', xlwt.easyxf('font: underline on;'))
sheet.write(1, 4, 'After 96H', xlwt.easyxf('font: underline on;'))
idx = 0
for idx, ec in enumerate([t for t in imported_rec.toxicities
if t.tox_type == 'EC']):
sheet.write(2 + idx, 0, idx)
sheet.write(2 + idx, 1, ec.species)
sheet.write(2 + idx, 2, ec.after_24h)
sheet.write(2 + idx, 3, ec.after_48h)
sheet.write(2 + idx, 4, ec.after_96h)
v_offset = 2 + idx + 2
sheet.write_merge(v_offset, v_offset, 0, 1, 'Lethal Concentrations')
v_offset += 1
sheet.write(v_offset, 0, 'Index', xlwt.easyxf('font: underline on;'))
sheet.write(v_offset, 1, 'Species', xlwt.easyxf('font: underline on;'))
sheet.write(v_offset, 2, 'After 24H', xlwt.easyxf('font: underline on;'))
sheet.write(v_offset, 3, 'After 48H', xlwt.easyxf('font: underline on;'))
sheet.write(v_offset, 4, 'After 96H', xlwt.easyxf('font: underline on;'))
v_offset += 1
for idx, lc in enumerate([t for t in imported_rec.toxicities
if t.tox_type == 'LC']):
sheet.write(v_offset + idx, 0, idx)
sheet.write(v_offset + idx, 1, lc.species)
sheet.write(v_offset + idx, 2, lc.after_24h)
sheet.write(v_offset + idx, 3, lc.after_48h)
sheet.write(v_offset + idx, 4, lc.after_96h)
def add_scores_to_sheet(sheet, imported_rec):
sheet.write_merge(0, 0, 0, 1, 'Oil Category Scores')
sheet.write(1, 0, 'Category', xlwt.easyxf('font: underline on;'))
sheet.write(1, 1, 'Score', xlwt.easyxf('font: underline on;'))
col = sheet.col(0)
col.width = 220 * 27
tests = (score_demographics,
score_api,
score_pour_point,
score_flash_point,
score_sara_fractions,
score_emulsion_constants,
score_interfacial_tensions,
score_densities,
score_viscosities,
score_cuts,
score_toxicities)
idx = 0
for idx, t in enumerate(tests):
sheet.write(2 + idx, 0, t.__name__)
sheet.write(2 + idx, 1, t(imported_rec))
v_offset = 2 + idx + 2
sheet.write(v_offset, 0, 'Overall Score')
sheet.write(v_offset, 1,
xlwt.Formula('AVERAGE($B$3:$B${0})'.format(v_offset)))
def make_protected(workbook_male, workbook_female, worksheet_male, worksheet_female, country):
row_country_m, col_country_m = find_row_col_index(country, worksheet_male)
row_country_f, col_country_f = find_row_col_index(country, worksheet_female)
wb_male = copy(workbook_male)
wb_female = copy(workbook_female)
for i in range(17, 258):
for j in range(3, 50):
if i != row_country_m:
wb_male.get_sheet(0).write(i, j, worksheet_male.cell(i, j).value, xlwt.easyxf('protection: cell_locked false;'))
else:
wb_male.get_sheet(0).write(i, j, worksheet_male.cell(i, j).value, xlwt.easyxf('protection: cell_locked true;'))
wb_male.get_sheet(0).set_protect(True)
wb_male.save('Data/WPP2015_POP_F01_2_TOTAL_POPULATION_MALE.XLS')
for i in range(17, 258):
for j in range(3, 50):
if i != row_country_f:
wb_female.get_sheet(0).write(i, j, worksheet_female.cell(i, j).value, xlwt.easyxf('protection: cell_locked false;'))
else:
wb_female.get_sheet(0).write(i, j, worksheet_female.cell(i, j).value, xlwt.easyxf('protection: cell_locked true;'))
wb_female.get_sheet(0).set_protect(True)
wb_female.save('Data/WPP2015_POP_F01_2_TOTAL_POPULATION_FEMALE.XLS')
def generate_custom_excel_output(self, fields, comments):
normal_style = xlwt.easyxf('align: wrap on, horiz left, vert top;')
header_style = xlwt.easyxf('font: bold on; align: wrap on, horiz left;')
wb = xlwt.Workbook(encoding='utf-8')
ws = wb.add_sheet('Sheet 1')
row = 0
for col in range(len(fields)):
ws.row(row).set_cell_text(col, fields[col][0], header_style)
ws.col(0).width = 3000
ws.col(1).width = 20000
ws.col(2).width = 7500
ws.col(3).width = 5000
for item in comments:
row += 1
for col in range(len(fields)):
ws.row(row).set_cell_text(col, fields[col][1](item),
normal_style)
row += 1
ws.row(row).set_cell_text(0, 'EEA Comments' , header_style)
output = StringIO()
wb.save(output)
return output.getvalue()
def generateReceipt(itemNames, itemPrices, subTotal, salesTax):
"""This new method relies on being called from makeSale(). It takes two
lists, and two integer values to generate a receipt for the customer.
It prints out to an excel file with formatted numbers. This is the
best we have to make a receipt for the cashier. It launches the file
after it has been created so the customer can print the receipt."""
global ticketID
global ALIGN_RIGHT
global ALIGN_CENTER
receipt = xlwt.Workbook()
sale = receipt.add_sheet('Customer Receipt')
sale.write_merge(0, 1, 0, 5, "My Tool", BRAND_CELL)
sale.write_merge(2, 2, 0, 3, "Today's date:", xlwt.easyxf('align: horiz right'))
sale.write_merge(2, 2, 4, 5, str(time.strftime("%m-%d-%Y")), xlwt.easyxf('align: horiz right'))
sale.write_merge(3, 3, 0, 3, "Your unique sale ID:", xlwt.easyxf('align: horiz right'))
sale.write_merge(3, 3, 4, 5, ticketID, xlwt.easyxf('align: horiz right'))
sale.write_merge(4, 4, 0, 3, "Item", BRAND_CELL)
sale.write_merge(4, 4, 4, 5, "Price", BRAND_CELL)
for x in range(0, len(itemNames)):
sale.write_merge(5+x, 5+x, 0, 3, itemNames[x])
sale.write_merge(5+x, 5+x, 4, 5, str(itemPrices[x]/100), ALIGN_RIGHT)
sale.write_merge(5+len(itemNames), 5+len(itemNames), 0, 3, "Sales Tax:", ALIGN_RIGHT)
sale.write_merge(5+len(itemNames), 5+len(itemNames), 4, 5, str(salesTax/100), ALIGN_RIGHT)
sale.write_merge(6+len(itemNames), 6+len(itemNames), 0, 3, "Sub total:", ALIGN_RIGHT)
sale.write_merge(6+len(itemNames), 6+len(itemNames), 4, 5, str(subTotal/100), ALIGN_RIGHT)
sale.write_merge(7+len(itemNames), 7+len(itemNames), 0, 3, "Total:", ALIGN_RIGHT)
sale.write_merge(7+len(itemNames), 7+len(itemNames), 4, 5, str((salesTax+subTotal)/100), ALIGN_RIGHT)
sale.write_merge(8+len(itemNames), 8+len(itemNames), 0, 5, "Thanks for shopping at My Tool!", ALIGN_RIGHT)
sale.write_merge(9+len(itemNames), 9+len(itemNames), 0, 5, "Have a great day!", ALIGN_CENTER)
receipt.save('Customer_receipt_' + str(ticketID)+'.xls')
os.system("start Customer_receipt_" + str(ticketID)+ '.xls')
def write_file(result_list, deal_date, company_name, filename):
'''
given a list, put it into excel file.
deal_date specifies a string which will be rendered as bold
company_name and filename are self-explanatory
'''
w = Workbook()
sheet = w.add_sheet(company_name)
row = 2
boldfont = easyxf(strg_to_parse='font: bold on')
normalfont = easyxf(strg_to_parse='')
sheet.write(0, 0, company_name)
sheet.write(1, 0, 'Date')
sheet.write(1, 1, 'Open')
sheet.write(1, 2, 'Close')
for line in result_list:
elements = line.decode('utf8').split(',')
date_string = elements[0]
open_value = elements[1]
close_value = elements[4]
if datetime.strptime(date_string, '%Y-%m-%d') == deal_date:
style = boldfont
else:
style = normalfont
sheet.write(row, 0, date_string, style)
sheet.write(row, 1, open_value, style)
sheet.write(row, 2, close_value, style)
row += 1
print(date_string, open_value, close_value)
w.save(filename)
def get(self,request,*args,**kwargs):
book = xlwt.Workbook(encoding='utf8')
sheet = book.add_sheet('untitled')
default_style = xlwt.Style.default_style
datetime_style = xlwt.easyxf(num_format_str='dd/mm/yyyy hh:mm')
date_style = xlwt.easyxf(num_format_str='dd/mm/yyyy')
values_list = JobApplication.objects.all().values_list('name','email')
for row, rowdata in enumerate(values_list):
for col, val in enumerate(rowdata):
# if isinstance(val, datetime):
# style = datetime_style
# elif isinstance(val, date):
# style = date_style
# else:
# style = default_style
sheet.write(row, col, val)
response = HttpResponse(mimetype='application/vnd.ms-excel')
response['Content-Disposition'] = 'attachment; filename=example.xls'
book.save(response)
return response
def __init__(self, name, table,
rml=False, parser=False, header=True, store=False):
super(AccountBalanceReportingXls,
self).__init__(name, table, rml, parser, header, store)
# Cell Styles
_xs = self.xls_styles
# header
rh_cell_format = _xs['bold'] + _xs['fill'] + _xs['borders_all']
self.rh_cell_style = xlwt.easyxf(rh_cell_format)
self.rh_cell_style_center = xlwt.easyxf(
rh_cell_format + _xs['center'])
self.rh_cell_style_right = xlwt.easyxf(
rh_cell_format + _xs['right'])
# lines
aml_cell_format = _xs['borders_all']
self.aml_cell_style = xlwt.easyxf(aml_cell_format)
self.aml_cell_style_center = xlwt.easyxf(
aml_cell_format + _xs['center'])
self.aml_cell_style_date = xlwt.easyxf(
aml_cell_format + _xs['left'],
num_format_str=report_xls.date_format)
self.aml_cell_style_decimal = xlwt.easyxf(
aml_cell_format + _xs['right'],
num_format_str=report_xls.decimal_format)
# totals
rt_cell_format = _xs['bold'] + _xs['fill'] + _xs['borders_all']
self.rt_cell_style = xlwt.easyxf(rt_cell_format)
self.rt_cell_style_right = xlwt.easyxf(
rt_cell_format + _xs['right'])
self.rt_cell_style_decimal = xlwt.easyxf(
rt_cell_format + _xs['right'],
num_format_str=report_xls.decimal_format)
def write(self):
sty = ''
header_sty = xlwt.easyxf(sty + 'font: bold on; align: wrap on, vert centre, horiz center;')
sty = xlwt.easyxf(sty)
ws = self.workbook.add_sheet(self.sheet_name)
titles = [_(u'Column'), _(u'Title')] + self.extension_titles
for col, title in enumerate(titles):
ws.write(0, col, title, style=header_sty)
row = 1
for column in self.board.columns:
column = column()
colname = _('Archived cards') if column.is_archive else column.get_title()
for card in column.cards:
card = card()
ws.write(row, 0, colname, sty)
ws.write(row, 1, card.get_title(), sty)
card_extensions = dict(card.extensions)
for col, key in enumerate(self.extensions, 2):
ext = card_extensions[key]()
write_extension_data(ext, ws, row, col, sty)
row += 1
for col in xrange(len(titles)):
ws.col(col).width = 0x3000
ws.set_panes_frozen(True)
ws.set_horz_split_pos(1)
def create_excel_file(db, kurzus):
book = Workbook(encoding='utf-8')
#sheet = book.add_sheet('{0} - {1} - {2}'.format(kurzus['nev'],kurzus['nap'],kurzus['sav']))
sheet = book.add_sheet('Névsor')
sheet = create_shit_head(sheet, kurzus_infok)
kurzus_hallgatok = get_kurzus_hallgatok(db, kurzus['id'])
sorszam = 1
for user in kurzus_hallgatok:
sheet.write(sorszam+4,0,sorszam,easyxf(
'borders: left thick, right thick, top thick, bottom thick;'
))
sheet.write(sorszam+4,1,user['neptun'],easyxf(
'borders: left thick, right thick, top thick, bottom thick;'
))
sheet.write(sorszam+4,2,user['nev'],easyxf(
'borders: left thick, right thick, top thick, bottom thick;'
))
for i in xrange(3,20):
sheet.write(sorszam+4,i,'',easyxf(
'borders: left thick, right thick, top thick, bottom thick;'
))
sorszam = sorszam + 1
book.save('{0}_{1}_{2}.xls'.format(ekezet_eltunteto(kurzus['nev'].lower()),
ekezet_eltunteto(kurzus['nap'].lower()),
kurzus['sav']))
def add_to_excel(self, state):
""" adds content to the excel file based on the specific statistic type """
import xlwt
answers = state['answers']
ws = state['ws']
current_row = state['current_row']
translator = self.getSite().getPortalTranslations()
total, answered, unanswered = self.calculate(self.question, answers)
#define Excel styles
header_style = xlwt.easyxf('font: bold on; align: vert centre')
normal_style = xlwt.easyxf('align: vert centre')
#write cell elements similarly to the zpt-->html output
ws.write(current_row, 1, self.question.title, header_style)
current_row += 1
ws.write(current_row, 2, translator('Count'), header_style)
ws.write(current_row, 3, translator('Percent'), header_style)
current_row += 1
ws.write(current_row, 1, translator('Answered'), header_style)
ws.write(current_row, 2, answered[0], normal_style)
ws.write(current_row, 3, '%.2f%%'
% (round(answered[1], 2), ), normal_style)
current_row += 1
ws.write(current_row, 1, translator('Not answered'), header_style)
ws.write(current_row, 2, unanswered[0], normal_style)
ws.write(current_row, 3, '%.2f%%'
% (round(unanswered[1], 2), ), normal_style)
current_row += 1
ws.write(current_row, 1, translator('Total'), header_style)
ws.write(current_row, 2, total, normal_style)
ws.write(current_row, 3, '100%%', normal_style)
current_row += 1
state['current_row'] = current_row
def configErrorReporting(headers):
"""
Configure import exception log, which is an Excel spreadsheet in the same
format as the input format, but with an extra column added - "Error",
which contains the error message.
Can only be called after first row of input Excel spreadsheet is read
to initialize the global, "headers"
"""
dateFmt = easyxf(
'font: name Arial, bold True, height 200;',
#'borders: left thick, right thick, top thick, bottom thick;',
num_format_str='MM-DD-YYYY'
)
headerFmt = easyxf(
'font: name Arial, bold True, height 200;',
)
global errorsWorkbook, erroutSheet, erroutRow
errorsWorkbook = Workbook()
erroutSheet = errorsWorkbook.add_sheet('Import Errors')
for colnum in range(0, len(headers)):
erroutSheet.write(0, colnum, headers[colnum][0],
tern(headers[colnum][0]==xlrd.XL_CELL_DATE, dateFmt, headerFmt))
# Add extra column for error message
erroutSheet.write(0, len(headers), "Error", headerFmt)
erroutSheet.flush_row_data()
erroutRow = 1
errorsWorkbook.save('errors.xls')
def report_group_summary_monthly_excel(request,year,month):
book = xlwt.Workbook(encoding='utf8')
sheet = book.add_sheet('untitled')
default_style = xlwt.Style.default_style
datetime_style = xlwt.easyxf(num_format_str='dd/mm/yyyy hh:mm')
date_style = xlwt.easyxf(num_format_str='dd/mm/yyyy')
row = 1
student_list = Student.objects.all()
for student in student_list:
row=row+1
col=0
sheet.write(row, col, "%s %s" % (student.people.name_th,student.people.lastname_th) , style=default_style)
activity_group_list = ActivityGroup.objects.filter()
for activity_group in activity_group_list:
activity_list = Activity.objects.filter(group=activity_group,date__month=month,date__year=year)
for activity in activity_list:
col=col+1
try :
sheet.write(row, col, ("%s" % ActivityReport.objects.get(activity=activity,attendance=student,version=activity.version).transaction_status),style=default_style)
except ActivityReport.DoesNotExist:
sheet.write(row, col, "-",style=default_style)
response = HttpResponse(mimetype='application/vnd.ms-excel')
response['Content-Disposition'] = 'attachment; filename=report_group_summary_monthly_excel-%s-%s.xls' % (year,month)
book.save(response)
return response
def predict(self,wave):
style = xl.easyxf( get_style(height=210,color='green',bold=True,horizontal='center') )
if not wave.has_subwaves:
self.sheet.row(self.row_no).write(3,'No subwaves, cannot predict waves for wave',style)
return
row_title = self.sheet.row(self.row_no)
row_title.write(3, 'Predict p3 and p5 for {}'.format(wave.name), style)
self.nextline()
p31,p51,p52,p53,p54,p54s,fib31,fib51,fib52,fib53,fib54,fib54s = get_prediction(wave)
style_n = xl.easyxf( get_style(bold=False,horizontal='center'))# + 'borders: bottom thin;')
style_hl = xl.easyxf( get_style(bold=True,horizontal='center'))# + 'borders: bottom thin;')
def write(title1,title2,price1,price2,price3,fib,style=style_n):
row = self.sheet.row(self.row_no)
row.write(1,title1,style); row.write(2,title2,style); row.write(3,price1,style); row.write(4,price2,style)
row.write(5,price3,style); row.write(6,fib,style)
self.nextline()
style_20 = xl.easyxf( get_style(bold=True,horizontal='center') + 'borders: bottom thin;')
#write('p3','2.0*i', p31,p31-wave.ii.end,compute_percent(wave.ii.end,p31),(p31-wave.ii.end)/wave.i.size,style=style_20)
#write('p5','2.382*i', p51,p51-wave.start,compute_percent(wave.start,p51),(p51-wave.i.start)/wave.i.size)
#write('p5','p3+(p2-p0)',p52,p52-wave.start,compute_percent(wave.start,p52),int((p52-wave.i.start)/wave.i.size) )
#write('p5','p3+0.382*i',p53,p53-wave.start,compute_percent(wave.start,p53),int((p53-wave.i.start)/wave.i.size),style=style_hl)
#write('p5','p4+1.0*i', p54,p54-wave.start,compute_percent(wave.start,p54),int((p54-wave.i.start)/wave.i.size))
#write('p5','p4+0.618*iii', p54s,p54s-wave.start,compute_percent(wave.start,p54s),int((p54s-wave.i.start)/wave.i.size) )
write('p3','2.0*i', p31,p31-wave.ii.end,compute_percent(wave.ii.end,p31),fib31,style=style_20)
write('p5','2.382*i', p51,p51-wave.start,compute_percent(wave.start,p51),fib51)
write('p5','p3+(p2-p0)',p52,p52-wave.start,compute_percent(wave.start,p52),fib52)
write('p5','p3+0.382*i',p53,p53-wave.start,compute_percent(wave.start,p53),fib53,style=style_hl)
write('p5','p4+1.0*i', p54,p54-wave.start,compute_percent(wave.start,p54),fib54)
write('p5','p4+0.618*iii', p54s,p54s-wave.start,compute_percent(wave.start,p54s),fib54s)
def generate_xls(Con):
wb = Workbook()
ws = wb.add_sheet(sheetname='Order Results')
headerStyle = easyxf('font: bold True')
wrapStyle = easyxf('alignment: wrap True')
ws.write(0, 0, 'Date', headerStyle)
ws.write(0, 1, 'Particulars', headerStyle)
ws.write(0, 2, 'Amount', headerStyle)
ws.write(0, 3, 'Revenue', headerStyle)
max_width_col0 = 0
max_width_col1 = 0
i = 1
for order in Con.Orders:
ws.write(i, 0, order.orderDate)
ws.write(i, 1, order.Particulars, wrapStyle)
ws.write(i, 2, float(order.Total_Amount))
ws.write(i, 3, float(order.Revenue_Amount))
for s in order.Particulars.split('\n'):
if max_width_col1 < len(s):
max_width_col1 = len(s)
if max_width_col0 < len(str(order.orderDate)):
max_width_col0 = len(str(order.orderDate))
i += 1
ws.col(1).width = 256 * max_width_col1 + 2
ws.col(0).width = 256 * max_width_col0 + 20
ws.write(i, 1, 'Total Amount', headerStyle)
ws.write(i, 2, Con.Total_Amount, headerStyle)
ws.write(i, 3, Con.Revenue_Amount, headerStyle)
filename = Con.Username + '_' + Con.Label + '_'
filename += Con.FromDate.strftime('%Y%m%d') + '_'
filename += Con.ToDate.strftime('%Y%m%d') + '.xls'
wb.save(config.get_main_dir() + '/Output/' + filename)
def check_attr_diff():
attributes = ["datanode_java_heapsize","dfs_datanode_failed_volumes_tolerated","dfs_namenode_handler_count","hbase_hregion_max_filesize","hbase_master_java_heapsize","hbase_regionserver_java_heapsize","io_sort_mb","jobtracker_java_heapsize","mapred_child_java_opts_max_heap","mapred_job_tracker_handler_count","mapred_submit_replication","mapred_tasktracker_map_tasks_maximum","mapred_tasktracker_reduce_tasks_maximum","maxSessionTimeout","namenode_java_heapsize","override_mapred_child_java_opts_max_heap","secondary_namenode_java_heapsize","task_tracker_java_heapsize","override_mapred_compress_map_output","override_mapred_map_tasks_speculative_execution","override_mapred_output_compress","override_mapred_reduce_tasks_speculative_execution"]
START_ROW = 1
ATTR_COL = 2
VALUE_COL = 3
w_sheets = [0]*10
rb = open_workbook("CombinedClusterConfigs.xls",formatting_info=True)
styles = Styles(rb)
r_sheets = rb.sheets() # read only copy of the sheets
print r_sheets
wb = copy(rb) # a writable copy (I can't read values out of this, only write to it)
style1 = xlwt.easyxf('pattern: pattern solid, fore_colour red;')
style2 = xlwt.easyxf('pattern: pattern solid, fore_colour orange;')
style3 = xlwt.easyxf('pattern: pattern solid, fore_colour blue;')
for i in range(0,rb.nsheets):
last_attrib = 0
last_value = 0
w_sheets[i] = wb.get_sheet(i) # the sheets to write to within the writable copy
for row_index in range(START_ROW, r_sheets[i].nrows):
current_attrib = r_sheets[i].cell(row_index, ATTR_COL).value
current_value = r_sheets[i].cell(row_index, VALUE_COL).value
#print w_sheets[i]
if current_attrib == last_attrib:
if current_value <> last_value:
w_sheets[i].write(row_index, VALUE_COL, r_sheets[i].cell(row_index, VALUE_COL).value, style1)
continue
for attribute in attributes:
if r_sheets[i].cell(row_index, ATTR_COL).value == attribute:
last_attrib = r_sheets[i].cell(row_index, ATTR_COL).value
last_value = r_sheets[i].cell(row_index, VALUE_COL).value
break
wb.save("CombinedClusterConfigs.xls")
def print_header_data(self, ws, _p, data, row_position, xlwt, _xs, initial_balance_text):
cell_format = _xs['borders_all'] + _xs['wrap'] + _xs['top']
cell_style = xlwt.easyxf(cell_format)
cell_style_center = xlwt.easyxf(cell_format + _xs['center'])
c_specs = [
('fy', 1, 0, 'text', _p.fiscalyear.name if _p.fiscalyear else '-', None, cell_style_center),
('af', 1, 0, 'text', _p.accounts(data) and ', '.join([account.code for account in _p.accounts(data)]) or _('All'), None, cell_style_center),
]
df = _('From') + ': '
if _p.filter_form(data) == 'filter_date':
df += _p.start_date if _p.start_date else u''
else:
df += _p.start_period.name if _p.start_period else u''
df += ' ' + _('\nTo') + ': '
if _p.filter_form(data) == 'filter_date':
df += _p.stop_date if _p.stop_date else u''
else:
df += _p.stop_period.name if _p.stop_period else u''
c_specs += [
('df', 1, 0, 'text', df, None, cell_style_center),
('tm', 1, 0, 'text', _p.display_partner_account(data), None, cell_style_center),
('pf', 1, 0, 'text', _p.display_target_move(data), None, cell_style_center),
('ib', 1, 0, 'text', initial_balance_text[_p.initial_balance_mode], None, cell_style_center),
('coa', 1, 0, 'text', _p.chart_account.name, None, cell_style_center),
]
row_data = self.xls_row_template(c_specs, [x[0] for x in c_specs])
row_position = self.xls_write_row(ws, row_position, row_data, row_style=cell_style)
return row_position
def styleUpdate(target):
rb = open_workbook(target)
wb = copy(rb)
#style
headline = xlwt.easyxf('pattern: pattern solid, fore_color yellow, back_color orange; font: height 260, bold true, color black; align: horizontal center, vertical center, wrap true; border: top thick, bottom double')
plain = xlwt.easyxf('font: height 200, color black; align: horizontal center, vertical center; border: top thin, bottom thin, right thin, left thin')
date_format = xlwt.XFStyle()
date_format.num_format_str = 'yyyy-mm-dd'
sheetNumber = len(rb.sheet_names())
#loop through the sheet
for num in range(sheetNumber):
sheet = wb.get_sheet(num)
#looping through columns
for colNum in range(1,20):
sheet.col(0).width = 256*4
sheet.write(0,colNum,rb.sheet_by_index(num).cell(0,colNum).value,style=headline)
sheet.col(colNum).width = 256*15
for rowNum in range(1,len(sheet.rows)):
sheet.write(rowNum,colNum,rb.sheet_by_index(num).cell(rowNum,colNum).value,style=plain)
#write the content with style
sheet.row(0).height_mismatch = True
sheet.row(0).height = 256*3
wb.save(target)
def from_data(self, fields, rows, file_address):
if file_address:
bk = xlrd.open_workbook(file_address, formatting_info=True)
workbook = xlutils.copy.copy(bk)
worksheet = workbook.get_sheet(0)
for i, fieldname in enumerate(fields):
self.setOutCell(worksheet, 0, i, fieldname)
for row, row_vals in enumerate(rows):
for col, col_value in enumerate(row_vals):
if isinstance(col_value, basestring):
col_value = re.sub("\r", " ", col_value)
self.setOutCell(worksheet, col, row + 1, col_value)
else:
workbook = xlwt.Workbook()
worksheet = workbook.add_sheet('Sheet 1')
base_style = xlwt.easyxf('align: wrap yes')
date_style = xlwt.easyxf('align: wrap yes', num_format_str='YYYY-MM-DD')
datetime_style = xlwt.easyxf('align: wrap yes', num_format_str='YYYY-MM-DD HH:mm:SS')
for row_index, row in enumerate(rows):
for cell_index, cell_value in enumerate(row):
cell_style = base_style
if isinstance(cell_value, basestring):
cell_value = re.sub("\r", " ", cell_value)
elif isinstance(cell_value, datetime.datetime):
cell_style = datetime_style
elif isinstance(cell_value, datetime.date):
cell_style = date_style
worksheet.write(row_index + 1, cell_index, cell_value, cell_style)
fp_currency = StringIO.StringIO()
workbook.save(fp_currency)
fp_currency.seek(0)
data = fp_currency.read()
fp_currency.close()
return data
def generate_excel_output(self, fields, comments):
header_style = xlwt.easyxf('font: bold on; align: horiz left;')
normal_style = xlwt.easyxf('align: horiz left, vert top;')
wb = xlwt.Workbook(encoding='utf-8')
ws = wb.add_sheet('Sheet 1')
ws.row(0).set_cell_text(0, 'Consultation deadline', header_style)
ws.row(0).set_cell_text(1, (self.end_date + 1).strftime('%Y/%m/%d %H:%M'),
normal_style)
ws.row(1).set_cell_text(0, 'Date of export', header_style)
ws.row(1).set_cell_text(1, DateTime().strftime('%Y/%m/%d %H:%M'),
normal_style)
row = 2
for col in range(len(fields)):
ws.row(row).set_cell_text(col, fields[col][0], header_style)
for item in comments:
row += 1
for col in range(len(fields)):
ws.row(row).set_cell_text(col, fields[col][1](item),
normal_style)
output = StringIO()
wb.save(output)
return output.getvalue()
def _generate_excel(response, columns, headers, grads):
import xlwt
book = xlwt.Workbook(encoding='utf-8')
sheet = book.add_sheet('Search Results')
hdrstyle = xlwt.easyxf('font: bold on; pattern: pattern solid, fore_colour grey25; align: horiz centre')
evenstyle = xlwt.easyxf('pattern: back_colour gray40')
oddstyle = xlwt.easyxf('pattern: pattern sparse_dots, fore_colour grey25')
# header row
sheet.write(0, 0, u'Graduate Student Search Results', xlwt.easyxf('font: bold on, height 320'))
sheet.row(0).height = 400
for i,hdr in enumerate(headers):
sheet.write(1, i, hdr, hdrstyle)
# data rows
for i,grad in enumerate(grads):
style = [oddstyle, evenstyle][i%2]
for j,column in enumerate(columns):
sheet.write(i+2, j, getattribute(grad, column, html=False), style)
# set column widths
for i,c in enumerate(columns):
wid = COLUMN_WIDTHS[c]
sheet.col(i).width = wid
count = len(grads)
sheet.write(count+4, 0, 'Number of students: %i' % (count))
sheet.write(count+5, 0, 'Report generated: %s' % (datetime.datetime.now()))
book.save(response)
def case_writer(BusinessDocument, workbook):
"""
Reads the all sheets of the business document and writes to the worksheet.
External and Internal test cases will written in seperate sheets.
@param BusinessDocument: Document to read from.
@param workbook: Document to write to
@return: None
"""
# Styling stuff
font = xlwt.Font()
font.bold = True
title_text = xlwt.easyxf("align: vert top, horiz left")
title_text.font = font
style_text = xlwt.easyxf("align: wrap on, vert top, horiz left")
#Create the worksheets for the test cases.
worksheet = workbook.add_sheet("Dashboard")
# for worksheet in [worksheet_ext, worksheet_int]:
# Write the Header row
worksheet.write(0, 0, "Test Type", title_text)
worksheet.write(0, 1, "HPQC Test Plan Folder name", title_text)
worksheet.write(0, 2, "Your User ID", title_text)
worksheet.write(0, 3, "Residence Type", title_text)
worksheet.write(0, 4,
"Test Case ID(Business ReqID+ BR/DFS/SC+screen Name)",
title_text)
worksheet.write(0, 5, "Test Case Description", title_text)
worksheet.write(0, 6, "Pre-Condition", title_text)
worksheet.write(0, 7, "Execution", title_text)
worksheet.write(0, 8, "Expected Result", title_text)
worksheet.write(0, 9, "Release", title_text)
worksheet.write(0, 10, "Created by(optional)", title_text)
worksheet.write(0, 11, "Creation Date(optional)", title_text)
# Size adjustments
worksheet.col(0).width = 30 * 70
worksheet.col(1).width = 30 * 200
worksheet.col(2).width = 30 * 70
worksheet.col(3).width = 30 * 70
worksheet.col(4).width = 30 * 180
worksheet.col(5).width = 30 * 270
worksheet.col(6).width = 30 * 70
worksheet.col(7).width = 30 * 256
worksheet.col(8).width = 30 * 220
worksheet.col(9).width = 30 * 70
worksheet.col(10).width = 30 * 70
worksheet.col(11).width = 30 * 100
# # The first sheet is revision history so we don't need it.
# business_sheets = BusinessDocument.sheets()[1:]
# We're going to write to both the external and internal sheets simultaneously
# a will serve as the counter for external, and b for internal
c = 1
# Iterate through the business documents, first iteration would be CRA, next YJ etc
RuleSheet = BusinessDocument.sheets()[0]
for i in range(1, RuleSheet.nrows):
# easy references
Role = RuleSheet.row_values(i)[0]
Table = RuleSheet.row_values(i)[1]
Section = RuleSheet.row_values(i)[2]
col_list = []
for column in range(3, 11):
col_list.append(RuleSheet.row_values(i)[column])
# Setting up the content for the cells
Description = ("Verify for the " + Role + ", in the table named:\n\n" +
Table + "\n\nUnder the section:\n\n" + Section +
"\n\nThe columns are oganized correctly.")
columnstring = ""
counter = 1
for entry in col_list:
if entry == "":
break #Nothing to do if its blank
columnstring += "\nColumn " + str(counter) + ": " + entry
counter += 1
Expected_Result = "The columns are organized as follows:" + columnstring
Execution = (
"1) Login as " + Role + "\n2) View the dashboard." +
"\n3) Verify the table, section, and column labels are correct." +
"\n4) Verify that the columns are organized correctly.")
case_id = "SO_Dashboard_Roles_" + str(c)
# Now that we have all the information, we can just write it to the correct sheet
worksheet.write(c, 0, "Manual", style_text)
worksheet.write(c, 1, "*****_SO_Dashboard", style_text)
worksheet.write(c, 2, "*****", style_text)
worksheet.write(c, 3, "All", style_text)
worksheet.write(c, 4, case_id, style_text)
worksheet.write(c, 5, Description, style_text)
worksheet.write(c, 6, "N/A", style_text)
worksheet.write(c, 7, Execution, style_text)
worksheet.write(c, 8, Expected_Result, style_text)
worksheet.write(c, 9, "2.0", style_text)
worksheet.write(c, 10, "Sagar", style_text)
worksheet.write(c, 11, now.strftime("%m-%d-%Y"), style_text)
worksheet.write
c += 1
workbook.save("DashboardRoles_test_Cases.xls")
# instantiate the parser and fed it some HTML
professor_list = list()
# for i in range(1, 10):
parser = MyHTMLParser()
f = urllib.urlopen("https://www.eecs.mit.edu/people/faculty-advisors")
html = f.read()
parser.feed(html)
professor_list = parser.professor_list
parser.close()
print professor_list
style0 = xlwt.easyxf('font: bold on')
book = xlwt.Workbook()
sheet1 = book.add_sheet('Mit')
sheet1.write(0, 1, 'First Name', style0)
sheet1.write(0, 2, 'Last Name', style0)
# sheet1.write(0, 1, 'Title', style0)
# sheet1.write(0, 2, 'Phone', style0)
sheet1.write(0, 0, 'E-Mail', style0)
grid = {
'First Name': 2,
'Last Name': 3,
# 'Phone': 2,
'E-Mail': 1
def print_excel(self):
if not self.price and not self.text and not self.quantity and not self.measure:
raise ValidationError(
'Debe escoger al menos una opción a comparar entre precio, texto, cantidad o línea de medición.'
)
# Estilos a usar
normal_right = xlwt.easyxf('align: wrap yes, horiz right;')
bold_left = xlwt.easyxf('align: wrap yes, horiz left; font: bold on;')
bold_right = xlwt.easyxf(
'align: wrap yes, horiz right; font: bold on;')
red_left = xlwt.easyxf(
'align: wrap yes, horiz left; font: colour red;')
red_bold_left = xlwt.easyxf(
'align: wrap yes, horiz left; font: colour red, bold on;')
red_right = xlwt.easyxf(
'align: wrap yes, horiz right; font: colour red;')
red_bold_right = xlwt.easyxf(
'align: wrap yes, horiz right; font: colour red, bold on;')
separator = xlwt.easyxf(
'pattern: pattern solid, fore_colour periwinkle;')
workbook = xlwt.Workbook()
sheet = workbook.add_sheet('Comparación')
# Anchos
sheet.col(0).width = 256 * 20
sheet.col(1).width = 256 * 20
sheet.col(2).width = 256 * 6
sheet.col(3).width = 256 * 6
if self.measure:
sheet.col(4).width = 256 * 6
sheet.col(5).width = 256 * 3
sheet.col(6).width = 256 * 5
sheet.col(7).width = 256 * 5
sheet.col(8).width = 256 * 5
sheet.col(9).width = 256 * 6
else:
sheet.col(4).width = sheet.col(5).width = sheet.col(
6).width = sheet.col(7).width = sheet.col(8).width = sheet.col(
9).width = 1
sheet.col(10).width = 256
sheet.col(11).width = 256 * 20
sheet.col(12).width = 256 * 6
sheet.col(13).width = 256 * 6
if self.measure:
sheet.col(14).width = 256 * 6
sheet.col(15).width = 256 * 3
sheet.col(16).width = 256 * 5
sheet.col(17).width = 256 * 5
sheet.col(18).width = 256 * 5
sheet.col(19).width = 256 * 6
else:
sheet.col(14).width = sheet.col(15).width = sheet.col(
16).width = sheet.col(17).width = sheet.col(
18).width = sheet.col(19).width = 1
sheet.col(20).width = 256
sheet.col(21).width = 256 * 20
sheet.col(22).width = 256 * 6
sheet.col(23).width = 256 * 6
if self.measure:
sheet.col(24).width = 256 * 6
sheet.col(25).width = 256 * 3
sheet.col(26).width = 256 * 5
sheet.col(27).width = 256 * 5
sheet.col(28).width = 256 * 5
sheet.col(29).width = 256 * 6
# Cabecera
sheet.write(0, 2, 'Cant', bold_left)
sheet.write(0, 3, 'Prec', bold_left)
if self.measure:
sheet.write(0, 4, 'Esp', bold_left)
sheet.write(0, 5, 'n', bold_right)
sheet.write(0, 6, 'x', bold_right)
sheet.write(0, 7, 'y', bold_right)
sheet.write(0, 8, 'z', bold_right)
sheet.write(0, 9, 'total', bold_right)
sheet.write(0, 10, '', separator)
sheet.write(0, 12, 'Cant', bold_left)
sheet.write(0, 13, 'Prec', bold_left)
if self.measure:
sheet.write(0, 14, 'Esp', bold_left)
sheet.write(0, 15, 'n', bold_right)
sheet.write(0, 16, 'x', bold_right)
sheet.write(0, 17, 'y', bold_right)
sheet.write(0, 18, 'z', bold_right)
sheet.write(0, 19, 'total', bold_right)
sheet.write(0, 20, '', separator)
sheet.write(0, 22, 'Cant', bold_left)
sheet.write(0, 23, 'Prec', bold_left)
if self.measure:
sheet.write(0, 24, 'Esp', bold_left)
sheet.write(0, 25, 'n', bold_right)
sheet.write(0, 26, 'x', bold_right)
sheet.write(0, 27, 'y', bold_right)
sheet.write(0, 28, 'z', bold_right)
sheet.write(0, 29, 'total', bold_right)
sheet.write(1, 0, self.origin_budget_id.display_name, bold_left)
sheet.write(1, 10, '', separator)
sheet.write(1, 11, self.compare_budget_id.display_name, bold_left)
sheet.write(1, 20, '', separator)
sheet.write(1, 21, 'Diferencia', bold_left)
types = ['chapter', 'departure'
] if self.compare == 'both' else [self.compare]
row = 2
# Buscamos códigos de conceptos en común (los repetidos dañan todo)
for origin_concept in self.origin_budget_id.concept_ids:
if origin_concept.type not in types:
continue
diferences1 = []
diferences2 = []
if self.budget_type == 'budget':
origin_quantity = origin_concept.quantity
origin_price = origin_concept.amount_fixed if origin_concept.amount_type == 'fixed' else origin_concept.amount_compute
else:
origin_quantity = origin_concept.quantity_cert
origin_price = origin_concept.amount_fixed_cert if origin_concept.amount_type == 'fixed' else origin_concept.amount_compute_cert
sheet.write(row, 0, origin_concept.code, bold_left)
sheet.write(row, 1, origin_concept.name, bold_left)
sheet.write(row, 2, origin_quantity, normal_right)
sheet.write(row, 3, origin_price, normal_right)
# No olvidemos los separadores
sheet.write(row, 10, '', separator)
sheet.write(row, 20, '', separator)
# Líneas de medición
measures = []
if self.measure:
for i, line in enumerate(origin_concept.measuring_ids, 1):
sheet.write(row + i, 4, line.name)
sheet.write(row + i, 5, line.qty, normal_right)
sheet.write(row + i, 6, line.length, normal_right)
sheet.write(row + i, 7, line.width, normal_right)
sheet.write(row + i, 8, line.height, normal_right)
sheet.write(row + i, 9, line.amount_subtotal, normal_right)
sheet.write(row + i, 10, '', separator)
sheet.write(row + i, 20, '', separator)
measures.append(line.name)
for compare_concept in self.compare_budget_id.concept_ids:
if compare_concept.type not in types:
continue
if origin_concept.code == compare_concept.code:
# Hora de buscar diferencias
if self.text and origin_concept.name != compare_concept.name:
diferences1.append(
(compare_concept.name, red_bold_left))
else:
diferences1.append((compare_concept.name, bold_left))
diferences2.append(
(compare_concept.name, red_bold_left if
(origin_concept.sequence != compare_concept.sequence
or origin_concept.parent_id.code !=
compare_concept.parent_id.code) else bold_left))
if self.budget_type == 'budget':
compare_quantity = compare_concept.quantity
compare_price = compare_concept.amount_fixed if compare_concept.amount_type == 'fixed' else compare_concept.amount_compute
else:
compare_quantity = compare_concept.quantity_cert
compare_price = compare_concept.amount_fixed_cert if compare_concept.amount_type == 'fixed' else compare_concept.amount_compute_cert
if self.quantity and origin_quantity != compare_quantity:
diferences1.append((compare_quantity, red_right))
diferences2.append(
(compare_quantity - origin_quantity, red_right))
else:
diferences1.append((compare_quantity, normal_right))
diferences2.append(
(compare_quantity - origin_quantity, normal_right))
if self.price and origin_price != compare_price:
diferences1.append((compare_price, red_right))
diferences2.append(
(compare_price - origin_price, red_right))
else:
diferences1.append((compare_price, normal_right))
diferences2.append(
(compare_price - origin_price, normal_right))
# Líneas de medición
if self.measure:
for line in compare_concept.measuring_ids:
try:
i = measures.index(line.name) + 1
except ValueError:
continue
sheet.write(row + i, 14, line.name)
sheet.write(row + i, 15, line.qty, normal_right)
sheet.write(row + i, 16, line.length, normal_right)
sheet.write(row + i, 17, line.width, normal_right)
sheet.write(row + i, 18, line.height, normal_right)
sheet.write(row + i, 19, line.amount_subtotal,
normal_right)
sheet.write(row + i, 24, line.name)
sheet.write(row + i, 25, line.qty, normal_right)
sheet.write(row + i, 26, line.length, normal_right)
sheet.write(row + i, 27, line.width, normal_right)
sheet.write(row + i, 28, line.height, normal_right)
sheet.write(row + i, 29, line.amount_subtotal,
normal_right)
break # Encontré el que coincide en código, no buscaré mas...
if diferences1:
for i, (value, style) in enumerate(diferences1, 11):
sheet.write(row, i, value, style)
for i, (value, style) in enumerate(diferences2, 21):
sheet.write(row, i, value, style)
# pasamos a la siguiente fila
row += 1 + (len(origin_concept.measuring_ids)
if self.measure else 0)
if row == 2:
raise ValidationError('No hay conceptos en común para comparar.')
# Unos cuantos separadores mas, hasta hacer al menos 50
for i in range(row, 53):
sheet.write(i, 10, '', separator)
sheet.write(i, 20, '', separator)
stream = io.BytesIO()
workbook.save(stream)
stream.seek(0)
filename = 'Comparacion%s_%s_%s.xls' % (
'_certificacion' if self.budget_type == 'cert' else '',
self.origin_budget_id.code, self.compare_budget_id.code)
attach_vals = {
'name': filename,
'datas': base64.b64encode(stream.getvalue()),
'store_fname': filename,
}
doc_id = self.env['ir.attachment'].create(attach_vals)
return {
'name': filename,
'type': 'ir.actions.act_url',
'url': 'web/content/%d?download=true' % doc_id.id,
'close_on_report_download': True,
'target': 'self',
}
# -*- coding: utf-8 -*-
import xlwt
import datetime
from django.forms.forms import pretty_name
from django.core.exceptions import ObjectDoesNotExist
HEADER_STYLE = xlwt.easyxf('font: bold on')
DEFAULT_STYLE = xlwt.easyxf()
CELL_STYLE_MAP = (
(datetime.date, xlwt.easyxf(num_format_str='DD/MM/YYYY')),
(datetime.time, xlwt.easyxf(num_format_str='HH:MM')),
(bool, xlwt.easyxf(num_format_str='BOOLEAN')),
)
def multi_getattr(obj, attr, default=None):
attributes = attr.split(".")
for i in attributes:
try:
obj = getattr(obj, i)
except AttributeError:
if default:
return default
else:
raise
return obj
def get_column_head(obj, name):
name = name.rsplit('.', 1)[-1]
def generate_xls_report(self, parser, data, obj, wb):
c = parser.localcontext['company']
ws = wb.add_sheet(('Partner Ledger')[:31])
ws.panes_frozen = True
ws.remove_splits = True
ws.portrait = 0 # Landscape
ws.fit_width_to_pages = 1
judul = "PARTNER LEDGER"
cols_specs = [
# Headers data
('Title', 7, 0, 'text',
lambda x, d, p: judul),
('Kosong', 7, 0, 'text',
lambda x, d, p: ""),
('Fiscal Year', 5, 0, 'text',
lambda x, d, p: self._display_fiscalyear(p, d)),
('Create Date', 2, 0, 'text',
lambda x, d, p: 'Create date: ' + p.formatLang(time.strftime("%Y-%m-%d %H:%M:%S", time.localtime()),date_time = True)),
('Filter', 7, 0, 'text',
lambda x, d, p: self._display_filter(p, d)),
# Line
('Date', 1, 65, 'text',
lambda x, d, p: p.formatLang(x['date'],date=True)),
('JNRL', 1, 28, 'text',
lambda x, d, p: x['code']),
('Ref.', 1, 45, 'text',
lambda x, d, p: x['ref']),
('Entry Label', 1, 175, 'text',
lambda x, d, p: x['name']),
('Debit', 1, 77, 'number',
lambda x, d, p: x['debit']),
('Credit', 1, 75, 'number',
lambda x, d, p: x['credit']),
('Balance', 1, 75, 'number',
lambda x, d, p: x['progress']),
# Partner Total
('Initial Balance', 4, 0, 'text',
lambda x, d, p: 'Initial Balance'),
('Initial Balance Debit', 1, 0, 'number',
lambda x, d, p: p._get_intial_balance(x)[0][0]),
('Initial Balance Credit', 1, 0, 'number',
lambda x, d, p: p._get_intial_balance(x)[0][1]),
('Initial Balance Balance', 1, 0, 'number',
lambda x, d, p: p._get_intial_balance(x)[0][2]),
# Partner Line
('Partner Name', 4, 0, 'text',
lambda x, d, p: '%s%s' % (x.ref and ('%s - ' % (x.ref)) or '', x.name)),
('Partner Debit', 1, 0, 'number',
lambda x, d, p: p._sum_debit_partner(x)),
('Partner Credit', 1, 0, 'number',
lambda x, d, p: p._sum_credit_partner(x)),
('Partner Balance', 1, 0, 'number',
lambda x, d, p: (p._sum_debit_partner(x) - p._sum_credit_partner(x)) or 0.0),
]
row_hdr0 = self.xls_row_template(cols_specs, ['Title'])
row_hdr5 = self.xls_row_template(cols_specs, ['Kosong'])
row_hdr1 = self.xls_row_template(cols_specs, ['Fiscal Year', 'Create Date'])
row_hdr2 = self.xls_row_template(cols_specs, ['Filter'])
row_hdr6 = self.xls_row_template(cols_specs, ['Kosong'])
hdr_line = ['Date', 'JNRL', 'Ref.', 'Entry Label', 'Debit', 'Credit', 'Balance']
hdr_partner_total = ['Partner Name', 'Partner Debit', 'Partner Credit', 'Partner Balance']
hdr_initial_total = [ 'Initial Balance', 'Initial Balance Debit', 'Initial Balance Credit', 'Initial Balance Balance']
row_line = self.xls_row_template(cols_specs, hdr_line)
tittle_style = xlwt.easyxf('font: height 240, name Arial Black, colour_index black, bold on; align: wrap on, vert centre, horiz left; pattern: pattern solid, fore_color white;')
subtittle_left_style = xlwt.easyxf('font: height 180, name Arial, colour_index brown, bold on, italic on; align: wrap on, vert centre, horiz left; pattern: pattern solid, fore_color white;')
hdr_style = xlwt.easyxf('pattern: pattern solid, fore_color white;',num_format_str='#,##0.00;(#,##0.00)')
row_partner_total = self.xls_row_template(cols_specs, hdr_partner_total)
row_initial_total = self.xls_row_template(cols_specs, hdr_initial_total)
# Style
row_hdr_style = xlwt.easyxf( 'pattern: pattern solid, fore_color white;',num_format_str='#,##0.00;(#,##0.00)')
row_partner_style = xlwt.easyxf('font: bold on;' 'borders: bottom thin;',num_format_str='#,##0.00;(#,##0.00)')
row_style = xlwt.easyxf(num_format_str='#,##0.00;(#,##0.00)')
self.xls_write_row(ws, None, data, parser, 0, row_hdr0, tittle_style)
self.xls_write_row(ws, None, data, parser, 1, row_hdr5, hdr_style)
self.xls_write_row(ws, None, data, parser, 2, row_hdr1, subtittle_left_style)
self.xls_write_row(ws, None, data, parser, 3, row_hdr2, row_hdr_style)
self.xls_write_row(ws, None, data, parser, 4, row_hdr6, hdr_style)
self.xls_write_row_header(ws, 5, row_partner_total, row_hdr_style)
self.xls_write_row_header(ws, 6, row_line, row_hdr_style, set_column_size=True)
#self.xls_write_row_header(ws, 4, row_forward_total, row_hdr_style)
row_count = 7
ws.horz_split_pos = row_count
for p in parser.objects:
r = ws.row(row_count)
self.xls_write_row(ws, p, data, parser,
row_count, row_partner_total, row_partner_style)
row_count += 1
if parser.initial_balance:
self.xls_write_row(ws, p, data, parser,
row_count, row_initial_total, row_style)
row_count += 1
for l in parser.lines(p):
self.xls_write_row(ws, l, data, parser,
row_count, row_line, row_style)
row_count += 1
pass
def __init__(self, name, table, rml=False,
parser=False, header=True, store=False):
super(report_request_tracking_xls, self).__init__(
name, table, rml, parser, header, store)
# Cell Styles
_xs = self.xls_styles
# header
# Report Column Headers format
rh_cell_format = _xs['bold'] + _xs['fill'] + _xs['borders_all']
self.rh_cell_style = xlwt.easyxf(rh_cell_format)
self.rh_cell_style_center = xlwt.easyxf(
rh_cell_format + _xs['center'])
self.rh_cell_style_right = xlwt.easyxf(rh_cell_format + _xs['right'])
# Partner Column Headers format
fill_blue = 'pattern: pattern solid, fore_color 27;'
ph_cell_format = _xs['bold'] + _xs['fill'] + _xs['borders_all']
self.ph_cell_style = xlwt.easyxf(ph_cell_format)
self.ph_cell_style_decimal = xlwt.easyxf(
ph_cell_format + _xs['right'],
num_format_str=report_xls.decimal_format)
# Partner Column Data format
pd_cell_format = _xs['borders_all']
self.pd_cell_style = xlwt.easyxf(pd_cell_format)
self.pd_cell_style_center = xlwt.easyxf(
pd_cell_format + _xs['center'])
self.pd_cell_style_date = xlwt.easyxf(
pd_cell_format + _xs['left'],
num_format_str=report_xls.date_format)
self.pd_cell_style_decimal = xlwt.easyxf(
pd_cell_format + _xs['right'],
num_format_str=report_xls.decimal_format)
# totals
rt_cell_format = _xs['bold'] + _xs['fill'] + _xs['borders_all']
self.rt_cell_style = xlwt.easyxf(rt_cell_format)
self.rt_cell_style_right = xlwt.easyxf(rt_cell_format + _xs['right'])
self.rt_cell_style_decimal = xlwt.easyxf(
rt_cell_format + _xs['right'],
num_format_str=report_xls.decimal_format)
# XLS Template
self.col_specs_template_overview = {
'no': {
'header': [1, 5, 'text', _render("_('No')")],
'lines': [1, 0, 'number', _render("p['no']")],
'totals': [1, 5, 'text', None]},
'date': {
'header': [1, 22, 'text', _render("_('Tanggal')")],
'lines': [1, 0, 'text', _render("p['date']")],
'totals': [1, 22, 'text', None]},
'branch_id': {
'header': [1, 22, 'text', _render("_('Cabang')")],
'lines': [1, 0, 'text', _render("p['branch_id']")],
'totals': [1, 22, 'text', _render("_('Total')")]},
'division': {
'header': [1, 22, 'text', _render("_('Divisi')")],
'lines': [1, 0, 'text', _render("p['division']")],
'totals': [1, 22, 'text', None]},
'number': {
'header': [1, 22, 'text', _render("_('Number')")],
'lines': [1, 0, 'text', _render("p['number']")],
'totals': [1, 22, 'text', None]},
'state_par': {
'header': [1, 22, 'text', _render("_('State Number (PAR)')")],
'lines': [1, 0, 'text', _render("p['state_par']")],
'totals': [1, 22, 'text', None]},
'partner_code': {
'header': [1, 22, 'text', _render("_('Supplier')")],
'lines': [1, 0, 'text', _render("p['partner_code']")],
'totals': [1, 22, 'text', None]},
'partner_name': {
'header': [1, 22, 'text', _render("_('Nama Supplier')")],
'lines': [1, 0, 'text', _render("p['partner_name']")],
'totals': [1, 22, 'text', None]},
'create_by': {
'header': [1, 22, 'text', _render("_('Created By')")],
'lines': [1, 0, 'text', _render("p['create_by']")],
'totals': [1, 22, 'text', None]},
'approved_by': {
'header': [1, 22, 'text', _render("_('Approved By')")],
'lines': [1, 0, 'text', _render("p['approved_by']")],
'totals': [1, 22, 'text', None]},
'par_type': {
'header': [1, 22, 'text', _render("_('Payment Request Type')")],
'lines': [1, 0, 'text', _render("p['par_type']")],
'totals': [1, 22, 'text', None]},
'spa_name': {
'header': [1, 22, 'text', _render("_('No. Supplier Payment')")],
'lines': [1, 0, 'text', _render("p['spa_name']")],
'totals': [1, 22, 'text', None]},
'spa_amount': {
'header': [1, 22, 'text', _render("_('Amount Supplier Payment')")],
'lines': [1, 0, 'number', _render("p['spa_amount']"), None, self.pd_cell_style_decimal],
'totals': [1, 22, 'number', _render("p['spa_amount']"), None, self.rt_cell_style_decimal]},
'spa_method': {
'header': [1, 22, 'text', _render("_('Payment Method')")],
'lines': [1, 0, 'text', _render("p['spa_method']")],
'totals': [1, 22, 'text', None]},
'a_code':{
'header': [1, 22, 'text', _render("_('No. Account')")],
'lines': [1, 0, 'text', _render("p['a_code']")],
'totals': [1, 22, 'text', None]},
'a_name':{
'header': [1, 22, 'text', _render("_('Nama Account')")],
'lines': [1, 0, 'text', _render("p['a_name']")],
'totals': [1, 22, 'text', None]},
'aa_combi' :{
'header': [1, 22, 'text', _render("_('Analytic Combination')")],
'lines': [1, 0, 'text', _render("p['aa_combi']")],
'totals': [1, 22, 'number', None]},
'aa_company' :{
'header': [1, 22, 'text', _render("_('Analytic Company')")],
'lines': [1, 0, 'text', _render("p['aa_company']")],
'totals': [1, 22, 'number', None]},
'aa_bisnisunit' :{
'header': [1, 22, 'text', _render("_('Analytic Bisnis Unit')")],
'lines': [1, 0, 'text', _render("p['aa_bisnisunit']")],
'totals': [1, 22, 'number', None]},
'aa_branch' :{
'header': [1, 22, 'text', _render("_('Analytic Branch')")],
'lines': [1, 0, 'text', _render("p['aa_branch']")],
'totals': [1, 22, 'number', None]},
'aa_costcenter' :{
'header': [1, 22, 'text', _render("_('Analytic Cost Center')")],
'lines': [1, 0, 'text', _render("p['aa_costcenter']")],
'totals': [1, 22, 'number', None]},
'approved_date': {
'header': [1, 22, 'text', _render("_('Approved On')")],
'lines': [1, 0, 'text', _render("p['approved_date']")],
'totals': [1, 22, 'text', None]},
'nomor_cba': {
'header': [1, 22, 'text', _render("_('No. Clearing Bank')")],
'lines': [1, 0, 'text', _render("p['nomor_cba']")],
'totals': [1, 22, 'text', None]},
'nomor_giro': {
'header': [1, 22, 'text', _render("_('Nomor Giro')")],
'lines': [1, 0, 'text', _render("p['nomor_giro']")],
'totals': [1, 22, 'text', None]},
'date_due': {
'header': [1, 22, 'text', _render("_('Tgl Jatuh Tempo')")],
'lines': [1, 0, 'text', _render("p['date_due']")],
'totals': [1, 22, 'text', None]},
'status_cair': {
'header': [1, 22, 'text', _render("_('Cair / Belum Cair')")],
'lines': [1, 0, 'text', _render("p['status_cair']")],
'totals': [1, 22, 'text', None]},
'memo': {
'header': [1, 22, 'text', _render("_('Memo')")],
'lines': [1, 0, 'text', _render("p['memo']")],
'totals': [1, 0, 'text', None]},
'ref': {
'header': [1, 22, 'text', _render("_('Ref')")],
'lines': [1, 0, 'text', _render("p['ref']")],
'totals': [1, 22, 'text', None]},
'description': {
'header': [1, 22, 'text', _render("_('Description')")],
'lines': [1, 0, 'text', _render("p['description']")],
'totals': [1, 22, 'text', None]},
'nama_account': {
'header': [1, 22, 'text', _render("_('Account')")],
'lines': [1, 0, 'text', _render("p['nama_account']")],
'totals': [1, 22, 'text', None]},
'no_btr': {
'header': [1, 22, 'text', _render("_('No HO to Branch')")],
'lines': [1, 0, 'text', _render("p['no_btr']")],
'totals': [1, 22, 'text', None]},
'no_bta': {
'header': [1, 22, 'text', _render("_('No BTA')")],
'lines': [1, 0, 'text', _render("p['no_bta']")],
'totals': [1, 22, 'text', None]},
'date_btr': {
'header': [1, 22, 'text', _render("_('Date HO to Branch')")],
'lines': [1, 0, 'text', _render("p['date_btr']")],
'totals': [1, 22, 'text', None]},
'date_bta': {
'header': [1, 22, 'text', _render("_('Date BTA')")],
'lines': [1, 0, 'text', _render("p['date_bta']")],
'totals': [1, 22, 'text', None]},
'state_btr': {
'header': [1, 22, 'text', _render("_('State HO to Branch')")],
'lines': [1, 0, 'text', _render("p['state_btr']")],
'totals': [1, 22, 'text', None]},
'state_bta': {
'header': [1, 22, 'text', _render("_('State BTA')")],
'lines': [1, 0, 'text', _render("p['state_bta']")],
'totals': [1, 22, 'text', None]},
'no_withdrawal': {
'header': [1, 22, 'text', _render("_('No Withdrawal')")],
'lines': [1, 0, 'text', _render("p['no_withdrawal']")],
'totals': [1, 22, 'text', None]},
'date_withdrawal': {
'header': [1, 22, 'text', _render("_('Date Withdrawal')")],
'lines': [1, 0, 'text', _render("p['date_withdrawal']")],
'totals': [1, 22, 'text', None]},
'state_withdrawal': {
'header': [1, 22, 'text', _render("_('State Withdrawal')")],
'lines': [1, 0, 'text', _render("p['state_withdrawal']")],
'totals': [1, 22, 'text', None]},
'total': {
'header': [1, 22, 'text', _render("_('Total')")],
'lines': [1, 0, 'number', _render("p['total']"), None, self.pd_cell_style_decimal],
'totals': [1, 22, 'number', _render("p['total']"), None, self.rt_cell_style_decimal]},
'net_total': {
'header': [1, 22, 'text', _render("_('Net Total')")],
'lines': [1, 0, 'number', _render("p['net_total']"), None, self.pd_cell_style_decimal],
'totals': [1, 22, 'number', _render("p['net_total']"), None, self.rt_cell_style_decimal]},
}
# XLS Template
self.col_specs_template_details = {
}
def generate_xls_report(self, _p, _xs, data, objects, wb):
wanted_list_overview = _p.wanted_list_overview
wanted_list_details = _p.wanted_list_details
self.col_specs_template_overview.update(_p.template_update_overview)
self.col_specs_template_details.update(_p.template_update_details)
_ = _p._
for r in _p.reports:
title_short = r['title_short'].replace('/', '-')
ws_o = wb.add_sheet(title_short)
for ws in [ws_o]:
ws.panes_frozen = True
ws.remove_splits = True
ws.portrait = 0 # Landscape
ws.fit_width_to_pages = 1
row_pos_o = 0
row_pos_d = 0
# set print header/footer
for ws in [ws_o]:
ws.header_str = self.xls_headers['standard']
ws.footer_str = self.xls_footers['standard']
# Title
## Company ##
cell_style = xlwt.easyxf(_xs['left'])
report_name = ' '.join(
[_p.company.name, r['title'],
_p.report_info])
c_specs_o = [
('report_name', 1, 0, 'text', report_name),
]
row_data = self.xls_row_template(c_specs_o, ['report_name'])
row_pos_o = self.xls_write_row(
ws_o, row_pos_o, row_data, row_style=cell_style)
## Text + Tgl ##
cell_style = xlwt.easyxf(_xs['xls_title'])
report_name = ' '.join(
[_('LAPORAN User Request Tracking Per Tanggal'), _(str(datetime.today().date())),
_p.report_info])
c_specs_o = [
('report_name', 1, 0, 'text', report_name),
]
row_data = self.xls_row_template(c_specs_o, ['report_name'])
row_pos_o = self.xls_write_row(
ws_o, row_pos_o, row_data, row_style=cell_style)
## Tanggal Jtp Start Date & End Date ##
cell_style = xlwt.easyxf(_xs['left'])
report_name = ' '.join(
[_('Tanggal Jatuh Tempo'), _('-' if data['start_date'] == False else str(data['start_date'])), _('s/d'), _('-' if data['end_date'] == False else str(data['end_date'])),
_p.report_info])
c_specs_o = [
('report_name', 1, 0, 'text', report_name),
]
row_data = self.xls_row_template(c_specs_o, ['report_name'])
row_pos_o = self.xls_write_row(
ws_o, row_pos_o, row_data, row_style=cell_style)
## Tanggal Trx Start Date & End Date ##
cell_style = xlwt.easyxf(_xs['left'])
report_name = ' '.join(
[_('Tanggal Transaksi'), _('-' if data['trx_start_date'] == False else str(data['trx_start_date'])), _('s/d'), _('-' if data['trx_end_date'] == False else str(data['trx_end_date'])),
_p.report_info])
c_specs_o = [
('report_name', 1, 0, 'text', report_name),
]
row_data = self.xls_row_template(c_specs_o, ['report_name'])
row_pos_o = self.xls_write_row(
ws_o, row_pos_o, row_data, row_style=cell_style)
row_pos_o += 1
# Report Column Headers
c_specs_o = map(
lambda x: self.render(
x, self.col_specs_template_overview, 'header',
render_space={'_': _p._}),
wanted_list_overview)
row_data = self.xls_row_template(
c_specs_o, [x[0] for x in c_specs_o])
row_pos_o = self.xls_write_row(
ws_o, row_pos_o, row_data, row_style=self.rh_cell_style,
set_column_size=True)
ws_o.set_horz_split_pos(row_pos_o)
row_data_begin = row_pos_o
# Columns and Rows
no = 0
for p in r['id_ai']:
c_specs_o = map(
lambda x: self.render(
x, self.col_specs_template_overview, 'lines'),
wanted_list_overview)
for x in c_specs_o :
if x[0] == 'no' :
no += 1
x[4] = no
row_data = self.xls_row_template(
c_specs_o, [x[0] for x in c_specs_o])
row_pos_o = self.xls_write_row(
ws_o, row_pos_o, row_data, row_style=self.pd_cell_style)
row_data_end = row_pos_o
# Totals
ws_o.write(row_pos_o, 0, None, self.rt_cell_style_decimal)
ws_o.write(row_pos_o, 1, 'Totals', self.ph_cell_style)
ws_o.write(row_pos_o, 2, None, self.rt_cell_style_decimal)
ws_o.write(row_pos_o, 3, None, self.rt_cell_style_decimal)
ws_o.write(row_pos_o, 4, None, self.rt_cell_style_decimal)
ws_o.write(row_pos_o, 5, None, self.rt_cell_style_decimal)
ws_o.write(row_pos_o, 6, None, self.rt_cell_style_decimal)
ws_o.write(row_pos_o, 7, None, self.rt_cell_style_decimal)
ws_o.write(row_pos_o, 8, None, self.rt_cell_style_decimal)
ws_o.write(row_pos_o, 9, None, self.rt_cell_style_decimal)
ws_o.write(row_pos_o, 10, None, self.rt_cell_style_decimal)
ws_o.write(row_pos_o, 11, None, self.rt_cell_style_decimal)
ws_o.write(row_pos_o, 12, None, self.rt_cell_style_decimal)
ws_o.write(row_pos_o, 13, None, self.rt_cell_style_decimal)
ws_o.write(row_pos_o, 14, None, self.rt_cell_style_decimal)
ws_o.write(row_pos_o, 15, None, self.rt_cell_style_decimal)
ws_o.write(row_pos_o, 16, None, self.rt_cell_style_decimal)
ws_o.write(row_pos_o, 17, None, self.rt_cell_style_decimal)
ws_o.write(row_pos_o, 18, None, self.rt_cell_style_decimal)
ws_o.write(row_pos_o, 19, None, self.rt_cell_style_decimal)
ws_o.write(row_pos_o, 20, None, self.rt_cell_style_decimal)
ws_o.write(row_pos_o, 21, None, self.rt_cell_style_decimal)
ws_o.write(row_pos_o, 22, xlwt.Formula("SUM(W"+str(row_data_begin)+":W"+str(row_data_end)+")"), self.rt_cell_style_decimal)
ws_o.write(row_pos_o, 23, xlwt.Formula("SUM(X"+str(row_data_begin)+":X"+str(row_data_end)+")"), self.rt_cell_style_decimal)
ws_o.write(row_pos_o, 24, None, self.rt_cell_style_decimal)
ws_o.write(row_pos_o, 25, xlwt.Formula("SUM(Z"+str(row_data_begin)+":Z"+str(row_data_end)+")"), self.rt_cell_style_decimal)
ws_o.write(row_pos_o, 26, None, self.rt_cell_style_decimal)
ws_o.write(row_pos_o, 27, None, self.rt_cell_style_decimal)
ws_o.write(row_pos_o, 28, None, self.rt_cell_style_decimal)
ws_o.write(row_pos_o, 29, None, self.rt_cell_style_decimal)
ws_o.write(row_pos_o, 30, None, self.rt_cell_style_decimal)
ws_o.write(row_pos_o, 31, None, self.rt_cell_style_decimal)
ws_o.write(row_pos_o, 32, None, self.rt_cell_style_decimal)
ws_o.write(row_pos_o, 33, None, self.rt_cell_style_decimal)
ws_o.write(row_pos_o, 34, None, self.rt_cell_style_decimal)
ws_o.write(row_pos_o, 35, None, self.rt_cell_style_decimal)
# Footer
ws_o.write(row_pos_o + 1, 0, None)
ws_o.write(row_pos_o + 2, 0, _p.report_date + ' ' + str(self.pool.get('res.users').browse(self.cr, self.uid, self.uid).name))
)
CELL_TYPES = {
xlrd.XL_CELL_BLANK: fields.TextField,
xlrd.XL_CELL_DATE: fields.DatetimeField,
xlrd.XL_CELL_ERROR: None,
xlrd.XL_CELL_TEXT: fields.TextField,
xlrd.XL_CELL_BOOLEAN: fields.BoolField,
xlrd.XL_CELL_EMPTY: None,
xlrd.XL_CELL_NUMBER: fields.FloatField,
}
# TODO: add more formatting styles for other types such as currency
# TODO: styles may be influenced by locale
FORMATTING_STYLES = {
fields.DateField: xlwt.easyxf(num_format_str="yyyy-mm-dd"),
fields.DatetimeField: xlwt.easyxf(num_format_str="yyyy-mm-dd hh:mm:ss"),
fields.PercentField: xlwt.easyxf(num_format_str="0.00%"),
}
def _python_to_xls(field_types):
def convert_value(field_type, value):
data = {}
if field_type in FORMATTING_STYLES:
data["style"] = FORMATTING_STYLES[field_type]
if field_type in (
fields.BinaryField,
fields.BoolField,
fields.DateField,
sheet1.write(1, 0, "Irms [A]")
sheet1.write(2, 0, "I1 [A]") # Fundamental
sheet1.write(3, 0, "THDi [%]")
sheet1.write(4, 0, "PWHC/I1 [%]")
sheet1.write(1, 1, rmsI)
sheet1.write(2, 1, RMSI[1])
sheet1.write(3, 1, THDI)
sheet1.write(4, 1, 100 * PWHC / RMSI[1])
sheet1.write(1, 2, "RMS Current")
sheet1.write(2, 2, "RMS Fundamental Current")
sheet1.write(3, 2, "Total Harmonic Current Distortion")
sheet1.write(4, 2, "Partial Weighted Harmonic Current")
sheet1.write(6, 0, "Harmonic content", easyxf('font:bold 1'))
sheet1.write(6, 1, "[%]", easyxf('font:bold 1'))
sheet1.write(6, 2, "[A rms]", easyxf('font:bold 1'))
index = [
'I1', 'I2', 'I3', 'I4', 'I5', 'I6', 'I7', 'I8', 'I9', 'I10', 'I11', 'I12',
'I13', 'I14', 'I15', 'I16', 'I17', 'I18', 'I19', 'I20', 'I21', 'I22',
'I23', 'I24', 'I25', 'I26', 'I27', 'I28', 'I29', 'I30', 'I31', 'I32',
'I33', 'I34', 'I35', 'I36', 'I37', 'I38', 'I39', 'I40'
]
i = 6
for n in index:
i = i + 1
if i % 2 == 0:
import xlwt
import random
from datetime import datetime
style1 = xlwt.easyxf('font: name Arial, color-index blue, italic on',
num_format_str='#,##0')
style2 = xlwt.easyxf(num_format_str='YYYY-MM-DD-HH-MM-SS')
wb = xlwt.Workbook()
ws = wb.add_sheet('Generated by Python')
for row in range(0, 10000):
ws.write(row, 0, datetime.now(), style2)
for column in range(1, 11):
ws.write(row, column, xlwt.Formula('randbetween(1000,100000)'))
i = 1
while i <= 20:
wb.add_sheet('Generated by Python - ' + str(i))
i = i + 1
wb.save('007_' + str(random.randint(10000, 99999)) + '.xls')
def writeCatalogsToSpreadsheet(grouping, webSiteName, catalogs, outputDir):
# Each location is a new sheet in the overall spreadsheet.
for currentCatalogName, currentCatalog in catalogs.iteritems():
# Extract the details of interest to use for the file path and name.
storeLocation = currentCatalogName.split("-")[1]
catalogDate = currentCatalog.mValidFrom
# Create the output dir so it has a scrape directory.
currentOutputDir = createOutputDir(outputDir, [
"XLSX",
webSiteName + "-" + storeLocation,
])
# Create the path to the file.
pathToFile = createPathToFile(currentOutputDir, webSiteName,
storeLocation, catalogDate)
# Must create a new name if the file already exists.
SPREAD_SHEET_NAME, uniqueId = createFileName(pathToFile, ".xls")
# Store the unique ID so that we can place all the images for this
# catalog into same directory.
currentCatalog.mUniqueId = uniqueId
# Create a new work book.
book = xlwt.Workbook(encoding="utf8")
# Might be nice to create a nice colour.
xlwt.add_palette_colour("custom_grey", 0x21)
book.set_colour_RGB(0x21, 128, 128, 128)
# Styles to be used in spreadsheet.
headerStyle = xlwt.easyxf(HEADER_FONT + \
HEADER_PATTERN + \
HEADER_ALIGN)
bodyStyle = xlwt.easyxf(BODY_FONT)
bodyStyleCenter = xlwt.easyxf(BODY_FONT_CENTER)
# Spreadsheet has a limit of 31 characters for a sheet's name.
currentSheetName = currentCatalogName.split(
"-")[0] + currentCatalogName.split("-")[1]
currentSheet = book.add_sheet(currentSheetName[:31])
# Keep track of the current active row in the spreadsheet.
activeRow = 0
# Write out the Title information.
for titleName, titleDetails in TITLES.iteritems():
# Populate the title text.
currentSheet.write(activeRow, titleDetails["ColumnNum"],
titleName.upper(), headerStyle)
# Set the header to be a little wider 25% than just the title.
titleDetails["ColumnMaxWidth"] = int(len(titleName) * 1.25)
# Set the title row height. (Magic 2 for a fuller effect)
currentSheet.row(
activeRow).height = POINTS_PER_CHAR_HEIGHT * FONT_SIZE * 2
# Write out the catalog items for each page in the catalog.
for currentPage, currentItems in sorted(
currentCatalog.mPages.iteritems()):
# Sort items from left most pixel postion to right most pixel position.
# If two store times at the same left pixel position then sort from
# top most pixel postion to bottom most pixel position.
for currentItem in sorted(currentItems,
key=lambda x: (-x.mTop, x.mLeft)):
# Only write the row if there is a valid title.
if not currentItem.mTitleCleansed:
continue
# Price of current item.
itemPrice = 0
# Bump the current active row just before we write.
activeRow += 1
# Set the height of the row.
currentSheet.row(activeRow).height = int(
POINTS_PER_CHAR_HEIGHT * FONT_SIZE * 1.5)
# Write data for the current data item to the current spreadsheet page.
# Customer wanted to call the JSON title as the description, so renamed it here.
writeDataToSheet(currentSheet, activeRow,
TITLES["Complete Description"],
currentItem.mTitleCleansed, bodyStyle)
writeDataToSheet(currentSheet, activeRow,
TITLES["Catalog Page"], currentPage,
bodyStyleCenter)
writeDataToSheet(currentSheet, activeRow,
TITLES["Promotion Type"],
currentItem.mPromotionType, bodyStyleCenter)
writeDataToSheet(currentSheet, activeRow,
TITLES["Multibuy Qty"], currentItem.mMultibuy,
bodyStyleCenter)
# if currentItem.mPromotionType == 0:
# writeDataToSheet(currentSheet, activeRow,
# TITLES["Price"], "XXXX", bodyStyle)
# else:
writeDataToSheet(currentSheet, activeRow, TITLES["Price"],
currentItem.mPriceCleansed, bodyStyle)
writeDataToSheet(currentSheet, activeRow,
TITLES["Department/Category"],
currentItem.mCategory, bodyStyleCenter)
writeDataToSheet(currentSheet, activeRow,
TITLES["Valid Start Date"],
currentItem.mStartDate, bodyStyleCenter)
writeDataToSheet(currentSheet, activeRow, TITLES["Item Type"],
currentItem.mType, bodyStyleCenter)
writeDataToSheet(currentSheet, activeRow,
TITLES["Description"], currentItem.mTitle,
bodyStyle)
# Customer wanted to calle the JSON description as the details, so renamed it here.
writeDataToSheet(currentSheet, activeRow, TITLES["Details"],
currentItem.mDescription, bodyStyle)
writeDataToSheet(currentSheet, activeRow,
TITLES["Valid End Date"],
currentItem.mEndDate, bodyStyleCenter)
writeDataToSheet(currentSheet, activeRow, TITLES["Price Raw"],
currentItem.mPrice, bodyStyle)
# Update the width of the columns.
for titleName, titleDetails in TITLES.iteritems():
# Don't let the columns get too wide, as it does not look very nice.
COL_WIDTH = 50
if TITLES[titleName]["ColumnMaxWidth"] < COL_WIDTH:
currentSheet.col(
TITLES[titleName]["ColumnNum"]
).width = 256 * TITLES[titleName]["ColumnMaxWidth"]
else:
currentSheet.col(
TITLES[titleName]["ColumnNum"]).width = 256 * COL_WIDTH
# Save out workbook to disk.
book.save(SPREAD_SHEET_NAME)
return
# -*- coding: utf-8 -*-
from __builtin__ import xrange
import itertools
from odoo import api, fields, models, _
import xlwt
from StringIO import StringIO
import base64
from datetime import datetime, timedelta
style_title = xlwt.easyxf(
'font: bold 1, name Tahoma, height 160;'
'align: vertical center, horizontal center, wrap on;'
'borders: left thin, right thin, top thin, bottom thin;'
'pattern: pattern solid, pattern_fore_colour gray25, pattern_back_colour black;'
)
style_filter = xlwt.easyxf(
'font: bold 1, name Tahoma, height 220;'
'align: vertical center, horizontal center, wrap on;')
style_footer = xlwt.easyxf(
'font: bold 1, name Tahoma, height 160;'
'align: vertical center, horizontal center, wrap on;')
base_style = xlwt.easyxf('align: wrap yes')
class CompareProduct(models.TransientModel):
_name = 'compare.product'
stock_warehouse = fields.Many2one('stock.warehouse',
'Warehouse',
required=True)
def make_excel(ifile, ofile, scenario):
tech = defaultdict(list)
tech_set = set()
sector = set()
period = []
emiss = set()
row = 0
count = 0
sheet = []
book = []
book_no = 0
flag = None
flag1 = None
flag2 = None
i = 0 # Sheet ID
header = ['Technologies', ]
header_emiss = []
header_v = ['Technologies', 'Output Name', 'Vintage', 'Cost']
tables = {"Output_VFlow_Out" : ["Activity", "vflow_out"], "Output_CapacityByPeriodAndTech" : ["Capacity", "capacity"], "Output_Emissions" : ["Emissions", "emissions"], "Output_Costs" : ["Costs", "output_cost"]}
if ifile is None :
raise "You did not specify the input file, remember to use '-i' option"
print "Use as :\n python DB_to_Excel.py -i <input_file> (Optional -o <output_excel_file_name_only>)\n Use -h for help."
sys.exit(2)
else :
file_type = re.search(r"(\w+)\.(\w+)\b", ifile) # Extract the input filename and extension
if not file_type :
print "The file type %s is not recognized. Use a db file." % ifile
sys.exit(2)
if ofile is None :
ofile = file_type.group(1)
print "Look for output in %s_*.xls" % ofile
con = sqlite3.connect(ifile)
cur = con.cursor() # a database cursor is a control structure that enables traversal over the records in a database
con.text_factory = str #this ensures data is explored with the correct UTF-8 encoding
for k in tables.keys() :
if not scenario :
cur.execute("SELECT DISTINCT scenario FROM "+k)
for val in cur :
scenario.add(val[0])
for axy in cur.execute("SELECT count(*) FROM sqlite_master WHERE type='table' AND name='technologies';") :
if axy[0] :
fields = [ads[1] for ads in cur.execute('PRAGMA table_info(technologies)')]
if 'sector' in fields :
cur.execute("SELECT sector FROM technologies")
for val in cur :
sector.add(val[0])
if not sector :
sector.add('0')
else :
flag = 1
if flag is None :
cur.execute("SELECT DISTINCT tech FROM "+k)
for val in cur :
tech['0'].append(val[0])
tech_set.add(val[0])
else :
for x in sector :
cur.execute("SELECT DISTINCT tech FROM technologies WHERE sector is '"+x+"'")
for val in cur :
if val[0] not in tech[x] :
tech[x].append(val[0])
tech_set.add(val[0])
if k is "Output_Emissions" :
cur.execute("SELECT DISTINCT emissions_comm FROM "+k)
for val in cur :
emiss.add(val[0])
if k is "Output_Costs" :
pass
else:#if k is not "Output_V_Capacity":
cur.execute("SELECT DISTINCT t_periods FROM "+k)
for val in cur :
val = str(val[0])
if val not in period :
period.append(val)
header.append(val)
header[1:].sort()
period.sort()
header_emiss = header[:]
header_emiss.insert(1, "Emission Commodity")
ostyle = easyxf('alignment: vertical centre, horizontal centre;')
ostyle_header = easyxf('alignment: vertical centre, horizontal centre, wrap True;')
for scene in scenario :
book.append(xlwt.Workbook(encoding="utf-8"))
for z in sector :
for a in tables.keys() :
if z is '0' :
sheet_name = str(tables[a][0])
if a is "Output_Costs" :
flag2 = '1'
if a is "Output_Emissions" :
flag1 = '1'
elif (a is "Output_Costs" and flag2 is None) :
sheet_name = str(tables[a][0])
flag2 = '1'
elif (a is "Output_Emissions" and flag1 is None) :
sheet_name = str(tables[a][0])
flag1 = '1'
elif (a is "Output_Costs" and flag2 is not None) or (a is "Output_Emissions" and flag1 is not None) :
continue
else :
sheet_name = str(tables[a][0])+"_"+str(z)
sheet.append(book[book_no].add_sheet(sheet_name))
if a is "Output_Emissions" and flag1 is '1':
for col in range(0, len(header_emiss)) :
sheet[i].write(row, col, header_emiss[col], ostyle_header)
sheet[i].col(col).width_in_pixels = 3300
row += 1
for x in tech_set :
for q in emiss :
sheet[i].write(row, 0, x, ostyle)
sheet[i].write(row, 1, q, ostyle)
for y in period :
cur.execute("SELECT sum("+tables[a][1]+") FROM "+a+" WHERE t_periods is '"+y+"' and scenario is '"+scene+"' and tech is '"+x+"' and emissions_comm is '"+q+"'")
xyz = cur.fetchone()
if xyz[0] is not None :
sheet[i].write(row, count+2, float(xyz[0]), ostyle)
else :
sheet[i].write(row, count+2, '-', ostyle)
count += 1
row += 1
count = 0
row = 0
i += 1
flag1 = '2'
elif a is "Output_Costs" and flag2 is '1':
for col in range(0, len(header_v)) :
sheet[i].write(row, col, header_v[col], ostyle_header)
sheet[i].col(col).width_in_pixels = 3300
row += 1
for x in tech_set :
cur.execute("SELECT output_name, vintage, "+tables[a][1]+" FROM "+a+" WHERE scenario is '"+scene+"' and tech is '"+x+"'")
for xyz in cur :
if xyz[0] is not None :
sheet[i].write(row, 0, x, ostyle)
sheet[i].write(row, count+1, xyz[0], ostyle)
sheet[i].write(row, count+2, xyz[1], ostyle)
sheet[i].write(row, count+3, xyz[2], ostyle)
else :
sheet[i].write(row, 0, x, ostyle)
sheet[i].write(row, count+1, '-', ostyle)
sheet[i].write(row, count+2, '-', ostyle)
sheet[i].write(row, count+3, '-', ostyle)
row += 1
count = 0
row = 0
i += 1
flag2 = '2'
elif (a is "Output_Costs" and flag2 is '2') or (a is "Output_Emissions" and flag1 is '2'):
pass
elif a is not "Output_V_Capacity":
for col in range(0, len(header)) :
sheet[i].write(row, col, header[col], ostyle_header)
sheet[i].col(col).width_in_pixels = 3300
row += 1
for x in tech[z] :
sheet[i].write(row, 0, x, ostyle)
for y in period :
cur.execute("SELECT sum("+tables[a][1]+") FROM "+a+" WHERE t_periods is '"+y+"' and scenario is '"+scene+"' and tech is '"+x+"'")
xyz = cur.fetchone()
if xyz[0] is not None :
sheet[i].write(row, count+1, float(xyz[0]), ostyle)
else :
sheet[i].write(row, count+1, '-', ostyle)
count += 1
row += 1
count = 0
row = 0
i += 1
if len(scenario) is 1:
book[book_no].save(ofile+".xls")
else :
book[book_no].save(ofile+"_"+scene+".xls")
book_no += 1
flag1 = None
flag2 = None
cur.close()
con.close()
def distribute(request):
result = [[0 for i in range(6)] for j in range(7)]
time1_2 = []
time3_4 = []
time5_6 = []
time7_8 = []
time9_10 = []
time11_12 = []
is_time1_2_free = True
is_time3_4_free = True
is_time5_6_free = True
is_time7_8_free = True
is_time9_10_free = True
is_time11_12_free = True
student_grade = {}
result_tmp = []
free_student = []
if request.POST:
if request.POST['submit'] == u"自动分配":
for is_single_week in range(0, 2):
for week_day in range(1, 8):
time1_2 = []
time3_4 = []
time5_6 = []
time7_8 = []
time9_10 = []
time11_12 = []
is_time1_2_free = True
is_time3_4_free = True
is_time5_6_free = True
is_time7_8_free = True
is_time9_10_free = True
is_time11_12_free = True
for member in Member.objects.all():
if member.stuID[3] != '4':
continue
is_time1_2_free = True
is_time3_4_free = True
is_time5_6_free = True
is_time7_8_free = True
is_time9_10_free = True
is_time11_12_free = True
student = Students.objects.filter(stu_no=member.stuID)
student_grade[student[0].name] = 0
courseTable = CourseTable.objects.filter(
student=student)
for ct in courseTable:
courses = Course.objects.filter(
course_id=ct.course_id)
for course in courses:
if course.weekday != '0':
if is_single_week:
if (course.weekday[1] == str(week_day)
) and (course.weekday[0] == '0' or
course.weekday[0] == '1'):
course_time = course.time.strip(
"[']")
masses = course_time.split(",")
for mass in masses:
if mass == '1' or mass == '2':
is_time1_2_free = False
if mass == '3' or mass == '4':
is_time3_4_free = False
if mass == '5' or mass == '6':
is_time5_6_free = False
if mass == '7' or mass == '8':
is_time7_8_free = False
if mass == '9' or mass == '10':
is_time9_10_free = False
if mass == '11' or mass == '12':
is_time11_12_free = False
else:
if (course.weekday[1] == str(week_day)
) and (course.weekday[0] == '0' or
course.weekday[0] == '2'):
course_time = course.time.strip(
"[']")
masses = course_time.split(",")
for mass in masses:
if mass == '1' or mass == '2':
is_time1_2_free = False
if mass == '3' or mass == '4':
is_time3_4_free = False
if mass == '5' or mass == '6':
is_time5_6_free = False
if mass == '7' or mass == '8':
is_time7_8_free = False
if mass == '9' or mass == '10':
is_time9_10_free = False
if mass == '11' or mass == '12':
is_time11_12_free = False
if is_time1_2_free:
time1_2.append(student[0].name)
if is_time3_4_free:
time3_4.append(student[0].name)
if is_time5_6_free:
time5_6.append(student[0].name)
if is_time7_8_free:
time7_8.append(student[0].name)
if is_time9_10_free:
time9_10.append(student[0].name)
if is_time11_12_free:
time11_12.append(student[0].name)
if is_single_week == 0:
result[week_day - 1][0] = time1_2
result[week_day - 1][1] = time3_4
result[week_day - 1][2] = time5_6
result[week_day - 1][3] = time7_8
result[week_day - 1][4] = time9_10
result[week_day - 1][5] = time11_12
else:
for each_student in time1_2:
if each_student not in result[week_day - 1][0]:
result[week_day - 1][0].append(each_student +
u"(单周)")
for each_student in time3_4:
if each_student not in result[week_day - 1][1]:
result[week_day - 1][1].append(each_student +
u"(单周)")
for each_student in time5_6:
if each_student not in result[week_day - 1][2]:
result[week_day - 1][2].append(each_student +
u"(单周)")
for each_student in time7_8:
if each_student not in result[week_day - 1][3]:
result[week_day - 1][3].append(each_student +
u"(单周)")
for each_student in time9_10:
if each_student not in result[week_day - 1][4]:
result[week_day - 1][4].append(each_student +
u"(单周)")
for each_student in time11_12:
if each_student not in result[week_day - 1][5]:
result[week_day - 1][5].append(each_student +
u"(单周)")
for index, each_student in enumerate(result[week_day -
1][0]):
if each_student.replace(u"(单周)",
"") not in time1_2:
result[week_day - 1][0][index] = result[
week_day - 1][0][index] + u"(双周)"
for index, each_student in enumerate(result[week_day -
1][1]):
if each_student.replace(u"(单周)",
"") not in time3_4:
result[week_day - 1][1][index] = result[
week_day - 1][1][index] + u"(双周)"
for index, each_student in enumerate(result[week_day -
1][2]):
if each_student.replace(u"(单周)",
"") not in time5_6:
result[week_day - 1][2][index] = result[
week_day - 1][2][index] + u"(双周)"
for index, each_student in enumerate(result[week_day -
1][3]):
if each_student.replace(u"(单周)",
"") not in time7_8:
result[week_day - 1][3][index] = result[
week_day - 1][3][index] + u"(双周)"
for index, each_student in enumerate(result[week_day -
1][4]):
if each_student.replace(u"(单周)",
"") not in time9_10:
result[week_day - 1][4][index] = result[
week_day - 1][4][index] + u"(双周)"
for index, each_student in enumerate(result[week_day -
1][5]):
if each_student.replace(u"(单周)",
"") not in time11_12:
result[week_day - 1][5][index] = result[
week_day - 1][5][index] + u"(双周)"
result_tmp = copy.deepcopy(result)
for i in range(0, 5):
for j in range(1, 4):
for each_student in result[i][j]:
if each_student.find("(") != -1:
each_student = each_student.replace(u"(双周)", "")
each_student = each_student.replace(u"(单周)", "")
student_grade[each_student] = student_grade[
each_student] + 0.5
else:
student_grade[
each_student] = student_grade[each_student] + 1
for key in student_grade:
print key + "=" + str(student_grade[key])
selected_student = []
tmp = []
for i in range(0, 5):
for j in range(1, 4):
tmp.append((i, j, len(result[i][j])))
#sort
for i in range(0, len(tmp)):
for j in range(i + 1, len(tmp)):
if tmp[i][2] > tmp[j][2]:
tmp[i], tmp[j] = tmp[j], tmp[i]
for i in range(0, 5):
for j in range(1, 4):
for m in range(0, len(result[i][j])):
for n in range(m + 1, len(result[i][j])):
tmp_m = result[i][j][m].replace(u"(双周)", "")
tmp_m = tmp_m.replace(u"(单周)", "")
tmp_n = result[i][j][n].replace(u"(双周)", "")
tmp_n = tmp_n.replace(u"(单周)", "")
if student_grade[tmp_m] > student_grade[tmp_n]:
result[i][j][m], result[i][j][n] = result[i][
j][n], result[i][j][m]
print tmp
#distribute
for i in range(0, len(tmp)):
if len(result[tmp[i][0]][tmp[i][1]]) <= 1:
for each in result[tmp[i][0]][tmp[i][1]]:
tmp_each = each.replace(u"(双周)", "")
tmp_each = tmp_each.replace(u"(单周)", "")
if tmp_each not in selected_student:
selected_student.append(tmp_each)
else:
count = 0
tmp_list = []
for each in result[tmp[i][0]][tmp[i][1]]:
tmp_each = each.replace(u"(双周)", "")
tmp_each = tmp_each.replace(u"(单周)", "")
if tmp_each not in selected_student:
if count == 1:
if (each.find(u"(双周)") != -1
and tmp_list[0].find(u"(双周)") != -1
) or (each.find(u"(单周)") != -1
and tmp_list[0].find(u"(单周)") != -1):
continue
selected_student.append(tmp_each)
tmp_list.append(each)
count = count + 1
if count == 2:
break
result[tmp[i][0]][tmp[i][1]] = tmp_list
for i in range(0, 5):
result[i][0] = []
for member in Member.objects.all():
student = Students.objects.filter(stu_no=member.stuID)
if (student[0].name not in selected_student
) and student[0].stu_no[3] == '4':
free_student.append(student[0].name)
book = xlwt.Workbook(encoding='utf-8', style_compression=0)
sheet1 = book.add_sheet(u"值班表", cell_overwrite_ok=True)
sheet2 = book.add_sheet(u"参考", cell_overwrite_ok=True)
style = xlwt.easyxf('align: wrap on')
text = [u'课程表', u'星期一', u'星期二', u'星期三', u'星期四', u'星期五']
for index, each in enumerate(text):
sheet1.write(0, index, each, style)
sheet2.write(0, index, each, style)
sheet1.col(index).width = 10000
sheet2.col(index).width = 10000
text = [u'第1、2节', u'第3、4节', u'第5、6节', u'第7、8节']
for index, each in enumerate(text):
sheet1.row(index + 1).height = 500
sheet2.row(index + 1).height = 2000
sheet1.write(index + 1, 0, each, style)
sheet2.write(index + 1, 0, each, style)
for i in range(0, 5):
sheet1.write(index + 1, i + 1, ",".join(result[i][index]),
style)
sheet2.write(index + 1, i + 1,
",".join(result_tmp[i][index]), style)
book.save("FreeTimeCount/info/distribute.xls")
else:
def file_iterator(file_name, chunk_size=512):
with open(file_name) as f:
while True:
c = f.read(chunk_size)
if c:
yield c
else:
break
the_file_name = "FreeTimeCount/info/distribute.xls"
response = StreamingHttpResponse(file_iterator(the_file_name))
response['Content-Type'] = 'application/octet-stream'
response[
'Content-Disposition'] = 'attachment;filename="{0}"'.format(
the_file_name)
return response
else:
print "not a post"
print result_tmp
response = TemplateResponse(
request, 'FreeTimeCount/distribute.html', {
"result": json.dumps(result),
"result_tmp": json.dumps(result_tmp),
"free_student": json.dumps(free_student)
})
return response
def generate_xls_report(self, parser, filters, obj, workbook):
worksheet = workbook.add_sheet(('Laporan Summary Pegawai'))
worksheet.panes_frozen = True
worksheet.remove_splits = True
worksheet.portrait = True # Landscape
worksheet.fit_wiresult_datah_to_pages = 1
worksheet.col(1).wiresult_datah = len("ABCDEFG") * 1024
# Specifying columns, the order doesn't matter
# lamda d,f,p: is a function who has filter,data,parser as the parameters it is expected to the value of the column
cols_specs = [
# ('header', column_span, column_type, lamda function)
# Infos
('Company', 9, 0, 'text', lambda x, d, p: p.get_title('', filters)
),
('Title', 9, 0, 'text', lambda x, d, p: 'Laporan Rekapitulasi'),
('Period', 9, 0, 'text', lambda x, d, p: p.get_period(filters)),
# Top Headers
# ('Penambahan', 2, 100, 'text', lambda x, d, p: 'Penambahan'),
# Main Headers / Rows
('No', 1, 50, 'text', lambda x, d, p: str(d['is_kepala_opd'])),
('Nama Pegawai', 1, 250, 'text',
lambda x, d, p: d['employee_id']['name']),
('NIP', 1, 100, 'text', lambda x, d, p: d['employee_id']['nip']),
('Jabatan', 1, 250, 'text',
lambda x, d, p: d['employee_id']['job_id']['name']),
('Nama OPD', 1, 250, 'text',
lambda x, d, p: d['employee_id']['company_id']['name']),
('Biro', 1, 250, 'text',
lambda x, d, p: d['employee_id']['biro_id']['name']),
('Bidang', 1, 250, 'text',
lambda x, d, p: d['employee_id']['department_id']['name']),
('Nilai SKP', 1, 90, 'number',
lambda x, d, p: d['nilai_skp_percent']),
('Nilai Perilaku', 1, 90, 'number',
lambda x, d, p: d['nilai_perilaku_percent']),
('Nilai Tambahan', 1, 90, 'number',
lambda x, d, p: d['fn_nilai_tambahan']),
('Nilai Kreatifitas', 1, 90, 'number',
lambda x, d, p: d['fn_nilai_kreatifitas']),
('Nilai Total', 1, 100, 'number',
lambda x, d, p: d['nilai_total']),
# Misc
('single_empty_column', 1, 0, 'text', lambda x, d, p: ''),
('triple_empty_column', 3, 0, 'text', lambda x, d, p: ''),
('quadruple_empty_column', 4, 0, 'text', lambda x, d, p: ''),
]
row_spec_value = [
'Nama Pegawai', 'NIP', 'Jabatan', 'Bidang', 'Biro', 'Nama OPD',
'Nilai SKP', 'Nilai Perilaku', 'Nilai Tambahan',
'Nilai Kreatifitas', 'Nilai Total'
]
# Row templates (Order Matters, this joins the columns that are specified in the second parameter)
company_template = self.xls_row_template(
cols_specs, ['single_empty_column', 'Company'])
title_template = self.xls_row_template(
cols_specs, ['single_empty_column', 'Title'])
period_template = self.xls_row_template(
cols_specs, ['single_empty_column', 'Period'])
#top_header_template = self.xls_row_template(cols_specs, ['quadruple_empty_column','Penambahan','Pengurangan','single_empty_column'])
row_template = self.xls_row_template(cols_specs, row_spec_value)
#sums_template = self.xls_row_template(cols_specs, ['triple_empty_column','saldo_awal_sum','penjualan_sum','penjualan_jm_sum','pelunasan_sum','pelunasan_jm_sum','saldo_akhir_sum'])
empty_row_template = self.xls_row_template(cols_specs,
['single_empty_column'])
# Styles (It's used for writing rows / headers)
row_normal_style = xlwt.easyxf(num_format_str='#,##0.00;(#,##0.00)')
row_normal_odd_style = xlwt.easyxf(
'pattern: pattern solid, fore_color silver_ega;',
num_format_str='#,##0.00;(#,##0.00)')
info_style = xlwt.easyxf(
'font: height 200, name Arial, colour_index white, bold on, italic off; align: wrap on, vert centre;pattern: pattern solid, fore_color gray50;',
num_format_str='#,##0.00;(#,##0.00)')
top_style = xlwt.easyxf(
'font: height 200, name Arial, colour_index white, bold on, italic off; align: wrap on, vert centre, horiz center;pattern: pattern solid, fore_color orange;',
num_format_str='#,##0.00;(#,##0.00)')
header_style = xlwt.easyxf(
'font: height 200, name Arial, colour_index white, bold on, italic off; align: wrap on, vert centre;pattern: pattern solid, fore_color green;',
num_format_str='#,##0.00;(#,##0.00)')
sum_style = xlwt.easyxf(
'font: height 200, name Arial, colour_index white, bold on, italic off; align: wrap on, vert centre;pattern: pattern solid, fore_color gray50;',
num_format_str='#,##0.00;(#,##0.00)')
# Write infos
# xls_write_row(worksheet, filters, data parser, row_number, template, style)
self.xls_write_row(worksheet, filters, None, parser, 0,
empty_row_template, row_normal_style)
self.xls_write_row(worksheet, filters, None, parser, 1,
company_template, info_style)
self.xls_write_row(worksheet, filters, None, parser, 2, title_template,
info_style)
self.xls_write_row(worksheet, filters, None, parser, 3,
period_template, info_style)
self.xls_write_row(worksheet, filters, None, parser, 4,
empty_row_template, row_normal_style)
# Write top header
#self.xls_write_row(worksheet, filters, None, parser, 5, top_header_template, top_style)
# Write headers (It uses the first parameter of cols_specs)
self.xls_write_row_header(worksheet,
6,
row_template,
header_style,
set_column_size=True)
row_count = 7
result = parser.get_skp_recap_report_raw(filters)
for skp_recap_data in result:
# Write Rows
style = row_normal_style
if (row_count % 2 == 0):
style = row_normal_odd_style
self.xls_write_row(worksheet, filters, skp_recap_data, parser,
row_count, row_template, style)
row_count += 1
def main(process_id, concentration_source, file_id, default_norm_dna,
default_vol):
lims = Lims(config.BASEURI, config.USERNAME, config.PASSWORD)
process = Process(lims, id=process_id)
book = xlwt.Workbook()
sheet1 = book.add_sheet('Sheet1')
inputs = []
outputs = []
for i, o in process.input_output_maps:
output = o['uri']
if o and o['output-type'] == 'Analyte' and o[
'output-generation-type'] == 'PerInput':
input = i['uri']
inputs.append(input)
outputs.append(output)
lims.get_batch(inputs + outputs)
if concentration_source == "sample":
samples = [input.samples[0] for input in inputs]
lims.get_batch(samples)
try:
concentrations = [
sample.udf['Sample conc. (ng/ul)'] for sample in samples
]
except KeyError:
print "Missing value for 'Sample conc. (ng/ul)'."
sys.exit(1)
elif concentration_source == "quantit":
# Look for sibling processes with QC information. Note that this only works
# because we know that the artifacts are generated directly above in the workflow,
# and go through a single Quant-iT step. If inputs were e.g. the root analyte, then
# this procedure could give unexpected results.
processes = sorted(lims.get_processes(
inputartifactlimsid=[input.id for input in inputs]),
key=lambda proc: proc.id)
concentrations = []
missing = []
for input in inputs:
qi_conc = None
for qc_process in processes:
if qc_process.type_name.startswith("Quant-iT"):
for i, o in qc_process.input_output_maps:
if i['uri'].id == input.id and o['output-type'] == "ResultFile"\
and o['output-generation-type'] == "PerInput":
conc = o['uri'].udf.get(
'Concentration (ng/ul)') or o['uri'].udf.get(
'Concentration')
if conc is not None:
qi_conc = conc
if qi_conc is None:
missing.append(i['uri'].name)
else:
concentrations.append(qi_conc)
if missing:
print "Missing Quant-iT results for", ",".join(missing), "."
sys.exit(1)
else:
concentrations = [None] * len(inputs)
missing_udf = [
input.name for input, conc in zip(inputs, concentrations)
if conc is None
]
if missing_udf:
print "Error: input concentration not known for samples", ", ".join(
missing_udf)
sys.exit(1)
warning = []
headers = []
i_o_s = zip(inputs, outputs, concentrations)
for index, (input, output,
input_conc) in enumerate(sorted(i_o_s, key=sort_key)):
sample_name = input.name.encode('utf-8')
well = output.location[1].replace(":", "")
sample_no = re.match(r"([0-9]+)-", sample_name)
# NOTE: NSC output format is no longer used (in fact, it wasn't ever used)
# It is still included in some sample prep protocols, but could be removed
# soon.
# NSC general quantity-based normalization
try:
norm_mass = output.udf['Input (ng)']
except KeyError:
norm_mass = default_norm_dna
output.udf['Input (ng)'] = norm_mass
try:
vol = output.udf['Volume (uL)']
except KeyError:
vol = default_vol
output.udf['Volume (uL)'] = vol
if input_conc == 0.0:
sample_volume = vol + 1 # Will produce a warning below
else:
sample_volume = norm_mass * 1.0 / input_conc
buffer_volume = vol - sample_volume
if buffer_volume < 0:
buffer_volume = 0.0
sample_volume = vol
warning.append(output.name)
columns = [
("Sample_Number",
sample_no.group(1) if sample_no else sample_name),
("Labware", "Rack%d" % ((index // 32) + 1)),
("Position_ID", str((index % 32) + 1)),
("Volume_DNA", round(sample_volume, 1)),
("Volume_EB", round(buffer_volume, 1)),
("Destination_Well_ID", well),
]
if not headers:
row = sheet1.row(0)
headers = [x[0] for x in columns]
for i, header in enumerate(headers):
row.write(
i, header,
xlwt.easyxf('pattern: pattern solid, fore_color yellow;'))
row = sheet1.row(index + 1)
for i, val in enumerate([x[1] for x in columns]):
row.write(i, val)
lims.put_batch(outputs)
outputstream = StringIO.StringIO()
book.save(outputstream)
outfile = Artifact(lims, id=file_id)
filename = "Inputfil_Hamilton_Normalisering.xls"
gs = lims.glsstorage(outfile, filename)
file_obj = gs.post()
file_obj.upload(outputstream.getvalue())
if warning:
print "Warning: too low input concentration for samples:", ", ".join(
warning), "."
sys.exit(1)
""" Tablib - XLS Support.
"""
import sys
from tablib.compat import BytesIO, xrange
import tablib
import xlrd
import xlwt
from xlrd.biffh import XLRDError
title = 'xls'
extensions = ('xls',)
# special styles
wrap = xlwt.easyxf("alignment: wrap on")
bold = xlwt.easyxf("font: bold on")
def detect(stream):
"""Returns True if given stream is a readable excel file."""
try:
xlrd.open_workbook(file_contents=stream)
return True
except (TypeError, XLRDError):
pass
try:
xlrd.open_workbook(file_contents=stream.read())
return True
except (AttributeError, XLRDError):
pass
# -*- coding: utf-8 -*-
"""
Created on Thu Oct 11 17:37:21 2018
@author: Sakshi
"""
import xlwt
import os
from datetime import datetime
currentpath = os.getcwd()
print (currentpath)
path1 = "C:/Users/Sakshi/Desktop/python"
style1 = xlwt.easyxf('font : name Time New Roman, color-index red, bold on','#,##0.00')
style2 = xlwt.easyxf('font : name Rockwell, color-index blue,bold on','DD-MM-YY')
style3 = xlwt.easyxf('font : name Arial, color-index blue,bold on')
wb = xlwt.Workbook()
ws = wb.add_sheet('first sheet#f5')
ws.write(0,0,1234.567,style1)
ws.write(1,0,datetime.now(),style2)
ws.write(2,0,'first excel via python',style2)
ws.write(3,0,1)
ws.write(3,1,11)
ws.write(3,2,111)
ws.write(3,3,xlwt.Formula("A4+B4+C4"))
os.chdir(path1)
wb.save('alv.xls')
os.chdir(currentpath)
def __init__(self,
data,
output_name='excel_data',
headers=None,
force_csv=False,
encoding='utf8'):
# Make sure we've got the right type of data to work with
valid_data = False
if isinstance(data, ValuesQuerySet):
data = list(data)
elif isinstance(data, QuerySet):
data = list(data.values())
if hasattr(data, '__getitem__'):
if isinstance(data[0], dict):
if headers is None:
headers = data[0].keys()
data = [[row[col] for col in headers] for row in data]
data.insert(0, headers)
if hasattr(data[0], '__getitem__'):
valid_data = True
assert valid_data is True, "ExcelResponse requires a sequence of sequences"
import StringIO
output = StringIO.StringIO()
# Excel has a limit on number of rows; if we have more than that, make a csv
use_xls = False
if len(data) <= 65536 and force_csv is not True:
try:
import xlwt
except ImportError:
# xlwt doesn't exist; fall back to csv
pass
else:
use_xls = True
if use_xls:
book = xlwt.Workbook(encoding=encoding)
sheet = book.add_sheet('Sheet 1')
styles = {
'datetime': xlwt.easyxf(num_format_str='yyyy-mm-dd hh:mm:ss'),
'date': xlwt.easyxf(num_format_str='yyyy-mm-dd'),
'time': xlwt.easyxf(num_format_str='hh:mm:ss'),
'default': xlwt.Style.default_style
}
for rowx, row in enumerate(data):
for colx, value in enumerate(row):
if isinstance(value, datetime.datetime):
cell_style = styles['datetime']
elif isinstance(value, datetime.date):
cell_style = styles['date']
elif isinstance(value, datetime.time):
cell_style = styles['time']
else:
cell_style = styles['default']
sheet.write(rowx, colx, value, style=cell_style)
book.save(output)
mimetype = 'application/vnd.ms-excel'
file_ext = 'xls'
else:
for row in data:
out_row = []
for value in row:
if not isinstance(value, basestring):
value = unicode(value)
value = value.encode(encoding)
out_row.append(value.replace('"', '""'))
output.write('"%s"\n' % '","'.join(out_row))
mimetype = 'text/csv'
file_ext = 'csv'
output.seek(0)
super(ExcelResponse, self).__init__(content=output.getvalue(),
mimetype=mimetype)
self['Content-Disposition'] = 'attachment;filename="%s.%s"' % \
(output_name.replace('"', '\"'), file_ext)
def generate_cdnur_report(self, wb1):
self.ensure_one()
ws1 = wb1.add_sheet('cdnur')
# Content/Text style
header_content_style = xlwt.easyxf(
"font: name Arial size 20 px, bold 1, height 170;")
sub_header_style = xlwt.easyxf(
"font: name Arial size 10 px, bold 1, height 170; align: horiz center"
)
sub_header_content_style = xlwt.easyxf(
"font: name Arial size 10 px, height 170;")
line_content_style = xlwt.easyxf("font: name Arial, height 170;")
row = 1
col = -1
ws1.row(row).height = 500
ws1.write_merge(row, row, 2, 6, "Summary For CDNUR(6C)",
header_content_style)
row += 2
ws1.write(row, col + 1, "From:", sub_header_style)
ws1.write(row, col + 2, self.format_date(self.date_from),
sub_header_content_style)
row += 1
ws1.write(row, col + 1, "To:", sub_header_style)
ws1.write(row, col + 2, self.format_date(self.date_to),
sub_header_content_style)
row += 1
ws1.write(row, col + 1, "Note/Voucher Number", sub_header_style)
ws1.write(row, col + 2, "Note/Voucher date", sub_header_style)
ws1.write(row, col + 3, "Invoice/Advance Payment Voucher number",
sub_header_style)
ws1.write(row, col + 4, "Invoice/Advance Payment Voucher date",
sub_header_style)
ws1.write(row, col + 5, "Pre GST", sub_header_style)
ws1.write(row, col + 6, "Document Typ", sub_header_style)
ws1.write(row, col + 7, "Reason For Issuing document",
sub_header_style)
ws1.write(row, col + 8, "Supply Type", sub_header_style)
ws1.write(row, col + 9, "Invoice Type", sub_header_style)
ws1.write(row, col + 10, "Note/Voucher Value", sub_header_style)
ws1.write(row, col + 11, "Rate", sub_header_style)
ws1.write(row, col + 12, "Taxable Value", sub_header_style)
ws1.write(row, col + 13, "Integrated Tax Paid", sub_header_style)
ws1.write(row, col + 14, "Central Tax Paid", sub_header_style)
ws1.write(row, col + 15, "State/UT Tax Paid", sub_header_style)
ws1.write(row, col + 16, "Cess Paid", sub_header_style)
ws1.write(row, col + 17, "Eligibility For ITC", sub_header_style)
ws1.write(row, col + 18, "Availed ITC Integrated Tax",
sub_header_style)
ws1.write(row, col + 19, "Availed ITC Central Tax", sub_header_style)
ws1.write(row, col + 20, "Availed ITC State/UT Tax", sub_header_style)
ws1.write(row, col + 21, "Availed ITC Cess", sub_header_style)
row += 1
invoice_gst_tax_lines = {}
igst = 0
cgst = 0
sgst = 0
for invoice in self.refund_invoices.filtered(lambda p: not p.partner_id.x_gstin and p.company_id.state_id != p.partner_id.state_id \
and (p.amount_untaxed_signed * -1) > B2CL_INVOICE_AMT_LIMIT and p.type in ('in_refund', 'out_refund')):
#Can't use invoice.tax_line_ids directly because it will contain on individual/leaf taxes (like [email protected]%, [email protected]%)
#while gstr2 report needs the 'group' tax (like GST@5%).
#Iterate through invoice.invoice_line_ids.invoice_line_tax_line_ids and collect/compute from there
#for tax_line in invoice.tax_line_ids:
_logger.info(invoice)
grouped_tax_lines = {}
for invoice_line in invoice.invoice_line_ids:
if invoice_line.discount_amount and invoice_line.quantity != 0:
price = invoice_line.price_unit - (
invoice_line.discount_amount / invoice_line.quantity)
elif invoice_line.discount:
price = invoice_line.price_unit * (
1 - (invoice_line.discount or 0.0) / 100.0)
else:
price = invoice_line.price_unit
if invoice_line.invoice_id.inclusive:
line_taxes = invoice_line.invoice_line_tax_ids.with_context(
price_include=True,
include_base_amount=True).compute_all_inc(
price,
invoice.currency_id,
invoice_line.quantity,
product=invoice_line.product_id,
partner=invoice.partner_id)
else:
line_taxes = invoice_line.invoice_line_tax_ids.compute_all(
price, invoice.currency_id, invoice_line.quantity,
invoice_line.product_id, invoice.partner_id)
# line_taxes = invoice_line.invoice_line_tax_ids.compute_all(invoice_line.price_unit, invoice.currency_id, invoice_line.quantity, invoice_line.product_id, invoice.partner_id)
#_logger.info(line_taxes)
for ln_tx in invoice_line.invoice_line_tax_ids: #.sorted(reverse=True):
gst_tax_id = None
# if ln_tx.gst_type in ('gst','ugst','igst'):
gst_tax_id = ln_tx
if grouped_tax_lines.get(ln_tx):
grouped_tax_lines[ln_tx][0] += line_taxes[
'total_excluded']
else:
grouped_tax_lines[ln_tx] = [
0, 0, 0, 0, 0
] #[Taxable value, Cess amount, IGST, CGST, SGST]
grouped_tax_lines[ln_tx][0] = line_taxes[
'total_excluded']
#Collect the IGST/CGST/SGST breakup for this tax rate
for leaf_tax in line_taxes['taxes']:
if 'IGST' in leaf_tax['name']:
grouped_tax_lines[ln_tx][2] += leaf_tax['amount']
elif 'CGST' in leaf_tax['name']:
grouped_tax_lines[ln_tx][3] += leaf_tax['amount']
elif 'SGST' in leaf_tax['name'] or 'UTGST' in leaf_tax[
'name']:
grouped_tax_lines[ln_tx][4] += leaf_tax['amount']
else: #CESS and other non-GST taxes
#TODO:Make the bold assumption that CESS is applied *after* GST taxes, so grouped_tax_lines[gst_tx_id] is already present
#Calculate CESS amount only
grouped_tax_lines[gst_tax_id][1] += sum(
l['amount'] for l in line_taxes['taxes']
if 'GST' not in l['name'])
invoice_gst_tax_lines[invoice] = grouped_tax_lines
#_logger.info(grouped_tax_lines)
for invoice, inv_tax_lines in sorted(
invoice_gst_tax_lines.items(),
key=lambda p:
(p[0].date, p[0].number)): # invoice_gst_tax_lines.items():
for tax_id, base_amount in inv_tax_lines.items():
#tax_id = self.env['account.tax'].browse(tax_id_id)
for rateObj in tax_id:
if rateObj.amount_type == "group":
for childObj in rateObj.children_tax_ids:
tax_rate = childObj.amount * 2
break
else:
tax_rate = rateObj.amount
break
# tax_rate = float( str(tax_id.name).split('@')[1].split('%')[0] )
if float_is_zero(
tax_rate,
precision_digits=3): #Skip zero rated/exempted rates
continue
ws1.write(row, col + 1, invoice.number, line_content_style)
ws1.write(row, col + 2, self.format_date(invoice.date_invoice),
line_content_style)
ws1.write(row, col + 3, invoice.refund_invoice_id.number,
line_content_style)
ws1.write(
row, col + 4,
self.format_date(invoice.refund_invoice_id.date_invoice),
line_content_style)
ws1.write(row, col + 5, "N", line_content_style) #Pre-GST
ws1.write(row, col + 6, invoice.type == 'in_refund' and "D"
or "C", line_content_style)
ws1.write(row, col + 7, invoice.name, line_content_style)
ws1.write(row, col + 8, "TODO",
line_content_style) #Supply-Type
ws1.write(row, col + 9, "Regular", line_content_style)
ws1.write(row, col + 10, invoice.amount_total,
line_content_style)
ws1.write(row, col + 11, tax_rate, line_content_style)
ws1.write(row, col + 12, base_amount[0], line_content_style)
ws1.write(row, col + 13, base_amount[2], line_content_style)
ws1.write(row, col + 14, base_amount[3], line_content_style)
ws1.write(row, col + 15, base_amount[4], line_content_style)
ws1.write(row, col + 16, base_amount[1], line_content_style)
ws1.write(row, col + 17, "", line_content_style)
ws1.write(row, col + 18, "", line_content_style)
ws1.write(row, col + 19, "", line_content_style)
ws1.write(row, col + 20, "", line_content_style)
ws1.write(row, col + 21, "", line_content_style)
row += 1
import xlrd
import xlwt
from datetime import datetime
book = xlwt.Workbook()
ws = book.add_sheet('a test sheet')
style0 = xlwt.easyxf(num_format_str="D-MMM_YY")
style1 = xlwt.easyxf('font:name Times New Roman,color-index red,bold on')
ws.write(0, 0, datetime.now(), style0)
ws.write(1, 0, 10, style1)
ws.write(2, 0, 10, style1)
ws.write(3, 0, xlwt.Formula("A2+A3"))
book.save('abs.xls')
def generate_xls_report(self, _p, _xs, data, objects, wb):
print "data on generate xls report>>>>>>>>>>>>>>>>>>>>>>>>>>>", data
from pprint import pprint
pprint(data)
ws = wb.add_sheet(_p.report_name[:31])
ws.panes_frozen = True
ws.remove_splits = True
ws.portrait = 0 # Landscape
ws.fit_width_to_pages = 1
row_pos = 0
# set print header/footer
ws.header_str = self.xls_headers['standard']
ws.footer_str = self.xls_footers['standard']
# cf. account_report_general_ledger.mako
initial_balance_text = {
'initial_balance': _('Computed'),
'opening_balance': _('Opening Entries'),
False: _('No')
}
# Title
cell_style = xlwt.easyxf(_xs['xls_title'])
report_name = ' - '.join([
_p.report_name.upper(), _p.company.partner_id.name,
_p.company.currency_id.name
])
c_specs = [
('report_name', 1, 0, 'text', report_name),
]
row_data = self.xls_row_template(c_specs, [x[0] for x in c_specs])
row_pos = self.xls_write_row(ws,
row_pos,
row_data,
row_style=cell_style)
# write empty row to define column sizes
c_sizes = self.column_sizes
c_specs = [('empty%s' % i, 1, c_sizes[i], 'text', None)
for i in range(0, len(c_sizes))]
row_data = self.xls_row_template(c_specs, [x[0] for x in c_specs])
row_pos = self.xls_write_row(ws,
row_pos,
row_data,
set_column_size=True)
# Header Table
cell_format = _xs['bold'] + _xs['fill_blue'] + _xs['borders_all']
cell_style = xlwt.easyxf(cell_format)
cell_style_center = xlwt.easyxf(cell_format + _xs['center'])
c_specs = [
('coa', 2, 0, 'text', _('Chart of Account')),
('fy', 1, 0, 'text', _('Fiscal Year')),
('df', 2, 0, 'text',
_p.filter_form(data) == 'filter_date' and _('Dates Filter')
or _('Periods Filter')),
('cd', 1, 0, 'text', _('Clearance Date')),
('af', 2, 0, 'text', _('Accounts Filter')),
('tm', 2, 0, 'text', _('Target Moves')),
]
row_data = self.xls_row_template(c_specs, [x[0] for x in c_specs])
row_pos = self.xls_write_row(ws,
row_pos,
row_data,
row_style=cell_style_center)
cell_format = _xs['borders_all']
cell_style = xlwt.easyxf(cell_format)
cell_style_center = xlwt.easyxf(cell_format + _xs['center'])
c_specs = [
('coa', 2, 0, 'text', _p.chart_account.name),
('fy', 1, 0, 'text', _p.fiscalyear.name if _p.fiscalyear else '-'),
]
df = _('From') + ': '
if _p.filter_form(data) == 'filter_date':
df += _p.start_date if _p.start_date else u''
else:
df += _p.start_period.name if _p.start_period else u''
df += ' ' + _('To') + ': '
if _p.filter_form(data) == 'filter_date':
df += _p.stop_date if _p.stop_date else u''
else:
df += _p.stop_period.name if _p.stop_period else u''
c_specs += [
('df', 2, 0, 'text', df),
('cd', 1, 0, 'text', _p.date_until),
('af', 2, 0, 'text', _p.display_partner_account(data)),
('tm', 2, 0, 'text', _p.display_target_move(data)),
]
row_data = self.xls_row_template(c_specs, [x[0] for x in c_specs])
row_pos = self.xls_write_row(ws,
row_pos,
row_data,
row_style=cell_style_center)
ws.set_horz_split_pos(row_pos)
row_pos += 1
# Column Title Row
cell_format = _xs['bold']
c_title_cell_style = xlwt.easyxf(cell_format)
# Column Header Row
cell_format = _xs['bold'] + _xs['fill'] + _xs['borders_all']
c_hdr_cell_style = xlwt.easyxf(cell_format)
c_hdr_cell_style_right = xlwt.easyxf(cell_format + _xs['right'])
c_hdr_cell_style_center = xlwt.easyxf(cell_format + _xs['center'])
c_hdr_cell_style_decimal = xlwt.easyxf(
cell_format + _xs['right'],
num_format_str=report_xls.decimal_format)
cell_format = _xs['italic'] + _xs['borders_all']
c_init_cell_style = xlwt.easyxf(cell_format)
c_init_cell_style_decimal = xlwt.easyxf(
cell_format + _xs['right'],
num_format_str=report_xls.decimal_format)
c_init_cell_style_decimal_bold = xlwt.easyxf(
cell_format + _xs['right'] + _xs['bold'],
num_format_str=report_xls.decimal_format)
c_specs = [
('partner', 2, 0, 'text', _('Partner'), None, c_hdr_cell_style),
('code', 1, 0, 'text', _('Code'), None, c_hdr_cell_style),
('balance', 1, 0, 'text', _('Balance'), None, c_hdr_cell_style)
]
cnt = 0
for range_title in _p.ranges_titles:
cnt += 1
c_specs.append(('classification' + str(cnt), 1, 0, 'text',
_(range_title), None, c_hdr_cell_style))
c_hdr_data = self.xls_row_template(c_specs, [x[0] for x in c_specs])
# cell styles for aged lines
ll_cell_format = _xs['borders_all']
ll_cell_style = xlwt.easyxf(ll_cell_format)
ll_cell_style_center = xlwt.easyxf(ll_cell_format + _xs['center'])
ll_cell_style_date = xlwt.easyxf(ll_cell_format + _xs['left'],
num_format_str=report_xls.date_format)
ll_cell_style_decimal = xlwt.easyxf(
ll_cell_format + _xs['right'],
num_format_str=report_xls.decimal_format)
cnt = 0
for acc in objects:
if _p.agged_lines_accounts[acc.id]:
cnt += 1
cumul_debit = 0.0
cumul_credit = 0.0
cumul_balance = 0.0
cumul_balance_curr = 0.0
c_specs = [
('acc_title', 11, 0, 'text',
' - '.join([acc.code, acc.name])),
]
row_data = self.xls_row_template(c_specs,
[x[0] for x in c_specs])
row_pos = self.xls_write_row(ws, row_pos, row_data,
c_title_cell_style)
row_pos += 1
row_pos = self.xls_write_row(ws, row_pos, c_hdr_data)
row_start = row_pos
for partner_name, p_id, p_ref, p_name in _p.partners_order[
acc.id]:
if _p.agged_lines_accounts[acc.id].get(p_id):
line = _p.agged_lines_accounts[acc.id][p_id]
c_specs = [
('partner', 2, 0, 'text', _(partner_name or '')),
('code', 1, 0, 'text', _(p_ref or '')),
('balance', 1, 0, 'number', line.get('balance'),
None, c_init_cell_style_decimal)
]
count = 0
for classif in _p.ranges:
count += 1
c_specs.append(
('classification' + str(count), 1, 0, 'number',
line['aged_lines'][classif]
or 0.0, None, c_init_cell_style_decimal))
row_data = self.xls_row_template(
c_specs, [x[0] for x in c_specs])
row_pos = self.xls_write_row(ws, row_pos, row_data)
percents = _p.agged_percents_accounts[acc.id]
totals = _p.agged_totals_accounts[acc.id]
c_specs = [('total', 3, 0, 'text', _('Total'), None,
c_hdr_cell_style),
('balance', 1, 0, 'number', totals['balance'], None,
c_init_cell_style_decimal_bold)]
count = 0
for classif in _p.ranges:
count += 1
c_specs.append(
('classification' + str(count), 1, 0, 'number',
totals[classif]
or 0.0, None, c_init_cell_style_decimal_bold))
row_data = self.xls_row_template(c_specs,
[x[0] for x in c_specs])
row_pos = self.xls_write_row(ws, row_pos, row_data)
c_specs = [('percents', 4, 0, 'text', _('Percents'), None,
c_hdr_cell_style)]
count = 0
for classif in _p.ranges:
count += 1
c_specs.append(
('classification' + str(count), 1, 0, 'number',
percents[classif]
or 0.0, None, c_init_cell_style_decimal_bold))
row_data = self.xls_row_template(c_specs,
[x[0] for x in c_specs])
row_pos = self.xls_write_row(ws, row_pos, row_data)
row_pos += 1
def generate_xls_report(self, _p, _xs, data, objects, wb):
# Initialisations
ws = wb.add_sheet(_p.report_name[:31])
ws.panes_frozen = True
ws.remove_splits = True
ws.portrait = 0 # Landscape
ws.fit_width_to_pages = 1
row_pos = 0
ws.header_str = self.xls_headers['standard']
ws.footer_str = self.xls_footers['standard']
# Print Title
row_pos = self.print_title(ws, _p, row_pos, xlwt, _xs)
# Print empty row to define column sizes
row_pos = self.print_empty_row(ws, row_pos)
# Print Header Table titles (Fiscal Year - Accounts Filter - Periods
# Filter...)
row_pos = self.print_header_titles(ws, _p, data, row_pos, xlwt, _xs)
initial_balance_text = {
'initial_balance': _('Computed'),
'opening_balance': _('Opening Entries'),
False: _('No')
} # cf. account_report_partner_balance.mako
# Print Header Table data
row_pos = self.print_header_data(ws, _p, data, row_pos, xlwt, _xs,
initial_balance_text)
# Print comparison header table
if _p.comparison_mode in ('single', 'multiple'):
row_pos += 1
row_pos = self.print_comparison_header(_xs, xlwt, row_pos, _p, ws,
initial_balance_text)
# Freeze the line
ws.set_horz_split_pos(row_pos)
# cell styles for account data
regular_cell_format = _xs['borders_all']
regular_cell_style = xlwt.easyxf(regular_cell_format)
regular_cell_style_decimal = xlwt.easyxf(
regular_cell_format + _xs['right'],
num_format_str=report_xls.decimal_format)
row_pos += 1
for current_account in objects:
partners_order = _p['partners_order_accounts']\
.get(current_account.id, False)
# do not display accounts without partners
if not partners_order:
continue
comparisons = _p['comparisons_accounts']\
.get(current_account.id, False)
# in multiple columns mode, we do not want to print accounts
# without any rows
if _p.comparison_mode in ('single', 'multiple'):
all_comparison_lines = [
comp['partners_amounts'][partner_id[1]]
for partner_id in partners_order for comp in comparisons
]
if not display_line(all_comparison_lines):
continue
current_partner_amounts = _p['partners_amounts_accounts']\
.get(current_account.id, False)
if _p.comparison_mode in ('single', 'multiple'):
comparison_total = {}
for i, comp in enumerate(comparisons):
comparison_total[i] = {'balance': 0.0}
# print row: Code - Account name
row_pos = self.print_row_code_account(ws, current_account, row_pos,
_xs, xlwt)
row_account_start = row_pos
# Print row: Titles "Account/Partner Name-Code/ref-Initial
# Balance-Debit-Credit-Balance" or "Account/Partner
# Name-Code/ref-Balance Year-Balance Year2-Balance C2-Balance C3"
row_pos = self.print_account_header(ws, _p, _xs, xlwt, row_pos)
for (partner_code_name, partner_id, partner_ref, partner_name) \
in partners_order:
partner = current_partner_amounts.get(partner_id, {})
# in single mode, we have to display all the partners even if
# their balance is 0.0 because the initial balance should match
# with the previous year closings
# in multiple columns mode, we do not want to print partners
# which have a balance at 0.0 in each comparison column
if _p.comparison_mode in ('single', 'multiple'):
all_comparison_lines = [
comp['partners_amounts'][partner_id]
for comp in comparisons
if comp['partners_amounts'].get(partner_id)
]
if not display_line(all_comparison_lines):
continue
# display data row
if len(_p.comp_params) == 2:
account_span = 3
else:
account_span = _p.initial_balance_mode and 2 or 3
c_specs = [('acc_title', account_span, 0, 'text',
partner_name if partner_name else _('Unallocated'))
]
c_specs += [('partner_ref', 1, 0, 'text',
partner_ref if partner_ref else '')]
if _p.comparison_mode == 'no_comparison':
bal_formula = ''
if _p.initial_balance_mode:
init_bal_cell = rowcol_to_cell(row_pos, 3)
bal_formula = init_bal_cell + '+'
debit_col = 4
c_specs += [
('init_bal', 1, 0, 'number',
partner.get('init_balance', 0.0), None,
regular_cell_style_decimal),
]
else:
debit_col = 3
c_specs += [
('debit', 1, 0, 'number', partner.get('debit', 0.0),
None, regular_cell_style_decimal),
('credit', 1, 0, 'number', partner.get('credit', 0.0),
None, regular_cell_style_decimal),
]
debit_cell = rowcol_to_cell(row_pos, debit_col)
credit_cell = rowcol_to_cell(row_pos, debit_col + 1)
bal_formula += debit_cell + '-' + credit_cell
c_specs += [
('bal', 1, 0, 'number', None, bal_formula,
regular_cell_style_decimal),
]
else:
c_specs += [
('bal', 1, 0, 'number', partner.get('balance', 0.0),
None, regular_cell_style_decimal),
]
if _p.comparison_mode in ('single', 'multiple'):
for i, comp in enumerate(comparisons):
comp_partners = comp['partners_amounts']
balance = diff = percent_diff = 0
if comp_partners.get(partner_id):
balance = comp_partners[partner_id]['balance']
diff = comp_partners[partner_id]['diff']
percent_diff = comp_partners[partner_id][
'percent_diff']
comparison_total[i]['balance'] += balance
c_specs += [
('balance_%s' % i, 1, 0, 'number', balance, None,
regular_cell_style_decimal),
]
# no diff in multiple comparisons because it shows too much
# data
if _p.comparison_mode == 'single':
c_specs += [
('balance_diff', 1, 0, 'number', diff, None,
regular_cell_style_decimal),
]
if percent_diff is False:
c_specs += [
('balance', 1, 0, 'number', diff, None,
regular_cell_style_decimal),
]
else:
c_specs += [
('perc_diff', 1, 0, 'number',
int(round(percent_diff))),
]
row_data = self.xls_row_template(c_specs,
[x[0] for x in c_specs])
row_pos = self.xls_write_row(ws, row_pos, row_data,
regular_cell_style)
row_pos = self.print_account_totals(_xs, xlwt, ws,
row_account_start, row_pos,
current_account, _p)
class ExcelTable(object):
"""
xlwt wrapper for managing simple excel tables.
Enforces each row of every sheet to have a value for each column
"""
# keep formatting objects in a dictionary; otherwise, each instance is
# treated as a separate entity by excel, which eventually (limit 4k)
# gives up trying to interpret them.
easyxfs = dict()
easyxfs['bold'] = xlwt.easyxf('font: bold 1')
easyxfs['bold+rotate'] = xlwt.easyxf('font: bold 1; align: rotation 90')
easyxfs['hyperlink'] = xlwt.easyxf('font: colour blue, underline on')
easyxfs[None] = xlwt.easyxf('font: colour black')
def __init__(self, sheetname=None, colnames=None, header_format='bold'):
"""
Initialize a workbook
"""
self.book = xlwt.Workbook()
self.name_to_sheet = dict()
self.sheet_to_colnames = dict()
self.sheet_to_rownum = defaultdict(int)
if sheetname is not None:
self.add_sheet(sheetname, colnames, header_format)
def add_sheet(self, sheetname, colnames, header_format='bold'):
"""
Add a worksheet to the workbook with given column names
"""
if sheetname not in self.name_to_sheet:
sheet = self.book.add_sheet(sheetname)
self.name_to_sheet[sheetname] = sheet
self.sheet_to_colnames[sheet] = colnames
self.add_row(colnames, header_format, sheetname)
else:
print 'Sheet "%s" already exists' % sheetname
def add_row(self, values, cell_formats=None, sheetname=None):
"""
Add a row to the specified worksheet. "values" iterator
must contain a value for each column specified when the sheet
was created (empty strings are fine)
"""
if sheetname is None:
assert len(self.name_to_sheet) == 1, 'Please specify a sheet name'
sheet = self.name_to_sheet.values()[0]
else:
assert sheetname in self.name_to_sheet, \
'Sheet "%s" has not yet been created.' % sheetname
sheet = self.name_to_sheet[sheetname]
assert len(values) == len(self.sheet_to_colnames[sheet]), \
'Please provide a value for each column: %s' % \
self.sheet_to_colnames[sheet]
if not isinstance(cell_formats, list):
cell_formats = [cell_formats] * len(values)
for idx, cell_format in enumerate(cell_formats):
if cell_format in self.easyxfs:
cell_formats[idx] = self.easyxfs[cell_format]
elif not isinstance(cell_format, xlwt.Style.XFStyle):
raise RuntimeError('Unexpected format "%s". '
'Please select from the following: %s' %
sorted(str(self.easyxfs.keys())))
row = self.sheet_to_rownum[sheet]
if row >= 65530:
warnings.warn('Maximum number of rows reached', RuntimeWarning)
return
self.sheet_to_rownum[sheet] += 1
for (col, (val, cell_format)) in enumerate(zip(values, cell_formats)):
sheet.write(row, col, _strip_non_ascii(val), cell_format)
def write_to_file(self, filename):
"""
Export table to disk
"""
self.book.save(filename)
def CreateExcel(cevObjeclist,portobject,filename):
bb = cevObjeclist
cc = portobject
style0 = xlwt.easyxf('font: name Times New Roman, color-index red, bold on',num_format_str='#,##0.00')
style1 = xlwt.easyxf(num_format_str='D-MMM-YY')
wb = xlwt.Workbook()
ws = wb.add_sheet(u'漏洞情况')
wp = wb.add_sheet(u'端口情况情况')
wp.write(0,0,u"序号")
wp.write(0,1,u"主机ip")
wp.write(0,2,u"开放端口")
wp.write(0,3,u"端口协议")
wp.write(0,4,u"端口服务")
wp.write(0,5,u"探测输出")
for x in xrange(0,len(cc)):
wp.write(x+1,0,x+1)
#host
for x in xrange(0,len(cc)):
wp.write(x+1,1,cc[x].Host)
#port
for x in xrange(0,len(cc)):
wp.write(x+1,2,cc[x].Port)
#Protocol
for x in xrange(0,len(cc)):
wp.write(x+1,3,cc[x].Protocol)
#Service
for x in xrange(0,len(cc)):
wp.write(x+1,4,cc[x].Service)
#Output
for x in xrange(0,len(cc)):
wp.write(x+1,5,cc[x].Output)
ws.write(0,0,u"序号")
ws.write(0,1,u"CVE编号")
ws.write(0,2,u"主机IP")
ws.write(0,3,u"风险等级")
ws.write(0,4,u"协议")
ws.write(0,5,u"端口")
ws.write(0,6,u"漏洞名称")
ws.write(0,7,u"漏洞描述")
ws.write(0,8,u"整改建议")
ws.write(0,9,u"探测输出")
#顺序
for x in xrange(0,len(bb)):
ws.write(x+1,0,x+1)
#cve
for x in xrange(0,len(bb)):
ws.write(x+1,1,bb[x].Cve)
#Host
for x in xrange(0,len(bb)):
ws.write(x+1,2,bb[x].Host)
#Risk
for x in xrange(0,len(bb)):
ws.write(x+1,3,bb[x].Risk)
#Protocol
for x in xrange(0,len(bb)):
ws.write(x+1,4,bb[x].Protocol)
#Port
for x in xrange(0,len(bb)):
ws.write(x+1,5,bb[x].Port)
#Name
for x in xrange(0,len(bb)):
ws.write(x+1,6,bb[x].Name)
#Description
for x in xrange(0,len(bb)):
ws.write(x+1,7,bb[x].Description)
#Solution
for x in xrange(0,len(bb)):
ws.write(x+1,8,bb[x].Solution)
#Output
for x in xrange(0,len(bb)):
ws.write(x+1,9,bb[x].Output)
wb.save(filename)
#
path = 'E:\\python_my_practice\\filehandling\\addfile.xls'
# #
# import xlwt
# from xlwt import Workbook
#
# # Workbook is created
# wb = xlwt.Workbook(path)
#
#
# # add_sheet is used to create sheet.
# sheet = wb.active_sheet
#
# cell=wb.set_use_cell_values(1, 0, 'ISBT DEHRADUN')
# sheet.write(2, 0, 'SHASTRADHARA')
# wb.save('xlwt example.xls')
from xlwt import Workbook
exel_file = Workbook()
sheet = exel_file.add_sheet('empinfo')
style = xlwt.easyxf('font : bold 1, color red;')
row = 0
sheet.write(row, 0, 'empid', style)
sheet.write(row, 1, 'empnm', style)
sheet.write(row, 2, 'empage', style)
sheet.write(row, 3, 'empaddr', style)
sheet.write(row, 4, 'empsal', style)
exel_file.save(path)
print('write into excel file')
import os
from xlwt import easyxf
import xlwt
tittle_style_color = xlwt.easyxf(
'font: height 240, bold on; align: wrap on, vert centre, horiz center; pattern: pattern solid, fore_colour 0x1A'
)
sub_tittle_style_color = xlwt.easyxf(
'font: height 240, bold on; align: wrap on, vert centre, horiz center; pattern: pattern solid, fore_colour 0x1A'
)
tittle_style = xlwt.easyxf(
'font: height 240, bold on; align: wrap on, vert centre, horiz center;')
main_tittle_style = xlwt.easyxf(
'font: height 240, name Arial, colour_index black, bold on; align: wrap on, vert centre, horiz left;'
'pattern: pattern solid, fore_colour ice_blue')
sub_main_tittle_style = xlwt.easyxf(
'font: height 240, name Arial, colour_index black, bold on; align: wrap on, vert centre, horiz left;'
'pattern: pattern solid, fore_colour tan')
tittle_style_left = xlwt.easyxf(
'font: height 240, bold on; align: wrap on, vert centre, horiz left;')
subTitle_style = xlwt.easyxf(
'font: height 200, bold on, italic on; align: wrap on, vert centre, horiz centre;'
)
subTitle_style_color = xlwt.easyxf(
'font: height 200, bold on, italic on; align: wrap on, vert centre, horiz centre;'
'pattern: pattern solid, fore_colour 0x1B')
subTitle_style_color_left = xlwt.easyxf(
args = parser.parse_args()
if args.inputfile is None:
parser.print_help()
exit()
root = ET.parse(args.inputfile)
map_version = root.getroot()
first_node = map_version.find('node')
wb = xlwt.Workbook()
ws = wb.add_sheet('freemind2excel',cell_overwrite_ok=True)
#设置行高
ws.width=256*20
tall_style = xlwt.easyxf('font:height 480;')
ws_row = ws.row(0)
ws_row.set_style(tall_style)
# 设置单元格宽度
# ws.col(0).width = 3333
# for i in range(8):
# a=[0,1,2,3,4,5,6,7]
b=[0, 1, 4, 5]
for i in b:
ws.col(i).width = 6666
ws.col(2).width = 3333
# 设置边框
perf_func2(first_node, count_row)
for row in range(1, g_row2+1):
XL_CELL_ERROR,
XL_CELL_BLANK, # for use in debugging, gathering stats, etc
) = range(7)
# 看下第一行第一列的单元格是个字符串
sh1 = rdata.sheet_by_index(0)
cell_0_0 = sh1.cell(0, 0)
print(cell_0_0, cell_0_0.ctype, cell_0_0.value)
# 第二行第一列的单元格
cell_1_0 = sh1.cell(1, 0)
# 写数据进表格
# 创建一个wbook对象，生成一个新的sheet
wbook = xlwt.Workbook()
wsheet = wbook.add_sheet(sh1.name)
# 在写入第一行，标题栏
# wsheet这个函数(row,col,value,style),这个style其实就是这个内容单元格的格式
style = xlwt.easyxf('align: vertical center, horizontal center')
wsheet.write(0, 0, u'时间', style)
wsheet.write(0, 1, u'人数1', style)
wsheet.write(0, 2, u'人数2', style)
wsheet.write(0, 3, u'总分', style)
# 写成文件new_data.xls
try:
wbook.save('new_data.xls')
except Exception as e:
print(e)
else:
print('write excel file successful')
from bs4 import BeautifulSoup as soup
from random import randint
import requests
import re
import random
import time
import xlwt
# generage the xls file and define the title
workbook = xlwt.Workbook()
worksheet = workbook.add_sheet('Sheet Name')
style = xlwt.easyxf('font: bold 1')
titles = [
'P url', 'EAN', 'ISBN', 'eBay item number', 'Title', 'Brand', 'Condition',
'Condition', 'FORMAT', 'Author', 'PUBLISHER', 'CATEGORY',
'About this product', 'DESCRIPTION', 'Item specifics', 'Product Summary',
'Shipping Weight', 'Product Dime', 'image1', 'imaage2', 'imaage3',
'imaage4', 'imaage5', 'imaage6', 'imaage7', 'Price'
]
row = 0
col = 0
for title in titles:
worksheet.write(row, col, title, style)
col += 1
proxies = [
"korleone:[email protected]:12345",
"korleone:[email protected]:12345",
