通用数据库导入导出excel

通用数据库导入导出excel

转载自：http://blog.sina.com.cn/programbus

需求：在实际J2EE项目中，经常遇到需要导入导出excel文件的情况

解决方案：利用java的annotation，自定义一个annotation ,在声明pojo的时候对需要操作字段的属性进行声明

然后，通用的处理类根据运行时，读取annotation 相关信息，解析

需要资源:poi3.6     jdk1.5以上

对于导入，读入是一个excel文件，输出一个list<pojo>，如果你用hibernate操作的话相当方便

对于导出，你需要组织一个List<pojo>传入，将得到一个excel

对于annotation可以自己扩展，也可以裁掉不需要的，excel文件合法性校验需要自己写

以下仅供参考：

1.自定义一个annotation

package com.huateng.common.excel.parser;

import java.lang.annotation.ElementType;

import java.lang.annotation.Retention;

import java.lang.annotation.RetentionPolicy;

import java.lang.annotation.Target;

@Retention(RetentionPolicy.RUNTIME)   

@Target(ElementType.FIELD)

public @interface Excel {

//导入时，对应数据库的字段 主要是用户区分每个字段，不能有annocation重名的

//导出时的列名   导出排序跟定义了annotation的字段的顺序有关 

public String exportName();

//导出时在excel中每个列的宽  单位为字符，一个汉字=2个字符 

//如 以列名列内容中较合适的长度   例如姓名列6 【姓名一般三个字】  性别列4【男女占1，但是列标题两个汉字】

//限制1-255

public int exportFieldWidth();

//导出时是否进行字段转换   例如 性别用int存储，导出时可能转换为男，女

//若是sign为1,则需要在pojo中加入一个方法 get字段名Convert()

//例如，字段sex ，需要加入 public String getSexConvert()  返回值为string

//若是sign为0,则不必管

public int exportConvertSign();

//导入数据是否需要转化       及 对已有的excel，是否需要将字段转为对应的数据

//若是sign为1,则需要在pojo中加入   voidset字段名Convert(String text)

public int importConvertSign();

}

2.声明对应的pojo，进行每个字段的注解

package com.huateng.test.pojo;

import java.text.SimpleDateFormat;

import java.util.Date;

import com.huateng.common.excel.parser.ConvertUtil;

import com.huateng.common.excel.parser.Excel;

public class Student {

@Excel(exportName="姓名",exportFieldWidth=18,exportConvertSign=0,importConvertSign=0)

private String name;

@Excel(exportName="年龄",exportFieldWidth=4,exportConvertSign=0,importConvertSign=0)

private Integer age;

@Excel(exportName="性别",exportFieldWidth=4,exportConvertSign=1,importConvertSign=1)

private Integer sex;

@Excel(exportName="出生日期",exportFieldWidth=20,exportConvertSign=1,importConvertSign=1)

private Date birthDate;

@Excel(exportName="描述",exportFieldWidth=30,exportConvertSign=0,importConvertSign=0)

private String desc;

@Excel(exportName="是否VIP",exportFieldWidth=7,exportConvertSign=1,importConvertSign=1)

private Boolean isVip;

//convertSign=1时必须加入转化方法   不加入会报错    返回值为String  用于翻译

//convertSign=0不需翻译，不用书写转化

//导入需要 set ,导出需要get

public String getSexConvert()

{

if(sex == 1)

{

return "男";

}else if(sex == 2){

return "女";

}else

{

return "";

}

}

public void setSexConvert(String text)

{

if("男".equals(text))

{

sex = 1;

}

if("女".equals(text))

{

sex = 2;

}

}

public String getBirthDateConvert()

{

if(birthDate == null)

{

return "";

}

returnConvertUtil.toDate14(birthDate); 

}

public void setBirthDateConvert(String text)

{

if(text != null &&!"".equals(text.trim()))

{

birthDate = ConvertUtil.string14toDate(text);

}

}

public String getIsVipConvert()

{

if(isVip == null)

{

return "";

}

if(isVip)

{

return "是";

}else{

return "否";

}

}

public void setIsVipConvert(String text)

{

if("是".equals(text))

{

isVip = true;

}

if("否".equals(text))

{

isVip = false;

}

}

//getter and setters

public String getName() {

return name;

}

public void setName(String name) {

this.name = name;

}

public Integer getAge() {

return age;

}

public void setAge(Integer age) {

this.age = age;

}

public Integer getSex() {

return sex;

}

public void setSex(Integer sex) {

this.sex = sex;

}

public String getDesc() {

return desc;

}

public void setDesc(String desc) {

this.desc = desc;

}

public Date getBirthDate() {

return birthDate;

}

public void setBirthDate(Date birthDate) {

this.birthDate = birthDate;

}

public Boolean getIsVip() {

return isVip;

}

public void setIsVip(Boolean isVip) {

this.isVip = isVip;

}

}

3.通用导出：

package com.huateng.common.excel.parser;

import java.io.FileOutputStream;

import java.io.OutputStream;

import java.lang.reflect.Field;

import java.lang.reflect.Method;

import java.util.ArrayList;

import java.util.Collection;

import java.util.Date;

import java.util.HashMap;

import java.util.Iterator;

import java.util.List;

import java.util.Map;

import org.apache.poi.hssf.usermodel.HSSFRichTextString;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;

import org.apache.poi.ss.usermodel.Cell;

import org.apache.poi.ss.usermodel.RichTextString;

import org.apache.poi.ss.usermodel.Row;

import org.apache.poi.ss.usermodel.Sheet;

import org.apache.poi.ss.usermodel.Workbook;

import com.huateng.test.pojo.Student;

public class ExcelExport2 {

public static void exportExcel(String title, ClasspojoClass,Collection dataSet,

OutputStream out) {

//使用userModel模式实现的，当excel文档出现10万级别的大数据文件可能导致OOM内存溢出

exportExcelInUserModel(title, pojoClass,dataSet,out);

//使用eventModel实现，可以一边读一边处理，效率较高，但是实现复杂，暂时未实现

}

private static void exportExcelInUserModel(String title, ClasspojoClass,Collection dataSet,

OutputStream out) {

try {

// 首先检查数据看是否是正确的

if (dataSet == null || dataSet.size()==0) {

throw new Exception("导出数据为空！");

}

if(title == null || out == null || pojoClass == null)

{

throw new Exception("传入参数不能为空！");

}

// 声明一个工作薄  

Workbook workbook = new HSSFWorkbook();

// 生成一个表格

Sheet sheet = workbook.createSheet(title);

// 标题

List<String> exportFieldTitle =new ArrayList<String>();

List<Integer> exportFieldWidth =new ArrayList<Integer>();

// 拿到所有列名，以及导出的字段的get方法

List<Method> methodObj = newArrayList<Method>();

Map<String,Method> convertMethod= new HashMap<String,Method>();

// 得到所有字段

Field fileds[] = pojoClass.getDeclaredFields();

// 遍历整个filed

for (int i = 0; i < fileds.length; i++) {

Field field = fileds[i];

Excel excel = field.getAnnotation(Excel.class);

// 如果设置了annottion

if (excel != null) {

// 添加到标题

exportFieldTitle.add(excel.exportName());

//添加标题的列宽

exportFieldWidth.add(excel.exportFieldWidth());

// 添加到需要导出的字段的方法

String fieldname = field.getName();

//System.out.println(i+"列宽"+excel.exportName()+""+excel.exportFieldWidth());

StringBuffer getMethodName = new StringBuffer("get");

getMethodName.append(fieldname.substring(0, 1)

.toUpperCase());

getMethodName.append(fieldname.substring(1));

Method getMethod =pojoClass.getMethod(getMethodName.toString(),

new Class[] {});

methodObj.add(getMethod);

if(excel.exportConvertSign()==1)

{

StringBuffer getConvertMethodName = newStringBuffer("get");

getConvertMethodName.append(fieldname.substring(0, 1)

.toUpperCase());

getConvertMethodName.append(fieldname.substring(1));

getConvertMethodName.append("Convert");

//System.out.println("convert:"+getConvertMethodName.toString());

Method getConvertMethod =pojoClass.getMethod(getConvertMethodName.toString(),

new Class[] {});

convertMethod.put(getMethodName.toString(),getConvertMethod);

}

}

}

int index = 0;

// 产生表格标题行

Row row = sheet.createRow(index);

for (int i = 0,exportFieldTitleSize = exportFieldTitle.size();i < exportFieldTitleSize; i++) {

Cell cell = row.createCell(i);

  //  cell.setCellStyle(style);

RichTextString text = new HSSFRichTextString(

exportFieldTitle.get(i));

cell.setCellValue(text);

}

//设置每行的列宽

for (int i = 0; i < exportFieldWidth.size();i++) {

//256=65280/255

sheet.setColumnWidth(i, 256*exportFieldWidth.get(i));

}

Iterator its = dataSet.iterator();

// 循环插入剩下的集合

while (its.hasNext()) {

// 从第二行开始写，第一行是标题

index++;

row = sheet.createRow(index);

Object t = its.next();

for (int k = 0, methodObjSize = methodObj.size(); k< methodObjSize; k++) {

Cell cell = row.createCell(k);

Method getMethod = methodObj.get(k);

Object value = null;

if(convertMethod.containsKey(getMethod.getName()))

{

Method cm = convertMethod.get(getMethod.getName());

value = cm.invoke(t, new Object[] {});

}else

{

value = getMethod.invoke(t, new Object[] {});

}

cell.setCellValue(value.toString());

}

}

workbook.write(out);

} catch (Exception e) {

e.printStackTrace();

}

}

public static void main(String[] args) throws Exception{

// 构造一个模拟的List来测试，实际使用时，这个集合用从数据库中查出来

Student pojo2 = new Student();

pojo2.setName("第一行数据");

pojo2.setAge(28);

pojo2.setSex(2);

pojo2.setDesc("abcdefghijklmnop");

pojo2.setBirthDate(new Date());

pojo2.setIsVip(true);

List list = new ArrayList();

list.add(pojo2);

for (int i = 0; i < 50000; i++) {

Student pojo = new Student();

pojo.setName("一二三四五六七八九");

pojo.setAge(22);

pojo.setSex(1);

pojo.setDesc("abcdefghijklmnop");

pojo.setBirthDate(new Date());

pojo.setIsVip(false);

list.add(pojo);

}

// 构造输出对象，可以从response输出，直接向用户提供下载

OutputStream out = newFileOutputStream("D:\\testOne.xls");

// 开始时间

Long l = System.currentTimeMillis();

// 注意

ExcelExport2 ex = new ExcelExport2();

//    

ex.exportExcel("测试",Student.class,list, out);

out.close();

// 结束时间

Long s = System.currentTimeMillis();

System.out.println("excel导出成功");

System.out.println("总共耗时：" + (s - l));

}

}

4.通用导入:

package com.huateng.common.excel.parser;

import java.io.File;

import java.io.FileInputStream;

import java.lang.reflect.Field;

import java.lang.reflect.Method;

import java.lang.reflect.Type;

import java.text.SimpleDateFormat;

import java.util.ArrayList;

import java.util.Collection;

import java.util.HashMap;

import java.util.Iterator;

import java.util.List;

import java.util.Map;

import org.apache.poi.hssf.usermodel.HSSFSheet;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;

import org.apache.poi.ss.usermodel.Cell;

import org.apache.poi.ss.usermodel.Row;

import com.huateng.test.pojo.Student;

public class ExcelImport2 {

   

      publicCollection importExcel(File file ,Class pojoClass,String...  pattern) {   

         

      Collection dist = new ArrayList();   

      try {   

              // 得到目标目标类的所有的字段列表   

              Field filed[] = pojoClass.getDeclaredFields();   

              // 将所有标有Annotation的字段，也就是允许导入数据的字段,放入到一个map中   

              Map<String,Method>fieldSetMap = newHashMap<String,Method>();   

             

              Map<String,Method>fieldSetConvertMap = newHashMap<String,Method>();

             

             

              // 循环读取所有字段   

              for (int i = 0; i < filed.length;i++) {    

                  Field f =filed[i];    

                 

                  //得到单个字段上的Annotation    

                  Excel excel= f.getAnnotation(Excel.class);   

                 

                  //如果标识了Annotationd的话    

                  if (excel!= null) {    

                      // 构造设置了Annotation的字段的Setter方法   

                      String fieldname =f.getName();    

                      String setMethodName = "set" 

                              +fieldname.substring(0, 1).toUpperCase()   

                              +fieldname.substring(1);    

                      // 构造调用的method，   

                      Method setMethod =pojoClass.getMethod(setMethodName,   

                              new Class[]{ f.getType() });    

                      //将这个method以Annotaion的名字为key来存入。 

                     

                      //对于重名将导致 覆盖失败，对于此处的限制需要

                      fieldSetMap.put(excel.exportName(), setMethod);   

                     

                      if(excel.importConvertSign()==1)

{

StringBuffer setConvertMethodName = newStringBuffer("set");

setConvertMethodName.append(fieldname.substring(0, 1)

.toUpperCase());

setConvertMethodName.append(fieldname.substring(1));

setConvertMethodName.append("Convert");

Method getConvertMethod =pojoClass.getMethod(setConvertMethodName.toString(),

new Class[] {String.class});

fieldSetConvertMap.put(excel.exportName(),getConvertMethod);

}

                 

                  }   

              }    

             

             

             

             

               

              // 将传入的File构造为FileInputStream;   

              FileInputStream in = new FileInputStream(file);   

              // // 得到工作表   

              HSSFWorkbook book = new HSSFWorkbook(in);   

              // // 得到第一页   

              HSSFSheet sheet = book.getSheetAt(0);   

              // // 得到第一面的所有行   

              Iterator<Row> row =sheet.rowIterator();    

   

               

              // 得到第一行，也就是标题行   

              Row title = row.next();   

              // 得到第一行的所有列   

              Iterator<Cell>cellTitle = title.cellIterator();   

              // 将标题的文字内容放入到一个map中。   

              Map titlemap = new HashMap();   

              // 从标题第一列开始   

              int i = 0;   

              // 循环标题所有的列   

              while (cellTitle.hasNext()) {   

                  Cell cell =cellTitle.next();    

                  Stringvalue = cell.getStringCellValue();   

                  titlemap.put(i, value);   

                  i = i + 1;   

              }    

             

               

              //用来格式化日期的DateFormat   

              SimpleDateFormat sf;   

              if(pattern.length<1)   

              {    

                  sf=newSimpleDateFormat("yyyy-MM-dd");      

              }    

              else  

                  sf=newSimpleDateFormat(pattern[0]); 

             

             

              while (row.hasNext()) {   

                  // 标题下的第一行   

                  Row rown =row.next();    

   

                  // 行的所有列   

                  Iterator<Cell>cellbody = rown.cellIterator(); 

                 

                  // 得到传入类的实例   

                  ObjecttObject = pojoClass.newInstance();  

                 

                  int k = 0;   

                  // 遍历一行的列   

                  while(cellbody.hasNext()) {    

                      Cell cell = cellbody.next();   

                      // 这里得到此列的对应的标题   

                      String titleString = (String)titlemap.get(k);    

                      //如果这一列的标题和类中的某一列的Annotation相同，那么则调用此类的的set方法，进行设值   

                      if(fieldSetMap.containsKey(titleString)) {  

                     

                          Method setMethod = (Method)fieldSetMap.get(titleString);   

                          //得到setter方法的参数   

                          Type[] ts = setMethod.getGenericParameterTypes ();   

                          //只要一个参数   

                          String xclass = ts[0].toString();   

                          //判断参数类型   

                          System.out.println("类型: "+xclass);

                         

                          if (fieldSetConvertMap.containsKey(titleString)){

fieldSetConvertMap.get(titleString).invoke(tObject,

cell.getStringCellValue());

} else {

if (xclass.equals("class java.lang.String")) {

setMethod.invoke(tObject, cell

.getStringCellValue());

}

else if (xclass.equals("class java.util.Date")) {

setMethod.invoke(tObject, cell

.getDateCellValue());

}

else if (xclass.equals("class java.lang.Boolean")) {

setMethod.invoke(tObject, cell

.getBooleanCellValue());

}

else if (xclass.equals("class java.lang.Integer")) {

setMethod.invoke(tObject, new Integer(cell

.getStringCellValue()));

}else if(xclass. equals("class java.lang.Long"))

{

setMethod.invoke(tObject,new Long(cell.getStringCellValue()));   

}

}

                      }   

                      // 下一列   

                      k = k + 1;   

                  }   

                  dist.add(tObject);   

              }    

          } catch (Exception e) {   

              e.printStackTrace();   

              return null;   

          }   

          return dist;   

      }   

   

      publicstatic void main(String[] args) {   

          ExcelImport2 test = newExcelImport2();    

          File file = newFile("D:\\testOne.xls");    

          Long befor =System.currentTimeMillis();   

          List<Student>result = (ArrayList) test.importExcel(file,Student.class);   

   

          Long after =System.currentTimeMillis();   

          System.out.println("此次操作共耗时："+ (after - befor) + "毫秒"); 

         

          for (int i = 0; i< result.size(); i++) {   

          Studenttestpojo=result.get(i);    

          System.out.println("导入的信息为："+testpojo.getName()+   

                  "--"+testpojo.getAge()+"-"+testpojo.getSex()+"--"+testpojo.getBirthDate()+"--"+testpojo.getDesc()+"--"+testpojo.getIsVip());   

          }   

   

          System.out.println("共转化为List的行数为：" +result.size());    

      }   

}