poi excel工具

导入excel返回List<List>
pom

		<!--处理2003 excel&ndash-->
		<dependency>
			<groupId>org.apache.poi</groupId>
			<artifactId>poi</artifactId>
			<version>3.15</version>
		</dependency>
		<!--处理2007 excel&ndash-->
 
		<dependency>
			<groupId>org.apache.poi</groupId>
			<artifactId>poi-ooxml</artifactId>
			<version>3.15</version>
		</dependency>

 /** 
    * @Description: excle转成list,指定表头几行
    * @Param:  
    * @return:   List<List<String>>
    * @Author: zzy 
    * @Date: 2019/10/15 
    */ 

    public static List<List<String>> getExcel(File file, Integer tableHead) {
        List<List<String>> list = new ArrayList<>();
        Workbook workbook = null;
        try {
            //得到Excel工作簿对象
            if (file.getName().endsWith("xlsx")) {
                workbook = new XSSFWorkbook(new FileInputStream(file));
            } else {
                workbook = new HSSFWorkbook(new FileInputStream(file));
            }
            //单元格辅助
            FormulaEvaluator evaluator = workbook.getCreationHelper().createFormulaEvaluator();
            DataFormatter formatter = new DataFormatter();

            //得到Excel工作表对象
            Sheet sheet = workbook.getSheetAt(0);
            //得到Excel工作表的行
            for (int i = tableHead; i <= sheet.getLastRowNum(); i++) {
                Row row = sheet.getRow(i);
                //第一列没有数据则跳过此行
                if (row.getCell(0) == null || !StringUtils.hasText(row.getCell(0).toString())) {
                    continue;
                }
                List<String> cells = new ArrayList<>();
                //得到Excel工作表指定行的单元格
                for (int j = 0; j <= row.getLastCellNum(); j++) {
                    Cell cell = row.getCell(j);
                    if (cell != null) {
                        cell.setCellStyle(null);
                        cells.add(formatter.formatCellValue(cell, evaluator).trim());
                    } else {
                        cells.add("");
                    }
                }
                list.add(cells);
            }
            return list;

        } catch (IOException e) {
            e.printStackTrace();
            return null;
        } finally {
            try {
                workbook.close();
            } catch (IOException ioException) {
                ioException.printStackTrace();
            }
        }
    }


  /**
     *
     * @param heads excel头
     * @param headProperties excel头属性（对应字段，顺序排列）
     * @param rows 导出的行(属性名：值)
     */
    public static byte[] exportExcel(String[] heads,String[] headProperties, List<Map> rows){

        HSSFWorkbook hssfWorkbook = new HSSFWorkbook();
        HSSFSheet sheet = hssfWorkbook.createSheet();
        HSSFRow headRow = sheet.createRow(0);
        for (int i = 0; i < heads.length; i++) {
            headRow.createCell(i).setCellValue(heads[i]);
        }
        for (int i = 0; i < rows.size(); i++) {
            HSSFRow row = sheet.createRow(i+1);
            Map<Object, Object> rowMap = rows.get(i);
            rowMap.forEach((key,value)->{
                //对应属性填对应位置
                for (int j = 0; j < headProperties.length; j++) {
                    if(headProperties[j].equals(key)){
                        row.createCell(j).setCellValue(value.toString());
                        return;
                    }
                }
            });
        }
        ByteArrayOutputStream os = new ByteArrayOutputStream();
        try {
            hssfWorkbook.write(os);
            os.flush();
            os.close();
            return os.toByteArray();
        } catch (IOException e) {
            throw new RuntimeException(e);
        }finally {
            try {
                hssfWorkbook.close();
            } catch (IOException ioException) {
                throw new RuntimeException(ioException);
            }
        }
    }