How can we map the excel file columns.

Question

I have three excel files with different columns file1(a,b) file2(b,c), file3(c,d). how can we map the columns. like file4(a,b,c,d) to one file or list.

Mayur Patel · Answer

first you need to convert excel to datatable pass excel file path and get data table public static DataTable ConvertExcelToDataTable(string FileName) { DataTable dtResult = null; int totalSheet = 0; //No of sheets on excel file using(OleDbConnection objConn = new OleDbConnection(@ "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + FileName + ";Extended Properties='Excel 12.0;HDR=YES;IMEX=1;';")) { objConn.Open(); OleDbCommand cmd = new OleDbCommand(); OleDbDataAdapter oleda = new OleDbDataAdapter(); DataSet ds = new DataSet(); DataTable dt = objConn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null); string sheetName = string.Empty; if (dt != null) { var tempDataTable = (from dataRow in dt.AsEnumerable() where!dataRow["TABLE_NAME"].ToString().Contains("FilterDatabase") select dataRow).CopyToDataTable(); dt = tempDataTable; totalSheet = dt.Rows.Count; sheetName = dt.Rows[0]["TABLE_NAME"].ToString(); } cmd.Connection = objConn; cmd.CommandType = CommandType.Text; cmd.CommandText = "SELECT * FROM [" + sheetName + "]"; oleda = new OleDbDataAdapter(cmd); oleda.Fill(ds, "excelData"); dtResult = ds.Tables["excelData"]; objConn.Close(); return dtResult; //Returning Dattable } } ------------------------End Excel to data table---------------------- and marge three table into one data table using linq. var newTable= from d1 in dtfile1 join d2 in dtfile2 on d1.b equals d2.b join d3 in dtfile3 on d2.c equals d3.c .Select(m => new { a= d1.a, b= d1.b, c=d2.c, d=d3.d }) ---------------------End marge table using linq----------------------- and export data table into excle. Excel.Application excelApp = new Excel.Application(); Excel.Workbook excelWorkBook = excelApp.Workbooks.Open(@"E:\Org.xlsx"); Excel.Worksheet excelWorkSheet = excelWorkBook.Sheets.Add(); excelWorkSheet.Name = table.TableName; for (int i = 1; i { excelWorkSheet.Cells[1, i] = table.Columns[i - 1].ColumnName; } for (int j = 0; j { for (int k = 0; k { excelWorkSheet.Cells[j + 2, k + 1] = table.Rows[j].ItemArray[k].ToString(); } } excelWorkBook.Save(); excelWorkBook.Close(); excelApp.Quit(); ----------------------End datatble to excel----------------------------

How can we map the excel file columns.

Answers (1)