java基于单个单元格值获取整个excel行

Question

我有一个excel工作表，需要根据特定列的单元格值从中获取行。例如，我有一个名为city和employee\u name的列。我将使用Java代码提供city值，它应该获取指定列中具有该city值的所有行。列名将被固定

Example:
City    Employee_Name Age
Vegas   Tom           23
Vegas   Ron           43
Vegas   Sam           19
Delhi   Rohit         32
Delhi   Ram           28
Jaipur  Ankit         31

因此，如果我从Java代码中将单元格值设为Vegas，那么它应该获取第1、2行&；三,

Answer 1

1. 把这个放在你项目poi-2.5.1的lib文件夹中。罐子
2. 试着在struts操作中使用这个方法，否则你可以在简单的类中使用它，避免/删除方法参数中的HttpSession参数

3. void uploadBulkQuestions（ExcelUploadForm ExcelUploadForm、字符串文件路径、HttpSession会话）抛出SQLException//您可以忽略参数中的ExcelUploadForm obj { ExcelForm ExcelForm=null； /** *//这里ExcelForm是一个简单的pojo，包含setter和getter * *公共类ExcelForm扩展了ActionForm{ *弦城； *字符串employeeName； *整数年龄； *//上述变量的setter和getter。 * } */ 短城市位置=0； 短期员工职位=1； 短期职位=2； 连接con=null； ArrayList invalidFields=新的ArrayList（）； ArrayList validFields=新的ArrayList（）； 布尔bulkUploadFlag=true

   try
   {
       FileInputStream fs =new FileInputStream(filePath+"\\"+excelUploadForm.getExcelFile().getFileName());//here you can give ur ExcelFile Path{like "D:\ExcelFiles\sample.xls"}(excelUploadForm in method parameter is an another form which pics a file through browse(i.e.,File Upload))) 
   
       HSSFWorkbook wb = new HSSFWorkbook(fs);
       for (int k = 0; k < wb.getNumberOfSheets(); k++)
       {
           HSSFSheet sheet = wb.getSheetAt(k);
           int rows  = sheet.getPhysicalNumberOfRows();
           HSSFRow firstRow   = sheet.getRow(0);
           int totalCells = firstRow.getPhysicalNumberOfCells(); 
           for(short i=0;i<totalCells;i++)
           {
               HSSFCell firstCell;
               firstCell=firstRow.getCell(i);
                    if(firstCell.getStringCellValue().trim().equalsIgnoreCase("City"))
                   {CityPosition=i;System.out.println("HSSFCellqidpos"+City);}
               else if(firstCell.getStringCellValue().trim().equalsIgnoreCase("Employee_Name"))
                   Employee_NamePosition=i;
               else if(firstCell.getStringCellValue().trim().equalsIgnoreCase("Age"))
                   AgePosition=i;
           }
   
           for (int r = 1; r < rows; r++)
           {
               excelForm = new ExcelForm();
               HSSFRow row   = sheet.getRow(r);
               HSSFCell cell;  
               if(CityPosition>=0)
               {
                   cell= row.getCell(CityPosition);
                   try{
                   excelForm.setCity(cell.getStringCellValue());
                   System.out.println("Check the Data of city"+excelForm.getCity());
               }catch(NullPointerException nullPointerException){
                   nullPointerException.printStackTrace();
               }
               }
   
               if(Employee_NamePosition>0)
               {
                   cell= row.getCell(Employee_NamePosition);
                   try{
                   excelForm.setEmployeeName(cell.getStringCellValue());
               }catch(NullPointerException nullPointerException){
                   nullPointerException.printStackTrace();
               }
               }
   
               if(AgePosition>0)
               {
                   cell= row.getCell(AgePosition);
                   try{
                   excelForm.setAge((int)cell.getNumericCellValue());
               }catch(NullPointerException nullPointerException){
                   nullPointerException.printStackTrace();
               }
               }
   
               //Validating Excel Data
               if(excelForm.getCity()==null || excelForm.getEmployeeName()==null || excelForm.getAge() < 0)
               {
                   System.out.println("inside invalidFields.........");
                   System.out.println(excelForm);
                   invalidFields.add(excelForm);
                   bulkUploadFlag=false;
               }
               else
               {   
               System.out.println("inside validQue.........");
               System.out.println(excelForm);  
               validFields.add(excelForm);
               }
   
           }
   
   
   
       }
   
   
   
   
       //Transaction Management to make sure all the validFields ArrayList obj data is inserted
   
       if(bulkUploadFlag)
       {
           con.setAutoCommit(false);
   
           try
           {
               //fetch data from validFields Array List Using iterator and insert into DB if u wish to do so...
               Iterator fieldsIterator=  validFields.iterator();
               excelForm =null;
               while(questionIterator.hasNext())
                   {
   
                   excelForm=(excelForm)fieldsIterator.next();                     
                       //storing in Questions DB table
                       PreparedStatement psFields = con.prepareStatement("........");//write ur query to insert
                       psFields.executeUpdate();
   
           }
   
           // If there is no error.
           con.commit();
           session.setAttribute("ValidFields", validFields);
   
           }
           catch(SQLException se)
           {
               // If there is any error.
                con.rollback();
                se.printStackTrace();
                session.setAttribute("BulkUploadError","No Data Uploded due to Some Errors in Excel File");     
           }
       }
       else
       {
           session.setAttribute("InvalidFields",invalidFields);
   
       }
   }
   catch(Exception e)
   {
       e.printStackTrace();
   }
   finally
   {
       con.close();
   }
   
   return null;
   

   }

Answer 2

您可以使用以下方法。 “工作表”是您需要在其中执行搜索的工作表， “colName”是列的名称，根据您的要求，它将被修复。 “textToFind”是你必须在colName列中找到的文本

public static XSSFRow test(XSSFSheet sheet, String colName, String textToFind){
    int colIndex=0;
    for (int colNum = 0; colNum<=sheet.getRow(0).getLastCellNum();colNum++){
        if (sheet.getRow(0).getCell(colNum).toString().equalsIgnoreCase(colName)){
            colIndex = colNum;
            break;
        }
    }
    for (int RowNum = 0; RowNum<sheet.getLastRowNum();RowNum++){
        if(sheet.getRow(RowNum).getCell(colIndex).toString().equalsIgnoreCase(textToFind)){
            return sheet.getRow(RowNum);
        }
    }
    System.out.println("No any row found that contains "+textToFind);
    return null;
}