如何从存储在内存中的excel表中读取值c#

debugcn 发表于 Dev

测试员

我正在尝试从 Excel 电子表格中获取单元格的值。该值存储在 sheet1 的单元格 A2 中，该单元格位于名为 Item 的列标题下。下面的代码总是将值返回为 null。请帮我获取存储在 A2 中的值。

using Excel;
using System;
using System.Collections.Generic;
using System.Data;
using System.IO;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

 namespace Test
 {
     public class ExcelUtil
     {
         public static DataTable ExcelToDataTable(string fileName)
         {
             FileStream stream = File.Open(fileName, FileMode.Open, FileAccess.Read);
             IExcelDataReader excelReader = ExcelReaderFactory.CreateOpenXmlReader(stream); //.xlsx
                        excelReader.IsFirstRowAsColumnNames = true;
              DataSet result = excelReader.AsDataSet();
              DataTableCollection table = result.Tables;
              DataTable resultTable = table["Sheet1"];

              return resultTable;
        }

        public static List<Datacollection> dataCol = new List<Datacollection>();

         public static void PopulateInCollection(string fileName)
         {
             DataTable table = ExcelToDataTable(fileName);
             //Iterate through all the rows and columns of the Table
             for (int row = 1; row <= table.Rows.Count; row++)
             {
                 for (int col = 0; col < table.Columns.Count; col++)
                 {
                     Datacollection dtTable = new Datacollection()
                     {
                         rowNumber = row,
                         colName = table.Columns[col].ColumnName,
                         colValue = table.Rows[row - 1][col].ToString()
                      };
                      //Add all the details for each row
                      dataCol.Add(dtTable);
                   }
               }
           }

       public static string ReadData(int rowNumber, string columnName)
       {
           try
           {
                //Retrieving Data using LINQ
                string data = (from colData in dataCol
                                           where colData.colName == columnName && colData.rowNumber == rowNumber
                                           select colData.colValue).SingleOrDefault();

                //var data = dataCol.Where(x => x.colName == columnName && x.rowNumber == rowNumber).SingleOrDefault().colValue;
                            return data.ToString();
           }
           catch (Exception e)
           {
               e.Message.ToString();
               return null;
            }
        }

        private static void mytest()
        {
            string itemNo = ExcelUtil.ReadData(1, "Item");
         }
    }

public class Datacollection
{
    public int rowNumber { get; set; }
    public string colName { get; set; }
    public string colValue { get; set; }
}
}

测试员

我找到了答案！我需要在上面的代码中添加以下 2 行：

            private static void mytest()
            {
                ExcelUtil util = new ExcelUtil();
                ExcelUtil.PopulateInCollection(@"c:\datalocation\Data.xlsx");

                string itemNo = ExcelUtil.ReadData(1, "Item");
            }

我还必须修改以下内容：

                    public static string ReadData(int rowNumber, string columnName)
            {
                try
                {
                    ////Retrieving Data using LINQ
                    var data = (from colData in dataCol
                                where colData.colName == columnName && colData.rowNumber == rowNumber
                                select colData.colValue).First().ToString();

                    //var data = dataCol.Where(x => x.colName == columnName && x.rowNumber == rowNumber).SingleOrDefault().colValue;
                    return data;
                }
                catch (Exception e)
                {
                    e.Message.ToString();
                    return null;
                }

本文收集自互联网，转载请注明来源。

如有侵权，请联系[email protected] 删除。