How to read a excel file?

Question

I have a excel file that look like this:

first Name       last Name
John             Doe
Sara             Boo

            string PATH = Directory.GetFiles(@"C:\file.xls);
        string connection = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + PATH + ";Extended Properties=Excel 12.0";



        OleDbConnection objConn = new OleDbConnection(connection);
        OleDbCommand objCmd = new OleDbCommand(string.Format(@"Select * From [Sheet1$A1:A43]"), objConn);
        OleDbDataAdapter objDatAdap = new OleDbDataAdapter();
        objDatAdap.SelectCommand = objCmd;
        DataSet ds = new DataSet();
        objDatAdap.Fill(ds);

I want to only get the values "John" and "Sara" from the file and do a compare method on it to see they match or not. any help?

If you read the data into a datatable, you could probably look at each row's first cell value I suspect. — JB King, May 13 '15 at 18:03
There are literally hundreds of existing questions (and answers) here about reading Excel files from C#. Have you searched for any of them and made any effort to figure this out yourself? Search for `[c#] read excel files`. — Ken White, May 13 '15 at 18:08
Ever heard of Linq to Excel. see [Create a list of objects that directly represents an Excel sheet][1] [1]: http://stackoverflow.com/questions/29482949/create-a-list-of-objects-that-directly-represents-an-excel-sheet — moi_meme, May 13 '15 at 18:09
I got hung up here System.Data.OleDb.OleDbException (0x80004005): External table is not in the expected format. — NewKidOnTheBlock, May 13 '15 at 18:09
Your question contains zero information about previous attempts, an error you received, or any code that we can help you fix. — Ken White, May 13 '15 at 18:10
There are several ways to get data from Excel. One way is to use the "Microsoft Excel 12.0 Object Library" http://csharp.net-informations.com/excel/csharp-read-excel.htm — Carl Prothman, May 13 '15 at 18:18
Show some code and we will be able to tell you what you did wrong. — Fjodr, May 13 '15 at 18:22
possible duplicate of [Reading Excel files from C#](http://stackoverflow.com/questions/15828/reading-excel-files-from-c-sharp) — Eugene Podskal, May 13 '15 at 19:02

Rafał Straszewski · Accepted Answer · 2015-05-13T20:47:15.937

You can use for example library LinqToExcel (https://github.com/paulyoder/LinqToExcel - or nuget) And use linq for comparison.

Class for mapping

public class Person
{
    public string Name { get; set; }
    public string Surname { get; set; }
}

And example of library usage

var excel = new ExcelQueryFactory("e.xlsx");//file name
excel.AddMapping<Person>(p => p.Name, "First Name");//mapping property to column
excel.AddMapping<Person>(p => p.Surname, "Last Name");//mapping property to column
var ppl = excel.Worksheet<Person>("Arkusz1").ToList();//Workspace name as parameter
var areTheSame = ppl.All(p => p.Name == ppl.First().Name);// Are all equal to first element

Edit: You can sort elements by property using this code:

var ppl = excel.Worksheet<Person>("Arkusz1").OrderBy(p => p.Name).ToList();//Workspace name as parameter, sort by Name and return as list

And to compare first and second element, you can just use this:

var areTheSame = ppl[0].Name == ppl[1].Name;

EDIT 2: I am not sure what you mean...you don't know how to compare objects? Your excel worksheet is mapped to list of objects - in this example list of objects type Person. Elements in list match rows, so ppl[0] is first row, ppl[1] second row etc. (of course before sorting, after sorting, the order is different). To compare objects you can use Equal method (you can define your own way of compare objects and implement IEquatable in class - more here https://msdn.microsoft.com/pl-pl/library/ms131190(v=vs.110).aspx)

I want to sort column First Name, and compare first name with the second name in that COLUMN. how do i do that? — NewKidOnTheBlock, May 13 '15 at 18:59
I'm getting this error after loading the excel. System.Data.OleDb.OleDbException (0x80040E57): The field is too small to accept the amount of data you attempted to add. Try inserting or pasting less data. — NewKidOnTheBlock, May 14 '15 at 15:10

score 0 · Answer 2 · edited May 23 '17 at 11:51

I will firstly suggested the quickest and easiest answer would be a formula in the third column something like =IF(A1=A2,"Match","No-Match" (Psuedo-code). So this would be a solution in Excel without the need for C#.

Otherwise the best way I have found to approach this is to save the file as .csv and then use standard C# code to do this.

To actually answer your question directly you can query Excel files as a datasource, example code taken from previous answer (Query excel sheet in c#):

OleDbConnection con = new OleDbConnection(
                "provider=Microsoft.Jet.OLEDB.4.0;data source="
                + XLS_FILE_NAME_AND_PATH_HERE
                + ";Extended Properties=Excel 8.0;");

            StringBuilder stbQuery = new StringBuilder();
            stbQuery.Append("SELECT * FROM [" + SHEETNAME_HERE + "$A1:D1]");
            OleDbDataAdapter adp = new OleDbDataAdapter(stbQuery.ToString(), con);

            DataSet dsXLS = new DataSet();
            adp.Fill(dsXLS);

            DataView dvEmp = new DataView(dsXLS.Tables[0]);

            dataGridView1.DataSource = dvEmp;

How to read a excel file?

2 Answers2