How to skip a cell when pasting content into an Excel by using Openpyxl?

I'm trying paste texts into a column of an existing excel. However, I need to skip all the highlighted cells. For example:

content.txt
1
2
3
4
5

workbook.xlsx should look like

Below are the codes I have so far:

import os, openpyxl
f = open("content.txt", mode="r+")
wb = openpyxl.load_workbook(filename = 'workbook.xlsx')
worksheet = wb.active

r = 1
for line in f.readlines():
worksheet.cell(row=r, column=1).value = line
r += 1
f.close()


But I'm not sure how to skip the highlighted cell..Anyone could figure it out? Thank you very much!!

2 Answers
2

Add an if statement inside your for loop

if

for

r = 1
for line in f.readlines():
if worksheet.cell(row=r, column=1).fill.bgColor.value != '00000000':
# '00000000' corresponds to no fill
continue
worksheet.cell(row=r, column=1).value = line
r += 1
f.close()


Also the indent is missing for r+=1 in the code you've posted here.

r+=1

Sorry, this is not what I'm looking for. I'm actually dealing with a more complicated excel and won't know exactly which row has the highlight.
– Penny
Jun 30 at 4:11

Alright, It was not clear that a random row could be highlighted within the excel input sheet. Could you please tell me if the highlight color remains the same.? Green in your case.?
– Ash Sharma
Jun 30 at 4:22

I have updated the answer accordingly.
– Ash Sharma
Jun 30 at 4:47

HI @Ash Sharma, thank you for updating your answer! I didn't try it until today. But after test the code, it still didn't work as expected. The content stopped to be pasted at the highlighted cell. (screenshot: i.prntscr.com/gdFoJ4vWTCyjZrVE09dr4Q.png)
– Penny
yesterday

Here's the updated code, by the way: i.prntscr.com/hdIMLI8KRVC8OdzcLsX9jA.png
– Penny
yesterday

OK I got the answer with the help of @Ash Sharma!

This code works for me:

import os, openpyxl
f = open("content.txt", mode="r+")
wb = openpyxl.load_workbook(filename = 'workbook.xlsx')
worksheet = wb.active

r = 1
for line in f.readlines():
if worksheet.cell(row=r, column=1).fill.bgColor.value != '00000000':
# '00000000' corresponds to no fill
r+=1
worksheet.cell(row=r, column=1).value = line
r+=1
continue
else:
worksheet.cell(row=r, column=1).value = line
r += 1

f.close()

wb.save('test_Result.xlsx')


By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.