private File writeSingleXLS() throws ReportException {
File file = null;
HSSFWorkbook wb = null;
OutputStream out = null;
try {
file = getReportFile();
wb = new HSSFWorkbook();
ReportDefineFrm definedReport = executeFrm.getDefinedReport();
String sheetName = definedReport.getReportName();
HSSFSheet sheet = wb.createSheet(sheetName);
sheet.setDisplayGridlines(false); // 不显示网格线
HSSFCellStyle dataStyle = createDataStyle(wb);
HSSFCellStyle headerStyle = createHeaderStyle(wb);
if (isFirstFile) {
writeDataHeader(sheet, headerStyle);
}
writeData2Excel(sheet, dataStyle, headerStyle);
autoReSizeSheet(sheet);
out = new FileOutputStream(file);
wb.write(out);
isFirstFile = false;
} catch (Throwable ex) {
Logger.logError(ex);
throw new ReportException(ex.getMessage());
} finally {
try {
if (out != null) {
out.close();
}
} catch (IOException ex) {
Logger.logError(ex);
}
}
return file;
}
/**
* All multi-binomial operator tests use this to create a worksheet with a huge set of x operator
* y formulas. Next we call binomialVerify and verify that they are all how we expect.
*/
private static void binomialOperator(String operator) {
HSSFWorkbook wb = new HSSFWorkbook();
HSSFSheet s = wb.createSheet();
HSSFRow r = null;
HSSFCell c = null;
// get our minimum values
r = s.createRow(0);
c = r.createCell(1);
c.setCellFormula(1 + operator + 1);
for (int x = 1; x < Short.MAX_VALUE && x > 0; x = (short) (x * 2)) {
r = s.createRow(x);
for (int y = 1; y < 256 && y > 0; y++) {
c = r.createCell(y);
c.setCellFormula("" + x + operator + y);
}
}
// make sure we do the maximum value of the Int operator
if (s.getLastRowNum() < Short.MAX_VALUE) {
r = s.getRow(0);
c = r.createCell(0);
c.setCellFormula("" + Short.MAX_VALUE + operator + Short.MAX_VALUE);
}
wb = HSSFTestDataSamples.writeOutAndReadBack(wb);
binomialVerify(operator, wb);
}
public void testFullColumnRefs() {
HSSFWorkbook wb = new HSSFWorkbook();
HSSFSheet sheet = wb.createSheet("Sheet1");
HSSFRow row = sheet.createRow(0);
HSSFCell cell0 = row.createCell(0);
cell0.setCellFormula("sum(D:D)");
HSSFCell cell1 = row.createCell(1);
cell1.setCellFormula("sum(D:E)");
// some values in column D
setValue(sheet, 1, 3, 5.0);
setValue(sheet, 2, 3, 6.0);
setValue(sheet, 5, 3, 7.0);
setValue(sheet, 50, 3, 8.0);
// some values in column E
setValue(sheet, 1, 4, 9.0);
setValue(sheet, 2, 4, 10.0);
setValue(sheet, 30000, 4, 11.0);
// some other values
setValue(sheet, 1, 2, 100.0);
setValue(sheet, 2, 5, 100.0);
setValue(sheet, 3, 6, 100.0);
HSSFFormulaEvaluator fe = new HSSFFormulaEvaluator(wb);
assertEquals(26.0, fe.evaluate(cell0).getNumberValue(), 0.0);
assertEquals(56.0, fe.evaluate(cell1).getNumberValue(), 0.0);
}
private static void floatTest(String operator) {
HSSFWorkbook wb = new HSSFWorkbook();
HSSFSheet s = wb.createSheet();
HSSFRow r = null;
HSSFCell c = null;
// get our minimum values
r = s.createRow(0);
c = r.createCell(1);
c.setCellFormula("" + Float.MIN_VALUE + operator + Float.MIN_VALUE);
for (int x = 1; x < Short.MAX_VALUE && x > 0; x = (short) (x * 2)) {
r = s.createRow(x);
for (int y = 1; y < 256 && y > 0; y = (short) (y + 2)) {
c = r.createCell(y);
c.setCellFormula("" + x + "." + y + operator + y + "." + x);
}
}
if (s.getLastRowNum() < Short.MAX_VALUE) {
r = s.createRow(0);
c = r.createCell(0);
c.setCellFormula("" + Float.MAX_VALUE + operator + Float.MAX_VALUE);
}
wb = HSSFTestDataSamples.writeOutAndReadBack(wb);
floatVerify(operator, wb);
}
public void testClearShapesForPatriarch() {
HSSFWorkbook wb = new HSSFWorkbook();
HSSFSheet sheet = wb.createSheet();
HSSFPatriarch patriarch = sheet.createDrawingPatriarch();
patriarch.createSimpleShape(new HSSFClientAnchor());
patriarch.createSimpleShape(new HSSFClientAnchor());
patriarch.createCellComment(new HSSFClientAnchor());
EscherAggregate agg = HSSFTestHelper.getEscherAggregate(patriarch);
assertEquals(agg.getShapeToObjMapping().size(), 6);
assertEquals(agg.getTailRecords().size(), 1);
assertEquals(patriarch.getChildren().size(), 3);
patriarch.clear();
assertEquals(agg.getShapeToObjMapping().size(), 0);
assertEquals(agg.getTailRecords().size(), 0);
assertEquals(patriarch.getChildren().size(), 0);
wb = HSSFTestDataSamples.writeOutAndReadBack(wb);
sheet = wb.getSheetAt(0);
patriarch = sheet.getDrawingPatriarch();
assertEquals(agg.getShapeToObjMapping().size(), 0);
assertEquals(agg.getTailRecords().size(), 0);
assertEquals(patriarch.getChildren().size(), 0);
}
public void testRotation() {
HSSFWorkbook wb = new HSSFWorkbook();
HSSFSheet sheet = wb.createSheet();
HSSFPatriarch patriarch = sheet.createDrawingPatriarch();
HSSFSimpleShape rectangle =
patriarch.createSimpleShape(
new HSSFClientAnchor(0, 0, 100, 100, (short) 0, 0, (short) 5, 5));
rectangle.setShapeType(HSSFSimpleShape.OBJECT_TYPE_RECTANGLE);
assertEquals(rectangle.getRotationDegree(), 0);
rectangle.setRotationDegree((short) 45);
assertEquals(rectangle.getRotationDegree(), 45);
rectangle.setFlipHorizontal(true);
wb = HSSFTestDataSamples.writeOutAndReadBack(wb);
sheet = wb.getSheetAt(0);
patriarch = sheet.getDrawingPatriarch();
rectangle = (HSSFSimpleShape) patriarch.getChildren().get(0);
assertEquals(rectangle.getRotationDegree(), 45);
rectangle.setRotationDegree((short) 30);
assertEquals(rectangle.getRotationDegree(), 30);
patriarch.setCoordinates(0, 0, 10, 10);
rectangle.setString(new HSSFRichTextString("1234"));
}
public void remover(int ID) {
for (int i = 1; i <= planilha.getSheet("Jogos").getLastRowNum(); i++) {
if (planilha.getSheet("Jogos").getRow(i).getCell(0).getNumericCellValue() == ID) {
HSSFRow novaRow = this.planilha.getSheet("Jogos").createRow(i);
novaRow.createCell(0).setCellValue(0);
novaRow.createCell(1).setCellValue("");
novaRow.createCell(2).setCellValue("");
novaRow.createCell(3).setCellValue("");
novaRow.createCell(4).setCellValue("");
novaRow.createCell(5).setCellValue("");
novaRow.createCell(6).setCellValue("");
FileOutputStream nFile = null;
try {
nFile = new FileOutputStream("Jogos.xls");
} catch (FileNotFoundException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
try {
this.planilha.write(nFile);
} catch (IOException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
try {
nFile.close();
} catch (IOException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
}
/**
* Test get new id for shapes from existing file File already have for 1 shape on each sheet,
* because document must contain EscherDgRecord for each sheet
*/
public void testAllocateNewIds() {
HSSFWorkbook wb = HSSFTestDataSamples.openSampleWorkbook("empty.xls");
HSSFSheet sheet = wb.getSheetAt(0);
HSSFPatriarch patriarch = sheet.getDrawingPatriarch();
/** 2048 - main SpContainer id 2049 - existing shape id */
assertEquals(HSSFTestHelper.allocateNewShapeId(patriarch), 2050);
assertEquals(HSSFTestHelper.allocateNewShapeId(patriarch), 2051);
assertEquals(HSSFTestHelper.allocateNewShapeId(patriarch), 2052);
sheet = wb.getSheetAt(1);
patriarch = sheet.getDrawingPatriarch();
/** 3072 - main SpContainer id 3073 - existing shape id */
assertEquals(HSSFTestHelper.allocateNewShapeId(patriarch), 3074);
assertEquals(HSSFTestHelper.allocateNewShapeId(patriarch), 3075);
assertEquals(HSSFTestHelper.allocateNewShapeId(patriarch), 3076);
sheet = wb.getSheetAt(2);
patriarch = sheet.getDrawingPatriarch();
assertEquals(HSSFTestHelper.allocateNewShapeId(patriarch), 1026);
assertEquals(HSSFTestHelper.allocateNewShapeId(patriarch), 1027);
assertEquals(HSSFTestHelper.allocateNewShapeId(patriarch), 1028);
}
public void atualizar(Jogo jogo) {
for (int i = 1; i <= planilha.getSheet("Jogos").getLastRowNum(); i++) {
if (planilha.getSheet("Jogos").getRow(i).getCell(0).getNumericCellValue() == jogo.getID()) {
HSSFRow novaRow = this.planilha.getSheet("Jogos").createRow(i);
novaRow.createCell(1).setCellValue(jogo.getNome());
novaRow.createCell(2).setCellValue(jogo.getConsole());
novaRow.createCell(3).setCellValue(jogo.getPrecoCompra());
novaRow.createCell(4).setCellValue(jogo.getPrecoVenda());
novaRow.createCell(5).setCellValue(jogo.getQuantidade());
novaRow.createCell(6).setCellValue(jogo.getGenero());
FileOutputStream nFile = null;
try {
nFile = new FileOutputStream("Jogos.xls");
} catch (FileNotFoundException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
try {
this.planilha.write(nFile);
} catch (IOException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
try {
nFile.close();
} catch (IOException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
}
public void testDateFormulas() {
HSSFWorkbook wb = new HSSFWorkbook();
HSSFSheet s = wb.createSheet("testSheet1");
HSSFRow r = null;
HSSFCell c = null;
r = s.createRow(0);
c = r.createCell(0);
HSSFCellStyle cellStyle = wb.createCellStyle();
cellStyle.setDataFormat(HSSFDataFormat.getBuiltinFormat("m/d/yy h:mm"));
c.setCellValue(new Date());
c.setCellStyle(cellStyle);
// assertEquals("Checking hour = " + hour, date.getTime().getTime(),
// HSSFDateUtil.getJavaDate(excelDate).getTime());
for (int k = 1; k < 100; k++) {
r = s.createRow(k);
c = r.createCell(0);
c.setCellFormula("A" + (k) + "+1");
c.setCellStyle(cellStyle);
}
HSSFTestDataSamples.writeOutAndReadBack(wb);
}
public void testSheetFunctions() {
HSSFWorkbook wb = new HSSFWorkbook();
HSSFSheet s = wb.createSheet("A");
HSSFRow r = null;
HSSFCell c = null;
r = s.createRow(0);
c = r.createCell(0);
c.setCellValue(1);
c = r.createCell(1);
c.setCellValue(2);
s = wb.createSheet("B");
r = s.createRow(0);
c = r.createCell(0);
c.setCellFormula("AVERAGE(A!A1:B1)");
c = r.createCell(1);
c.setCellFormula("A!A1+A!B1");
c = r.createCell(2);
c.setCellFormula("A!$A$1+A!$B1");
wb = HSSFTestDataSamples.writeOutAndReadBack(wb);
s = wb.getSheet("B");
r = s.getRow(0);
c = r.getCell(0);
assertTrue(
"expected: AVERAGE(A!A1:B1) got: " + c.getCellFormula(),
("AVERAGE(A!A1:B1)").equals(c.getCellFormula()));
c = r.getCell(1);
assertTrue(
"expected: A!A1+A!B1 got: " + c.getCellFormula(), ("A!A1+A!B1").equals(c.getCellFormula()));
}
/** Writes a function then tests to see if its correct */
public void refAreaArrayFunctionTest(String function) {
HSSFWorkbook wb = new HSSFWorkbook();
HSSFSheet s = wb.createSheet();
HSSFRow r = null;
HSSFCell c = null;
r = s.createRow(0);
c = r.createCell(0);
c.setCellFormula(function + "(A2:A4,B2:B4)");
c = r.createCell(1);
c.setCellFormula(function + "($A$2:$A4,B$2:B4)");
wb = HSSFTestDataSamples.writeOutAndReadBack(wb);
s = wb.getSheetAt(0);
r = s.getRow(0);
c = r.getCell(0);
assertTrue(
"function =" + function + "(A2:A4,B2:B4)",
((function + "(A2:A4,B2:B4)").equals(c.getCellFormula())));
c = r.getCell(1);
assertTrue(
"function =" + function + "($A$2:$A4,B$2:B4)",
((function + "($A$2:$A4,B$2:B4)").equals(c.getCellFormula())));
}
public void testAbsRefs() {
HSSFWorkbook wb = new HSSFWorkbook();
HSSFSheet s = wb.createSheet();
HSSFRow r;
HSSFCell c;
r = s.createRow(0);
c = r.createCell(0);
c.setCellFormula("A3+A2");
c = r.createCell(1);
c.setCellFormula("$A3+$A2");
c = r.createCell(2);
c.setCellFormula("A$3+A$2");
c = r.createCell(3);
c.setCellFormula("$A$3+$A$2");
c = r.createCell(4);
c.setCellFormula("SUM($A$3,$A$2)");
wb = HSSFTestDataSamples.writeOutAndReadBack(wb);
s = wb.getSheetAt(0);
r = s.getRow(0);
c = r.getCell(0);
assertTrue("A3+A2", ("A3+A2").equals(c.getCellFormula()));
c = r.getCell(1);
assertTrue("$A3+$A2", ("$A3+$A2").equals(c.getCellFormula()));
c = r.getCell(2);
assertTrue("A$3+A$2", ("A$3+A$2").equals(c.getCellFormula()));
c = r.getCell(3);
assertTrue("$A$3+$A$2", ("$A$3+$A$2").equals(c.getCellFormula()));
c = r.getCell(4);
assertTrue("SUM($A$3,$A$2)", ("SUM($A$3,$A$2)").equals(c.getCellFormula()));
}
public void testShapeIds() {
HSSFWorkbook wb = new HSSFWorkbook();
HSSFSheet sheet1 = wb.createSheet();
HSSFPatriarch patriarch1 = sheet1.createDrawingPatriarch();
for (int i = 0; i < 2; i++) {
patriarch1.createSimpleShape(new HSSFClientAnchor());
}
wb = HSSFTestDataSamples.writeOutAndReadBack(wb);
sheet1 = wb.getSheetAt(0);
patriarch1 = sheet1.getDrawingPatriarch();
EscherAggregate agg1 = HSSFTestHelper.getEscherAggregate(patriarch1);
// last shape ID cached in EscherDgRecord
EscherDgRecord dg1 = agg1.getEscherContainer().getChildById(EscherDgRecord.RECORD_ID);
assertEquals(1026, dg1.getLastMSOSPID());
// iterate over shapes and check shapeId
EscherContainerRecord spgrContainer = agg1.getEscherContainer().getChildContainers().get(0);
// root spContainer + 2 spContainers for shapes
assertEquals(3, spgrContainer.getChildRecords().size());
EscherSpRecord sp0 =
((EscherContainerRecord) spgrContainer.getChild(0)).getChildById(EscherSpRecord.RECORD_ID);
assertEquals(1024, sp0.getShapeId());
EscherSpRecord sp1 =
((EscherContainerRecord) spgrContainer.getChild(1)).getChildById(EscherSpRecord.RECORD_ID);
assertEquals(1025, sp1.getShapeId());
EscherSpRecord sp2 =
((EscherContainerRecord) spgrContainer.getChild(2)).getChildById(EscherSpRecord.RECORD_ID);
assertEquals(1026, sp2.getShapeId());
}
private static void operationRefTest(String operator) {
HSSFWorkbook wb = new HSSFWorkbook();
HSSFSheet s = wb.createSheet();
HSSFRow r = null;
HSSFCell c = null;
// get our minimum values
r = s.createRow(0);
c = r.createCell(1);
c.setCellFormula("A2" + operator + "A3");
for (int x = 1; x < Short.MAX_VALUE && x > 0; x = (short) (x * 2)) {
r = s.createRow(x);
for (int y = 1; y < 256 && y > 0; y++) {
String ref = null;
String ref2 = null;
short refx1 = 0;
short refy1 = 0;
short refx2 = 0;
short refy2 = 0;
if (x + 50 < Short.MAX_VALUE) {
refx1 = (short) (x + 50);
refx2 = (short) (x + 46);
} else {
refx1 = (short) (x - 4);
refx2 = (short) (x - 3);
}
if (y + 50 < 255) {
refy1 = (short) (y + 50);
refy2 = (short) (y + 49);
} else {
refy1 = (short) (y - 4);
refy2 = (short) (y - 3);
}
c = r.getCell(y);
CellReference cr = new CellReference(refx1, refy1, false, false);
ref = cr.formatAsString();
cr = new CellReference(refx2, refy2, false, false);
ref2 = cr.formatAsString();
c = r.createCell(y);
c.setCellFormula("" + ref + operator + ref2);
}
}
// make sure we do the maximum value of the Int operator
if (s.getLastRowNum() < Short.MAX_VALUE) {
r = s.getRow(0);
c = r.createCell(0);
c.setCellFormula("" + "B1" + operator + "IV255");
}
wb = HSSFTestDataSamples.writeOutAndReadBack(wb);
operationalRefVerify(operator, wb);
}
public void testSharedFormula() {
HSSFWorkbook wb = openSample("SharedFormulaTest.xls");
assertEquals("A$1*2", wb.getSheetAt(0).getRow(1).getCell(1).toString());
assertEquals("$A11*2", wb.getSheetAt(0).getRow(11).getCell(1).toString());
assertEquals("DZ2*2", wb.getSheetAt(0).getRow(1).getCell(128).toString());
assertEquals("B32770*2", wb.getSheetAt(0).getRow(32768).getCell(1).toString());
}
/**
* {@link HSSFFormulaEvaluator#evaluate(org.apache.poi.ss.usermodel.Cell)} should behave the same
* whether the cell is <code>null</code> or blank.
*/
public void testEvaluateBlank() {
HSSFWorkbook wb = new HSSFWorkbook();
HSSFFormulaEvaluator fe = new HSSFFormulaEvaluator(wb);
assertNull(fe.evaluate(null));
HSSFSheet sheet = wb.createSheet("Sheet1");
HSSFCell cell = sheet.createRow(0).createCell(0);
assertNull(fe.evaluate(cell));
}
/** test for bug 34021 */
public void testComplexSheetRefs() throws IOException {
HSSFWorkbook sb = new HSSFWorkbook();
HSSFSheet s1 = sb.createSheet("Sheet a.1");
HSSFSheet s2 = sb.createSheet("Sheet.A");
s2.createRow(1).createCell(2).setCellFormula("'Sheet a.1'!A1");
s1.createRow(1).createCell(2).setCellFormula("'Sheet.A'!A1");
File file = TempFile.createTempFile("testComplexSheetRefs", ".xls");
sb.write(new FileOutputStream(file));
}
/** Unknown Ptg 3C */
public void test27272_1() throws Exception {
HSSFWorkbook wb = openSample("27272_1.xls");
wb.getSheetAt(0);
assertEquals(
"Reference for named range ", "Compliance!#REF!", wb.getNameAt(0).getRefersToFormula());
File outF = TempFile.createTempFile("bug27272_1", ".xls");
wb.write(new FileOutputStream(outF));
System.out.println("Open " + outF.getAbsolutePath() + " in Excel");
}
/**
* Test that the HSSFFormulaEvaluator can evaluate simple named ranges (single cells and
* rectangular areas)
*/
public void testEvaluateSimple() {
HSSFWorkbook wb = HSSFTestDataSamples.openSampleWorkbook("testNames.xls");
HSSFSheet sheet = wb.getSheetAt(0);
HSSFCell cell = sheet.getRow(8).getCell(0);
HSSFFormulaEvaluator fe = new HSSFFormulaEvaluator(wb);
CellValue cv = fe.evaluate(cell);
assertEquals(HSSFCell.CELL_TYPE_NUMERIC, cv.getCellType());
assertEquals(3.72, cv.getNumberValue(), 0.0);
}
/**
* HSSFShape tree bust be built correctly Check file with such records structure: -patriarch
* --shape --group ---group ----shape ----shape ---shape ---group ----shape ----shape
*/
public void testDrawingGroups() {
HSSFWorkbook wb = HSSFTestDataSamples.openSampleWorkbook("drawings.xls");
HSSFSheet sheet = wb.getSheet("groups");
HSSFPatriarch patriarch = sheet.getDrawingPatriarch();
assertEquals(patriarch.getChildren().size(), 2);
HSSFShapeGroup group = (HSSFShapeGroup) patriarch.getChildren().get(1);
assertEquals(3, group.getChildren().size());
HSSFShapeGroup group1 = (HSSFShapeGroup) group.getChildren().get(0);
assertEquals(2, group1.getChildren().size());
group1 = (HSSFShapeGroup) group.getChildren().get(2);
assertEquals(2, group1.getChildren().size());
}
public void testDgRecordNumShapes() throws IOException {
HSSFWorkbook wb = new HSSFWorkbook();
try {
HSSFSheet sheet = wb.createSheet();
HSSFPatriarch patriarch = sheet.createDrawingPatriarch();
EscherAggregate aggregate = HSSFTestHelper.getEscherAggregate(patriarch);
EscherDgRecord dgRecord = (EscherDgRecord) aggregate.getEscherRecord(0).getChild(0);
assertEquals(dgRecord.getNumShapes(), 1);
} finally {
wb.close();
}
}
public void testSumIf() {
String function = "SUMIF(A1:A5,\">4000\",B1:B5)";
HSSFWorkbook wb = openSample("sumifformula.xls");
HSSFSheet s = wb.getSheetAt(0);
HSSFRow r = s.getRow(0);
HSSFCell c = r.getCell(2);
assertEquals(function, c.getCellFormula());
wb = new HSSFWorkbook();
s = wb.createSheet();
r = s.createRow(0);
c = r.createCell(0);
c.setCellValue(1000);
c = r.createCell(1);
c.setCellValue(1);
r = s.createRow(1);
c = r.createCell(0);
c.setCellValue(2000);
c = r.createCell(1);
c.setCellValue(2);
r = s.createRow(2);
c = r.createCell(0);
c.setCellValue(3000);
c = r.createCell(1);
c.setCellValue(3);
r = s.createRow(3);
c = r.createCell(0);
c.setCellValue(4000);
c = r.createCell(1);
c.setCellValue(4);
r = s.createRow(4);
c = r.createCell(0);
c.setCellValue(5000);
c = r.createCell(1);
c.setCellValue(5);
r = s.getRow(0);
c = r.createCell(2);
c.setCellFormula(function);
HSSFTestDataSamples.writeOutAndReadBack(wb);
}
/* assert shape properties when reading shapes from a existing workbook */
public void testReadExistingRectangle() {
HSSFWorkbook wb = HSSFTestDataSamples.openSampleWorkbook("drawings.xls");
HSSFSheet sheet = wb.getSheet("rectangles");
HSSFPatriarch drawing = sheet.getDrawingPatriarch();
assertEquals(1, drawing.getChildren().size());
HSSFSimpleShape shape = (HSSFSimpleShape) drawing.getChildren().get(0);
assertEquals(shape.isNoFill(), false);
assertEquals(shape.getLineStyle(), HSSFShape.LINESTYLE_DASHDOTGEL);
assertEquals(shape.getLineStyleColor(), 0x616161);
assertEquals(HexDump.toHex(shape.getFillColor()), shape.getFillColor(), 0x2CE03D);
assertEquals(shape.getLineWidth(), HSSFShape.LINEWIDTH_ONE_PT * 2);
assertEquals(shape.getString().getString(), "POItest");
assertEquals(shape.getRotationDegree(), 27);
}
/** Verify that FormulaParser handles defined names beginning with underscores, see Bug #49640 */
public void testFormulasWithUnderscore() {
HSSFWorkbook wb = new HSSFWorkbook();
Name nm1 = wb.createName();
nm1.setNameName("_score1");
nm1.setRefersToFormula("A1");
Name nm2 = wb.createName();
nm2.setNameName("_score2");
nm2.setRefersToFormula("A2");
Sheet sheet = wb.createSheet();
Cell cell = sheet.createRow(0).createCell(2);
cell.setCellFormula("_score1*SUM(_score1+_score2)");
assertEquals("_score1*SUM(_score1+_score2)", cell.getCellFormula());
}
/**
* Opens the sheet we wrote out by binomialOperator and makes sure the formulas all match what we
* expect (x operator y)
*/
private static void binomialVerify(String operator, HSSFWorkbook wb) {
HSSFSheet s = wb.getSheetAt(0);
HSSFRow r = null;
HSSFCell c = null;
// get our minimum values
r = s.getRow(0);
c = r.getCell(1);
assertTrue(
"minval Formula is as expected 1" + operator + "1 != " + c.getCellFormula(),
(("1" + operator + "1").equals(c.getCellFormula())));
for (int x = 1; x < Short.MAX_VALUE && x > 0; x = (short) (x * 2)) {
r = s.getRow(x);
for (int y = 1; y < 256 && y > 0; y++) {
c = r.getCell(y);
assertTrue(
"loop Formula is as expected " + x + operator + y + "!=" + c.getCellFormula(),
(("" + x + operator + y).equals(c.getCellFormula())));
}
}
// test our maximum values
r = s.getRow(0);
c = r.getCell(0);
assertTrue(
"maxval Formula is as expected",
(("" + Short.MAX_VALUE + operator + Short.MAX_VALUE).equals(c.getCellFormula())));
}
public Jogo procurar(int ID) {
Jogo resposta = null;
for (int i = 1; i <= planilha.getSheet("Jogos").getLastRowNum(); i++) {
if (planilha.getSheet("Jogos").getRow(i).getCell(0).getNumericCellValue() == ID) {
HSSFRow Row = this.planilha.getSheet("Jogos").getRow(i);
String nome = Row.getCell(1).getStringCellValue();
String console = Row.getCell(2).getStringCellValue();
double precoCompra = Row.getCell(3).getNumericCellValue();
double precoVenda = Row.getCell(4).getNumericCellValue();
int quantidade = (int) Row.getCell(5).getNumericCellValue();
String genero = Row.getCell(6).getStringCellValue();
resposta = new Jogo(ID, nome, console, precoCompra, precoVenda, quantidade, genero);
}
}
return resposta;
}
private static void floatVerify(String operator, HSSFWorkbook wb) {
HSSFSheet s = wb.getSheetAt(0);
// don't know how to check correct result .. for the moment, we just verify that the file can be
// read.
for (int x = 1; x < Short.MAX_VALUE && x > 0; x = (short) (x * 2)) {
HSSFRow r = s.getRow(x);
for (int y = 1; y < 256 && y > 0; y = (short) (y + 2)) {
HSSFCell c = r.getCell(y);
assertTrue("got a formula", c.getCellFormula() != null);
assertTrue(
"loop Formula is as expected "
+ x
+ "."
+ y
+ operator
+ y
+ "."
+ x
+ "!="
+ c.getCellFormula(),
(("" + x + "." + y + operator + y + "." + x).equals(c.getCellFormula())));
}
}
}
public void testLogicalFormulas() {
HSSFWorkbook wb = new HSSFWorkbook();
HSSFSheet s = wb.createSheet("A");
HSSFRow r = null;
HSSFCell c = null;
r = s.createRow(0);
c = r.createCell(1);
c.setCellFormula("IF(A1<A2,B1,B2)");
wb = HSSFTestDataSamples.writeOutAndReadBack(wb);
s = wb.getSheetAt(0);
r = s.getRow(0);
c = r.getCell(1);
assertEquals("Formula in cell 1 ", "IF(A1<A2,B1,B2)", c.getCellFormula());
}
public void testCorrectOrderInOptRecord() throws IOException {
HSSFWorkbook wb = new HSSFWorkbook();
try {
HSSFSheet sheet = wb.createSheet();
HSSFPatriarch patriarch = sheet.createDrawingPatriarch();
HSSFTextbox textbox = patriarch.createTextbox(new HSSFClientAnchor());
EscherOptRecord opt = HSSFTestHelper.getOptRecord(textbox);
String opt1Str = opt.toXml();
textbox.setFillColor(textbox.getFillColor());
EscherContainerRecord container = HSSFTestHelper.getEscherContainer(textbox);
EscherOptRecord optRecord = container.getChildById(EscherOptRecord.RECORD_ID);
assertEquals(opt1Str, optRecord.toXml());
textbox.setLineStyle(textbox.getLineStyle());
assertEquals(opt1Str, optRecord.toXml());
textbox.setLineWidth(textbox.getLineWidth());
assertEquals(opt1Str, optRecord.toXml());
textbox.setLineStyleColor(textbox.getLineStyleColor());
assertEquals(opt1Str, optRecord.toXml());
} finally {
wb.close();
}
}
