using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Data.SqlClient;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using Microsoft.Office.Interop.Excel;
//Program for Displaying details of an Employee based on the Selection of Employee Id
namespace ComboBoxDemo
{
public partial class Form1 : Form
{
public Form1()
{
InitializeComponent();
}
private void Form1_Load(object sender, EventArgs e)
{
// TODO: This line of code loads data into the 'adityaDataSet1.EmpTable' table. You can move, or remove it, as needed.
this.empTableTableAdapter.Fill(this.adityaDataSet1.EmpTable);
// TODO: This line of code loads data into the 'adityaDataSet.EmpLogin' table. You can move, or remove it, as needed.
this.empLoginTableAdapter.Fill(this.adityaDataSet.EmpLogin);
comboBox1.Text = "";
SqlConnection con = new SqlConnection("Data Source=.;Initial Catalog=Aditya;User ID=sa;Password=123");
con.Open();
SqlCommand cmd = new SqlCommand();
cmd = con.CreateCommand();
cmd.CommandType = CommandType.Text;
cmd.CommandText = "select EmpId from EmpTable";
SqlDataAdapter dto = new SqlDataAdapter(cmd);
DataSet ds = new DataSet();
dto.Fill(ds, "EmpTable");
comboBox1.DataSource = ds.Tables["EmpTable"];
comboBox1.DisplayMember = "EmpId";
comboBox1.ValueMember = "EmpId";
con.Close();
}
private void comboBox1_SelectedIndexChanged(object sender, EventArgs e)
{
comboBox2.Text = "";
SqlConnection con = new SqlConnection("Data Source=.;Initial Catalog=Aditya;User ID=sa;Password=123");
con.Open();
SqlCommand cmd = new SqlCommand();
cmd = con.CreateCommand();
cmd.CommandType = CommandType.Text;
cmd.CommandText = "select EmpName from EmpTable where EmpId='" + comboBox1.SelectedValue + " ' ";
SqlDataAdapter da = new SqlDataAdapter(cmd);
DataSet ds = new DataSet();
da.Fill(ds, "EmpTable");
comboBox2.DataSource = ds.Tables["EmpTable"];
comboBox2.DisplayMember = "EmpName";
comboBox2.ValueMember = "EmpName";
con.Close();
}
private void comboBox2_SelectedIndexChanged(object sender, EventArgs e)
{
comboBox3.Text = "";
SqlConnection con = new SqlConnection("Data Source=.;Initial Catalog=Aditya;User ID=sa;Password=123");
con.Open();
SqlCommand cmd = new SqlCommand();
cmd = con.CreateCommand();
cmd.CommandType = CommandType.Text;
cmd.CommandText = "select Location from EmpTable where EmpId='" + comboBox1.SelectedValue + " ' ";
SqlDataAdapter adap = new SqlDataAdapter(cmd);
DataSet ds = new DataSet();
adap.Fill(ds, "EmpTable");
comboBox3.DataSource = ds.Tables["EmpTable"];
comboBox3.DisplayMember = "Location";
comboBox3.ValueMember = "Location";
con.Close();
}
private void comboBox3_SelectedIndexChanged(object sender, EventArgs e)
{
comboBox4.Text = "";
SqlConnection con = new SqlConnection("Data Source=.;Initial Catalog=Aditya;User ID=sa;Password=123");
con.Open();
SqlCommand cmd = new SqlCommand();
cmd = con.CreateCommand();
cmd.CommandType = CommandType.Text;
cmd.CommandText = "select Salary from EmpTable where EmpId='" + comboBox1.SelectedValue + " ' ";
SqlDataAdapter adap = new SqlDataAdapter(cmd);
DataSet ds = new DataSet();
adap.Fill(ds, "EmpTable");
comboBox4.DataSource = ds.Tables["EmpTable"];
comboBox4.DisplayMember = "Salary";
comboBox4.ValueMember = "Salary";
con.Close();
}
private void button1_Click(object sender, EventArgs e)
{
Microsoft.Office.Interop.Excel.Application xlApp;
Microsoft.Office.Interop.Excel.Workbook xlWorkBook;
Microsoft.Office.Interop.Excel.Worksheet xlWorkSheet;
object misValue = System.Reflection.Missing.Value;
xlApp = new Microsoft.Office.Interop.Excel.Application();
xlWorkBook = xlApp.Workbooks.Add(misValue);
// xlWorkSheet = (Microsoft.Office.Interop.Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);
xlWorkSheet=(Microsoft.Office.Interop.Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);
int i = 0;
int j = 0;
for (i = 0; i <= dataGridView1.RowCount - 1; i++)
{
for (j = 0; j <= dataGridView1.ColumnCount - 1; j++)
{
DataGridViewCell cell = dataGridView1[j, i];
xlWorkSheet.Cells[i + 1, j + 1] = cell.Value;
}
}
xlWorkBook.SaveAs("csharp.net-informations.xls", Microsoft.Office.Interop.Excel.XlFileFormat.xlWorkbookNormal, misValue, misValue, misValue, misValue, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue);
xlWorkBook.Close(true, misValue, misValue);
xlApp.Quit();
releaseObject(xlWorkSheet);
releaseObject(xlWorkBook);
releaseObject(xlApp);
MessageBox.Show("Excel file created , you can find the file c:\\csharp.net-informations.xls");
}
private void releaseObject(object obj)
{
try
{
System.Runtime.InteropServices.Marshal.ReleaseComObject(obj);
obj = null;
}
catch (Exception ex)
{
obj = null;
MessageBox.Show("Exception Occured while releasing object " + ex.ToString());
}
finally
{
GC.Collect();
}
}
}
private void dataGridView1_CellContentClick(object sender, DataGridViewCellEventArgs e)
{
}
}
}
Darren KangPosted Jul 29, 2014, 5:33 AM
Second use safe casting to retrieve the worksheet after adding a new workbook:
Now to export to PDF just use the following:
Rahul PrasadPosted Jun 9, 2014, 2:50 AM
sorry that I can't figure out where the error occurs, you can check by reading -Export DataTable to Excel using Interop ,which domenstrates this procedure step by step with code.
Alternatively, if you would to like to do it in more simple way, you can choose to use C# DataExport dll which enables you export datatable to commenly used formats like Excel ,PDF,Word,HTML ,etc. Here comes the tutorails in detail.
kind regards