EPPlus导出Excel中插入图片
var fileUrl = "";
FileInfo fileInfo = new FileInfo(fileUrl);
var picture = excelWorksheet.Drawings.AddPicture($"image_{DateTime.Now.Ticks}", fileInfo);
var image = Image.FromStream(new MemoryStream(File.ReadAllBytes(fileUrl)));
var cell = excelWorksheet.Cells[lineNumber, columnNumber];
int cellColumnWidthInPix = GetWidthInPixels(cell);
int cellRowHeightInPix = GetHeightInPixels(cell);
int adjustImageWidthInPix = cellColumnWidthInPix;
int adjustImageHeightInPix = cellRowHeightInPix;
//图片尺寸适应单元格
var adjustImageSize = GetAdjustImageSize(image, cellColumnWidthInPix, cellRowHeightInPix);
adjustImageWidthInPix = adjustImageSize.Item1;
adjustImageHeightInPix = adjustImageSize.Item2;
//设置为居中显示
int columnOffsetPixels = (int)((cellColumnWidthInPix - adjustImageWidthInPix) / 2.0);
int rowOffsetPixels = (int)((cellRowHeightInPix - adjustImageHeightInPix) / 2.0);
picture.SetSize(adjustImageWidthInPix, adjustImageHeightInPix);
picture.SetPosition(lineNumber - 1, rowOffsetPixels, columnNumber - 1, columnOffsetPixels);
/// <summary>
/// 获取单元格的宽度(像素)
/// </summary>
/// <param name="cell"></param>
/// <returns></returns>
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);
}
/// <summary>
/// MeasureString
/// </summary>
/// <param name="s"></param>
/// <param name="font"></param>
/// <returns></returns>
private 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;
}
}
/// <summary>
/// 获取单元格的高度(像素)
/// </summary>
/// <param name="cell"></param>
/// <returns></returns>
private static int GetHeightInPixels(ExcelRange cell)
{
double rowHeight = cell.Worksheet.Row(cell.Start.Row).Height;
using (Graphics graphics = Graphics.FromHwnd(IntPtr.Zero))
{
float dpiY = graphics.DpiY;
return (int) (rowHeight* (1.0 / 90) * dpiY);
}
}
/// <summary>
/// 获取自适应调整后的图片尺寸
/// </summary>
/// <param name="image"></param>
/// <param name="cellColumnWidthInPix"></param>
/// <param name="cellRowHeightInPix"></param>
/// <returns>item1:调整后的图片宽度; item2:调整后的图片高度</returns>
private static Tuple<int, int> GetAdjustImageSize(Image image, int cellColumnWidthInPix, int cellRowHeightInPix)
{
int imageWidthInPix = image.Width;
int imageHeightInPix = image.Height;
//调整图片尺寸,适应单元格
int adjustImageWidthInPix;
int adjustImageHeightInPix;
if (imageHeightInPix* cellColumnWidthInPix > imageWidthInPix* cellRowHeightInPix)
{
//图片高度固定,宽度自适应
adjustImageHeightInPix = cellRowHeightInPix;
double ratio = (1.0) * adjustImageHeightInPix / imageHeightInPix;
adjustImageWidthInPix = (int) (imageWidthInPix* ratio);
}
else
{
//图片宽度固定,高度自适应
adjustImageWidthInPix = cellColumnWidthInPix;
double ratio = (1.0) * adjustImageWidthInPix / imageWidthInPix;
adjustImageHeightInPix = (int) (imageHeightInPix* ratio);
}
return new Tuple<int, int>(adjustImageWidthInPix, adjustImageHeightInPix);
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
上次更新: 2023/05/09, 17:54:37