Openpyxl: Code fails to write data from one Excel spreadsheet to another

  cell, excel, openpyxl, python, rows

I am trying to use Openpyxl to search for data in one Excel workbook, and write it to another (pre-existing) Excel workbook. The aim is something like this:

  1. Search in Workbook1 for rows containing the word "Sales" (there being several such rows)
  2. Copy the data from Column E of those rows (a numerical value), into a specific cell in Workbook2 (second worksheet, cell C3).

My code below appears to run without any errors, however, when I open Workbook 2, no data is being written in to it. Does anyone know why/can suggest a fix?

# importing openpyxl module
import openpyxl as xl
import sys

# opening the source excel file
filename ="C:UsershadamDocumentsTestingTB1.xlsx"
wb1 = xl.load_workbook(filename)
ws1 = wb1.worksheets[0]

# opening the destination excel file
filename1 = "C:UsershadamDocumentsTestingcomp2.xlsx"
wb2 = xl.load_workbook(filename1)
ws2 = wb2.worksheets[1]

for sheet in wb1.worksheets:
    for row in sheet.iter_rows():
        for cell in row:
            try:
                if 'Sale' in cell.value:
                    # reading cell value from source excel file
                    c = ws1.cell(row=cell.row, column=4).value

                    # writing the read value to destination excel file
                    ws2.cell(row=2, column=2).value = c.value
            
            except (AttributeError, TypeError):
                continue

            # saving the destination excel file
            wb2.save(str(filename1))
                
sys.exit()

Other info:

  • Specifically, the text string I am searching for (‘Sales’) is in Column A of Workbook1. It is not an exact match, but e.g. a given cell contains "5301 Sales – Domestic – type4". Therefore I want to sum the numerical values in Column E which contain "Sales" in Column A, into a single cell in Workbook2.
  • I am mega new to Python/coding. However, my environment seems to be set up okay, e.g. I have already tested a code (copied from elsewhere in the Web) that can write all the data from one spreadsheet into another pre-existing spreadsheet). I am using Mu editor in Python 3 mode and openpyxl module.

Source: Python Questions

LEAVE A COMMENT