【Python学习】操作excel样例
1、写入并读取excel表格的数据
1 #!usr/bin/python 2 #-*- coding: UTF-8 -*- 3 #Author:mollsweet 4 import xlwt 5 import xlrd 6 from datetime import * 7 import sys 8 import traceback 9 from datetime import datetime 10 from xlrd import xldate_as_tuple 11 from xlutils.copy import copy #复制原excel格式 12 reload(sys) 13 sys.setdefaultencoding('utf-8') 14 account_date=datetime.now().strftime('%Y/%m/%d %H:%M:%S')#获取当前系统日期 15 path=r"F:\PyCharm 2017.2.3\PycharmProjects\20190304\file\moll_excel.xls" 16 path01=r"F:\PyCharm 2017.2.3\PycharmProjects\20190304\file\mollnew_excel.xls" 17 def write_excel(): 18 book = xlwt.Workbook(encoding='utf-8', style_compression=0)#创建excel表格 19 sheet = book.add_sheet('工作簿01', cell_overwrite_ok=True)#表格中添加工作簿 20 21 # 第range(0,3):2列的宽度,也可以循环设置整个工作簿的宽度 22 for i in list(range(0,3)): 23 sheet.col(i).width = 7000 24 sheet.col(0).width = 3000 25 26 #设置标题的背景颜色为蓝色 27 pattern = xlwt.Pattern() # Create the Pattern 28 pattern.pattern = xlwt.Pattern.SOLID_PATTERN # May be: NO_PATTERN, SOLID_PATTERN, or 0x00 through 0x12 29 pattern.pattern_fore_colour = 3 # 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... 30 style = xlwt.XFStyle() # Create the Pattern 31 style.pattern = pattern # Add Pattern to Style 32 # sheet.write(0, 0, 'Cell Contents', style) #将样式写入表格 33 34 ##横向标题 35 txt0 = '数据类型' 36 sheet.write(0, 0, txt0.decode('utf-8'),style) # 此处需要将中文字符串解码成unicode码,否则会报错,弄成英文将不用转换格式 37 txt1 = '内容' 38 sheet.write(0, 1, txt1.decode('utf-8'),style) 39 txt2 = '备注' 40 sheet.write(0, 2, txt2.decode('utf-8'),style) 41 42 #写入第一列的内容 43 sheet.write(1, 0, '整型'.decode('utf-8')) 44 sheet.write(2, 0, '字符串'.decode('utf-8')) 45 sheet.write(3, 0, '浮点型'.decode('utf-8')) 46 sheet.write(4, 0, '日期'.decode('utf-8')) 47 sheet.write(5, 0, '空值'.decode('utf-8')) 48 sheet.write(6, 0, '布尔型'.decode('utf-8')) 49 50 #写入第2列的内容 51 sheet.write(1, 1, 12000) 52 sheet.write(2, 1, '心里有光,眼里有太阳'.decode('utf-8')) 53 sheet.write(3, 1, 12000.99) 54 sheet.write(4, 1, account_date) 55 sheet.write(5, 1, ) 56 sheet.write(6, 1, True) 57 note="备注数据" 58 # 写入第3列的内容 59 sheet.write(1, 2, note) 60 sheet.write(2, 2, note) 61 sheet.write(3, 2, note) 62 sheet.write(4, 2, note) 63 sheet.write(5, 2,note ) 64 sheet.write(6, 2, note) 65 66 book.save(path) 67 print "创建工作表格成功!" 68 print "" 69 70 ############################读出表格的内容 71 class excelHandle: 72 def decode(self, filename, sheetname): 73 try: 74 filename = filename.decode('utf-8') 75 sheetname = sheetname.decode('utf-8') 76 except Exception: 77 print traceback.print_exc() 78 return filename, sheetname 79 80 def read_excel(self, filename, sheetname): 81 filename, sheetname = self.decode(filename, sheetname) 82 rbook = xlrd.open_workbook(filename) 83 sheet = rbook.sheet_by_name(sheetname) 84 rows = sheet.nrows 85 cols = sheet.ncols 86 all_content = [] 87 for i in range(rows): 88 row_content = [] 89 for j in range(cols): 90 ctype = sheet.cell(i, j).ctype # 表格的数据类型 91 cell = sheet.cell_value(i, j) 92 if ctype == 2 and cell % 1 == 0: # 如果是整形 93 cell = int(cell) 94 elif ctype == 3: 95 # 转成datetime对象 96 date = datetime(*xldate_as_tuple(cell, 0)) 97 cell = date.strftime('%Y/%d/%m %H:%M:%S') 98 elif ctype == 4: 99 cell = True if cell == 1 else False 100 row_content.append(cell) 101 all_content.append(row_content) 102 print '[' + ','.join("'" + str(element) + "'" for element in row_content) + ']' 103 return all_content 104 105 106 def openfile():#打开一个文件写入数据,不改变之前文档的格式 107 old_book=xlrd.open_workbook(path,formatting_info=True) 108 new_book=copy(old_book) 109 newWs = new_book.get_sheet(0) 110 style1 = xlwt.XFStyle() 111 style1.num_format_str = 'yyyy/m/d' 112 113 newWs.write(4, 1, account_date, style1) 114 # 写入第2列的内容 115 print "^^^^^^^^^^^" 116 print account_date 117 new_book.save(path01) 118 print "复制并创建工作表格成功!" 119 print "" 120 if __name__ == '__main__': 121 write_excel() 122 # openfile() 123 eh = excelHandle() 124 filename = path 125 sheetname = '工作簿01' 126 eh.read_excel(filename, sheetname)
作者:gtea
博客地址:https://www.cnblogs.com/gtea