hi,
At first excel sheet data store in data grid. When you excel sheet data store in datagried then data insert in to SQL database.
For Example, This code excel sheet data store in datagried.
Here Use tow user define function.
(1) ExcelFileBrowse() And
(2) String[] GetExcelSheetNames(string excelFile)
Description function:
First function use where your excel file in hard disk.
and second use get excel sheet name such as sheet1 , sheet2
And finally use ChkBoxList_ItemCheck : you are selected this excel sheet data store in datagried.
Here use: One button , One Checkboxlist and One datagried.
string ExcelSheetName = "";
string ExcelFileName = "";
OleDbConnection objConn = null;
System.Data.DataTable dt = null;
string SheetName = "";
string connectionString = "";
private void BtnImport_Click(object sender, EventArgs e)
{
ExcelFileBrowse();
}
public void ExcelFileBrowse()
{
OpenFileDialog fdlg = new OpenFileDialog();
fdlg.Title = "Open File Dialog";
fdlg.InitialDirectory = @"E:\Document\PROYOJON:\";
fdlg.Filter = "All files (*.*)|*.*|All files (*.*)|*.*";
fdlg.FilterIndex = 2;
fdlg.RestoreDirectory = true;
if (fdlg.ShowDialog() == DialogResult.OK)
{
ExcelFileName = fdlg.FileName;
}
else
{
return;
}
GetExcelSheetNames(ExcelFileName);
}
----------------------------------------------------
private String[] GetExcelSheetNames(string excelFile)
{
ChkBoxList.Items.Clear();
ChkBoxList.Visible = true;
try
{
connectionString = String.Format(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=""Excel 8.0;HDR=YES;IMEX=1;""", excelFile);
objConn = new OleDbConnection(connectionString);
objConn.Open();
dt = objConn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
if (dt == null)
{
return null;
}
String[] excelSheets = new String[dt.Rows.Count];
int i = 0;
foreach (DataRow row in dt.Rows)
{
excelSheets[i] = row["TABLE_NAME"].ToString();
SheetName = excelSheets[i];
ChkBoxList.Items.Add(SheetName);
i++;
}
return excelSheets;
}
catch (Exception ex)
{
MessageBox.Show("Error = " + ex);
return null;
}
finally
{
if (objConn != null)
{
objConn.Close();
objConn.Dispose();
}
if (dt != null)
{
dt.Dispose();
}
}
}
-------------------------------------------------------
private void ChkBoxList_ItemCheck(object sender, ItemCheckEventArgs e)
{
int ListIndex;
ChkBoxList.ItemCheck -= ChkBoxList_ItemCheck;
for (ListIndex = 0;
ListIndex < ChkBoxList.Items.Count;
ListIndex++)
{
if (ChkBoxList.SelectedIndex != ListIndex)
{
ChkBoxList.SetItemChecked(ListIndex, false);
}
else
{
ChkBoxList.SetItemChecked(ListIndex, true);
}
}
ChkBoxList.ItemCheck += ChkBoxList_ItemCheck;
ExcelSheetName = ChkBoxList.CheckedItems[0].ToString();
string query = String.Format("select * from [{0}]", ExcelSheetName.ToString());
OleDbDataAdapter dataAdapter = new OleDbDataAdapter(query, connectionString);
DataSet dataSet = new DataSet();
dataAdapter.Fill(dataSet);
dgGried.DataSource = dataSet.Tables[0];
ChkBoxList.Visible = false;
}
---------------------------------------------
Finally Data Insert into SQL database
Insert Into TableName (Coloum1, Coloum2 )
Values(Datagried.GetRowCellValue(i, "Coloum1").ToString(), Datagried.GetRowCellValue(i, "Coloum2").ToString() )