def returnStyle():
borders = Borders()
borders.left = 0
borders.right = 0
borders.top = 0
borders.bottom = 0
#borders.Font.Size = 10
borders.Font = "10"
al = Alignment()
al.horz = Alignment.HORZ_CENTER
al.vert = Alignment.VERT_CENTER
style = XFStyle()
style.borders = borders
style.alignment = al
return style
def write_xls_price(self):
if self.options['whole']:
filename = path.join(settings.MEDIA_ROOT, 'upload/wprice.xls')
else:
filename = path.join(settings.MEDIA_ROOT, 'upload/price.xls')
workBookDocument = Workbook()
docSheet = workBookDocument.add_sheet(u'Прайс соло-парфюм')
docSheet.col(1).width = 10000
headerFont = Font()
headerFont.bold = True
headerFont.size = 400
headerStyle = XFStyle()
headerStyle.font = headerFont
docSheet.row(0).set_style(headerStyle)
if self.options['whole']:
docSheet.write_merge(0, 0, 0, 2, u'Оптовый прайс-лист Соло-парфюм (%s)' % datetime.now().strftime('%d.%m.%Y'))
else:
docSheet.write_merge(0, 0, 0, 2, u'Прайс-лист Соло-парфюм (%s)' % datetime.now().strftime('%d.%m.%Y'))
docSheet.write(2, 0, u'Артикул')
docSheet.write(2, 1, u'Наименование', )
docSheet.write(2, 2, u'Цена')
sectionFont = Font()
sectionFont.bold = True
sectionStyle = XFStyle()
sectionStyle.font = sectionFont
align = Alignment()
align.horz = Alignment.HORZ_CENTER
sectionStyle.alignment = align
row = 3
for section in self.data.iterkeys():
docSheet.write_merge(row, row, 0, 2, section, sectionStyle)
row += 1
for item in self.data[section]:
docSheet.write(row, 0, item['identifier'])
docSheet.write(row, 1, item['name'])
docSheet.write(row, 2, item['price'])
row += 1
workBookDocument.save(filename)
def sandbox():
#set attributes for workbook
fnt = Font()
fnt.name, fnt.colour_index, fnt.bold, fnt._weight = 'Arial',0x08,False,0x0190
borders = Borders()
borders.left, borders.right, borders.top, borders.bottom = 0,0,0,0
al = Alignment()
al.horz, al.vert = Alignment.HORZ_CENTER,Alignment.VERT_CENTER
style = XFStyle()
style.font, style.borders,style.alignment = fnt,borders,al
# create wb instance
wb = Workbook()
#add a sheet for each csv file being imported to excel
s = util.GetFileListing('Note*.csv')
csv_files = s.sys_cmd.output
worksheet_titles = [ ':'.join(a_file.split(':')[1:-2]) for a_file in csv_files ]
ws_list = [ wb.add_sheet(ws_title) for ws_title in worksheet_titles ]
F = util.FileEdit(csv_titles)
for ws,f in zip(ws_list,F.file_utils):
a_list = [ a_row.rstrip().split(',') for a_row in f.contents ]
for a_row,a_line in enumerate(a_list):
for a_col,a_cell in enumerate(a_line): ws.write(a_row,a_col,a_cell,style)
wb.save('chk_out.xls')
def write_xls_price(self):
if self.options['whole']:
filename = path.join(settings.MEDIA_ROOT, 'upload/wprice.xls')
else:
filename = path.join(settings.MEDIA_ROOT, 'upload/price.xls')
workBookDocument = Workbook()
docSheet = workBookDocument.add_sheet(u'Прайс соло-парфюм')
docSheet.col(1).width = 10000
headerFont = Font()
headerFont.bold = True
headerFont.size = 400
headerStyle = XFStyle()
headerStyle.font = headerFont
docSheet.row(0).set_style(headerStyle)
if self.options['whole']:
docSheet.write_merge(
0, 0, 0, 2, u'Оптовый прайс-лист Соло-парфюм (%s)' %
datetime.now().strftime('%d.%m.%Y'))
else:
docSheet.write_merge(
0, 0, 0, 2, u'Прайс-лист Соло-парфюм (%s)' %
datetime.now().strftime('%d.%m.%Y'))
docSheet.write(2, 0, u'Артикул')
docSheet.write(
2,
1,
u'Наименование',
)
docSheet.write(2, 2, u'Цена')
sectionFont = Font()
sectionFont.bold = True
sectionStyle = XFStyle()
sectionStyle.font = sectionFont
align = Alignment()
align.horz = Alignment.HORZ_CENTER
sectionStyle.alignment = align
row = 3
for section in self.data.iterkeys():
docSheet.write_merge(row, row, 0, 2, section, sectionStyle)
row += 1
for item in self.data[section]:
docSheet.write(row, 0, item['identifier'])
docSheet.write(row, 1, item['name'])
docSheet.write(row, 2, item['price'])
row += 1
workBookDocument.save(filename)
from pyExcelerator import Workbook, Borders, XFStyle, Alignment
from pymongo import Connection
one_hour = timedelta(hours=1)
borders = Borders()
borders.left = 1
borders.right = 1
borders.top = 1
borders.bottom = 1
align = Alignment()
align.horz = Alignment.HORZ_CENTER
align.vert = Alignment.VERT_CENTER
style = XFStyle()
style.borders = borders
# center style
cs = XFStyle()
cs.borders = borders
cs.alignment = align
date_fmt = '%Y-%m-%d'
time_fmt = '{:02d}:00 - {:02d}:00'
st_fmt = '%Y-%m-%d %H:%M:%S'
def get_dept_ids():
data = dict()
for dp in depts:
def exportXlsData(self):
"""get XLS file for all folder objects"""
filename = datetime.now().strftime('ict_%Y%m%d%H%M%S.xls')
f_handle, f_name = tempfile.mkstemp(filename)
wbook = Workbook()
wb_hosts = wbook.add_sheet('ddd')
style0 = XFStyle()
font0 = Font()
font0.height = 6 * 20
style0.num_format_str = '@'
style0.font = font0
style1 = XFStyle()
font1 = Font()
font1.height = 6 * 20
style1.num_format_str = '@'
style1.font = font1
heading_pattern = xl.Pattern()
heading_pattern.pattern = xl.Pattern.SOLID_PATTERN
heading_pattern.pattern_back_colour = 0x5
heading_pattern.pattern_fore_colour = 0x5
fields = self.fields
attrList = [fname for fname, fval in fields.items()]
itemList = self.context.items()
pos_y = 0
pos_x = 0
for attr in attrList:
wb_data = Formula(u'"%s"' % attr)
style0.pattern = heading_pattern
wb_hosts.write(pos_y, pos_x, wb_data, style0)
pos_x += 1
# IntID
wb_data = Formula(u'"IntID"')
wb_hosts.write(pos_y, pos_x, wb_data, style0)
pos_x += 1
# objectID
wb_data = Formula(u'"objectID"')
wb_hosts.write(pos_y, pos_x, wb_data, style0)
wb_hosts.col(pos_x).width *= 3
pos_x += 1
pos_y = 1
#
allAttributes = {}
for interface in implementedBy(self.factory):
for i_attrName in interface:
i_attr = interface[i_attrName]
if IField.providedBy(i_attr):
allAttributes[i_attrName] = i_attr
#
for item_n, item_v in itemList:
pos_x = 0
for attr in attrList:
# from zope.interface import implementedBy
# ff=self.factory
# tt=[i for i in implementedBy(ff)]
# it=tt[-1]
#attrField = self.attrInterface[attr]
attrField = allAttributes[attr]
# tmpFieldProperty = getattr(self.factory, attr)
# if hasattr(tmpFieldProperty, '_FieldProperty__field'):
# attrField = getattr(self.factory, attr)._FieldProperty__field
attrDm = datamanager.AttributeField(item_v, attrField)
v_style = XFStyle()
v_font = Font()
v_font.height = 6 * 20
v_style.font = v_font
value = None
if IChoice.providedBy(attrField):
v_style.num_format_str = '@'
dateValue = attrDm.get()
v_widget = getMultiAdapter(\
(attrField,self.request),
interfaces.IFieldWidget)
v_widget.context = item_v
# dm = zope.component.getMultiAdapter(
# (self.content, field.field), interfaces.IDataManager)
# zope.component.getMultiAdapter(
# (self.context,
# self.request,
# self.form,
# getattr(widget, 'field', None),
# widget),
# interfaces.IValidator).validate(fvalue)
# dm = zope.component.getMultiAdapter(
# (self.__context__, field), interfaces.IDataManager)
v_dataconverter = queryMultiAdapter(\
(attrDm.field, v_widget),
interfaces.IDataConverter)
#print u"ddd55: %s: %s" % (attr, dateValue)
if dateValue is not None:
value = v_dataconverter.toWidgetValue(dateValue)[0]
#print "value3-> %s: %s " % (attr, value)
# elif ICollection.providedBy(attrField):
# v_style.num_format_str = '@'
# value = getattr(item_v, attr)
# print "ddd66: %s: %s" % (attr, value)
# elif IBool.providedBy(attrField):
# v_style.num_format_str = '@'
# value = getattr(item_v, attr)
# print "value2-> %s: %s " % (attr, value)
else:
v_style.num_format_str = '@'
dateValue = attrDm.get()
v_widget = getMultiAdapter(\
(attrField,self.request),
interfaces.IFieldWidget)
v_widget.context = item_v
v_dataconverter = queryMultiAdapter(\
(attrDm.field, v_widget),
interfaces.IDataConverter)
#d2 = queryMultiAdapter((attrDm.field, v_widget),interfaces.IDataConverter)
if dateValue is not None:
value = v_dataconverter.toWidgetValue(dateValue)
if type(value) is list:
value = u";".join(value)
#print u"value1-> %s: %s " % (attr, value)
if value is not None:
#print u"wb_hosts.write(%s, %s, %s, %s)" % (pos_y, pos_x, value, v_style)
wb_hosts.write(pos_y, pos_x, value, v_style)
pos_x += 1
# IntID
uidutil = queryUtility(IIntIds)
wb_data = Formula(u'"%s"' % uidutil.getId(item_v))
wb_hosts.write(pos_y, pos_x, wb_data, style0)
pos_x += 1
# objectID
wb_data = Formula(u'"%s"' % item_v.objectID)
wb_hosts.write(pos_y, pos_x, wb_data, style0)
pos_x += 1
pos_y += 1
wbook.save(f_name)
self.request.response.setHeader('Content-Type',
'application/vnd.ms-excel')
self.request.response.setHeader(\
'Content-Disposition',
'attachment; filename=\"%s\"' % filename)
setNoCacheHeaders(self.request.response)
datafile = open(f_name, "r")
dataMem = datafile.read()
datafile.close()
os.remove(f_name)
return dataMem
def setup_timetable_worksheet(self,title,base_offsets,maxcells):
h1 = []
h2 = []
h1.append(title)
h2.extend(['Monday','Tuesday','Wednesday','Thursday','Friday'])
labels = ['1st period','2nd period','3rd period','4th period','5th period']
times = ['(8:45 - 10:15)','(10:30 - 12:00)','(13:00 - 14:30)','(14:45 - 16:15)','(16:30 - 18:00)']
h1Style = XFStyle()
font = Font()
font.height = 300
font.bold = True
align = Alignment()
align.horz = Alignment.HORZ_CENTER
h1Style.font = font
h1Style.alignment = align
h2Style = XFStyle()
align = Alignment()
align.rota = 30
align.horz = Alignment.HORZ_CENTER
align.vert = Alignment.VERT_CENTER
h2Style.alignment = align
borders = Borders()
borders.bottom = Borders.THIN
h2Style.borders = borders
labelStyle = XFStyle()
borders = Borders()
borders.bottom = Borders.THIN
borders.top = Borders.THIN
borders.left = Borders.THIN
borders.right = Borders.THIN
labelStyle.borders = borders
align = Alignment()
align.rota = 90
align.horz = Alignment.HORZ_CENTER
align.vert = Alignment.VERT_CENTER
labelStyle.alignment = align
self.ws.write_merge(0,0,0,5,title,h1Style)
for pos in range (0,5,1):
self.ws.write(1,pos+1,h2[pos],h2Style)
self.ws.write(1,0,'',h2Style)
for pos in range(0,5,1):
base_offset = base_offsets[pos]
offset = maxcells[pos]
for r in range(2+base_offset,2+base_offset+offset,1):
row = self.ws.row(r)
row.height = 5000/offset
#row.has_default_height = False
text = '%s\n%s' % (labels[pos],times[pos])
print "base_offset: %d" % (base_offset,)
print "offset: %d" % (offset,)
self.ws.write_merge(2+base_offset,2+base_offset+offset-1,0,1,text,labelStyle)
#self.ws.write_merge(2+base_offset,2+base_offset+offset,0,0,text,labelStyle)
self.ws.col(0).width = 1200
for pos in range(1,6,1):
self.ws.col(pos).width = 5500
#self.ws.row(10).has_default_height = False
self.ws.row(10).height = 400
def exportXlsData(self, request, sheetName=u'ict', wbook=None):
"""get XLS file for all folder objects"""
if wbook is None:
localWbook = True
filename = datetime.now().strftime('ict_%Y%m%d%H%M%S.xls')
f_handle, f_name = tempfile.mkstemp(filename)
wbook = Workbook()
else:
localWbook = False
wb_hosts = wbook.add_sheet(getattr(self, 'ikName', sheetName))
style0 = XFStyle()
font0 = Font()
font0.height = 6 * 20
#style0.num_format_str = '@'
style0.font = font0
style1 = XFStyle()
font1 = Font()
font1.height = 6 * 20
#style1.num_format_str = '@'
style1.font = font1
heading_pattern = xl.Pattern()
heading_pattern.pattern = xl.Pattern.SOLID_PATTERN
heading_pattern.pattern_back_colour = 0x5
heading_pattern.pattern_fore_colour = 0x5
fields = fieldsForFactory(self.contentFactory, ['objectID'])
attrList = [fname for fname, fval in fields.items()]
itemList = self.items()
pos_y = 0
pos_x = 0
for attr in attrList:
style0.pattern = heading_pattern
wb_hosts.write(pos_y, pos_x, attr, style0)
pos_x += 1
# objectID
wb_hosts.write(pos_y, pos_x, "objectID", style0)
wb_hosts.col(pos_x).width *= 3
pos_x += 1
pos_y = 1
#
allAttributes = {}
for interface in implementedBy(self.contentFactory):
for i_attrName in interface:
i_attr = interface[i_attrName]
if IField.providedBy(i_attr):
allAttributes[i_attrName] = i_attr
#
for item_n, item_v in itemList:
pos_x = 0
for attr in attrList:
attrField = allAttributes[attr]
attrDm = datamanager.AttributeField(item_v, attrField)
v_style = XFStyle()
v_font = Font()
v_font.height = 6 * 20
v_style.font = v_font
value = None
if IChoice.providedBy(attrField):
v_style.num_format_str = '@'
# try:
dateValue = attrDm.get()
v_widget = getMultiAdapter(\
(attrField,request),
interfaces.IFieldWidget)
v_widget.context = item_v
v_dataconverter = queryMultiAdapter(\
(attrDm.field, v_widget),
interfaces.IDataConverter)
# except AttributeError, errText:
# print "Error2: ### ", errText
# print "item_v: ", item_v
# print "item_v.ikName: ", item_v.ikName
# print "attr: ", attr
# dateValue = None
# #import pdb
# #pdb.set_trace()
if dateValue is not None:
# try:
valueVector = v_dataconverter.toWidgetValue(dateValue)
if len(valueVector) > 0:
value = valueVector[0]
# except IndexError, errText:
# print "Error3: ### ", errText
# print "item_v: ", item_v
# print "item_v.ikName: ", item_v.ikName
# print "attr: ", attr
else:
v_style.num_format_str = '@'
try:
# import pdb
# pdb.set_trace()
dateValue = attrDm.get()
v_widget = getMultiAdapter(\
(attrField,request),
interfaces.IFieldWidget)
v_widget.context = item_v
v_dataconverter = queryMultiAdapter(\
(attrDm.field, v_widget),
interfaces.IDataConverter)
except AttributeError, errText:
print "Error1: ### ", errText
print "item_v: ", item_v
print "item_v.ikName: ", item_v.ikName
print "attr: ", attr
dateValue = None
if dateValue is not None:
try:
value = v_dataconverter.toWidgetValue(dateValue)
except AttributeError, errText:
print "Error4: ### ", errText
print "item_v: ", item_v
print "item_v.ikName: ", item_v.ikName
print "attr: ", attr
value = None
if type(value) is list:
value = u";".join(value)
if value is not None:
wb_hosts.write(pos_y, pos_x, value, v_style)
pos_x += 1
