java poi excel导出_excelexporttitlestyle-CSDN博客

直接上代码

0. pom依赖

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

1. 设置导出样式

//设置导出样式
public class ExcelExportTitleStyle extends AbstractExcelExportStyler implements IExcelExportStyler {
    public ExcelExportTitleStyle(Workbook workbook) {
        super.createStyles(workbook);
    }

    @Override
    public CellStyle getTitleStyle(short color) {
        CellStyle titleStyle = workbook.createCellStyle();
        // 自定义字体
        Font font = workbook.createFont();
        font.setColor(IndexedColors.WHITE1.getIndex());
        font.setBold(true);
        font.setFontName("宋体");
        titleStyle.setFont(font);

        // 自定义背景色
        titleStyle.setFillForegroundColor(IndexedColors.GREY_50_PERCENT.getIndex());
        titleStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);

        titleStyle.setBorderBottom(BorderStyle.THIN);
        titleStyle.setBorderTop(BorderStyle.THIN);
        titleStyle.setBorderLeft(BorderStyle.THIN);
        titleStyle.setBorderRight(BorderStyle.THIN);

        titleStyle.setAlignment(HorizontalAlignment.CENTER);
        titleStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        titleStyle.setWrapText(true);
        return titleStyle;
    }

    @Override
    public CellStyle stringSeptailStyle(Workbook workbook, boolean isWarp) {
        CellStyle style = workbook.createCellStyle();
        style.setAlignment(HorizontalAlignment.CENTER);
        style.setVerticalAlignment(VerticalAlignment.CENTER);
        style.setDataFormat(STRING_FORMAT);
        if (isWarp) {
            style.setWrapText(true);
        }
        return style;
    }

    @Override
    public CellStyle getHeaderStyle(short color) {
        CellStyle titleStyle = workbook.createCellStyle();
        Font font = workbook.createFont();
        font.setFontHeightInPoints((short) 12);
        titleStyle.setFont(font);
        titleStyle.setAlignment(HorizontalAlignment.CENTER);
        titleStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        return titleStyle;
    }

    @Override
    public CellStyle stringNoneStyle(Workbook workbook, boolean isWarp) {
        CellStyle style = workbook.createCellStyle();
        style.setAlignment(HorizontalAlignment.CENTER);
        style.setVerticalAlignment(VerticalAlignment.CENTER);
        style.setDataFormat(STRING_FORMAT);
        if (isWarp) {
            style.setWrapText(true);
        }
        return style;
    }
}

2. 导出util

//导出util
@Component
public class ExcelUtils {

    private static Logger logger = LoggerFactory.getLogger(ExcelUtils.class);
    /**
     * Excel导出-全列导出
     *
     * @param response      response
     * @param fileName      文件名
     * @param list          数据List
     * @param pojoClass     对象Class
     */
    public static void exportExcel(HttpServletResponse response, String fileName, Collection<?> list,
                                   Class<?> pojoClass) throws IOException {
        if (StringUtils.isBlank(fileName)) {
            //当前日期
            fileName = DateFormatUtils.format(new Date(), "yyyy-MM-dd HH:mm:ss");
        }
        ExportParams params =new ExportParams();

        Workbook workbook = ExcelExportUtil.exportExcel(params, pojoClass, list);
        response.setCharacterEncoding("UTF-8");
        response.setHeader("content-Type", "application/vnd.ms-excel");
        response.setHeader("Content-Disposition",
                "attachment;filename=" + URLEncoder.encode(fileName, "UTF-8") + ".xls");
        ServletOutputStream out = response.getOutputStream();
        workbook.write(out);
        out.flush();
    }

    /**
     * Excel导出-自定义列
     *
     * @param response      response
     * @param fileName      文件名
     * @param list          数据List
     * @param colList     自定义列
     */
    public static void exportExcel(HttpServletResponse response, String fileName, Collection<?> list,
                                   List<ExcelExportEntity> colList) throws IOException {
        if (StringUtils.isBlank(fileName)) {
            //当前日期
            fileName = DateFormatUtils.format(new Date(), "yyyy-MM-dd HH:mm:ss");
        }
        ExportParams params =new ExportParams();
        params.setStyle(ExcelExportTitleStyle.class);//导出表头样式
        Workbook workbook = ExcelExportUtil.exportExcel(params, colList, list);

        Sheet sheet = workbook.getSheetAt(0);
        for (int i = 0; i < colList.size(); i++) {
            sheet.autoSizeColumn(i);
//            String s = colList.get(i).getName();
//            sheet.setColumnWidth(i, sheet.getColumnWidth(i) * 10 / 10); // 中文宽度
//            sheet.setColumnWidth(i, s.getBytes().length*2*256); // 中文宽度
            //设置最小宽度，防止title字符过短导致内容折叠显示，体验不佳
            int minWidth = colList.get(i).getName().length() * 1800;
            if (sheet.getColumnWidth(i) < minWidth)
                sheet.setColumnWidth(i, minWidth); // 无数据额外处理
        }

        response.setCharacterEncoding("UTF-8");
        response.setHeader("content-Type", "application/vnd.ms-excel");
        response.setHeader("Content-Disposition",
                "attachment;filename=" + URLEncoder.encode(fileName, "UTF-8") + ".xls");
        ServletOutputStream out = response.getOutputStream();
        workbook.write(out);
        out.flush();
    }


    public static String toNull(Object obj){
         if(null!=obj&&!"".equals(obj)&&!"null".equalsIgnoreCase(String.valueOf(obj))&&!"undefined".equals(String.valueOf(obj).toLowerCase())){
            return String.valueOf(obj);
        }else{
            return "";
        }
    }

    /**
     * excel导入
     * @param inputstream
     * @param pojoClass
     * @param params
     * @param <T>
     * @return
     * @throws Exception
     */
    public static <T> ExcelImportResult<T> importExcel(InputStream inputstream, Class<?> pojoClass,
                                                       ImportParams params) throws Exception
    {
        try
        {


           return ExcelImportUtil.importExcelMore(inputstream, pojoClass, params);

        } catch (Exception e)
        {
            e.printStackTrace();
            logger.error("error{}", getErrorStackTrace(e));
            throw e;
        }
    }

    /**
     * 根据条件动态生成列信息
     **/
    private List<ExcelExportEntity> dynaCol(String name,String key) {
        List<ExcelExportEntity> colList = new ArrayList<ExcelExportEntity>();
        colList.add(new ExcelExportEntity("姓名", "realName"));
        return colList;
    }

	/**
     * 获取异常信息
     * @param ex  异常
     * @return    返回异常信息
     */
    public static String getErrorStackTrace(Exception ex){
        StringWriter sw = null;
        PrintWriter pw = null;
        try {
            sw = new StringWriter();
            pw = new PrintWriter(sw, true);
            ex.printStackTrace(pw);
        }finally {
            try {
                if(pw != null) {
                    pw.close();
                }
            } catch (Exception e) {

            }
            try {
                if(sw != null) {
                    sw.close();
                }
            } catch (IOException e) {

            }
        }

        return sw.toString();
    }
}

3.controller导出调用

@GetMapping("yljdjslistExport")
    @ApiOperation("xxx-导出")
    public Object yljdjslistExport(String vo, HttpServletResponse response,HttpServletRequest req) {
        try {
            YljdjsqksVO entity;
            if (StringUtils.isNotBlank(vo)) {//查询条件vo转实体类 
                entity = JSON.parseObject(vo, YljdjsqksVO.class);
            } else {
                entity = new YljdjsqksVO();
            }
            List<Record> list = xxxService.xxx方法; //获取数据列表
//            List<yljdjsqkExcel> list1 = new ArrayList<>(list.size());
//            if (list!=null && list.size()>0){
//                for (int i = 0; i < list.size(); i++) {
//                    yljdjsqkExcel excel = BeanUtilss.BeanCopys(list.get(i),yljdjsqkExcel.class);
//                    list1.add(excel);
//                }
//            }
//            ExcelUtils.exportExcelToTarget(response, null, list1, yljdjsqkExcel.class);
            List<ExcelExportEntity> colList = new ArrayList<ExcelExportEntity>();//导出自定义列
            colList.add(new ExcelExportEntity("企业名称", "companyName"));
            colList.add(new ExcelExportEntity("市", "city"));
            colList.add(new ExcelExportEntity("区/县", "area"));
            ExcelUtils.exportExcel(response, null, list, colList);
            return Message.success("导出成功！");
        }catch (Exception e){
            e.printStackTrace();
            return Message.error(e.getMessage());
        }
    }