/**
* Get the format index that matches the given format string, creating a new format entry if
* required. Aliases text to the proper format as required.
*
* @param pFormat string matching a built in format
* @return index of format.
*/
public short getFormat(String pFormat) {
// Normalise the format string
String format;
if (pFormat.toUpperCase(Locale.ROOT).equals("TEXT")) {
format = "@";
} else {
format = pFormat;
}
// Merge in the built in formats if we haven't already
if (!_movedBuiltins) {
for (int i = 0; i < _builtinFormats.length; i++) {
ensureFormatsSize(i);
if (_formats.get(i) == null) {
_formats.set(i, _builtinFormats[i]);
} else {
// The workbook overrides this default format
}
}
_movedBuiltins = true;
}
// See if we can find it
for (int i = 0; i < _formats.size(); i++) {
if (format.equals(_formats.get(i))) {
return (short) i;
}
}
// We can't find it, so add it as a new one
short index = _workbook.getFormat(format, true);
ensureFormatsSize(index);
_formats.set(index, format);
return index;
}
public void testContents() {
assertEquals(2, listener.getSSTRecord().getNumStrings());
assertEquals(3, listener.getBoundSheetRecords().length);
assertEquals(1, listener.getExternSheetRecords().length);
assertEquals(3, listener.getStubWorkbook().getNumSheets());
InternalWorkbook ref = listener.getStubWorkbook();
assertEquals("Sh3", ref.findSheetFirstNameFromExternSheet(0));
assertEquals("Sheet1", ref.findSheetFirstNameFromExternSheet(1));
assertEquals("S2", ref.findSheetFirstNameFromExternSheet(2));
}
/**
* Constructs a new data formatter. It takes a workbook to have access to the workbooks format
* records.
*
* @param workbook the workbook the formats are tied to.
*/
HSSFDataFormat(InternalWorkbook workbook) {
_workbook = workbook;
Iterator<FormatRecord> i = workbook.getFormats().iterator();
while (i.hasNext()) {
FormatRecord r = i.next();
ensureFormatsSize(r.getIndexCode());
_formats.set(r.getIndexCode(), r.getFormatString());
}
}
public void testFormulas() {
FormulaRecord[] fRecs = mockListen.getFormulaRecords();
// Check our formula records
assertEquals(6, fRecs.length);
InternalWorkbook stubWB = listener.getStubWorkbook();
assertNotNull(stubWB);
HSSFWorkbook stubHSSF = listener.getStubHSSFWorkbook();
assertNotNull(stubHSSF);
// Check these stubs have the right stuff on them
assertEquals("Sheet1", stubWB.getSheetName(0));
assertEquals("Sheet1", stubHSSF.getSheetName(0));
assertEquals("S2", stubWB.getSheetName(1));
assertEquals("S2", stubHSSF.getSheetName(1));
assertEquals("Sh3", stubWB.getSheetName(2));
assertEquals("Sh3", stubHSSF.getSheetName(2));
// Check we can get the formula without breaking
for (int i = 0; i < fRecs.length; i++) {
HSSFFormulaParser.toFormulaString(stubHSSF, fRecs[i].getParsedExpression());
}
// Peer into just one formula, and check that
// all the ptgs give back the right things
Ptg[] ptgs = fRecs[0].getParsedExpression();
assertEquals(1, ptgs.length);
assertTrue(ptgs[0] instanceof Ref3DPtg);
Ref3DPtg ptg = (Ref3DPtg) ptgs[0];
HSSFEvaluationWorkbook book = HSSFEvaluationWorkbook.create(stubHSSF);
assertEquals("Sheet1!A1", ptg.toFormulaString(book));
// Now check we get the right formula back for
// a few sample ones
FormulaRecord fr;
// Sheet 1 A2 is on same sheet
fr = fRecs[0];
assertEquals(1, fr.getRow());
assertEquals(0, fr.getColumn());
assertEquals(
"Sheet1!A1", HSSFFormulaParser.toFormulaString(stubHSSF, fr.getParsedExpression()));
// Sheet 1 A5 is to another sheet
fr = fRecs[3];
assertEquals(4, fr.getRow());
assertEquals(0, fr.getColumn());
assertEquals("'S2'!A1", HSSFFormulaParser.toFormulaString(stubHSSF, fr.getParsedExpression()));
// Sheet 1 A7 is to another sheet, range
fr = fRecs[5];
assertEquals(6, fr.getRow());
assertEquals(0, fr.getColumn());
assertEquals(
"SUM(Sh3!A1:A4)", HSSFFormulaParser.toFormulaString(stubHSSF, fr.getParsedExpression()));
// Now, load via Usermodel and re-check
HSSFWorkbook wb = HSSFTestDataSamples.openSampleWorkbook("3dFormulas.xls");
assertEquals("Sheet1!A1", wb.getSheetAt(0).getRow(1).getCell(0).getCellFormula());
assertEquals("SUM(Sh3!A1:A4)", wb.getSheetAt(0).getRow(6).getCell(0).getCellFormula());
}
