The simplest way to export data from dataset to Excel

Step 1. Create a dynamic datagrid.
Step 2: Set the datasource of datagrid to dataset.
Step 3: Render the datagrid to Excel sheet.

This Button click event will establish connection with oracle and fill the dataset with the data from the acc_system_moniter table.

protected void btnExport_Click(object sender, EventArgs e)
{
OracleConnection con = new OracleConnection();
string strCon = "Data Source=CONF_INS;
User Id=INS;Password=INS555;Integrated Security=no;";
con.ConnectionString = strCon;

OracleCommand cmd = new OracleCommand();
cmd.Connection = con;
cmd.CommandText = "select * from acc_system_monitor";
cmd.CommandType = CommandType.Text;

OracleDataAdapter da = new OracleDataAdapter(cmd);
DataSet ds = new DataSet();
da.Fill(ds);
vikramWB(ds);
}


This function will create a dynamic dataGrid and bind the datagrid with the datset which was passed as an argument to this function. Then it render the dataGrid to the HtmlTextWriter

public static void vikramWB(DataSet ds)
{
System.Web.UI.WebControls.DataGrid grid =
new System.Web.UI.WebControls.DataGrid();
grid.HeaderStyle.Font.Bold = true;
grid.DataSource = ds;
grid.DataMember = ds.Tables[0].TableName;

grid.DataBind();

//StreamWriter will write the excel file
using (StreamWriter sw = new StreamWriter("c:\\test.xls"))
{
//HtmlTextWriter constructer will
//take StringWriter as an argument.
using (HtmlTextWriter hw = new HtmlTextWriter(sw))
{
//The Grid RenderControl will render
// the grid to the HtmlTextWriter
grid.RenderControl(hw);
}
}
}

Post Reference: Vikram Aristocratic Elfin Share