Tuesday, May 5, 2009

DataSet export to Excel

private void createDataInExcel(DataSet ds)
{
Application oXL;
_Workbook oWB;
_Worksheet oSheet;
Range oRng;
string strCurrentDir = Server.MapPath(".") + "\\reports\\";
try
{
oXL = new Application();
oXL.Visible = false;
//Get a new workbook.
oWB = (_Workbook)(oXL.Workbooks.Add( Missing.Value ));
oSheet = (_Worksheet)oWB.ActiveSheet;
//System.Data.DataTable dtGridData=ds.Tables[0];
int iRow =2;
if(ds.Tables[0].Rows.Count>0)
{
for(int j=0;j {
oSheet.Cells[1,j+1]=ds.Tables[0].Columns[j].ColumnName;

for(int j=0;j {
oSheet.Cells[1,j+1]=ds.Tables[0].Columns[j].ColumnName;
}
// For each row, print the values of each column.
for(int rowNo=0;rowNo {
for(int colNo=0;colNo {
oSheet.Cells[iRow,colNo+1]=ds.Tables[0].Rows[rowNo][colNo].ToString();
}
}
iRow++;
}
oRng = oSheet.get_Range("A1", "IV1");
oRng.EntireColumn.AutoFit();
oXL.Visible = false;
oXL.UserControl = false;
string strFile ="report"+ DateTime.Now.Ticks.ToString() +".xls";//+
oWB.SaveAs( strCurrentDir + strFile,XlFileFormat.xlWorkbookNormal,null,null,false,false,XlSaveAsAccessMode.xlShared,false,false,null,null);
// Need all following code to clean up and remove all references!!!
oWB.Close(null,null,null);
oXL.Workbooks.Close();
oXL.Quit();
Marshal.ReleaseComObject (oRng);
Marshal.ReleaseComObject (oXL);
Marshal.ReleaseComObject (oSheet);
Marshal.ReleaseComObject (oWB);
string strMachineName = Request.ServerVariables["SERVER_NAME"];
Response.Redirect("http://" + strMachineName +"/"+"ViewNorthWindSample/reports/"+strFile);
}
catch( Exception theException )
{
Response.Write(theException.Message);
}
}

No comments:

Post a Comment

 
Locations of visitors to this page