poi导出excel xls

--------------------------------------------工具类 excel样式---------------------------------------------------------------

package com.duor.aladdin.freight.util;

import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;

import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletResponse;
import java.io.*;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.List;
import java.util.Map;

/**
 * @ClassName ExcelUtilXls
 * @Description TODO
 * @Author lz
 * @Date 2019/7/12 14:26
 **/
public class ExcelUtilXls {

    public Object downloadFile;
    private HSSFSheet sheet;
    private String bDate;
    private int year;


    /**
     * 创建行元素
     * @param style    样式
     * @param height   行高
     * @param value    行显示的内容
     * @param row1     起始行
     * @param row2     结束行
     * @param col1     起始列
     * @param col2     结束列
     */
    private void createRow(HSSFCellStyle style, int height, String value, int row1, int row2, int col1, int col2){

        sheet.addMergedRegion(new CellRangeAddress(row1, row2, col1, col2));  //设置从第row1行合并到第row2行，第col1列合并到col2列
        HSSFRow rows = sheet.createRow(row1);        //设置第几行
        rows.setHeight((short) height);              //设置行高
        HSSFCell cell = rows.createCell(col1);       //设置内容开始的列
        cell.setCellStyle(style);                    //设置样式
        cell.setCellValue(value);                    //设置该行的值
    }


    /**
     * 创建样式
     * @param fontSize   字体大小
     * @param align  水平位置  左右居中2 居右3 默认居左 垂直均为居中
     * @param bold   是否加粗
     * @return
     */

    private HSSFCellStyle getStyle(HSSFWorkbook workbook, int fontSize, int align, boolean bold, boolean border){
        HSSFFont font = workbook.createFont();
        font.setFontName("宋体");
        font.setFontHeightInPoints((short) fontSize);// 字体大小
        if (bold){
            font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
        }
        HSSFCellStyle style = workbook.createCellStyle();
        style.setFont(font);                         //设置字体
        style.setAlignment((short) align);          // 左右居中2 居右3 默认居左
        style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);// 上下居中1
        if (border){
            style.setBorderRight((short) 2);
            style.setBorderLeft((short) 2);
            style.setBorderBottom((short) 2);
            style.setBorderTop((short) 2);
            style.setLocked(true);
        }
        return style;
    }


    /**
     * 根据数据集生成Excel，并返回Excel文件流
     * @param data 数据集
     * @param sheetName Excel中sheet单元名称
     * @param headNames 列表头名称数组
     * @param colKeys 列key,数据集根据该key进行按顺序取值
     * @return
     * @throws IOException
     */
    public InputStream getExcelFile(String fileName,HttpServletResponse response, HSSFWorkbook workbook, int j, List<Map> data, String sheetName, String[] headNames,
                                    String[] colKeys, int colWidths[], String bDate) throws IOException {
        this.bDate = bDate;
        sheet = workbook.createSheet(sheetName);
        // 创建表头 startRow代表表体开始的行
        int startRow = createHeadCell(workbook, headNames, colWidths);

        // 创建表体数据
        HSSFCellStyle cellStyle = getStyle(workbook,14,2,false,true); // 建立新的cell样式
        setCellData(data, cellStyle, startRow, colKeys);

        //创建表尾
//        createTailCell(data.size()+4,headNames.length);
        ByteArrayOutputStream baos = new ByteArrayOutputStream();
        workbook.write(baos);
        byte[] ba = baos.toByteArray();
        ByteArrayInputStream bais = new ByteArrayInputStream(ba);

//        // 4. 将数组放入输入流中
//        InputStream is = new ByteArrayInputStream(ba);
//        // 5. 设置response参数
//        response.reset(); // 重置response的设置
//        response.setContentType("application/vnd.ms-excel;charset=utf-8");
//        response.setHeader("Content-Disposition", "attachment;filename=" + new String((fileName + ".xls").getBytes(), "iso-8859-1"));
//        // 6. 创建Servlet 输出流对象
//        ServletOutputStream out = response.getOutputStream();
//        BufferedInputStream bis = null;
//        BufferedOutputStream bos = null;
//        try {
//            // 6.1装载缓冲输出流
//            bis = new BufferedInputStream(is);
//            bos = new BufferedOutputStream(out);
//            byte[] buff = new byte[2048];
//            int bytesRead;
//            // 6.2 输出内容
//            while (-1 != (bytesRead = bis.read(buff, 0, buff.length))) {
//                bos.write(buff, 0, bytesRead);
//            }
//        } catch (final IOException e) {
//            throw e;
//        } finally {
//            if (bis != null)
//                bis.close();
//            if (bos != null)
//                bos.close();
//        }
        return bais;
    }

    /**
     * 创建表头
     *
     * @param headNames
     * @param colWidths
     */
    private int createHeadCell(HSSFWorkbook workbook, String[] headNames, int colWidths[]) {
        year = Integer.parseInt(bDate.substring(0,4));
        sheet.addMergedRegion(new CellRangeAddress(2, 2, 0, 1));
        HSSFRow row = sheet.createRow(0);
        row.setHeight((short) 0x190);
        HSSFCell cell = row.createCell(0);
        cell.setCellStyle(getStyle(workbook,12,1,true,false));

        //第三行右边部分
        Date date = new Date();
        SimpleDateFormat sdf=new SimpleDateFormat("yyyy年MM月dd日");
        sheet.addMergedRegion(new CellRangeAddress(2, 2, 3, 5));
        HSSFCell cell2 = row.createCell(3);
        cell2.setCellStyle(getStyle(workbook,12,3,true,false));
        cell2.setCellValue("制表时间: "+sdf.format(date));

        //第四行表头
        boolean b = (headNames != null && headNames.length > 0);
        if (b) {
            HSSFRow row2 = sheet.createRow(3);
            row2.setHeight((short) 0x289);
            HSSFCell fcell = null;

            HSSFCellStyle cellStyle = getStyle(workbook,15,2,true,true); // 建立新的cell样式

            for (int i = 0; i < headNames.length; i++) {
                fcell = row2.createCell(i);

                fcell.setCellStyle(cellStyle);
                fcell.setCellValue(headNames[i]);
                if (colWidths != null && i < colWidths.length) {
                    sheet.setColumnWidth(i, 32 * colWidths[i]);
                }
            }
        }
        return b ? 4 : 3;  //从哪一行开始渲染表体
    }

    /**
     * 创建表体数据
     * @param data           表体数据
     * @param cellStyle      样式
     * @param startRow       开始行
     * @param colKeys        值对应map的key
     */
    private void setCellData(List<Map> data, HSSFCellStyle cellStyle, int startRow,
                             String[] colKeys) {
        // 创建数据
        HSSFRow row = null;
        HSSFCell cell = null;
        int i = startRow;

        if (data != null && data.size() > 0) {
            DecimalFormat df = new DecimalFormat("#0.00");
            for (Map<String, Object> rowData : data) {
                row = sheet.createRow(i);
                row.setHeight((short) 0x279);
                int j = 0;
                for (String key : colKeys) {
                    Object colValue = rowData.get(key);
                    cell = row.createCell(j);
                    cell.setCellStyle(cellStyle);
                    if (colValue != null) {
                        cell.setCellType(HSSFCell.CELL_TYPE_STRING);
                        cell.setCellValue(colValue.toString());
                    }
                    j++;
                }
                i++;
            }
        }
    }
}

-----------------------------------------------service 主要是封装数据-----------------------------------------------------------

 public String exportFclExcel(HttpServletResponse response, ServletRequest req, Integer loadingPort, Integer dischargePort, String transit, String organizationCode, Integer ship, Integer failure, String orderBy, Integer pageSize, Integer type) throws IOException {
        HSSFWorkbook book = new HSSFWorkbook();
        String fileName = "整箱运价信息一览";
        FreightChargeFcl freightChargeFcl = new FreightChargeFcl();
        freightChargeFcl.setLoadingPort(loadingPort);
        freightChargeFcl.setDischargePort(dischargePort);
        freightChargeFcl.setTransit(transit);
        freightChargeFcl.setOrganizationCode(organizationCode);
        freightChargeFcl.setFailure(1);
        freightChargeFcl.setShip(ship);
        freightChargeFcl.setOrderBy(orderBy);

        //获取公司id
        ExcelUtil excel = new ExcelUtil();
        ExcelUtilXls excelUtilXls = new ExcelUtilXls();
        List<Map> data = new ArrayList<>();
        List<FreightChargeFcl> freightChargeFclList = freightChargeFclMapper.selectFcl(freightChargeFcl);
        String path = "";
        OutputStream out;
        InputStream input = null;
        //判断下载文件格式
        if (type != null && type == 1) {
            //分割list的起始循环数
            int count = 0;
            //每个sheet页显示的数据条数
            if (pageSize == null) {
                pageSize = 10;
            }
            int divisor = pageSize;
            int number = pageSize;
            Integer sheetCount = freightChargeFclList.size() / divisor;
            Integer sheetRemainder = freightChargeFclList.size() % divisor;
            if (sheetRemainder > 0) {
                sheetCount += 1;
            }
            for (int j = 0; j < sheetCount; j++) {
                data = new ArrayList<>();
                if (divisor < freightChargeFclList.size()) {
                    for (int i = count; i < divisor; i++) {
                        LinkedHashMap<String, Object> e = new LinkedHashMap<String, Object>();
                        e.put("loadingPortNameEn", freightChargeFclList.get(i).getLoadingPortNameEn());
                        e.put("dischargePortNameEn", freightChargeFclList.get(i).getDischargePortNameEn());
                        e.put("shippingCode", freightChargeFclList.get(i).getShippingCode());
                        e.put("routesNameCn", freightChargeFclList.get(i).getRoutesNameCn());
                        e.put("routesCode", freightChargeFclList.get(i).getRoutesNameEn());
                        e.put("weekOfDay", freightChargeFclList.get(i).getWeekOfDay());
                        e.put("range", freightChargeFclList.get(i).getRange());
                        e.put("transitPortNameEn", freightChargeFclList.get(i).getTransitPortNameEn());
                        e.put("gp20", freightChargeFclList.get(i).getGp20());
                        e.put("gp40", freightChargeFclList.get(i).getGp40());
                        e.put("hq40", freightChargeFclList.get(i).getHq40());
                        e.put("startDate", freightChargeFclList.get(i).getStartDate());
                        e.put("endDate", freightChargeFclList.get(i).getEndDate());
                        e.put("createUser", freightChargeFclList.get(i).getCreateUser());
                        e.put("createTime", freightChargeFclList.get(i).getCreateTime());
                        data.add(e);
                    }
                } else {
                    data = new ArrayList<>();
                    for (int k = divisor - number; k < freightChargeFclList.size(); k++) {
                        LinkedHashMap<String, Object> e = new LinkedHashMap<String, Object>();
                        e.put("loadingPortNameEn", freightChargeFclList.get(k).getLoadingPortNameEn());
                        e.put("dischargePortNameEn", freightChargeFclList.get(k).getDischargePortNameEn());
                        e.put("shippingCode", freightChargeFclList.get(k).getShippingCode());
                        e.put("routesNameCn", freightChargeFclList.get(k).getRoutesNameCn());
                        e.put("routesCode", freightChargeFclList.get(k).getRoutesNameEn());
                        e.put("weekOfDay", freightChargeFclList.get(k).getWeekOfDay());
                        e.put("range", freightChargeFclList.get(k).getRange());
                        e.put("transitPortNameEn", freightChargeFclList.get(k).getTransitPortNameEn());
                        e.put("gp20", freightChargeFclList.get(k).getGp20());
                        e.put("gp40", freightChargeFclList.get(k).getGp40());
                        e.put("hq40", freightChargeFclList.get(k).getHq40());
                        e.put("startDate", freightChargeFclList.get(k).getStartDate());
                        e.put("endDate", freightChargeFclList.get(k).getEndDate());
                        e.put("createUser", freightChargeFclList.get(k).getCreateUser());
                        e.put("createTime", freightChargeFclList.get(k).getCreateTime());
                        data.add(e);
                    }
                }
                count = divisor;
                divisor = number + count;
                String[] headNames = {"起始港", "目的港", "船公司", "航线", "航线代码",
                        "班期", "航程", "中转", "GP20", "GP40", "HQ40",
                        "有效期", "失效期", "操作人", "操作时间"};
                String[] keys = {"loadingPortNameEn", "dischargePortNameEn", "shippingCode", "routesNameCn", "routesCode",
                        "weekOfDay", "range", "transitPortNameEn", "gp20", "gp40", "hq40",
                        "startDate", "endDate", "createUser", "createTime"};
                int colWidths[] = {160, 260, 160, 160, 160, 160, 160, 160, 160, 160, 160, 245, 245, 160, 245};
                String bDate = "201708";
                //创建sheet
                input = excelUtilXls.getExcelFile(fileName, response, book, j, data, "整箱运价信息" + (j + 1) + "页", headNames, keys, colWidths, bDate);

            }
        }
        // 4. 将数组放入输入流中
//        InputStream is = new ByteArrayInputStream(ba);
        // 5. 设置response参数
        response.reset(); // 重置response的设置
        response.setContentType("application/vnd.ms-excel;charset=utf-8");
        response.setHeader("Content-Disposition", "attachment;filename=" + new String((fileName + ".xls").getBytes(), "iso-8859-1"));
        // 6. 创建Servlet 输出流对象
        out = response.getOutputStream();
        book = new HSSFWorkbook(input);
        book.write(out);
        BufferedInputStream bis = null;
        BufferedOutputStream bos = null;
        try {
            // 6.1装载缓冲输出流
            bis = new BufferedInputStream(input);
            bos = new BufferedOutputStream(out);
            byte[] buff = new byte[2048];
            int bytesRead;
            // 6.2 输出内容
            while (-1 != (bytesRead = bis.read(buff, 0, buff.length))) {
                bos.write(buff, 0, bytesRead);
            }
        } catch (final IOException e) {
            throw e;
        }finally {
            if (bis != null)
                bis.close();
            if (bos != null)
                bos.close();
            bos.flush();
        }
        return path;
    }