In this article, we are going to see how to open a write a dataset to a excel file and open the excel file in the browser.
In order for this to work, there is an important modification in web.config file. We have to add <identity impersonate="true"> else you will get an 'Access is denied' error.
In the application, we have to add a reference for a COM component called "Microsoft Excel 9.0 object library".
Now we have to just loop through the dataset records and populate to each cell in the excel.
Code:
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<ds.Tables[0].Columns.Count;j++)
// {
// oSheet.Cells[1,j+1]=ds.Tables[0].Columns[j].ColumnName;
//
for(int j=0;j<ds.Tables[0].Columns.Count;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<ds.Tables[0].Rows.Count;rowNo++)
{
for(int colNo=0;colNo<ds.Tables[0].Columns.Count;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);
}
}