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 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);
}
/**
* 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);
}
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 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();
}
}
}
}
private static void setValue(HSSFSheet sheet, int rowIndex, int colIndex, double value) {
HSSFRow row = sheet.getRow(rowIndex);
if (row == null) {
row = sheet.createRow(rowIndex);
}
row.createCell(colIndex).setCellValue(value);
}
private void writeData2Excel(HSSFSheet sheet, HSSFCellStyle dataStyle, HSSFCellStyle headerStyle)
throws ContainerException {
RowSet searchResult = executeFrm.getSearchResult();
if (searchResult != null && !searchResult.isEmpty()) {
List<ReportViewFrm> definedViews = executeFrm.getDefinedViews();
int fieldCount = definedViews.size();
int dataCount = searchResult.getSize();
int xlsRowNum = 0;
for (int i = 0; i < dataCount; i++) {
Row row = searchResult.getRow(i);
xlsRowNum = i;
if (isFirstFile) {
xlsRowNum++;
}
HSSFRow xlsRow = sheet.createRow(xlsRowNum);
xlsRow.setHeightInPoints(18);
for (short j = 0; j < fieldCount; j++) {
ReportViewFrm viewFrm = definedViews.get(j);
String fieldName = viewFrm.getFieldName();
HSSFCell xlsCell = xlsRow.createCell(j);
xlsCell.setCellStyle(dataStyle);
HSSFRichTextString richText = new HSSFRichTextString(row.getStrValue(fieldName));
xlsCell.setCellValue(richText);
}
}
}
}
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 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();
}
}
}
}
/** 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())));
}
/**
* 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())));
}
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);
}
/**
* Opens the sheet we wrote out by binomialOperator and makes sure the formulas all match what we
* expect (x operator y)
*/
private static void operationalRefVerify(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);
// get our minimum values
assertTrue(
"minval Formula is as expected A2" + operator + "A3 != " + c.getCellFormula(),
(("A2" + operator + "A3").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++) {
int refx1;
int refy1;
int refx2;
int refy2;
if (x + 50 < Short.MAX_VALUE) {
refx1 = x + 50;
refx2 = x + 46;
} else {
refx1 = x - 4;
refx2 = x - 3;
}
if (y + 50 < 255) {
refy1 = y + 50;
refy2 = y + 49;
} else {
refy1 = y - 4;
refy2 = y - 3;
}
c = r.getCell(y);
CellReference cr = new CellReference(refx1, refy1, false, false);
String ref = cr.formatAsString();
ref = cr.formatAsString();
cr = new CellReference(refx2, refy2, false, false);
String ref2 = cr.formatAsString();
assertTrue(
"loop Formula is as expected " + ref + operator + ref2 + "!=" + c.getCellFormula(),
(("" + ref + operator + ref2).equals(c.getCellFormula())));
}
}
// test our maximum values
r = s.getRow(0);
c = r.getCell(0);
assertEquals("B1" + operator + "IV255", c.getCellFormula());
}
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()));
}
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());
}
/**
* It is responsible for creating a cell within the row.
*
* @param fila: row where create the cell
* @param position: Determines the position where anger cell within the row.
* @param value: Sets the value that will be created within the cell.
*/
private void createCell(HSSFRow fila, int position, String value) {
HSSFCell cell;
cell =
fila.createCell(
(short) position); // Se crea una cell dentro de la fila
cell.setCellValue(new HSSFRichTextString(value));
}
public RepositorioJogosArquivo() {
boolean arquivoExiste = true;
this.planilha = new HSSFWorkbook();
FileInputStream arquivo = null;
try {
arquivo = new FileInputStream("Jogos.xls");
} catch (FileNotFoundException e) {
arquivoExiste = false;
HSSFRow inicioJogos = this.planilha.createSheet("Jogos").createRow(0);
this.planilha.getSheet("Jogos").createRow(1).createCell(0).setCellValue(0);
FileOutputStream novoArquivo = null;
try {
novoArquivo = new FileOutputStream("Jogos.xls");
} catch (FileNotFoundException e1) {
// TODO Auto-generated catch block
e1.printStackTrace();
}
inicioJogos.createCell(0).setCellValue("ID");
inicioJogos.createCell(1).setCellValue("nome");
inicioJogos.createCell(2).setCellValue("console");
inicioJogos.createCell(3).setCellValue("preço compra");
inicioJogos.createCell(4).setCellValue("preço venda");
inicioJogos.createCell(5).setCellValue("quantidade");
inicioJogos.createCell(6).setCellValue("genero");
try {
this.planilha.write(novoArquivo);
} catch (IOException e1) {
// TODO Auto-generated catch block
e1.printStackTrace();
}
try {
novoArquivo.close();
} catch (IOException e1) {
// TODO Auto-generated catch block
e1.printStackTrace();
}
}
if (arquivoExiste) {
try {
this.planilha = new HSSFWorkbook(arquivo);
} catch (IOException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
private void writeDataHeader(HSSFSheet sheet, HSSFCellStyle headerStyle) throws ReportException {
try {
sheet.setDisplayGridlines(false); // 不显示网格线
HSSFRow xlsRow = sheet.createRow(0);
xlsRow.setHeightInPoints(18);
List<ReportViewFrm> definedViews = executeFrm.getDefinedViews();
int fieldCount = definedViews.size();
for (short j = 0; j < fieldCount; j++) {
ReportViewFrm viewFrm = definedViews.get(j);
HSSFCell xlsCell = xlsRow.createCell(j);
xlsCell.setCellStyle(headerStyle);
HSSFRichTextString richText = new HSSFRichTextString(viewFrm.getFieldDesc());
xlsCell.setCellValue(richText);
}
} catch (Throwable ex) {
Logger.logError(ex);
throw new ReportException(ex.getMessage());
}
}
/** tests order wrting out == order writing in for a given formula */
private static void orderTest(String formula) {
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(formula);
wb = HSSFTestDataSamples.writeOutAndReadBack(wb);
s = wb.getSheetAt(0);
// get our minimum values
r = s.getRow(0);
c = r.getCell(1);
assertTrue("minval Formula is as expected", formula.equals(c.getCellFormula()));
}
private void writeSheet(
List<Map<String, String>> valueMaps, String worksheetName, HSSFWorkbook wb) {
// Set column widths
HSSFSheet sheet = wb.createSheet(worksheetName);
sheet.setColumnWidth(Short.parseShort("0"), Short.parseShort("15000"));
sheet.setColumnWidth(Short.parseShort("1"), Short.parseShort("30000"));
// header style
HSSFCellStyle headerStyle;
HSSFFont headerFont = wb.createFont();
headerFont.setFontHeightInPoints((short) 11);
headerStyle = wb.createCellStyle();
headerStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
headerStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
headerStyle.setFillForegroundColor(HSSFColor.GREY_50_PERCENT.index);
headerStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
headerStyle.setFont(headerFont);
headerStyle.setWrapText(true);
// header row
HSSFRow headerRow = sheet.createRow(0);
headerRow.setHeightInPoints(30);
HSSFCell headerCell0 = headerRow.createCell((short) 0);
HSSFCell headerCell1 = headerRow.createCell((short) 1);
headerCell0.setCellStyle(headerStyle);
setText(headerCell0, "Layer Name");
headerCell1.setCellStyle(headerStyle);
setText(headerCell1, "Message");
int counter = 1;
for (Map<String, String> valueMap : valueMaps) {
HSSFRow dataRow = sheet.createRow(counter);
String layer = valueMap.get("layer");
String status = valueMap.get("status");
status = HtmlUtils.htmlUnescape(status);
HSSFCell currentCell0 = dataRow.createCell((short) 0);
HSSFCell currentCell1 = dataRow.createCell((short) 1);
setText(currentCell0, layer);
setText(currentCell1, status);
counter++;
}
}
/** Add 1+1 -- WHoohoo! */
public void testBasicAddIntegers() {
HSSFWorkbook wb = new HSSFWorkbook();
HSSFSheet s = wb.createSheet();
HSSFRow r = null;
HSSFCell c = null;
// get our minimum values
r = s.createRow(1);
c = r.createCell(1);
c.setCellFormula(1 + "+" + 1);
wb = HSSFTestDataSamples.writeOutAndReadBack(wb);
s = wb.getSheetAt(0);
r = s.getRow(1);
c = r.getCell(1);
assertTrue("Formula is as expected", ("1+1".equals(c.getCellFormula())));
}
/** Test creation / evaluation of formulas with sheet-level names */
public void testSheetLevelFormulas() {
HSSFWorkbook wb = new HSSFWorkbook();
HSSFRow row;
HSSFSheet sh1 = wb.createSheet("Sheet1");
HSSFName nm1 = wb.createName();
nm1.setNameName("sales_1");
nm1.setSheetIndex(0);
nm1.setRefersToFormula("Sheet1!$A$1");
row = sh1.createRow(0);
row.createCell(0).setCellValue(3);
row.createCell(1).setCellFormula("sales_1");
row.createCell(2).setCellFormula("sales_1*2");
HSSFSheet sh2 = wb.createSheet("Sheet2");
HSSFName nm2 = wb.createName();
nm2.setNameName("sales_1");
nm2.setSheetIndex(1);
nm2.setRefersToFormula("Sheet2!$A$1");
row = sh2.createRow(0);
row.createCell(0).setCellValue(5);
row.createCell(1).setCellFormula("sales_1");
row.createCell(2).setCellFormula("sales_1*3");
// check that NamePtg refers to the correct NameRecord
Ptg[] ptgs1 = HSSFFormulaParser.parse("sales_1", wb, FormulaType.CELL, 0);
NamePtg nPtg1 = (NamePtg) ptgs1[0];
assertSame(nm1, wb.getNameAt(nPtg1.getIndex()));
Ptg[] ptgs2 = HSSFFormulaParser.parse("sales_1", wb, FormulaType.CELL, 1);
NamePtg nPtg2 = (NamePtg) ptgs2[0];
assertSame(nm2, wb.getNameAt(nPtg2.getIndex()));
// check that the formula evaluator returns the correct result
HSSFFormulaEvaluator evaluator = new HSSFFormulaEvaluator(wb);
assertEquals(3.0, evaluator.evaluate(sh1.getRow(0).getCell(1)).getNumberValue(), 0.0);
assertEquals(6.0, evaluator.evaluate(sh1.getRow(0).getCell(2)).getNumberValue(), 0.0);
assertEquals(5.0, evaluator.evaluate(sh2.getRow(0).getCell(1)).getNumberValue(), 0.0);
assertEquals(15.0, evaluator.evaluate(sh2.getRow(0).getCell(2)).getNumberValue(), 0.0);
}
public void testStringFormulas() {
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("UPPER(\"abc\")");
c = r.createCell(2);
c.setCellFormula("LOWER(\"ABC\")");
c = r.createCell(3);
c.setCellFormula("CONCATENATE(\" my \",\" name \")");
HSSFTestDataSamples.writeOutAndReadBack(wb);
wb = openSample("StringFormulas.xls");
s = wb.getSheetAt(0);
r = s.getRow(0);
c = r.getCell(0);
assertEquals("UPPER(\"xyz\")", c.getCellFormula());
}
public void testDoesNoHarmIfNothingToDo() {
HSSFWorkbook wb = new HSSFWorkbook();
// New files start with 4 built in fonts, and 21 built in styles
assertEquals(4, wb.getNumberOfFonts());
assertEquals(21, wb.getNumCellStyles());
// Create a test font and style, and use them
HSSFFont f = wb.createFont();
f.setFontName("Testing");
HSSFCellStyle s = wb.createCellStyle();
s.setFont(f);
HSSFSheet sheet = wb.createSheet();
HSSFRow row = sheet.createRow(0);
row.createCell(0).setCellStyle(s);
// Should have one more than the default of each
assertEquals(5, wb.getNumberOfFonts());
assertEquals(22, wb.getNumCellStyles());
// Optimise fonts
HSSFOptimiser.optimiseFonts(wb);
assertEquals(5, wb.getNumberOfFonts());
assertEquals(22, wb.getNumCellStyles());
assertEquals(f, s.getFont(wb));
// Optimise styles
HSSFOptimiser.optimiseCellStyles(wb);
assertEquals(5, wb.getNumberOfFonts());
assertEquals(22, wb.getNumCellStyles());
assertEquals(f, s.getFont(wb));
}
/** Test for bug due to attempt to convert a cached formula error result to a boolean */
public void testUpdateCachedFormulaResultFromErrorToNumber_bug46479() {
HSSFWorkbook wb = new HSSFWorkbook();
HSSFSheet sheet = wb.createSheet("Sheet1");
HSSFRow row = sheet.createRow(0);
HSSFCell cellA1 = row.createCell(0);
HSSFCell cellB1 = row.createCell(1);
cellB1.setCellFormula("A1+1");
HSSFFormulaEvaluator fe = new HSSFFormulaEvaluator(wb);
cellA1.setCellErrorValue((byte) HSSFErrorConstants.ERROR_NAME);
fe.evaluateFormulaCell(cellB1);
cellA1.setCellValue(2.5);
fe.notifyUpdateCell(cellA1);
try {
fe.evaluateInCell(cellB1);
} catch (IllegalStateException e) {
if (e.getMessage().equals("Cannot get a numeric value from a error formula cell")) {
throw new AssertionFailedError("Identified bug 46479a");
}
}
assertEquals(3.5, cellB1.getNumericCellValue(), 0.0);
}
/**
* When evaluating defined names, POI has to decide whether it is capable. Currently (May2009) POI
* only supports simple cell and area refs.<br>
* The sample spreadsheet (bugzilla attachment 23508) had a name flagged as 'complex' which
* contained a simple area ref. It is not clear what the 'complex' flag is used for but POI should
* look elsewhere to decide whether it can evaluate the name.
*/
public void testDefinedNameWithComplexFlag_bug47048() {
// Mock up a spreadsheet to match the critical details of the sample
HSSFWorkbook wb = new HSSFWorkbook();
HSSFSheet sheet = wb.createSheet("Input");
HSSFName definedName = wb.createName();
definedName.setNameName("Is_Multicar_Vehicle");
definedName.setRefersToFormula("Input!$B$17:$G$17");
// Set up some data and the formula
HSSFRow row17 = sheet.createRow(16);
row17.createCell(0).setCellValue(25.0);
row17.createCell(1).setCellValue(1.33);
row17.createCell(2).setCellValue(4.0);
HSSFRow row = sheet.createRow(0);
HSSFCell cellA1 = row.createCell(0);
cellA1.setCellFormula("SUM(Is_Multicar_Vehicle)");
// Set the complex flag - POI doesn't usually manipulate this flag
NameRecord nameRec = TestHSSFName.getNameRecord(definedName);
nameRec.setOptionFlag((short) 0x10); // 0x10 -> complex
HSSFFormulaEvaluator hsf = new HSSFFormulaEvaluator(wb);
CellValue value;
try {
value = hsf.evaluate(cellA1);
} catch (RuntimeException e) {
if (e.getMessage().equals("Don't now how to evalate name 'Is_Multicar_Vehicle'")) {
throw new AssertionFailedError("Identified bug 47048a");
}
throw e;
}
assertEquals(Cell.CELL_TYPE_NUMERIC, value.getCellType());
assertEquals(5.33, value.getNumberValue(), 0.0);
}
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;
}
public Iterator getIterator() {
Jogo[] jogo = new Jogo[planilha.getSheet("Jogos").getLastRowNum()];
int indice = 0;
for (int i = 1; i <= planilha.getSheet("Jogos").getLastRowNum(); i++) {
if (planilha.getSheet("Jogos").getRow(i).getCell(0).getNumericCellValue() != 0) {
HSSFRow Row = this.planilha.getSheet("Jogos").getRow(i);
int id = (int) Row.getCell(0).getNumericCellValue();
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();
Jogo resposta = new Jogo(id, nome, console, precoCompra, precoVenda, quantidade, genero);
jogo[indice] = resposta;
indice = indice + 1;
}
}
return new IteratorArquivo(jogo);
}
public void testOptimiseStylesCheckActualStyles() {
HSSFWorkbook wb = new HSSFWorkbook();
// Several styles
assertEquals(21, wb.getNumCellStyles());
HSSFCellStyle cs1 = wb.createCellStyle();
cs1.setBorderBottom(HSSFCellStyle.BORDER_THICK);
HSSFCellStyle cs2 = wb.createCellStyle();
cs2.setBorderBottom(HSSFCellStyle.BORDER_DASH_DOT);
HSSFCellStyle cs3 = wb.createCellStyle(); // = cs1
cs3.setBorderBottom(HSSFCellStyle.BORDER_THICK);
assertEquals(24, wb.getNumCellStyles());
// Use them
HSSFSheet s = wb.createSheet();
HSSFRow r = s.createRow(0);
r.createCell(0).setCellStyle(cs1);
r.createCell(1).setCellStyle(cs2);
r.createCell(2).setCellStyle(cs3);
assertEquals(21, r.getCell(0).getCellValueRecord().getXFIndex());
assertEquals(22, r.getCell(1).getCellValueRecord().getXFIndex());
assertEquals(23, r.getCell(2).getCellValueRecord().getXFIndex());
// Optimise
HSSFOptimiser.optimiseCellStyles(wb);
// Check
assertEquals(23, wb.getNumCellStyles());
assertEquals(HSSFCellStyle.BORDER_THICK, r.getCell(0).getCellStyle().getBorderBottom());
assertEquals(HSSFCellStyle.BORDER_DASH_DOT, r.getCell(1).getCellStyle().getBorderBottom());
assertEquals(HSSFCellStyle.BORDER_THICK, r.getCell(2).getCellStyle().getBorderBottom());
}
public void generateExcel(OutputStream out, XLSCallBack<T> xlscaCallBack)
throws RuntimeException, IOException {
HSSFWorkbook workbook = new HSSFWorkbook();
// create a sheet with specified name
HSSFSheet sheet = workbook.createSheet(xlscaCallBack.getSheetName());
for (int i = 0; i < xlscaCallBack.getColumnWidth().length; i++) {
sheet.setColumnWidth(i, xlscaCallBack.getColumnWidth()[i] * 256);
}
// create a title for sheet title
sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, xlscaCallBack.getHeaders().length - 1));
HSSFCellStyle titleCellStyle = workbook.createCellStyle(); // create titleCellStyle for cell
titleCellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
HSSFFont titleFont = workbook.createFont(); // set font
titleFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
titleFont.setFontName("宋体");
titleFont.setFontHeight((short) (240));
titleFont.setColor(HSSFColor.AUTOMATIC.index);
titleCellStyle.setFont(titleFont);
titleCellStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN); // set border
titleCellStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
titleCellStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
titleCellStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);
titleCellStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); // set background
titleCellStyle.setFillForegroundColor(HSSFColor.LIGHT_BLUE.index);
titleCellStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
titleCellStyle.setFillForegroundColor(HSSFColor.LIGHT_YELLOW.index);
HSSFRow title = sheet.createRow(0);
title.setHeight((short) 300);
for (int i = 0; i < xlscaCallBack.getHeaders().length; i++) { // create tytle for title
HSSFCell cell = title.createCell(i);
cell.setCellValue(xlscaCallBack.getTitle());
cell.setCellStyle(titleCellStyle);
}
HSSFCellStyle headerCellStyle = workbook.createCellStyle(); // create headerCellStyle for cell
headerCellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
HSSFFont headerFont = workbook.createFont(); // set font
headerFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
headerFont.setFontName("幼圆");
headerFont.setColor(HSSFColor.AUTOMATIC.index);
headerCellStyle.setFont(headerFont);
headerCellStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN); // set border
headerCellStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
headerCellStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
headerCellStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);
headerCellStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); // set background
headerCellStyle.setFillForegroundColor(HSSFColor.LIGHT_BLUE.index);
headerCellStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
headerCellStyle.setFillForegroundColor(HSSFColor.LIGHT_YELLOW.index);
HSSFRow header = sheet.createRow(1);
for (int i = 0; i < xlscaCallBack.getHeaders().length; i++) { // create tytle for header
HSSFCell cell = header.createCell(i);
cell.setCellValue(xlscaCallBack.getHeaders()[i]);
cell.setCellStyle(headerCellStyle);
}
HSSFCellStyle rowCellStyle = workbook.createCellStyle(); // create headerCellStyle for cell
rowCellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
HSSFFont rowFont = workbook.createFont(); // set font
rowFont.setBoldweight(HSSFFont.BOLDWEIGHT_NORMAL);
rowFont.setFontName("幼圆");
rowFont.setColor(HSSFColor.AUTOMATIC.index);
rowCellStyle.setFont(rowFont);
rowCellStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN); // set border
rowCellStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
rowCellStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
rowCellStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);
for (int i = 0; i < lists.size(); i++) {
HSSFRow row = sheet.createRow(i + 2);
String values[] = xlscaCallBack.getValue(lists.get(i));
for (int j = 0; j < values.length; j++) {
HSSFCell cell = row.createCell(j);
cell.setCellValue(values[j]);
cell.setCellStyle(rowCellStyle);
}
}
HSSFCellStyle bottomCellStyle = workbook.createCellStyle(); // create titleCellStyle for cell
bottomCellStyle.setAlignment(HSSFCellStyle.ALIGN_LEFT);
HSSFFont bottomFont = workbook.createFont(); // set font
bottomFont.setBoldweight(HSSFFont.BOLDWEIGHT_NORMAL);
bottomFont.setFontName("幼圆");
bottomFont.setColor(HSSFColor.AUTOMATIC.index);
bottomCellStyle.setFont(bottomFont);
bottomCellStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN); // set border
bottomCellStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
bottomCellStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
bottomCellStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);
bottomCellStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); // set background
bottomCellStyle.setFillForegroundColor(HSSFColor.LIGHT_BLUE.index);
bottomCellStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
bottomCellStyle.setFillForegroundColor(HSSFColor.LIGHT_YELLOW.index);
sheet.addMergedRegion(
new CellRangeAddress(
lists.size() + 2,
lists.size() + 2,
0,
xlscaCallBack.getHeaders().length - 1)); // creaet bootom for xls
HSSFRow bottomRow = sheet.createRow(lists.size() + 2);
for (int i = 0; i < xlscaCallBack.getHeaders().length; i++) { // create tytle for title
HSSFCell cell = bottomRow.createCell(i);
SimpleDateFormat sf = new SimpleDateFormat("yyyy-MM-dd hh:mm:ss");
cell.setCellValue("共计导出 " + lists.size() + " 条记录 ,导出日期:" + sf.format(new Date()));
cell.setCellStyle(bottomCellStyle);
}
try {
workbook.write(out);
} catch (IOException e) {
e.printStackTrace();
throw new RuntimeException("create Excel failed due to some unkonw reasion~");
}
}
