Adding Images into Excel Using Epplus

Adding images into Excel using EPPlus

I'm not sure if this is the best solution but definetly a workaround for your problem.

Here's what I did:

ExcelPackage package = new ExcelPackage();
var ws = package.Workbook.Worksheets.Add("Test Page");

for (int a = 0; a < 5; a++)
{
ws.Row(a * 5).Height = 39.00D;
}

for (int a = 0; a < 5; a++)
{
var picture = ws.Drawings.AddPicture(a.ToString(), logo);
picture.SetPosition(a * 5, 0, 2, 0);
}

Here is how it looks.

Sample Image

For some reason when we have the row height set, its interfering with the picture height.

Adding Background Image to Excel using EPPlus not working

The BackgroundImage on the sheet has a method SetFromFile which does the trick.

Full code

var file = @"c:\folder\spreadsheet.xlsx"; // Path to your source spreadsheet file here.
var image = @"c:\folder\background.png"; // Path to your background image here.
var imageFile = new FileInfo(image);

using (var fs = new FileStream(file, FileMode.Open, FileAccess.Read, FileShare.ReadWrite))
using (var package = new ExcelPackage(fs))
{
foreach (var sheet in package.Workbook.Worksheets)
{
sheet.BackgroundImage.SetFromFile(imageFile);
}

package.SaveAs(new FileInfo(@"c:\folder\new.xlsx")); // Path to destination spreadsheet file here;
}

EPPlus: Position image in a cell

So I abandoned the

picture.To.Column = cell.Start.Column;
picture.To.Row = cell.Start.Row;

since I just could not get it to work and decided to calculated my own dimensions using:

picture.SetSize(width, height);

The trick is to understand how Excel actually calculates widths and heights.

Height of a cell: Its measured in points, but we want pixels. There are 72 points in an inch. One can convert points to pixel using the following formula points* (1/72.0) * DPI. DPI is dots per inch and can be found using the following method:

using (Graphics graphics = Graphics.FromHwnd(IntPtr.Zero))
{
float dpiY = graphics.DpiY;
}

So to calculate the height of a cell in pixels I used

private static int GetHeightInPixels(ExcelRange cell)
{
using (Graphics graphics = Graphics.FromHwnd(IntPtr.Zero))
{
float dpiY = graphics.DpiY;
return (int)(cell.Worksheet.Row(cell.Start.Row).Height * (1 / 72.0) * dpiY);
}
}

Width of a cell: This is a bit trickier. Basically the width of a cell in excel is equal to the number of characters (formatted using the default font) that a cell can contain horizontally.

For example

Sample Image

This colum is of length 12 and can contain 12 numbers in the Calibri(11) font.

That is also my excel default since my body default is calibri(11)
Sample Image

Here is an article explaining it in more depth.

The next question is how on earth does one translate that to pixels.

Firstly we need to discover what the length of a character is in the default font. One could use TextRenderer.MeasureText in the System.Windows.Forms namespace. However I am using .Net Core and needed another way. Another way is to use the the System.Drawing.Common core lib which is now in preview.

public static float MeasureString(string s, Font font)
{
using (var g = Graphics.FromHwnd(IntPtr.Zero))
{
g.TextRenderingHint = System.Drawing.Text.TextRenderingHint.AntiAlias;

return g.MeasureString(s, font, int.MaxValue, StringFormat.GenericTypographic).Width;
}
}

I then used that method to calculate the width in pixels as follows

private static int GetWidthInPixels(ExcelRange cell)
{
double columnWidth = cell.Worksheet.Column(cell.Start.Column).Width;
Font font = new Font(cell.Style.Font.Name, cell.Style.Font.Size, FontStyle.Regular);

double pxBaseline = Math.Round(MeasureString("1234567890", font) / 10);

return (int)(columnWidth * pxBaseline);
}

Edit: Please note that overlap still happens when the zoom factor is set to more than 100% under display settings

Writing Images to Excel file using EPPlus

Your issue is most certainly with this line:

img.SetPosition(pRowIndex, Pixel2MTU(2), 1, Pixel2MTU(2));

I'm not sure why you are converting pixels to anything considering SetPosition is looking for the offset in pixels. From the metadata:

    // Summary:
// Set the top left corner of a drawing. Note that resizing columns / rows after
// using this function will effect the position of the drawing
//
// Parameters:
// Row:
// Start row
//
// RowOffsetPixels:
// Offset in pixels
//
// Column:
// Start Column
//
// ColumnOffsetPixels:
// Offset in pixels
public void SetPosition(int Row, int RowOffsetPixels, int Column, int ColumnOffsetPixels);

I would recommend just passing through small values, such as 2, for the RowOffestPixels and ColumnOffsetPixels parameters:

img.SetPosition(pRowIndex, 2, 1, 2);

I found a method called Pixel2MTU(int pixels) on codeproject from a quick google search. The method is as follows:

public int Pixel2MTU(int pixels)
{
int mtus = pixels * 9525;
return mtus;
}

If this is the same method you are using, your images might be at the very far bottom right of your excel document.

Insert Same Image Multiple Times Into Excel Using EPplus

The error already told you the answer


This line your count1 is 1

var pic = ws.Drawings.AddPicture(count1.ToString(), myImage);  

Then this line

for (int a = 0; a < 5; a++)
{
var picture = ws.Drawings.AddPicture(a.ToString(), repeatimage);
picture.SetPosition(a * 5, 0, 2, 0);
}

Your a will be 0, 1, 2, 3, 4 as the name of the picture

You see the error ?

Placing image inside Excel file

You can try to use EPPlus library http://epplus.codeplex.com/.

You do not need to convert chart to image and insert it into Excel, you can create chart with this library same as one in your app.

Example of adding image to Excel using EPPlus (this is only example, not full code):

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);
}
}

Adding image to Excel

Figured it out, I just have to adjust the position of the picture by changing these 2 values

ws.Drawings.Item("picture1").SetPosition(row, -27, column, -27)

Just have to play around with the values to get the position you want.

C# EPPlus resize pictures to fit in the cell

This issue resolved. i place image at the end before saving excel file.
here is the code.

    if (imgCompanyLogo != null)
{
decimal mdw = ws.Workbook.MaxFontWidth;
int pixelHeight = (int)(ws.Row(1).Height / 0.75);
int pixelWidth = (int)decimal.Truncate(((256 * (decimal)ws.Column(2).Width + decimal.Truncate(128 / (decimal)mdw)) / 256) * mdw);

var picture = ws.Drawings.AddPicture("Test", imgCompanyLogo);
picture.SetSize(pixelWidth, pixelHeight);
//picture.EditAs = OfficeOpenXml.Drawing.eEditAs.TwoCell;
picture.SetPosition(0, 0, 1, 0);
}
output.Save();

Thanks



Related Topics



Leave a reply



Submit