Unity 使用Linq解析Excel

Linq to Excel 是一个 .Net 库能够让你使用LINQ语法来查询Excel电子表格。

添加LinqToExcel 到你的工程

NuGet自动添加

You can use NuGet to quickly add LinqToExcel to your project. Just search for linqtoexceland install the package.

手动添加

如果你不想使用 NuGet ，那么你可以 Download ，解压后把下面两个.dll文件添加到你的工程  

* LinqToExcel.dll 
* Remotion.Data.Linq.dll

x64 Support

If you want LinqToExcel to run in a 64 bit application, make sure to use the 64 bit version of the library.

You will also need to make sure to have the 64 bit version of the Access Database Engineinstalled on the computer.

按行查询

The default query expects the first row to be the header row containing column names that match the property names on the generic class being used. It also expects the data to be in the worksheet named "Sheet1".

var excel = new ExcelQueryFactory("excelFileName");
var indianaCompanies = from c in excel.Worksheet<Company>()
                       where c.State == "IN"
                       select c;

按名字查询

Data from the worksheet named "Sheet1" is queried by default. To query a worksheet with a different name, pass the worksheet name in as an argument.

var excel = new ExcelQueryFactory("excelFileName");
var oldCompanies = from c in repo.Worksheet<Company>("US Companies") //worksheet name = 'US Companies'
                   where c.LaunchDate < new DateTime(1900, 1, 1)
                   select c;

查询符合条件的数据

还可以对输出的结果进行过滤，比如找到年龄大于12岁的，需要加上条件。

var excel=excelFile.Worksheet<Student>().Where(entity=>entity.Age>12);

手动添加映射

如果实体类里面定义的字段跟Excel里面的列名不一一对应，也没关系，我们可以手动添加映射，比如我们把Student里面的字段名都加上一个str的前缀，手动添加映射也是可以读取的。

void Read()
{
    var fileName=System.IO.Path.Combine(@"D:\","学生基本信息.xlsx");
    var excelFile=new ExcelQueryFactory(fileName);
     
     excelFile.AddMapping<Student>(d=>d.strName,"Name");
     excelFile.AddMapping<Student>(d=>d.strNo,"No");
     excelFile.AddMapping<Student>(d=>d.strAge,"Age");
    
    //不传参数，默认读取的就是sheet1里面的数据（括号中可以传入字符串，也可以是索引）
    //var excel=excelFile.Worksheet<Student>();
    var excel=excelFile.Worksheet<Student>().Where(entity=>entity.strAge>12);
    
    foreach (var element in excel)
    {
        element.Dump();
    }
}
 
public class Student
{
    public string strName {get;set;}
    public string strNo {get;set;}
    public int strAge {get;set;}
}

其他用法

读取某个范围内单元格的数据。（还可以读取csv文件，写法跟Excel一样）

var excel=excelFile.WorksheetRange<Student>("A1","C4").Where(entity=>entity.strAge>12);

项目主页：https://github.com/paulyoder/LinqToExcel

linqtoexcel文件下载 ：https://code.google.com/archive/p/linqtoexcel/downloads