excel工具类记录

maven

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

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186

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;         }     } }

　　

使用方式 

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27

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);<br><br><br><br>public class ExcelRiskDataDTO implements Serializable {<br>    private static final long serialVersionUID = -8754505839853771317L;<br><br>    private String makeDate;<br>    private String channel;<br>    private String shop;<br>    private String qrcode;<br>    private String task1;<br>    private String task2;<br>    private String task3;<br>    private String task4;<br>    private String task5;<br>    private String task6;<br>    private String task7;<br>    private String task8;<br>    private String task9;<br>    private String task10;<br>    private String task11;<br>    private String task12;<br>    private String reason;<br><br>    public String getMakeDate() {<br>        return makeDate;<br>    }<br><br>    public void setMakeDate(String makeDate) {<br>        this.makeDate = makeDate;<br>    }<br><br>    public String getChannel() {<br>        return channel;<br>    }<br><br>    public void setChannel(String channel) {<br>        this.channel = channel;<br>    }<br><br>    public String getShop() {<br>        return shop;<br>    }<br><br>    public void setShop(String shop) {<br>        this.shop = shop;<br>    }<br><br>    public String getQrcode() {<br>        return qrcode;<br>    }<br><br>    public void setQrcode(String qrcode) {<br>        this.qrcode = qrcode;<br>    }<br><br>    public String getTask1() {<br>        return task1;<br>    }<br><br>    public void setTask1(String task1) {<br>        this.task1 = task1;<br>    }<br><br>    public String getTask2() {<br>        return task2;<br>    }<br><br>    public void setTask2(String task2) {<br>        this.task2 = task2;<br>    }<br><br>    public String getTask3() {<br>        return task3;<br>    }<br><br>    public void setTask3(String task3) {<br>        this.task3 = task3;<br>    }<br><br>    public String getTask4() {<br>        return task4;<br>    }<br><br>    public void setTask4(String task4) {<br>        this.task4 = task4;<br>    }<br><br>    public String getTask5() {<br>        return task5;<br>    }<br><br>    public void setTask5(String task5) {<br>        this.task5 = task5;<br>    }<br><br>    public String getTask6() {<br>        return task6;<br>    }<br><br>    public void setTask6(String task6) {<br>        this.task6 = task6;<br>    }<br><br>    public String getTask7() {<br>        return task7;<br>    }<br><br>    public void setTask7(String task7) {<br>        this.task7 = task7;<br>    }<br><br>    public String getTask8() {<br>        return task8;<br>    }<br><br>    public void setTask8(String task8) {<br>        this.task8 = task8;<br>    }<br><br>    public String getTask9() {<br>        return task9;<br>    }<br><br>    public void setTask9(String task9) {<br>        this.task9 = task9;<br>    }<br><br>    public String getTask10() {<br>        return task10;<br>    }<br><br>    public void setTask10(String task10) {<br>        this.task10 = task10;<br>    }<br><br>    public String getTask11() {<br>        return task11;<br>    }<br><br>    public void setTask11(String task11) {<br>        this.task11 = task11;<br>    }<br><br>    public String getTask12() {<br>        return task12;<br>    }<br><br>    public void setTask12(String task12) {<br>        this.task12 = task12;<br>    }<br><br>    public String getReason() {<br>        return reason;<br>    }<br><br>    public void setReason(String reason) {<br>        this.reason = reason;<br>    }<br>}