none
How to determine date format in excel worksheet

    Question

  • In Excel 2010, if I enter a date into a date formatted cell, it stores the format correctly in the sheet1.xml in the zip file.  When I paste a date string into the same field, the format stored in the zip file is numeric, not date.

    When I open the sheet in excel, it displays correctly.  When I attempt to import the sheet using openxml, my code thinks it is a numeric value instead of a date.

    This works correctly in Excel 2007.  Only workbooks saved in Excel 2010 exhibit this issue.

    When attempting to load data from a spreadsheet, how can I determine if it is a date?  How can I programatically access the styles?


    dave

    Monday, November 05, 2012 12:01 PM

Answers

  • Hi Dave,

    Thanks for posting in the MSDN Forum.

    Following Snippet will access the Format Code of "A1":

            private bool FormatAccess(SpreadsheetDocument SSD)
            {
                bool result = false;
                WorkbookPart WBP = SSD.WorkbookPart;
                Workbook WB = WBP.Workbook;
                Sheet S = WB.Descendants<Sheet>().Where(Sh => Sh.Name == "Sheet1")
                    .FirstOrDefault();
                WorksheetPart WSP = WBP.GetPartById(S.Id) as WorksheetPart;
                if (WSP != null)
                {
                    Worksheet WS = WSP.Worksheet;
                    SheetData SD = WS.Descendants<SheetData>().FirstOrDefault();
                    if (SD != null)
                    {
                        UInt32Value CSIndex = 0U;
                        Cell A1 = SD.Descendants<Cell>()
                            .Where(Cl => Cl.CellReference == "A1").FirstOrDefault();
                        if (A1 != null)
                        {
                            CSIndex = A1.StyleIndex;
                        }
                        WorkbookStylesPart WSlP = WBP.WorkbookStylesPart;
                        Stylesheet Sls = WSlP.Stylesheet;
                        CellFormats CFs = Sls.Descendants<CellFormats>()
                            .FirstOrDefault();
                        CellFormat CF = CFs.Descendants<CellFormat>()
                            .ToList()[(int)CSIndex.Value];
                        UInt32Value NIndex = 0U;
                        if (CF != null)
                        {
                            NIndex = CF.NumberFormatId;
                        }
                        NumberingFormats NFs = Sls.Descendants<NumberingFormats>()
                            .FirstOrDefault();
                        NumberingFormat NF = NFs.Descendants<NumberingFormat>()
                            .Where(X => X.NumberFormatId.Value == NIndex.Value)
                            .FirstOrDefault();
                        //Now we get Format code
                        string FormatCode = NF.FormatCode.Value;
                    }
                }
                return result;
            }

    I hope it can help you.

    Have a good day,

    Tom


    Tom Xu [MSFT]
    MSDN Community Support | Feedback to us

    • Marked as answer by Dave Alessi Tuesday, November 06, 2012 11:11 AM
    Tuesday, November 06, 2012 7:17 AM
    Moderator

All replies

  •  I don'g think this have anything to so with styles.  It is the format of the excel cells that is the issue.  An excel cell defaults to General format.  When you type data into the cell excel will attempt to recognize the format of the data and then automatically changes the format as required. 
    A date is double  with 1 = Jan 1, 1900.  A hour equals 1/24.  The best way in C# to determine if a cell is a date is to use DateTime.TryParse().  the excel format may not be correct because excel will often make mistakes.  If you type 10/12 in an excel cell, it wil convert this to Oct-12 when the cell is formated to General.

    jdweng

    Monday, November 05, 2012 2:19 PM
  • So the  only way in c# to determine what type of formatting is there, it to test the data?  Doesn't seem reliable.  A "1" could be Jan 1, 1900 or it could be "1".

    My code is converting the spreadsheet to a data table.  I try to figure out the column type based on the style setting the the spreadsheet.  In my case, using Excel 2010, the style for teh date column is numeric, not date.  This happens when I paste a string in the cell, but not when I enter it.  If the spreadsheet has a type=numeric, and it is really a date, how do I know what data type so set?  It seems that there should be a way to find out what format is assigned to the cell.  In Excel, when I look at the format specifier, it is "date".  How do it know?


    dave

    Monday, November 05, 2012 2:27 PM
  • Excel stores date and number exactly the same internally.  Only the "FORMAT" of the cell determines how the cell will be displayed.  If you type into an excel cell 11/5/2012 and then change the format to a number you will get 41218.00.

    jdweng

    Monday, November 05, 2012 2:32 PM
  • Yes, I understand that.  But if Excel can determine that it is a date, my code should be able to as well.  What I am asking, is how do I access the format using OpenXML, so that I can correctly interpret the information.

    dave

    Monday, November 05, 2012 2:58 PM
  • How are you opening the XML file?  Are you using an XML method or using the Excel Interop Library.  In either case you need to read the cell format which may be incorrect.  Let me know your open method and then I will provide the code.

    jdweng

    Monday, November 05, 2012 3:12 PM
  •       WorkbookPart workbookpart = spreadsheetDocument.WorkbookPart;
                Workbook wb = workbookpart.Workbook;
                WorksheetPart worksheetPart = workbookpart.WorksheetParts.First<WorksheetPart>();
                Worksheet ws = worksheetPart.Worksheet;
    
                DataTable data = new DataTable();
                Sheet theSheet = workbookpart.Workbook.Descendants<Sheet>().Where(s => s.Name == sheetName).FirstOrDefault();
                WorksheetPart sheetPart = (WorksheetPart)(workbookpart.GetPartById(theSheet.Id));
    
                SheetData sheetData = sheetPart.Worksheet.Elements<SheetData>().First();
    
    
    From here, I don;t know how to access the format.

    dave

    Monday, November 05, 2012 3:22 PM
  • Is there programatic access to the styles page?

    dave

    Monday, November 05, 2012 6:31 PM
  • The styles are fixed, but you can add additional custom styles.  The styles are the values that are used as the cells numberformat property.

    jdweng

    Tuesday, November 06, 2012 6:41 AM
  • Hi Dave,

    Thanks for posting in the MSDN Forum.

    Following Snippet will access the Format Code of "A1":

            private bool FormatAccess(SpreadsheetDocument SSD)
            {
                bool result = false;
                WorkbookPart WBP = SSD.WorkbookPart;
                Workbook WB = WBP.Workbook;
                Sheet S = WB.Descendants<Sheet>().Where(Sh => Sh.Name == "Sheet1")
                    .FirstOrDefault();
                WorksheetPart WSP = WBP.GetPartById(S.Id) as WorksheetPart;
                if (WSP != null)
                {
                    Worksheet WS = WSP.Worksheet;
                    SheetData SD = WS.Descendants<SheetData>().FirstOrDefault();
                    if (SD != null)
                    {
                        UInt32Value CSIndex = 0U;
                        Cell A1 = SD.Descendants<Cell>()
                            .Where(Cl => Cl.CellReference == "A1").FirstOrDefault();
                        if (A1 != null)
                        {
                            CSIndex = A1.StyleIndex;
                        }
                        WorkbookStylesPart WSlP = WBP.WorkbookStylesPart;
                        Stylesheet Sls = WSlP.Stylesheet;
                        CellFormats CFs = Sls.Descendants<CellFormats>()
                            .FirstOrDefault();
                        CellFormat CF = CFs.Descendants<CellFormat>()
                            .ToList()[(int)CSIndex.Value];
                        UInt32Value NIndex = 0U;
                        if (CF != null)
                        {
                            NIndex = CF.NumberFormatId;
                        }
                        NumberingFormats NFs = Sls.Descendants<NumberingFormats>()
                            .FirstOrDefault();
                        NumberingFormat NF = NFs.Descendants<NumberingFormat>()
                            .Where(X => X.NumberFormatId.Value == NIndex.Value)
                            .FirstOrDefault();
                        //Now we get Format code
                        string FormatCode = NF.FormatCode.Value;
                    }
                }
                return result;
            }

    I hope it can help you.

    Have a good day,

    Tom


    Tom Xu [MSFT]
    MSDN Community Support | Feedback to us

    • Marked as answer by Dave Alessi Tuesday, November 06, 2012 11:11 AM
    Tuesday, November 06, 2012 7:17 AM
    Moderator
  • Thanks for this.  InThe NumberFormats variable in my code is null, so I am unable to pull the format string.  Don;t know wht.

    I was able to accomplish what I needed by using the index (value = 14) to determine is is a date.

    Thanks.


    dave

    Tuesday, November 06, 2012 11:11 AM
  • Hi Dave,

    If you use the default date format the Numberformats nodes will not exists. And the NumberFormatId of CellFormat will be 14.

    Have a good day,

    Tom


    Tom Xu [MSFT]
    MSDN Community Support | Feedback to us


    Wednesday, November 07, 2012 5:16 AM
    Moderator