数据分析(六) 使用openpyxl读写xlsx文件

简单的开始

from openpyxl import Workbook
import datetime

wb = Workbook()

# 获取工作表
ws = wb.active

# 直接给单元格赋值
ws['A1'] = 42

# 使用appended添加值
ws.append([1, 2, 3])

# 直接写入Python类型
ws['A2'] = datetime.datetime.now()

# 保存工作表
wb.save("sample.xlsx")

 

读取已有表格

import openpyxl
wb = openpyxl.load_workbook('sample.xlsx')
ws = wb.active
for row in ws:
    for cell in row:
        print(cell)
        # print(cell.value)

 

<Cell 'Sheet'.A1>
<Cell 'Sheet'.B1>
<Cell 'Sheet'.C1>
<Cell 'Sheet'.A2>
<Cell 'Sheet'.B2>
<Cell 'Sheet'.C2>

 

与DataFrame交互

import numpy as np 
import pandas as pd 
from openpyxl import Workbook
from openpyxl.utils.dataframe import dataframe_to_rows  


wb = Workbook()
ws = wb.active

df = pd.DataFrame({
'A':['A1','A2','A3','A4'],
'B':['B1','B2','B3','B4'],
'C':['C1','C2','C3','C4'],
'D':['D1','D2','D3','D4']
    })



for r in dataframe_to_rows(df,index = True,header = True):
    ws.append(r)

wb.save("pandas_openpyxl.xlsx")

将DataFrame数据保存至xlsx文件