Excel导入导出工具类 POI

/**
 * Copyright (C), 2015-2018, XXX有限公司
* FileName: ExcelUtils
 * Author:   luohui
 * Date:     2018/9/12 15:31
 * Description: Excel工具类
* History:
 * <author>          <time>          <version>          <desc>
* 作者姓名           修改时间           版本号              描述
*/
package com.test.util.sys.excel;

import com.test.util.sys.Tools;
import org.apache.poi.hssf.usermodel.HSSFCell;
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.poifs.filesystem.POIFSFileSystem;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.lang.reflect.Field;
import java.lang.reflect.Method;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * 〈一句话功能简述〉<br> 
* 〈Excel工具类〉
*
 * @author luohui
 * @create 2018/9/12
 * @since 1.0.0
 */
public class ExcelUtils {
    /**
    * @方法概述 导入excel数据并映射到类
    * @param path 文件路径
    * @param sheetNumber 第几张表，默认从0开始为第一张表
    * @param mappingClass 要映射的类
    */
    public static <T> List<T> inputExcel(String path, int sheetNumber, Class<T> mappingClass) throws Exception{
        //存储读取的数据
        List<T>      result   = new ArrayList<>();
        HSSFWorkbook workbook = new HSSFWorkbook(new FileInputStream(path));
        HSSFSheet sheet       = workbook.getSheetAt(sheetNumber);

        // 获取数据总行数，编号是从0开始的
        int rowcount = sheet.getLastRowNum() + 1;
        if (rowcount < 1) {
            return result;
        }

        //获取类属性
        Field[] property = mappingClass.getDeclaredFields();
        Field.setAccessible(property, true);
        //逐行读取数据
        for (int i = 0; i < rowcount; i++) {
            //创建对象
            T bean = mappingClass.newInstance();
            // 获取行对象
            HSSFRow row = sheet.getRow(i + 1);
            if (row != null) {
                // 获取本行中单元格个数
                int column = row.getLastCellNum();
                // 获取本行中各单元格的数据
                for (int cindex = 0; cindex < column; cindex++) {
                    HSSFCell cell  = row.getCell(cindex);
                    // 获得指定单元格中的数据
                    Object cellstr = getCellString(cell);
                    if(i > property.length) {
                        break;
                    }
                    //获取属性类型
                    String attType = property[cindex].getGenericType().toString();
                    //判断属性类型
                    if("class java.lang.String".equals(attType)) {
                        property[cindex].set(bean, cellstr);
                    }
                    else if("int".equals(attType)) {
                        property[cindex].setInt(bean, Integer.parseInt(cellstr.toString()));
                    }
                    else if("double".equals(attType)) {
                        property[cindex].setDouble(bean, Double.parseDouble(cellstr.toString()));
                    }
                    else if("float".equals(attType)) {
                        property[cindex].setFloat(bean, Float.parseFloat(cellstr.toString()));
                    }
                    else if("long".equals(attType)) {
                        property[cindex].setLong(bean, Long.parseLong(cellstr.toString()));
                    }
                    else if("boolean".equals(attType)) {
                        property[cindex].setBoolean(bean, Boolean.parseBoolean(cellstr.toString()));
                    }
                    else if("class java.lang.Integer".equals(attType)) {
                        String method = alephUppercase( property[cindex].getName() );
                        Method set    = bean.getClass().getMethod(method,Integer.class );
                        int tempNum   = Integer.parseInt(cellstr.toString());
                        set.invoke(bean, tempNum);
                    }
                    else if("class java.lang.Double".equals(attType)) {
                        String method  = alephUppercase( property[cindex].getName() );
                        Method set     = bean.getClass().getMethod(method,Double.class );
                        double tempNum = Double.parseDouble(cellstr.toString());
                        set.invoke(bean, tempNum);
                    }
                    else if("class java.lang.Long".equals(attType)) {
                        String method = alephUppercase( property[cindex].getName() );
                        Method set    = bean.getClass().getMethod(method,Long.class );
                        long tempNum  = Long.parseLong(cellstr.toString());
                        set.invoke(bean, tempNum);
                    }
                }
                result.add(bean);
            }
        }
        return result;
    }

    /**
    * 导出Excel
    * @param <T>
    * @param sourceData 源数据
    * @param sheetName  表格别名
    * @param title      表格标题,如果为空默认为类属性
    * @param fieldList  字段集合
    * @param file       保存路径,包括文件名,如:F:/test.xls
    * @param isCover    false 追加工作表，true 覆盖工作表
    */
    public static File outputExcel(List sourceData, String sheetName, String[] titles, String[] fieldList, File file, boolean isCover) {
        if(file == null || Tools.isEmpty(sourceData) || titles == null || titles.length == 0 || fieldList == null || fieldList.length == 0) {
            return null;
        }

        FileOutputStream fos = null;
        FileInputStream  fis = null;
        HSSFWorkbook book;
        HSSFSheet sheet;
        HSSFRow hssfrow;
        HSSFCell hssfcell;

        try {
            if(file.exists()) {
                //是否覆盖原有数据,还是在原基础上创建一张新的工作表
                if(isCover) {
                    book = new HSSFWorkbook();// 所有execl的父节点
                } else {
                    fis  = new FileInputStream(file);
                    POIFSFileSystem poiFile = new POIFSFileSystem(fis);
                    book = new HSSFWorkbook(poiFile);
                }
            } else{
                //如果文件不存在,直接创建一个新的文件,而不去读取
                book = new HSSFWorkbook();// 所有execl的父节点
            }

            //-----------------------------------------------------------
            sheet   = book.createSheet( sheetName == null ? "sheet1" : sheetName);// 此处可以随意设置
            hssfrow = sheet.createRow(0); // 创建首行标题

            //-----------------------------------------------------------
            //获取参数中集合对象的字段
            Field[] fields = sourceData.get(0).getClass().getDeclaredFields();
            Field.setAccessible(fields, true);

            //字段转成Map集合
            Map<String, Field> fieldMap = new HashMap<>();
            for (int i = 0; i < fields. length; i++) {
                fieldMap.put(fields[i].getName(), fields[i]);
            }

            //-----------------------------------------------------------
            for (int i = 0; i < titles. length; i++) { // 创建标题栏目，也就是表格第一行
                hssfcell = hssfrow.createCell(i);
                hssfcell.setCellType (HSSFCell.ENCODING_UTF_16);
                hssfcell.setCellValue(titles[i]);
            }

            //-----------------------------------------------------------
            for (int i = 0; i < sourceData.size(); i++) { // 添加表格中的内容
                hssfrow = sheet.createRow(i + 1); // 创建表格第二行，由于标记为0,这里设置为一，主要为了区别标题和内容
                Object bean = sourceData.get(i);
                for(int f = 0; f < fieldList.length; f++) {
                    Field field = fieldMap.get(fieldList[f]);
                    if(field == null) {
                        continue;
                    }
                    hssfcell = hssfrow.createCell(f);
                    hssfcell.setCellType (HSSFCell.ENCODING_UTF_16); // 关于数据编码的问题
                    //获取对象属性值
                    Object value = field.get(bean);
                    if(value == null) {
                        value = "";
                    }
                    hssfcell.setCellValue(value + ""); // 转换为字符串的方式
                }
            }

            fos = new FileOutputStream(file);
            book.write(fos);
            fos.flush();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if(fis != null) {
                try {
                    fis.close();
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }
            if(fos != null) {
                try {
                    fos.close();
                } catch (Exception e) {
                    e.printStackTrace();
                }
            }
        }
        return file;
    }

    /**
    * @方法概述 获取单元格中的内容 ,该犯法用于解析各种形式的数据
    * @param cell
    */
    private static Object getCellString( HSSFCell cell) {
        Object result = null;
        if (cell != null) {
            int cellType = cell.getCellType();
            switch (cellType) {
                case HSSFCell.CELL_TYPE_STRING:
                    result = cell.getRichStringCellValue().getString();
                    break;
                case HSSFCell.CELL_TYPE_NUMERIC:
                    result = cell.getNumericCellValue();
                    break;
                case HSSFCell.CELL_TYPE_FORMULA:
                    result = cell.getNumericCellValue();
                    break;
                case HSSFCell.CELL_TYPE_ERROR:
                    result = null;
                    break;
                case HSSFCell.CELL_TYPE_BOOLEAN:
                    result = cell.getBooleanCellValue();
                    break;
                case HSSFCell.CELL_TYPE_BLANK:
                    result = null;
                    break;
                default:
                    break;
            }
        }
        return result;
    }

    private static String alephUppercase(String name) {
        String tmp = name.trim().intern();
        if (Tools.isEmpty(tmp))
            return "";
        if (tmp.length() < 2) {
            return "set" + name.toUpperCase();
        } else {
            return "set" + name.substring(0, 1).toUpperCase() + name.substring(1);
        }
    }
}