用应用的数字格式读取单元格值openpyxl？ - 减速 [英] Reading cell value with applied number format openpyxl? - redux

问题描述

Python 2.7，openpyxl 2.4.x

Python 2.7, openpyxl 2.4.x

我正在尝试读取Excel中的日期值（05/01/2016）的单元格，已应用自定义数字格式（yyyymm），因此显示为201605。我想要结束的是应用自定义数字格式后的值（即用户在excel（201605）中看到的值）。任何想法如何使用openpyxl？

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". What I want to end up with is the value after the custom number formatting is applied (i.e. what the user sees in excel ("201605")). Any idea how to do this using openpyxl?

注意：我不能简单地将日期重新格式化为yyyymm，因为用户可能会更改自定义格式，我需要任何最终结果在定制后格式被应用。那么也许Openpyxl中有一种方法可以在读取的单元格中应用自定义格式？

Note: I can't just simply take the date and reformat it to yyyymm as the user might change the custom formatting and I need whatever the end result is after the custom format is applied. So maybe there's a way in openpyxl to apply the custom formatting in cells that are read?

我意识到这与这篇文章非常相似（相似的问题）但我不太清楚如何在我的情况下应用它

I realize this is very similar to this post (similar question) but I wasn't quite sure how to apply it in my circumstance

提前感谢帮助。

推荐答案

没有实现。
您正在寻找的是cell.number_format。
尝试以下操作：

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

在您需要的时候formed cell.value do：

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) )

的 使用Python测试：3.4.2 - openpyxl：2.4.1 - LibreOffice：4.3.3.2

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

这篇关于用应用的数字格式读取单元格值openpyxl？ - 减速的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！