How to read from existing excel sheet and how to create new excel sheet and write to that?

Question

Hi, I want the excel sheet to be loaded using filedialog box and read the excel sheet. I also want to know how to create a new excel file with only one sheet and write to that excel sheet

Satyapriya Nayak · Answer

Hi Karthik, 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_exportxls { 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

Karthik Agarwal · Answer

based on the attached excel format I want to create labels based on first row in the excel sheet then 1. From A2 to end of the column should be in dropdown 2. based on the dropdown selected corresponding cells i.e if A2 is selected then B1 to B8 should be created as check boxes with the names in the those cells 3. After that based on the checked item in the above checkboxes the corresponding elements should be created as checkboxes again. i.e if Key board is checked then Matrix key, Register ladder Key(SWC), Keyboard controller should be created as checkboxes. similarly if voice is checked voice inputs should be created as chexkbox. 4. Similarly for the next columns as well.. Logic required ASAP.

Karthik Agarwal · Answer

What is this OleDb and OleDbConnection mean? Can anyone explain in more detail about this?

Karthik Agarwal · Answer

Thanks satya. You gave me more than what i wanted but i wanted to build something based on that. Hence your work would be of no use i guess. Anyways Thanks again.

sufiya khan · Answer

add openfiledialog take a textbox and one button in button event write the code openFileDialog1.Filter = "xls|*"; openFileDialog1.ShowDialog(); txtFile.Text = openFileDialog1.FileName; and this is for reading the excel OleDbConnection MyConnection; OleDbDataAdapter MyCommand; string filename1, conn; filename1 = ""; if (txtFile.Text != "") { filename1 = txtFile.Text.Trim(); } else { MessageBox.Show("Please select file"); return; } try { conn = "provider=Microsoft.Jet.OLEDB.4.0;Data Source='" + filename1 + "';Extended Properties=Excel 8.0;"; DataSet ds; MyCommand = new OleDbDataAdapter(); MyConnection = new System.Data.OleDb.OleDbConnection(conn); MyCommand = new System.Data.OleDb.OleDbDataAdapter("select * from [" + txtSheetName.Text.Trim() + "$]", MyConnection); ds = new System.Data.DataSet(); bool isFirstRow = true; MyCommand.Fill(ds); now the data stored in the dataset u can easily read the data using loop u can create the new excel sheet using string builder and I/O stream function

How to read from existing excel sheet and how to create new excel sheet and write to that?

Answers (5)