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

Hi Harish,

Thank you for your patience and understanding.

As far as I have understood your scenario (by looking at you previously provided sample spreadsheet), you wish to bind the database fields with the ComboBox in your spreadsheet. This cannot be accomplished directly because Aspose.Cells API does not provide any means to retrieve data from a Database and bind it to any control or place it in worksheet cells. Although, Aspose.Cells APIs provide an efficient means to import the data to spreadsheet cells if you have such data in a DataTable, ArrayList, DataGrid, DataReader and so on. Please check the detailed article on how to import the data to worksheets.

The workaround I devised for you can be achieved as follow,

  1. Connect to Database and retrieve the required data into one of the data holders as stated in the above shared article. You have to write your own custom routines to connect with your Database and retrieve required data because as discussed above this feature is beyond the scope of Aspose.Cells API.
  2. Once you have the data in any of the mentioned data holders, you can import it in worksheet cells by calling appropriate import method provided by Cells Class.
  3. Create a ComboBox in any worksheet and set the ComboBox.InputRange property so that it points to the imported data.
  4. Optionally, if you wish to hide the worksheet having the imported data, you can set the Worksheet.IsVisible property to false.


For better elaboration, I have dynamically created a DataTable and imported it to worksheet cells. Then I have created a ComboBox in another worksheet (other than the one where data resides) and sets its InputRange property. Please check the below provided code snippet and attached resultant spreadsheet for your reference.

C#


DataTable table = new DataTable();
table.Columns.Add(“Field”, typeof(string));
table.Rows.Add(“Finance”);
table.Rows.Add(“Auto”);
table.Rows.Add(“Games”);
table.Rows.Add(“Groups”);
table.Rows.Add(“HotJobs”);
table.Rows.Add(“Maps”);
table.Rows.Add(“Mobile”);
table.Rows.Add(“Movies”);
table.Rows.Add(“Music”);
table.Rows.Add(“Shopping”);
table.Rows.Add(“Sports”);
table.Rows.Add(“Tech”);

Workbook book = new Workbook();
Worksheet sheet = book.Worksheets[0];
//Import Data to worksheet
sheet.Cells.ImportDataTable(table, false, 0, 0, true);
//Add another worksheet to hold the ComboBox
sheet = book.Worksheets.Add(“Sheet2”);
//Add a ComboBox at a specified location in worksheet
ComboBox comboBox = sheet.Shapes.AddComboBox(0, 0, 4, 0, 25, 90);
//Specify the range of cells that will be treated as ComboBox Values
comboBox.InputRange = “Sheet1!A2:A12”;
//Specify the SelectedIndex for the default selected value
comboBox.SelectedIndex = 0;

//[Optional] hide the worksheet holding the actual data
book.Worksheets[0].IsVisible = false;

//Save workbook
book.Save(myDir + “CustomComboBox.xlsx”);

I hope the above provided code snippet covers your main scenario with ComboBox. Please feel free to write back in case you need our further assistance.

Hi Babar,


Thank you for your quickly reply on my requirement.
But unfortunately given mentioned code is not sufficient to achieve my requirement as I said we mainly focused on value field not text of what user has select while reading excel file using our code. Like in normal asp.net we can bind both text and value field of dropdown list and when user has select dropdown, and then we get its “selected value” to insert into table likewise this functionality is need here.

My code which I have created my previous attached file using Microsoft Interop namespace are below :

//Get the first worksheet
xlsWorksheet = (Worksheet)(xlsWorkbook.Worksheets[1]);

string[] ddl_item = { “Answers”, “Autos”, “Finance”, “Games”, “Groups”, “HotJobs”,
“Maps”, “Mobile Web”, “Movies”, “Music”, “Personals”, “Real Estate”, “Shopping”, “Sports”,
“Tech”, “Travel”, “TV”, “Yellow Pages” };

Range xlsRange;
xlsRange = xlsWorksheet.get_Range(“A1”, “A1”);

DropDowns xlDropDowns;
DropDown xlDropDown;

xlDropDowns = ((DropDowns)(xlsWorksheet.DropDowns(oMissing)));
xlDropDown = xlDropDowns.Add((double)xlsRange.Left, (double)xlsRange.Top, (double)xlsRange.Width, (double)xlsRange.Height, true);

//Add item into drop down list
for (int i = 0; i < ddl_item.Length; i++)
{
xlDropDown.AddItem(ddl_item[i], i + 1);
}

xlsApp.DisplayAlerts = false;
xlsWorkbook.Close(true, Filename, null);
xlsApp.Quit();
xlsWorksheet = null;
xlsWorkbook = null;
xlsApp = null;

As given code we can bind dropdown list with both text and value(Index) field. This is an simple example while we can do this with our database table just need to do some code.
So my mainly focus while reading an excel we need its index or value field rather than text.

Thanks & Regards,
Harish

Hi Harish,

bsssngh77:
So my mainly focus while reading an excel we need its index or value field rather than text.


Focusing on your above statement, could you please try the below provided code snippet on my recently shared sample spreadsheet (customcombobox.xlsx)?

C#

var workbook = new Workbook(myDir + “customComboBox.xlsx”);
var worksheet = workbook.Worksheets[1];
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;
var selectedValue = comboBox.SelectedValue;
var selectedIndex = comboBox.SelectedIndex;
Console.WriteLine("Selected Value is " + selectedValue);
Console.WriteLine("Selected Index is " + selectedIndex);
break;
}
}

The code snippet allows you to get the customer selected value as well as selected index from the control in the spreadsheet.

Does this suffice your requirement?

Hi Babar,


Thank you for reply.
Ok, I written your code and then read excel file then its showing default indexing of Microsoft office like (0,1,2,3 etc) can we bind our own index file writing(creating) excel file as its showing in my previous code.

Int32 Index_Value = 500;
for (int i = 0; i < ddl_item.Length; i++)
{
xlDropDown.AddItem(ddl_item[i],Index_Value);
Index_Value = Index_Value + 1;
}

In this code we can bind both text and index(value).So my question is that can we bind custom indexing while creating excel file, and after that when we will read excel file its take it’s own index rather then Microsoft excel default index value .

Please suggest me for this query.

Thanks & Regards,
Harish

Hi Harish,

Thank you for writing back.

I am afraid, custom index binding of ComboBox items isn’t supported. Currently, indexed value starts from 1 (by default) and binding is done automatically. This is same as MS Excel practices it. If development team considers providing your requested feature, that is; ability to specify the position/indexed value of individual list item then it will be a big change to the API, and therefore may take longer time to implement. It would be rather easy to expose a property in order to specify the starting index for the list. Will that help your project?

Hi Babar,


Thank you for reply,

So I think this is an issue I have found in Aspose.Cell tool. I would appreciate if your development team will fix this issue, as we can not do our operation on the basis of “text” field as text may change by day by day basis so its have unique “value” field.
Is there any solution that we can link two dropdowns, when user select a value in first dropdown then it’s auto select another value in second dropdown. So i can achieve my requirement like in my first dropdown I bind all text and in second dropdown bind all value field.So when user select text in first dropdown list then it auto select in second dropdown.

Please suggest me.

Thanks & Regards,
Harish

Hi Harish,

Thank you for wiring back.

As discussed earlier, Aspose.Cells APIs follow MS Excel guidelines and practices in its implementation therefore your presented scenario isn’t a bug/issue in Aspose.Cells APl. Although Microsoft.Interop does provided such means so we may consider implementing your requested enhancement. I have logged a ticket, and requested the development team to look further into your requirement. Please spare us little time to properly analyze the request. In the meanwhile, we will keep you posted with updates in this regard.

Ticket Id for your future reference is CELLSNET-42286.

Hi Babar,


Thank you for writing back.And Please accept my humble thanks for your support and suggestion. I will wait for your kind response/reply.

Thanks & Regards,
Harish

Hi Babar,


I am waiting for your reply. Please reply soon.

Thanks & Regards,
Harish

Hi Harish,

I am afraid, the ticket associated with this thread is currently pending for analysis, and is in the queue with other tasks. We will not be able to provide any estimated time frame for the implementation of your requested feature unless we have thoroughly analyzed the request on our end.

I have requested the concerned developer to look into it at earliest, and provide estimated timelines for the availability of said feature. As soon as any news comes in, we will post here for your kind reference.

Thank you for your patience and cooperation.

Hi Babar,


Ok, thank you for your cooperation and suggestion. I will wait for your post.

Thanks & Regards,
Harish

Hi Harish,

This is to inform you that we are currently looking into your requirement of binding custom indexes to the Dropdown values. We need a sample exhibiting this scenario that you may create using the VSTO code. Please note, your previously provided sample has 0-based index, just like MS Excel. We require a sample in which the index value should start from any value other than 0.

bsssngh77:

Is there any solution that we can link two dropdowns, when user select a value in first dropdown then it’s auto select another value in second dropdown. So i can achieve my requirement like in my first dropdown I bind all text and in second dropdown bind all value field.So when user select text in first dropdown list then it auto select in second dropdown.


Regarding your comments as above, we will require a sample spreadsheet exhibiting the said scenario so we could analyze it on our end to provide a solution for your requirement. You may create the sample manually using MS Excel or through code by using VSTO.

Looking forward to your kind response.

Hi Babar,


Sorry for late reply,
Ok, I will create another excel file which have index value greater then 1. I will post my excel file when I done.

Thanks & Regards,
Harish

Hi Harish,


This is to inform you that we have closed the ticket logged earlier as CELLSNET-42286 due to the unavailability of the requested artifacts. If you still can provide the sample spreadsheet exhibiting the scenario of Custom Index Binding of ComboBox Items that you may create using the VSTO code then we can re-open the ticket for further analysis.

Thank you for your understanding.