How to get excel table data in to a LIST in c#

Question

I want to get excel table values to a list in c#

here is my code

           [HttpPost]
    public ActionResult GetExcelData()
    {
        List<ExcelData> exData = new List<ExcelData>();

         string status;
        string fileparth;
        string json;
        using (var reader = new StreamReader(Request.InputStream))
        {
            json = reader.ReadToEnd();
        }
        JObject jo = (JObject)JsonConvert.DeserializeObject(json);
        fileparth = jo.Value<string>("uplaodFile");
        string conString = string.Empty;

        string extension = Path.GetExtension(fileparth);

        switch (extension)
        {
            case ".xls": //Excel 97-03
                conString = String.Format("Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties='Excel 8.0;HDR=YES'", fileparth);

                break;
            case ".xlsx": //Excel 07 or higher
                conString = String.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties='Excel 12.0;HDR=YES'", fileparth);

                break;

        }
      //  conString = string.Format(conString/*strConnection*/, fileparth/*FileUpload1*/);
        using (OleDbConnection excel_con = new OleDbConnection(conString))
        {
            excel_con.Open();
            string sheet1 = excel_con.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null).Rows[0]["TABLE_NAME"].ToString();
            DataTable dtExcelData = new DataTable();

            //[OPTIONAL]: It is recommended as otherwise the data will be considered as String by default.

            dtExcelData.Columns.AddRange(new DataColumn[3] { 
                new DataColumn("TRID", typeof(decimal)),
                 new DataColumn("Actual(km)", typeof(string)),
                  new DataColumn("Amount(Rs)", typeof(decimal))
            });
            using (OleDbDataAdapter oda = new OleDbDataAdapter("SELECT * FROM [" + sheet1 + "]", excel_con))
            {
                oda.Fill(dtExcelData);
            }
            excel_con.Close();
        }
    }

after excel_con.Close();

i need to go through a loop until the recourds are end in the sheet. then i need to add these values to

exData

these are the getters and setters

                 public class ExcelData
    {
        private decimal _RnEID;

        public decimal RnEID
        {
            get { return _RnEID; }
            set { _RnEID = value; }
        }
        private string _EActual;

        public string EActual
        {
            get { return _EActual; }
            set { _EActual = value; }
        }
        private string _EAmount;

        public string EAmount
        {
            get { return _EAmount; }
            set { _EAmount = value; }
        }
    }

here i the dummy excel sheet

please help me to solve this matter

thank you all.

Answer 1

Loading the DataTable is unnecessary if you want to return a List<ExcelData>.

Effectively a DataAdapter.Fill method loops over the returned data using an internal OleDbDataReader and fills the DataTable. Now, if you want to return a List<ExcelData> you need to loop again on the table and build an element of type ExcelData for every row in the table.

So, you could do it yourself without the loop required by the adapter fill method.

    List<ExcelData> exData = new List<ExcelData>();
    .....

    using (OleDbConnection excel_con = new OleDbConnection(conString))
    using (OleDbCommand cmd = new OleDBCommand())
    {
        excel_con.Open();
        string sheet1 = excel_con.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null).Rows[0]["TABLE_NAME"].ToString();

        cmd.CommandText = "SELECT * FROM [" + sheet1 + "]";
        cmd.Connection = excel_con;
        using (OleDbDataReader reader = cmd.ExecuteReader())
        {
             ExcelData curLine = new ExcelData();
             curLine.RnEID = (reader.IsDbNull(0) ? 0m : Convert.ToDecimal(reader[0]));
             curLine.EActual = (reader.IsDbNull(1) ? "" : reader[1].ToString());
             curLine.EAmount = (reader.IsDbNull(2) ? 0m : Convert.ToDecimal(reader[2]));
             exData.Add(curLine);
        }
    }
    return exData;

Having empty cells could cause an error message for null values. You could check and prevent the error message using OleDbDataReader.IsDbNull method