I'm importing excel file in to datagrid and then i'm inserting it in to database. Problem is excel blank rows are also getting inserted in to database.
private void btnChooseFile_Click(object sender, EventArgs e)
{
string filePath = string.Empty;
string fileExt = string.Empty;
OpenFileDialog file = new OpenFileDialog(); //open dialog to choose file
if (file.ShowDialog() == System.Windows.Forms.DialogResult.OK) //if there is a file choosen by the user
{
filePath = file.FileName; //get the path of the file
fileExt = Path.GetExtension(filePath); //get the file extension
if (fileExt.CompareTo(".xls") == 0 || fileExt.CompareTo(".xlsx") == 0)
{
try
{
DataTable dtExcel = new DataTable();
dtExcel = ReadExcel(filePath, fileExt); //read excel file
dataGridView1.Visible = true;
dataGridView1.DataSource = dtExcel;
}
catch (Exception ex)
{
MessageBox.Show(ex.Message.ToString());
}
}
else
{
MessageBox.Show("Please choose .xls or .xlsx file only.", "Warning", MessageBoxButtons.OK, MessageBoxIcon.Error); //custom messageBox to show error
}
}
}
private void btnClose_Click(object sender, EventArgs e)
{
for(int i = 0; i < dataGridView1.Rows.Count; i++)
{
string val = this.dataGridView1.Rows[i].Cells[0].Value as string;
if (string.IsNullOrEmpty(val) == false)
{
con = new SqlConnection(constring);
con.Open();
cmd = new SqlCommand();
cmd.Connection = con;
cmd.CommandText = "insert into Barcode(PartNumber, Description, Quantity, Bdate, Location, Mrp, ParentModel) values('" + dataGridView1.Rows[i].Cells[0].Value + "', '" + dataGridView1.Rows[i].Cells[1].Value + "', '" + dataGridView1.Rows[i].Cells[2].Value + "', '" + dataGridView1.Rows[i].Cells[3].Value + "', '" + dataGridView1.Rows[i].Cells[4].Value + "', '" + dataGridView1.Rows[i].Cells[5].Value + "', '" + dataGridView1.Rows[i].Cells[6].Value + "')";
cmd.ExecuteNonQuery();
con.Close();
}
else
{
//this.dataGridView1.Rows[i].Cells[0].Visible = false;
MessageBox.Show("Some of the contents of this excel files are empty!");
}
}
MessageBox.Show("Part Details Uploaded Successfully!");
}