-
June 25th, 2010, 04:36 AM
#1
Excel crashes
A simple code to export data to excel
<pre>
public partial class Form1 : Form
{
Microsoft.Office.Interop.Excel.Application _objAppln;
Workbook _objWorkBook;
Workbooks _objWorkBooks;
Worksheet _objWorkSheet;
public Form1()
{
InitializeComponent();
InitializeExcelObjectModel();
}
~Form1()
{
DisposeExcelObjects();
}
void SaveExcel()
{
_objAppln.DisplayAlerts = false;//Since, we are using SaveFileDialog's overwrite prompt(control is on view).
_objWorkBook.SaveAs("C:\\tmp.xls",
XlFileFormat.xlWorkbookNormal, Type.Missing, Type.Missing, false, Type.Missing, XlSaveAsAccessMode.xlNoChange,
Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
_objWorkBook.Close(true, "C:\\tmp.xls", false);
_objAppln.DisplayAlerts = true;//restore back for other display alerts
}
private void InitializeExcelObjectModel()
{
_objAppln = new Microsoft.Office.Interop.Excel.Application(); // To initialize excel file
//_objAppln.Visible = true;
if (_objAppln != null)
{
_objWorkBooks = _objAppln.Workbooks;
_objWorkBook = _objWorkBooks.Add(Type.Missing); // To add workbook with sheets in excel file
_objWorkSheet = (Worksheet)_objAppln.ActiveSheet; // To get the current active sheet in excel file
}
}
public void DisposeExcelObjects()
{
System.Runtime.InteropServices.Marshal.ReleaseComObject(_objWorkSheet);
System.Runtime.InteropServices.Marshal.ReleaseComObject(_objWorkBook);
System.Runtime.InteropServices.Marshal.ReleaseComObject(_objWorkBooks);
System.Runtime.InteropServices.Marshal.ReleaseComObject(_objAppln);
_objWorkSheet = null; _objWorkBooks = null; _objWorkBooks = null; _objAppln = null;
}
private void button1_Click(object sender, EventArgs e)
{
Range objRange=null;
string cell1 = string.Empty, cell2 = string.Empty;
string[] chars = { "A", "B", "C", "D", "E", "F", "G", "H", "I", "J", "K", "L", "M", "N", "O", "P", "Q", "R", "S", "T" };
for (int row = 1; row < 1000; row++)
{
for (int column = 0; column < 20; column++)
{
cell1 = chars[column] + row.ToString();
objRange = _objWorkSheet.get_Range(cell1, cell1);
objRange.Value2 = cell1;
}
}
System.Runtime.InteropServices.Marshal.ReleaseComObject(objRange);
objRange = null;
SaveExcel();
}
}
</pre>
Step 1: Add the above code to a solution and add reference for Microsoft.Office.Interop.Excel.dll
Step 2: Run the application and Execute the code in button1_Click.
Step 3: While the processing is on, open any other excel file and just click on cells here and there.
COM exception occurs. Anything I am doing wrong above?
How to resolve?
-
June 28th, 2010, 10:55 AM
#2
Re: Excel crashes
Try using the following code, it looks like the interop/automation server is busy when you're trying to hit it because the dll's it needs are already in use.
I made a wrapper function that basically takes any other function (in this case, your unsafe COM calls) as a delegate, and returns an object that you can then cast back to the type you need.
The wrapper function checks to see if it gets a COM exception, and if it does, waits a slice, and tries again; you'll probably want to limit that to certain errors, or a certain number of retries, and have it throw the real error after 50 tries or something like that.
Code:
using System;
using System.Windows.Forms;
using Microsoft.Office.Interop.Excel;
using System.Runtime.InteropServices;
namespace ExcelExport
{
public partial class Form1 : Form
{
Microsoft.Office.Interop.Excel.Application _objAppln;
Workbook _objWorkBook;
Workbooks _objWorkBooks;
Worksheet _objWorkSheet;
public Form1()
{
InitializeComponent();
InitializeExcelObjectModel();
}
private void button1_Click(object sender, EventArgs e)
{
Range objRange = null;
string cell1 = string.Empty, cell2 = string.Empty;
string[] chars = { "A", "B", "C", "D", "E", "F", "G", "H", "I", "J", "K", "L", "M", "N", "O", "P", "Q", "R", "S", "T" };
for (int row = 1; row < 1000; row++)
{
for (int column = 0; column < 20; column++)
{
//simple method call
cell1 = chars[column] + row.ToString();
objRange = (Range) COMExecute(() => _objWorkSheet.get_Range(cell1, cell1));
objRange.Value2 = cell1;
}
}
System.Runtime.InteropServices.Marshal.ReleaseComObject(objRange);
objRange = null;
SaveExcel();
}
~Form1()
{
DisposeExcelObjects();
}
void SaveExcel()
{
_objAppln.DisplayAlerts = false;//Since, we are using SaveFileDialog's overwrite prompt(control is on view).
_objWorkBook.SaveAs("C:\\tmp.xls",
XlFileFormat.xlWorkbookNormal, Type.Missing, Type.Missing, false, Type.Missing, XlSaveAsAccessMode.xlNoChange,
Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
_objWorkBook.Close(true, "C:\\tmp.xls", false);
_objAppln.DisplayAlerts = true;//restore back for other display alerts
}
private void InitializeExcelObjectModel()
{
_objAppln = new Microsoft.Office.Interop.Excel.Application(); // To initialize excel file
//_objAppln.Visible = true;
if (_objAppln == null) return;
_objWorkBooks = _objWorkBooks = _objAppln.Workbooks;
_objWorkBook = _objWorkBook = _objWorkBooks.Add(Type.Missing); // To add workbook with sheets in excel file
_objWorkSheet = (Worksheet) _objAppln.ActiveSheet; // To get the current active sheet in excel file
}
public void DisposeExcelObjects()
{
System.Runtime.InteropServices.Marshal.ReleaseComObject(_objWorkSheet);
System.Runtime.InteropServices.Marshal.ReleaseComObject(_objWorkBook);
System.Runtime.InteropServices.Marshal.ReleaseComObject(_objWorkBooks);
System.Runtime.InteropServices.Marshal.ReleaseComObject(_objAppln);
_objWorkSheet = null; _objWorkBooks = null; _objWorkBooks = null; _objAppln = null;
}
delegate object COMWrapper();
private object COMExecute(COMWrapper executeCommand)
{
bool executeFailed = true;
while (executeFailed)
{
try
{
return executeCommand();
}
catch (COMException ex)
{
System.Threading.Thread.Sleep(50);
if (executeFailed) continue;
}
}
return false;
}
}
}
Hope that helps, it fixed the errors on my box at least.
Tags for this Thread
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|