Mego-Net simple implementation of Excel import and export

Objective

I believe that friends who have done information systems will encounter Excel import and export related development, did a lot of Excel import and export after summing up roughly there are several ways to achieve:

• Ado. NET OLDDB or ODBC connection Excel uses a DataTable to read data.
• Microsoft.Office.Interop.Excel operates worksheet objects with components provided by Microsoft.
• Use some third-party libraries such as Fast Excel, Exceldatareader, and so on.

Today to introduce you to the more simple way to achieve the daily development of a variety of Excel import and export requirements.

Simple Import

We still use the System.Data.OleDb in the ADO as the bottom layer, this way can be very efficient. Define an object to host the data first.
c# public class Product { public int Id { get; set; } public string Code { get; set; } public string Name { get; set; } public int Category { get; set; } public bool IsValid { get; set; } }C#
Then declare a connection string model as follows

private const string conStr =    @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=‘Excel 12.0 Xml;HDR=YES‘";

Finally, declare a context that accesses Excel

public class ExcelContext : DbContext{    public ExcelContext(string filename)        : base(string.Format(conStr, filename), "System.Data.OleDb.Excel")    {        this.Configuration.EnableAutoConversionStorageTypes = true;    }    public DbSet<Product> Products { get; set; }}

The preparation work is done here, and then we can extract the data from the data source as follows:

using (var context = new ExcelContext("sample.xls")){    var data = context.Products.ToArray();}

As long as two lines of code can get the data into net object, if there is a context to access the business database can directly import data, such as the following temporary code:

using (var context = new OracleContext()){    context.Products.AddRange(data);    context.Executor.Execute();}

Here we are able to complete the work of extracting from Excel and importing data to the database in a very simple way.

Universal Import

You might consider that there are many formats for importing Excel, and you cannot define a context and data object class every time, and here we can define a common way to read Excel.
We also use the above connection string to define a common data context.

public class AnonymouExcelContext : DbContext{    public AnonymouExcelContext(string filename)        : base(string.Format(conStr, filename), "System.Data.OleDb.Excel")    {        this.Configuration.EnableAutoConversionStorageTypes = true;    }}

Then we use C # 's anonymous object to read the data.

using (var context = new ExcelContext("sample.xls")){    var item = new { Id = 1, Name = "P", IsValid = false };    var data = context.Set(item, "Products").Where(a => a.Id > 20).ToArray();}

We first define an anonymous object, which is actually declaring the field to be imported in an anonymous form, and the advantage of using an anonymous type is that you can do LINQ operations, such as the code above.

Exporting data

Exporting Excel is also a hassle, first you need to write the table header, and then write the data, perhaps in different scenarios you need to write the export of important code this is particularly serious when using Microsoft.Office.Interop.Excel export. Here we still use the data context above to export the data.
Let's start by creating some data for export.

Random r = new Random();var products = Enumerable.Range(0, 1000).Select(i => new Product(){    Id = i,    Name = "Product " + i.ToString(),    Category = r.Next(1, 10),    Code = "P" + i.ToString(),    IsValid = true});

We need to create a blank Excel file, where the code is not declared.
The end is to write the table header and the contents:

using (var context = new ExcelContext(filename)){    var operate = context.Database.Manager.CreateTable<Product>();    context.Executor.Execute(operate);//创建表头    context.Products.AddRange(products);    context.Executor.Execute();//写入数据}

The same can be done with the same anonymous object,
Create data

Random r = new Random();var items = Enumerable.Range(0, 1000).Select(i => new{    Id = i,    Name = "Product " + i.ToString(),    Category = r.Next(1, 10),    IsValid = true}).ToArray();

Write Data

using (var context = new ExcelContext(filename)){    var item = items[0];    var operate = context.Database.Manager.CreateTable(item.GetType(),        DbName.NameOnly("Sheet1$"));    context.Executor.Execute(operate);    context.Set(item, "[Sheet1$]").AddRange(items);    context.Executor.Execute();}

Complex Excel Import

The above code has been able to meet most of the development needs, but the business requirements are always endless, do not know the following Excel import case everyone has encountered.
Customers need to import tens of thousands of orders and detailed data, before the official import into the database, but also in the system interface to browse the confirmation and modification, confirm the error before sending commands to the database. (The most troublesome thing is that this is a web-based system.)

Uploading Excel is not necessary, but browsing changes can be a bit tricky, but based on a good user experience, you need to save excel in a temporary location on the server, then page the data to the user and provide modification functionality, and then submit to the database when the user confirms it.
First we start by creating a relatively complex data context.

internal class ComplexContext : DbContext{    public ComplexContext(string filename)        : base(string.Format(conStr, filename), "System.Data.OleDb.Excel")    {        this.Configuration.EnableAutoConversionStorageTypes = true;    }    public DbSet<Order> Orders { get; set; }    public DbSet<OrderDetail> OrderDetails { get; set; }    public DbSet<Customer> Customers { get; set; }    public DbSet<Product> Products { get; set; }}

This ignores the definition of the data class, where the relationship between the data is the order has a number of details, order relationship a customer, the details of a product, for Excel this is already very complex.
But here you can easily query all orders and order details, filter and page to display data to the user, as shown below

using (var context = new ComplexContext("sample.xls")){    var query = from a in context.Orders.Include(a=>a.Details)                where a.Id > 4                select a;    var items = query.Take(10).Skip(20).ToArray();}

Let's go directly to the last figure to prove the correctness of the data.

The above code has been uploaded to GitHub.

All of these are based on MEGO Framework implementation of the Excel operation, of course Mego also support many databases, welcome to try.

Mego-Net simple implementation of Excel import and export