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

?

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 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 207 208 209 210 211 212 213 214 215 216 217 218 219 220 221 222 223 224 225 226 227 228 229 230 231 232 233 234 235 236

<br>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