Hello
how to load data into datagridview from excel and insert it to db from datagridview
this is my code for load data from excel file
select excel sheet code
private void btnselect_file_Click(object sender, EventArgs e)
{
OpenFileDialog _objdlg = new OpenFileDialog();
DialogResult _objdlgresult = _objdlg.ShowDialog();
if (_objdlgresult == DialogResult.OK)
{
textBox_filename.Text = _objdlg.FileName;
}
GetExcelSheetNames(textBox_filename.Text);
}
GetExcelSheetNames Void
public void GetExcelSheetNames(string path)
{
List<string> sheets = new List<string>();
string connectionString =
String.Format(
@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=""Excel 8.0;HDR=YES;IMEX=1;""",
path);
DbProviderFactory factory = DbProviderFactories.GetFactory("System.Data.OleDb");
DbConnection connection = factory.CreateConnection();
connection.ConnectionString = connectionString;
connection.Open();
DataTable tbl = connection.GetSchema("Tables");
connection.Close();
foreach (DataRow row in tbl.Rows)
{
string sheetName = (string) row["TABLE_NAME"];
if (sheetName.EndsWith("$"))
{
sheetName = sheetName.Substring(0, sheetName.Length - 1);
}
sheets.Add(sheetName);
}
combo_sheetname.DataSource = sheets;
}
load the selected sheet into datagridview
private void btnsload_file_Click(object sender, EventArgs e)
{
String name = "Items";
String constr = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + textBox_filename.Text +
";Extended Properties='Excel 12.0 XML;HDR=YES;';";
OleDbConnection con = new OleDbConnection(constr);
OleDbCommand oconn = new OleDbCommand("Select * From [" + combo_sheetname.Text + "$]", con);
con.Open();
OleDbDataAdapter sda = new OleDbDataAdapter(oconn);
DataTable data = new DataTable();
sda.Fill(data);
dataGridView1.DataSource = data;
}
and this is my code to insert the data from datagridview into db
private void btn_savefromexcel_Click(object sender, EventArgs e)
{
SqlConnection cne =
new SqlConnection(ConfigurationManager.ConnectionStrings["payroll"].ConnectionString);
for (int i = 0; i < dataGridView1.Rows.Count; i++)
{
SqlCommand cmdz =new SqlCommand(@"INSERT INTO WPS_data (emp_id,emp_QID,emp_visa_ID,emp_name,bank_short_name,bank_account_number, salary_frequency, working_days, net_salary, basic_salary, extra_hours, extra_income, Deductions, Payment_Type, Comments) VALUES ("
+ dataGridView1.Rows[i].Cells[0].Value.ToString() + ", " +
dataGridView1.Rows[i].Cells[1].Value.ToString() + " ," +
dataGridView1.Rows[i].Cells[2].Value.ToString() + ","
+ dataGridView1.Rows[i].Cells[3].Value.ToString() + ", " +
dataGridView1.Rows[i].Cells[4].Value.ToString() + " ," +
dataGridView1.Rows[i].Cells[5].Value.ToString() + ","
+ dataGridView1.Rows[i].Cells[6].Value.ToString() + ", " +
dataGridView1.Rows[i].Cells[7].Value.ToString() + " ," +
dataGridView1.Rows[i].Cells[8].Value.ToString() + ","
+ dataGridView1.Rows[i].Cells[9].Value.ToString() + ", " +
dataGridView1.Rows[i].Cells[10].Value.ToString() + " ," +
dataGridView1.Rows[i].Cells[11].Value.ToString() + ","
+ dataGridView1.Rows[i].Cells[12].Value.ToString() + ", " +
dataGridView1.Rows[i].Cells[13].Value.ToString() + " ," +
dataGridView1.Rows[i].Cells[14].Value.ToString() + ");", cne);
cmdz.Connection = cne;
cmdz.CommandType = CommandType.Text;
cne.Open();
cmdz.ExecuteScalar();
cne.Close();
MessageBox.Show(@"Insert New Data ", @"New Data Has Been Imported Successfully !",
MessageBoxButtons.OK, MessageBoxIcon.Information);
}
the error that i faced is invalid column name and its see the cell as column idk why
here is my excel file
http://i.imgur.com/hF4NoZS.png