如何使用 C# 2.0 读取特定的 Excel 列值并将它们放入字符串数组中

Question

我有下面的代码示例，我可以在其中读取我的 Excel 文件：

private bool excelvalidate(string requestType, string filename)
{
    Excel.Application ExcelObj = null;
    ExcelObj = new Excel.Application();
    Excel.Range range = null;
    Excel.Workbook theWorkbook = null;
    Excel.Sheets sheets = null;
    Excel.Worksheet worksheet = null;

    bool strValResult = false;
    string[] strArray = null;
    if (ExcelObj != null)
    {
        theWorkbook = ExcelObj.Workbooks.Open("D:/Support/" + filename, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
        sheets = theWorkbook.Worksheets;
        worksheet = (Excel.Worksheet)sheets.get_Item(1);
        for (int x = 1; x <= 1; x++)
        {
            range = worksheet.get_Range("A" + x.ToString(), "G" + x.ToString());
            System.Array myvalues = (System.Array)range.Cells.get_Value(null);
            strArray = ConvertToStringArray(myvalues);
        }
        if (requestType == "CreateCityKeys" || requestType == "CreateCityShortKeys")
        {
            strValResult = CheckStringInArray("City Name", strArray);
            if (strValResult == true)
            {
               //Here I want to call a function which will return all the column values in  City Name something like below
               string [] columnArray = ReadAllValuesFromColumn("City Name");
            }
        }
        else if (requestType == "CreateAirportKeys")
        {
            strValResult = CheckStringInArray("Full Airport Name", strArray);
        }
        else if (requestType == "CreateAirportShortKeys")
        {
            strValResult = CheckStringInArray("Airport Short Name", strArray);
        }
        ExcelObj.Workbooks.Close();
        ExcelObj.Quit();
        ExcelObj = null;
        theWorkbook = null;
        sheets = null;
        range = null;
        Marshal.ReleaseComObject(theWorkbook);
    }
    return strValResult;
}
private bool CheckStringInArray(string chkstr, string[] strArr)
{
    bool exists = false;
    foreach (string x in strArr)
    {
        if (x.Contains(chkstr))
        {
            exists = true;
        }
    }
    return exists;
}

private string[] ConvertToStringArray(System.Array values)
{
    // create a new string array
    string[] theArray = new string[values.Length];

    // loop through the 2-D System.Array and populate the 1-D String Array
    for (int i = 1; i <= values.Length; i++)
    {
        if (values.GetValue(1, i) == null)
            theArray[i - 1] = "";
        else
            theArray[i - 1] = (string)values.GetValue(1, i).ToString();
    }
    return theArray;
}  

在上面的代码中，您可以看到，我正在尝试调用如下函数：

string [] columnArray = ReadAllValuesFromColumn("City Name");

请建议编写上述函数来读取所有列值的逻辑如果我将列标题名称作为“城市名称”传递，

请建议一些代码片段。

谢谢

Answer 1

以下是我针对上述问题编写的解决方案，请提出任何更改。

 private string ReadAllValuesFromColumn(string fieldname, string filename)
    {
        Excel.Application ExcelObj = null;
        ExcelObj = new Excel.Application();
        Excel.Range cxRange = null;
        Excel.Range range = null; 
        Excel.Workbook theWorkbook = null;
        Excel.Sheets sheets = null;
        Excel.Worksheet worksheet = null;
        string[] strArray = null;
        StringBuilder sbReturn = new StringBuilder();
        Dictionary<string, ArrayList> dicArray = new Dictionary<string, ArrayList>();
        ArrayList fullArray = new ArrayList();
        try
        {
            if (ExcelObj != null)
            {
                theWorkbook = ExcelObj.Workbooks.Open("D:/Support/" + filename, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
                sheets = theWorkbook.Worksheets;
                worksheet = (Excel.Worksheet)sheets.get_Item(1);
                cxRange = worksheet.UsedRange;
                string str;
                long lRowCount = cxRange.Rows.Count;
                long lColumnCount = cxRange.Columns.Count;
                for (int x = 1; x <= 1; x++)
                {
                    range = worksheet.get_Range("A" + x.ToString(), "G" + x.ToString());
                    System.Array myvalues = (System.Array)range.Cells.get_Value(null);
                    strArray = ConvertToStringArray(myvalues);
                }
                int fiedIndex = Array.IndexOf(strArray, fieldname);

                if (fiedIndex != -1)
                {
                    for (int y = 2; y <= lRowCount; y++)
                    {
                        str = (string)(cxRange.Cells[y,fiedIndex+1] as Excel.Range).Value2;
                        if (!string.IsNullOrEmpty(str))
                        {
                            fullArray.Add(str);
                        }
                    }
                }
                ExcelObj.Workbooks.Close();
                ExcelObj.Quit();
                ExcelObj = null;
                theWorkbook = null;
                sheets = null;
                range = null;
                cxRange=null;
            }
        }
        catch (Exception ex)
        {
            throw ex;
        }
        return sbReturn.ToString();
    }

谢谢。