Making Excel with ComboBoxes in specific column

Aspose.Cells Product Family
1

Hi Team,


I cannot understand this article: Managing Controls|Documentation

Kindly please help in my situation.

I need to export some data to Excel and have the whole column have ComboBoxes in the cells, for users to chose only the restricted values. Then I need to import this excel and parse the values into dataset back.

How can I add those comboBoxes into all the cells in the column, keeping existing values there?
Your example puts comboBox somewhere “above” the worksheet, in a separate layer or so…


Thanks,
Sergey

P.S. My scenario in a simple example:

1. I have MyAttribute with possible values “a, b, c”.
2. I have a grid in application having some rows and MyAttribute column. There are one of the possible values in the grid rows, e.g. 1st row value is “a”, 2nd and 3rd values are “b”, and so on.
3. I’m exporting this to Excel, and I need to have MyAttribute column where all cells displayed as comboboxes. Values should remain as it was in the grid.
4. Users should be able to change MyAttribute via dropdown in each cell.
5. This Excel should be uploaded back and parsed to have new values.

Thanks,
Sergey

2

Hi Sergey,


I think for your scenario, you may try to use List Data Validation (MS Excel feature) to accomplish your task. Please see the following sample code for your reference, you may change or update the codes accordingly for your needs:

Sample code:

Workbook workbook = new Workbook();
// Get the first worksheet.
Worksheet worksheet1 = workbook.Worksheets[0];
// Add a new worksheet and access it.
int i = workbook.Worksheets.Add();
Worksheet worksheet2 = workbook.Worksheets[i];

OleDbConnection con = new OleDbConnection(“provider=microsoft.jet.oledb.4.0;data source=e:\test\Northwind.mdb”);
//Open the connection object.
con.Open();
//Create a command object and specify the SQL query.
OleDbCommand cmd = new OleDbCommand(“Select distinct(Country) from Employees”, con);
//Create a data adapter object.
OleDbDataAdapter da = new OleDbDataAdapter();
//Specify the command.
da.SelectCommand = cmd;
//Create a dataset object.
DataSet ds = new DataSet();
//Fill the dataset with the table records.
da.Fill(ds, “Employees”);
//Create a datatable with respect to dataset table.
DataTable dt = ds.Tables[“Employees”];
worksheet2.Cells.ImportDataTable(dt, false, “A1”);

// Create a range in the second worksheet.
int rowindex = worksheet2.Cells.MaxDataRow;
string cellname = CellsHelper.CellIndexToName(rowindex, 0);
Range range = worksheet2.Cells.CreateRange(“A1”, cellname);
// Name the range.
range.Name = “MyRange”;

// Get the validations collection.
ValidationCollection validations = worksheet1.Validations;
// Create a new validation to the validations list.
Validation validation = validations[validations.Add()];
// Set the validation type.
validation.Type = Aspose.Cells.ValidationType.List;
// Set the operator.
validation.Operator = OperatorType.None;
// Set the in cell drop down.
validation.InCellDropDown = true;
// Set the formula1.
validation.Formula1 = “=MyRange”;
// Enable it to show error.
validation.ShowError = true;
// Set the alert type severity level.
validation.AlertStyle = ValidationAlertType.Stop;
// Set the error title.
validation.ErrorTitle = “Invalid Country Error”;
// Set the error message.
validation.ErrorMessage = “Please select a country from the drop down”;
// Specify the validation area.
CellArea area;
area.StartRow = 0;
area.EndRow = 14;
area.StartColumn = 0;
area.EndColumn = 0;
// Add the validation area.
validation.AreaList.Add(area);

workbook.Worksheets.ActiveSheetIndex = 0;
// Save the excel file.
workbook.Save(“e:\test2\outdropdownscountry.xlsx”);

I imported a field from a table in the source Database to fill the cells in the second worksheet which will provide a source for the data list validation type. I added the validation to a range of cells in the first sheet (e.g. A1:A15), the user can click on the cells, the drop down will be shown and he may select a value in the given list. Finally, I save the file.

Also, see the document for your complete reference on data Validations:
http://www.aspose.com/docs/display/cellsnet/Data+Filtering+and+Validation

Hope, this helps.

Thank you.

3

Thanks, that worked for me.


Regards,
Sergey
4

Hi,


Good to know that your issue is sorted out.

Feel free to contact us anytime if you need further help or have some other inquiries, we will be happy to assist you soon.

Thank you.