testevaluationcache.java

来自「EXCEL read and write」· Java 代码 · 共 611 行 · 第 1/2 页

/* ====================================================================
   Licensed to the Apache Software Foundation (ASF) under one or more
   contributor license agreements.  See the NOTICE file distributed with
   this work for additional information regarding copyright ownership.
   The ASF licenses this file to You under the Apache License, Version 2.0
   (the "License"); you may not use this file except in compliance with
   the License.  You may obtain a copy of the License at

       http://www.apache.org/licenses/LICENSE-2.0

   Unless required by applicable law or agreed to in writing, software
   distributed under the License is distributed on an "AS IS" BASIS,
   WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
   See the License for the specific language governing permissions and
   limitations under the License.
==================================================================== */

package org.apache.poi.ss.formula;

import java.io.PrintStream;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.Comparator;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import junit.framework.AssertionFailedError;
import junit.framework.TestCase;

import org.apache.poi.hssf.model.HSSFFormulaParser;
import org.apache.poi.hssf.record.formula.Ptg;
import org.apache.poi.hssf.record.formula.eval.BlankEval;
import org.apache.poi.hssf.record.formula.eval.BoolEval;
import org.apache.poi.hssf.record.formula.eval.ErrorEval;
import org.apache.poi.hssf.record.formula.eval.NumberEval;
import org.apache.poi.hssf.record.formula.eval.StringEval;
import org.apache.poi.hssf.record.formula.eval.ValueEval;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFEvaluationTestHelper;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.CellReference;
import org.apache.poi.ss.formula.PlainCellCache.Loc;

/**
 * Tests {@link EvaluationCache}.  Makes sure that where possible (previously calculated) cached 
 * values are used.  Also checks that changing cell values causes the correct (minimal) set of
 * dependent cached values to be cleared.
 *
 * @author Josh Micich
 */
public class TestEvaluationCache extends TestCase {
	
	private static final class FormulaCellCacheEntryComparer implements Comparator {

		private final Map _formulaCellsByCacheEntry;

		public FormulaCellCacheEntryComparer(Map formulaCellsByCacheEntry) {
			_formulaCellsByCacheEntry = formulaCellsByCacheEntry;
		}
		private EvaluationCell getCell(Object a) {
			return (EvaluationCell)_formulaCellsByCacheEntry.get(a);
		}

		public int compare(Object oa, Object ob) {
			EvaluationCell a = getCell(oa);
			EvaluationCell b = getCell(ob);
			int cmp;
			cmp = a.getRowIndex() - b.getRowIndex();
			if (cmp != 0) {
				return cmp;
			}
			cmp = a.getColumnIndex() - b.getColumnIndex();
			if (cmp != 0) {
				return cmp;
			}
			if (a.getSheet() == b.getSheet()) {
				return 0;
			}
			throw new RuntimeException("Incomplete code - don't know how to order sheets");
		}
	}
	
	private static final class EvalListener extends EvaluationListener {

		private final List _logList;
		private final HSSFWorkbook _book;
		private Map _formulaCellsByCacheEntry;
		private Map _plainCellLocsByCacheEntry;

		public EvalListener(HSSFWorkbook wb) {
			_book = wb;
			_logList = new ArrayList();
			_formulaCellsByCacheEntry = new HashMap();
			_plainCellLocsByCacheEntry = new HashMap();
		}
		public void onCacheHit(int sheetIndex, int rowIndex, int columnIndex, ValueEval result) {
			log("hit", rowIndex, columnIndex, result);
		}
		public void onReadPlainValue(int sheetIndex, int rowIndex, int columnIndex, ICacheEntry entry) {
			Loc loc = new Loc(0, sheetIndex, rowIndex, columnIndex);
			_plainCellLocsByCacheEntry.put(entry, loc);
			log("value", rowIndex, columnIndex, entry.getValue());
		}
		public void onStartEvaluate(EvaluationCell cell, ICacheEntry entry, Ptg[] ptgs) {
			_formulaCellsByCacheEntry.put(entry, cell);
			log("start", cell.getRowIndex(), cell.getColumnIndex(), ptgs);
		}
		public void onEndEvaluate(ICacheEntry entry, ValueEval result) {
			EvaluationCell cell = (EvaluationCell) _formulaCellsByCacheEntry.get(entry);
			log("end", cell.getRowIndex(), cell.getColumnIndex(), result);
		}
		public void onClearCachedValue(ICacheEntry entry) {
			int rowIndex;
			int columnIndex;
			EvaluationCell cell = (EvaluationCell) _formulaCellsByCacheEntry.get(entry);
			if (cell == null) {
				Loc loc = (Loc)_plainCellLocsByCacheEntry.get(entry);
				if (loc == null) {
					throw new IllegalStateException("can't find cell or location");
				}
				rowIndex = loc.getRowIndex();
				columnIndex = loc.getColumnIndex();
			} else {
				rowIndex = cell.getRowIndex();
				columnIndex = cell.getColumnIndex();
			}
			log("clear", rowIndex, columnIndex, entry.getValue());
		}
		public void sortDependentCachedValues(ICacheEntry[] entries) {
			Arrays.sort(entries, new FormulaCellCacheEntryComparer(_formulaCellsByCacheEntry));
		}
		public void onClearDependentCachedValue(ICacheEntry entry, int depth) {
			EvaluationCell cell = (EvaluationCell) _formulaCellsByCacheEntry.get(entry);
   			log("clear" + depth, cell.getRowIndex(), cell.getColumnIndex(),  entry.getValue());
		}
		private void log(String tag, int rowIndex, int columnIndex, Object value) {
			StringBuffer sb = new StringBuffer(64);
			sb.append(tag).append(' ');
			sb.append(new CellReference(rowIndex, columnIndex).formatAsString());
			if (value != null) {
				sb.append(' ').append(formatValue(value));
			}
			_logList.add(sb.toString());
		}
		private String formatValue(Object value) {
			if (value instanceof Ptg[]) {
				Ptg[] ptgs = (Ptg[]) value;
				return HSSFFormulaParser.toFormulaString(_book, ptgs);
			}
			if (value instanceof NumberEval) {
				NumberEval ne = (NumberEval) value;
				return ne.getStringValue();
			}
			if (value instanceof StringEval) {
				StringEval se = (StringEval) value;
				return "'" + se.getStringValue() + "'";
			}
			if (value instanceof BoolEval) {
				BoolEval be = (BoolEval) value;
				return be.getStringValue();
			}
			if (value == BlankEval.INSTANCE) {
				return "#BLANK#";
			}
			if (value instanceof ErrorEval) {
				ErrorEval ee = (ErrorEval) value;
				return ErrorEval.getText(ee.getErrorCode());
			}
			throw new IllegalArgumentException("Unexpected value class ("
					+ value.getClass().getName() + ")");
		}
		public String[] getAndClearLog() {
			String[] result = new String[_logList.size()];
			_logList.toArray(result);
			_logList.clear();
			return result;
		}
	}
	/**
	 * Wrapper class to manage repetitive tasks from this test,
	 *
	 * Note - this class does a little bit more than just plain set-up of data. The method
	 * {@link WorkbookEvaluator#clearCachedResultValue(HSSFSheet, int, int)} is called whenever a
	 * cell value is changed.
	 *
	 */
	private static final class MySheet {

		private final HSSFSheet _sheet;
		private final WorkbookEvaluator _evaluator;
		private final HSSFWorkbook _wb;
		private final EvalListener _evalListener;

		public MySheet() {
			_wb = new HSSFWorkbook();
			_evalListener = new EvalListener(_wb);
			_evaluator = WorkbookEvaluatorTestHelper.createEvaluator(_wb, _evalListener);
			_sheet = _wb.createSheet("Sheet1");
		}

		private static EvaluationCell wrapCell(HSSFCell cell) {
			return HSSFEvaluationTestHelper.wrapCell(cell);
		}

		public void setCellValue(String cellRefText, double value) {
			HSSFCell cell = getOrCreateCell(cellRefText);
			// be sure to blank cell, in case it is currently a formula
			cell.setCellType(HSSFCell.CELL_TYPE_BLANK);
			// otherwise this line will only set the formula cached result;
			cell.setCellValue(value);
			_evaluator.notifyUpdateCell(wrapCell(cell));
		}

		public void setCellFormula(String cellRefText, String formulaText) {
			HSSFCell cell = getOrCreateCell(cellRefText);
			cell.setCellFormula(formulaText);
			_evaluator.notifyUpdateCell(wrapCell(cell));
		}

		private HSSFCell getOrCreateCell(String cellRefText) {
			CellReference cr = new CellReference(cellRefText);
			int rowIndex = cr.getRow();
			HSSFRow row = _sheet.getRow(rowIndex);
			if (row == null) {
				row = _sheet.createRow(rowIndex);
			}
			int cellIndex = cr.getCol();
			HSSFCell cell = row.getCell(cellIndex);
			if (cell == null) {
				cell = row.createCell(cellIndex);
			}
			return cell;
		}

		public ValueEval evaluateCell(String cellRefText) {
			return _evaluator.evaluate(wrapCell(getOrCreateCell(cellRefText)));
		}

		public String[] getAndClearLog() {
			return _evalListener.getAndClearLog();
		}

		public void clearAllCachedResultValues() {
			_evaluator.clearAllCachedResultValues();
		}
	}

	private static MySheet createMediumComplex() {
		MySheet ms = new MySheet();

		// plain data in D1:F3
		ms.setCellValue("D1", 12);
		ms.setCellValue("E1", 13);
		ms.setCellValue("D2", 14);
		ms.setCellValue("E2", 15);
		ms.setCellValue("D3", 16);
		ms.setCellValue("E3", 17);


		ms.setCellFormula("C1", "SUM(D1:E2)");
		ms.setCellFormula("C2", "SUM(D2:E3)");
		ms.setCellFormula("C3", "SUM(D3:E4)");

		ms.setCellFormula("B1", "C2-C1");
		ms.setCellFormula("B2", "B3*C1-C2");
		ms.setCellValue("B3", 2);

		ms.setCellFormula("A1", "MAX(B1:B2)");
		ms.setCellFormula("A2", "MIN(B3,D2:F2)");
		ms.setCellFormula("A3", "B3*C3");

		// clear all the logging from the above initialisation
		ms.getAndClearLog();
		ms.clearAllCachedResultValues();
		return ms;
	}

	public void testMediumComplex() {

		MySheet ms = createMediumComplex();
		// completely fresh evaluation
		confirmEvaluate(ms, "A1", 46);
		confirmLog(ms, new String[] {
			"start A1 MAX(B1:B2)",
				"start B1 C2-C1",
					"start C2 SUM(D2:E3)",
						"value D2 14", "value E2 15", "value D3 16", "value E3 17",
					"end C2 62",
					"start C1 SUM(D1:E2)",
						"value D1 12", "value E1 13", "hit D2 14", "hit E2 15",
					"end C1 54",
				"end B1 8",
				"start B2 B3*C1-C2",
					"value B3 2",
					"hit C1 54",
					"hit C2 62",
				"end B2 46",
			"end A1 46",
		});


		// simple cache hit - immediate re-evaluation with no changes
		confirmEvaluate(ms, "A1", 46);