xlrd模块提供在任何平台上从excel电子表格(.xls和.xlsx)中提取数据的功能,xlwt模块提供生成与Microsoft Excel 95 到2003版本兼容的excel文件的功能。
# 方法一:
pip3 install xlrd
pip3 install xlwt
在python官网http://pypi.python.org/pypi/xlrd/(xlwt)下载xlrd/xlwt的安装包。
压缩包放置在python安装目录下python/Lib/site-packages。
解压压缩包:tar zxvf xlrd-1.0.0.tar.gz
进入到解压的文件夹:cd xlrd-1.0.0
执行命令:python setup,py install
准备excel文档:联系人.xls,内容如下所示:
import xlrd
data = xlrd.open_workbook('联系人.xls', encoding_override='utf-8')
sheetnames = data.sheet_names()
print(sheetnames)
"""
['银行1', '银行2']
"""
sheet_name = data.sheet_names()[1]
print(sheet_name)
"""
银行2
"""
table_name = data.sheet_by_name(sheet_name)
print(table_name)
"""
"""
table_index = data.sheet_by_index(0)
print(table_index)
"""
"""
# 获取行数和列数
nrows = table_name.nrows # 总行数
ncols = table_name.ncols # 总列数
print("银行2表,总行数: %s,总列数: %s" % (nrows, ncols))
"""
银行2表,总行数: 5,总列数: 7
"""
sheet_row_val = table_name.row_values(3) # 表示第四行数据是列表形式
sheet_col_val = table_name.col_values(3) # 表示第四列数据是列表形式
print("整行的值:%s , \n整列的值: %s" % (sheet_row_val, sheet_col_val))
"""
整行的值:['', '李九', '开发', 'IOS开发', 87888.0, 13213123.0, 'wang1@164.com'] ,
整列的值: ['', '工作职责', 'UI设计', 'IOS开发', '硬件维护']
"""
print(table_name.row_len(3))
print(table_index.row_len(3))
"""
7
8
"""
单元格是组成表格的最小单位,可以拆分或合并。每个数据的输入和修改都是在单元格中进行的。
# 获取指定单元格内容
print(table_name.cell(1,0).value) # 第二行第一列
print(table_name.cell_value(2,1)) # 第三行第二列
print(table_name.row(1)[0].value) # 第二行第一列
"""
机构名称
张八
机构名称
"""
print(table_index.cell(1,0).ctype) # 银行1表,2行1列内容 机构名称
print(table_index.cell(3,4).ctype) # 银行1表,4行5列内容 888
print(table_index.cell(2,6).ctype) # 银行1表,3行7列内容 2019/5/7
"""
1
2
3
"""
from datetime import datetime, date
if table_index.cell(2,6).ctype == 3:
print(table_index.cell(2, 6).value)
date_value = xlrd.xldate_as_tuple(table_index.cell(2, 6).value, data.datemode)
print(date_value)
print(date(*date_value[:3]))
print(date(*date_value[:3]).strftime('%Y/%m/%d'))
"""
43592.0
(2019, 5, 7, 0, 0, 0)
2019-05-07
2019/05/07
"""
if table_index.cell(3, 4).ctype == 2:
print(table_index.cell(3, 4).value)
num_value = int(table_index.cell(3, 4).value) # 转为整型
print(num_value)
"""
888.0
888
"""
需要使用merged_cells属性,获取merged_cells返回的row和col低位的索引即可。
# 需要在读取文件时添加formatting_info参数,默认是False
xls_data = xlrd.open_workbook('联系人.xls', formatting_info=True)
sheet_data = xls_data.sheet_by_name('银行2')
print(sheet_data.merged_cells)
"""
[(0, 1, 0, 7), (2, 5, 0, 1)]
"""
merged_cells返回的这四个参数的含义:(row,row_range,col,col_range)。
因此,(0,1,0,7)表示第1列~第7列合并;(2,5,0,1)表示第3行~第6行合并。
# 分别获取合并两个单元格内容
merge_value = []
for (row, row_range, col, col_range) in sheet_data.merged_cells:
merge_value.append((row, col))
"""
[(0, 0), (2, 0)]
"""
print(merge_value)
for i in merge_value:
print(sheet_data.cell(i[0], i[1]).value)
"""
银行2
银行2
"""
使用xlwt模块对Excel文件进行创建、设置、保存等操作。
import xlwt
workbook = xlwt.Workbook(encoding='utf-8')
worksheet = workbook.add_sheet("My Worksheet")
style = xlwt.XFStyle() # 初始化样式
font = xlwt.Font() # 为样式创建字体
font.name = "Times New Roman"
font.bold = True # 加粗
font.underline = True # 下划线
font.italic = True # 斜体字
style.font = font # 设定样式
worksheet.write(1,0, 'Unformatted value')
worksheet.write(2,0, 'Formatted value', style)
workbook.save("xlwt_test.xls")
打开Excel文件显示效果如下:
import xlwt
workbook = xlwt.Workbook()
worksheet = workbook.add_sheet('My Sheet')
worksheet.write(0, 0,'My Cell Contents')
worksheet.col(0).width = 30003
workbook.save('cell_width.xls')
显示效果:
import xlwt
import datetime
workbook = xlwt.Workbook()
worksheet = workbook.add_sheet('My Sheet')
style = xlwt.XFStyle()
style.num_format_str = 'M/D/YY' # Other options: D-MMM-YY, D-MMM, MMM-YY, h:mm, h:mm:ss, h:mm, h:mm:ss, M/D/YY h:mm, mm:ss, [h]:mm:ss, mm:ss.0
worksheet.write(0, 0, datetime.datetime.now(), style)
workbook.save('Excel_Workbook.xls')
显示效果:
import xlwt
workbook = xlwt.Workbook()
worksheet = workbook.add_sheet('My Sheet')
worksheet.write(0, 0, 5) # Outputs 5
worksheet.write(0, 1, 2) # Outputs 2
worksheet.write(1, 0, xlwt.Formula('A1*B1')) # Should output "10" (A1[5] * A2[2])
worksheet.write(1, 1, xlwt.Formula('SUM(A1,B1)')) # Should output "7" (A1[5] + A2[2])
workbook.save('Excel_formula.xls')
显示效果:
import xlwt
workbook = xlwt.Workbook()
worksheet = workbook.add_sheet('My Sheet')
worksheet.write(0, 0, xlwt.Formula('HYPERLINK("http://www.baidu.com";"Baidu")')) # Outputs the text "Baidu" linking to http://www.baidu.com
workbook.save('Excel_hyperlink.xls')
显示效果:
import xlwt
workbook = xlwt.Workbook()
worksheet = workbook.add_sheet('My Sheet')
worksheet.write_merge(0, 0, 0, 3, 'First Merge') # Merges row 0's columns 0 through 3.
font = xlwt.Font() # Create Font
font.bold = True # Set font to Bold
style = xlwt.XFStyle() # Create Style
style.font = font # Add Bold Font to Style
worksheet.write_merge(1, 2, 0, 3, 'Second Merge', style) # Merges row 1 through 2's columns 0 through 3.
workbook.save('Excel_merge.xls')
显示效果:
"""
设置单元格内容的对其方式:
"""
import xlwt
workbook = xlwt.Workbook()
worksheet = workbook.add_sheet('My Sheet')
alignment = xlwt.Alignment() # Create Alignment
alignment.horz = xlwt.Alignment.HORZ_CENTER # May be: HORZ_GENERAL, HORZ_LEFT, HORZ_CENTER, HORZ_RIGHT, HORZ_FILLED, HORZ_JUSTIFIED, HORZ_CENTER_ACROSS_SEL, HORZ_DISTRIBUTED
alignment.vert = xlwt.Alignment.VERT_CENTER # May be: VERT_TOP, VERT_CENTER, VERT_BOTTOM, VERT_JUSTIFIED, VERT_DISTRIBUTED
style = xlwt.XFStyle() # Create Style
style.alignment = alignment # Add Alignment to Style
worksheet.write(0, 0, 'Cell Contents', style)
workbook.save('Excel_Workbook.xls')
"""
为单元格议添加边框:
"""
import xlwt
workbook = xlwt.Workbook()
worksheet = workbook.add_sheet('My Sheet')
borders = xlwt.Borders() # Create Borders
borders.left = xlwt.Borders.DASHED
DASHED虚线
NO_LINE没有
THIN实线
borders.right = xlwt.Borders.DASHED
borders.top = xlwt.Borders.DASHED
borders.bottom = xlwt.Borders.DASHED
borders.left_colour = 0x40
borders.right_colour = 0x40
borders.top_colour = 0x40
borders.bottom_colour = 0x40
style = xlwt.XFStyle() # Create Style
style.borders = borders # Add Borders to Style
worksheet.write(0, 0, 'Cell Contents', style)
workbook.save('Excel_Workbook.xls')
"""
为单元格设置背景色:
"""
import xlwt
workbook = xlwt.Workbook()
worksheet = workbook.add_sheet('My Sheet')
pattern = xlwt.Pattern() # Create the Pattern
pattern.pattern = xlwt.Pattern.SOLID_PATTERN # May be: NO_PATTERN, SOLID_PATTERN, or 0x00 through 0x12
pattern.pattern_fore_colour = 5 # May be: 8 through 63. 0 = Black, 1 = White, 2 = Red, 3 = Green, 4 = Blue, 5 = Yellow, 6 = Magenta, 7 = Cyan, 16 = Maroon, 17 = Dark Green, 18 = Dark Blue, 19 = Dark Yellow , almost brown), 20 = Dark Magenta, 21 = Teal, 22 = Light Gray, 23 = Dark Gray, the list goes on…
style = xlwt.XFStyle() # Create the Pattern
style.pattern = pattern # Add Pattern to Style
worksheet.write(0, 0, 'Cell Contents', style)
workbook.save('Excel_Workbook.xls')
xlutils 常用方法(修改 excel)
然而并没有直接修改 xls 文件的方法。通常的做法是,读取出文件,复制一份数据,对其进行修改,再保存。在复制时,需要用到 xlutils 中的方法:
例如:
from xlrd import open_workbook
from xlutils.copy import copy
rb = open_workbook("example.xls")
wb = copy(rb)
s = wb.get_sheet(0)
s.write(0, 1, 'new data')
wb.save('example.xls')
在django项目中实现用excel导出数据库中数据。
# 注册客户(学生)到stark
class CustomerConfig(ModelStark):
"""代码省略"""
def excel\_export(self, request):
"""导出excel表格"""
list\_obj = models.Customer.objects.all().order\_by("create\_time")
if list\_obj:
# 创建工作薄
ws = Workbook(encoding="UTF-8")
w = ws.add\_sheet(u'数据报表第一页')
w.write(0, 0, 'id')
w.write(0, 1, u'姓名')
w.write(0, 2, u'性别')
w.write(0, 3, u'民族')
w.write(0, 4, u'籍贯')
w.write(0, 5, u'身份证号')
w.write(0, 6, u'通知书邮寄地址')
w.write(0, 7, u'邮编')
w.write(0, 8, u'联系电话')
w.write(0, 9, u'联系电话2')
w.write(0, 10, u'院校')
w.write(0, 11, u'专业')
w.write(0, 12, u'创建日期')
w.write(0, 13, u'生源人')
w.write(0, 14, u'备注')
# 写入数据
excel\_row = 1
for obj in list\_obj:
data\_id = obj.id
data\_name = obj.name
# data\_gender = obj.gender\_choices # 只显示数字
data\_gender = obj.get\_gender\_display()
data\_nation = obj.nation
data\_birth = obj.birth\_place
data\_identity\_num = obj.identity\_num
data\_address = obj.address
data\_postcode = obj.postcode
data\_tel = obj.tel
data\_tel2 = obj.tel\_2
data\_school = obj.stu\_school.title
data\_course = obj.course.first().name
data\_time = obj.create\_time.strftime('%Y-%m-%d %H:%M:%S')
data\_consultant = obj.consultant.name
data\_memo = obj.memo
w.write(excel\_row, 0, data\_id)
w.write(excel\_row, 1, data\_name)
w.write(excel\_row, 2, data\_gender)
w.write(excel\_row, 3, data\_nation)
w.write(excel\_row, 4, data\_birth)
w.write(excel\_row, 5, data\_identity\_num)
w.write(excel\_row, 6, data\_address)
w.write(excel\_row, 7, data\_postcode)
w.write(excel\_row, 8, data\_tel)
w.write(excel\_row, 9, data\_tel2)
w.write(excel\_row, 10, data\_school)
w.write(excel\_row, 11, data\_course)
w.write(excel\_row, 12, data\_time)
w.write(excel\_row, 13, data\_consultant)
w.write(excel\_row, 14, data\_memo)
excel\_row += 1
# 检测文件是否存在
# 方框中代码是保存本地文件使用,如不需要请删除该代码
###########################
exist\_file = os.path.exists("stu\_info.xls")
if exist\_file:
os.remove(r"stu\_info.xls")
ws.save("customer\_info.xls")
############################
sio = BytesIO()
ws.save(sio)
sio.seek(0)
response = HttpResponse(sio.getvalue(), content\_type='application/vnd.ms-excel')
response\['Content-Disposition'\] = 'attachment; filename=stu\_info.xls'
response.write(sio.getvalue())
return response
手机扫一扫
移动阅读更方便
你可能感兴趣的文章