从Excel电子表格计算三角洲数字

Question

我想从Excel电子表格中计算三角洲号码。现在，我只能计算一行的增量数字。我想自动化计算增量数字的过程。这是我的代码：

import os
os.chdir("/Users/user/Desktop")
import openpyxl
wb = openpyxl.load_workbook('lotto.xlsx')
sheet = wb.active
def delta():
    a = sheet['A2'].value
    b = sheet['B2'].value - sheet['A2'].value
    c = sheet['C2'].value - sheet['B2'].value
    d = sheet['D2'].value - sheet['C2'].value
    e = sheet['E2'].value - sheet['D2'].value
    f = sheet['F2'].value - sheet['E2'].value
    return [a, b, c, d, e, f]
delt = delta()
delt.sort()
if delt[-1] < 16:
    print("The delta numbers are: " + str(delt))
else:
    print("These are not delta numbers.")

这是Excel电子表格：

http://ge.tt/3lzenrw2

Answer 1

您可以在所有行上迭代：

import openpyxl
def delta(i):
    a = sheet[f'A{i}'].value
    b = sheet[f'B{i}'].value - sheet[f'A{i}'].value
    c = sheet[f'C{i}'].value - sheet[f'B{i}'].value
    d = sheet[f'D{i}'].value - sheet[f'C{i}'].value
    e = sheet[f'E{i}'].value - sheet[f'D{i}'].value
    f = sheet[f'F{i}'].value - sheet[f'E{i}'].value
    return sorted([a, b, c, d, e, f])
wb = openpyxl.load_workbook('lotto.xlsx')
sheet = wb.active
for row in range(1, sheet.max_row+1, 1):
    d = delta(row)
    if d[-1] < 16:
        print(f'{d[0]}-{d[1]}-{d[2]}-{d[3]}-{d[4]}-{d[5]}')
    else:
        print('--------------------')

输出：

--------------------
1-2-3-7-9-10
3-4-6-7-9-15

等。

Answer 2

操纵excel表中值的更好方法是使用熊猫数据框架。使用熊猫数据框架打开表格，然后简单地从一个列减去另一列。

这是代码 -

import pandas as pd
# Assign spreadsheet filename to `file`
file = "/Users/user/Desktop/lotto.xlsx"
# Load spreadsheet
#If you get an error "ModuleNotFoundError: No module named 'xlrd'" then install the 'xlrd' package by pip install xlrd
xl = pd.ExcelFile(file)
# Print the sheet names
print(xl.sheet_names)
# Load a sheet into a DataFrame by name: df
df = xl.parse('Sheet1',header=None)
#Example for delta calculation of cloumn 0 from column 1
diff=df[1]-df[0]