0
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 <= 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
0 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.
0 What is this OleDb and OleDbConnection mean? Can anyone explain in more detail about this?
0 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.
0 - 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);