POI流式导出Excel

需要的pom依赖

       <dependency>
            <groupId>cn.afterturn</groupId>
            <artifactId>easypoi-base</artifactId>
            <version>3.0.3</version>
        </dependency>
        <dependency>
            <groupId>cn.afterturn</groupId>
            <artifactId>easypoi-web</artifactId>
            <version>3.0.3</version>
        </dependency>
        <dependency>
            <groupId>cn.afterturn</groupId>
            <artifactId>easypoi-annotation</artifactId>
            <version>3.0.3</version>
        </dependency>

一、Excel模板导出，不是数据导出(导出是带着限制条件的空Excel)

(PS:工具类在最下面)

controller层方法

    @GetMapping("/export")
    @ApiOperation(value = "模板下载", notes = "模板下载")
    public void export(HttpServletResponse response, HttpServletRequest request) {
        OutputStream out = null;
        try {
            //取得输出流
            out = response.getOutputStream();
            //清空输出流
            response.reset();
            //设置响应头和下载保存的文件名
            String agent = request.getHeader("USER-AGENT").toLowerCase();
            response.setContentType("application/vnd.ms-excel");
            String fileName = "资产导入模板.xlsx";
            String codedFileName = java.net.URLEncoder.encode(fileName, "UTF-8");
            if (agent.contains("firefox")) {
                response.setCharacterEncoding("utf-8");
                codedFileName = "=?UTF-8?B?" + (new String(Base64Utils.encodeToString(fileName.getBytes("UTF-8")))) + "?=";
                response.setHeader("content-disposition", "attachment;filename=" + codedFileName);
            } else {
                response.setHeader("content-disposition", "attachment;filename=" + codedFileName);
            }
            String cnt = "\n（请不要删除列）";
            //下来列表的选项
            String resultCnt = "\n（请选择“1采购合同”“2电子网购”）\n（请不要删除列）";
            String[] titles = new String[]{"资产名称" + cnt,  "品牌" + cnt, "型号" + cnt, "规格/配置" + cnt,
                    "特性值" + cnt, "SN码" + cnt, "启用日期" + cnt, "责任人" + cnt, "使用人" + cnt,  "一级部门" + cnt, "二级部门" + cnt, "三级部门" + cnt,
                    "公司主体名称" + cnt,"资产状态名称" + cnt, "地点名称" + cnt,
                    "存放地点" + cnt, "具体位置" + cnt, "管理条线名称" + cnt, "资产大类名称" + cnt,  "资产小类名称" + cnt,  "实物分类名称" + cnt, "单位" + cnt,
                    "数量" + cnt, "资产原值" + cnt, "资产净值" + cnt, "残值" + cnt, "供应商" + cnt, "PO单号" + cnt, "PO接收编码" + cnt,
                    "来源" + resultCnt};// 设置列名

            int[] columnLength = {5000, 5000, 5000, 5000, 5000, 5000, 5000, 5000, 5000, 5000, 5000, 5000, 5000, 5000, 5000, 5000, 5000, 5000,
                    5000, 5000, 5000, 5000, 5000, 5000, 5000, 5000, 5000, 5000, 5000, 15000};
            String[] names = new String[]{"采购合同", "电子网购"};
            CellRangeAddressList cellRangeAddressNumber = new CellRangeAddressList(1,2000,22,25);
            CellRangeAddressList cellRangeAddressList = new CellRangeAddressList(1,2000,29,29);

            SXSSFWorkbook workbook = ExcelPoiUtil.getSXSSFWorkbookAssets("资产导入模板", titles, columnLength, null, names, cellRangeAddressList,
                    cellRangeAddressNumber);
            ExcelPoiUtil.writeExceleByHSSFPOI(workbook, out);
        } catch (Exception e) {
            log.error("模板下载异常", e);
        }
    }

二、Excel数据导出，导出指定数据Excel

(PS:工具类在最下面)

controller层方法

     @GetMapping("/export")
     @ApiOperation(value = "个人数据导出", notes = "个人数据导出")
     public void export(HttpServletResponse response, HttpServletRequest request) {
          OutputStream out = null;
        try {
            //查询出来Excel需要填写的数据
            List<Task> taskList = taskService.selectMyself(task);
            //取得输出流
            out = response.getOutputStream();
            //清空输出流
            response.reset();
            //设置响应头和下载保存的文件名
            String agent = request.getHeader("USER-AGENT").toLowerCase();
            response.setContentType("application/vnd.ms-excel");
            String fileName = "个人数据明细.xlsx";
            String codedFileName = java.net.URLEncoder.encode(fileName, "UTF-8");
            if (agent.contains("firefox")) {
                response.setCharacterEncoding("utf-8");
                codedFileName = "=?UTF-8?B?" + (new String(Base64Utils.encodeToString(fileName.getBytes("UTF-8")))) + "?=";
                response.setHeader("content-disposition", "attachment;filename=" + codedFileName);
            } else {
                response.setHeader("content-disposition", "attachment;filename=" + codedFileName);
            }
            //某一列的提示语
            String cnt = "\n（请不要删除列）";
            String userNameCnt = "\n（请不要删除列，无需填写）";
            //下拉框选项
            String resultCnt = "\n（请选择“确认”“无法确认”）";
            String remarkCnt = "\n（盘点结果为“无法确认”时备注为必填项）";
            String[] titles = new String[]{"序号" + cnt, "资产标签号" + cnt, "资产名称" + cnt, "品牌" + cnt, "型号" + cnt, "规格/配置" + cnt, "SN码" + cnt, "特性值" + cnt,
                    "公司主体" + cnt, "领用日期" + cnt, "责任人" + cnt, "资产状态" + cnt, "存放地点" + cnt, "具体位置" + cnt, "计划编号" + cnt, "盘点计划" + cnt, "盘点结果" + resultCnt, "盘点人" + userNameCnt,
                    "盘点备注" + remarkCnt, "使用人备注" + cnt};// 设置列名

            int[] columnLength = {5000, 5000, 5000, 5000, 5000, 5000, 5000, 5000, 5000, 5000, 5000, 5000, 5000, 5000, 5000, 5000, 5000, 5000, 5000, 5000};
            String[] fileds = new String[]{"indexNumber", "labelNumber", "assetName", "brand", "modelNumber", "spec",
                    "snNumber", "feature", "companyName", "useDate", "personLiableName", "assetStatusName",
                    "locationName", "position", "planCode", "taskName", "result", "assetUserName", "assetRemark", "userRemark"};// 设置列对应的对象的属性
            CellRangeAddressList cellRangeAddressList = null;
            String[] names = null;
            //调用上面的工具类即可，修改一下调用时候的入参
            SXSSFWorkbook workbook = ExcelPoiUtil.getSXSSFWorkbook("个人盘点明细", titles, columnLength, null, names, cellRangeAddressList);
            ExcelPoiUtil.addRow(workbook, taskList, fileds);
            ExcelPoiUtil.writeExceleByPOI(workbook, out);
        }  catch (Exception e) {
            log.error("数据导出异常", e);
        }
     }

Util工具类中方法(ExcelPoiUtil工具类)

public static SXSSFWorkbook getSXSSFWorkbookAssets(String sheetName, String[] titles, int[] columnLength, int[] columnHidden,
                                                       String[] names, CellRangeAddressList rangeAddressList,
                                                       CellRangeAddressList rangeAddressListNumber) {
        SXSSFWorkbook workbook = new SXSSFWorkbook();// 创建个workbook，默认每100条刷新一次内存数据到硬盘
        CellStyle cellStyleTitle = workbook.createCellStyle();

        cellStyleTitle.setBorderBottom(BorderStyle.THIN); //下边框
        cellStyleTitle.setBorderLeft(BorderStyle.THIN);//左边框
        cellStyleTitle.setBorderTop(BorderStyle.THIN);//上边框
        cellStyleTitle.setBorderRight(BorderStyle.THIN);//右边框
        cellStyleTitle.setAlignment(HorizontalAlignment.CENTER); // 水平居中
        cellStyleTitle.setVerticalAlignment(VerticalAlignment.CENTER); // 垂直居中
        Font fontTitle = workbook.createFont();
        fontTitle.setFontName("宋体");//设置字体
        fontTitle.setFontHeightInPoints((short) 12);//设置字体大小
        fontTitle.setBold(true);
        cellStyleTitle.setFont(fontTitle);
        cellStyleTitle.setWrapText(true);

        Sheet sheet = workbook.createSheet(sheetName);
        //设置某一列单元格的下拉框
        if (names != null && rangeAddressList != null) {
            DataValidationHelper helper = sheet.getDataValidationHelper();
            DataValidationConstraint constraint = helper.createExplicitListConstraint(names);
            DataValidation dataValidation = helper.createValidation(constraint, rangeAddressList);

            //处理Excel兼容性问题
            if (dataValidation instanceof XSSFDataValidation) {
                dataValidation.setSuppressDropDownArrow(true);
                dataValidation.setShowErrorBox(true);
            } else {
                dataValidation.setSuppressDropDownArrow(false);
            }
            sheet.addValidationData(dataValidation);
        }

        //限制某一列单元格只能填写数字
        if (rangeAddressListNumber != null) {
            DataValidationHelper helper = sheet.getDataValidationHelper();
            DataValidationConstraint numericConstraint = helper.createNumericConstraint(DVConstraint.ValidationType.DECIMAL,
                    DVConstraint.OperatorType.BETWEEN, "0", "2000000");
            // 设定在哪个单元格生效
            CellRangeAddressList regions = new CellRangeAddressList(1, 2000, 22, 25);
            DataValidation dataValidation = helper.createValidation(numericConstraint, regions);
            //处理Excel兼容性问题
            if (dataValidation instanceof XSSFDataValidation) {
                dataValidation.setSuppressDropDownArrow(true);
                dataValidation.setShowErrorBox(true);
            } else {
                dataValidation.setSuppressDropDownArrow(false);
            }
            sheet.addValidationData(dataValidation);
        }

        if (columnHidden != null) {
            for (int i = 0; i < columnHidden.length; i++) {
                sheet.setColumnHidden(columnHidden[i],true);
            }
        }
        if (columnLength != null) {
            for (int i = 0; i < columnLength.length; i++) {//设置列宽
                sheet.setColumnWidth(i, columnLength[i]);
            }
        }
        Row row = sheet.createRow(0);//创建表头
        for (int i = 0; titles != null && i < titles.length; i++) {
            Cell cell = row.createCell(i);
            cell.setCellValue(titles[i]);
            cell.setCellStyle(cellStyleTitle);
        }
        CellStyle cellStyleBody = workbook.createCellStyle();
        cellStyleBody.cloneStyleFrom(cellStyleTitle);
        Font fontBody = workbook.createFont();
        fontBody.setFontName("宋体");//设置字体
        fontBody.setFontHeightInPoints((short) 10);//设置字体大小
        cellStyleBody.setFont(fontBody);
        return workbook;
    }

    public static <T> void addRow(SXSSFWorkbook workbook, List<T> list, String[] fileds) throws NoSuchMethodException, InvocationTargetException, IllegalAccessException {
        if (null == fileds) {
            return;
        }
        Sheet sheet = workbook.getSheetAt(0);
        CellStyle cellStyle = workbook.getCellStyleAt((short) 2);
        if (list != null && list.size() > 0) {
            int lastRowNum = sheet.getLastRowNum();
            Row row = null;
            for (T t : list) {
                row = sheet.createRow(++lastRowNum);
                Class clazz = t.getClass();
                String[] contents = new String[fileds.length];
                for (int i = 0; i < fileds.length; i++) {
                    String filedName = toUpperCaseFirstOne(fileds[i]);
                    Method method = clazz.getMethod(filedName);
                    method.setAccessible(true);
                    Class<?> returnType = method.getReturnType();
                    Object obj = method.invoke(t);
                    Cell cell = row.createCell(i);
                    cell.setCellStyle(cellStyle);
                    if (returnType == Date.class && obj != null) {
                        Date objDate = (Date) obj;
                        MyDateFormat annotation = method.getDeclaredAnnotation(MyDateFormat.class);
                        String dateFormat = "yyyy-MM-dd";
                        if (annotation != null) {
                            dateFormat = annotation.value();
                        }
                        contents[i] = new SimpleDateFormat(dateFormat).format(objDate);
                        cell.setCellValue(contents[i]);
                        continue;
                    }
                    if (obj instanceof BigDecimal) {
                        double v = ((long) (((BigDecimal) obj).doubleValue() * 100)) / 100.00;
                        cell.setCellValue(v);
                        continue;
                    } else if (obj instanceof Double) {
                        cell.setCellValue((Double) obj);
                        continue;
                    }
                    String str = String.valueOf(obj);
                    if (str == null || str.equals("null"))
                        str = "";
                    contents[i] = str;
                    cell.setCellValue(contents[i]);
                }
            }
        }
    }

    public static void writeExceleByPOI(SXSSFWorkbook sxssfWorkbook, OutputStream out) {
        try {
            sxssfWorkbook.write(out);
            out.flush();
        } catch (IOException e) {
            e.printStackTrace();
        } finally {
            try {
                if (out != null) {
                    out.close();
                }
                // 处理在磁盘上备份此工作簿的临时文件
                sxssfWorkbook.dispose();
            } catch (IOException e) {
                e.printStackTrace();
            }
        }
    }

大家会发现一个问题，代码都码完了，但是Excel中的字段如何和实体类里面的字段对应呢？？？

这时候就需要大家在实体类里面的需要对应的属性上面添加一个注解

//name里面的值和代码里面的汉字是对应的哦，不要写错了，不然就对应不上了。。。
@Excel(name = "计划编号（请不要删除列）")
private String planCode;