How do i use LinqToExcel to get cell values contained in the excel file

Question

I am using this code to retrieve the receipient name and receipient number but the recpt.receipient_name and recpt.receipient_number are null.

The excel table is of this format

Name Number

andrew 1223

james 12223

dave 454545

//select names from the excel file with specified sheet name
var receipients = from n in messages.Worksheet<BulkSmsModel>(sheetName)
                  select n;

foreach (var recpt in receipients)
{
    BulkSms newRecpt = new BulkSms();

    if (recpt.receipient_number.Equals("") == true || recpt.receipient_number == 0)
    {
        continue;
    }

    newRecpt.receipient_name = recpt.receipient_name;
    newRecpt.receipient_number = Int32.Parse(recpt.receipient_number.ToString());

    IBsmsRepo.insertReceipient(newRecpt);
    IBsmsRepo.save();
}

You haven't given us any idea what is in the spreadsheet so maybe there is not data for that particular row for those columns — Ben Robinson, Sep 07 '11 at 09:42
there is data in the spreadsheet. it contains two named headers(name and number) and under these are various names and corresponding numbers — plasteezy, Sep 07 '11 at 09:48
So how many items in recipients and if there are more than one then are the properties null in all of them? — Ben Robinson, Sep 07 '11 at 09:52
yes the properties are null in all of the 10 rows in the recipient — plasteezy, Sep 07 '11 at 10:50
@plasteezy: could you please add a picture to your question with the way the sheet is structured? and also show us code for BulkSmsModel and BulkSms — Răzvan Flavius Panda, Sep 07 '11 at 12:37
@ravan panda...i have updated the question. i dont have enough points to upload an image so the edit portrays the excel sheet i have — plasteezy, Sep 07 '11 at 14:07

Ruben · Answer 1 · 2020-05-14T22:00:49.283

After some research I found a way to get the value from excel file with LinqToExcel and get the list of all Cells. Check this MVC C# Sample.

using LinqToExcel;
using Syncfusion.Olap.Reports;
using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data.OleDb;
using System.Linq;
using System.Web;
using System.Web.Mvc;

namespace YourProject.Controllers
{
    public class DefaultController : Controller
    {
        // GET: Default1
        public ActionResult Index()
        {
            return View();
        }
        public dynamic UploadExcel(HttpPostedFileBase FileUpload)
        {
            string PathToyurDirectory = ConfigurationManager.AppSettings["Path"].ToString();//This can be  in Anywhere, but you have to create a variable in WebConfig AppSettings like this  <add key="Path" value="~/Doc/"/> This directory in my case is inside App whereI upload the files here, and  I Delete it  after use it ; 
           if (FileUpload.ContentType == "application/vnd.ms-excel"
                            || FileUpload.ContentType == "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"
                            || FileUpload.ContentType == "application/vnd.ms-excel.sheet.binary.macroEnabled.12"
                            )
            {
                string filename = FileUpload.FileName;
                string PathToExcelFile = Server.MapPath(PathToyurDirectory + filename);
                //   string targetpath = ;
                FileUpload.SaveAs(PathToyurDirectory);
                var connectionString = string.Empty;
                string sheetName = string.Empty;
                yourmodel db = new yourmodel();
                Employee Employee = New Employee(); //This is your class no matter What. 
                try
                {

                    if (filename.EndsWith(".xls") || filename.EndsWith(".csv") || filename.EndsWith(".xlsx") || filename.EndsWith(".xlsb"))
                    {

                        connectionString = string.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=\"Excel 12.0 Xml;HDR=YES;IMEX=1\";", PathToExcelFile);
                        sheetName = GetTableName(connectionString);
                    }
                    var ExcelFile = new ExcelQueryFactory(PathToExcelFile);
                    var Data = ExcelFile.Worksheet(sheetName).ToList();

                    foreach (var item in Data)
                    {
                        //if yout excel file does not meet the class estructure.
                        Employee = new Employee
                        {

                            Name = item[1].ToString(),
                            LastName = item[2].ToString(),
                            Address = item[3].ToString(),
                            Phone = item[4].ToString(),
                            CelPghone = item[5].ToString()
                        };
                         db.Employee.Add(Employee);
                        db.SaveChanges();
                    }
                }
                catch (Exception)
                {
                    throw;
                }

            }

            return View();
        }

        private string GetTableName(string connectionString)
        {
            //  You can return all Sheets for a Dropdown if you want to, for me, I just want the first one; 
            OleDbConnection oledbconn = new OleDbConnection(connectionString);
            oledbconn.Open();
            // Get the data table containg the schema guid.
            var dt = oledbconn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
            var sheet = dt.Rows[0]["TABLE_NAME"].ToString().Replace("$", string.Empty);
            oledbconn.Close();
            return sheet;
        }

    }
}

score 0 · Answer 2 · answered Dec 16 '11 at 03:16

Since the property names on the BulkSmsModel class do not correlate directly to the column names in the spreadsheet, you will need to map the property names to the column names.

Assuming messages is the ExcelQueryFactory object, this would be the code.

var messages = new ExcelQueryFactory("excelFileName");
messages.AddMapping<BulkSmsModel>(x => x.receipient_name, "Name");
messages.AddMapping<BulkSmsModel>(x => x.receipient_number, "Number");

var receipients = from n in messages.Worksheet<BulkSmsModel>(sheetName)
                  select n;

How do i use LinqToExcel to get cell values contained in the excel file

2 Answers2