EPPlus AddPicture 100% size
Update: part of the problem is Excels fault. Its changing the image size between different view settings (normal layout vs page layout). I may have to re-evaluate, whether the remaining difference even matters...
How can I add an image to an excel sheet via EPPlus without any resizing (open in Excel afterwards and have 100% scaling set in the image properties)?
The image ( Testfile.jpg ) in my test case is a jpg with 508px width, 177px height and 300 DPI resolution.
My Test code:
class Program
{
static void Main(string[] args)
{
using (var package = new OfficeOpenXml.ExcelPackage(new FileInfo(@"C:UserTempTestTestFormat.xlsx")))
{
OfficeOpenXml.ExcelWorksheet worksheet = package.Workbook.Worksheets[1];
var img = worksheet.Drawings.AddPicture("Test", new FileInfo(@"C:UserTempTestTestfile.jpg"));
img.SetPosition(4, 0, 5, 0);
img.SetSize(100);
package.SaveAs(new FileInfo(@"C:UserTempTestTestFormat2.xlsx"));
}
}
}
For EPPlus 4.0.5, I get the following results when looking at the image size in the resulting xlsx file:
4.31cm instead of 4.30cm image width. 4.47cm instead of 4.30cm image width. For EPPlus 4.1.0, I get completely different results:
13,44cm instead of 4.30cm image width. 13,81cm instead of 4.30cm image width. The heigth properties are behaving similar, but unfortunately the width:height ratio is not consistent.
I already experimented with a few things like img.SetSize(widthPx, heigthPx) and different image formats without success.
So for now I'm lost with inconsistent results
and I need at least guaranteed width:height proportions, best would be guaranteed image size.
Note: I can't create and use an image at 96 DPI here, since this results in very pixelated printing.
Edit:
The following image shows the result for a file with a bit irregular layout that I just created as showcase. The green box is the inserted image, the messed up proportions are highlighted in the dialog window (its in german, but the relevant things are the numbers anyway).
In case someone runs into a similar problem, here is what I do to fix my specific problem.
Excel is stretching the image in page view , compared to normal view . Since I don't care about image proportions in normal view, I can adjust the image size. I analyzed the stretching for my case and found the following ranges for 96 DPI adjusted image widths (height was not changed in my case):
Note that the correction values are not 100% accurate, since there are also some rounding errors in image size transformation for different DPI
0px - 54px: 0px to much width
55px - 109px: 4px to much width
110px - 165px: 8px to much width
166px - 317px: 12px to much width
318px - 442px: 23px to much width
443px - ???: 32px to much width // I didn't bother to investigate larger values
This means that most widths can be adjusted by subtracting the mentioned offset. For border cases between different offsets, a small error will remain. The following width transformation function ( AdjustExcelPageViewImageWidth ) implements correction for the described errors
/// <summary>
/// Excel is changing the image size in page view, this is an attempt to correct the change for a range of widths.
/// Input width is required as pixel equivalent of the desired width for 96 DPI.
/// </summary>
int AdjustExcelPageViewImageWidth(int desiredWidth96DPI)
{
return
LimitedRangeTransform(desiredWidth96DPI, 0, 54, 0, 4) ??
LimitedRangeTransform(desiredWidth96DPI, 55, 109, 4, 8) ??
LimitedRangeTransform(desiredWidth96DPI, 110, 165, 8, 12) ??
LimitedRangeTransform(desiredWidth96DPI, 166, 317, 12, 23) ??
LimitedRangeTransform(desiredWidth96DPI, 318, 442, 23, 32) ??
// no data gathered for larger images
desiredWidth96DPI - 32;
}
/// <summary>
/// Interpolation function between ranges with different limit and offset. Returns the transformed width or null.
/// </summary>
/// <param name="width">the desired width</param>
/// <param name="lower">the lower bound of the range, where the offset applies</param>
/// <param name="upper">the upper bound of the range, where the offset applies</param>
/// <param name="offset">the offset value will be subtracted for width within the range</param>
/// <param name="nextOffset">the offset value for the next range following the upper bound</param>
/// <returns></returns>
int? LimitedRangeTransform(int width, int lower, int upper, int offset, int nextOffset)
{
// not handling those cases
if (upper < lower || width < lower + offset)
{
return null;
}
if (width <= upper + offset)
{
return width - offset;
}
if (width <= upper + offset + (nextOffset - offset) / 2)
{
// border cases, can't be accurate
return upper;
}
if (width <= upper + nextOffset)
{
// border cases, can't be accurate
return upper + 1;
}
return null;
}
The other problem - different behavior of EPPlus SetSize(percentage) between versions was avoided by using the SetSize(widthPx, heightPx) function.
So, my final implementation would be similar to the following:
.
static void Main(string[] args)
{
using (var package = new OfficeOpenXml.ExcelPackage(new FileInfo(@"C:UserTempTestTestFormat.xlsx")))
{
OfficeOpenXml.ExcelWorksheet worksheet = package.Workbook.Worksheets[1];
var img = worksheet.Drawings.AddPicture("Test", new FileInfo(@"C:UserTempTestTestfile.jpg"));
img.SetPosition(4, 0, 5, 0);
var width = (int)Math.Round(img.Image.Width * 96.0 / img.Image.HorizontalResolution);
var height = (int)Math.Round(img.Image.Height * 96.0 / img.Image.VerticalResolution);
var adjustedWidth = AdjustExcelPageViewImageWidth(width);
img.SetSize(adjustedWidth, height);
package.SaveAs(new FileInfo(@"C:UserTempTestTestFormat2.xlsx"));
}
}
The result naturally has some minor rounding errors, since EPPlus takes size as integers, but the image proportions are not visibly distorted anymore.
链接地址: http://www.djcxy.com/p/72032.html上一篇: CSV与EPPlus解析问题