11-python文件I/O-excel文件读取

1、包的下载与安装

• 下载说明（python3 读写Excel）

  说明

• 2007版以前的Excel（xls结尾的），需要使用xlrd读，xlwt写。

• 2007版以后的Excel（xlsx结尾的），需要使用openpyxl来读写。

  pypi的地址：

• https://pypi.python.org/pypi/xlwt

• https://pypi.python.org/pypi/xlrd

• https://pypi.python.org/pypi/openpyxl

  openpyxl文档地址：

https://openpyxl.readthedocs.io/en/latest/changes.html

xlrd包下载网址：https://pypi.python.org/pypi/xlrd
pyExcelerator 0.6.4a包下载网址：https://pypi.org/project/pyExcelerator/#files

安装

下载后解压，然后通过命令行进入下载的目录，然后C:\python34\python.exe setup.py install即可

• 1. 进入xlrd解压后的目录
     D:\xlrd-1.1.0>

• 2.安装
  

  python setup.py install image.png
• 3. 进入pyExcelerator安装目录
     D:\pyexcelerator-0.6.4.1>

• 4.安装
  

  python setup.py install image.png

2、案例1：

import openpyxl
import xlrd
import xlwt

def write03Excel(path):
    wb = xlwt.Workbook()
    sheet = wb.add_sheet("2003测试表")
    value = [
        ["名称", "价格", "出版社", "语言"],
        ["如何高效读懂一本书", "22.3", "机械工业出版社", "中文"],
        ["暗时间", "32.4", "人民邮电出版社", "中文"],
        ["拆掉思维里的墙", "26.7", "机械工业出版社", "中文"]
    ]

    for i in range(0, 4):
        for j in range(0, len(value[i])):
            sheet.write(i, j, value[i][j])
    wb.save(path)
    print("写入数据成功！")

def read03Excel(path):
    workbook = xlrd.open_workbook(path)
    # 获取文件中的所有表格
    sheets = workbook.sheet_names()
    #print(sheets)
    worksheet = workbook.sheet_by_name(sheets[0])
    for i in range(0, worksheet.nrows):
        #row = worksheet.row(i)
        for j in range(0, worksheet.ncols):
            print(worksheet.cell_value(i,j), "\t", end="")
        print()

def write07Excel(path):
    wb = openpyxl.Workbook()
    sheet = wb.active
    sheet.title = '2007测试表'

    value = [["名称", "价格", "出版社", "语言"],
             ["如何高效读懂一本书", "22.3", "机械工业出版社", "中文"],
             ["暗时间", "32.4", "人民邮电出版社", "中文"],
             ["拆掉思维里的墙", "26.7", "机械工业出版社", "中文"]]
    for i in range(0, 4):
        for j in range(0, len(value[i])):
            sheet.cell(row=i+1, column=j+1, value=str(value[i][j]))

    wb.save(path)
    print("写入数据成功！")

def read07Excel(path):
    wb = openpyxl.load_workbook(path)
    # sheets = wb.get_sheet_names()
    # 这个方法已经过时了
    # sheet = wb.get_sheet_by_name('2007测试表')
    sheet =wb.worksheets[0]

    print(sheet.rows)

    for row in sheet.rows:
        for cell in row:
            print(cell.value, "\t", end="")
        print()


file_2003 = '2003.xls'
file_2007 = '2007.xlsx'
#write03Excel(file_2003)
#read03Excel(file_2003)
#write07Excel(file_2007)
read07Excel(file_2007)