java poi导出excel 带图片

版权声明：本文为博主原创文章，转载记得声明出处。 https://blog.csdn.net/qq_40100817/article/details/82799036

首先引入jar包的maven依赖：

                    <!-- poi office poi.version:3.9-->
		<dependency>
			<groupId>org.apache.poi</groupId>
			<artifactId>poi</artifactId>
			<version>${poi.version}</version>
		</dependency>
		<dependency>
			<groupId>org.apache.poi</groupId>
			<artifactId>poi-ooxml</artifactId>
			<version>${poi.version}</version>
		</dependency>
		<dependency>
			<groupId>org.apache.poi</groupId>
			<artifactId>poi-ooxml-schemas</artifactId>
			<version>${poi.version}</version>
		</dependency>

1.前端显示

//"#btnExport"导出按钮的id  #searchForm from表单的id
$("#btnExport").click(function(){
   if(confirm("确认要导出样品数据吗？")==true){ 
			 		                                                                                                                                                                            
      $("#searchForm").submit();
		
}
});

2.导出的编写

package com.thinkgem.jeesite.common.utils.excel;


import java.awt.image.BufferedImage;
import java.io.ByteArrayOutputStream;
import java.io.File;
import java.io.IOException;
import java.lang.reflect.Method;
import java.net.URLDecoder;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

import javax.annotation.PostConstruct;
import javax.imageio.ImageIO;
import javax.servlet.http.HttpServletRequest;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFClientAnchor;
import org.apache.poi.hssf.usermodel.HSSFDataFormat;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFPatriarch;
import org.apache.poi.hssf.usermodel.HSSFRichTextString;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.util.CellRangeAddress;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Component;
import org.springframework.web.context.request.RequestContextHolder;
import org.springframework.web.context.request.ServletRequestAttributes;

import com.thinkgem.jeesite.common.utils.DateUtils;
import com.thinkgem.jeesite.common.utils.FileUtils;
import com.thinkgem.jeesite.common.utils.PropertiesLoader;
import com.thinkgem.jeesite.common.utils.SpringContextHolder;
import com.thinkgem.jeesite.common.utils.StringUtils;
import com.thinkgem.jeesite.modules.kee.specimen.dao.KeeSeriesDetailsDao;
import com.thinkgem.jeesite.modules.kee.specimen.dao.KeeSpecimenDao;
import com.thinkgem.jeesite.modules.kee.specimen.entity.KeeSeries;
import com.thinkgem.jeesite.modules.kee.specimen.entity.KeeSeriesDetails;
import com.thinkgem.jeesite.modules.kee.specimen.entity.KeeSpecimen;
import com.thinkgem.jeesite.modules.kee.specimen.entity.KeeUserSeries;
import com.thinkgem.jeesite.modules.kee.specimen.service.KeeSeriesDetailService;
import com.thinkgem.jeesite.modules.kee.specimen.service.KeeSpecimenService;
import com.thinkgem.jeesite.modules.sys.utils.DictUtils;
public class ExcelUtil { 

	private static Logger logger = LoggerFactory.getLogger(ExcelUtil.class);
	
	/**
     * 导出Excel（样品）
     * @param sheetName sheet名称
     * @param title 标题
     * @param values 内容
     * @param wb HSSFWorkbook对象
     * @return
     */
    public static HSSFWorkbook getHSSFWorkbook(String sheetName,String []title,List<KeeSpecimen> list, HSSFWorkbook wb){
    	HttpServletRequest request = ((ServletRequestAttributes)RequestContextHolder.getRequestAttributes()).getRequest();
        // 第一步，创建一个HSSFWorkbook，对应一个Excel文件
        if(wb == null){
            wb = new HSSFWorkbook();
        }

        // 第二步，在workbook中添加一个sheet,对应Excel文件中的sheet
        HSSFSheet sheet = wb.createSheet(sheetName);

        // 第三步，在sheet中添加表头第0行,注意老版本poi对Excel的行数列数有限制
        HSSFRow row = sheet.createRow(0);
        row.setHeight((short) 650);
        // 第四步，创建单元格，并设置值表头 设置表头居中
        HSSFCellStyle style = wb.createCellStyle();
        style.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 创建一个居中格式
        style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
        //声明列对象
        HSSFCell cell = null;
        
        //创建标题
        for(int i=0;i<title.length;i++){
        	sheet.setColumnWidth(i, 6000);
            cell = row.createCell(i);
            cell.setCellValue(title[i]);
            HSSFFont font = wb.createFont();
            font.setFontName("黑体");
            font.setFontHeightInPoints((short) 15);//设置字体大小
            style.setFont(font);
            cell.setCellStyle(style);
        }
        BufferedImage bufferImg = null;//图片一
        BufferedImage bufferImg1 = null;//图片二
        try {
        	 //创建内容
        	HSSFCellStyle styleCon = wb.createCellStyle();
        	styleCon.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 创建一个居中格式
        	styleCon.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
            for(int i=0;i<list.size();i++){
                row = sheet.createRow(i + 1);
                row.setHeight((short) 550);
                KeeSpecimen keeSpecimen = list.get(i);
                //将内容按顺序赋给对应的列对象
                ByteArrayOutputStream byteArrayOut = new ByteArrayOutputStream();
                ByteArrayOutputStream byteArrayOut1 = new ByteArrayOutputStream();
                HSSFPatriarch patriarch = sheet.createDrawingPatriarch();
                //将两张图片读到BufferedImage
                String qrcode = "/static/images/qrcode/"+keeSpecimen.getRes1();
                qrcode = request.getSession().getServletContext().getRealPath(qrcode);
                if (new File(qrcode).exists()) {
                	bufferImg = ImageIO.read(new File(qrcode));
                	ImageIO.write(bufferImg, "jpg", byteArrayOut);
                	//图片一导出到单元格B2中
                    HSSFClientAnchor anchor = new HSSFClientAnchor(480, 30, 700, 250,
                            (short) 1, i+1, (short) 1, i+1);
                    // 插入图片,注意这个问题PICTURE_TYPE_JPEG。如果导出的图片没有显示出来，改动这里
                    patriarch.createPicture(anchor, wb.addPicture(byteArrayOut
                            .toByteArray(), HSSFWorkbook.PICTURE_TYPE_JPEG));
				}
                //获取图片路径并且处理
                String baseImage = keeSpecimen.getBaseImage();
                baseImage = baseImage.replaceFirst("/kee", "");
                baseImage = request.getSession().getServletContext().getRealPath(baseImage);
                //判断图片是否存在
                if (new File(baseImage).exists()) {
                	bufferImg1 = ImageIO.read(new File(baseImage));
                	ImageIO.write(bufferImg1, FileUtils.getFileExtension(keeSpecimen.getBaseImage()), byteArrayOut1);
                	//图片一导出到单元格B6中
                    HSSFClientAnchor anchor1 = new HSSFClientAnchor(400, 30, 700, 220,
                            (short) 5, i+1, (short) 5, i+1);
                    patriarch.createPicture(anchor1, wb.addPicture(byteArrayOut1
                            .toByteArray(), HSSFWorkbook.PICTURE_TYPE_JPEG));
                }
                cell = row.createCell(0);cell.setCellValue(keeSpecimen.getCode());
                cell.setCellStyle(styleCon);
            //DictUtils是我的字典工具类，根据业务场景需要，有的朋友纠结，特此声明
                cell = row.createCell(2);cell.setCellValue(DictUtils.getDictLabel(keeSpecimen.getType(), "specimen_type", ""));
                cell.setCellStyle(styleCon);
                cell = row.createCell(3);cell.setCellValue(keeSpecimen.getChName());
                cell.setCellStyle(styleCon);
                cell = row.createCell(4);cell.setCellValue(keeSpecimen.getEnName());
                cell.setCellStyle(styleCon);
                cell = row.createCell(6);cell.setCellValue(keeSpecimen.getLookNum());
                cell.setCellStyle(styleCon);
                cell = row.createCell(7);cell.setCellValue(keeSpecimen.getOrdersNum());
                cell.setCellStyle(styleCon);
            }
            return wb;
		} catch (Exception e) {
			// TODO: handle exception
			System.err.println(e.getMessage());
		}
       return wb;
    }
    
    /**
     * 导出Excel（主推）
     * @param sheetName sheet名称
     * @param title 标题
     * @param values 内容
     * @param wb HSSFWorkbook对象
     * @return
     */
    public static HSSFWorkbook getSeriesHSSFWorkbook(String []title,String []titleSpecimen,List<KeeSeries> list,Map<String, List<KeeSpecimen>> map, HSSFWorkbook wb){
    	HttpServletRequest request = ((ServletRequestAttributes)RequestContextHolder.getRequestAttributes()).getRequest();
        // 第一步，创建一个HSSFWorkbook，对应一个Excel文件
        if(wb == null){
            wb = new HSSFWorkbook();
        }
        //样式
    	HSSFCellStyle style = wb.createCellStyle();
    	style.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 创建一个居中格式
        style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
        
        HSSFCellStyle styleCon = wb.createCellStyle();
        styleCon.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 创建一个居中格式
        styleCon.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
        //字体
        HSSFFont font = wb.createFont();
        font.setFontName("黑体");
        font.setFontHeightInPoints((short) 17);//设置字体大小
        style.setFont(font);
        // 第二步，在workbook中添加一个sheet,对应Excel文件中的sheet（根据主推来创建）
        for (KeeSeries keeSeries : list) {
        	HSSFSheet sheet = wb.createSheet(keeSeries.getChName());
        	HSSFCell cell = null;
        	//设置表头的列宽
        	for (int i = 0; i < title.length; i++) {
        		sheet.setColumnWidth(i, 6000);
			}
        	// 第三步，在sheet中添加表头第0行,注意老版本poi对Excel的行数列数有限制
        	//设置主推表头
            HSSFRow row = sheet.createRow(0);
            row.setHeight((short) 1000);
            cell=row.createCell(0);
            cell.setCellValue("主推系列");
            cell.setCellStyle(style);
            //合并第一行，6列
            CellRangeAddress region=new CellRangeAddress(0, 0, 0,6);
            sheet.addMergedRegion(region);
            //设置样品表头
            HSSFRow rowThree = sheet.createRow(3);
            rowThree.setHeight((short) 1000);
            cell=rowThree.createCell(0);
            cell.setCellValue("系列样品");
            cell.setCellStyle(style);
            CellRangeAddress regionT=new CellRangeAddress(3, 3, 0,6);
            sheet.addMergedRegion(regionT);
            
            //创建主推标题
            HSSFRow rowOne = sheet.createRow(1);
            HSSFRow rowFour = sheet.createRow(4);
            HSSFCell cellFour = null;
            for(int i=0;i<title.length;i++){
            	rowOne.setHeight((short) 650);
            	rowFour.setHeight((short) 550);
                cell = rowOne.createCell(i);
                cell.setCellValue(title[i]);
                cellFour = rowFour.createCell(i);
                cellFour.setCellValue(titleSpecimen[i]);
                font.setFontHeightInPoints((short) 14);//设置字体大小
                style.setFont(font);
                cell.setCellStyle(style);
                cellFour.setCellStyle(style);
            }
            
            BufferedImage bufferImg = null;//图片一
            BufferedImage bufferImg1 = null;//图片二
            try {
            	 //主推系列数据
                HSSFRow rowTwo = sheet.createRow(2);
                rowTwo.setHeight((short) 550);
                ByteArrayOutputStream byteArrayOut = new ByteArrayOutputStream();
                ByteArrayOutputStream byteArrayOut1 = new ByteArrayOutputStream();
                HSSFPatriarch patriarch = sheet.createDrawingPatriarch();
                //将两张图片读到BufferedImage
                String chImage = keeSeries.getChImage();
                chImage = chImage.replaceFirst("/kee", "");
                chImage = request.getSession().getServletContext().getRealPath(chImage);
                if (new File(chImage).exists()) {
                	bufferImg = ImageIO.read(new File(chImage));
                	ImageIO.write(bufferImg, FileUtils.getFileExtension(keeSeries.getChImage()), byteArrayOut);
                	//图片一导出到单元格B2中
                    HSSFClientAnchor anchor = new HSSFClientAnchor(480, 30, 700, 250,
                            (short) 2, 2, (short) 2, 2);
                    // 插入图片
                    patriarch.createPicture(anchor, wb.addPicture(byteArrayOut
                            .toByteArray(), HSSFWorkbook.PICTURE_TYPE_JPEG));
    			}
                
                String enImage = keeSeries.getEnImage();
                enImage = enImage.replaceFirst("/kee", "");
                enImage = request.getSession().getServletContext().getRealPath(enImage);
                if (new File(enImage).exists()) {
                	bufferImg1 = ImageIO.read(new File(enImage));
                	ImageIO.write(bufferImg1, FileUtils.getFileExtension(keeSeries.getEnImage()), byteArrayOut1);
                	//图片一导出到单元格B6中
                    HSSFClientAnchor anchor1 = new HSSFClientAnchor(400, 30, 700, 220,
                            (short) 3, 2, (short) 3, 2);
                    patriarch.createPicture(anchor1, wb.addPicture(byteArrayOut1
                            .toByteArray(), HSSFWorkbook.PICTURE_TYPE_JPEG));
                }
                cell=rowTwo.createCell(0);cell.setCellValue(keeSeries.getChName());cell.setCellStyle(styleCon);
                cell=rowTwo.createCell(1);cell.setCellValue(keeSeries.getEnName());cell.setCellStyle(styleCon);
                cell=rowTwo.createCell(4);cell.setCellValue(keeSeries.getLookNum());cell.setCellStyle(styleCon);
                cell=rowTwo.createCell(5);cell.setCellValue(keeSeries.getOrdersNum());cell.setCellStyle(styleCon);
                cell=rowTwo.createCell(6);cell.setCellStyle(styleCon);cell.setCellValue(DictUtils.getDictLabel(keeSeries.getRes1(), "is_show", ""));cell.setCellStyle(styleCon);
                
                //查询主推下边的样品
                List<KeeSpecimen> keeSpecimenList = map.get(keeSeries.getId());
    			if (!keeSpecimenList.isEmpty()) {
    				 for(int i=0;i<keeSpecimenList.size();i++){
    		                row = sheet.createRow(i + 5);
    		                row.setHeight((short) 550);
    		                KeeSpecimen keeSpecimen = keeSpecimenList.get(i);
    		                //将两张图片读到BufferedImage
    		                String qrcode = "/static/images/qrcode/"+keeSpecimen.getRes1();
    		                qrcode = request.getSession().getServletContext().getRealPath(qrcode);
    		                if (new File(qrcode).exists()) {
    		                	bufferImg = ImageIO.read(new File(qrcode));
    		                	ImageIO.write(bufferImg, "jpg", byteArrayOut);
    		                	//图片一导出到单元格B2中
    		                    HSSFClientAnchor anchor = new HSSFClientAnchor(480, 30, 700, 250,
    		                            (short) 1, i+5, (short) 1, i+5);
    		                    // 插入图片
    		                    patriarch.createPicture(anchor, wb.addPicture(byteArrayOut
    		                            .toByteArray(), HSSFWorkbook.PICTURE_TYPE_JPEG));
    						}
    		                
    		                String baseImage = keeSpecimen.getBaseImage();
    		                baseImage = baseImage.replaceFirst("/kee", "");
    		                baseImage = request.getSession().getServletContext().getRealPath(baseImage);
    		                if (new File(baseImage).exists()) {
    		                	bufferImg1 = ImageIO.read(new File(baseImage));
//FileUtils.getFileExtension获取图片的后缀名，是jpg还是png
    		                	ImageIO.write(bufferImg1, FileUtils.getFileExtension(keeSpecimen.getBaseImage()), byteArrayOut1);
    		                	//图片一导出到单元格B6中
    		                    HSSFClientAnchor anchor1 = new HSSFClientAnchor(400, 30, 700, 220,
    		                            (short) 5, i+5, (short) 5, i+5);
    		                    patriarch.createPicture(anchor1, wb.addPicture(byteArrayOut1
    		                            .toByteArray(), HSSFWorkbook.PICTURE_TYPE_JPEG));
    		                }
    		                cell=row.createCell(0);cell.setCellValue(keeSpecimen.getCode());cell.setCellStyle(styleCon);
    		                cell=row.createCell(2);cell.setCellValue(DictUtils.getDictLabel(keeSpecimen.getType(), "specimen_type", ""));cell.setCellStyle(styleCon);
    		                cell=row.createCell(3);cell.setCellValue(keeSpecimen.getChName());cell.setCellStyle(styleCon);
    		                cell=row.createCell(4);cell.setCellValue(keeSpecimen.getEnName());cell.setCellStyle(styleCon);
    		                cell=row.createCell(6);cell.setCellValue(keeSpecimen.getOrdersNum()+" / "+keeSpecimen.getLookNum());cell.setCellStyle(styleCon);
    		            }
    			}
	    			
			} catch (Exception e) {
				System.err.println(e.getMessage());
			}
            
		}
        
       return wb;
    }
}

3.控制层调用

	//样品导出
	@RequiresPermissions("specimen:keeSpecimen:view")
    @RequestMapping(value = "export", method=RequestMethod.POST)
    public void exportFile(KeeSpecimen keeSpecimen, HttpServletRequest request, HttpServletResponse response, RedirectAttributes redirectAttributes) {
			List<KeeSpecimen> keeSpecimenList= keeSpecimenService.findList(keeSpecimen);
			// 生成Excel
			//excel标题
			 String[] title={"编码","二维码","分类归属","中文名称","英文名称","主图","浏览数","下单数"};
			 //excel名称
			 String fileName = "样品数据.xls";
			//sheet名 
			 String sheetName = "样品信息";
			 
			HSSFWorkbook wb = ExcelUtil.getHSSFWorkbook(sheetName, title, keeSpecimenList,null);
			//响应到客户端
			try {
				this.setResponseHeader(response, fileName);
				OutputStream os = response.getOutputStream();
				wb.write(os);
				os.flush();os.close();
			} catch (Exception e) {
				e.printStackTrace();
				System.err.println(e.getMessage());
			}
    }
	
//主推导出
	@RequiresPermissions("specimen:keeSeries:view")
    @RequestMapping(value = "export", method=RequestMethod.POST)
    public void exportFile(KeeSeries keeSeries, HttpServletRequest request, HttpServletResponse response, RedirectAttributes redirectAttributes) {
			//获取所有的主推
			List<KeeSeries> keeSeriesList= keeSeriesService.findList(keeSeries);
			Map<String, List<KeeSpecimen>> map = new HashMap(); 
			//查询主推系列下面的样品
			for (KeeSeries keeSeries2 : keeSeriesList) {
				List<KeeSpecimen> keeSpecimenList = new ArrayList<KeeSpecimen>();
				KeeSeriesDetails keeSeriesDetails = new KeeSeriesDetails();
				keeSeriesDetails.setSeriesid(keeSeries2.getId());
				List<KeeSeriesDetails> keeSeriesDetailsList = keeSeriesDetailsDao.findList(keeSeriesDetails);
				if (!keeSeriesDetailsList.isEmpty()) {
					for (KeeSeriesDetails keeSeriesDetails2 : keeSeriesDetailsList) {
						keeSpecimenList.add(keeSpecimenService.get(keeSeriesDetails2.getSpecimenid()));
					}
				}
				map.put(keeSeries2.getId(), keeSpecimenList);
			}
			
			// 生成Excel
			//excel标题
			 String[] title={"中文名称","英文名称","中文图片","英文图片","浏览量","下单量","发布状态"};
			 String[] titleSpecimen={"编码","二维码","分类归属","中文名称","英文名称","主图","下单数/浏览数"};
			 //excel名称
			 String fileName = "主推系列数据.xls";
			 
			HSSFWorkbook wb = ExcelUtil.getSeriesHSSFWorkbook( title, titleSpecimen, keeSeriesList,map,null);
			//响应到客户端
			try {
				this.setResponseHeader(response, fileName);
				OutputStream os = response.getOutputStream();
				wb.write(os);
				os.flush();os.close();
			} catch (Exception e) {
				e.printStackTrace();
				System.err.println(e.getMessage());
			}
    }

	 //发送响应流方法
    public void setResponseHeader(HttpServletResponse response, String fileName) {
        try {
            try {
                fileName = new String(fileName.getBytes(),"ISO8859-1");
            } catch (UnsupportedEncodingException e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
            }
            response.setContentType("application/octet-stream;charset=ISO8859-1");
            response.setHeader("Content-Disposition", "attachment;filename="+ fileName);
            response.addHeader("Pargam", "no-cache");
            response.addHeader("Cache-Control", "no-cache");
        } catch (Exception ex) {
            ex.printStackTrace();
        }
    }

4.导出效果：