def export_xls(modeladmin, request, queryset):
response = HttpResponse(content_type='application/ms-excel')
response['Content-Disposition'] = 'attachment; filename=quejas.xls'
wb = xlwt.Workbook(encoding='utf-8')
ws = wb.add_sheet("Quejas")
row_num = 0
columns = [
(u"Queja", 15000),
(u"Sugerencia", 15000),
(u"Sucursal", 8000),
(u"Comercio", 8000),
(u"No. Factura", 3000),
(u"Fecha de Incidente", 3000),
(u"Fecha de Registro", 3000),
(u"Nombre Cliente", 3000),
(u"Apellido Cliente", 3000),
]
font_style = xlwt.XFStyle()
font_style.font.bold = True
for col_num in xrange(len(columns)):
ws.write(row_num, col_num, columns[col_num][0], font_style)
# set column width
ws.col(col_num).width = columns[col_num][1]
font_style = xlwt.XFStyle()
font_style.alignment.wrap = 1
for obj in queryset:
row_num += 1
row = [
obj.detalle_queja,
obj.solicitud,
obj.id_sucursal.nombre_sucursal,
obj.id_comercio.razon_comercial,
obj.factura,
obj.fecha_incidente,
obj.fecha_registro,
obj.nombre_cliente,
obj.apellido_cliente,
]
for col_num in xrange(len(row)):
ws.write(row_num, col_num, row[col_num], font_style)
wb.save(response)
return response
def dfs(nums, temp, index):
self.res.append(temp[:])
for i in xrange(index, len(nums)):
temp.append(nums[i])
dfs(nums, temp, i+1)
temp.pop()
def dfs(nums, temp, index):
self.res.append(temp[:])
for i in xrange(index, len(nums)):
if i > index and nums[i] == nums[i - 1]:
continue
temp.append(nums[i])
dfs(nums, temp, i + 1)
temp.pop()
def dp(i, j):
if i > j: return 0
if (i, j) not in memo:
ans = dp(i + 1, j) + 1
for k in xrange(i + 1, j + 1):
if S[k] == S[i]:
ans = min(ans, dp(i, k - 1) + dp(k + 1, j))
memo[i, j] = ans
return memo[i, j]
def dfs(nums, temp):
if len(nums) == len(temp):
self.res.append(temp[:])
return
for i in xrange(len(nums)):
if nums[i] in temp:
continue
temp.append(nums[i])
dfs(nums, temp)
temp.pop()
def export_xls(modeladmin, request, queryset):
response = HttpResponse(content_type='application/ms-excel')
response[
'Content-Disposition'] = 'attachment; filename=Radyo Dinleyiciler.xls'
wb = xlwt.Workbook(encoding='utf-8')
ws = wb.add_sheet('Tümü')
row_num = 0
columns = [
(u"İsim", 2000),
(u"Grup", 2000),
(u"IP Adresi", 6000),
(u"Tarayıcı", 8000),
(u"Zaman", 8000),
]
font_style = xlwt.XFStyle()
font_style.font.bold = True
for col_num in xrange(len(columns)):
ws.write(row_num, col_num, columns[col_num][0], font_style)
# set column width
ws.col(col_num).width = columns[col_num][1]
font_style = xlwt.XFStyle()
font_style.alignment.wrap = 1
for obj in queryset:
row_num += 1
row = [
# obj.pk,
obj.name,
obj.organization,
obj.ip,
obj.browser,
str(obj.updated),
]
for col_num in xrange(len(row)):
ws.write(row_num, col_num, row[col_num], font_style)
wb.save(response)
return response
def dfs(n, temp, map):
if n == 0:
self.res.append(map[0])
return
for i in xrange(n):
if i * math.factorial(n) < temp and (i + 1) * math.factorial(n) > temp:
self.res.append(map[i + 1])
map.remove(i + 1)
curdigit = i
break
dfs(n - 1, temp - curdigit * math.factorial(n), map)
def export_xls(modeladmin, request, queryset):
import xlwt
response = HttpResponse(content_type='application/ms-excel')
response['Content-Disposition'] = 'attachment; filename=ListaClienti.xls'
wb = xlwt.Workbook(encoding='utf-8')
ws = wb.add_sheet("ListaClienti")
row_num = 0
columns = [
(u"cf", 7000),
(u"Nome", 7000),
(u"Cognome", 7000),
(u"Cellulare", 7000),
]
font_style = xlwt.XFStyle()
font_style.font.bold = True
for col_num in xrange(len(columns)):
ws.write(row_num, col_num, columns[col_num][0], font_style)
# set column width
ws.col(col_num).width = columns[col_num][1]
font_style = xlwt.XFStyle()
font_style.alignment.wrap = 1
for obj in queryset:
row_num += 1
row = [
obj.cf,
obj.nome,
obj.cognome,
obj.tel_cellulare,
]
for col_num in xrange(len(row)):
ws.write(row_num, col_num, row[col_num], font_style)
wb.save(response)
return response
def getUrls(self):
'''获取数据来源网页
'''
URL = r'http://kaijiang.zhcw.com/zhcw/html/ssq/list_1.html'
htmlContent = self.getResponseContent(URL)
soup = BeautifulSoup(htmlContent, 'lxml')
tag = soup.find_all(re.compile('p'))[-1]
pages = tag.strong.get_text()
for i in xrange(1, int(pages) + 1):
url = r'http://kaijiang.zhcw.com/zhcw/html/ssq/list_' + str(
i) + '.html'
self.urls.append(url)
self.log.info(u'添加URL:%s 到URLS \r\n' % url)
def cherryPickup(self, grid):
N = len(grid)
memo = [[[None] * N for _1 in xrange(N)] for _2 in xrange(N)]
def dp(r1, c1, c2):
r2 = r1 + c1 - c2
if (N == r1 or N == r2 or N == c1 or N == c2 or grid[r1][c1] == -1
or grid[r2][c2] == -1):
return float('-inf')
elif r1 == c1 == N - 1:
return grid[r1][c1]
elif memo[r1][c1][c2] is not None:
return memo[r1][c1][c2]
else:
ans = grid[r1][c1] + (c1 != c2) * grid[r2][c2]
ans += max(dp(r1, c1 + 1, c2 + 1), dp(r1 + 1, c1, c2 + 1),
dp(r1, c1 + 1, c2), dp(r1 + 1, c1, c2))
memo[r1][c1][c2] = ans
return ans
return max(0, dp(0, 0, 0))
def getPermutation(self, n, k):
"""
:type nums: List[int]
:rtype: List[List[int]]
"""
def dfs(n, temp, map):
if n == 0:
self.res.append(map[0])
return
for i in xrange(n):
if i * math.factorial(n) < temp and (i + 1) * math.factorial(n) > temp:
self.res.append(map[i + 1])
map.remove(i + 1)
curdigit = i
break
dfs(n - 1, temp - curdigit * math.factorial(n), map)
map = []
for i in xrange(n):
map.append(i)
dfs(n, k-1, map)
return self.res
def export_xls(modeladmin, request, queryset):
response = HttpResponse(content_type='application/ms-excel')
response[
'Content-Disposition'] = 'attachment; filename=ListaInterventi.xls'
wb = xlwt.Workbook(encoding='utf-8')
ws = wb.add_sheet("ListaInterventi")
row_num = 0
columns = [
(u"tecnico", 7000),
(u"tipo servizio", 7000),
(u"OpCo", 7000),
(u"location", 7000),
# (u"palazzo", 7000),
(u"richiedente", 7000),
(u"data richiesta", 7000),
(u"data chiusura", 7000),
(u"ora intervento", 7000),
(u"cognome", 7000),
(u"nome", 7000),
(u"tipo asset", 7000),
(u"asset", 7000),
(u"tipo ticket", 7000),
(u"numero ticket", 7000),
(u"area intervento", 7000),
(u"descrizione richiesta", 7000),
(u"soluzione adottata", 7000),
(u"stato intervento", 7000),
(u"note", 7000),
(u"tipo ingaggio", 7000),
]
font_style = xlwt.XFStyle()
font_style.font.bold = True
for col_num in xrange(len(columns)):
ws.write(row_num, col_num, columns[col_num][0], font_style)
# set column width
ws.col(col_num).width = columns[col_num][1]
font_style = xlwt.XFStyle()
font_style.alignment.wrap = 1
for objects in reversed(queryset):
row_num += 1
# qui intercetto gli eventuali campi NULL dovuti alla non scelta del dispositivo
if (objects.asset != None):
location = objects.asset.location
palazzo = objects.asset.palazzo
tipo_dispositivo = objects.asset.tipo_dispositivo.tipo_dispositivo
asset = objects.asset.asset
else:
location = ""
palazzo = ""
tipo_dispositivo = "Altro"
asset = ""
if (objects.datetime_ingaggio != None):
datetime_ingaggio = objects.datetime_ingaggio.strftime(
'%d-%m-%Y %H:%M:%S')
else:
datetime_ingaggio = ""
row = [
objects.tecnico,
objects.tipo_servizio,
objects.fk_beneficiario.divisione,
location,
# palazzo,
objects.richiedente,
objects.data_richiesta.strftime('%d-%m-%Y'),
objects.data_chiusura.strftime('%d-%m-%Y'),
datetime_ingaggio,
objects.fk_beneficiario.cognome,
objects.fk_beneficiario.nome,
tipo_dispositivo,
asset,
objects.tipo_ticket,
objects.numero_ticket,
objects.area_intervento,
objects.descrizione_richiesta,
objects.soluzione_adottata,
objects.stato_intervento,
objects.note,
objects.tipo_ingaggio,
]
for col_num in xrange(len(row)):
ws.write(row_num, col_num, row[col_num], font_style)
wb.save(response)
return response
def export_xls(request, week, start_date):
import xlwt
data = get_statement_data(week, start_date)
file_name = 'statement({} {}).xls'.format(data['start_week'], data['end_week'])
response = HttpResponse(content_type='application/ms-excel')
response['Content-Disposition'] = 'attachment; filename=' + file_name
wb = xlwt.Workbook(encoding='utf-8')
# кастомные цвета
xlwt.add_palette_colour("custom_blue", 0x21)
wb.set_colour_RGB(0x21, 0, 176, 240)
blue_style = xlwt.easyxf('pattern: pattern solid, fore_colour custom_blue')
xlwt.add_palette_colour("custom_light_blue", 0x22)
wb.set_colour_RGB(0x22, 146, 205, 220)
light_blue_style = xlwt.easyxf('pattern: pattern solid, fore_colour custom_light_blue')
xlwt.add_palette_colour("custom_orange", 0x23)
wb.set_colour_RGB(0x23, 255, 192, 0)
orange_style = xlwt.easyxf('pattern: pattern solid, fore_colour custom_orange')
xlwt.add_palette_colour("custom_light_green", 0x24)
wb.set_colour_RGB(0x24, 146, 208, 80)
light_green_style = xlwt.easyxf('pattern: pattern solid, fore_colour custom_light_green')
xlwt.add_palette_colour("custom_yellow", 0x25)
wb.set_colour_RGB(0x25, 255, 255, 0)
yellow_style = xlwt.easyxf('pattern: pattern solid, fore_colour custom_yellow')
xlwt.add_palette_colour("custom_pink", 0x26)
wb.set_colour_RGB(0x26, 230, 184, 183)
pink_style = xlwt.easyxf('pattern: pattern solid, fore_colour custom_pink')
xlwt.add_palette_colour("custom_dark_pink", 0x27)
wb.set_colour_RGB(0x27, 218, 150, 148)
dark_pink_style = xlwt.easyxf('pattern: pattern solid, fore_colour custom_dark_pink')
xlwt.add_palette_colour("custom_penalty", 0x28)
wb.set_colour_RGB(0x28, 218, 150, 148)
penalty_style = xlwt.easyxf('pattern: pattern solid, fore_colour custom_penalty')
xlwt.add_palette_colour("custom_purple", 0x29)
wb.set_colour_RGB(0x29, 177, 160, 199)
purple_style = xlwt.easyxf('pattern: pattern solid, fore_colour custom_purple')
xlwt.add_palette_colour("custom_red", 0x30)
wb.set_colour_RGB(0x30, 255, 71, 71)
red_style = xlwt.easyxf('pattern: pattern solid, fore_colour custom_red')
font_style_bold = xlwt.XFStyle()
font_style_bold.font.bold = True
writer = Writer(wb.add_sheet('statement'))
# ws = wb.add_sheet('statement')#, cell_overwrite_ok=True)
# ПЕРВАЯ СТРОКА
font_style = xlwt.XFStyle()
for i in xrange(len(data['drinks_table_header']) + 2):
writer.write('', font_style)
writer.write('Tequilla Girl', font_style_bold)
# имена сотрудников
for idx, employee in enumerate(data['employees_table_header']):
writer.write('{}. {}'.format(idx + 1, employee.get_full_name()), font_style)
# ВТОРАЯ СТРОКА
writer.new_row()
writer.write('', font_style)
writer.write('', font_style)
# названия напитков
for drink_data in data['drinks_table_header']:
if drink_data['used']:
writer.write(drink_data['name'], font_style)
writer.write('Менеджер Tequilla girl', font_style_bold)
# координаторы
for employee in data['employees_table_header']:
text = employee.coordinator.get_full_name() if employee.coordinator else ''
writer.write(text, font_style)
# ТРЕТЬЯ СТРОКА
writer.new_row()
writer.write('Клуб', font_style_bold)
writer.write('Менеджер клуба', font_style_bold)
for drink_data in data['drinks_table_header']:
if drink_data['used']:
writer.write('Цена', font_style)
writer.write('Напитки', font_style_bold)
# названия напитков
for drinks in data['header_drinks_for_employee'].values():
names = [drink for drink in drinks]
writer.write('{}, {}, {}'.format(', '.join(names), 'Всего клуб', 'Коорд-р'), font_style)
# ВЫВОД ТАБЛИЦЫ
for idx, row in enumerate(data['grid']):
writer.new_row()
writer.write(
'{}. {} {}, {}'.format(
idx + 1, row['club'].name, row['club'].get_address(), row['club'].city if row['club'].city else ''
),
yellow_style if idx % 2 == 0 else orange_style
)
writer.write(row['club'].coordinator.surname if row['club'].coordinator else '', light_green_style)
for ndx, price in enumerate(row['drinks_for_club']):
if price['used']:
writer.write(
price['sum'],
blue_style if ndx % 2 == 0 else light_blue_style
)
writer.write('', font_style)
for info in row['employees_info']:
count = [str(drink['count']) for drink in info['drinks_list']]
writer.write(
'{}, {:.2f}р., {:.2f}р.'.format(' '.join(count), info['sum_for_club'], info['sum_for_coordinator']),
yellow_style
)
# ФУТЕР
writer.new_row()
writer.write('ВСЕГО (Перевод Tequilla girl)', pink_style)
for i in xrange(len(data['drinks_table_header']) + 2):
writer.write('', pink_style)
for info in data['bottom_prices_for_employee'].values():
writer.write('{:.2f}'.format(info['all']), pink_style)
writer.new_row()
writer.write('Залог Tequilla girl', dark_pink_style)
for i in xrange(len(data['drinks_table_header']) + 2):
writer.write('', dark_pink_style)
for info in data['bottom_prices_for_employee'].values():
writer.write(info['pledge'], dark_pink_style)
writer.new_row()
writer.write('Штраф', penalty_style)
for i in xrange(len(data['drinks_table_header']) + 2):
writer.write('', penalty_style)
for info in data['bottom_prices_for_employee'].values():
writer.write('{:.2f}'.format(info['penalty']), penalty_style)
writer.new_row()
writer.write('Причина штрафа', penalty_style)
for i in xrange(len(data['drinks_table_header']) + 2):
writer.write('', penalty_style)
for info in data['bottom_prices_for_employee'].values():
description = [description for description in info['penalty_description']]
writer.write('; '.join(description), penalty_style)
writer.new_row()
writer.write('Координатор (всего за клубы по каждой Tequilla Girl)', purple_style)
for i in xrange(len(data['drinks_table_header']) + 2):
writer.write('', purple_style)
for info in data['bottom_prices_for_employee'].values():
writer.write('{:.2f}'.format(info['coordinator']), purple_style)
writer.new_row()
writer.write('Ермакова (всего за клубы по каждой Tequilla Girl)', light_blue_style)
for i in xrange(len(data['drinks_table_header']) + 2):
writer.write('', light_blue_style)
for info in data['bottom_prices_for_employee'].values():
writer.write('{:.2f}'.format(info['director']), light_blue_style)
writer.new_row()
writer.new_row()
writer.write(
'Координатор(за все клубы и за всех tequila girls): {:.2f}'.format(data['admins_salary']['coordinator']),
pink_style
)
writer.new_row()
writer.write(
'Ермакова(за все клубы и за всех tequila girls): {:.2f}'.format(data['admins_salary']['director']),
red_style
)
wb.save(response)
return response
import xlrd
from xlwt.compat import xrange
if __name__ == '__main__':
# excel路径
xlPath = '/Users/yvetteyoung/Desktop/test.xlsx'
# 用于读取excel
xlBook = xlrd.open_workbook(xlPath)
# 获取excel工作簿数
count = len(xlBook.sheets())
print("工作簿数为：", count)
# 获取表数据的行列数
table = xlBook.sheets()[0]
nrows = table.nrows
ncols = table.ncols
print("表数据行列为（%d,%d)" % (nrows, ncols))
# 循环读取数据
for i in xrange(0, nrows):
rowValues = table.row_values(i) # 按行读取数据
# 输出读取的数据
for data in rowValues:
print(data, "\t")
print("\n")
print ("saved to file: " , text)
file = open('C:/Users/ryann/PycharmProjects/script4lab/Script9LabAssignment/file.txt', 'r')
print (file.read())
style1 = easyxf('pattern: pattern solid, fore_colour yellow;')
wb = Workbook()
sheet1 = wb.add_sheet('Sheet 1')
sheet1.write(0,0, 'Column 1')
sheet1.write(0,1, 'Column 2')
sheet1.write(0,2, 'Column 3')
for i in xrange(10):
sheet1.write(i+1, 0, i + 1)
sheet1.write(i+1, 1, (100 + 10 * i))
sheet1.write(i + 1, 2, (1500 + 1500 * i))
sheet1.write(12, 0, Formula('SUM(A2:A11)'), style1)
sheet1.write(12, 1, Formula('SUM(B2:B11)'), style1)
sheet1.write(12, 2, Formula('SUM(C2:C11)'), style1)
wb.save("Lab9.xls")
from xlwt.compat import xrange
if __name__ == '__main__':
# 注意这里的excel文件的后缀是xls 如果是xlsx打开会无效
wtPath = '/Users/yvetteyoung/Desktop/test.xls'
wtBook = xlwt.Workbook()
# 新增一个sheet
sheet = wtBook.add_sheet('test', cell_overwrite_ok=True)
# 写入数据头
headList = ['数据1', '数据2', '数据3']
rowIndex = 0
col = 0
# 循环写入
for head in headList:
sheet.write(rowIndex, col, head)
col = col + 1
# 写入10行随机数
for index in xrange(1, 11):
for col in xrange(1, 3):
data = random.randint(0, 99)
sheet.write(index, 0, index) # 写序号
sheet.write(index, col, data) # 写数据
print("写第[%d]行数据" % index)
# 保存
wtBook.save(wtPath)
def export_tracciato_xls(modeladmin, request, queryset):
response = HttpResponse(content_type='application/ms-excel')
response['Content-Disposition'] = 'attachment; filename=ListaAsset.xls'
wb = xlwt.Workbook(encoding='utf-8')
ws = wb.add_sheet("ListaAsset")
row_num = 0
columns = [
(u"asset", 7000),
(u"seriale", 7000),
(u"tipo dispositivo", 7000),
(u"produttore", 7000),
(u"modello", 7000),
(u"data installazione", 7000),
(u"data dismissione", 7000),
(u"fine garanzia", 7000),
(u"assegnatario", 7000),
(u"location", 7000),
(u"palazzo", 7000),
(u"piano", 7000),
(u"stanza", 7000),
]
font_style = xlwt.XFStyle()
font_style.font.bold = True
for col_num in xrange(len(columns)):
ws.write(row_num, col_num, columns[col_num][0], font_style)
# set column width
ws.col(col_num).width = columns[col_num][1]
font_style = xlwt.XFStyle()
font_style.alignment.wrap = 1
for objects in queryset:
row_num += 1
if (objects.data_installazione):
dataInstallazione = objects.data_installazione.strftime('%d-%m-%Y')
else:
dataInstallazione = ""
if (objects.data_dismissione):
dataDismissione = objects.data_dismissione.strftime('%d-%m-%Y')
else:
dataDismissione = ""
if (objects.fine_garanzia):
fineGaranzia = objects.fine_garanzia.strftime('%d-%m-%Y')
else:
fineGaranzia = ""
row = [
objects.asset,
objects.seriale,
objects.tipo_dispositivo.tipo_dispositivo,
objects.produttore.produttore,
objects.modello.modello,
dataInstallazione,
dataDismissione,
fineGaranzia,
objects.utente.__str__(),
objects.location,
objects.palazzo,
objects.piano,
objects.stanza,
# 'asset', 'seriale', 'tipo_dispositivo', 'produttore', 'modello', 'data_installazione', 'data_dismissione',
# 'fine_garanzia', 'utente', 'location'
]
for col_num in xrange(len(row)):
ws.write(row_num, col_num, row[col_num], font_style)
wb.save(response)
return response