Finally, I came up with a solution that is fairly good.
I first use Office XP Web Components to create the Excel Worksheet and apply all the necessary formatting. Then I check if my spreadsheet is multisheet or not.
If it is not multisheet, I export the Spreadsheet as HTML Spreadsheet, which is readable by both Office 2000 and XP.
If it is multisheet, I export the Spreadsheet as Spreadsheet XML which is readable only by Office XP. Then I create a Excel 2002 Application on the server, open the SpreadSheet XML (by OpenXML()) and save it as a Excel Normal Workbook. So using Excel I save the Spreadsheet XML in Excel binary format which is compatible with Office 2000 also.
In this case Excel terminates properly because I instantiate the object inside its own method without the presence of a DataTable because I have already created the Spreadsheet XML file and disposed the DataTable.
Here is the code:
----------------------------------------------------------------------------------------------------
static private void ExportToExcel(string Filename,System.Web.UI.Page page,System.Data.DataTable DT,string[] colNames)
{
System.Web.HttpResponse res=page.Response;
res.ContentType="application/vnd.ms-excel";
res.AppendHeader("content-disposition", "attachment; filename="+Filename);
res.Charset="";
int num_rows=DT.Rows.Count;
//MaxRowsPerSheet must be the maximum number of rows plus 1
int MaxRowsPerSheet=64001;
int rowIndex=MaxRowsPerSheet;
int colIndex=1;
int sheetIndex=0;
System.Threading.Thread.CurrentThread.CurrentCulture=System.Globalization.CultureInfo.CreateSpecificCulture("en-US");
OWC10.Spreadsheet oExcel=new OWC10.Spreadsheet();
OWC10.Workbook WB=oExcel.ActiveWorkbook;
OWC10.Worksheet sheet=null;
OWC10._Range range=null;
while(WB.Worksheets.Count>1)
((OWC10.Worksheet)WB.Worksheets["Sheet"+WB.Worksheets.Count]).Delete();
foreach(DataRow row in DT.Rows)
{
if (rowIndex==MaxRowsPerSheet)
{
rowIndex=1;
colIndex=1;
sheetIndex++;
if (sheet!=null)
sheet=(OWC10.Worksheet)WB.Worksheets.Add(Missing.Value,sheet,Missing.Value,Missing.Value);
else
sheet=oExcel.ActiveSheet;
if (colNames==null)
{
foreach(DataColumn col in DT.Columns)
{
sheet.Cells[rowIndex,colIndex++]=col.ColumnName.ToString();
}
}
else
{
foreach(string str in colNames)
{
sheet.Cells[rowIndex,colIndex++]=str.ToString();
}
}
rowIndex++;
sheet.Name="Sheet"+(sheetIndex);
}
colIndex=1;
foreach(object item in row.ItemArray)
{
sheet.Cells[rowIndex,colIndex]=item.ToString();
range=(OWC10._Range)sheet.Cells[rowIndex,colIndex];
if (item.ToString().Length>11 && range.get_NumberFormat().ToString()!="#")
{
bool IsPhoneNumber=true;
string strItem=item.ToString();
for(int i=0;i<strItem.Length;i++)
{
if (!Char.IsDigit(strItem,i))
{
IsPhoneNumber=false;
break;
}
}
if (IsPhoneNumber)
range.EntireColumn.set_NumberFormat("#");
}
colIndex++;
}
rowIndex++;
}
for(int i=1;i<=WB.Worksheets.Count;i++)
{
sheet=(OWC10.Worksheet)WB.Worksheets["Sheet"+i];
int num_cols=DT.Columns.Count;
for(int j=1;j<=num_cols;j++)
{
range=(OWC10.Range)sheet.Cells[1,j];
range=range.EntireColumn;
range.AutoFit();
range.set_HorizontalAlignment(OWC10.XlHAlign.xlHAlignRight);
}
}
((OWC10.Worksheet)oExcel.Worksheets["Sheet1"]).Activate();
string filename=page.Server.MapPath("/APP_TPH_local/temp")+"\\"+page.Session.SessionID;
if (DT.Rows.Count<MaxRowsPerSheet)
{
oExcel.Export(filename+".xls",OWC10.SheetExportActionEnum.ssExportActionNone,OWC10.SheetExportFormat.ssExportHTML);
}
else
{
oExcel.Export(filename+".xml",OWC10.SheetExportActionEnum.ssExportActionNone,OWC10.SheetExportFormat.ssExportAsAppropriate);
ReturnExcelFile(filename);
System.IO.File.Delete(filename+".xml");
}
oExcel=null;
System.IO.FileStream input=new System.IO.FileStream(filename+".xls",System.IO.FileMode.Open);
System.IO.Stream output=res.OutputStream;
int len=(int)input.Length;
byte[] buffer=new byte[len];
input.Read(buffer,0,len);
output.Write(buffer,0,len);
input.Close();
output.Close();
System.IO.File.Delete(filename+".xls");
}
private static void ReturnExcelFile(string filename)
{
Excel._Application ExcelApp=new Excel.Application();
Excel._Workbook ExcelWB=ExcelApp.Workbooks.OpenXML(filename+".xml",Missing.Value);
ExcelWB.SaveAs(filename+".xls",Excel.XlFileFormat.xlWorkbookNormal,Missing.Value,Missing.Value,Missing.Value,Missing.Value,Excel.XlSaveAsAccessMode.xlExclusive,Missing.Value,Missing.Value,Missing.Value,Missing.Value,Missing.Value);
ExcelWB.Close(Missing.Value,Missing.Value,Missing.Value);
ExcelApp.Workbooks.Close();
ExcelApp.Quit();
System.Runtime.InteropServices.Marshal.ReleaseComObject(ExcelWB);
System.Runtime.InteropServices.Marshal.ReleaseComObject(ExcelApp);
ExcelWB=null;
ExcelApp=null;
GC.Collect();
GC.WaitForPendingFinalizers();
}