Python自动化之Excel

概述Python自动化之Excel方法一：应用pip执行命令安装openpyxl模块pipinstallopenpyxl方法二：在Pycharm中：File->Setting->左侧ProjectInterpreterExcel读取读取对应表格打开已经存在的Excel表格fromopenpyxlimportload_workbookexl=load_workbook(filename='t Python自动化之Excel

方法一：应用pip执行命令

安装openpyxl模块pip install openpyxl

方法二：在Pycharm中：file->Setting->左侧Project Interpreter

Excel读取读取对应表格打开已经存在的Excel表格

from openpyxl import load_workbookexl = load_workbook(filename = 'test.xlsx')print(exl.sheetnames)

根据名称或去表格

from openpyxl import load_workbookexl_1 = load_workbook(filename = 'test.xlsx')print(exl_1.sheetnames)sheet = exl_1['work']'若只有一张表则：'sheet = exl_1.active

获取Excel 内容占据的大小

print(sheet.dimensions)

读取单元格获取某个单元格的具体内容

cell = sheet.cell(row=1,column=2) #指定行列数print(cell.value)cell_1 = sheet['A1'] #指定坐标print(cell_1.value)

获取单元格对应的行、列和坐标

print(cell_1.row, cell_1.column, cell.coordinate)

读取多个格子的值指定坐标范围

cells = sheet['A1:C8'] #A1到C8区域的值

指定行的值

Row = sheet[1] #第1行的值Rows = sheet[1:2] #第1到2行的值

指定列的值

Column = sheet['A'] #第A列Columns = sheet['A:C'] #第A到C列

指定范围的值

# 行获取for row in sheet.iter_rows(min_row = 1, max_row = 5,						   min_col = 2, max_col = 6):	print(row)	# 一列由多个单元格组成，若需要获取每个单元格的值则循环获取即可	for cell in row:		print(cell.value)		# 列获取for col in sheet.iter_cols(min_row = 1, max_row = 5,						   min_col = 2, max_col = 6):	print(col)		for cell in col:

print(cell.value)

练习题

找出text_1.xlsx中sheet1表中空着的格子，并输出这些格子的坐标

from openpyxl import load_workbookexl = load_workbood('test_1.xlsx')sheet = exl.activefor row in sheet.iter_rows(min_row = 1, max_row = 29972,						   min_col = 1, max_col = 10):						   #具体查看对应表格的行列数	for cell in row:		if not cell.value:			print(cell.coordinate)

Excel写入写入单元格并保存

from openpyxl import load_workbookexl = load_workbook(filename = 'test.xlsx')sheet = exl.activesheet['A1'] = 'hello word'       #或者cell = sheet['A1'] #cell.value = 'hello word'exl.save(filename = 'test.xlsx') #存入原Excel表中，若创建新文件则可命名为不同名称

写入行数据并保存写入一行数据并保存

import xlwtworkbook = xlwt.Workbook(enCoding = 'utf-8')# 创建一个sheetsheet = workbook.add_sheet('My Worksheet')# 写入excel# 参数对应 行, 列, 值sheet.write(1,0,label = 'this is test')# 保存workbook.save('new_test.xls')

写入多行数据并保存

data = [['hello',22,'hi'],		['hell',23,'h'],		['he',25,'him']]for i in range(len(data)):    for j in range(len(data[i])):        worksheet.write(i,j,data[i][j])exl.save(filename = 'test.xlsx')

将公式写入单元格保存

sheet['A2'] = 'SUM(A1:D1)'exl.save(filename='test.xlsx')

插入列数据插入一列

sheet.insert_cols(IDx=2) #IDx=2第2列，第2列前插入一列

插入多列

#第2列前插入5列作为举例sheet.insert_cols(IDx=2, amount=5)

插入行数据

第2行前上面插入一行(或多行)

#插入一行sheet.insert_cols(IDx=2)#插入多行sheet.insert_cols(IDx=2, amount=5)

@L_403_12@删除删除多列

sheet.delete_cols(IDx=5, amount=2) #第5列前删除2列

删除多行

sheet.delete_rows(IDx=2, amount=5)

移动

当数字为正即向下或向右，为负即为向上或向左

sheet.move_range('C5:F10', row=2, cols=-3)

Sheet表 *** 作创建新的sheet

from openpyxl import load_workbookexl = load_workbook(filename = 'test.xlsx')print(exl.sheetnames)exl.create_sheet('new_sheet')

复制已有的sheet

exl.copy_worksheet(sheet)

修改sheet表名

sheet = exl.activesheet.Title = 'newname'

创建新的Excel表

from openpyxl import load_workbookworkbook = Workbook()sheet = workbook.activeworkbook.save(filename = 'new_test.xlsx')

Excel 样式设置字体样式

设置字体样式

Font(name字体名称,size大小,bold粗体,italic斜体,color颜色)

   from openpyxl import Workbook   from openpyxl.styles import Font      workbook = Workbook()   sheet = workbook.active   cell = sheet['A1']   Font = Font(name='字体', sizee=10, bold=True, italic=True, color='FF0000')   cell.Font = Font   workbook.save(filename='new_test')

设置多个格子的字体样式

   from openpyxl import Workbook   from openpyxl.styles import Font      workbook = Workbook()   sheet = workbook.active   cells = sheet[2]   Font = Font(name='字体', sizee=10, bold=True, italic=True, color='FF000000')   for cell in cells:   	cell.Font = Font   workbook.save(filename='new_test')

设置对其样式

水平对齐：distributed, justify, center, left, fill, centerContinuous, right, general

垂直对齐：bottom, distributed, justify, center, top

设置单元格边框样式

SIDe(style变现样式， color边线颜色)

border(左右上下边线)

from openpyxl import Workbookfrom openpyxl.styles import Fontworkbook = Workbook()sheet = workbook.activecell = sheet['A1']sIDe = SIDe(style='thin', color='FF000000')#先定好sIDe的格式border = border(left=sIDe, right=sIDe, top=sIDe, bottom=sIDe)#代入边线中cell.border = borderworkbook.save(filename='new_test')

设置单元格边框样式

变现样式：double, mediumDashDotDot, slantDashDot, dashDotDot, dotted, hair, mediumDashed, dashed, dashDot, thin, mediumDashDot, medium, thick

from openpyxl import Workbookfrom openpyxl.styles import Fontworkbook = Workbook()sheet = workbook.activecell = sheet['A1']pattern_fill = PatternFill(fill_type='solID', fgcolor cell1.fill = pattern_fill#单色填充cell2 = sheet['A3']gradIEnt_fill = GradIEntFill(stop=('FFFFFF', '99ccff','000000'))cell2.fill = gradIEnt_fill#渐变填充workbook.save(filename='new_test')

设置行高与列宽

from openpyxl import Workbook workbook = Workbook()sheet = workbook.activesheet.row_dimensions[1].height = 50sheet.column_dimensions['C'].wIDth = 20 workbook.save(filename='new_test')

合并、取消合并单元格

sheet.merge_cells('A1:B2')sheet.merge_cells(start_row=1, start_column=3,				  end_row=2, end_column=4)sheet.unmerge_cells('A1:B2')sheet.unmerge_cells(start_row=1, start_column=3,				    end_row=2, end_column=4)

练习题

打开test文件，找出文件中购买数量buy_mount超过5的行，并对其标红、加粗、附上边框。

from openpyxl import load_workbookfrom openpyxl.styles import Font, SIDe, border workbook = load_workbook('./test.xlsx') sheet = workbook.activebuy_mount = sheet['F'] row_lst = []for cell in buy_mount:	if isinstance(cell.value, int) and cell.value > 5: 		print(cell.row)		row_lst.append(cell.row)		sIDe = SIDe(style='thin', color='FF000000')border = border(left=sIDe, right=sIDe, top=sIDe, bottom=sIDe) Font = Font(bold=True, color='FF0000')for row in row_lst:	for cell in sheet[row]: 		cell.Font = Font 		cell.border = borderworkbook.save('new_test'.xlsx')

总结

以上是内存溢出为你收集整理的Python自动化之Excel全部内容，希望文章能够帮你解决Python自动化之Excel所遇到的程序开发问题。

如果觉得内存溢出网站内容还不错，欢迎将内存溢出网站推荐给程序员好友。