使用poi,运用泛型跟反射导入导出excel

import java.io.FileInputStream;

import java.io.FileNotFoundException; import java.io.IOException; import java.io.InputStream; import java.lang.reflect.Field; import java.lang.reflect.Method; import java.text.SimpleDateFormat; import java.util.ArrayList; import java.util.Date; import java.util.HashMap; import java.util.List; import java.util.Map;

import org.apache.poi.hssf.usermodel.HSSFCell; import org.apache.poi.hssf.usermodel.HSSFRow; import org.apache.poi.hssf.usermodel.HSSFSheet; import org.apache.poi.hssf.usermodel.HSSFWorkbook;

public class ExportDB { private static final SimpleDateFormat sdf=new SimpleDateFormat(\"yyyy-MM-dd\"); private Class entityClass; public List getAllByExcel(ClassentityClass,String excelPath,int start,int end){ this.entityClass=entityClass; Listlist=null; try { InputStream is=new FileInputStream(excelPath); try { HSSFWorkbook hsswork=new HSSFWorkbook(is); HSSFSheet sheet=hsswork.getSheetAt(0); int rowNum=sheet.getPhysicalNumberOfRows(); if(rowNum>0){ list=new ArrayList<>(); if(start<=0||start>=end||end>rowNum){ return null; } for(int i=start-1;i<=end-1;i++){ Map map=new HashMap<>(); HSSFRow row=sheet.getRow(i); int cellNum=row.getPhysicalNumberOfCells(); for(int j=0;jHSSFCell cell=row.getCell(j); if(cell!=null){ switch(cell.getCellType()){ case HSSFCell.CELL_TYPE_STRING: map.put(j, cell.getRichStringCellValue()); break; case HSSFCell.CELL_TYPE_NUMERIC: map.put(j, cell.getNumericCellValue()); break; case HSSFCell.CELL_TYPE_FORMULA: map.put(j, cell.getCellFormula()); break; } } } list.add(setT(map)); } } } catch (IOException e) { e.printStackTrace(); } } catch (FileNotFoundException e) { e.printStackTrace(); } return list; } @SuppressWarnings(\"unchecked\") public T setT(Mapmap){ try{ T tobj=entityClass.newInstance(); Class cl=tobj.getClass(); Field[] fields=cl.getDeclaredFields(); for(int i=0;i}else if(typeclass==Integer.class){ method.invoke(tobj,new Object[]{Integer.parseInt (map.get(i).toString().trim())}); }else if(typeclass==Double.class){ method.invoke(tobj, new Object[] {Double.parseDouble(map.get(i).toString().trim())}); }else if(typeclass==Float.class){ method.invoke(tobj, new Object[] {Float.parseFloat(map.get(i).toString().trim())}); }else if(typeclass==Date.class){ method.invoke(tobj, new Object[] {sdf.parse(map.get(i).toString())}); } } } return tobj; }catch(Exception e){ e.printStackTrace(); } return null; } }

2.将数据库中的数据导入到excel，主要代码如下：

package com.poi.util;

import java.io.IOException; import java.io.OutputStream; import java.lang.reflect.Field; import java.lang.reflect.Method; import java.text.SimpleDateFormat; import java.util.Collection; import java.util.Date; import java.util.Iterator;

import java.util.regex.Matcher; import java.util.regex.Pattern;

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;

public class ExportExcel {

private static final SimpleDateFormat sdf=new SimpleDateFormat(\"yyyy-MM-dd HH:mm\");

/** * 将数据导入到表格 * @param title 表格名称 * @param headers 表格列名数组 * @param dataset 数据集合 * @param out 输出流 * @param pattern 有其他格式的设定输出格式 */ @SuppressWarnings(\"unchecked\") public void exportExcel(String title,String []headers,Collection dataset,OutputStream out){ //生成一个工作薄 HSSFWorkbook workbook=new HSSFWorkbook(); //生成一个表格 HSSFSheet sheet=workbook.createSheet(title); //生成标题行 HSSFRow row=sheet.createRow(0); for(int i=0;iit=dataset.iterator(); int index=0; while(it.hasNext()){ index++; row=sheet.createRow(index); T t=it.next(); //利用反射，根据javabean属性的先后顺序，动态调用getXxx()方法得到属性值

Field[] fields=t.getClass().getDeclaredFields(); //根据属性名获取属性值 for(int i=0;iClass tCls = t.getClass();

try { Method getMethod=tCls.getMethod(getMethodName, Class[]{});

Object value=getMethod.invoke(t, new Object[]{}); String textValue=null; if(value instanceof Date){ Date date=(Date)value; textValue=sdf.format(date); }else{ textValue=value!=null?value.toString():\"\"; } if(textValue!=null){ //是数字当做double处理 Pattern p = Pattern.compile(\"^//d+(//.//d+)?$\"); Matcher matcher=p.matcher(textValue); if(matcher.matches()){ cell.setCellValue(Double.parseDouble(textValue)); }else{ HSSFRichTextString richString = HSSFRichTextString(textValue);

cell.setCellValue(richString); } } } catch (Exception e) { e.printStackTrace(); } } } try { workbook.write(out); } catch (IOException e) { e.printStackTrace(); } } }

new

new