Excel - Python

[Pedroski55]

Oh dear. Works great for me in Idle. I never use PyCharm. I tried it, but it has too many extras for me!

Start Idle. Idle comes with Python.

Just run every line 1 by 1, (except for the for-loops), you will find the problem. Probably a path problem.

I often put all the code I am trying in a function called myApp(), then just run myApp()

def myApp():
    import openpyxl
    import re

    # open the XL
    path2XL = '/home/pedro/myPython/openpyxl/xlsx_files/get_data1.xlsx'
    sourceFile = openpyxl.load_workbook(path2XL)
    # always good to get the sheet names, you can loop through them if you need to
    sourceFilesheets = sourceFile.sheetnames
    mysheet = 'Standard'
    # you often need these 2 to loop along or down an XL
    maxRow = sourceFile[mysheet].max_row
    maxCol = sourceFile[mysheet].max_column
    # if your column is H, that is column 8
    # openpyxl does have a "get column number from letter" function, but H is column 8
    # I assume your data starts in row 2 change that if needed
    # loop through all the rows and collect your data
    # mystring = "this_sample_name_is_really_long_and_inconvenient_std01_date_name_information 300"
    results = []
    for rowNum in range(2, maxRow + 1):
        mystring = sourceFile[mysheet].cell(row=rowNum, column=8).value
        # split the string on space, the number you want is at the end, separated by a space I think!
        mydata = mystring.split()
        # re.search only returns 1 instance, I believe
        mystd = re.search(r'std\d\d', mydata[0])
        mynumber = int(mydata[1])
        mytup = ('Row ' + str(rowNum), mystd[0], mynumber)
        results.append(mytup)

    for data in results:
        print(data)

    print('Gotcha!')

That gives this:

Output:
myApp()
('Row 2', 'std01', 300)
('Row 3', 'std02', 600)
('Row 4', 'std03', 1200)
('Row 5', 'std04', 2400)
('Row 6', 'std05', 4800)
('Row 7', 'std06', 9600)
('Row 8', 'std07', 19200)
('Row 9', 'std08', 38400)
('Row 10', 'std01', 302)
('Row 11', 'std02', 602)
('Row 12', 'std03', 1202)
('Row 13', 'std04', 2402)
('Row 14', 'std05', 4802)
('Row 15', 'std06', 9602)
('Row 16', 'std07', 19202)
('Row 17', 'std08', 38402)
('Row 18', 'std01', 315)
('Row 19', 'std02', 615)
('Row 20', 'std03', 1215)
('Row 21', 'std04', 2415)
('Row 22', 'std05', 4815)
('Row 23', 'std06', 9615)
('Row 24', 'std07', 19215)
('Row 25', 'std08', 38415)
Gotcha!