一、xlwt写excel
xlwt是一个用于向旧的Excel文件写入数据和格式化信息的库，旧的Excel即后缀为.xls（2007版本之前的Office），新的后缀为.xlsx（2007版本及之后的Office）。xlwt、wlrd只能读写xls文件，而不能操作xlsx文件。旧的Excel一个工作表最多可有65536行，行用数字1—65536表示，最多可有256列，列用英文字母A—Z，AA—AZ，BA—BZ，……，IA—IV表示；一个工作簿中最多含有255个工作表，默认情况下是三个工作表。
对于新版本的Office最大行数已经支持到了1048576，最大列数是16384。当然也有新的Python库来对新的Office Excel进行操作，就是openpyxl库，下面也会简单使用一下。
先看xlwt库，在写入Excel表格之前，你必须初始化workbook对象，然后添加一个workbook对象。比如：
|
1 2 3 |
import xlwt file = xlwt.Workbook() table = file.add_sheet('sheet name') |
这样表单就被创建了，然后就可以写入数据：
|
1 |
table.write(0,0,'row and column') |
这里第一个参数0表示Excel的行，第二个参数0表示Excel的列，第三个参数就是写入到此单元格的内容。
当你更改表单内容的时候，会有警告提示。解决方式就是使用cell_overwrite_ok=True来创建worksheet。
|
1 |
file.add_sheet('sheet name', cell_overwrite_ok=True) |
这样就可以更改单元格的内容了。
然后就可以保存文件（这里不需要像打开文件一样需要close文件)
|
1 |
file.save('demo.xls') |
除了上面这些基本操作外，也支持格式化设置。如单元格格式属性或单元格样式的属性设置，字体设置等，格式属性分为6组：
| Group | Attributes |
|---|---|
| Number format | 数字格式索引（FORMAT记录的索引） |
| Font | 字体索引（FONT记录的索引） |
| Alignment | 水平和垂直对齐，文本换行，缩进，方向/旋转，文本方向 |
| Border | 边框线条样式和颜色 |
| Background | 背景区域风格和颜色 |
| Protection | 单元格锁定，公式隐藏 |
使用方式：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
# 初始化样式 style = xlwt.XFStyle() # 创建一个与样式一起使用的字体 font = xlwt.Font() font.name = 'Times New Roman' font.bold = True # 设置样式的字体 style.font = font # 写入时应用样式 sheet.write(0, 0, 'some bold Times text', style) |
还有比如格式化数字，添加超链接等，更多可以参考官方样例： https://github.com/python-excel/xlwt/tree/master/examples
从MySQL中读出数据，然后批量插入Excel表格中。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 |
#!/usr/bin/python # -*- coding: UTF-8 -*- import pymysql import xlwt connection = pymysql.connect( host = '127.0.0.1', port = 3306, user = 'root', passwd = '123456', db = 'sbtest', charset = 'utf8' ) try: file = xlwt.Workbook() table = file.add_sheet('sheet name', cell_overwrite_ok=True) with connection.cursor() as cursor: sql = "select * from sbtest1" curosr = connection.cursor() curosr.execute(sql) column = 0 for i in curosr.description: table.write(0,column,i[0]) column += 1 row = 1 column = 0 for i in curosr.fetchall(): for i in i: table.write(row,column,i) column += 1 row += 1 column = 0 file.save('demo.xls') finally: connection.close() |
二、xlrd读excel
上面介绍了写Excel的库，有写自然就有读的库了，就是xlrd，使用起来跟xlwd差不多。
首先，打开workbook：
|
1 2 |
import xlrd table = xlrd.open_workbook('demo.xls') |
得到表的sheet名称：
|
1 |
table.sheet_names() |
得到第一个sheet名称，两种方式：索引和名字
|
1 2 |
sheet = table.sheet_by_index(0) sheet = table.sheet_by_name('sheet name') |
递归打印出每行的信息（列表）：
|
1 2 |
for r in range(sheet.nrows): print(sheet.row_values(r)) |
如果只想返回第一列数据：
|
1 |
sheet.col_values(0) |
通过索引读取数据：
|
1 2 |
sheet.cell(0,0).value sheet.cell(rowx=3,colx=2).value |
注意：这里的索引都是从0开始的，根据行和列的坐标读取具体的Excel单元格。
三、openpyxl操作excel
其实上面的xlwt或xlrd都是相对限制较多，比如无法修改excel表格，以及读写一个sheet不能超过65535条，如果超过就会报错：ValueError: row index was 65536, not allowed by .xls format。这也是因为xlwt或xlrd只能用于后缀为.xls老版本的Excel，也是Excel本身的限制。上面也说了，新版Excel已经支持更大的行数了。
openpyxl库就是用来操作新版的Excel，相对xlwt和xlrd也更加友好。在openpyxl中，主要用到三个概念：Workbooks，Sheets，Cells。Workbook就是一个excel工作表；Sheet是工作表中的一张表页；Cell就是简单的一个格。openpyxl就是围绕着这三个概念进行的，不管读写都是“三板斧”：打开Workbook，定位Sheet，操作Cell。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 |
# 导入模块 >>> from openpyxl import Workbook # 打开Workbook >>> file = Workbook() # 创建Sheet >>> sheet = file.active # 定义Sheet名称 >>> sheet.title = "sheet name" # 操作Cell >>> sheet['A1'] = 4 # 获取Cell值 >>> sheet['A1'].value # 保存xlsx文件 >>> file.save('demo.xlsx') |
关于openpyxl更为详细的使用可以看这篇文章“OpenPyXL的使用教程（一）”。
在使用上与xlwt/xlrt不同的是，xlwt/xlrt可以Excel行和列的坐标（0,0）来定位Cell，而openpyxl使用类似A1-Z1这样的方式来定位Cell。其实在处理起来使用A1-Z1这种方式定位Cell不是很方便，但列数大于A-Z后，就要从AA-AZ开始算起。我这里提供了一个生成定位Cell的字母列表的方法。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 |
def GenerateCharacter(n, limit=65535): import string c = int(n / 26) y = int(n % 26) if n <= 26: character = [] for i in range(n): character.append(string.ascii_uppercase[i]) return character elif n <= limit: character = [c for c in string.ascii_uppercase] for i in range(c): if i + 1 == c: for e in range(y): character.append(character[i] + string.ascii_uppercase[e]) else: for e in [character[i] + c for c in string.ascii_uppercase]: character.append(e) return character else: return "more than %s restrictions" % limit |
只需要传入一个数字即可，数字就是你要生成的字母数量，默认最大生成65535，你也可以自己限制。
|
1 2 |
>>> print(GenerateCharacter(10)) ['A', 'B', 'C', 'D', 'E', 'F', 'G', 'H', 'I', 'J'] |
下面提供了一个从数据库读取数据然后循序插入到Excel中的例子：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 |
#!/usr/bin/python # -*- coding: UTF-8 -*- import pymysql from openpyxl import Workbook class DatabaseConn: def __init__(self, ip=None, user=None, password=None, db=None, port=None): self.ip = ip self.user = user self.password = password self.db = db self.port = int(port) self.con = object def __enter__(self): self.con = pymysql.connect( host=self.ip, user=self.user, passwd=self.password, db=self.db, charset='utf8mb4', port=self.port ) return self def __exit__(self, exc_type, exc_val, exc_tb): self.con.close() def select_execute(self, sql=None): with self.con.cursor() as cursor: cursor.execute(sql) result = cursor.fetchall() fields = cursor.description return fields, result class ExportSQL: def GenerateCharacter(self, n, limit=65535): import string c = int(n / 26) y = int(n % 26) if n <= 26: character = [] for i in range(n): character.append(string.ascii_uppercase[i]) return character elif n <= limit: character = [c for c in string.ascii_uppercase] for i in range(c): if i + 1 == c: for e in range(y): character.append(character[i] + string.ascii_uppercase[e]) else: for e in [character[i] + c for c in string.ascii_uppercase]: character.append(e) return character else: return "more than %s restrictions" % limit def execute(self): try: with DatabaseConn( ip='127.0.0.1', user='root', password='', port=3306, db='sbtest', ) as curosr: fields, result = curosr.select_execute(sql='select * from sbtest1 limit 10') file = Workbook() sheet = file.active sheet.title = "sheet name" ascii_uppercase = self.GenerateCharacter(len(fields)) for field in range(0, len(fields)): sheet["%s%d" % (ascii_uppercase[field], 1)] = fields[field][0] for row in range(1, len(result) + 1): for field in range(0, len(fields)): sheet["%s%d" % (ascii_uppercase[field], row + 1)] = result[row - 1][field] file.save('demo.xlsx') except Exception as e: print(e) ExportSQL().execute() |
上面是一个一次性导出 Excel 的脚本，如果有多次查询追加导入到 Excel 中，大概的方式如下：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 |
#!/usr/bin/python # -*- coding: UTF-8 -*- import pymysql from openpyxl import Workbook class DatabaseConn: def __init__(self, ip=None, user=None, password=None, db=None, port=None): self.ip = ip self.user = user self.password = password self.db = db self.port = int(port) self.con = object def __enter__(self): self.con = pymysql.connect( host=self.ip, user=self.user, passwd=self.password, db=self.db, charset='utf8mb4', port=self.port ) return self def __exit__(self, exc_type, exc_val, exc_tb): self.con.close() def select_execute(self, sql=None): with self.con.cursor() as cursor: cursor.execute(sql) result = cursor.fetchall() fields = cursor.description return fields, result def execute(sql): try: with DatabaseConn( ip='', user='', password='', port=3306, db='', ) as curosr: fields, result = curosr.select_execute(sql=sql) return (fields, result) except Exception as e: print(e) def generate_character(n, limit=65535): import string c = int(n / 26) y = int(n % 26) if n <= 26: character = [] for i in range(n): character.append(string.ascii_uppercase[i]) return character elif n <= limit: character = [c for c in string.ascii_uppercase] for i in range(c): if i + 1 == c: for e in range(y): character.append(character[i] + string.ascii_uppercase[e]) else: for e in [character[i] + c for c in string.ascii_uppercase]: character.append(e) return character else: return "more than %s restrictions" % limit if __name__ == '__main__': count = 0 file = Workbook() sheet = file.active sheet.title = "sheet name" f = open('/tmp/1.txt', 'r') while True: line = f.readline() if not line: break else: sql = ("selec * from sbtest.sbtest1 where id={}".format(line)) fields, result = execute(sql) ascii_uppercase = generate_character(len(fields)) if count == 0: for field in range(0, len(fields)): sheet["%s%d" % (ascii_uppercase[field], 1)] = fields[field][0] if result: for row in range(count + 1, count + len(result) + 1): for field in range(0, len(fields)): sheet["%s%d" % (ascii_uppercase[field], row + 1)] = result[row - count - 1][field] count += len(result) + 1 else: fields = [None] * len(fields) if len(fields) >= 2: fields[0] = line else: print("fields data error") for field in range(0, len(fields)): sheet["%s%d" % (ascii_uppercase[field], count + 1)] = fields[field] count += 1 print(count) file.save('demo.xlsx') |
这是一个从文件中读取数据，然后进行查询，查询结果追加到 Excel 中，没有查询结果的把文件读取的数值追加进 Excel 中。
四、xlutils修改excel
Python中一般使用xlrd（excel read）来读取Excel文件，使用xlwt（excel write）来生成Excel文件（可以控制Excel中单元格的格式），需要注意的是，用xlrd读取excel是不能对其进行操作的：xlrd.open_workbook()方法返回xlrd.Book类型，是只读的，不能对其进行操作。而 xlwt.Workbook()返回的xlwt.Workbook类型的save(filepath)方法可以保存excel文件。因此对于读取和生成Excel文件都非常容易处理，但是对于已经存在的Excel文件进行修改就比较麻烦了。不过，还有一个xlutils（依赖于xlrd和xlwt）提供复制excel文件内容和修改文件的功能。其实际也只是在xlrd.Book和xlwt.Workbook之间建立了一个管道而已。如果有用到可以参考：Python xlrd、xlwt、xlutils读取、修改Excel文件
