C# 使用 open xml sdk 从 xlsx 读取日期
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/13176832/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Reading a date from xlsx using open xml sdk
提问by Santhos
I have a date in format "4/5/2011" (month/day/year) in a xlsx file in one of the cells. Im trying to parse the file and load those data in some classes.
我在其中一个单元格的 xlsx 文件中有一个格式为“4/5/2011”（月/日/年）的日期。我试图解析文件并将这些数据加载到某些类中。
So far the part where I parse the cell looks like this:
到目前为止，我解析单元格的部分如下所示：
string cellValue = cell.InnerText;
if (cell.DataType != null)
{
switch (cell.DataType.Value)
{
case CellValues.SharedString:
// get string from shared string table
cellValue = this.GetStringFromSharedStringTable(int.Parse(cellValue));
break;
}
}
I hoped that date would be a cell.DataType. The truth is when parsing the cell with the date "4/5/2011", the value of cell.DataType is null and the value of the cell is "40638" and it is not an index to the shared string table. (I have tried that before and it ended up with an exception.)
我希望那个日期是一个 cell.DataType。事实是，当解析日期为“4/5/2011”的单元格时，cell.DataType 的值为空，单元格的值为“40638”，它不是共享字符串表的索引。（我之前尝试过，但最终出现了异常。）
Any ideas? Thanks
有任何想法吗？谢谢
采纳答案by Vincent Tan
Open XML stores dates as the number of days from 1 Jan 1900. Well, skipping the incorrect 29 Feb 1900 as a valid day. You should be able to find out algorithms to help you calculate the correct value. I believe some developers use DateTime.FromOADate()as a helper.
Open XML 将日期存储为从 1900 年 1 月 1 日起的天数。好吧，跳过不正确的 1900 年 2 月 29 日作为有效日期。您应该能够找出算法来帮助您计算正确的值。相信有的开发者DateTime.FromOADate()作为帮手使用。
Also, the Cellclass has the DataTypeproperty as Number by default. So if it's null, it's a number, which includes dates in our case.
此外，Cell该类的DataType属性默认为 Number。所以如果它是空的，它是一个数字，在我们的例子中包括日期。
You only go to the shared strings table when the date stored is before the epoch (1 Jan 1900 in this case). And then in that case, the CellValue of the Cell class holds the index to the shared string table.
只有当存储的日期在纪元之前（在这种情况下为 1900 年 1 月 1 日）时，您才转到共享字符串表。然后在这种情况下，Cell 类的 CellValue 保存共享字符串表的索引。
回答by CKmum
I had same issue - switched to EPPlus http://epplus.codeplex.com/
我有同样的问题 - 切换到 EPPlus http://epplus.codeplex.com/
Note that it has LGPL license. So if you need your code base to be safe from GPL issue, simply use the library as is and your original code base license is safe.
请注意，它具有 LGPL 许可证。因此，如果您需要您的代码库免受 GPL 问题的影响，只需按原样使用该库，您的原始代码库许可证是安全的。
回答by mzoabi
you can use DateTime.FromOADate(41690)
你可以使用 DateTime.FromOADate(41690)
回答by Thierry_S
Adding my 2 pence worth. I am processing a template, so I know a given cell is meant to be a DateTime. So I end up in this method with a string parameter excelDateTime containing the cell value, which typically will be a OADate number like "42540.041666666664".
加上我的 2 便士价值。我正在处理一个模板，所以我知道给定的单元格是一个 DateTime。所以我最终在这个方法中使用了一个包含单元格值的字符串参数 excelDateTime，它通常是一个 OADate 数字，如“42540.041666666664”。
public static bool TryParseExcelDateTime(string excelDateTimeAsString, out DateTime dateTime)
{
double oaDateAsDouble;
if (!double.TryParse(excelDateTimeAsString, out oaDateAsDouble)) //this line is Culture dependent!
return false;
//[...]
dateTime = DateTime.FromOADate(oaDateAsDouble);
My problem is that the end user is in Germany, and because this is a website, we've set the Thread.CurrentThread.CurrentCulture and Thread.CurrentThread.CurrentUICulture to "DE-de". And when you call double.TryParse, it uses the culture to parse the number. So this line: double.TryParse("42540.041666666664", out oaDate)does indeed work, but it returns 42540041666666664as in Germany the dot is a group separator. DateTime.FromOADatethen fails because the number is out of range (minOaDate = -657435.0, maxOaDate = +2958465.99999999).
我的问题是最终用户在德国，因为这是一个网站，我们将 Thread.CurrentThread.CurrentCulture 和 Thread.CurrentThread.CurrentUICulture 设置为“DE-de”。当您调用 时double.TryParse，它会使用文化来解析数字。所以这一行：double.TryParse("42540.041666666664", out oaDate)确实有效，但它返回42540041666666664德国，点是组分隔符。DateTime.FromOADate然后失败，因为数字超出范围（minOaDate = -657435.0, maxOaDate = +2958465.99999999）。
This make me think that:
这让我觉得：
- regardless of the locale on a user's machine, the OpenXML document contains numbers formatted in a default locale (US? invariant? in any case, with the dot as a decimal separator). I've searched, but not found the spec for this.
- when doing
double.TryParseon a potential OADate string, we should do it withdouble.TryParse(excelDateTimeAsString, NumberStyles.Any, CultureInfo.InvariantCulture, out oaDateAsDouble)). I'm using CultureInfo.InvariantCulture, but it should be whatever point 1 is, which I don't know for sure.
- 无论用户机器上的语言环境如何，OpenXML 文档都包含以默认语言环境（美国？不变？在任何情况下，用点作为小数点分隔符）格式化的数字。我已经搜索过，但没有找到这个规范。
- 在
double.TryParse处理潜在的 OADate 字符串时，我们应该使用double.TryParse(excelDateTimeAsString, NumberStyles.Any, CultureInfo.InvariantCulture, out oaDateAsDouble)). 我正在使用 CultureInfo.InvariantCulture，但它应该是第 1 点，我不确定。
回答by Israel Cortés
Each cell has 2 properties r (CellReference) and s(StyleIndex)
每个单元格有 2 个属性 r (CellReference) 和 s(StyleIndex)
StyleIndex for numbers is 2 and for date is 3
数字的 StyleIndex 为 2，日期的 StyleIndex 为 3
Date it is in ODate and you can convert to string format
日期在 ODate 中，您可以转换为字符串格式
value = DateTime.FromOADate(double.Parse(value)).ToShortDateString();
value = DateTime.FromOADate(double.Parse(value)).ToShortDateString();
回答by Philip Johnson
It appears that the cell.DataType is not set for dates.
似乎没有为日期设置 cell.DataType。
The way to do it is to see if the cell has a StyleIndex, which is an index into an array of cell formats in the document.
这样做的方法是查看单元格是否有 StyleIndex，它是文档中单元格格式数组的索引。
You then use the cellFormat.NumberFormatId to see if this is a date data type.
然后使用 cellFormat.NumberFormatId 来查看这是否是日期数据类型。
Here is some code:
这是一些代码：
public class ExcelCellWithType
{
public string Value { get; set; }
public UInt32Value ExcelCellFormat { get; set; }
public bool IsDateTimeType { get; set; }
}
public class ExcelDocumentData
{
public ExcelXmlStatus Status { get; set; }
public IList<Sheet> Sheets { get; set; }
public IList<ExcelSheetData> SheetData { get; set; }
public ExcelDocumentData()
{
Status = new ExcelXmlStatus();
Sheets = new List<Sheet>();
SheetData = new List<ExcelSheetData>();
}
}
...
public ExcelDocumentData ReadSpreadSheetDocument(SpreadsheetDocument mySpreadsheet, ExcelDocumentData data)
{
var workbookPart = mySpreadsheet.WorkbookPart;
data.Sheets = workbookPart.Workbook.Descendants<Sheet>().ToList();
foreach (var sheet in data.Sheets)
{
var sheetData = new ExcelSheetData { SheetName = sheet.Name };
var workSheet = ((WorksheetPart)workbookPart.GetPartById(sheet.Id)).Worksheet;
sheetData.ColumnConfigurations = workSheet.Descendants<Columns>().FirstOrDefault();
var rows = workSheet.Elements<SheetData>().First().Elements<Row>().ToList();
if (rows.Count > 1)
{
foreach (var row in rows)
{
var dataRow = new List<ExcelCellWithType>();
var cellEnumerator = GetExcelCellEnumerator(row);
while (cellEnumerator.MoveNext())
{
var cell = cellEnumerator.Current;
var cellWithType = ReadExcelCell(cell, workbookPart);
dataRow.Add(cellWithType);
}
sheetData.DataRows.Add(dataRow);
}
}
data.SheetData.Add(sheetData);
}
return data;
}
...
private ExcelCellWithType ReadExcelCell(Cell cell, WorkbookPart workbookPart)
{
var cellValue = cell.CellValue;
var text = (cellValue == null) ? cell.InnerText : cellValue.Text;
if (cell.DataType?.Value == CellValues.SharedString)
{
text = workbookPart.SharedStringTablePart.SharedStringTable
.Elements<SharedStringItem>().ElementAt(
Convert.ToInt32(cell.CellValue.Text)).InnerText;
}
var cellText = (text ?? string.Empty).Trim();
var cellWithType = new ExcelCellWithType();
if (cell.StyleIndex != null)
{
var cellFormat = workbookPart.WorkbookStylesPart.Stylesheet.CellFormats.ChildElements[
int.Parse(cell.StyleIndex.InnerText)] as CellFormat;
if (cellFormat != null)
{
cellWithType.ExcelCellFormat = cellFormat.NumberFormatId;
var dateFormat = GetDateTimeFormat(cellFormat.NumberFormatId);
if (!string.IsNullOrEmpty(dateFormat))
{
cellWithType.IsDateTimeType = true;
if (!string.IsNullOrEmpty(cellText))
{
if (double.TryParse(cellText, out var cellDouble))
{
var theDate = DateTime.FromOADate(cellDouble);
cellText = theDate.ToString(dateFormat);
}
}
}
}
}
cellWithType.Value = cellText;
return cellWithType;
}
//// https://msdn.microsoft.com/en-GB/library/documentformat.openxml.spreadsheet.numberingformat(v=office.14).aspx
private readonly Dictionary<uint, string> DateFormatDictionary = new Dictionary<uint, string>()
{
[14] = "dd/MM/yyyy",
[15] = "d-MMM-yy",
[16] = "d-MMM",
[17] = "MMM-yy",
[18] = "h:mm AM/PM",
[19] = "h:mm:ss AM/PM",
[20] = "h:mm",
[21] = "h:mm:ss",
[22] = "M/d/yy h:mm",
[30] = "M/d/yy",
[34] = "yyyy-MM-dd",
[45] = "mm:ss",
[46] = "[h]:mm:ss",
[47] = "mmss.0",
[51] = "MM-dd",
[52] = "yyyy-MM-dd",
[53] = "yyyy-MM-dd",
[55] = "yyyy-MM-dd",
[56] = "yyyy-MM-dd",
[58] = "MM-dd",
[165] = "M/d/yy",
[166] = "dd MMMM yyyy",
[167] = "dd/MM/yyyy",
[168] = "dd/MM/yy",
[169] = "d.M.yy",
[170] = "yyyy-MM-dd",
[171] = "dd MMMM yyyy",
[172] = "d MMMM yyyy",
[173] = "M/d",
[174] = "M/d/yy",
[175] = "MM/dd/yy",
[176] = "d-MMM",
[177] = "d-MMM-yy",
[178] = "dd-MMM-yy",
[179] = "MMM-yy",
[180] = "MMMM-yy",
[181] = "MMMM d, yyyy",
[182] = "M/d/yy hh:mm t",
[183] = "M/d/y HH:mm",
[184] = "MMM",
[185] = "MMM-dd",
[186] = "M/d/yyyy",
[187] = "d-MMM-yyyy"
};
private string GetDateTimeFormat(UInt32Value numberFormatId)
{
return DateFormatDictionary.ContainsKey(numberFormatId) ? DateFormatDictionary[numberFormatId] : string.Empty;
}
