Convert dimension in Pts to Character width

Aspose.Cells Product Family
1

Hi,

I am actually inserting an image in a worksheet. And along with it, I am also trying to expand the underlying cell’s size behind the image. But I am unable to do so accurately. I see that the column width is still smaller than the image width. This is happening in aspose.cells 18.7 java. Here is the code to reproduce the issue :

wings.jpg (230.5 KB)

public static void main(String[] args) throws Exception {
TestAsposeUtils.setupLicense(TestAsposeUtils.LICENSE_FILE_PATH);
String dataDir = Utils.getPOCDir(ImageTest.class);
Workbook book = new Workbook();
WorksheetCollection worksheets = book.getWorksheets();
Worksheet sheet = worksheets.get(0);
int pictureIndex = sheet.getPictures().add(1, 1, “C:\Users\akash_srivastava\Downloads\wings.jpg”); //$NON-NLS-1$

	//these dimensions would be user inputs (in points and non-negative)
	double imageWidth = 159;
	double imageHeight = 80;
	Picture picture = sheet.getPictures().get(pictureIndex);
	picture.setWidthPt(imageWidth);
	picture.setHeightPt(imageHeight);
	
	LineFormat lf = picture.getLine();
	lf.setCapType(LineCapType.FLAT);
	lf.setJoinType(LineJoinType.MITER);
	lf.setCompoundType(MsoLineStyle.SINGLE);
	lf.setDashStyle(MsoLineDashStyle.SOLID);
	lf.setWeight(0.75);
	picture.setBorderWeight(0.75);
	picture.setBorderLineColor(Color.getBlack());
	
	//Now, expand the background cell as much as possible
	Cells cells = sheet.getCells();
	double pts2charConversionRate = (cells.getStandardWidth() / (cells.getStandardWidthInch() * 72));
	double imageWidthInChars = imageWidth * pts2charConversionRate;
	if(imageWidthInChars < 255)
	{
		cells.setColumnWidth(1, imageWidthInChars);
	}
	else
	{
		cells.setColumnWidth(1, 255);
	}
	
	if(imageHeight < 409)
	{
		cells.setRowHeight(1, imageHeight);
	}
	else
	{
		cells.setRowHeight(1, 409);
	}
	
	book.save(dataDir + "ImageTest_out.xlsx"); //$NON-NLS-1$
	System.out.println(dataDir + "ImageTest_out.xlsx"); //$NON-NLS-1$
}
2

@Akash007,

Thanks for the sample code and image file.

After an initial test, I observed the issue as you mentioned by using your sample code with your template file. I found an issue with accuracy, the column width and row height are still a bit smaller than the image’s height/width. I have logged a ticket with an id “CELLSJAVA-42726” for your issue. We need to investigate if your sample code (which apparently looks ok) needs some tweaks or not.

Once we have an update on it, we will let you know here.

3

Thanks, Amjad for the quick reply.

4

@Akash007,

You are welcome.

5

@Akash007,

  1. The columns’ width is not simply the width of character * n. See the following description:
    Column width measured as the number of characters of the maximum digit width of the
    numbers 0, 1, 2, …, 9 as rendered in the normal style’s font. There are 4 pixels of margin
    padding (two on each side), plus 1 pixel padding for the gridlines.
    width = Truncate([{Number of Characters} * {Maximum Digit Width} + {5 pixel
    padding}]/{Maximum Digit Width}256)/256
    Using the Calibri font as an example, the maximum digit width of 11 point font size is 7
    pixels (at 96 dpi). In fact, each digit is the same width for this font. Therefore if the cell
    width is 8 characters wide, the value of this attribute shall be
    Truncate([8    7+5]/7*256)/256 = 8.7109375.
    To translate the value of width in the file into the column width value at runtime
  2. Please simply use cells.setColumnWidthInch(1, imageWidth /72.0 ); to set width in unit of Pts.
  3. The shape’s width and height will be changed if the placement of the shape is MoveAndSize when you change row and columns.
    a) If you want to set the width and height first, please try calling the following lines to set placement of the shape:
    picture.setPlacement(PlacementType.FREE_FLOATING);
    b) It’s better that you set the shape after setting rows and columns.
    See the following sample code:
    e.g
    Sample code:

    Workbook book = new Workbook();

		WorksheetCollection worksheets = book.getWorksheets();
		Worksheet sheet = worksheets.get(0);
		int pictureIndex = sheet.getPictures().add(1, 1, sourceFileDir + "wings.jpg"); //$NON-NLS-1$
		
		//these dimensions would be user inputs (in points and non-negative)
		double imageWidth = 159;
		double imageHeight = 80;
		
		
		//Now, expand the background cell as much as possible
		Cells cells = sheet.getCells();
//		double pts2charConversionRate = (cells.getStandardWidth() / (cells.getStandardWidthInch() * 72));
//		double imageWidthInChars = imageWidth * pts2charConversionRate;
//		if(imageWidthInChars < 255)
//		{
//			cells.setColumnWidth(1, imageWidthInChars);
//		}
//		else
//		{
//			cells.setColumnWidth(1, 255);
//		}
		cells.setColumnWidthInch(1, imageWidth /72.0 );
		if(imageHeight < 409)
		{
			cells.setRowHeight(1, imageHeight);
		}
		else
		{
			cells.setRowHeight(1, 409);
		}
		Picture picture = sheet.getPictures().get(pictureIndex);
		
		picture.setWidthPt(imageWidth);
		picture.setHeightPt(imageHeight);
		
		LineFormat lf = picture.getLine();
		lf.setCapType(LineCapType.FLAT);
		lf.setJoinType(LineJoinType.MITER);
		lf.setCompoundType(MsoLineStyle.SINGLE);
		lf.setDashStyle(MsoLineDashStyle.SOLID);
		lf.setWeight(0.75);
		picture.setBorderWeight(0.75);
		picture.setBorderLineColor(Color.getBlack());
		
		book.save(sourceFileDir + "dest.xlsx"); //$NON-NLS-1$
6

Thanks, Amjad. Given any value in pts for the column width, it can not be determined if the column will accept that value as internally it is converted into character widths. And if it turns out to be more than 255, we get the CellsException. We’ll have to try-catch the setColumnWidthInch() method every where we feed it with direct user input. In addition to that, this also makes it difficult to playaround with the column widths.

7

@Akash007,

Yes, you need to use Cells.setColumnWidthInch() method instead and follow the description/suggestions and sample code (as described in the previous post) to cope with it.

8

@Akash007,

Moreover, to avoid try-catch for too large column width values other than characters, you may try to get the maximum value corresponding to column width 255 and then use it as the limit.
e.g
Sample code:

                cells.SetColumnWidth(1, 255);
                double limit = cells.GetColumnWidthInch(1);
                ...
                if (wInch < limit)
                {
                    cells.SetColumnWidthInch(1, wInch);
                }
                else
                {
                    cells.SetColumnWidth(1, 255);
                }