Problem with the pivot table

Aspose.Cells Product Family
1

Hello,

We are in the process of converting our excel 2003 templates to support Excel 2007 format. Those excel templates were built to be compatible with ExcelWriter. To test with Aspose, I have changed all their data markers to Aspose smart marker type and save them in xltm format. After merging data using AsposeDataTable, I found there are two issues with the output file:

1. All columns in the raw data sheet are text type, the numeric columns show a green triangle on the left upper corner with a warning mouse-over message: "The number in this cell is formatted as text or preceded by an apostrophe."

This issue affects the pivot table where it refers to these columns. Their values in the pivot table were shown as 0. If I manually convert some cells to be numeric, the pivot table shows the correct value after refreshing.

2. The Grand Total on pivot table was screwed up after merging with the data. It displays all rows that it uses to summarize below the actual Grand Total row. Please see the attached file.

Please advice.

Thanks,
Michelle

2

Hi,

Thanks for sharing some details with template files

We will look into it soon.

Thank you.

3

For the first problem, can you give us your sample code about how do you populate the data?

It seems that your data are string instead of a number. For example, if you put "2" instead of 2 into a cell, it will display with a green triangle in MS Excel and the pivot table won't work well.

For the second problem, we are still checking it.

4

Thanks for the reply.

We have resolved the 2nd problem regarding to Grand Total in pivot table by ourselves.

For the 1st issue, yes I found the data are String instead of number. We did this specifically because we handle the number formatting in sql before merging with the template, otherwise the default number format from excel will be applied to all number columns. It seems ExcelWriter is smart enough to detect the object type and convert the value to be number while maintaining the original format. Is this feature available in Aspose as well?

Since we have too many templates with number columns, we would prefer handling this in our code instead of changing formatting in the templates. Below is the code to merge the data:

if (queries != null) {

for (int i = 0; i < queries.size(); i++) {

QueryDefBean queryDef = (QueryDefBean) queries.elementAt(i);

QueryResultBean queryResult = (QueryResultBean) queryResults.elementAt(i);

_logger.info("queryResult size is " + queryResult.dataRows.size());

AsposeDataTable dataTable = new QueryResultDataTable(queryResult);

String smartMarkerName = StringUtils.replace(queryDef.name, " ", "");

designer.setDataSource(smartMarkerName, dataTable);

// wb.setConvertNumericData(true);

}

}

//--- Process the template to populate it with the Data Source data.

designer.process();

Thanks for your help.

-Michelle

5
Hi Mchelle,
Good to know that you have resolved the Grand total issue.
For your 1st issue, we have supported a new flag for SmartMarkers, i.e.., "numeric", so, the String values will be converted to numeric values if it can be converted while processing a marker with this flag set. For example, just change your marker "&=FormStatusData.[LEVEL IN ORG]" to "&=FormStatusData.[LEVEL IN ORG](numeric)", the filled values would be converted to numeric values for you need. We will provide you a new fix soon for this feature.
For your 2nd issue, I think it is because there are smart markers in the view area of PivotTable, when you process smart markers, they are extended and filled with data too. For resolving the issue, we are considering to ignore the smart markers in the view area of PivotTable. Would you please share us your resolution/solution for it if you have? It can help us to choose the best way to resolve such issues.
Thank you.
6

Hi,

Thank you for considering Aspose.

Please try this new fix, as we said in the previous reply, we have supported the flag “numeric” for SmartMarker.

For your 2nd issue, we have changed the logic of processing smart markers. If a marker is in the view area of a PivotTable, it will be ignored. Any way, we would appreciate if you can share your solution.

Thank You & Best Regards,

7

Thanks for the quick turnaround.

I will try the fix for numeric columns and let you know the result. Just wondering if the display value will keep the original formatting of the string, or the excel number formatting will be applied?

For the pivot table issue, I think what you mentioned is the correct root cause. I simply relabeled all smart markers in pivot table to be some other texts, the Grand Total then works fine.

Thanks,

Michelle

8

Hi,

Thank you for sharing your solution. When creating a PivotTable as a template that uses a field with Smart marker, the marker will be copied automatically to the cell in the view area of the PivotTable. So, I think ignoring those markers in the view area of PivotTable is better and more convenient for you to create such templates.

For your other question, " if the display value will keep the original formatting of the string, or the excel number formatting will be applied?", Well, the excel number formatting will be applied to the appended cells when processing those markers.


Thank you.