Add TextBox to MS Excel worksheet in Java

Aspose.Cells Product Family
1

Hi,


Using Java Aspose, I would like to add text box to excel giving start column and end column.
I find API for adding text box by giving start column and width, but I would like to give end column , since width wouldn’t be constant in my application use case. How can I achieve this?

Need another way than below approach:
textboxIndex = worksheet.getTextBoxes().add(15, 4, 85, 120);
2

Hi,


Thanks for your query.

Well, you need to calculate the column(s) width (where you place the text box) in pixels first and then add the text box to the cell. You may use Cells.getColumnWidthPixel method to get the column’s width, so you could apply that value for textbox’s width while adding the text box. Please see the sample code with template file (attached). I have also attached the output file for your reference:
e.g
Sample code:

Workbook wb = new Workbook(“bk_addtextbox1.xlsx”);

Worksheet ws = wb.getWorksheets().get(0);

Cells cells = ws.getCells();

//Get the column width in pixels of second column i.e, B.

int colwidth = cells.getColumnWidthPixel(1);

System.out.println(colwidth);

//Now add the text box in B2 cell.

int index = ws.getTextBoxes().add(1, 1, 85, colwidth);

wb.save(“outbkaddtextbox1.xlsx”);

Hope, this helps a bit.

Thank you.
3

Amjad,


Thanks for responding. I agree with your response, but in my application , say after drawing text box, I might have other tables below the text box , and data in table is wider than standard pixel column width, in such cases width of text box is fixed. I wanted to draw text box between given column range (say 1-6),so that even though excel cell width increases, text box should also increase accordingly. Can I achieve that?

Thanks,
Rajitha.
4

Hi,


In that case, you may set the placement type attribute of the text box accordingly. You need to set its placement in that way, so, when you increase the column’s width, the text box’s width also gets increased accordingly, see the updated sample code below for your reference:
e.g
Sample code:

Workbook wb = new Workbook(“bk_addtextbox1.xlsx”);

Worksheet ws = wb.getWorksheets().get(0);

Cells cells = ws.getCells();

//Get the column width in pixels of second column i.e, B.

int colwidth = cells.getColumnWidthPixel(1);

System.out.println(colwidth);

//Now add the text box in B2 cell.

int index = ws.getTextBoxes().add(1, 1, 85, colwidth);

**//Get the text box.**

**TextBox textBox = ws.getTextBoxes().get(index);**

**//Set its placement type so, when you increase the column’s width, the text box’s width also gets increased.**

**textBox.setPlacement(PlacementType.MOVE_AND_SIZE);**

wb.save(“outbkaddtextbox1.xlsx”);
Hope, this helps a bit.

Thank you.
5

Amjad,


I tried it in first place, it didn’t work. I am using 7.3.2 aspose cells jar.

My Code:
int add = sheet.getTextBoxes().add(startRowIndex, startColumnIndex, height , width);

TextBox addTextBox = sheet.getTextBoxes().get(add);

addTextBox.setText(value);

TextFormat textFormat = label.getTextFormat();

if(null != textFormat) {

Font font = addTextBox.getFont();

font.setBold(textFormat.isBold());

font.setSize(textFormat.getFontSize());

font.setName(textFormat.getFontName());

if(0 != textFormat.gethAlignment()) {

addTextBox.setTextHorizontalAlignment(textFormat.gethAlignment());

}

}

addTextBox.setPlacement(PlacementType.MOVE_AND_SIZE);
6

Hi,


Thanks for the code segment and details.

Please try our latest fix/version: Aspose.Cells for Java v8.4.0.5 if it makes any difference.

I am not sure about some of the object variables/ pointers’ values in your code segment. So, if you still find the issue with v8.4.0.5, kindly provide us your complete sample JAVA program (runnable) and output Excel file here, we will check it soon. Also provide your desired file in which you have manually added the text box with your desired settings and formattings in MS Excel, it will help us to evaluate your issue precisely.

Thank you.