Search/Filter for data in excel

Matthew Pawelke

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);
      }
   }
}

21 Replies

Know the answer? Post it — somebody with the same question will find it here.

PJ Martins16y ago
Accepted 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\t \t \t" + orderNumber);
sb.AppendLine("-------------------------------------------------------------------------------------------------------");
sb.AppendLine("");
sb.AppendLine(description);
sb.AppendLine("\t \t \t \t \t Purchased\t \t Sold");
sb.AppendLine("Date\t \tComponent\tCost\tQty\tValue\t \tQty\tValue");
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);
if (string.IsNullOrEmpty(counterColumn)) continue;
for (counterVar = 2; counterVar < chartRange.Rows.Count; 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) + "\t \t" +
Convert.ToString((chartRange.Cells[1, 1] as Excel.Range).Value2) + "\t $" +
Convert.ToString((chartRange.Cells[counterVar, 7] as Excel.Range).Value2) + "\t" +
Convert.ToString((chartRange.Cells[counterVar, 5] as Excel.Range).Value2) + "\t $" +
Convert.ToString((chartRange.Cells[counterVar, 9] as Excel.Range).Value2) + "\t" +
Convert.ToString((chartRange.Cells[counterVar, 6] as Excel.Range).Value2)+ "\t $" +
Convert.ToString((chartRange.Cells[counterVar, 10] as Excel.Range).Value2));
}
}
}
MessageBox.Show(sb.ToString());
PJ Martins16y ago
Glad you solved it, remember to change your i < sheetNum to i <= sheetNum otherwise your last sheet will not get checked.
Matthew Pawelke16y ago
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 Pawelke16y ago
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 < sheetNum; i++)
{
   xlWorkSheet = (Excel.Worksheet)xlSheets.get_Item(i);
   chartRange = xlWorkSheet.UsedRange;
   for (int counterVar = 2; counterVar < xlWorkSheet.Rows.Count; 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) + "\t \t" +
         Convert.ToString((chartRange.Cells[1, 1] as Excel.Range).Value2) + "\t \t $" +
         Convert.ToString((chartRange.Cells[counterVar, 7] as Excel.Range).Value2) + "\t \t" +
         Convert.ToString((chartRange.Cells[counterVar, 5] as Excel.Range).Value2) + "\t $" +
         Convert.ToString((chartRange.Cells[counterVar, 9] as Excel.Range).Value2) + "\t" +
         Convert.ToString((chartRange.Cells[counterVar, 6] as Excel.Range).Value2) + "\t \t $" +
         Convert.ToString((chartRange.Cells[counterVar, 10] as Excel.Range).Value2));
      }
   }
}
Matthew Pawelke16y ago
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\t \t \t" + orderNumber);
sb.AppendLine("-------------------------------------------------------------------------------------------------------");
sb.AppendLine("");
sb.AppendLine(description);
sb.AppendLine("\t \t \t \t \t Purchased\t \t Sold");
sb.AppendLine("Date\t \tComponent\tCost\tQty\tValue\t \tQty\tValue");

for (int i = 1; i < sheetNum; i++)
{
   xlWorkSheet = (Excel.Worksheet)xlSheets.get_Item(i);
   chartRange = xlWorkSheet.UsedRange;

   sb.AppendLine(Convert.ToString((chartRange.Cells[3, 3] as Excel.Range).Value2) + "\t \t" +
   Convert.ToString((chartRange.Cells[1, 1] as Excel.Range).Value2) + "\t $" +
   Convert.ToString((chartRange.Cells[3, 7] as Excel.Range).Value2) + "\t" +
   Convert.ToString((chartRange.Cells[3, 5] as Excel.Range).Value2) + "\t $" +
   Convert.ToString((chartRange.Cells[3, 9] as Excel.Range).Value2) + "\t" +
   Convert.ToString((chartRange.Cells[3, 6] as Excel.Range).Value2) + "\t $" +
   Convert.ToString((chartRange.Cells[3, 10] as Excel.Range).Value2));
}

MessageBox.Show(sb.ToString());
}
Matthew Pawelke16y ago
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 Martins16y ago
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 < sheetNum changed to i <= sheetNum, lastly change the date cell to use .Text instead of .Value2 to get the raw text, let me know if that works:
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);

for (counterVar = 2; counterVar <= chartRange.Rows.Count; 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) + "\t \t" +
Convert.ToString((chartRange.Cells[1, 1] as Excel.Range).Value2) + "\t $" +
Convert.ToString((chartRange.Cells[counterVar, 7] as Excel.Range).Value2) + "\t" +
Convert.ToString((chartRange.Cells[counterVar, 5] as Excel.Range).Value2) + "\t $" +
Convert.ToString((chartRange.Cells[counterVar, 9] as Excel.Range).Value2) + "\t" +
Convert.ToString((chartRange.Cells[counterVar, 6] as Excel.Range).Value2)+ "\t $" +
Convert.ToString((chartRange.Cells[counterVar, 10] as Excel.Range).Value2));
}
}
}
Matthew Pawelke16y ago
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 Martins16y ago
No problem, could you send me the excel file you're working with?
Matthew Pawelke16y ago
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 Pawelke16y ago
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 < (sheetNum - 6); 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 < chartRange.Rows.Count; 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] + "\t \t" + component[i] + "\t $" + cost[i] + "\t" + quantityPurchased[i] + "\t $" + valuePurchased[i] + "\t" + quantitySold[i] + "\t $" + valueSold[i] + "\n";
}

if (string.IsNullOrEmpty(counterColumn)) continue;
}
}
receiptRow[0] = "Transaction Receipt" + "\t \t \t" + orderNumber + "\n";
receiptRow[1] = "-------------------------------------------------------------------------------------------------------" + "\n";
receiptRow[2] = "" + "\n";
receiptRow[3] = description + "\n";
receiptRow[4] = "\t \t \t \t \t" + " Purchased" + "\t \t" + " Sold" + "\n";
receiptRow[5] = "Date" + "\t \t" + "Component" + "\t" + "Cost" + "\t" + "Qty" + "\t" + "Value" + "\t \t" + "Qty" + "\t" + "Value" + "\n";

MessageBox.Show(receiptRow[0] + receiptRow[1] + receiptRow[2] + receiptRow[3] + receiptRow[4] + receiptRow[5] + receiptRow[9]);
PJ Martins16y ago
Can you send me the code before your loop as well?
Matthew Pawelke16y ago
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" + "\t \t \t" + orderNumber + "\n";
receiptRow[1] = "-------------------------------------------------------------------------------------------------------" + "\n";
receiptRow[2] = "" + "\n";
receiptRow[3] = description + "\n";
receiptRow[4] = "\t \t \t \t \t" + " Purchased" + "\t \t" + " Sold" + "\n";
receiptRow[5] = "Date" + "\t \t" + "Component" + "\t" + "Cost" + "\t" + "Qty" + "\t" + "Value" + "\t \t" + "Qty" + "\t" + "Value" + "\n";

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 Martins16y ago
Where is it crashing and what error message does it give you?
Matthew Pawelke16y ago
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 Pawelke16y ago
like that?

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);

for (counterVar = 2; counterVar < chartRange.Rows.Count; 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] + "\t \t" + component[i] + "\t $" + cost[i] + "\t" + quantityPurchased[i] + "\t $" + valuePurchased[i] + "\t" + quantitySold[i] + "\t $" + valueSold[i] + "\n";
}

if (string.IsNullOrEmpty(counterColumn)) continue;
}
}
PJ Martins16y ago
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 <= chartRange.Rows.Count; j++) instead of your while loop. Doing this will pick up the range between the first cell value and last cell value. If you want to skip empty cells within that range you can do something like this:
for (int j = 1; j <= chartRange.Rows.Count; 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 Pawelke16y ago
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 Martins16y ago
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 Pawelke16y ago
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 Martins16y ago
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 <= sheetNum else the last work sheet will not get checked.