JAVA POI自动导出数据和图片到excel

1.需求描述
由于程序导出excel，还有图片，对内存和cpu占用率很高，如果很多人同时点击导出按钮，服务器压力会很大，所以用定时程序让它每天晚上跑一次，然后获取生成路径，点击超链接就可以了，下载速度很快，有人推荐我使用线程，查了一下很繁琐，自己又不熟练，一把伤心泪。。。
2.效果截图

3.创建excel，代码都有注释，不过多解释

package com.demo.task;

import java.io.BufferedInputStream;
import java.io.ByteArrayInputStream;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.sql.SQLException;
import java.util.Date;
import java.util.List;
import java.util.Map;

import javax.servlet.ServletException;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.CellRangeAddress;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFCellStyle;
import org.apache.poi.xssf.usermodel.XSSFClientAnchor;
import org.apache.poi.xssf.usermodel.XSSFDrawing;
import org.apache.poi.xssf.usermodel.XSSFFont;
import org.apache.poi.xssf.usermodel.XSSFPicture;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.junit.Test;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.ui.ModelMap;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.servlet.support.RequestContext;
import common.image.util.ImageHepler;
import common.util.DateUtil;
import jxl.write.WriteException;
import oracle.sql.BLOB;

public class TrainerImageJob {
	@Autowired
	private TrainerService trainerService;
	private Logger logger = LoggerFactory.getLogger(this.getClass().getName());
	/**
	 * 导出图片
	 * @param model
	 * @param query
	 * @param request
	 * @param workbook
	 * @param response
	 * @throws NoSuchFieldException
	 * @throws SecurityException
	 * @throws IllegalStateException
	 * @throws ServletException
	 * @throws IOException
	 * @throws WriteException
	 * @throws SQLException
	 */
	public void run(){
		try {
			TrainerQuery query=new TrainerQuery();
			query.setTrainerLevelPh("3");
			query.setFullName("Trainer_T3");
			exportEmployeeImage(query);
		} catch (NoSuchFieldException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (SecurityException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (IllegalStateException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (WriteException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (ServletException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (IOException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
	private void exportEmployeeImage(TrainerQuery  query)
			throws NoSuchFieldException, SecurityException,
			IllegalStateException, ServletException, IOException,
			WriteException, SQLException {
			long startTime=System.currentTimeMillis();   //获取开始时间  
			XSSFWorkbook input_work = new XSSFWorkbook();
			XSSFSheet inpub_sheet=input_work.createSheet("Trainer");
			// 设置样式
			XSSFCellStyle textStyle10 = input_work.createCellStyle();
			textStyle10.setAlignment(XSSFCellStyle.ALIGN_CENTER); // 居中 
			// 设置垂直居中
			textStyle10.setAlignment(HorizontalAlignment.CENTER);
			textStyle10.setVerticalAlignment((short) 1);
			// 设置细边框
			textStyle10.setWrapText(true);
			XSSFFont fontText2 = input_work.createFont();
			// 字体号码
			fontText2.setFontHeightInPoints((short) 11);
			// 字体名称
			fontText2.setFontName("微软雅黑");
			textStyle10.setFont(fontText2);
			// 设置样式
			XSSFCellStyle textStyle = input_work.createCellStyle();
			textStyle.setAlignment(XSSFCellStyle.ALIGN_CENTER);//左右居中      
			textStyle10.setAlignment(HorizontalAlignment.CENTER);//上下居中  
			// 设置细边框
			textStyle.setWrapText(true);
			XSSFFont fontText = input_work.createFont();
			// 字体名称
			fontText.setFontName("微软黑体");
			// 字体号码
			fontText.setFontHeightInPoints((short) 18);
			fontText.setBoldweight(XSSFFont.BOLDWEIGHT_BOLD);//加粗     
			textStyle.setFont(fontText);
			query.setNeedQueryAll(false);
			query.setTotalItem(query.getTotal());
			List<Map> picture = trainerService.getLecturersPhotos(query); //查询数据库获取图片，可根据实际需要修改
			XSSFRow input_row3 = null;
			XSSFRow input_row4 = null;
			input_row3 = inpub_sheet.createRow(0);
			input_row3.setHeight((short) (21*20)); //使用Apache POI精确设置Excel的行高和列宽  参考  http://blog.youkuaiyun.com/feg545/article/details/11983429
			input_row4 = inpub_sheet.createRow(1);
			input_row4.setHeight((short) (21*20)); 
			//参数说明：1：开始行 2：结束行  3：开始列 4：结束列  
			//比如我要合并 第二行到第四行的    第六列到第八列     sheet.addMergedRegion(new CellRangeAddress(1,3,5,7));
			inpub_sheet.addMergedRegion(new CellRangeAddress(0,1,0,13));  
			input_row3.createCell(0).setCellStyle(textStyle10);  //设置单元格格式
			input_row3.createCell(0).setCellValue("中国度银牌教员");  //往单元格中填充内容
			int rowNum=11;
			int colNum=0;
			int colNum1=1;
			int row1=2;
			int row2=10;
			for (int i = 0; i < picture.size(); i++) {
				Map map = (Map) picture.get(i);
				String trainCode = map.get("TRAINERCODE").toString();  //读取数据
				String name = map.get("NAME").toString();
				String orgEnName=null;
				if(map.containsKey("ORGENNAME")){
					orgEnName=map.get("ORGENNAME").toString();
				}
				String positionCode=null;
				if (map.containsKey("POSITIONCODE")) {
					 positionCode= map.get("POSITIONCODE").toString();
				}
				//参数说明：1：开始行 2：结束行  3：开始列 4：结束列  
				//比如我要合并 第二行到第四行的    第六列到第八列     sheet.addMergedRegion(new CellRangeAddress(1,3,5,7));  
				inpub_sheet.addMergedRegion(new CellRangeAddress(rowNum,rowNum,colNum,colNum1));  
				XSSFRow input_row = inpub_sheet.getRow(rowNum);
				if (input_row == null) {
					input_row = inpub_sheet.createRow(rowNum);
				}
				XSSFCell input_cell=input_row.createCell(colNum);  //创建列
				input_cell.setCellStyle(textStyle10);  //设置单元格格式
				input_cell.setCellValue(trainCode+"-"+name);  //往单元格中填充内容
				int rowNum2=rowNum+1;
				inpub_sheet.addMergedRegion(new CellRangeAddress(rowNum2,rowNum2,colNum,colNum1)); 
				XSSFRow input_row1 = inpub_sheet.getRow(rowNum2);
				if (input_row1 == null) {
					input_row1 = inpub_sheet.createRow(rowNum2);
				}
				XSSFCell input_cell1=input_row1.createCell(colNum);  //创建列
				input_cell1.setCellStyle(textStyle10);  //设置单元格格式
				input_cell1.setCellValue(orgEnName+"-"+positionCode);  //往单元格中填充内容
				BLOB blob = (BLOB) map.get("AVATAR");
				inpub_sheet.addMergedRegion(new CellRangeAddress(row1,row2,colNum,colNum1)); 
				if (blob != null && blob.length() != 0) {
					BufferedInputStream in =null;
					InputStream inStream = blob.getBinaryStream();  //得到流对象
					long nLen = blob.length();
					int nSize = (int) nLen;  
					byte[] data = new byte[nSize];
					inStream.read(data);
					data=ImageHepler.ChangeImgSize(data,130,176);  //将读取数据流转换成图片，并设置大小
					//ChangeImgSize 参考我之前的文章http://www.cnblogs.com/wangjianguang/p/7852060.html
					inStream = new ByteArrayInputStream(data);
					try {
						in = new BufferedInputStream(inStream,1024);
					} catch (Exception e1) {
						e1.printStackTrace();
					}
					int pictureIdx =input_work.addPicture(in, input_work.PICTURE_TYPE_JPEG);//向excel中插入图片
					XSSFDrawing drawing = inpub_sheet.createDrawingPatriarch();    //创建绘图对象
				//   XSSFClientAnchor的参数说明：    
			         //   参数   说明    
			         //  dx1  第1个单元格中x轴的偏移量     
			         //  dy1  第1个单元格中y轴的偏移量     
			         //  dx2     第2个单元格中x轴的偏移量     
			         //  dy2  第2个单元格中y轴的偏移量    
			         //  col1 第1个单元格的列号     
			         //  row1  第1个单元格的行号     
			         //  col2 第2个单元格的列号     
			         //  row2 第2个单元格的行号     
					XSSFClientAnchor anchor= new XSSFClientAnchor(1, 1, 1, 1,(short) colNum, row1, (short) colNum+1, row2);//定位图片的位置
					XSSFPicture pict = drawing.createPicture(anchor, pictureIdx);
					pict.resize();
				}
				colNum=colNum+3;
				colNum1=colNum+1;
				if(colNum==15){
					colNum=0;
					colNum1=1;
					rowNum=rowNum+13;
					row1=row1+13;
					row2=row2+13;
				}
			}
		try {
			String path=System.getProperty("user.dir");//获取项目的相对路径
			int a=path.lastIndexOf("\\");
			path=path.substring(0, a);
			File file = new File(path+"\\web\\src\\main\\webapp\\tmp\\"+query.getFullName()+".xlsx"); //指定存放目录
			FileOutputStream fout = new FileOutputStream(file);    
			input_work.write(fout);
			fout.flush();
			fout.close();
			long endTime=System.currentTimeMillis(); //获取结束时间  
			logger.info("程序运行时间： "+(endTime-startTime)+"ms"+file.getAbsolutePath());
		} catch (Exception e) {
			e.printStackTrace();
		}
	}
}

4.在spring-quartz.xml中配置定时执行时间

<bean id="trainerImageJob" class="com.demo.task.TrainerImageJob"></bean>
	<bean id="JobtrainerPhoto"
		class="org.springframework.scheduling.quartz.MethodInvokingJobDetailFactoryBean">
		<property name="targetObject">
			<ref local="trainerImageJob" />
		</property>
		<property name="targetMethod">
			<!-- 要执行的方法名称 -->
			<value>run</value>
		</property>
	</bean>
	<bean id="cronTrainerImage" class="org.springframework.scheduling.quartz.CronTriggerBean">
		<property name="jobDetail">
			<ref bean="JobtrainerPhoto" />
		</property>
		<property name="cronExpression">
			<value>0 0 7 * * ?</value>
		</property>
	</bean>

4.项目启动，大功告成