Create controls

Aspose.Cells Product Family
1

Hi ,

I want to make a program that takes input from the user for which control they want to insert in their excel and at what location.

they enter the cordinates as textbox at "AA3", combobox at "IH2" etc.

is their anyway by which I can get to create a control at position AA3 , as the

AddComboBox function just takes input in the form of numbers like

AddComboBox(14, 0, 8, 0, 22, 190)

Please advice.

Regards

Shalini

2
Hi Shalini,

Well, you can make use of CellsHelper static class. May the following code help you for your requirement. I think similar approach can be used for creating TextBox control:

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"].Style.Font.IsBold = true;
//Input some values that denotes the input range
//for the combo box.
cells["A2"].PutValue("Emp001");
cells["A3"].PutValue("Emp002");
cells["A4"].PutValue("Emp003");
cells["A5"].PutValue("Emp004");
cells["A6"].PutValue("Emp005");
cells["A7"].PutValue("Emp006");
int row, col;
CellsHelper.CellNameToIndex("AA3",out row, out col);
//Add a new combo box.
Aspose.Cells.ComboBox comboBox = sheet.Shapes.AddComboBox(row, 0, col, 0, 18,70 );
//Set the linked cell;
comboBox.LinkedCell = "A1";
//Set the input range.
comboBox.InputRange = "A2:A7";
//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;
sheet.AutoFitColumns();
workbook.Save("d:\\test\\cbo_test.xls");

Thank you.

3

Hi Amjad,

Thanks for your response. Now I want to add controls and specify their dimesion as following

Textbox at D10 and Merge cells D10-F14. is their any way i can specify the size of the control in this way rather giving its height and width in the function AddTextBox()

4

Hi Shalini,

May the following cod help you for your requirement:

//Instantiate a new Workbook.
Workbook workbook = new Workbook();
//Get the first worksheet in the book.
Worksheet worksheet = workbook.Worksheets[0];
//Merge cells D10:F14.
worksheet.Cells.Merge(9,3,4,2);
int row, col;
CellsHelper.CellNameToIndex("D10",out row,out col);
int ht=0, wid=0;
for (int i = 9;i<14;i++)
{
ht = ht + worksheet.Cells.GetRowHeightPixel(i);
}
for (int j=3;j<6;j++)
{
wid = wid + worksheet.Cells.GetColumnWidthPixel(j);
}
//Add a new textbox to the collection.
int textboxIndex = worksheet.TextBoxes.Add(row,col,ht ,wid);
//Get the textbox object.
Aspose.Cells.TextBox textbox0 = worksheet.TextBoxes[textboxIndex];
//Fill the text.
textbox0.Text = "ASPOSE TextBox";
//Set the font color.
textbox0.Font.Color = Color.Blue;
//Set the font to bold.
textbox0.Font.IsBold = true;
//Set the font size.
textbox0.Font.Size = 14;
//Set font attribute to italic.
textbox0.Font.IsItalic = true;
//Save the excel file.
workbook.Save("d:\\test\\textbox_new.xls");
Thank you.
5

Hi

-- I want to create a control listbox at position D10. But i dont know how many cells and rows to merge.

-- i check the template excel sheet at the position D10.

---check how many cells and rows are merged at this position D10

---. Then i create my ListBox control spanning the number of cells and rows found merged at D10

can you advice on how to get this done using Aspose please.

6

Hi

I want to create a listbox control at position D10

Program dont know how many cells and coloumn to merge to create it

it looks at the template excel sheet and finds the number of rows and columns merged at D10.

It creates a Listbox xpanning those number of cols and rows.

Please advice how to get it done using Aspose.

Regards

Shalini

7

Hi,

Kindly consult the following code. I used it with a template file (attached). In the template file D10:F21 cells are merged. I dynamically get the merged cells area and then insert a list box into those merged cells. The output file (attached) shows that the list box control will be placed into the merged cells area.

Workbook workbook = new Workbook();
workbook.Open("d:\\test\\listboxmerged.xls");
Worksheet sheet = workbook.Worksheets[0];
Cells cells = sheet.Cells;
//Input a value.
cells["B3"].PutValue("Employee:");
//Set it bold.
cells["B3"].Style.Font.IsBold = true;
//Input some values that denotes the input range
//for the list box.
cells["A2"].PutValue("Emp001");
cells["A3"].PutValue("Emp002");
cells["A4"].PutValue("Emp003");
cells["A5"].PutValue("Emp004");
cells["A6"].PutValue("Emp005");
cells["A7"].PutValue("Emp006");

//Creat an array list for getting the merged cells area i.e. D10:F21
ArrayList al = new ArrayList();
al = cells.MergedCells;
CellArea ca;
int frow,fcol,erow,ecol;
ca = new CellArea();
ca = (CellArea)al[0];
frow = ca.StartRow;
fcol = ca.StartColumn;
erow = ca.EndRow;
ecol = ca.EndColumn;


int ht=0, wid=0;
for (int r = frow; r<=erow; r++)
{
ht = ht + sheet.Cells.GetRowHeightPixel(r);

}

for (int c = fcol; c<=ecol; c++)
{
wid = wid + sheet.Cells.GetColumnWidthPixel(c);

}

//Add a new list box.
Aspose.Cells.ListBox listbox = sheet.Shapes.AddListBox(frow, 0,fcol, 0, ht,wid);
//Set the input range.
listbox.InputRange = "A2:A7";
//Set the list box with 3-D shading.
listbox.Shadow = true;
workbook.Save("d:\\test\\lstbox_test.xls");
8

hi,
This code can work only if there is one mergerd cellarea in a sheet.
If I have many cells at different locations merged than how do i calculate if a cell is mergerd at a location D10 ?
also could you please give the code in VB.net rather c#
regards

9
Hi,

Thanks for considering Aspose.

Well you may loop through the merged cell areas, check the merged cell area at D10 location and apply your desired code.

E.g.,

.
.
Dim al As ArrayList = New ArrayList()
al = cells.MergedCells
Dim ca As CellArea
Dim frow,fcol,erow,ecol As Integer

Dim i As Integer
For i = 0 To al.Count -1
ca = New CellArea()
ca = CType(al(i),CellArea)
frow = ca.StartRow
fcol = ca.StartColumn
erow = ca.EndRow
ecol = ca.EndColumn
'Check if the merged cell area is on D10 cell.
If (frow=9 And fcol = 3 )

' your code.......

End If
.
.
.
.
Next

Thank you.