POI generate Excel tool class

POI tool class

Encapsulation class implementation

public class GenerateExcelService<O> {

    private static final int CELL_WIDTH_DEFAULT = 20;
    private XSSFWorkbook workbook;

    /**
     * Initialize operation
     */
    public GenerateExcelService() {
        workbook = new XSSFWorkbook();
    }

    /**
     * Create sheet page
     *
     * @return sheet Page object
     */
    public XSSFSheet createSheet() {
        return workbook.createSheet();
    }

    public void write(OutputStream out) throws IOException {
        workbook.write(out);
    }

    /**
     * Create sheet page
     *
     * @param sheetName sheet Page name
     * @return sheet Page object
     */
    public XSSFSheet createSheet(String sheetName) {
        return workbook.createSheet(sheetName);
    }

    /**
     * Create a title style
     *
     * @return style
     */
    public XSSFCellStyle createTitleStyle() {
        return createTitleStyle(250);
    }

    /**
     * Create a title style
     *
     * @param height character height
     * @return style
     */
    public XSSFCellStyle createTitleStyle(int height) {
        XSSFFont boldFont = workbook.createFont();
        boldFont.setFontHeight((short) height);
        XSSFCellStyle style = workbook.createCellStyle();
        style.setFont(boldFont);
        style.setDataFormat(HSSFDataFormat.getBuiltinFormat("###,##0.00"));
        style.setAlignment(HorizontalAlignment.CENTER_SELECTION);
        return style;
    }

    /**
     * Header object
     *
     * @param sheet sheet page
     * @return Header object
     */
    public XSSFRow createTitleRow(XSSFSheet sheet) {
        return sheet.createRow(0);
    }

    /**
     * Insert a cell data
     *
     * @param row         Row object
     * @param sheet       sheet object
     * @param style       Style object
     * @param columnNum   Column number
     * @param columnValue Cell content
     * @param width       width
     */
    public void insertCell(XSSFSheet sheet, XSSFRow row, XSSFCellStyle style, int columnNum, String columnValue, int width) {
        Cell cell = row.createCell(columnNum);
        cell.setCellValue(columnValue);
        cell.setCellStyle(style);
        sheet.setColumnWidth(columnNum, (width * 256));  //Set column width, 50 characters wide
    }

    /**
     * Insert a cell data
     *
     * @param row         Row object
     * @param sheet       sheet object
     * @param columnNum   Column number
     * @param columnValue Cell content
     * @param width       width
     */
    public void insertCell(XSSFSheet sheet, XSSFRow row, int columnNum, String columnValue, int width) {
        Cell cell = row.createCell(columnNum);
        cell.setCellValue(columnValue);
        sheet.setColumnWidth(columnNum, (width * 256));  //Set column width, 50 characters wide
    }

    /**
     * Insert a cell data
     *
     * @param row         Row object
     * @param sheet       sheet object
     * @param columnNum   Column number
     * @param columnValue Cell content
     */
    public void insertCell(XSSFSheet sheet, XSSFRow row, int columnNum, String columnValue) {
        insertCell(sheet, row, columnNum, columnValue, 10);
    }

    /**
     * Insert a cell data with a default width of 10
     *
     * @param row         Row object
     * @param sheet       sheet object
     * @param style       Style object
     * @param columnNum   Column number
     * @param columnValue Cell content
     */
    public void insertCell(XSSFSheet sheet, XSSFRow row, XSSFCellStyle style, int columnNum, String columnValue) {
        insertCell(sheet, row, style, columnNum, columnValue, 10);
    }

    /**
     * Insert cell data
     *
     * @param sheet   sheet object
     * @param objList Data set
     */
    public void insertRow(XSSFSheet sheet, List<O> objList) {
        if (objList != null && !objList.isEmpty()) {

            AtomicInteger rowNum = new AtomicInteger(1);
            objList.forEach(obj -> {

                XSSFRow row = sheet.createRow(rowNum.get());
                // Get all properties of entity class and return the file array
                obj2Cell(obj, row);
                rowNum.getAndIncrement();
            });
        }
    }

    /**
     * Object data to a cell data
     *
     * @param obj data object
     * @param row Row data
     */
    private void obj2Cell(O obj, XSSFRow row) {

        Class<?> clazz = obj.getClass();
        Field[] field = clazz.getDeclaredFields();
        int columnIndex = 0;
        for (Field item : field) {

            // Property name, this field must not be empty
            String name = item.getName();
            // Capitalize the initial of the property name to construct the set/get function
            name = name.substring(0, 1).toUpperCase() + name.substring(1);
            // Get the type of property
            String type = item.getGenericType().toString();
            try {

                Method m = clazz.getMethod("get" + name);
                SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
                switch (type) {
                    case "class java.lang.String":
                        //Call getter method to get property value
                        String valueStr = (String) m.invoke(obj);
                        row.createCell(columnIndex, CellType.STRING).setCellValue(valueStr == null ? "" : valueStr);
                        break;

                    case "class java.lang.Integer":
                        //Call getter method to get property value
                        Integer valueInt = (Integer) m.invoke(obj);
                        row.createCell(columnIndex, CellType.STRING).setCellValue(valueInt == null ? 0 : valueInt);
                        break;

                    case "class java.lang.Short":
                        //Call getter method to get property value
                        Short valueShort = (Short) m.invoke(obj);
                        row.createCell(columnIndex, CellType.STRING).setCellValue(valueShort == null ? 0 : valueShort);
                        break;

                    case "class java.lang.Double":
                        //Call getter method to get property value
                        Double valueDouble = (Double) m.invoke(obj);
                        row.createCell(columnIndex, CellType.STRING).setCellValue(valueDouble == null ? 0.00 : valueDouble);
                        break;

                    case "class java.lang.Boolean":
                        //Call getter method to get property value
                        Boolean valueBoolean = (Boolean) m.invoke(obj);
                        row.createCell(columnIndex, CellType.STRING).setCellValue(valueBoolean == null ? false : valueBoolean);
                        break;

                    case "class java.util.Date":
                        //Call getter method to get property value
                        Date valueDate = (Date) m.invoke(obj);
                        String dateStr = "";
                        if (valueDate != null) {
                            dateStr = sdf.format(valueDate);
                        }
                        row.createCell(columnIndex, CellType.STRING).setCellValue(dateStr);
                        break;
                    default:
                        break;
                }

                columnIndex++;
            } catch (NoSuchMethodException | IllegalAccessException | InvocationTargetException e) {
                throw new IllegalArgumentException("No definition get function");
            }

        }
    }

    public void insertTitle(XSSFSheet sheet, XSSFRow row, XSSFCellStyle titleStyle, String[] titles, int[] widths) {
        if (titles.length > 0) {
            for (int i = 0; i < titles.length; i++) {
                if (widths.length >= i + 1) {
                    insertCell(sheet, row, titleStyle, i, titles[i], widths[i]);
                } else {
                    insertCell(sheet, row, titleStyle, i, titles[i], CELL_WIDTH_DEFAULT);
                }
            }
        }
    }
}

Use

List<Share> shareList = orderService.listOrders();

String filedisplay = "Clearing query.xlsx";
filedisplay = URLEncoder.encode(filedisplay, "UTF-8");
request.setCharacterEncoding("UTF-8");
response.setCharacterEncoding("UTF-8");
response.setContentType("application/x-download");
response.addHeader("Content-Disposition", "attachment;filename=" + filedisplay);

GenerateExcelService<Share> excelService = new GenerateExcelService<>();
XSSFSheet sheet = excelService.createSheet("Sorting query statistics(1)");
XSSFCellStyle titleStyle = excelService.createTitleStyle();
XSSFRow row = excelService.createTitleRow(sheet);

String[] titles = new String[]{"Order number", "Order family", "Payment amount", "Points", "Clearing amount", "Is it cleared?", "Payment time", "Clearing time"};
int[] widths = new int[]{30, 25, 10, 10, 10, 10, 20, 20};
excelService.insertTitle(sheet, row, titleStyle, titles, widths);

excelService.insertRow(sheet, shareList);

OutputStream out = response.getOutputStream();
excelService.write(out);
out.close();
  • First, query the data set according to your own business
  • Set request header information, Excel name, contentType, header and other information
  • To generate Excel, first instantiate GenerateExcelService, where Share can be replaced with any object structure that needs to be converted to Excel
  • Create a sheet page and specify the sheet name
  • Create the style of the first row (header) of the table (or not, so the header is the same as the list data format)
  • Get the first row (header) object
  • Set the field name displayed in the header and encapsulate it into an array
  • Set the width of each cell in the header. If it is not set, the default value is 20. It's better to keep the length of the width array consistent with that of the titles array. There's no problem if they are inconsistent, but the format may not be what you expect
  • Insert table
  • Turn the set you query into a list in Excel
  • Output data, download files

The data structure of Share is:

public class Share implements Serializable {
    private String orderNo;
    private String familyName;
    private Double payAmount;
    private Integer channelShare;
    private Double shareAmount;
    private Integer beShare;
    private Date payTime;
    private Date shareTime;

	// Setter... Getter omitted
}

In the insertRow method, this class automatically converts pojo of any format to Excel list data, which is completed by reflection, and does not need to be processed separately for each different object

Published 21 original articles, won praise 5, 20000 visitors+
Private letter follow

Tags: Java Excel

Posted on Wed, 15 Jan 2020 04:58:06 -0500 by samsunnyuk