Examples with HSSFCell org.apache.poi.hssf.usermodel.HSSFCell used on opensource projects

Example 21 with HSSFCell

use of org.apache.poi.hssf.usermodel.HSSFCell in project poi by apache.

the class Hyperlinks method main.

public static void main(String[] args) throws IOException {
    HSSFWorkbook wb = new HSSFWorkbook();
    HSSFCreationHelper helper = wb.getCreationHelper();
    //cell style for hyperlinks
    //by default hyperlinks are blue and underlined
    HSSFCellStyle hlink_style = wb.createCellStyle();
    HSSFFont hlink_font = wb.createFont();
    hlink_font.setUnderline(Font.U_SINGLE);
    hlink_font.setColor(HSSFColorPredefined.BLUE.getIndex());
    hlink_style.setFont(hlink_font);
    HSSFCell cell;
    HSSFSheet sheet = wb.createSheet("Hyperlinks");
    //URL
    cell = sheet.createRow(0).createCell(0);
    cell.setCellValue("URL Link");
    HSSFHyperlink link = helper.createHyperlink(HyperlinkType.URL);
    link.setAddress("http://poi.apache.org/");
    cell.setHyperlink(link);
    cell.setCellStyle(hlink_style);
    //link to a file in the current directory
    cell = sheet.createRow(1).createCell(0);
    cell.setCellValue("File Link");
    link = helper.createHyperlink(HyperlinkType.FILE);
    link.setAddress("link1.xls");
    cell.setHyperlink(link);
    cell.setCellStyle(hlink_style);
    //e-mail link
    cell = sheet.createRow(2).createCell(0);
    cell.setCellValue("Email Link");
    link = helper.createHyperlink(HyperlinkType.EMAIL);
    //note, if subject contains white spaces, make sure they are url-encoded
    link.setAddress("mailto:poi@apache.org?subject=Hyperlinks");
    cell.setHyperlink(link);
    cell.setCellStyle(hlink_style);
    //link to a place in this workbook
    //create a target sheet and cell
    HSSFSheet sheet2 = wb.createSheet("Target Sheet");
    sheet2.createRow(0).createCell(0).setCellValue("Target Cell");
    cell = sheet.createRow(3).createCell(0);
    cell.setCellValue("Worksheet Link");
    link = helper.createHyperlink(HyperlinkType.DOCUMENT);
    link.setAddress("'Target Sheet'!A1");
    cell.setHyperlink(link);
    cell.setCellStyle(hlink_style);
    FileOutputStream out = new FileOutputStream("hssf-links.xls");
    wb.write(out);
    out.close();
    wb.close();
}

Example 22 with HSSFCell

use of org.apache.poi.hssf.usermodel.HSSFCell in project poi by apache.

the class HyperlinkFormula method main.

public static void main(String[] args) throws IOException {
    HSSFWorkbook wb = new HSSFWorkbook();
    HSSFSheet sheet = wb.createSheet("new sheet");
    HSSFRow row = sheet.createRow(0);
    HSSFCell cell = row.createCell(0);
    cell.setCellType(CellType.FORMULA);
    cell.setCellFormula("HYPERLINK(\"http://127.0.0.1:8080/toto/truc/index.html?test=aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa\", \"test\")");
    FileOutputStream fileOut = new FileOutputStream("workbook.xls");
    wb.write(fileOut);
    fileOut.close();
    wb.close();
}

Example 23 with HSSFCell

use of org.apache.poi.hssf.usermodel.HSSFCell in project poi by apache.

the class TestRank method testFromFile.

public void testFromFile() {
    HSSFWorkbook wb = HSSFTestDataSamples.openSampleWorkbook("rank.xls");
    HSSFFormulaEvaluator fe = new HSSFFormulaEvaluator(wb);
    HSSFSheet example1 = wb.getSheet("Example 1");
    HSSFCell ex1cell1 = example1.getRow(7).getCell(0);
    assertEquals(3.0, fe.evaluate(ex1cell1).getNumberValue());
    HSSFCell ex1cell2 = example1.getRow(8).getCell(0);
    assertEquals(5.0, fe.evaluate(ex1cell2).getNumberValue());
    HSSFSheet example2 = wb.getSheet("Example 2");
    for (int rownum = 1; rownum <= 10; rownum++) {
        HSSFCell cell = example2.getRow(rownum).getCell(2);
        //cached formula result
        double cachedResult = cell.getNumericCellValue();
        assertEquals(cachedResult, fe.evaluate(cell).getNumberValue());
    }
    HSSFSheet example3 = wb.getSheet("Example 3");
    for (int rownum = 1; rownum <= 10; rownum++) {
        HSSFCell cellD = example3.getRow(rownum).getCell(3);
        //cached formula result
        double cachedResultD = cellD.getNumericCellValue();
        assertEquals(new CellReference(cellD).formatAsString(), cachedResultD, fe.evaluate(cellD).getNumberValue());
        HSSFCell cellE = example3.getRow(rownum).getCell(4);
        //cached formula result
        double cachedResultE = cellE.getNumericCellValue();
        assertEquals(new CellReference(cellE).formatAsString(), cachedResultE, fe.evaluate(cellE).getNumberValue());
        HSSFCell cellF = example3.getRow(rownum).getCell(5);
        //cached formula result
        double cachedResultF = cellF.getNumericCellValue();
        assertEquals(new CellReference(cellF).formatAsString(), cachedResultF, fe.evaluate(cellF).getNumberValue());
    }
}

Example 24 with HSSFCell

use of org.apache.poi.hssf.usermodel.HSSFCell in project poi by apache.

the class TestExternalFunctionFormulas method confirmCellEval.

private static void confirmCellEval(HSSFSheet sheet, int rowIx, int colIx, HSSFFormulaEvaluator fe, String expectedFormula, double expectedResult) {
    HSSFCell cell = sheet.getRow(rowIx).getCell(colIx);
    assertEquals(expectedFormula, cell.getCellFormula());
    CellValue cv = fe.evaluate(cell);
    assertEquals(expectedResult, cv.getNumberValue(), 0.0);
}

Example 25 with HSSFCell

use of org.apache.poi.hssf.usermodel.HSSFCell in project poi by apache.

the class TestExternalNameReference method testEvaluate.

public void testEvaluate() throws Exception {
    HSSFWorkbook wb = HSSFTestDataSamples.openSampleWorkbook("XRefCalc.xls");
    HSSFWorkbook wb2 = HSSFTestDataSamples.openSampleWorkbook("XRefCalcData.xls");
    CellReference cellRef = new CellReference(wb.getName("QUANT").getRefersToFormula());
    HSSFCell cell = wb.getSheet(cellRef.getSheetName()).getRow(cellRef.getRow()).getCell((int) cellRef.getCol());
    cell.setCellValue(NEW_QUANT);
    cell = wb2.getSheet("CostSheet").getRow(1).getCell(1);
    cell.setCellValue(NEW_PART_COST);
    HSSFFormulaEvaluator evaluator = new HSSFFormulaEvaluator(wb);
    HSSFFormulaEvaluator evaluatorCost = new HSSFFormulaEvaluator(wb2);
    String[] bookNames = { "XRefCalc.xls", "XRefCalcData.xls" };
    HSSFFormulaEvaluator[] evaluators = { evaluator, evaluatorCost };
    HSSFFormulaEvaluator.setupEnvironment(bookNames, evaluators);
    cellRef = new CellReference(wb.getName("UNITCOST").getRefersToFormula());
    HSSFCell uccell = wb.getSheet(cellRef.getSheetName()).getRow(cellRef.getRow()).getCell((int) cellRef.getCol());
    cellRef = new CellReference(wb.getName("COST").getRefersToFormula());
    HSSFCell ccell = wb.getSheet(cellRef.getSheetName()).getRow(cellRef.getRow()).getCell((int) cellRef.getCol());
    cellRef = new CellReference(wb.getName("TOTALCOST").getRefersToFormula());
    HSSFCell tccell = wb.getSheet(cellRef.getSheetName()).getRow(cellRef.getRow()).getCell((int) cellRef.getCol());
    evaluator.evaluateFormulaCellEnum(uccell);
    evaluator.evaluateFormulaCellEnum(ccell);
    evaluator.evaluateFormulaCellEnum(tccell);
    assertEquals(NEW_PART_COST, uccell.getNumericCellValue());
    assertEquals(NEW_PART_COST * NEW_QUANT, ccell.getNumericCellValue());
    assertEquals(NEW_PART_COST * NEW_QUANT * MARKUP_COST_2, tccell.getNumericCellValue());
}