How to set width for the merged cells in Excel worksheet in Java

Aspose.Cells Product Family
1

Hi Aspose Team,


I have one doubt regarding cells merging… after merging of cells how can i set column width for the merged cells. I have tried lot of combinations, but not able to set the width. If you have some sample examples, please share it with me. it will be helpful to me…

Thanks & Regards,
Balakrishna Paluvari

2

Hi,


Well, in MS Excel, when you merge some cells, the merged cell becomes the top left most cell. Now, if you are merging cells spanning in multiple columns, the cells are merged to become the left most cell (will use its name etc.) but it does not mean that the columns are merged too. In short, you got to set width of the relevant individual columns separately. I have written a sample code for your reference:

e.g
Sample code:

Workbook workbook = new Workbook();

Worksheet workSheet = workbook.getWorksheets().get(0);

workSheet.getCells().get(2, 1).putValue(100);

Style style = workSheet.getCells().get(“B3”).getStyle();

style.setGradient(true);

style.setTwoColorGradient(Color.fromArgb(255, 255, 255), Color.fromArgb(79, 129, 189), GradientStyleType.HORIZONTAL, 1);

style.getFont().setColor(Color.getRed());

style.setHorizontalAlignment(TextAlignmentType.CENTER);

style.setVerticalAlignment(TextAlignmentType.CENTER);

workSheet.getCells().get(“B3”).setStyle(style);

workSheet.getCells().setRowHeightPixel(2, 53);

//Merge B3:C3 --> B3(merged)

workSheet.getCells().merge(2, 1, 1, 2);

//Set the column width of the merged cell (B3) i.e., B column

workSheet.getCells().setColumnWidth(workSheet.getCells().get(2, 1).getColumn(), 44);

//…Similarly you may set column width of the C column separately.

workbook.save(“out1.xlsx”);


Hope, this helps a bit.

Thank you.
3

Thanks Sahi,


Its helpful to me and can able to fix some issues, but not all the column width issues.

The above procedure will be applicable only for the one cell, but the other cell is forming with default width.

For reference i am attaching 2 excels, 1. dest excel. 2. excel producing after setting column width.

Please help on this.

Thanks & Regards
Balakrishna Paluvari.
4

Hi,


Thanks for the template files.

I have checked your template files. Well, your desired file has many merged cells with different alignment and other formatting settings. Moreover, different merged cells have equal widths of the corresponding columns involved, different merged cells have different row heights as well. You have to apply different formattings to different merged cells as per your expected file.

I have written a sample code for your reference. In the example, I pick the A8 merged cell and specify equal width of each column involved in the merged cell’s range. Also, I specify the font attributes and alignment settings to match with your desired file’s formatting a bit. Please refer to the code segment and write your own code to apply formatting for other cells in accordance with your desired formatting in your expected file.
e.g
Sample code:

Workbook workbook = new Workbook(“f:\files\What+i+am+getting.xlsx”);
Worksheet workSheet = workbook.getWorksheets().get(0);
//Get the cell
Cell cell = workSheet.getCells().get(“A8”);
if(cell.isMerged())
{
Style style = cell.getStyle();
//Set the font attributes
style.getFont().setName(“Tamoma”);
style.getFont().setSize(8);
//Set the alignment settings
style.setHorizontalAlignment(TextAlignmentType.GENERAL);
style.setVerticalAlignment(TextAlignmentType.TOP);
//Apply the style
cell.setStyle(style);
//get the merged cell’s range
Range range = cell.getMergedRange();

int sectionStartColumn = range.getFirstColumn();
int sectionEndColumn = range.getColumnCount() - range.getFirstColumn() + 1;


//Iterate through the columns to specify the width equally for each column in the merged cell’s range.
for (int i = sectionStartColumn; i <= sectionEndColumn; i++)

{
workSheet.getCells().setColumnWidth(i, 8.29);

}
}
workbook.save(“f:\files\out1.xlsx”);

Hope, this helps a bit.

Thank you.