4.4.1.9 Color Bug...what now?

I finally got the new fix to open and save an Excel 2007 File.

However now I have color issues.

I have enclosed a before and after spreadsheet.

Notice how the colors are mangled.

On a side note, if I expand a pivot table on one of the sheets excel fixes the color.


I'll have to suspend development until this issue is resolved as color is not controlled on our end.

Your thoughts?

Hi,

We found the problem, we will fix this issue soon.

Thank you.

Dear Robin,

The color issue is fixed, please try the attached version.

Thank you for being patient.

Regards,

The color is far from fixed.

Look again at the two spreadsheets I gave you.

Look at the before and the after when you run it through the new version.

Take for example the Title sheet, there are two colors on the before, one on the after.

Look at the "Ethnicity_Chart". The pivot table shows two colors before, and only 1 color after Aspose saves it.

Something is still not correct in your code.

Robin

Thank you for pointing it out, we will check it soon.

Please try the attached version. Thank you.

It works. Now I have two small questions/things to test.

How to select all cells and autosize cell width like in excel. If I click on the top left corner of the cell grid, it selects all cells, then I double click on the column splitter in between two column headers and all the columns autosize to the largest width in their column.

Second. Because the spreadsheet attached in the earlier message has tables and pivot charts/tables. I am loading the data into some of the sheets during runtime. How can I refresh everything (pivot data sources) in the workbook before I save it so end users don’t have to open the workbook and manually refresh the data on each sheet.

Thanks for your help.

Hi,

  1. Please use Worksheet.AutoFitColumns () to autosize cell width like in excel.

  2. Currently Aspose.Cells does not support to get/set pivot data from .xlsx files. Counting table’s formula is not supported too. We are working on these features.

Thank you

I just want to be clear on your answers.

I’ll review what I am trying to do with your component.

Users are uploading their spreadsheets into my system. I find the two data sheets (LoanApplication and LoanApplicant). My system opens those two sheets, deletes the rows and inserts the latest data from the system.

The spreadsheet I attached in a earlier message has many pivot tables and charts. They seem to work fine with your component now. However upon re-opening the document, I have to manually click on the refresh button on each pivot table to tell it that the refresh the data from its datasource (the table on the other sheet). I wont to avoid this step because users will forget to refresh or won’t know how. I have already done all of this with an Excel 2007 addin. Excel’s model can do this in code. However I want to evaluate your solution so I can rid myself the client installation and data connection issues.

As for the for your second comment. If I have a formula somewhere that uses the table count function, the spreadsheet will throw an exception when I try to open and save it?

Robin

Hi,

If you only need pivot data be auto refreshed when opening a file, please set the pivot table’s data option to “Refresh on open” when you create the file with MS Excel 2007. This will force the pivot table to auto refresh data when the file is opened in MS Excel.

“If I have a formula somewhere that uses the table count function, the spreadsheet will throw an exception when I try to open and save it?”

Aspose.Cells will not throw an exception if you open and save a file containing unsupported formula. But if you open the file and call Workbook.CalculateFormula(), some exception may be thrown out. You can also use Workbook.CalculateFormula(true) to ignore calculate formula error, it means exceptions will be swallowed.

Thank you

Great, I will try that. I am not too worried about the calculations at the moment. I am more interested in updating the data in the Tables, and having the charts, pivot objects update when you open the spreadsheet.

I think this was the most advanced of scenarios I will be testing. Looking at previous post in 2004 when I last evaluated this product, there were some questions about formula support, I’ll review your formula support in the documentation and see what has changed. Although with Excel updating the calculations when you open the spreadsheet, I’m not sure I will need runtime calculating unless I want to use Excel for advanced calculations without the notion of ever giving the end user the spreadsheet.

Anyway thanks for your prompt replies and fixes.

You are wellcome!

@RobinMarks,

We supported the feature (create, manipulate and refresh Pivot Tables in XLS/XLSX (MS Excel 2007 - 2019)) with enhancements in newer versions. We recommend you to kindly upgrade to and give it a try to latest version of Aspose.Cells. Also, see the document for your reference:
Create Pivot Tables and Pivot Charts