CODEWITHSUNDEEP
python
Maharajaparaman
Maharajaparaman

Reputation: 141

deleting records from an excel based on the format of the column

Hope everyone's having a great day!

I am trying to read data from an excel but before that i want to remove records that are struckthrough(strikethrough :-string format just like bold or italic) and replace the data with null/space. is there any package /code that can help me find the format of a particular cell and delete the value of it.

Expecting your help.

Thanks, Prashanth

Upvotes: 1

Views: 1087

Answers (2)

GordonAitchJay
GordonAitchJay

Reputation: 4860

This can also be done with openpyxl.

from openpyxl import load_workbook


wb = load_workbook("strikethrough.xlsx")
ws = wb.active # wb.sheetnames[0] will do, too
for row in ws:
    for cell in row:
        value = cell.value # Note, empty cells have a value of None
        if cell.font.strike:
            value = ""
        print(value)
        #print(cell.font) # See more attributes

wb.close()

With this as my spreadsheet:

Small spreadsheet with strikethroughs

I get this output:

HeaderA
HeaderB
Row2 ColA

Row3 ColA
Row3 ColB


None
None
Above cell is empty
None

Upvotes: 3

Guillermo Kolmerer
Guillermo Kolmerer

Reputation: 1

  1. open the workbook passing the parameter formatting_info=True.
  2. Get the XF object of the cells and get the Font object.
  3. You will find your value in the attribute The struck_out
workbook = xlrd.open_workbook(filename, formatting_info=True)
sh = workbook.sheet_by_name(sheet)
xf = workbook.xf_list[sh.cell_xf_index(row, col)]
font = workbook.font_list[xf.font_index]
if font.struck_out:
    print(row, col)

Upvotes: 0

Related Questions