Not able to Populate dropdown in Excel Sheet with more than 105 items

Aspose.Cells Product Family
1

Hi,

I am using Aspose.Cells for creating an excel file for user to export,

which contains some columns which have dropdowns with items for selection for user,

But when I am trying to add more than 105 Items in dropdown by fetching from database the excel doesnot populate and hangs the window.

When I tried with less than 105 items it is working properly,

I have 250-300 items to be populated in the dropdown list.

can anyone please suggest something for this.

Thanks in advance.

2

Hi Pravin,


Thank you for contacting Aspose support.

We have evaluated your presented scenario on our end while using the following piece of code and the latest version of Aspose.Cells for .NET 8.0.1.4. Unfortunately, we are unable to experience any problem as stated in your post.

C#

//Create a new Workbook.
Workbook workbook = new Workbook();

//Get the first worksheet.
Worksheet sheet = workbook.Worksheets[0];

//Get the worksheet cells collection.
Cells cells = sheet.Cells;

//Input a value.
cells[“B3”].PutValue(“Employee:”);

//Set it bold.
cells[“B3”].GetStyle().Font.IsBold = true;

//Input some values that denote the input range
//for the combo box.

int i = 0;
for (i = 1; i <= 300; i++)
{
cells[“A” + i].PutValue(“Emp00” + i);
}

//Add a new combo box.
Aspose.Cells.Drawing.ComboBox comboBox = sheet.Shapes.AddComboBox(2, 0, 2, 0, 22, 100);

//Set the linked cell;
comboBox.LinkedCell = “C1”;

//Set the input range.
comboBox.InputRange = “A2:A”+i;

//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();

//Saves the file.
workbook.Save(myDir + “combobox.xls”);


The above code successfully inserts 300 items to the dropdown list, whereas the resultant spreadsheet (attached) does not exhibit any irregular behavior when loaded with MS Excel 2007 and 2010.

We would request you to please give the latest version a try (link shared above) on your end. In case the problem persists, please provide us the following, for further investigation.

  • An executable standalone application replicating the issue.
  • Resultant spreadsheet produced on your end.
  • MS Excel application version on your end.

Thank you for your cooperation and understanding.
3

Thanks Babar for your quick reply.

I tried with the this latest version of Aspose.cells but no luck,

Moreover I am not using combobox in My appication,

I am creating the drodown from range, You can take a look in the attached image.

Please let me knw how can we achieve this for such kind of dropdown.

4

Hi Praven,


As requested earlier, please share a standalone sample application replicating the problem and the resultant spreadsheet generated on your end for our review.

Thank you for your cooperation.
5

Hi Babar,

Please find attached the sample of excel which I need to generate through my code.

Please let me know how can we achieve this using aspose.cells.

Thanks.

6

Hi,


Thanks for providing us template file.

Well, I checked your template file, you have actually specified List Data Validation to the B column in the first worksheet whose source is coming from Second worksheet’s range. I have implemented your scenario/ requirements using your template file and add data validation (List) to B2:B32 in the first worksheet. I re-inserted the source values in D column of the second worksheet and created the range based on the area (D1:D300), I added the Data Validation to the B column cells B2:B32 in the first worksheet based on the source range of the second worksheet, it works absolutely fine here. I have attached the output Excel file for your reference.
e.g
Sample code:

Workbook workbook = new Workbook(“e:\test2\sampleExcel.xls”);
//Get the second worksheet in the workbook
Worksheet worksheet2 = workbook.Worksheets[1];

//Populate the D column in the second sheet with data 300 Employees.
int i = 0;
for (i = 1; i <= 300; i++)
{
worksheet2.Cells[“D” + i].PutValue(“Emp00” + i);
}

Range range = worksheet2.Cells.CreateRange(“D1”, “D300”);
range.Name = “MyRange”;

//Get the first worksheet in the workbook
Worksheet worksheet1 = workbook.Worksheets[0];

ValidationCollection validations = worksheet1.Validations;

//Clear the existing validation if you want.
validations.Clear();

Validation validation = validations[validations.Add()];

validation.Type = Aspose.Cells.ValidationType.List;

validation.Operator = OperatorType.None;

validation.InCellDropDown = true;
validation.IgnoreBlank = true;
validation.Formula1 = “=MyRange”;
validation.ShowError = true;
validation.AlertStyle = ValidationAlertType.Stop;
validation.ErrorTitle = “Error”;
validation.ErrorMessage = “Please select a Provider Group Code from the list.”;


//Specify the area i.e. B2:B32
CellArea area;
area.StartRow = 1;

area.EndRow = 31;

area.StartColumn = 1;

area.EndColumn = 1;

validation.AreaList.Add(area);

workbook.Save(“e:\test2\outSampleExcel1.xls”);

Note: Please try using our latest version/fix v8.0.1.x (as Babar recommended), it will work fine.

Thank you.