Remove XLA references

Aspose.Cells Product Family
1
Hello.

We have an XLA for our functions, but when we upload file to server, we should remove all references to this XLA. How can i do this?

@Test
public void removeXLATest() throws Exception {
Workbook workbook = new Workbook("D:\\in.xlsx");

Cells cells = workbook.getWorksheets().get(1).getCells();

Cell cell = cells.get("G12");

//With xla
System.out.println(cell.getFormula());
// TODO Some actions with cell or workbook
//Without xla
System.out.println(cell.getFormula());
}

Will we nice, if i will have ability to perform replace by xla's name. For example
worbook.removeXla("FunctionsList.xla")

Best regards. Alexey
2

Hi Alexey,

Thanks for your posting and using Aspose.Cells.

Please try Workbook.removeExternalLinks() methods, it should remove all of your XLA references.

I have tested this issue with the following code and it works fine. I have attached the output Excel file for your reference.

Java


String filePath = “F:\Shak-Data-RW\Downloads\in.xlsx”;


Workbook workbook = new Workbook(filePath);


workbook.removeExternalLinks();


workbook.save(filePath + “.out.xlsx”);

3

Hello.


So before remove external links i had formula
=‘D:\asuproject\designer\designer\UsoiTemplateAddin\UsoiTemplateAddin\bin\Debug\FunctionListAddin.xla’!USVAL($A$1&".D.FACT.OIL_PRODUCTION_KOEF.O.TO",$A11,$A$3)

after remove external link i have no more formula
null

There is exists a way to leave in cell this formula (without external path)
=USVAL($A$1&".D.FACT.OIL_PRODUCTION_KOEF.O.TO",$A11,$A$3)

Best regards. Alexey
4

Hi Alexey,

Thanks for your posting and using Aspose.Cells.

As a workaround, you can find and replace all your .XLA references using Aspose.Cells API.

Please see the following code. It finds all the cells with the formulas that contain “.xla’” string and then replaces the formulas by removing “'D:\asuproject\designer…” string. You will have to make it little general and not use the hard coded substring value which I am using. You should modify the code as per your needs.

I have attached the output Excel file generated by it for your reference. As you can see, all the xla references have been removed but the formulas are retained inside it.

Java


String filePath = “F:\Shak-Data-RW\Downloads\in.xlsx”;


Workbook workbook = new Workbook(filePath);


String strToFind = “.xla<span class=“str”>’!”;

FindOptions options = new FindOptions();

options.setLookAtType(LookAtType.CONTAINS);

options.setLookInType(LookInType.ONLY_FORMULAS);



for (int i = 0; i < workbook.getWorksheets().getCount(); i++)

{

Worksheet worksheet = workbook.getWorksheets().get(i);


Cell found = null;


do

{

found = worksheet.getCells().find(strToFind, found, options);


if (found == null)

break;


//Remove XLA reference from the formula

String formula = found.getFormula();


//int idx1 = formula.indexOf("’");

//int idx2 = formula.indexOf("’", idx1 + 1);


//String subStr = formula.substring(idx1, idx2 - idx1 + 3);


String subStr = “‘D:\asuproject\designer\designer\UsoiTemplateAddin\UsoiTemplateAddin\bin\Debug\FunctionListAddin.xla’!”;


String newFormula = formula.replace(subStr, “”);


System.out.println("----------------------------------------------");

System.out.println(subStr);

System.out.println(formula);

System.out.println(newFormula);

System.out.println("----------------------------------------------");


found.setFormula(newFormula);


} while (true);

}


workbook.save(filePath + “.output.xlsx”);

5

Hello Shakeel.


I have implemented already like this code. I just think, that probably you have embedded tool for do this.

Thank you very much. Alexey
6

Hi Alexey,

Thanks for your posting and using Aspose.Cells.

It is good to know that you were able to sort out this issue. For a time being, you can use this workaround. We will discuss your requested feature with the development team and log it in our database so that our future versions could support this feature.

7

Hi Alexey,

Thanks for using Aspose.Cells.

We have logged this issue in our database for investigation. We will look into it and see if this feature could be supported in our future versions. Once, there is some fix or other update for you, we will let you know asap.

This issue has been logged as

  • CELLSJAVA-41069 - Remove XLA references

8

Hi Alexey,

Thanks for using Aspose.Cells for Java.

Please download and try this fix: Aspose.Cells for Java v8.2.2.2 and let us know your feedback.

Please try the following code:

Java


ExternalLinkCollection links = workbook.getWorksheets().getExternalLinks();

for(int i = links.getCount()-1; i>-1; i–)

{

ExternalLink link = links.get(i);

if(link.getDataSource().endsWith(“FunctionListAddin.xla”))

{

link.setDataSource(“”);

}

}

workbook.save(“res.xlsx”);


9

Hello guys.


Now my first test returns

=‘D:\asuproject\designer\designer\UsoiTemplateAddin\UsoiTemplateAddin\bin\Debug\FunctionListAddin.xla’!USVAL($A$1&".D.FACT.OIL_PRODUCTION_KOEF.O.TO",$A11,$A$3)
=’’!USVAL($A$1&".D.FACT.OIL_PRODUCTION_KOEF.O.TO",$A11,$A$3)

but ‘’!USVAL is wrong start for function is it possible to remove ‘’! from formula, when datasource is empty
10

Hi Alexey,

Thanks for your posting and using Aspose.Cells.

Please provide us your runnable sample code and the source file containing your formulas which are not working fine. Delete all other formulas which are working fine.

Also, this forumla does not look a valid formula. Because it is beginning from ‘’.

=’’!USVAL($A$1&".D.FACT.OIL_PRODUCTION_KOEF.O.TO",$A11,$A$3)

11

A target of this post was remove reference to our xla and make cell’s formula valid. But last way of removing ExternalLinks not provided this ability.


I take “in.xlsx” from my first message and wrote this test. Formula in G12 is invalid. But for my expectations it should be.

public void removeXLATest() throws Exception {
Workbook workbook = new Workbook(“D:\in.xlsx”);

Cells cells = workbook.getWorksheets().get(1).getCells();

Cell cell = cells.get(“G12”);

//With xla
System.out.println(cell.getFormula());

ExternalLinkCollection links = workbook.getWorksheets().getExternalLinks();

for(int i = links.getCount()-1; i>-1; i–) {
ExternalLink link = links.get(i);
if(link.getDataSource().endsWith(".xla")) {
link.setDataSource("");
}
}

//Without xla
System.out.println(cell.getFormula());
}

Best regards. Alexey
12

Hi Alexey,

Thanks for your posting and using Aspose.Cells.

We were able to observe this issue after executing your provided code with the source file you attached earlier. XLA reference was not removed correctly.

We have logged this issue in our database for investigation. We will look into it and fix this issue. Once, the issue is resolved or we have some other update for you, we will let you know asap.

This issue has been logged as

  • CELLSJAVA-41076 - XLA reference was not removed correctly by ExternalLink.setDataSource

Below is a console output of your sample code for a reference.

Console Output:
=‘D:\asuproject\designer\designer\UsoiTemplateAddin\UsoiTemplateAddin\bin\Debug\FunctionListAddin.xla’!USVAL($A$1&".D.FACT.OIL_PRODUCTION_KOEF.O.TO",$A11,$A$3)
=’’!USVAL($A$1&".D.FACT.OIL_PRODUCTION_KOEF.O.TO",$A11,$A$3)

13

Hi,

Thanks for using Aspose.Cells for Java.

Please download and try this fix: Aspose.Cells for Java v8.2.2.3 and let us know your feedback.

14

The issues you have found earlier (filed as CELLSJAVA-41069;CELLSJAVA-41076) have been fixed in this update.

This message was posted using Notification2Forum from Downloads module by Aspose Notifier.