Python openpyxl.Workbook方法代码示例

# 需要导入模块: import openpyxl [as 别名]
# 或者: from openpyxl import Workbook [as 别名]
def __init__(self, file=None, template_styles=None, timestamp=None, templated_sheets=None, keep_vba=False,
                  data_only=False, keep_links=True):
        super(TemplatedWorkbook, self).__init__()

        self.workbook = load_workbook(
            filename=file,
            data_only=data_only,
            keep_vba=keep_vba,
            keep_links=keep_links
        ) 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() 

# 需要导入模块: import openpyxl [as 别名]
# 或者: from openpyxl import Workbook [as 别名]
def main():
    print('Starting Write Excel Example with openPyXL')

    workbook = Workbook()
    # Get the current active worksheet
    ws = workbook.active
    ws.title = 'my worksheet'
    ws.sheet_properties.tabColor = '1072BA'

    ws['A1'] = 42
    ws['A2'] = 12
    ws['A3'] = '=SUM(A1, A2)'

    ws2 = workbook.create_sheet(title='my other sheet')
    ws2['A1'] = 3.42
    ws2.append([1, 2, 3])
    ws2.cell(column=2, row=1, value=15)

    workbook.save('sample.xlsx')

    print('Done Write Excel Example') 

# 需要导入模块: import openpyxl [as 别名]
# 或者: from openpyxl import Workbook [as 别名]
def test_write_append_mode(self, merge_cells, ext, engine, mode, expected):
        import openpyxl
        df = DataFrame([1], columns=['baz'])

        with ensure_clean(ext) as f:
            wb = openpyxl.Workbook()
            wb.worksheets[0].title = 'foo'
            wb.worksheets[0]['A1'].value = 'foo'
            wb.create_sheet('bar')
            wb.worksheets[1]['A1'].value = 'bar'
            wb.save(f)

            writer = ExcelWriter(f, engine=engine, mode=mode)
            df.to_excel(writer, sheet_name='baz', index=False)
            writer.save()

            wb2 = openpyxl.load_workbook(f)
            result = [sheet.title for sheet in wb2.worksheets]
            assert result == expected

            for index, cell_value in enumerate(expected):
                assert wb2.worksheets[index]['A1'].value == cell_value 

# 需要导入模块: import openpyxl [as 别名]
# 或者: from openpyxl import Workbook [as 别名]
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 

# 需要导入模块: import openpyxl [as 别名]
# 或者: from openpyxl import Workbook [as 别名]
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)) 

# 需要导入模块: import openpyxl [as 别名]
# 或者: from openpyxl import Workbook [as 别名]
def creat_xlsx():
    if os.path.exists(define.filename) == False:
        s = 0
        wb = ws.Workbook()
        ws1 = wb.active
        if os.path.exists('out/') == False:
            os.mkdir('out')
        word=['风险目标','风险名称','风险等级(3-0由高危到infomation)','风险参数','风险地址','风险请求','整改意见','风险描述','风险详情']
        for i in word:
            s = s + 1
            ws1.cell(row =1,column = s,value = i)
        wb.save(define.filename)
        print(define.RED+"[*]创建文件成功 %s"%define.filename)
    else:
        print(define.RED+"[*]文件已存在 文件为:%s"%define.filename)

#定义全局列表方便调用 

# 需要导入模块: import openpyxl [as 别名]
# 或者: from openpyxl import Workbook [as 别名]
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) 

# 需要导入模块: import openpyxl [as 别名]
# 或者: from openpyxl import Workbook [as 别名]
def post(self):
        filename = self.get_argument('filename')
        items = set(self.get_argument('items').split(','))
        workbook = Workbook()
        workbook.remove(workbook.active)
        for item in items:
            if item == 'friend':
                self._export_friend(workbook)
            elif item == 'movie':
                self._export_movie(workbook)
            elif item == 'music':
                self._export_music(workbook)
            elif item == 'book':
                self._export_book(workbook)
            elif item == 'broadcast':
                self._export_broadcast(workbook)
                
        workbook.save(filename)
        self.write('OK') 

# 需要导入模块: import openpyxl [as 别名]
# 或者: from openpyxl import Workbook [as 别名]
def convert_xls_to_xlsx(src_file_path, dst_file_path):

    print (src_file_path, dst_file_path)
    book_xls = xlrd.open_workbook(src_file_path)
    book_xlsx = Workbook()

    sheet_names = book_xls.sheet_names()
    for sheet_index in range(0,len(sheet_names)):
        sheet_xls = book_xls.sheet_by_name(sheet_names[sheet_index])
        if sheet_index == 0:
            sheet_xlsx = book_xlsx.get_active_sheet()
            sheet_xlsx.title = sheet_names[sheet_index]
        else:
            sheet_xlsx = book_xlsx.create_sheet(title=sheet_names[sheet_index])

        for row in range(0, sheet_xls.nrows):
            for col in range(0, sheet_xls.ncols):
                sheet_xlsx.cell(row = row+1 , column = col+1).value = sheet_xls.cell_value(row, col)

    book_xlsx.save(dst_file_path)
    return dst_file_path 

# 需要导入模块: import openpyxl [as 别名]
# 或者: from openpyxl import Workbook [as 别名]
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) 

# 需要导入模块: import openpyxl [as 别名]
# 或者: from openpyxl import Workbook [as 别名]
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') 

# 需要导入模块: import openpyxl [as 别名]
# 或者: from openpyxl import Workbook [as 别名]
def export_to_excel(dbapi, xlsx_path):
    from openpyxl import Workbook

    if os.path.exists(xlsx_path):
        raise RuntimeError("The Excel file '%s' has existed" % xlsx_path)

    wb = Workbook()
    ws = wb.active
    ws["A1"] = "SID"
    ws["B1"] = "Song Name"
    ws["C1"] = "Album Name"
    ws["D1"] = "Singer Name"
    ws["E1"] = "URL"
    for i, m in enumerate(dbapi.get_checked_musics(), 2):
        ws["A%s" % i] = m.sid
        ws["B%s" % i] = m.name
        ws["C%s" % i] = m.ablum
        ws["D%s" % i] = m.singer
        ws["E%s" % i] = m.url
    wb.save(xlsx_path) 

# 需要导入模块: import openpyxl [as 别名]
# 或者: from openpyxl import Workbook [as 别名]
def make_download(self, es_dump_path):
        self.workbook = Workbook(write_only=True)

        # Process ES dump file
        f = open(es_dump_path, encoding='utf-8')
        while True:
            # Read 2 lines in the dump file
            type_line = f.readline()
            source_line = f.readline()
            if not type_line:
                break
            self.process_entity(type_line, source_line, self.write_xls_row)

        # Finalize
        xls_path = os.path.splitext(es_dump_path)[0] + '.xlsx'
        self.workbook.save(filename=xls_path)
        f.close()
        return xls_path, MIME_TYPE 

# 需要导入模块: import openpyxl [as 别名]
# 或者: from openpyxl import Workbook [as 别名]
def write_account_transaction_to_excel(filename, account):
    print('Starting write of Excel example')
    workbook = Workbook()
    # Get the current active worksheet
    ws = workbook.active
    ws.title = 'transactions'

    ws['A1'] = 'transaction type'
    ws['B1'] = 'amount'

    row = 2

    # Write out the transactions
    for transaction in account.history:
        ws['A' + str(row)] = transaction.action
        ws['B' + str(row)] = transaction.amount
        row += 1

    workbook.save(filename)

    print('Done Write Excel Example') 

# 需要导入模块: import openpyxl [as 别名]
# 或者: from openpyxl import Workbook [as 别名]
def textToSheet(directory, filename):
    """converts text files to columns in excel worksheet
    Args:
        directory (str): folder containing text files
        filename (str): name of excel file
    Returns:
        None
    """
    wb = openpyxl.Workbook()
    wb.create_sheet(index=0, title='result')
    sheet = wb.active

    colIndex = 1

    # write text files as columns in worksheet
    for file in os.listdir():
        if file.endswith('.txt'):
            rowIndex = 1
            with open(file) as f:
                for line in f:
                    sheet.cell(row=rowIndex, column=colIndex).value = line
                    rowIndex += 1
            colIndex += 1

    wb.save(filename)