private void btnImport_Click(object sender, EventArgs e)
{
OpenFileDialog openfileDialog1 = new OpenFileDialog();
openfileDialog1.Filter = "Excel files only (*.xls)|";
if (openfileDialog1.ShowDialog() == DialogResult.OK)
{
string FNExtn = Path.GetFileName(openfileDialog1.FileName);
string FN = Path.GetExtension(openfileDialog1.FileName);
string SaveLocation = Path.GetFullPath(openfileDialog1.FileName);
if (FN == ".xls")
{
sExcelConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" + SaveLocation + "; Extended Properties=\"Excel 8.0;IMEX=1;\"";
Import_sample(FNExtn);
}
else if (FN == ".xlsx")
{
sExcelConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + SaveLocation + ";Extended Properties=\"Excel 12.0;HDR=YES;\"";
Import_sample(FNExtn);
}
else if (FN != "xls" || FN != "xlsx")
{
//Alert message "Please Select an Excel File to import!"
}
}
}
private void Import_sample(string FNE)
{
DataSet myDataSet = new DataSet();
DataTable dtFile = new DataTable();
try
{
//You must use the $ after the object you reference in the spreadsheet
OleDbConnection excelConnection = new OleDbConnection(sExcelConnectionString);
excelConnection.Open();
DataTable dtSheetName = new DataTable();
dtSheetName = excelConnection.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
string SheetName = "";
foreach (DataRow row in dtSheetName.Rows)
{
string strSheetTableName = row["TABLE_NAME"].ToString(); //dtSheetName.Rows[0]["TABLE_NAME"].ToString();
if (strSheetTableName.Length > 1)
SheetName = strSheetTableName.Substring(0, strSheetTableName.Length - 1).Replace("$", "").Replace("'", "");
OleDbDataAdapter myCommand = new OleDbDataAdapter("SELECT * FROM [" + SheetName + "$]", sExcelConnectionString);
myCommand.Fill(myDataSet, "Import");
if (myDataSet.Tables["Import"].Rows.Count > 0 && SheetName == "Sheet1")
{
dtFile = myDataSet.Tables["Import"]; // All the records are filled in a datatable - dtFile
string sheet1col1 = dtFile.Columns[0].ColumnName.ToString().Trim().Replace(":", "");
string sheet1col2 = dtFile.Columns[1].ColumnName.ToString().Trim().Replace(":", "");
if (sheet1col1.Trim() == "Name" && sheet1col2.Trim() == "Type") // (SheetName == sheet)
{
}
}
}
}
catch (Exception)
{
throw;
}
}
