python类Workbook()的实例源码

def xlsx_transactions(self, year, month, file_name):
        transactions = self.transactions(year, month)

        if len(transactions) == 0:
            warnings.warn('No transactions for the period ({}-{})'.format(
                year, month))
            return

        wb = openpyxl.Workbook()
        ws = wb.active

        ws.append(self.fieldnames)

        for trans in transactions:
            if u'date' in trans:
                trans[u'date'] = datetime.datetime.fromtimestamp(
                    trans[u'date']/1000).date()
            row = [trans[k] for k in self.fieldnames]
            ws.append(row)

        wb.save(file_name)

def xls_as_xlsx(xls_file):
    # first open using xlrd
    source_workbook = xlrd.open_workbook(file_contents=xls_file.read())

    # Create the destination workbook, deleting and auto-generated worksheets.
    destination_workbook = openpyxl.Workbook() # TODO: Would like to figure out how to make appends work with a "write_only" workbook.
    for wksht_nm in destination_workbook.get_sheet_names():
        worksheet= destination_workbook.get_sheet_by_name(wksht_nm)
        destination_workbook.remove_sheet(worksheet)

    worksheet_names= ['survey', 'choices']
    for wksht_nm in source_workbook.sheet_names():
        source_worksheet= source_workbook.sheet_by_name(wksht_nm)
        destination_worksheet= destination_workbook.create_sheet(title=wksht_nm)

        for row in xrange(source_worksheet.nrows):
            destination_worksheet.append( [source_worksheet.cell_value(row, col) for col in xrange(source_worksheet.ncols)] )

    return io.BytesIO(save_virtual_workbook(destination_workbook))

def to_xlsx(layout, output_fp, orientation='vertical', **kwargs):
        '''
        Take a layout which contains a list of presentation models builts using the build_presentation_model function.

        Args:
            layout: An nested list of presentation_models, examples: [presentation_model] or [presentation_model1, presentation_mode2] etc
            output_fp: the xlsx file name
            orientation: if vertical, the top level presentation model elements are rendered vertically, and for every nested level the orientation is flipped.
                         if horizontal, then the behavior is inverse
            kwargs:
                    column-width: default=20, the default column width of all columns in the worksheet. Individual column width cannot be set currently
        '''
        row_col_dict = GridLayoutManager.get_row_col_dict(
            layout, orientation=orientation)
        wb = Workbook()
        ws = wb.active
        XLSXWriter._to_xlsx_worksheet(row_col_dict, ws, **kwargs)
        wb.save(output_fp)

def json2xlsx(xlsx_path,json_path):
    wb=Workbook()
    ws1=wb.active
    ws1.title=xlsx_path.split('.')[0].split('\\')[-1]
    with open(json_path) as f:
        fulljson=f.read()
    j=json.loads(fulljson,object_pairs_hook=OrderedDict)#object_pairs_hook ??json???

    for row,row_val in enumerate(j):
        _ = ws1.cell(row=row+1,column=1,value=row_val)
        if isinstance(j[row_val],Iterable) and not isinstance(j[row_val],str):#????????string??
            for col,col_val in enumerate(j[row_val]):
                _= ws1.cell(row=row+1,column=col+2,value=col_val)
        else:
            _ = ws1.cell(row=row+1,column=2,value=j[row_val])

    wb.save(xlsx_path)

def test_groups(self):
        wb = pyxl.Workbook()
        ws = wb.active
        header = ('Ref', 'x', 'y', 'z')
        ws.append(header)
        ws.append(('C1', '1', '1', '1'))
        ws.append(('C2', '1', '1', '1'))
        ws.append(('C3', '1', '1', '1'))

        wb = kifield.group_wb(wb)
        ws = wb.active

        assert ws.max_row == 2
        assert ws.max_column == 4

        values = tuple(ws.values)
        assert values[0] == header
        assert values[1] == ('C1-C3', '1', '1', '1')

def test_groups2(self):
        wb = pyxl.Workbook()
        ws = wb.active
        header = ('Ref', 'x', 'y', 'z')
        ws.append(header)
        ws.append(('C1', '1', '1', '1'))
        ws.append(('R3', '2', '1', '1'))
        ws.append(('R5', '2', '1', '1'))
        ws.append(('X1', '3', '1', '1'))
        ws.append(('X2', '1', '3', '1'))
        ws.append(('X3', '1', '1', '3'))

        wb = kifield.group_wb(wb)
        ws = wb.active

        assert ws.max_row == 6
        assert ws.max_column == 4

        values = tuple(ws.values)
        assert values[0] == header
        assert values[1] == ('C1', '1', '1', '1')
        assert values[2] == ('R3, R5', '2', '1', '1')
        assert values[3] == ('X1', '3', '1', '1')
        assert values[4] == ('X2', '1', '3', '1')
        assert values[5] == ('X3', '1', '1', '3')

def csvfile_to_wb(csv_filename):
    '''Open a CSV file and return an openpyxl workbook.'''

    logger.log(
        DEBUG_DETAILED,
        'Converting CSV file {} into an XLSX workbook.'.format(csv_filename))

    with open(csv_filename) as csv_file:
        dialect = csv.Sniffer().sniff(csv_file.read())
        if USING_PYTHON2:
            for attr in dir(dialect):
                a = getattr(dialect, attr)
                if type(a) == unicode:
                    setattr(dialect, attr, bytes(a))
        csv_file.seek(0)
        reader = csv.reader(csv_file, dialect)
        wb = pyxl.Workbook()
        ws = wb.active
        for row_index, row in enumerate(reader, 1):
            for column_index, cell in enumerate(row, 1):
                if cell not in ('', None):
                    ws.cell(row=row_index, column=column_index).value = cell
    return (wb, dialect)

def Open(self):
        try:
            if self.v_extension == 'csv':
                self.v_file = open(self.v_filename, 'w', encoding=self.v_encoding)
                self.v_object = csv.DictWriter(v_file, fieldnames=self.v_header)
                self.v_object.writeheader()
                self.v_open = True
            elif self.v_extension == 'xlsx':
                self.v_object = openpyxl.Workbook(write_only=True)
                self.v_open = True
            else:
                raise Spartacus.Utils.Exception('File extension "{0}" not supported.'.format(self.v_extension))
        except Spartacus.Utils.Exception as exc:
            raise exc
        except Exception as exc:
            raise Spartacus.Utils.Exception(str(exc))

def print_book_lists_excel(book_lists, book_tag_lists):
    wb = Workbook(optimized_write=True)
    ws = []
    for i in range(len(book_tag_lists)):
        # utf8->unicode
        ws.append(wb.create_sheet(title=book_tag_lists[i].decode()))
    for i in range(len(book_tag_lists)):
        ws[i].append(['??', '??', '??', '????', '??', '???'])
        count = 1
        for bl in book_lists[i]:
            ws[i].append([count, bl[u'a'], float(bl[u'b']),
                          int(bl[2]), bl[3], bl[4]])
            count += 1
    save_path = 'book_list'
    for i in range(len(book_tag_lists)):
        save_path += ('-' + book_tag_lists[i].decode())
    save_path += '.xlsx'
    wb.save(save_path)

def __init__(self, account, options, time_range=None):
        self.options = options
        self.skip_tiers = options.get('skip_tiers', False)
        self.granularity = options.get('granularity', 'hourly')
        self.tabs = options.get('tabs')
        self.sheet_per_app = options.get('sheet_per_app', False)
        self.include_non_apm_summary = options.get('include_non_npm_agent_summary', False)
        self.file_name = '%s_%s_%s.xlsx' % (options.get('report_file_prefix', 'license_report'),
            datetime.utcfromtimestamp(time_range.start_time / 1000).strftime("%m-%d-%Y"),
            datetime.utcfromtimestamp((time_range.end_time + 3600000) / 1000).strftime("%m-%d-%Y") )
        self.account = account
        self.applications = account.applications
        self.account_name = account.name
        self.time_range = time_range
        self.workbook = Workbook()
        self.skip_applications = options.get('skip_applications', False)

def get(self, request, *args, **kwargs):
        profesiones = Profesion.objects.filter(estado=True)
        wb = Workbook()
        ws = wb.active
        ws['B1'] = 'REPORTE DE PROFESIONES'
        ws.merge_cells('B1:J1')
        ws['B3'] = 'ABREVIATURA'
        ws['C3'] = 'DESCRIPCION'
        ws['D3'] = 'ESTADO'
        cont = 4
        for profesion in profesiones:
            ws.cell(row=cont, column=2).value = profesion.abreviatura
            ws.cell(row=cont, column=3).value = profesion.descripcion
            ws.cell(row=cont, column=4).value = profesion.estado
            cont = cont + 1
        nombre_archivo = "Profesiones.xlsx"
        response = HttpResponse(content_type="application/ms-excel")
        contenido = "attachment; filename={0}".format(nombre_archivo)
        response["Content-Disposition"] = contenido
        wb.save(response)
        return response

def get(self, request, *args, **kwargs):
        formas_pago = FormaPago.objects.all().order_by('codigo')
        wb = Workbook()
        ws = wb.active
        ws['B1'] = 'REPORTE DE FORMAS DE PAGO'
        ws.merge_cells('B1:J1')
        ws['B3'] = 'CODIGO'
        ws['C3'] = 'DESCRIPCIÓN'
        ws['D3'] = 'DIAS_CREDITO'
        cont=4
        for forma_pago in formas_pago:
            ws.cell(row=cont,column=2).value = forma_pago.codigo
            ws.cell(row=cont,column=3).value = forma_pago.descripcion
            ws.cell(row=cont,column=4).value = forma_pago.dias_credito
            cont = cont + 1
        nombre_archivo ="ListadoFormasPago.xlsx" 
        response = HttpResponse(content_type="application/ms-excel") 
        contenido = "attachment; filename={0}".format(nombre_archivo)
        response["Content-Disposition"] = contenido
        wb.save(response)
        return response

def get(self, request, *args, **kwargs):
        tipos = TipoDocumento.objects.all().order_by('codigo_sunat')
        wb = Workbook()
        ws = wb.active
        ws['B1'] = 'REPORTE DE TIPOS DE DOCUMENTOS'
        ws.merge_cells('B1:J1')
        ws['B3'] = 'CODIGO SUNAT'
        ws['C3'] = 'NOMBRE'
        ws['D3'] = 'DESCRIPCIÓN'
        cont=4
        for tipo in tipos:
            ws.cell(row=cont,column=2).value = tipo.codigo_sunat
            ws.cell(row=cont,column=3).value = tipo.nombre
            ws.cell(row=cont,column=4).value = tipo.descripcion
            cont = cont + 1
        nombre_archivo ="ListadoTiposDocumentos.xlsx" 
        response = HttpResponse(content_type="application/ms-excel") 
        contenido = "attachment; filename={0}".format(nombre_archivo)
        response["Content-Disposition"] = contenido
        wb.save(response)
        return response

def get(self, request, *args, **kwargs):
        almacenes = Almacen.objects.filter(estado=True).order_by('codigo')
        wb = Workbook()
        ws = wb.active
        ws['B1'] = 'REPORTE DE ALMACENES'
        ws.merge_cells('B1:J1')
        ws['B3'] = 'CODIGO'
        ws['C3'] = 'DESCRIPCIÓN'
        cont=4
        for almacen in almacenes:
            ws.cell(row=cont,column=2).value = almacen.codigo
            ws.cell(row=cont,column=3).value = almacen.descripcion
            cont = cont + 1
        nombre_archivo ="ListadoAlmacenes.xlsx" 
        response = HttpResponse(content_type="application/ms-excel") 
        contenido = "attachment; filename={0}".format(nombre_archivo)
        response["Content-Disposition"] = contenido
        wb.save(response)
        return response

def get(self, request, *args, **kwargs):
        tipos = TipoMovimiento.objects.filter(estado=True).order_by('codigo')
        wb = Workbook()
        ws = wb.active
        ws['B1'] = 'REPORTE DE TIPOS DE MOVIMIENTOS'
        ws.merge_cells('B1:J1')
        ws['B3'] = 'CODIGO'
        ws['C3'] = 'DESCRIPCIÓN'
        cont=4
        for tipo in tipos:
            ws.cell(row=cont,column=2).value = tipo.codigo
            ws.cell(row=cont,column=3).value = tipo.descripcion
            cont = cont + 1
        nombre_archivo ="MaestroTiposMovimientos.xlsx" 
        response = HttpResponse(content_type="application/ms-excel") 
        contenido = "attachment; filename={0}".format(nombre_archivo)
        response["Content-Disposition"] = contenido
        wb.save(response)
        return response

def get(self, request, *args, **kwargs):
        grupos_productos = GrupoProductos.objects.filter(estado=True).order_by('codigo')
        wb = Workbook()
        ws = wb.active
        ws['B1'] = 'REPORTE DE GRUPOS DE PRODUCTOS'
        ws.merge_cells('B1:J1')
        ws['B3'] = 'CODIGO'
        ws['C3'] = 'DESCRIPCION'
        ws['D3'] = 'CTA_CONTABLE'
        ws['E3'] = 'CREADO'
        cont=4
        for grupo_productos in grupos_productos:
            ws.cell(row=cont,column=2).value = grupo_productos.codigo
            ws.cell(row=cont,column=3).value = grupo_productos.descripcion
            ws.cell(row=cont,column=4).value = grupo_productos.ctacontable.cuenta
            ws.cell(row=cont,column=5).value = grupo_productos.created
            ws.cell(row=cont,column=5).number_format = 'dd/mm/yyyy hh:mm:ss' 
            cont = cont + 1
        nombre_archivo ="ListadoGruposProductos.xlsx" 
        response = HttpResponse(content_type="application/ms-excel") 
        contenido = "attachment; filename={0}".format(nombre_archivo)
        response["Content-Disposition"] = contenido
        wb.save(response)
        return response

def get(self, request, *args, **kwargs):
        servicios = Producto.objects.filter(es_servicio=True,estado=True).order_by('codigo')
        wb = Workbook()
        ws = wb.active
        ws['B1'] = 'REPORTE DE SERVICIOS'
        ws.merge_cells('B1:J1')
        ws['B3'] = 'CODIGO'
        ws['C3'] = 'DESCRIPCION'
        ws['D3'] = 'ESTADO'
        cont=4
        for servicio in servicios:
            ws.cell(row=cont,column=2).value = servicio.codigo
            ws.cell(row=cont,column=3).value = servicio.descripcion
            ws.cell(row=cont,column=4).value = servicio.estado  
            cont = cont + 1
        nombre_archivo ="ListadoServicios.xlsx" 
        response = HttpResponse(content_type="application/ms-excel") 
        contenido = "attachment; filename={0}".format(nombre_archivo)
        response["Content-Disposition"] = contenido
        wb.save(response)
        return response

def __init__(self, file=None, template_styles=None, timestamp=None, templated_sheets=None):
        super().__init__()

        self.workbook = load_workbook(filename=file) if file else Workbook()

        self.template_styles = template_styles or DefaultStyleSet()
        self.timestamp = timestamp

        self.templated_sheets = []
        for sheetname, templated_sheet in self._items.items():
            self.add_templated_sheet(templated_sheet, sheetname=sheetname, add_to_self=False)

        for templated_sheet in templated_sheets or []:
            self.add_templated_sheet(sheet=templated_sheet, sheetname=templated_sheet.sheetname, add_to_self=True)

        self._validate()

def test_write_hyperlink_rels(datadir):
    from .. relations import write_rels
    wb = Workbook()
    ws = wb.active

    assert 0 == len(ws.relationships)
    ws.cell('A1').value = "test"
    ws.cell('A1').hyperlink = "http://test.com/"
    assert 1 == len(ws.relationships)
    ws.cell('A2').value = "test"
    ws.cell('A2').hyperlink = "http://test2.com/"
    assert 2 == len(ws.relationships)

    el = write_rels(ws, 1, 1)
    xml = tostring(el)

    datadir.chdir()
    with open('sheet1_hyperlink.xml.rels') as expected:
        diff = compare_xml(xml, expected.read())
        assert diff is None, diff

def test_write_hidden_worksheet():
    wb = Workbook()
    ws = wb.active
    ws.sheet_state = ws.SHEETSTATE_HIDDEN
    wb.create_sheet()
    xml = write_workbook(wb)
    expected = """
    <workbook xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main" xmlns:r="http://schemas.openxmlformats.org/officeDocument/2006/relationships">
    <fileVersion appName="xl" lastEdited="4" lowestEdited="4" rupBuild="4505"/>
    <workbookPr codeName="ThisWorkbook" defaultThemeVersion="124226"/>
    <bookViews>
      <workbookView activeTab="0" autoFilterDateGrouping="1" firstSheet="0" minimized="0" showHorizontalScroll="1" showSheetTabs="1" showVerticalScroll="1" tabRatio="600" visibility="visible"/>
    </bookViews>
    <sheets>
      <sheet name="Sheet" sheetId="1" state="hidden" r:id="rId1"/>
      <sheet name="Sheet1" sheetId="2" r:id="rId2"/>
    </sheets>
      <definedNames/>
      <calcPr calcId="124519" calcMode="auto" fullCalcOnLoad="1"/>
    </workbook>
    """
    diff = compare_xml(xml, expected)
    assert diff is None, diff

def test_write_named_range():
    from openpyxl.writer.workbook import _write_defined_names
    wb = Workbook()
    ws = wb.active
    xlrange = NamedRange('test_range', [(ws, "A1:B5")])
    wb._named_ranges.append(xlrange)
    root = Element("root")
    _write_defined_names(wb, root)
    xml = tostring(root)
    expected = """
    <root>
     <s:definedName xmlns:s="http://schemas.openxmlformats.org/spreadsheetml/2006/main" name="test_range">'Sheet'!$A$1:$B$5</s:definedName>
    </root>
    """
    diff = compare_xml(xml, expected)
    assert diff is None, diff

def test_read_style_iter(tmpdir):
    '''
    Test if cell styles are read properly in iter mode.
    '''
    tmpdir.chdir()
    from openpyxl import Workbook
    from openpyxl.styles import Style, Font

    FONT_NAME = "Times New Roman"
    FONT_SIZE = 15
    ft = Font(name=FONT_NAME, size=FONT_SIZE)

    wb = Workbook()
    ws = wb.worksheets[0]
    cell = ws.cell('A1')
    cell.style = Style(font=ft)

    xlsx_file = "read_only_styles.xlsx"
    wb.save(xlsx_file)

    wb_iter = load_workbook(xlsx_file, read_only=True)
    ws_iter = wb_iter.worksheets[0]
    cell = ws_iter['A1']

    assert cell.style.font == ft

def writer(optimised, cols, rows):
    """
    Create a worksheet with variable width rows. Because data must be
    serialised row by row it is often the width of the rows which is most
    important.
    """
    wb = openpyxl.Workbook(optimized_write=optimised)
    ws = wb.create_sheet()
    row = list(range(cols))
    for idx in range(rows):
        if not (idx + 1) % rows/10:
            progress = "." * int((idx + 1) / (1 + rows/10))
            sys.stdout.write("\r" + progress)
            sys.stdout.flush()
        ws.append(row)
    folder = os.path.split(__file__)[0]
    print()
    wb.save(os.path.join(folder, "files", "large.xlsx"))

def export_to_xlsx(table, headers=None, title="Exported table", file_name=None):
    """
    Create a simple Excel workbook from the given table and optional headers.
    """
    assert_is_two_dimensional_list(table)
    # XXX https://bitbucket.org/openpyxl/openpyxl/issue/375/use-save_virtual_workbook-and-optimized
    wb = Workbook(write_only=False)
    ws = wb.active
    ws.title = title
    if (headers is not None):
        assert (len(table) == 0) or (len(headers) == len(table[0]))
        ws.append(headers)
    for row in table:
        ws.append(row)
    if (file_name is None):
        return save_virtual_workbook(wb)
    else:
        wb.save(file_name)
        return file_name

def writeExcel(poxy_list):
    """
    ??????????excel
    """
    pata = '/Users/wangjiacan/Desktop/shawn/????/agentPool.xlsx'
    title = ['ip', '??', '????',]

    if not os.path.exists(pata):
        workbook = openpyxl.Workbook()
        sheet = workbook.active
        sheet["A1"].value = title[0]
        sheet["B1"].value = title[1]
        sheet["C1"].value = title[2]
        workbook.save(pata)

    page_workbook = openpyxl.load_workbook(pata)
    page_sheet = page_workbook.get_sheet_by_name(page_workbook.get_sheet_names()[0])
    row = page_sheet.max_row
    for i in range(len(poxy_list)):
        page_sheet["A%d" % (row + i + 1)].value = poxy_list[i]['ip']
        page_sheet["B%d" % (row + i + 1)].value = poxy_list[i]['??']
        page_sheet["C%d" % (row + i + 1)].value = poxy_list[i]['????']
    page_workbook.save(pata)

def writeExcel(poxy_list):
    """
    ??????????excel
    """
    pata = '/Users/wangjiacan/Desktop/shawn/????/agentPool.xlsx'
    title = ['ip', '??', '????',]

    if not os.path.exists(pata):
        workbook = openpyxl.Workbook()
        sheet = workbook.active
        sheet["A1"].value = title[0]
        sheet["B1"].value = title[1]
        sheet["C1"].value = title[2]
        workbook.save(pata)

    page_workbook = openpyxl.load_workbook(pata)
    page_sheet = page_workbook.get_sheet_by_name(page_workbook.get_sheet_names()[0])
    row = page_sheet.max_row
    for i in range(len(poxy_list)):
        page_sheet["A%d" % (row + i + 1)].value = poxy_list[i]['ip:port']
        page_sheet["B%d" % (row + i + 1)].value = poxy_list[i]['http_type']
        page_sheet["C%d" % (row + i + 1)].value = poxy_list[i]['check_dtime']
    page_workbook.save(pata)

def __init__(self, output=None, **kwargs):
        super(XLSRenderer, self).__init__(**kwargs)

        # Make a single delegate text renderer for reuse. Most of the time we
        # will just replicate the output from the TextRenderer inside the
        # spreadsheet cell.
        self.delegate_text_renderer = text.TextRenderer(session=self.session)

        self.output = output or self.session.GetParameter("output")

        # If no output filename was give, just make a name based on the time
        # stamp.
        if self.output == None:
            self.output = "%s.xls" % time.ctime()

        try:
            self.wb = openpyxl.load_workbook(self.output)
            self.current_ws = self.wb.create_sheet()
        except IOError:
            self.wb = openpyxl.Workbook()
            self.current_ws = self.wb.active

def write_elements_data_to_excel_workbook(filepath, elements, details, elements_data):
    wb = openpyxl.Workbook()
    ws = wb.worksheets[0]
    for d in range(len(details)):
        ws.cell(row=1, column=1+3+d).value = details[d]
    for e in range(len(elements)):
        for d in range(3):
            ws.cell(row=1+1+e, column=1+d).value = elements[e][d]
    for e in range(len(elements_data)):
        for d in range(len(elements_data[e])):
            i = details.index(elements_data[e][d][0])
            ws.cell(row=1+1+e, column=1+3+i).value = elements_data[e][d][1]
    for c in range(3):
        ws.cell(row=1, column=1+c).value = ['number', 'name', 'symbol'][c]
    ws.freeze_panes = ws['D2']
    wb.save(filepath)

def excel_write(file = 'write_pyxl.xlsx', sheel_name = 'Sheet1', write_dict = dict_excel):
    wb = Workbook()
    ws = wb.active
    #ws = wb.create_sheet()
    #ws.title = "QYT PyXL"
    ws['A1'] = '??'
    ws['B1'] = '??'
    ws['C1'] = '??'
    row_location = 2 
    for x,y in write_dict.items():
        user_locatin = 'A' + str(row_location)
        pass_locatin = 'B' + str(row_location)
        priv_locatin = 'C' + str(row_location)
        ws[user_locatin] = x
        ws[pass_locatin] = y[0]
        ws[priv_locatin] = y[1]
        row_location += 1 
    wb.save(file)

def main(output_filename):
    workbook = openpyxl.Workbook()
    worksheet = workbook.active
    column_widths = defaultdict(int)
    for csvfile in glob.glob(os.path.join('.', '*.csv')):
        with open(csvfile, 'rb') as f:
            title = os.path.basename(csvfile).replace('.csv', '')
            worksheet.title = title
            reader = csv.reader(f, delimiter='\t')
            headers = None
            for r, row in enumerate(reader, start=1):
                if headers is None:
                    if not row[0].startswith('#'):
                        headers = [ header_type(key) for key in row ]
                for c, val in enumerate(row, start=1):
                    nv = numberize(val)
                    cell = worksheet.cell(row=r, column=c)
                    cell.value = nv
                    column_widths[c] = max(column_widths[c], string_width(nv))
        for col,column_width in column_widths.items():
            worksheet.column_dimensions[column_name(col)].width = column_width + 1
        worksheet = workbook.create_sheet()
        cols = set()
    workbook.save(output_filename)