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中加入 void set字段名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 "";
- }
- return ConvertUtil.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, Class pojoClass,Collection dataSet,
- OutputStream out) {
- //使用userModel模式实现的,当excel文档出现10万级别的大数据文件可能导致OOM内存溢出
- exportExcelInUserModel(title, pojoClass,dataSet,out);
- //使用eventModel实现,可以一边读一边处理,效率较高,但是实现复杂,暂时未实现
- }
- private static void exportExcelInUserModel(String title, Class pojoClass,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 = new ArrayList<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 = new StringBuffer("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 = new FileOutputStream("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 {
- public Collection importExcel(File file ,Class pojoClass,String... pattern) {
- Collection dist = new ArrayList();
- try {
- // 得到目标目标类的所有的字段列表
- Field filed[] = pojoClass.getDeclaredFields();
- // 将所有标有Annotation的字段,也就是允许导入数据的字段,放入到一个map中
- Map<String,Method> fieldSetMap = new HashMap<String,Method>();
- Map<String,Method> fieldSetConvertMap = new HashMap<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 = new StringBuffer("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();
- String value = cell.getStringCellValue();
- titlemap.put(i, value);
- i = i + 1;
- }
- //用来格式化日期的DateFormat
- SimpleDateFormat sf;
- if(pattern.length<1)
- {
- sf=new SimpleDateFormat("yyyy-MM-dd");
- }
- else
- sf=new SimpleDateFormat(pattern[0]);
- while (row.hasNext()) {
- // 标题下的第一行
- Row rown = row.next();
- // 行的所有列
- Iterator<Cell> cellbody = rown.cellIterator();
- // 得到传入类的实例
- Object tObject = 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;
- }
- public static void main(String[] args) {
- ExcelImport2 test = new ExcelImport2();
- File file = new File("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++) {
- Student testpojo=result.get(i);
- System.out.println("导入的信息为:"+testpojo.getName()+
- "--"+testpojo.getAge()+"-"+testpojo.getSex()+"--"+testpojo.getBirthDate()+"--"+testpojo.getDesc()+"--"+testpojo.getIsVip());
- }
- System.out.println("共转化为List的行数为:" + result.size());
- }
- }