Cell-Link for Checkboxes

[Semjasa]

Hey,

has spire xls a way to link an cell to an checkbox?
to use an formular like: =IF(E2=TRUE, "Done", "To Be Done").


Thx Thomas

[Betsy.jiang]

Hi Thomas,

Thanks for your inquiry.
To help us investigate further, could you please firstly tell us your checkbox is From control or ActiveX control?
Besides, providing the desired result( you could generate it via MS Excel) would be better.

Sincerely,
Betsy
E-iceblue support team

[Semjasa]

Code: Select all

sheet.CheckBoxes.AddCheckBox(1, 11, 15, 75);

this is what i have created and the file below is the example file what i need to do

thx so much

cb_test.zip

(13.59 KiB) Downloaded 207 times

[Betsy.jiang]

Hi Thomas,

Thanks for your information.
After further investigation, we find the feature you need is in internal class, at present it is not public. Once it is public, we will let you know.

Sincerely,
Betsy
E-iceblue support team

[Semjasa]

CRY,

Thx for this information. i have read a post from 2016 about this feature I thought it was already available... this feature would be very nice

I am looking forward to hearing from you.

have a nice Day

Thomas

[Betsy.jiang]

Hi Thomas,

Once there is any good news, we will let you know.

Sincerely,
Betsy
E-iceblue support team

[Betsy.jiang]

Hi Thomas,

Glad to inform you the feature has been implemented. Please download Spire.XLS Pack(Hotfix) Version:9.1.14 and use following code.
Our website: https://www.e-iceblue.com/Download/down ... t-now.html
NuGet: https://www.nuget.org/packages/Spire.XLS

Code: Select all

checkbox.LinkedCell = sheet.Range["A3"];

Sincerely,
Betsy
E-iceblue support team

[Betsy.jiang]

Hi Thomas,

Greetings from E-iceblue.
Do you use the hotfix? Has your issue been resolved?

Sincerely,
Betsy
E-iceblue support team

[TimothyIdext]

Thanks for your reply.

I tried this with no luck.

Does Cell E1 have to be defined like cell A1? See attached image

[Jane.Bai]

Dear Thomas,

This is Jane.
For the data source link, you could directly set the formula. Please refer to the following code to accomplish the task.

Code: Select all

//load file
Workbook book = new Workbook();
book.LoadFromFile(@"input.xlsx");

//add checkbox
Worksheet sheet = book.Worksheets[0];
CellRange range_1 = sheet.Range["S7"];
ICheckBox checkbox_1 = sheet.CheckBoxes.AddCheckBox(7, 11, 15, 75);
checkbox_1.Text = "Person 1";
checkbox_1.LinkedCell = range_1;

CellRange range_2 = sheet.Range["S8"];
ICheckBox checkbox_2 = sheet.CheckBoxes.AddCheckBox(8, 11, 15, 75);
checkbox_2.Text = "Person 2";
checkbox_2.LinkedCell = range_2;

CellRange range_3 = sheet.Range["S9"];
ICheckBox checkbox_3 = sheet.CheckBoxes.AddCheckBox(9, 11, 15, 75);
checkbox_3.Text = "Person 3";
checkbox_3.LinkedCell = range_3;

//set the if formula for chart data
sheet.Range["N2"].Formula = "=IF(S7=TRUE,T2,NA())";
sheet.Range["O2"].Formula = "=IF(S7=TRUE,U2,NA())";
sheet.Range["P2"].Formula = "=IF(S7=TRUE,V2,NA())";

sheet.Range["N3"].Formula = "=IF(S8=TRUE,T3,NA())";
sheet.Range["O3"].Formula = "=IF(S8=TRUE,U3,NA())";
sheet.Range["P3"].Formula = "=IF(S8=TRUE,V3,NA())";

sheet.Range["N4"].Formula = "=IF(S9=TRUE,T4,NA())";
sheet.Range["O4"].Formula = "=IF(S9=TRUE,U4,NA())";
sheet.Range["P4"].Formula = "=IF(S9=TRUE,V4,NA())";

//calculate the formula and set the check state for checkbox
book.CalculateAllValue();
checkbox_1.CheckState = Spire.Xls.CheckState.Checked;
checkbox_2.CheckState = Spire.Xls.CheckState.Checked;
checkbox_3.CheckState = Spire.Xls.CheckState.Checked;

book.SaveToFile("output.xlsx", ExcelVersion.Version2010);
book.Dispose();


Also attach the input and output file.

Sincerely,
Jane
E-iceblue support team

[Semjasa]

Thx,
i have test this update and this is nice work and this points exactly my problem
thank you

[Betsy.jiang]

Hi,

Thanks for your feedback.
Any question, please feel free to contact us.

Sincerely,
Betsy
E-iceblue support team