java向excel的指定的sheet中指定的单元格中写入数据

java向excel的指定的sheet中指定的单元格中写入数据

这里写目录标题

• 准备
• 工具类代码

准备

   org.apache.poi
    poi
    4.0.1


    org.apache.poi
    poi-ooxml
    4.0.1

工具类代码

    public static boolean writeIntoExcelCell(String data, Integer sheetIndex,
                                             Integer rowIndex, Integer cellIndex, String file) {
        boolean flag = false;
        // 获取Excel后缀名
        String fileType = file.substring(file.lastIndexOf(".") + 1, file.length());
        if (Arrays.asList(XLS, XLSX).contains(fileType) == false) {
            log.warn("文件后缀名不正确");
            return flag;
        }
        if (fileType.equals(XLS)) {
            try {
                // 创建Excel的工作书册 Workbook,对应到一个excel文档
                HSSFWorkbook wb = new HSSFWorkbook(new FileInputStream(file));
                HSSFSheet sheet = wb.getSheetAt(sheetIndex);
                HSSFRow row = sheet.getRow( rowIndex);//行
                HSSFCell cell = row.getCell(cellIndex);//获取指定列

                // todo
                cell.setCellValue(data);

                FileOutputStream os;
                os = new FileOutputStream(file);
                wb.write(os);
                os.close();
                flag = true;


            } catch (Exception e) {
                e.printStackTrace();
            }
        } else {
            try {
                XSSFWorkbook wb = new XSSFWorkbook(new FileInputStream(file));
                XSSFSheet sheet = wb.getSheetAt(sheetIndex);
                XSSFRow row1 = sheet.getRow(rowIndex);
                Cell cell = row1.getCell(cellIndex);

                cell.setCellValue(data);

                FileOutputStream os;
                os = new FileOutputStream(file);
                wb.write(os);
                os.close();
                flag = true;

            } catch (IOException e) {
                e.printStackTrace();
            }
        }
        return flag;
    }

 
     public static boolean writeIntoCell(String data, Integer getDataCellIndex, Integer sheetIndex, Integer rowIndex, Integer cellIndex, String file) {
        boolean flag = false;
        // 获取Excel后缀名
        String fileType = file.substring(file.lastIndexOf(".") + 1, file.length());
        if (Arrays.asList(XLS, XLSX).contains(fileType) == false) {
            log.warn("文件后缀名不正确");
            return flag;
        }
        if (fileType.equals(XLS)) {
            try {
                // 创建Excel的工作书册 Workbook,对应到一个excel文档
                HSSFWorkbook wb = new HSSFWorkbook(new FileInputStream(file));
                HSSFSheet sheet = wb.getSheetAt(sheetIndex);

                int physicalNumberOfRows = sheet.getPhysicalNumberOfRows();//数据结束的行
                HSSFRow row1 = sheet.getRow(sheet.getFirstRowNum());//表头行
                int physicalNumberOfCells = row1.getPhysicalNumberOfCells();//数据结束列
                //从第二行开始
                for (int h = 1; h < physicalNumberOfRows; h++) {
                    HSSFRow row = sheet.getRow(h); //获取出每一行

                    HSSFCell cell = row.getCell(cellIndex);//获取指定列
                    if (cell == null) cell = row.createCell(cellIndex);

                    HSSFCell getDataCell = row.getCell(getDataCellIndex); //指定行列对应的单元格
                    String basiData = convertCellValueToString(getDataCell); //得到该单元格数据

                    // todo 用 basiData 查找百度坐标、
                    StringJoiner sj = new StringJoiner(",");
                    if(StringUtil.isNotEmpty(basiData)){
                        Map coordinate = EntCoordSyncJob.getCoordinate(basiData);
                        if(coordinate==null || coordinate.isEmpty()) continue;
                        BigDecimal lat = coordinate.get("lat");
                        BigDecimal lng = coordinate.get("lng");
                        sj.add(lat.toString());
                        sj.add(lng.toString());
                    }

                    String pos = sj.toString();
                    cell.setCellValue(pos);

                    FileOutputStream os;
                    os = new FileOutputStream(file);
                    wb.write(os);
                    os.close();
                    flag = true;
                }

            } catch (Exception e) {
                e.printStackTrace();
            }
        } else {
            try {
                XSSFWorkbook wb = new XSSFWorkbook(new FileInputStream(file));
                XSSFSheet sheet = wb.getSheetAt(sheetIndex);
                int physicalNumberOfRows = sheet.getPhysicalNumberOfRows();//数据结束的行
                XSSFRow row1 = sheet.getRow(sheet.getFirstRowNum());//表头行
                int physicalNumberOfCells = row1.getPhysicalNumberOfCells();//数据结束列
                //从第二行开始
                for (int h = 1; h < physicalNumberOfRows; h++) {
                    XSSFRow row = sheet.getRow(h); //获取每一行

                    XSSFCell cell = row.getCell(cellIndex);//获取指定列
                    if (cell == null) cell = row.createCell(cellIndex);

                    XSSFCell getDataCell = row.getCell(getDataCellIndex);//指定行列对应的单元格
                    String basiData = convertCellValueToString(getDataCell);//得到该单元格数据

                   
                    cell.setCellValue(pos);

                    FileOutputStream os;
                    os = new FileOutputStream(file);
                    wb.write(os);
                    os.close();
                    flag = true;
                }
            } catch (IOException e) {
                e.printStackTrace();
            }
        }
        return flag;
    }