python3.7扩展库是什么_Python excel 功能扩展库 ——> openpyxl 的基本使用

1 from openpyxl importWorkbook2

3 from openpyxl importload_workbook4 #实例化一个操作对象

5 wb =Workbook()6

7 #获取当前活跃状态的sheet

8 ws =wb.active9

10 '''

11 # 基本操作，插入方式按顺序逐行插入12 # 单元格内容控制13 ws['A1'] = '姓名'14 ws['B1'] = '性别'15 # 在上文基础上添加整行(上文存在三行，则从第四行开始整行插入)16 ws.append(['项目','姓名','时间','报价','备注'])17 ws.append([1,2,3])18 ws.append([23,34,45,56])19 # 单元格内容控制20 ws['A2'] = 'jony'21 ws['A3'] = 'male'22 # 文件保存(必须用绝对路径)23 wb.save('/home/ht/fir.xlsx')24 '''

25

26 '''

27 # 创建新的工作区，并输入该区的标签名(标签名，位置)* 位置：该区在标签中的排序28 w1 = wb.create_sheet('sheet1')29 w2 = wb.create_sheet('sheet2')30 w3 = wb.create_sheet('sheet3',0)31

32 # 这里是修改该区标签名33 w1.title = 'sheet-1'34 w2.title = 'sheet-2'35 w3.title = 'sheet-3'36

37 w2.append(['ds','hp','wq'])38

39 # 设置标签的背景色(不是表格的单元格)40 w2.sheet_properties.tabColor = "1072BA"41

42 # 可以通过工作区的title名来获取该sheet对象43 wanna = wb.get_sheet_by_name('sheet-2')44

45 print(wanna)46 print(wb.sheetnames)47

48 # 49 # ['sheet-3', 'Sheet', 'sheet-1', 'sheet-2']50

51 wb['sheet-3'].append([1,2,3,4,5])52

53 # 复制工作区，新的工作区的默认命名为sheet-3 Copy54 new_3 = wb.copy_worksheet(w3)55 # 复制品重命名56 new_3.title = 'new'57 wb.save('/home/ht/mul_sheet.xlsx')58 '''

59

60 '''

61 sh1 = wb.active62 sh2 = wb.create_sheet('sheet-cell')63

64 # 单表格坐标数值输入65 sh2['A1'] = 'aaa插入内容'66 # 单元格坐标接受小写67 sh2['d4'] = '表格小写'68 # 单元格行列值坐标输入69 cell1 = sh2.cell(row=3,column=2,value='三行二列')70 cell2 = sh2.cell(3,4,'三行四列')71 print(cell1.value)72 # 三行二列73 wb.save('/home/ht/sheet-cell.xlsx')74 '''

75

76 '''

77 # 批量获取单元格78 mul_cell = wb.active79 mul_cell.append(['a1','b1','c1','d1'])80 mul_cell.append(['a2','b2','c2','d2'])81 mul_cell.append(['a3','b3','c3','d3'])82

83 # 获取A列所有单元格84 print(mul_cell['a'])85 # (,86 # ,87 # )88

89 # 获取 BCD 三列所有单元格90 print(mul_cell['b:d'])91 # ((, , ),92 # (, , ),93 # (, , ))94

95 # 获取第 2到3 行所有单元格96 print(mul_cell[2:3])97 # ((, , , ),98 # (, , , ))99

100 # iter_rows 方法，设定参数，获取二行三列区块内的所有单元格(获取基本单位为行，再从行内获取单元格)101 for r in mul_cell.iter_rows(min_row=1,max_row=2,min_col=1,max_col=3):102 print(r)103 for c in r:104 print(c,c.value)105

106 # (, , )107 # a1108 # b1109 # c1110 # (, , )111 # a2112 # b2113 # c2114

115 # iter_rows 方法，设定参数，获取二行三列区块内的所有单元格(获取基本单位为列，再从列内获取单元格)116 for r in mul_cell.iter_cols(min_row=1,max_row=2,min_col=1,max_col=3):117 print(r)118

119 # (, )120 # (, )121 # (, )122

123 # 获取所有行124 for r in mul_cell.rows:125 print(r)126 # (, , , )127 # (, , , )128 # (, , , )129

130 print(mul_cell.rows)131 # 132

133 # 获取所有列134 print(mul_cell.columns)135 # 136 wb.save('/home/ht/mul_cell.xlsx')137 '''

138

139 '''

140 from openpyxl import load_workbook141

142 # 操作文件143 read_sheet = load_workbook('/home/ht/mul_sheet.xlsx')144

145 read_sheet.guess_types = True146

147 current = read_sheet.active148 current['e2'] = '65%'149

150 read_sheet.save('/home/ht/mul_sheet.xlsx')151 '''

152

153 '''

154 from openpyxl import load_workbook155 wsheet = load_workbook('/home/ht/mul_cell.xlsx')156

157 # sheet 对象不存在脚标，只能通过坐标获取，wsheet.active.rows[1]是不能获取第二行的！158 print(wsheet.active['a'][1].value)159 print(wsheet.active['2'][1].value)160 # a2161 # b2162 '''

163

164 '''单元格分割合并165

166 deal_cell = load_workbook('/home/ht/sum_sheet.xlsx')167 sheet = deal_cell.active168

169 # 合并第一行1到4单元格(a1,b1,c1,d1)170 sheet.merge_cells('a1:d1')171

172 # 将 第一行 a1 到 d1 位置的单元格分离出来173 sheet.unmerge_cells('a1:d1')174

175 # 采用区块的方式合并或分割操作176 sheet.merge_cells(start_row=1,end_row=3,start_column=4,end_column=6)177

178 deal_cell.save('/home/ht/sum_sheet.xlsx')179 '''

180

181 '''单元格插入图片182

183 # 需要安装PIL图片包184 from openpyxl.drawing.image import Image185

186 img = Image('/home/ht/qcode.png')187 ws.add_image(img,'F3') # 这里图片填充的位置坐标必须使用大写字母188 wb.save('/home/ht/addimg.xlsx')189 '''