Python openpyxl.Workbook() Examples

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 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 

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 

def test_excel_writer():
    table = Table(header=[['header1', 'header2', 'header3']],
                  body=[[1, 2, 3], [1, 2, 4], [1, 3, 5], [2, 3, 4], [2, 4, 5]])

    areas = table.body.select(ColumnSelector(lambda col: col == 1))
    areas.group().merge().left.summary(label_span=1, label='total')

    area = Area(table, 3, 7, (1, 0))
    area.summary(label_span=2, label='total')

    wb = Workbook()
    ws = wb.active
    # must be unicode
    ws.title = '报表'
    ws.sheet_properties.tabColor = "1072BA"

    WorkSheetWriter.write(ws, table, (1, 1))

    wb.save('1.xlsx')


# todo: dictnary pool,cell pool etc. 

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') 

def main(log_dir, output_filename):
    logs = []
    for dirpath, dirnames, filenames in os.walk(log_dir):
        for filename in filenames:
            # The shop is the last directory
            shop = os.path.basename(dirpath)
            fullpath = os.path.join(dirpath, filename)
            logs.extend(get_logs_from_file(shop, fullpath))

    # Create and save the Excel sheet
    xlsfile = openpyxl.Workbook()
    sheet = xlsfile['Sheet']

    # Write the first row
    sheet.append(SaleLog.row_header())
    for log in logs:
        sheet.append(log.row())

    xlsfile.save(output_filename) 

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 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 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)

#定义全局列表方便调用 

def save_spreadsheet(filename, data_sample):
    
    wb = Workbook()
    ws = wb.active

    rowIndex = 1
    for rows in data_sample:
        colIndex = 1 
        for field in rows:
            colIndex2 = get_column_letter(colIndex)
            ws.cell('%s%s'%(colIndex2, rowIndex)).value = field
            colIndex +=1
        rowIndex += 1
    
    wb.save(filename)

## Stage 4 end

# --------------------------------------

## Stage 5 begin 

def attendance_workbook(class_name):
    sl = StudentsList(class_name)
    tupl = sl.load_pkl_file()
    global NAMES, ROLLS, NUMBER_OF_STUDENTS
    NAMES = tupl[0]
    ROLLS = tupl[1]
    NUMBER_OF_STUDENTS = len(NAMES)
    # Current length of NAMES is len(NAMES)
    filename = make_file_name(class_name)
    try:
        wb = load_workbook(filename)
    except:
        wb = Workbook()
    wb.guess_types = True
    if not current_month_exists(wb):
        createWorksheet(wb)
    wb.save(filename)
    return wb 

def generate_excel(temp_list,inputfiles):
    rows = len(temp_list)

    wb = Workbook()

    dest_filename = inputfiles[:-4] + '.xlsx'
    ws1 = wb.active

    ws1.title = "Analysis report V1"

    for row in range(rows):
        aline = temp_list[row]
        aline = parse_line(aline)
        for col in range(len(aline)):
            ws1.cell(column=col + 1, row=row + 1, value="{0}".format(aline[col]))

    wb.save(filename = dest_filename) 

def init():
    print('初始化中')
    if not os.path.exists(downloadPath):
        print('正在创建文件夹download...')
        os.makedirs(downloadPath)
        print('done...')
    if not os.path.exists(taskPath):
        print('正在创建task文件')
        os.makedirs(taskPath)
        # 第一次跑脚本时候使用
        taskMaker(start=start_num, end=end_num)
        print('done...')
    if not os.path.exists(imagePath):
        print('正在创建文件夹image...')
        os.makedirs(imagePath)
        print('done...')
    if not os.path.exists(wbName):
        print('正在创建Excel...')
        wb = Workbook()
        wb.create_sheet(title=wsName)
        wb.save(wbName)
        print('done...')
    print('初始化完成') 

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 

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) 

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() 

def __store_table(tabdata, filename, format='csv'):
    """Saves table data as csv file"""
    if format == 'csv':
        f = open(filename, 'w')
        w = csv.writer(f, delimiter=',')
        for row in tabdata:
            w.writerow(row)
    elif format == 'tsv':
        f = open(filename, 'w')
        w = csv.writer(f, delimiter='\t')
        for row in tabdata:
            w.writerow(row)
    elif format == 'xls':
        workbook = xlwt.Workbook()
        ws = __xls_table_to_sheet(tabdata, workbook.add_sheet('0'))
        print(dir(ws))
        workbook.save(filename)
    elif format == 'xlsx':
        workbook = openpyxl.Workbook()
        ws = __xlsx_table_to_sheet(tabdata, workbook.create_sheet('0'))
        workbook.save(filename) 

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) 

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 

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 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') 

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') 

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 multiplicationTable(n, filename='multiplicationTable.xlsx'):
    """ create multiplication table, store in excel file
    Args:
        n (int): n for nxn multiplication table
        filename (str): name of excel file to store table in
    Returns:
        None
    """
    # create excel file
    wb = openpyxl.Workbook()
    # create worksheet
    sheet = wb.active
    sheet.title = '{}x{} multiplication table'.format(n, n)
    boldFont = Font(bold=True)
    
    # write row headers
    for i in range(1, n+1):
        sheet.cell(row=i+1, column=1).value = i
        sheet.cell(row=i+1, column=1).font = boldFont

    # write column headers
    for i in range(1, n+1):
        sheet.cell(row=1, column=i+1).value = i
        sheet.cell(row=1, column=i+1).font = boldFont

    # write multiplication table
    for row in range(1, n+1):
        for col in range(1, n+1):
            sheet.cell(row=row+1, column=col+1).value = row*col

    # save table
    wb.save(filename) 

def write_to_excel(filename, table, position=(0, 0)):
    """write table into excel. 
    
    If the file does not exist, a new file will be created. If the file has already
    existed, the file will be rewritten.
    
    By default, the table will be written in default worksheet at position (0,0).
    You can change the position by setting ``position`` argument.
    """
    wb = Workbook()
    ws = wb.active
    WorkSheetWriter.write(ws, table, position)

    wb.save(filename) 

def createxls(config, mode):
    sheet = 'OF_' + config.OF_option if not config.HYDRA else 'Hydra_OF_'\
        + config.OF_option
    try:
        wb = openpyxl.load_workbook(
            config.xlsfile.replace('.xlsx', '_' + mode + '.xlsx'))
    except BaseException:
        wb = openpyxl.Workbook()
        wb.remove_sheet(wb.active)

    try:
        ws = wb.get_sheet_by_name(sheet)
    except BaseException:
        ws = wb.create_sheet(sheet)

    count = 1
    start_pos = [count]
    count = createSectionxls(ws, '0.5', count)
    start_pos.append(count)
    count = createSectionxls(ws, 'median3', count)
    start_pos.append(count)
    count = createSectionxls(ws, 'median5', count)
    start_pos.append(count)
    count = createSectionxls(ws, 'median7', count)
    start_pos.append(count)
    count = createSectionxls(ws, '1', count)
    return wb, ws, start_pos 

def __init__(self, request):
        self.request = request
        self.w = Workbook() 

def open(self):
        self.workbook = openpyxl.Workbook() 

def _query_results_to_xlsx(query_results: DocumentQueryResults) -> HttpResponse:
    from openpyxl import Workbook
    from openpyxl.writer.excel import save_virtual_workbook
    from openpyxl.styles import Font, Alignment

    wb = Workbook()
    ws = wb.active
    ws.append(query_results.column_titles)
    for cells in ws.rows:
        for cell in cells:
            cell.font = Font(name=cell.font.name, bold=True)
            cell.alignment = Alignment(horizontal='center')
        break

    for row in query_results.fetch():
        ws.append(row)

    def str_len(value):
        return len(str(value)) if value is not None else 0

    for column_cells in ws.columns:
        length = min(max(str_len(cell.value) for cell in column_cells), 100) + 1
        ws.column_dimensions[column_cells[0].column_letter].width = length

    response = HttpResponse(save_virtual_workbook(wb),
                            content_type='application/vnd.openxmlformats-officedocument.spreadsheetml.sheet')
    response['Content-Disposition'] = 'attachment; filename=export.xlsx'
    return response 

def _upload_spreadsheet(self, usage_file, spreadsheet):
        # type: (UsageFile, openpyxl.Workbook) -> None

        # Generate spreadsheet file
        with NamedTemporaryFile() as tmp:
            spreadsheet.save(tmp)
            tmp.seek(0)
            file_contents = tmp.read()

        # Setup request
        url = '{}usage/files/{}/upload/'.format(self.config.api_url, usage_file.id)
        headers = self._api.headers
        headers['Accept'] = 'application/json'
        del headers['Content-Type']  # This must NOT be set for multipart post requests
        multipart = {'usage_file': ('usage_file.xlsx', file_contents)}
        self.logger.info('HTTP Request: {} - {} - {}'.format(url, headers, multipart))

        # Post request
        try:
            content, status = self._api.post(
                url=url,
                headers=headers,
                files=multipart)
        except requests.RequestException as ex:
            raise FileCreationError('Error uploading file: {}'.format(ex))
        self.logger.info('HTTP Code: {}'.format(status))
        if status != 201:
            msg = 'Unexpected server response, returned code {}'.format(status)
            self.logger.error('{} -- Raw response: {}'.format(msg, content))
            raise FileCreationError(msg)