by
210 9
4
7
3,864
1
Top 1% !
Popular
Famous
Tagged
Easy-to-find
Specified
OpenSource
Popularity: 2796th place
Created
Modified Jul 23, 2014

Published on:

.NETFramework4.5
Languagecsharp
LicenseMIT_X11

Reading Excel file and inserting into List with Serialization

This class reads a sheet from excel file and insert into a list by matching the column names at sheet with member names of the class.
Copy Embed Code
<iframe id="embedFrame" style="width:600px; height:300px;"
src="https://www.snip2code.com/Embed/96336/Reading-Excel-file-and-inserting-into-Li?startLine=0"></iframe>
Click on the embed code to copy it into your clipboard Width Height
Leave empty to retrieve all the content Start End
using System.Data; using System.Data.Odbc; using System.Reflection; using System.Runtime.Remoting; using System; using System.Collections.Generic; public class ExcelReader { public List<DataItem> DataList = new List<DataItem>(); public List<AnotherDataItem> AnotherDataList = new List<AnotherDataItem>(); string fileName; string con; public ExcelReader() { fileName = "a.xls"; con = "Driver={Microsoft Excel Driver (*.xls)};DriverId=790;Dbq=" + fileName + ";"; ReadAndFill<DataItem>("DataSheet", DataList); ReadAndFill<AnotherDataItem>("AnotherDataSheet", AnotherDataList); } void ReadAndFill<T>(string sheetName, List<T> list) { DataTable table = this.FillDataTable(sheetName); this.FillDataList<T>(table, list); table.Clear(); } DataTable FillDataTable(string sheetName) { string query = "SELECT * FROM [" + sheetName + "$]"; OdbcConnection oCon = new OdbcConnection(con); OdbcCommand oCmd = new OdbcCommand(query, oCon); DataTable dataTable = new DataTable(sheetName); oCon.Open(); try { OdbcDataReader rData = oCmd.ExecuteReader(); dataTable.Load(rData); rData.Close(); oCon.Close(); } catch ( Exception ) { oCon.Close(); } return dataTable; } void FillDataList<T>(DataTable table, List<T> list) { list.Clear(); if ( table.Rows.Count <= 0 ) return; FieldInfo [] fields = typeof(T).GetFields(); foreach ( DataRow row in table.Rows ) { if ( row.IsNull(0) ) { //"Sheet " + table.TableName + " has a null column at index = " + index); continue; } object o = Activator.CreateInstance(typeof(T)); foreach ( FieldInfo field in fields ) { try { if ( row.Field<object>(field.Name) == null ) continue; if ( field.FieldType.IsEnum ) field.SetValue(o, Enum.Parse(field.FieldType, Convert.ToString(row.Field<object>(field.Name)), true)); else field.SetValue(o, Convert.ChangeType(row.Field<object>(field.Name), field.FieldType)); } catch ( Exception e ) { if ( e.GetType() == typeof(IndexOutOfRangeException) ) { //There is no column named as " + field.Name + " at sheet " + table.TableName; } else { //Excel Reading Error at sheet " + table.TableName + " at index = " + index + " at field = " + field.Name + "\nException Message = " + e.Message; } } } list.Add(( T )o); } } }
If you want to be updated about similar snippets, Sign in and follow our Channels

blog comments powered by Disqus