Python------Excel

写一下最近用过的python excel包

xlrd及作图

import xlrd
import matplotlib.pyplot as plt
import matplotlib

# 打开workbook
wb = xlrd.open_workbook(r'pathToExcelFile')
# 获取excel中的第一张表(也可以sheet_by_name())
sh = wb.sheet_by_index(0)
# sh.nrows可以得到sh表的总行数
# 获取表中单元格的数据（excel默认数据类型是float）
# 此坐标是从(0,0)开始，按需获取
cell = sh.cell_value(行，列)
# 作图需要 三维图
fig = plt.figure()
ax = fig.add_subplot(111,projection='3d')
# 散点图
ax.scatter()
# 在图上显示汉字
zhfont1 = matplotlib.font_manager.FontProperties(fname=r'D:\Python34\Lib\site-packages\matplotlib\mpl-data\fonts\ttf\msyh.ttf')
ax.set_title(u'汉字',fontproperties=zhfont1)
ax.set_xlabel(u'汉字',fontproperties=zhfont1)
ax.set_ylabel(u'汉字',fontproperties=zhfont1)
ax.set_zlabel(u'汉字',fontproperties=zhfont1)
plt.savefig("1.png")
plt.show()

xlwt （写入行数为0~65535）

import xlwt
# 创建Workbook,编码为ascii
wb1 = xlwt.Workbook(encoding='ascii')
# 创建名为Worksheet One的worksheet
worksheet = wb1.add_sheet('Worksheet One')
# 向单元格写入数据
worksheet.write(行,列,值)
# 保存
wb1.save('ShaiXuan.xls')

openpyxl （写入行数不受65535限制，文件格式可为xlsx）

from openpyxl import Workbook
from openpyxl.worksheet.datavalidation import DataValidation

# Create the workbook and worksheet we'll be working with
wb1 = Workbook()
ws = wb1.active
# Create a data-validation object with list validation
# dv = DataValidation(type="list", formula1='"Dog,Cat,Bat"', allow_blank=True)
dv = DataValidation(type="decimal")
ws.add_data_validation(dv)
# openpyxl的单元格坐标是用excel的行列来表示的 i.e.A1 B3 C5
# 向单元格A1写入数据需要cell = ws["A1"]
# 想连续向单元格写数据，可用以下方法
# r是需要循环的数字，如 
for r in range(1,10):
# 将R转为str类型，然后列字母加上str，利用py的字符串连接，可表示出来单元格“Ar”
# 这样就可以连续向A1 A2 A3 到 A9 单元格插入数据，不用每次给单元格赋值时都写出来“A1” “A2” “A3”
    cell = ws["A" + str(r)]
    cell.value = "Dog"
    dv.add(cell)
wb1.save("ShaiXuan_openpyxl.xlsx")

# 还有个ExcelWriter类，有些地方还不会用，但上面的DataValidation够用了
# from openpyxl.writer.excel import ExcelWriter

用openpyxl筛选某Excel中的数据，然后存入新表的例子：

# -*- coding: UTF-8 -*-
import xlrd
from openpyxl import Workbook
from openpyxl.worksheet.datavalidation import DataValidation

wb = xlrd.open_workbook(r'some.xlsx')
sh = wb.sheet_by_index(0)
# Create the workbook and worksheet we'll be working with
wb1 = Workbook()
ws = wb1.active
# Create a data-validation object with list validation
# dv = DataValidation(type="list", formula1='"Dog,Cat,Bat"', allow_blank=True)
dv = DataValidation(type="decimal")
ws.add_data_validation(dv)
r = 1
for rows in range(1,sh.nrows):
    if sh.cell_value(rows,8)>4000 and sh.cell_value(rows,8)<16000:
        print(rows)
        cell = ws["A" + str(r)]
        cell.value = sh.cell_value(rows,1)
        dv.add(cell)
        cell = ws["B" + str(r)]
        cell.value = sh.cell_value(rows,3)
        dv.add(cell)
        cell = ws["C" + str(r)]
        cell.value = sh.cell_value(rows,8)
        dv.add(cell)
        r = r + 1
print("共有", (r-1), "行")
wb1.save("some_Shai_openpyxl.xlsx")

更多用法请看openpyxl、xlrd、xlwt的官方文档