excel相同名称数据转java list对象

1. excel数据转java list对象

现在需要将excel中的数据转化为可用数据格式
原:

目标格式:

并且按照路段采点的顺序升序排列

2. 思路分析

1. 导入excel, 做数据处理, 将excel名称前缀字符串和后缀排序数字分开
2. 对excel数据分析, 相同名称排序不同用一个对象接受, 坐标用对象属性list接受
3. 对处理好的数据排序,并转二维数组

3. 导入excel

3.1 excel依赖(只写了关键依赖)

<!-- https://mvnrepository.com/artifact/commons-io/commons-io -->
        <!-- excel下载 -->
        <dependency>
            <groupId>commons-io</groupId>
            <artifactId>commons-io</artifactId>
            <version>2.4</version>
        </dependency>
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi</artifactId>
            <version>3.15</version>
        </dependency>
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml</artifactId>
            <version>3.15</version>
        </dependency>
        <!-- https://mvnrepository.com/artifact/com.sun.xml.messaging.saaj/saaj-impl -->
        <dependency>
            <groupId>com.sun.xml.messaging.saaj</groupId>
            <artifactId>saaj-impl</artifactId>
            <version>1.4.0</version>
        </dependency>

3.2 excel导入工具类ImportExcel

/**
 * @program: panda-prison
 * @description: excel读取类
 * @author: jiahao
 * @create: 2019-01-04 15:25
 **/
@Service("import")
public class ImportExcel {

    //总行数
    private int totalRows = 0;
    //总条数
    private int totalCells = 0;
    //错误信息接收器
    private StringBuilder errorMsg;

    //构造方法
    public ImportExcel() {
    }

    /**
     * @param mFile :  file
     * @description 读EXCEL文件，获取信息集合
     * @author jia hao
     * @Date 2019/8/7 12:09
     */
    public Map<String, Object> getExcelInfo(MultipartFile mFile) {
        errorMsg = new StringBuilder();
        Map<String, Object> map;
        //获取文件名
        String fileName = mFile.getOriginalFilename();
        System.out.println("fileName==>" + fileName);
        try {
            // 验证文件名是否合格
            if (!validateExcel(fileName)) {
                return null;
            }
            // 根据文件名判断文件是2003版本还是2007版本
            boolean isExcel2003 = true;
            if (isExcel2007(fileName)) {
                isExcel2003 = false;
            }
            map = createExcel(mFile.getInputStream(), isExcel2003);
        } catch (Exception e) {
            throw new BusinessException(BusCommonErrorCode.IMPORT_PRISONER_FORMAT_EXCEPTION);
        }
        map.put("errorMsg", errorMsg.toString());
        return map;
    }

    /**
     * @param is          :  输入流
     * @param isExcel2003 :  excel是2003还是2007版本
     * @description 根据excel里面的内容读取客户信息
     * @author jia hao
     * @Date 2019/8/7 12:09
     */
    public Map<String, Object> createExcel(InputStream is, boolean isExcel2003) {
        List<ExcelVO> list = new ArrayList<>();
        try {
            Workbook wb = null;
            // 当excel是2003时,创建excel2003
            if (isExcel2003) {
                wb = new HSSFWorkbook(is);
            } else {// 当excel是2007时,创建excel2007  
                wb = new XSSFWorkbook(is);
            }
            // 读取Excel里面客户的信息
            list = readExcelValue(wb);
        } catch (IOException e) {
            e.printStackTrace();
        }
        Map<String, Object> map = new HashMap<>();
        map.put("list", list);
        return map;
    }

    /**
     * 读取Excel里面的信息
     * @param wb
     * @return
     */
    private List<ExcelVO> readExcelValue(Workbook wb) {
        List<ExcelVO> list = new ArrayList<>();
        try {
            // 得到第一个shell
            Sheet sheet = wb.getSheetAt(0);
            // 得到Excel的行数 行数
            this.totalRows = sheet.getLastRowNum() + 1;
            //得到Excel的行数 列数
            this.totalCells = sheet.getRow(1).getPhysicalNumberOfCells();
            // 循环Excel行数
            for (int r = 1; r < totalRows; r++) {
                Row row = sheet.getRow(r);
                if (row != null) {
                    ExcelVO vo = new ExcelVO();
                    Integer sort = 1;
                    List<ExcelTraceVO> traceVOList = new ArrayList<>();
                    ExcelTraceVO trace = new ExcelTraceVO();
                    boolean isSameCellName = false;
                    // 循环Excel的列
                    for (int c = 1; c < this.totalCells + 1; c++) {
                        Cell cell = row.getCell(c - 1);
                        if (null != cell) {
                            switch (c) {
                                case 1:
                                    //名称
                                    String name = cell.getStringCellValue();
                                    Map<String, Object> map = ExcelStringUtil.get(name);
                                    if (map != null) {
                                        name = map.get("name").toString();
                                        if (CollectionUtils.isNotEmpty(list)) {
                                            for (ExcelVO vos : list) {
                                                if (vos.getName().equals(name)) {
                                                    vo = vos;
                                                    traceVOList = vos.getTraceVOList();
                                                    isSameCellName = true;
                                                    break;
                                                } else {
                                                    isSameCellName = false;
                                                }
                                            }
                                        }
                                        sort = Integer.valueOf(map.get("sort").toString());
                                    }
                                    vo.setName(name);
                                    break;
                                case 2:
                                    //纬度
                                    final Double lat = cell.getNumericCellValue();
                                    trace.setLat(lat);
                                    break;
                                case 3:
                                    //经度
                                    final Double lon = cell.getNumericCellValue();
                                    trace.setLon(lon);
                                    trace.setSort(sort);
                                    break;
                                default:
                                    break;
                            }
                        }
                    }
                    // 添加到list
                    traceVOList.add(trace);
                    if (!isSameCellName) {
                        vo.setTraceVOList(traceVOList);
                        list.add(vo);
                    }
                }
            }
        } catch (
                Exception e) {
            e.printStackTrace();
        }

        return list;
    }

    /**
     * 验证EXCEL文件
     * @param filePath
     * @return
     */
    public boolean validateExcel(String filePath) {
        if (filePath == null || !(isExcel2003(filePath) || isExcel2007(filePath))) {
            errorMsg = new StringBuilder().append("文件名不是excel格式");
            return false;
        }
        return true;
    }

    /**
     * 是否是2003的excel，返回true是2003
     * @param filePath
     * @return
     */
    public static boolean isExcel2003(String filePath) {
        return filePath.endsWith("xls");
    }

    /**
     * 是否是2007的excel，返回true是2007
     * @param filePath
     * @return
     */
    public static boolean isExcel2007(String filePath) {
        return filePath.endsWith("xlsx");
    }
}

3.3 字符串处理工具类ExcelStringUtil

/**
 * @program: YeeGuideServer
 * @description: excel字符串处理工具
 * @author: jiahao
 * @create: 2019-08-05 15:56
 **/
public class ExcelStringUtil {

    public static Map<String, Object> get(String word) {
        Map<String, Object> map = new HashMap<>();
        word = word.trim();
        String str;
        Integer num;
        int i = 1;
        boolean isEnd = true;
        while (isEnd) {
            String end = word.substring(word.length() - i, word.length());
            if (i == 1 && !StringUtils.isNumeric(end)) {
                return null;
            }
            if (StringUtils.isNumeric(end)) {
                isEnd = true;
                i++;
                continue;
            }
            isEnd = false;
            end = word.substring(word.length() - i + 1);
            num = Integer.valueOf(end);
            str = word.substring(0, word.length()- end.length());
            map.put("name", str);
            map.put("sort", num);
        }
        return map;
    }
}

4. excel接受对象

4.1 ExcelVO

@Builder
@AllArgsConstructor
@NoArgsConstructor
@Data
@JsonInclude(JsonInclude.Include.NON_NULL)
public class ExcelVO {

    /**
     * 名称
     */
    private String name;

    /**
     * 坐标二维数组
     */
    private Object [][] list;

    /**
     * 坐标list
     */
    private List<ExcelTraceVO> traceVOList;
}

4.2 ExcelTraceVO

@Builder
@AllArgsConstructor
@NoArgsConstructor
@Data
public class ExcelTraceVO {
    /**
     * 经度
     */
    private Double lon;

    /**
     * 纬度
     */
    private Double lat;

    /**
     * 排序
     */
    private Integer sort;
}

5. 排序,转二维数组

/**
 * @program: YeeGuideServer
 * @description:
 * @author: jiahao
 * @create: 2019-08-05 16:57
 **/
@Service
public class CommonService {

    @Autowired
    ImportExcel importExcel;

    /**
     * 路线段锚点
     *
     * @param file
     * @return
     */
    public List<ExcelVO> importExcel(MultipartFile file) {
        final Map<String, Object> excelInfo = importExcel.getExcelInfo(file);
        if (excelInfo != null) {
            final List<ExcelVO> list = (List<ExcelVO>) excelInfo.get("list");
            if (CollectionUtils.isNotEmpty(list)) {
                for (ExcelVO vo : list) {
                    List<ExcelTraceVO> voList = vo.getTraceVOList();
                    //数据排序
                    EntityListSort.sort(voList, "sort");
                    String[] variable = {"lon", "lat"};
                    //数据转二维数组
                    Object[][] o = EntityToArrayUtil.toArray(voList, variable);
                    vo.setList(o);
                    vo.setTraceVOList(null);
                }
                System.out.println(list);
                return list;
            }
        }
        return null;
    }
 }

其中数据排序和转二维数组所用的工具类我再其他文章已经介绍,本文不再赘述,下方传送门

//数据排序
EntityListSort.sort(voList, "sort");
String[] variable = {"lon", "lat"};
//数据转二维数组
Object[][] o = EntityToArrayUtil.toArray(voList, variable);

实体类list排序 工具类
list对象转二维数组 工具类

6. controller类

	/**
     * @param file :
     * @description  处理excel数据
     * @author  jia hao
     * @Date  2019/5/7 16:41
     */
    @PostMapping("import")
    public JsonData importExcel(@RequestParam(value = "file", required = false) MultipartFile file){
        final List<ExcelVO> list = commonService.importExcel(file);
        return JsonData.ok(list);
    }

7. 结果