模板下载excel

1，导入依赖

<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>

2 准备一个下载的工具类

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();											//刷数据
	}

3 准备好对象：存取数据库查到的数据

4 模板打印  这是根据时间下载

  public void printExcel(String inputDate) throws IOException {
        //设置list，存放查询好准备放入表格的数据
        List<ContractProductVo> list = contractProductService.findVoByShipTime(inputDate,companyId);
        //读取模板样式并写入内容
        String path=session.getServletContext().getRealPath("/")+"/make/xlsprint/tOUTPRODUCT.xlsx";
        //根据模板创建wb
        Workbook wb=new XSSFWorkbook(path);
        //获得第一页
        Sheet st = wb.getSheetAt(0);
        //准备数据
        int rowIndex=0;
        Row row=null;
        Cell cell=null;
        //第一行
        row=st.getRow(rowIndex++);
        cell=row.getCell(1);
        String value = inputDate.replaceAll("-0", "-").replaceAll("-", "年");
        cell.setCellValue(value+"月份出货表");
        // 略过第二行
        rowIndex++;
        //获取第三行的样式
        CellStyle[] styleArr=new CellStyle[9];//创建数组存放每一个单元格样式
        for(int i=1;i<styleArr.length;i++){
            row=st.getRow(rowIndex);
            cell=row.getCell(i);
            styleArr[i]=cell.getCellStyle();
        }
        //遍历给每个单元格填数据
        for (ContractProductVo contractProductVo : list) {
            row=st.createRow(rowIndex++);
            cell=row.createCell(1);
            cell.setCellValue(contractProductVo.getCustomName());
            cell.setCellStyle(styleArr[1]);

            cell=row.createCell(2);
            cell.setCellValue(contractProductVo.getContractNo());
            cell.setCellStyle(styleArr[2]);

            cell=row.createCell(3);
            cell.setCellValue(contractProductVo.getProductNo());
            cell.setCellStyle(styleArr[3]);

            cell=row.createCell(4);
            cell.setCellValue(contractProductVo.getCnumber());
            cell.setCellStyle(styleArr[4]);

            cell=row.createCell(5);
            cell.setCellValue(contractProductVo.getFactoryName());
            cell.setCellStyle(styleArr[5]);

            cell=row.createCell(6);
            cell.setCellValue(contractProductVo.getDeliveryPeriod());
            cell.setCellStyle(styleArr[6]);

            cell=row.createCell(7);
            cell.setCellValue(contractProductVo.getShipTime());
            cell.setCellStyle(styleArr[7]);

            cell=row.createCell(8);
            cell.setCellValue(contractProductVo.getTradeTerms());
            cell.setCellStyle(styleArr[8]);
        }

        //下载
        ByteArrayOutputStream byteArrayOutputStream = new ByteArrayOutputStream();
        wb.write(byteArrayOutputStream);
        new DownloadUtil().download(byteArrayOutputStream,response,"出货表.xlsx");
    }