AddImage - Image Scaling Aspect Ratio

[Manry_Steven]

First, let me say that this is a great product.  I have been able to accomplish most everything quite easily.  The only issue I am having is with images. 

We have a report where we layout a number of rows/cell with data.  At the end of the report, we need to add some images to it that stack one on top of the other.  I have this working for the most part and I have restricted the overall column width of the image from columns 1 to 5.  The problem is that when adding images, you have to also specify the starting and ending row as well.  This can skew an image as well as make the overlap because we had to use a fixed height in cells for the images. 

Below is the code portion that adds the images:

//Add Images

var files1 = Directory.EnumerateFiles (picPath);
int i = 0;
int iImageHeightInCells = 20;

TClientAnchor imgAnchor = new TClientAnchor();
imgAnchor.AnchorType = TFlxAnchorType.MoveAndResize;

imgAnchor.Row1 = iImageRowStart;
imgAnchor.Row2 = iImageRowStart + iImageHeightInCells;
imgAnchor.Col1 = 1;
imgAnchor.Col2 = 5;
foreach (var file in files1) {
 

if (file.EndsWith ("jpg")) {

     TImageProperties imgP = new TImageProperties();

      if (i > 0) {

         imgAnchor.Row1 = imgAnchor.Row1 + iImageHeightInCells + 2;

         imgAnchor.Row2 = imgAnchor.Row2 + iImageHeightInCells + 2;

      }

      imgP.Anchor = imgAnchor;

      xls.AddImage(file, imgP);

      imgP = null;

      i++;
 
}
}

We chose a random height in cells (iImageHeightInCells) for the images that works most of the time for our images being 5 columns wide.  Sometimes depending on the picture size, this isn't always the best way to do it.

What would be nice is to have a starting row where the images need to be added and then calculate the number of rows that it will take up if the width of the image is restricted to 5 columns so that the image would scale properly and maintain aspect ratio.

The, image 2 would go after image 1 and the "height in rows" would be calculated by the TImageProperties somehow.

Is there functionality like this alread that I might be missing?

Thanks in advance.

Bo

[adrian]

Hi,

The functionality is almost built in. Basically, you need to use the constructor of TClientAnchor that ttakes a width and a height, and FlexCel will calculate the ending rows and columns. (Excel always needs rows and columns, so TClientAnchor needs to calculate them.

The line should be:

TClientAnchor imgAnchor = new TClientAnchor(TFlxAnchorType.MoveAndResize, iImageRowStart, 0, 1, 0, imgHeight, imgWidth, xls);

Where imgHeight and imgWidth are the height an width of the image in "pixels". I wrote "pixels" in quotes because Excel doesn't deal with real pixels, but with resolution independent pixels instead. An image might have 640 pixels width, but the width in the screen will depend on the screen resolution.

So, now having this part, how do we calculate imgHeight and imgWidth.

You want imgWidth to be 5 columns. So you'll need to loop over all columns, sum their width, and divide by ExcelMetrics.ColMult to get the row in pixels. Then to keep the aspect ratio, imgHeight should be originalimageheight * newWidth/OriginalImageWidth

A full working example is below:

    private double GetWidth(ExcelFile xls, int col1, int col2)
    {
        double Result = 0;
        for (int c = col1; c < col2; c++)
        {
            Result += xls.GetColWidth(c, true);
        }

        return Result / ExcelMetrics.ColMult(xls);
    }

    private void TmpButton_Click(object sender, System.EventArgs e)
    {
        string file = "r:\\test.png";
        XlsFile xls = new XlsFile("r:\\test.xlsx", true);
        int imgColStart = 3;
        int imgColWidth = 5;
        int imgWidth = (int)(GetWidth(xls, imgColStart, imgColStart + imgColWidth));
        int imgHeight = 0;
        using (Image img = Image.FromFile(file))
        {
            if (img.Widht > 0) imgHeight = img.Height * imgWidth / img.Width;
        }
        int iImageRowStart = 2;
        TClientAnchor imgAnchor = new TClientAnchor(TFlxAnchorType.MoveAndResize, iImageRowStart, 0, imgColStart, 0, imgHeight, imgWidth, xls);

        xls.AddImage(file, new TImageProperties(imgAnchor, file));
        xls.Save("r:\\test2.xlsx");

}

regards,

Adrian

Adrian Gallero2014-01-23 08:14:43

[Manry_Steven]

Adrian,

Your code works PERFECTLY.  Thanks a ton.  I am new to Flexcell and I don't think I would have figured that out.  I only have 2 small issues remaining and hopefully you can fill in the gaps and both are realted to this post so I am putting it here for other people in the future who might have the same issue.

If I have multiple images that need to be inserted into the file, I want to add them one after each other with 2 rows as a separator.  In my code, I was using the following:

if (i > 0) {
   iImageRowStart = iImageRowStart + imgHeight + 2;
}

i represents a counter variable.  The problem here is that imgHeight was calcualted as the Pixel height and not the "excel rows".

Do you have a routine handy that can calculate the number of rows that an image of a certain pixel height will take up since we know the starting row?



The last piece to the puzzle is that once we construct the excel file, we also export it to PDF.  As the images are added to the excel file, page breaks are being inserted in the middle of an image.  Is there a way to know if the images is going to cross over a page break and if so, I can reset the iImageRowStart variable to be the first row on the next page.

Thanks in advance,

Bo

[adrian]

Hi,

The first one is easy: When you construct a TClientAnchor, even if you construct it from pixel height, the ClientAnchor will contain the rows and columns of the image.

So you can just add the following line to the sample I wrote last time:

        TClientAnchor imgAnchor = new TClientAnchor(TFlxAnchorType.MoveAndResize, iImageRowStart, 0, imgColStart, 0, imgHeight, imgWidth, xls);

        int finalRow = imgAnchor.Row2;

And you'll get the row where the image ends in final row. Note that you might also need to look at the ClienAnchor.Dy2 property to know how muc of the row is used by the image. (0 means the image ends at the start of the row, 255 at the end, and anything in the middle means that percentage of the row)

About the second quesiton, sadly that's more difficult. The reason being, we can't really know until print time where the page breaks are going to end up. For example, the user could choose a different paper size, or print landscape instead of portrait, and the page breaks will move. Sometimes they even move depending on the printer where you are printing.

So no, you can't know exactly, but you can guess by adding the row heights (the same way we added the column widths in the code from my last post).

But FlexCel also comes with something else that you could use, and that is "Intelligent page breaks"  (I'll cal them ipb in the rest of this post). Olease in addition to what I say here, take a look to the section in intelligent page breaks in the APIGuide.pdf. 

With ipb, you define a number of rows that you want to "keep together", and then call a method so FlexCel calculates and adds manual page breaks to the file. In this example, you want to keep together the images, so those are the rows we want to keep together. Note that the problem of user changing page size still exists, but aside from that it should work fine.

This is some code that adds an image with random heights (to simulate your case) and ensures page breaks don't land in the middle:

   private void TmpButton_Click(object sender, System.EventArgs e)
    {
        string file = "r:\\test.png";
        XlsFile xls = new XlsFile("r:\\test.xls", true);
        int imgColStart = 3;
        int imgColWidth = 5;
        int imgWidth = (int)(GetWidth(xls, imgColStart, imgColStart + imgColWidth));
        var rnd = new Random();

        int iImageRowStart = 2;
        for (int i = 0; i < 30; i++)
        {
            int imgHeight = 200 + rnd.Next(100);

            TClientAnchor imgAnchor = new TClientAnchor(TFlxAnchorType.MoveAndResize, iImageRowStart, 0, imgColStart, 0, imgHeight, imgWidth, xls);

            iImageRowStart = imgAnchor.Row2 + 2;

            xls.AddImage(file, new TImageProperties(imgAnchor, file));
            xls.KeepRowsTogether(imgAnchor.Row1, imgAnchor.Row2, 1, false);
        }

        xls.AutoPageBreaks(20, 95); 
        xls.Save("r:\\test2.xlsx");

}

[Manry_Steven]

Thanks again for your reply.  Everything worked perfectly!

Best Regards,

Bo