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.
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
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)
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
Sending Outlook Meeting Requests Without Outlook
String.Isnullorempty(String) VS. String.Isnullorwhitespace(String)
Get a List of Distinct Values in List
Why Would I Bother to Use Task.Configureawait(Continueoncapturedcontext: False);
Convert Wpf (Xaml) Control to Xps Document
How to Add a Line Break in C# .Net Documentation
What Is a Good Unique Pc Identifier
How to Write a Viewmodelbase in Mvvm
What Replaces Wcf in .Net Core
Best Way to Convert Callback-Based Async Method to Awaitable Task
What Do Programmers Mean When They Say, "Code Against an Interface, Not an Object."
Observablecollection and Item Propertychanged
Programmatic Control of Virtual Desktops in Windows 10
Open File Dialog and Select a File Using Wpf Controls and C#