none
Retrieve cell value via column name via DataSet with ExcelDataReader RRS feed

  • Question

  • I need to read all rows in a *.xlsx file by looping each row and read each cell value within a row, by using column name like below.

         public  void ReadCSV(byte[] content){
         
         MemoryStream stream = new MemoryStream();
         stream.Write(content, 0, content.Length);
         stream.Position = 0;
        
          IExcelDataReader excelReader = ExcelReaderFactory.CreateReader(stream);
           DataSet result = excelReader.AsDataSet();
        
        //exception occurs below:
        result.Tables[0].Rows[1]["ColA"]

        
        }

    However, an error occurs.

        System.ArgumentException
          HResult=0x80070057
          Message=Column 'ColA' does not belong to table Sheet1.
          Source=System.Data.Common
          StackTrace:
           at System.Data.DataRow.GetDataColumn(String columnName)
           at System.Data.DataRow.get_Item(String columnName)

    Below works, but the column name is not the name on excel file.

        result.Tables[0].Rows[1]["Column0"]



    Below results in `not implemented` exception, can anyone confirm this is correct?

          var aIndex = excelReader.GetOrdinal("ColA");  
          var c = (string)excelReader["ColA"];


      [1]:
    • Edited by Pingpong689 Saturday, August 17, 2019 2:38 PM
    Friday, August 16, 2019 10:08 PM

All replies

  • To figure out the names of columns, set a breakpoint on the next line of code after

    DataSet result = excelReader.AsDataSet();

    Then hover your mouse over the line above, a hover window appears which provides a way to view the DataSet with a ComboBox on top to select the DataTable which will reveal column names. My bet is ColA does not appear in the columns shown.


    Please remember to mark the replies as answers if they help and unmarked them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.

    NuGet BaseConnectionLibrary for database connections.

    StackOverFlow
    profile for Karen Payne on Stack Exchange

    Friday, August 16, 2019 11:08 PM
    Moderator
  • Hi 

    Thank you for posting here.

    For some reasons, my reply has been deleted. You could try the following code to solve your problem.

      private void Form1_Load(object sender, EventArgs e)
            {
                byte[] b = File.ReadAllBytes(@"D:\t.xlsx");
                var m = ReadCSV(b);
            }
            public string ReadCSV(byte[] content)
            {
    
                MemoryStream stream = new MemoryStream();
                stream.Write(content, 0, content.Length);
                stream.Position = 0;
    
                IExcelDataReader excelReader = ExcelReaderFactory.CreateReader(stream);
                var result = excelReader.AsDataSet(new ExcelDataSetConfiguration()
                {
                    ConfigureDataTable = (_) => new ExcelDataTableConfiguration()
                    {
                        UseHeaderRow = true
                    }
                });
    
                string a =result.Tables[0].Rows[1]["Date"].ToString();
                return a;
    
        }

    Best Regards,

    Jack


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Wednesday, August 21, 2019 8:47 AM
    Moderator