Error when opening excel document: "Can't set current serie to secondary axis"

[Dimitri]

Dear Syncfusion,

When I try to open my excel document with "IWorkbook MasterDocWorkbook = MasterExcelDocument.Workbooks.Open(masterExcelDocumentUrl);" it gives me the following error "Can't set current serie to secondary axis". So I have tested this and it crashes on a specific sheet that has a lot of complicated graphs in it. I can't delete this sheet because some people need it. But as for me I don't really need it to complete my software.

So is there a way to get rid of this error and still open the excel document? Or is there a way to maybe ignore a specific tab when opening an excel document or is there any other way to fix this?

[Dimitri]

Here is some more information about my problem. Perhaps it helps to clarify my problem.

Extra Info:

Message = "Can't set current serie to secondary axis"

Source = "Syncfusion.XlsIO.Base"

Excel Version: 2016

The Code: See the zip file in the attachments

The StackTrace:

   at Syncfusion.XlsIO.Implementation.Charts.ChartSerieImpl.ChangeAxis(Boolean bToPrimary)

   at Syncfusion.XlsIO.Implementation.Charts.ChartSerieImpl.set_UsePrimaryAxis(Boolean value)

   at Syncfusion.XlsIO.Implementation.XmlSerialization.Charts.ChartParser.ParseFilteredSeries(XmlReader reader, ChartImpl chart, RelationCollection relations, Boolean is3D, ExcelChartType SeriesType, Boolean secondary)

   at Syncfusion.XlsIO.Implementation.XmlSerialization.Charts.ChartParser.ParseFilterSecondaryAxis(XmlReader reader, ExcelChartType seriesType, Boolean is3D, List`1 lstSeries, ChartImpl chart, RelationCollection relations, ChartSerieImpl& series)

   at Syncfusion.XlsIO.Implementation.XmlSerialization.Charts.ChartParser.ParseBarChartShared(XmlReader reader, ChartImpl chart, RelationCollection relations, Boolean is3D, List`1 lstSeries, String& shape, ExcelChartType& chartType)

   at Syncfusion.XlsIO.Implementation.XmlSerialization.Charts.ChartParser.ParseBarChart(XmlReader reader, ChartImpl chart, RelationCollection relations, Dictionary`2 dictSeriesAxis, ExcelChartType& chartType)

   at Syncfusion.XlsIO.Implementation.XmlSerialization.Charts.ChartParser.ParsePlotAreaGeneral(XmlReader reader, ChartImpl chart, RelationCollection relations, Excel2007Parser parser)

   at Syncfusion.XlsIO.Implementation.XmlSerialization.Charts.ChartParser.ParsePlotArea(XmlReader reader, ChartImpl chart, RelationCollection relations, Excel2007Parser parser)

   at Syncfusion.XlsIO.Implementation.XmlSerialization.Charts.ChartParser.ParseChartElement(XmlReader reader, ChartImpl chart, RelationCollection relations)

   at Syncfusion.XlsIO.Implementation.XmlSerialization.Charts.ChartParser.ParseChart(XmlReader reader, ChartImpl chart, RelationCollection relations)

   at Syncfusion.XlsIO.Implementation.XmlReaders.Excel2007Parser.ParseChartTag(XmlReader reader, ChartImpl chart, RelationCollection relations, FileDataHolder dataHolder, String itemName, Boolean isChartEx)

   at Syncfusion.XlsIO.Implementation.XmlReaders.Excel2007Parser.TryParseChart(MemoryStream data, WorksheetBaseImpl sheet, String drawingPath, Boolean isChartEx)

   at Syncfusion.XlsIO.Implementation.XmlReaders.Excel2007Parser.ParseTwoCellAnchor(XmlReader reader, WorksheetBaseImpl sheet, String drawingsPath, List`1 lstRelationIds, Dictionary`2 dictItemsToRemove)

   at Syncfusion.XlsIO.Implementation.XmlReaders.Excel2007Parser.ParseDrawings(XmlReader reader, WorksheetBaseImpl sheet, String drawingsPath, List`1 lstRelationIds, Dictionary`2 dictItemsToRemove)

   at Syncfusion.XlsIO.Implementation.XmlSerialization.WorksheetDataHolder.ParseDrawings(WorksheetBaseImpl sheet, Relation drawingRelation, Dictionary`2 dictItemsToRemove)

   at Syncfusion.XlsIO.Implementation.XmlSerialization.WorksheetDataHolder.ParseDrawings(WorksheetBaseImpl sheet, String relationId, Dictionary`2 dictItemsToRemove)

   at Syncfusion.XlsIO.Implementation.XmlReaders.Excel2007Parser.ParseDrawings(XmlReader reader, WorksheetBaseImpl sheet, Dictionary`2 dictItemsToRemove)

   at Syncfusion.XlsIO.Implementation.XmlReaders.Excel2007Parser.ParseAfterSheetData(XmlReader reader, WorksheetImpl sheet, MemoryStream& streamCF, String strParentPath, Dictionary`2 dictItemsToRemove)

   at Syncfusion.XlsIO.Implementation.XmlReaders.Excel2007Parser.ParseSheet(XmlReader reader, WorksheetImpl sheet, String strParentPath, MemoryStream& streamStart, MemoryStream& streamCF, List`1 arrStyles, Dictionary`2 dictItemsToRemove, Dictionary`2 dictUpdatedSSTIndexes)

   at Syncfusion.XlsIO.Implementation.XmlSerialization.WorksheetDataHolder.ParseWorksheetData(WorksheetImpl sheet, Dictionary`2 dictUpdateSSTIndexes, Boolean parseOnDemand)

   at Syncfusion.XlsIO.Implementation.WorksheetImpl.ParseData(Dictionary`2 dictUpdatedSSTIndexes)

   at Syncfusion.XlsIO.Implementation.XmlReaders.Excel2007Parser.ParseWorksheets(Dictionary`2 dictUpdatedSSTIndexes, Boolean parseOnDemand)

   at Syncfusion.XlsIO.Implementation.XmlSerialization.FileDataHolder.ParseWorkbook(List`1& themeColors, Boolean parseOnDemand)

   at Syncfusion.XlsIO.Implementation.XmlSerialization.FileDataHolder.ParseDocument(List`1& themeColors, Boolean parseOnDemand)

   at Syncfusion.XlsIO.Implementation.WorkbookImpl.ParseExcel2007Stream(Stream stream, String password, Boolean parseOnDemand)

   at Syncfusion.XlsIO.Implementation.WorkbookImpl.ParseStream(Stream stream, String password, ExcelVersion version, ExcelParseOptions options)

   at Syncfusion.XlsIO.Implementation.WorkbookImpl..ctor(IApplication application, Object parent, Stream stream, ExcelParseOptions options, ExcelVersion version)

   at Syncfusion.XlsIO.Implementation.ApplicationImpl.CreateWorkbook(Object parent, Stream stream, ExcelVersion version, ExcelParseOptions options)

   at Syncfusion.XlsIO.Implementation.Collections.WorkbooksCollection.Open(Stream stream, ExcelVersion version, ExcelParseOptions options)

   at Syncfusion.XlsIO.Implementation.Collections.WorkbooksCollection.Open(Stream stream, ExcelOpenType openType, String fileName, ExcelVersion version, ExcelParseOptions options)

   at Syncfusion.XlsIO.Implementation.Collections.WorkbooksCollection.Open(String fileName, ExcelOpenType openType, ExcelVersion version, ExcelParseOptions options)

   at Syncfusion.XlsIO.Implementation.Collections.WorkbooksCollection.Open(String filename)

   at UpdateMasterExcelDocument.VerwerkingsInfoCollection.GenerateInfo() in ..\VerwerkingsInfoCollection.cs:line 72

   at UpdateMasterExcelDocument.VerwerkingsInfoCollection..ctor() in ..\VerwerkingsInfoCollection.cs:line 26

   at UpdateMasterExcelDocument.MainForm.btnProcessDocuments_Click(Object sender, EventArgs e) in ..\MainForm.cs:line 306

Attachment: UpdateMasterExcelDocument_338804fe.zip

[Konduru Keerthi Konduru Ravichandra Raju]

Hi Dimitri, 

Greetings from Syncfusion. 

On analysis from the stack trace provided, we understood that the exception is thrown while opening the Excel workbook. So, we request you to share the Excel document you are using at your end, which will be helpful for us to investigate further and provide prompt solution at the earliest. 

You can overcome the exception by opening the Excel document with ParseWorksheetsOnDemand option from ExcelParseOptions, which parses the worksheets only when they are accessed. Please find the code snippet below. 

Code Snippet: 

 

IWorkbook workbook = application.Workbooks.Open("Sample.xlsx", ExcelParseOptions.ParseWorksheetsOnDemand); 

Regards, 

Keerthi.