This is my attempt at creating an excel sheet from in asp.net with C#. I think I'm close to getting there. I got most of this code from eggheadcafe. The line oWB.SaveAs(...) always throws this error Error: Object reference not set to an instance of an object. Line: Interop.Excel Anyone see what the hell is wrong
private void Page_Load(object sender, System.EventArgs e)
{
string strCurrentDir = Server.MapPath(".") + "\\";
RemoveFiles(strCurrentDir); // utility method to clean up old files
Excel.Application oXL;
Excel._Workbook oWB;
Excel._Worksheet oSheet;
Excel.Range oRng;
try
{
oXL = new Excel.Application();
oXL.Visible = true;
//Get a new workbook.
oWB = (Excel._Workbook)(oXL.Workbooks.Add( Missing.Value ));
oSheet = (Excel._Worksheet)oWB.ActiveSheet;
//Format A1:Z1 as bold, vertical alignment = center.
oSheet.get_Range("A1", "Z1").Font.Bold = true;
oSheet.get_Range("A1", "Z1").VerticalAlignment =Excel.XlVAlign.xlVAlignCenter;
//AutoFit columns A:Z.
oRng = oSheet.get_Range("A1", "Z1");
oRng.EntireColumn.AutoFit();
oXL.Visible = true;
oXL.UserControl = true;
string strFile ="report" + System.DateTime.Now.Ticks.ToString() +".xls";
strFile = strCurrentDir + strFile;
oWB.SaveAs( strFile,Excel.XlFileFormat.xlWorkbookNormal,null,null,false,false,Excel.XlSaveAsAccessMode.xlShared,false,false,null,null,null);
//Need all following code to clean up and extingush all references!!!
oWB.Close(null,null,null);
oXL.Workbooks.Close();
oXL.Quit();
System.Runtime.InteropServices.Marshal.ReleaseComObject (oRng);
System.Runtime.InteropServices.Marshal.ReleaseComObject (oXL);
System.Runtime.InteropServices.Marshal.ReleaseComObject (oSheet);
System.Runtime.InteropServices.Marshal.ReleaseComObject (oWB);
oSheet=null;
oWB=null;
oXL = null;
GC.Collect(); // force final cleanup!
string strMachineName = Request.ServerVariables["SERVER_NAME"];
Label1.Text="
Download Report";
}
catch( Exception theException )
{
String errorMessage;
errorMessage = "Error: ";
errorMessage = String.Concat( errorMessage, theException.Message );
errorMessage = String.Concat( errorMessage, " Line: " );
errorMessage = String.Concat( errorMessage, theException.Source);
Label1.Text= errorMessage ;
}
}