时间戳

首页 > 解决方案 > 如何获得两个excel列的平均值

java - 如何获得两个excel列的平均值

问题描述

像这样，我需要一些帮助来获得 xlsx 文件的两列平均值的最大值我可以用 jxl 做到这一点，但我正在使用大型 excel .. 非常感谢任何答案

public void readExcel() throws BiffException, IOException{//method to read 
contents form excel
    String FilePath = "C:\\Users\\ok.xls";
    double max1=0;
    double max2=0;
    FileInputStream fs = new FileInputStream(FilePath);
    Workbook wb = Workbook.getWorkbook(fs);
    Sheet sh = wb.getSheet(0);// TO get the access to the sheet
    int totalNoOfRows = sh.getRows();// To get the number of rows present in  sheet
 //   int totalNoOfCols = sh.getColumns();// To get the number of columns present in sheet

    for (int row = 0; row < totalNoOfRows; row++)
    {
        firstarray.add(sh.getCell(18, row).getContents());
        secondarray.add(sh.getCell(17, row).getContents());
    }
    for (int row = 0; row < totalNoOfRows; row++)
    {
          if(Double.parseDouble(firstarray.get(row))>=max1 ){
            max1= Double.parseDouble(firstarray.get(row));
    }
          if(Double.parseDouble(secondarray.get(row))>=max2 ){ 
            max2= Double.parseDouble(secondarray.get(row));
      }  
}     double max=(max1 + max2)/2; 
    System.out.println(max);
    }//end of method to read contents from excel

标签： javaapache-poi

解决方案

您可以让 Excel 通过在放置未使用单元格的公式中使用 AVERAGE 函数来执行此操作。然后你评估它并得到结果。

编辑：这是一个代码片段。

CellReference cellReference = new CellReference("S1"); // Choose an unused cell here
Row row = sheet.getRow(cellReference.getRow());
Cell cell = row.getCell(cellReference.getCol()); 

// Formula that gets average of Q column and R columns' maximums
cell.setCellFormula("AVERAGE(MAX(Q:Q);MAX(R:R))");

// Let Excel do the math
CellValue cellValue = evaluator.evaluate(cell);
System.out.println(cellValue.getNumberValue());

推荐阅读