NPOI在EXCEL中插入图片和超链接

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.IO;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using MySql.Data;
using MySql.Data.MySqlClient;
using System.Configuration;
using System.Net;
using NPOI.SS.Formula.Functions;
using System.Runtime.Remoting.Messaging;
using System.Runtime.CompilerServices;

namespace NPOI
{
    public partial class Form1 : Form
    {
        public static string connectionString = "charset='utf8';pooling=true;Data Source='" + ConfigurationManager.AppSettings["Conn_server"] + "';"
                                                            + "Database='" + ConfigurationManager.AppSettings["Conn_database"] + "';"
                                                            + "User Id='" + ConfigurationManager.AppSettings["Conn_uid"] + "';"
                                                            + "Password='" + ConfigurationManager.AppSettings["Conn_pwd"] + "';";

        public Form1()
        {
            InitializeComponent();
        }

        public static DataTable fsQuery(string strSQL)
        {
            return GetDataSet(CommandType.Text, strSQL, null).Tables[0];
        }
        private static void PrepareCommand(MySqlCommand cmd, MySqlConnection conn, MySqlTransaction trans, CommandType cmdType, string cmdText, MySqlParameter[] cmdParms)
        {

            if (conn.State != ConnectionState.Open)

                conn.Open();

            cmd.Connection = conn;

            cmd.CommandText = cmdText;

            if (trans != null)

                cmd.Transaction = trans;

            cmd.CommandType = cmdType;

            if (cmdParms != null)
            {

                foreach (MySqlParameter parm in cmdParms)

                    cmd.Parameters.Add(parm);

            }
        }


        public static DataSet GetDataSet(CommandType cmdType, string cmdText, params MySqlParameter[] commandParameters)

        {

            //创建一个MySqlCommand对象          

            MySqlCommand cmd = new MySqlCommand();

            //创建一个MySqlConnection对象          

            MySqlConnection conn = new MySqlConnection(connectionString);

            try
            {

                //调用 PrepareCommand 方法，对 MySqlCommand 对象设置参数                

                PrepareCommand(cmd, conn, null, cmdType, cmdText, commandParameters);

                //调用 MySqlCommand  的 ExecuteReader 方法              

                MySqlDataAdapter adapter = new MySqlDataAdapter();

                adapter.SelectCommand = cmd;

                DataSet ds = new DataSet();

                adapter.Fill(ds);

                //清除参数              

                cmd.Parameters.Clear();

                conn.Close();

                return ds;

            }

            catch (Exception e) { throw e; }
        }

        public bool Add()
        {
            

            return true;

        }


        private void button1_Click(object sender, EventArgs e)
        {

            DateTime dtStart;
            DateTime dtEnd;



            dtStart = DateTime.Now;
            //创建一个工作簿
            HSSFWorkbook workbook = new HSSFWorkbook();
            //创建一个sheet
            ISheet sheet1 = workbook.CreateSheet("sheet1");
            // 设置列宽,excel列宽每个像素是1/256
            sheet1.SetColumnWidth(0, 18 * 256);
            sheet1.SetColumnWidth(1, 18 * 256);
            sheet1.SetColumnWidth(2, 18 * 256);
            sheet1.SetColumnWidth(3, 18 * 256);
            sheet1.SetColumnWidth(4, 18 * 256);
            IRow rowHeader = sheet1.CreateRow(0);//创建表头行
            rowHeader.CreateCell(0, CellType.String).SetCellValue("id");
            rowHeader.CreateCell(1, CellType.String).SetCellValue("站");
            rowHeader.CreateCell(2, CellType.String).SetCellValue("点位");
            rowHeader.CreateCell(3, CellType.String).SetCellValue("时间");
            rowHeader.CreateCell(4, CellType.String).SetCellValue("图片");
            DataTable dt = fsQuery("SELECT * from USER  limit 50");
            if (dt.Rows.Count > 0)
            {
                int rowline = 1;//从第二行开始(索引从0开始)
                foreach (DataRow datarow in dt.Rows)
                {
                    IRow row = sheet1.CreateRow(rowline);
                    //设置行高 ,excel行高度每个像素点是1/20
                    row.Height = 80 * 20;
                    //填入生产单号
                    row.CreateCell(0, CellType.String).SetCellValue(datarow["id"].ToString());
                    row.CreateCell(1, CellType.String).SetCellValue(datarow["USER_NAME"].ToString());
                    row.CreateCell(2, CellType.String).SetCellValue(datarow["USER_CODE"].ToString());
                    row.CreateCell(3, CellType.String).SetCellValue(datarow["PHONE"].ToString());

                    ICell cell = row.CreateCell(8);
                    cell.SetCellValue("链接");
                    HSSFHyperlink link = new HSSFHyperlink(HyperlinkType.Url);
                    link.Address = "http://vfx.mtime.cn/Video/2019/03/12/mp4/190312143927981075.mp4";
                    cell.Hyperlink = link;


                    byte[] bytes = GetFileData_Remote("http://192.168.10.2:8202/filelib/2022-03/05/d3215a0afdd44d9ab94313f0379e2ebf.jpg");

                    //byte[] bytes = System.IO.File.ReadAllBytes(datarow["picture"].ToString());
                    int pictureIdx = workbook.AddPicture(bytes, PictureType.JPEG);

                    HSSFPatriarch patriarch = (HSSFPatriarch)sheet1.CreateDrawingPatriarch();
                    // 插图片的位置  HSSFClientAnchor（dx1,dy1,dx2,dy2,col1,row1,col2,row2) 后面再作解释
                    HSSFClientAnchor anchor = new HSSFClientAnchor(70, 10, 0, 0, 4, rowline, 5, rowline + 1);
                    //把图片插到相应的位置
                    HSSFPicture pict = (HSSFPicture)patriarch.CreatePicture(anchor, pictureIdx);

                    
                    rowline++;
                }
            }
            //把文件保存到d:\aaa.xls，注意扩展名是.xls不要写成.xlsx
            using (Stream stream = File.OpenWrite(Application.StartupPath + "//报表.xls"))
            {
                workbook.Write(stream);
            }
            //如果要操作.xlsx的excel，引入命名空间  using NPOI.XSSF.UserModel;
            // 然后把所有的HSS改为XSS(比喻HSSWorkbook->XSSWorkbook)
            dtEnd = DateTime.Now;
            this.Text = "总共用时"+(dtEnd - dtStart).TotalSeconds.ToString("0.00")+"秒";
        }

        protected byte[] GetFileData_local(string fileUrl)
        {
            FileStream fs = new FileStream(fileUrl, FileMode.Open, FileAccess.Read);
            try
            {
                byte[] buffur = new byte[fs.Length];
                fs.Read(buffur, 0, (int)fs.Length);

                return buffur;
            }
            catch (Exception ex)
            {
                //MessageBoxHelper.ShowPrompt(ex.Message);
                return null;
            }
            finally
            {
                if (fs != null)
                {

                    //关闭资源
                    fs.Close();
                }
            }
        }

        private static byte[] GetFileData_Remote(string serverUrl)
        {
            string url = serverUrl;
            WebRequest request = WebRequest.Create(url);
            WebResponse response = request.GetResponse();
            Stream stream = response.GetResponseStream();
            byte[] byteFile = null;
            List<byte> bytes = new List<byte>();
            int temp = stream.ReadByte();
            while (temp != -1)
            {
                bytes.Add((byte)temp);
                temp = stream.ReadByte();
            }
            byteFile = bytes.ToArray();
            string str = Convert.ToBase64String(byteFile);
            response.Close();
            return byteFile;
        }
    }
}

　　生成按钮就是button1，另外包含了访问mysql的基本方法，再附赠两个转换文件为字节数组的方法，一个是转换本地路径，如d:\aaa\x1.jpg 另一个是转换远程路径，如http：//x/1.jpg