def write_one_docx(row,write_dict):
write_list=['name','birth','sex','city','birth_city','hobby','tel','phone','email']
template_file='/var/ftpadmin/forwenjie/template/template.xlsx'
rb=xlrd.open_workbook(template_file)
wb=xlcopy(rb)
sheet=wb.get_sheet(0)
sheet.write(row,0,write_dict[0])
sheet.write(row,2,write_dict[2])
sheet.write(row,1,write_dict[1])
sheet.write(row,7,write_dict[7])
sheet.write(row,8,write_dict[8])
wb.save('/var/ftpadmin/forwenjie/file_output/output.xls')
def get_menu():
order_menu_date = get_order_menu_date()
formatted_date = menu_date_format(order_menu_date)
menu_xls = UserDocument.objects(date=formatted_date).first()
xls_contents = menu_xls.contents.read()
weekday = order_menu_date.weekday()
tmp_filename = '/tmp/tmp.xls'
tmp_xls = open(tmp_filename, 'w')
tmp_xls.write(xls_contents)
tmp_xls.close()
read_book = xlrd.open_workbook(tmp_filename, on_demand=True, formatting_info=True)
write_book = xlcopy(read_book)
write_sheet = write_book.get_sheet(weekday)
menu = Menu.objects(date=formatted_date).first()
orders = Order.objects(menu=menu)
ppixs = ProductPositionsInXLS.objects.filter(product__in=orders.values_list('product'), menu=menu)
style = xlwt.XFStyle()
font = xlwt.Font()
font.bold = True
font.height = 12*20
font.name = 'Calibri'
style.font = font
borders = xlwt.Borders()
borders.bottom = xlwt.Borders.THIN
borders.right = xlwt.Borders.THIN
borders.top = xlwt.Borders.THIN
borders.left = xlwt.Borders.THIN
style.borders = borders
total_cell_style = xlwt.XFStyle()
total_cell_style.font = font
total = 0
total_row_pos = ProductPositionsInXLS.objects.filter(menu=menu).order_by('-row_in_xls').first().row_in_xls + 1
for ppix in ppixs:
count = sum(order.count for order in orders.filter(product=ppix.product))
cost = ppix.product.cost * count
write_sheet.write(ppix.row_in_xls, 4, count, style=style)
write_sheet.write(ppix.row_in_xls, 5, cost, style=style)
total += cost
write_sheet.write(total_row_pos, 5, total, style=total_cell_style)
write_book.save(tmp_filename)
return Response(open(tmp_filename, 'r'), content_type='application/vnd.ms-excel')
def main():
keys=['company_name','job_in_company','company_industry','company_setup_time','company_major_job','company_business_model','company_cash','company_for_who','company_development_stage','company_incash_way','company_incash_number','company_city','company_location','company_empolyees_number']
path_input=r'/var/ftpadmin/forwenjie/file_input'
path_output=r'/var/ftpadmin/forwenjie/file_output'
files=read_all_docxs(path_input)
path_saving_xls=path_output+'/output.xls'
read_book=xlrd.open_workbook(path_saving_xls,formatting_info=True)
write_book=xlcopy(read_book)
sheet1=write_book.get_sheet(0)
insert_index=range(9,23)
for i,file in enumerate(files):
company_info_dict={}
company_info_dict=read_one_docx(file)
for j,key in enumerate(keys):
sheet1.write(i+1,insert_index[j],company_info_dict[key])
write_book.save(path_saving_xls)
def read_docx_and_write():
#use read_all_docx_in,and write to excel one by one
#write logs if error
files=read_all_docx_in()
if files==[]:
return 404
else:
template_file='/var/ftpadmin/forwenjie/template/template.xlsx'
rb=xlrd.open_workbook(template_file)
wb=xlcopy(rb)
sheet=wb.get_sheet(0)
#row=0
for index,file in enumerate(files):
write_dict=read_one_docx(file)
#write_one_docx(index+1,write_dict)
sheet.write(index+1,0,write_dict[0])
sheet.write(index+1,2,write_dict[2])
sheet.write(index+1,1,write_dict[1])
sheet.write(index+1,7,write_dict[7])
sheet.write(index+1,8,write_dict[8])
wb.save('/var/ftpadmin/forwenjie/file_output/output.xls')
def update_xls(log, xl_infile, xl_outfile, keyed_values={}, default='0'):
out_name, out_ext = os.path.splitext(xl_outfile)
if out_ext.lower() == '.xlsx':
raise NotImplementedError('Output must be .xls, not .xlsx. Sorry.')
# Get the key/cell indexes of interest
wb_in, ws_in, field_keys = get_indexes(log, xl_infile)
# Copy the Excel sheet to a write workbook
wb_out = xlcopy(wb_in)
ws_out = wb_out.get_sheet(ws_in.number)
# Set the style to text
style = xlwt.easyxf(num_format_str="0")
for key in field_keys.keys():
if key in keyed_values.keys():
val = keyed_values[key]
log.debug('Writing %s to %s' % (keyed_values[key], key))
else:
val = default
log.warning('Spreadsheet field %s not found in results.' % key)
ws_out.write(*field_keys[key], label=val, style=style)
wb_out.save(xl_outfile)
assert resp.status_code == 200
print(resp.text)
tree = ElementTree.fromstring(resp.content)
genres = []
for child in tree.iter('*'):
if child.tag == "name":
genres.append(child.text)
output_file.write("genres: " + str(genres) + "\n")
return genres
r_book = open_workbook(spreadsheet_loc, formatting_info=False)
r_sheet = r_book.sheet_by_index(0)
w_book = xlcopy(r_book) # cant write and read to same excel file unfortunately
w_sheet = w_book.get_sheet(0)
if not auth_token or not auth_token_secret:
print("getting auth token")
auth_token, auth_token_secret = get_auth_token()
all_genres = set()
r = 3
genre_col = 8
# go through r_sheet and make api call for each
for row in r_sheet.get_rows():
'Phonology':len(all_conditions['Phonology'])-1,
'Dots':len(all_conditions['Dots'])-1,
'Reading':len(all_conditions['Reading'])-1,
'Spatial':150
}
low_thresh_operations = {'addition': len(all_conditions['Math']['addition'])-1}
math_operations = ['addition','subtraction','multiplication','division']
#load pickled data if applicable
if pdata:
print 'load pickle data'
for k in pdata.keys():
exec('{} = pdata[k]'.format(k))
wb_read = open_workbook(old_filename)
wb = xlcopy(wb_read)
all_sheets = {}
for i, sheet in enumerate(wb_read.sheet_names()):
ws_read = wb_read.sheet_by_index(i)
all_sheets[sheet] = dict(sheet = wb.get_sheet(i), headers=[ws_read.cell_value(0,col) for col in range(ws_read.ncols)], row=ws_read.nrows)
else:
# print 'loading things outside of pickle'
points = 0
thesePoints=0
first_pass=True
trial_number = 1
#create output structure
wb = xlwt.Workbook()
choice_headers = [("choice_task_{choice}".format(choice=i+1), "choice_points_{choice}".format(choice=i+1), "choice_pos_{choice}".format(choice=i+1))[j%3] for j,i in enumerate(sorted(range(0, number_of_choices)*3))]
def get_menu(request):
menu = Menu.get_current_menu()
try:
contents = menu.attachment.menufile.read()
except IOError:
raise
read_book = xlrd.open_workbook(file_contents=contents, on_demand=True, formatting_info=True)
write_book = xlcopy(read_book)
dp = DateParser()
menu_date = dp.parse(str(menu.date))
for sheet_index in range(len(write_book._Workbook__worksheets)):
write_sheet = write_book.get_sheet(sheet_index)
sheet_date = dp.parse(write_sheet.name)
if sheet_date == menu_date:
break
else:
raise Exception('Sheet not found!')
orders = Order.objects.filter(menu=menu)
ppixs = XLStructure.objects.filter(product__in=orders.values_list('product'), menu=menu)
style = xlwt.XFStyle()
font = xlwt.Font()
font.bold = True
font.height = 12*20
font.name = 'Calibri'
style.font = font
borders = xlwt.Borders()
borders.bottom = xlwt.Borders.THIN
borders.right = xlwt.Borders.THIN
borders.top = xlwt.Borders.THIN
borders.left = xlwt.Borders.THIN
style.borders = borders
total_cell_style = xlwt.XFStyle()
total_cell_style.font = font
total = 0
total_row_pos = XLStructure.objects.filter(menu=menu).order_by('-position').first().position + 1
for ppix in ppixs:
count = sum(order.count for order in orders.filter(product=ppix.product))
cost = ppix.product.cost * count
write_sheet.write(ppix.row_in_xls, 4, count, style=style)
write_sheet.write(ppix.row_in_xls, 5, cost, style=style)
total += cost
write_sheet.write(total_row_pos, 5, total, style=total_cell_style)
response = HttpResponse(content_type='application/vnd.ms-excel')
write_book.save(response)
menu_dt = datetime.strptime(menu_date, '%Y-%m-%dT%H:%M:%S')
filename = menu_dt.strftime('%d.%m.%Y')
# To inspect details for the below code, see http://greenbytes.de/tech/tc2231/
if u'WebKit' in request.META['HTTP_USER_AGENT']:
# Safari 3.0 and Chrome 2.0 accepts UTF-8 encoded string directly.
filename_header = 'filename={fn}.xls'.format(fn=filename)
elif u'MSIE' in request.META['HTTP_USER_AGENT']:
# IE does not support internationalized filename at all.
# It can only recognize internationalized URL, so we do the trick via routing rules.
filename_header = ''
else:
# For others like Firefox, we follow RFC2231 (encoding extension in HTTP headers).
filename_header = 'filename*=UTF-8\'\'%s' % urllib.quote(filename)
response['Content-Disposition'] = 'attachment; ' + filename_header
return response