get column value from excel based on column names in java

Question

I want to read a excel sheet based on column name in java. I want to get column values correponding to column name like get TXT_Policy_no in policy no string etc. as the excel sheet values can be suffled i want to read values based on column names to be on safer side.

I want to get column values correponding to column name like get TXT_Policy_no in policy no string etc. as the excel sheet values can be suffled i want to read values based on column names to be on safer side.

My java Code:

for (int i = 0; i < listofexcel.length; i++)
            {
              if (listofexcel[i].isFile()) 
              {
                //System.out.println("File " + listOfCsv[i].getName());
                Csv_list.add(listofexcel[i].getName()); 
              } else if (listofexcel[i].isDirectory()) 
              {
                System.out.println("Directory " + listofexcel[i].getName());
              }
            }
           
        
            Iterator itr = Csv_list.iterator();  
            while(itr.hasNext())
            {   
                String CsvName = (String) itr.next();
                
                 
                 System.out.println(CsvName);
                 ProcessMain worker = new ProcessMain(CsvName);
                
                 //    Runnable worker = new UploadFiles(CsvName);
                // System.out.println("^^^^^^^");
                 String csvpath= GlobalVariable.getCsv_path()+CsvName;
                 WFLogger.printOut(csvpath);
                 first(csvpath);
                 final File f2 = new File(csvpath);
                 if(f2.exists())
                 {
                     f2.delete();
                 }
                //executor.execute(worker);
            }
            
        
        
    }
        catch (NumberFormatException e) {
        // TODO Auto-generated catch block
        e.printStackTrace();
    }
    
}
   
 
    //Returns the Headings used inside the excel sheet
    public void getHeadingFromXlsFile(Sheet sheet) {
        int columnCount = sheet.getColumns();
        for (int i = 0; i < columnCount; i++) {
            System.out.println(sheet.getCell(i, 0).getContents());
        }
    }
 



private void first(String csvName) {
    // TODO Auto-generated method stub
     FileInputStream fs = null;
        try {
            fs = new FileInputStream(new File(csvName));
            contentReading(fs);
        } catch (IOException e) {
            e.printStackTrace();
        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            try {
                fs.close();
            } catch (IOException e) {
                e.printStackTrace();
            }
        }
}






private void contentReading(FileInputStream fs) throws ClassNotFoundException, BiffException {
    // TODO Auto-generated method stub
    

    WorkbookSettings ws = null;
    Workbook workbook = null;
    Sheet s = null;
    jxl.Cell[] rowData=null;
    int rowCount = '0';
    int columnCount = '0';
    DateCell dc = null;
    int totalSheet = 0;
         
        ws = new WorkbookSettings();
        ws.setLocale(new Locale("en", "EN"));
        workbook = Workbook.getWorkbook(fs, ws);

        totalSheet = workbook.getNumberOfSheets();
        if(totalSheet > 0) {
            System.out.println("Total Sheet Found:" + totalSheet);
            String sheetName="";
            for(int jj=0;jj<totalSheet ;jj++) {
                sheetName=workbook.getSheet(jj).getName();
                System.out.println("Sheet Name:" + sheetName);

                s = workbook.getSheet(jj);

                //Reading Individual Cell
                //getHeadingFromXlsFile(s);

                System.out.println("Total Rows inside Sheet:" + s.getRows());
                rowCount = s.getRows();

                System.out.println("Total Column inside Sheet:" + s.getColumns());
                columnCount = s.getColumns();

                //Reading Individual Row Content
                for (int i = 1; i < rowCount; i++) {
                    //Get Individual Row
                    rowData = s.getRow(i);                      
                    if (rowData[0].getContents().length() != 0) {
                        int j=0;
                        String Claimno="";
                        String Product="";
                        String Mobileno="";
                        String Omniflag="N";
                        String Policyno="";
                        String Customername="";
                        String Certno="";
                        String Department="";
                        String lossdate="";
                        String Srno="";
                        
                        



                        for ( j = 0; j < columnCount; j++) {                 
                            try{
                                switch (j) {
                                case 0:
                                    Srno= rowData[j].getContents();
                                    break;
                                case 1:
                                    Policyno=rowData[j].getContents();                                   
                                    break;
                                case 2:
                                    Certno=rowData[j].getContents();
                                    break;
                                case 3:
                                    lossdate= rowData[j].getContents();
                                    break;
                                case 4:
                                    Department=rowData[j].getContents();                                   
                                    break;
                                case 5:
                                    Product=rowData[j].getContents();
                                    break;
                                case 6:
                                    Claimno= rowData[j].getContents();
                                    break;
                                case 7:
                                    Customername=rowData[j].getContents();                                   
                                    break;
                                
                                default:
                                    break;
                                }
                            }catch (Exception e) {
                                System.out.print(e);
                            }
                        }
                       

Answer 1

I assume that we know what row contains headers. Then we can iterate through it's cells and check their values to gather information about columns. Like

int headerRow = sheet.getRow(n); 
Map<String, Integer> columnIndexes = new HashMap();
 for ( j = 0; j < columnCount; j++) {                 
    switch (rowData[j].getContents()) {
       case "TXT_Policy_no":
            columnIndexes.put("TXT_Policy_no", j); break;
        ...
        case "CLAIMNO":
            columnIndexes.put("CLAIMNO", j); break;
    }
}

After map is filled, you can access cells like

int columnIndex = columnIndexes.get("CLAIMNO")
var cell = row.getCell(columnIndex);