美文网首页
java操作excel表格

java操作excel表格

作者: 春苟哈皮 | 来源:发表于2018-11-06 19:04 被阅读0次
private Workbook getReadWorkbookType(String filepath) {
        FileInputStream inputStream = null;
        try {
            inputStream = new FileInputStream(filepath);
            //xlsx---2007
            if (filepath.toLowerCase().endsWith("xlsx")) {
                return new XSSFWorkbook(inputStream);
            } else if (filepath.toLowerCase().endsWith("xls")) {
                //xls---2003
                return new HSSFWorkbook(inputStream);
            } else {
                return null;
            }
        } catch (FileNotFoundException e) {
            e.printStackTrace();
        } catch (IOException e) {
            e.printStackTrace();
        }

        return null;
    }
public List<Pair<String, String>> readExcel(String sourceFilePath) {
        Workbook workbook = null;

        try {
            workbook = getReadWorkbookType(sourceFilePath);

            List<Pair<String, String>> contents = new ArrayList<>();

            Sheet sheet = workbook.getSheetAt(0);

            for (int rowNum = 1; rowNum <= sheet.getLastRowNum(); rowNum++) {
                Row row = sheet.getRow(rowNum);
                Cell name = row.getCell(1);
                Cell idCard = row.getCell(0);
//                contents.add(getCellStringVal(cell).trim());
                contents.add(Pair.of(getCellStringVal(name).trim(), getCellStringVal(idCard).trim()));
            }
            return contents;
        } finally {
            IOUtils.closeQuietly(workbook);
        }

    }
private String getCellStringVal(Cell cell) {
        CellType cellType = cell.getCellTypeEnum();

        switch (cellType) {
            case NUMERIC:
                return cell.getStringCellValue();
            case STRING:
                return cell.getStringCellValue();
            case BOOLEAN:
                return String.valueOf(cell.getBooleanCellValue());
            case FORMULA:
                return cell.getCellFormula();
            case BLANK:
                return "";
            case ERROR:
                return String.valueOf(cell.getErrorCellValue());
            default:
                return StringUtils.EMPTY;
        }
    }
public void wirteExcel(String targetFilePath, List<JSONObject> result) {
        Workbook workbook;
        FileOutputStream outputStream;

        workbook = getReadWorkbookType(targetFilePath);

        Sheet sheet = workbook.createSheet("测试结果");
        Row titleRow = sheet.createRow(0);
        titleRow.createCell(0).setCellValue("orderNo");
        titleRow.createCell(1).setCellValue("handleTime");

        Integer rowIndex = result.size();

        for (int i = 0; i < rowIndex; i++) {
            Row row = sheet.createRow(i + 1);
            row.createCell(0).setCellValue(result.get(i).getString("orderNo"));
            row.createCell(1).setCellValue(result.get(i).getString("handleTime"));
        }

        try {
            outputStream = new FileOutputStream(targetFilePath);
            workbook.write(outputStream);
        } catch (FileNotFoundException e) {
            e.printStackTrace();
        } catch (IOException e) {
            e.printStackTrace();
        }

    }

相关文章

网友评论

      本文标题:java操作excel表格

      本文链接:https://www.haomeiwen.com/subject/pmfpxqtx.html