0

Have used ExcelDataReader NuGET Package.

This is the class setting it up -

    private static DataTable ExcelToDataTable(string fileName)
    {
        //open file and returns as Stream
        FileStream stream = File.Open(fileName, FileMode.Open, FileAccess.Read);
        //Createopenxmlreader via ExcelReaderFactory
        IExcelDataReader excelReader = ExcelReaderFactory.CreateOpenXmlReader(stream); //.xlsx
                                                                                       //Set the First Row as Column Name
        excelReader.IsFirstRowAsColumnNames = true;
        //Return as DataSet
        DataSet result = excelReader.AsDataSet();
        //Get all the Tables
        DataTableCollection table = result.Tables;
        //Store it in DataTable
        DataTable resultTable = table["Sheet1"];
        //return
        return resultTable;
    }
   static List<Datacollection> dataCol = new List<Datacollection>();

public static void PopulateInCollection(string fileName)
    {
        DataTable table = ExcelToDataTable(fileName);

        //Iterate through 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
        {
            //Retriving Data using LINQ to reduce much of iterations
            string data = (from colData in dataCol
                           where colData.colName == columnName && colData.rowNumber == rowNumber
                           select colData.colValue).SingleOrDefault();

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


}

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

}


It called in the code like -

   //setting the excel file location
ExcelLib.PopulateInCollection(@"C:\Users\User1\Documents\data.xlsx");
//reading data from row 1 & column named middlename
xrmApp.Entity.SetValue("middlename", ExcelLib.ReadData(1, "middlename"));

I want to be able to use this code in different method and for each method I need different data. So is their anyway I can add a worksheet number parameter to the code so I can specify which worksheet to look into when reading the row number and column number.

Thanks!

HH44
  • 15
  • 1
  • 2
  • 10
  • 1
    In order to solve your problem better, I need to confirm something with you. What is your final goal? The topic is that you want to read data, but in the code Entity.SetValue you are setting the value. Therefore, I am confused. If you want to read data from many sheets in the same excel file, I suggest that you can convert your current excel to dataset, then we only need to read the datatable in the dataset. Upon you can achieve the data in the datatable, we can achieve any data in the exel file. – Jack J Jun Apr 22 '20 at 06:44
  • @JackJJun-MSFT.. I will be reading a cell value from excel which will contain a word and then I am setting the word in my code with " ExcelLib.ReadData(1, "middlename") " .. so this is reading row 1 and column name off the excel sheet to get the word from that cell. For now I can only read from one worksheet so what I want is for there to be another parameter and I can set the worksheet number along with row and column name. I hope this clears it up, thanks – HH44 Apr 24 '20 at 09:37

1 Answers1

0

Based on your description, you want to read the data from different worksheet in the same

excel file.

I suggest that you can convert the excel file to dataset and choose the datatable from the

dataset.

Like the following code:

Before that, please install nuget package ExcelDataReader and ExcelDataReader.DataSet both.

 class Program
 {
    static void Main(string[] args)
    {
        DataSet set = ExcelToDataSet("D:\\test.xlsx");
        DataTable table = set.Tables[0];   // first sheet
        string data = ReadData(table, 0, "TeacherName");// the first row and colunname is teachername
        Console.WriteLine(data);
        table = set.Tables[1];             //second sheet
        data = ReadData(table, 0, "StuName");// the first row and colunname is stuname
        Console.WriteLine(data);
        Console.ReadKey();

    }

    private static DataSet ExcelToDataSet(string fileName)
    {
        //open file and returns as Stream
        FileStream stream = File.Open(fileName, FileMode.Open, FileAccess.Read);
        //Createopenxmlreader via ExcelReaderFactory
        IExcelDataReader excelReader = ExcelReaderFactory.CreateOpenXmlReader(stream); //.xlsx
                                                                                       //Set the First Row as Column Name
        DataSet result = excelReader.AsDataSet(new ExcelDataSetConfiguration()
        {
            ConfigureDataTable = (_) => new ExcelDataTableConfiguration()
            {
                UseHeaderRow = true
            }
        });
        //Get all the Tables
        //return
        return result;
    }

    static string ReadData(DataTable table,int row,string columnname)
    {
        return table.Rows[row].Field<string>(columnname).ToString();
    }


}

I created the following excel:

First sheet:

enter image description here

Second sheet:

enter image description here

If I run the above code, you will get the value of first row and first column of two sheet. enter image description here

Jack J Jun
  • 5,633
  • 1
  • 9
  • 27