python使用openpyxl操作excel
2022-04-14 python 2619
一、openpyxl模块安装
pip install openpyxl
二、使用范围 (可读写表)专门处理及以上版本产生的文件,若其它格式,可先转为格式。 三、常用方法、生成新的
import openpyxl if __name__ == '__main__': #生成一个新的空的Excel wb = openpyxl.Workbook() # 保存可指定保存路径并定义文件名,如下,若不指定路径,则保存到当前py文件目录下,如 wb.save('example222.xlsx') wb.save('D:\Document\Workspace\pywokrspace\example222.xlsx')
3.2、打开已存在的excel
# 导入模块 #coding=utf-8 import openpyxl if __name__ == '__main__': #打开已存在的excel wb = openpyxl.load_workbook(filename = r'D:\Document\Workspace\pywokrspace\test001.xlsx') # 选择需要操作的sheet页 ws1=wb.worksheets[2] #通过索引获取需要操作的sheet页,也可以通过sheet页名称获取需要操作的sheet页,如:ws1 = wb.get_sheet_by_name('frequency')或ws = wb["frequency"] # 更改Sheet页名称 ws1.title = "WorkSheetTitle" # 保存 wb.save(r'D:\Document\Workspace\pywokrspace\test001.xlsx')
3.3、sheet页重命名
import openpyxl if __name__ == '__main__': #生成一个新的空的Excel wb = openpyxl.Workbook #打开excel默认sheet页 ws = wb.active # 更改默认名称Sheet` ws.title = "WorkSheetTitle" # 保存 wb.save('D:\Document\Workspace\pywokrspace\example222.xlsx')
3.4excelsheetsheetsheet
import openpyxl if __name__ == '__main__': wb = openpyxl.Workbook() #打开默认sheet页 ws = wb.active # #更改默认名称Sheet` ws.title = "WorkSheetTitle" # 定义第二个sheet页,并重命名 ws2 = wb.create_sheet("NewWorkSheet2") # 定义第三个sheet页,并重命名和定义排序 # create_sheet()方法,可传title和index两个参数,如`0` 的设定 会将该sheet页 置于wb最前面。 ws3 = wb.create_sheet("NewWorkSheet3", 0) # 保存 wb.save('D:\Document\Workspace\pywokrspace\example222.xlsx')
3.5excel-Cellrowcolumn
3.5.1
if __name__ == '__main__': wb = openpyxl.Workbook() ws = wb.active # 给单元格赋值 ws["A1"] = "HOGE" ws["B1"] = "FUGA" ws['C1'] = '=AVERAGE(C1:C10)' #值也可以是公式 # 保存 wb.save('D:\Document\Workspace\pywokrspace\example222.xlsx')
3.5.2
if __name__ == '__main__': wb = openpyxl.Workbook() ws = wb.active # 指定行列给单元格赋值 ws.cell(row=4, column=2, value=10) # 保存 wb.save('D:\Document\Workspace\pywokrspace\example222.xlsx')
3.5.3excel-sheet.append()
import openpyxl if __name__ == '__main__': #打开已存在的excel wb = openpyxl.load_workbook(filename = r'D:\Document\Workspace\pywokrspace\test001.xlsx') # # 选择需要操作的sheet页 ws=wb.worksheets[0] data1=['100','101','a'] ws.append(data1) data2 = ['200', '201', 'b'] ws.append(data2) wb.save(r'D:\Document\Workspace\pywokrspace\test001.xlsx')
四、设置单元格、设置单元格字体
import openpyxl if __name__ == '__main__': """ 设置字体font """ wb = openpyxl.Workbook() ws = wb.active # 设置font font = openpyxl.styles.Font(name = "宋体",size = 15) ws['A2'].font = font ws['A2'].value = "TEST002" # 保存 wb.save('D:\Document\Workspace\pywokrspace\example222.xlsx')
4.2、单元格颜色填充
import openpyxl from openpyxl.styles import PatternFill if __name__ == '__main__': wb = openpyxl.Workbook() ws = wb.active # 单元格填充颜色 fill = PatternFill(fill_type='solid',fgColor='FFFF0000') ws['A1'].fill = fill ws['A1'] = 'TEST001' # 保存 wb.save('D:\Document\Workspace\pywokrspace\example222.xlsx')
五、页中行增删改查、页中插入列
import openpyxl if __name__ == '__main__': #打开已存在的excel wb = openpyxl.load_workbook(filename = r'D:\Document\Workspace\pywokrspace\test001.xlsx') # # 选择需要操作的sheet页 ws=wb.worksheets[0] ws.insert_cols(idx=1) # 在第一列左侧插入1列 # 保存 wb.save(r'D:\Document\Workspace\pywokrspace\test001.xlsx')
5.2、sheet页中插入多列
import openpyxl if __name__ == '__main__': #打开已存在的excel wb = openpyxl.load_workbook(filename = r'D:\Document\Workspace\pywokrspace\test001.xlsx') # # 选择需要操作的sheet页 ws=wb.worksheets[0] ws.insert_cols(idx=2,amount=3) # 在第二列左侧插入3列 # 保存 wb.save(r'D:\Document\Workspace\pywokrspace\test001.xlsx')
5.31
import openpyxl if __name__ == '__main__': #打开已存在的excel wb = openpyxl.load_workbook(filename = r'D:\Document\Workspace\pywokrspace\test001.xlsx') # 选择需要操作的sheet页 ws=wb.worksheets[0] ws.delete_cols(idx=2) #删除第二列 # 保存 wb.save(r'D:\Document\Workspace\pywokrspace\test001.xlsx')
5.4
import openpyxl if __name__ == '__main__': #打开已存在的excel wb = openpyxl.load_workbook(filename = r'D:\Document\Workspace\pywokrspace\test001.xlsx') # 选择需要操作的sheet页 ws=wb.worksheets[0] ws.delete_cols(idx=5,amount=3) #从第5列开始删除3列 # 保存 wb.save(r'D:\Document\Workspace\pywokrspace\test001.xlsx')
5.5、插入一行
import openpyxl if __name__ == '__main__': #打开已存在的excel wb = openpyxl.load_workbook(filename = r'D:\Document\Workspace\pywokrspace\test001.xlsx') # 选择需要操作的sheet页 ws=wb.worksheets[0] ws.insert_rows(idx=2) #插入第二行 # 保存 wb.save(r'D:\Document\Workspace\pywokrspace\test001.xlsx')
5.6、插入多行
import openpyxl if __name__ == '__main__': #打开已存在的excel wb = openpyxl.load_workbook(filename = r'D:\Document\Workspace\pywokrspace\test001.xlsx') # 选择需要操作的sheet页 ws=wb.worksheets[0] ws.insert_rows(idx=2,amount=3) #从第二行开始插入3行 # 保存 wb.save(r'D:\Document\Workspace\pywokrspace\test001.xlsx')
5.7、删除1行
import openpyxl if __name__ == '__main__': #打开已存在的excel wb = openpyxl.load_workbook(filename = r'D:\Document\Workspace\pywokrspace\test001.xlsx') # # 选择需要操作的sheet页 ws=wb.worksheets[0] ws.delete_rows(idx=2) #删除第二行 # 保存 wb.save(r'D:\Document\Workspace\pywokrspace\test001.xlsx')
5.8、删除多行
import openpyxl if __name__ == '__main__': #打开已存在的excel wb = openpyxl.load_workbook(filename = r'D:\Document\Workspace\pywokrspace\test001.xlsx') # # 选择需要操作的sheet页 ws=wb.worksheets[0] ws.delete_rows(idx=2,amount=3) #从第二行开始删除3行 # 保存 wb.save(r'D:\Document\Workspace\pywokrspace\test001.xlsx')
六、读取中内容、读取中内容各种方法
import openpyxl if __name__ == '__main__': #打开已存在的excel wb = openpyxl.load_workbook(filename = r'D:\Document\Workspace\pywokrspace\test001.xlsx') # # 选择需要操作的sheet页 ws=wb.worksheets[0] print('---------------读取指定单元格数据方法1--指定坐标-------------------------') cell01 = ws['A2'].value # 通过坐标获取单元格的值 print(cell01) print('---------------读取指定单元格数据方法2--指定行列-------------------------') cell01 = ws.cell(2, 1).value # 通过指定行列获取单元格的值,第一个参数为行、第二个参数为列,即第2行第1列 print(cell01) print('---------------读取第2行数据方法1-------------------------') rows = ws['2'] for cell01 in rows: print(cell01.value) print('----------------读取第2行数据方法2------------------------') for i in range(1, 4): a=ws.cell(2,i).value #第2行,第i列 print(a) print('---------------读取B列数据方法1-------------------------') cols = ws['B'] for cell01 in cols: print(cell01.value) print('----------------读取B列数据方法2------------------------') for i in range(1, 18): a=ws.cell(i,2).value #第i行,第2列 print(a) print('----------------按行读取多行范围内数据方法------------------------') rows = ws['1:3'] for ro in rows: for cell in ro: print(cell.value) print('----------------按列读取多列范围内数据方法------------------------') cols = ws['B:C'] for col in cols: for cell in col: print(cell.value) print('----------------读取多行多列范围内数据方法------------------------') cells = ws['B2:C5'] cell01=ws['B2'] rowNumber01 = cell01.row # 获取单元格行号 columnNumber01 = cell01.column # 获取单元格列号 cell02 = ws['C5'] rowNumber02 = cell02.row # 获取单元格行号 columnNumber02 = cell02.column # 获取单元格列号 print(rowNumber01,rowNumber02,columnNumber01,columnNumber02) for rowNumber in range(rowNumber01,rowNumber02+1): for columnNumber in range(columnNumber01,columnNumber02+1): a=ws.cell(rowNumber,columnNumber).value print(a) print('----------------读取所有行数据方法------------------------') for row in ws.rows: for cell in row: cell01 = cell.value # 按行的维度输出值 print(cell01) print('----------------读取所有列数据方法------------------------') for column in ws.columns: for cell in column: cell01 = cell.value # 按列的维度输出值 print(cell01) print('----------------读取所有单元格数据方法------------------------') for row in ws: for cell in row: cell01 = cell.value print(cell01)
6.2、获取某个单元格的行号、列号、坐标
import openpyxl if __name__ == '__main__': #打开已存在的excel wb = openpyxl.load_workbook(filename = r'D:\Document\Workspace\pywokrspace\test001.xlsx') # # 选择需要操作的sheet页 ws=wb.worksheets[0] cell01= ws['B5'] rowNumber=cell01.row #获取单元格行号 columnNumber=cell01.column #获取单元格列号 coordinateNumber=cell01.coordinate #获取单元格坐标 print(rowNumber,columnNumber,coordinateNumber)
6.3、获取最大行数、列数 (存在值的有效行列号)
import openpyxl if __name__ == '__main__': #打开已存在的excel wb = openpyxl.load_workbook(filename = r'D:\Document\Workspace\pywokrspace\test001.xlsx') # # 选择需要操作的sheet页 ws=wb.worksheets[0] print(ws.max_row) print(ws.max_column)
demo:
def record_excel(data): xls_file = r'out.xlsx' if os.path.exists(xls_file): wb = openpyxl.load_workbook(filename=xls_file) ws = wb.worksheets[0] else: wb = openpyxl.Workbook() ws = wb.worksheets[0] ws["A1"] = "序号" ws["B1"] = "域名" ws["C1"] = "官方" ws["D1"] = "PC历史权重" ws["E1"] = "移动历史权重" ws["F1"] = "PC词量" ws["G1"] = "移动词量" ws["H1"] = "权重最早出现日期" ws["I1"] = "权重最后出现日期" ws.column_dimensions['A'].width = 10 ws.column_dimensions['B'].width = 20 ws.column_dimensions['C'].width = 15 ws.column_dimensions['D'].width = 15 ws.column_dimensions['E'].width = 15 ws.column_dimensions['F'].width = 15 ws.column_dimensions['G'].width = 15 ws.column_dimensions['H'].width = 15 ws.column_dimensions['I'].width = 15 # data = ['A', 'B', 'C', 'D', 'E', 'F', 'G', 'H', 'I'] ws.append(data) # 保存 wb.save(xls_file)
本文转载自:https://blog.csdn.net/baidu_24752135/article/details/123442407
很赞哦! (0)
相关文章
文章评论
-
-
-
0条评论