POI 自定义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代码编写打印(仅仅写了表现层)

@Autowired
    private DownloadUtil downloadUtil;
    @RequestMapping("/printExcel")
    public void printExcel(String inputDate) throws Exception {
        List<ContractProductVo> contractProductVoList = contractProductService.findContractProductVoByShipTime(inputDate, companyId);
        XSSFWorkbook workbook = new XSSFWorkbook();//一个全新的工作表
        XSSFSheet sheet = workbook.createSheet();
        sheet.setColumnWidth(0, 2 * 256);
        sheet.setColumnWidth(1, 26 * 256);
        sheet.setColumnWidth(2, 10 * 256);
        sheet.setColumnWidth(3, 30 * 256);
        sheet.setColumnWidth(4, 11 * 256);
        sheet.setColumnWidth(5, 11 * 256);
        sheet.setColumnWidth(6, 11 * 256);
        sheet.setColumnWidth(7, 11 * 256);
        sheet.setColumnWidth(8, 11 * 256);
        XSSFRow row = null;
        XSSFCell cell = null;
        //处理大标题
        row = sheet.createRow(0);
        for (int i = 0; i < 9; i++) {
            row.createCell(i);
        }
        sheet.addMergedRegion(new CellRangeAddress(0, 0, 1, 8));//合并单元格
        row.setHeightInPoints(36);//设置行高
        cell = row.getCell(1);
        cell.setCellStyle(bigTitle(workbook));
        cell.setCellValue(inputDate.replaceAll("-0", "年").replaceAll("-", "年") + "月份出货表");
        //处理小标题

        String[] titles = new String[]{"客户", "合同号", "货号", "数量", "工厂", "工厂交期", "船期", "贸易条款"};
        row = sheet.createRow(1);
        row.setHeightInPoints(20);
        for (int i = 1; i <= titles.length; i++) {
            cell = row.createCell(i);
            cell.setCellValue(titles[i - 1]);
            cell.setCellStyle(title(workbook));

        }
        //处理文本内容
        int rowIndex = 2;
        for (ContractProductVo productVo : contractProductVoList) {

            row = sheet.createRow(rowIndex);
            row.setHeightInPoints(20);

            cell = row.createCell(1);
            cell.setCellStyle(text(workbook));
            cell.setCellValue(productVo.getCustomName());

            cell = row.createCell(2);
            cell.setCellStyle(text(workbook));
            cell.setCellValue(productVo.getContractNo());

            cell = row.createCell(3);
            cell.setCellStyle(text(workbook));
            cell.setCellValue(productVo.getProductNo());

            cell = row.createCell(4);
            cell.setCellStyle(text(workbook));
            cell.setCellValue(productVo.getCnumber());

            cell = row.createCell(5);
            cell.setCellStyle(text(workbook));
            cell.setCellValue(productVo.getFactoryName());

            cell = row.createCell(6);
            cell.setCellStyle(text(workbook));
            cell.setCellValue(new SimpleDateFormat("yyyy-MM-dd").format(productVo.getDeliveryPeriod()));

            cell = row.createCell(7);
            cell.setCellStyle(text(workbook));
            cell.setCellValue(new SimpleDateFormat("yyyy-MM-dd").format(productVo.getShipTime()));

            cell = row.createCell(8);
            cell.setCellStyle(text(workbook));
            cell.setCellValue(productVo.getTradeTerms());

            rowIndex++;
        }
    }

        @RequestMapping("/printExcelWithTemplate")
        public void printExcelWithTemplate(String inputDate) throws Exception{
            String realPath = session.getServletContext().getRealPath("/make/xlsprint/tOUTPRODUCT.xlsx");
            List<ContractProductVo> contractProductVoList = contractProductService.findContractProductVoByShipTime(inputDate,companyId);
            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);
            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");//调用工具类
        }