Winforn中通过NPOI导出Excel时通过XSSFClientAnchor和XSSFPicture添加图片

短命女 2023-10-06 09:59 22阅读 0赞

## 场景 ##

Winform中通过NPOI导出Excel的三种方式(HSSFWorkbook，XSSFWorkbook，SXSSFWorkbook)附代码下载：

[https://blog.csdn.net/BADAO\_LIUMANG\_QIZHI/article/details/106423452][https_blog.csdn.net_BADAO_LIUMANG_QIZHI_article_details_106423452]

在上面介绍了NPOI的三种导出Excel的方式后，如果想在导出的Excel中添加照片，该怎样实现。

注：

博客主页：  
[https://blog.csdn.net/badao\_liumang\_qizhi ][https_blog.csdn.net_badao_liumang_qizhi]  
关注公众号  
霸道的程序猿  
获取编程相关电子书、教程推送与免费下载。

## 实现 ##

通过上面的博客添加了NPOI的引用后，拖拽一个按钮，在按钮的点击事件中

private void button1_Click(object sender, EventArgs e)
            {
                //新建XSSFWorkbook对象
                XSSFWorkbook wb = new XSSFWorkbook();
    
                #region 设置样式
    
                ICellStyle style1 = wb.CreateCellStyle();//样式
                IFont font1 = wb.CreateFont();//字体
                font1.FontName = "宋体";
                font1.FontHeightInPoints = 11;
                font1.Boldweight = (short)FontBoldWeight.Bold;
                style1.SetFont(font1);//样式里的字体设置具体的字体样式
    
    
                #endregion
    
                #region 基础信息页sheet
    
                ISheet sheet0 = wb.CreateSheet("图形");
    
                //获取图像
                System.Drawing.Image image = Properties.Resources.badao;
                Byte[] bytes = ImageToBytes(image);
                int widthPx = image.Width;
                int heightPx = image.Height;
                int pictureIdx = wb.AddPicture(bytes, PictureType.JPEG);
                XSSFDrawing patriarch = (XSSFDrawing)sheet0.CreateDrawingPatriarch();
                // 插图片的位置  HSSFClientAnchor（dx1,dy1,dx2,dy2,col1,row1,col2,row2)
                XSSFClientAnchor anchor = new XSSFClientAnchor(0, 0, 0, 0, 0, 3, 1, 4);
                //把图片插到相应的位置
                XSSFPicture pict = (XSSFPicture)patriarch.CreatePicture(anchor, pictureIdx);
    
                //设置列宽度，根据公式：POI中的列宽 ≈ 像素/8*256
                decimal width = Math.Round((decimal)(heightPx) / 8, 2);
                //将图片缩小为原来的十分之九
                decimal lessWidth = Math.Round(width * 9 / 10, 2);
                sheet0.SetColumnWidth(0, Decimal.ToInt32(lessWidth * 256));
                IRow row3 = sheet0.CreateRow(3);
                //设置行高度，根据公式：POI中的行高 = 像素/DPI*72*20
                decimal poiHeight = Math.Round((decimal)(widthPx) / dpi, 2);
                //将图片缩小为原来的十分之九
                decimal lessPoiHeight = Math.Round(poiHeight * 9 / 10, 2);
                row3.Height = (short)Decimal.ToInt32(lessPoiHeight * 72 * 20);
    
                #endregion
    
                try
                {
                    //将内存中的数据写入磁盘
                    using (FileStream filestream = new FileStream(System.IO.Path.Combine(@"D:\", "badao.xlsx"), FileMode.Create))
                    {
                        wb.Write(filestream);
                        filestream.Close();
                    }
                }
                catch (Exception ex)
                {
                    Console.Write(ex);
                }
    
            }

其中用到了将image转为byte数组的方法

public byte[] ImageToBytes(Image image)
            {
                ImageFormat format = image.RawFormat;
                using (MemoryStream ms = new MemoryStream())
                {
                    image.Save(ms, ImageFormat.Bmp);
                    byte[] buffer = new byte[ms.Length];
                    //Image.Save()会改变MemoryStream的Position，需要重新Seek到Begin
                    ms.Seek(0, SeekOrigin.Begin);
                    ms.Read(buffer, 0, buffer.Length);
                    return buffer;
                }
            }

用到的图片的资源文件在Resouce中添加的

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0JBREFPX0xJVU1BTkdfUUlaSEk_size_16_color_FFFFFF_t_70][]

运行项目，然后点击按钮，就会在D盘下生成Excel

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0JBREFPX0xJVU1BTkdfUUlaSEk_size_16_color_FFFFFF_t_70 1][]

[https_blog.csdn.net_BADAO_LIUMANG_QIZHI_article_details_106423452]: https://blog.csdn.net/BADAO_LIUMANG_QIZHI/article/details/106423452
[https_blog.csdn.net_badao_liumang_qizhi]: https://blog.csdn.net/badao_liumang_qizhi
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0JBREFPX0xJVU1BTkdfUUlaSEk_size_16_color_FFFFFF_t_70]: https://img-blog.csdnimg.cn/20200529134346224.jpg?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0JBREFPX0xJVU1BTkdfUUlaSEk=,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0JBREFPX0xJVU1BTkdfUUlaSEk_size_16_color_FFFFFF_t_70 1]: https://img-blog.csdnimg.cn/20200529134350516.jpg?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0JBREFPX0xJVU1BTkdfUUlaSEk=,size_16,color_FFFFFF,t_70