How can we map the excel file columns.

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 < table.Columns.Count + 1; i++) //table is a datatable which is convert from datatble to datatle

{

excelWorkSheet.Cells[1, i] = table.Columns[i - 1].ColumnName;

}

for (int j = 0; j < table.Rows.Count; j++)

{

for (int k = 0; k < table.Columns.Count; k++)

{

excelWorkSheet.Cells[j + 2, k + 1] = table.Rows[j].ItemArray[k].ToString();

}

excelWorkBook.Save();

excelWorkBook.Close();

excelApp.Quit();

----------------------End datatble to excel----------------------------

1 Reply

Know the answer? Post it — somebody with the same question will find it here.

Mayur Patel8y ago
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 < table.Columns.Count + 1; i++) //table is a datatable which is convert from datatble to datatle
{
excelWorkSheet.Cells[1, i] = table.Columns[i - 1].ColumnName;
}

for (int j = 0; j < table.Rows.Count; j++)
{
for (int k = 0; k < table.Columns.Count; k++)
{
excelWorkSheet.Cells[j + 2, k + 1] = table.Rows[j].ItemArray[k].ToString();

}

}
excelWorkBook.Save();
excelWorkBook.Close();
excelApp.Quit();

----------------------End datatble to excel----------------------------