Need to write/ insert values to MS EXcel

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

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

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 <= dt.Rows.Count - 1; 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 < DataGridView1.Columns.Count + 1; i++)
                {
                    worksheet.Cells[1, i] = DataGridView1.Columns[i - 1].HeaderText;
                }
               
                for (int i = 0; i < DataGridView1.Rows.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();
                    }
                }

              
                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