excel工具类记录

maven

<dependency>
    <groupId>net.sourceforge.jexcelapi</groupId>
    <artifactId>jxl</artifactId>
    <version>2.6.12</version>
</dependency>

public class ExcelExportUtil<T> {

    public boolean exportCustomExcel(String fileName,
                                      List<T> list, Map<String,String> titleMap,
                                      HttpServletResponse response) throws Exception {

        fileName = fileName + DateUtils.formatDateYMD(new Date());
        response.setContentType("aplication/vnd.ms-excel");
        response.setCharacterEncoding("UTF-8");
        response.setHeader("Content-Disposition", "inline; filename="
                + new String(fileName.getBytes("gb2312"), "ISO8859-1") + ".xls");

        NumberFormat nf = new NumberFormat("#0"); // 设置数字格式
        WritableCellFormat wcfN = new WritableCellFormat(nf);

        try {
            // 创建Excel工作薄
            WritableWorkbook wwb = Workbook.createWorkbook(response
                    .getOutputStream());
            // 添加第一个工作表并设置第一个Sheet的名字
            WritableSheet sheet = wwb.createSheet(fileName, 0);
            Label label = null;
            boolean title = true;
            int rowNum = 1;
            Field fields[] = list.get(0).getClass().getDeclaredFields();
            List<Field> validFieldList = new ArrayList<Field>();
            for (int i = 0; i < fields.length; i++) {
                Field field = fields[i];
                String fieldName = field.getName();
                if (fieldName.equals(1)) {
                    continue;
                }
                if (!titleMap.containsKey(fieldName)) {
                    continue;
                }

                validFieldList.add(field);
            }

            for (T t : list) {
                // 填充数据
                for (int i = 0; i < validFieldList.size(); i++) {
                    Field field = validFieldList.get(i);
                    String fieldName = field.getName();
                    Object type = field.getType();
                    // 添加标题
                    if (title) {
                        fieldName =titleMap.get(fieldName);
                        label = new Label(i, 0, fieldName);
                        sheet.addCell(label);
                    }

                    field.setAccessible(true);
                    Object value = field.get(t);

                    if (value != null) {
                        if (type.toString().equals("class java.util.Date")) {
                            try {
                                label = new Label(i, rowNum, DateUtils.formatDateYMDHMS((Date) value));
                            } catch (Exception e) {
                                value = "";
                            }
                        }else if(value instanceof Integer
                                || value instanceof Long
                                || value instanceof Double
                                || value instanceof Short){
                            label = new Label(i, rowNum, value.toString(), wcfN);
                        }else if(fieldName.endsWith("price")||fieldName.endsWith("Price")){
                            label = new Label(i, rowNum, MathUtils.div(value + "","100"));
                        }else {
                            label = new Label(i, rowNum, value + "");
                        }
                        sheet.addCell(label);
                    }
                }

                title = false;
                rowNum++;
            }

            wwb.write();
            wwb.close();
            return true;
        } catch (Exception e) {
            e.printStackTrace();
            return false;
        }
    }


    public boolean exportRedExcel(String fileName,
                                     List<T> list, Map<String,String> titleMap,
                                     HttpServletResponse response) throws Exception {

        fileName = fileName + DateUtils.formatDateYMD(new Date());
        response.setContentType("aplication/vnd.ms-excel");
        response.setCharacterEncoding("UTF-8");
        response.setHeader("Content-Disposition", "inline; filename="
                + new String(fileName.getBytes("gb2312"), "ISO8859-1") + ".xls");

        NumberFormat nf = new NumberFormat("#0"); // 设置数字格式
        WritableFont wf = new WritableFont(WritableFont.TAHOMA);
        wf.setColour(Colour.RED);
        WritableCellFormat wcfN = new WritableCellFormat(nf);
        WritableCellFormat wcfR = new WritableCellFormat(wf);

        try {
            // 创建Excel工作薄
            WritableWorkbook wwb = Workbook.createWorkbook(response
                    .getOutputStream());
            // 添加第一个工作表并设置第一个Sheet的名字
            WritableSheet sheet = wwb.createSheet(fileName, 0);
            Label label = null;
            boolean title = true;
            int rowNum = 1;
            Field fields[] = list.get(0).getClass().getDeclaredFields();
            List<Field> validFieldList = new ArrayList<Field>();
            for (int i = 0; i < fields.length; i++) {
                Field field = fields[i];
                String fieldName = field.getName();
                if (fieldName.equals(1)) {
                    continue;
                }
                if (!titleMap.containsKey(fieldName)) {
                    continue;
                }

                validFieldList.add(field);
            }

            for (T t : list) {
                // 填充数据
                for (int i = 0; i < validFieldList.size(); i++) {
                    Field field = validFieldList.get(i);
                    String fieldName = field.getName();
                    Object type = field.getType();
                    // 添加标题
                    if (title) {
                        fieldName =titleMap.get(fieldName);
                        label = new Label(i, 0, fieldName);
                        sheet.addCell(label);
                    }

                    field.setAccessible(true);
                    Object value = field.get(t);

                    if (value != null) {
                        if (type.toString().equals("class java.util.Date")) {
                            try {
                                label = new Label(i, rowNum, DateUtils.formatDateYMDHMS((Date) value));
                            } catch (Exception e) {
                                value = "";
                            }
                        }else if(value instanceof Integer
                                || value instanceof Long
                                || value instanceof Double
                                || value instanceof Short){
                            label = new Label(i, rowNum, value.toString(), wcfN);
                        }else if(fieldName.endsWith("price")||fieldName.endsWith("Price")){
                            label = new Label(i, rowNum, MathUtils.div(value + "","100"));
                        }else {
                            if(value.toString().startsWith("red")){
                                String substring = value.toString().substring(3);
                                label = new Label(i, rowNum, substring ,wcfR);
                            }else{
                                label = new Label(i, rowNum, value + "");
                            }

                        }
                        sheet.addCell(label);
                    }
                }

                title = false;
                rowNum++;
            }

            wwb.write();
            wwb.close();
            return true;
        } catch (Exception e) {
            e.printStackTrace();
            return false;
        }
    }
}

　　

使用方式 

ExcelExportUtil<ExcelRiskDataDTO> excelUtil = new ExcelExportUtil<ExcelRiskDataDTO>();
        Map<String, String> titleMap = new HashMap<String, String>();
        titleMap.put("makeDate", "XX");
        titleMap.put("channel", "XX");
        titleMap.put("shop", "XX");
        titleMap.put("qrcode", "XX");
            titleMap.put("task1", "XXXX");
            titleMap.put("task2", "XXX");
            titleMap.put("task3", "XXXX");
            titleMap.put("task4", "XXXXX");
            titleMap.put("task5", "XXXX");
            titleMap.put("task6", "XXXX");
            titleMap.put("task7", "XXXX");
            titleMap.put("task8", "XXXX");
            titleMap.put("task9", "XXXX");
            titleMap.put("task10", "XXXX");
            titleMap.put("task11", "XXXX");
            titleMap.put("task12", "XXXX");
            titleMap.put("reason", "XXXX");
        }
        String fileName = "";
        if (tag == 1) {
            fileName = "预警";
        } else {
            fileName = "淘汰";
        }
        excelUtil.exportRedExcel(fileName, excelDTOList, titleMap, response);

public class ExcelRiskDataDTO implements Serializable {
    private static final long serialVersionUID = -8754505839853771317L;

    private String makeDate;
    private String channel;
    private String shop;
    private String qrcode;
    private String task1;
    private String task2;
    private String task3;
    private String task4;
    private String task5;
    private String task6;
    private String task7;
    private String task8;
    private String task9;
    private String task10;
    private String task11;
    private String task12;
    private String reason;

    public String getMakeDate() {
        return makeDate;
    }

    public void setMakeDate(String makeDate) {
        this.makeDate = makeDate;
    }

    public String getChannel() {
        return channel;
    }

    public void setChannel(String channel) {
        this.channel = channel;
    }

    public String getShop() {
        return shop;
    }

    public void setShop(String shop) {
        this.shop = shop;
    }

    public String getQrcode() {
        return qrcode;
    }

    public void setQrcode(String qrcode) {
        this.qrcode = qrcode;
    }

    public String getTask1() {
        return task1;
    }

    public void setTask1(String task1) {
        this.task1 = task1;
    }

    public String getTask2() {
        return task2;
    }

    public void setTask2(String task2) {
        this.task2 = task2;
    }

    public String getTask3() {
        return task3;
    }

    public void setTask3(String task3) {
        this.task3 = task3;
    }

    public String getTask4() {
        return task4;
    }

    public void setTask4(String task4) {
        this.task4 = task4;
    }

    public String getTask5() {
        return task5;
    }

    public void setTask5(String task5) {
        this.task5 = task5;
    }

    public String getTask6() {
        return task6;
    }

    public void setTask6(String task6) {
        this.task6 = task6;
    }

    public String getTask7() {
        return task7;
    }

    public void setTask7(String task7) {
        this.task7 = task7;
    }

    public String getTask8() {
        return task8;
    }

    public void setTask8(String task8) {
        this.task8 = task8;
    }

    public String getTask9() {
        return task9;
    }

    public void setTask9(String task9) {
        this.task9 = task9;
    }

    public String getTask10() {
        return task10;
    }

    public void setTask10(String task10) {
        this.task10 = task10;
    }

    public String getTask11() {
        return task11;
    }

    public void setTask11(String task11) {
        this.task11 = task11;
    }

    public String getTask12() {
        return task12;
    }

    public void setTask12(String task12) {
        this.task12 = task12;
    }

    public String getReason() {
        return reason;
    }

    public void setReason(String reason) {
        this.reason = reason;
    }
}