This project has moved. For the latest updates, please go here.

Add image to comment/Embed images

Sep 30, 2010 at 11:33 AM

Hello

I am wanting to have an image per each row in an excel spreadsheet. One solution would be to add the image to the comment of the cell as shown here . Is it possible to add an image to a comment using Epplus?

If this isn't possible, is it possible to embed images into the excel documents and then just have a link to the image in the cell, so when the user clicks the link the image is displayed using their default image app?

If anyone has any other solutions that I could implement using Epplus please share them.

Thanks

Liam

Sep 30, 2010 at 12:48 PM

Hi,

I've done that already. Here's an example of how you can add one picture per row and adjust the row height to accommodate the picture. In my case al images have the same size.

I've also added my ExcelHelper class that I use to deal width unit conversion from pixel to MTU to column width...(just a mess)...that is the hardest part to deal with.

So here it goes:

	int pictureCol = 3;
	int pictureWidth = 41;
	int pictureHeight = 44;
	int currentRow = 1;

	//set picture column width to accommodate the picture
	ws.Column(pictureCol).Width = ExcelHelper.Pixel2ColumnWidth(ws, pictureWidth + 1);

	using (System.Drawing.Image img = /*...Load image here...*/)
	{
		if (img != null)
		{
			//set row height to accommodate the picture
			ws.Row(currentRow).Height = ExcelHelper.Pixel2RowHeight(pictureHeight + 1);

			//add picture to cell
			ExcelPicture pic = ws.Drawings.AddPicture("PictureUniqueName", img);
			//position picture on desired column
			pic.From.Column = pictureCol - 1;
			pic.From.Row = currentRow - 1;
			pic.From.ColumnOff = ExcelHelper.Pixel2MTU(1);
			pic.From.RowOff = ExcelHelper.Pixel2MTU(1);
			//set picture size to fit inside the cell
			pic.SetSize(pictureWidth, pictureHeight);
		}
	}
		
		
		
    public class ExcelHelper
    {
        //The correct method to convert width to pixel is:
        //Pixel =Truncate(((256 * {width} + Truncate(128/{Maximum DigitWidth}))/256)*{Maximum Digit Width})

        //The correct method to convert pixel to width is:
        //1. use the formula =Truncate(({pixels}-5)/{Maximum Digit Width} * 100+0.5)/100 
        //    to convert pixel to character number.
        //2. use the formula width = Truncate([{Number of Characters} * {Maximum Digit Width} + {5 pixel padding}]/{Maximum Digit Width}*256)/256 
        //    to convert the character number to width.

        public const int MTU_PER_PIXEL = 9525;

        public static int ColumnWidth2Pixel(ExcelWorksheet ws, double excelColumnWidth)
        {
            //The correct method to convert width to pixel is:
            //Pixel =Truncate(((256 * {width} + Truncate(128/{Maximum DigitWidth}))/256)*{Maximum Digit Width})

            //get the maximum digit width
            decimal mdw = ws.Workbook.MaxFontWidth;

            //convert width to pixel
            decimal pixels = decimal.Truncate(((256 * (decimal)excelColumnWidth + decimal.Truncate(128 / (decimal)mdw)) / 256) * mdw);
            //double columnWidthInTwips = (double)(pixels * (1440f / 96f));

            return Convert.ToInt32(pixels);

        }

        public static double Pixel2ColumnWidth(ExcelWorksheet ws, int pixels)
        {
            //The correct method to convert pixel to width is:
            //1. use the formula =Truncate(({pixels}-5)/{Maximum Digit Width} * 100+0.5)/100 
            //    to convert pixel to character number.
            //2. use the formula width = Truncate([{Number of Characters} * {Maximum Digit Width} + {5 pixel padding}]/{Maximum Digit Width}*256)/256 
            //    to convert the character number to width.

            //get the maximum digit width
            decimal mdw = ws.Workbook.MaxFontWidth;

            //convert pixel to character number
            decimal numChars = decimal.Truncate(decimal.Add((decimal)(pixels - 5) / mdw * 100, (decimal)0.5)) / 100;
            //convert the character number to width
            decimal excelColumnWidth = decimal.Truncate((decimal.Add(numChars * mdw, (decimal)5)) / mdw * 256) / 256;

            return Convert.ToDouble(excelColumnWidth);
        }

        public static int RowHeight2Pixel(double excelRowHeight)
        {
            //convert height to pixel
            decimal pixels = decimal.Truncate((decimal)(excelRowHeight / 0.75));

            return Convert.ToInt32(pixels);
        }

        public static double Pixel2RowHeight(int pixels)
        {
            //convert height to pixel
            double excelRowHeight = pixels * (double)0.75;

            return excelRowHeight;
        }

        public static int MTU2Pixel(int mtus)
        {
            //convert MTU to pixel
            decimal pixels = decimal.Truncate((decimal)(mtus / MTU_PER_PIXEL));

            return Convert.ToInt32(pixels);
        }

        public static int Pixel2MTU(int pixels)
        {
            //convert pixel to MTU
            int mtus = pixels * MTU_PER_PIXEL;

            return mtus;
        }
	}
Hope this helps.
Regards,
Orlando
Nov 21, 2014 at 4:23 PM
With EPPlus Can I get the width of the column as a number of '0' characters in the font of the 'Normal' style, like WidthInChars in ExcelWriter http://docs.softartisans.com/officewriterwindows/3.0.4/ExcelWriter/reference/ColumnProperties-WidthInCharsProperty.aspx ?