Can anyone help me out to export only selected rows from dgv into excle. I've below code for importing and searching and exporting. But not sure why it is not exporting only selected row.
private void button1_Click_1(object sender, EventArgs e)
{
OpenFileDialog openFileDialog1 = new OpenFileDialog();
if (openFileDialog1.ShowDialog() == System.Windows.Forms.DialogResult.OK)
{
//this.textBox1.Text = openFileDialog1.FileName;
//string PathConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + textBox1.Text + ";Extended Properties=\"Excel 8.0;HDR=Yes:\";";
String PathConn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + openFileDialog1.FileName + ";Extended Properties=\"Excel 12.0 XML;HDR=Yes:\";";
OleDbConnection conn = new OleDbConnection(PathConn);
OleDbDataAdapter myDataAdapter = new OleDbDataAdapter("Select * from [Sheet1$]", conn);
DataTable dt = new DataTable();
myDataAdapter.Fill(dt);
dataGridView1.DataSource = dt;
/*Another way
string constr = "Provider = MicroSoft.Jet.OLEDB.4.0; Data Source=" + textBox1.Text + "; Extended Properties =\"Excel 8.0; HDR=Yes;\";";
OleDbConnection con = new OleDbConnection(constr);
OleDbDataAdapter sda = new OleDbDataAdapter("Select * From [" + textBox2.Text + "$]",con);
DataTable dt = new DataTable();
sda.Fill(dt);
dataGridView1.DataSource = dt; */
}
}
private void label1_Click(object sender, EventArgs e)
{
}
private void button2_Click_1(object sender, EventArgs e)
{
dataGridView1.ClearSelection();
dataGridView1.SelectionMode = DataGridViewSelectionMode.FullRowSelect;
try
{
foreach (DataGridViewRow row in dataGridView1.Rows)
{
if (row.Cells["Last Name"].Value.ToString().ToUpperInvariant().Contains(textBox1.Text.ToUpperInvariant()))
{
dataGridView1.Rows[row.Index].Selected = true;
}
else
if (row.Cells["First Name"].Value.ToString().ToUpperInvariant().Contains(textBox1.Text.ToUpperInvariant()))
{
dataGridView1.Rows[row.Index].Selected = true;
}
else
if (row.Cells["Age"].Value.ToString().Equals(textBox1.Text))
{
dataGridView1.Rows[row.Index].Selected = true;
}
else
if (row.Cells["Vendor"].Value.ToString().ToUpperInvariant().Contains(textBox1.Text.ToUpperInvariant()))
{
dataGridView1.Rows[row.Index].Selected = true;
}
}
}
catch (Exception)
{
}
}
private void button3_Click_1(object sender, EventArgs e)
{
Microsoft.Office.Interop.Excel._Application app = new Microsoft.Office.Interop.Excel.Application();
Microsoft.Office.Interop.Excel._Workbook workbook = app.Workbooks.Add(Type.Missing);
Microsoft.Office.Interop.Excel._Worksheet worksheet = null;
app.Visible = true;
worksheet = workbook.Sheets["Sheet1"];
worksheet = workbook.ActiveSheet;
worksheet.Name = "Exported from gridview";
for (int i = 1; i < dataGridView1.Columns.Count + 1; i++)
{
worksheet.Cells[1, i] = dataGridView1.Columns[i - 1].HeaderText;
}
// It's the part which we are interested in
// we just need to change dataGridView1.Rows to dataGridView1.SelectedRows
for (int i = 0; i < dataGridView1.SelectedRows.Count - 1; i++)
{
for (int j = 0; j < dataGridView1.Columns.Count; j++)
{
worksheet.Cells[i + 2, j + 1] = dataGridView1.Rows[i].Cells[j].Value.ToString();
}
}
workbook.SaveAs("c:\\NetProject\\output.xls", Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlExclusive, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
//app.Quit();
}
