Java处理Excel导入的惯用技法

import java.lang.annotation.Retention;
import java.lang.annotation.RetentionPolicy;


@Retention(RetentionPolicy.RUNTIME)
public @interface ExcelField {

    /**
     * 属性的标题名称
     */
    String title();

    /**
     * 在excel的顺
     */
    int colum() default 9999;
    
    Class claz();
    
    int maxLength() ;

}

public class ExcelHeader implements Comparable
 
   {
    /**
     * excel的标题名称
     */
    private String title;
    /**
     * 每一个标题的顺
     */
    private int order;
    /**
     * 变量名
     */
    private String filed;
    

    public String getTitle() {
        return title;
    }

    public void setTitle(String title) {
        this.title = title;
    }

    public int getOrder() {
        return order;
    }

    public void setOrder(int order) {
        this.order = order;
    }

    public String getFiled() {
        return filed;
    }

    public void setFiled(String filed) {
        this.filed = filed;
    }


    public int compareTo(ExcelHeader o) {
        return order - o.order;
    }

    public ExcelHeader(String title, int order, String filed) {
        super();
        this.title = title;
        this.order = order;
        this.filed = filed;
        
    }

}
 

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

import java.lang.reflect.Field;
import java.util.*;


public class ImportExcelHeaderUtil {

    /**
     * 
根据JAVA对象注解获取Excel表头信息


     */
    public static List
 
   getHeaderList(Class clz) {
        List
  
    headers = new ArrayList<>(); List
   
     fields = new ArrayList<>(); for (Class clazz = clz; clazz != Object.class; clazz = clazz.getSuperclass()) { fields.addAll(Arrays.asList(clazz.getDeclaredFields())); } for (Field field : fields) { // 是否使用ExcelField注解 if (field.isAnnotationPresent(ExcelField.class)) { ExcelField er = field.getAnnotation(ExcelField.class); headers.add(new ExcelHeader(er.title(), er.order(), field.getName())); } } Collections.sort(headers); return headers; } public static Map
    
      getHeaderMap(Row titleRow, Class clz) { List
     
       headers = getHeaderList(clz); Map
      
        maps = new HashMap<>(); for (Cell c : titleRow) { String title = c.getStringCellValue(); for (ExcelHeader eh : headers) { if (eh.getTitle().equals(title.trim())) { maps.put(c.getColumnIndex(), eh); break; } } } return maps; } }
      
     
    
   
  
 

import org.apache.commons.beanutils.BeanUtils;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import java.io.*;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

public class ImportExcelUtil {

    private static final Logger LOGGER = LoggerFactory.getLogger(ImportExcelUtil.class);

    private final static String excel2003L =".xls";    //2003- 版本的excel
    private final static String excel2007U =".xlsx";   //2007+ 版本的excel



    /**
     * 描述：获取IO流中的数据，组装成List
 
  对象
     *      获取第0个sheet中,从0行到最后一行的数据,默认第0行为title
     * @param in    导入文件转换的流
     * @param fileName 文件名称
     * @param beanClass 转换成beanClass
     * @return
     * @throws IOException
     */
    public static 
  
    List
   
     getDefaultDataListByExcel(InputStream in,String fileName,Class
    
      beanClass) throws Exception{ return getDataListByExcel(in,fileName,beanClass,0,0,Integer.MAX_VALUE); } /** * 描述：获取IO流中的数据，组装成List
     
      对象 * 获取第sheetIndex个sheet中,从0行到最后一行 * @param in 导入文件转换的流 * @param fileName 文件名称 * @param beanClass 转换成beanClass * @param sheetIndex 要获取的sheet的index值 * @return * @throws IOException */ public static 
      
        List
       
         getFullDataListByExcel(InputStream in,String fileName,Class
        
          beanClass,int sheetIndex) throws Exception{ return getDataListByExcel(in,fileName,beanClass,sheetIndex,0,Integer.MAX_VALUE); } /** * 描述：获取IO流中的数据，组装成List
         
          对象 * @param in 导入文件转换的流 * @param fileName 文件名称 * @param beanClass 转换成beanClass * @param sheetIndex 要获取的sheet的index值 * @return * @throws IOException */ public static 
          
            List
           
             getDataListByExcel(InputStream in,String fileName,Class
            
              beanClass,int sheetIndex,int beginRowNum,int endRownum) throws Exception{ //创建Excel工作薄 Workbook work = getWorkbook(in,fileName); if(null == work){ throw new Exception("创建Excel工作薄为空！"); } //获取响应的sheet Sheet sheet = getSheet(work,sheetIndex); //获取sheet的表头 Row titleRow = getRow(sheet,beginRowNum); //返回的list
             
              结果 List
              
                list = getPartDataListByExcel(titleRow,sheet,beanClass,beginRowNum,endRownum); work.close(); return list; } public static 
               
                 List
                
                  getPartDataListByExcel(Row titleRow,Sheet sheet,Class
                 
                   beanClass,int beginRowNum,int endRownum) throws Exception{ //返回的list
                  
                   结果 List
                   
                     list = new ArrayList
                    
                     (); //获取导入文件对应的bean的标题map Map
                     
                       maps = ImportExcelHeaderUtil.getHeaderMap(titleRow, beanClass); //遍历当前sheet中的所有行 for (int j = beginRowNum+1; j <= endRownum; j++) { Row row = sheet.getRow(j); //新建一个T类 T bean = beanClass.newInstance(); //遍历所有的列 for (Cell cell : row) { int ci = cell.getColumnIndex(); ExcelHeader header = maps.get(ci); if (null == header) continue; String filed = header.getFiled(); Object value = getCellValue(cell); BeanUtils.copyProperty(bean, filed, value); } list.add(bean); } //work.close(); return list; } /** * 描述：根据sheetIndex,得到响应的sheet * @param work,fileName * @return Sheet * @throws Exception */ public static Sheet getSheet(Workbook work,int sheetIndex) throws Exception{ Sheet sheet = null; if(work.getNumberOfSheets() >= 0){ sheet = work.getSheetAt(sheetIndex); } return sheet; } /** * 描述：根据rowIndex,得到响应的row * @param sheet,rowIndex * @return Sheet * @throws Exception */ public static Row getRow(Sheet sheet,int rowIndex) throws Exception{ return sheet.getRow(rowIndex); } /** * 描述：根据文件后缀，自适应上传文件的版本 * @param inStr,fileName * @return * @throws Exception */ public static Workbook getWorkbook(InputStream inStr,String fileName) throws Exception{ Workbook wb = null; String fileType = fileName.substring(fileName.lastIndexOf(".")); if(excel2003L.equals(fileType)){ wb = new HSSFWorkbook(inStr); //2003- }else if(excel2007U.equals(fileType)){ wb = new XSSFWorkbook(inStr); //2007+ }else{ throw new Exception("解析的文件格式有误！"); } return wb; } /** * 描述：对表格中数值进行格式化 * @param cell * @return */ private static Object getCellValue(Cell cell){ Object value = null; DecimalFormat df = new DecimalFormat("0"); //格式化number String字符 SimpleDateFormat sdf = new SimpleDateFormat("yyy-MM-dd"); //日期格式化 DecimalFormat df2 = new DecimalFormat("0.00"); //格式化数字 switch (cell.getCellType()) { case Cell.CELL_TYPE_STRING://字符串型 value = cell.getRichStringCellValue().getString(); break; case Cell.CELL_TYPE_NUMERIC://日期或数字 if("General".equals(cell.getCellStyle().getDataFormatString())){ value = df.format(cell.getNumericCellValue()); }else if("m/d/yy".equals(cell.getCellStyle().getDataFormatString())){ value = sdf.format(cell.getDateCellValue()); }else{ value = df2.format(cell.getNumericCellValue()); } break; case Cell.CELL_TYPE_BOOLEAN://布尔型 value = cell.getBooleanCellValue(); break; case Cell.CELL_TYPE_BLANK://空字符串 value = ""; break; case Cell.CELL_TYPE_FORMULA://公式 CellStyle style = cell.getCellStyle(); value = cell.getNumericCellValue(); break; default: break; } return value; } public static void main(String[] args) throws FileNotFoundException { String url = "/usr/kaifeng/test.xlsx"; File file = new File(url); FileInputStream fileInputStream = new FileInputStream(file); try { List
                      
                        list = getDataListByExcel(fileInputStream,"redeem.xlsx", DataVo.class,0,0,2); } catch (Exception e) { e.printStackTrace(); } } }