如何使用LinqToExcel获取excel文件中包含的单元格值

Question

我使用此代码来检索接收方名称和接收方编号，但recpt.receipient_name和recpt.receipient_number为空。

excel表的格式如下

名称编号

安德鲁1223

詹姆斯12223

戴夫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();
}

Answer 1

经过研究，我找到了一种用LinqToExcel从excel文件中获取值的方法，并获得了所有单元格的列表。查看此MVC C#示例。

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;
        }

    }
}

Answer 2

由于BulkSmsModel类上的属性名与电子表格中的列名没有直接关联，因此您需要将属性名映射到列名。

假设messages是ExcelQueryFactory对象，这就是代码。

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;