Reading Excel in c#

Reading excel is the piece of cake but for me its took some trail and errors and here is the post about it. The module flow as follows,
1. Load excel file obviously
2. Use Microsoft.ACE.OLEDB to convert excel data into DataTable format
3. DataTable will make the life easier [traversal throw the data]
the code as below,

        public DataTable readExcel(String sheetName, String fileName)
        {
            string dirLocation = Directory.GetCurrentDirectory();
            String filePath = dirLocation + "\\" + fileName;
            string connString = string.Empty;
            if (filePath.EndsWith(".xlsx"))
            {
                //2007 Format
                connString = string.Format("Provider=Microsoft.ACE.OLEDB.12.0; Data Source={0}; Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=1\"", filePath);
            }
            using (OleDbConnection con = new OleDbConnection(connString))
            {
                using (OleDbCommand cmd = new OleDbCommand())
                {
                    //Read Sheet
                    cmd.Connection = con;
                    con.Open();
                    DataTable dtExcelSchema = con.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
                    con.Close();

                    //Read the Header Row and value
                    cmd.CommandText = "SELECT * From [" + sheetName + "$]";
                    using (OleDbDataAdapter da = new OleDbDataAdapter(cmd))
                    {
                        System.Data.DataTable data = new DataTable();
                        da.SelectCommand = cmd;
                        da.Fill(data);
                        return data;
                    }
                }
            }
        }

As this method return Data Table so we can traversal the data table to get the data by row/column as below,

Excel Data like,

Super Hero	Feature
Iron Man	High tech suite
Superman	Flying
Thor	Hammer
Spiderman	Web

Get Row Data:

 foreach (DataRow rowData in data.Rows)
    {
    	console.WriteLine("Row --> "+rowData[0]+", "+rowData[1]);
    }

this will return Iron Man, High tech suite
Get Column Data and Header:

 foreach (DataRow rowData in data.Rows)
    {
    	console.WriteLine("Row: "+rowData);
    	foreach (DataColumn column in data.Columns)
        {
        	string columnValue = rowData[column.ColumnName].ToString();
        	Console.WriteLine(column.ColumnName + ", " + columnValue);
        }
    }

this will return Super Hero, Iron Man

Import data to Sharepoint list from excel

Its been rough day with excel and importing data into sharepoint list, But as a good developer Aka. Warroir never stop fighting, So finally i managed to import data from excel sheet, I thankful for all the blogs and post that saved my day, So here is a bits and pieces of snippets i collected and build this method as below,

Things to do,
1. Install AccessDatabaseEnging.exe 32-Bit works like charm but not 64 bit [Trust me on this]
2. Excel with sheet name in here i used same as list name to relate between excel data and list. [listName == Sheet Name]
3. Create taxonomy termset fields before run the method, else it will thow term not found exception.
4. Make sure there is no special characters in the document set title else it will thorws exception and docset will not get created

Module Flow:
1.Read excel from Debug folder in project

string dirLocation = Directory.GetCurrentDirectory();
//Location will be like ProjectName\bin\Debug\data.xlsx
String filePath = dirLocation + "\\data.xlsx";

1. Use OLEDB to push data to the table to make life easier.
2. Read sheet that related to list

cmd.CommandText = "SELECT * From ["+listName+"$]";

1. Traversal throw the table and get column name and values

foreach (DataColumn column in data.Columns)
{
string columnValue = i[column.ColumnName].ToString();
Console.WriteLine("Column Name --> " + column.ColumnName + "Value ->" + columnValue);
oListItem[column.ColumnName] = columnValue;
}

1. Creating list items is just two lines as above but document library need special treatment as below,

listCreationInformation.UnderlyingObjectType = FileSystemObjectType.Folder;
foreach (DataColumn column in data.Columns)
{
if (column.ColumnName == "Title")
{
string columnValue = i[column.ColumnName].ToString();
listCreationInformation.LeafName = columnValue;
}
}
ListItem newListItem = oList.AddItem(listCreationInformation);
newListItem["ContentTypeId"] = "0x0120D520";
foreach (DataColumn column in data.Columns)
{
string columnValue = i[column.ColumnName].ToString();
newListItem[column.ColumnName] = columnValue;
}
newListItem.Update();

Give a try the code below,

public void readExcel_importListData(ClientContext context, String listName) {
	string dirLocation = Directory.GetCurrentDirectory();
	//Location will be like ProjectName\bin\Debug\data.xlsx
	String filePath = dirLocation + "\\data.xlsx";
	string connString = string.Empty;
	Console.WriteLine("Location: : " + filePath);
	List oList = context.Web.Lists.GetByTitle(listName);
	ListItemCreationInformation listCreationInformation = new ListItemCreationInformation();
	if (filePath.EndsWith(".xlsx")) {
		//supports from 2007 Format
		connString = string.Format("Provider=Microsoft.ACE.OLEDB.12.0; Data Source={0}; Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=1\"", filePath);
	}
	using(OleDbConnection con = new OleDbConnection(connString)) {
		using(OleDbCommand cmd = new OleDbCommand()) {
			//Read Sheet
			cmd.Connection = con;
			con.Open();
			DataTable dtExcelSchema = con.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
			con.Close();

			//Read the Header Row and value
			cmd.CommandText = "SELECT * From [" + listName + "$]";
			using(OleDbDataAdapter da = new OleDbDataAdapter(cmd)) {
				System.Data.DataTable data = new DataTable();
				da.SelectCommand = cmd;
				da.Fill(data);

				foreach(DataRow i in data.Rows) {
					ListItem oListItem = oList.AddItem(listCreationInformation);
					if (listName != "Document Library") {
						foreach(DataColumn column in data.Columns) {
							string columnValue = i[column.ColumnName].ToString();
							Console.WriteLine("Column Name --> " + column.ColumnName + "Value ->" + columnValue);
							oListItem[column.ColumnName] = columnValue;
						}
						oListItem.Update();
						context.ExecuteQuery();
					} else {
						listCreationInformation.UnderlyingObjectType = FileSystemObjectType.Folder;
						foreach(DataColumn column in data.Columns) {
							if (column.ColumnName == "Title") {
								string columnValue = i[column.ColumnName].ToString();
								listCreationInformation.LeafName = columnValue;
							}
						}
						ListItem newListItem = oList.AddItem(listCreationInformation);
						newListItem["ContentTypeId"] = "0x0120D520";
						foreach(DataColumn column in data.Columns) {
							string columnValue = i[column.ColumnName].ToString();
							newListItem[column.ColumnName] = columnValue;
						}
						newListItem.Update();
						context.Load(oList);
						context.ExecuteQuery();
					}
				}

				Console.WriteLine("List data imported Successfully");
			}
		}
	}
}

---