locked
Looping through an excel spreadsheet RRS feed

  • Question

  • Ok, I have been trying to get this right for a few days now and can't seem to be able to get it right. Can someone please look at this and let me know what am doing wrong? I am trying to loop through an excel spreadsheet and grab information from certain cells. The cells that interest me are B2, A2, A21 through A31 and B21 through B31. The code i came up with is not stepping through the entire logic...so i am definitely doing something wrong. Looking at the code below, it steps into the start of the loop just fine, but when it gets to the first "if" statement, it doesn't go into the rest of the code even if that statement is true. Is my construct wrong in that am using the if incorrectly or something?

    What I thought my code would do was first grab the info in B1, then grab that in A2, then loop through the spreadsheet until it comes across the string "proceeds"...at that point, it will then grab the rest of the info and then dump them all in a sql database table (trying to create dynamically)

    Please help.

    private void button1_Click_1(object sender, EventArgs e)
     {
      Excel.Application xlApp;
      Excel.Workbook xlWorkBook;
      Excel.Worksheet xlWorkSheet;
      Excel.Range range;
      List<string> myList = new List<string>();
      //Excel.Range cell;
    
      string str;
      string midStr;
      string outerStr;
      int rCnt = 0;
      int cCnt = 0;
    
      xlApp = new Excel.ApplicationClass();
      xlWorkBook = xlApp.Workbooks.Open(textBox1.Text, 0, true, 5, "", "", true, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "\t", false, false, 0, true, 1, 0);
      //xlWorkBook = xlApp.Workbooks.;
      xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);
    
      range = xlWorkSheet.get_Range("A1", "B31");
    
    
      for (int outerRCnt = 1; outerRCnt <= 42; outerRCnt++)
      {
      for (int outerCCnt = 1; outerCCnt <= 2; outerCCnt++)
      {
       outerStr = (range.Cells[outerRCnt, outerCCnt] as Excel.Range).Value2.ToString();
    
       if (outerStr == "1, 2")
       {   
       
       string myDate = outerStr.ToString();
       DateTime dt = Convert.ToDateTime(myDate);
    
       for (int midRCnt = 1; midRCnt <= 42; midRCnt++)
       {
        for (int midCCnt = 1; midCCnt <= 2; midCCnt++)
        {
        midStr = (string)(range.Cells[outerRCnt, outerCCnt] as Excel.Range).Value2;
    
        if (midStr == "2, 1")
        {
         
         string lNumber = outerStr.ToString();
    
         str = (string)(range.Cells[rCnt, cCnt] as Excel.Range).Value2;
    
         while (str.ToString() == "Proceeds")
         {
         //Excel.Range newRange;
         for (rCnt = 1; rCnt <= 11; rCnt++)
         {
    
          for (cCnt = 1; cCnt <= 2; cCnt++)
          {
          str = (string)(range.Cells[rCnt, cCnt] as Excel.Range).Value2;
    
          myList.Add(str);
    
          }
    
          Dictionary<string, string> keyValue = new Dictionary<string, string>();
    
          keyValue.Add(myList[0], myList[1]);
         }
         //do insert statement to DB
         cmd.Connection = conn;
         cmd.CommandText = "create table " + myDate + "_lremoved(l_date date not null, lnumber int not null"+ myList[0] + "money null) values("
          + myDate + "," + lNumber + "," + myList[1] +")";
         }
        }
        }
       }
       }
      }
      }
      
     }
    

    • Moved by Mike Dos Zhang Monday, November 29, 2010 3:25 AM (From:Visual C# General)
    Tuesday, November 23, 2010 5:18 PM

Answers

  • I think you can simplify your loops.  Store the required ranges in an array/list, loop through the array/list and then loop through the cells of the range.

    Something like this:

        private static void ExcelTest(String S_Directory, String FileName)
        {
          xls.Application ExcelObj = new xls.Application();
    
          ExcelObj.DisplayAlerts = false;
          ExcelObj.Visible = true;
    
          xls.Workbook eBook;
          xls.Workbooks wb = ExcelObj.Workbooks;
          eBook = wb.Add(S_Directory + FileName);
          xls.Worksheet ws = (xls.Worksheet)eBook.Sheets["Missing_Data"];
    
          List<String> xlsRanges = new List<String> { "A2:A2", "B2:B2", "A21:A31", "B21:B31" };
    
          foreach (String s in xlsRanges)
          {
            xls.Range r = ws.get_Range(s, Type.Missing);
            foreach (xls.Range c in r.Cells)
            {
              myList.Add(c.Value2.ToString());
            }
          }
        }
    
    Or create the dictionary within the foreach loop and add the cell information and its value.
    • Proposed as answer by JohnGrove Tuesday, November 23, 2010 7:32 PM
    • Unproposed as answer by Spawn10 Tuesday, November 23, 2010 7:58 PM
    • Marked as answer by Spawn10 Tuesday, November 30, 2010 4:20 PM
    Tuesday, November 23, 2010 6:59 PM

All replies

  • I think you can simplify your loops.  Store the required ranges in an array/list, loop through the array/list and then loop through the cells of the range.

    Something like this:

        private static void ExcelTest(String S_Directory, String FileName)
        {
          xls.Application ExcelObj = new xls.Application();
    
          ExcelObj.DisplayAlerts = false;
          ExcelObj.Visible = true;
    
          xls.Workbook eBook;
          xls.Workbooks wb = ExcelObj.Workbooks;
          eBook = wb.Add(S_Directory + FileName);
          xls.Worksheet ws = (xls.Worksheet)eBook.Sheets["Missing_Data"];
    
          List<String> xlsRanges = new List<String> { "A2:A2", "B2:B2", "A21:A31", "B21:B31" };
    
          foreach (String s in xlsRanges)
          {
            xls.Range r = ws.get_Range(s, Type.Missing);
            foreach (xls.Range c in r.Cells)
            {
              myList.Add(c.Value2.ToString());
            }
          }
        }
    
    Or create the dictionary within the foreach loop and add the cell information and its value.
    • Proposed as answer by JohnGrove Tuesday, November 23, 2010 7:32 PM
    • Unproposed as answer by Spawn10 Tuesday, November 23, 2010 7:58 PM
    • Marked as answer by Spawn10 Tuesday, November 30, 2010 4:20 PM
    Tuesday, November 23, 2010 6:59 PM
  • Thanks for the update Chris...forgive me if am still not getting it completely. In my code, there was a section where I wanted to test to see if the content of the cell was "Proceeds". The reason is this...here is a list of what I would be grabbing from start to finish.

    1) date content in B1

    2) Id number in A2

    3) All entries under the cell that says "Proceeds". This will cover about 11 A cells and 11 B cells. Their particular location could vary.

    I have tried to play with your code...it did simplify some of what i was trying to do, but I could not add the "Proceeds" test without making it look like mine again...and we know my is not going through it all. Any suggestions as to how I might be able to get this done in your simpler version?

    Thanks a bunch.

    Tuesday, November 23, 2010 9:50 PM
  • Not sure, if the contents of the cell equals exactly "Proceeds," but you can just add an IF statement to the FOREACH loop, like so:

              if (c.Value2.ToString().Substring(0, 8) == "Proceeds")
              {
                myList.Add(c.Value2.ToString());
              }
    

    There may be a better way of checking the string contents, but you'd need to get a more seasoned programmer here to respond.

    Are you looking in one cell for Proceeds, and then grabbing the value from an adjacent cell?  If so, I'm pretty sure you can use the cell reference to get the needed range.  I don't recall, what that's called, but it looks like r1c1, or some such, or you can just get the column and row values from the current cell and increment it appropriately and add the contents of that cell to your list.

    Tuesday, November 23, 2010 10:12 PM
  • Hi Spawn10,

     

    You'll need to post it at http://social.msdn.microsoft.com/Forums/en-US/exceldev/threads for quicker and better responses, where excel develop experts live in.

     

    Have a nice weekend!

    Mike

    *****************************************************

    [All-In-One Code Framework]

    Sample world! You will get more from this world!

    Welcome to the new world!

    Sunday, November 28, 2010 11:22 AM