Java FormulaEvaluator.evaluateAll方法代碼示例

import org.apache.poi.ss.usermodel.FormulaEvaluator; //導入方法依賴的package包/類
@Test
public void testRead_FORMULA_ERROR() throws IOException {
	try (Workbook workbook = new XSSFWorkbook()) {
		// 準備
		Sheet sheet = workbook.createSheet();
		Row row0 = sheet.createRow(0);
		row0.createCell(0).setCellErrorValue((byte) 0);
		row0.createCell(1).setCellFormula("A1");
		FormulaEvaluator evaluator = workbook.getCreationHelper().createFormulaEvaluator();
		evaluator.evaluateAll();

		// 実行＆検証
		try (ExcelReader reader = new ExcelReader(workbook)) {

			String[] r0 = reader.read();
			assertNotNull(r0);
			assertEquals(2, r0.length);
			assertNull(r0[0]);
			assertNull(r0[1]);

			assertNull(reader.read());
		}
	}
}
 

import org.apache.poi.ss.usermodel.FormulaEvaluator; //導入方法依賴的package包/類
@Test
public void testRead_FORMULA_NUMERIC() throws IOException {
	try (Workbook workbook = new XSSFWorkbook()) {
		// 準備
		Sheet sheet = workbook.createSheet();
		Row row0 = sheet.createRow(0);
		row0.createCell(0).setCellFormula("1200+34");
		row0.createCell(1).setCellFormula("1200+34.56");
		FormulaEvaluator evaluator = workbook.getCreationHelper().createFormulaEvaluator();
		evaluator.evaluateAll();

		// 実行＆検証
		try (ExcelReader reader = new ExcelReader(workbook)) {

			String[] r0 = reader.read();
			assertNotNull(r0);
			assertEquals(2, r0.length);
			assertEquals("1234", r0[0]);
			assertEquals("1234.56", r0[1]);

			assertNull(reader.read());
		}
	}
}
 

import org.apache.poi.ss.usermodel.FormulaEvaluator; //導入方法依賴的package包/類
@Test
public void testRead_FORMULA_STRING() throws IOException {
	try (Workbook workbook = new XSSFWorkbook()) {
		// 準備
		Sheet sheet = workbook.createSheet();
		Row row0 = sheet.createRow(0);
		row0.createCell(0).setCellFormula("\"CELL\"&\"00\"");
		row0.createCell(1).setCellFormula("\"CELL\"&\"01\"");
		FormulaEvaluator evaluator = workbook.getCreationHelper().createFormulaEvaluator();
		evaluator.evaluateAll();

		// 実行＆検証
		try (ExcelReader reader = new ExcelReader(workbook)) {

			String[] r0 = reader.read();
			assertNotNull(r0);
			assertEquals(2, r0.length);
			assertEquals("CELL00", r0[0]);
			assertEquals("CELL01", r0[1]);

			assertNull(reader.read());
		}
	}
}
 

import org.apache.poi.ss.usermodel.FormulaEvaluator; //導入方法依賴的package包/類
@Test
public void testRead_FORMULA_BOOLEAN() throws IOException {
	try (Workbook workbook = new XSSFWorkbook()) {
		// 準備
		Sheet sheet = workbook.createSheet();
		Row row0 = sheet.createRow(0);
		row0.createCell(0).setCellFormula("1=1");
		row0.createCell(1).setCellFormula("1=0");
		FormulaEvaluator evaluator = workbook.getCreationHelper().createFormulaEvaluator();
		evaluator.evaluateAll();

		// 実行＆検証
		try (ExcelReader reader = new ExcelReader(workbook)) {

			String[] r0 = reader.read();
			assertNotNull(r0);
			assertEquals(2, r0.length);
			assertEquals("true", r0[0]);
			assertEquals("false", r0[1]);

			assertNull(reader.read());
		}
	}
}
 

import org.apache.poi.ss.usermodel.FormulaEvaluator; //導入方法依賴的package包/類
public static void evaluateFormulas(String fileName) throws IOException {
  xLogger.fine("Entering evaluateFormulas. fileName: {0}", fileName);
  // Create a InoutStream from the bytes in the cloud storage.
  // Create a template workbook
  // Evaluate the formulas
  // Save the workbook.
  if (fileName == null || fileName.isEmpty()) {
    xLogger.severe("Cannot evaluate formulas in a null or empty file");
    return;
  }

  InputStream is = null;
  OutputStream os = null;
  // Create a workbook from the bytes
  try {
    // Get the template bytes from GCS ( Note: By now the data has been added to the appropriate sheet/s)
    is = _storageUtil.getInputStream(CustomReportsExportMgr.CUSTOMREPORTS_BUCKETNAME, fileName);
    if (is == null) {
      xLogger.severe("Failed to create Input stream for {0}", fileName);
      return;
    }
    Workbook
        templateWb =
        WorkbookFactory.create(
            is); // From the bytes downloaded from the google cloud storage, form the Workbook
    if (templateWb != null) {
      CreationHelper createHelper = templateWb.getCreationHelper();
      xLogger.fine("Created createHelper. {0}", createHelper);
      if (createHelper != null) {
        FormulaEvaluator evaluator = createHelper.createFormulaEvaluator();
        xLogger.fine("Created evaluator. {0}", evaluator);
        if (evaluator != null) {
          evaluator.evaluateAll();
          xLogger.fine("After evaluator.evaluateAll");
          templateWb.setForceFormulaRecalculation(
              true); // Added this line because some formula cells were not getting updated even after calling evaluateAll
          xLogger.fine("After templateWb.setForceFormulaRecalculation");
          // Write to file
          xLogger.fine("Now creating baos");
          os =
              _storageUtil
                  .getOutputStream(CustomReportsExportMgr.CUSTOMREPORTS_BUCKETNAME, fileName,
                      false);
          xLogger.fine("os: {0}", os);
          templateWb.write(os); // Write the workbook to OutputStream
          xLogger.fine("Wrote templateWb to baos");
        } // end if evaluator != null
      } // end if createHelper != null
    } // end if templateWb != null
  } catch (Exception e) {
    xLogger.severe("{0} while evaluating formulas in the file {1}. Message: {2}",
        e.getClass().getName(), fileName, e.getMessage(), e);
  } finally {
    if (is != null) {
      is.close();
    }
    if (os != null) {
      os.close();
    }
  }
  xLogger.fine("Exiting evaluateFormulas");
}
 

import org.apache.poi.ss.usermodel.FormulaEvaluator; //導入方法依賴的package包/類
/**
 * Final formatting of the sheet upon completion of writing the data. For
 * example, we can only size the column widths once the data is in the
 * report and the sheet knows how wide the data is.
 */
protected void finalSheetFormat() {
    final FormulaEvaluator evaluator = workbook.getCreationHelper().createFormulaEvaluator();
    if (isHierarchical()) {
        /*
         * evaluateInCell() is equivalent to paste special -> value. The formula refers to cells
         * in the other sheet we are going to delete. We sum in the other sheet because if we
         * summed in the main sheet, we would double count. Subtotal with hidden rows is not yet
         * implemented in POI.
         */
        for (final Row r : sheet) {
            for (final Cell c : r) {
                if (c.getCellTypeEnum() == CellType.FORMULA) {
                    evaluator.evaluateInCell(c);
                }
            }
        }
        workbook.setActiveSheet(workbook.getSheetIndex(sheet));
        if (hierarchicalTotalsSheet != null) {
            workbook.removeSheetAt(workbook.getSheetIndex(hierarchicalTotalsSheet));
        }
    } else {
        evaluator.evaluateAll();
    }
    for (int col = 0; col < getPropIds().size(); col++) {
        sheet.autoSizeColumn(col);
    }
}
 

import org.apache.poi.ss.usermodel.FormulaEvaluator; //導入方法依賴的package包/類
@Test
public void excerptGoesReadOnly() throws Exception
{
   for (Workbook wb : new Workbook[] { new HSSFWorkbook(), new XSSFWorkbook() })
   {
      FormulaEvaluator eval = wb.getCreationHelper().createFormulaEvaluator();
            
      Sheet s = wb.createSheet("Test");
      
      // Numeric formulas
      Row r1 = s.createRow(0);
      Cell c1 = r1.createCell(0);
      Cell c2 = r1.createCell(1);
      Cell c3 = r1.createCell(2);
      Cell c4 = r1.createCell(3);
      
      c1.setCellValue(1);
      c2.setCellValue(2);
      c3.setCellFormula("A1+B1");
      c4.setCellFormula("(A1+B1)*B1");

      // Strings, booleans and errors
      Row r2 = s.createRow(1);
      Cell c21 = r2.createCell(0);
      Cell c22 = r2.createCell(1);
      Cell c23 = r2.createCell(2);
      Cell c24 = r2.createCell(3);
      
      c21.setCellValue("Testing");
      c22.setCellFormula("CONCATENATE(A2,A2)");
      c23.setCellFormula("FALSE()");
      c24.setCellFormula("A1/0");
      
      // Ensure the formulas are current
      eval.evaluateAll();
      
      
      // Run the excerpt
      File tmp = File.createTempFile("test", ".xls");
      wb.write(new FileOutputStream(tmp));
      
      ByteArrayOutputStream baos = new ByteArrayOutputStream();
      excerpter.excerpt(new int[] {0}, tmp, baos);
      
      
      // Check
      Workbook newwb = WorkbookFactory.create(new ByteArrayInputStream(baos.toByteArray()));
      assertEquals(1, newwb.getNumberOfSheets());
      
      s = newwb.getSheetAt(0);
      r1 = s.getRow(0);
      assertEquals(Cell.CELL_TYPE_NUMERIC, r1.getCell(0).getCellType());
      assertEquals(Cell.CELL_TYPE_NUMERIC, r1.getCell(1).getCellType());
      assertEquals(Cell.CELL_TYPE_NUMERIC, r1.getCell(2).getCellType());
      assertEquals(Cell.CELL_TYPE_NUMERIC, r1.getCell(3).getCellType());

      assertEquals(1.0, s.getRow(0).getCell(0).getNumericCellValue(), 0.001);
      assertEquals(2.0, s.getRow(0).getCell(1).getNumericCellValue(), 0.001);
      assertEquals(3.0, s.getRow(0).getCell(2).getNumericCellValue(), 0.001);
      assertEquals(6.0, s.getRow(0).getCell(3).getNumericCellValue(), 0.001);
      
      r2 = s.getRow(1);
      assertEquals(Cell.CELL_TYPE_STRING,  r2.getCell(0).getCellType());
      assertEquals(Cell.CELL_TYPE_STRING,  r2.getCell(1).getCellType());
      assertEquals(Cell.CELL_TYPE_BOOLEAN, r2.getCell(2).getCellType());
      assertEquals(Cell.CELL_TYPE_BLANK,   r2.getCell(3).getCellType());

      assertEquals("Testing",        s.getRow(1).getCell(0).getStringCellValue());
      assertEquals("TestingTesting", s.getRow(1).getCell(1).getStringCellValue());
      assertEquals(false, s.getRow(1).getCell(2).getBooleanCellValue());
   }
}
 

import org.apache.poi.ss.usermodel.FormulaEvaluator; //導入方法依賴的package包/類
private void processXls(InputStream stream, ClientSession session, String className) {
		try {
			HSSFWorkbook myWorkBook = new HSSFWorkbook(stream);
			FormulaEvaluator evaluator = myWorkBook.getCreationHelper().createFormulaEvaluator();
			evaluator.evaluateAll();

			HSSFSheet sheet = myWorkBook.getSheetAt(0);
			
			ArrayList<ArrayList<HSSFCell>> sheetData = new ArrayList<ArrayList<HSSFCell>>();
			for (Iterator<Row> ir = sheet.rowIterator(); ir.hasNext();) {
				HSSFRow row = (HSSFRow) ir.next();
				ArrayList<HSSFCell> rowData = new ArrayList<HSSFCell>();
								
				int minColIx = row.getFirstCellNum();
				int maxColIx = row.getLastCellNum();
				for (int colIx = minColIx; colIx < maxColIx; colIx++) {
					HSSFCell cell = row.getCell(colIx);
					rowData.add(cell);
//					if (cell == null) {
//						continue;
//					}
					// ... do something with cell
				}
				
//				for (Iterator<Cell> ic = row.cellIterator(); ic.hasNext();) {
//					HSSFCell myCell = (HSSFCell) ic.next();
//					// AtomTools.log(Level.INFO, "" +
//					// myCell.getStringCellValue(), this);
//					// rowData.add(myCell.getStringCellValue());
//					rowData.add(myCell);
//				}
				sheetData.add(rowData);
			}

			processData(sheetData, session, className);

		} catch (Throwable t) {
			AtomTools.log(Level.SEVERE, "UploadImportServlet doPost uploaded file could not be parsed as HSSFWorkbook!! " + t.getMessage(), this, t);
			throw new AtomException(t);
		}
	}
 

import org.apache.poi.ss.usermodel.FormulaEvaluator; //導入方法依賴的package包/類
private void merge(Workbook excerptWB, Workbook fullWB, String[] sheetsToMerge, OutputStream output) throws IOException
{
   // Identify the sheets in both workbooks
   List<Sheet> sourceSheets = identifySheets(sheetsToMerge, excerptWB);
   List<Sheet> destSheets = identifySheets(sheetsToMerge, fullWB);

   // Process each sheet from the excerpt in turn
   for (int i=0; i<sheetsToMerge.length; i++)
   {
      Sheet source = sourceSheets.get(i);
      Sheet dest = destSheets.get(i);

      for (Row srcR : source)
      {
         for (Cell srcC : srcR)
         {
            if (srcC.getCellType() == Cell.CELL_TYPE_FORMULA ||
                srcC.getCellType() == Cell.CELL_TYPE_ERROR)
            {
               // Don't merge these kinds of cells
            }
            else
            {
               Row destR = dest.getRow(srcR.getRowNum());
               if (destR == null)
               {
                  // Newly added row to the excerpt file, skip this
               }
               else
               {
                  Cell destC = destR.getCell(srcC.getColumnIndex());
                  if (destC == null && srcC.getCellType() == Cell.CELL_TYPE_BLANK)
                  {
                     // Both are empty, don't need to do anything
                  }
                  else
                  {
                     if (destC == null)
                        destC = destR.createCell(srcC.getColumnIndex(), srcC.getCellType());

                     // Sync contents
                     if (srcC.getCellType() == Cell.CELL_TYPE_BLANK)
                     {
                        destC.setCellType(Cell.CELL_TYPE_BLANK);
                     }
                     else if (srcC.getCellType() == Cell.CELL_TYPE_BOOLEAN)
                     {
                        destC.setCellValue(srcC.getBooleanCellValue());
                     }
                     else if (srcC.getCellType() == Cell.CELL_TYPE_NUMERIC)
                     {
                        destC.setCellValue(srcC.getNumericCellValue());
                     }
                     else if (srcC.getCellType() == Cell.CELL_TYPE_STRING)
                     {
                        destC.setCellValue(srcC.getStringCellValue());
                     }

                     // Sync formatting rules
                     // TODO
                  }
               }
            }
         }
      }
   }

   // Re-evaluate all the formulas in the destination workbook, now that
   //  we have updated cells in it
   FormulaEvaluator eval = fullWB.getCreationHelper().createFormulaEvaluator();
   eval.evaluateAll();

   // Save the new file
   fullWB.write(output);
}