wordpress hit counter
Date cells Retrieve value - .Net - Development Tools - OpenXML Developer

Date cells Retrieve value

Development Tools

Discussions about working with Open XML using a wide range of development tools

Date cells Retrieve value

  • rated by 0 users
  • This post has 1 Reply |
  • 2 Followers
  • 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);
    

Page 1 of 1 (2 items)