Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Change excel cell number format "General" to "Text", using openpyxl 1.8.6

Tags:

openpyxl

How to change excel cell number format General to Text irrespective of data in cell?

I am using openpyxl 1.8.6.

like image 588
Monty Avatar asked Apr 23 '15 20:04

Monty


People also ask

How do I style a cell in Openpyxl?

To change a style property of a cell, first you either have to copy the existing style object from the cell and change the value of the property or you have to create a new style object with the desired settings. Then, assign the new style object to the cell.

How do I get cell value using Openpyxl?

Program to read cell value using openpyxl Library in Python Step1: Import the openpyxl library to the Python program. Step2: Load/Connect the Excel workbook to the program. Step3: Get the title of the default first worksheet of the Workbook. Step4: Create variables and initialize them with the cell names.


3 Answers

I know this question is really old, but it could still be relevant as I just found it when Googling the same thing. The above will work most of the time, but not in those annoying cases where Excel starts acting like guys I've known in the past, and thinking everything is a date. ;)

I would do it like this:

cell = ws['A1']
cell.number_format = '@'

The '@' is a placeholder that forces text formatting. The docs suggest this works on version 1.8.6, as well as latest release.

like image 178
SuperScienceGrl Avatar answered Sep 22 '22 00:09

SuperScienceGrl


Based on @SuperScienceGrl's great answer:

from openpyxl.styles import numbers

cell.number_format = numbers.FORMAT_TEXT

You can see a full list of format at their official readthedocs page

like image 29
run_the_race Avatar answered Sep 23 '22 00:09

run_the_race


Number formatting applies only to numbers. If you want to change a number to text then you must change the datatype:

ws['A1'] = str(ws['A1'].value)

Version 1.8.6 is no longer supported. You should consider upgrading to a more recent release.

like image 44
Charlie Clark Avatar answered Sep 20 '22 00:09

Charlie Clark