如何按名称访问XLSheet列数据？ [英] How to access XLSheet column data by name?

问题描述

  string filepath =C： \\SAddresses.xls; 
 
 XLApp = new Excel.Application（）; 
 XLBook = XLApp.Workbooks.Open（filepath，Type.Missing，Type.Missing，Type.Missing，Type.Missing，Type.Missing，Type.Missing，Type.Missing，Type.Missing，Type.Missing，型号，型号，型号，型号，型号，型号，型号，型号等）; 
 XLSheet =（Excel.Worksheet）XLBook.Worksheets.get_Item（1）; 
 cRange = XLSheet.UsedRange; （int rowcnt = 2; rowcnt< cRange.Rows.Count; rowcnt ++）
 {
 for（int colidx = 1; colidx< cRange .Columns.Count; colidx ++）
 {
 colvalue + =（（cRange.Cells [rowidx，colidx] as Excel.Range）.Value2）; 
} 
} 
  

在上面我可以使用每行索引和列索引。但是我想使用列名称而不是列索引读取列数据。如何执行此操作。

解决方案

以下是ExelTable类的摘录，我一直在使用一段时间：

  class ExcelTable 
 {
 public const int SignificantCharsInVariableName = 10; 
 
 private string [] columnNames = null; 
 private string [，] data = null; 
 
 private string [] DefineColumnNames（string [，] R）
 {
 string [] names = new string [R.GetLength（1）]; （int i = 0; i< R.GetLength（1）; i ++）
 {
 names [i] = R [0，i] .Trim ）; 
} 
 
返回名称; 
} 
 
 
 public string GetValue（int row，string varName）
 {
 string s; 
 int col 
 
 try 
 {
 col = GetColumn（varName）; 
 
 s = data [row，col] .Trim（）; 
} 
 catch（异常）
 {
 s =???; 
} 
 
 return s; 
} 
 
 
 private int GetColumn（string name）
 {
 return GetColumn（name，this.columnNames，obligatory：true）; 
} 
 
 private int GetColumn（string name，string [] names，bool obligatory = false）
 {
 string nameStart; 
 
 //首先尝试完美匹配
 for（int i = 0; i< names.Length; i ++）
 if（names [i] .Equals（name） 
 return i; 
 
 //第二次尝试仅匹配有效字符
 nameStart = name.PadRight（SignificantCharsInVariableName，''）.Substring（0，SignificantCharsInVariableName）; （int i = 0; i< names.Length; i ++）
 if（names [i] .StartsWith（nameStart））
 return i; 
 
 if（obligatory）
 {
 throw new Exception（Undefined Excel sheet column+ name +'）; 
} 
 
 return -1; 
} 
 
} 
  

数据以命名列。名称是数据数组顶行中的第一个单元格。示例代码尝试处理不正确的名称匹配和其他错误情况。可以使用更多聪明的集合，如 Dictionary< string，int> 来存储列名和列索引之间的映射。

我的GetColumn（）函数允许非强制列。 GetValue（）用于返回给定行按名称的值。如果没有找到列，则返回问号。

My application is reading the excel file data using Interop libraries.My code is below.

string filepath = "C:\\SAddresses.xls";

XLApp=new Excel.Application();
XLBook = XLApp.Workbooks.Open(filepath, 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);
XLSheet = (Excel.Worksheet)XLBook.Worksheets.get_Item(1);
cRange = XLSheet.UsedRange;


for (int rowcnt = 2; rowcnt < cRange.Rows.Count; rowcnt++)
{
    for (int colidx = 1; colidx < cRange.Columns.Count; colidx++)
    {
        colvalue+=((cRange.Cells[rowidx,colidx] as Excel.Range).Value2);
    }
}

In the above I am able to get the data using each row index and column index.But I want to read the column data using column name instead of column index.How to do this.

解决方案

Here is an excerpt from an ExelTable class, I have been using for a while:

class ExcelTable
{
    public const int SignificantCharsInVariableName = 10;

    private string[] columnNames = null;
    private string[,] data = null;

    private string[] DefineColumnNames(string[,] R)
    {
        string[] names = new string[R.GetLength(1)];

        for (int i = 0; i < R.GetLength(1); i++)
        {
            names[i] = R[0, i].Trim();
        }

        return names;
    }


    public string GetValue(int row, string varName)
    {
        string s;
        int col;

        try
        {
            col = GetColumn(varName);

            s = data[row, col].Trim();
        }
        catch (Exception)
        {
            s = "???";
        }

        return s;
    }


    private int GetColumn(string name)
    {
        return GetColumn(name, this.columnNames, obligatory: true);
    }

    private int GetColumn(string name, string[] names, bool obligatory = false)
    {
        string nameStart;

        //  first try perfect match
        for (int i = 0; i < names.Length; i++)
            if (names[i].Equals(name))
                return i;

        //  2nd try to match the significant characters only
        nameStart = name.PadRight(SignificantCharsInVariableName, ' ').Substring(0, SignificantCharsInVariableName);
        for (int i = 0; i < names.Length; i++)
            if (names[i].StartsWith(nameStart))
                return i;

        if (obligatory)
        {
            throw new Exception("Undefined Excel sheet column '" + name + "'");
        }

        return -1;
    }

}

The data is organized in named columns. The name is the first cell in the top row of the data array. The sample code attempts to deal with inperfect name matches and other error situations. One could use more clever collections like Dictionary<string, int> to store the mapping between column name and column index.

My GetColumn() function allows non-obligatory columns. It is used by GetValue() to return a value in a given row "by name". Questionmarks are returned, if the column was not found.

这篇关于如何按名称访问XLSheet列数据？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！