The following xml represents the cell that contains the date.
<c r="A4" s="64"> <v>41029</v> </c>
The issue is 41029 does not exist in the shared string. I understand that is the way it is supposed to work and that I need to retrieve the value from the cell and not the shared string table. But I can not find anywhere in the xml that the actual value exists. Any help on finding out how to get the actual date value would be great.
I use this right now to retrieve each cell
using System; using System.Collections.Generic; using System.Linq; using System.Xml.Linq; using System.Text; using DocumentFormat.OpenXml.Spreadsheet; using DocumentFormat.OpenXml.Packaging; namespace SnipITXML { class LoadWorkBook { public static List<WorkBookSheetRow> LoadWorkSheetRows(Worksheet worksheet, SharedStringTable sharedString, string name) { IEnumerable<Row> dataRows = from row in worksheet.Descendants<Row>() where row.RowIndex >= 1 select row; List<WorkBookSheetRow> _rowList = new List<WorkBookSheetRow>(); ///Loops thru each row of data in worksheet. /// foreach (Row row in dataRows) { List<WorkBookSheetRowsCell> _cellList = new List<WorkBookSheetRowsCell>(); WorkBookSheetRow _row = new WorkBookSheetRow(); ///Loop thru each row from worksheet and assigns retrieves each cell. and assigns it to a row. /// foreach (var cell in row.Descendants<Cell>()) { WorkBookSheetRowsCell _cell = new WorkBookSheetRowsCell(); _cell.Name = cell.CellReference.Value; _cell.CellValue1 = GetCellContents(sharedString, cell); _cell.CellStyle = cell.StyleIndex; _cellList.Add(_cell); } _row.Cells = _cellList; _rowList.Add(_row); } //Return populated list of customers. return _rowList; //result; } private static List<WorkBookSheetCellContent> GetCellContents(SharedStringTable sharedString, Cell cell) { WorkBookSheetRowsCell _cell = new WorkBookSheetRowsCell(); foreach( var c in cell.Descendants()) { if (cell.CellValue == null) { cell.CellValue = null; var result = cell.CellValue; return null; }else if(cell.DataType != CellValues.SharedString) { List<WorkBookSheetCellContent> newContent = new List<WorkBookSheetCellContent>(); WorkBookSheetCellContent content = new WorkBookSheetCellContent(); content.CellContent=c.InnerText; } else{ try { List<WorkBookSheetCellContent> completeCell = new List<WorkBookSheetCellContent>(); var contents = sharedString.ElementAt(int.Parse(c.InnerText)) as SharedStringItem; foreach(var element in contents.ChildElements) { var run = element as Run; if(run != null) { if(run.RunProperties != null) { var cellContent = new WorkBookSheetCellContent(); cellContent.CellContent = element.InnerText; foreach(var property in run.RunProperties) { if (property is Bold) { cellContent.Bold = true; } if (property is Italic) { cellContent.Italic = true; } if (property is Strike) { cellContent.Strikethrough = true; } if (property is Underline) { cellContent.SingleUnderline = true; } } completeCell.Add(cellContent); } } } return _cell.CellValue1 = completeCell; } catch(Exception exp) { return _cell.CellValue1 = null; } } } return null; } } }
41029 is the date. I did some quick tests and found that 1 is 1/1/1900, but that Excel also thinks that 1900 is a leap year even though it is not.The following code seems to work, unless the date is before 2/1/1900.
DateTime d = new DateTime(1899, 12, 30); d = d.AddDays(41029);