网站怎么做app吗,泗阳网页定制,建筑施工建设网站,中国建设银行网站登录不上注#xff1a;以下文字大部分文字和代码由GPT生成
一、openpyxl详细介绍 Openpyxl是一个用于读取和编写Excel 2010 xlsx/xlsm/xltx/xltm文件的Python库。它允许您使用Python操作Excel文件#xff0c;包括创建新的工作簿、读取和修改现有工作簿中的数据、设置单元格格式以及编…注以下文字大部分文字和代码由GPT生成
一、openpyxl详细介绍 Openpyxl是一个用于读取和编写Excel 2010 xlsx/xlsm/xltx/xltm文件的Python库。它允许您使用Python操作Excel文件包括创建新的工作簿、读取和修改现有工作簿中的数据、设置单元格格式以及编写公式。Openpyxl提供了丰富的功能包括对工作表、单元格、图表和样式的操作使得处理Excel文件变得简单而高效。
二、基本操作
2.1 openpyxl 修改字体
from openpyxl import load_workbook
from openpyxl.styles import Font, Alignment# Load the workbook
workbook load_workbook(your_file.xlsx)
sheet workbook.active# Apply font and alignment to all cells
for row in sheet.iter_rows():for cell in row:cell.font Font(nameArial, size12, boldTrue)cell.alignment Alignment(horizontalcenter, verticalcenter)# Save the workbook
workbook.save(your_file.xlsx)
2.2 openpyxl 加边框
from openpyxl import load_workbook
from openpyxl.styles import Border, Side# Load the workbook
workbook load_workbook(your_file.xlsx)
sheet workbook.active# Create a border style
border_style Border(leftSide(stylethin), rightSide(stylethin), topSide(stylethin), bottomSide(stylethin))# Apply border to all cells
for row in sheet.iter_rows():for cell in row:cell.border border_style# Save the workbook
workbook.save(your_file.xlsx)
2.3 openpyxl 文字居中
from openpyxl import load_workbook
from openpyxl.styles import Alignment# Load the workbook
workbook load_workbook(your_file.xlsx)
sheet workbook.active# Apply center alignment to all cells
for row in sheet.iter_rows():for cell in row:cell.alignment Alignment(horizontalcenter, verticalcenter)# Save the workbook
workbook.save(your_file.xlsx)
2.4 openpyxl 调整列宽
from openpyxl import load_workbook# Load the workbook
workbook load_workbook(your_file.xlsx)
sheet workbook.active# Adjust column widths
sheet.column_dimensions[A].width 20 # Adjust the width of column A to 20
sheet.column_dimensions[B].width 30 # Adjust the width of column B to 30# Save the workbook
workbook.save(your_file.xlsx)
2.5 openpyxl 数字只显示小数点后4位
from openpyxl import load_workbook# Load the workbook
workbook load_workbook(your_file.xlsx)
sheet workbook.active# Set the number format to display only four decimal places
for row in sheet.iter_rows():for cell in row:cell.number_format 0.0000 # Display only four decimal places# Save the workbook
workbook.save(your_file.xlsx)
2.6 openpyxl 改变背景色
from openpyxl import load_workbook
from openpyxl.styles import PatternFill# Load the workbook
workbook load_workbook(your_file.xlsx)
sheet workbook.active# Set the background color to green for all cells
green_fill PatternFill(start_color00FF00, end_color00FF00, fill_typesolid)
for row in sheet.iter_rows():for cell in row:cell.fill green_fill# Save the workbook
workbook.save(your_file.xlsx)
2.7 openpyxl 读取值某行某列值
from openpyxl import load_workbook# Load the workbook
workbook load_workbook(your_file.xlsx)
sheet workbook.active# Read the value of a specific cell
value sheet.cell(row1, column1).value # Replace row and column with the desired cell coordinates# Print the value
print(value)
2.8 openpyxl 写入某行某列值
from openpyxl import load_workbook# Load the workbook
workbook load_workbook(your_file.xlsx)
sheet workbook.active# Write a value to a specific cell
sheet.cell(row1, column1, valueHello, World!) # Replace row and column with the desired cell coordinates# Save the workbook
workbook.save(your_file.xlsx)
2.9 openpyxl 设置文字颜色
from openpyxl import load_workbook
from openpyxl.styles import Font# Load the workbook
workbook load_workbook(your_file.xlsx)
sheet workbook.active# Set the text color of a specific cell
cell sheet.cell(row1, column1) # Replace row and column with the desired cell coordinates
cell.value Hello, World!
cell.font Font(colorFF0000) # Set the text color to red# Save the workbook
workbook.save(your_file.xlsx)
2.10 openpyxl设置公式
直接写入公式字符串即可例如AVERAGE(D18:D33) 但是表格的列需要转换为字母表达
# 使用以下代码将列数转换为字母
def num_to_col_letters(num):letters while num 0:num, remainder divmod(num - 1, 26)letters chr(65 remainder) lettersreturn letters
三、综合效果
以上gpt生成的代码都验证通过最后加粗、加边框、加颜色、居中实现效果如下
