Free Support Forum - aspose.com

Create a drop down reading data from a table

Hi<br>Can I create a form in ASPOSE cells that picks the information to be filled in the drop down boxes from a table. When I fill the form by selecting options available in the drop down and fill in some texts in the text boxes of the form, when i upload back the form to the system,can I update the entries in the form directly to the database.<br><br>This is urgent.<br>Could you please answer.<br><br>Regards<br>Shalini<br>

Hi Shalini,

Thanks for considering Aspose.

Could you elaborate your need in details.

For your info, Aspose.Cells is a server-side non GUI Excel® spreadsheet managemnt library (component) that enables either .Net or Java applications to read and write Excel® spreadsheets without utilizing Microsoft Excel® to be installed either on the server or client side. It can work both on desktop and web environments. If you want to view the data, you do need to have Excel Viewer or MS Excel. Well, it does not has such properties, neither it provides any interface. Although you may open any excel spreadshet into browser.

Do you need some GUI Component (Control) for your tasks to be implemented in WYSIWYG manner.

Thank you.

hi Amjad,

I have a form in my portal developed using .NET that can have some drop down menus. Sometimes, I want the user to fill details in an excel sheet rather than using a form on my portal. I might send the excel sheet to his computer with similar drop down menus option in the excel sheet so that he can not fill in the wrong entries.Can I provide auch an excel sheet with ASPOSE utility that reads the drop down contents from a database?

Case 2 - when the user fills in the details and send the xls back to me, I will upload it to my system and expect the entries be made in corresponding tables of database. Is it possible. If yes can you please tell me where can i find the sample code.

Hi,

Thanks for considering Aspose.

Well, I think you may do it. You may create drop down list / Combobox control using Aspose.Cells APIs, for reference kindly check: http://www.aspose.com/Wiki/default.aspx/Aspose.Cells/OtherDrawingObjects.html#ComboBox. For setting the contents of the dropdown from a database/datasource, you can import data from database to some Worksheet cells and set this data as a data source range for the Combobox control, kindly check how to import data to worksheet from different data sources: http://www.aspose.com/Wiki/default.aspx/Aspose.Cells/ImportData.html. Moreover Aspose.Cells allows to save the excel file to open/download it onto client browser, please check the different options for opening/saving excel files: http://www.aspose.com/Wiki/default.aspx/Aspose.Cells/SavingFile.html. Further you may create textboxes or other controls for making your forms, please check: http://www.aspose.com/Wiki/default.aspx/Aspose.Cells/OtherDrawingObjects.html

I think your Case2 is simple, you may get the updated excel file, you may load your updated excel file and get the filled/selected data and update this data to your datasource, for reference how to export data to some data source, please check: http://www.aspose.com/Wiki/default.aspx/Aspose.Cells/ExportData.html

For you scenario, I have created a simple example, hopefully it will give you some insight. Following is the code and attached is the output excel file (I utilized Northwind (Access) database and get the CustomerID field from Customers table, import it to the second worksheet and set this field's data as a data source range for the combobox control in the first worksheet).

OleDbConnection con = new OleDbConnection("provider=microsoft.jet.oledb.4.0;data source=d:\\test\\Northwind.mdb");
con.Open();
OleDbCommand cmd = new OleDbCommand("Select CustomerID from Customers",con);
OleDbDataAdapter da = new OleDbDataAdapter();
da.SelectCommand = cmd;
DataSet ds = new DataSet();
da.Fill(ds,"Customers");

Workbook wb = new Workbook();
//Add a new sheet.
Worksheet sheet2 = wb.Worksheets[wb.Worksheets.Add()];
sheet2.Name = "Customers";
//Import the datatable
sheet2.Cells.ImportDataTable(ds.Tables["Customers"],false,0,0);
//Get the first default worksheet.
Worksheet sheet = wb.Worksheets[0];
//Get the worksheet cells collection.
Cells cells = sheet.Cells;
//Input a value.
cells["B3"].PutValue("Customers:");
//Set it bold.
cells["B3"].Style.Font.IsBold = true;
//Add a new combo box.
Aspose.Cells.ComboBox comboBox = sheet.Shapes.AddComboBox(2, 0, 2, 0, 22, 100);
//Set the linked cell;
//comboBox.LinkedCell = "A1";
int maxrow = sheet2.Cells.MaxDataRowInColumn(0);
string maxcellname = CellsHelper.CellIndexToName(maxrow,0);
//Set the input range.
comboBox.InputRange = "Customers!A1:" + maxcellname;
//Set no. of list lines displayed in the combo box's
//list portion.
comboBox.DropDownLines = 5;
//Set the combo box with 3-D shading.
comboBox.Shadow = true;
//AutoFit Columns;
sheet.AutoFitColumns();
//To hide Customers sheet (Sheet2);
//wb.Worksheets[1].IsVisible = false;
//Saves the file.
wb.Save(@"d:\test\tst_dataobtained.xls");

Thank you.