Reading Cell Value With Applied Number Format Openpyxl? - Redux

Python 2.7, openpyxl 2.4.x I'm trying to read a cell that is a date value ('05/01/2016') in excel and has custom number formatting applied ('yyyymm') so it displays as '201605'. Wh

Solution 1:

It's not implemented. What you are looking for is cell.number_format. Try the following:

def pyDateFormat(nf):
  import re
  """Convert xlsx cell.number_format to Python date Format."""
  #xlsx Format   = 'YYYYMMDD', 'YYYY\\-MM\\-DD'
  #Python Format = '%Y%m%d'
  nf = re.sub(r'\\',r'',nf,0,re.IGNORECASE) #Remove Escapes
  nf = re.sub(r'([^Y]*)(Y+)(.*)',r'\1%Y\3',nf,0,re.IGNORECASE) #Change YYYY to %Y
  nf = re.sub(r'([^M]*)(M+)(.*)',r'\1%m\3',nf,0,re.IGNORECASE) #Change MM to %m
  nf = re.sub(r'([^D]*)(D+)(.*)',r'\1%d\3',nf,0,re.IGNORECASE) #Change DD to %d
  return nf
#end def pyDateFormat

At the point you need the formated cell.value do:

  cell = wb.ws['A1']
  if cell.is_date:
    value = cell.value.date().__format__( pyDateFormat(cell.number_format) )

Tested with Python: 3.4.2 - openpyxl: 2.4.1 - LibreOffice: 4.3.3.2