把oracel导出得xlsx导入到数据库-主要解决得问题是数据同步Clob大字段得时候报错

最新推荐文章于 2021-06-07 09:14:32 发布

Jerry_carry

最新推荐文章于 2021-06-07 09:14:32 发布

阅读量272

点赞数

文章标签： xlsx导入数据库

本文链接：https://blog.csdn.net/qq_28461661/article/details/90041138

版权

将xlsx导入到数据库

解决得问题：应为在同步大字段（clob）到数据库得时候，会出现问题，所有我们先把数据库中的数据导出成xlsx文件然后，再把它入库。

public List<PipeConfig> importExcelAction() throws Exception {
//文件路径
        String webPath = FileUtil.getWebPath("/excelImportTemp/");
        String filePath = webPath+"/tmp001.xlsx";
        XSSFWorkbook wookbook = new XSSFWorkbook(new FileInputStream(filePath));
        XSSFSheet sheet = wookbook.getSheet("SQL Results");
        //获取到Excel文件中的所有行数
        int rows = sheet.getPhysicalNumberOfRows();
        //遍历行
        List<PipeConfig> pipelist = new ArrayList<PipeConfig>();
        for (int i = 1; i < rows; i++) {
            // 读取左上端单元格
            XSSFRow row = sheet.getRow(i);
            // 行不为空
            if (row != null) {
                //获取到Excel文件中的所有的列
                int cells = row.getPhysicalNumberOfCells();
                XSSFCell pkIdCell = row.getCell(1);
                String pkId = getValue(pkIdCell);
                XSSFCell ifCodeCell = row.getCell(2);
                String ifCode = getValue(ifCodeCell);
                XSSFCell ifNameCell = row.getCell(3);
                String ifName = getValue(ifNameCell);
                XSSFCell ifTypeCell = row.getCell(4);
                String ifType = getValue(ifTypeCell);
                XSSFCell fileNameCell = row.getCell(5);
                String fileName = getValue(fileNameCell);
                XSSFCell fileTypeCell = row.getCell(6);
                String fileType = getValue(fileTypeCell);
                XSSFCell encodingTypeCell = row.getCell(7);
                String encodingType = getValue(encodingTypeCell);
                XSSFCell specialArgsCell = row.getCell(8);
                String specialArgs = getValue(specialArgsCell);
                XSSFCell execTypeCell = row.getCell(9);
                String execType = getValue(execTypeCell);
                XSSFCell headSqlCell = row.getCell(10);
                String headSql = getValue(headSqlCell);
                XSSFCell tailSqlCell = row.getCell(11);
                String tailSql = getValue(tailSqlCell);
                XSSFCell confDetailsCell = row.getCell(12);
                String confDetails = getValue(confDetailsCell);
             //   confDetails = confDetails.replace("'","''");
                XSSFCell statusCell = row.getCell(13);
                String status = getValue(statusCell);
                XSSFCell deletedCell = row.getCell(14);
                String deleted = getValue(deletedCell);
                XSSFCell createdTsCell = row.getCell(15);
                String createdTs = getValue(createdTsCell);
                XSSFCell createdByCell = row.getCell(16);
                String createdBy = getValue(createdByCell);
                XSSFCell updatedTsCell = row.getCell(17);
                String updatedTs = getValue(updatedTsCell);
                XSSFCell updatedByCell = row.getCell(18);
                String updatedBy = getValue(updatedByCell);
                PipeConfig pipeConfig = new PipeConfig(pkId,ifCode,ifName,ifType,fileName,fileType,encodingType,specialArgs,
                        execType,headSql,tailSql,confDetails,status,deleted,createdTs,createdBy,updatedTs,updatedBy);
                pipelist.add(pipeConfig);
            }
        }
        return pipelist;
    }

    private String getValue(XSSFCell xSSFCell) {
        if (null == xSSFCell) {
            return "";
        }
        if (xSSFCell.getCellType() == 4) {
        // 返回布尔类型的值  
            return String.valueOf(xSSFCell.getBooleanCellValue());
        } else if (xSSFCell.getCellType() == 0) {
        // 返回数值类型的值  
            return String.valueOf(xSSFCell.getNumericCellValue());
        } else {
        // 返回字符串类型的值  
            return String.valueOf(xSSFCell.getStringCellValue());
        }
    }