Invalid Formula from SUMIF

Aspose.Cells Product Family
1

I am using your product through LogiXML 9 to populate an Excel Template. This report used to work with the prior release, but now I encounter the following error

Message:
Error in Cell: P561-Invalid formula:"=SUMIF(#REF!:OFFSET(T562,-1,0),1,#REF!:OFFSET(P562,-1,0))".
Source:
Aspose.Cells
Stack Trace:
at Aspose.Cells.ਾ.ి(Cell ஑)
at Aspose.Cells.Cell.set_Formula(String value)
at Aspose.Cells.Cell.౲(UInt16 ਜ, Int32 ௯, UInt16 ৤)
at Aspose.Cells.Cells.DeleteRows(Int32 rowIndex, Int32 rowNumber)
at Aspose.Cells.Cells.DeleteRow(Int32 rowIndex)
at rdExcelTemplate.rdExcelTemplate.a()
at rdExcelTemplate.rdExcelTemplate.subMakeExcel(XmlElement xmlTemplateDefinition, XmlDocument xmlDataSource)
at rdServer.rdTemplate.sBuildTemplateReport(XmlDocument xmlSettings)
at rdServer.ResponseBuilder.BuildResponse()



I am using the SUMIF function to calculate group and grand totals for a few columns. The "REF1" used to point to the starting lines of the data set.

Any suggestions?

Thanks,
Troy
2

Hi Troy,

Could you post your template file here, we will check it soon.

Thank you.

3

Hey Troy,

Thank you for your post and for sharing about this issue. After reviewing your post I was hoping that you could share a bit more about LogiXML and Aspose. More specifically I was curious if the LogiXML package allows you to access Aspose components directly or if you work through their API (which then references our products)? I thank you again for your post and for any information you may be able to share.

4

Here is a copy of the template file. I believe the problem is coming from the formulas used to create the group and grand totals on the ReportType(n) sheets.

In order to upload the file I had to change the type from .XLT to a .XLS file.

-Troy

5

LogiXML is a web-based reporting engine. (http://logixml.com)

We are using it to create reports from Oracle databases. Our implementation uses a standard format (CSS styling) to create a consistent look and feel for reports. LogiXML creates table-based web layout.

LogiXML reports can be extended in a number of different ways. They can incorporate user interaction with the data to sort, filter, drill-down, etc. They also have several ways to export the report data - Word, PDF, and Excel.

The way I discovered that they use your product is through their Excel Template capability. This is different style of report that does not create a web layout; rather it pulls the data then pushes it into an excel template. LogiXML provides grouping and sub-data table capabilities. The template can include formulas to provide additional logic on the data. We have used this format in cases where the users will want to add their own information to the report or if the formatting requires the precise control of Excel.

That’s a very brief description of LogiXML, I hope it helps. Feel free to contact me if you have other questions.

Thanks,
Troy

6

Hi Troy,

Thanks for providing us the template file.

Which version of Aspose.Cells for .NET you are using. I have used your template file a bit with the attached version and it works fine. Could you try the attached version and tell us if it works fine now.

If you still find the issue, could you post your sample console App. with excel file(s) to reproduce the issue, we will check your issue soon.

Thank you.

7

We have two different version of Aspose.Cells.

The template file works fine with version 4.1.05 (LogiXML version 8)

When we upgraded to LogiXML version 9 we now have Aspose.Cells version 4.4.05. This is the version that is causing the error.

I tried replacing the Aspose.Cells.dll with the one you posted, but when I try to run the template report I get a new error message.

—beg
Could not load file or assembly ‘Aspose.Cells, Version=4.4.0.5,
Culture=neutral, PublicKeyToken=716fcc553a201e56’ or one of its dependencies.
The located assembly’s manifest definition does not match the assembly
reference. (Exception from HRESULT: 0x80131040)
—end

Any suggestions are greatly appreciated.

Thanks,
Troy

8

Hi Troy,

Could you try to re-build the project using the version I attached on the server before running it. In VS.NET you may simply remove the older Aspose.Cells component's reference and then add reference to the new Aspose.Cells.dll fix.

Thank you.

9

LogiXML is a commercial product we use. I do not have the ability to re-build it, if that is what you are referring to.

Their tool would be the one calling Aspose.Cells.

UPDATE: I posted to their forum asking about this same error and about trying a new version of your tool. The link to the forum thread is here.

10

I found a work-around!

I reworked the sub-total formulas to not use the OFFSET function. Instead I added the group IDs and a copy of the numeric data to be summed in some extra columns that are hidden. Then I use a SUMIF over the entire column based on matching IDs.