async def post(self):
try:
output = BytesIO()
workbook = Workbook(output, {'in_memory': True})
sheet_accuracy = workbook.add_worksheet(name='题目正确率统计')
title_format = workbook.add_format({'font_size': 12, 'bold': '1', 'valign': 'vcenter', 'align': 'left',
'font_name': 'Microsoft YaHei'})
data_format = workbook.add_format({'valign': 'vcenter', 'align': 'left', 'font_name': 'Microsoft YaHei'})
percent_format = workbook.add_format({'valign': 'vcenter', 'align': 'left', 'font_name': 'Microsoft YaHei'})
percent_format.set_num_format('0.00%')
sheet_accuracy.set_column(0, 0, 6)
sheet_accuracy.set_column(1, 1, 24)
sheet_accuracy.set_column(2, 2, 12)
sheet_accuracy.set_column(3, 3, 64)
sheet_accuracy.set_column(4, 4, 36)
sheet_accuracy.set_column(5, 7, 12)
# 标题
sheet_accuracy.write_row(0, 0, data=['序号', '题目ID', '题号', '题目标题', '正确答案', '总作答次数', '正确次数', '正确率'],
cell_format=title_format)
index = 1 # 起始行
report_data = await self.__do_get_report_data()
if report_data:
while await report_data.fetch_next:
data = report_data.next_object()
if data:
sheet_accuracy.set_row(index, cell_format=title_format)
sheet_accuracy.write_number(index, 0, index, data_format)
# 题目属性
subject = data.subject_list[0] if data.subject_list else None
if subject:
sheet_accuracy.write_string(index, 1, subject.custom_code if subject.custom_code else '-',
data_format)
sheet_accuracy.write_string(index, 2, subject.code if subject.code else '-', data_format)
sheet_accuracy.write_string(index, 3, subject.title if subject.title else '-',
data_format)
# 选项属性
for opt in data.subject_option_list:
if opt.correct:
sheet_accuracy.write_string(index, 4,
'%s、%s' % (opt.sort, opt.title) if opt.title else '-',
data_format)
correct = data.correct if data.correct else 0
total = data.total if data.total else 0
sheet_accuracy.write_number(index, 5, total, data_format)
sheet_accuracy.write_number(index, 6, correct, data_format)
sheet_accuracy.write(index, 7, correct / total if total > 0 else 0, percent_format)
index += 1
workbook.close()
self.set_header('Content-Type', 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet')
self.set_header('Content-Disposition', 'attachment; filename=%s' % (
'subject_accuracy_statistics_%s.xlsx' % datetime.datetime.now().strftime('%Y%m%d%H%M%S%f')))
self.write(output.getvalue())
self.finish()
except Exception:
logger.error(traceback.format_exc())
def wr_xlsx(fout_xlsx, xlsx_data, **kws):
"""Write a spreadsheet into a xlsx file."""
if xlsx_data:
from xlsxwriter import Workbook
workbook = Workbook(fout_xlsx)
worksheet = workbook.add_worksheet()
# Cell formatting
fmt_hdr = workbook.add_format({
'top': 1,
'bottom': 1,
'left': 0,
'right': 0,
'bold': True
})
fmt_txt = workbook.add_format({'border': 0})
flds = xlsx_data[0]._fields
if 'fld2col_widths' in kws:
_set_xlsx_colwidths(worksheet, kws['fld2col_widths'], flds)
# Print header
hdrs = flds if 'hdrs' not in kws else kws['hdrs']
for col_idx, hdr in enumerate(hdrs):
worksheet.write(0, col_idx, hdr, fmt_hdr)
row_data_start = 1
# Print data
row_idx = _wrxlsxdata(xlsx_data, row_data_start, worksheet, fmt_txt,
**kws)
workbook.close()
sys.stdout.write(" {:>5} items WROTE: {}\n".format(
row_idx - row_data_start, fout_xlsx))
else:
sys.stdout.write(" 0 items. NOT WRITING {}\n".format(fout_xlsx))
def _write_excel_rows(rows: list, workbook: Workbook,
worksheet: Worksheet) -> int:
excel_longest_word = ''
for i, row in enumerate(rows, 1):
row_format = None
if i == 1:
row_format = workbook.add_format({
'bold': True,
'bg_color': '#cccccc',
})
elif i % 2 == 0:
row_format = workbook.add_format({
'bg_color': '#f1f1f1',
})
range_cells = 'A%s:I10' % i
row_longest_word = find_longest_word(row)
if len(row_longest_word) > len(excel_longest_word):
excel_longest_word = row_longest_word
worksheet.write_row(range_cells, row, row_format)
self.update_state(state='PROGRESS',
meta={
'current': i,
'total': self.total_progress,
'status': 'In progress...',
})
return len(excel_longest_word)
def generate_pricelist(**options):
output = BytesIO()
book = Workbook(output)
sheet = book.add_worksheet()
row = 0
col = 0
merge_format = book.add_format({
'align':'center',
'valign':'vcenter',
'bold': 1,
})
url_format = book.add_format({
'font_color': 'blue',
'underline': 1
})
user = options['user'][0] if options.get('user') else None
if options.get('filters'):
filters = options.get('filters')
for product in Product.objects.filter(**filters):
sheet.write(row, col, product.model)
# sheet.write(row, col + 1, product.name)
sheet.write_url(row, col + 1, "http://%s/%s" % (BASE_URL,product.slug), url_format, product.names(lang='ru'))
sheet.write(row, col + 2, product.big_opt_price)
row += 1
else:
for cat in Category.objects.filter(active=True):
if cat.is_leaf_node():
sheet.merge_range(row,col,row,2,cat.names(lang='ru'),merge_format)
sheet.write(row, col, cat.names(lang='ru'))
row +=1
if not user:
products = Product.objects.filter(category=cat,is_available=True)
else:
products = Product.objects.filter(category=cat,is_available=True,storage=1)
for product in products:
sheet.write(row, col, product.model)
# sheet.write(row, col + 1, product.name)
sheet.write_url(row, col + 1, "http://%s/%s" % (BASE_URL,product.slug), url_format, product.names(lang='ru'))
if not user:
sheet.write(row, col + 2, product.retail_price)
else:
sheet.write(row, col + 2, product.big_opt_price)
row += 1
book.close()
output.seek(0)
if options.get('name'):
path = STATIC_ROOT + options.get('name') + '.xlsx'
else:
path = 'static/pricelist_opt.xlsx' if user else 'static/pricelist.xlsx'
with open(path,'wb') as f:
f.write(output.read())
print('Завершено.')
def prepare_bold_and_border(workbook: Workbook, is_bold: bool,
is_border: bool):
bold_value = 1 if is_bold else 0
border_value = 1 if is_border else 0
bold = workbook.add_format({"bold": bold_value, "border": border_value})
border = workbook.add_format({"border": border_value})
return bold, border
def write_finish(wb: xlsxwriter.Workbook,
ws: xlsxwriter.Workbook.worksheet_class):
values, formats = set(cells_value.keys()), set(cells_format.keys())
for c in values.difference(formats):
ws.write(c, cells_value[c])
for c in values.intersection(formats):
ws.write(c, cells_value[c], wb.add_format(cells_format[c]))
for c in formats.difference(values):
ws.write_blank(c, None, wb.add_format(cells_format[c]))
def write_bookings_to_excel_file(self, booking_path):
print('\nwriting to excel...')
workbook = Workbook(os.path.join(booking_path, 'bookings.xlsx'))
worksheet = workbook.add_worksheet()
worksheet.set_column(2, 3, 50)
worksheet.set_column(1, 1, 9)
worksheet.set_column(4, 4, 9)
bold = workbook.add_format({'bold': True})
cell_format = workbook.add_format({
'bold': True,
'italic': True,
'font_color': 'blue'
})
cell_money = workbook.add_format({
'bold': True,
'italic': True,
'font_color': 'blue',
'num_format': '#,##0.00'
})
money = workbook.add_format({'num_format': '#,##0.00'})
row = 0
worksheet.write(row, 0, 'Code',
bold) # 3 --> row number, column number, value
worksheet.write(row, 1, 'Price', bold)
worksheet.write(row, 2, 'Hotel', bold)
worksheet.write(row, 3, 'Address', bold)
worksheet.write(row, 4, 'Retail', bold)
worksheet.write(row, 5, 'Profit', bold)
row += 1
worksheet.write(row, 0, 'BEST', cell_money)
worksheet.write(row, 1, self.cheap[1], cell_format)
worksheet.write(row, 2, self.cheap[0], cell_format)
worksheet.write(row, 3, self.cheap[2], cell_format)
worksheet.write_formula(1, 4, '=1.374*B2', cell_money)
worksheet.write_formula(1, 5, '=E2-B2', cell_money)
row += 1
for i, option in enumerate(self.options):
if i < 9:
worksheet.write(row, 0, 'AA0' + str(i + 1))
else:
worksheet.write(row, 0, 'AA' + str(i + 1))
worksheet.write(row, 1, option[1], money)
worksheet.write(row, 2, option[0])
worksheet.write(row, 3, option[2])
worksheet.write_array_formula('E3:E31', '{=1.374*B3:B31}', money)
worksheet.write_array_formula('F3:F31', '{=E3:E31-B3:B31}', money)
row += 1
workbook.close()
# fixme WARNING:
# in order for xlsxwriter to create the spreadsheet, the workbook must be closed
# right at the end. If it closes after it won't create it, check it by closing it after the line:
# self.send_attachment (spreadsheet)
spreadsheet = '//home/jmartorell/Booking/bookings/bookings.xlsx'
self.send_attachment(spreadsheet)
def schedule_creator(start, end):
delta = end - start
time = str(datetime.datetime.now().hour) + str(datetime.datetime.now().minute) + str(datetime.datetime.now().second)
year_list = []
month_list = []
date_list = []
year_key = []
month_key = []
date_dict = {}
week_col = []
schedule_book = Workbook(str(start.year)+str(start.month)+str(start.day)+"_"+time+'.xlsx')
worksheet = schedule_book.add_worksheet('Schedule')
cell_format = schedule_book.add_format({'bold':1,'align':'center','valign':'vcenter','border':1})
blank_format = schedule_book.add_format()
blank_format.set_left(7)
blank_format.set_right(7)
week_format = schedule_book.add_format()
week_format.set_right(12)
border_format = schedule_book.add_format()
border_format.set_right(5)
for i in range(0,delta.days):
date = start + datetime.timedelta(days = i)
if calendar.weekday(date.year,date.month,date.day) == 5 or calendar.weekday(date.year,date.month,date.day) == 6:
continue
year_list.append(date.year)
month_list.append(date.month)
date_list.append(date)
count = 0
for i in year_list:
if i not in date_dict.keys():
date_dict[i] = {}
if month_list[count] not in date_dict[i].keys():
date_dict[i][month_list[count]] = []
date_dict[i][month_list[count]].append(date_list[count])
count += 1
col = 1
for year_key in date_dict.keys():
worksheet.merge_range(0,col,0,col + year_list.count(year_key)-1,year_key,cell_format)
for month_key in date_dict[year_key].keys():
worksheet.merge_range(1,col,1,col + len(date_dict[year_key][month_key])-1,calendar.month_name[month_key],cell_format)
for i in date_dict[year_key][month_key]:
worksheet.write(2,col,calendar.day_abbr[calendar.weekday(i.year,i.month,i.day)],cell_format)
worksheet.write(3,col,i.day,cell_format)
if calendar.weekday(i.year,i.month,i.day) == 4:
week_col.append(col)
col += 1
worksheet.set_column(1,col,None,blank_format)
for i in week_col:
worksheet.set_column(i,i,None,week_format)
worksheet.set_column(0,0,None,border_format)
worksheet.set_column(col-1,col-1,None,border_format)
worksheet.set_h_pagebreaks([79])
worksheet.set_v_pagebreaks([35])
schedule_book.close()
def write_df_to_excel(df,
header=None,
filename_end=None,
bold_row_0=False,
bold_col_0=False):
"""
Writes a dataframe with a datetime index to a file.
Takes in an optional header and extra label for the filename.
The first rows and columns can be bolded in the saved excel file with bold_[row/col]_0.
:param pd.DataFrame df: Pandas dataframe to write to file
**must have a datetime index
:param list header: list of headers to write to the file if desired. Usually, it's ['date'] + df.columns.tolist()
:param str filename_end: a string to append to the filename if desired, otherwise the current date is used only
:param bool bold_row_0: True to bold the first row, defaults to false/non-bold
:param bool bold_col_0: True to bold the first column, defaults to false/non-bold
:return: None; writes to file
"""
from xlsxwriter import Workbook
df.index = df.index.map(lambda x: datetime.strftime(
x, '%Y-%m-%d %H:%M:%S')) # change index to ISO8601 time
datestamp = datetime.now().strftime("%Y_%m_%d")
filename = f"{datestamp}_{filename_end}.xlsx" if filename_end else f"{datestamp}_data.xlsx"
workbook = Workbook(filename)
workbook.nan_inf_to_errors = True
worksheet = workbook.add_worksheet('Data')
row_0_fmt = workbook.add_format(
{'bold': bold_row_0}) # create row formats to apply based on inputs
col_0_fmt = workbook.add_format({'bold': bold_col_0})
if header:
for col, head in enumerate(header):
worksheet.write(0, col, head, row_0_fmt)
row_count = 1 if header else 0 # start on row 1 if a header was provided
for row in df.iterrows():
col_count = 1
worksheet.write(row_count, 0, row[0],
col_0_fmt) # write index to first col
for col in row[
1]: # row[1] is all values that aren't the index - pandas df.iterrows() specific
worksheet.write(row_count, col_count, col)
col_count += 1
row_count += 1
workbook.close()
class ExcelManager():
def __init__(self, path):
# Create Workbook
self.workbook = Workbook(path)
# Create Sheet
self.sheet = self.workbook.add_worksheet()
def close(self):
try:
self.workbook.close()
except:
print("Please close the excel file!")
def write(self, cell, value, options=None):
self.sheet.write(cell, value, options)
def get_xlsx_file(self, prices_dic):
alphabet = list("ABCDEFGHIJKLMNOPQRSTUVWXYZ")
names = [price["name"] for price in prices_dic]
links = [price["link"] for price in prices_dic]
prices = [price["price"] for price in prices_dic]
updated = [price["updated"] for price in prices_dic]
# Configure Formats
bold = self.workbook.add_format({
"bold": True,
})
money = self.workbook.add_format({'num_format': '#,##0.00 €'})
# Define columns
self.write("A1", "Name:", bold)
self.write("B1", "Link:", bold)
self.write("C1", "Preis:", bold)
self.write("D1", "Updated:", bold)
# Write names
for index, name in enumerate(names, 2):
self.write("A" + str(index), name)
# Write links
for index, link in enumerate(links, 2):
self.write("B" + str(index), link)
# Write prices
for index, price in enumerate(prices, 2):
self.write("C" + str(index), price, money)
# Write update time
for index, updated in enumerate(updated, 2):
self.write("D" + str(index), updated)
# Close / Save file
self.close()
def write_xlsx(serializer, headers, title_text):
output = StringIO.StringIO()
workbook = Workbook(output)
worksheet_s = workbook.add_worksheet('Summary')
# Here we will adding the code to add data
title = workbook.add_format({
'bold': True,
'font_size': 14,
'align': 'center',
'valign': 'vcenter'
})
header = workbook.add_format({
'bg_color': '#92d050',
'color': 'black',
'align': 'center',
'valign': 'top',
'border': 1
})
cell = workbook.add_format({
'align': 'left',
'valign': 'top',
'text_wrap': False,
'border': 1
})
hearder_len = len(headers)
header_range = xl_range(0, 0, 0, hearder_len - 1)
worksheet_s.merge_range(header_range, title_text, title)
# column header
for col, field in enumerate(headers):
worksheet_s.write(1, col, field[1], header)
worksheet_s.set_column(col, col, width=field[2])
# column content
for row, line in enumerate(serializer.data, start=2):
# Write a line for each record
for col, field in enumerate(headers):
keys = field[0].split('.')
value = line
for key in keys[:-1]:
value = value[key]
if "|" in keys[-1]:
key, flt = keys[-1].split("|")
value = field_display(value[key], flt)
else:
value = value[keys[-1]]
worksheet_s.write(row, col, value, cell)
workbook.close()
xlsx_data = output.getvalue()
# xlsx_data contains the Excel file
return xlsx_data
def make_xlsx_response(sheets, basename):
output = StringIO()
workbook = Workbook(output)
header_format = workbook.add_format({'bold': True, 'border': 1})
for title, rows in sheets.items():
if not len(rows):
continue
worksheet = workbook.add_worksheet(title)
row_idx = 0
headers = None
for row in rows:
if headers is None:
headers = row.keys()
for c, cell in enumerate(headers):
worksheet.write(row_idx, c, cell, header_format)
row_idx += 1
col_idx = 0
for cell in headers:
worksheet.write(row_idx, col_idx, row.get(cell))
col_idx += 1
row_idx += 1
worksheet.freeze_panes(1, 0)
workbook.close()
output.seek(0)
fn = basename + '.xlsx'
headers = {'Content-Disposition': 'attachment; filename=%s' % fn}
return Response(output, mimetype=XLSX_MIME, headers=headers)
def weekly_report():
date = request.args.get('date')
if date:
dt = datetime.strptime(date, '%Y-%m-%d').date()
else:
dt = datetime.now().date()
start = dt- timedelta(days=dt.weekday()) # start of the week
end = start + timedelta(days=6) # end of the week
timesheets = Timesheet.query.filter(Timesheet.user_id == current_user.id, db.func.date(Timesheet.date)>=start, db.func.date(Timesheet.date)<=end).order_by(Timesheet.date).all()
output = BytesIO()
workbook = Workbook(output, {'in_memory': True})
worksheet = workbook.add_worksheet('weekly timesheet')
row = 1
total = 0
bold = workbook.add_format({'bold': True})
for timesheet in timesheets:
worksheet.write(row, 0, datetime.strftime(timesheet.date, "%m/%d/%Y"))
subject_task = timesheet.subject.name
if timesheet.task:
subject_task += f", {timesheet.task.name}"
worksheet.write(row, 1, subject_task)
worksheet.write(row, 2, timesheet.workhours)
worksheet.write(row, 3, timesheet.description)
total = add_workhours(total, timesheet.workhours)
row += 1
worksheet.write(0, 0, "Sum", bold)
worksheet.write(0, 2, total, bold)
for i in range(4):
set_column_autowidth(worksheet, i)
workbook.close()
output.seek(0)
return Response(output, mimetype="application/ms-excel", headers={"Content-Disposition":f"attachment;filename=weekly_report_{current_user.username}_{date}.xlsx"})
def ParsetoExcel(oldfilename, newfilename, Format, oldLines, newLines,
LineDifference, percentage):
outworkbook = Workbook('CodeTracker_Mohiddin.xlsx',
{'strings_to_numbers': True})
DataSheet = outworkbook.add_worksheet()
cell_format = outworkbook.add_format({
'bold': True,
'bg_color': '#e6f2ff',
'border': 1,
'border_color': '#000000'
})
#Datacell_format = outworkbook.add_format({'border':1,'border_color':'#000000'})
DataSheet.write(0, 0, 'OLDFILENAME', cell_format)
DataSheet.write(0, 1, 'NEWFILENAME', cell_format)
DataSheet.write(0, 2, 'EXTENSION_FORMAT', cell_format)
DataSheet.write(0, 3, 'NO_OF_OLDLINES', cell_format)
DataSheet.write(0, 4, 'NO_OF_NEWLINES', cell_format)
DataSheet.write(0, 5, 'LINE_DIFFERENCE', cell_format)
DataSheet.write(0, 6, 'CHANGE_PERCENTAGE', cell_format)
DataSheet.write(0, 7, 'RE_USE_PERCENTAGE', cell_format)
DataSheet.write(0, 8, 'MODULE_NAME', cell_format)
for row_index, row_value in enumerate(
zip(oldfilename, newfilename, Format, oldLines, newLines,
LineDifference, percentage, ReusePercent, Modulename)):
for col_index, col_value in enumerate(row_value):
DataSheet.write(row_index + 1, col_index, col_value)
outworkbook.close()
def __create_common_excel(self, first_column_list, options={}):
"""
生成excel 模板
:param first_column_list: 单位列名
:param options:
:return:
"""
output = BytesIO()
workbook = Workbook(output, {'in_memory': True})
# sheet 1名称
worksheet_subject = workbook.add_worksheet(
options['sheet_name1'] if 'sheet_name1' in options else None)
line_index = 0 # 起始line
first_column_num = len(first_column_list) # 列总数
worksheet_subject.set_column(0, first_column_num - 1, 14)
column_format = workbook.add_format({
'bold': '1',
'valign': 'vcenter',
'align': 'center',
'font_name': 'Microsoft YaHei'
})
worksheet_subject.set_row(line_index, 20, column_format)
for index, column in enumerate(first_column_list):
worksheet_subject.write_string(line_index, index, column,
column_format)
workbook.close()
return output.getvalue()
class XlsWriter(Writer):
def write(self):
super().write()
self.workbook = Workbook(self.filename + ".xlsx")
self.cell_formats = {}
for key, value in self.colours.items():
self.cell_formats[key] = self.workbook.add_format()
self.cell_formats[key].set_bg_color(value)
for sheet in self.sheets:
self.write_worksheet(sheet)
self.workbook.close()
def write_worksheet(self, name):
worksheet = self.workbook.add_worksheet(name)
r = 0
c = 0
for header in self.sheet_data[name]["headers"]:
cell = worksheet.write(r, c, header, self.cell_formats["s"])
c += 1
c = 0
r += 1
for row in self.sheet_data[name]["rows"]:
c = 0
for col in row:
if c >= len(self.sheet_data[name]["colours"]):
cell_format = "p"
else:
cell_format = self.sheet_data[name]["colours"][c]
cell = worksheet.write(r, c, col,
self.cell_formats[cell_format])
c += 1
r += 1
def write_cell_errors(xlsxname, header, lines, cells_to_mark):
workbook = Workbook(join(SPREADSHEETS_DIR, xlsxname))
cell_fmt = workbook.add_format()
cell_fmt.set_bg_color('cyan')
worksheet = workbook.add_worksheet()
write_sheet(worksheet, cell_fmt, header, lines, cells_to_mark)
workbook.close()
def sqlExcel2():
global window, canvas, paper, filename, inImage, outImage, inW, inH, outW, outH
outfilename = asksaveasfile(parent=window,
mode='wb',
defaultextension="*.xlsx",
filetypes=(("xlsx파일", "*.xlsx"), ("모든파일",
"*.*")))
wb = Workbook(outfilename)
ws = wb.add_worksheet(os.path.basename(filename))
with open(filename, 'rb') as fReader:
# 워크시트의 열 너비 / 행 높이 지정
ws.set_column(0, inW, 1.0) # 0.34
for row in range(inH):
ws.set_row(row, 9.5) # 0.35
for i in range(inW):
for j in range(inH):
data = inImage[i][j] # 기존에 있던 inImage에서 출력
# data 셀 배경색 지정 #000000~FFFFFF
if data[0] <= 15: # 15 이하일 경우, 1자리 수이기 때문에 0을 추가
hexStr = '#' + ('0' + hex(data[0])[2:])
else:
hexStr = '#' + (hex(data[0])[2:]) # 16진수 변환 후, R(2자리)
if data[1] <= 15:
hexStr += ('0' + hex(data[1])[2:]) # G(2자리)
else:
hexStr += hex(data[1])[2:]
if data[2] <= 15:
hexStr += ('0' + hex(data[2])[2:]) # B(2자리)
else:
hexStr += hex(data[2])[2:]
cell_format = wb.add_format() # RGB코드는 #을 앞에
cell_format.set_bg_color(hexStr)
ws.write(i, j, '', cell_format)
wb.close()
def export_xlsx(request):
output = io.BytesIO()
workbook = Workbook(output, {'in_memory': True})
stores_worksheet = workbook.add_worksheet('stores')
products_worksheet = workbook.add_worksheet('products')
stores_worksheet.write('A1', 'Name')
stores_worksheet.write('B1', 'Address')
for i, store in enumerate(Store.objects.all()):
stores_worksheet.write(i + 1, 0, store.name)
stores_worksheet.write(i + 1, 1, store.address)
products_worksheet.write('A1', 'Name')
products_worksheet.write('B1', 'Weight')
products_worksheet.write('C1', 'Brand')
products_worksheet.write('D1', 'Price')
products_worksheet.write('E1', 'Expiration Date')
dateFormat = workbook.add_format({'num_format': 'dd/mm/yy'})
for i, product in enumerate(Product.objects.all()):
products_worksheet.write(i + 1, 0, product.name)
products_worksheet.write(i + 1, 1, product.weight)
products_worksheet.write(i + 1, 2, product.brand)
products_worksheet.write(i + 1, 3, product.price)
products_worksheet.write(i + 1, 4, product.expirationDate, dateFormat)
workbook.close()
output.seek(0)
response = HttpResponse(
output.read(),
content_type=
"application/vnd.openxmlformats-officedocument.spreadsheetml.sheet")
response['Content-Disposition'] = "attachment; filename=testapp.xlsx"
return response
def write_excel(f_name, data):
print("Writing to Excel file..")
row = 0
wb = Workbook(f_name)
sheet1 = wb.add_worksheet('Sheet 1')
bold = wb.add_format({'bold': True, 'bg_color': 'silver'})
# Add header
for x in data.keys():
column = 0
for y in data[x].keys():
sheet1.write(row, column, y, bold)
column += 1
break
row += 1
# Add data
for x in data.keys():
column = 0
for y in data[x].keys():
#sheet1.write(row, column, x)
sheet1.write(row, column, data[x][y])
column += 1
print(x, "-", y, "-", data[x][y], row, column)
row += 1
wb.close()
def write_data_excel(data1, data2, path):
# print ('data1:', data1)
# print ('data2:', data2)
name = path.split('/')[-1]
create_time = time.strftime('%Y-%m-%d-%H-%M', time.localtime(time.time()))
pure_name = '%s_%s.xlsx' % (name, create_time)
file_name = os.path.join(path, pure_name)
# print("file_name:", file_name)
new_f = Workbook(file_name)
sheet1 = new_f.add_worksheet(create_time)
sheet1_format = new_f.add_format({})
sheet1_format.set_text_wrap()
sheet1.set_column(0, 0, 25)
sheet1.set_column(1, 4, 60)
for i in range(len(data1)):
if i:
sheet1.set_row(i, 125)
print "start to insert image {}".format(data2[i])
sheet1.insert_image(i, 0, data2[i])
for j in range(len(data1[i])):
if i == 0:
sheet1.set_row(0, 18)
headers = ['PRODUCE NAME', 'PRODUCE PRICE', 'THE LINK']
sheet1.write(0, j + 1, headers[j])
else:
try:
sheet1.write(i, (j + 1), data1[i][j], sheet1_format)
except:
pass
new_f.close()
return file_name
def imprimirCabecera(workbook: xlsxwriter.Workbook, ws, config: tripConfig,
tipo: int):
# Damos formato final
formato_titulo = workbook.add_format({
"bold": 1,
"border": 1,
"align": "center",
"valign": "vcenter",
"font_size": 14,
})
formato_cabecera = workbook.add_format({
"bold": 1,
"border": 1,
"align": "center",
"valign": "vcenter",
"font_size": 13,
})
ancho_cabecera = 0
if tipo == 1:
ancho_cabecera = 10
if tipo == 2:
ancho_cabecera = 8
ws.set_paper(9)
ws.merge_range(0, 0, 2, 1, "", formato_titulo)
ws.merge_range(0, 2, 0, ancho_cabecera,
"LISTA DE ZARPE - " + ws.get_name(), formato_titulo)
ws.merge_range(
1,
2,
1,
ancho_cabecera,
f"Embarcación: {config.nave} | Placa: {config.placa} | {config.ruta}",
formato_cabecera,
)
ws.merge_range(
2,
2,
2,
ancho_cabecera,
f"Trayecto: {config.trayecto} | Fecha: {config.fecha}",
formato_cabecera,
)
def process(self, items: List[Any]) -> None:
workbook = Workbook('deaditions.xlsx')
worksheet = workbook.add_worksheet()
worksheet.set_column(0, 9, 15)
bold = workbook.add_format({'bold': 1})
date = workbook.add_format({'num_format': 'dd.mm.yyyy HH:ss'})
ranking = workbook.add_format({'num_format': '#.##'})
worksheet.write('A1', 'Name', bold)
worksheet.write('B1', 'Link', bold)
worksheet.write('C1', 'Available', bold)
worksheet.write('D1', 'Description', bold)
worksheet.write('E1', 'Editors', bold)
worksheet.write('F1', 'Ranking', bold)
worksheet.write('G1', 'Published (manual)', bold)
worksheet.write('H1', 'Lastseen (manual)', bold)
worksheet.write('I1', 'Published (wayback)', bold)
worksheet.write('J1', 'Lastseen (wayback)', bold)
row = 0
for item in items:
row += 1
worksheet.write_string(row, 0, item.name)
worksheet.write_url(row, 1, item.href, string='Link')
worksheet.write_string(row, 2, str(item.meta.available))
worksheet.write_string(row, 3, item.meta.description)
worksheet.write_string(row, 4, item.meta.editors or '')
worksheet.write_number(row, 5, item.meta.ranking, ranking)
if item.meta.published.man:
worksheet.write_number(row, 6, item.meta.published.man)
if item.meta.lastseen.man:
worksheet.write_number(row, 7, item.meta.lastseen.man)
if item.meta.published.ia:
worksheet.write_datetime(row, 8, item.meta.published.ia, date)
if item.meta.lastseen.ia:
worksheet.write_datetime(row, 9, item.meta.lastseen.ia, date)
worksheet.write(row + 1, 0, 'Deaditions', bold)
worksheet.write(row + 1, 9, '=ROW()-1', bold)
workbook.close()
def create_sheet_with_resightings(workbook_name: str, resightings: list) -> Workbook:
"""Places resightings in workbook with named columns, treating images specially"""
if not resightings:
return
workbook = Workbook(workbook_name)
sheet = workbook.add_worksheet()
# Format object to center data and wrap text
cell_format = workbook.add_format()
cell_format.set_shrink()
cell_format.set_text_wrap()
cell_format.set_align("center")
cell_format.set_align("vcenter")
# Add column headings
for field_name, col_index in FIELDS.items():
sheet.write(0, col_index, field_name)
# Set starting column width (modifies from range col_index -> col_index)
if "Image" in field_name:
# Images need a wider column
sheet.set_column(col_index, col_index, 45, cell_format)
else:
sheet.set_column(col_index, col_index, 12, cell_format)
# For each resighting, slot the data in each field into its corresponding cell
for row_num, resight in enumerate(resightings, 1):
sheet.set_row(row_num, 160) # Set row height
for field, data in resight.items():
if field in FIELDS:
# image data needs special handling
# TODO: save image in a directory as well
if "Image" in field:
# Read from json as string
binary = io.BytesIO(base64.b64decode(data))
img = Image.open(binary)
# Resize to fit Excel
img.thumbnail((300, 300), Image.ANTIALIAS)
resized = io.BytesIO()
img.save(resized, format=img.format)
# Insert into correct cell
sheet.insert_image(
row_num, FIELDS[field], "image", {"image_data": resized, "positioning": 1}
)
# Write other data, converting if necessary
else:
if field == "Timestamp":
# Convert epoch time to month/day/year
data = datetime.datetime.fromtimestamp(data).strftime("%m/%d/%Y %H:%M:%S")
sheet.write(row_num, FIELDS[field], data)
else:
print("Field not recognized: {}".format(field))
return workbook
def write_comments_to_xlsx(xlsx_file_name, comments):
workbook = Workbook(xlsx_file_name)
worksheet = workbook.add_worksheet()
bold = workbook.add_format({'bold': True})
worksheet.set_column('A:A', 55)
worksheet.set_column('B:B', 25)
worksheet.set_column('C:C', 100)
cell_format = workbook.add_format()
col = 0
for comment_number, comment in enumerate(comments):
row = comment_number
worksheet.write_url(
row, col, f"https://www.instagram.com/{comment['username']}", bold)
worksheet.write(row, col + 1, comment['created_at'])
worksheet.write(row, col + 2, comment['text'], cell_format)
workbook.close()
logging.info(f'Saved {len(comments)} comments to {xlsx_file_name}')
def genera_e_salva(self,
nome='File.xlsx',
scadenza=None,
ordina_fogli=True,
save_to_memory=False):
"""
Genera il file e lo salva su database.
:param nome: Il nome del file da allegare (opzionale, default 'File.xlsx').
:param scadenza: Scadenza del file. Domani.
:return:
"""
generatore = GeneratoreNomeFile('allegati/')
zname = generatore(self, nome)
self.prepara_cartelle(MEDIA_ROOT + zname)
if save_to_memory:
workbook = Workbook(self.output)
else:
workbook = Workbook(MEDIA_ROOT + zname)
bold = workbook.add_format({'bold': True})
# Per ogni foglio
for foglio in [x for x in self.fogli]:
# Aggiunge il foglio
worksheet = workbook.add_worksheet(foglio.nome)
# Aggiunge l'intestazione
for col, testo in enumerate(foglio.intestazione):
worksheet.write(0, col, str(testo), bold)
# Aggiunge il contenuto
for riga, colonne in enumerate(foglio.contenuto):
riga += 1 # Indice shiftato per intestazione
for colonna, testo in enumerate(colonne):
if isinstance(testo, datetime):
testo = testo.strftime("%d/%m/%Y %H:%M")
if isinstance(testo, date):
testo = testo.strftime("%d/%m/%Y")
if testo == ", ": # Rimuove campi ', '
testo = ""
worksheet.write(riga, colonna, str(testo))
if ordina_fogli:
workbook.worksheets_objs.sort(key=lambda x: x.name)
workbook.close()
if save_to_memory:
self.output.seek(0)
else:
self.file = zname
self.nome = nome
self.scadenza = scadenza or domani()
self.save()
def write_mismatch(filename, etalon, tocomp, mismatch):
workbook = Workbook(join(SPREADSHEETS_DIR, get_filebase(filename)+'.xlsx'))
cell_fmt = workbook.add_format()
cell_fmt.set_bg_color('cyan')
worksheet = workbook.add_worksheet(name='test')
write_sheet(worksheet, cell_fmt, *tocomp, cells_to_mark=mismatch)
worksheet = workbook.add_worksheet(name='etalon')
write_sheet(worksheet, cell_fmt, *etalon)
workbook.close()
def generar_layout(datos, salida):
wb = Workbook(salida)
ws = wb.add_worksheet('Layout')
encabezados = '''
Nombre
Inicio
Longitud
Tipo de dato capturado
'''.split('\n')[1:-1]
# Escribir encabezados
col_enc_bg = "#{:02x}{:02x}{:02x}".format(15, 36, 62).upper()
col_ren2 = "#{:02x}{:02x}{:02x}".format(220, 230, 241).upper()
format_enc = wb.add_format({
'bold': True,
'font_color': 'white',
'bg_color': col_enc_bg
})
format_ren1 = wb.add_format({'border': 1})
format_ren2 = wb.add_format({'border': 1, 'bg_color': col_ren2})
for col, encabezado in enumerate(encabezados):
ws.write(0, col, encabezado, format_enc)
# Escribir datos del diccionario
for renglon, registro in enumerate(datos, 1):
formato = format_ren1 if renglon % 2 == 0 else format_ren2
# Registros2 = namedtuple('Registros', 'etiqueta nombre inicio longitud tipo_cap')
ws.write(renglon, 0, registro.etiqueta, formato)
ws.write(renglon, 1, int(registro.inicio), formato)
ws.write(renglon, 2, int(registro.longitud), formato)
ws.write(renglon, 3, registro.tipo_cap, formato)
# Aplicando formato a la hoja
ws.freeze_panes(1, 0)
ws.autofilter('A1:D1')
ws.set_column(0, 0, 55)
ws.set_column(1, 1, 8)
ws.set_column(2, 2, 10)
ws.set_column(3, 3, 25)
ws.hide_gridlines(2)
wb.close()
click.launch(salida)
print("Layout generado")
def get_writer_format(workbook: xlsxwriter.Workbook,
style: ColumnStyle) -> Union[Tuple[Format, int], None]:
if style:
wb_format = workbook.add_format({
'text_wrap': 1,
'valign': 'top'
}) if style['wrap_text'] else None
return wb_format, style['width']
else:
return None
def dispatch(dataset):
buf = StringIO()
wb = Workbook(buf,
{'constant_memory': True,
'in_memory': True,
'default_date_format': 'yyyy/mm/dd hh:mm:ss'}
)
format_ = wb.add_format(FORMAT)
# dataset metadata
ws = wb.add_worksheet('Global attributes')
write_metadata(ws, dataset, 0, 0, format_)
# 1D grids
for grid in [g for g in walk(dataset, GridType) if len(g.shape) == 1]:
logger.debug('Grid {}'.format(grid.name))
ws = wb.add_worksheet(grid.name)
# headers
ws.write(0, 0, grid.dimensions[0], format_)
ws.write(0, 1, grid.name, format_)
# data
for j, data in enumerate(grid.data):
for i, value in enumerate(numpy.asarray(data)):
ws.write(i+1, 1-j, value)
# add var metadata
write_metadata(ws, grid, 0, 2, format_)
# sequences
for seq in walk(dataset, SequenceType):
logger.debug('Sequence {}'.format(seq.name))
ws = wb.add_worksheet(seq.name)
# add header across the first row
for j, var_ in enumerate(seq.keys()):
ws.write(0, j, var_, format_)
# add data in the subsequent rows
for i, row in enumerate(seq.data):
for j, value in enumerate(row):
ws.write(i+1, j, value)
# add var metadata in columns to the right of the data
n = 0
j = len(seq.keys())+1
for child in seq.children():
logger.debug("Child {}".format(child.name))
ws.merge_range(n, j, n, j+1, child.name, format_)
n = write_metadata(ws, child, n+1, j, format_)+1
wb.close()
return buf
def adjust_rows_height(cls,
df: pd.DataFrame,
wb: Workbook,
worksheet: Worksheet):
bold_fmt = wb.add_format({'bold': True})
worksheet.set_row(0, cell_format=bold_fmt)
for idx, row in df.iterrows():
height = max([1 + sum([1 for ch in str(c or '') if ch == '\n'])
for c in row])
height = min(height, cls.MAX_ROW_HEIGHT)
worksheet.set_row(idx + 1, height * worksheet.default_row_height)
def genera_e_salva(self, nome='File.xlsx', scadenza=None, ordina_fogli=True, save_to_memory=False):
"""
Genera il file e lo salva su database.
:param nome: Il nome del file da allegare (opzionale, default 'File.xlsx').
:param scadenza: Scadenza del file. Domani.
:return:
"""
generatore = GeneratoreNomeFile('allegati/')
zname = generatore(self, nome)
self.prepara_cartelle(MEDIA_ROOT + zname)
if save_to_memory:
workbook = Workbook(self.output)
else:
workbook = Workbook(MEDIA_ROOT + zname)
bold = workbook.add_format({'bold': True})
# Per ogni foglio
for foglio in [x for x in self.fogli]:
# Aggiunge il foglio
worksheet = workbook.add_worksheet(foglio.nome)
# Aggiunge l'intestazione
for col, testo in enumerate(foglio.intestazione):
worksheet.write(0, col, str(testo), bold)
# Aggiunge il contenuto
for riga, colonne in enumerate(foglio.contenuto):
riga += 1 # Indice shiftato per intestazione
for colonna, testo in enumerate(colonne):
if isinstance(testo, datetime):
testo = testo.strftime("%d/%m/%Y %H:%M")
if isinstance(testo, date):
testo = testo.strftime("%d/%m/%Y")
if testo == ", ": # Rimuove campi ', '
testo = ""
worksheet.write(riga, colonna, str(testo))
if ordina_fogli:
workbook.worksheets_objs.sort(key=lambda x: x.name)
workbook.close()
if save_to_memory:
self.output.seek(0)
else:
self.file = zname
self.nome = nome
self.scadenza = scadenza or domani()
self.save()
def generar_layout(datos, salida):
wb = Workbook(salida)
ws = wb.add_worksheet('Layout')
encabezados = '''
Nombre
Inicio
Longitud
Tipo de dato capturado
'''.split('\n')[1:-1]
# Escribir encabezados
col_enc_bg = "#{:02x}{:02x}{:02x}".format(15, 36, 62).upper()
col_ren2 = "#{:02x}{:02x}{:02x}".format(220, 230, 241).upper()
format_enc = wb.add_format({'bold': True, 'font_color': 'white', 'bg_color': col_enc_bg})
format_ren1 = wb.add_format({'border': 1})
format_ren2 = wb.add_format({'border': 1, 'bg_color': col_ren2})
for col, encabezado in enumerate(encabezados):
ws.write(0, col, encabezado, format_enc)
# Escribir datos del diccionario
for renglon, registro in enumerate(datos, 1):
formato = format_ren1 if renglon % 2 == 0 else format_ren2
# Registros2 = namedtuple('Registros', 'etiqueta nombre inicio longitud tipo_cap')
ws.write(renglon, 0, registro.etiqueta, formato)
ws.write(renglon, 1, int(registro.inicio), formato)
ws.write(renglon, 2, int(registro.longitud), formato)
ws.write(renglon, 3, registro.tipo_cap, formato)
# Aplicando formato a la hoja
ws.freeze_panes(1, 0)
ws.autofilter('A1:D1')
ws.set_column(0, 0, 55)
ws.set_column(1, 1, 8)
ws.set_column(2, 2, 10)
ws.set_column(3, 3, 25)
ws.hide_gridlines(2)
wb.close()
click.launch(salida)
print("Layout generado")
def medicamentos_topOrganizacionesPorPedidoExcel(request):
datos = request.session['estadistica']['excel']['datos']
medicamento = request.session['estadistica']['excel']['medicamento']
excel = io.BytesIO()
workbook = Workbook(excel, {'in_memory': True})
worksheet = workbook.add_worksheet()
titulo = workbook.add_format({
'font_name':'Arial',
'font_size': 12,
'font_color': 'navy',
'bold': True
})
encabezado = workbook.add_format({
'font_name': 'Arial',
'bold': True
})
alignLeft = workbook.add_format({
'align': 'left',
})
worksheet.write('A1:B1', 'Organizaciones mas demandantes del medicamento '+medicamento+' (por pedido)', titulo)
worksheet.set_column('B:B', 40)
worksheet.set_column('C:C', 20)
worksheet.write('A2', '#', encabezado)
worksheet.write('B2', 'Organizacion', encabezado)
worksheet.write('C2', 'Cantidad', encabezado)
fila = 2
tope = len(datos)
for i in range(0, tope):
worksheet.write(fila, 0, i + 1, alignLeft)
worksheet.write(fila, 1, datos[i]['organizacion'], alignLeft)
worksheet.write(fila, 2, datos[i]['cantidad'], alignLeft)
fila += 1
workbook.close()
excel.seek(0)
response = HttpResponse(excel.read(), content_type="application/vnd.openxmlformats-officedocument.spreadsheetml.sheet")
response['Content-Disposition'] = "attachment; filename=OrganizacionesMasDemandantesDeMedicamentoPorPedido.xlsx"
return response
def pedidosAlaboratorio_topLabConMasSolicitudesPedidosExcel(request):
datos = request.session['estadistica']['excel']
excel = io.BytesIO()
workbook = Workbook(excel, {'in_memory': True})
worksheet = workbook.add_worksheet()
titulo = workbook.add_format({
'font_name':'Arial',
'font_size': 12,
'font_color': 'navy',
'bold': True
})
encabezado = workbook.add_format({
'font_name':'Arial',
'bold': True
})
alignLeft = workbook.add_format({
'align':'left',
})
worksheet.write('A1:B1', 'Laboratorios con mas solicitudes (por medicamento)', titulo)
worksheet.set_column('B:B', 40)
worksheet.set_column('C:C', 20)
worksheet.write('A2', '#', encabezado)
worksheet.write('B2', 'Laboratorio', encabezado)
worksheet.write('C2', 'Cantidad', encabezado)
fila = 2
tope = len(datos)
for i in range(0, tope):
worksheet.write(fila, 0, i + 1, alignLeft)
worksheet.write(fila, 1, datos[i]['laboratorio'], alignLeft)
worksheet.write(fila, 2, datos[i]['cantidad'], alignLeft)
fila += 1
workbook.close()
excel.seek(0)
response = HttpResponse(excel.read(), content_type="application/vnd.openxmlformats-officedocument.spreadsheetml.sheet")
response['Content-Disposition'] = "attachment; filename=LaboratoriosConMasSolicitudesDeMedicamentos.xlsx"
return response
def GetDefaultCellFormat(work_book: Workbook) -> Format:
if not Workbook:
return None
format_dict = {
'bold': 1,
'border': 1,
'align': 'center',
'valign': 'vcenter',
'fg_color': 'yellow'
}
return work_book.add_format(format_dict)
def toExcel(self, outFileName):
"""
Write the distribution fitter results to an Excel spreadsheet
:param outFileName: output spreadsheet name
:type outFileName: str
:return: Nothing
:rtype: None
"""
workbook = Workbook(outFileName, {'constant_memory': True})
workbook.use_zip64() # allow large size Excels just in case
wks = workbook.add_worksheet('Distribution Fitting')
hdrFmt = workbook.add_format({
'bold': True,
'underline': True,
'align': 'center'
})
resultFormats = [workbook.add_format({'num_format' : fmtStr}) \
for fmtStr in ['0.000000', '0.0000%']]
row = 0
wks.set_column(0, 0, 11)
wks.set_column(1, 1, 8, resultFormats[0])
wks.set_column(2, 2, 10.6, resultFormats[1])
for col, headerName in enumerate(self.getHeaderList()):
wks.write_string(row, col, headerName, hdrFmt)
for distrName, (results, params) in self.result.iteritems():
row += 1
col = 0
wks.write_string(row, col, distrName)
for col, (result, outFormat) in \
enumerate(itertools.izip(results, resultFormats), col+1):
wks.write_number(row, col, result, outFormat)
for col, paramValue in enumerate(params, col + 1):
wks.write_number(row, col, paramValue)
workbook.close()
def wr_xlsx(fout_xlsx, xlsx_data, **kws):
"""Write a spreadsheet into a xlsx file."""
if xlsx_data:
from xlsxwriter import Workbook
workbook = Workbook(fout_xlsx)
worksheet = workbook.add_worksheet()
# Cell formatting
fmt_hdr = workbook.add_format({'top':1, 'bottom':1, 'left':0, 'right':0, 'bold':True})
fmt_txt = workbook.add_format({'border':0})
flds = xlsx_data[0]._fields
if 'fld2col_widths' in kws:
_set_xlsx_colwidths(worksheet, kws['fld2col_widths'], flds)
# Print header
hdrs = flds if 'hdrs' not in kws else kws['hdrs']
for col_idx, hdr in enumerate(hdrs):
worksheet.write(0, col_idx, hdr, fmt_hdr)
row_data_start = 1
# Print data
row_idx = _wrxlsxdata(xlsx_data, row_data_start, worksheet, fmt_txt, **kws)
workbook.close()
sys.stdout.write(" {:>5} items WROTE: {}\n".format(row_idx-row_data_start, fout_xlsx))
else:
sys.stdout.write(" 0 items. NOT WRITING {}\n".format(fout_xlsx))
def toExcel(self, outFileName):
"""
Write the distribution fitter results to an Excel spreadsheet
:param outFileName: output spreadsheet name
:type outFileName: str
:return: Nothing
:rtype: None
"""
workbook = Workbook(outFileName, {'constant_memory': True})
workbook.use_zip64() # allow large size Excels just in case
wks = workbook.add_worksheet('Distribution Fitting')
hdrFmt = workbook.add_format({'bold' : True,
'underline' : True,
'align' : 'center'})
resultFormats = [workbook.add_format({'num_format' : fmtStr}) \
for fmtStr in ['0.000000', '0.0000%']]
row = 0
wks.set_column(0, 0, 11)
wks.set_column(1, 1, 8, resultFormats[0])
wks.set_column(2, 2, 10.6, resultFormats[1])
for col, headerName in enumerate(self.getHeaderList()):
wks.write_string(row, col, headerName, hdrFmt)
for distrName, (results, params) in self.result.iteritems():
row += 1
col = 0
wks.write_string(row, col, distrName)
for col, (result, outFormat) in \
enumerate(itertools.izip(results, resultFormats), col+1):
wks.write_number(row, col, result, outFormat)
for col, paramValue in enumerate(params, col+1):
wks.write_number(row, col, paramValue)
workbook.close()
def get_query_result_excel(self, sql, excelFile, header='True'):
'''
Build an excel file from result set of the sql command \n
Example: get query result excel select * from customer test.xlsx
'''
cur = self._dbconnection.cursor()
cur.execute(sql + ';')
rows = cur.fetchall()
print(cur.description)
workbook = Workbook(excelFile)
sheet = workbook.add_worksheet()
format = workbook.add_format({'bold': True})
if header.lower() == 'true':
for i, val in enumerate([column[0] for column in cur.description]): sheet.write(0, i, self._f(val), format) #Write header column
for r, row in enumerate(rows):
for c, s in enumerate(row): sheet.write(r+1, c, self._f(s)) # Write table data
workbook.close()
def xlsx_generate(file_name, sheets):
log.info(' -> %s', file_name)
with open(file_name, 'w') as fh:
workbook = Workbook(fh)
header_format = workbook.add_format({
'bold': True,
'border': 1
})
for title, rows in sheets:
if not len(rows):
continue
headers = get_headers(rows)
if not len(headers):
continue
worksheet = workbook.add_worksheet(title)
worksheet.strings_to_numbers = False
worksheet.strings_to_urls = False
worksheet.strings_to_formulas = False
row_idx = 0
headers = None
for row in rows:
if headers is None:
headers = row.keys()
for c, cell in enumerate(headers):
worksheet.write(row_idx, c, cell, header_format)
row_idx += 1
col_idx = 0
for cell in headers:
worksheet.write(row_idx, col_idx, row.get(cell))
col_idx += 1
row_idx += 1
worksheet.freeze_panes(1, 0)
workbook.close()
def generate_change_template(request):
stream = BytesIO()
book = Workbook(stream)
bold = book.add_format({'bold': True})
# create sheet for user input
sheet = book.add_worksheet('Updates')
# set headers
sheet.write('A1', 'category (like destID)', bold)
sheet.write('B1', 'title (like input cat)', bold)
sheet.write('C1', 'url (like output cat)', bold)
rows = 400
# no idead
sheet.data_validation(1, 1, rows, 1, {
'validate': 'list',
'source': 'Valid_Categories'
})
# set width
sheet.set_column('A:A', 25)
sheet.set_column('B:B', 25)
sheet.set_column('C:C', 25)
# Create sheet containing data for user validations
# sheet = book.add_worksheet('Validations')
# sheet.write('A1', 'Categories', bold)
categories = Category.objects.all()
for i, cat in enumerate(categories):
sheet.write(i + 1, 0, cat.slug)
# book.define_name('Valid_Categories', '=Validations!$A$2:$A${}'.format(1 + categories.count()))
# sheet.set_column('A:A', 10)
# sheet.set_column('B:B', 2)
book.close()
stream.seek(0)
response = HttpResponse(stream,
content_type='application/vnd.openxmlformats-officedocument.spreadsheetml.sheet')
response['Content-Disposition'] = 'attachement; filename="page_add_template.xlsx"'
return response
def make_xlsx_response(sheets, basename):
output = StringIO()
workbook = Workbook(output)
header_format = workbook.add_format({
'bold': True,
'border': 1
})
for title, rows in sheets.items():
if not len(rows):
continue
worksheet = workbook.add_worksheet(title)
row_idx = 0
headers = None
for row in rows:
if headers is None:
headers = row.keys()
for c, cell in enumerate(headers):
worksheet.write(row_idx, c, cell, header_format)
row_idx += 1
col_idx = 0
for cell in headers:
worksheet.write(row_idx, col_idx, row.get(cell))
col_idx += 1
row_idx += 1
worksheet.freeze_panes(1, 0)
workbook.close()
output.seek(0)
fn = basename + '.xlsx'
headers = {'Content-Disposition': 'attachment; filename=%s' % fn}
return Response(output, mimetype=XLSX_MIME, headers=headers)
def testYieldCurveFwdsGeneration():
# ==========================================================================
# A_Auto: USD_IRS_LIBOR_3M
# Test based on Auto Zero rates
# zeroRates=[0.00233531255143132,
# 0.00233531255143297,
# 0.00233531255143352,
# 0.00233531255143333,
# 0.00297455023730465,
# 0.00319723236979883,
# 0.00330765830399476,
# 0.00691586090809845,
# 0.01085698247648050,
# 0.01394073189902450,
# 0.01633917521905550,
# 0.02000659612723410,
# 0.02346660204636610,
# 0.02685361531988290,
# 0.02854781579990930,
# 0.02975553109452430]
# Test based on Manual Zero rates
zeroRates = [
0.00236843676021893,
0.00236821394576394,
0.00236797582662841,
0.00236773777132554,
0.00290042675432344,
0.00308558288679761,
0.00330592988104989,
0.00691281016439806,
0.01085462048320750,
0.01393891008113780,
0.01633775879077160,
0.02000467532244330,
0.02346505198474200,
0.02685243199737300,
0.02854687951971270,
0.02975479160795850
]
yieldCurve = FYC.yieldCurveFactory(times=ZERO_TIMES,
zeros=zeroRates,
type='linearFwd')
runTime = dt.strptime('20141128.230000', dtFmtLog)
runDate = runTime.date() # same as outDates
# make sure the first out date is the end of the following month
nextDate = Cal.NextEndOfMonth(runDate)
startDate = nextDate if nextDate.month == runDate.month else runDate
dfltFwdDates = SortedList(unfoldGen(func = Cal.NextEndOfMonth,
initArg = startDate,
numSteps = NUMBER_FWD_MONTHS))
cal = Cal()
fwdDates = [[cal.AddSplitTenor(baseDate = baseDate,
tenorSize = x[0],
tenorUnit = x[1])
for x in [[0,'D']] + TENORS]
for baseDate in dfltFwdDates]
fwdTimes = [cal.DateTimesToFloats(dateTimes = fwdDateList,
baseDate = runDate)
for fwdDateList in fwdDates]
outFwds = np.empty((len(fwdTimes[0])-1,NUMBER_FWD_MONTHS))
for idx, fwdTimesAtDate in enumerate(fwdTimes):
outFwds[:,idx] \
= yieldCurve.getFwds(
times = fwdTimesAtDate[1:],
baseTime = fwdTimesAtDate[0])
rateTable = np.column_stack((zeroRates, outFwds))
# outFwds = np.maximum(0., outFwds)
workbook = Workbook('tests/test_YieldCurve.xlsx',{'constant_memory': True})
headerFormat = workbook.add_format({'bold' : True,
'font_name': 'Arial',
'font_size': 8})
rateFormat = workbook.add_format({'num_format': '0.000%',
'font_name' : 'Arial',
'font_size' : 8,
'border' : 1})
stringFormat = workbook.add_format({'font_name' : 'Arial',
'font_size' : 8,
'border' : 1})
dateFormat = workbook.add_format({'bold' : False,
'num_format': 'mm/dd/yyyy',
'font_name' : 'Arial',
'font_size' : 8,
'border' : 1})
headerDateFormat = workbook.add_format({'bold' : True,
'num_format': 'mm/dd/yyyy',
'font_name' : 'Arial',
'font_size' : 8,
'border' : 1})
# ==========================================================================
wks = workbook.add_worksheet("Forwards")
row = 1
initCol = 2
col = initCol
wks.write_string(row, col, 'Maturities/Dates', headerFormat)
col +=1
wks.write_datetime(row, col, runDate, headerDateFormat)
for outDate in fwdDates:
col +=1
wks.write_datetime(row, col, outDate[0], headerDateFormat)
# write values
for tenor, rateRow in zip(TENORS, rateTable):
row += 1
col = initCol
# print row, col, tenor
wks.write_string(row, col, "%d%s"%(tenor[0],tenor[1]), headerFormat)
col += 1
# print rateRow
for rate in rateRow:
# print row, col, rate
wks.write_number(row, col, rate,rateFormat)
col += 1
# ==========================================================================
# Format desired output
crvName = "USD_IRS_LIBOR_3M"
scenarioName = "A_Base"
# for baseDate in dfltFwdDates
# for tenor in TENORS
wks = workbook.add_worksheet("Comparision")
initRow = 0
initCol = 0
headerString = ['Curve','Scenario','Base Date', 'Tenor', 'Rate']
wks.write_row(initRow, initCol, headerString, headerFormat)
col = initCol
row = initRow + 1
for tenor, rateRow in zip(TENORS, outFwds):
for baseDate, rate in zip (dfltFwdDates, rateRow):
col=initCol
wks.write_string(row, col, crvName, stringFormat)
col +=1
wks.write_string(row, col, scenarioName, stringFormat)
col +=1
wks.write_datetime(row, col, baseDate, dateFormat)
col +=1
wks.write_string(row, col, "%d%s"%(tenor[0],tenor[1]), stringFormat)
col +=1
wks.write_number(row, col, rate, rateFormat)
row +=1
workbook.close()
class WrXlsx(object):
"""Class to store/manage Excel spreadsheet parameters."""
dflt_fmt_hdr = {'top':0, 'bottom':0, 'left':0, 'right':0, 'bold':True}
def __init__(self, fout_xlsx, nt_flds, **kws):
# KEYWORDS FOR WRITING DATA:
self.vars = WrXlsxParams(nt_flds, **kws)
# Workbook
from xlsxwriter import Workbook
self.workbook = Workbook(fout_xlsx)
self.fmt_hdr = self.workbook.add_format(self.dflt_fmt_hdr)
self.fmt_txt = {
'plain': self.workbook.add_format(self.vars.fmt_txt_arg[0]),
'plain bold': self.workbook.add_format(self.vars.fmt_txt_arg[3]),
'very light grey' : self.workbook.add_format(self.vars.fmt_txt_arg[1]),
'light grey' :self.workbook.add_format(self.vars.fmt_txt_arg[2])}
def wr_title(self, worksheet, row_idx=0):
"""Write title (optional)."""
if self.vars.title is not None:
return self.wr_row_mergeall(worksheet, self.vars.title, self.fmt_hdr, row_idx)
return row_idx
def wr_row_mergeall(self, worksheet, txtstr, fmt, row_idx):
"""Merge all columns and place text string in widened cell."""
hdridxval = len(self.vars.hdrs) - 1
worksheet.merge_range(row_idx, 0, row_idx, hdridxval, txtstr, fmt)
return row_idx + 1
def wr_hdrs(self, worksheet, row_idx):
"""Print row of column headers"""
for col_idx, hdr in enumerate(self.vars.hdrs):
worksheet.write(row_idx, col_idx, hdr, self.fmt_hdr)
row_idx += 1
return row_idx
def wr_data(self, xlsx_data, row_idx, worksheet):
"""Write data into xlsx worksheet."""
fld2fmt = self.vars.fld2fmt
# User may specify to skip rows based on values in row
prt_if = self.vars.prt_if
# User may specify a subset of columns to print or
# a column ordering different from the _fields seen in the namedtuple
prt_flds = self.vars.prt_flds
if self.vars.sort_by is not None:
xlsx_data = sorted(xlsx_data, key=self.vars.sort_by)
try:
for data_nt in xlsx_data:
fmt_txt = self._get_fmt_txt(data_nt)
if prt_if is None or prt_if(data_nt):
# Print an xlsx row by printing each column in order.
for col_idx, fld in enumerate(prt_flds):
try:
# If fld "format_txt" present, use value for formatting, but don't print.
val = getattr(data_nt, fld, "")
# Optional user-formatting of specific fields, eg, pval: "{:8.2e}"
# If field value is empty (""), don't use fld2fmt
if fld2fmt is not None and fld in fld2fmt and val != "" and val != "*":
val = fld2fmt[fld].format(val)
worksheet.write(row_idx, col_idx, val, fmt_txt)
except:
raise RuntimeError(self._get_fatal_rcv(row_idx, col_idx, fld, val))
row_idx += 1
except RuntimeError as inst:
import sys
import traceback
traceback.print_exc()
sys.stdout.write("\n **FATAL in wr_data: {MSG}\n\n".format(MSG=str(inst)))
sys.exit()
return row_idx
@staticmethod
def _get_fatal_rcv(row, col, fld, val):
"""Return an informative message with details of xlsx write attempt."""
import traceback
traceback.print_exc()
return "ROW({R}) COL({C}) FIELD({F}) VAL({V})".format(R=row, C=col, F=fld, V=val)
def add_worksheet(self):
"""Add a worksheet to the workbook."""
worksheet = self.workbook.add_worksheet()
if self.vars.fld2col_widths is not None:
self.set_xlsx_colwidths(worksheet, self.vars.fld2col_widths, self.vars.prt_flds)
return worksheet
def _get_fmt_txt(self, data_nt=None):
"""Return format for text cell."""
if data_nt is None or not self.vars.b_format_txt:
return self.fmt_txt.get('plain')
format_txt_val = getattr(data_nt, "format_txt")
if format_txt_val == 1:
return self.fmt_txt.get("very light grey")
if format_txt_val == 2:
return self.fmt_txt.get("light grey")
fmt = self.fmt_txt.get(format_txt_val)
if fmt is not None:
return fmt
return self.fmt_txt.get('plain')
def get_fmt_section(self):
"""Grey if printing header GOs and plain if not printing header GOs."""
if self.vars.b_format_txt:
return self.fmt_txt.get("light grey")
return self.fmt_txt.get("plain bold")
@staticmethod
def set_xlsx_colwidths(worksheet, fld2col_widths, fldnames):
"""Set xlsx column widths using fld2col_widths."""
for col_idx, fld in enumerate(fldnames):
col_width = fld2col_widths.get(fld, None)
if col_width is not None:
worksheet.set_column(col_idx, col_idx, col_width)
class XlsxReport():
'''
导出Excel报表，主要的用法：
data=(('data11','data12'),
('data21','data22'))
columns=[{'header':'标题1',
'width':11,},
{'header':'标题2',
'width':13,
'format':'currency'}]
with XlsxReport('xlsx_name')as rpt:
rpt.add_table('A1','sheetname',columns=columns,data=data)
'''
def __init__(self,*args,**kwargs):
self.book=Workbook(*args,**kwargs)
self.formats={}
self.add_formats(DefaultFormats)
self.sheet=None
self.sheets={}
def __enter__(self):
return self
def __exit__(self,_type,value,trace):
self.close()
def ensure_sheet(self,sheet):
self.get_sheet(sheet)
if not sheet in self.sheets:
self.sheets[sheet]=Sheet(self)
return self.sheets.get(sheet)
def get_sheet(self,sheetname):
'''获取工作表，并设为默认工作表'''
if isinstance(sheetname,Worksheet):
return sheetname
for sheet in self.book.worksheets():
if sheet.name==sheetname:break
else:
sheet=self.book.add_worksheet(sheetname)
self.sheet=sheet
return sheet
def set_row(self,row,height=None,format=None,options={}):
if format:
format=self.formats.get(format,format)
self.sheet.set_row(row,height,format,options)
@convert_column_args
def set_column(self,firstcol,lastcol,width=None,
format=None,options={}):
if format:
format=self.formats.get(format,format)
self.sheet.set_column(firstcol,lastcol,width,format,options)
@convert_range_args
def mwrite(self,first_row,first_col,last_row,last_col,\
value,format=None):
'''合并写入到默认的工作表中'''
if format:
format=self.formats.get(format,format)
self.sheet.merge_range(first_row,first_col,last_row,last_col,
value,format)
@convert_range_args
def write_formulas(self,*args,**kwargs):
pass
@convert_cell_args
def write_formula(self,row,col,formula,format=None,value=0):
if format:
format=self.formats.get(format,format)
self.sheet.write_formula(row,col,formula,format,value)
@convert_cell_args
def write(self,row,col,value,format=None):
'''单一单元格写入'''
if format:
format=self.formats.get(format,format)
self.sheet.write(row,col,value,format)
@convert_cell_args
def rwrite(self,row,col,values,format=None):
'''按行写入'''
if format:
format=self.formats.get(format,format)
self.sheet.write_row(row,col,values,format)
def close(self):
'''关闭文件'''
self.book.close()
def add_formats(self,args):
'''添加格式'''
if isinstance(args,dict):
args=args.items()
self.formats.update({name:self.book.add_format(format) for \
name,format in args})
@convert_range_args
def add_table(self,first_row,first_col,last_row,last_col,\
sheet=None,**kwargs):
'''添加图表，如sheet为空，则使用默认的工作表'''
sheet=self.get_sheet(sheet)if sheet else self.sheet
columns=kwargs.get('columns')
if columns:
new_columns=[]
for idx,column in enumerate(columns):
if 'width' in column:
sheet.set_column("{0}:{0}".format(
xl_col_to_name(idx+first_col)),\
column.get('width'))
format=column.get("format")
if format and isinstance(format,str):
new_column=column.copy()
new_column['format']=self.formats.get(format)
new_columns.append(new_column)
else:
new_columns.append(column)
kwargs['columns']=new_columns
last_col=first_col+len(columns)-1
if 'data' in kwargs:
last_row=first_row+len(kwargs['data'])
if kwargs.get('total_row',False):
last_row+=1
sheet.add_table(first_row,first_col,last_row,last_col,kwargs)
def process(self, files):
# Get the filename from the already-open file handle
filename = self.fout.name
# Close the existing file as the XlsxWriter library handles that for us
self.fout.close()
# Create the new workbook
workbook = Workbook(filename)
# Define some common styles/ formats
bold = workbook.add_format({
'bold': 1,
})
# Create the main worksheet where all the raw data is shown
main_sheet = workbook.add_worksheet('Raw Data')
# Write the header
main_sheet.write(0, 0, 'IP', bold) # The IP field can be either ip_str or ipv6 so we treat it differently
main_sheet.set_column(0, 0, 20)
row = 0
col = 1
for field in self.fields:
name = self.field_names.get(field, field.capitalize())
main_sheet.write(row, col, name, bold)
col += 1
row += 1
total = 0
ports = defaultdict(int)
for banner in iterate_files(files):
try:
# Build the list that contains all the relevant values
data = []
for field in self.fields:
value = self.banner_field(banner, field)
data.append(value)
# Write those values to the main workbook
# Starting off w/ the special "IP" property
main_sheet.write_string(row, 0, get_ip(banner))
col = 1
for value in data:
main_sheet.write(row, col, value)
col += 1
row += 1
except Exception:
pass
# Aggregate summary information
total += 1
ports[banner['port']] += 1
summary_sheet = workbook.add_worksheet('Summary')
summary_sheet.write(0, 0, 'Total', bold)
summary_sheet.write(0, 1, total)
# Ports Distribution
summary_sheet.write(0, 3, 'Ports Distribution', bold)
row = 1
col = 3
for key, value in sorted(ports.items(), reverse=True, key=lambda kv: (kv[1], kv[0])):
summary_sheet.write(row, col, key)
summary_sheet.write(row, col + 1, value)
row += 1
def csv_to_xlsx():
""""""
# xlsxwriter format info: http://xlsxwriter.readthedocs.io/format.html
aqua = '#31849B'
gold = '#DDD9C3'
gray = '#D8D8D8'
light_gold = '#EEECE1'
light_gray = '#F2F2F2'
white = 'white'
money_format = '$#,##0'
int_format = '#,##0'
float_format = '#,##0.00'
wb = Workbook(EXCEL_PATH, {'strings_to_numbers': True})
header_format = wb.add_format({
'align': 'center',
'bg_color': aqua,
'bold': True,
'font_color': white
})
footer_format = wb.add_format({
'bg_color': aqua,
'bold': True,
'font_color': white,
'underline': 1
})
for x, tbl in enumerate(STATS_TABLES):
csv_path = join(CSV_DIR, '{}.csv'.format(tbl))
ws = wb.add_worksheet(SHEET_BASENAME.format(SHEET_TYPE[x]))
ws.freeze_panes(1, 0)
with open(csv_path) as stats_csv:
stats_reader = csv.reader(stats_csv)
rows = len(list(stats_reader))
stats_csv.seek(0)
header = next(stats_reader)
cols = len(header)
for j, item in enumerate(header):
ws.write(0, j, HEADER_MAP[item], header_format)
for i, row in enumerate(stats_reader, 1):
# set row colors
if i == 1:
bg_color = gold
elif i <= 4:
bg_color = light_gold
elif i % 4 == 1:
bg_color = gray
else:
bg_color = light_gray
for j, item in enumerate(row):
cell_format = wb.add_format({
'bg_color': bg_color,
'border': 1, # solid, width 1
'border_color': gray
})
# format numbers/set alignment
if j == 2:
cell_format.set_align('right')
elif j in (3, 5):
cell_format.set_num_format(int_format)
elif j in (4, 7):
cell_format.set_num_format(money_format)
elif j in (6, 8):
cell_format.set_num_format(float_format)
ws.write(i, j, item, cell_format)
ws.merge_range(rows, 0, rows, cols - 1, '')
ws.write_url(rows, 0, METADATA_URL, footer_format, FOOTNOTE)
wb.close()
