Python读取excel

openpyxl

# Write a workbook 创建
from openpyxl import Workbook
from openpyxl.utils import get_column_letter
wb = Workbook()
dest_filename = 'empty_book.xlsx'
ws1 = wb.active
ws1.title = "range names"
for row in range(1, 40):
        ws1.append(range(600))
ws2 = wb.create_sheet(title="Pi")
ws2['F5'] = 3.14
ws3 = wb.create_sheet(title="Data")
for row in range(10, 20):
     for col in range(27, 54):
         _ = ws3.cell(column=col, row=row, value="{0}".format(get_column_letter(col)))
wb.save(filename = dest_filename)

# Read an existing workbook 读取
from openpyxl import load_workbook
wb = load_workbook(filename = 'empty_book.xlsx')
sheet1 = wb['Sheet1']
print(sheet_1['D1'].value)

pandas

# 读取
import pandas as pd
titanic = pd.read_csv("data/titanic.csv")
titanic = pd.read_excel('titanic.xlsx', sheet_name='passengers')
ages = titanic["Age"]
ages.head()
age_sex = titanic[["Age", "Sex"]]
age_sex.head()
# 创建
df = pd.DataFrame()
df.to_excel('excel_path')

openpyxl和pandas配合使用

# 这种写法可避免pandas每次向Excel写入sheet，覆盖之前的sheet

book = openpyxl.load_workbook(excel_path)
writer = pd.ExcelWriter(excel_path, engine='openpyxl')
writer.book = book
writer.sheets = dict((ws.title, ws) for ws in book.worksheets)
# sheet = writer.sheets.get('sheetname')

# 将data写入writer
data.to_excel(writer,sheet_name="",index=False)
writer.save()

rpa

# 读取
excel=rpa.excel.open(path,visible=True,readonly=True)
sheet=excel.get_sheet('质检表')
rowsOfheet=sheet.row_count()
# 创建
xls = rpa.excel.create()
xls.save() # 保存
xls.close() # 关闭