def get_donor_cell(self): size = 9 if self.has_acceptor: cell = WriteOnlyCell(ws_result, value='OK') cell.fill = PatternFill(start_color='FFdff0d8', end_color='FFdff0d8', fill_type='solid') cell.font = Font(name=self.font, size=size, color='3c763d', bold=True) cell.comment = Comment(text="Good =)", author=self.author) else: cell = WriteOnlyCell(ws_result, value='NO') cell.fill = PatternFill(start_color='FFf2dede', end_color='FFf2dede', fill_type='solid') cell.font = Font(name=self.font, size=size, color='a94442', bold=True) cell.comment = Comment( text="There are no acceptor ({}) on the site: {}".format( self.acceptor, self.donor), author=self.author) return cell
def set_row_data(cell_lis, ws, row, data_lis, string_lis): for index, data in enumerate(data_lis): col = index * 3 + 4 cell_num = WriteOnlyCell(ws, data) cell_num.number_format = num_format # 单元格值设置为数字 # ws[f"{string_lis[col - 1]}{row}"].number_format = num_format # 单元格值设置为数字 # ws[f"{string_lis[col - 1]}{row}"].value = data # 单元格值 cell_pdf = WriteOnlyCell( ws, f"={string_lis[col - 1]}{row}/{string_lis[col - 1]}2") cell_pdf.number_format = pdf_format # 当前所占百分比设置为百分数 # ws[f"{string_lis[col]}{row}"].number_format = pdf_format # 当前所占百分比设置为百分数 # ws[f"{string_lis[col]}{row}"].value = f"={string_lis[col - 1]}{row}/{string_lis[col - 1]}2" # 当前所占百分比 # ws[f"{string_lis[col + 1]}{row}"].number_format = pdf_format # 累计百分比设置为百分数 if row == 3: cell_cdf = WriteOnlyCell(ws, f"={string_lis[col]}{row}") # ws[f"{string_lis[col + 1]}{row}"].value = f"={string_lis[col]}{row}" # 第一条数据的累计百分比 else: cell_cdf = WriteOnlyCell( ws, f"={string_lis[col]}{row}+{string_lis[col + 1]}{row - 1}") # ws[ # f"{string_lis[col + 1]}{row}"].value = f"={string_lis[col]}{row}+{string_lis[col + 1]}{row - 1}" # 当前累计百分比 cell_cdf.number_format = pdf_format # 当前所占百分比设置为百分数 cell_lis.append(cell_num) cell_lis.append(cell_pdf) cell_lis.append(cell_cdf) ws.append(cell_lis)
def _values_to_row(self, values, row_idx): """ Convert whatever has been appended into a form suitable for work_rows """ cell = WriteOnlyCell(self) for col_idx, value in enumerate(values, 1): if value is None: continue try: cell.value = value except ValueError: if isinstance(value, Cell): cell = value else: raise ValueError cell.column = col_idx cell.row = row_idx if cell.hyperlink is not None: cell.hyperlink.ref = cell.coordinate yield cell # reset cell if style applied if cell.has_style or cell.hyperlink: cell = WriteOnlyCell(self)
def _write_row(self, sheet_index, row): from couchexport.export import FormattedRow sheet = self.tables[sheet_index] cells = [] for col_ind, val in enumerate(row): skip_formatting_on_row = (isinstance(row, FormattedRow) and col_ind in row.skip_excel_formatting) if (self.use_formatted_cells and not skip_formatting_on_row and not self.format_as_text): excel_format, val_fmt = get_excel_format_value(val) cell = WriteOnlyCell(sheet, val_fmt) cell.number_format = excel_format else: cell = WriteOnlyCell(sheet, get_legacy_excel_safe_value(val)) if self.format_as_text: cell.number_format = numbers.FORMAT_TEXT cells.append(cell) if isinstance(row, FormattedRow): for hyperlink_column_index in row.hyperlink_column_indices: cells[hyperlink_column_index].hyperlink = cells[hyperlink_column_index].value cells[hyperlink_column_index].style = 'Hyperlink' sheet.append(cells)
def export_xlsx(self, filename): um = self.units.description data = [(i+1,v,um) for (i,v) in enumerate(self.values)] wb = Workbook(write_only = True) ws = wb.create_sheet() # create some styles cellstyle = NamedStyle(name="highlight") headerstyle = NamedStyle(name='headercell') wb.add_named_style(cellstyle) wb.add_named_style(headerstyle) cellstyle.font = Font(name='Calibri', size=11) headerstyle.font = Font(name='Calibri', size=11, bold=True) bd = Side(border_style='thin') cellstyle.border = Border(bottom=bd, right=bd, top=bd, left=bd) headerstyle.border = Border(bottom=bd, right=bd, top=bd, left=bd) header_labels = ['#', 'Value', 'Units'] # write header header = [] for el in header_labels: cell = WriteOnlyCell(ws, value=el) cell.style = 'headercell' header.append(cell) ws.append(header) # write data for t in data: row = [] for el in t: cell = WriteOnlyCell(ws, value=el) cell.style = 'highlight' row.append(cell) ws.append(row) wb.save(filename) # doctest: +SKIP
def get_array_cells(self): acceptor_cell = WriteOnlyCell(ws_result, value=self.acceptor) anchor_cell = WriteOnlyCell(ws_result, value=self.anchor) donor_cell = WriteOnlyCell(ws_result, value=self.donor) if not self.has_anchor: acceptor_cell.fill = anchor_cell.fill = donor_cell.fill = PatternFill( start_color='FFfcf8e3', end_color='FFfcf8e3', fill_type='solid') if not self.has_acceptor: acceptor_cell.fill = anchor_cell.fill = donor_cell.fill = PatternFill( start_color='FFf2dede', end_color='FFf2dede', fill_type='solid') return [ acceptor_cell, # anchor_cell, donor_cell, # self.get_anchor_cell(), self.get_donor_cell(), self.get_status_cell(), ]
def write_to_excel2_2(datas, file_full_name): workbook = openpyxl.Workbook(write_only=True) # 默认无sheet sheet名从'Sheet'开始 workbook.create_sheet() # 创建名为'Sheet'的sheet for data in datas: worksheet = workbook.create_sheet() # 创建名为'Sheet1'的sheet alignment_title = Alignment(horizontal='center') font_title = Font(bold=True, size=10) font_content = Font(size=9) keys = data[0].keys() row = [] for key in keys: cell = WriteOnlyCell(worksheet, str(key) if key is not None else '') cell.font = font_title cell.alignment = alignment_title row.append(cell) worksheet.append(row) for js in data: row = [] for key in keys: cell = WriteOnlyCell( worksheet, str(js[key]) if js[key] is not None else '') cell.font = font_content cell.alignment = alignment_title row.append(cell) worksheet.append(row) workbook.remove(workbook[workbook.sheetnames[0]]) # 删除名为'Sheet'的sheet workbook.save(file_full_name) log_util.log('file_util.write_to_excel', 'filename:%s' % (file_full_name))
def get(self, request, *args, **kwargs): fmt = request.GET.get('format', None) if fmt is None: content = self._get_data(request) return TemplateResponse(request, "bom.html", content) elif fmt == 'spreadsheet': # 下载 excel json = self._get_data(request, in_page=False) wb = Workbook(write_only=True) title = "物料清单" ws = wb.create_sheet(title) headers = [] for h in self.file_headers: cell = WriteOnlyCell(ws, value=h) headers.append(cell) ws.append(headers) body_fields = ("id", "parent", "parent_nr", "item", "item_nr", "effective_start", "effective_end", "qty") data = [] for i in json["nodes"]: adict = {} adict["id"] = i.id adict["item"] = i.name adict["item_nr"] = i.nr if i.parent: adict["parent"] = i.parent.name adict["parent_nr"] = i.parent.nr else: adict["parent"] = "" adict["parent_nr"] = "" adict["effective_start"] = i.effective_start adict["effective_end"] = i.effective_end adict["qty"] = i.qty data.append(adict) for i in data: body = [] for field in body_fields: if field in i: cell = WriteOnlyCell(ws, value=i[field]) body.append(cell) ws.append(body) output = BytesIO() wb.save(output) response = HttpResponse( content_type= 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet', content=output.getvalue()) response[ 'Content-Disposition'] = "attachment; filename*=utf-8''%s.xlsx" % urllib.parse.quote( force_str(title)) response['Cache-Control'] = "no-cache, no-store" return response
def openpyxl_stream(df): """ Write a dataframe straight to disk """ wb = Workbook(write_only=True) ws = wb.create_sheet() cell = WriteOnlyCell(ws) cell.style = 'Pandas' def format_first_row(row, cell): for c in row: cell.value = c yield cell rows = dataframe_to_rows(df) first_row = format_first_row(next(rows), cell) ws.append(first_row) for row in rows: row = list(row) cell.value = row[0] row[0] = cell ws.append(row) wb.save("openpyxl_stream.xlsx")
def read_only(): wb = openpyxl.Workbook(write_only=True) ws = wb.create_sheet() cell_total = WriteOnlyCell(ws, "=SUM(A2:A1048576)") cell_total.number_format = num_format cell_str = WriteOnlyCell(ws, "") ws.append([cell_total, cell_str]) for i in range(2**20 - 1): cell_cur = WriteOnlyCell(ws, i) cell_cur.number_format = num_format cell_pdf = WriteOnlyCell(ws, F"=A{i + 2}/A1") cell_pdf.number_format = pdf_format ws.append([cell_cur, cell_pdf]) wb.save("ohmygod.xlsx")
def test_write_only_cell(WriteOnlyWorksheet): ws = WriteOnlyWorksheet c = WriteOnlyCell(ws, value=5) ws.append([c]) ws.close() with open(ws._writer.out, "rb") as src: xml = src.read() expected = """ <worksheet xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main"> <sheetPr> <outlinePr summaryRight="1" summaryBelow="1"/> <pageSetUpPr/> </sheetPr> <sheetViews> <sheetView workbookViewId="0"> <selection sqref="A1" activeCell="A1"/> </sheetView> </sheetViews> <sheetFormatPr baseColWidth="8" defaultRowHeight="15"/> <sheetData> <row r="1"> <c t="n" r="A1"> <v>5</v> </c> </row> </sheetData> <pageMargins bottom="1" footer="0.5" header="0.5" left="0.75" right="0.75" top="1"/> </worksheet> """ diff = compare_xml(xml, expected) assert diff is None, diff
def f_cs_que(data): wb = openpyxl.Workbook(write_only=True) ws = wb.create_sheet('Out') all_ngss = {} for kt, t in data.items(): ngss = None row = [int(kt)] elem = elem_que(t) for w in t.words: if w in elem: cpt = elem.index(w) c = WriteOnlyCell(ws, value=w.form) c.fill = patterns[cpt] if cpt == 0: ngss = w.lemma if w.lemma not in all_ngss: all_ngss[w.lemma] = 1 else: all_ngss[w.lemma] += 1 row.append(c) else: row.append(w.form) ws.append(row) ws = wb.create_sheet('NGSS') ngss_total = sum(all_ngss.values()) for k in sorted(all_ngss, key=all_ngss.get, reverse=True): ws.append([k, all_ngss[k], all_ngss[k] / ngss_total]) ws = wb.create_sheet('Info') ws.append(['Total occ NGSS', ngss_total]) ws.append(['Total res', len(data)]) wb.save('CS-que.xlsx')
def __get_cell(self, string, color=None, style=None): """ Helper method for log message. This method takes a string as well as color and style arguments to create a write-only cell. Args: string: a string object to be written to the cell's value field. color: a string object containing a color hex code "######" style: a string choosing 1 of 3 formatting options (ITALICS, BOLD, UNDERLINED) """ if not MODULE_INSTALLED: return cell = WriteOnlyCell(self.worksheet, value=string) if color is not None: # pylint: disable=E0237 cell.fill = PatternFill("solid", fgColor=color) # pylint: disable=E0237 cell.font = Font( name=self.font_name, bold=(style == self.BOLD), italic=(style == self.ITALICS), underline=("single" if style == self.UNDERLINED else "none"), ) # pylint: disable=E0237 cell.alignment = self.__align return cell
def _write_row(self, sheet_index, row): from couchexport.export import FormattedRow sheet = self.tables[sheet_index] # Source: http://stackoverflow.com/questions/1707890/fast-way-to-filter-illegal-xml-unicode-chars-in-python dirty_chars = re.compile( '[\x00-\x08\x0b-\x1f\x7f-\x84\x86-\x9f\ud800-\udfff\ufdd0-\ufddf\ufffe-\uffff]' ) def get_write_value(value): if isinstance(value, (int, float)): return value if isinstance(value, bytes): value = value.decode('utf-8') elif value is not None: value = str(value) else: value = '' return dirty_chars.sub('?', value) write_values = [get_write_value(val) for val in row] cells = [WriteOnlyCell(sheet, val) for val in write_values] if self.format_as_text: for cell in cells: cell.number_format = numbers.FORMAT_TEXT if isinstance(row, FormattedRow): for hyperlink_column_index in row.hyperlink_column_indices: cells[hyperlink_column_index].hyperlink = cells[ hyperlink_column_index].value cells[hyperlink_column_index].style = 'Hyperlink' sheet.append(cells)
def test_hyperlink(WriteOnlyWorksheet): ws = WriteOnlyWorksheet cell = WriteOnlyCell(ws, 'should have hyperlink') cell.hyperlink = 'http://bbc.co.uk' ws.append([]) ws.append([cell]) assert cell.hyperlink.ref == "A2" ws.close()
def write_title(self, worksheet, title=None): if not title: return title = WriteOnlyCell(ws=worksheet, value=title) self.template_styles.style_cell(title, self.title_style) worksheet.append((title,))
def write_description(self, worksheet, description=None): if not description: return description = WriteOnlyCell(ws=worksheet, value=description) self.template_styles.style_cell(description, self.description_style) worksheet.append((description,))
def append(self, row): """ :param row: iterable containing values to append :type row: iterable """ if (not isgenerator(row) and not isinstance(row, (list, tuple, range))): self._invalid_row(row) cell = WriteOnlyCell(self) # singleton self._max_row += 1 row_idx = self._max_row if self.writer is None: self.writer = self._write_header() next(self.writer) el = Element("row", r='%d' % self._max_row) col_idx = None for col_idx, value in enumerate(row, 1): if value is None: continue try: cell.value = value except ValueError: if isinstance(value, Cell): cell = value else: raise ValueError cell.col_idx = col_idx cell.row = row_idx styled = cell.has_style tree = write_cell(self, cell, styled) el.append(tree) if styled: # styled cell or datetime cell = WriteOnlyCell(self) if col_idx: self._max_col = max(self._max_col, col_idx) el.set('spans', '1:%d' % col_idx) try: self.writer.send(el) except StopIteration: self._already_saved()
def write_ref_headings(self, data): cells = [] for d in data: cell = WriteOnlyCell(self._sheet, d) cell.font = cell.font.copy(bold=True) cells.append(cell) self._sheet.append(cells) self._sheet.merged_cells.ranges.append('A1:G1') self._written_lines += 1
def _create_cell(self, worksheet: WriteOnlyWorksheet, data: dict) -> WriteOnlyCell: cell = WriteOnlyCell(ws=worksheet, value=data.get('value')) cell.column = data.get('column') cell.row = data.get('row') cell.font = self._create_cell_font(data.get('font')) cell.alignment = self._dict_to_object(data.get('alignment'), Alignment) cell.border = self._create_cell_borders(data.get('border')) return cell
def _to_excel(self, value, row_type=None): if value in self.BLANK_VALUES: if self.default is not None: return self.to_excel(self.default, row_type=row_type) if self.allow_blank: return None raise BlankNotAllowed(WriteOnlyCell()) return self.to_excel(value)
def format_output(val, eachformat, eachstyle, xls_sheet): """Returns a excel cell with the data formated as specified in the template table""" new_cell = WriteOnlyCell(xls_sheet, value="init") new_cell.style = eachstyle if val == None: val = "None" elif eachformat in [None, "OLE"]: pass elif eachformat.startswith("OLE:"): val = val.strftime(eachformat[4:]) elif eachformat == "FILE": val = file_timestamp(val) new_cell.number_format = 'YYYY MMM DD' elif eachformat.startswith("FILE:"): val = file_timestamp(val) val = val.strftime(eachformat[5:]) elif eachformat.lower().startswith("lookup-"): lookup_name = eachformat.split("-")[1] if lookup_name in template_lookups: lookup_table = template_lookups.get(lookup_name, {}) val = lookup_table.get(val, val) elif eachformat.lower() == "lookup_id": val = id_table.get(val, "No match in srum lookup table for %s" % (val)) elif eachformat.lower() == "lookup_luid": inttype = struct.unpack(">H6B", codecs.decode(format(val, '016x'), 'hex'))[0] val = template_lookups.get("LUID Interfaces", {}).get(inttype, "") elif eachformat.lower() == "seconds": val = val / 86400.0 new_cell.number_format = 'dd hh:mm:ss' elif eachformat.lower() == "md5": val = hashlib.md5(str(val)).hexdigest() elif eachformat.lower() == "sha1": val = hashlib.sha1(str(val)).hexdigest() elif eachformat.lower() == "sha256": val = hashlib.sha256(str(val)).hexdigest() elif eachformat.lower() == "base16": if type(val) == int: val = hex(val) else: val = format(val, "08x") elif eachformat.lower() == "base2": if type(val) == int: val = format(val, "032b") else: try: val = int(str(val), 2) except: val = val elif eachformat.lower() == "interface_id" and options.reghive: val = interface_table.get(str(val), "") elif eachformat.lower() == "interface_id" and not options.reghive: val = val else: val = val new_cell.value = val return new_cell
def create_cell(self, worksheet, style_set, value=None, row_type=None): cell = WriteOnlyCell( worksheet, value=self._to_excel(value if value is not None else self.default, row_type=row_type) ) cell_style = self.cell_styles[row_type] if cell_style: style_set.style_cell(cell, cell_style) return cell
def f_cs_n_est_n(data): wb = openpyxl.Workbook(write_only=True) ws = wb.create_sheet('Out') all_ngss = {} all_nco = {} all_cs = {} for kt, t in data.items(): ngss = None row = [int(kt)] elem = elem_n_est_n(t) for w in t.words: if w in elem: cpt = elem.index(w) c = WriteOnlyCell(ws, value=w.form) c.fill = patterns[cpt] if cpt == 0: ngss = w.lemma if w.lemma not in all_ngss: all_ngss[w.lemma] = 1 else: all_ngss[w.lemma] += 1 elif cpt == 1: pass elif cpt == 2: nco = w.lemma if w.lemma not in all_nco: all_nco[w.lemma] = 1 else: all_nco[w.lemma] += 1 cs = (ngss, nco) if cs not in all_cs: all_cs[cs] = 1 else: all_cs[cs] += 1 row.append(c) else: row.append(w.form) ws.append(row) ws = wb.create_sheet('NGSS') ngss_total = sum(all_ngss.values()) for k in sorted(all_ngss, key=all_ngss.get, reverse=True): ws.append([k, all_ngss[k], all_ngss[k] / ngss_total]) ws = wb.create_sheet('NCO') nco_total = sum(all_nco.values()) for k in sorted(all_nco, key=all_nco.get, reverse=True): ws.append([k, all_nco[k], all_nco[k] / nco_total]) ws = wb.create_sheet('CS') cs_total = sum(all_cs.values()) for k in sorted(all_cs, key=all_cs.get, reverse=True): ws.append([*k, all_cs[k], all_cs[k] / cs_total]) ws = wb.create_sheet('Info') ws.append(['Total occ NGSS', ngss_total]) ws.append(['Total occ NCO', nco_total]) ws.append(['Total diff CS', cs_total]) ws.append(['Total res', len(data)]) wb.save('CS-n_est_n.xlsx')
def save(self): from openpyxl import Workbook from openpyxl.cell import WriteOnlyCell from openpyxl.styles import PatternFill, Alignment self.check_is_ready() self.wb = Workbook(write_only=True) meta_ws = self.wb.create_sheet() meta_ws.title = "meta" meta_ws.sheet_properties.tabColor = "8888ff" meta_ws.column_dimensions['A'].width = 15 meta_ws.column_dimensions['B'].width = 40 meta_ws.column_dimensions['C'].width = 20 meta_ws.column_dimensions['D'].width = 20 meta_ws.column_dimensions['E'].width = 20 self.sections.resources.sort_by_term() self.load_declares() self.doc.cleanse() self._load_resources() self._clean_doc() fill = PatternFill( "solid", fgColor="acc0e0") # PatternFill(patternType='gray125') table_fill = PatternFill( "solid", fgColor="d9dce0") # PatternFill(patternType='gray125') alignment = Alignment(wrap_text=False) self._doc['Root'].get_or_new_term('Root.Issued').value = datetime_now() for i, row in enumerate(self.doc.rows, 1): if row[0] == 'Section' or row[0] == 'Table': styled_row = [] for c in row + [''] * 5: cell = WriteOnlyCell(meta_ws, value=c) cell.fill = fill if row[0] == 'Section' else table_fill styled_row.append(cell) meta_ws.append(styled_row) else: meta_ws.append(row) self.wb.save(self.package_path.path) return self.package_path
def serialise(self, worksheet, record, triggered: Triggered): linked_cell = WriteOnlyCell(worksheet, self.get_internal_id(record)) linked_cell.hyperlink = self.get_noms_ops_url(record) linked_cell.style = 'Hyperlink' row = { 'Notification rule': self.rule_description, 'Internal ID': linked_cell, } additional_header = self.additional_headers.get(type(self.rule), None) if additional_header: row[additional_header['header']] = triggered.kwargs[additional_header['triggered_kwarg']] return row
def f_cs_de_inf(data): wb = openpyxl.Workbook(write_only=True) ws = wb.create_sheet('Out') all_ngss = {} all_inf = {} all_cs = {} for kt, t in data.items(): ngss = None row = [int(kt)] elem = elem_cs_de_inf(t) for w in t.words: if w in elem: cpt = elem.index(w) c = WriteOnlyCell(ws, value=w.form) c.fill = patterns[cpt] if cpt == 0: ngss = w.lemma if w.lemma not in all_ngss: all_ngss[w.lemma] = 1 else: all_ngss[w.lemma] += 1 elif cpt == 2: # on ne retient pas le "être" optionnel cs = (ngss, 'de', w.lemma) if w.lemma not in all_inf: all_inf[w.lemma] = 1 else: all_inf[w.lemma] += 1 if cs not in all_cs: all_cs[cs] = 1 else: all_cs[cs] += 1 row.append(c) else: row.append(w.form) ws.append(row) ws = wb.create_sheet('NGSS') ngss_total = sum(all_ngss.values()) for k in sorted(all_ngss, key=all_ngss.get, reverse=True): ws.append([k, all_ngss[k], all_ngss[k] / ngss_total]) ws = wb.create_sheet('INF') inf_total = sum(all_inf.values()) for k in sorted(all_inf, key=all_inf.get, reverse=True): ws.append([k, all_inf[k], all_inf[k] / inf_total]) ws = wb.create_sheet('CS') cs_total = sum(all_cs.values()) for k in sorted(all_cs, key=all_cs.get, reverse=True): ws.append([*k, all_cs[k], all_cs[k] / cs_total]) ws = wb.create_sheet('Info') ws.append(['Total occ NGSS', ngss_total]) ws.append(['Total occ CS', cs_total]) ws.append(['Total res', len(data)]) wb.save('CS-inf.xlsx')
def get_status_cell(self): size = 8 if self.url_status < 400: cell = WriteOnlyCell(ws_result, value=self.status_reason) color = '3c763d' if self.has_acceptor else 'a94442' cell.font = Font(name=self.font, size=size, color=color, bold=True) cell.comment = Comment(text="Good =)", author=self.author) else: cell = WriteOnlyCell(ws_result, value="Site Access Problem") cell.font = Font(name=self.font, size=size, color='a94442', bold=True) cell.comment = Comment( text="Seems we have some troubles with the site: {}".format( self.acceptor), author=self.author) return cell
def make_sheet(self, title: str, headers: List[str]) -> Worksheet: sheet = self.workbook.create_sheet(title=title) sheet.freeze_panes = "A2" sheet.sheet_properties.filterMode = True cells = [] for header in headers: header_ = sanitize_text(header) cell = WriteOnlyCell(sheet, value=header_) cell.font = self.HEADER_FONT cell.fill = self.HEADER_FILL cells.append(cell) sheet.append(cells) return sheet
def create_cell(self, worksheet, value=None): cell = WriteOnlyCell( worksheet, value=self._to_excel(value if value is not None else self.default)) if self.row_style: cell.style = self.row_style return cell