I created an Excel file using Aspose. In a sheet there are few rows written using Aspose. But I am facing an issue while trying to fill in the next row manually in the generated Excel. The cells in the new row are copying the color from the previous row when I enter some text and hit enter. After doing a quick google search I found that there is a setting in Excel called ""Extend data range formats and formulas, disabling which stopped the cell from copying the cell color from the cell above. But I want to know how I can achieve the same behavior without needing to disable that setting manually. Is there some programming way to specify about that setting. Note that I am using CellArea to define the formula over the whole range of rows in that column where this is happening.
How do you insert rows in the sheet by Aspose.Cells APIs? Aspose.Cells should work the same way as MS Excel does. Could you create a simple console demo application, zip the project and provide us here (excluding Aspose.Cells assembly) to reproduce the issue on our end, we will check it soon. Also provide us the template file (if any) and attach some screenshots to highlight the issue with the steps (involved) to show the issue. This will help us really to evaluate your issue properly and to figure it out soon.
I will describe here the whole workflow of what I am doing:
In an excel sheet I write a formula in all 1048576 rows. That formula sets some cell style when true. That cell style colors the cell into white using Foreground and fillpattern properties like this:
After all the formulas are filled in in all rows, I do a second iteration, in which I write some values in the cells (which successfully have executed the formula rules as desired). In this step for some cells I fill in some other cell colors while writing the values, which I would expect to overwrite the formula cell colors. But it seems like the formula gets executed once the cell is completely written and thus possibly overwriting the color written here in this step.
To answer your question how I write the rows:
I write rows cell by cell like this:
cell.setValue(“value”);
cell.setStyle();
I use FormatCondition object to specify what to specify the style when a condition is met:
FormatCondition fc = fcs.get(index);
fc.setFormula1(formula);
fc.setStyle(style);
I use CellArea while writing the formula in all the rows. It seems like the normal Excel behavior would say that repetition of a formatting for at least 3 preceding rows would automatically cause the cell in the next row to have the same formatting like the cell in the previous row. But then the question is, is it at all possible to disable this behavior using Aspose (I do not want to use the Excel’s preferences option - the one mentioned in my previous post - to disable this).
Also, could you tell me which style would override the other? - Based on the behavior seen what I could conclude is that the formula execution happens the latest for a cell and that overwrites any coloring style we might have introduced while writing that cell.
I know I am little bit deviated from the problem I stated earlier - but for my use case solving any of these 2 closely related issues will serve the purpose!
We did try your scenario a bit by inserting some rows with data in the sheet. We found when inserting rows, the formattings will always be copied from the last row before the insert position whether disabling extend data range formatting option or not. To evaluate your scenario/issue precisely, we appreciate if you could create a simple sample console (runnable) application (you may pick/use small data range in the sheet to make it simple, so you do not need to write formulas/data in all the rows/cells (1048576)) to provide us here to demonstrate the problem.
We will also try to conduct some tests following your mentioned steps if we could find the issue.