Prakash J

Prakash J

  • NA
  • 3
  • 2k

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

Feb 17 2015 2:03 AM

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 */
}
 
 

Answers (3)