1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58

# Return a set of row values within a specified row range
# for a specific column on a sheet within an excel file.

import openpyxl
from openpyxl import load_workbook

# xlsPath is the path to an excel file (e.g. 'D:\Data\myfile.xlsx'
# sheetName must be the actual sheet name (e.g. Sheet2).
# columnName must be an uppercase alphabetical excel column designator (e.g. 'A')
# rowStart and rowEnd are included in the result set to return.
def getColumnCellValues(excelFilePath, sheetName, columnName, rowStart, rowEnd):

    # load excel with its path
    workBook = openpyxl.load_workbook(excelFilePath)  # open excel workbook.

    # get index of specified sheet and set that sheet as active:
    activeSheet = workBook.get_sheet_by_name(sheetName)

    # read row values in specified colName:
    rowIndex = 0
    valueArray = []
    for cell in activeSheet[columnName]:
        rowIndex += 1
        if rowIndex < rowStart:  # skip rows before rowStart.
            continue
        if rowIndex > rowEnd:  # skip rows after rowEnd.
            continue
        valueArray.append(cell.value)   # add to array.

    # return values:
    return valueArray

# xlsPath is the path to an excel file (e.g. 'D:\Data\myfile.xlsx'
# sheetName must be the actual sheet name (e.g. Sheet2).
# columnName must be an uppercase alphabetical excel column designator (e.g. 'A')
# rowStart is the row to start writing cellValues.
# cellValues are the values to write to the column cells starting at rowStart.
def setColumnCellValues(excelFilePath, sheetName, columnName, rowStart, cellValues):

    # load excel with its path
    workBook = openpyxl.load_workbook(excelFilePath)  # open excel workbook.

    # get index of specified sheet and set that sheet as active:
    activeSheet = workBook.get_sheet_by_name(sheetName)

    # read row values in specified colName:
    rowIndex = 0
    writeIndex = 0  # track value index in cellValues list.
    for cell in activeSheet[columnName]:
        rowIndex += 1
        if rowIndex < rowStart:  # skip rows before rowStart.
            continue
        cell.value = cellValues[writeIndex]   # write to cell.
        writeIndex += 1
        if writeIndex > len(cellValues) - 1:
            break
    #save excel file:
    workBook.save(excelFilePath)

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21

import excelLib

# read cell values from column E starting at row 10 and ending on row 15 (6 values):
columnCellValues = excelLib.getColumnCellValues(
    xlsPath='D:\\data\\myfile.xlsx', 
    sheetName='mysheet', 
    columnName='E', 
    rowStart=10, 
    rowEnd=15)

# print the 6 values read:
for cellValue in columnCellValues:
    print(cellValue)

# write cell values into column G starting at row 10:
excelLib.setColumnCellValues(
    xlsPath='D:\\data\\myfile.xlsx', 
    sheetName='mysheet', 
    columnName='F', 
    rowStart=10, 
    cellValues=[1,2,3,4,5,6])