This article mainly describes about how to import the data from Excel sheet to the SQL Server database.
OpenFileDialog openFileDialog = new OpenFileDialog();
if (lueCustomerImport.Text == "Product")
openFileDialog.Filter = "All xlsx Files(Product.xlsx)|Product.xlsx";
if (lueCustomerImport.Text == "Supplier")
openFileDialog.Filter = "All xlsx Files(Supplier.xlsx)|Supplier.xlsx";
if (lueCustomerImport.Text == "Customer")
openFileDialog.Filter = "All xlsx Files(Customer.xlsx)|Customer.xlsx";
if (DialogResult.OK == openFileDialog.ShowDialog())
{
string connectionString = String.Format(@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties=""Excel 8.0;HDR=YES;IMEX=1;""", openFileDialog.FileName);
string query =String.Format("select * from [{0}$]","Sheet1");
OleDbDataAdapter dataAdapter=new OleDbDataAdapter(query,connectionString);
DataSet dataSet=new DataSet();
dataAdapter.Fill(dataSet);
dataGridView1.DataSource=dataSet.Tables[0];
}
For Example:..........
for (int i = 0; i <= dataGridView1.RowCount - 1; i++)
{
//cmd = new SqlCommand("AddArea");
//cmd.CommandType = CommandType.StoredProcedure;
//cmd.Parameters.Add(new SqlParameter("@ID", SqlDbType.Int, 0, ParameterDirection.Output, false, 0, 0, "ID", DataRowVersion.Default, null));
//cmd.Parameters.AddWithValue("@Name",dataGridView1.Rows[i].Cells[0].Value.ToString());
//DataManager.ExecuteNonQuery(cmd);
if (lueCustomerImport.Text == "Product")
{
cmd = new SqlCommand("AddProduct");
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add(new SqlParameter("@ID", SqlDbType.Int, 0, ParameterDirection.Output, false, 0, 0, "ID", DataRowVersion.Default, null));
cmd.Parameters.AddWithValue("@BlnUPCManually", false);
cmd.Parameters.AddWithValue("@Code", dataGridView1.Rows[i].Cells[0].Value.ToString());
cmd.Parameters.AddWithValue("@Name", dataGridView1.Rows[i].Cells[1].Value.ToString());
cmd.Parameters.AddWithValue("@PurchaseRate", dataGridView1.Rows[i].Cells[2].Value.ToString());
cmd.Parameters.AddWithValue("@RetailSalesRate", dataGridView1.Rows[i].Cells[3].Value.ToString());
cmd.Parameters.AddWithValue("@WholeSaleRate", dataGridView1.Rows[i].Cells[4].Value.ToString());
cmd.Parameters.AddWithValue("@OtherSalesRate", dataGridView1.Rows[i].Cells[5].Value.ToString());
cmd.Parameters.AddWithValue("@MRP", dataGridView1.Rows[i].Cells[6].Value.ToString());
cmd.Parameters.AddWithValue("@Rack", dataGridView1.Rows[i].Cells[7].Value.ToString());
cmd.Parameters.AddWithValue("@Bin", dataGridView1.Rows[i].Cells[8].Value.ToString());
cmd.Parameters.AddWithValue("@GCode", "");
cmd.Parameters.AddWithValue("@Manufacture", "");
cmd.Parameters.AddWithValue("@ReOrderLevel", 0);
cmd.Parameters.AddWithValue("@MaxOrderLevel", 0);
cmd.Parameters.AddWithValue("@CategoryID", 1);
cmd.Parameters.AddWithValue("@MeasurementID", 82);
cmd.Parameters.AddWithValue("@InitMeasurementID", 82);
cmd.Parameters.AddWithValue("@MinOrderLevel", 0);
cmd.Parameters.AddWithValue("@ProfitPercentage", 0);
cmd.Parameters.AddWithValue("@SlowMovingLevel", 0);
cmd.Parameters.AddWithValue("@FastMovingLevel", 0);
cmd.Parameters.AddWithValue("@SubClassID", 1);
cmd.Parameters.AddWithValue("@BrandID", 1);
cmd.Parameters.AddWithValue("@OriginID", 1);
cmd.Parameters.AddWithValue("@ShortDescription", "");
cmd.Parameters.AddWithValue("@Attribute", "");
cmd.Parameters.AddWithValue("@Size", "");
cmd.Parameters.AddWithValue("@CostingMethodID", 0);
cmd.Parameters.AddWithValue("@ItemTypeID", 1);
cmd.Parameters.AddWithValue("@UPC", 0);
cmd.Parameters.AddWithValue("@PurchaseUOMID", 82);
cmd.Parameters.AddWithValue("@SalesUOMID", 82);
cmd.Parameters.AddWithValue("@IsPacking", false);
cmd.Parameters.AddWithValue("@IsDeActive", false);
cmd.Parameters.AddWithValue("@SupplierID", 1);
DataManager.ExecuteNonQuery(cmd);
MessageBox.Show("Success");
}
sumayya pPosted Mar 17, 2015, 4:57 AM
Rahul BansalPosted Mar 17, 2015, 4:46 AM
//-------------------------------------
string strConn = "";
switch (Path.GetExtension(openFileDialog.FileName))
{
case ".xls": //Excel 1997-2003
strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + openFileDialog.FileName
+ ";Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=1\"";
break;
case ".xlsx": //Excel 2007-2010
strConn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + openFileDialog.FileName
+ ";Extended Properties=\"Excel 12.0 xml;HDR=Yes;IMEX=1\"";
break;
}
//Get the data from the excel sheet1 which is default
string query = "select * from [Sheet1$]";
// Add System.Data.OleDb;
OleDbConnection objConn;
OleDbDataAdapter oleDA;
DataTable dt = new DataTable();
objConn = new OleDbConnection(strConn);
objConn.Open();
oleDA = new OleDbDataAdapter(query, objConn);
oleDA.Fill(dt);
objConn.Close();
oleDA.Dispose();
objConn.Dispose();
//Save the dt value in to the database
//------------------------------------