python操作Excel
一、–xls–
1.xlwt
新建 Excel 写入数据
写入excel无所谓用哪个,后缀改一下就可以了。
主要用xlwt处理xls
import xlwt
def datas_2_excel_xls(datas,file_path):
# 创建一个Workbook对象,这就相当于创建了一个Excel文件
workbook = xlwt.Workbook(encoding="utf-8", style_compression=0)
# 添加一个工作表
worksheet = workbook.add_sheet('Sheet1', cell_overwrite_ok=True)
for row in range(len(datas)):
for col in range(len(datas[0])):
worksheet.write(row, col, datas[row][col])
# 保存为Excel文件
workbook.save(file_path)
if __name__ == '__main__':
demo_datas = [
['Number', 'Batch', 'ID'],
[2, 'A', '001'],
[4, 'B', '002'],
[6, 'A', '003'],
[8, 'C', '004']
]
file_path = 'example.xls'
datas_2_excel_xls(demo_datas,file_path)
2.xlrd
读取Excel 数据
import xlrd
def read_xls_xlrd(file_path):
# 打开 Excel 文件
workbook = xlrd.open_workbook(file_path)
# 选择工作簿中的一个工作表
worksheet = workbook.sheet_by_index(0) # 通过索引选择,0 表示第一个工作表
# 或者
# worksheet = workbook.sheet_by_name('Sheet1') # 通过名称选择工作表
# 获取工作表的行数和列数
rows = worksheet.nrows
cols = worksheet.ncols
datas = []
# 1.遍历工作表,获取每行的内容,按行
for i in range(rows):
data = worksheet.row_values(i) # 打印整行内容
datas.append(data)
# #2.遍历工作表每个数据,按单元格
# for i in range(rows):
# data = []
# for j in range(cols):
# data.append(worksheet.cell_value(i,j))
# datas.append(data)
# print(datas)
# # 也可以获取特定单元格的内容
# cell_value = worksheet.cell_value(0, 0) # 获取第一行第一列的单元格内容
# print(cell_value)
return datas
if __name__ == '__main__':
file_path = 'example.xls'
req_datas = read_xls_xlrd(file_path)
print(req_datas)
二、–xlsx–
处理xlsx有两个都可以用
1.xlsxwriter
2.openpyxl
1.xlsxwriter
xlsxwriter 是一个用于创建和写入 Excel 2010 xlsx 文件的 Python 模块。它提供了一个用于写入 Excel
文件的简单接口,支持创建工作簿、工作表、设置单元格样式、合并单元格等功能。与 openpyxl 不同,xlsxwriter
主要用于写入数据到 Excel 文件,而不是读取或修改现有文件。
1️⃣读取 Excel 数据(没办法)
2️⃣新建 Excel 写入数据
import xlsxwriter
def datas_2_excel_xlsx_xlsxwriter(datas,file_path):
wb = xlsxwriter.Workbook(file_path)
sheet = wb.add_worksheet('Sheet1')
# 样式设置
format1 = {
'font_name': '宋', # 字体
'font_size': 10, # 字体大小
'font_color': 'black', # 字体颜色
'bold': True, # 是否粗体
'align': 'center', # 水平居中对齐
'valign': 'vcenter', # 垂直居中对齐
'right': 1,
'bottom': 2, # 底边框
'bg_color': '#ffc7ce', # 设置背景颜色粉色
}
# 默认背景白色
format2 = {
'font_name': '宋', # 字体
'font_size': 10, # 字体大小
'font_color': 'black', # 字体颜色
'bold': True, # 是否粗体
'align': 'center', # 水平居中对齐
'valign': 'vcenter', # 垂直居中对齐
'right': 1,
'bottom': 2, # 底边框
}
red_style = wb.add_format(format1)
white_style = wb.add_format(format2)
# 设置列宽
sheet.set_column(0, 0, 15)
sheet.set_column(1, 1, 15)
# 设置列宽
sheet.set_column(0, 0, 15)
sheet.set_column(1, 1, 15)
for row in range(len(datas)):
for col in range(len(datas[0])):
sheet.write(row, col, datas[row][col], white_style)
wb.close()
if __name__ == '__main__':
demo_datas = [
['Number', 'Batch', 'ID'],
[2, 'A', '001'],
[4, 'B', '002'],
[6, 'A', '003'],
[8, 'C', '004']
]
file_path = 'example.xls'
datas_2_excel_xlsx_xlsxwriter(demo_datas, file_path)
2.openpyxl
2.1 靠自己
1️⃣读取 Excel 数据
import openpyxl
def get_rules(worksheet):
# 获取条件格式规则及其索引
datas = {}
for index, rules in worksheet.conditional_formatting._cf_rules.items():
# 打印规则的详细信息
for rule in rules:
try:
print(f"Rule Index: {index.sqref}")
print(f"Rule Type: {rule.type}")
print(f"Formula: {rule.formula}")
print(f"Stop If True: {rule.stopIfTrue}")
# print(f"Show Formula Bar: {rules.showFormulaBar}")
# print(f"Priority: {rule.priority}")
# ... 你可以根据需要添加更多属性的访问
print()
except:
pass
datas[index.sqref] = {
'Rule Index':index.sqref,
'Rule Type':rule.type,
'Formula':rule.formula,
'Stop If True':rule.stopIfTrue
}
return datas
def read_xlsx_openpyxl(file_path):
wb = openpyxl.load_workbook(file_path)
sheet = wb.get_sheet_by_name(wb.sheetnames[0])
datas = []
#获取条件格式
#datas = get_rules(sheet)
#print(datas)
#获取数据
for i in range(1, sheet.max_row + 1):
data = []
for j in range(1, sheet.max_column + 1):
data.append(sheet.cell(i, j).value)
datas.append(data)
return datas
if __name__ == '__main__':
file_path = 'example.xlsx'
req_datas = read_xlsx_openpyxl(file_path)
print(req_datas)
2️⃣新建 Excel 写入数据
openpyxl 是一个用于读写 Excel 2010 xlsx/xlsm/xltx/xltm 文件的 Python 库。它允许用户创建新的 Excel 文件,修改现有的文件,以及从文件中读取数据。此外,openpyxl 还支持添加图表、图像、表格等元素到 Excel 文件中。
注意和其他方法不一样的是,openpyxl是用numpy为基础,打包很大,行列从1开始。
import openpyxl
from openpyxl.styles import NamedStyle, Font, Alignment, Border, Side, PatternFill, Color
from openpyxl.utils import get_column_letter
from openpyxl.styles.fills import PatternFill
def set_style():
# ☝定义样式
style = NamedStyle(name='format1')
# 字体
# openpyxl.styles.fonts.Font(name=None, sz=None, b=None, i=None, charset=None, u=None, strike=None, color=None,
# scheme=None, family=None, size=None, bold=None, italic=None, strikethrough=None,
# underline=None, vertAlign=None, outline=None, shadow=None, condense=None, extend=None)
# 常用设置:
# name==字体名称
# color==字体颜色=Color
# sz==size==字体大小=float
# b==bold==是否粗体=bool
# i==italic==是否斜体=bool
# u==underline==下划线
# 取值范围: 'single', 'doubleAccounting', 'singleAccounting', 'double'
# strike==strikethrough== 删除线=bool
# vertAlign 取值范围 'superscript', 'baseline', 'subscript'
# superscript==上标
# subscript==下标
font = Font(name="黑体", sz=10, b=True, i=True, color=Color('00000000'))
style.font = font
# 边框
# openpyxl.styles.borders.Side(style=None, color=None, border_style=None)
# style 取值范围:
# style == 'dashDot', 'dashDotDot', 'dashed', 'mediumDashDot', 'double', 'slantDashDot', 'thin', 'hair', 'dotted',
# 'thick', 'mediumDashed', 'mediumDashDotDot', 'medium'
l_side = Side(style='hair', color=Color('00000000'))
r_side = Side(style='hair', color=Color('00000000'))
t_side = Side(style='hair', color=Color('00000000'))
b_side = Side(style='thick', color=Color('00000000'))
border = Border(left=l_side, right=r_side, top=t_side, bottom=b_side, vertical=l_side)
style.border = border
# 设置水平方向对齐方式
alignment = Alignment()
# horizontal 取值范围:
# horizontal = {'right', 'justify', 'fill', 'center', 'general', 'left', 'distributed', 'centerContinuous'}
alignment.horizontal = 'center'
# vertical 取值范围:
# vertical = {'bottom', 'justify', 'center', 'top', 'distributed'}
alignment.vertical = 'center'
# 单元格设置
style.alignment = alignment
# 填充
# openpyxl.styles.fills模块:
# openpyxl.styles.fills.GradientFill 用渐变填充区域
# openpyxl.styles.fills.PatternFill 用图案填充区域
## openpyxl.styles.fills.GradientFill(type='linear', degree=0, left=0, right=0, top=0, bottom=0, stop=())
# 支持两种渐变填充
# 1.type='linear'渐变在一个区域的长度上,在一组指定的停止点之间插入颜色
# 2.type='path'渐变从区域的每个边缘应用线性渐变
# from openpyxl.styles.fills import GradientFill
# cell.fill = GradientFill(type='linear', stop=(Color(indexed=10), Color(indexed=20), Color(indexed=30)))
# cell.fill = GradientFill(type='path', stop=(Color(indexed=10), Color(indexed=20), Color(indexed=30)))
# openpyxl.styles.fills.PatternFill 用图案填充区域
# openpyxl.styles.fills.PatternFill(patternType=None, fgColor= < openpyxl.styles.colors.Color object >
# bgColor = < openpyxl.styles.colors.Color object >
# fill_type = None, start_color = None, end_color = None)
# patternType 取值范围:
# 'gray0625', 'gray125', 'solid', 'darkHorizontal', 'lightGrid', 'lightTrellis', 'lightVertical',
# 'mediumGray', 'darkTrellis', 'lightGray', 'darkUp', 'darkGrid', 'lightHorizontal', 'lightUp', 'darkDown',
# 'darkGray', 'darkVertical', 'lightDown'
# from openpyxl.styles.fills import PatternFill
#
# cell.fill = PatternFill(patternType='solid', fgColor=Color(indexed=10))
#
black_fill = PatternFill(start_color="00000000", end_color="00000000", fill_type="solid")
# red_fill = PatternFill(start_color="00FF0000", end_color="00FF0000", fill_type="solid")
style.fill = black_fill
return style
def datas_2_excel_xlsx_openpyxl(datas, file_path):
wb = openpyxl.Workbook()
sheet = wb.get_sheet_by_name(wb.sheetnames[0])
# 设置第1列的宽度为30
column_index = 1
column_letter = get_column_letter(column_index)
sheet.column_dimensions[column_letter].width = 20
wb.add_named_style(set_style())
for row in range(1, len(datas) + 1):
for col in range(1, len(datas[0]) + 1):
# 获取单元格
cell = sheet.cell(row, col )
cell.style = 'format1'
value = datas[row-1][col-1]
cell.value = value
wb.save(file_path)
if __name__ == '__main__':
demo_datas = [
['Number', 'Batch', 'ID'],
[2, 'A', '001'],
[4, 'B', '002'],
[6, 'A', '003'],
[8, 'C', '004']
]
file_path = 'example1.xlsx'
datas_2_excel_xlsx_openpyxl(demo_datas, file_path)
3️⃣新增Excel数据
import openpyxl
from openpyxl.styles import NamedStyle, Font, Alignment, Border, Side, PatternFill, Color
from openpyxl.utils import get_column_letter
from openpyxl.styles.fills import PatternFill
def set_style():
# ☝定义样式
style = NamedStyle(name='format1')
# 字体
# openpyxl.styles.fonts.Font(name=None, sz=None, b=None, i=None, charset=None, u=None, strike=None, color=None,
# scheme=None, family=None, size=None, bold=None, italic=None, strikethrough=None,
# underline=None, vertAlign=None, outline=None, shadow=None, condense=None, extend=None)
# 常用设置:
# name==字体名称
# color==字体颜色=Color
# sz==size==字体大小=float
# b==bold==是否粗体=bool
# i==italic==是否斜体=bool
# u==underline==下划线
# 取值范围: 'single', 'doubleAccounting', 'singleAccounting', 'double'
# strike==strikethrough== 删除线=bool
# vertAlign 取值范围 'superscript', 'baseline', 'subscript'
# superscript==上标
# subscript==下标
font = Font(name="黑体", sz=10, b=True, i=True, color=Color('00000000'))
style.font = font
# 边框
# openpyxl.styles.borders.Side(style=None, color=None, border_style=None)
# style 取值范围:
# style == 'dashDot', 'dashDotDot', 'dashed', 'mediumDashDot', 'double', 'slantDashDot', 'thin', 'hair', 'dotted',
# 'thick', 'mediumDashed', 'mediumDashDotDot', 'medium'
l_side = Side(style='hair', color=Color('00000000'))
r_side = Side(style='hair', color=Color('00000000'))
t_side = Side(style='hair', color=Color('00000000'))
b_side = Side(style='thick', color=Color('00000000'))
border = Border(left=l_side, right=r_side, top=t_side, bottom=b_side, vertical=l_side)
style.border = border
# 设置水平方向对齐方式
alignment = Alignment()
# horizontal 取值范围:
# horizontal = {'right', 'justify', 'fill', 'center', 'general', 'left', 'distributed', 'centerContinuous'}
alignment.horizontal = 'center'
# vertical 取值范围:
# vertical = {'bottom', 'justify', 'center', 'top', 'distributed'}
alignment.vertical = 'center'
# 单元格设置
style.alignment = alignment
# 填充
# openpyxl.styles.fills模块:
# openpyxl.styles.fills.GradientFill 用渐变填充区域
# openpyxl.styles.fills.PatternFill 用图案填充区域
## openpyxl.styles.fills.GradientFill(type='linear', degree=0, left=0, right=0, top=0, bottom=0, stop=())
# 支持两种渐变填充
# 1.type='linear'渐变在一个区域的长度上,在一组指定的停止点之间插入颜色
# 2.type='path'渐变从区域的每个边缘应用线性渐变
# from openpyxl.styles.fills import GradientFill
# cell.fill = GradientFill(type='linear', stop=(Color(indexed=10), Color(indexed=20), Color(indexed=30)))
# cell.fill = GradientFill(type='path', stop=(Color(indexed=10), Color(indexed=20), Color(indexed=30)))
# openpyxl.styles.fills.PatternFill 用图案填充区域
# openpyxl.styles.fills.PatternFill(patternType=None, fgColor= < openpyxl.styles.colors.Color object >
# bgColor = < openpyxl.styles.colors.Color object >
# fill_type = None, start_color = None, end_color = None)
# patternType 取值范围:
# 'gray0625', 'gray125', 'solid', 'darkHorizontal', 'lightGrid', 'lightTrellis', 'lightVertical',
# 'mediumGray', 'darkTrellis', 'lightGray', 'darkUp', 'darkGrid', 'lightHorizontal', 'lightUp', 'darkDown',
# 'darkGray', 'darkVertical', 'lightDown'
# from openpyxl.styles.fills import PatternFill
#
# cell.fill = PatternFill(patternType='solid', fgColor=Color(indexed=10))
#
black_fill = PatternFill(start_color="00000000", end_color="00000000", fill_type="solid")
# red_fill = PatternFill(start_color="00FF0000", end_color="00FF0000", fill_type="solid")
style.fill = black_fill
return style
def datas_2_excel_xlsx_openpyxl(datas, file_path,flag='row'):
wb = openpyxl.load_workbook(file_path)
sheet = wb.get_sheet_by_name(wb.sheetnames[0])
#读取已经有的列号
start_col = sheet.max_column
# 读取已经有的行号
start_row = sheet.max_row
if flag=='row':
#向下添加
for row in range(1, len(datas) + 1):
for col in range(1, len(datas[0]) + 1):
# 获取单元格
cell = sheet.cell(row+start_row, col )
cell.style = 'format1'
value = datas[row-1][col-1]
cell.value = value
elif flag=='col':
# 向右添加
for row in range(1, len(datas) + 1):
for col in range(1, len(datas[0]) + 1):
# 获取单元格
cell = sheet.cell(row, col+start_col )
cell.style = 'format1'
value = datas[row-1][col-1]
cell.value = value
wb.save(file_path)
if __name__ == '__main__':
demo_datas = [
['Number', 'Batch', 'ID'],
[2, 'A', '001'],
[4, 'B', '002'],
[6, 'A', '003'],
[8, 'C', '004']
]
file_path = 'example1.xlsx'
# 向下添加
datas_2_excel_xlsx_openpyxl(demo_datas, file_path,flag='row')
# 向右添加
datas_2_excel_xlsx_openpyxl(demo_datas, file_path,flag='col')
2.2 pandas
使用 pandas 结合 openpyxl(对于 .xlsx 文件)或 xlrd(对于 .xls 文件)
1️⃣读取 Excel 数据
import pandas as pd
def read_xlsx_pandas_openpyxl(file_path):
# 使用 pandas 读取 Excel 文件
df = pd.read_excel(file_path, engine='openpyxl')
# 将DataFrame输出为列表
list_of_lists = df.values.tolist()
return df
if __name__ == '__main__':
file_path = 'example1.xlsx'
df = read_xlsx_pandas_openpyxl(file_path)
# 显示数据
print(df)
2️⃣新建 Excel 写入数据
import pandas as pd
def datas_2_excel_xlsx_openpyxl_pandas(datas,file_path):
# 将列表转换为Pandas DataFrame
df = pd.DataFrame(datas)
# 使用to_excel方法将DataFrame输出到Excel文件
# index参数决定是否输出行索引,默认为True 表头header=False
# engine参数指定使用的写入引擎,这里使用'openpyxl'
df.to_excel(file_path, index=False,header=False, engine='openpyxl' )
if __name__ == '__main__':
demo_datas = [
['Number', 'Batch', 'ID'],
[2, 'A', '001'],
[4, 'B', '002'],
[6, 'A', '003'],
[8, 'C', '004']
]
file_path = 'example2.xlsx'
datas_2_excel_xlsx_openpyxl_pandas(demo_datas, file_path )
获取时间,主要起名字用
import time
def getDateAndTime( ):
# 获得时间
dateandtime = time.strftime("%Y-%m-%d", time.localtime())
return dateandtime
获取网络时间
import ntplib
import datetime
def getDateAndTime( ):
# 创建NTP客户端
client = ntplib.NTPClient()
# 从时间服务器获取时间
response = client.request('pool.ntp.org')
# 将NTP时间戳转换为datetime对象
ntp_time = datetime.datetime.fromtimestamp(response.tx_time)
# 将时间转换为字符串
ntp_time_str = ntp_time.strftime('%Y-%m-%d %H:%M:%S')
return ntp_time_str