How to generate excel file with controls(Dropdown list-Checbox list and Radio button)

bsssngh77 · January 7, 2014, 12:19am

Hello Sir,

I want to know that how to generate excel file which contains controls like Dropdown list, Checkbox list and Radio button as well as some text control. After generated excel file we also read generated excel file with selected control which selected by user.

Please any one help me for this implementation.

Thanks,

Harish

babar.raza · January 7, 2014, 12:57am

Hi Harish,

Thank you for considering Aspose products.

I would suggest you to go through the below linked technical article for your requirement. The article hosts source code samples for creating and managing controls
such as ComboBox/Dropdown List, ListBox, Radio Buttons and Text Boxes.
http://www.aspose.com/docs/display/cellsnet/Working+with+Controls

In case you face any difficulty, please feel free to write back.

bsssngh77 · January 7, 2014, 1:14am

Hi Babar,

Thanks for your help.

As your mentioned linked only provide the information how to write excel file with controls but I also want to know how to read excel file with whatever control selected by user let’

s suppose I am generating a excel file with one dropdownl list control and one checkbox list controls after generated excel file user select some value in excel file then after import these excel file. So how I can identified which value user select in dropdown list and which checkobx user checked in excel. I hope you are getting my point.

Thanks & Reagrds,

Harish

babar.raza · January 7, 2014, 2:25am

Hi Harish,

Thank you for providing further details of your scenario.

Please check the below provided code snippet to read all shapes from a given worksheet, and to cast each given shape to a relevant type in order to extract the user specified selection/value. The code snippet exhibits the usage to read ComboBox, CheckBox and TextBox. You can extend the functionality to read other controls supported by Aspose.Cells for .NET API.

C#

var workbook = new Workbook(myDir + “sample-controls.xlsx”);
var worksheet = workbook.Worksheets[0];
var shapes = worksheet.Shapes;
foreach (Shape shape in shapes)
{
Type type = shape.GetType();
switch (type.FullName)
{
case “Aspose.Cells.Drawing.ComboBox”:
ComboBox comboBox = (ComboBox)shape;
Console.WriteLine(“This is a ComboBox " + comboBox.Name + " :” + comboBox.SelectedValue);
continue;
case “Aspose.Cells.Drawing.TextBox”:
TextBox textBox = (TextBox)shape;
Console.WriteLine(“This is a TextBox " + textBox.Name + " :” + textBox.Text);
continue;
case “Aspose.Cells.Drawing.CheckBox”:
CheckBox checkBox = (CheckBox)shape;
Console.WriteLine(“This is a CheckBox " + checkBox.Name + " :” + checkBox.CheckedValue);
continue;
}
}

Hope this helps a bit.

bsssngh77 · January 7, 2014, 3:14am

Hi Babar,

Thank you for your support and concern.

So, above line of code is sufficient to read excel file that have controls in each row of excel because my requirement is that there are many controls in different cell in each row. Suppose there are 100 no. of rows in excel and in Cell B which have dropdown list, Cell C have checkbox list, Cell D have Radio button list etc. and these control are in each row with their respective cell.

So it is possible to read each controls in each row whatever user have select in excel file.

Thaks & Regards,

Harish

bsssngh77 · January 7, 2014, 3:21am

Hi Babar,

I also want to know that Can you also provide me a sample project or Code which provide first write/generate controls in excel and after that read generated excel file read with these controls.

We need it very urgent. So please help me for this implementation.

Thanks,

Harish

babar.raza · January 7, 2014, 5:06am

Hi Harish,

Thank you for your response.

bsssngh77:

So, above line of code is sufficient to read excel file that have controls in each row of excel because my requirement is that there are many controls in different cell in each row. Suppose there are 100 no. of rows in excel and in Cell B which have dropdown list, Cell C have checkbox list, Cell D have Radio button list etc. and these control are in each row with their respective cell.

So it is possible to read each controls in each row whatever user have select in excel file.

As discussed in my previous response, the code snippet provided earlier could handle the ComboBox, Text Box and CheckBox controls in a given worksheet. You surely can extend the functionality to add more controls to the switch case as per your requirement. Moreover, as you may observe that the source code snippet is getting all the shapes from a worksheet irrespective of its cell location so the code snippet will work seamlessly in your scenario.

bsssngh77:

I also want to know that Can you also provide me a sample project
or Code which provide first write/generate controls in excel and after
that read generated excel file read with these controls.

We need it very urgent. So please help me for this implementation.

I would suggest you again to check the detailed article on how to create controls in a spreadsheet. The said article hosts source code snippets exhibiting the usage along with detailed descriptions on all the controls supported by Aspose.Cells API. Once you have created the controls in a spreadsheet, you can manually open it using MS Excel to change/set a few values for your testing. Next, you could load the spreadsheet with Aspose.Cells API using the code snippet provided in my previous post in order to check what value has been set for each control.

In case you face any difficulty, please feel free to write back.

bsssngh77 · January 7, 2014, 5:52am

Hi Babar,

Thanks for your quick reply on my query. Now I am able to create excel file with controls and also read value which user has been set for each control. But when I create multiple radio button in different cell excel then i am only able select one radio button not both, so is any property for radio button which need to set while creating radio button( like both radio button consider as different group).

And also when I Click on “how to create controls in a spreadsheet” then its showing Page Not Found Message.

Please advise me or suggest me some alternatives to achieve this functionality.

Thanks & Regards,

Harish

babar.raza · January 7, 2014, 7:26am

Hi Harish,

First of all, please accept my apology for the broken link. I have now corrected the link in same post. The link refers to the same document shared earlier in this thread.

Regarding your inquiry about selecting multiple radio buttons, I am afraid this is the restriction imposed by MS Excel itself that in a given worksheet only one radio button could be selected regardless of its group. You may confirm this by manually creating a spreadsheet in MS Excel application.

Moreover, Aspose.Cells API follows MS Excel guidelines in its implementation therefore the said behavior is correct and as expected. I would suggest you to use Check Boxes instead of Radio Buttons where you need to select more than one options in a single worksheet.

Thank you for your understanding.

bsssngh77 · January 7, 2014, 7:55am

Hi Babar,

Thanks for reply.

Oh,That means if we have different Radio button in each cell for each row then user not able to select multiple radio button in excel file. As mentioned above comment i assume that in excel user only selected one radio button doesn’t matter how many number of radio button in excel file, Please let me correct if i am going in wrong direction.

Please reply quick I need answer urgently.

Thanks & Regards,

Harish

babar.raza · January 7, 2014, 9:06am

Hi Harish,

I have found a solution for your scenario while using the Aspose.Cells API. Trick is to first create a Group Box large enough to hold one set of Radio Buttons then create a set of Radio Buttons inside the Group Box. Steps should be as follow in order to select multiple Radio Buttons in a given worksheet.

Create a Group Box using the Worksheet.Shapes.AddGroupBox method.
Create a set of Radio Buttons using the Worksheet.Shapes.AddRadioButton method.
Group a set of Radio Buttons using the Worksheets.Shapes.Group method.

Complete source code is provided below, also attached is the output spreadsheet for your reference.

C#

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

//Insert a value.
excelbook.Worksheets[0].Cells[“C2”].PutValue(“Age Groups”);
//Set the font text bold.
excelbook.Worksheets[0].Cells[“C2”].GetStyle().Font.IsBold = true;

//Add a GroupBox to the first sheet.
Aspose.Cells.Drawing.GroupBox groupBox = excelbook.Worksheets[0].Shapes.AddGroupBox(2, 0, 2, 0, 150, 110);

//Add a radio button to the first sheet.
Aspose.Cells.Drawing.RadioButton radio1 = excelbook.Worksheets[0].Shapes.AddRadioButton(3, 0, 2, 0, 30, 110);
//Set its text string.
radio1.Text = “20-29”;
//Set A1 cell as a linked cell for the radio button.
radio1.LinkedCell = “A1”;
//Add another radio button to the first sheet.
Aspose.Cells.Drawing.RadioButton radio2 = excelbook.Worksheets[0].Shapes.AddRadioButton(6, 0, 2, 0, 30, 110);
//Set its text string.
radio2.Text = “30-39”;
//Set A1 cell as a linked cell for the radio button.
radio2.LinkedCell = “A1”;
//Add another radio button to the first sheet.
Aspose.Cells.Drawing.RadioButton radio3 = excelbook.Worksheets[0].Shapes.AddRadioButton(9, 0, 2, 0, 30, 110);
//Set its text string.
radio3.Text = “40-49”;
//Set A1 cell as a linked cell for the radio button.
radio3.LinkedCell = “A1”;

//Group all similar Radio Buttons
Shape[] radioGroupA = new Shape[] { radio1, radio2, radio3 };
excelbook.Worksheets[0].Shapes.Group(radioGroupA);

//Insert a value.
excelbook.Worksheets[0].Cells[“F2”].PutValue(“Age Groups”);
//Set the font text bold.
excelbook.Worksheets[0].Cells[“F2”].GetStyle().Font.IsBold = true;
//Add a radio button to the first sheet.
Aspose.Cells.Drawing.RadioButton radio4 = excelbook.Worksheets[0].Shapes.AddRadioButton(3, 0, 5, 0, 30, 110);
//Set its text string.
radio4.Text = “20-29”;
//Set A1 cell as a linked cell for the radio button.
radio4.LinkedCell = “A2”;
//Add another radio button to the first sheet.
Aspose.Cells.Drawing.RadioButton radio5 = excelbook.Worksheets[0].Shapes.AddRadioButton(6, 0, 5, 0, 30, 110);
//Set its text string.
radio5.Text = “30-39”;
//Set A1 cell as a linked cell for the radio button.
radio5.LinkedCell = “A2”;
//Add another radio button to the first sheet.
Aspose.Cells.Drawing.RadioButton radio6 = excelbook.Worksheets[0].Shapes.AddRadioButton(9, 0, 5, 0, 30, 110);
//Set its text string.
radio6.Text = “40-49”;
//Set A1 cell as a linked cell for the radio button.
radio6.LinkedCell = “A2”;

//Group all similar Radio Buttons
Shape[] radioGroupB = new Shape[] { radio4, radio5, radio6 };
excelbook.Worksheets[0].Shapes.Group(radioGroupB);

//Save the excel file.
excelbook.Save(“radiobuttons.xls”);

bsssngh77 · January 7, 2014, 11:08pm

Hi Babar,

Thank you for your reply and suggestion.

I have some doubt/Question which mentioned below:

1. How to bind value for their linked cell my radio button value fetch from database table so how can i do this for Example if user select any radio then its value bind from table then it will show value that is their respective value which bind from database table (Same question for dropdown list linked cell).

2. Can we bind multiple radio button in one cell ?

Please suggest me for my question.

Thanks & Regards

Harish

babar.raza · January 8, 2014, 3:53am

Hi Harish,

Thank you for writing back.

MS Excel does not allow to bind custom values for the linked cells when you create a spreadsheet manually. I have performed a few tests with Aspose.Cells as well as MS Excel; the linked cell for radio buttons always represent the 1-based index position. Same is the case with combo box when you have specified a range for the combo box, the linked cell will represent the 1-based index position of the selected value. If you can create a spreadsheet manually (using MS Excel) that could accept custom values then please share such file for our reference so we could analyze it to provide similar functionality in Aspose.Cells API.
You can bind/link a set of radio buttons (there could be any number of radio buttons in a set/group) to a single cell. If you bind multiple sets of radio buttons to a single cell then selecting a radio button in one set will automatically select the similarly positioned radio button in another set. I have attached a spreadsheet exhibiting the problem that can occur if you bind multiple sets of radio buttons to one cell.

bsssngh77 · January 8, 2014, 4:16am

Hi Babar,

Thanks for your reply on my query.

#1. Ok, But our requirement is that when user selects any control like dropdown list and radio button list then its linked cell populated value which came from table and then after we perform our operation like update data into table. Is their any way to bind value field to dropdown or radio I know that we can bind only text for these controls using Aspose.cell tool. But using Microsoft.Inetrop (Microsoft.Office) namespace we can also bind index to dropdown list but we can’t use Microsft.Interop functionality as its have some drawback.

Please suggest me how we can achieve our requirement using your tool. Is any workaround solution for this.

#2. Thanks for attached file, but in file Radio button group are in vertical alignment can we also align that in horizontal direction?. Like i want to arrange all its related group of radio buttons in Cell[“A2”] etc.

Thanks & Regards,

Harish

bsssngh77 · January 8, 2014, 7:32am

Hi Babar,

Please reply for my above query which i mentioned in my previous post.I kindly request to to post your query as soon as possible.

babar.raza · January 8, 2014, 1:15pm

Hi Harish,

Please accept my apology for a bit delayed response.

I am afraid, I couldn’t find a workaround for the said situation. I would suggest you again to provide us a sample spreadsheet exhibiting your scenario. You can create the sample spreadsheet either with MS Excel or by using Microsoft.Interop. By reviewing your provided sample, we will try again to replicate the functionality using the current set of Aspose.Cells API or we may log a feature request to provide the implementation with future releases.
You can set the position of radio buttons as per your requirement. All you have to do is to pass appropriate values to Shapes.AddRadioButton method. Please check the below provided code snippet that places the radio buttons in a single row unlike previously shared sample that places the radio buttons in multiple rows but same column.

C#

//Instantiate a new Workbook.
Workbook excelbook = new Workbook();
//Add a radio button to the first sheet.
Aspose.Cells.Drawing.RadioButton radio1 = excelbook.Worksheets[0].Shapes.AddRadioButton(3, 0, 2, 0, 30, 110);
//Set its text string.
radio1.Text = “20-29”;
//Set A1 cell as a linked cell for the radio button.
radio1.LinkedCell = “A1”;
//Add another radio button to the first sheet.
Aspose.Cells.Drawing.RadioButton radio2 = excelbook.Worksheets[0].Shapes.AddRadioButton(3, 0, 3, 0, 30, 110);
//Set its text string.
radio2.Text = “30-39”;
//Set A1 cell as a linked cell for the radio button.
radio2.LinkedCell = “A1”;
//Add another radio button to the first sheet.
Aspose.Cells.Drawing.RadioButton radio3 = excelbook.Worksheets[0].Shapes.AddRadioButton(3, 0, 4, 0, 30, 110);
//Set its text string.
radio3.Text = “40-49”;
//Set A1 cell as a linked cell for the radio button.
radio3.LinkedCell = “A1”;
//Saves the file.
excelbook.Save(“radiobutton-horizontal.xls”);

Hope this helps.

bsssngh77 · January 8, 2014, 11:49pm

Hi Babar,

Thanks for reply/suggestion for my query.I have attached an sample file

Ok, for my query #1 I have attached a sample file which consist two dropdown list in cell A,B for row 1 and row 2 and i have bind this dropdown with text and index using my database table. And this file i have created using microsoft.Interop namespace so i have written code and then generate file. So when i read this file then whatever user has select in dropdown then i will read both index and text value which was bind from my table.

So i hope now you are getting my point/requirement.

I am waiting for your answer.

Thanks & Regards,

Harish

bsssngh77 · January 9, 2014, 5:38am

Hi Babar,

Please reply my previous post as i need it very urgent.Please kindly reply on time.

Thanks & Regards,

Harish

babar.raza · January 9, 2014, 8:02am

Hi Harish,

Thank you for providing the sample file.

We are currently looking into your requirement, and we will shortly respond back with more information in this regard. Please spare us little time to properly analyze the requirement on our end.

bsssngh77 · January 9, 2014, 11:26am

Hi Babar,

Thanks for look into my requirement. Please let us update regrading this issue.

Thanks & Regards,

Harish