Searching a collection of excel sheets w/C#

Question

I'm trying to get C# to examine whatever workbook the user has selected and find any sheets which would contain stock data. Concretely this would mean looking at a range of cells (say r<6, c<10) for "Close", "close" or "CLOSE".

The following code shows the point at which the user has selected an .xls file.

I'm not sure how to loop through the sheets in the workbook to look for the desired text.

I'm assuming it involves creating a collection of sheets and assigning it to those in the current workbook, but my attempts so far haven't worked.

private void button1_Click(object sender, System.EventArgs e)
{
  try
  {
    OpenFileDialog dlg = new OpenFileDialog();
    dlg.Filter = "Excel Files (*.xls)|*.XLS";

    if (dlg.ShowDialog() == DialogResult.OK)
    {

       // MessageBox.Show(dlg.FileName, "My Application", MessageBoxButtons.OKCancel, MessageBoxIcon.Asterisk);
       Excel.Application xlApp = new Excel.ApplicationClass();
       xlApp.Visible = true;
       Excel.Workbook xlWorkbook = xlApp.Workbooks.Open(dlg.FileName,
                0, false, 5, "", "", false, Excel.XlPlatform.xlWindows, "",
                true, false, 0, true, false, false);

     }

  }
  catch (Exception theException)
  {
    String errorMessage;
    errorMessage = "Error: ";
    errorMessage = String.Concat(errorMessage, theException.Message);
    errorMessage = String.Concat(errorMessage, " Line: ");
    errorMessage = String.Concat(errorMessage, theException.Source);

    MessageBox.Show(errorMessage, "Error");
  }
}

Thanks for any ideas.

Jeff

Answer 1

Always take extra care to clean up when using the Interop libraries. Otherwise, you're likely to end up with a couple dozen EXCEL.EXE processes running in the background while you debug (or when a user hits an error).

private static bool IsStockDataWorkbook(string fileName)
{
    Excel.Application application = null;
    Excel.Workbook workbook = null;
    try
    {
        application = new Excel.ApplicationClass();
        application.Visible = true;
        workbook = application.Workbooks.Open(fileName, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value);

        foreach (Excel.Worksheet sheet in workbook.Worksheets)
        {
            if (IsStockWorksheet(sheet))
            {
                return true;
            }
        }

        return false;
    }
    finally
    {
        if (workbook != null)
        {
            workbook.Close(false, Missing.Value, Missing.Value);
        }
        if (application != null)
        {
            application.Quit();
        }
    }
}
private static bool IsStockWorksheet(Excel.Worksheet workSheet)
{
    Excel.Range testRange = workSheet.get_Range("C10", Missing.Value);
    string value = testRange.get_Value(Missing.Value).ToString();

    return value.Equals("close", StringComparison.InvariantCultureIgnoreCase);
}

Answer 2

You'll need to assign objSheets to something, most likely:

Excel.Sheets objSheets = xlWorkbook.Sheets;

Your foreach statement should look more like this (with no prior declaration of the ws variable):

foreach(Excel.Worksheet ws in objSheets)
{
     rng = ws.get_Range(ws.Cells[1,1], ws.Cells[5,9]);
}

Obviously, you'll want to do something more substantial in that loop.

Answer 3

This is an easy one :) use the sheets collection in the workbook object.

Answer 4

Workbooks workbooks = xlApp.Workbooks;
foreach(Workbook wb in workbooks)
{
    Worksheets worksheets = wb.Worksheets;
    foreach(Worksheet ws in worksheets)
    {
        Range range = ws.get_Range(ws.Cells[1,1], ws.Cells[5,9]);
        Range match = range.Find("close", ws.Cells[1,1],
            xlFindLookIn.xlValues, xlLookAt.xlPart,
            xlSearchOrder.xlByColumns, xlSearchDirection.xlNext,
            false, false, false); //that first false means ignore case
        // do something with your match here
        // this will only return the first match; to return all
        // you'll need to run the match in a while loop
    }
}