实现代码
import openpyxl
# 打开Excel文件
input_file = 'input.xlsx'
output_file = 'input3.xlsx'
wb = openpyxl.load_workbook(input_file)
output_wb = openpyxl.Workbook()
# 处理每个工作表
for sheet_name in wb.sheetnames:
sheet = wb[sheet_name]
# 新建一个工作表，用于存储处理后的数据
output_sheet = output_wb.create_sheet(title=sheet_name)
# 处理数据
for row in range(1, sheet.max_row+1):
if row % 2 == 0:
# 计算上下两行的平均值
avg_values = []
for col in range(1, sheet.max_column+1):
avg_value = (sheet.cell(row=row-1, column=col).value + sheet.cell(row=row+1, column=col).value) / 2
avg_values.append(avg_value)
# 将平均值写入新行
output_sheet.append(avg_values)
else:
# 直接将原数据写入新行
row_values = []
for col in range(1, sheet.max_column+1):
row_values.append(sheet.cell(row=row, column=col).value)
output_sheet.append(row_values)
# 保存新Excel文件
output_wb.save(output_file)
补充:取出excel的奇数行
import openpyxl
# 打开Excel文件
input_file = 'input.xlsx'
output_file = 'output.xlsx'
wb = openpyxl.load_workbook(input_file)
output_wb = openpyxl.Workbook()
# 选择需要处理的sheet
sheet = wb.active
# 选择需要提取的行数
rows_to_extract = []
for i in range(1, sheet.max_row+1):
if i % 2 == 1: # 只提取奇数行
rows_to_extract.append(i)
# 处理数据
output_sheet = output_wb.active
for row_num, row in enumerate(sheet.iter_rows(values_only=True), start=1):
if row_num in rows_to_extract:
output_sheet.append(row)
# 保存新Excel文件
output_wb.save(output_file)
Excel测试数据
0.00 0.00 4887.00 -424.88 0.00 4856.45 -132.02 -406.33 4883.35 346.70 -251.89 4898.29 344.51 250.30 4867.41 -131.19 403.76 4852.46 -690.76 501.87 4842.30 -263.15 809.89 4829.50 263.20 810.06 4830.48 689.22 500.74 4831.47 863.90 0.00 4899.42 704.53 -511.87 4938.81 270.88 -833.67 4971.31 -267.17 -822.28 4903.36 -700.74 -509.11 4912.22 -859.21 0.00 4872.83 -1151.06 0.00 4616.67 -1023.25 -590.77 4738.92 -613.27 -1062.22 4919.40 0.00 -1242.51 4983.44 620.89 -1075.42 4980.53 1071.86 -618.84 4964.03 1223.88 0.00 4908.73 1058.03 610.85 4899.99 541.54 937.98 4344.01 0.00 1046.80 4198.47 -595.97 1032.26 4780.65 -1033.52 596.70 4786.47 -1435.50 639.13 4836.12 -1051.85 1168.20 4838.02 -332.41 1023.04 3310.63 94.67 900.77 2787.55 466.46 807.93 2871.24 844.75 613.75 3213.62 1539.73 327.28 4844.68 1415.81 -300.94 4454.75 1180.25 -857.50 4489.94 810.55 -1403.92 4989.24 168.81 -1606.08 4970.22 -488.63 -1503.85 4866.56 -1075.01 -1193.92 4944.54 -1446.79 -644.15 4874.16 -1471.54 0.00 4528.93 -1683.86 0.00 4167.69 -1612.23 -586.80 4246.50 -1490.21 -1250.44 4814.87 -970.23 -1680.49 4802.81 -342.68 -1943.45 4884.40 344.76 -1955.25 4914.07 983.72 -1703.85 4869.57 1287.61 -1080.44 4160.27 1428.48 -519.92 3762.51 1577.84 0.00 3905.30 1578.08 574.38 4156.57 913.70 766.68 2952.15 503.66 872.36 2493.20 168.22 954.02 2397.70 -174.98 992.38 2494.12 -594.31 1029.38 2941.95 -1470.70 1234.06 4751.82 -1568.93 571.04 4132.46 -1782.31 579.11 3842.34 -1483.85 1078.08 3760.55 -924.00 1271.77 3223.08 -353.02 1086.48 2342.26 0.00 988.09 2025.88 296.94 913.88 1970.16 617.63 850.10 2154.41 936.98 680.76 2374.61 1683.09 546.87 3628.43 1597.42 0.00 3275.21 1515.07 -492.28 3266.22 1367.88 -993.83 3466.65 1195.84 -1645.93 4171.30 744.38 -2290.95 4938.87 0.00 -2423.32 4968.53 -745.73 -2295.12 4947.86 -1396.82 -1922.56 4872.36 -1920.07 -1395.02 4866.07 -1822.34 -592.11 3928.63 -1801.71 0.00 3694.04 -1634.50 0.00 2831.04 -1786.48 -478.69 3203.43 -1960.25 -1131.75 3920.50 -1983.08 -1983.08 4857.54 -1421.25 -2461.68 4923.35 -729.61 -2722.94 4882.65 0.00 -2829.00 4899.97 748.38 -2792.97 5008.22 1119.00 -1938.16 3876.33 1280.92 -1280.92 3137.61 1513.38 -873.75 3026.76 1576.87 -422.52 2827.57 1625.50 0.00 2815.45 1655.60 443.62 2968.74 1316.36 760.00 2632.72 826.61 826.61 2024.77 543.00 940.50 1881.01 259.34 967.86 1735.51 0.00 1011.00 1751.10 -295.83 1104.05 1979.73 -621.75 1076.90 2153.81 -1184.76 1184.76 2902.05 -1492.59 861.75 2985.19 -1695.68 454.36 3040.62 -1654.86 736.79 2789.42 -1449.21 1052.91 2758.39 -1188.42 1319.88 2734.90 -675.35 1169.74 2079.90 -343.17 1056.17 1710.05 -114.20 1086.56 1682.37 108.74 1034.56 1601.86 312.20 960.86 1555.73 514.96 891.93 1585.93 731.78 812.73 1684.05 998.45 725.42 1900.43 1477.23 657.71 2490.01 1729.80 367.68 2723.16 1673.68 0.00 2577.23 1625.91 -345.60 2559.62 1530.97 -681.63 2580.59 1379.15 -1002.01 2625.04 1303.58 -1447.78 2999.92 1159.26 -2007.90 3570.22 895.03 -2754.63 4460.05 336.80 -3204.43 4961.58 -332.99 -3168.14 4905.38 -986.93 -3037.45 4917.96 -1544.05 -2674.38 4755.26 -2117.37 -2351.57 4872.68 -2044.93 -1485.73 3892.27 -2263.37 -1007.72 3815.11 -1779.88 -378.32 2802.00 -1646.44 0.00 2535.30 -1685.08 0.00 2236.18 -1727.05 -304.52 2327.22 -1801.18 -655.58 2543.65 -2059.73 -1189.19 3156.21 -2029.40 -1702.87 3515.59 -2126.46 -2534.21 4390.10 -1861.11 -3223.54 4939.56 -1270.40 -3490.40 4929.18 -653.46 -3705.98 4993.87 0.00 -3744.49 4969.11 640.82 -3634.27 4897.23 978.74 -2689.05 3797.51 1228.91 -2128.53 3261.63 1322.60 -1576.22 2730.53 1428.69 -1198.81 2474.97 1546.36 -892.79 2369.55 1620.22 -589.71 2288.09 1635.18 -288.33 2203.44 1689.90 0.00 2242.57 1772.68 312.57 2388.72 1727.67 628.82 2439.83 1336.84 771.83 2048.50 1013.32 850.27 1755.40 734.61 875.47 1516.61 526.29 911.56 1396.81 356.09 978.35 1381.64 179.96 1020.58 1375.25 0.00 1036.93 1376.05 -189.26 1073.33 1446.33 -407.55 1119.73 1581.30 -626.49 1085.11 1662.76 -963.62 1148.39 1989.40 -1403.80 1177.93 2431.85 -1548.45 894.00 2372.75 -1498.07 545.25 2115.59
