We're sorry Aspose doesn't work properply without JavaScript enabled.

Free Support Forum - aspose.com

Sorting data in Excel file causes pictures stay misaligned in Java

Hi,

i’m not sure if it is an excel 2010 limitation or i’m doing something wrong while attaching pictures to an excel file.
Issue is that pictures are correctly added, set to move_and_size but when i apply a sort on other columns present into the sheet, pictures stay locked generating a misalignment between cells content and pictures.
I’ve created another process using ole instead of add pictures and seems sorting works properly.

This is the code i used so far

for( int x = 1; x <= rowcount ; x++ ){

String stcell = cellsw.get(x,13).getStringValue();

File imgin = new File(dataDir + “large_pictures\”+ COU + “\” +stcell);

if (!imgin.exists()){

logger.warn(" Image " + stcell + “doesn’t exists!”);

Cell cellw = cellsw.get(x,13);

cellw.setValue(“PICTURE NOT FOUND”);

Style style = cellw.getStyle();

Font font = style.getFont();

font.setColor(Color.getRed());

style.setBorder(BorderType.BOTTOM_BORDER, CellBorderType.THICK, Color.getLightGray());

style.setBorder(BorderType.LEFT_BORDER, CellBorderType.THICK, Color.getLightGray());

style.setBorder(BorderType.RIGHT_BORDER, CellBorderType.THICK, Color.getLightGray());

style.setBorder(BorderType.TOP_BORDER, CellBorderType.THICK, Color.getLightGray());

cellw.setStyle(style);

}

else{

BufferedImage in = ImageIO.read(imgin);

File imgot = new File(dataDir + “small_pictures\”+ COU + “\” +stcell);

BufferedImage ot = Scalr.resize(in,Method.QUALITY,150,150,Scalr.OP_ANTIALIAS);

ImageIO.write(ot, “jpg”, imgot);

//Set the row height

sheet2.getCells().setRowHeight(x,150 );

//Set the column width

sheet2.getCells().setColumnWidth(13,40);

//Add a picture to the cell

int index = sheet2.getPictures().add(x,13,x+1,14,dataDir + “small_pictures\”+ COU + “\” +stcell);//,he,wd);

//Get the picture object

Picture pic = sheet2.getPictures().get(index);

//Set the placement type

pic.setPlacement(PlacementType.MOVE_AND_SIZE);

//Add border

Cell cellw = cellsw.get(x,13);

cellw.setValue(cellsw.get(x,5).getValue());

Style style = cellw.getStyle();

style.setBorder(BorderType.BOTTOM_BORDER, CellBorderType.THICK, Color.getLightGray());

style.setBorder(BorderType.LEFT_BORDER, CellBorderType.THICK, Color.getLightGray());

style.setBorder(BorderType.RIGHT_BORDER, CellBorderType.THICK, Color.getLightGray());

style.setBorder(BorderType.TOP_BORDER, CellBorderType.THICK, Color.getLightGray());

style.setCustom(";;;");

cellw.setStyle(style);

}

}
Thanks in advance,
Regards

Hi again,


i copied a workaround from another topic

int height = pic.getHeight();
int width = pic.getWidth();
pic.setHeight(height - 1);
pic.setWidth(width - 1);

and sort is now working fine.

Regards,

Hi,

Thanks for sharing us nice code and workaround and using Aspose.Cells.

We are glad to know that you were able to sort out your issue yourself and shared it with everyone. Let us know if you encounter any other issue, we will be glad to look into it and help you further. Have a good day and best wishes.