python中操作excel的常用库和方法

本文介绍: 无论办公自动化或者数据分析中，我们常会用到 excel 表格。在python中都有哪些库处理数据表格？下面就说明一下在python中有哪些库能够处理数据表格。

无论办公自动化或者数据分析中，我们常会用到excel 表格。在python中都有哪些库处理数据表格？下面就说明一下在py t h on中有哪些库能够处理数据表格。

xlwt库

pip install xlwt

xlwt库仅仅能向excel 中写入数据，流程如下：


import xlwt

book = xlwt.Workbook()

sheet = book.add_sheet("Sheet1")

sheet.write(0,0,"你好")

book.save("record.xls")

在这里插入图片描述

openpyxl库

pip install openpyxl

生成一个 excel 表格，流程如下：


import openpyxl

wb = openpyxl.Workbook()

print(wb.sheetnames)
# ws  = wb.active

ws2 = wb.create_sheet("NewTitle",1)
ws2.sheet_properties.tabColor = 'ff00ff'
# 下表是从1开始的
ws2.cell(row=1,column=1,value="你好")

wb.save("record1.xlsx")

在这里插入图片描述
打开一个已存在 excel 文件，并写入新内容


wb = openpyxl.load_workbook("record1.xlsx")

ws = wb['Sheet']

ws.sheet_properties.tabColor = '99cccc'

# 下表是从1开始的
ws.cell(row=1,column=1,value="重新写入内容")

wb.save("record2.xlsx")

在这里插入图片描述
批量写入列表数据


import openpyxl
from openpyxl.styles import colors,Font,Alignment

wb = openpyxl.Workbook()

ws = wb.active

rows = [
    ["ID","姓名","性别"],
    [1,"老朱","男"],
    [2,"老朱1","男"],
    [3,"老朱2","男"],
]

for row in rows:
    ws.append(row)

font = Font(name="微软雅黑",size=25,italic=True,bold=True)

ws['A1'].font = font

ws.row_dimensions[5].height = 40
ws.column_dimensions['A'].width = 30

ws.merge_cells("A7:C7")
ws.merge_cells("A9:C13")
ws['A9'] = "合并单元格"

wb.save("record3.xlsx")

在这里插入图片描述
下面是一个在excel中生成日历的简单演示


import calendar
import time
import openpyxl

from openpyxl.styles import Font

year = time.strftime("%Y", time.localtime())


#周六为每周第一天
calendar.setfirstweekday(firstweekday=6)


wb = openpyxl.Workbook()
for i in range(12,0,-1):
    # 创建一个sheet
    sheet = wb.create_sheet(index=0,title= str(i) + '月')
    
    # 每月天数
    days = calendar.monthcalendar(int(year),i)
    
    for j in range(len(days)): 
        for k in range(len(days[j])):
            value = days[j][k]
            if value == 0:
                value = ''
            else:
                sheet.cell(row=j+9,column=k+1).font =  Font(u"微软雅黑",size=11)
    
            sheet.cell(row=j+9,column=k+1).value = value

wb.save("calendar.xlsx")
wb

calend ar.xlsx文件中生成12个sheet，每个sheet中显示日历信息。

效果如下图：

在这里插入图片描述

pandas库

最后就是做数据分析的pandas库了。通过 read_excel和to_exc el进行数据读取和写入。

读取一个xlsx文件，进行数据处理后，存储在person2.xlsx中。


import pandas as pd
import datetime
from datetime import date

#skiprows跳过几行 usecols第几列,dtype设置某一类的类型
persons = pd.read_excel("people.xlsx",skiprows="1",usecols="A:K",index_col="编号",dtype={"旷工天数":str})

startday = datetime.datetime.now()

# 增加了2列，生成和出勤天数
for i in persons.index:
    persons.at[i,'生日'] = date(startday.year+i,startday.month,startday.day)
    persons.at[i,"出勤天数"] = 1 if i % 2 == 1 else 0

persons.to_excel("person2.xlsx")

print("Done")

在这里插入图片描述
而且可以通过 pandas读取excel数据并进一步处理后生成各种图表。

下面给出pands生成几种常见图表示例：

柱状图

import pandas as pd
import matplotlib.pyplot as plt


persons = pd.read_excel("person2.xlsx",index_col="编号")

#通过实发工资，进行排序，inplace原地修改,ascending False从大到小，True从小到大
persons.sort_values(by="实发工资",inplace=True,ascending=False)

#plt.bar 生成树状图
plt.bar(persons['姓名'],persons['实发工资'],color="Orange")

#添加中文字体支持
from matplotlib.font_manager import FontProperties

font = FontProperties(fname=r"c:/windows/fonts/simhei.ttf",size=15)

# 设置图标标题，横纵坐标
plt.title("员工工资统计",fontproperties=font,fontsize=15)
plt.xlabel("姓名",fontproperties=font,fontsize=12)
plt.ylabel("工资",fontproperties=font,fontsize=12)

# 因为xlabel过长，使用rotaion翻转90
plt.xticks(persons['姓名'],font=font,rotation=90)

plt.show()

在这里插入图片描述

叠加树状图


#叠加树状图
import pandas as pd
import matplotlib.pyplot as plt

users = pd.read_excel("test.xlsx")

users['total'] = users['yuwen'] + users['yingyu'] + users['shuxue']

users.sort_values(by="total",inplace=True,ascending=False)

users.plot.bar(x="name",y=["yuwen","shuxue","yingyu"],stacked=True)

plt.tight_layout()

plt.show

在这里插入图片描述


#横向的叠加树状图
users.plot.barh(x="name",y=["yuwen","shuxue","yingyu"],stacked=True)

plt.tight_layout()

plt.show

饼图


#饼图绘图，体现占比

import pandas as pd
import matplotlib.pyplot as plt

totals = pd.read_excel("test1.xlsx",index_col="from")

#通过pandas的plot.pie绘制饼图，饼图只需要使用一列数据，我们直接使用Series
#counterclock为False让饼图顺时针方向显示
#startangle其实角度，从0度角开始
totals["order"].plot.pie(fontsize=9,counterclock=False,startangle=0)

#订单饼图
plt.title("order",fontsize=15,fontweight="bold")
plt.ylabel("order",fontsize=12,fontweight="bold")
plt.show()

plt.show()

在这里插入图片描述

折线图


#通过pandas绘制折线图

import pandas as pd
import matplotlib.pyplot as plt

persons = pd.read_excel("test.xlsx",index_col="id")

persons.plot(y=['yuwen','yingyu','shuxue'])

plt.title('Title',fontsize=15)
plt.xlabel("student no")
plt.xticks(persons.index)
plt.show()

在这里插入图片描述


#仅仅plot.area方法
persons.plot.area(y=['yuwen','yingyu','shuxue'])

plt.title('Title',fontsize=15)
plt.xlabel("student no")
plt.xticks(persons.index)
plt.show()