java利用poi导出数据

public void export(HttpServletRequest request,HttpServletResponse response) {
        String title = "ERP应付发票";
        List<String> headers = Arrays.asList("发票编号 ","税率","发票金额","已付金额","已核销金额","发票余额","行金额","发票币种","发票描述","发票批名","合同号","供应商名称","供应商地点","公司","发票日期","入账日期","状态","入账状态");
        List<String> fields = Arrays.asList("FP_BH","SL","FPJE","YFJE","YXHJE","FPYE","HJE","FPBZ","FPMS","FPPM","HTH","GYS_MC","GYS_DD","OU_NAME","FPRQ","RZRQ","ZT","RZZT");
        String sql = "SELECT FP_BH,SL,FPJE,YFJE,YXHJE,FPYE,HJE,FPBZ,FPMS,FPPM,HTH,GYS_MC,GYS_DD,OU_NAME,FPRQ,RZRQ,ZT,RZZT FROM DB2ADMIN.T_MFT_INQUIRY_AP_INVOICE_DETAIL_SRV  where 1=1 ";
        List<Map<String,Object>> list = baseJdbcDao.queryForList(sql);
        XSSFWorkbook workbook = new ExportExcel("yyyy-MM-dd").exportExcel(title, headers, fields, list);
        try{
            response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
            response.setHeader("Content-Disposition", "attachment;fileName=" + URLEncoder.encode("ERP应付发票.xlsx", "UTF-8"));
            ServletOutputStream ouputStream = response.getOutputStream();
            workbook.write(ouputStream);
            ouputStream.flush();
            ouputStream.close();
        }catch(Exception e){
            e.printStackTrace();
        }
    }

?

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 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 207 208 209 210 211 212 213 214 215 216 217 218 219 220 221 222 223 224 225 226 227 228 229 230 231 232 233 234 235 236 237 238 239 240 241 242 243 244 245 246 247 248 249 250 251 252 253 254 255 256 257 258 259 260 261 262 263 264 265 266 267 268 269 270 271 272 273 274 275 276 277 278 279 280 281 282 283 284 285 286 287 288 289 290 291 292 293 294 295 296 297

package beff.util.excelTool;   import java.lang.reflect.InvocationTargetException; import java.lang.reflect.Method; import java.text.SimpleDateFormat; import java.util.Collection; import java.util.Date; import java.util.Iterator; import java.util.List; import java.util.Map; import java.util.regex.Matcher; import java.util.regex.Pattern;   import org.apache.commons.lang3.StringUtils; import org.apache.poi.hssf.usermodel.HSSFCell; import org.apache.poi.hssf.usermodel.HSSFRichTextString; import org.apache.poi.hssf.usermodel.HSSFRow; import org.apache.poi.hssf.usermodel.HSSFSheet; import org.apache.poi.hssf.usermodel.HSSFWorkbook; import org.apache.poi.xssf.usermodel.XSSFCell; import org.apache.poi.xssf.usermodel.XSSFRow; import org.apache.poi.xssf.usermodel.XSSFSheet; import org.apache.poi.xssf.usermodel.XSSFWorkbook;   public class ExportExcel {     //默认日期格式     private String pattern = "yyyy-MM-dd HH:mm:ss";           /**      * 此方法生成2003版本的excel,文件名后缀：xls      * @param title   表格标题      * @param headers 头部标题集合      * @param fields  属性名集合      * @param dataset excel表格需要显示的数据集合,类型为List<Map<String,Object>>      */     public HSSFWorkbook exportExcel2003(String title, List<String> headers, List<String> fields,List<Map<String,Object>> dataset){         // 创建一个工作簿对象         HSSFWorkbook workbook = new HSSFWorkbook();         // 创建一个表格对象         HSSFSheet sheet = workbook.createSheet(title);         // 设置表格默认列宽         sheet.setDefaultColumnWidth(20);         // 创建表格的标题行         HSSFRow row = sheet.createRow(0);         HSSFCell cellHeader;         for (int i = 0; i < headers.size(); i++) {             cellHeader = row.createCell(i);             cellHeader.setCellValue(new HSSFRichTextString(headers.get(i)));         }         // 使用迭代器遍历集合数据，创建数据行         Iterator<Map<String,Object>> it = dataset.iterator();         //正则表达式  用于校验是否是数字         Pattern p = Pattern.compile("^//d+(//.//d+)?$");         //创建一个日期格式化对象         SimpleDateFormat sdf = new SimpleDateFormat(pattern);         //对数据对象进行遍历         int index = 0;         while (it.hasNext()) {             index++;             //每一次循环创建一行（每一行对应着一个对象）             row = sheet.createRow(index);             Map<String,Object> m =  it.next();             for (int i = 0; i < fields.size(); i++) {                 //创建单元格（每个单元格对应着对象的每个属性）                 HSSFCell cell = row.createCell(i);                 Object value = m.get(fields.get(i));                 // 判断值的类型后进行强制类型转换                 String textValue = null;                 if (value instanceof Integer) {                     cell.setCellValue((Integer) value);                 } else if (value instanceof Float) {                     textValue = String.valueOf((Float) value);                     cell.setCellValue(textValue);                 } else if (value instanceof Double) {                     textValue = String.valueOf((Double) value);                     cell.setCellValue(textValue);                 } else if (value instanceof Long) {                     cell.setCellValue((Long) value);                 }else{                     if (value instanceof Boolean) {                         textValue = "是";                         if (!(Boolean) value) {                             textValue = "否";                         }                     } else if (value instanceof Date) {                         textValue = sdf.format((Date) value);                     } else {                         // 其它数据类型都当作字符串简单处理                         if (value!=null && !"".equals(value.toString().trim())) {                             textValue = value.toString();                         } else {                             textValue = "";                         }                     }                     Matcher  matcher = p.matcher(textValue);                     if (!StringUtils.isBlank(textValue)&&matcher.matches()) {                         // 如果是数字当作double处理                         cell.setCellValue(Double.parseDouble(textValue));                     } else {                         HSSFRichTextString richString = new HSSFRichTextString(textValue);                         cell.setCellValue(richString);                     }                 }             }         }         return workbook;     }                 /**      * 通用Excel导出方法,利用反射机制遍历对象的所有字段，将数据写入Excel文件中      * 此方法生成2003版本的excel,文件名后缀：xls      * @param title   表格标题      * @param headers 头部标题集合      * @param fields  属性名集合      * @param dataset excel表格需要显示的数据集合。      */     public <T> HSSFWorkbook exportExcel2003(String title, List<String> headers, List<String> fields, Collection<T> dataset) {         // 创建一个工作簿对象         HSSFWorkbook workbook = new HSSFWorkbook();         // 创建一个表格对象         HSSFSheet sheet = workbook.createSheet(title);         // 设置表格默认列宽         sheet.setDefaultColumnWidth(20);         // 创建表格的标题行         HSSFRow row = sheet.createRow(0);         HSSFCell cellHeader;         for (int i = 0; i < headers.size(); i++) {             cellHeader = row.createCell(i);             cellHeader.setCellValue(new HSSFRichTextString(headers.get(i)));         }         // 使用迭代器遍历集合数据，创建数据行         Iterator<T> it = dataset.iterator();         int index = 0;         //正则表达式  用于校验是否是数字         Pattern p = Pattern.compile("^//d+(//.//d+)?$");         //创建一个日期格式化对象         SimpleDateFormat sdf = new SimpleDateFormat(pattern);         //对数据对象进行遍历         while (it.hasNext()) {             index++;             //每一次循环创建一行（每一行对应着一个对象）             row = sheet.createRow(index);             T t = (T) it.next();             for (int i = 0; i < fields.size(); i++) {                 //创建单元格（每个单元格对应着对象的每个属性）                 HSSFCell cell = row.createCell(i);                 try {                     //获取属性名                     String fieldName = fields.get(i);                     //根据属性名拼接出对应的get方法名                     String getMethodName = "get" + fieldName.substring(0, 1).toUpperCase()                             + fieldName.substring(1);                     //获取对象的字节码对象                     Class<? extends Object> tCls = t.getClass();                     //获取对象的get方法                     Method getMethod = tCls.getMethod(getMethodName, new Class[]{});                     //执行get方法                     Object value = getMethod.invoke(t, new Object[]{});                     // 判断值的类型后进行强制类型转换                     String textValue = null;                     if (value instanceof Integer) {                         cell.setCellValue((Integer) value);                     } else if (value instanceof Float) {                         textValue = String.valueOf((Float) value);                         cell.setCellValue(textValue);                     } else if (value instanceof Double) {                         textValue = String.valueOf((Double) value);                         cell.setCellValue(textValue);                     } else if (value instanceof Long) {                         cell.setCellValue((Long) value);                     }else{                         if (value instanceof Boolean) {                             textValue = "是";                             if (!(Boolean) value) {                                 textValue = "否";                             }                         } else if (value instanceof Date) {                             textValue = sdf.format((Date) value);                         } else {                             // 其它数据类型都当作字符串简单处理                             if (value!=null && !"".equals(value.toString().trim())) {                                 textValue = value.toString();                             } else {                                 textValue = "";                             }                         }                         Matcher  matcher = p.matcher(textValue);                         if (!StringUtils.isBlank(textValue)&&matcher.matches()) {                             // 如果是数字当作double处理                             cell.setCellValue(Double.parseDouble(textValue));                         } else {                             HSSFRichTextString richString = new HSSFRichTextString(textValue);                             cell.setCellValue(richString);                         }                     }                 } catch (SecurityException e) {                     e.printStackTrace();                 } catch (NoSuchMethodException e) {                     e.printStackTrace();                 } catch (IllegalArgumentException e) {                     e.printStackTrace();                 } catch (IllegalAccessException e) {                     e.printStackTrace();                 } catch (InvocationTargetException e) {                     e.printStackTrace();                 }             }         }         return workbook;     }           public ExportExcel() {}       /**      * 自定义日期格式      * @param pattern 日期格式字符串      */     public ExportExcel(String pattern) {         this.pattern = pattern;     }         public XSSFWorkbook exportExcel(String title, List<String> headers, List<String> fields,List<Map<String,Object>> dataset){         // 创建一个工作簿对象         XSSFWorkbook workbook = new XSSFWorkbook();         // 创建一个表格对象         XSSFSheet sheet = workbook.createSheet(title);         // 设置表格默认列宽         sheet.setDefaultColumnWidth(20);         // 创建表格的标题行         XSSFRow row = sheet.createRow(0);         XSSFCell cellHeader;         for (int i = 0; i < headers.size(); i++) {             cellHeader = row.createCell(i);             cellHeader.setCellValue(headers.get(i));         }         // 使用迭代器遍历集合数据，创建数据行         Iterator<Map<String,Object>> it = dataset.iterator();         //正则表达式  用于校验是否是数字         Pattern p = Pattern.compile("^//d+(//.//d+)?$");         //创建一个日期格式化对象         SimpleDateFormat sdf = new SimpleDateFormat(pattern);         //对数据对象进行遍历         int index = 0;         while (it.hasNext()) {             index++;             //每一次循环创建一行（每一行对应着一个对象）             row = sheet.createRow(index);             Map<String,Object> m =  it.next();             for (int i = 0; i < fields.size(); i++) {                 //创建单元格（每个单元格对应着对象的每个属性）                 XSSFCell cell = row.createCell(i);                 Object value = m.get(fields.get(i));                 // 判断值的类型后进行强制类型转换                 String textValue = null;                 if (value instanceof Integer) {                     cell.setCellValue((Integer) value);                 } else if (value instanceof Float) {                     textValue = String.valueOf((Float) value);                     cell.setCellValue(textValue);                 } else if (value instanceof Double) {                     textValue = String.valueOf((Double) value);                     cell.setCellValue(textValue);                 } else if (value instanceof Long) {                     cell.setCellValue((Long) value);                 }else{                     if (value instanceof Boolean) {                         textValue = "是";                         if (!(Boolean) value) {                             textValue = "否";                         }                     } else if (value instanceof Date) {                         textValue = sdf.format((Date) value);                     } else {                         // 其它数据类型都当作字符串简单处理                         if (value!=null && !"".equals(value.toString().trim())) {                             textValue = value.toString();                         } else {                             textValue = "";                         }                     }                     Matcher  matcher = p.matcher(textValue);                     if (!StringUtils.isBlank(textValue)&&matcher.matches()) {                         // 如果是数字当作double处理                         cell.setCellValue(Double.parseDouble(textValue));                     } else {                         //HSSFRichTextString richString = new HSSFRichTextString(textValue);                         cell.setCellValue(textValue);                     }                 }             }         }         return workbook;     }       }