实现一个配置简单功能强大的excel工具类搞定excel导入导出(二)

上篇文章留了个小悬念,您是否有这样的需求呢:
1.实体类中存放的值是一个编码,而导出的文件中需要把编码转成有意义的文字.例如:实体类中性别用0,1表示,而希望导出的excel文件中是"男","女".
2.想对导入的内容加一些逻辑,例如:判断某些值不能为空.或判断年龄不能小于0且不能大于100.

我们可以用一个中间类来实现这个功能.

请看代码:

(1)我们程序中真正的实体类:

package com.tgb.lk.test04;import java.util.Date;public class Student {private int id;private String name;private int sex;private int clazz;private Date birthday;private String company;public int getId() {return id;}   //get和set方法(略).....@Overridepublic String toString() {return "Student [birthday=" + birthday + ", clazz=" + clazz+ ", company=" + company + ", id=" + id + ", name=" + name+ ", sex=" + sex + "]";}}

(2)我们用于导入导出的excelVO类:

package com.tgb.lk.test04;import com.tgb.lk.util.ExcelVOAttribute;public class StudentVO {@ExcelVOAttribute(name = "姓名", column = "B", isExport = true, prompt = "姓名为必填项哦!")private String name;@ExcelVOAttribute(name = "性别", column = "C", combo = { "男", "女" })private String sex;@ExcelVOAttribute(name = "班级", column = "D", combo = { "五期提高班", "六期提高班","七期提高班" })private String clazz;@ExcelVOAttribute(name="生日",column="E")private String birthday;@ExcelVOAttribute(name = "公司", column = "F")private String company;public String getName() {return name;}public void setName(String name) {this.name = name;}public String getSex() {return sex;}public void setSex(String sex) {this.sex = sex;}public String getClazz() {return clazz;}public void setClazz(String clazz) {this.clazz = clazz;}public String getCompany() {return company;}public void setCompany(String company) {this.company = company;}public String getBirthday() {return birthday;}public void setBirthday(String birthday) {this.birthday = birthday;}@Overridepublic String toString() {return "StudentVO [birthday=" + birthday + ", clazz=" + clazz+ ", company=" + company + ", name=" + name + ", sex=" + sex+ "]";}}

(3)导出demo,注意convertStu2VO方法,这个方法中实现了我们导出时把编码转成有意义的文字功能.

package com.tgb.lk.test04;import java.io.FileNotFoundException;import java.io.FileOutputStream;import java.text.SimpleDateFormat;import java.util.ArrayList;import java.util.Date;import java.util.List;import com.tgb.lk.util.ExcelUtil;/* * 使用步骤: * 1.新建一个类,例如StudentVO. * 2.设置哪些属性需要导出,哪些需要设置提示. * 3.设置实体数据 * 4.调用exportExcel方法. * 本例向您提供以下问题的解决方案: * 1.实体对象存放的值需要转换为其他文字的情况,例如:实例中有0,1表示男,女;而导入导出的excel中是中文的"男","女". * 2.实体对象的时间类型处理. *  */public class ExportTest04 {public static void main(String[] args) {// 初始化数据List<Student> list = new ArrayList<Student>();Student stu = new Student();stu.setId(1);stu.setName("李坤");stu.setSex(0);stu.setClazz(5);stu.setCompany("天融信");stu.setBirthday(new Date());list.add(stu);Student stu2 = new Student();stu2.setId(2);stu2.setName("曹贵生");stu2.setSex(0);stu2.setClazz(5);stu2.setCompany("中银");list.add(stu2);Student stu3 = new Student();stu3.setId(3);stu3.setName("李学宇");stu3.setSex(1);stu3.setClazz(6);list.add(stu3);FileOutputStream out = null;try {out = new FileOutputStream("d:\\success4.xls");} catch (FileNotFoundException e) {e.printStackTrace();}ExcelUtil<StudentVO> util = new ExcelUtil<StudentVO>(StudentVO.class);util.exportExcel(convertStu2VO(list), "学生信息", 60000, out);System.out.println("----执行完毕----------");}//将student对象转换为studentVO用于导出.private static List<StudentVO> convertStu2VO(List<Student> list) {List<StudentVO> list2 = new ArrayList<StudentVO>();SimpleDateFormat sdf = new SimpleDateFormat("yyyy/M/d HH:mm:ss");for (int i = 0; i < list.size(); i++) {StudentVO stuVo = new StudentVO();Student stu = list.get(i);stuVo.setName(stu.getName());switch (stu.getSex()) {case 0:stuVo.setSex("男");break;case 1:stuVo.setSex("女");break;default:break;}//处理时间if (stu.getBirthday() != null) {stuVo.setBirthday(sdf.format(stu.getBirthday()));}switch (stu.getClazz()) {case 5:stuVo.setClazz("五期提高班");break;case 6:stuVo.setClazz("六期提高班");break;case 7:stuVo.setClazz("七期提高班");default:break;}list2.add(stuVo);}return list2;}}

(4)导入demo,在convertStu2VO这个方法中我们可以加一些逻辑控制.

package com.tgb.lk.test04;import java.io.FileInputStream;import java.io.FileNotFoundException;import java.text.ParseException;import java.text.SimpleDateFormat;import java.util.ArrayList;import java.util.List;import com.tgb.lk.util.ExcelUtil;public class ImportTest04 {public static void main(String[] args) {FileInputStream fis = null;try {fis = new FileInputStream("d:\\success4.xls");ExcelUtil<StudentVO> util = new ExcelUtil<StudentVO>(StudentVO.class);List<StudentVO> list = util.importExcel("学生信息", fis);System.out.println(list);//输出:[StudentVO [birthday=2012/9/22 15:44:10, clazz=五期提高班, company=null, name=李坤, sex=男], StudentVO [birthday=null, clazz=五期提高班, company=null, name=曹贵生, sex=男], StudentVO [birthday=null, clazz=六期提高班, company=null, name=李学宇, sex=女]]System.out.println(convertStu2VO(list));//输出:[Student [birthday=Sat Sep 22 15:44:10 CST 2012, clazz=5, company=null, id=0, name=李坤, sex=0], Student [birthday=null, clazz=5, company=null, id=0, name=曹贵生, sex=0], Student [birthday=null, clazz=6, company=null, id=0, name=李学宇, sex=1]]} catch (FileNotFoundException e) {e.printStackTrace();}}private static List<Student> convertStu2VO(List<StudentVO> list) {List<Student> retList = new ArrayList<Student>();SimpleDateFormat sdf = new SimpleDateFormat("yyyy/M/d HH:mm:ss");for (int i = 0; i < list.size(); i++) {StudentVO vo = list.get(i);Student student = new Student();student.setName(vo.getName());//在这个方法中还可以控制不允许为空,年龄值不能小于0后大于100等逻辑.String sex = vo.getSex();if (sex.equals("男")) {student.setSex(0);} else {student.setSex(1);}if (vo.getBirthday()!=null && !vo.getBirthday().trim().equals("")) {try {student.setBirthday(sdf.parse(vo.getBirthday()));} catch (ParseException e) {e.printStackTrace();}}String clazz = vo.getClazz();if (clazz != null) {if (clazz.equals("五期提高班")) {student.setClazz(5);} else if (clazz.equals("六期提高班")) {student.setClazz(6);} else if (clazz.equals("七期提高班")) {student.setClazz(7);} else {System.out.println("输入的数据不合法");}}retList.add(student);}return retList;}}

 

    代码下载: http://download.csdn.net/detail/lk_blog/4588280

 

实现一个配置简单功能强大的excel工具类搞定excel导入导出
http://blog.csdn.net/lk_blog/article/details/8007777
http://blog.csdn.net/lk_blog/article/details/8007837

    限于本人水平有限,很多地方写的并不完美,希望大家不吝赐教.如果觉得本文对您有帮助请顶支持一下,如果有不足之处欢迎留言交流,希望在和大家的交流中得到提高.