POI使用Excel模板打印

• 自己制作一个Excel当作模板如下
  
• 把模板放到你自己的工程里
• 引入依赖

<dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi</artifactId>
            <version>4.0.1</version>
        </dependency>

        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml</artifactId>
            <version>4.0.1</version>
        </dependency>
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml-schemas</artifactId>
            <version>4.0.1</version>
        </dependency>

• 创建一个具有要输出数据属性的一个对象

@Data
public class ContractProductVo implements Serializable {

	private String customName;		//客户名称
	private String contractNo;		//合同号，订单号
	private String productNo;		//货号
	private Integer cnumber;		//数量
	private String factoryName;		//厂家名称，冗余字段
	private Date deliveryPeriod;	//交货期限
	private Date shipTime;			//船期
	private String tradeTerms;		//贸易条款
 
}

• 准备一个用于下载的工具类

public class DownloadUtil {
	
	/**
	 * @param filePath 要下载的文件路径
	 * @param returnName 返回的文件名
	 * @param response HttpServletResponse
	 * @param delFlag 是否删除文件
	 */
	protected void download(String filePath,String returnName,HttpServletResponse response,boolean delFlag){
		this.prototypeDownload(new File(filePath), returnName, response, delFlag);
	}


	/**
	 * @param file 要下载的文件
	 * @param returnName 返回的文件名
	 * @param response HttpServletResponse
	 * @param delFlag 是否删除文件
	 */
	protected void download(File file,String returnName,HttpServletResponse response,boolean delFlag){
		this.prototypeDownload(file, returnName, response, delFlag);
	}
	
	/**
	 * @param file 要下载的文件
	 * @param returnName 返回的文件名
	 * @param response HttpServletResponse
	 * @param delFlag 是否删除文件
	 */
	public void prototypeDownload(File file,String returnName,HttpServletResponse response,boolean delFlag){
		// 下载文件
		FileInputStream inputStream = null;
		ServletOutputStream outputStream = null;
		try {
			if(!file.exists()) return;
			response.reset();
			//设置响应类型	PDF文件为"application/pdf"，WORD文件为："application/msword"， EXCEL文件为："application/vnd.ms-excel"。  
			response.setContentType("application/octet-stream;charset=utf-8");

			//设置响应的文件名称,并转换成中文编码
			//returnName = URLEncoder.encode(returnName,"UTF-8");
			returnName = response.encodeURL(new String(returnName.getBytes(),"iso8859-1"));	//保存的文件名,必须和页面编码一致,否则乱码
			
			//attachment作为附件下载；inline客户端机器有安装匹配程序，则直接打开；注意改变配置，清除缓存，否则可能不能看到效果
			response.addHeader("Content-Disposition",   "attachment;filename="+returnName);  
			
			//将文件读入响应流
			inputStream = new FileInputStream(file);
			outputStream = response.getOutputStream();
			int length = 1024;
			int readLength=0;
			byte buf[] = new byte[1024];
			readLength = inputStream.read(buf, 0, length);
			while (readLength != -1) {
				outputStream.write(buf, 0, readLength);
				readLength = inputStream.read(buf, 0, length);
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				outputStream.flush();
			} catch (IOException e) {
				e.printStackTrace();
			}
			try {
				outputStream.close();
			} catch (IOException e) {
				e.printStackTrace();
			}
			try {
				inputStream.close();
			} catch (IOException e) {
				e.printStackTrace();
			}
			//删除原文件
			
			if(delFlag) {				
				file.delete();
			}
		}
	}

	/**
	 * by tony 2013-10-17
	 * @param byteArrayOutputStream 将文件内容写入ByteArrayOutputStream
	 * @param response HttpServletResponse	写入response
	 * @param returnName 返回的文件名
	 */
	public void download(ByteArrayOutputStream byteArrayOutputStream, HttpServletResponse response, String returnName) throws IOException{
		response.setContentType("application/octet-stream;charset=utf-8");
		returnName = response.encodeURL(new String(returnName.getBytes(),"iso8859-1"));			//保存的文件名,必须和页面编码一致,否则乱码
		response.addHeader("Content-Disposition",   "attachment;filename=" + returnName);  
		response.setContentLength(byteArrayOutputStream.size());
		
		ServletOutputStream outputstream = response.getOutputStream();	//取得输出流
		byteArrayOutputStream.writeTo(outputstream);					//写到输出流
		byteArrayOutputStream.close();									//关闭
		outputstream.flush();											//刷数据
	}
}

• java代码如下

public void printExcelWithTemplate(String inputDate) throws Exception{
	//        获取模板路径
	//        session.getServletContext().getRealPath("/") 获取根目录
            String realPath = session.getServletContext().getRealPath("/make/xlsprint/tOUTPRODUCT.xlsx");
            List<ContractProductVo> contractProductVoList = contractProductService.findContractProductVoByShipTime(inputDate,companyId);
            //       准备导出一个Excel
            XSSFWorkbook workbook = new XSSFWorkbook(new FileInputStream(realPath));
            //       获取一个工作表
            XSSFSheet sheet = workbook.getSheetAt(0);
            //        获取大标题的单元格
            Row bigTitleRow = sheet.getRow(0);
            Cell bigCell = bigTitleRow.getCell(1);

			//        向单元格中放入数据
            bigCell.setCellValue(inputDate.replaceAll("-0","年").replaceAll("-","年")+"月份出货表");
            //        数据库中查询的需要出货的数据
            Row row = sheet.getRow(2);
            //准备一个数组 用来存放模板中8个单元格样式   也可以用集合
            CellStyle[] cellStyles = new CellStyle[8];
            for (int i = 1; i <= 8; i++) {
                cellStyles[i-1] =  row.getCell(i).getCellStyle();
            }
            //处理文本内容
            int rowIndex = 2;
            XSSFRow row1 = null;
            XSSFCell cell = null;
            for (ContractProductVo productVo : contractProductVoList) {

                row1 = sheet.createRow(rowIndex);
                cell = row1.createCell(1);
                cell.setCellValue(productVo.getCustomName());
                cell.setCellStyle(cellStyles[0]);

                cell = row1.createCell(2);
                cell.setCellValue(productVo.getContractNo());
                cell.setCellStyle(cellStyles[1]);

                cell = row1.createCell(3);
                cell.setCellValue(productVo.getProductNo());
                cell.setCellStyle(cellStyles[2]);

                cell = row1.createCell(4);
                cell.setCellValue(productVo.getCnumber());
                cell.setCellStyle(cellStyles[3]);

                cell = row1.createCell(5);
                cell.setCellValue(productVo.getFactoryName());
                cell.setCellStyle(cellStyles[4]);

                cell = row1.createCell(6);
                cell.setCellValue(new SimpleDateFormat("yyyy-MM-dd").format(productVo.getDeliveryPeriod()));
                cell.setCellStyle(cellStyles[5]);

                cell = row1.createCell(7);
                cell.setCellValue(new SimpleDateFormat("yyyy-MM-dd").format(productVo.getShipTime()));
                cell.setCellStyle(cellStyles[6]);

                cell = row1.createCell(8);
                cell.setCellValue(productVo.getTradeTerms());
                cell.setCellStyle(cellStyles[7]);
                rowIndex++;
            }


            ByteArrayOutputStream byteArrayOutputStream = new ByteArrayOutputStream();//创建一个字节缓冲流
            workbook.write(byteArrayOutputStream);//将工作簿写入到字节缓冲流
            downloadUtil.download(byteArrayOutputStream,response,"出货表.xlsx");//调用工具类
        }