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

使用poi，利用泛型跟反射，从数据库中导入导出excel

1.将excel表中的信息导入到数据库，主要代码代码如下：

package com.poi.util;

import java.io.FileInputStream;

import java.io.FileNotFoundException;

import java.io.IOException;

import java.io.InputStream;

import https://www.360docs.net/doc/2d15549074.html,ng.reflect.Field;

import https://www.360docs.net/doc/2d15549074.html,ng.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 https://www.360docs.net/doc/2d15549074.html,ermodel.HSSFCell;

import https://www.360docs.net/doc/2d15549074.html,ermodel.HSSFRow;

import https://www.360docs.net/doc/2d15549074.html,ermodel.HSSFSheet;

import https://www.360docs.net/doc/2d15549074.html,ermodel.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;j

HSSFCell 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

if(map.get(i)!=null){

String propertyName=fields[i].getName();

String methodName="set"+propertyName.substring(0, 1).toUpperCase()+

propertyName.substring(1, propertyName.length());

Class typeclass=fields[i].getType();

Method method=cl.getMethod(methodName, new Class[]{typeclass});

if(typeclass==String.class){

method.invoke(tobj, new Object[]

{map.get(i).toString().trim()});

}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 https://www.360docs.net/doc/2d15549074.html,ng.reflect.Field;

import https://www.360docs.net/doc/2d15549074.html,ng.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 https://www.360docs.net/doc/2d15549074.html,ermodel.HSSFCell;

import https://www.360docs.net/doc/2d15549074.html,ermodel.HSSFRichTextString;

import https://www.360docs.net/doc/2d15549074.html,ermodel.HSSFRow;

import https://www.360docs.net/doc/2d15549074.html,ermodel.HSSFSheet;

import https://www.360docs.net/doc/2d15549074.html,ermodel.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;i

HSSFCell cell=row.createCell(i);

HSSFRichTextString text=new HSSFRichTextString(headers[i]);

cell.setCellValue(text);

}

//遍历集合，获取当中的数据

Iteratorit=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;i

HSSFCell cell=row.createCell(i);

Field field=fields[i];

//获取属性名

String fieldName=field.getName();

//获取属性值

String getMethodName="get"+fieldName.substring(0, 1).toUpperCase()+fieldName.substring(1);

Class tCls = t.getClass();

try {

Method getMethod=tCls.getMethod(getMethodName, new 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 = https://www.360docs.net/doc/2d15549074.html,pile("^//d+(//.//d+)?$");

Matcher matcher=p.matcher(textValue);

if(matcher.matches()){

cell.setCellValue(Double.parseDouble(textValue));

}else{

HSSFRichTextString richString = new HSSFRichTextString(textValue);

cell.setCellValue(richString);

}

}

} catch (Exception e) {

e.printStackTrace();

}

}

}

try {

workbook.write(out);

} catch (IOException e) {

e.printStackTrace();

}

}

}