Check Box and Linked Cell

Aspose.Cells Product Family
1

Greetings,
I’ve got a quick question regarding the real-time functionality of a check box and a linked cell. Assuming I that I do the following:

//Instantiate a new Workbook.


Workbook workbook = new Workbook();



//Get the first worksheet in the book.


Worksheet worksheet = workbook.getWorksheets().getSheet(0);



//Add a checkbox to the first worksheet in the workbook.


com.aspose.cells.CheckBox checkBox = worksheet.getShapes().addCheckBox(5,5,100,120,50,50);



//Set its text string.


checkBox.setText(“Check it!”);



//Put a value into B1 cell.


worksheet.getCells().getCell(“B1”).setValue(“LnkCell”);



//Set B1 cell as a linked cell for the checkbox.


checkBox.setLinkedCell(“B1”);


//Change to value of the check box via the linked cell

worksheet.getCells().getCell(“B1”).setValue(“true”);


System.out.println(checkBox.getCheckValue()); // Should this reflect the linked cell change?



Will the check box be updated (real-time) as a result of changing the linked cell value, or do I need to save the workbook and reload it in order to see the change? The reason I ask is because I’m trying to update a check box via the linked cell and am not seeing the changes reflected in the check box state (CheckBox.getCheckValue()).

Thank you for your help.

2

I should also note that I am using Aspose.Cells for Java version 2.0.1.23.

3

Hi,

We found an issue i.e.., the linked cell is not attached to the control so it does not reflect with the checkbox control's value, we will figure out the issue soon.

Thank you.

4

Hi,

Thank you for considering Aspose.

For setting linked cell and range to control shapes, please append '=' before the cell/range, such as:

checkBox.setLinkedCell("=B1");

For changing the CheckBox.getCheckValue() at runtime according to the value of linked cell, currently we do not support this feature. We will try to support it soon.

Thank You & Best Regards,

5

Not only does it not work at run-time, it does not seem to work at all, even if I save the workbook and then reload it. I’ve tried updating linked cells (within Apose) with a check box, combo box, and list box controls, all to no avail.

Going back to Amjad’s reply, he hit the nail on the head when he said that “the linked cell is not attached to the control”. If the linked cell is not attached to the control, what is the point of making API calls available that manipulate the linked cell? Is this supported in the .NET version?

If you are unable to fix this issue now or in the near future, could you please provide me a more detailed time line other than “soon”? My program manager is starting to grow increasingly frustrated that we are having to roll our own solutions when he believes that they should be supported in the first place.

6

Hi,

Sorry for the confusion.

Well, I mean to say that currently we do not support to retrieve the CheckBox's value at run time according to the value of the linked cell. Anyways, we have analyzed the feature. For updating the status of controls according to its linked cell, we will provide you a new fix in this week. Hopefully, it will fulfil your requirement.

Thank you and have a good day!

7

Thank you very much for your prompt reply and addressing the issue. I have just one followup question:

"For updating the status of controls according to its linked cell, we will provide you a new fix in this week."

Does this also mean that you will update the status of a linked cell according to the control it is linked to? Our requirement is that controls be updated via the linked cell, and vice versa. Just making one of the two work will only satisfy 50% of our requirements.

8

My company also requires that controls (combobox, listbox, checkbox, and radio) update their associated linked cell. It does not seem like this is supported now. Do you know when will this feature be supported?

We are using Aspose Cells for Java as well.

Thank you.

9

Hi,

Thank you for considering Aspose.

Hopefully, we will support this feature by the end of this week.

Thank You & Best Regards,

10

Hi,

Thank you for considering Aspose.

Please try the attached latest version of Aspose.Cells. We have supported to update controls status via the linked cell, and vice versa for following control shapes: CheckBox, ComboBox, ListBox, RadioButton, ScrollBar, and Spinner.

Thank You & Best Regards,

11

Thank you very much for making this fix a priority and turning it around so quickly. I have done a couple of tests and noticed the following behaviors:

Check Box
If value of linked cell is not specified as a formula, the corresponding check box object will not update. In Excel, I have the option of setting the linked cell value to “FALSE” or “=FALSE”, and both will update the check box value. In Aspose, the check box will only update if I perform Cell.setFormula("=FALSE"), even though Cell.setValue(“FALSE”) should be supported as well, just as it is in Excel.

Combo Box
Works perfectly, and I can set the linked cell value by doing either Cell.setFormula("=1") or Cell.setValue(1.0). This is also how Check Box should be behaving, but is not.

List Box
After updating the Aspose jar in my project, any workbook containing a List Box now throws an exception when trying to save the workbook. I’ve attached a workbook I’ve been using to test list box functionality, and maybe you can reproduce the error. The exception being thrown is as follows:

Exception Type: NullPointerException
java.lang.NullPointerException
at com.aspose.cells.ListBox.getSelectedIndex(Unknown Source)
at com.aspose.cells.ListBox.b(Unknown Source)
at com.aspose.cells.ListBox.c(Unknown Source)
at com.aspose.cells.Shape.g(Unknown Source)
at com.aspose.cells.Shape.a(Unknown Source)
at com.aspose.cells.dK.a(Unknown Source)
at com.aspose.cells.gr.f(Unknown Source)
at com.aspose.cells.gr.a(Unknown Source)
at com.aspose.cells.Workbook.save(Unknown Source)
at com.aspose.cells.Workbook.save(Unknown Source)
at com.sharecel.framework.document.excel.aspose.AsposeReaderWriter.saveWorkbook(AsposeReaderWriter.java:420)
at com.sharecel.framework.document.excel.aspose.AsposeReaderWriter.finalizeWorkbook(AsposeReaderWriter.java:346)
at com.sharecel.framework.document.excel.aspose.AsposeReaderWriter.write(AsposeReaderWriter.java:295)
at com.sharecel.framework.document.excel.ExcelDoc.updateWorkbook(ExcelDoc.java:235)
at com.sharecel.framework.document.excel.ExcelDocManager.updateWorkbook(ExcelDocManager.java:360)
at com.sharecel.awe.actions.WorksheetFieldChangeUpdateAction.executeAction(WorksheetFieldChangeUpdateAction.java:131)
at com.sharecel.awe.actions.ActionExt.execute(ActionExt.java:68)
at org.apache.struts.action.RequestProcessor.processActionPerform(RequestProcessor.java:425)
at org.apache.struts.action.RequestProcessor.process(RequestProcessor.java:228)
at org.apache.struts.action.ActionServlet.process(ActionServlet.java:1913)
at org.apache.struts.action.ActionServlet.doPost(ActionServlet.java:462)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:637)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:717)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:290)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
at com.sharecel.awe.filters.EncodingContentTypeFilter.doFilter(EncodingContentTypeFilter.java:50)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:235)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:233)
at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:191)
at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:128)
at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:102)
at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:109)
at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:286)
at org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:845)
at org.apache.coyote.http11.Http11Protocol$Http11ConnectionHandler.process(Http11Protocol.java:583)
at org.apache.tomcat.util.net.JIoEndpoint$Worker.run(JIoEndpoint.java:447)
at java.lang.Thread.run(Thread.java:619)

Thank you again for any help that you can provide.

12

Hi,

chadwickFL77:

Check Box
If value of linked cell is not specified as a formula, the corresponding check box object will not update. In Excel, I have the option of setting the linked cell value to "FALSE" or "=FALSE", and both will update the check box value. In Aspose, the check box will only update if I perform Cell.setFormula("=FALSE"), even though Cell.setValue("FALSE") should be supported as well, just as it is in Excel.

Please use Cell.setValue(false) instead of Cell.setValue("FALSE")


chadwickFL77:

List Box
After updating the Aspose jar in my project, any workbook containing a List Box now throws an exception when trying to save the workbook. I've attached a workbook I've been using to test list box functionality, and maybe you can reproduce the error. The exception being thrown is as follows:

Exception Type: NullPointerException
java.lang.NullPointerException

.................

Yes, we found the issue and will figure it out soon.

Thank you.

13

Hi,

Please try the attached version v2.0.2.1, we have fixed the listbox issue.

Thank you.

14

The fix seems to resolve the null pointer exception, but it appears as though the list box linked to the cell is only at 50% functionality. It appears as though a linked cell can drive the list box control, but the control cannot drive the linked cell value. I’ve attached a spreadsheet that I’ve been using to test. The list box labeled “Single selection, linked cell” demonstrates the issue that I have mentioned.

15

Hi,

Well, if no selection is made and you provide a value less than or equal to 0 in the linked cell, Aspose.Cells for Java will return -1 for the ListBox.getSelectedIndex() method. I have implemented your scenario using your template file's list box (you mentioned) with the following code and it works fine, the output file is also fine.

Sample code:

//Instantiate a new Workbook.
Workbook workbook = new Workbook();

workbook.open("e:\\files\\ListBox.xls");
//Get the first worksheet in the book.
Worksheet worksheet = workbook.getWorksheets().getSheet(0);

//Add a checkbox to the first worksheet in the workbook.
com.aspose.cells.ListBox listBox = (com.aspose.cells.ListBox)worksheet.getShapes().get(4);


System.out.println(listBox.getLinkedCell()); //it returns =$N$25

//it means no selection is made.

if(listBox.getSelectedIndex()==-1)
{
System.out.println(worksheet.getCells().getCell("N25").getStringValue()); //it returns 0

}
else
{
System.out.println(listBox.getSelectedIndex());

}


//Now select the second option in the list.
listBox.setSelectedIndex(1);

System.out.println(listBox.getLinkedCell()); //it returns =$N$25

System.out.println(listBox.getSelectedIndex()); it returns 1


//Change to selected index of the listbox via the linked cell
worksheet.getCells().getCell("N25").setValue(3);

System.out.println(listBox.getLinkedCell()); //it returns =$N$25

System.out.println(listBox.getSelectedIndex()); it returns 2


workbook.save("e:\\files\\outListBoxteste.xls");

If you still find the issue, could you provide us more details and provide sample code to re-produce the issue, we will check it soon.

Thank you.

16

Amjad,
Thanks for the detailed response. As it turns out, I am using the ListBox.setSelectedItem(…) method, not the ListBox.setSelectedIndex(…) method to set the list box values (regardless of whether it is a single or multi-select list box).

Could this be the reason I am not seeing the desired result? I guess that would make sense since multi-select list boxes don’t have linked cells, and setSelectedItem() is primarily for multi-select list boxes.

Thanks again for your continued help.

17

Amjad,
I changed my code to call ListBox.setSelectedIndex(…) when the list box is a single select type, and that fixed my problem. Thanks again for the help.

18

The issues you have found earlier (filed as 9769) have been fixed in this update.

This message was posted using Notification2Forum from Downloads module by aspose.notifier.
19

Thanks for the quick response.

20

Thanks to your help I have gotten check boxes, list boxes and combo boxes working properly now, but I have run into an issue with the radio buttons. In the attached spreadsheet I have 5 radio buttons pointing to the same linked cell, but when I do a RadioButton.setChecked(true) on any of the buttons, the linked cell does not update. The same holds true the other way around. If I update the linked cell, the radio buttons don’t change accordingly.

I am using Aspose.Cells for Java 2.1.0.0. Given my history with the linked cells, I would not be surprised if this was my fault, I’m just having trouble figuring out what the problem might be.

Thank you for any help that you can provide.