easypoi 创建模板下载

雨点打透心脏的1/2处 2022-03-11 03:05 301阅读 0赞

1、引入easypoi包

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

2、创建poi工具类

public class PoiUtil \{

public static void exportExcel(List<?> list, String title, String sheetName, Class<?> pojoClass, String fileName,  
            boolean isCreateHeader, HttpServletResponse response) \{  
        ExportParams exportParams = new ExportParams(title, sheetName);  
        exportParams.setCreateHeadRows(isCreateHeader);  
        defaultExport(list, pojoClass, fileName, response, exportParams);

public static void exportExcel(List<?> list, String title, String sheetName, Class<?> pojoClass, String fileName,  
            HttpServletResponse response) \{  
        defaultExport(list, pojoClass, fileName, response, new ExportParams(title, sheetName));  
    \}

public static void exportExcel(List<Map<String, Object>> list, String fileName, HttpServletResponse response) \{  
        defaultExport(list, fileName, response);  
    \}

private static void defaultExport(List<?> list, Class<?> pojoClass, String fileName, HttpServletResponse response,  
            ExportParams exportParams) \{  
        Workbook workbook = ExcelExportUtil.exportExcel(exportParams, pojoClass, list);  
        if (workbook != null)  
            ;  
        downLoadExcel(fileName, response, workbook);  
    \}

private static void downLoadExcel(String fileName, HttpServletResponse response, Workbook workbook) \{  
        try \{  
            response.setCharacterEncoding("UTF-8");  
            response.setHeader("content-Type", "application/vnd.ms-excel");  
            response.setHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode(fileName, "UTF-8"));  
            workbook.write(response.getOutputStream());  
        \} catch (IOException e) \{  
            throw new NormalException(e.getMessage());  
        \}  
    \}

private static void defaultExport(List<Map<String, Object>> list, String fileName, HttpServletResponse response) \{  
        Workbook workbook = ExcelExportUtil.exportExcel(list, ExcelType.HSSF);  
        if (workbook != null)  
            ;  
        downLoadExcel(fileName, response, workbook);  
    \}

public static <T> List<T> importExcel(String filePath, Integer titleRows, Integer headerRows, Class<T> pojoClass) \{  
        if (StringUtils.isBlank(filePath)) \{  
            return null;  
        \}  
        ImportParams params = new ImportParams();  
        params.setTitleRows(titleRows);  
        params.setHeadRows(headerRows);  
        List<T> list = null;  
        try \{  
            list = ExcelImportUtil.importExcel(new File(filePath), pojoClass, params);  
        \} catch (NoSuchElementException e) \{  
            throw new NormalException("模板不能为空");  
        \} catch (Exception e) \{  
            e.printStackTrace();  
            throw new NormalException(e.getMessage());  
        \}  
        return list;  
    \}

public static <T> List<T> importExcel(MultipartFile file, Integer titleRows, Integer headerRows,  
            Class<T> pojoClass) \{  
        if (file == null) \{  
            return null;  
        \}  
        ImportParams params = new ImportParams();  
        params.setTitleRows(titleRows);  
        params.setHeadRows(headerRows);  
        List<T> list = null;  
        try \{  
            list = ExcelImportUtil.importExcel(file.getInputStream(), pojoClass, params);  
        \} catch (NoSuchElementException e) \{  
            throw new NormalException("excel文件不能为空");  
        \} catch (Exception e) \{  
            throw new NormalException(e.getMessage());  
        \}  
        return list;  
    \}

private static class NormalException extends RuntimeException \{

public NormalException(String msg) \{  
            super(msg);

\}  
    \}

3、创建表格样式工具类

public class ExcelStyleUtil implements IExcelExportStyler\{  
    private static final short STRING\_FORMAT = (short) BuiltinFormats.getBuiltinFormat("TEXT");  
    private static final short FONT\_SIZE\_TEN = 10;  
    private static final short FONT\_SIZE\_ELEVEN = 11;  
    private static final short FONT\_SIZE\_TWELVE = 12;  
    /\*\*  
     \* 大标题样式  
     \*/  
    private CellStyle headerStyle;  
    /\*\*  
     \* 每列标题样式  
     \*/  
    private CellStyle titleStyle;  
    /\*\*  
     \* 数据行样式  
     \*/  
    private CellStyle styles;

public ExcelStyleUtil(Workbook workbook) \{  
        this.init(workbook);  
    \}  
      
    /\*\*  
     \* 初始化样式  
     \*  
     \* @param workbook  
     \*/  
    private void init(Workbook workbook) \{  
        this.headerStyle = initHeaderStyle(workbook);  
        this.titleStyle = initTitleStyle(workbook);  
        this.styles = initStyles(workbook);  
    \}

@Override  
    public CellStyle getHeaderStyle(short headerColor) \{  
        // TODO Auto-generated method stub  
        return headerStyle;  
    \}

@Override  
    public CellStyle getTitleStyle(short color) \{  
        // TODO Auto-generated method stub  
        return titleStyle;  
    \}

@Override  
    public CellStyle getStyles(boolean parity, ExcelExportEntity entity) \{  
        // TODO Auto-generated method stub  
        return styles;  
    \}

@Override  
    public CellStyle getStyles(Cell cell, int dataRow, ExcelExportEntity entity, Object obj, Object data) \{  
        // TODO Auto-generated method stub  
        return getStyles(true, entity);  
    \}

@Override  
    public CellStyle getTemplateStyles(boolean isSingle, ExcelForEachParams excelForEachParams) \{  
        // TODO Auto-generated method stub  
        return null;  
    \}

/\*\*  
     \* 初始化--大标题样式  
     \*  
     \* @param workbook  
     \* @return  
     \*/  
    private CellStyle initHeaderStyle(Workbook workbook) \{  
        CellStyle style = getBaseCellStyle(workbook);  
        style.setFont(getFont(workbook, FONT\_SIZE\_TWELVE, true));  
        return style;  
    \}

/\*\*  
     \* 初始化--每列标题样式  
     \*  
     \* @param workbook  
     \* @return  
     \*/  
    private CellStyle initTitleStyle(Workbook workbook) \{  
        CellStyle style = getBaseCellStyle(workbook);  
        style.setFont(getFont(workbook, FONT\_SIZE\_ELEVEN, false));  
        //背景色  
        style.setFillForegroundColor(IndexedColors.GREY\_25\_PERCENT.getIndex());  
        style.setFillPattern(FillPatternType.SOLID\_FOREGROUND);  
        return style;  
    \}

/\*\*  
     \* 初始化--数据行样式  
     \*  
     \* @param workbook  
     \* @return  
     \*/  
    private CellStyle initStyles(Workbook workbook) \{  
        CellStyle style = getBaseCellStyle(workbook);  
        style.setFont(getFont(workbook, FONT\_SIZE\_TEN, false));  
        style.setDataFormat(STRING\_FORMAT);  
        return style;  
    \}

/\*\*  
     \* 基础样式  
     \*  
     \* @return  
     \*/  
    private CellStyle getBaseCellStyle(Workbook workbook) \{  
        CellStyle style = workbook.createCellStyle();  
        //下边框  
        style.setBorderBottom(BorderStyle.THIN);  
        //左边框  
        style.setBorderLeft(BorderStyle.THIN);  
        //上边框  
        style.setBorderTop(BorderStyle.THIN);  
        //右边框  
        style.setBorderRight(BorderStyle.THIN);  
        //水平居中  
        style.setAlignment(HorizontalAlignment.CENTER);  
        //上下居中  
        style.setVerticalAlignment(VerticalAlignment.CENTER);  
        //设置自动换行  
        style.setWrapText(true);  
        return style;  
    \}

/\*\*  
     \* 字体样式  
     \*  
     \* @param size   字体大小  
     \* @param isBold 是否加粗  
     \* @return  
     \*/  
    private Font getFont(Workbook workbook, short size, boolean isBold) \{  
        Font font = workbook.createFont();  
        //字体样式  
        font.setFontName("宋体");  
        //是否加粗  
        font.setBold(isBold);  
        //字体大小  
        font.setFontHeightInPoints(size);  
        return font;  
    \}  
\}

4、创建表格实体类每一个sheet是一个类（两个sheet）。

public class RtuExcelEntity \{

private String id;

@Excel(name = "RTU编号", orderNum = "1")  
    private int rtu\_no;

@Excel(name = "RTU名称", width=20, orderNum = "2")  
    private String rtu\_name;

public RtuExcelEntity(String id, int rtu\_no, String rtu\_name) \{  
        this.id = id;  
        this.rtu\_no = rtu\_no;  
        this.rtu\_name = rtu\_name;  
    \}

public RtuExcelEntity() \{  
        // TODO Auto-generated constructor stub  
    \}

public String getId() \{  
        return id;  
    \}

public void setId(String id) \{  
        this.id = id;  
    \}

public int getRtu\_no() \{  
        return rtu\_no;  
    \}

public void setRtu\_no(int rtu\_no) \{  
        this.rtu\_no = rtu\_no;  
    \}

public String getRtu\_name() \{  
        return rtu\_name;  
    \}

public void setRtu\_name(String rtu\_name) \{  
        this.rtu\_name = rtu\_name;  
    \}  
\}

public class YcParamExcelEntity \{  
      
    private long id;

@Excel(name="RTU名称",width=20,orderNum="0")  
    private String rtu\_name;  
      
    @Excel(name="RTU编号",orderNum="1")  
    private int rtu\_no;  
      
    @Excel(name = "遥测名称",width=20, orderNum = "2")  
    private String name;

@Excel(name = "遥测编号", orderNum = "3")  
    private int data\_no;

@Excel(name = "数据类型",width=15,replace = \{"累加遥测\_1", "普通遥测\_2"\}, orderNum = "4")  
    private int type;

@Excel(name = "系数", orderNum = "5")  
    private double factor;

@Excel(name = "基值", orderNum = "6")  
    private double base\_value;

public YcParamExcelEntity(long id, int rtu\_no, String name, String rtu\_name, int data\_no, int type, double factor,  
            double base\_value) \{  
        super();  
        this.id = id;  
        this.rtu\_no = rtu\_no;  
        this.name = name;  
        this.rtu\_name = rtu\_name;  
        this.data\_no = data\_no;  
        this.type = type;  
        this.factor = factor;  
        this.base\_value = base\_value;  
    \}

public YcParamExcelEntity() \{  
        super();  
        // TODO Auto-generated constructor stub  
    \}

public long getId() \{  
        return id;  
    \}

public void setId(long id) \{  
        this.id = id;  
    \}

public int getRtu\_no() \{  
        return rtu\_no;  
    \}

public void setRtu\_no(int rtu\_no) \{  
        this.rtu\_no = rtu\_no;  
    \}

public String getName() \{  
        return name;  
    \}

public void setName(String name) \{  
        this.name = name;  
    \}

public String getRtu\_name() \{  
        return rtu\_name;  
    \}

public void setRtu\_name(String rtu\_name) \{  
        this.rtu\_name = rtu\_name;  
    \}

public int getData\_no() \{  
        return data\_no;  
    \}

public void setData\_no(int data\_no) \{  
        this.data\_no = data\_no;  
    \}

public int getType() \{  
        return type;  
    \}

public void setType(int type) \{  
        this.type = type;  
    \}

public double getFactor() \{  
        return factor;  
    \}

public void setFactor(double factor) \{  
        this.factor = factor;  
    \}

public double getBase\_value() \{  
        return base\_value;  
    \}

public void setBase\_value(double base\_value) \{  
        this.base\_value = base\_value;  
    \}  
\}

5、实现导出

/\*\*  
     \* 模板下载  
     \* @param request  
     \* @param response  
     \*/  
    @RequestMapping("/downloadExcel")  
    public void downloadExcel(HttpServletRequest request, HttpServletResponse response) \{

//需要导出的数据列表。  
        List<RtuExcelEntity> list = new ArrayList<>();

//workbook（两个sheet）前两个参数是sheet页名称，后两个参数是对应的数据集合  
        Workbook workbook = getSheetsList("遥测参数","rtu参数",new ArrayList<>(),list );

//设置sheet中的单元格  
        Sheet sheet = workbook.getSheet("遥测参数");

//下拉列表单元格  
        String\[\] textlist = \{ "普通遥测", "累加遥测"\};  
        sheet = setHSSFValidation(sheet, textlist, 2, 2, 4, 4);

//单元格设置公式  
        sheet = setCellFormula(sheet,rtuList.size());

response.setHeader("Access-Control-Allow-Origin", "\*");  
        response.setHeader("Access-Control-Allow-Methods", "POST,GET,OPTIONS,DELETE");  
        response.setHeader("Access-Control-Max-Age", "3600");  
        response.setHeader("Access-Control-Allow-Headers", "Origin, X-Requested-With, Content-Type, Accept");  
        response.setContentType("application/x-msdownload");  
        String filedisplayName = "遥测参数列表.xls";  
        try \{  
            filedisplayName = URLEncoder.encode(filedisplayName, "UTF-8");  
        \} catch (UnsupportedEncodingException e1) \{  
            // TODO Auto-generated catch block  
            e1.printStackTrace();  
        \}  
        response.addHeader("Content-Disposition", "attachment;filename=" + filedisplayName);  
        try (OutputStream out = response.getOutputStream()) \{  
            workbook.write(out);  
        \} catch (Exception e) \{  
            e.printStackTrace();  
        \}

/\*\*  
     \* 设置某些列的值只能输入预制的数据,显示下拉框.  
     \* @param sheet 要设置的sheet.  
     \* @param textlist 下拉框显示的内容  
     \* @param firstRow 开始行  
     \* @param endRow 结束行  
     \* @param firstCol   开始列  
     \* @param endCol  结束列  
     \* @return 设置好的sheet.  
     \*/  
    public static Sheet setHSSFValidation(Sheet sheet,  
            String\[\] textlist, int firstRow, int endRow, int firstCol,  
            int endCol) \{  
        // 加载下拉列表内容  
        DVConstraint constraint = DVConstraint.createExplicitListConstraint(textlist);  
        // 设置数据有效性加载在哪个单元格上,四个参数分别是：起始行、终止行、起始列、终止列  
        CellRangeAddressList regions = new CellRangeAddressList(firstRow,endRow, firstCol, endCol);  
        // 数据有效性对象  
        HSSFDataValidation data\_validation\_list = new HSSFDataValidation(regions, constraint);  
        sheet.addValidationData(data\_validation\_list);  
        return sheet;

/\*\*  
     \* 为单元格设置公式  
     \* @param sheet  
     \* @param size  
     \* @return  
     \*/

public static Sheet setCellFormula(Sheet sheet,int size) \{  
        Row row = sheet.createRow(2);  
        Cell cell=row.createCell(0);  
        // Create POWER formula  
        cell.setCellType(HSSFCell.CELL\_TYPE\_FORMULA);   
        //行号加上表格名称行与表头行  
        size += 2;   
        String string = "LOOKUP(TEXT(B3,\\"0\\"),rtu参数!$A$3:$A$"+size+",rtu参数!$B$3:$B$"+size+")";  
        cell.setCellFormula(string);  
        return sheet;  
    \}