Why am I getting a Type Mismatch when assigning a Worksheet to a Worksheet var?

-1

I am trying to incorporate Aspose Cells for .NET into this class that is using Excel Interop (rather than rewrite the whole thing to use Aspose Cells, which I am just now trying out, I want to at least start with a hybrid approach).

The problem is that I get a "Type mismatch" exception with this code:

private Workbook _xlBook;
. . .
Worksheet asposePT = _xlBook.Worksheets[_xlBook.Worksheets.Add()];

My project has a reference to Aspose.Cells, version 16.11.0.0 (Runtime version v4.0.30319)

I have these usings in the class file:

using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Drawing;
using System.IO;
using System.Linq;
using System.Threading;
using System.Windows.Forms;
using ReportRunner.SharedCode;
using Microsoft.Office.Interop.Excel;
using System.Runtime.InteropServices;
using System.Text;
using Excel = Microsoft.Office.Interop.Excel;

This is the exception detail:

System.Runtime.InteropServices.COMException was caught
  HResult=-2147352571
  Message=Type mismatch. (Exception from HRESULT: 0x80020005 (DISP_E_TYPEMISMATCH))
  Source=System.Dynamic
  ErrorCode=-2147352571
  StackTrace:
       at System.Dynamic.ComRuntimeHelpers.CheckThrowException(Int32 hresult, ExcepInfo& excepInfo, UInt32 argErr, String 
message)
       at CallSite.Target(Closure , CallSite , ComObject , Object )
       at System.Dynamic.UpdateDelegates.UpdateAndExecute2[T0,T1,TRet](CallSite site, T0 arg0, T1 arg1)
       at CallSite.Target(Closure , CallSite , Sheets , Object )
       at System.Dynamic.UpdateDelegates.UpdateAndExecute2[T0,T1,TRet](CallSite site, T0 arg0, T1 arg1)
       at ReportRunner.ProduceUsage.ProduceUsageRpt.PopulatePivotTableSheetAsposeCells() in c:\Projects\ReportRunner - 
Private Build\ReportRunner\ProduceUsage\ProduceUsageRpt.cs:line 1550
       at ReportRunner.ProduceUsage.ProduceUsageRpt.GenerateProduceUsageRpt() in c:\Projects\ReportRunner - Private Build
\ReportRunner\ProduceUsage\ProduceUsageRpt.cs:line 167
  InnerException: 
ProduceUsageRpt.cs:line 1550 is:
Worksheet asposePT = _xlBook.Worksheets[_xlBook.Worksheets.Add()];

Do I need a "using Aspose" or something like that? Do I need to explicitly declare Worksheet as an Aspose Worksheet (as opposed to Excel Interop), or what?

UPDATE

Even when I changed the code from this:

Worksheet asposePT = _xlBook.Worksheets[_xlBook.Worksheets.Add()];
asposePT.Name = "AsposePivotTable";
Aspose.Cells.Pivot.PivotTableCollection pivotTables = asposePT.PivotTables();

...to this (not parens are removed at end of last line):

Aspose.Cells.Worksheet asposePT = _xlBook.Worksheets[_xlBook.Worksheets.Add()];
asposePT.Name = "AsposePivotTable";
Aspose.Cells.Pivot.PivotTableCollection pivotTables = asposePT.PivotTables;

...I still get the same exception.

worksheet
type-mismatch
aspose-cells
asked on Stack Overflow Nov 15, 2016 by B. Clay Shannon • edited Nov 15, 2016 by B. Clay Shannon

1 Answer

2

Well, Aspose.Cells APIs (an independent .NET library) and MS Excel Interop (Office Automation) APIs are different in many ways, both have different architectures, so you cannot just parse objects regarding their API b/w them. I think you should use only Aspose.Cells APIs to instantiate a Workbook (spreadsheet), add/update or manipulate data into the worksheets, etc. and finally save the Excel file. I guess your underlying _xlBook object might be culprit which you might have instantiated with respect to MS Excel Interop. APIs.

I am working as Support developer/ Evangelist at Aspose.

answered on Stack Overflow Nov 15, 2016 by Amjad Sahi • edited Jan 3, 2019 by Adrita Sharma

User contributions licensed under CC BY-SA 3.0