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
// {
// 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);
}
}
AbhijeetPosted Apr 17, 2009, 1:59 AM
This has been resolved.
I just put the line where the error occurs like this -
oWB = (_Workbook)(oXL.Workbooks.Add(System.Reflection.Missing.Value));
and made change in Web.Config where i set
Thanks,
Regards,
Abhijeet
AbhijeetPosted Apr 10, 2009, 5:02 AM
Thanks for the reply.
Haven't got your solution. What exactly you are saying?
Kindly explain.
Thanks
Regards
Abhijeet
Ashish PandeyPosted Apr 8, 2009, 1:19 PM