场景
Winform中通过NPOI导出Excel的三种方式(HSSFWorkbook,XSSFWorkbook,SXSSFWorkbook)附代码下载:
https://blog.csdn.net/BADAO_LIUMANG_QIZHI/article/details/106423452
在上面介绍了NPOI的三种导出Excel的方式后,如果想在导出的Excel中添加照片,该怎样实现。
注:
博客主页:
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中添加的
运行项目,然后点击按钮,就会在D盘下生成Excel