读取Excel表格内容

读取Excel表格内容

/// <summary>
/// 读取excel文件内容
/// </summary>
/// <param name="filePath">文件路径</param>
/// <param name="columnNum">行数</param>
/// <param name="rowNum">列数</param>
/// <returns></returns>
public DataRowCollection ReadExcel(string filePath, ref int columnNum, ref int rowNum)
{
    FileStream stream = File.Open(filePath, FileMode.Open, FileAccess.Read, FileShare.Read);
    IExcelDataReader excelReader = ExcelReaderFactory.CreateOpenXmlReader(stream);

    DataSet result = excelReader.AsDataSet();
    //Tables[0] 下标0表示excel文件中第一张表的数据
    columnNum = result.Tables[0].Columns.Count;
    rowNum = result.Tables[0].Rows.Count;
    return result.Tables[0].Rows;
}

/// <summary>
/// 读取excel表数据，生成对应数组
/// </summary>
/// <param name="excelPath">名称+ID表路径</param>
/// <returns></returns>
public Dictionary<string,string> CreateArrayByExcel(string excelPath, int valueCol,int keyCol = 0)
{
    Dictionary<string, string> dic = new Dictionary<string, string>();

    int columnNum = 0, rowNum = 0;

    string dicValue = "", dicName = "";

    excelPath = excelRootPath + excelPath + ".xlsx";

    DataRowCollection collect = ReadExcel(excelPath, ref columnNum, ref rowNum);

    for (int i = 1; i < rowNum; i++)
    {
        dicName = collect[i][keyCol].ToString();
        dicValue = collect[i][valueCol].ToString();

        if (dic.ContainsKey(dicName))
        {
            dic[dicName] = dicValue;
        }
        else
        {
            dic.Add(dicName, dicValue);
        }
    }
    return dic;
}