【我们一起自学Python】-转载：py
看到一篇很好的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 filesExtract 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+。
1. 关于xlrd:
Library for developers to extract data from Microsoft Excel (tm) spreadsheet filesExtract 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+。
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=xlwt.Font()
font.name=font_name # 'Times New Roman'
font.height=font_height
font.bold=bold
font.colour_index=4
borders=xlwt.Borders()
borders.left=6
borders.right=6
borders.top=6
borders.bottom=6
style.font=font
style.borders=borders
return 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 = xlwt.easyxf('font: name Times New Roman, color-index red, bold on',num_format_str='#,##0.00')
style1 = xlwt.easyxf(num_format_str='D-MMM-YY')
new_sheet.write(1, 0, 1234.56, style0)
new_sheet.write(1, 1, datetime.now(), style1)
#write cell with formula
new_sheet.write(2,0,5)
new_sheet.write(2,1,8)
new_sheet.write(3,0, xlwt.Formula("A3+B3"))
new_workbook.save(r"NewCreateWorkbook.xls") #if change to xlsx,then open failed
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")
#sheet
all_sheets_list=TC_workbook.sheet_names()
print("All sheets name in File:",all_sheets_list)
first_sheet=TC_workbook.sheet_by_index(0)
print("First sheet Name:",first_sheet.name)
print("First sheet Rows:",first_sheet.nrows)
print("First sheet Cols:",first_sheet.ncols)
second_sheet=TC_workbook.sheet_by_name("SheetName_test")
print("Second sheet Rows:",second_sheet.nrows)
print("Second sheet Cols:",second_sheet.ncols)
first_row=first_sheet.row_values(0)
print("First row:",first_row)
first_col=first_sheet.col_values(0)
print("First Column:",first_col)
# cell
cell_value=first_sheet.cell(1,0).value
print("The 1th method to get Cell value of row 2 & col 1:",cell_value)
cell_value2=first_sheet.row(1)[0].value
print("The 2th method to get Cell value of row 2 & col 1:",cell_value2)
cell_value3=first_sheet.col(0)[1].value
print("The 3th method to get Cell value of row 2 & col 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.563. 向已经存在的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 = copy(rb)
ws = wb.get_sheet(0)
font=xlwt.Font()
font.name="Times New Roman"
font.height=220
font.bold=False
borders = xlwt.Borders()
borders.left = xlwt.Borders.THIN
borders.right = xlwt.Borders.THIN
borders.top = xlwt.Borders.THIN
borders.bottom = xlwt.Borders.THIN
pattern = xlwt.Pattern()
pattern.pattern = xlwt.Pattern.SOLID_PATTERN
pattern.pattern_fore_colour = 2
cell_style = xlwt.XFStyle()
cell_style.font = font
cell_style.borders = borders
cell_style.pattern = pattern
ws.write(6,7,"hello world",cell_style)
wb.save(r"NewCreateWorkbook.xls")
if __name__=="__main__":
write_to_existed_file()运行之后，excel文件内容如下：
作者：微微微笑
出处：http://www.cnblogs.com/miniren/
本文版权归作者和博客园共有，欢迎转载，但未经作者同意必须保留此段声明，且在文章页面明显位置给出原文连接，否则保留追究法律责任的权利.
免责声明：
① 本站未注明“稿件来源”的信息均来自网络整理。其文字、图片和音视频稿件的所属权归原作者所有。本站收集整理出于非商业性的教育和科研之目的，并不意味着本站赞同其观点或证实其内容的真实性。仅作为临时的测试数据，供内部测试之用。本站并未授权任何人以任何方式主动获取本站任何信息。
② 本站未注明“稿件来源”的临时测试数据将在测试完成后最终做删除处理。有问题或投稿请发送至: 邮箱/279061341@qq.com QQ/279061341
