Range.find in C# with multiple 'what'

2019-07-29 10:32发布

Stuck in writing this thing... I am writing a C# Winform app in which I am looking up certain values in an Excel file in a certain column. I am using Excel.Range.Find but I could be wrong in this choice of course. I want to find the Excel adress of the cell with either €,EUR,USD,CAD,GBP, or NOR. But Range.Find only seems to handle one at a time? What would be the best way to proceed?

Here's a snippet of my code:

Excel.Application xlApp = (Excel.Application)System.Runtime.InteropServices.Marshal.GetActiveObject("Excel.Application");

Excel.Range myRange;
myRange = xlApp.ActiveSheet.UsedRange;

DataTable dtProductRowsFromExcel = new DataTable(); //to save all found rows form excel .
//DataGridView dataGridView1 = new DataGridView(); //for use of copying from excel to clipboard.

dtProductRowsFromExcel.Columns.Add("Code", typeof(String)); //column 1          Excel B
dtProductRowsFromExcel.Columns.Add("Amount", typeof(String)); //column 2        Excel C
dtProductRowsFromExcel.Columns.Add("Unit", typeof(String)); //column 3          Excel D
dtProductRowsFromExcel.Columns.Add("ValutaUnit", typeof(String)); //column 4    Excel E
dtProductRowsFromExcel.Columns.Add("PriceUnit", typeof(String)); //column 5     Excel F
dtProductRowsFromExcel.Columns.Add("ValutaTotal", typeof(String)); //column 6   Excel G
dtProductRowsFromExcel.Columns.Add("PriceTotal", typeof(String)); //column 7    Excel H


try
{
    Excel.Range currentFind = null;
    Excel.Range firstFind = null;

    var missing = Missing.Value;


    Excel.Range RangeWithValutaSigns = xlApp.ActiveSheet.Range("g1", "g500");
    currentFind = RangeWithValutaSigns.Find(lblValutaTeken.Text, missing,
        Excel.XlFindLookIn.xlValues, Excel.XlLookAt.xlPart,
        Excel.XlSearchOrder.xlByRows, Excel.XlSearchDirection.xlNext, false,
        missing, missing);

    while (currentFind != null)
    {
        if (firstFind == null)
        {
            firstFind = currentFind;
        }
        else if (currentFind.get_Address(Excel.XlReferenceStyle.xlA1)
              == firstFind.get_Address(Excel.XlReferenceStyle.xlA1))
        {
            break;
        }
        Console.WriteLine("~~~~ currentFind.Row = " + currentFind.Row);

        string B = currentFind.Offset[0, -5].Value2.ToString();
        string C = (currentFind.Offset[0, -4].Value2 != null) ? currentFind.Offset[0, -4].Value2.ToString() : "";
        string D = (currentFind.Offset[0, -3].Value2 != null) ? currentFind.Offset[0, -3].Value2.ToString() : "";
        string E = (currentFind.Offset[0, -2].Value2 != null) ? currentFind.Offset[0, -2].Value2.ToString() : "";
        string F = (currentFind.Offset[0, -1].Value2 != null) ? currentFind.Offset[0, -1].Value2.ToString() : "";
        string G = currentFind.Value2.ToString();
        string H = (currentFind.Offset[0, 1].Value2 != null) ? currentFind.Offset[0, 1].Value2.ToString() : "";

        dtProductRowsFromExcel.Rows.Add(B, C, D, E, F, G, H);

        currentFind = RangeWithValutaSigns.FindNext(currentFind);

        }
    // check datatable in output window:
    foreach (DataRow dataRow in dtProductRowsFromExcel.Rows)
    {
        foreach (var item in dataRow.ItemArray)
        {
            Console.WriteLine(item);
        }

    }

    dataGridView1.ReadOnly = true;
    dataGridView1.RowHeadersVisible = false;
    dataGridView1.AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.AllCells;
    dataGridView1.DataSource = dtProductRowsFromExcel;
    dataGridView1.ClipboardCopyMode = DataGridViewClipboardCopyMode.EnableWithoutHeaderText;
    dataGridView1.SelectAll();

    DataObject d = dataGridView1.GetClipboardContent();
    Clipboard.SetDataObject(d);


}
catch (Exception ex)
{
    MessageBox.Show("Error message:" + System.Environment.NewLine + ex.Message);
}

0条回答
登录 后发表回答