如何使用POI Excel和Java获取所有数值。

huangapple
未分类评论47阅读模式
英文:

how to get all values with poi excel and java

问题

@PostMapping("/upload")
public ResponseEntity<?> addRfp(@RequestParam("file") MultipartFile file) throws IOException, InvalidFormatException {
    ZipSecureFile.setMinInflateRatio(0);

    FileInputStream fis = (FileInputStream) file.getInputStream();
    XSSFWorkbook myWorkBook = new XSSFWorkbook(fis);
    XSSFSheet mySheet = myWorkBook.getSheetAt(0);
    Iterator<Row> rowIterator = mySheet.iterator();
    while (rowIterator.hasNext()) {
        Row row = rowIterator.next();
        Iterator<Cell> cellIterator = row.cellIterator();
        while (cellIterator.hasNext()) {
            final DataFormatter df = new DataFormatter();
            Rfp rfp = new Rfp();
            for (int i = 0; i < mySheet.getPhysicalNumberOfRows(); i++) {
                Row nextRow = mySheet.getRow(row.getRowNum() + 1);
                if (nextRow != null) {
                    System.out.println(df.formatCellValue(row.getCell(3)));
                    rfp.setIde(df.formatCellValue(row.getCell(0)));
                    rfp.setUnit(df.formatCellValue(row.getCell(1)));
                    rfp.setRFPID(df.formatCellValue(row.getCell(2)));
                    // ... (omitting other set methods for brevity)
                    rfprepository.save(rfp);
                }
            }
        }
    }
    return null;
}
英文:

Hello with this code i just get the first line in my database
i need to gett all line please what a should to do
I'm using Apache POI to read an Excel document. and i need to stock all data in my database but with this code i just save the first line please what i should to do !
how to do read all line in my excel thanks

@PostMapping(&quot;/upload&quot;)
public ResponseEntity&lt;?&gt; addRfp  (@RequestParam(&quot;file&quot;) MultipartFile file) throws IOException, InvalidFormatException  {
	ZipSecureFile.setMinInflateRatio(0);
	
	 

    FileInputStream fis =  (FileInputStream) file.getInputStream();
    XSSFWorkbook myWorkBook = new XSSFWorkbook (fis);
    XSSFSheet mySheet = myWorkBook.getSheetAt(0);
    Iterator&lt;Row&gt; rowIterator = mySheet.iterator();
    while (rowIterator.hasNext()) {
        Row row = rowIterator.next();
        Iterator&lt;Cell&gt; cellIterator = row.cellIterator();
        while (cellIterator.hasNext()) {       
            final DataFormatter df = new DataFormatter();
            Rfp rfp = new Rfp();
           for(int i =0 ; i &lt; mySheet.getPhysicalNumberOfRows() ; i++)
       {
    	   Row nextRow = mySheet.getRow(row.getRowNum() + 1);
    	   if (nextRow != null)
    	   {
    	  System.out.println(df.formatCellValue(row.getCell(3)));
           rfp.setIde(df.formatCellValue(row.getCell(0)));
           rfp.setUnit(df.formatCellValue(row.getCell(1)));
			rfp.setRFPID(df.formatCellValue(row.getCell(2)));
			rfp.setREQUESTID(df.formatCellValue(row.getCell(3)));
			rfp.setINCOMING(df.formatCellValue(row.getCell(4)));
			rfp.setPROJECTTITLE(df.formatCellValue(row.getCell(5)));
			rfp.setSERVICELINE(df.formatCellValue(row.getCell(6)));
			rfp.setBUSINESSUNIT(df.formatCellValue(row.getCell(7)));
			rfp.setREQUSITIONBUSINESSUNIT(df.formatCellValue(row.getCell(8)));
			rfp.setCOUNTRY(df.formatCellValue(row.getCell(9)));
			rfp.setTARGETPROPOSE(df.formatCellValue(row.getCell(10)));
			rfp.setRFX(df.formatCellValue(row.getCell(11)));
			rfp.setWORKFLOWTRACKING(df.formatCellValue(row.getCell(12)));
			rfp.setPROTOCOL(df.formatCellValue(row.getCell(13)));
			rfp.setJOURFIXKUNDE(df.formatCellValue(row.getCell(14)));
			rfp.setREJECTEDSTATUS(df.formatCellValue(row.getCell(15)));
			rfp.setCLOSEDREASON(df.formatCellValue(row.getCell(16)));
			rfp.setPRACTICE(df.formatCellValue(row.getCell(17)));
			rfp.setSTARTDATE(df.formatCellValue(row.getCell(18)));
			rfp.setENDDATE(df.formatCellValue(row.getCell(19)));
			rfp.setVERTRAGSART(df.formatCellValue(row.getCell(20)));
			rfp.setRFPCOMMENT(df.formatCellValue(row.getCell(21)));
			rfp.setPERSONDAYS(df.formatCellValue(row.getCell(22)));
			rfp.setACCOUNTEXECUTIVE(df.formatCellValue(row.getCell(23)));
			rfp.setACCOUNTSALESMANAGER(df.formatCellValue(row.getCell(24)));
			rfp.setEXPERTSALES(df.formatCellValue(row.getCell(25)));
			rfp.setDELIVERYUNIT(df.formatCellValue(row.getCell(26)));
			rfp.setDELIVERYRESPONSIBLE(df.formatCellValue(row.getCell(27)));
			rfp.setREQUISITIONER(df.formatCellValue(row.getCell(28)));
			rfp.setPROCUREMENT(df.formatCellValue(row.getCell(29)));
			rfp.setREFERENCENUMBER(df.formatCellValue(row.getCell(30)));
			rfp.setATOSCONTRACTNO(df.formatCellValue(row.getCell(31)));
			rfp.setCANDIDATES(df.formatCellValue(row.getCell(32)));
			rfp.setITEC(df.formatCellValue(row.getCell(33)));
			rfp.setTCV(df.formatCellValue(row.getCell(34)));
			rfp.setUPDATED(df.formatCellValue(row.getCell(35)));
			rfp.setUPDATEDBY(df.formatCellValue(row.getCell(36)));
			rfp.setPORTFOLIO(df.formatCellValue(row.getCell(37)));
			rfp.setPATH(df.formatCellValue(row.getCell(38)));
			rfp.setRMPROTOCOL(df.formatCellValue(row.getCell(39)));
			rfp.setRM(df.formatCellValue(row.getCell(40)));
			rfp.setWFM_ID(df.formatCellValue(row.getCell(41)));
			rfp.setNEEDCONFOPART(df.formatCellValue(row.getCell(42)));
			rfp.setCONFIRMATIONOFPARTICIPATION(df.formatCellValue(row.getCell(43)));
			rfp.setPROPOSE(df.formatCellValue(row.getCell(44)));
			rfp.setLASTCALL(df.formatCellValue(row.getCell(45)));
			rfp.setLASTCALLDATE(df.formatCellValue(row.getCell(46)));
			rfp.setSKILLS(df.formatCellValue(row.getCell(47)));
			rfp.setLEISTUNGSERBRINGUNG(df.formatCellValue(row.getCell(48)));
			rfp.setROLLEFUNKTION(df.formatCellValue(row.getCell(49)));
			rfp.setINHALTSCOPE(df.formatCellValue(row.getCell(50)));
			rfp.setLAUFZEITPERSPEKTIVE(df.formatCellValue(row.getCell(51)));
			rfp.setLAUFZEITANGEFRAGT(df.formatCellValue(row.getCell(52)));
			rfp.setPHASE(df.formatCellValue(row.getCell(53)));
			rfp.setFRISTVERLAENGERUNG(df.formatCellValue(row.getCell(54)));
			rfp.setCLOSEDSTATUS(df.formatCellValue(row.getCell(55)));
			rfp.setDEFERRED_TILL(df.formatCellValue(row.getCell(56)));
			rfp.setSIPA_YN(df.formatCellValue(row.getCell(57)));
			rfp.setPO_NO(df.formatCellValue(row.getCell(58)));
			rfp.setNESSIE_NO(df.formatCellValue(row.getCell(59)));
			rfp.setNUMBER_OF_PERSONS(df.formatCellValue(row.getCell(60)));
			rfp.setATT_JOB_SITE(df.formatCellValue(row.getCell(61)));
			rfp.setATT_LANGUAGE(df.formatCellValue(row.getCell(62)));
			rfp.setATT_MAIN_TOPICS(df.formatCellValue(row.getCell(63)));
			rfp.setREF_RFPID(df.formatCellValue(row.getCell(64)));
			rfp.setREF_HISTORY(df.formatCellValue(row.getCell(65)));
			rfp.setREF_REJECTIONS(df.formatCellValue(row.getCell(66)));
			rfp.setRV_REJ_STATUS_DETAILS(df.formatCellValue(row.getCell(67)));
			rfp.setRV_ALLIANCE_MANAGER(df.formatCellValue(row.getCell(68)));
			rfp.setRV_FINAL_STATUS(df.formatCellValue(row.getCell(69)));
			rfp.setRV_CUST_LIST_DATE(df.formatCellValue(row.getCell(70)));
			rfp.setRV_FINAL_NOTE(df.formatCellValue(row.getCell(71)));
			rfp.setRV_ATOS_ROOT_CAUSES(df.formatCellValue(row.getCell(72)));
			rfp.setCURRENCY(df.formatCellValue(row.getCell(73)));
			rfp.setEXTERNALID(df.formatCellValue(row.getCell(74)));
			rfp.setRESULTSTATUS(df.formatCellValue(row.getCell(75)));
			rfp.setREASONCODE(df.formatCellValue(row.getCell(76)));
			rfp.setSYSID(df.formatCellValue(row.getCell(77)));
			rfp.setNCRM_COMMENT(df.formatCellValue(row.getCell(78)));
			rfp.setIDEMAND_COMMENT(df.formatCellValue(row.getCell(79)));
			rfp.setOFFSHORE_COMMENT(df.formatCellValue(row.getCell(80)));
			rfp.setRV_RC_UPDBY(df.formatCellValue(row.getCell(81)));
			rfp.setRV_RC_DATE(df.formatCellValue(row.getCell(82)));
			rfp.setRV_FINALCOMMENT(df.formatCellValue(row.getCell(83)));
			rfp.setRV_FINALDATE(df.formatCellValue(row.getCell(84)));
			rfp.setTCV_EURO(df.formatCellValue(row.getCell(85)));
			rfp.setREM_ACTIVE(df.formatCellValue(row.getCell(86)));
			rfp.setREM_SEND(df.formatCellValue(row.getCell(87)));
			rfp.setREMCOP_ACTIVE(df.formatCellValue(row.getCell(88)));
			rfp.setREMCOP_SEND(df.formatCellValue(row.getCell(89)));
			rfp.setIDEMAND(df.formatCellValue(row.getCell(90)));
			rfp.setIDEMAND_URL(df.formatCellValue(row.getCell(91)));
			rfp.setLABOUR_LEASING_YN(df.formatCellValue(row.getCell(92)));
			rfp.setCAS_NO(df.formatCellValue(row.getCell(93)));
			rfp.setPROLONGATION(df.formatCellValue(row.getCell(94)));
			rfp.setRV_SKILLS_MISS_1(df.formatCellValue(row.getCell(95)));
			rfp.setRV_SKILLS_MISS_2(df.formatCellValue(row.getCell(96)));
			rfp.setRV_SKILLS_MISS_3(df.formatCellValue(row.getCell(97)));
			rfp.setREF_RENEWAL_DATE(df.formatCellValue(row.getCell(98)));
			rfprepository.save(rfp);
    	   }
       }}
        }

	
	return null;	
}}

答案1

得分: 0

我过去是这样获取所有行的。

FileInputStream fis = (FileInputStream) file.getInputStream();
XSSFWorkbook myWorkBook = new XSSFWorkbook(fis);
XSSFSheet mySheet = myWorkBook.getSheetAt(0);
for (Row row : mySheet) {
    final DataFormatter df = new DataFormatter();
    System.out.println(df.formatCellValue(row.getCell(0)));
}
英文:

I use to get all rows this way.

FileInputStream fis =  (FileInputStream) file.getInputStream();
XSSFWorkbook myWorkBook = new XSSFWorkbook (fis);
XSSFSheet mySheet = myWorkBook.getSheetAt(0);
for(Row row : mySheet){
   final DataFormatter df = new DataFormatter();
   System.out.println(df.formatCellValue(row.getCell(0)));
}

huangapple
  • 本文由 发表于 2020年4月10日 02:06:23
  • 转载请务必保留本文链接：https://java.coder-hub.com/61127466.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定