Hello,
I am using following code to generate Excel file for large number of data (15000 + rows). I am trying to write that in Excel file so that user can open that excel file and check the data.
as this requirement is urgent and dont have much time, kindly help me out why this error is occured and what could be the solution for the same.
Code -
"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 )); //I am getting error here The complete stack trace is mentioned here
{System.Runtime.InteropServices.COMException (0x800A03EC): Microsoft Office Excel cannot open or save any more documents because there is not enough available memory or disk space.
• To make more memory available, close workbooks or programs you no longer need.
• To free disk space, delete files you no longer need from the disk you are saving to.
at Microsoft.Office.Interop.Excel.Workbooks.Add(Object Template)
at GridFinal.FpLoadEditData(ArrayList userName, ArrayList siteId, String from, String to) in .............................................
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);
}
}