python操作excel（Python操作excel清除公式保留内容）

python操作excel（Python操作excel清除公式保留内容）

pip install openpyxl 读excel

from openpyxl import load_workbook wb = load_workbook("files/p1.xlsx") # sheet相关操作 # 1.获取excel文件中的所有sheet名称 """ print(wb.sheetnames) # ['数据导出', '用户列表', 'Sheet1', 'Sheet2'] """ # 2.选择sheet，基于sheet名称 """ sheet = wb["数据导出"] cell = sheet.cell(1, 2) print(cell.value) """ # 3.选择sheet，基于索引位置 """ sheet = wb.worksheets[0] cell = sheet.cell(1,2) print(cell.value) """ # 4.循环所有的sheet """ for name in wb.sheetnames: sheet = wb[name] cell = sheet.cell(1, 1) print(cell.value) """ """ for sheet in wb.worksheets: cell = sheet.cell(1, 1) print(cell.value) """ """ for sheet in wb: cell = sheet.cell(1, 1) print(cell.value) """ ================================================== from openpyxl import load_workbook wb = load_workbook("files/p1.xlsx") sheet = wb.worksheets[0] # 1.获取第N行第N列的单元格(位置是从1开始） """ cell = sheet.cell(1, 1) print(cell.value) print(cell.style) print(cell.font) print(cell.alignment) """ # 2.获取某个单元格 """ c1 = sheet["A2"] print(c1.value) c2 = sheet['D4'] print(c2.value) """ # 3.第N行所有的单元格 """ for cell in sheet[1]: print(cell.value) """ # 4.所有行的数据（获取某一列数据） """ for row in sheet.rows: print(row[0].value, row[1].value) """ # 5.获取所有列的数据 """ for col in sheet.columns: print(col[1].value) """ ======================================================= from openpyxl import load_workbook wb = load_workbook("files/p1.xlsx") sheet = wb.worksheets[2] # 获取第N行第N列的单元格(位置是从1开始） c1 = sheet.cell(1, 1) print(c1) # print(c1.value) # 用户信息 c2 = sheet.cell(1, 2) print(c2) # print(c2.value) # None ==================================== from openpyxl import load_workbook wb = load_workbook('files/p1.xlsx') sheet = wb.worksheets[2] for row in sheet.rows: print(row)

写excel

# 源文件上写 from openpyxl import load_workbook wb = load_workbook('files/p1.xlsx') sheet = wb.worksheets[0] # 找到单元格，并修改单元格的内容 cell = sheet.cell(1, 1) cell.value = "新的开始" # 将excel文件保存到p2.xlsx文件中 wb.save("files/p2.xlsx") =================================== # 新建文件写 from openpyxl import workbook # 创建excel且默认会创建一个sheet（名称为Sheet） wb = workbook.Workbook() sheet = wb.worksheets[0] # 或 sheet = wb["Sheet"] # 找到单元格，并修改单元格的内容 cell = sheet.cell(1, 1) cell.value = "新的开始" # 将excel文件保存到p2.xlsx文件中 wb.save("files/p2.xlsx") =================================== from openpyxl import workbook wb = workbook.Workbook() # Sheet # 1. 修改sheet名称 """ sheet = wb.worksheets[0] sheet.title = "数据集" wb.save("p2.xlsx") """ # 2. 创建sheet并设置sheet颜色 """ sheet = wb.create_sheet("工作计划", 0) sheet.sheet_properties.tabColor = "1072BA" wb.save("p2.xlsx") """ # 3. 默认打开的sheet """ wb.active = 0 wb.save("p2.xlsx") """ # 4. 拷贝sheet """ sheet = wb.create_sheet("工作计划") sheet.sheet_properties.tabColor = "1072BA" new_sheet = wb.copy_worksheet(wb["Sheet"]) new_sheet.title = "新的计划" wb.save("p2.xlsx") """ # 5.删除sheet """ del wb["用户列表"] wb.save('files/p2.xlsx') """ =========================================== from openpyxl import load_workbook from openpyxl.styles import Alignment, Border, Side, Font, PatternFill, GradientFill wb = load_workbook('files/p1.xlsx') sheet = wb.worksheets[1] # 1. 获取某个单元格，修改值 """ cell = sheet.cell(1, 1) cell.value = "开始" wb.save("p2.xlsx") """ # 2. 获取某个单元格，修改值 """ sheet["B3"] = "Alex" wb.save("p2.xlsx") """ # 3. 获取某些单元格，修改值 """ cell_list = sheet["B2":"C3"] for row in cell_list: for cell in row: cell.value = "新的值" wb.save("p2.xlsx") """ # 4. 对齐方式 """ cell = sheet.cell(1, 1) # horizontal，水平方向对齐方式："general", "left", "center", "right", "fill", "justify", "centerContinuous", "distributed" # vertical，垂直方向对齐方式："top", "center", "bottom", "justify", "distributed" # text_rotation，旋转角度。 # wrap_text，是否自动换行。 cell.alignment = Alignment(horizontal='center', vertical='distributed', text_rotation=45, wrap_text=True) wb.save("p2.xlsx") """ # 5. 边框 # side的style有如下：dashDot','dashDotDot', 'dashed','dotted','double','hair', 'medium', 'mediumDashDot', 'mediumDashDotDot','mediumDashed', 'slantDashDot', 'thick', 'thin' """ cell = sheet.cell(9, 2) cell.border = Border( top=Side(style="thin", color="FFB6C1"), bottom=Side(style="dashed", color="FFB6C1"), left=Side(style="dashed", color="FFB6C1"), right=Side(style="dashed", color="9932CC"), diagonal=Side(style="thin", color="483D8B"), # 对角线 diagonalUp=True, # 左下 ~ 右上 diagonalDown=True # 左上 ~ 右下 ) wb.save("p2.xlsx") """ # 6.字体 """ cell = sheet.cell(5, 1) cell.font = Font(name="微软雅黑", size=45, color="ff0000", underline="single") wb.save("p2.xlsx") """ # 7.背景色 """ cell = sheet.cell(5, 3) cell.fill = PatternFill("solid", fgColor="99ccff") wb.save("p2.xlsx") """ # 8.渐变背景色 """ cell = sheet.cell(5, 5) cell.fill = GradientFill("linear", stop=("FFFFFF", "99ccff", "000000")) wb.save("p2.xlsx") """ # 9.宽高（索引从1开始） """ sheet.row_dimensions[1].height = 50 sheet.column_dimensions["E"].width = 100 wb.save("p2.xlsx") """ # 10.合并单元格 """ sheet.merge_cells("B2:D8") sheet.merge_cells(start_row=15, start_column=3, end_row=18, end_column=8) wb.save("p2.xlsx") """ """ sheet.unmerge_cells("B2:D8") wb.save("p2.xlsx") """ # 11.写入公式 """ sheet = wb.worksheets[3] sheet["D1"] = "合计" sheet["D2"] = "=B2*C2" wb.save("p2.xlsx") """ """ sheet = wb.worksheets[3] sheet["D3"] = "=SUM(B3,C3)" wb.save("p2.xlsx") """ # 12.删除 """ # idx，要删除的索引位置 # amount，从索引位置开始要删除的个数（默认为1） sheet.delete_rows(idx=1, amount=20) sheet.delete_cols(idx=1, amount=3) wb.save("p2.xlsx") """ # 13.插入 """ sheet.insert_rows(idx=5, amount=10) sheet.insert_cols(idx=3, amount=2) wb.save("p2.xlsx") """ # 14.循环写内容 """ sheet = wb["Sheet"] cell_range = sheet['A1:C2'] for row in cell_range: for cell in row: cell.value = "xx" for row in sheet.iter_rows(min_row=5, min_col=1, max_col=7, max_row=10): for cell in row: cell.value = "oo" wb.save("p2.xlsx") """ # 15.移动 """ # 将H2:J10范围的数据，向右移动15个位置、向上移动1个位置 sheet.move_range("H2:J10",rows=1, cols=15) wb.save("p2.xlsx") """ """ sheet = wb.worksheets[3] sheet["D1"] = "合计" sheet["D2"] = "=B2*C2" sheet["D3"] = "=SUM(B3,C3)" sheet.move_range("B1:D3",cols=10, translate=True) # 自动翻译公式 wb.save("p2.xlsx") """ # 16.打印区域 """ sheet.print_area = "A1:D200" wb.save("p2.xlsx") """ # 17.打印时，每个页面的固定表头 """ sheet.print_title_cols = "A:D" sheet.print_title_rows = "1:3" wb.save("p2.xlsx") """

文件路径处理

import shutil import os # 1. 获取当前脚本绝对路径 """ abs_path = os.path.abspath(__file__) print(abs_path) """ # 2. 获取当前文件的上级目录 """ base_path = os.path.dirname( os.path.dirname(路径) ） print(base_path) """ # 3. 路径拼接 """ p1 = os.path.join(base_path, 'xx') print(p1) p2 = os.path.join(base_path, 'xx', 'oo', 'a1.png') print(p2) """ # 4. 判断路径是否存在 """ exists = os.path.exists(p1) print(exists) """ # 5. 创建文件夹 """ os.makedirs(路径) """ """ path = os.path.join(base_path, 'xx', 'oo', 'uuuu') if not os.path.exists(path): os.makedirs(path) """ # 6. 是否是文件夹 """ file_path = os.path.join(base_path, 'xx', 'oo', 'uuuu.png') is_dir = os.path.isdir(file_path) print(is_dir) # False folder_path = os.path.join(base_path, 'xx', 'oo', 'uuuu') is_dir = os.path.isdir(folder_path) print(is_dir) # True """ # 7. 删除文件或文件夹 """ os.remove("文件路径") """ """ path = os.path.join(base_path, 'xx') shutil.rmtree(path) """ # 8. 拷贝文件夹 """ shutil.copytree("/Users/wupeiqi/Desktop/图/csdn/","/Users/wupeiqi/PycharmProjects/CodeRepository/files") """ # 9.拷贝文件 """ shutil.copy("/Users/wupeiqi/Desktop/图/csdn/WX20201123-112406@2x.png","/Users/wupeiqi/PycharmProjects/CodeRepository/") shutil.copy("/Users/wupeiqi/Desktop/图/csdn/WX20201123-112406@2x.png","/Users/wupeiqi/PycharmProjects/CodeRepository/x.png") """ # 10.文件或文件夹重命名 """ shutil.move("/Users/wupeiqi/PycharmProjects/CodeRepository/x.png","/Users/wupeiqi/PycharmProjects/CodeRepository/xxxx.png") shutil.move("/Users/wupeiqi/PycharmProjects/CodeRepository/files","/Users/wupeiqi/PycharmProjects/CodeRepository/images") """

版权声明：本文内容由网络用户投稿，版权归原作者所有，本站不拥有其著作权，亦不承担相应法律责任。如果您发现本站中有涉嫌抄袭或描述失实的内容，请联系我们jiasou666@gmail.com 处理，核实后本网站将在24小时内删除侵权内容。