def __init__(self, template_path):
self.notformatbook = open_workbook(template_path)
self.sheetslen = len(self.notformatbook.sheets())
self.rbook = open_workbook(template_path, formatting_info=True)
self.styles = Styles(self.rbook)
self.conf = {}
def test_multiple_names_for_xfi_bad_1(self):
self.wb.style_name_map = {
'A': (0, 0),
'B': (0, 0),
}
with ShouldRaise(AssertionError()):
Styles(self.wb)
def createSkeleton(ps, filename):
newfilename = filename.split('.')[0] + '_oferta_claro.xls'
originwb = xlrd.open_workbook(ps, formatting_info=True)
styles = Styles(originwb)
rs = originwb.sheet_by_index(0)
destinationwb = xlutils.copy.copy(originwb)
xl_sheet = destinationwb.get_sheet(0)
destinationwb.save(newfilename)
return newfilename
def test_multiple_names_for_xfi_okay(self):
# setup our mock workbooks
self.wb.style_name_map = {
'': (0, 0),
'Normal': (1, 0),
}
# process it into styles
s = Styles(self.wb)
# now lookup a "cell" with xfi 0
cell = Mock()
cell.xf_index = 1
self.assertEqual(s[cell].name, 'Normal')
def __init__(self, config, xlsFileName):
self.log = logging.getLogger("Mess")
# Save a pointer to the configuration
self.config = config
# Load the workbook
self.log.debug('Loading Excel file {0}'.format(xlsFileName))
self.workbook = open_workbook(xlsFileName,
formatting_info=True,
on_demand=True)
self.styles = Styles(self.workbook)
print self.workbook.style_name_map
print self.workbook.xf_list
def read_excel():
wb = xlrd.open_workbook(filename="test.xls", formatting_info=True) #打开文件
style = Styles(wb)
sheet = wb.sheet_by_index(0)
cell = sheet.cell(0, 0)
print("cell.xf_index is %d" % cell.xf_index)
fmt = wb.xf_list[cell.xf_index]
print("type(fmt) is %s" % type(fmt))
print("fmt.dump():")
fmt.dump()
# https://xlutils.readthedocs.io/en/latest/styles.html
print(style[sheet.cell(0, 0)])
print(style[sheet.cell(0, 0)].xf)
def writeCaseInfoList(self, sheet_name, CCell_list = []): # (值， 行， 列)
'''写入check结果。本这方法的缺点是: 不能保存Formulae, Names, anything ignored by xlrd'''
from xlrd import open_workbook, XLRDError
from xlutils.copy import copy
from xlwt import easyxf, XFStyle, Font
from mmap import mmap, ACCESS_READ
if CCell_list == []:
return
#from mmap import mmap, ACCESS_READ
with open(self.__path,'rb') as f:
rb = open_workbook(file_contents=mmap(f.fileno(), 0, access=ACCESS_READ), \
formatting_info = True)
# 取得sheet index
try:
#print rb.sheet_names()
#print sheet_name
sheetx = rb.sheet_names().index(sheet_name)
except ValueError:
common.Logger.CLogger.instance().\
logger('CExcel').warning("No sheet named <%s>.", sheet_name)
#raise XLRDError('No sheet named <%r>' % sheet_name)
# 通过xlutils.copy转换为可以写的格式
wb = copy(rb)
from xlutils.styles import Styles
styles = Styles(rb)
#print sheet_name, sheetx
# 通过序号获取的sheet
ws = wb.get_sheet(sheetx)
rs = rb.sheet_by_index(sheetx)
for objCCell in CCell_list:
row, col, value = objCCell.getCellValue()
org_style = styles[rs.cell(row, col)]
org_font = rb.font_list[org_style.xf.font_index]
style = self._getStyle(org_style, org_font)
ws.write(row, col, value, style)
print dir(ws)
#print row, col, value
wb.save(self.__path)
return 0
def down_report_excel(self, cr, uid, params, context=None):
report_name = params['report_name']
report_title = params['report_title']
#the data will update to excel: {'code':{'fieldname1':fieldvalue1,'fieldname2':fieldvalue2}}
report_data = params['report_data']
#template file dara or file name
tmpl_file_data = params.get('tmpl_file_data', False)
tmpl_file_name = params.get('tmpl_file_name', False)
#dc_prefix: data cell prefix, data cell format: dc_prefix:fieldname@code
dc_prefix = params['dc_prefix']
#1.get excel template data
if tmpl_file_data:
tmpl_file_data = base64.decodestring(tmpl_file_data)
elif tmpl_file_name:
cur_path = os.path.split(os.path.realpath(__file__))[0]
tmpl_file_path = os.path.join(cur_path, tmpl_file_name)
tmpl_file_data = open(tmpl_file_path, 'rb').read()
if not tmpl_file_data:
raise osv.except_osv(
_('Error'), _('No file data found for down_report_excel()!'))
#2.read template data, and write the data cell
wb_rd = xlrd.open_workbook(file_contents=tmpl_file_data,
formatting_info=True,
on_demand=True)
sheet_rd = wb_rd.sheets()[0]
#for write
wb_wt = copy(wb_rd)
sheet_wt = wb_wt.get_sheet(0)
row_cnt = sheet_rd.nrows
styles = Styles(wb_rd)
#loop to check rows one by one
lang = self.pool.get('res.users').browse(cr, uid, uid,
context=context).lang
lang_obj = self.pool.get('res.lang')
lang_id = lang_obj.search(cr, uid, [('code', '=', lang)])[0]
for i in range(0, row_cnt):
col_cnt = sheet_rd.row_len(i)
for j in range(0, col_cnt):
field_val = sheet_rd.cell(i, j).value
#cell data format: "rpt:balance@bscn_1_2"
if isinstance(field_val, type(u' ')) and field_val.startswith(
dc_prefix) and field_val.find('@') > 0:
#find the report code and field name
rpt_flag = field_val[field_val.index(dc_prefix) +
len(dc_prefix):]
field_name, rpt_code = rpt_flag.split('@')
#write data to the writable sheet
rpt_fld_val = 0.0
if rpt_code and report_data.get(rpt_code, False):
rpt_fld_val = report_data[rpt_code].get(
field_name, 0.0)
# print sheet_rd.cell(i,j)
# print sheet_rd.cell(i,j).xf_index
# print styles[sheet_rd.cell(i,j)]
# print styles.cell_styles[sheet_rd.cell(i,j).xf_index]
style_wt = self.style_rd2wt(wb_rd,
styles[sheet_rd.cell(i, j)].xf)
if rpt_fld_val != 0.0:
rpt_fld_val = lang_obj.format(cr,
uid, [lang_id],
'%.2f',
rpt_fld_val,
grouping=True,
context=context)
sheet_wt.write(i, j, rpt_fld_val, style_wt)
else:
sheet_wt.write(i, j, '', style_wt)
#output the file
buf = cStringIO.StringIO()
wb_wt.save(buf)
filedata = base64.encodestring(buf.getvalue())
buf.close()
#goto file download page
file_ext = tmpl_file_name.split('.')
file_ext = file_ext[len(file_ext) - 1]
return self.pool.get('file.down').download_data(
cr, uid, "%s.%s" % (report_title, file_ext), filedata, context)
headerStyle = xlwt.easyxf(
'alignment: horiz centre, wrap on; font: name Calibri, bold on, height 220; borders: left thin, top thin, bottom thin, right thin'
)
generalStyle = xlwt.easyxf(
'alignment: horiz left; font: name Calibri, height 220; borders: left thin, top thin, bottom thin, right thin'
)
# i/o
va_file = open(va_path, "r")
va_reader = csv.reader(va_file, delimiter="\t")
xls_reader = xlrd.open_workbook(xls_path, formatting_info=True)
xls_sheet = xls_reader.sheet_by_index(0)
s = Styles(xls_reader)
final_xls = copy(xls_reader)
final_sheet = final_xls.get_sheet(0)
temoin_xls_workbook = xlwt.Workbook()
temoin_xls_sheet = temoin_xls_workbook.add_sheet('variants')
# header 1
header1 = xls_sheet.row(0)
final_sheet.row(0).write(len(header1), run_name, headerStyle)
temoin_xls_sheet.row(0).write(0, header1[0].value, headerStyle)
temoin_xls_sheet.row(0).write(1, header1[1].value, headerStyle)
temoin_xls_sheet.row(0).write(2, header1[2].value, headerStyle)
temoin_xls_sheet.row(0).write(3, header1[3].value, headerStyle)
nonerownumber = 0
for m, everyitem in list(enumerate(col_values_0)):
if "INT TIC" in everyitem:
nonerownumber = m
break
else:
nonerownumber = collength
RTID = col_values_RT[RTIDcolnumber + 1:nonerownumber]
#print(nonerownumber,RTIDcolnumber)
#print(RTID)
#创建对应的模板表格
workbook = xlrd.open_workbook(".\空白配方转化表FECHII_TEST.xls",
formatting_info=True)
table = workbook.sheet_by_index(0)
styles = Styles(workbook)
sheets_object = workbook.sheets()
sheet1_object = workbook.sheet_by_index(0)
col_values = sheet1_object.col_values(colx=9)
#print(col_values)
#collength = len(CAS_REAL)
new_excel = copy(workbook)
new_sheet = new_excel.get_sheet(0)
for index, everycas in list(enumerate(CAS_REAL)):
if everycas == '' or everycas.endswith("-00-0"):
if Cis_3_hexenyl_acetate_flag == 0:
new_sheet.write(index + 2, 8, LibID[index], style=styleOK)
new_sheet.write(index + 2, 0, index + 2, style=styleOK)
new_sheet.write(index + 2, 1, '', style=styleOK)
new_sheet.write(index + 2, 7, '', style=styleOK)
new_sheet.write(index + 2, 1, RTID[index], style=styleOK)
self.ignore_row = False
rdrowx, wtrowx = self.pending_row
self.next.row(rdrowx, wtrowx + self.wtrowxi)
elif not self.ignore_row:
self.next.cell(rdrowx, rdcolx, wtrowx + self.wtrowxi, wtcolx - 1)
process(Reader(), Filter(), Writer())
########NEW FILE########
__FILENAME__ = styles
from xlrd import open_workbook
from xlutils.styles import Styles
book = open_workbook('source.xls', formatting_info=True)
styles = Styles(book)
sheet = book.sheet_by_index(0)
print styles[sheet.cell(1, 1)].name
print styles[sheet.cell(1, 2)].name
A1_style = styles[sheet.cell(0, 0)]
A1_font = book.font_list[A1_style.xf.font_index]
print book.colour_map[A1_font.colour_index]
########NEW FILE########
__FILENAME__ = borders
from xlwt import Workbook, easyxf
tl = easyxf('border: left thick, top thick')
t = easyxf('border: top thick')
tr = easyxf('border: right thick, top thick')
def writePriceSheet(bom, siteList, ps, filename):
content_normal = xlwt.easyxf('font: bold off, color black;\
borders: top_color black, bottom_color black, right_color black, left_color black,\
left thin, right thin, top thin, bottom thin;\
pattern: pattern solid, fore_color white;\
align: horiz center;')
header_normal = xlwt.easyxf('font: bold True, color black;\
borders: top_color black, bottom_color black, right_color black, left_color black,\
left thin, right thin, top thin, bottom thin;\
pattern: pattern solid, fore_color white;\
align: horiz center;')
content_strong = xlwt.easyxf('font: bold True, color black;\
borders: top_color black, bottom_color black, right_color black, left_color black,\
left thick, right thick, top thick, bottom thick;\
pattern: pattern solid, fore_color white;\
align: horiz center;')
subTotal_stong = xlwt.easyxf('font: bold True, color black;\
borders: top_color black, top thin;\
pattern: pattern solid, fore_color white;')
granTotal_strong = xlwt.easyxf('font: bold True, color black;\
pattern: pattern solid, fore_color yellow;')
sites_red = xlwt.easyxf('font: bold off, color white, height 280;\
pattern: pattern solid, fore_color red;\
align: horiz center, vert centre')
gray_background = xlwt.easyxf('font: bold off, color white;\
pattern: pattern solid, fore_color gray40;\
align: horiz center')
title_strong = xlwt.easyxf('font: bold on, color black, height 280;\
borders: top_color black, bottom_color black, right_color black, left_color black,\
left thick, right thick, top thick, bottom thick;\
pattern: pattern solid, fore_color ice_blue;')
fix_border = xlwt.easyxf('font: bold True, color black;\
pattern: pattern solid, fore_color white;\
borders: top_color black, bottom_color black, right_color black, left_color black,\
left thick, right thick, top thick, bottom thick;\
align: horiz center, vert centre;')
# Intial values
site_id = []
granTotal = 0.0
subTotals = []
linesPerSite = []
# Create skeleton
newfilename = filename.split('.')[0] + '_oferta_claro.xls'
originwb = xlrd.open_workbook(ps, formatting_info=True)
styles = Styles(originwb)
rs = originwb.sheet_by_index(0)
destinationwb = xlutils.copy.copy(originwb)
xl_sheet = destinationwb.get_sheet(0)
# Get original BOM start and end
Ids = getIds(filename)
start_of_bom_id = Ids[0]
end_of_bom_id = Ids[1]
total_price_id = Ids[2]
xl_sheet.write_merge(1, 3, 4, 9, "Country (Project)", fix_border)
xl_sheet.write_merge(10, 10, 2, 4, "Cisco Systems", fix_border)
xl_sheet.row(1).height_mismatch = True
xl_sheet.row(1).height = 15 * 20
xl_sheet.row(2).height_mismatch = True
xl_sheet.row(2).height = 15 * 20
xl_sheet.row(3).height_mismatch = True
xl_sheet.row(3).height = 15 * 20
xl_sheet.row(10).height_mismatch = True
xl_sheet.row(10).height = 33 * 20
# Get each site ID
for i in range(start_of_bom_id, end_of_bom_id):
for site in siteList:
if site in bom[i]:
site_id.append(i)
# Get variables with site, siteID and endID
for i in range(len(siteList)):
site = siteList[i]
siteID = site_id[i]
try:
endID = site_id[i + 1]
except IndexError:
endID = end_of_bom_id
# Define offset for first Site is static
if i == 0:
offset = 13
# linesPerSite.append(offset)
# For others sites just increase the value leaving two spaces
else:
offset += 2
# Write the site name
xl_sheet.row(offset).height_mismatch = True
xl_sheet.row(offset).height = 19 * 19
xl_sheet.write_merge(offset, offset, 4, 8, site, sites_red)
offset += 1
xl_sheet.write_merge(offset, offset, 4, 8, '', gray_background)
# Add 5 spaces to offset the template
offset += 2
# Write part of the template
xl_sheet.row(offset).height_mismatch = True
xl_sheet.row(offset).height = 20 * 20
xl_sheet.write_merge(offset, offset, 1, 4, 'Hardware y/o Garantias',
title_strong)
offset += 2
# Write the headers
headers = [
'Item', '# de Parte del Fabricante', 'Codigo Sinergia',
'Descripcion Corta', 'Cantidad', 'Precio Unitario', 'Descuento',
'Precio Unitario con descuento', 'Total'
]
for i in range(len(headers)):
xl_sheet.write(offset, i + 1, headers[i], header_normal)
offset += 1
# Clear subTotal and Item index
subTotal = 0.0
item = 1
# Iterate within the range of items of each site
for i in range(siteID + 1, endID):
# print bom[i]
# Write each item
if bom[i][5] == 0:
continue
xl_sheet.write(offset, 1, item, content_normal)
xl_sheet.write(offset, 2, bom[i][1], content_normal)
xl_sheet.write(offset, 3, bom[i][10], content_normal)
xl_sheet.write(offset, 4, bom[i][11], content_normal)
xl_sheet.write(offset, 5, bom[i][6], content_normal)
xl_sheet.write(offset, 6, bom[i][5], content_normal)
xl_sheet.write(offset, 7, bom[i][8], content_normal)
xl_sheet.write(offset, 8, bom[i][7], content_normal)
xl_sheet.write(offset, 9, bom[i][9], content_normal)
offset += 1
item += 1
try:
subTotal += bom[i][9]
except:
pass
# Write Total for each site
xl_sheet.write(offset, 8, 'Total: ' + site, subTotal_stong)
xl_sheet.write(offset, 9, subTotal, subTotal_stong)
# Add the total to a tuple for resumen later
subTotals.append((site, subTotal))
# print "Sub Total:" + site +" "+ str(subTotal)
# Add the Total to the grand total for resumen later
granTotal += subTotal
# Write Resumen Table
offset = 6
for site in subTotals:
xl_sheet.write(offset, 13, 'Total: ' + site[0], content_strong)
xl_sheet.write(offset, 14, site[1], content_strong)
offset += 1
# Write Grand Total
offset += 1
xl_sheet.write(offset, 13, 'Total de la solucion', granTotal_strong)
xl_sheet.write(offset, 14, granTotal, granTotal_strong)
# print "Grand Total:" + str(granTotal)
# Save the pricesheet to the newfile
destinationwb.save(newfilename)
return newfilename
def xlswritter(bom, filename):
Ids = getIds(filename)
start_of_bom_id = Ids[0]
end_of_bom_id = Ids[1]
total_price_id = Ids[2]
# Copy the workbook to create a new one passing the formatting
newfilename = filename.split('.')[0] + '_oferta_cisco.xls'
originwb = xlrd.open_workbook(filename, formatting_info=True)
styles = Styles(originwb)
rs = originwb.sheet_by_index(0)
destinationwb = xlutils.copy.copy(originwb)
xl_sheet = destinationwb.get_sheet(0)
# Write the
# for i,cell in enumerate(rs.col(8)):
# if not i:
# continue
# print i
# # xl_sheet.write(row,column,value)
# xl_sheet.write(i,7,22)
# header_style = styles[rs.cell(start_of_bom_id,1)]
# content_style = styles[rs.cell(start_of_bom_id+1,1)]
# style = xlwt.XFStyle()
# # bold
# font = xlwt.Font()
# font.bold = True
# style.font = font
#
# # background color
# pattern = xlwt.Pattern()
# pattern.pattern = xlwt.Pattern.SOLID_PATTERN
# pattern.pattern_fore_colour = xlwt.Style.colour_map['pale_blue']
# style.pattern = pattern
header_style = xlwt.easyxf('pattern: pattern solid, fore_colour gray40;'
'font: colour black, bold True;')
# content_style = xlwt.easyxf('pattern: pattern solid, fore_colour light_blue;'
# 'font: colour black, bold ff;')
content_style = xlwt.easyxf('font: bold off, color black;\
borders: top_color gray25, bottom_color gray25, right_color gray25, left_color gray25,\
left thin, right thin, top thin, bottom thin;\
pattern: pattern solid, fore_color white;')
for i in range(start_of_bom_id, end_of_bom_id - 2):
xl_sheet.write(i, 7, bom[i][7], content_style)
for i in range(start_of_bom_id, end_of_bom_id - 2):
xl_sheet.write(i, 9, bom[i][9], content_style)
xl_sheet.write(end_of_bom_id, 9, bom[end_of_bom_id][9])
xl_sheet.write(start_of_bom_id - 1, 10, 'Codigo SAP', header_style)
xl_sheet.write(start_of_bom_id - 1, 11, 'Descrip Corta', header_style)
for i in range(start_of_bom_id, end_of_bom_id - 2):
xl_sheet.write(i, 10, bom[i][10], content_style)
for i in range(start_of_bom_id, end_of_bom_id - 2):
xl_sheet.write(i, 11, bom[i][11], content_style)
destinationwb.save(newfilename)
return newfilename