How can i prevent the COMException 0x80010108 and 0x80010105?

  • Thread starter Thread starter Nilavu
  • Start date Start date
N

Nilavu

Scenario: I am developing a tool using C#, it create a Excel files contains
with 100 pie-Chart with data (I am programmatically created).

While i am start my program, it start creating the excel file and write
chart data and create the pie chart. I specify the folder "C:\SampleChart"
the excel file will be create in the folder.

Every thing is working fine.

While my tool creating the 100 charts, at the time i am open some other
Excel file (for example, i am open the excel file from this location
"D:\test\Test.xls")
and close the excel file i got following the error message.



Unhandled Exception: System.Runtime.InteropServices.COMException
(0x80010108):
The object invoked has disconnected from its clients.
(Exception from HRESULT: 0x80010108 (RPC_E_DISCONNECTED))
at Microsoft.Office.Interop.Excel.WorkbookClass.Close(Object SaveChanges,
Object Filename, Object RouteWorkbook)
at SamplechartReporter.ChartReporter.ObjectCleanUp() in
E:\CARTRD\SamplechartReporter\SamplechartReporter\Program.cs:line 291
at SamplechartReporter.Program.Main(String[] args) in
E:\CARTRD\SamplechartReporter\SamplechartReporter\Program.cs:line 55

Unhandled Exception: System.Runtime.InteropServices.COMException
(0x80010105):
The server threw an exception. (Exception from HRESULT: 0x80010105
(RPC_E_SERVERFAULT))
at System.RuntimeType.ForwardCallToInvokeMember(String memberName,
BindingFlags flags, Object target, Int32[] aWrapperTypes, MessageData&
msgData)
at Microsoft.Office.Interop.Excel.ChartArea.set_Shadow(Boolean )
at SamplechartReporter.ChartReporter.CreatePieChart(Int32 dataStart,
Int32 dataEnd) in
E:\CARTRD\SamplechartReporter\SamplechartReporter\Program.cs:line 231
at SamplechartReporter.ChartReporter.Generate() in
E:\CARTRD\SamplechartReporter\SamplechartReporter\Program.cs:line 196
at SamplechartReporter.Program.Main(String[] args) in
E:\CARTRD\SamplechartReporter\SamplechartReporter\Program.cs:line 52


How can i prevent this error? What is the magic behind
ApplicationClass,Workbook class ?

How can i handle the above errors?


My C# code is below

using System;
using System.Collections.Generic;
using System.Text;
using Microsoft.Office.Interop.Excel;
using System.Runtime.InteropServices;
using System.Data;

namespace SamplechartReporter
{
class Program
{
static void Main(string[] args)
{


//============================================================================
//Pie chart data
System.Data.DataTable PieDataTable = new System.Data.DataTable();

PieDataTable.Columns.Add("", typeof(string));
PieDataTable.Columns.Add("Company1", typeof(string));
PieDataTable.Columns.Add("Company2", typeof(string));
PieDataTable.Columns.Add("Company3", typeof(string));
PieDataTable.Columns.Add("Company4", typeof(string));
PieDataTable.Columns.Add("Company5", typeof(string));
PieDataTable.Columns.Add("Company6", typeof(string));

DataRow PieDataRow = PieDataTable.NewRow();

PieDataRow[0] = "Data";
PieDataRow[1] = "150";
PieDataRow[2] = "60";
PieDataRow[3] = "100";
PieDataRow[4] = "75";
PieDataRow[5] = "50";
PieDataRow[6] = "40";

PieDataTable.Rows.Add(PieDataRow);


//============================================================================

ChartReporter chartReporter = new ChartReporter();

chartReporter.FilePath = @"C:\SampleChart\Piechart.xls";

for (int index = 1; index <= 100; index++)
{
chartReporter.ChartTitle = "Pie Chart Data";
chartReporter.ChartDataTitle = "Pie chart Data";

chartReporter.ChartReportData = PieDataTable;

chartReporter.Generate();

Console.WriteLine("Creating Piechart successfully");
}

chartReporter.SaveExcelFile();
chartReporter.ObjectCleanUp();

Console.WriteLine("All charts successfully");
}
}

public static class ExcelFactory
{
private static ApplicationClass excelApplication = null;

private static readonly object synLock = new object();

public static ApplicationClass GetExcelApplicationInstance()
{
lock (synLock)
{
if (null == excelApplication)
{
excelApplication = new ApplicationClass();
}

return excelApplication;
}
}
}

public class ChartReporter
{
private const int ColNumber = 65; //'A'

private object paramMissing = Type.Missing;

static int lastRow = 0;
static int chartTop = 50;

ApplicationClass excelApplication = null;
Workbook newWorkbook = null;
Worksheet ChartSheet = null;

Worksheet DataSheet = null;
Range dataRange = null;
ChartObjects chartObjects = null;
ChartObject newChartObject = null;

#region Propertys

private string chartTitle = string.Empty;
private string filePath = string.Empty;
private string chartDataTitle = string.Empty;

private System.Data.DataTable chartReportData;


public string ChartTitle
{
get
{
return chartTitle;
}
set
{
chartTitle = value;
}
}

public string FilePath
{
get
{
return filePath;
}
set
{
filePath = value;
}
}

public string ChartDataTitle
{
get
{
return chartDataTitle;
}
set
{
chartDataTitle = value;
}
}

public System.Data.DataTable ChartReportData
{
get
{
return chartReportData;
}
set
{
chartReportData = value;
}
}

#endregion Propertys

public ChartReporter()
{
excelApplication = ExcelFactory.GetExcelApplicationInstance();

newWorkbook =
excelApplication.Workbooks.Add(XlWBATemplate.xlWBATWorksheet);

DataSheet =
(Worksheet)newWorkbook.Worksheets.Add(newWorkbook.ActiveSheet, paramMissing,
paramMissing, paramMissing);
DataSheet.Name = "DataSheet";

ChartSheet =
(Worksheet)newWorkbook.Worksheets.Add(newWorkbook.ActiveSheet, paramMissing,
paramMissing, paramMissing);
ChartSheet.Name = "ChartSheet";
}

public void Generate()
{
int dataStart = 0;
int dataEnd = 0;

lastRow += 2;

//write chart and data title...
WriteChartDataTitle(DataSheet, chartDataTitle);

lastRow += 2;

dataStart = lastRow;

//write column data...
WriteColumnData(DataSheet, chartReportData);

//write row data...
WriteRowData(DataSheet, chartReportData);

dataEnd = lastRow;

CreatePieChart(dataStart, dataEnd);
}

public void CreatePieChart(int dataStart, int dataEnd)
{
int colNumber = ColNumber;
char colChar;

colChar = (char)colNumber;

string startCell = Convert.ToString(colChar) +
Convert.ToString(dataStart);

colChar = (char)(colNumber + chartReportData.Columns.Count - 1);

string endCell = Convert.ToString(colChar) +
Convert.ToString(dataEnd);

dataRange = DataSheet.get_Range(startCell, endCell);

chartObjects =
(ChartObjects)(ChartSheet.ChartObjects(paramMissing));

newChartObject = chartObjects.Add(50, chartTop, 500, 350);

chartTop += 350 + 100;

newChartObject.Chart.SetSourceData(dataRange, XlRowCol.xlRows);
newChartObject.Chart.ChartType = XlChartType.xlPieExploded;
newChartObject.Chart.Legend.Position =
XlLegendPosition.xlLegendPositionTop;

newChartObject.Chart.HasTitle = true;
newChartObject.Chart.ChartTitle.Text = chartTitle;
newChartObject.Chart.ChartTitle.Font.Size = 12;

newChartObject.Chart.PlotArea.Border.LineStyle =
XlLineStyle.xlLineStyleNone;

//color the chartarea, plotarea, and legend..
newChartObject.Chart.ChartArea.Shadow = true;
newChartObject.RoundedCorners = true;
newChartObject.Chart.ChartArea.Fill.ForeColor.SchemeColor = 2;

newChartObject.Chart.ChartArea.Fill.OneColorGradient(Microsoft.Office.Core.MsoGradientStyle.msoGradientHorizontal, 1, (float)0.4);

newChartObject.Chart.PlotArea.Fill.ForeColor.SchemeColor = 2;

newChartObject.Chart.PlotArea.Fill.OneColorGradient(Microsoft.Office.Core.MsoGradientStyle.msoGradientHorizontal, 1, (float)0.4);

newChartObject.Chart.Legend.Fill.ForeColor.SchemeColor = 2;

newChartObject.Chart.Legend.Fill.OneColorGradient(Microsoft.Office.Core.MsoGradientStyle.msoGradientHorizontal, 1, (float)0.4);

SeriesCollection seriesCollection =
(SeriesCollection)newChartObject.Chart.SeriesCollection(Type.Missing);

Series[] seriesArray = new Series[seriesCollection.Count];

for (int seriesIndex = 1; seriesIndex <= seriesCollection.Count;
seriesIndex++)
{
seriesArray[seriesIndex - 1] =
(Series)newChartObject.Chart.SeriesCollection(seriesIndex);
seriesArray[seriesIndex -
1].ApplyDataLabels(XlDataLabelsType.xlDataLabelsShowPercent,
Type.Missing, false, Type.Missing, Type.Missing,
true, Type.Missing, true, Type.Missing, Type.Missing);
}
}

public void SaveExcelFile()
{
try
{
excelApplication.DisplayAlerts = false;

newWorkbook.SaveAs(filePath, paramMissing, paramMissing,
paramMissing, paramMissing, paramMissing,
XlSaveAsAccessMode.xlNoChange, paramMissing, paramMissing,
paramMissing, paramMissing, paramMissing);

excelApplication.DisplayAlerts = true;
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
ObjectCleanUp();
}
}

public void ObjectCleanUp()
{
if (DataSheet != null)
{
Marshal.ReleaseComObject(DataSheet);
DataSheet = null;
}

if (ChartSheet != null)
{
Marshal.ReleaseComObject(ChartSheet);
ChartSheet = null;
}

// Close and release the Workbook object.
if (newWorkbook != null)
{
newWorkbook.Close(false, paramMissing, paramMissing);
Marshal.ReleaseComObject(newWorkbook);
newWorkbook = null;
}

// Quit Excel and release the ApplicationClass object.
if (excelApplication != null)
{
excelApplication.Quit();
excelApplication = null;
}

GC.Collect();
GC.WaitForPendingFinalizers();
GC.Collect();
GC.WaitForPendingFinalizers();
}


public void WriteColumnData(Worksheet targetSheet,
System.Data.DataTable dt)
{
int colNumber = ColNumber;
string cellName = string.Empty;
string dataValue = string.Empty;
char colChar;

foreach (DataColumn col in dt.Columns)
{
colChar = (char)colNumber;

cellName = Convert.ToString(colChar) +
Convert.ToString(lastRow);

dataValue = col.ColumnName;

if (dataValue.IndexOf("Column") == 0)
{
dataValue = "";
}
targetSheet.get_Range(cellName,
cellName).set_Value(XlRangeValueDataType.xlRangeValueDefault, dataValue);

colNumber += 1;
}
}

public void WriteRowData(Worksheet targetSheet,
System.Data.DataTable dt)
{
int colNumber = ColNumber;
string cellName = string.Empty;
string dataValue = string.Empty;
char colChar;

for (int i = 1; i <= dt.Rows.Count; i++)
{
colNumber = ColNumber;
lastRow += 1;

for (int j = 1; j <= dt.Columns.Count; j++)
{
colChar = (char)colNumber;
cellName = Convert.ToString(colChar) +
Convert.ToString(lastRow);
dataValue = dt.Rows[i - 1].ItemArray[j - 1].ToString();

targetSheet.get_Range(cellName,
cellName).set_Value(XlRangeValueDataType.xlRangeValueDefault, dataValue);
colNumber += 1;
}
}
}

public void WriteChartDataTitle(Worksheet targetSheet, string
titleValue)
{
string startCellName = string.Empty;
string endCellName = string.Empty;


startCellName = Convert.ToString((char)ColNumber) +
Convert.ToString(lastRow);

lastRow = lastRow + 2;

char c = (char)(ColNumber + 6);
endCellName = Convert.ToString((char)c) +
Convert.ToString(lastRow);

targetSheet.get_Range(startCellName, endCellName).MergeCells =
true;
targetSheet.get_Range(startCellName,
endCellName).set_Value(XlRangeValueDataType.xlRangeValueDefault, titleValue);
targetSheet.get_Range(startCellName, endCellName).Font.Bold =
true;
targetSheet.get_Range(startCellName, endCellName).Font.Size = 16;
}
}
}
 
Back
Top