0

I have a sample of Excel data that needs to be read and pass to API as XML format. Example:

enter image description here

I have tried to use Apache POI in reading data by row. But how do I get the columnName and the value from the column & pass that data into API request:

API Request Sample:

RequestBody body = RequestBody.create(mediaType, "<?xml version=\"1.0\" encoding=\"UTF-8\" standalone=\"yes\"?>\r\n<ns2:Fields xmlns:ns2=\"csng\" xmlns:ns1=\"http://www.w3.org/1999/xlink\">\r\n\t<ns2:Field name=\"**Project**\">**customerCloud**</ns2:Field>\r\n\t<ns2:Field name=\"**Product**\">**customerPortal**</ns2:Field>\r\n\t<ns2:Field name=\"**Component**\">**cloudPlatform**</ns2:Field></ns2:Fields>");

Java code:

File file = new File("testdata.xlsx"); 
    
    FileInputStream inputStream = null;
    
    try {
        inputStream = new FileInputStream(file);
    } catch (FileNotFoundException e) {
        e.printStackTrace();
    }
    
    workbook = null;
    try {
        workbook = new XSSFWorkbook(inputStream);
        
        rows = workbook.getSheetAt(0).getPhysicalNumberOfRows();
        
        System.out.println("No Of Rows in ExcelSheet---"+rows);
        
        XSSFSheet sheet = workbook.getSheetAt(0);     //creating a Sheet object to retrieve object  
        Iterator<Row> itr = sheet.iterator();    //iterating over excel file  
        
        int i = 0;
        while (itr.hasNext())                 
        {  
            Row rowdata = itr.next();  
            
            Iterator<Cell> cellIterator = rowdata.cellIterator();   //iterating over each column  
            int j=0;
            while (cellIterator.hasNext())   
            {  
                Cell celldata = cellIterator.next();  
                
                System.out.print(celldata.getStringCellValue() + "\n");  
                
                j++;
            
            }
            System.out.println("");  
            i++;
        }
        
    } catch (IOException e) {
        e.printStackTrace();
    }

With above code I am getting output as follow:

Project
Product
Componant

customerCloud
customerPortal
cloudPlatform

Any help would be appreciated. Thanks

Garry S
  • 85
  • 1
  • 9
  • 2
    Build a POJO and map the file content to it. That way you always know which value was in which column – XtremeBaumer Apr 28 '22 at 06:20
  • 1
    A default Excel sheet does not have something like column names. In your case "Project", "Product" and "Componant" are the same kind of cell content as all other, nothing special. So your program logic needs to determine what cell content is the key (or name) and what cell content is the value. – Axel Richter Apr 28 '22 at 06:28
  • 1
    maybe first answer in https://stackoverflow.com/questions/58981017/java-excel-pojo-mapping-in-poi would be of interest to you – PJ Fanning Apr 28 '22 at 08:58
  • This should answer your question https://stackoverflow.com/a/68878883/7731623 – Nandan A Apr 28 '22 at 14:30

0 Answers0