AI专业者开发社区 1 号成员
  •  0 回帖  •  9 浏览  •  5 个月前
   

python 3 操作 excel

看到一篇很好的 python 读写 excel 方式的对比文章： 用 Python 读写 Excel 文件

关于其他版本的 excel，可以通过他提供的链接教程进行学习。

XlsxWriter：

https://github.com/jmcnamara/XlsxWriter

http://xlsxwriter.readthedocs.org

openpyxl： http://openpyxl.readthedocs.io/en/default/

Microsoft excel API：https://msdn.microsoft.com/en-us/library/fp179694.aspx

简介

xlrd 用来读取 excel 文件，xlwt 用来写 excel 文件，它们合作来对 excel 进行操作。

官方文档：http://www.python-excel.org/

xlrd 官方介绍：https://pypi.python.org/pypi/xlrd/1.0.0

xlwt 官方介绍：https://pypi.python.org/pypi/xlwt/1.1.2

xlutils 官方介绍：https://pypi.python.org/pypi/xlutils

http://xlutils.readthedocs.io/en/latest/

1. 关于 xlrd:

Library for developers to extract data from Microsoft Excel (tm) spreadsheet files

Extract data from Excel spreadsheets (.xls and .xlsx, versions 2.0 onwards) on any platform. Pure Python (2.6, 2.7, 3.2+). Strong support for Excel dates. Unicode-aware.

翻译过来总结就是：

xlrd 可以在任意平台上读取的 excel 为： .xls 以及 .xlsx 。

xlrd 支持和的 python 版本是： 2.6，2.7 ， 3.2+。

2. 关于 xlwt：

Library to create spreadsheet files compatible with MS Excel 97/2000/XP/2003 XLS files, on any platform, with Python 2.6, 2.6, 3.3+
This is a library for developers to use to generate spreadsheet files compatible with Microsoft Excel versions 95 to 2003.

翻译过来总结就是：

xlwt 支持的 excel 版本是： Microsoft excel 版本 95---2003，也就是 xls 文件。

xlwt 支持的 python 版本是：2.6 , 3.3+.

3. 关于 xlutils：

This package provides a collection of utilities for working with Excel files. Since these utilities may require either or both of the xlrd and xlwt packages, they are collected together here, separate from either package.

Currently available are:


xlutils.copy
Tools for copying xlrd.Book objects to xlwt.Workbook objects.
xlutils.display
Utility functions for displaying information about xlrd-related objects in a user-friendly and safe fashion.
xlutils.filter
A mini framework for splitting and filtering Excel files into new Excel files.
xlutils.margins
Tools for finding how much of an Excel file contains useful data.
xlutils.save
Tools for serializing xlrd.Book objects back to Excel files.
xlutils.styles
Tools for working with formatting information expressed in styles.

翻译过来总结就是：

如果需要在 xlrd 以及 xlwt 之间进行交互的话，比如拷贝 xlrd 到 xlwt 需要用到 xlutils。

目前提供了 copy、display、filter、margins、Save、styles 几个函数。

 

安装 xlrd 和 xlwt

pip install xlrd
pip install xlwt
pip install xlutils
pip list

xlrd (1.0.0)
xlutils (2.0.0)
xlwt (1.1.2)

使用

1. 新建一个 excel 文件（xlwt）

#coding='utf-8'

import xlwt
from datetime import  datetime


def set_style(font_name,font_height,bold=False):
style=xlwt.XFStyle()


font</span>=<span style="color: rgba(0, 0, 0, 1)">xlwt.Font()
font.name</span>=font_name         <span style="color: rgba(0, 128, 0, 1)">#</span><span style="color: rgba(0, 128, 0, 1)"> 'Times New Roman'</span>
font.height=<span style="color: rgba(0, 0, 0, 1)">font_height
font.bold</span>=<span style="color: rgba(0, 0, 0, 1)">bold
font.colour_index</span>=4<span style="color: rgba(0, 0, 0, 1)">

borders</span>=<span style="color: rgba(0, 0, 0, 1)">xlwt.Borders()
borders.left</span>=6<span style="color: rgba(0, 0, 0, 1)">
borders.right</span>=6<span style="color: rgba(0, 0, 0, 1)">
borders.top</span>=6<span style="color: rgba(0, 0, 0, 1)">
borders.bottom</span>=6<span style="color: rgba(0, 0, 0, 1)">

style.font</span>=<span style="color: rgba(0, 0, 0, 1)">font
style.borders</span>=<span style="color: rgba(0, 0, 0, 1)">borders
</span><span style="color: rgba(0, 0, 255, 1)">return</span><span style="color: rgba(0, 0, 0, 1)"> style



def write_to_excel_xlwt():
'''Write content to a new excel'''
new_workbook=xlwt.Workbook()
new_sheet=new_workbook.add_sheet("SheetName_test")
new_sheet.write(0,0,"hello")
#write cell with style
new_sheet.write(0,1,"world",set_style("Times New Roman", 220, True))


style0 </span>= xlwt.easyxf(<span style="color: rgba(128, 0, 0, 1)">'</span><span style="color: rgba(128, 0, 0, 1)">font: name Times New Roman, color-index red, bold on</span><span style="color: rgba(128, 0, 0, 1)">'</span>,num_format_str=<span style="color: rgba(128, 0, 0, 1)">'</span><span style="color: rgba(128, 0, 0, 1)">#,##0.00</span><span style="color: rgba(128, 0, 0, 1)">'</span><span style="color: rgba(0, 0, 0, 1)">)
style1 </span>= xlwt.easyxf(num_format_str=<span style="color: rgba(128, 0, 0, 1)">'</span><span style="color: rgba(128, 0, 0, 1)">D-MMM-YY</span><span style="color: rgba(128, 0, 0, 1)">'</span><span style="color: rgba(0, 0, 0, 1)">)
new_sheet.write(</span>1, 0, 1234.56<span style="color: rgba(0, 0, 0, 1)">, style0)
new_sheet.write(</span>1, 1<span style="color: rgba(0, 0, 0, 1)">, datetime.now(), style1)

</span><span style="color: rgba(0, 128, 0, 1)">#</span><span style="color: rgba(0, 128, 0, 1)">write cell with formula</span>
new_sheet.write(2,0,5<span style="color: rgba(0, 0, 0, 1)">)
new_sheet.write(</span>2,1,8<span style="color: rgba(0, 0, 0, 1)">)
new_sheet.write(</span>3,0, xlwt.Formula(<span style="color: rgba(128, 0, 0, 1)">"</span><span style="color: rgba(128, 0, 0, 1)">A3+B3</span><span style="color: rgba(128, 0, 0, 1)">"</span><span style="color: rgba(0, 0, 0, 1)">))

new_workbook.save(r</span><span style="color: rgba(128, 0, 0, 1)">"</span><span style="color: rgba(128, 0, 0, 1)">NewCreateWorkbook.xls</span><span style="color: rgba(128, 0, 0, 1)">"</span>)         <span style="color: rgba(0, 128, 0, 1)">#</span><span style="color: rgba(0, 128, 0, 1)">if change to xlsx,then open failed</span>



if name=="main":
write_to_excel_xlwt()

代码执行之后，在当前路径下生成 excel 文件 “NewCreateWorkbook.xls”。内容如下 ：

2. 读取 excel 文件 (xlrd)

#coding='utf-8'

import xlrd


def read_excel_xlrd():
'''Read Excel with xlrd'''
#file
TC_workbook=xlrd.open_workbook(r"NewCreateWorkbook.xls")


</span><span style="color: rgba(0, 128, 0, 1)">#</span><span style="color: rgba(0, 128, 0, 1)">sheet</span>
all_sheets_list=<span style="color: rgba(0, 0, 0, 1)">TC_workbook.sheet_names()
</span><span style="color: rgba(0, 0, 255, 1)">print</span>(<span style="color: rgba(128, 0, 0, 1)">"</span><span style="color: rgba(128, 0, 0, 1)">All sheets name in File:</span><span style="color: rgba(128, 0, 0, 1)">"</span><span style="color: rgba(0, 0, 0, 1)">,all_sheets_list)

first_sheet</span>=<span style="color: rgba(0, 0, 0, 1)">TC_workbook.sheet_by_index(0)
</span><span style="color: rgba(0, 0, 255, 1)">print</span>(<span style="color: rgba(128, 0, 0, 1)">"</span><span style="color: rgba(128, 0, 0, 1)">First sheet Name:</span><span style="color: rgba(128, 0, 0, 1)">"</span><span style="color: rgba(0, 0, 0, 1)">,first_sheet.name)
</span><span style="color: rgba(0, 0, 255, 1)">print</span>(<span style="color: rgba(128, 0, 0, 1)">"</span><span style="color: rgba(128, 0, 0, 1)">First sheet Rows:</span><span style="color: rgba(128, 0, 0, 1)">"</span><span style="color: rgba(0, 0, 0, 1)">,first_sheet.nrows)
</span><span style="color: rgba(0, 0, 255, 1)">print</span>(<span style="color: rgba(128, 0, 0, 1)">"</span><span style="color: rgba(128, 0, 0, 1)">First sheet Cols:</span><span style="color: rgba(128, 0, 0, 1)">"</span><span style="color: rgba(0, 0, 0, 1)">,first_sheet.ncols)

second_sheet</span>=TC_workbook.sheet_by_name(<span style="color: rgba(128, 0, 0, 1)">"</span><span style="color: rgba(128, 0, 0, 1)">SheetName_test</span><span style="color: rgba(128, 0, 0, 1)">"</span><span style="color: rgba(0, 0, 0, 1)">)
</span><span style="color: rgba(0, 0, 255, 1)">print</span>(<span style="color: rgba(128, 0, 0, 1)">"</span><span style="color: rgba(128, 0, 0, 1)">Second sheet Rows:</span><span style="color: rgba(128, 0, 0, 1)">"</span><span style="color: rgba(0, 0, 0, 1)">,second_sheet.nrows)
</span><span style="color: rgba(0, 0, 255, 1)">print</span>(<span style="color: rgba(128, 0, 0, 1)">"</span><span style="color: rgba(128, 0, 0, 1)">Second sheet Cols:</span><span style="color: rgba(128, 0, 0, 1)">"</span><span style="color: rgba(0, 0, 0, 1)">,second_sheet.ncols)

first_row</span>=<span style="color: rgba(0, 0, 0, 1)">first_sheet.row_values(0)
</span><span style="color: rgba(0, 0, 255, 1)">print</span>(<span style="color: rgba(128, 0, 0, 1)">"</span><span style="color: rgba(128, 0, 0, 1)">First row:</span><span style="color: rgba(128, 0, 0, 1)">"</span><span style="color: rgba(0, 0, 0, 1)">,first_row)
first_col</span>=<span style="color: rgba(0, 0, 0, 1)">first_sheet.col_values(0)
</span><span style="color: rgba(0, 0, 255, 1)">print</span>(<span style="color: rgba(128, 0, 0, 1)">"</span><span style="color: rgba(128, 0, 0, 1)">First Column:</span><span style="color: rgba(128, 0, 0, 1)">"</span><span style="color: rgba(0, 0, 0, 1)">,first_col)

</span><span style="color: rgba(0, 128, 0, 1)">#</span><span style="color: rgba(0, 128, 0, 1)"> cell</span>
cell_value=first_sheet.cell(1<span style="color: rgba(0, 0, 0, 1)">,0).value
</span><span style="color: rgba(0, 0, 255, 1)">print</span>(<span style="color: rgba(128, 0, 0, 1)">"</span><span style="color: rgba(128, 0, 0, 1)">The 1th method to get Cell value of row 2 &amp; col 1:</span><span style="color: rgba(128, 0, 0, 1)">"</span><span style="color: rgba(0, 0, 0, 1)">,cell_value)
cell_value2</span>=first_sheet.row(1<span style="color: rgba(0, 0, 0, 1)">)[0].value
</span><span style="color: rgba(0, 0, 255, 1)">print</span>(<span style="color: rgba(128, 0, 0, 1)">"</span><span style="color: rgba(128, 0, 0, 1)">The 2th method to get Cell value of row 2 &amp; col 1:</span><span style="color: rgba(128, 0, 0, 1)">"</span><span style="color: rgba(0, 0, 0, 1)">,cell_value2)
cell_value3</span>=first_sheet.col(0)[1<span style="color: rgba(0, 0, 0, 1)">].value
</span><span style="color: rgba(0, 0, 255, 1)">print</span>(<span style="color: rgba(128, 0, 0, 1)">"</span><span style="color: rgba(128, 0, 0, 1)">The 3th method to get Cell value of row 2 &amp; col 1:</span><span style="color: rgba(128, 0, 0, 1)">"</span><span style="color: rgba(0, 0, 0, 1)">,cell_value3)



if name=="main":
read_excel_xlrd()

运行之后，控制台输出如下 ：

All sheets name in File: ['SheetName_test']
First sheet Name: SheetName_test
First sheet Rows: 4
First sheet Cols: 2
Second sheet Rows: 4
Second sheet Cols: 2
First row: ['hello', 'world']
First Column: ['hello', 1234.56, 5.0, '']
The 1th method to get Cell value of row 2 & col 1: 1234.56
The 2th method to get Cell value of row 2 & col 1: 1234.56
The 3th method to get Cell value of row 2 & col 1: 1234.56

3. 向已经存在的 excel 写入 (xlrd&xlwt&xlutils)

#coding='utf-8'

import xlrd
import xlwt
from xlutils.copy import copy


def write_to_existed_file():
'''Write content to existed excel file with xlrd&xlutils&xlwt'''
rb = xlrd.open_workbook(r"NewCreateWorkbook.xls",formatting_info=True)


wb </span>=<span style="color: rgba(0, 0, 0, 1)"> copy(rb)
ws </span>=<span style="color: rgba(0, 0, 0, 1)"> wb.get_sheet(0)

font</span>=<span style="color: rgba(0, 0, 0, 1)">xlwt.Font()
font.name</span>=<span style="color: rgba(128, 0, 0, 1)">"</span><span style="color: rgba(128, 0, 0, 1)">Times New Roman</span><span style="color: rgba(128, 0, 0, 1)">"</span><span style="color: rgba(0, 0, 0, 1)">
font.height</span>=220<span style="color: rgba(0, 0, 0, 1)">
font.bold</span>=<span style="color: rgba(0, 0, 0, 1)">False

borders </span>=<span style="color: rgba(0, 0, 0, 1)"> xlwt.Borders()
borders.left </span>=<span style="color: rgba(0, 0, 0, 1)"> xlwt.Borders.THIN
borders.right </span>=<span style="color: rgba(0, 0, 0, 1)"> xlwt.Borders.THIN
borders.top </span>=<span style="color: rgba(0, 0, 0, 1)"> xlwt.Borders.THIN
borders.bottom </span>=<span style="color: rgba(0, 0, 0, 1)"> xlwt.Borders.THIN

pattern </span>=<span style="color: rgba(0, 0, 0, 1)"> xlwt.Pattern()
pattern.pattern </span>=<span style="color: rgba(0, 0, 0, 1)"> xlwt.Pattern.SOLID_PATTERN
pattern.pattern_fore_colour </span>= 2<span style="color: rgba(0, 0, 0, 1)">

cell_style </span>=<span style="color: rgba(0, 0, 0, 1)"> xlwt.XFStyle()
cell_style.font </span>=<span style="color: rgba(0, 0, 0, 1)"> font
cell_style.borders </span>=<span style="color: rgba(0, 0, 0, 1)"> borders
cell_style.pattern </span>=<span style="color: rgba(0, 0, 0, 1)"> pattern

ws.write(</span>6,7,<span style="color: rgba(128, 0, 0, 1)">"</span><span style="color: rgba(128, 0, 0, 1)">hello world</span><span style="color: rgba(128, 0, 0, 1)">"</span><span style="color: rgba(0, 0, 0, 1)">,cell_style)
wb.save(r</span><span style="color: rgba(128, 0, 0, 1)">"</span><span style="color: rgba(128, 0, 0, 1)">NewCreateWorkbook.xls</span><span style="color: rgba(128, 0, 0, 1)">"</span><span style="color: rgba(0, 0, 0, 1)">)



if name=="main":
write_to_existed_file()

运行之后，excel 文件内容如下：