none
c# and excel

    Question

  • How to count number of cells with Datetime datatype in excelsheet.It should be in c#.

    Number of cells containing Datetime datatype in excelsheet.

    Please send the code as soon as possible


    Tuesday, March 22, 2011 10:45 AM

Answers

  • Hi,

    First of all, is the formatting of dates same in all the cells.. I'm assuming everything is "m/d/yyyy" format. if so, you can use the sample code. else you will need to modify it accordingly. hope this is useful!

          Excel.Range UsedRange = Globals.Sheet1.UsedRange;
          int count = 0;
          List<Excel.Range> DateTimeRange = new List<Microsoft.Office.Interop.Excel.Range>();
          foreach (Excel.Range cell in UsedRange.Cells)
          {
            string s = cell.NumberFormat.ToString();
            if (s== "m/d/yyyy")
            {
              count++;
              DateTimeRange.Add(cell);
            }
          }
          MessageBox.Show("Number of cells with DateTime as number format is : " + count);
    


    Regards, Subhash Sreenivasachar
    • Marked as answer by Rishiketh Wednesday, March 23, 2011 8:58 AM
    Tuesday, March 22, 2011 11:33 AM

All replies

  • Hi,

    First of all, is the formatting of dates same in all the cells.. I'm assuming everything is "m/d/yyyy" format. if so, you can use the sample code. else you will need to modify it accordingly. hope this is useful!

          Excel.Range UsedRange = Globals.Sheet1.UsedRange;
          int count = 0;
          List<Excel.Range> DateTimeRange = new List<Microsoft.Office.Interop.Excel.Range>();
          foreach (Excel.Range cell in UsedRange.Cells)
          {
            string s = cell.NumberFormat.ToString();
            if (s== "m/d/yyyy")
            {
              count++;
              DateTimeRange.Add(cell);
            }
          }
          MessageBox.Show("Number of cells with DateTime as number format is : " + count);
    


    Regards, Subhash Sreenivasachar
    • Marked as answer by Rishiketh Wednesday, March 23, 2011 8:58 AM
    Tuesday, March 22, 2011 11:33 AM
  • Hi Subhash

     

    It is showing error near Globals as "Globals does not exixt in the current context"

     

    Please look at this

    Tuesday, March 22, 2011 12:30 PM
  • Can you let me know how you are reading excel through C#? Is it a vsto application?
    Regards, Subhash Sreenivasachar
    Tuesday, March 22, 2011 1:29 PM
  • I got it,it is working

    Thanks a lot Subhash

     

    • Proposed as answer by Tell_Me_More Monday, June 27, 2011 9:20 AM
    • Unproposed as answer by Tell_Me_More Monday, June 27, 2011 9:20 AM
    Wednesday, March 23, 2011 4:58 AM
  • How write to excel cell as numeric ? ( Not as Text )

    Thanks

    Monday, June 27, 2011 9:21 AM