方法一:应用pip执行命令
安装openpyxl模块pip install openpyxl
方法二:在Pycharm中:File->Setting->左侧Project Interpreter,右上角有个 + ,搜openpyxl,再install package
from openpyxl import load_workbook exl = load_workbook(filename = '你说你咋个就不会呢.xlsx') print(exl.sheetnames)
['不会就是不会']
exl_1 = load_workbook(filename = '你说你咋个就不会呢.xlsx') # print(exl_1.sheetnames) sheet = exl_1['不会就是不会'] # 直接取名为“不会就是不会”的sheet
# 若只有一张表则: sheet = exl_1.active
print(sheet.dimensions) #看数据有多少行列
A1:D7
cell = sheet.cell(row=1,column=2) #指定行列数 print(cell.value) cell_1 = sheet['A2'] #指定坐标 print(cell_1.value)
销售单价 哆啦A梦
print(cell_1.row, cell_1.column, cell.coordinate) # 2 1 是A2单元格所在的位置,B1 是cell所在的位置
2 1 B1
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_workbook exl = load_workbook('test_1.xlsx') sheet = exl.active for row in sheet.iter_rows(min_row = 1, max_row = 5, min_col = 1, max_col = 4): #具体查看对应表格的行列数 for cell in row: if not cell.value: print(cell.coordinate)
备注:实际应用中可以查看有哪些值是空值,找到对应的数据并填充,比如问卷调查。
from openpyxl import load_workbook exl = load_workbook(filename = '你说你咋个就不会呢.xlsx') sheet = exl.active sheet['A1'] = 'hello 我是斗战胜佛' #或者cell = sheet['A1'] #cell.value = 'hello word' exl.save(filename = 'test.xlsx') #存入原Excel表中,若创建新文件则可命名为不同名称
import xlwt workbook = xlwt.Workbook(encoding = 'utf-8') # 创建一个sheet sheet = workbook.add_sheet('你干啥呢') # 写入excel # 参数对应 行, 列, 值 sheet.write(1,0,label = '我啥也没干') # 保存成一个新表 workbook.save('信你才怪.xlsx')
import xlwt # from openpyxl import load_workbook book = xlwt.Workbook(encoding = 'utf-8') # 创建一个sheet exl = book.add_sheet('只要数字不要文字') data = [[1,22,1], [3,23,3], [3,25,2]] for i in range(len(data)): for j in range(len(data[i])): exl.write(i,j,data[i][j]) book.save('数学使我快乐.xlsx')
python中使用openpyxl模块时报错: File is not a zip file。
来自网友的经验:
最大的原因就是不是真正的 xlsx文件, 如果是通过 库xlwt 新建的文件,或者是通过自己修改后缀名得到的 xlsx文件,都会报错,我遇到的解决办法基本都是自己使用 office 新建一个xlsx文件,网上说的是由于新版 office 加密的原因,只能通过 office 的软件才能建立真正的xlsx文件。
Python大坑:
openpyxl和(xlrd,xlwt)不可混用!
工程名、包名、文件名、模块名绝对不可重复!
(xlrd、xlwt)读写过的文件千万不可让openpyxl读写
以下代码可以将对应的位置更改相应的数据,其他数据不覆盖
from openpyxl import load_workbook exl = load_workbook(filename = '数学使我快乐.xlsx') xml_sheet = exl["只要数字不要文字"] data = [[1, 22, 3], [2, "我是异类",5], [3, 255, "异类也是你"]] for i in range(len(data)): for j in range(len(data[i])): print(i, j, data[i][j]) xml_sheet.cell(row=i + 1, column=j + 1).value = data[i][j] exl.save('数学使我快乐.xlsx')
0 0 1 0 1 22 0 2 3 1 0 2 1 1 我是异类 1 2 5 2 0 3 2 1 255 2 2 异类也是你
from openpyxl import load_workbook exl = load_workbook(filename ='test.xlsx') sheet =exl.active # 这一步骤很重要,因为要打开这个表才能进行后面的操作 sheet['D2'] = '=B2*C2' exl.save('test_new.xlsx')
from openpyxl import load_workbook exl = load_workbook(filename ='test.xlsx') sheet =exl.active sheet.insert_cols(idx=2) #idx=2第2列,第2列前插入一列 exl.save('test_11.xlsx')
#第2列前插入5列作为举例 sheet.insert_cols(idx=2, amount=5) exl.save('test_22.xlsx')
第2行前上面插入一行(或多行)
#插入一行 sheet.insert_rows(idx=2) #插入多行 sheet.insert_rows(idx=2, amount=5) # 记得save 就可看到对应的变化。
sheet.delete_cols(idx=5, amount=2) #第5列前删除2列
sheet.delete_rows(idx=2, amount=5)
当数字为正即向下或向右,为负即为向上或向左
move_range(“数据区域”,rows=1,cols=-1,translate=True) 向下移动1行,向左移动1列
在工作表中移动指定范围的单元格:
ws.move_range(“D4:F10”, rows=-1, cols=2)
以上代码将范围 D4:F10 中的单元格向上移动一列,向右移动两列,被移动单元将覆盖现有单元格。
如果单元格包含公式,openpyxl 可以翻译这些公式,由于这并非是想要的结果,默认情况下处于禁用状态。只有被移动单元格中的公式会被翻译,其他单元格或已定义名称对单元格的引用将不会更新,可以使用解析公式转换器执行此操作:
ws.move_range(“G4:H10”, rows=1, cols=1, translate=True)
以上代码将指定移动内单元格范围内公式相对引用移动一行和一列。
from openpyxl import load_workbook exl = load_workbook(filename ='你说你咋个就不会呢.xlsx') sheet =exl.active sheet.move_range('C1:D4', rows =1, cols=-1) exl.save(filename='我是一个鬼精灵.xlsx')
from openpyxl import load_workbook # 读取文件的sheet名 exl = load_workbook(filename = '抄表大佬.xlsx') print(exl.sheetnames)
['是你吗', '是的', '不是']
# 创建新的sheet exl.create_sheet('哦,我们都不是')
<Worksheet "哦,我们都不是">
ws = exl['是的'] exl.copy_worksheet(ws)
<Worksheet "是的 Copy">
sheet = exl['是你吗'] sheet.title = '真的是你吗'
以上都要重新保存才可出现你想要的效果。
from openpyxl import Workbook wb = Workbook() sheet = wb.active wb.save(filename = '露娜不是噜啦.xlsx')
设置字体样式
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='草书', size=10, bold=True, italic=True, color='FF0000') cell.font = font workbook.save('excel样式设置试运行.xlsx')
from openpyxl import Workbook from openpyxl.styles import Font workbook = Workbook() sheet = workbook.active font = Font(name='隶书', size=10, bold=True, italic=True, color='FF000000') for x in range(1,5): sheet.cell(row=x,column=2).font = font #将第二列的1,2,3,4行样式进行逐一替换 workbook.save('excel样式.xlsx')
想要某列/某行设置同样的格式,如下:
from openpyxl import load_workbook from openpyxl.styles import Font exl = load_workbook(filename ='你说你咋个就不会呢.xlsx') sheet =exl.active cells = sheet[2] font = Font(name='字体', size=10, bold=True, italic=True, color='FF000000') for cell in cells: cell.font = font exl.save('看看我变化了没.xlsx')
---------------------------------------------------------------------------
水平对齐:distributed, justify, center, left, fill, centerContinuous, right, general
垂直对齐:bottom, distributed, justify, center, top
from openpyxl import Workbook from openpyxl.styles import Alignment workbook = Workbook() sheet = workbook.active cell = sheet['A1'] alignment = Alignment(horizontal="center",vertical="center",text_rotation=30,wrap_text=True) cell.alignment = alignment workbook.save('对齐样式.xlsx')
Side(style=边线样式,color=边线颜色),颜色只能是16进制的颜色名
Border(left=左边线样式,right=右边线样式,top=上边线样式,bottom=下边线样式)
边线样式:double, mediumDashDotDot, slantDashDot, dashDotDot, dotted, hair, mediumDashed, dashed, dashDot, thin, mediumDashDot, medium, thick
from openpyxl import Workbook from openpyxl.styles import Side,Border workbook = Workbook() sheet = workbook.active cell = sheet['D6'] side_left = Side(style='double', color='FF0000') side_right = Side(style='dashDot', color='FF000000') #先定好side的格式 border = Border(left=side_left, right=side_right, top=side_right, bottom=side_left) #代入边线中 cell.border = border workbook.save('单元格边框样式.xlsx')
from openpyxl import Workbook from openpyxl.styles import PatternFill,GradientFill workbook = Workbook() sheet = workbook.active cell1 = sheet['B3'] cell2 = sheet['B4'] #单色填充 pattern_fill = PatternFill(fill_type='solid', fgColor ="FF0000") cell1.fill = pattern_fill #渐变填充 gradient_fill = GradientFill(stop=('FFFFFF', '99ccff','000000')) cell2.fill = gradient_fill workbook.save(filename='单元格填充样式.xlsx')
from openpyxl import Workbook workbook = Workbook() sheet = workbook.active sheet.row_dimensions[1].height = 50 sheet.column_dimensions['C'].width = 20 workbook.save(filename='行高列宽设置.xlsx')
from openpyxl import Workbook workbook = Workbook() sheet = workbook.active sheet.merge_cells('A1:B2') sheet.merge_cells(start_row=1, start_column=3,end_row=2, end_column=4) workbook.save(filename='合并单元格.xlsx')
from openpyxl import load_workbook workbook = load_workbook(filename="合并单元格.xlsx") sheet = workbook.active sheet.unmerge_cells('A1:B2') sheet.unmerge_cells(start_row=1, start_column=3,end_row=2, end_column=4) workbook.save(filename='取消合并单元格.xlsx')
打开“你说你咋个就不会呢.xlsx”文件,找出文件中单价超过5的行,并对其标红、加粗、附上边框。
from openpyxl import load_workbook from openpyxl.styles import Font, Side, Border workbook = load_workbook('你说你咋个就不会呢.xlsx') sheet = workbook.active price = sheet['B'] row_lst = [] for cell in price: if isinstance(cell.value, int) and cell.value > 5: row_lst.append(cell.row) side_left = Side(style='mediumDashDotDot', color='FF0000') side_right = Side(style='mediumDashDotDot', color='6C91FC') side_top = Side(style='thin', color='3E7AAB') side_bottom = Side(style='double', color='3E7AAB') border = Border(left=side_left, right=side_right, top=side_top, bottom=side_bottom) font = Font(bold=True, color='FF0000') for row in row_lst: for cell in sheet[row]: cell.font = font cell.border = border workbook.save('练习题.xlsx')