Skip to content

openpyxl.md

Latest commit

 

History

History
169 lines (132 loc) · 3.66 KB

openpyxl.md

File metadata and controls

169 lines (132 loc) · 3.66 KB

openpyxl

  • 合并单元格 sheet.merge_cells( start_row=1, end_row=2, start_column=1, end_column=2, )
  • 样式
    • openpyxl.styles.Alignment Alignment(vertical='center')

Install

pip install openpyxl
pip install pillow  # 如果你需要插入图片
  • 读取数据
from openpyxl import Workbook
from openpyxl import load_workbook
wb = load_workbook(<filename>)
ws = wb.active  # 旧版本使用 ws = wb.get_active_sheet()
for row in ws.rows:
    for cell in row:
        print(cell.value)
  • 写入数据
ws['A4'] = 4
ws.cell(row=1, column=1, value=1)  # 因为excel是从1开始的，所以这里也是从1开始

Workbook

  • 读取workbook
    load_workbook(filename, data_only=True)  # data_only会把公式变成value
  • 获取sheet, 操作sheet
    wb = load_workbook(filename)
    wb.sheetnames
    wb['其他sheetname']  # 获取sheet, 操作sheet
    wb.active = wb["another sheet"]  # 修改激活的sheet

Simple Usage

Merge Cells

When you merge cells the column and row number starts from 1

>>> from openpyxl.workbook import Workbook
>>>
>>> wb = Workbook()
>>> ws = wb.active
>>>
>>> ws.merge_cells('A2:D2')
>>> ws.unmerge_cells('A2:D2')
>>> 
>>> # or equivalently
>>> ws.merge_cells(start_row=2, start_column=1, end_row=4, end_column=4)
>>> ws.unmerge_cells(start_row=2, start_column=1, end_row=4, end_column=4)

Insert an Image

>>> from openpyxl import Workbook
>>> from openpyxl.drawing.image import Image
>>>
>>> wb = Workbook()
>>> ws = wb.active
>>> ws['A1'] = 'You should see three logos below'
>>> img = Image('logo.png')
>>> # add to worksheet and anchor next to cells
>>> ws.add_image(img, 'A1')
>>> wb.save('logo.xlsx')

>>> image = Image("logo.png")
>>> image.width = 500
>>> image.height = 370
>>> ws.add_image(image, 'B2')

Working with Styles

Worksheet Tables

  • 例子
from openpyxl import Workbook
from openpyxl.worksheet.table import Table, TableStyleInfo

wb = Workbook()
ws = wb.active

data = [
    ['Apples', 10000, 5000, 8000, 6000],
    ['Pears',   2000, 3000, 4000, 5000],
    ['Bananas', 6000, 6000, 6500, 6000],
    ['Oranges',  500,  300,  200,  700],
]

## add column headings. NB. these must be strings
ws.append(["Fruit", "2011", "2012", "2013", "2014"])
for row in data:
    ws.append(row)

tab = Table(displayName="Table1", ref="A1:E5")

## Add a default style with striped rows and banded columns
style = TableStyleInfo(name="TableStyleMedium9", showFirstColumn=False,
                       showLastColumn=False, showRowStripes=True, showColumnStripes=True)
tab.tableStyleInfo = style
ws.add_table(tab)
wb.save("table.xlsx")
  • max_row: 数字最大行数
  • max_column: 数字最大宽度

Utils

  • openpyxl.utils.column_index_from_string
column_index_from_string('A') => 1
  • get_column_letter
get_column_letter(1) => A

xlrd

用来处理xls文件

  • 打开文件

    workbook = xlrd.open_workbook(filename)
# 但是更多的情况是文件已经在内存的
data = open(filename, 'rb').read()
workbook = xlrd.open_workbook(file_contents=data)

  • Sheets

    sheet = workbook.sheets()[0] # 获取 workbook 的sheets
sheet.nrows # 获取 sheet 的行数
sheet.row_values(1) # 获取某一行的数据(返回一个 list)