/* ====================================================================
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.hssf.record.aggregates;
import java.util.Collection;
import java.util.HashMap;
import junit.framework.AssertionFailedError;
import junit.framework.TestCase;
import org.apache.poi.POITestCase;
import org.apache.poi.hssf.HSSFTestDataSamples;
import org.apache.poi.hssf.record.Record;
import org.apache.poi.hssf.record.SharedFormulaRecord;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.usermodel.RecordInspector;
/**
* Tests for {@link SharedValueManager}
*
* @author Josh Micich
*/
public final class TestSharedValueManager extends TestCase {
/**
* This Excel workbook contains two sheets that each have a pair of overlapping shared formula
* ranges. The first sheet has one row and one column shared formula ranges which intersect.
* The second sheet has two column shared formula ranges - one contained within the other.
* These shared formula ranges were created by fill-dragging a single cell formula across the
* desired region. The larger shared formula ranges were placed first.<br/>
*
* There are probably many ways to produce similar effects, but it should be noted that Excel
* is quite temperamental in this regard. Slight variations in technique can cause the shared
* formulas to spill out into plain formula records (which would make these tests pointless).
*
*/
private static final String SAMPLE_FILE_NAME = "overlapSharedFormula.xls";
/**
* Some of these bugs are intermittent, and the test author couldn't think of a way to write
* test code to hit them bug deterministically. The reason for the unpredictability is that
* the bugs depended on the {@link SharedFormulaRecord}s being searched in a particular order.
* At the time of writing of the test, the order was being determined by the call to {@link
* Collection#toArray(Object[])} on {@link HashMap#values()} where the items in the map were
* using default {@link Object#hashCode()}<br/>
*/
private static final int MAX_ATTEMPTS=5;
/**
* This bug happened when there were two or more shared formula ranges that overlapped. POI
* would sometimes associate formulas in the overlapping region with the wrong shared formula
*/
public void testPartiallyOverlappingRanges() {
Record[] records;
int attempt=1;
do {
HSSFWorkbook wb = HSSFTestDataSamples.openSampleWorkbook(SAMPLE_FILE_NAME);
HSSFSheet sheet = wb.getSheetAt(0);
RecordInspector.getRecords(sheet, 0);
assertEquals("1+1", sheet.getRow(2).getCell(0).getCellFormula());
if ("1+1".equals(sheet.getRow(3).getCell(0).getCellFormula())) {
throw new AssertionFailedError("Identified bug - wrong shared formula record chosen"
+ " (attempt " + attempt + ")");
}
assertEquals("2+2", sheet.getRow(3).getCell(0).getCellFormula());
records = RecordInspector.getRecords(sheet, 0);
} while (attempt++ < MAX_ATTEMPTS);
int count=0;
for (Record record : records) {
if (record instanceof SharedFormulaRecord) {
count++;
}
}
assertEquals(2, count);
}
/**
* This bug occurs for similar reasons to the bug in {@link #testPartiallyOverlappingRanges()}
* but the symptoms are much uglier - serialization fails with {@link NullPointerException}.<br/>
*/
public void testCompletelyOverlappedRanges() {
Record[] records;
int attempt=1;
do {
HSSFWorkbook wb = HSSFTestDataSamples.openSampleWorkbook(SAMPLE_FILE_NAME);
HSSFSheet sheet = wb.getSheetAt(1);
try {
records = RecordInspector.getRecords(sheet, 0);
} catch (NullPointerException e) {
throw new AssertionFailedError("Identified bug " +
"- cannot reserialize completely overlapped shared formula"
+ " (attempt " + attempt + ")");
}
} while (attempt++ < MAX_ATTEMPTS);
int count=0;
for (Record record : records) {
if (record instanceof SharedFormulaRecord) {
count++;
}
}
assertEquals(2, count);
}
/**
* Tests fix for a bug in the way shared formula cells are associated with shared formula
* records. Prior to this fix, POI would attempt to use the upper left corner of the
* shared formula range as the locator cell. The correct cell to use is the 'first cell'
* in the shared formula group which is not always the top left cell. This is possible
* because shared formula groups may be sparse and may overlap.<br/>
*
* Two existing sample files (15228.xls and ex45046-21984.xls) had similar issues.
* These were not explored fully, but seem to be fixed now.
*/
public void testRecalculateFormulas47747() {
/*
* ex47747-sharedFormula.xls is a heavily cut-down version of the spreadsheet from
* the attachment (id=24176) in Bugzilla 47747. This was done to make the sample
* file smaller, which hopefully allows the special data encoding condition to be
* seen more easily. Care must be taken when modifying this file since the
* special conditions are easily destroyed (which would make this test useless).
* It seems that removing the worksheet protection has made this more so - if the
* current file is re-saved in Excel(2007) the bug condition disappears.
*
*
* Using BiffViewer, one can see that there are two shared formula groups representing
* the essentially same formula over ~20 cells. The shared group ranges overlap and
* are A12:Q20 and A20:Q27. The locator cell ('first cell') for the second group is
* Q20 which is not the top left cell of the enclosing range. It is this specific
* condition which caused the bug to occur
*/
HSSFWorkbook wb = HSSFTestDataSamples.openSampleWorkbook("ex47747-sharedFormula.xls");
// pick out a cell from within the second shared formula group
HSSFCell cell = wb.getSheetAt(0).getRow(23).getCell(0);
String formulaText;
try {
formulaText = cell.getCellFormula();
// succeeds if the formula record has been associated
// with the second shared formula group
} catch (RuntimeException e) {
// bug occurs if the formula record has been associated
// with the first shared formula group
if ("Shared Formula Conversion: Coding Error".equals(e.getMessage())) {
throw new AssertionFailedError("Identified bug 47747");
}
throw e;
}
assertEquals("$AF24*A$7", formulaText);
}
/**
* Convenience test method for digging the {@link SharedValueManager} out of a
* {@link RowRecordsAggregate}.
*/
public static SharedValueManager extractFromRRA(RowRecordsAggregate rra) {
return POITestCase.getFieldValue(RowRecordsAggregate.class, rra, SharedValueManager.class, "_sharedValueManager");
}
public void testBug52527() {
HSSFWorkbook wb1 = HSSFTestDataSamples.openSampleWorkbook("52527.xls");
HSSFWorkbook wb2 = HSSFTestDataSamples.writeOutAndReadBack(wb1);
assertEquals("IF(H3,LINEST(N9:N14,K9:M14,FALSE),LINEST(N8:N14,K8:M14,FALSE))",
wb1.getSheetAt(0).getRow(4).getCell(11).getCellFormula());
assertEquals("IF(H3,LINEST(N9:N14,K9:M14,FALSE),LINEST(N8:N14,K8:M14,FALSE))",
wb2.getSheetAt(0).getRow(4).getCell(11).getCellFormula());
assertEquals("1/SQRT(J9)",
wb1.getSheetAt(0).getRow(8).getCell(10).getCellFormula());
assertEquals("1/SQRT(J9)",
wb2.getSheetAt(0).getRow(8).getCell(10).getCellFormula());
assertEquals("1/SQRT(J26)",
wb1.getSheetAt(0).getRow(25).getCell(10).getCellFormula());
assertEquals("1/SQRT(J26)",
wb2.getSheetAt(0).getRow(25).getCell(10).getCellFormula());
}
}