Re: [openpyxl-users] import spreadsheet integer vs character

[Charlie Clark]

On 5 Aug 2021, at 3:26, Rockie Brockway wrote:

If I import a spreadsheet through openpyxl that is simply a matrix of 0s

and 1s, those values get treated as characters as opposed to integers, so

queries such as 'IF(A1=1,A,B)' fail because A1 is a character, not an

integer. Is there a simple way to import said spreadsheet and convert all

"character" numbers to actual integers so formulas work without additional

massaging?

This sounds like the values were somewhat carelessly imported.

There's no way to change all cells at once. You could import the sheet into Pandas which can change all the entries in a column at once and then go back to openpyxl but you're probably best off just looping over all the cells and converting them individually but you'll probably want to add some checking and exception handling.

for row in ws:
	for cell in row:
		try:
			cell.value = int(cell.value)
		except ValueError:
			print(f"Cannot convert {cell.coordinate} with value {cell.value}")

Charlie

--
Charlie Clark
Managing Director
Clark Consulting & Research
German Office
Waldlehne 23
Düsseldorf
D- 40489
Tel: +49-203-3925-0390
Mobile: +49-178-782-6226