Search/Filter for data in excel

Question

i have a number of sheets with sales information on different products. each sale has an orderNumber assigned to it, usually one orderNumber will cover numerous sales (ex. 3 balls, 2 shoes, and 5 hats all at differ prices). i want to print an orderReceipt, so have the user search for an order number 12345 and then loop through each sheet (ea. sheet is a product), check if orderNumber column has 12345 in any of its cells, if it does, store all other data in that row for that orderNumber, (i.e. cost, quantity sold, etc.) and store this in a string array.

current code:

// declare variables and arrays here
for (int i = 1; i < sheetNum; i++)
{
   xlWorkSheet = (Excel.Worksheet)xlSheets.get_Item(i);
   chartRange = xlWorkSheet.UsedRange;
   string counterColumn = Convert.ToString((chartRange.Cells[counterVar, 2] as Excel.Range).Value2);

   // read all rows it data
   while (counterColumn != null)
   {
      if (counterColumn == orderNumber)
      {
         date[i] = Convert.ToString((chartRange.Cells[counterVar, 3] as Excel.Range).Value2);
         component[i] = Convert.ToString((chartRange.Cells[1, 1] as Excel.Range).Value2);
         cost[i] = Convert.ToString((chartRange.Cells[counterVar, 7] as Excel.Range).Value2);
         quantityPurchased[i] = Convert.ToString((chartRange.Cells[counterVar, 5] as Excel.Range).Value2);
         quantitySold[i] = Convert.ToString((chartRange.Cells[counterVar, 6] as Excel.Range).Value2);
         valuePurchased[i] = Convert.ToString((chartRange.Cells[counterVar, 9] as Excel.Range).Value2);
         valueSold[i] = Convert.ToString((chartRange.Cells[counterVar, 10] as Excel.Range).Value2);

      receiptRow[i] = date[i] + "\t \t" + component[i] + "\t $" + cost[i] + "\t" + quantityPurchased[i] + "\t $" + valuePurchased[i] + "\t" + quantitySold[i] + "\t $" + valueSold[i];
      }
      else
      {
         counterVar++;
         counterColumn = Convert.ToString((chartRange.Cells[counterVar, 2] as Excel.Range).Value2);
      }
   }
}

PJ Martins · Answer

try the following piece of code: string orderNumber = lblOrderNumber.Text; int sheetNum = xlWorkBook.Worksheets.Count; int counterVar = 2; string description = richTextBoxDescription.Text; StringBuilder sb = new StringBuilder (); sb.AppendLine( Transaction Receipt	 	 	 + orderNumber); sb.AppendLine( ------------------------------------------------------------------------------------------------------- ); sb.AppendLine(  ); sb.AppendLine(description); sb.AppendLine( 	 	 	 	 	 Purchased	 	 Sold ); sb.AppendLine( Date	 	Component	Cost	Qty	Value	 	Qty	Value ); for ( int i = 1; i { xlWorkSheet = (Excel. Worksheet )xlSheets.get_Item(i); chartRange = xlWorkSheet.UsedRange; string counterColumn = Convert .ToString((chartRange.Cells[counterVar 2] as Excel. Range ).Value2); if ( string .IsNullOrEmpty(counterColumn)) continue ; for (counterVar = 2; counterVar { counterColumn = Convert .ToString((chartRange.Cells[counterVar 2] as Excel. Range ).Value2); if (counterColumn == orderNumber) { sb.AppendLine( Convert .ToString((chartRange.Cells[counterVar 3] as Excel. Range ).Value2) + 	 	 + Convert .ToString((chartRange.Cells[1 1] as Excel. Range ).Value2) + 	 $ + Convert .ToString((chartRange.Cells[counterVar 7] as Excel. Range ).Value2) + 	 + Convert .ToString((chartRange.Cells[counterVar 5] as Excel. Range ).Value2) + 	 $ + Convert .ToString((chartRange.Cells[counterVar 9] as Excel. Range ).Value2) + 	 + Convert .ToString((chartRange.Cells[counterVar 6] as Excel. Range ).Value2)+ 	 $ + Convert .ToString((chartRange.Cells[counterVar 10] as Excel. Range ).Value2)); } } } MessageBox.Show(sb.ToString());

PJ Martins · Answer

Glad you solved it remember to change your i < sheetNum to i <= sheetNum otherwise your last sheet will not get checked.

Matthew Pawelke · Answer

problem resolved chartRange.Rows.Count and xlWorkSheet.Rows.Count was causing the crash i just set it to i  1000 and it works thanks for the help previously though

Matthew Pawelke · Answer

trying to keep it simple and follow the structure of the code above just print method) here is my modified loop still crashing though... for ( int i = 1; i { xlWorkSheet = (Excel. Worksheet )xlSheets.get_Item(i); chartRange = xlWorkSheet.UsedRange; for ( int counterVar = 2; counterVar { string counterColumn = Convert .ToString((chartRange.Cells[counterVar 2] as Excel. Range ).Text); if (counterColumn == orderNumber) { sb.AppendLine( Convert .ToString((chartRange.Cells[counterVar 3] as Excel. Range ).Text) + 	 	 + Convert .ToString((chartRange.Cells[1 1] as Excel. Range ).Value2) + 	 	 $ + Convert .ToString((chartRange.Cells[counterVar 7] as Excel. Range ).Value2) + 	 	 + Convert .ToString((chartRange.Cells[counterVar 5] as Excel. Range ).Value2) + 	 $ + Convert .ToString((chartRange.Cells[counterVar 9] as Excel. Range ).Value2) + 	 + Convert .ToString((chartRange.Cells[counterVar 6] as Excel. Range ).Value2) + 	 	 $ + Convert .ToString((chartRange.Cells[counterVar 10] as Excel. Range ).Value2)); } } }

Matthew Pawelke · Answer

So i just tried getting rid of the loop to filter data and just displaying all data from all sheets in the 3rd row and displaying that in the way i want and it works fine so the code is looping through the sheets and writing to a string. the problem was/is filtering to check if the current sheet has the orderNumber then writing to string... here's the code for the modified bit: { string orderNumber = lblOrderNumber.Text; int sheetNum = xlWorkBook.Worksheets.Count; string description = richTextBoxDescription.Text; StringBuilder sb = new StringBuilder (); sb.AppendLine( Transaction Receipt	 	 	 + orderNumber); sb.AppendLine( ------------------------------------------------------------------------------------------------------- ); sb.AppendLine(  ); sb.AppendLine(description); sb.AppendLine( 	 	 	 	 	 Purchased	 	 Sold ); sb.AppendLine( Date	 	Component	Cost	Qty	Value	 	Qty	Value ); for ( int i = 1; i { xlWorkSheet = (Excel. Worksheet )xlSheets.get_Item(i); chartRange = xlWorkSheet.UsedRange; sb.AppendLine( Convert .ToString((chartRange.Cells[3 3] as Excel. Range ).Value2) + 	 	 + Convert .ToString((chartRange.Cells[1 1] as Excel. Range ).Value2) + 	 $ + Convert .ToString((chartRange.Cells[3 7] as Excel. Range ).Value2) + 	 + Convert .ToString((chartRange.Cells[3 5] as Excel. Range ).Value2) + 	 $ + Convert .ToString((chartRange.Cells[3 9] as Excel. Range ).Value2) + 	 + Convert .ToString((chartRange.Cells[3 6] as Excel. Range ).Value2) + 	 $ + Convert .ToString((chartRange.Cells[3 10] as Excel. Range ).Value2)); } MessageBox .Show(sb.ToString()); }

Matthew Pawelke · Answer

I tried re-positioning the if ( string .IsNullOrEmpty(counterColumn)) continue ; statement and that caused the program to crash the same way as before all other changes didn't do any damage but didn't help at all either i'm still getting the first 6 elements that are hardcoded in and then only the second row (heading row) of one sheet in the message box and thats it

PJ Martins · Answer

alright try this couple of things changed I made a mistake on the last submission the if ( string .IsNullOrEmpty(counterColumn)) continue ; should be inside the second loop not the first. Next thing your first loop was not including the last sheet note the i for ( int i = 1; i { xlWorkSheet = (Excel. Worksheet )xlSheets.get_Item(i); chartRange = xlWorkSheet.UsedRange; string counterColumn = Convert .ToString((chartRange.Cells[counterVar 2] as Excel. Range ).Value2); for (counterVar = 2; counterVar { counterColumn = Convert .ToString((chartRange.Cells[counterVar 2] as Excel. Range ).Value2); if ( string .IsNullOrEmpty(counterColumn)) continue ; if (counterColumn == orderNumber) { sb.AppendLine( Convert .ToString((chartRange.Cells[counterVar 3] as Excel. Range ).Text) + 	 	 + Convert .ToString((chartRange.Cells[1 1] as Excel. Range ).Value2) + 	 $ + Convert .ToString((chartRange.Cells[counterVar 7] as Excel. Range ).Value2) + 	 + Convert .ToString((chartRange.Cells[counterVar 5] as Excel. Range ).Value2) + 	 $ + Convert .ToString((chartRange.Cells[counterVar 9] as Excel. Range ).Value2) + 	 + Convert .ToString((chartRange.Cells[counterVar 6] as Excel. Range ).Value2)+ 	 $ + Convert .ToString((chartRange.Cells[counterVar 10] as Excel. Range ).Value2)); } } }

Matthew Pawelke · Answer

for confidentiality reasons i can't give you the actual file but this is the same template only difference is all the columns with zero values actually have equations in them that are calculating the value I am already entering data from the interface saving it viewing any of the columns i need to view all i want to do now is print all actions matching a particular order number thanks for all the help

PJ Martins · Answer

No problem could you send me the excel file you're working with?

Matthew Pawelke · Answer

Thanks a lot! that helps but still a few problems it's not actually taking orderNumber into account i can enter anything and it'll still compile but also instead of showing numerous rows of cost quantity etc like i want in list view its showing only the 2nd row which is the heading row in the excel file and only from the first sheet.

Matthew Pawelke · Answer

here is the whole thing at the end i'm displaying in message box just for trials for now i will write to notepad file once i get this part working also the Show(...) i will use a loop to display all the elements right now i'm entering specific elements that i know have orderNumber and should show data even when the first 6 elements (the ones hard coded in) were showing though receiptRow[9] (the sheet that has orderNumber) wasn't showing string orderNumber = lblOrderNumber.Text; int sheetNum = xlWorkBook.Worksheets.Count; int counterVar = 2; string description = richTextBoxDescription.Text; string [] receiptRow = new string [sheetNum]; string [] date = new string [sheetNum]; string [] component = new string [sheetNum]; string [] cost = new string [sheetNum]; string [] quantityPurchased = new string [sheetNum]; string [] quantitySold = new string [sheetNum]; string [] valuePurchased = new string [sheetNum]; string [] valueSold = new string [sheetNum]; for ( int i = 1; i { xlWorkSheet = (Excel. Worksheet )xlSheets.get_Item(i); chartRange = xlWorkSheet.UsedRange; string counterColumn = Convert .ToString((chartRange.Cells[counterVar 2] as Excel. Range ).Value2); for (counterVar = 2; counterVar { counterColumn = Convert .ToString((chartRange.Cells[counterVar 2] as Excel. Range ).Value2); if (counterColumn == orderNumber) { date[i] = Convert .ToString((chartRange.Cells[counterVar 3] as Excel. Range ).Value2); component[i] = Convert .ToString((chartRange.Cells[1 1] as Excel. Range ).Value2); cost[i] = Convert .ToString((chartRange.Cells[counterVar 7] as Excel. Range ).Value2); quantityPurchased[i] = Convert .ToString((chartRange.Cells[counterVar 5] as Excel. Range ).Value2); quantitySold[i] = Convert .ToString((chartRange.Cells[counterVar 6] as Excel. Range ).Value2); valuePurchased[i] = Convert .ToString((chartRange.Cells[counterVar 9] as Excel. Range ).Value2); valueSold[i] = Convert .ToString((chartRange.Cells[counterVar 10] as Excel. Range ).Value2); receiptRow[(i + 6)] = date[i] + 	 	 + component[i] + 	 $ + cost[i] + 	 + quantityPurchased[i] + 	 $ + valuePurchased[i] + 	 + quantitySold[i] + 	 $ + valueSold[i] + 
 ; } if ( string .IsNullOrEmpty(counterColumn)) continue ; } } receiptRow[0] = Transaction Receipt + 	 	 	 + orderNumber + 
 ; receiptRow[1] = ------------------------------------------------------------------------------------------------------- + 
 ; receiptRow[2] =  + 
 ; receiptRow[3] = description + 
 ; receiptRow[4] = 	 	 	 	 	 +  Purchased + 	 	 +  Sold + 
 ; receiptRow[5] = Date + 	 	 + Component + 	 + Cost + 	 + Qty + 	 + Value + 	 	 + Qty + 	 + Value + 
 ; MessageBox .Show(receiptRow[0] + receiptRow[1] + receiptRow[2] + receiptRow[3] + receiptRow[4] + receiptRow[5] + receiptRow[9]);

PJ Martins · Answer

Can you send me the code before your loop as well?

Matthew Pawelke · Answer

no error message all that happens is the drop down menu i have to select to print goes semi transparent and it just sits and does nothing can't close or do anything and no highlighted line of code or any error message i just stop debugging i think it might be getting caught in a loop somewhere but not sure here is the code i have after the loop if you want to see it... ... if ( string .IsNullOrEmpty(counterColumn)) continue ; } } receiptRow[0] = Transaction Receipt + 	 	 	 + orderNumber + 
 ; receiptRow[1] = ------------------------------------------------------------------------------------------------------- + 
 ; receiptRow[2] =  + 
 ; receiptRow[3] = description + 
 ; receiptRow[4] = 	 	 	 	 	 +  Purchased + 	 	 +  Sold + 
 ; receiptRow[5] = Date + 	 	 + Component + 	 + Cost + 	 + Qty + 	 + Value + 	 	 + Qty + 	 + Value + 
 ; MessageBox .Show(receiptRow[0] + receiptRow[1] + receiptRow[2] + receiptRow[3] + receiptRow[4] + receiptRow[5] + receiptRow[9]); edit: the first 6 elements are just to format my file for notepad probably an easier way but i guess this works

PJ Martins · Answer

Where is it crashing and what error message does it give you?

Matthew Pawelke · Answer

i know what you're getting at and that helps with the problem of a blank cell between two filled cells but i just changed to the code above and now i am back to crashing once i click print before i could atleast display the first 6 elements without gathering any data from the worksheet

Matthew Pawelke · Answer

like that? for ( int i = 1; i { xlWorkSheet = (Excel. Worksheet )xlSheets.get_Item(i); chartRange = xlWorkSheet.UsedRange; string counterColumn = Convert .ToString((chartRange.Cells[counterVar 2] as Excel. Range ).Value2); for (counterVar = 2; counterVar { counterColumn = Convert .ToString((chartRange.Cells[counterVar 2] as Excel. Range ).Value2); if (counterColumn == orderNumber) { date[i] = Convert .ToString((chartRange.Cells[counterVar 3] as Excel. Range ).Value2); component[i] = Convert .ToString((chartRange.Cells[1 1] as Excel. Range ).Value2); cost[i] = Convert .ToString((chartRange.Cells[counterVar 7] as Excel. Range ).Value2); quantityPurchased[i] = Convert .ToString((chartRange.Cells[counterVar 5] as Excel. Range ).Value2); quantitySold[i] = Convert .ToString((chartRange.Cells[counterVar 6] as Excel. Range ).Value2); valuePurchased[i] = Convert .ToString((chartRange.Cells[counterVar 9] as Excel. Range ).Value2); valueSold[i] = Convert .ToString((chartRange.Cells[counterVar 10] as Excel. Range ).Value2); receiptRow[(i + 6)] = date[i] + 	 	 + component[i] + 	 $ + cost[i] + 	 + quantityPurchased[i] + 	 $ + valuePurchased[i] + 	 + quantitySold[i] + 	 $ + valueSold[i] + 
 ; } if ( string .IsNullOrEmpty(counterColumn)) continue ; } }

PJ Martins · Answer

Well the problem with checking for empty strings is what if you have two cells with values the next cell empty and then more cells to follow with values. Once your loop hits the first empty cell it will stop and not pick up the values which follow. Again may I suggest you do a for loop for ( int j = 1; j for ( int j = 1; j { counterColumn = Convert .ToString((chartRange.Cells[j 2] as Excel. Range ).Value2); if ( string .IsNullOrEmpty(counterColumn)) continue; } The continue statement will immediately skip to the next row

Matthew Pawelke · Answer

I tried your suggestion and its works right now i'm displays the first 6 elements fine but nothing beyond i think there is a problem still in the way i'm looking through each sheet and assigning data to string array if orderNumber is there any ideas?

PJ Martins · Answer

Again you're getting caught in an endless loop. Change while (counterColumn != null) to while (! string .IsNullOrEmpty(counterColumn)) and see if you still get stuck.

Matthew Pawelke · Answer

i've used the counterColumn loop with the exact same code (i.e. Convert.ToString(...)) previously in the program to find the first empty cell in a column and it was fine i'm using it now just to keep checking all cells with a value. i tried switching my loop order but didn't make a difference. i think the problem is the nested loop either way its ordered its getting caught somewhere and doesn't terminate. I think the code itself is ok but any suggestions on the logic would be great thanks!

PJ Martins · Answer

The only thing I can see wrong with your code is counterColumn will never be null since you do a Convert.ToString it will be an empty string for empty cells resulting in an endless loop. Try using a second counter variable: for ( int j = 1; j chartRange.Rows.Count ; j++) { counterColumn = Convert .ToString((chartRange.Cells[j 2] as Excel. Range ).Value2); } Also make sure you loop your i to