CODEWITHSUNDEEP
c#excelwinforms
Dan
Dan

Reputation: 39

Process two large Excel files

Task: Create a Windows Forms application in C# that will take two Excel files (about 50 mb each with ~300k rows) that were exported from two different databases containing data about clients.

In a screenshot below I tried to explain visually what is needed:

enter image description here]

The problem is with loading large files! First I thought I could do this with oledb and load data into datatables and process them in memory, but it takes a long time and uses a lot of memory:

OleDbConnection con1 = new OleDbConnection(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + filePath1 + ";Extended Properties=Excel 12.0");
OleDbDataAdapter da1 = new OleDbDataAdapter("select * from [Sheet1$]", con1);

DataTable dt1 = new DataTable();
da1.Fill(dt1);
MessageBox.Show("filled");

And if takes so much just to load 1 file I can't image how long it will take to process and compare rows... Can anybody suggest a better way to complete this task?

Upvotes: 1

Views: 149

Answers (1)

Christian Sauer
Christian Sauer

Reputation: 10899

There is an easy way - use the library EPPlus. It parsing and reading is quite fast.

Then you can transform your data to a DataTable (.toDataTable()) and compare them using built in tools.

Even your output document can be easily generated in DataTables, which can be easily converted to excel using EPPLUS.

Upvotes: 4

Related Questions