java 利用poi对Excel解析读取和写入,解析resources下的.json文件

首先在pom.xml中引入所需要的依赖

 <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi</artifactId>
            <version>4.1.2</version>
        </dependency>
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml</artifactId>
            <version>4.1.2</version>
        </dependency>
        <!-- https://mvnrepository.com/artifact/com.alibaba/fastjson -->
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>fastjson</artifactId>
            <version>1.2.75</version>
        </dependency>
        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-core</artifactId>
            <version>5.3.8</version>
        </dependency>

此处对Excel的导出导入代码分开展示,以免混乱      注(此处读取的返回类型和写入的参数类型均为JsonArray类型)

1,Excel解析读取中的数据

 private static Logger logger = Logger.getLogger(ExcelUtils.class.getName());

    /**
     * 读取Excel解析为json格式
     *
     * @param filePath 路径
     * @return JSONArray
     */
    public JSONArray readExcel(String filePath) {
        Workbook workbook = null;
        FileInputStream inputStream = null;
        JSONArray jsonArray = new JSONArray();
        File file = new File(filePath);
        try {
            if (!file.exists()) {
                throw new FileNotFoundException("指定文件不存在");
            }
            inputStream = new FileInputStream(filePath);
            if (filePath.endsWith(".xls")) {
                workbook = new HSSFWorkbook(inputStream);
            } else if (filePath.endsWith(".xlsx")) {
                workbook = new XSSFWorkbook(inputStream);
            } else {
                throw new FileNotFoundException("文件格式错误");
            }
            Sheet sheet = workbook.getSheetAt(0);
            int lastRowNum = sheet.getLastRowNum();
            for (int i = 1; i <= lastRowNum; i++) {
                Row row = sheet.getRow(i);
                int lastCellNum = row.getLastCellNum();
                JSONObject jsonObject = new JSONObject(true);
                for (int j = 0; j < lastCellNum; j++) {
                    Row firstRow = sheet.getRow(sheet.getFirstRowNum());
                    if (null == firstRow) {
                        throw new ConnectException("解析失败,无法解析第一行数据");
                    }
                    String fistRowCellValue = firstRow.getCell(j).toString();
                    String value = sheet.getRow(i).getCell(j).toString();
                    jsonObject.put(fistRowCellValue, value);
                }
                jsonArray.add(jsonObject);
            }
            inputStream.close();
        } catch (IOException e) {
            logger.warning("解析失败,错误信息" + e.getMessage());
            return null;
        } finally {
            try {
                if (null != workbook) {
                    workbook.close();
                }
                if (null != inputStream) {
                    inputStream.close();
                }
            } catch (Exception e) {
                logger.warning("关闭数据流错误!错误信息:" + e.getMessage());
                return null;
            }
        }
        return jsonArray;
    }

 

2,将数据写入Excel

/**
     * @param jsonArray 数据参数类型
     * @param filePath  路径
     * @return boolean
     */
    public boolean writeExcel(JSONArray jsonArray, String filePath) {
        final File file = new File(filePath);
        FileOutputStream outputStream = null;
        if (file.exists()) {
            file.delete();
        }
        try {
            file.createNewFile();
            boolean is03Excel = filePath.matches("^.+\\.(?i)(xlsx)$") ? true : false;
            final Workbook workbook = is03Excel ? new XSSFWorkbook() : new HSSFWorkbook();
            Sheet sheet = workbook.createSheet("1");
            Row firstRow = sheet.createRow(0);
            final JSONObject jsonObject = jsonArray.getJSONObject(0);
            final Set<String> strings = jsonObject.keySet();
            final List<String> list = new ArrayList<>();
            for (String string : strings) {
                list.add(string);
            }
            for (int i = 0; i < jsonArray.size(); i++) {
                final Row row = sheet.createRow(i + 1);
                final JSONObject object = jsonArray.getJSONObject(i);
                for (int j = 0; j < jsonObject.size(); j++) {
                    final Cell firstCell = firstRow.createCell(j);
                    firstCell.setCellValue(list.get(j));
                    final Cell cell = row.createCell(j);
                    cell.setCellValue(object.getString(list.get(j)));
                }
            }
            outputStream = new FileOutputStream(file);
            workbook.write(outputStream);
            outputStream.flush();
        } catch (Exception e) {
            e.printStackTrace();
            logger.warning(e.getMessage());
        } finally {
            try {
                if (outputStream != null) {
                    outputStream.close();
                }
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
        return true;
    }

3,解析resources下的.json文件

 

 /**
     * 读取接送文件
     *
     * @param fileName
     * @return
     */

    public JSONArray readJsonFile(String fileName) {
        FileReader fileReader = null;
        Reader reader = null;
        try {
            File jsonFile = ResourceUtils.getFile("classpath:" + fileName);
            fileReader = new FileReader(jsonFile);
            reader = new InputStreamReader(new FileInputStream(jsonFile), "utf-8");
            int ch;
            StringBuffer sb = new StringBuffer();
            while ((ch = reader.read()) != -1) {
                sb.append((char) ch);
            }
            fileReader.close();
            reader.close();
            String jsonStr = sb.toString();
            final JSONArray jsonArray = JSONArray.parseArray(jsonStr);
            return jsonArray;
        } catch (IOException e) {
            e.printStackTrace();
        } finally {
            if (fileReader != null) {
                try {
                    fileReader.close();
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }
            if (reader != null) {
                try {
                    reader.close();
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }
        }
        return null;
    }