HttpUtis带有参数

package com.timespark.core.util;

import org.apache.poi.hssf.usermodel.*;

import javax.imageio.ImageIO;
import javax.servlet.http.HttpServletResponse;
import java.awt.image.BufferedImage;
import java.io.*;
import java.net.URLEncoder;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

/**
 * Author:szy
 * Date:2019/6/11
 * Description:
 */
public class ExcelUtils {

    static final short borderpx = 1;

    static final short IMAGETYPE = 250;
    /**
     * 导出excel表格
     * @param head
     * @param body
     * @return
     */
    public static HSSFWorkbook expExcel(List<String> head, List<List<String>> body) {
        HSSFWorkbook workbook = new HSSFWorkbook();
        HSSFSheet sheet = workbook.createSheet("Sheet1");
        HSSFRow row = sheet.createRow(0);
        HSSFCell cell= null;
        HSSFCellStyle cellStyle = workbook.createCellStyle();
        setBorderStyle(cellStyle, borderpx);
        cellStyle.setFont(setFontStyle(workbook, "黑体", (short) 14));
        cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        sheet.createFreezePane(0,1,0,1);

        for (int i = 0; i<head.size(); i++) {
            cell = row.createCell(i);
            cell.setCellValue(head.get(i));
            cell.setCellStyle(cellStyle);
        }

        HSSFCellStyle cellStyle2 = workbook.createCellStyle();
        setBorderStyle(cellStyle2, borderpx);
        cellStyle2.setFont(setFontStyle(workbook, "宋体", (short) 12));
        cellStyle2.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        for (int i = 0; i < body.size(); i++) {
            row = sheet.createRow(i + 1);
            List<String> paramList = body.get(i);
            for (int p = 0; p < paramList.size(); p++) {
                cell = row.createCell(p);
                cell.setCellValue(paramList.get(p));
                cell.setCellStyle(cellStyle2);
            }
        }
        for (int i = 0, isize = head.size(); i < isize; i++) {
            sheet.autoSizeColumn(i);
        }
        return workbook;
    }

    /**
     *  导出图片Excel的方法
     *
     * @param head
     * @param body
     * @param outFile
     */
    public static void expExcelImage(List<String> head, List<List<String>> body,String outFile) {
        HSSFWorkbook workbook = new HSSFWorkbook();
        HSSFSheet sheet = workbook.createSheet("Sheet1");
        HSSFRow row = sheet.createRow(0);
        HSSFCell cell= null;
        HSSFCellStyle cellStyle = workbook.createCellStyle();
        setBorderStyle(cellStyle, borderpx);
        cellStyle.setFont(setFontStyle(workbook, "黑体", (short) 14));
        cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); //水平居中
//        cellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);//垂直居中
        sheet.createFreezePane(0,1,0,1);

        for (int i = 0; i<head.size(); i++) {
            cell = row.createCell(i);
            cell.setCellValue(head.get(i));
            cell.setCellStyle(cellStyle);
        }
        BufferedImage bufferImg = null;
        FileOutputStream fileOut = null;
        try {
            HSSFCellStyle cellStyle2 = workbook.createCellStyle();
            setBorderStyle(cellStyle2, borderpx);
            cellStyle2.setFont(setFontStyle(workbook, "宋体", (short) 12));
            cellStyle2.setAlignment(HSSFCellStyle.ALIGN_CENTER); //水平居中
            cellStyle2.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);//垂直居中


            ByteArrayOutputStream byteArrayOut = new ByteArrayOutputStream();
            bufferImg = ImageIO.read(new File("C:/Users/胡祥德/Desktop/image/7.jpg"));
            ImageIO.write(bufferImg, "jpg", byteArrayOut);

            // 画图的顶级管理器，一个sheet只能获取一个（一定要注意这点）
            HSSFPatriarch patriarch = sheet.createDrawingPatriarch();

//        // anchor主要用于设置图片的属性
//        HSSFClientAnchor anchor = new HSSFClientAnchor(0, 0, 1023, 250, (short) 1, 1+i*10, (short) 5, 8+i*10);
//        // 插入图片
//        patriarch.createPicture(anchor, wb.addPicture(byteArrayOut.toByteArray(), HSSFWorkbook.PICTURE_TYPE_JPEG));

            for (int i = 0; i < body.size(); i++) {
                row = sheet.createRow(i + 1);
                //设置行的高度
                row.setHeightInPoints(6*20);

                List<String> paramList = body.get(i);
                for (int p = 0; p < paramList.size(); p++) {
                    if (p == paramList.size() - 1) { //最后一列插入图片
                        int lastCell = paramList.size() - 1;
                        System.err.println(lastCell);
                        cell = row.createCell(p);
//                        cell.setCellValue(paramList.get(p));
                        cell.setCellStyle(cellStyle2);

                        HSSFClientAnchor anchor = new HSSFClientAnchor(0, 0, 300, 250, (short) lastCell, i+1, (short)(lastCell+1) , i+1);
                        patriarch.createPicture(anchor, workbook.addPicture(byteArrayOut.toByteArray(), HSSFWorkbook.PICTURE_TYPE_JPEG));
                    }else {
                        cell = row.createCell(p);
                        cell.setCellValue(paramList.get(p));
                        cell.setCellStyle(cellStyle2);
                    }
                }
            }
            for (int i = 0, isize = head.size(); i < isize; i++) {
                sheet.autoSizeColumn(i);
            }
            //设置最后一列的宽度
            sheet.setColumnWidth(head.size()-1, 50 * 256);

            fileOut = new FileOutputStream(outFile);
            // 写入excel文件
            workbook.write(fileOut);
        } catch (IOException e) {
            e.printStackTrace();
        }

    }

    /**
     * 工具类测试代码
     *
     *
     * @param args
     */
    public static void main(String[] args) {

        String outFile = "E:/test.xls";

        List<String> head = new ArrayList<>();
        head.add("序号");
        head.add("车牌");
        head.add("图片");

        List<List<String>> body = new ArrayList<>();
        for (int i = 0; i <3 ; i++) {
            List<String> list = new ArrayList<>();
            list.add(i + "");
            list.add("京A11111");
            list.add(null);
            body.add(list);
        }

        ExcelUtils.expExcelImage(head,body,outFile);
    }

    /**
     * 文件输出
     * @author LiuYang
     * @param workbook 填充好的workbook
     * @param path 存放的位置
     */
    public static void outFile(HSSFWorkbook workbook, String path, HttpServletResponse response) {
        SimpleDateFormat fdate=new SimpleDateFormat("yyyyMMdd-HH点mm分");
        path = path.substring(0, path.lastIndexOf(".")) + fdate.format(new Date()) + path.substring(path.lastIndexOf("."));
        OutputStream os=null;
        File file = null;
        try {
            file = new File(path);
            String filename = file.getName();
            os = new FileOutputStream(file);
            response.addHeader("Content-Disposition", "attachment;filename="+ URLEncoder.encode(filename, "UTF-8"));
            os= new BufferedOutputStream(response.getOutputStream());
            response.setContentType("application/vnd.ms-excel;charset=utf-8");
            System.out.println(file);
            workbook.write(os);
        } catch (FileNotFoundException e1) {
            e1.printStackTrace();
        } catch (IOException e) {
            e.printStackTrace();
        }
        try {
            os.flush();
            os.close();
            System.gc();
            System.out.println(file.delete());
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

    /**
     * 设置字体样式
     * @author LiuYang
     * @param workbook 工作簿
     * @param name 字体类型
     * @param height 字体大小
     * @return HSSFFont
     */
    private static HSSFFont setFontStyle(HSSFWorkbook workbook, String name, short height) {
        HSSFFont font = workbook.createFont();
        font.setFontHeightInPoints(height);
        font.setFontName(name);
        return font;
    }

    /**
     * 设置单元格样式
     * @author LiuYang
     * @param cellStyle 工作簿
     * @param border border样式
     */
    private static void setBorderStyle(HSSFCellStyle cellStyle, short border) {
        cellStyle.setBorderBottom(border); // 下边框
        cellStyle.setBorderLeft(border);// 左边框
        cellStyle.setBorderTop(border);// 上边框
        cellStyle.setBorderRight(border);// 右边框
    }

    /**
     * 设置图片的单元格样式
     * @param cellStyle  工作簿
     * @param TopAndBborder  上边框 下边框 样式
     * @param RandLBorder 左边框 右边框 样式
     */
    private static void setBorderStyle0(HSSFCellStyle cellStyle, short TopAndBborder,short RandLBorder) {
        cellStyle.setBorderBottom(TopAndBborder); // 下边框
        cellStyle.setBorderTop(TopAndBborder);// 上边框
        cellStyle.setBorderLeft(RandLBorder);// 左边框
        cellStyle.setBorderRight(RandLBorder);// 右边框
    }

}