none
get value from a cell RRS feed

  • Question

  • Hi,

    i need to read values from an excel sheet. i'm not sure the way i'm doing is the right thing ...

    i get a cell object from column, row - that's OK.

    then check the cell.DataType, which is most of the cases NULL. it's not null if the value is SharedString, and here i can get the value from the sharedstring table - that's OK.

    if the DataType is null, then i try to check the numberformat. based on the styleindex i get the NumberingFormat.

    here i have the problem. i have a currency formatted cell, and i the numberformat of that field is [$€-2] plus the real fomatting (for positive, negative)

    if i check the numberformat for a date cell i have the [$-409] plus the formatting for year/month/day

    can you tell me what is the prefix in numberformat?

    how can i parse this? is there a rule somewhere describing these prefixes?

    is there a better way to get a typed value from excel with openxml sdk 2.0?

    thanks in advance


    Ivan
    Thursday, July 14, 2011 1:16 PM

Answers

  • i have a template, it means i know very well what data type i can expect in a certain cell. so when i get a value from a cell i know how to cast it.

    that means i changed the way i get a value from a cell, and to identify how to cast it.


    Ivan
    • Marked as answer by Ivan Horvath Tuesday, July 19, 2011 11:51 AM
    Tuesday, July 19, 2011 11:51 AM

All replies

  • I had the same problem before. The key is that Excel's formats don't allow just ANY characters. You can't mix and match date keywords with numeric ones. I use the following function after getting the NumberFormat string:

        private static XLCellValues GetDataTypeFromFormat(String format)
        {
          int length = format.Length;
          String f = format.ToLower();
          for (Int32 i = 0; i < length; i++)
          {
            Char c = f[i];
            if (c == '"')
              i = f.IndexOf('"', i + 1); // Ignore quoted strings
            else if (c == '0' || c == '#' || c == '?')
              return XLCellValues.Number;
            else if (c == 'y' || c == 'm' || c == 'd' || c == 'h' || c == 's')
              return XLCellValues.DateTime;
          }
          return XLCellValues.Text;
        }
    

    Just replace XLCellValues with your own enumeration...


    ClosedXML - Create Excel files in .Net
    Thursday, July 14, 2011 2:18 PM
  • Hi Ivan,

    What is the status of the problem on your side? If you still show any concern on the thread, just feel free to follow up.

    Best Regards,


    Bruce Song [MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Tuesday, July 19, 2011 10:39 AM
  • i have a template, it means i know very well what data type i can expect in a certain cell. so when i get a value from a cell i know how to cast it.

    that means i changed the way i get a value from a cell, and to identify how to cast it.


    Ivan
    • Marked as answer by Ivan Horvath Tuesday, July 19, 2011 11:51 AM
    Tuesday, July 19, 2011 11:51 AM