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