天天看點

c# epplus 使用插入圖檔,連結

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.IO;
using System.Linq;
using System.Net;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using OfficeOpenXml;
using OfficeOpenXml.Drawing;
using OfficeOpenXml.Style;
using OfficeOpenXml.Style.XmlAccess;

namespace Epplus
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }

        private void button1_Click(object sender, EventArgs e)
        {
            ExcelPackage excelPkg= new ExcelPackage();
            ExcelWorksheet wsSheet1= excelPkg.Workbook.Worksheets.Add("Sheet1");
            ExcelWorksheet wsSheet2= excelPkg.Workbook.Worksheets.Add("Sheet2");

            using (ExcelRange rng= wsSheet1.Cells[2,2])
            {
                rng.Value = "welcome to coding";
                rng.Style.Font.Size = 16;
                rng.Style.Font.Bold = true;
                rng.Style.Font.Italic = true;
            }

            using (ExcelRange rng= wsSheet1.Cells[4,2,7,4])
            {
                rng.Value = "字型 和 背景";
                rng.Merge = true;
                rng.Style.Font.Color.SetColor(Color.Gold);

                rng.Style.Fill.PatternType = ExcelFillStyle.Solid;
                rng.Style.Fill.BackgroundColor.SetColor(Color.Gray);
            }

            using (ExcelRange rng= wsSheet1.Cells[4,6,7,9])
            {
                Color deepBlueHexCode = ColorTranslator.FromHtml("#254061");

                rng.Value = "邊框樣式";
                rng.Merge = true;
                rng.Style.Font.Color.SetColor(Color.Gold);

                rng.Style.Border.Top.Style = ExcelBorderStyle.Thin;
                rng.Style.Border.Top.Color.SetColor(Color.Chocolate);
                rng.Style.Border.Left.Style = ExcelBorderStyle.Medium;
                rng.Style.Border.Left.Color.SetColor(Color.Gold);
                rng.Style.Border.Right.Style = ExcelBorderStyle.Dashed;
                rng.Style.Border.Right.Color.SetColor(Color.Indigo);
                rng.Style.Border.Bottom.Style = ExcelBorderStyle.DashDot;
                rng.Style.Border.Bottom.Color.SetColor(deepBlueHexCode);
            }

            wsSheet1.Row(9).Height = 30;
            wsSheet1.Cells[wsSheet1.Dimension.Address].AutoFitColumns();
            using (ExcelRange rng= wsSheet1.Cells[9,2])
            {
                rng.Value = "字型水準居中和垂直居中";
                rng.Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
                rng.Style.VerticalAlignment = ExcelVerticalAlignment.Center;
            }


            /********************插入圖檔**********************/
            int RowIndex = 10;
            int colIndex = 1;
            int pixelTop = 88;
            int pixelLeft = 129;
            int width = 40;
            int height = 40;

            var img_url = "https://q4.qlogo.cn/g?b=qq&nk=517964095&s=140";
            Image img = Image.FromStream(WebRequest.Create(img_url).GetResponse().GetResponseStream());
            img = cutEllipse(img, new Rectangle(0, 0, img.Width, img.Height), new Size(60, 60));
            ExcelPicture pic= wsSheet1.Drawings.AddPicture("圖檔名字", img);
            pic.SetPosition(RowIndex,0,colIndex, 0);
            //pic.SetPosition(pixelTop, pixelLeft);
            //pic.SetSize(width, height);
            pic.SetSize(65); //隻設定一個值寬度等于高度


            /********************跳轉連結**********************/

            string styleName = "超連結";
            ExcelNamedStyleXml hyperStyle = wsSheet1.Workbook.Styles.CreateNamedStyle(styleName);
            hyperStyle.Style.Font.UnderLine = true;
            hyperStyle.Style.Font.Size = 12;
            hyperStyle.Style.Font.Color.SetColor(Color.Gray);

            using (ExcelRange rng = wsSheet1.Cells[11, 4])
            {
                rng.Hyperlink = new Uri("http://www.baidu.com", UriKind.Absolute);
                rng.Value = "跳轉到百度頁面";
                rng.StyleName = styleName;
            }

            using (ExcelRange rng = wsSheet1.Cells[11, 6])
            {
                rng.Hyperlink = new Uri("#'Sheet1'!B9", UriKind.Relative);
                rng.Value = "跳轉到B9欄";
                rng.StyleName = styleName;
            }
            using (ExcelRange rng = wsSheet1.Cells[11, 9])
            {
                rng.Hyperlink = new Uri("#'Sheet2'!B2", UriKind.Relative);
                rng.Value = "跳轉到Sheet2 B9欄";
                rng.StyleName = styleName;
            }


            wsSheet1.Protection.IsProtected = false;
            wsSheet1.Protection.AllowSelectLockedCells = false;
            excelPkg.SaveAs(new FileInfo(@"E:\test.xlsx"));
        }

        //将圖檔裁剪成圓形
        public static Image cutEllipse(Image img, Rectangle rec, Size size)
        {
            Bitmap bitmap = new Bitmap(size.Width, size.Height);
            using (Graphics g = Graphics.FromImage(bitmap))
            {
                using (TextureBrush br = new TextureBrush(img, System.Drawing.Drawing2D.WrapMode.Clamp, rec))
                {
                    br.ScaleTransform(bitmap.Width / (float)rec.Width, bitmap.Height / (float)rec.Height);
                    g.SmoothingMode = System.Drawing.Drawing2D.SmoothingMode.HighQuality;
                    g.FillEllipse(br, new Rectangle(Point.Empty, size));
                }
            }
            return bitmap;
        }
    }
}