def data_amend_report(workbook, gl, ibm, nc_sp, pvs_sp, fm_sp):
# Sheet 1
sheet = workbook.get_sheet(0)
# Download hyperlink:
sheet.write(1, 0, Formula('HYPERLINK("{}")'.format(settings.IBM_DATA_AMEND_URI)))
# Padded zeroes number format
pad2, pad3, pad4 = XFStyle(), XFStyle(), XFStyle()
pad2.num_format_str = '00'
pad3.num_format_str = '000'
pad4.num_format_str = '0000'
current_row = 3
code_id = ''
for row, data in enumerate(gl, current_row):
# Only insert GLPivDownload objects with a matching IBMData object.
if ibm.filter(ibmIdentifier=data.codeID).exists():
# We have to aggregate all the GLPivotDownload objects with
# matching codeID values, and insert one row with total
# ytdActual and fyBudget values.
if code_id != data.codeID: # Reached the next codeID value.
current_row += 1 # Advance one row.
code_id = data.codeID
i = ibm.get(ibmIdentifier=data.codeID)
sheet.write(current_row, 0, data.codeID)
sheet.write(current_row, 1, int(data.costCentre), pad3)
sheet.write(current_row, 2, data.account, pad2)
sheet.write(current_row, 3, data.service, pad2)
sheet.write(current_row, 4, data.activity, pad3)
try:
sheet.write(current_row, 5, int(data.project), pad4)
except ValueError:
sheet.write(current_row, 5, data.project, pad4)
try:
sheet.write(current_row, 6, int(data.job), pad3)
except ValueError:
sheet.write(current_row, 6, data.job, pad3)
sheet.write(current_row, 7, data.jobName)
sheet.write(current_row, 8, data.activityName)
sheet.write(current_row, 9, data.projNameNo)
sheet.write(current_row, 10, i.budgetArea)
sheet.write(current_row, 11, i.projectSponsor)
sheet.write(current_row, 14, i.regionalSpecificInfo)
sheet.write(current_row, 15, i.servicePriorityID)
sheet.write(current_row, 18, i.annualWPInfo)
sheet.write(current_row, 19, data.mPRACategory)
ytd = gl.filter(codeID=code_id).aggregate(Sum('ytdActual'))
fy = gl.filter(codeID=code_id).aggregate(Sum('fybudget'))
sheet.write(current_row, 20, ytd['ytdActual__sum'])
sheet.write(current_row, 21, fy['fybudget__sum'])
# Insert the footer row formulae and '#END OF INPUT'
sheet.write(current_row+2, 0, '#END OF INPUT')
sheet.write(current_row+2, 20, Formula('SUM({}:{})'.format(cellname(4, 20), cellname(current_row, 20))))
sheet.write(current_row+2, 21, Formula('SUM({}:{})'.format(cellname(4, 21), cellname(current_row, 21))))
# Sheet 2 - Service priority checkboxes.
sheet = workbook.get_sheet(1)
write_service_priorities(sheet, nc_sp, pvs_sp, fm_sp)
def RUNN():
# ghi file excel
fnt = Font()
fnt.name = "Times New Roman"
fnt.height = 280
borders = Borders()
borders.left = Borders.THIN
borders.right = Borders.THIN
borders.top = Borders.THIN
borders.bottom = Borders.THIN
style = XFStyle()
style.font = fnt
style.borders = borders
wb = Workbook()
sheet1 = wb.add_sheet("Điểm")
col = 0
row = 0
sheet1.write(row, col, "STT", style)
sheet1.write(row, col + 1, "TÊN BÀI LÀM", style)
sheet1.write(row, col + 2, "ĐIỂM", style)
row += 1
diem = []
st = 1
for i in t:
if i == name_form: continue
else:
try:
t1 = docx.Document(i)
sheet1.write(row, col, st, style)
sheet1.write(row, col + 1, i[:-5], style)
sheet1.write(row, col + 2, round(10 * so_sanh(t1), 2), style)
row += 1
st += 1
except Exception: pass
wb.save("KetQua.xls")
def css2excel(css):
#custom_css = 'font: name "%s", %s on'%(current_value['font-family'].split(",")[0], current_value['font-weight'].split(",")[0])
#export_sheet.write(row_idx, col_idx, current_value['data'], xlwt.easyxf('font: italic on; pattern: pattern solid, fore-colour grey25'))
xf_list = []
fnt = Font()
borders = Borders()
pattern = Pattern()
align = Alignment()
process_css = {
'font-family' : [fnt, "name" , lambda x : x.split(",")[0]],
'color' : [fnt, "colour_index", lambda x : excel_color_map.get(x,0)+8],
'font-weight' : [fnt, "bold", lambda x : x.upper() == 'BOLD'],
#'font-weight' : [fnt, "bold", lambda x : x == '700'],
'text-align' : [align, "horz", lambda x : {'left':align.HORZ_LEFT, 'right':align.HORZ_RIGHT, 'center':align.HORZ_CENTER, 'justified': align.HORZ_JUSTIFIED}[x]],
'background-color' : [pattern,"pattern_fore_colour", lambda x: excel_color_map.get(x,16)+8],
}
#TODO process_css -> css
for i in process_css.keys():
#print process_css[i][0] ,".",process_css[i][1], " => " , css[i] ," | ", process_css[i][2](css[i])
setattr(process_css[i][0], process_css[i][1], process_css[i][2](css[i]))
style = XFStyle()
style.font = fnt
borders.left = Borders.THIN
borders.right = Borders.THIN
borders.top = Borders.THIN
borders.bottom = Borders.THIN
style.borders = borders
style.pattern = pattern
style.pattern.pattern = 1
style.alignment = align
return style
def setStyle(n):
pattern = Pattern()
pattern.pattern = Pattern.SOLID_PATTERN
pattern.pattern_fore_colour = n
style = XFStyle()
style.pattern = pattern
return style
def writeErrorRec(record):
"""
Exception report spreadsheet output format same as input format and
assumes first column is of type Date - adds an extra, last column
for error message.
"""
dateFmt = XFStyle()
dateFmt.num_format_str='MM-DD-YYYY'
defaultFmt = XFStyle()
global erroutRow
for colnum in range(0, len(headers)):
erroutSheet.write(erroutRow, colnum, record[headers[colnum][0]],
tern(headers[colnum][0]==xlrd.XL_CELL_DATE, dateFmt, defaultFmt))
# add extra column for error message
erroutSheet.row(erroutRow).write(len(headers), record['Error'])
erroutRow += 1
# flush every 200 rows...
#if ((erroutRow % 200) + 1 == 200):
# since, xlwt uses aggressive caching, we must
# write each row manually, or the data may be lost upon exception
erroutSheet.flush_row_data()
errorsWorkbook.save('errors.xls')
def getDefualtStyle(): fnt = Font() fnt.name = 'Arial' borders = Borders() borders.left = Borders.THIN borders.right = Borders.THIN borders.top = Borders.THIN borders.bottom = Borders.THIN #pattern = Pattern() #pattern.pattern = Style.pattern_map['solid'] ###pattern.pattern_back_colour = 0xBFBFBF #pattern.pattern_fore_colour = 0x37 alignment = Alignment() #alignment.horizontal = Alignment.HORZ_LEFT alignment.horizontal = Alignment.HORZ_RIGHT style = XFStyle() #~ style.num_format_str='0.000%' #~ style.num_format_str='0+' #~ style.font = fnt style.align = alignment #style.borders = borders #~ style.pattern = pattern return style
def get_style( font_specs=dict(),
border_specs = dict(),
pattern = None,
num_format_str = None, ):
style_key = ( freeze(font_specs),
freeze(border_specs),
pattern,
num_format_str )
try:
return styles[style_key]
except KeyError:
style = XFStyle()
style.font = Font()
for key, value in font_specs.items():
setattr( style.font, key, value )
style.borders = Borders()
for key, value in border_specs.items():
setattr( style.borders, key, value )
if pattern:
style.pattern = pattern
if num_format_str:
style.num_format_str = num_format_str
styles[ style_key ] = style
return style
def export_to_excel(self, **kwargs):
work_book = Workbook(encoding="ascii")
center_alignment = Alignment()
center_alignment.horz = Alignment.HORZ_RIGHT
center_alignment.vert = Alignment.VERT_CENTER
border = Borders()
border.top = Borders.THIN
border.left = Borders.THIN
border.bottom = Borders.THIN
border.right = Borders.THIN
head_style = XFStyle()
head_pattern = Pattern()
head_pattern.pattern = Pattern.SOLID_PATTERN
head_pattern.pattern_fore_colour = Style.colour_map['gray25']
head_style.pattern = head_pattern
head_font = Font()
head_font.bold = True
head_style.font = head_font
head_style.alignment = center_alignment
head_style.borders = border
data_style = XFStyle()
data_style.alignment = center_alignment
data_style.borders = border
work_sheet = work_book.add_sheet("Sheet")
limit = 10000
count = self.get_data_scale(**kwargs)['count']
times = (count + limit - 1) // limit
add_header = False
for i in range(1):
kwargs["startIndex"] = i * limit + 1
kwargs['endIndex'] = (i + 1) * limit + 1
records = self.get_data(**kwargs)
column_names = []
y = 0
for data in records:
if not add_header:
x = 0
for column_name in data.keys():
work_sheet.write(y, x, column_name, head_style)
column_names.append(column_name)
x += 1
add_header = True
y += 1
x = 0
for column_name in column_names:
work_sheet.write(y, x, data[column_name], data_style)
x += 1
y += 1
x = 0
work_book.save(BASE_WIN_OUTPUT_PATH + "result.xls")
def defineFontStyle(argFontName,argIsBold):
style = XFStyle()
font = Font()
font.name = argFontName
font.bold = argIsBold
style.font = font
return style
def dictToXls(data):
from xlwt import Workbook,XFStyle
from cStringIO import StringIO
date_format = XFStyle()
date_format.num_format_str = 'dd/mm/YYYY'
stream = StringIO()
wb = Workbook(encoding='utf8')
from datetime import datetime,date
for sheetname in data['sheets']:
sheet = data['data'][sheetname]
ws = wb.add_sheet(sheetname)
for j,field in enumerate(sheet['fields']):
fieldtxt = field[1] if isinstance(field,tuple) else field
ws.row(0).write(j,fieldtxt)
for i,row in enumerate(sheet['data']):
for j,field in enumerate(sheet['fields']):
fieldtxt = field[0] if isinstance(field,tuple) else field
f = row.get(fieldtxt,None)
if isinstance(f,date) or isinstance(f,datetime):
ws.row(i+1).write(j,f,date_format)
else:
ws.row(i+1).write(j,f)
wb.save(stream)
return stream.getvalue()
def getDefualtStyle(): fnt = Font() fnt.name = 'Arial' borders = Borders() borders.left = Borders.THIN borders.right = Borders.THIN borders.top = Borders.THIN borders.bottom = Borders.THIN #pattern = Pattern() #pattern.pattern = Style.pattern_map['solid'] ###pattern.pattern_back_colour = 0xBFBFBF #pattern.pattern_fore_colour = 0x37 alignment = Alignment() #alignment.horizontal = Alignment.HORZ_LEFT alignment.horizontal = Alignment.HORZ_RIGHT style = XFStyle() #~ style.num_format_str='0.000%' #~ style.num_format_str='0+' #~ style.font = fnt style.align = alignment style.borders = borders #~ style.pattern = pattern return style
def writeErrorRec(record):
"""
Exception report spreadsheet output format same as input format and
assumes first column is of type Date - adds an extra, last column
for error message.
"""
dateFmt = XFStyle()
dateFmt.num_format_str = 'MM-DD-YYYY'
defaultFmt = XFStyle()
global erroutRow
for colnum in range(0, len(headers)):
erroutSheet.write(
erroutRow, colnum, record[headers[colnum][0]],
tern(headers[colnum][0] == xlrd.XL_CELL_DATE, dateFmt, defaultFmt))
# add extra column for error message
erroutSheet.row(erroutRow).write(len(headers), record['Error'])
erroutRow += 1
# flush every 200 rows...
#if ((erroutRow % 200) + 1 == 200):
# since, xlwt uses aggressive caching, we must
# write each row manually, or the data may be lost upon exception
erroutSheet.flush_row_data()
errorsWorkbook.save('errors.xls')
def write_result(self, row, actualresult, result,
casefile): # 文件不用写全路径，只写文件名只可
self.lock.acquire()
case_path = os.path.join(CASEPATH, casefile)
print(case_path)
book = xlrd.open_workbook(case_path) # 创建一个excel操作对象
book2 = copy(
book
) #复制book对象 #管道作用:利用xlutils.copy函数，将xlrd.Book转为xlwt.Workbook，再用xlwt模块进行存储
sheet = book2.get_sheet(0)
# 创建一个sheet操作实例,读取的是第一个excel ,#通过get_sheet()获取的sheet有write()方法
# 红色
# styleBlueBkg = xlwt.easyxf('pattern: pattern solid, fore_colour red;')
style = XFStyle() # 格式信息
font = xlwt.Font() # 字体基本设置
# 红色
font.colour_index = 0xff
style.font = font
sheet.write(row, 11, actualresult)
# 回写时，如果是失败，则颜色标成红色
# if result=='0':
# # sheet.write(row, 12, result,styleBlueBkg)
# sheet.write(row, 12, result,style)
# else:
# sheet.write(row, 12, result)
sheet.write(row, 12, result)
# sheet.write(row, 13, user)
book2.save(case_path)
print('结果写入保存成功！')
self.lock.release()
def set_style(self, isblod):
style = XFStyle()
fnt = Font() # 创建一个文本格式，包括字体、字号和颜色样式特性
fnt.name = u'微软雅黑' # 设置其字体为微软雅黑
fnt.bold = isblod
style.font = fnt
return style
def exportToExcel(self,objectProject):
book = Workbook();
sheet1 = book.add_sheet('Sheet 1')
if( objectProject):
i=0;
row1 = sheet1.row(i) ;
row1.write(0, ('ประเภท').decode('UTF8') );
row1.write(1, ('ชื่อโครงการ').decode('UTF8'));
row1.write(2, ('รายละเอืยด').decode('UTF8') );
row1.write(3, ('งบประมาณรวม').decode('UTF8') );
row1.write(4, ('งบประมาณ').decode('UTF8') );
row1.write(5, ('เงินบำรุง').decode('UTF8') );
row1.write(6, ('งบประมาณอื่น').decode('UTF8') );
row1.write(7, ('งบประมาณอื่นจาก').decode('UTF8') );
row1.write(8, ('ผู้รับผิดชอบ').decode('UTF8') );
row1.write(9, ('กลุ่ม').decode('UTF8') );
row1.write(10, ('หน่วย/งาน').decode('UTF8') );
i=i+1;
style = XFStyle();
style.num_format_str = '#,##0.00';
for value in objectProject:
row1 = sheet1.row(i) ;
row1.write(0, value.get('project_type').decode('UTF8') );
row1.write(1, value.get('project_name').decode('UTF8') );
row1.write(2, value.get('detail').decode('UTF8') );
row1.write(3, value.get('allBudget') ,style );
row1.write(4, value.get('project_budget' ) ,style );
row1.write(5, value.get('maintenance_funds_budget'),style );
row1.write(6, value.get('budget_other') ,style );
if(value.get('budget_other_from')):
row1.write(7, value.get('budget_other_from').decode('UTF8') );
if(value.get('user_name')):
row1.write(8, value.get('user_name').decode('UTF8') );
row1.write(9, value.get('division').decode('UTF8') );
row1.write(10, value.get('section').decode('UTF8') );
i=i+1;
dirTempFile = gettempdir() + _os.sep + str('simple.xls');
book.save(dirTempFile);
#book.save(TemporaryFile());
return dirTempFile;
def Style2():
style1 = XFStyle()
alignment = xlwt.Alignment()
alignment.horz = xlwt.Alignment.HORZ_CENTER
alignment.vert = xlwt.Alignment.VERT_CENTER
style1.alignment = alignment # 给样式添加文字居中属性
style1.font.height = 330 # 设置字体大小
return style1
def get_report_info_perid(
self,
cmd,
diff_time,
thre_time,
max_number,
):
# 发送要执行的命令
pre_time_stamp = [0] * 4
self._channel.send(cmd + '\r')
# 回显很长的命令可能执行较久，通过循环分批次取回回显
index = [0] * 4
line_counter = 0
line_feed_byte = '\n'.encode(self.encoding)
while True:
buffer = self._channel.recv(65535)
if len(buffer) == 0:
logger.info('end______________')
break
lines = buffer.split(line_feed_byte)
for current_line in lines:
style = None
line = current_line.decode(self.encoding)
# logger.debug('shell显示：%s' % line)
col = self.check_type(line)
if not line.startswith(self.rq) or col == 10:
line_counter += 1
continue
time_stamp = int(
time.mktime(
time.strptime(' '.join([line[:8], line[9:17]]),
"%Y%m%d %H:%M:%S")))
time_stamp_dec = line[18:21] # 精确到毫秒
time_stamp = time_stamp * 1000 + int(time_stamp_dec)
logger.info('%s:%s' % (senior_name[col], time_stamp))
if pre_time_stamp[col] == 0:
pre_time_stamp[col] = time_stamp
else:
if abs((time_stamp - pre_time_stamp[col]) -
diff_time[col]) > thre_time[col]:
logger.error('两帧数据间隔为{}ms,时间戳分别为:({},{}),行号：{}'.format(
time_stamp - pre_time_stamp[col], time_stamp,
pre_time_stamp[col], index[col]))
style = XFStyle()
fnt = Font()
fnt.name = u'微软雅黑' # 设置其字体为微软雅黑
fnt.colour_index = 2 # 设置其字体颜色
fnt.bold = True
style.font = fnt
self.write_xl(index[col] + 1, col * 2, time_stamp)
self.write_xl(index[col] + 1,
col * 2 + 1,
time_stamp - pre_time_stamp[col],
style=style)
index[col] += 1
pre_time_stamp[col] = time_stamp
line_counter += 1
def __init__(self, *args, **kwargs):
super(ExcelEmitter, self).__init__(*args, **kwargs)
self.mdy_style = XFStyle()
self.mdy_style.num_format_str = 'MM/DD/YYYY'
self.mdyhm_style = XFStyle()
self.mdyhm_style.num_format_str = 'MM/DD/YYYY h:mm'
def style(fontsize=10, bold=False, italic=False, align=None):
""" XFStyle builder helper """
style = XFStyle()
style.font.height = twips(fontsize)
style.font.bold = bold
style.font.italic = italic
if align is not None:
style.alignment = align
return style
def export_to_xls(self):
# TODO: take care of all the special cases
wb = Workbook()
ws = wb.add_sheet("Sheet 1")
objects = self.ordered_query(request)
form = self.edit_form_class()
DATE_STYLE = XFStyle()
DATE_STYLE.num_format_str = "DD/MM/YYYY"
col_names = ['id']
for field in form:
if isinstance(field, ModelFieldList):
continue
if hasattr(self.managed_class, field.name):
col_names.append(field.name)
for c, col_name in enumerate(col_names):
ws.write(0, c, col_name)
for r, obj in enumerate(objects):
for c, col_name in enumerate(col_names):
style = None
value = obj.display_value(col_name)
if isinstance(value, Entity):
value = value.name
elif isinstance(value, list):
if all(isinstance(x, basestring) for x in value):
value = "; ".join(value)
elif all(isinstance(x, Entity) for x in value):
value = "; ".join([x.name for x in value])
else:
raise Exception("I don't know how to export column {}".format(col_name))
elif isinstance(value, date):
style = DATE_STYLE
if style:
ws.write(r + 1, c, value, style)
else:
ws.write(r + 1, c, value)
fd = StringIO.StringIO()
wb.save(fd)
debug = request.args.get('debug_sql')
if debug:
# useful only in DEBUG mode, to get the debug toolbar in browser
return '<html><body>Exported</body></html>'
response = make_response(fd.getvalue())
response.headers['content-type'] = 'application/ms-excel'
filename = "%s-%s.xls" % (self.managed_class.__name__,
strftime("%d:%m:%Y-%H:%M:%S", gmtime()))
response.headers['content-disposition'] = 'attachment;filename="%s"' % filename
return response
def export_vehicles(request,lenguage,vehicle_list):
email_template = '/email/0/'
error_list = 'Empty List'
if int(lenguage) == 1:
email_template = '/email/1/'
error_list = 'Lista vacía'
if not Validator.validate_view(request):
return redirect(email_template)
if len(vehicle_list) > 0:
user_id = request.session['id']
response = HttpResponse(content_type='application/ms-excel')
response['Content-Disposition'] = 'attachment; filename="vehicles.xls"'
if int(lenguage) == 1:
response['Content-Disposition'] = 'attachment; filename="vehiculos.xls"'
wb = xlwt.Workbook(encoding='utf-8')
ws = wb.add_sheet('Trailers')
row_num = 0
rows_vehicle = Vehicle.objects.filter(economic_no__in = vehicle_list).filter(user_id=user_id).values_list('economic_no','vin','plate_no',
'country','state','year',
'model','brand','type','status',
)
date_format = XFStyle()
date_format.num_format_str = 'M/D/YY'
for row_vehicle in rows_vehicle:
row_num += 1
columns = [
'Economic No', 'Vin','Plate No',
'Country', 'State', 'Year',
'Model', 'Brand','type','status'
]
if int(lenguage) == 1:
columns = [
'No. Económico', 'Vin', 'No. de Placas',
'País', 'Estado', 'Año',
'Modelo', 'Marca', 'Tipo','Condición'
]
row_num = XlsGenerator.create_headers(columns, ws, row_num)
row_num = XlsGenerator.create_content(ws,row_num,row_vehicle,xlwt.XFStyle())
wb.save(response)
return response
return render(request,'hound-eng/error.html',{'error':error_list})
def write_row(self, array):
"""
write a row into the file
"""
for i in range(0, len(array)):
value = array[i]
style = None
tmp_array = []
if isinstance(value, datetime.datetime):
tmp_array = [
value.year, value.month, value.day, value.hour,
value.minute, value.second
]
value = xlrd.xldate.xldate_from_datetime_tuple(tmp_array, 0)
style = XFStyle()
style.num_format_str = DEFAULT_DATETIME_FORMAT
elif isinstance(value, datetime.date):
tmp_array = [value.year, value.month, value.day]
value = xlrd.xldate.xldate_from_date_tuple(tmp_array, 0)
style = XFStyle()
style.num_format_str = DEFAULT_DATE_FORMAT
elif isinstance(value, datetime.time):
tmp_array = [value.hour, value.minute, value.second]
value = xlrd.xldate.xldate_from_time_tuple(tmp_array)
style = XFStyle()
style.num_format_str = DEFAULT_TIME_FORMAT
if style:
self.native_sheet.write(self.current_row, i, value, style)
else:
self.native_sheet.write(self.current_row, i, value)
self.current_row += 1
def output(file_or_stream, sheetname, headers, values, encoding = 'utf8', footer_text = None, footer_link = None):
import xlwt
book = xlwt.Workbook(encoding)
sh = book.add_sheet(sheetname)
datestyle = XFStyle()
datestyle.num_format_str = 'DD/MM/YYYY'
timestyle = XFStyle()
timestyle.num_format_str = 'HH:MM:SS'
header_font = Font()
header_font.bold = True
al = Alignment()
al.horz = Alignment.HORZ_CENTER
header_style = XFStyle()
header_style.font = header_font
header_style.alignment = al
for i,header in enumerate(headers):
sh.write(0, i, header, header_style)
sh.set_panes_frozen(True) # frozen headings instead of split panes
sh.set_horz_split_pos(1) # in general, freeze after last heading row
sh.set_remove_splits(True) # if user does unfreeze, don't leave a split there
for j, row in enumerate(values):
for i, value in enumerate(row):
if value.__class__ == date:
sh.write(j+1, i, value, datestyle)
elif value.__class__ == time:
sh.write(j+1, i, value, timestyle)
else:
sh.write(j+1, i, value)
if footer_link and footer_text:
link_font = Font()
link_font.name = 'Verdana'
link_font.colour_index = 4
link_font.height = 20*8
al = Alignment()
al.horz = Alignment.HORZ_CENTER
al.vert = Alignment.VERT_BOTTOM
link_style = XFStyle()
link_style.font = link_font
link_style.alignment = al
row = len(values) + 1
sh.write_merge(row, row, 0, len(headers)-1, Formula('HYPERLINK("' + footer_link + '";"' + footer_text + '")'), link_style)
book.save(file_or_stream)
def write_to_book(sheet: Worksheet, row: int, cur_inf: dict):
"""
Запись обработанной информации в Worksheet.
"""
row = sheet.row(row)
style = XFStyle()
for index, value in enumerate(cur_inf.values()):
if (row != 0) and (index != 0 or 3):
style.num_format_str = '"₽"#,##0.0000;"₽"#,##0.0000'
sheet.col(index).width = 4000
row.write(index, value, style=style)
def excelWriter(self, filename):
workbook1 = xlwt.Workbook()
sheet1 = workbook1.add_sheet('表01')
sheet1.write(0, 0, 'Unformated value')
sheet1.write(0, 1, '大家好')
# 设置字体样式
font1 = Font()
font1.name = '华文琥珀'
style = XFStyle()
style.font = font1
sheet1.write(1, 0, 'Formated value', style)
workbook1.save(filename)
def Style1():
style = XFStyle()
fnt = Font()
fnt.name = u'微软雅黑'
fnt.bold = True
style.font = fnt
alignment = xlwt.Alignment()
alignment.horz = xlwt.Alignment.HORZ_CENTER
alignment.vert = xlwt.Alignment.VERT_CENTER
style.alignment = alignment # 给样式添加文字居中属性
style.font.height = 430 # 设置字体大小
return style
def exportToExcel(self, objectBooks):
book = Workbook()
sheet1 = book.add_sheet('Sheet 1')
if (objectBooks):
i = 0
row1 = sheet1.row(i)
row1.write(0, ('เลขทะเบียน').decode('UTF8'))
row1.write(1, ('ที่').decode('UTF8'))
row1.write(2, ('ลงวันที่').decode('UTF8'))
row1.write(3, ('จาก').decode('UTF8'))
row1.write(4, ('ถึง').decode('UTF8'))
row1.write(5, ('เรื่อง').decode('UTF8'))
row1.write(6, ('การปฏิบัติ').decode('UTF8'))
row1.write(7, ('หมายเหตุ').decode('UTF8'))
i = i + 1
style = XFStyle()
style.num_format_str = 'D-MMM-YY'
for value in objectBooks:
row1 = sheet1.row(i)
row1.write(0,
value.get('book_number').decode('UTF8'))
row1.write(1,
value.get('book_at').decode('UTF8'))
row1.write(2, value.get('book_recive'), style)
row1.write(3,
value.get('book_from').decode('UTF8'))
row1.write(4,
value.get('book_to').decode('UTF8'))
row1.write(5,
value.get('book_detail').decode('UTF8'))
row1.write(6,
value.get('book_operations').decode('UTF8'))
row1.write(7,
value.get('book_remark').decode('UTF8'))
#row1.write(7, value.get('book_type_name').decode('UTF8') );
i = i + 1
dirTempFile = gettempdir() + _os.sep + str('books.xls')
book.save(dirTempFile)
#book.save(TemporaryFile());
return dirTempFile
def writLabel(self):
"""向excel写入内容，内容自定义"""
style = XFStyle() # 初始化样式
font = Font() # 为样式创建字体
font.name = 'Times New Roman'
# font.bold = True # 黑体
# font.underline = True # 下划线
# font.italic = True # 斜体字
style.font = font # 设定样式
for i in range(10):
for j in range(5):
self.excel_w_sheet.write(i,j, label = 'admin%d%d'%(i,j))# 参数对应 行, 列, 值
self.excel_w.save('Excelw.xls')
def write_file(headers, data):
wb = Workbook()
font = Font()
style = XFStyle()
page = wb.add_sheet('All Data')
for i in range(len(data)):
font.bold = True
style.font = font
page.write(0, i, headers[i], style=style)
for j in range(len(data[0])):
page.write(j + 1, i, data[i][j])
wb.save(search_excel_file)
def write_file(headers, data):
wb = Workbook()
font = Font()
style = XFStyle()
page = wb.add_sheet('All Data')
for i in range(len(data)):
font.bold = True
style.font = font
page.write(0, i, headers[i], style=style)
for j in range(len(data[i])):
page.write(j + 1, i, data[i][j])
wb.save('info/misc/rating_plot.xls')
def __init__(self):
"""Initialize class"""
APIView.__init__(self)
self._file_title = ''
self._topic = ''
self._times = []
self.__workbook = Workbook(encoding='utf-8')
self._response = HttpResponse(content_type='application/ms-excel')
self._head_row_style = XFStyle()
self._data_row_style = XFStyle()
self.__start_row = 5
self.__start_column = 5
self.__message_row = 2
self.__message_col = 2
def exportToExcel(self,objectBooks):
book = Workbook();
sheet1 = book.add_sheet('Sheet 1')
if( objectBooks):
i=0;
row1 = sheet1.row(i) ;
row1.write(0, ('เลขทะเบียน').decode('UTF8') );
row1.write(1, ('ที่').decode('UTF8'));
row1.write(2, ('ลงวันที่').decode('UTF8') );
row1.write(3, ('จาก').decode('UTF8') );
row1.write(4, ('ถึง').decode('UTF8') );
row1.write(5, ('เรื่อง').decode('UTF8') );
row1.write(6, ('การปฏิบัติ').decode('UTF8') );
row1.write(7, ('หมายเหตุ').decode('UTF8') );
i=i+1;
style = XFStyle();
style.num_format_str = 'D-MMM-YY';
for value in objectBooks:
row1 = sheet1.row(i) ;
row1.write(0, value.get('book_number').decode('UTF8') );
row1.write(1, value.get('book_at').decode('UTF8') );
row1.write(2, value.get('book_recive') ,style );
row1.write(3, value.get('book_from').decode('UTF8') );
row1.write(4, value.get('book_to' ).decode('UTF8') );
row1.write(5, value.get('book_detail').decode('UTF8') );
row1.write(6, value.get('book_operations').decode('UTF8') );
row1.write(7, value.get('book_remark').decode('UTF8') );
#row1.write(7, value.get('book_type_name').decode('UTF8') );
i=i+1;
dirTempFile = gettempdir() + _os.sep + str('books.xls');
book.save(dirTempFile);
#book.save(TemporaryFile());
return dirTempFile;
def def_style():
style = XFStyle()
font = Font() # 这部分设置字体
font.name = 'Times New Roman' # 或者换成外面传进来的参数，这样可以使一个函数定义所有style
# font.bold = 'True'
# font.height = 24
# font.size = 2000
# font.colour_index = 3
style.font = font
alignment = Alignment() # 这部分设置居中格式
alignment.horz = Alignment.HORZ_CENTER # 水平居中
alignment.vert = Alignment.VERT_CENTER # 垂直居中
style.alignment = alignment
return style
def CountAv(self):
numstyle = XFStyle()
numstyle.num_format_str = "0.0000"
if self.mode == 0:
i = 0
while i <= (self.finSize - self.startSize) / 100:
formula = 'AVERAGE(C1:%s)' % (Utils.rowcol_to_cell(
i, self.repeat + 1))
self.res.write(i, 2 + self.repeat, xlwt.Formula(formula),
numstyle)
i += 1
elif self.mode == 2:
formula = 'AVERAGE(C1:%s)' % (Utils.rowcol_to_cell(
0, self.repeat + 1))
self.res.write(0, 2 + self.repeat, xlwt.Formula(formula), numstyle)
def _make_irradiation_line(self, sheet, row, irradname, cols):
dvc = self.dvc
chron = dvc.get_chronology(irradname)
_, prod = dvc.meta_repo.get_production(irradname, 'A')
sheet.write(row, 0, irradname)
for i, (label, key, fmt) in enumerate(cols[1:]):
style = XFStyle()
if key == 'duration':
v = chron.duration
elif key in INTERFERENCE_KEYS:
v = getattr(prod, key)
style.num_format_str = fmt
sheet.write(row, i + 1, v, style)
def _make_irradiation_line(self, sheet, row, irradname, cols):
dvc = self.dvc
chron = dvc.get_chronology(irradname)
_, prod = dvc.meta_repo.get_production(irradname, 'A')
sheet.write(row, 0, irradname)
for i, (label, key, fmt) in enumerate(cols[1:]):
style = XFStyle()
if key == 'duration':
v = chron.duration
elif key in INTERFERENCE_KEYS:
v = getattr(prod, key)
style.num_format_str = fmt
sheet.write(row, i + 1, v, style)
def render(self, request, context, **response_kwargs):
from xlwt import Workbook, XFStyle, easyxf
w = Workbook(encoding='utf-8')
ws = w.add_sheet('Report')
style = XFStyle()
row = 0
heading_xf = easyxf('font:height 200; font: bold on; align: wrap on, vert centre, horiz center')
ws.write(row, 0, '#', style)
for col, fieldname in enumerate(context['report'].headers, start=1):
ws.write(row, col, str(fieldname), heading_xf)
ws.col(col).width = 5000
ws.row(row).height = 500
# we have to prepare all the styles before going into the loop
# to avoid the "More than 4094 XFs (styles)" Error
styles = self._get_styles(context)
for rownum, data in enumerate(context['report']):
ws.write(rownum + 1, 0, rownum + 1)
for idx, (fieldname, rowvalue) in enumerate(data.items()):
style = styles[rowvalue.column.name]
try:
ws.write(rownum + 1, idx + 1, with_widget(rowvalue, format='xls'), style)
except Exception:
#logger.warning("TODO refine this exception: %s" % e)
ws.write(rownum + 1, idx + 1, smart_str(with_widget(rowvalue)), style)
f = StringIO.StringIO()
w.save(f)
f.seek(0)
return f.read()
def __init__(self, encoding='utf-8'):
self.nline = 0
self.wb = wb = Workbook(encoding=encoding)
self.ws = wb.add_sheet('sheet 1')
self.date_format = XFStyle()
self.date_format.num_format_str = 'dd/mm/yyyy' # TODO: convert from settings.DATE_FORMAT
self.truncate_prefix = _('(truncated cell)') # TODO: cached_gettext
def get_style():
fnt = Font()
fnt.bold = True
bor = Borders()
bor.top = 1
bor.right = 1
bor.bottom = 1
bor.left = 1
al = Alignment()
al.horz = Alignment.HORZ_CENTER
al.vert = Alignment.VERT_CENTER
style = XFStyle()
style.font = fnt
style.borders = bor
style.alignment = al
return style
def export_to_excel(self):
if not os.path.isdir('./export_excel'): # 导出结果存储目录
os.makedirs('./export_excel')
save_path = os.path.join('./export_excel', self.select_table + '.xls')
head_list = ['学号', '姓名', '是否出勤', '出勤时间']
xls = ExcelWrite.Workbook() # 创建Excel控制对象
sheet = xls.add_sheet("Sheet1") # 创建被写入的表格sheet1
style = XFStyle()
pattern = Pattern() # 创建一个模式
pattern.pattern = Pattern.SOLID_PATTERN # 设置其模式为实型
pattern.pattern_fore_colour = 0x16 # 设置其模式单元格背景色
# 设置单元格背景颜色 0 = Black, 1 = White, 2 = Red, 3 = Green, 4 = Blue, 5 = Yellow, 6 = Magenta, the list goes on...
style.pattern = pattern
for col in range(len(head_list)): # 写入首行信息，为表头，表示列名
sheet.write(0, col, head_list[col], style)
sheet.col(col).width = 4240
try:
# 连接数据库读取数据
conn, cursor = DataManageUI.connect_to_sql()
sql = 'select * from `%s`' % self.select_table
cursor.execute(sql)
row = 0
stu_data = cursor.fetchall()
for stu_info in stu_data: # 遍历数据库中每行信息，一行表示一部电影的所有信息
stu_info = list(stu_info)
if stu_info[3]:
stu_info[3] = stu_info[3].strftime('%Y/%m/%d %H:%M:%S')
row = row + 1 # 第0行为表头，不添加数据，因此从第一列开始写入
for col in range(len(stu_info)): # 对于一行信息进行遍历，分别存入每列
sheet.write(row, col, stu_info[col])
xls.save(save_path) # 写入完成，存储
cursor.close()
conn.close()
text = 'Success!'
informativeText = '<b>课程{}签到表 导出成功! 目标路径：./export_excel</b>'.format(
self.select_table)
DataRecordUI.callDialog(QMessageBox.Information, text,
informativeText, QMessageBox.Ok)
except Exception as e:
print(e)
text = 'Error!'
informativeText = '<b>导出失败!</b>'
DataRecordUI.callDialog(QMessageBox.Critical, text,
informativeText, QMessageBox.Ok)
def _getStyle(self, org_style, org_font):
from xlwt import XFStyle, Font, Borders, Pattern
# font
font = Font()
font.name = org_font.name
font.height = org_font.height
font.italic = org_font.italic
font.struck_out = org_font.struck_out
font.outline = org_font.outline
font.shadow = org_font.shadow
font.colour_index = org_font.colour_index
font.bold = org_font.bold
font._weight = org_font.weight
font.escapement = org_font.escapement_type
font.underline = org_font.underline_type
font.family = org_font.family
font.charset = org_font.character_set
# border
borders = Borders()
borders.left = Borders.THIN #org_style.xf.border.left_line_style
borders.right = Borders.THIN #org_style.xf.border.right_line_style
borders.top = Borders.THIN #org_style.xf.border.top_line_style
borders.bottom = Borders.THIN #org_style.xf.border.bottom_line_style
#borders.diag = self.NO_LINE
borders.left_colour = org_style.xf.border.left_colour_index
borders.right_colour = org_style.xf.border.right_colour_index
borders.top_colour = org_style.xf.border.top_colour_index
borders.bottom_colour = org_style.xf.border.bottom_colour_index
#borders.diag_colour = org_style.xf.border.left_colour_index
#borders.need_diag1 = self.NO_NEED_DIAG1
#borders.need_diag2 = self.NO_NEED_DIAG2
# Pattern
pattern = Pattern()
pattern.pattern = org_style.xf.background.fill_pattern
pattern.pattern_fore_colour = org_style.xf.background.pattern_colour_index
pattern.pattern_back_colour = org_style.xf.background.background_colour_index
#
style = XFStyle()
style.borders = borders
style.font = font
style.pattern = pattern
return style
def save_xls(path,data):
"""
Save formated data to xls file
"""
w = Workbook()
ws = w.add_sheet('Sheet 1')
for i in range(len(data)):
for j in range(len(data[i])):
if j==4 and i>0:
continue
ws.write(i, j, data[i][j])
#rewrite date with apropriate format
date_format = XFStyle()
date_format.num_format_str = 'mm/dd/yyyy'
for i in range(1,len(data)):
ws.write(i, 4, data[i][4], date_format)
w.save(path)
def merge_styles(row_style, col_style, default_style=easyxf('')):
"""Merges row and column style.
Method tries to get "strongest" style feauters from col (row) style
and replicate it to row (col) style.
Alas, it doesn't stable yet.
"""
if (row_style, col_style) in styles_cache:
new_style = styles_cache[row_style, col_style]
else:
new_style = XFStyle()
# Merge borders
new_style.borders.top = row_style.borders.top if row_style.borders.top > col_style.borders.top \
else col_style.borders.top
new_style.borders.left = row_style.borders.left if row_style.borders.left > col_style.borders.left \
else col_style.borders.left
new_style.borders.right = row_style.borders.right if row_style.borders.right > col_style.borders.right \
else col_style.borders.right
new_style.borders.bottom = row_style.borders.bottom \
if row_style.borders.bottom > col_style.borders.bottom else col_style.borders.bottom
# Merge pattern
if default_style.pattern.pattern == row_style.pattern.pattern:
new_style.pattern.pattern = col_style.pattern.pattern
else:
new_style.pattern.pattern = row_style.pattern.pattern
if default_style.pattern.pattern_fore_colour == row_style.pattern.pattern_fore_colour:
new_style.pattern.pattern_fore_colour = col_style.pattern.pattern_fore_colour
else:
new_style.pattern.pattern_fore_colour = row_style.pattern.pattern_fore_colour
if default_style.pattern.pattern_back_colour == row_style.pattern.pattern_back_colour:
new_style.pattern.pattern_back_colour = col_style.pattern.pattern_back_colour
else:
new_style.pattern.pattern_back_colour = row_style.pattern.pattern_back_colour
# Merge font
new_style.font = merge_fonts(row_style.font, col_style.font, default_style.font)
styles_cache[row_style, col_style] = new_style
return new_style
def excel_style():
borders_amount = Borders()
borders_amount.right = Borders.THIN
borders_amount.top = Borders.THIN
borders_amount.bottom = Borders.THIN
borders_name = Borders()
borders_name.left = Borders.THIN
borders_name.right = Borders.THIN
borders_name.top = Borders.THIN
borders_name.bottom = Borders.THIN
style_amount = XFStyle()
style_amount.borders = borders_amount
style_name = XFStyle()
style_name.borders = borders_name
return style_amount, style_name
def estiloCeldasSeparadas():
fnt = Font()
fnt.bold=True
#fnt.colour_index=2
borders = Borders()
borders.left = Borders.MEDIUM
borders.right = Borders.MEDIUM
borders.top = Borders.MEDIUM
borders.bottom = Borders.MEDIUM
pattern = Pattern()
pattern.pattern = Pattern.SOLID_PATTERN
pattern.pattern_fore_colour = 0x18#0x99
style = XFStyle()
style.num_format_str='YYYY-MM-DD'
style.font = fnt
style.borders = borders
style.pattern = pattern
return style
def render_excel(filename, col_title_list, data_row_list):
import StringIO
output = StringIO.StringIO()
export_wb = Workbook()
export_sheet = export_wb.add_sheet('Export')
col_idx = 0
for col_title in col_title_list:
export_sheet.write(0, col_idx, col_title)
col_idx += 1
row_idx = 1
for row_item_list in data_row_list:
col_idx = 0
for current_value in row_item_list:
if current_value:
current_value_is_date = False
if isinstance(current_value, datetime.datetime):
current_value = xlrd.xldate.xldate_from_datetime_tuple((current_value.year, current_value.month, \
current_value.day, current_value.hour, current_value.minute, \
current_value.second), 0)
current_value_is_date = True
elif isinstance(current_value, datetime.date):
current_value = xlrd.xldate.xldate_from_date_tuple((current_value.year, current_value.month, \
current_value.day), 0)
current_value_is_date = True
elif isinstance(current_value, datetime.time):
current_value = xlrd.xldate.xldate_from_time_tuple((current_value.hour, current_value.minute, \
current_value.second))
current_value_is_date = True
elif isinstance(current_value, models.Model):
current_value = str(current_value)
if current_value_is_date:
s = XFStyle()
s.num_format_str = 'M/D/YY'
export_sheet.write(row_idx, col_idx, current_value, s)
else:
export_sheet.write(row_idx, col_idx, current_value)
col_idx += 1
row_idx += 1
export_wb.save(output)
output.seek(0)
response = HttpResponse(output.getvalue())
response['Content-Type'] = 'application/vnd.ms-excel'
response['Content-Disposition'] = 'attachment; filename='+filename
return response
def toExcel(self, frames):
import StringIO
from xlwt import Workbook, XFStyle
# Need a file handle to save to
f = StringIO.StringIO()
frames = self.flattenFeature(frames)
if len(frames) > 0:
keys = frames[0].keys()
# Construct a workbook with one sheet
wb = Workbook()
s = wb.add_sheet("frames")
# Create the style for date/time
dateStyle = XFStyle()
dateStyle.num_format_str = "MM/DD/YYYY HH:MM:SS"
# Add the header/field labels
r = s.row(0)
for i, name in enumerate(keys):
r.write(i, name)
# Write the data
for i, frame in enumerate(frames):
print type(frame["capturetime"])
for j, name in enumerate(keys):
try:
if type(frame[name]) == datetime:
s.write(i + 1, j, frame[name], dateStyle)
else:
s.write(i + 1, j, str(frame[name]))
except KeyError:
pass
# Save the the string IO and grab the string data
wb.save(f)
output = f.getvalue()
f.close()
return output
def render_excel_to_file(filename, col_title_list, data_row_list):
output = StringIO.StringIO()
export_wb = Workbook()
export_sheet = export_wb.add_sheet('Export')
col_idx = 0
s = XFStyle()
s.num_format_str = 'M/D/YY'
for col_title in col_title_list:
export_sheet.write(0, col_idx, col_title)
col_idx += 1
row_idx = 1
for row_item_list in data_row_list:
col_idx = 0
for current_value in row_item_list:
if current_value:
current_value_is_date = False
if isinstance(current_value, datetime.datetime):
current_value = xlrd.xldate.xldate_from_datetime_tuple((current_value.year, current_value.month, \
current_value.day, current_value.hour, current_value.minute, \
current_value.second), 0)
current_value_is_date = True
elif isinstance(current_value, datetime.date):
current_value = xlrd.xldate.xldate_from_date_tuple((current_value.year, current_value.month, \
current_value.day), 0)
current_value_is_date = True
elif isinstance(current_value, datetime.time):
current_value = xlrd.xldate.xldate_from_time_tuple((current_value.hour, current_value.minute, \
current_value.second))
current_value_is_date = True
elif isinstance(current_value, models.Model):
current_value = str(current_value)
if current_value_is_date:
"""
s = XFStyle()
s.num_format_str = 'M/D/YY'
"""
export_sheet.write(row_idx, col_idx, current_value, s)
else:
export_sheet.write(row_idx, col_idx, current_value)
col_idx += 1
row_idx += 1
export_wb.save(filename)
def write_row(self, array):
"""
write a row into the file
"""
for i in range(0, len(array)):
value = array[i]
style = None
tmp_array = []
if isinstance(value, datetime.datetime):
tmp_array = [
value.year, value.month, value.day,
value.hour, value.minute, value.second
]
value = xlrd.xldate.xldate_from_datetime_tuple(tmp_array, 0)
style = XFStyle()
style.num_format_str = DEFAULT_DATETIME_FORMAT
elif isinstance(value, datetime.date):
tmp_array = [value.year, value.month, value.day]
value = xlrd.xldate.xldate_from_date_tuple(tmp_array, 0)
style = XFStyle()
style.num_format_str = DEFAULT_DATE_FORMAT
elif isinstance(value, datetime.time):
tmp_array = [value.hour, value.minute, value.second]
value = xlrd.xldate.xldate_from_time_tuple(tmp_array)
style = XFStyle()
style.num_format_str = DEFAULT_TIME_FORMAT
if style:
self.native_sheet.write(self.current_row, i, value, style)
else:
self.native_sheet.write(self.current_row, i, value)
self.current_row += 1
def creaHojaDeTraduccion(excel, nombreHoja="traduccion"):
hoja=excel.creaHoja(nombreHoja)
hoja.col(0).width=90*256
hoja.col(1).width=20*256
fnt = Font()
fnt.bold=True
#fnt.colour_index=2
borders = Borders()
borders.left = Borders.MEDIUM
borders.right = Borders.MEDIUM
borders.top = Borders.MEDIUM
borders.bottom = Borders.MEDIUM
pattern = Pattern()
pattern.pattern = Pattern.SOLID_PATTERN
pattern.pattern_fore_colour = 0x18#0x99
style = XFStyle()
style.num_format_str='YYYY-MM-DD'
style.font = fnt
style.borders = borders
style.pattern = pattern
hoja.write(0,0,"Original Text",style)
hoja.write(0,1,"Translation",style)
return hoja
def write_row(self, array):
"""
write a row into the file
"""
for i in range(0, len(array)):
value = array[i]
style = None
tmp_array = []
if isinstance(value, datetime.date) or isinstance(value, datetime.datetime):
tmp_array = [value.year, value.month, value.day]
value = xlrd.xldate.xldate_from_date_tuple(tmp_array, 0)
style = XFStyle()
style.num_format_str = "DD/MM/YY"
elif isinstance(value, datetime.time):
tmp_array = [value.hour, value.minute, value.second]
value = xlrd.xldate.xldate_from_time_tuple(tmp_array)
style = XFStyle()
style.num_format_str = "HH:MM:SS"
if style:
self.ws.write(self.current_row, i, value, style)
else:
self.ws.write(self.current_row, i, value)
self.current_row += 1
def write_resultset_to_xls_book(book,sheetname,results,**kwargs):
logging.debug('MAKE SHEET %s',sheetname)
sheet1 = book.add_sheet(sheetname)
names = None
if kwargs.has_key('names'):
names = kwargs['names']
if names is None:
names=[ "FIELD%03d" % (i+1) for i in range(len(results[0])) ]
for i,n in enumerate(names):
sheet1.write(0,i,n)
style = XFStyle()
style.num_format_str = "0.0"
for i,t in enumerate(results):
for j,v in enumerate(t):
if np.isnan(v):
sheet1.write(j+1,i,None)
else:
sheet1.write(j+1,i,v,style)
def export(match_list, file_path):
wb = xlwt.Workbook()
ws = wb.add_sheet('Ranking')
# writing header
header = ['Ranking',
'Name',
'Rating',
'Reviews',
'Price',
'Cuisines',
'Edenred Name',
'Match',
'Edenred Address']
font = Font()
font.name = 'Arial'
font.bold = True
style = XFStyle()
style.font = font
for index in range(len(header)):
ws.write(0, index, header[index], style)
# writing restaurants
for index in range(len(match_list)):
match = match_list[index]
ws.write(index+1, 0, int(match.restaurant1.ranking))
ws.write(index+1, 1, xlwt.Formula('HYPERLINK("%s"; "%s")' % (match.restaurant1.url, match.restaurant1.name)))
ws.write(index+1, 2, float(match.restaurant1.rating.replace(',', '.')))
ws.write(index+1, 3, int(match.restaurant1.review_count))
ws.write(index+1, 4, match.restaurant1.price)
ws.write(index+1, 5, ', '.join(match.restaurant1.cuisines))
ws.write(index+1, 6, match.restaurant2.name)
ws.write(index+1, 7, int(100.0 * match.score))
address = match.restaurant2.address + ', ' + match.restaurant2.postcode
map_link = 'https://www.google.com/maps?f=q&source=s_q&hl=es&q=%s' % address
ws.write(index+1, 8, xlwt.Formula('HYPERLINK("%s"; "%s")' % (map_link, address)))
wb.save(file_path)
def write(self, r, c, label=u'', style=None):
if not style:
style = XFStyle()
if isinstance(label, datetime.datetime):
_saved_format = style.num_format_str
style.num_format_str = 'dd/mm/yyyy hh:mm:ss'
self.sheet.write(r, c, label, style)
style.num_format_str = _saved_format
elif isinstance(label, datetime.date):
_saved_format = style.num_format_str
style.num_format_str = 'dd/mm/yyyy'
self.sheet.write(r, c, label, style)
style.num_format_str = _saved_format
# elif isinstance(label, (float, Decimal)):
# _saved_format = style.num_format_str
# style.num_format_str = '#,##0.00'
# self.sheet.write(r, c, label, style)
# style.num_format_str = _saved_format
else:
self.sheet.write(r, c, label, style)
self.sheet.row(r).collapse = True
unicode_label = unicode(label)
bold = str(style.font.bold) in ('1', 'true', 'True')
width = min(int(arial10.fitwidth(unicode_label, bold)), MAX_COLUMN_WIDTH)
if width > self.widths.get(c, 0):
self.widths[c] = width
self.sheet.col(c).width = width
height = int(arial10.fitheight(unicode_label, bold))
if height > self.heights.get(r, 0):
self.heights[r] = height
self.sheet.row(r).height = height
def write_hash_to_xls_book(book,sheetname,h,**kwargs):
sheet1 = book.add_sheet(sheetname)
sheet1.write(0,0,' / ')
for i,d in enumerate(h.keys()):
sheet1.write(i+1,0,str(d))
all_keys = list()
for k in h.values():
kk=k.keys()
all_keys.extend(kk)
all_keys=set(all_keys)
for i,d in enumerate(sorted(all_keys)):
sheet1.write(0,i+1,str(d))
style = XFStyle()
style.num_format_str = "0.0"
for i,t in enumerate(h.values()):
for j,k in enumerate(sorted(all_keys)):
if t.has_key(k):
sheet1.write(i+1,j+1,t[k],style)
else:
sheet1.write(i+1,j+1,None)
def __init__(self, output=None, sheetname='Dieses Sheet'):
self.book = Workbook()
self.sheet = self.book.add_sheet(sheetname)
self.rownum = 0
self.output = output
datestyle = XFStyle()
datestyle.num_format_str = 'YYYY-MM-DD'
intstyle = XFStyle()
intstyle.num_format_str = '0'
floatstyle = XFStyle()
floatstyle.num_format_str = '0.00'
self.styles = {
'date': datestyle,
'int': intstyle,
'float': floatstyle,
}
def code_update_report(workbook_ro, workbook, gl, gl_codeids, nc_sp, pvs_sp, fm_sp, ibm):
"""This report reads from the readonly workbook in order to perform some
cell processing.
"""
# Sheet 1
sheet = workbook.get_sheet(0)
sheet_ro = workbook_ro.get_sheet(0)
# Download hyperlink:
bigfont = easyxf('font: bold 1,height 360;') # Font height is in "twips" (1/20 of a point)
url = Formula('HYPERLINK("{}")'.format(settings.IBM_CODE_UPDATER_URI))
sheet.write(1, 0, url, bigfont)
# Padded zeroes number format
pad2, pad3, pad4 = XFStyle(), XFStyle(), XFStyle()
pad2.num_format_str = '00'
pad3.num_format_str = '000'
pad4.num_format_str = '0000'
# For each of the GL code IDs, take a subset of the query
# and insert values as required.
row = 4
max_col_idx = 21 # Start at column V.
for codeID in gl_codeids:
gl_pivs = gl.filter(codeID=codeID)
g = gl_pivs[0]
# Fill the non-resource columns.
sheet.write(row, 0, g.codeID)
sheet.write(row, 1, int(g.costCentre), pad3)
sheet.write(row, 2, g.account, pad2)
sheet.write(row, 3, g.service, pad2)
sheet.write(row, 4, g.activity, pad3)
try:
sheet.write(row, 5, int(g.project), pad4)
except ValueError:
sheet.write(row, 5, g.project, pad4)
try:
sheet.write(row, 6, int(g.job), pad3)
except ValueError:
sheet.write(row, 6, g.job, pad3)
sheet.write(row, 7, g.jobName)
sheet.write(row, 8, g.activityName)
sheet.write(row, 9, g.projNameNo)
sheet.write(row, 19, g.mPRACategory)
# Write the SUM formula.
sheet.write(row, 20, Formula('ROUND(SUM(V{}:GP{}), 0)'.format(row+1, row+1)))
# First, find the maximum column index in the template headers (row 4).
blank_cell = False
while not blank_cell:
if not sheet_ro.cell_value(3, max_col_idx): # Blank cell
blank_cell = True
else:
max_col_idx += 1
# Write ytdActual values for matching resource columns.
# Find the correct cell index of a matching resource code.
# If no match found, use the '0000' column (the first).
for gl_piv in gl_pivs:
resource_idx = 21 # Column V, '0000'
match_resource_code = False
for i in range(resource_idx, max_col_idx + 1):
if sheet_ro.cell_value(3, i) and int(sheet_ro.cell_value(3, i)) == gl_piv.resource:
match_resource_code = True
break
resource_idx += 1
if not match_resource_code: # No match was found.
resource_idx = 21 # Insert the ytdActual into column V.
# Write the ytdActual to the sheet.
sheet.write(row, resource_idx, gl_piv.ytdActual)
row += 1 # Advance one row, to the next Code ID.
row += 1
# Insert the footer row formulae and '#END OF INPUT'
sheet.write(row, 0, '#END OF INPUT')
sheet.write(row, 20, Formula('ROUND(SUM(V{}:GP{}), 0)'.format(row+1, row+1)))
for i in range(21, max_col_idx):
# For cell V:<end> in the footer row, insert a SUM formula.
sheet.write(row, i, Formula('ROUND(SUM({}:{}), 0)'.format(cellname(4, i), cellname(row-1, i))))
# Sheet 2: Service priority checkboxes.
sheet = workbook.get_sheet(1)
write_service_priorities(sheet, nc_sp, pvs_sp, fm_sp)
# Sheet 3: Budget area & project sponsor lookup data.
# This is a list of unique budgetArea and projectSponsor values, written in
# as reference data for macros.
sheet = workbook.get_sheet(2)
write_budget_areas(sheet, ibm)
write_project_sponsors(sheet, ibm)
write_regional_spec_info(sheet, ibm)
# Select the first sheet.
sheet = workbook.get_sheet(0)
def write(folder,key, all_ID, all_score, all_correct, all_answer):
letters = ['A', 'B', 'C', 'D', 'E', 'X', 'M']
num_students = len(all_ID)
num_problems = len(key)
book = Workbook()
sheet1 = book.add_sheet('Score Summary')
sheet1.col(0).width = 3000
sheet1.col(1).width = 3000
sheet1.col(2).width = 3000
sheet1.col(3).width = 500
sheet1.col(4).width = 5000
sheet1.col(5).width = 3000
sheet1.col(6).width = 3000
sheet1.write(0, 0, 'Student ID') # A1
sheet1.write(0, 1, 'Raw Score') # B1
sheet1.write(0, 2, 'Percentage') # B1
pstyle = XFStyle()
pstyle.num_format_str = '0.00%'
dstyle = XFStyle()
dstyle.num_format_str = '0.00'
for j in range(num_students):
ID = all_ID[j]
score = all_score[j]
sheet1.row(j+1).set_cell_number(0, ID)
sheet1.row(j+1).set_cell_number(1, score)
sheet1.row(j+1).set_cell_number(2, score/float(num_problems), pstyle)
sheet1.write(0, 4, 'Test Score Statistics')
sheet1.write(0, 5, 'Raw Score')
sheet1.write(0, 6, 'Percentage')
sheet1.write(1, 4, 'Mean')
sheet1.write(2, 4, 'Median')
sheet1.write(3, 4, 'Max')
sheet1.write(4, 4, 'Min')
sheet1.write(5, 4, 'Standard Deviation')
mean_score = 'AVERAGE(B%d:B%d)' % (2, 1+num_students)
median_score = 'MEDIAN(B%d:B%d)' % (2, 1+num_students)
max_score = 'MAX(B%d:B%d)' % (2, 1+num_students)
min_score = 'MIN(B%d:B%d)' % (2, 1+num_students)
stdev = 'STDEV(B%d:B%d)' % (2, 1+num_students)
sheet1.write(1, 5, Formula(mean_score), dstyle)
sheet1.write(2, 5, Formula(median_score), dstyle)
sheet1.write(3, 5, Formula(max_score), dstyle)
sheet1.write(4, 5, Formula(min_score), dstyle)
sheet1.write(5, 5, Formula(stdev), dstyle)
mean_score = 'AVERAGE(C%d:C%d)' % (2, 1+num_students)
median_score = 'MEDIAN(C%d:C%d)' % (2, 1+num_students)
max_score = 'MAX(C%d:C%d)' % (2, 1+num_students)
min_score = 'MIN(C%d:C%d)' % (2, 1+num_students)
stdev = 'STDEV(C%d:C%d)' % (2, 1+num_students)
sheet1.write(1, 6, Formula(mean_score), pstyle)
sheet1.write(2, 6, Formula(median_score), pstyle)
sheet1.write(3, 6, Formula(max_score), pstyle)
sheet1.write(4, 6, Formula(min_score), pstyle)
sheet1.write(5, 6, Formula(stdev), pstyle)
col_totals = [sum(x) for x in zip(*all_correct)]
sheet2 = book.add_sheet('Test Diagnostics')
sheet2.col(0).width = 3000
sheet2.col(1).width = 3000
sheet2.col(2).width = 2000
sheet2.col(3).width = 500
sheet2.col(4).width = 5000
sheet2.col(5).width = 5000
sheet2.write(0, 0, 'Problem') # A1
sheet2.write(0, 1, '% Correct') # B1
for i in range(len(key)):
sheet2.row(i+1).set_cell_number(0, i+1)
sheet2.row(i+1).set_cell_number(1, col_totals[i]/float(num_students), pstyle)
# SHEET 3
sheet3 = book.add_sheet('Answer Summary')
sheet3.col(0).width = 3000
sheet3.col(1).width = 2000
sheet3.write(0, 0, 'Problem #') # A1
sheet3.write(0, 1, 'Key') # A1
for j in range(len(all_ID)):
sheet3.col(j+2).width = 2000
ID = all_ID[j]
sheet3.row(0).set_cell_number(j+2, ID)
for i in range(len(key)):
sheet3.write(i+1, j+2, letters[all_answer[j][i]])
for i in range(len(key)):
sheet3.row(i+1).set_cell_number(0, i+1)
sheet3.write(i+1, 1, letters[key[i]])
fout=os.path.join(folder,'grades.xls')
book.save(fout)
book.save(TemporaryFile())
def render_excel(filename, title_list, data_list, file_extension='.xls'):
if file_extension == '.csv':
response = HttpResponse(content_type='text/csv')
response['Content-Disposition'] = 'attachment; filename="%s"' % filename
csv_writer = csv.writer(response)
csv_writer.writerow(title_list)
for row_item_list in data_list:
for i in range(0, len(row_item_list)):
if row_item_list[i]:
if isinstance(row_item_list[i], datetime.datetime):
row_item_list[i] = row_item_list[i].strftime(
'%Y-%m-%d %H:%M:%S'
)
elif isinstance(row_item_list[i], datetime.date):
row_item_list[i] = row_item_list[i].strftime(
'%Y-%m-%d'
)
elif isinstance(row_item_list[i], datetime.time):
row_item_list[i] = row_item_list[i].strftime(
'%H:%M:%S'
)
csv_writer.writerow(row_item_list)
else:
from io import BytesIO
output = BytesIO()
export_wb = Workbook()
export_sheet = export_wb.add_sheet('Sheet1')
col_idx = 0
for col_title in title_list:
export_sheet.write(0, col_idx, "%s" % col_title)
col_idx += 1
row_idx = 1
for row_item_list in data_list:
col_idx = 0
for cell_value in row_item_list:
if cell_value:
cell_value_is_date = False
if isinstance(cell_value, datetime.datetime):
cell_value = xlrd.xldate.xldate_from_datetime_tuple((
cell_value.year, cell_value.month,
cell_value.day, cell_value.hour,
cell_value.minute,
cell_value.second), 0)
cell_value_is_date = True
elif isinstance(cell_value, datetime.date):
cell_value = xlrd.xldate.xldate_from_date_tuple((
cell_value.year,
cell_value.month,
cell_value.day), 0)
cell_value_is_date = True
elif isinstance(cell_value, datetime.time):
cell_value = xlrd.xldate.xldate_from_time_tuple((
cell_value.hour,
cell_value.minute,
cell_value.second))
cell_value_is_date = True
elif isinstance(cell_value, models.Model):
cell_value = str(cell_value)
if cell_value_is_date:
s = XFStyle()
s.num_format_str = 'M/D/YY'
export_sheet.write(row_idx, col_idx, cell_value, s)
else:
export_sheet.write(row_idx, col_idx, cell_value)
col_idx += 1
row_idx += 1
export_wb.save(output)
output.seek(0)
str_out = output.getvalue()
response = HttpResponse(str_out)
response['Content-Type'] = 'application/vnd.ms-excel'
response['Content-Disposition'] = 'attachment; filename="%s"' % filename
return response
def do_export(self):
"""
Does actual export. Called from a celery task as it may require a lot of API calls to grab all messages.
"""
from xlwt import Workbook, XFStyle
book = Workbook()
date_style = XFStyle()
date_style.num_format_str = 'DD-MM-YYYY HH:MM:SS'
base_fields = ["Time", "Message ID", "Flagged", "Labels", "Text", "Contact"]
contact_fields = self.org.get_contact_fields()
all_fields = base_fields + contact_fields
label_map = {l.name: l for l in Label.get_all(self.org)}
client = self.org.get_temba_client()
search = self.get_search()
# fetch all messages to be exported
messages = Message.search(self.org, search, None)
# extract all unique contacts in those messages
contact_uuids = set()
for msg in messages:
contact_uuids.add(msg.contact)
# fetch all contacts in batches of 25 and organize by UUID
contacts_by_uuid = {}
for uuid_chunk in chunks(list(contact_uuids), 25):
for contact in client.get_contacts(uuids=uuid_chunk):
contacts_by_uuid[contact.uuid] = contact
def add_sheet(num):
sheet = book.add_sheet(unicode(_("Messages %d" % num)))
for col in range(len(all_fields)):
field = all_fields[col]
sheet.write(0, col, unicode(field))
return sheet
# even if there are no messages - still add a sheet
if not messages:
add_sheet(1)
else:
sheet_number = 1
for msg_chunk in chunks(messages, 65535):
current_sheet = add_sheet(sheet_number)
row = 1
for msg in msg_chunk:
created_on = msg.created_on.astimezone(pytz.utc).replace(tzinfo=None)
flagged = SYSTEM_LABEL_FLAGGED in msg.labels
labels = ', '.join([label_map[l_name].name for l_name in msg.labels if l_name in label_map])
contact = contacts_by_uuid.get(msg.contact, None) # contact may no longer exist in RapidPro
current_sheet.write(row, 0, created_on, date_style)
current_sheet.write(row, 1, msg.id)
current_sheet.write(row, 2, 'Yes' if flagged else 'No')
current_sheet.write(row, 3, labels)
current_sheet.write(row, 4, msg.text)
current_sheet.write(row, 5, msg.contact)
for cf in range(len(contact_fields)):
if contact:
contact_field = contact_fields[cf]
current_sheet.write(row, 6 + cf, contact.fields.get(contact_field, None))
else:
current_sheet.write(row, 6 + cf, None)
row += 1
sheet_number += 1
temp = NamedTemporaryFile(delete=True)
book.save(temp)
temp.flush()
filename = 'orgs/%d/message_exports/%s.xls' % (self.org.id, random_string(20))
default_storage.save(filename, File(temp))
self.filename = filename
self.save(update_fields=('filename',))
subject = "Your messages export is ready"
download_url = settings.SITE_HOST_PATTERN % self.org.subdomain + reverse('cases.messageexport_read', args=[self.pk])
send_email(self.created_by.username, subject, 'cases/email/message_export', dict(link=download_url))
# force a gc
import gc
gc.collect()
