Interoperation & Processing EXCEL Sheet Data

//HERE IN THIS METHOD, WE PROCESS EXCEL SHEET FOR EACH CELL AND //CHECKING ITS DATATYPE SO ACCORDINGLY CREATED A TABLE HAVING THOSE //DATATYPE FIELDS. THEN DATA ENTERED IN THE TABLE FROM EXCEL SHEET.

//FINALLY IT IS MOVED TO SQL DATABASE.

public void ImportExcelToSQLServer()
{
Microsoft.Office.Interop.Excel.Application excel = null;
Microsoft.Office.Interop.Excel.Workbook wb = null;
object missing = Type.Missing;
try
{
btnImport.Text = "Data importing in Process";
btnImport.Enabled = false;
excel = new Microsoft.Office.Interop.Excel.Application();
string ExcelFile = txtFilePath.Text.Trim();
wb = excel.Workbooks.Open(ExcelFile, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing);
foreach (Microsoft.Office.Interop.Excel.Worksheet x in wb.Worksheets)
{
object rowIndex = 1;
object colIndex1 = 2;
//Getting total no of rows available in Excel sheet.
int rowCount = Convert.ToInt32(((Microsoft.Office.Interop.Excel.Range)x.Cells[rowIndex, colIndex1]).Text.ToString());
rowIndex = 1;
colIndex1 = 4;
//Getting total no of columns available in Excel sheet.
int columnCount = Convert.ToInt32(((Microsoft.Office.Interop.Excel.Range)x.Cells[rowIndex, colIndex1]).Text.ToString());
string tblName = x.Name;
System.Data.DataTable dtnew = new System.Data.DataTable(tblName);
for (int i = 4; i <= rowCount; i++)
{
DataRow drn = dtnew.NewRow();
for (int j = 1; j <= columnCount; j++)
{
//Creating Table structure with assigning Column name & types
if (i == 4)
{
rowIndex = i - 1;
colIndex1 = j;
string columnType = ((Microsoft.Office.Interop.Excel.Range)x.Cells[rowIndex, colIndex1]).Text.ToString().Trim();
rowIndex = i;
colIndex1 = j;
string columnName = ((Microsoft.Office.Interop.Excel.Range)x.Cells[rowIndex, colIndex1]).Text.ToString().Trim();
string columnTypeValue = "";
switch (columnType)
{
case "INT":
columnTypeValue = "System.Int32";
break;
case "Varchar":
columnTypeValue = "System.String";
break;
case "Bool":
columnTypeValue = "System.Boolean";
break;
case "Date-Time":
columnTypeValue = "System.DateTime";
break;
case "Bit":
columnTypeValue = "System.Int32";
break;
case "Decimal":
columnTypeValue = "System.Decimal";
break;
default:
columnTypeValue = "System.String";
break;
}
dtnew.Columns.Add(columnName, Type.GetType(columnTypeValue));
}
else
{
//Pushing data from each column to DataTable. Note: Here 4 as hardcoded appearing used as upto 4 rows sheet description given.
if (i > 4)
{
rowIndex = i;
colIndex1 = j;
string columnValue = ((Microsoft.Office.Interop.Excel.Range)x.Cells[rowIndex, colIndex1]).Text.ToString().Trim();
//Setting default value in case of column having null value.
if (columnValue == "")
{
rowIndex = 2;
columnValue = ((Microsoft.Office.Interop.Excel.Range)x.Cells[rowIndex, colIndex1]).Text.ToString().Trim();
}
if (columnValue == "")
{
}
drn[j - 1] = columnValue;
}

}
}
if (i != 4)
{
dtnew.Rows.Add(drn);
}
}
//
if (dtnew.Rows.Count > 0)
{
// SQL Server Connection String
string dataBaseName = cmbDatabaseList.SelectedItem.ToString();
string sqlConnectionString = "";
//Getting connection value on behalf of selected database.
switch (dataBaseName)
{
case "A":
sqlConnectionString = ConfigurationSettings.AppSettings["A_Database"].ToString();
break;
case "B":
sqlConnectionString = ConfigurationSettings.AppSettings["B"].ToString();
break;
case "C":
sqlConnectionString = ConfigurationSettings.AppSettings["C"].ToString();
break;
case "D":
sqlConnectionString = ConfigurationSettings.AppSettings["D"].ToString();
break;
case "E":
sqlConnectionString = ConfigurationSettings.AppSettings["E"].ToString();
break;
default:
sqlConnectionString = "Not available";
break;
}
if (sqlConnectionString == "Not available")
{
MessageBox.Show("Proper database connection not available!!\n Please check it.");
return;
}
using (SqlBulkCopy bulkCopy = new SqlBulkCopy((sqlConnectionString), SqlBulkCopyOptions.KeepIdentity))
{
//Truncating the table before entring data from Excel sheets.
if (chkbxAppendData.Checked == false)
{
SqlConnection con = new SqlConnection(sqlConnectionString);
SqlCommand CMD = new SqlCommand("truncate table " + dtnew.TableName, con);
if (con.State == ConnectionState.Closed)
con.Open();
CMD.ExecuteNonQuery();
con.Close();
}
//Inserting data in bulk in SQL Table from excel sheet.
bulkCopy.DestinationTableName = dtnew.TableName;
bulkCopy.BatchSize = 1000;
bulkCopy.WriteToServer(dtnew);
MessageBox.Show(dtnew.TableName + " Table" + " successfuly imported from Excel sheet to SQL Server.");
if(chkbxAppendData.Checked == true)
chkbxAppendData.Checked = false;
}
}

}
txtFilePath.Text = "";
}
catch (COMException ex)
{
MessageBox.Show("Error accessing Excel: " + ex.ToString());
}
catch (Exception ex)
{
MessageBox.Show("Error: " + ex.ToString());
}
finally
{
btnImport.Enabled = true;
btnImport.Text = "Import Excel to SQL Server";
}

}

I hope this will help developers looking to process excel sheet data and want to use in .NET Application.