public void TestClassCast_bug44861()
{
HSSFWorkbook wb = HSSFTestDataSamples.OpenSampleWorkbook("44861.xls");
// Check direct
HSSFFormulaEvaluator.EvaluateAllFormulaCells(wb);
// And via calls
int numSheets = wb.NumberOfSheets;
for (int i = 0; i < numSheets; i++)
{
NPOI.SS.UserModel.Sheet s = wb.GetSheetAt(i);
HSSFFormulaEvaluator eval = new HSSFFormulaEvaluator(wb);
for (IEnumerator rows = s.GetRowEnumerator(); rows.MoveNext();)
{
Row r = (Row)rows.Current;
for (IEnumerator cells = r.GetCellEnumerator(); cells.MoveNext();)
{
Cell c = (Cell)cells.Current;
eval.EvaluateFormulaCell(c);
}
}
}
}
private static void Process(HSSFWorkbook wb)
{
HSSFFormulaEvaluator eval = new HSSFFormulaEvaluator(wb);
for (int i = 0; i < wb.NumberOfSheets; i++)
{
NPOI.SS.UserModel.Sheet s = wb.GetSheetAt(i);
IEnumerator it = s.GetRowEnumerator();
while (it.MoveNext())
{
Row r = (Row)it.Current;
Process(r, eval);
}
}
}
public void TestAll()
{
HSSFWorkbook wb = HSSFTestDataSamples.OpenSampleWorkbook("yearfracExamples.xls");
NPOI.SS.UserModel.Sheet sheet = wb.GetSheetAt(0);
HSSFFormulaEvaluator formulaEvaluator = new HSSFFormulaEvaluator(wb);
int nSuccess = 0;
int nFailures = 0;
int nUnexpectedErrors = 0;
IEnumerator rowIterator = sheet.GetRowEnumerator();
while (rowIterator.MoveNext())
{
Row row = (Row)rowIterator.Current;
Cell cell = row.GetCell(SS.YEARFRAC_FORMULA_COLUMN);
if (cell == null || cell.CellType != NPOI.SS.UserModel.CellType.FORMULA)
{
continue;
}
try
{
ProcessRow(row, cell, formulaEvaluator);
nSuccess++;
}
catch (AssertFailedException e)
{
nFailures++;
}
catch (Exception e)
{
nUnexpectedErrors++;
}
}
if (nUnexpectedErrors + nFailures > 0)
{
String msg = nFailures + " failures(s) and " + nUnexpectedErrors
+ " unexpected errors(s) occurred. See stderr for details";
throw new AssertFailedException(msg);
}
if (nSuccess < 1)
{
throw new Exception("No test sample cases found");
}
}
/**
* Loops over all cells in all sheets of the supplied
* workbook.
* For cells that contain formulas, their formulas are
* Evaluated, and the results are saved. These cells
* remain as formula cells.
* For cells that do not contain formulas, no Changes
* are made.
* This is a helpful wrapper around looping over all
* cells, and calling EvaluateFormulaCell on each one.
*/
public static void EvaluateAllFormulaCells(HSSFWorkbook wb)
{
for (int i = 0; i < wb.NumberOfSheets; i++)
{
NPOI.SS.UserModel.Sheet sheet = wb.GetSheetAt(i);
HSSFFormulaEvaluator evaluator = new HSSFFormulaEvaluator(sheet, wb);
for (IEnumerator rit = sheet.GetRowEnumerator(); rit.MoveNext();)
{
HSSFRow r = (HSSFRow)rit.Current;
//evaluator.SetCurrentRow(r);
for (IEnumerator cit = r.GetCellEnumerator(); cit.MoveNext();)
{
NPOI.SS.UserModel.Cell c = (HSSFCell)cit.Current;
if (c.CellType == NPOI.SS.UserModel.CellType.FORMULA)
{
evaluator.EvaluateFormulaCell(c);
}
}
}
}
}
public void TestEvaluateAll()
{
HSSFWorkbook wb = new HSSFWorkbook();
NPOI.SS.UserModel.Sheet s1 = wb.CreateSheet();
NPOI.SS.UserModel.Sheet s2 = wb.CreateSheet();
wb.SetSheetName(0, "S1");
wb.SetSheetName(1, "S2");
Row s1r1 = s1.CreateRow(0);
Row s1r2 = s1.CreateRow(1);
Row s2r1 = s2.CreateRow(0);
Cell s1r1c1 = s1r1.CreateCell(0);
Cell s1r1c2 = s1r1.CreateCell(1);
Cell s1r1c3 = s1r1.CreateCell(2);
s1r1c1.SetCellValue(22.3);
s1r1c2.SetCellValue(33.4);
s1r1c3.CellFormula = ("SUM(A1:B1)");
Cell s1r2c1 = s1r2.CreateCell(0);
Cell s1r2c2 = s1r2.CreateCell(1);
Cell s1r2c3 = s1r2.CreateCell(2);
s1r2c1.SetCellValue(-1.2);
s1r2c2.SetCellValue(-3.4);
s1r2c3.CellFormula = ("SUM(A2:B2)");
Cell s2r1c1 = s2r1.CreateCell(0);
s2r1c1.CellFormula = ("S1!A1");
// Not Evaluated yet
Assert.AreEqual(0.0, s1r1c3.NumericCellValue, 0);
Assert.AreEqual(0.0, s1r2c3.NumericCellValue, 0);
Assert.AreEqual(0.0, s2r1c1.NumericCellValue, 0);
// Do a full Evaluate, as per our docs
// uses EvaluateFormulaCell()
for (int sheetNum = 0; sheetNum < wb.NumberOfSheets; sheetNum++)
{
NPOI.SS.UserModel.Sheet sheet = wb.GetSheetAt(sheetNum);
HSSFFormulaEvaluator evaluator = new HSSFFormulaEvaluator(wb);
for (IEnumerator rit = sheet.GetRowEnumerator(); rit.MoveNext();)
{
Row r = (Row)rit.Current;
for (IEnumerator cit = r.GetCellEnumerator(); cit.MoveNext();)
{
Cell c = (Cell)cit.Current;
if (c.CellType == NPOI.SS.UserModel.CellType.FORMULA)
{
evaluator.EvaluateFormulaCell(c);
// For Testing - all should be numeric
Assert.AreEqual(NPOI.SS.UserModel.CellType.NUMERIC, evaluator.EvaluateFormulaCell(c));
}
}
}
}
// Check now as expected
Assert.AreEqual(55.7, wb.GetSheetAt(0).GetRow(0).GetCell(2).NumericCellValue, 0);
Assert.AreEqual("SUM(A1:B1)", wb.GetSheetAt(0).GetRow(0).GetCell(2).CellFormula);
Assert.AreEqual(NPOI.SS.UserModel.CellType.FORMULA, wb.GetSheetAt(0).GetRow(0).GetCell(2).CellType);
Assert.AreEqual(-4.6, wb.GetSheetAt(0).GetRow(1).GetCell(2).NumericCellValue, 0);
Assert.AreEqual("SUM(A2:B2)", wb.GetSheetAt(0).GetRow(1).GetCell(2).CellFormula);
Assert.AreEqual(NPOI.SS.UserModel.CellType.FORMULA, wb.GetSheetAt(0).GetRow(1).GetCell(2).CellType);
Assert.AreEqual(22.3, wb.GetSheetAt(1).GetRow(0).GetCell(0).NumericCellValue, 0);
Assert.AreEqual("'S1'!A1", wb.GetSheetAt(1).GetRow(0).GetCell(0).CellFormula);
Assert.AreEqual(NPOI.SS.UserModel.CellType.FORMULA, wb.GetSheetAt(1).GetRow(0).GetCell(0).CellType);
// Now do the alternate call, which zaps the formulas
// uses EvaluateInCell()
for (int sheetNum = 0; sheetNum < wb.NumberOfSheets; sheetNum++)
{
NPOI.SS.UserModel.Sheet sheet = wb.GetSheetAt(sheetNum);
HSSFFormulaEvaluator evaluator = new HSSFFormulaEvaluator(wb);
for (IEnumerator rit = sheet.GetRowEnumerator(); rit.MoveNext();)
{
Row r = (Row)rit.Current;
for (IEnumerator cit = r.GetCellEnumerator(); cit.MoveNext();)
{
Cell c = (Cell)cit.Current;
if (c.CellType == NPOI.SS.UserModel.CellType.FORMULA)
{
evaluator.EvaluateInCell(c);
}
}
}
}
Assert.AreEqual(55.7, wb.GetSheetAt(0).GetRow(0).GetCell(2).NumericCellValue, 0);
Assert.AreEqual(NPOI.SS.UserModel.CellType.NUMERIC, wb.GetSheetAt(0).GetRow(0).GetCell(2).CellType);
Assert.AreEqual(-4.6, wb.GetSheetAt(0).GetRow(1).GetCell(2).NumericCellValue, 0);
Assert.AreEqual(NPOI.SS.UserModel.CellType.NUMERIC, wb.GetSheetAt(0).GetRow(1).GetCell(2).CellType);
Assert.AreEqual(22.3, wb.GetSheetAt(1).GetRow(0).GetCell(0).NumericCellValue, 0);
Assert.AreEqual(NPOI.SS.UserModel.CellType.NUMERIC, wb.GetSheetAt(1).GetRow(0).GetCell(0).CellType);
}
/// <summary>
/// Goes through the Workbook, optimising the fonts by
/// removing duplicate ones.
/// For now, only works on fonts used in HSSFCellStyle
/// and HSSFRichTextString. Any other font uses
/// (eg charts, pictures) may well end up broken!
/// This can be a slow operation, especially if you have
/// lots of cells, cell styles or rich text strings
/// </summary>
/// <param name="workbook">The workbook in which to optimise the fonts</param>
public static void OptimiseFonts(HSSFWorkbook workbook)
{
// Where each font has ended up, and if we need to
// delete the record for it. Start off with no change
short[] newPos =
new short[workbook.Workbook.NumberOfFontRecords + 1];
bool[] zapRecords = new bool[newPos.Length];
for (int i = 0; i < newPos.Length; i++)
{
newPos[i] = (short)i;
zapRecords[i] = false;
}
// Get each font record, so we can do deletes
// without Getting confused
FontRecord[] frecs = new FontRecord[newPos.Length];
for (int i = 0; i < newPos.Length; i++)
{
// There is no 4!
if (i == 4)
{
continue;
}
frecs[i] = workbook.Workbook.GetFontRecordAt(i);
}
// Loop over each font, seeing if it is the same
// as an earlier one. If it is, point users of the
// later duplicate copy to the earlier one, and
// mark the later one as needing deleting
// Note - don't change built in fonts (those before 5)
for (int i = 5; i < newPos.Length; i++)
{
// Check this one for being a duplicate
// of an earlier one
int earlierDuplicate = -1;
for (int j = 0; j < i && earlierDuplicate == -1; j++)
{
if (j == 4)
{
continue;
}
FontRecord frCheck = workbook.Workbook.GetFontRecordAt(j);
if (frCheck.SameProperties(frecs[i]))
{
earlierDuplicate = j;
}
}
// If we got a duplicate, mark it as such
if (earlierDuplicate != -1)
{
newPos[i] = (short)earlierDuplicate;
zapRecords[i] = true;
}
}
// Update the new positions based on
// deletes that have occurred between
// the start and them
// Only need to worry about user fonts
for (int i = 5; i < newPos.Length; i++)
{
// Find the number deleted to that
// point, and adjust
short preDeletePos = newPos[i];
short newPosition = preDeletePos;
for (int j = 0; j < preDeletePos; j++)
{
if (zapRecords[j])
{
newPosition--;
}
}
// Update the new position
newPos[i] = newPosition;
}
// Zap the un-needed user font records
for (int i = 5; i < newPos.Length; i++)
{
if (zapRecords[i])
{
workbook.Workbook.RemoveFontRecord(
frecs[i]
);
}
}
// Tell HSSFWorkbook that it needs to
// re-start its HSSFFontCache
workbook.ResetFontCache();
// Update the cell styles to point at the
// new locations of the fonts
for (int i = 0; i < workbook.Workbook.NumExFormats; i++)
{
ExtendedFormatRecord xfr = workbook.Workbook.GetExFormatAt(i);
xfr.FontIndex = (
newPos[xfr.FontIndex]
);
}
// Update the rich text strings to point at
// the new locations of the fonts
// Remember that one underlying unicode string
// may be shared by multiple RichTextStrings!
ArrayList doneUnicodeStrings = new ArrayList();
for (int sheetNum = 0; sheetNum < workbook.NumberOfSheets; sheetNum++)
{
NPOI.SS.UserModel.Sheet s = workbook.GetSheetAt(sheetNum);
IEnumerator rIt = s.GetRowEnumerator();
while (rIt.MoveNext())
{
HSSFRow row = (HSSFRow)rIt.Current;
IEnumerator cIt = row.GetCellEnumerator();
while (cIt.MoveNext())
{
Cell cell = (HSSFCell)cIt.Current;
if (cell.CellType == NPOI.SS.UserModel.CellType.STRING)
{
HSSFRichTextString rtr = (HSSFRichTextString)cell.RichStringCellValue;
UnicodeString u = rtr.RawUnicodeString;
// Have we done this string already?
if (!doneUnicodeStrings.Contains(u))
{
// Update for each new position
for (short i = 5; i < newPos.Length; i++)
{
if (i != newPos[i])
{
u.SwapFontUse(i, newPos[i]);
}
}
// Mark as done
doneUnicodeStrings.Add(u);
}
}
}
}
}
}