How to turn off "Extend data range formats and formulas" setting in Java API for Asopse

Hi there!

I am facing issues such as my cell coloring or locks are being copied on next rows. After searching the internet I learnt about a setting called “Extend data range formats and formulas” which we can disable to avoid this behavior. Can you please tell me how to disable that in Java API? What class or method provides that option?

@codersingh,

Thanks for your query.

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.

Thanks for your reply! While I gather all that could you tell me if there is such setting control in Aspose like in Excel “Extend data range formats and formulas”? I have tried it in my Excel app on my Mac and noticed that turning off this setting actually prevents copying over formatting and formulas specially when there are ranges involved in previous cells.

@ahsaniqbalsidiqui,

Thanks for your reply!

    Workbook workbook = new Workbook();
    Worksheet worksheet = workbook.getWorksheets().get(0);
    Cells cells = worksheet.getCells();
    Cell cell = cells.get(0, 0);
    cell.setValue("coder");
    Style style = cell.getStyle();
    style.setForegroundColor(Color.getOrange());
    style.setPattern(BackgroundType.SOLID);
    style.setLocked(true);
    cell.setStyle(style);

    cell = cells.get(1, 0);
    cell.setValue("singh");
    style = cell.getStyle();
    style.setForegroundColor(Color.getOrange());
    style.setPattern(BackgroundType.SOLID);
    style.setLocked(true);
    cell.setStyle(style);

    cell = cells.get(2, 0);
    cell.setValue("trying");
    style = cell.getStyle();
    style.setForegroundColor(Color.getOrange());
    style.setPattern(BackgroundType.SOLID);
    style.setLocked(true);
    cell.setStyle(style);

    cell = cells.get(3, 0);
    style = cell.getStyle();
    style.setPattern(BackgroundType.SOLID);
    cell.setValue("check");
    cell.setStyle(style, false);

    workbook.save("test.xlsm");            

In the above code I did not lock 4’th cell but the file generated test.xlsm will show that 4’th cell is also locked.

Could you tell me if there is such setting control in Aspose like in Excel “Extend data range formats and formulas”? I have tried it in my Excel app on my Mac and noticed that turning off this setting actually prevents copying over formatting and formulas specially when there are ranges involved in previous cells.

@codersingh,

We were able to observe the issue but we need to look into it more. We have logged the issue in our database for investigation and for a fix. Once, we will have some news for you, we will update you in this topic.

This issue has been logged as

CELLSJAVA-42818 - Support for Extend data range formats and formulas

@codersingh,

We did evaluate your requested feature a bit. Well, your example code has nothing to do with insert options or somethings related with “Extend data range formats and formulas”. In the code you just get (instantiates) one cell and sets style for it. All properties of the style have default value, such as for the property “Locked” the default value is “true”, it is same with MS Excel’s behavior. That is why you can get “true” value for the cell’s style even though it has not been set explicitly, see the screenshot for your reference. I just opened a new Workbook and right click on a blank cell to show the Format Cells… dialog box in the default “Sheet1” and checked/confirmed the Locked attribute is enabled/checked: