java使用POI处理EXCEL的核心类（二）

接上一篇写了使用poi导出excel，今天把读取excel的方法补上，核心类如下：

package cn.qazit.common.utils;

import java.io.ByteArrayOutputStream; import java.io.FileInputStream; import java.io.IOException; import java.io.InputStream; import java.lang.reflect.InvocationTargetException; import java.lang.reflect.Method; import java.util.ArrayList; import java.util.Iterator; import java.util.List;

import org.apache.poi.hssf.usermodel.HSSFCell; import org.apache.poi.hssf.usermodel.HSSFCellStyle; import org.apache.poi.hssf.usermodel.HSSFRow; 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 org.apache.poi.ss.usermodel.Sheet; import org.apache.poi.ss.usermodel.Workbook; import org.apache.poi.xssf.usermodel.XSSFWorkbook; import org.junit.internal.runners.statements.ExpectException;

import cn.qazit.app.core.charge.model.ZywsptGfxwjb; import cn.qazit.common.bean.ExportFiledsBean;

/**

• 导出的公共类
• @ClassName:ExportUtil.java
• @Title:ExportUtil.java
• @Description:
• @CopyRight:CopyRight(c)2016
• @Company:www.qazit.cn
• @author
• @date 2016年10月11日 下午7:14:05
• @version:v1.0 */ public class ExportUtil { /**
  • 导出的通用方法
  • @param sheet1
  • @param title
  • @param clazz
  • @param list
  • @return
  • @throws IOException
  • @throws ClassNotFoundException
  • @throws SecurityException
  • @throws NoSuchMethodException
  • @throws IllegalArgumentException
  • @throws Exception */ public static byte[] export(String sheet1, String title, Class<?> clazz,List<?> list) throws IOException, ClassNotFoundException, SecurityException, NoSuchMethodException, IllegalArgumentException, Exception{ ByteArrayOutputStream out = new ByteArrayOutputStream(); // 第一步，创建一个webbook，对应一个Excel文件 HSSFWorkbook wb = new HSSFWorkbook(); // 第二步，在webbook中添加一个sheet,对应Excel文件中的sheet HSSFSheet sheet = wb.createSheet(sheet1); // 第三步，在sheet中添加表头第0行,注意老版本poi对Excel的行数列数有限制short HSSFRow row = sheet.createRow((int) 0); // 第四步，创建单元格，并设置值表头 设置表头居中 HSSFCellStyle style = wb.createCellStyle(); style.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 创建一个居中格式 //设置表头 List<ExportFiledsBean> excelHead = getExcelHead(clazz); Class _class = createClazz(clazz); HSSFCell cell = null; // excel头 for (int i = 0; i < excelHead.size(); i++) { ExportFiledsBean exportFiledsBean = excelHead.get(i); cell = row.createCell(i); cell.setCellValue(exportFiledsBean.getName()); cell.setCellStyle(style); } for (int i = 0;i<list.size();i++) { row = sheet.createRow((int) i + 1); Object obj = (Object) list.get(i); for(int j=0;j<excelHead.size();j++) { ExportFiledsBean exportFiledsBean = excelHead.get(j); String getFuncName = exportFiledsBean.getGetFuncName(); Method addMethod = _class.getMethod(getFuncName); Object result = addMethod.invoke(obj); if(result==null) { row.createCell((int) j).setCellValue(""); }else{ row.createCell((int) j).setCellValue(result.toString()); } } } wb.write(out); // 第五步，写入实体数据 实际应用中这些数据从数据库得到 return out.toByteArray(); }

  private static List<ExportFiledsBean> getExcelHead(Class<?> clazz){ return AunotationUtil.getExportFileds(clazz); } private void insertCell(HSSFRow row,int i,Object object){ if(object==null){ row.createCell(i).setCellValue(""); }else{ row.createCell(i).setCellValue(object.toString()); } } /**

  • 读取excel
  • @param fileName
  • @param clazz
  • @return
  • @throws ClassNotFoundException
  • @throws SecurityException
  • @throws NoSuchMethodException
  • @throws IllegalArgumentException
  • @throws Exception
  • @throws InvocationTargetException */ public static List<?> readExport(String fileName,Class<?> clazz) throws ClassNotFoundException, SecurityException, NoSuchMethodException, IllegalArgumentException, Exception, InvocationTargetException{ List<Object> list = new ArrayList<Object>(); List<ExportFiledsBean> excelHead = getExcelHead(clazz); Class _class = createClazz(clazz); boolean isE2007 = false; //判断是否是excel2007格式 if(fileName.endsWith("xlsx")) isE2007 = true; try { InputStream input = new FileInputStream(fileName); //建立输入流 Workbook wb = creatWorkbook(isE2007,input); //根据文件格式(2003或者2007)来初始化 Sheet sheet = wb.getSheetAt(0); //获得第一个表单 Iterator<Row> rows = sheet.rowIterator(); //获得第一个表单的迭代器 while (rows.hasNext()) { Row row = rows.next(); //获得行数据 int rowNum = row.getRowNum(); if(rowNum>0) { Object obj = _class.newInstance(); Iterator<Cell> cells = row.cellIterator(); //获得第一行的迭代器 while (cells.hasNext()) { Cell cell = cells.next(); int cellColumnIndex = cell.getColumnIndex(); for(int i=0;i<excelHead.size();i++) { ExportFiledsBean exportFiledsBean = excelHead.get(i); int order = exportFiledsBean.getOrder(); if(order==cellColumnIndex) { String fitFuncName = exportFiledsBean.getFitFuncName(); obj = addObjctList(cell,fitFuncName,obj,_class); } } } list.add(obj); } } } catch (IOException ex) { ex.printStackTrace(); } return list; }

  private static Workbook creatWorkbook(boolean isE2007,InputStream input) throws IOException { Workbook wb = null; if(isE2007) wb = new XSSFWorkbook(input); else wb = new HSSFWorkbook(input); return wb; } /**

  • 使用反射机制创建对象
  • @param clazz
  • @return
  • @throws ClassNotFoundException */ private static Class createClazz(Class<?> clazz) throws ClassNotFoundException { String clazzName = clazz.getName(); Class _class = Class.forName(clazzName); return _class; }

  /**

  • 对象赋值
  • @param cell
  • @param addMethod
  • @param obj
  • @return
  • @throws IllegalArgumentException
  • @throws IllegalAccessException
  • @throws InvocationTargetException
  • @throws NoSuchMethodException
  • @throws SecurityException / private static Object addObjctList(Cell cell,String fitFuncName,Object obj,Class _class) throws IllegalArgumentException, IllegalAccessException, InvocationTargetException, SecurityException, NoSuchMethodException{ Method addMethod = null; switch (cell.getCellType()) { //根据cell中的类型来输出数据 case HSSFCell.CELL_TYPE_NUMERIC: addMethod = _class.getMethod(fitFuncName,new Class[]{double.class}); addMethod.invoke(obj,cell.getNumericCellValue()); break; case HSSFCell.CELL_TYPE_STRING: addMethod = _class.getMethod(fitFuncName,new Class[]{String.class}); addMethod.invoke(obj,cell.getStringCellValue()); break; case HSSFCell.CELL_TYPE_BOOLEAN: addMethod = _class.getMethod(fitFuncName,new Class[]{boolean.class}); addMethod.invoke(obj,cell.getBooleanCellValue()); break; case HSSFCell.CELL_TYPE_FORMULA: addMethod = _class.getMethod(fitFuncName,new Class[]{String.class}); addMethod.invoke(obj,cell.getCellFormula()); break; default: addMethod = _class.getMethod(fitFuncName,new Class[]{String.class}); addMethod.invoke(obj,""); break; } return obj; } public static void main(String[] args) throws SecurityException, IllegalArgumentException, ClassNotFoundException, NoSuchMethodException, InvocationTargetException, Exception { List<ZywsptGfxwjb> list = (List<ZywsptGfxwjb>) readExport("D:/05download/例子/职业病基础知识.xls", ZywsptGfxwjb.class); System.out.println("结果对象**"); for(ZywsptGfxwjb z:list) { System.out.println(z.getMc()); } }

}