def save_excel(excel_name, head, body):
wb = xlwt.Workbook(encoding='utf-8')
ws = wb.add_sheet("itjuzi")
style0 = xlwt.easyxf('font: name Times New Roman, color-index red, bold on',
num_format_str='#,##0.00')
style1 = xlwt.easyxf(num_format_str='D-MMM-YY')
for headColumn, headValue in enumerate(head):
ws.write(0, headColumn, headValue)
for row, rowData in enumerate(body):
for column, columnData in enumerate(rowData):
ws.write(row+1, column, columnData)
wb.save(excel_name)
print("Output Excel File Saved!")
def insertSum2(sheet,keylist,count,sdict,index):
tstyle = xlwt.easyxf('pattern: pattern solid, fore_colour blue_gray; font: bold on,name SimSun,height 200,'
'colour_index 1;borders: left thin,top thin,right thin,bottom thin;align: horiz center,vert center')
sstyle = xlwt.easyxf('pattern: pattern solid,fore_colour white;font:name SimSun,height 200;'
'borders:left thin,top thin,right thin,bottom thin;align: horiz center,vert center')
slist=sorted(sdict.keys(), key=lambda d:d)
for key in slist:
item = sdict[key]
#写入合计文本
colindex = index
col1 = item[keylist[0]]
sheet.write_merge(count,count,0,colindex-1,col1,tstyle)
#写入合计数值
for j in range(1,len(keylist)):
k = keylist[j]
if k in item:
val = item[k]
sheet.write(count,j,val,sstyle)
else:
sheet.write(count,j,'',sstyle)
count += 1
return count
def __SaveResultToExcel(self):
try:
if not os.path.exists(self.pathResult):
raise Exception('No such directory: "%s"' %self.pathResult)
if self.result:
description = 'Frequency Dictionary'
style = xlwt.easyxf('font: name Times New Roman')
wb = xlwt.Workbook()
ws = wb.add_sheet(description + ' ' + self.countWord)
nRow = 0
for item in self.result:
ws.write(nRow, 0, item[0], style)
ws.write(nRow, 1, item[1], style)
ws.write(nRow, 2, item[2], style)
nRow +=1
wb.save(os.path.join(self.pathResult, description +'.xls'))
except Exception as e:
print(e)
def insertTitle2(sheet,titles,keyList,widthList):
#设置导出文件标题样式
tstyle = xlwt.easyxf('pattern: pattern solid, fore_colour blue_gray; font: bold on,name SimSun,height 230,'
'colour_index 1;borders: left thin,top thin,right thin,bottom thin;align: horiz center,vert center')
#设置行高
sheet.row(0).height_mismatch = True
sheet.row(0).height =500
#设置列宽
if widthList:
for i in range(len(widthList)):
sheet.col(i).width = 0x0d00 + widthList[i]
for i in range(len(titles)):
trow = titles[i]
if keyList:
for j in range(len(keyList)):
sheet.write(i,j,"",tstyle)
for col in trow:
#写入导出文件标题
sheet.write_merge(i,i+col[2]-1,col[1],col[1]+col[3]-1,str(col[0]),tstyle)
def insertCell2(sheet,count,datalist,keylist,dictlist):
#设置商品不存在时提示信息的样式
sstyle = xlwt.easyxf('pattern: pattern solid,fore_colour white;font:name SimSun,height 200;'
'borders:left thin,top thin,right thin,bottom thin;align: horiz center,vert center')
#根据商品条码查询商品信息
for i in range(0,len(datalist)):
row = datalist[i]
#数据为list、tuple
if isinstance(row,list) or isinstance(row,tuple):
for j in range(len(row)):
cell = row[j]
dt = None
if dictlist:
dt = dictlist[j]
if dt:
#根据key取value
sheet.write(count,j,dt[str(cell)],sstyle)
else:
sheet.write(count,j,cell,sstyle)
elif isinstance(row,dict):
#数据为字典
for j in range(len(keylist)):
dt = None
if dictlist:
dt = dictlist[j]
key = keylist[j]
cell = row.get(key)
if isinstance(cell,str):
cell = cell.strip()
if dt:
#根据key取value
sheet.write(count,j,dt[str(cell)],sstyle)
else:
sheet.write(count,j,cell,sstyle)
count += 1
# sheet.row(i).hidden=1 隐藏行
return count
sheet1.write(4, 0, "Stimulus Time")
sheet1.write(4, 1, "Reaction Time")
i=4
for n in list1:
i = i+1
sheet1.write(i, 0, n)
book.save("trial.xls")
"""
import xlwt3 as xlwt
from datetime import datetime
style0 = xlwt.easyxf("font: name Times New Roman, color-index red, bold on", num_format_str="#,##0.00")
style1 = xlwt.easyxf(num_format_str="D-MMM-YY")
wb = xlwt.Workbook()
ws = wb.add_sheet("A Test Sheet")
ws.write(0, 0, 1234.56, style0)
ws.write(1, 0, datetime.now(), style1)
ws.write(2, 0, 1)
ws.write(2, 1, 1)
ws.write(2, 2, xlwt.Formula("A3+B3"))
wb.save("example.xls")
style = xlwt.XFStyle()
style.font = font
style.alignment = alignment
style.borders = borders
### Описание стиля 2(vertical)
# перенос по словам, выравнивание
alignment_v = xlwt.Alignment()
alignment_v.wrap = 1
alignment_v.horz = xlwt.Alignment.HORZ_CENTER # May be: HORZ_GENERAL, HORZ_LEFT, HORZ_CENTER, HORZ_RIGHT, HORZ_FILLED, HORZ_JUSTIFIED, HORZ_CENTER_ACROSS_SEL, HORZ_DISTRIBUTED
alignment_v.vert = xlwt.Alignment.VERT_CENTER # May be: VERT_TOP, VERT_CENTER, VERT_BOTTOM, VERT_JUSTIFIED, VERT_DISTRIBUTED
alignment_v.rota = 90
# шрифт
font_v = xlwt.Font()
font_v.name = 'Arial Cyr'
font_v.bold = True
# границы
borders_v = xlwt.Borders()
borders_v.left = xlwt.Borders.THIN # May be: NO_LINE, THIN, MEDIUM, DASHED, DOTTED, THICK, DOUBLE, HAIR, MEDIUM_DASHED, THIN_DASH_DOTTED, MEDIUM_DASH_DOTTED, THIN_DASH_DOT_DOTTED, MEDIUM_DASH_DOT_DOTTED, SLANTED_MEDIUM_DASH_DOTTED, or 0x00 through 0x0D.
borders_v.right = xlwt.Borders.THIN
borders_v.top = xlwt.Borders.THIN
borders_v.bottom = xlwt.Borders.THIN
# Создаём стиль с нашими установками
style_v = xlwt.XFStyle()
style_v.font = font_v
style_v.alignment = alignment_v
style_v.borders = borders_v
### Описание стиля 3(Editable cells)
np_style = xlwt.easyxf("protection: cell_locked false")
def main():
style0 = xlwt3.easyxf('font: name Times New Roman, color-index red, bold on',
num_format_str='#,##0.00')
style1 = xlwt3.easyxf(num_format_str='D-MMM-YY')
wb = xlwt3.Workbook()
ws = wb.add_sheet('A Test Sheet')
ws.write(0, 0, 1234.56, style0)
ws.write(1, 0, datetime.now(), style1)
ws.write(2, 0, 1)
ws.write(2, 1, 1)
ws.write(2, 2, xlwt3.Formula("A3+B3"))
wb.save('example.xls')
def main():
style0 = xlwt3.easyxf(
'font: name Times New Roman, color-index red, bold on',
num_format_str='#,##0.00')
style1 = xlwt3.easyxf(num_format_str='D-MMM-YY')
wb = xlwt3.Workbook()
ws = wb.add_sheet('A Test Sheet')
ws.write(0, 0, 1234.56, style0)
ws.write(1, 0, datetime.now(), style1)
ws.write(2, 0, 1)
ws.write(2, 1, 1)
ws.write(2, 2, xlwt3.Formula("A3+B3"))
wb.save('example.xls')
def __init__(self, dataList, path="output.xls"):
#print("\n\n\n", dataList)
farmsLastRows={}
farmsSheets={}
wb = xlwt.Workbook()
for rowlist in dataList:
if not (rowlist[-1] in farmsSheets):
farmsSheets[rowlist[-1]] = wb.add_sheet(rowlist[-1])
style = xlwt.easyxf('pattern: pattern solid, fore_colour gray40; borders: top thin, bottom thin, left thin, right thin;')
farmsSheets[rowlist[-1]].write(0, 0, "Наименование", style)
farmsSheets[rowlist[-1]].write(0, 1, "Цена", style)
farmsSheets[rowlist[-1]].write(0, 2, "Кол-во", style)
farmsSheets[rowlist[-1]].write(0, 3, "Стоимость", style)
farmsSheets[rowlist[-1]].write(0, 4, "Срок годности", style)
farmsSheets[rowlist[-1]].write(0, 5, "Производитель", style)
farmsSheets[rowlist[-1]].col(0).width = 0x4d00
farmsSheets[rowlist[-1]].col(1).width = 0x0A00
farmsSheets[rowlist[-1]].col(2).width = 0x0800
farmsSheets[rowlist[-1]].col(3).width = 0x0A00
farmsSheets[rowlist[-1]].col(4).width = 0x0F00
farmsSheets[rowlist[-1]].col(5).width = 0x2d00
farmsLastRows[rowlist[-1]]=1
farmsSheets[rowlist[-1]].write(farmsLastRows[rowlist[-1]], 0, rowlist[0])
farmsSheets[rowlist[-1]].write(farmsLastRows[rowlist[-1]], 1, rowlist[1].replace('.', ','))
farmsSheets[rowlist[-1]].write(farmsLastRows[rowlist[-1]], 2, rowlist[2])
index = (farmsLastRows[rowlist[-1]]+1,)*2
formula="B%i*C%i" % index
farmsSheets[rowlist[-1]].write(farmsLastRows[rowlist[-1]], 3, xlwt.Formula(formula))
farmsSheets[rowlist[-1]].write(farmsLastRows[rowlist[-1]], 4, rowlist[4])
farmsSheets[rowlist[-1]].write(farmsLastRows[rowlist[-1]], 5, rowlist[5])
farmsLastRows[rowlist[-1]]+=1
try:
wb.save(path)
except (PermissionError, FileExistsError, FileNotFoundError) as er:
QtGui.QMessageBox.critical(None,"Export error!",str(er), QtGui.QMessageBox.Ok)
else:
bt = QtGui.QMessageBox.information(None,"Экспорт","OK. Сохранение в " + path, QtGui.QMessageBox.Close, QtGui.QMessageBox.Open)
if bt == QtGui.QMessageBox.Open:
if sys.platform == "win32":
os.startfile(path)
else:
subprocess.Popen(['xdg-open', path])
def makeXls(self,tableTitle,outputFilename,tableSubtitle=None):
nLevels=len(self.levelColLists)
wb=xlwt.Workbook()
ws=wb.add_sheet('expenditures')
styleTableTitle=xlwt.easyxf('font: bold on, height 240')
styleHeader=xlwt.easyxf('font: bold on; align: wrap on')
styleThinHeader=xlwt.easyxf('font: bold on, height 180; align: wrap on')
styleVeryThinHeader=xlwt.easyxf('font: height 140; align: wrap on')
styleStandard=xlwt.easyxf('')
styleShallowStandard=xlwt.easyxf('font: bold on')
styleAmount=xlwt.easyxf(num_format_str='#,##0.0;-#,##0.0;""')
styleShallowAmount=xlwt.easyxf('font: bold on',num_format_str='#,##0.0;-#,##0.0;""')
codeColumnsData={
'departmentCode': {'text':'Код ведомства', 'width':4, 'headerStyle':styleVeryThinHeader, 'cellStyle':styleStandard,'shallowCellStyle':styleShallowStandard},
'superSectionCode': {'text':'Код надраздела', 'width':5, 'headerStyle':styleThinHeader, 'cellStyle':styleStandard,'shallowCellStyle':styleShallowStandard},
'sectionCode': {'text':'Код раздела', 'width':5, 'headerStyle':styleThinHeader, 'cellStyle':styleStandard,'shallowCellStyle':styleShallowStandard},
'categoryCode': {'text':'Код целевой статьи', 'width':8, 'headerStyle':styleThinHeader, 'cellStyle':styleStandard,'shallowCellStyle':styleShallowStandard},
'typeCode': {'text':'Код вида расходов', 'width':4, 'headerStyle':styleVeryThinHeader, 'cellStyle':styleStandard,'shallowCellStyle':styleShallowStandard},
}
columns=[
{'text':'№ в приложении '+str(appendix),'width':10,'headerStyle':styleThinHeader,'cellStyle':styleStandard,'shallowCellStyle':styleShallowStandard} for appendix in self.yearsInAppendices
]+[
{'text':'Наименование','width':100,'headerStyle':styleHeader,'cellStyle':styleStandard,'shallowCellStyle':styleShallowStandard}
]+[
codeColumnsData[col] for cols in self.levelColLists for col in cols
]+[
{'text':[f(v) for f,v in zip(self.fakeYearNameFns,year)],'width':15,'headerStyle':styleHeader,'cellStyle':styleAmount,'shallowCellStyle':styleShallowAmount} for year in self.years
]
ws.set_panes_frozen(True)
ws.set_horz_split_pos(self.nHeaderRows)
ws.row(0).height=400
ws.merge(0,0,0,len(columns)-1)
ws.write(0,0,tableTitle,styleTableTitle)
if tableSubtitle:
ws.merge(1,1,0,len(columns)-1)
ws.write(1,0,tableSubtitle)
for i in range(self.nHeaderRows-len(self.fakeYearNameFns),self.nHeaderRows):
ws.row(i).height=1200//len(self.fakeYearNameFns)
def setCellWidth(nCol,width):
ws.col(nCol).width=256*width
def writeMergedCellText(nRow1,nCol1,nRow2,nCol2,text,style):
ws.merge(nRow1,nRow2,nCol1,nCol2)
ws.write(nRow1,nCol1,text,style)
self.makeSheetHeader(columns,setCellWidth,ws.write,writeMergedCellText)
for nRow,row in enumerate(self.outRows):
for nCol,(cell,col) in enumerate(zip(row,columns)):
shallow=self.levels[nRow]<nLevels//2
style=col['shallowCellStyle' if shallow else 'cellStyle']
if cell is None:
continue
elif type(cell) is str and cell[0]=='=':
ws.write(self.nHeaderRows+nRow,nCol,xlwt.Formula(cell[1:]),style)
else:
ws.write(self.nHeaderRows+nRow,nCol,cell,style)
wb.save(outputFilename)
def save_file(f, test):
style = 'pattern: pattern solid, fore_colour yellow; '
style += 'font: bold on; '
style += 'align: horz centre, vert center; '
header_style = xlwt3.easyxf(style)
for page_num in range(10):
page_url = get_url(page_num)
html = get_html(page_url)
items = get_info(html)
write_info(items, page_num * 25 + 1, test)
for i in range(0, len(row0)):
test.write(0, i, row0[i], header_style)
f.save('movie250.xls')
def createSheet(self, table_data, ws):
mystyle = xlwt3.easyxf(
'''borders: left thin, right thin, top thin, bottom thin;
pattern: pattern solid, fore_colour yellow;''')
for counter_row, row_data in enumerate(table_data):
for counter_column, field_data in enumerate(row_data):
if counter_row == 0:
ws.write(counter_row, counter_column, field_data.title(),
mystyle)
else:
cell_style = xlwt3.XFStyle()
if isinstance(field_data, datetime):
cell_style.num_format_str = 'dd/mm/yyyy'
ws.write(counter_row, counter_column, field_data,
cell_style)
def __SaveResultToExcel(self):
try:
if not os.path.exists(self.pathResult):
raise Exception('No such directory: "%s"' % self.pathResult)
if self.result:
description = 'Frequency Dictionary'
style = xlwt.easyxf('font: name Times New Roman')
wb = xlwt.Workbook()
ws = wb.add_sheet(description + ' ' + self.countWord)
nRow = 0
for item in self.result:
ws.write(nRow, 0, item[0], style)
ws.write(nRow, 1, item[1], style)
ws.write(nRow, 2, item[2], style)
nRow += 1
wb.save(os.path.join(self.pathResult, description + '.xls'))
except Exception as e:
print(e)
import xlwt3
if __name__ == '__main__':
datas = [['a', 'b', 'c'], ['d', 'e', 'f'], ['g', 'h']] # 二维数组
file_path = '/Users/vpjacob/Desktop/Demo/03-Python\ 股票'
wb = xlwt3.Workbook()
sheet = wb.add_sheet('test') # sheet的名称为test
# 单元格的格式
style = 'pattern: pattern solid, fore_colour yellow; ' # 背景颜色为黄色
style += 'font: bold on; ' # 粗体字
style += 'align: horz centre, vert center; ' # 居中
header_style = xlwt3.easyxf(style)
row_count = len(datas)
col_count = len(datas[0])
for row in range(0, row_count):
col_count = len(datas[row])
for col in range(0, col_count):
if row == 0: # 设置表头单元格的格式
sheet.write(row, col, datas[row][col], header_style)
else:
sheet.write(row, col, datas[row][col])
wb.save(file_path)
sheet1.write(4, 0, "Stimulus Time")
sheet1.write(4, 1, "Reaction Time")
i=4
for n in list1:
i = i+1
sheet1.write(i, 0, n)
book.save("trial.xls")
'''
import xlwt3 as xlwt
from datetime import datetime
style0 = xlwt.easyxf('font: name Times New Roman, color-index red, bold on',
num_format_str='#,##0.00')
style1 = xlwt.easyxf(num_format_str='D-MMM-YY')
wb = xlwt.Workbook()
ws = wb.add_sheet('A Test Sheet')
ws.write(0, 0, 1234.56, style0)
ws.write(1, 0, datetime.now(), style1)
ws.write(2, 0, 1)
ws.write(2, 1, 1)
ws.write(2, 2, xlwt.Formula("A3+B3"))
wb.save('example.xls')
'''
import xlwt3 as xlwt
from lightapp import utils
NUM_COLS = 5 # Columns will be 0 indexed, so set it to one less
### STYLE CONSTANTS ###
#######################
MAIN_HEADER_BORDERS = xlwt.Borders()
MAIN_HEADER_BORDERS.left = xlwt.Borders.MEDIUM
MAIN_HEADER_BORDERS.right = xlwt.Borders.MEDIUM
MAIN_HEADER_BORDERS.top = xlwt.Borders.MEDIUM
MAIN_HEADER_STYLE = \
xlwt.easyxf('font: bold on, height 0x00F0; '
'align: wrap on, vert centre, horiz center')
MAIN_HEADER_STYLE.borders = MAIN_HEADER_BORDERS
SLOT_HEADER_BORDERS = xlwt.Borders()
SLOT_HEADER_BORDERS.top = xlwt.Borders.MEDIUM
SLOT_HEADER_BORDERS.left = xlwt.Borders.MEDIUM
SLOT_HEADER_BORDERS.right = xlwt.Borders.MEDIUM
SLOT_HEADER_STYLE = \
xlwt.easyxf('font: bold on; align: wrap on, vert centre, horiz center')
SLOT_HEADER_STYLE.borders = SLOT_HEADER_BORDERS
SINGLE_CELL_STYLE = xlwt.easyxf('align: horiz center')
def _get_borders(row, col, last_row):
total = len(project_list)
if total == 0:
print('No project name found in "' + filename + '", please check and input the agenda file name: ')
filename = input().strip()
continue
print(str(total) + " projects in total.")
break
except exception as e:
filename = input().strip()
description_needed = True
log = open(log, "w")
wb = xlwt.Workbook()
ws = wb.add_sheet("Result")
strStyle = xlwt.easyxf("font: name Calibri")
dateStyle = xlwt.easyxf("font: name Calibri", num_format_str="YY/MM/DD")
ws.write(0, 0, "Project Id", strStyle)
ws.write(0, 1, "Status", strStyle)
ws.write(0, 2, "Start Date", strStyle)
ws.write(0, 3, "Due Date", strStyle)
ws.write(0, 4, "Countries involved", strStyle)
ws.write(0, 5, "Countries done", strStyle)
ws.write(0, 6, "Countries staged", strStyle)
ws.write(0, 7, "In progress", strStyle)
ws.write(0, 8, "Idiom", strStyle)
ws.write(0, 9, "Description", strStyle)
ws.write(0, 10, "Notes", strStyle)
ws.write(0, 11, "Priority", strStyle)
ws.write(0, 12, "Link", strStyle)
def writeXls(self,filename,delta,stairs,useSums=True):
wb=xlwt.Workbook()
# ws=wb.add_sheet('Ведомственная структура расходов') # can't use Russian?
ws=wb.add_sheet('expenditures')
metadata=self.makeColumnsMetadata(stairs)
# styles
styleDocumentTitle=xlwt.easyxf('font: height 240')
styleTableTitle=xlwt.easyxf('font: bold on, height 240')
styleHeader=xlwt.easyxf('font: bold on; align: wrap on')
styleThinHeader=xlwt.easyxf('font: bold on, height 180; align: wrap on')
styleVeryThinHeader=xlwt.easyxf('font: height 140; align: wrap on')
# для русского в числовом формате НУЖНО писать ',' вместо ' ' и '.' вместо ','
if delta:
styleAmount=xlwt.easyxf(num_format_str="+#,##0.0;-#,##0.0;0.0")
else:
styleAmount=xlwt.easyxf(num_format_str='#,##0.0')
# table header
nHeaderRows=3
ws.set_panes_frozen(True)
ws.set_horz_split_pos(nHeaderRows)
ws.row(0).height=ws.row(1).height=400
ws.merge(0,0,0,len(metadata)-1)
ws.write(0,0,self.documentTitle,styleDocumentTitle)
ws.merge(1,1,0,len(metadata)-1)
ws.write(1,0,self.tableTitle,styleTableTitle)
# columns header
ws.row(nHeaderRows-1).height=1200
columnTypes={
'number': {'width':10, 'style':styleHeader},
'name': {'width':100, 'style':styleHeader},
'section': {'width':5, 'style':styleThinHeader},
'article': {'width':8, 'style':styleThinHeader},
'type': {'width':4, 'style':styleVeryThinHeader},
'OSGU': {'width':4, 'style':styleVeryThinHeader},
'econ': {'width':4, 'style':styleVeryThinHeader},
}
amountType= {'width':15, 'style':styleHeader}
skip=0
for i,m in enumerate(metadata):
if skip>0:
skip-=1
continue
r=nHeaderRows-1
if m['span']>1:
ws.merge(r,r,i,i+m['span']-1)
skip=m['span']-1
if m['id'] in columnTypes:
ct=columnTypes[m['id']]
ws.col(i).width=256*ct['width']
style=ct['style']
elif type(m['id']) is tuple:
ws.col(i).width=256*amountType['width']
style=amountType['style']
ws.write(r,i,m['text'],style)
# data
class Writer:
def __init__(self):
self.row=nHeaderRows # private
def writerow(self,cells):
for i,cell in enumerate(cells):
if cell is None:
continue
if type(metadata[i]['id']) is tuple:
if cell[0]=='=':
ws.write(self.row,i,xlwt.Formula(cell[1:]),styleAmount)
else:
ws.write(self.row,i,float(cell.replace(',','.')),styleAmount)
else:
ws.write(self.row,i,cell)
self.row+=1
self.write(Writer(),stairs,useSums,nHeaderRows+1)
wb.save(filename)
total=len(project_list)
if total==0:
print('No project name found in "'+filename+'", please check and input the agenda file name: ')
filename=input().strip()
continue
print(str(total)+' projects in total.')
break
except exception as e:
filename=input().strip()
description_needed=True
log=open(log,'w')
wb = xlwt.Workbook()
ws = wb.add_sheet('Result')
strStyle = xlwt.easyxf('font: name Calibri')
dateStyle = xlwt.easyxf('font: name Calibri',num_format_str='YY/MM/DD')
ws.write(0, 0, 'Project Id', strStyle)
ws.write(0, 1, 'Status', strStyle)
ws.write(0, 2, 'Start Date', strStyle)
ws.write(0, 3, 'Due Date', strStyle)
ws.write(0, 4, 'Countries involved', strStyle)
ws.write(0, 5, 'Countries done', strStyle)
ws.write(0, 6, 'Countries staged', strStyle)
ws.write(0, 7, 'In progress', strStyle)
ws.write(0, 8, 'Idiom', strStyle)
ws.write(0, 9, 'Description', strStyle)
ws.write(0, 10, 'Notes', strStyle)
ws.write(0, 11, 'Priority', strStyle)
ws.write(0, 12, 'Link', strStyle)
def makeXls(self, tableTitle, outputFilename, tableSubtitle=None):
nLevels = len(self.levelColLists)
wb = xlwt.Workbook()
ws = wb.add_sheet('expenditures')
styleTableTitle = xlwt.easyxf('font: bold on, height 240')
styleHeader = xlwt.easyxf('font: bold on; align: wrap on')
styleThinHeader = xlwt.easyxf(
'font: bold on, height 180; align: wrap on')
styleVeryThinHeader = xlwt.easyxf('font: height 140; align: wrap on')
styleStandard = xlwt.easyxf('')
styleShallowStandard = xlwt.easyxf('font: bold on')
styleAmount = xlwt.easyxf(num_format_str='#,##0.0;-#,##0.0;""')
styleShallowAmount = xlwt.easyxf('font: bold on',
num_format_str='#,##0.0;-#,##0.0;""')
codeColumnsData = {
'departmentCode': {
'text': 'Код ведомства',
'width': 4,
'headerStyle': styleVeryThinHeader,
'cellStyle': styleStandard,
'shallowCellStyle': styleShallowStandard
},
'superSectionCode': {
'text': 'Код надраздела',
'width': 5,
'headerStyle': styleThinHeader,
'cellStyle': styleStandard,
'shallowCellStyle': styleShallowStandard
},
'sectionCode': {
'text': 'Код раздела',
'width': 5,
'headerStyle': styleThinHeader,
'cellStyle': styleStandard,
'shallowCellStyle': styleShallowStandard
},
'categoryCode': {
'text': 'Код целевой статьи',
'width': 8,
'headerStyle': styleThinHeader,
'cellStyle': styleStandard,
'shallowCellStyle': styleShallowStandard
},
'typeCode': {
'text': 'Код вида расходов',
'width': 4,
'headerStyle': styleVeryThinHeader,
'cellStyle': styleStandard,
'shallowCellStyle': styleShallowStandard
},
}
columns = [{
'text': '№ в приложении ' + str(appendix),
'width': 10,
'headerStyle': styleThinHeader,
'cellStyle': styleStandard,
'shallowCellStyle': styleShallowStandard
} for appendix in self.yearsInAppendices] + [
{
'text': 'Наименование',
'width': 100,
'headerStyle': styleHeader,
'cellStyle': styleStandard,
'shallowCellStyle': styleShallowStandard
}
] + [
codeColumnsData[col] for cols in self.levelColLists for col in cols
] + [{
'text': [f(v) for f, v in zip(self.fakeYearNameFns, year)],
'width': 15,
'headerStyle': styleHeader,
'cellStyle': styleAmount,
'shallowCellStyle': styleShallowAmount
} for year in self.years]
ws.set_panes_frozen(True)
ws.set_horz_split_pos(self.nHeaderRows)
ws.row(0).height = 400
ws.merge(0, 0, 0, len(columns) - 1)
ws.write(0, 0, tableTitle, styleTableTitle)
if tableSubtitle:
ws.merge(1, 1, 0, len(columns) - 1)
ws.write(1, 0, tableSubtitle)
for i in range(self.nHeaderRows - len(self.fakeYearNameFns),
self.nHeaderRows):
ws.row(i).height = 1200 // len(self.fakeYearNameFns)
def setCellWidth(nCol, width):
ws.col(nCol).width = 256 * width
def writeMergedCellText(nRow1, nCol1, nRow2, nCol2, text, style):
ws.merge(nRow1, nRow2, nCol1, nCol2)
ws.write(nRow1, nCol1, text, style)
self.makeSheetHeader(columns, setCellWidth, ws.write,
writeMergedCellText)
for nRow, row in enumerate(self.outRows):
for nCol, (cell, col) in enumerate(zip(row, columns)):
shallow = self.levels[nRow] < nLevels // 2
style = col['shallowCellStyle' if shallow else 'cellStyle']
if cell is None:
continue
elif type(cell) is str and cell[0] == '=':
ws.write(self.nHeaderRows + nRow, nCol,
xlwt.Formula(cell[1:]), style)
else:
ws.write(self.nHeaderRows + nRow, nCol, cell, style)
wb.save(outputFilename)
