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