Examples with Sheet com.alibaba.excel.metadata.Sheet used on opensource projects

Example 16 with Sheet

use of com.alibaba.excel.metadata.Sheet in project springboot by lbshold.

the class CoordinateExcelController method convertCoordinate.

/**
 * 1, 读取Excel中的坐标数据
 * 2. 遍历坐标数据，
 * a. 坐标数据格式转换,度分秒、度分转度
 * b. 坐标数据经纬度格式转换，WGS84转GCJ02
 */
private List<MyCoordinate> convertCoordinate(@RequestParam("file") MultipartFile file) throws IOException {
    // 读取Excel中的坐标数据
    ExcelListener listener = new ExcelListener();
    ExcelReader excelReader = new ExcelReader(file.getInputStream(), ExcelTypeEnum.XLSX, null, listener);
    excelReader.read(new Sheet(1, 1, MyCoordinate.class));
    List<MyCoordinate> datas = listener.getDatas();
    Pattern pattern = Pattern.compile(REGEX);
    Pattern pattern2 = Pattern.compile(REGEX2);
    Pattern pattern3 = Pattern.compile(REGEX3);
    List<MyCoordinate> result = new ArrayList<>();
    String lat;
    String lng;
    // 遍历坐标数据
    for (MyCoordinate myCoordinate : datas) {
        lat = myCoordinate.getLatitude();
        lng = myCoordinate.getLongitude();
        if (StringUtils.isEmpty(lat) || StringUtils.isEmpty(lng) || StringUtils.isEmpty(lat.trim()) || StringUtils.isEmpty(lng.trim())) {
            myCoordinate.setRemark("数据不能为空");
            myCoordinate.setIsSucceeded(FALSE);
            result.add(myCoordinate);
            continue;
        }
        lat = lat.trim();
        lng = lng.trim();
        if (pattern.matcher(lat).find() && pattern2.matcher(lng).find()) {
            myCoordinate.setLongitude(CoordinateFormatUtil.DmsTurnDD(lat));
            myCoordinate.setLatitude(CoordinateFormatUtil.DmsTurnDD(lng));
            myCoordinate.setIsSucceeded(TRUE);
            result.add(MyUitls.wgs84ToGcj02Copy(myCoordinate));
            continue;
        }
        if (pattern2.matcher(lat).find() && pattern2.matcher(lng).find()) {
            myCoordinate.setLongitude(CoordinateFormatUtil.DmTurnDD(lat));
            myCoordinate.setLatitude(CoordinateFormatUtil.DmTurnDD(lng));
            myCoordinate.setIsSucceeded(TRUE);
            result.add(MyUitls.wgs84ToGcj02Copy(myCoordinate));
            continue;
        }
        if (pattern3.matcher(lat).find() && pattern3.matcher(lng).find()) {
            myCoordinate.setIsSucceeded(TRUE);
            result.add(MyUitls.wgs84ToGcj02Copy(myCoordinate));
            continue;
        }
        myCoordinate.setIsSucceeded(FALSE);
        myCoordinate.setRemark("数据格式有误");
        result.add(myCoordinate);
    }
    return result;
}

Example 17 with Sheet

use of com.alibaba.excel.metadata.Sheet in project SpringBoot-Hello by ruiyeclub.

the class ExcelUtil method writeExcel.

/**
 * @param outputStream  Excel的输出流
 * @param sheetAndTable sheet和table名，格式：<sheet名，<table名集合>>
 * @param data          <sheet名，<table名，table数据集>>
 * @param clazz         <sheet名，<table名，table数据集实体class类型>>
 * @param excelTypeEnum Excel的格式(XLS或XLSX)
 * @Description: 使用模型来写入Excel，多sheet，多table
 * @Date: 2020/1/16 21:43
 * @Return: byte[]
 * @Throws: Exception
 */
public static byte[] writeExcel(ByteArrayOutputStream outputStream, Map<String, List<String>> sheetAndTable, Map<String, Map<String, List<? extends BaseRowModel>>> data, Map<String, Map<String, Class<? extends BaseRowModel>>> clazz, ExcelTypeEnum excelTypeEnum) throws Exception {
    // 这里指定需要表头，因为model通常包含表头信息
    ExcelWriter writer = new ExcelWriter(outputStream, excelTypeEnum, true);
    Iterator<Map.Entry<String, List<String>>> iterator = sheetAndTable.entrySet().iterator();
    int sheetNo = 1;
    // 遍历sheet
    while (iterator.hasNext()) {
        Map.Entry<String, List<String>> next = iterator.next();
        // 当前sheet名
        String sheetName = next.getKey();
        // 当前sheet对应的table的实体类class对象集合
        Map<String, Class<? extends BaseRowModel>> tableClasses = clazz.get(sheetName);
        // 当前sheet对应的table的数据集合
        Map<String, List<? extends BaseRowModel>> dataListMaps = data.get(sheetName);
        Sheet sheet = new Sheet(sheetNo, 0);
        sheet.setSheetName(sheetName);
        int tableNo = 1;
        Iterator<Map.Entry<String, Class<? extends BaseRowModel>>> iterator1 = tableClasses.entrySet().iterator();
        // 遍历table
        while (iterator1.hasNext()) {
            Map.Entry<String, Class<? extends BaseRowModel>> next1 = iterator1.next();
            // 当前table名
            String tableName = next1.getKey();
            // 当前table对应的class
            Class<? extends BaseRowModel> tableClass = next1.getValue();
            // 当前table对应的数据集
            List<? extends BaseRowModel> tableData = dataListMaps.get(tableName);
            Table table = new Table(tableNo);
            table.setClazz(tableClass);
            writer.write(tableData, sheet, table);
            tableNo++;
        }
        sheetNo++;
    }
    writer.finish();
    return outputStream.toByteArray();
}

Example 18 with Sheet

use of com.alibaba.excel.metadata.Sheet in project SpringBoot-Hello by ruiyeclub.

the class ExcelUtil method writeExcel.

/**
 * @param outputStream  Excel的输出流
 * @param data          要写入的以 模型 为单位的数据
 * @param clazz         模型的类
 * @param excelTypeEnum Excel的格式(XLS或XLSX)
 * @Description: 使用模型来写入Excel，单sheet，单table（返回字节数组）
 * @Date: 2020/1/16 21:43
 * @Return: byte[]
 * @Throws: Exception
 */
public static byte[] writeExcel(ByteArrayOutputStream outputStream, List<? extends BaseRowModel> data, Class<? extends BaseRowModel> clazz, ExcelTypeEnum excelTypeEnum) throws Exception {
    // 这里指定需要表头，因为model通常包含表头信息
    ExcelWriter writer = new ExcelWriter(outputStream, excelTypeEnum, true);
    // 写第一个sheet, sheet1  数据全是List<String> 无模型映射关系
    Sheet sheet1 = new Sheet(1, 0, clazz);
    writer.write(data, sheet1);
    writer.finish();
    return outputStream.toByteArray();
}