2020-11-13 Python and Excel

1.基本操作

建立/開啟工作簿
訪問工作表單元及其值
儲存工作表

from openpyxl import Workbook

wb = Workbook()  # 例項化一個工作簿物件

print(wb)

from openpyxl import load_workbook

wb = load_workbook(filename='C:\Users\admin\Desktop\校慶邀請函式據.xlsx')

print(wb)

#工作簿建立時總是會預設建立一個名為 Sheet 工作表，可以通過使用Workbook.active 屬性獲取:

from openpyxl import load_workbook
wb = load_workbook(filename='C:\\Users\\admin\\Desktop\\校慶邀請函式據.xlsx')
ws = wb.active  # 獲取當前活躍的工作表
print(ws)

from openpyxl import Workbook

wb =Workbook()
ws = wb.create_sheet() # sheet
ws1 = wb.create_sheet("Mysheet")       # 命名為 Mysheet
ws2 = wb.create_sheet("Mysheet1", 0)   # 新建 Mysheet1 工作表插入到第一個位置
ws3 = wb.create_sheet("Mysheet2", -1)  # 新建 Mysheet2 工作表插入到倒數第二個的位置
ws.title = "lihuahua"  # 將上述 ws 工作表重新命名為 lihuahua
print(wb.sheetnames)
wb.save("C:/Users/admin/Desktop/我是李大海.xlsx")
print("Success")

繪圖 直方圖

import pandas as pd
import plotly.offline as off
import plotly.graph_objs as go


off.init_notebook_mode()

df = pd.read_excel("plot.xlsx", sheet_name='C:/Users/admin/Desktop/高等教育入學率')
trace1 = go.Bar(
        x=df['國家'],
        y=df[1995],
        name='1995',
        opacity=0.6,
        marker=dict(
                color='powderblue'
                )
        )

trace2 = go.Bar(
        x=df['國家'],
        y=df[2005],
        name='2005',
        opacity=0.6,
        marker=dict(
                color='aliceblue',
                )
        )

trace3 = go.Bar(
        x=df['國家'],
        y=df[2014],
        name='2014',
        opacity=0.6,
        marker=dict(
                color='royalblue'
                )
        )

layout = go.Layout(barmode='group')
data = [trace1, trace2, trace3]
fig = go.Figure(data, layout)
off.plot(fig)

繪圖 雷達圖

df = pd.read_excel('plot.xlsx', sheet_name='政治治理')
theta = df.columns.tolist()
theta.append(theta[0])
names = df.index
df[''] = df.iloc[:,0]
df = np.array(df)

trace1 = go.Scatterpolar(
        r=df[0],
        theta=theta,
        name=names[0]
        )

trace2 = go.Scatterpolar(
        r=df[1],
        theta=theta,
        name=names[1]
        )

trace3 = go.Scatterpolar(
        r=df[2],
        theta=theta,
        name=names[2]
        )

trace4 = go.Scatterpolar(
        r=df[3],
        theta=theta,
        name=names[3]
        )

data = [trace1, trace2, trace3, trace4]
layout = go.Layout(
        polar=dict(
                radialaxis=dict(
                        visible=True,
                        range=[0,1]
                        )
                ),
        showlegend=True
        )
fig = go.Figure(data, layout)
off.plot(fig)