Dataset to Excel sheets using interop.excel generation in C#

Question

Dataset to Excel sheets using interop.excel generation in C#.net

 

protected void Page_Load(object sender, EventArgs e)

{

if (!IsPostBack)

{

getquery();

}

}

void getquery()

{

con = new SqlConnection(strcon);

con.Open();

string query = " Select top 100 * from getezee; ";

cmd = new SqlCommand(query,con);

SqlDataAdapter da = new SqlDataAdapter(cmd);

ds = new DataSet();

da.Fill(ds);

con.Close();

}

protected void excel_generation_Click(object sender, EventArgs e)

{

try

{

Export_Excel(ds);

}

catch (Exception ex)

{

throw ex;

}

}

public void Export_Excel(DataSet ds)

{

Microsoft.Office.Interop.Excel.Application objXL = null;

Microsoft.Office.Interop.Excel.Workbook objWB = null;

try

{

objXL = new Microsoft.Office.Interop.Excel.Application();

objWB = objXL.Workbooks.Add(ds.Tables.Count);

int sheetcount = 1;

foreach (System.Data.DataTable dt in ds.Tables)

{

Microsoft.Office.Interop.Excel.Worksheet objSHT = (Microsoft.Office.Interop.Excel.Worksheet)objWB.Sheets.Add();

objSHT.Name = "SheetData" + sheetcount.ToString();

for (int j = 0; j < dt.Rows.Count; j++)

{

for (int i = 0; i < dt.Columns.Count; i++)

{

if (j == 0)

{

objSHT.Cells[1, i + 1] = dt.Columns[i].ColumnName.ToString();

}

objSHT.Cells[j + 2, i + 1] = dt.Rows[j][i].ToString();

}

}

sheetcount++;

}

objWB.Saved = true;

objWB.SaveCopyAs("C:\\" + Guid.NewGuid().ToString() + ".xlsx");

objWB.Close();

objXL.Quit();

}

catch (Exception ex)

{

objWB.Saved = true;

objWB.Close();

objXL.Quit();

Response.Write("Illegal permission");

}

}

public override void VerifyRenderingInServerForm(Control control)

{

/* Verifies that the control is rendered */

}