Need to write/ insert values to MS EXcel

Question

Hi, I have a requirement werin i have to write/ insert values into excel from visual studio 2008 using oledb connection . Thanks

Satyapriya Nayak · Answer

Hi venu, Here is your solution There is a test.xls file in the attachments.Store that file in d: drive. using System; using System.Collections.Generic; using System.ComponentModel; using System.Data; using System.Drawing; using System.Linq; using System.Text; using System.Windows.Forms; using System.Data.OleDb; namespace Insert_data_into_excel { public partial class Form1 : Form { OleDbConnection con; OleDbCommand com; string str; public Form1() { InitializeComponent(); } private void btn_add_Click(object sender, EventArgs e) { try { com = new OleDbCommand(); con = new OleDbConnection("provider=Microsoft.Jet.OLEDB.4.0;Data Source='d:\test.xls';Extended Properties=Excel 8.0;"); con.Open(); com.Connection = con; str = "Insert into [sheet1$] (Name) values('" + textBox1.Text + "')"; com.CommandText = str; com.ExecuteNonQuery(); con.Close(); MessageBox.Show("Records Added Successfully"); textBox1.Text = ""; textBox1.Focus(); } catch (Exception ex) { MessageBox.Show(ex.ToString()); } } } } Thanks If this post helps you mark it as answer

venu Gajjala · Answer

Hi , The thing is that i already have a file and i need to insert into that file a row when user clicks add ... something like a database insert operation with excel . Thanks

Satyapriya Nayak · Answer

Hi Venu, Here is your solution We have to add a reference to the Microsoft Excel object library. Right click on your project and select Add Reference menu. After that go to COM tab and select and add Microsoft Excel 12.0 object library. using System; using System.Collections.Generic; using System.ComponentModel; using System.Data; using System.Drawing; using System.Linq; using System.Text; using System.Windows.Forms; using System.Data.OleDb; using System.IO; namespace Retrieving_excel_data_to_combobox { public partial class Form1 : Form { public OleDbConnection con; public void pintu(string s) { con = new OleDbConnection("provider=Microsoft.Jet.OLEDB.4.0; " + "data source='" + s + " '; " + "Extended Properties=Excel 8.0;"); } public OleDbCommand com; public DataSet ds; public OleDbDataAdapter oledbda; public DataTable dt; public string str; public Form1() { InitializeComponent(); } private void btnbrowse_Click(object sender, EventArgs e) { OpenFileDialog openfiledialog1 = new OpenFileDialog(); openfiledialog1.ShowDialog(); openfiledialog1.Filter = "allfiles|*.xls"; TextBox1.Text = openfiledialog1.FileName; } private void Form1_Load(object sender, EventArgs e) { comboBox1.Text = "Please select"; } private void btndisplay_Click(object sender, EventArgs e) { pintu(TextBox1.Text); try { con.Open(); str = "select * from [sheet1$]"; com = new OleDbCommand(str, con); ds = new DataSet(); oledbda = new OleDbDataAdapter(com); oledbda.Fill(ds, "[sheet1$]"); con.Close(); DataGridView1.DataSource = ds; DataGridView1.DataMember = "[sheet1$]"; } catch (Exception ex) { MessageBox.Show(ex.Message); } try { con.Open(); str = "select * from [sheet1$]"; com = new OleDbCommand(str, con); oledbda = new OleDbDataAdapter(com); ds = new DataSet(); oledbda.Fill(ds, "[sheet1$]"); con.Close(); dt = ds.Tables["[sheet1$]"]; int i = 0; for (i = 0; i { comboBox1.Items.Add(dt.Rows[i].ItemArray[0]); } } catch (Exception ex) { MessageBox.Show(ex.Message); } } private void btn_export_Click(object sender, EventArgs e) { //We have to add a reference to the Microsoft Excel object library. //Right click on your project and select Add Reference menu. After that go to COM tab and select and add Microsoft Excel 12.0 object library. 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; try { worksheet = (Microsoft.Office.Interop.Excel.Worksheet)workbook.Sheets["Sheet1"]; worksheet = (Microsoft.Office.Interop.Excel.Worksheet)workbook.ActiveSheet; worksheet.Name = "Exported from DataGridView"; for (int i = 1; i { worksheet.Cells[1, i] = DataGridView1.Columns[i - 1].HeaderText; } for (int i = 0; i { for (int j = 0; j { worksheet.Cells[i + 2, j + 1] = DataGridView1.Rows[i].Cells[j].Value.ToString(); } } string fileName = String.Empty; saveFileExcel.Filter = "Excel files |*.xls|All files (*.*)|*.*"; saveFileExcel.FilterIndex = 2; saveFileExcel.RestoreDirectory = true; if (saveFileExcel.ShowDialog() == DialogResult.OK) { fileName = saveFileExcel.FileName; workbook.SaveAs(fileName, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlExclusive, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing); } else return; } catch (System.Exception ex) { MessageBox.Show(ex.Message); } finally { app.Quit(); workbook = null; app = null; } } } } Thanks If this post helps you mark it as answer