You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@poi.apache.org by ki...@apache.org on 2019/09/24 23:26:00 UTC
svn commit: r1867502 [10/20] - in /poi/site/publish: ./ components/
components/hmef/ components/hpbf/ components/hpsf/ components/poifs/
components/slideshow/ components/spreadsheet/ devel/ devel/plan/
devel/references/ skin/ skin/images/
Modified: poi/site/publish/components/spreadsheet/converting.html
URL: http://svn.apache.org/viewvc/poi/site/publish/components/spreadsheet/converting.html?rev=1867502&r1=1867501&r2=1867502&view=diff
==============================================================================
--- poi/site/publish/components/spreadsheet/converting.html (original)
+++ poi/site/publish/components/spreadsheet/converting.html Tue Sep 24 23:26:00 2019
@@ -368,132 +368,384 @@ document.write("Last Published: " + docu
<div class="section">
<a name="Old+HSSF+Code"></a>
<h3 class="boxed">Old HSSF Code</h3>
-<pre class="code">
-// import org.apache.poi.hssf.usermodel.*;
-
-HSSFWorkbook wb = new HSSFWorkbook();
-// create a new sheet
-HSSFSheet s = wb.createSheet();
-// declare a row object reference
-HSSFRow r = null;
-// declare a cell object reference
-HSSFCell c = null;
-// create 2 cell styles
-HSSFCellStyle cs = wb.createCellStyle();
-HSSFCellStyle cs2 = wb.createCellStyle();
-HSSFDataFormat df = wb.createDataFormat();
-
-// create 2 fonts objects
-HSSFFont f = wb.createFont();
-HSSFFont f2 = wb.createFont();
-
-// Set font 1 to 12 point type, blue and bold
-f.setFontHeightInPoints((short) 12);
-f.setColor( HSSFColor.RED.index );
-f.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
-
-// Set font 2 to 10 point type, red and bold
-f2.setFontHeightInPoints((short) 10);
-f2.setColor( HSSFFont.RED.index );
-f2.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
-
-// Set cell style and formatting
-cs.setFont(f);
-cs.setDataFormat(df.getFormat("#,##0.0"));
-
-// Set the other cell style and formatting
-cs2.setBorderBottom(cs2.BORDER_THIN);
-cs2.setDataFormat(HSSFDataFormat.getBuiltinFormat("text"));
-cs2.setFont(f2);
-
-
-// Define a few rows
-for(short rownum = (short)0; rownum < 30; rownum++) {
- HSSFRow r = s.createRow(rownum);
- for(short cellnum = (short)0; cellnum < 10; cellnum += 2) {
- HSSFCell c = r.createCell(cellnum);
- HSSFCell c2 = r.createCell(cellnum+1);
-
- c.setCellValue((double)rownum + (cellnum/10));
- c2.setCellValue(new HSSFRichTextString("Hello! " + cellnum);
- }
-}
-
-// Save
-FileOutputStream out = new FileOutputStream("workbook.xls");
-wb.write(out);
-out.close();
- </pre>
+<div class="code">
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"></span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">// import org.apache.poi.hssf.usermodel.*;</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"></span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">HSSFWorkbook wb = new HSSFWorkbook();</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">// create a new sheet</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">HSSFSheet s = wb.createSheet();</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">// declare a row object reference</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">HSSFRow r = null;</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">// declare a cell object reference</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">HSSFCell c = null;</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">// create 2 cell styles</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">HSSFCellStyle cs = wb.createCellStyle();</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">HSSFCellStyle cs2 = wb.createCellStyle();</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">HSSFDataFormat df = wb.createDataFormat();</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"></span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">// create 2 fonts objects</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">HSSFFont f = wb.createFont();</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">HSSFFont f2 = wb.createFont();</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"></span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">// Set font 1 to 12 point type, blue and bold</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">f.setFontHeightInPoints((short) 12);</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">f.setColor( HSSFColor.RED.index );</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">f.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"></span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">// Set font 2 to 10 point type, red and bold</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">f2.setFontHeightInPoints((short) 10);</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">f2.setColor( HSSFFont.RED.index );</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">f2.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"></span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">// Set cell style and formatting</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">cs.setFont(f);</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">cs.setDataFormat(df.getFormat("#,##0.0"));</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"></span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">// Set the other cell style and formatting</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">cs2.setBorderBottom(cs2.BORDER_THIN);</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">cs2.setDataFormat(HSSFDataFormat.getBuiltinFormat("text"));</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">cs2.setFont(f2);</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"></span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"></span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">// Define a few rows</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">for(short rownum = (short)0; rownum < 30; rownum++) {</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"> HSSFRow r = s.createRow(rownum);</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"> for(short cellnum = (short)0; cellnum < 10; cellnum += 2) {</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"> HSSFCell c = r.createCell(cellnum);</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"> HSSFCell c2 = r.createCell(cellnum+1);</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"></span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"> c.setCellValue((double)rownum + (cellnum/10));</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"> c2.setCellValue(new HSSFRichTextString("Hello! " + cellnum);</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"> }</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">}</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"></span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">// Save</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">FileOutputStream out = new FileOutputStream("workbook.xls");</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">wb.write(out);</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">out.close();</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"> </span>
+</div>
+</div>
<a name="New%2C+generic+SS+Usermodel+Code"></a>
<h3 class="boxed">New, generic SS Usermodel Code</h3>
-<pre class="code">
-// import org.apache.poi.ss.usermodel.*;
-
-Workbook[] wbs = new Workbook[] { new HSSFWorkbook(), new XSSFWorkbook() };
-for(int i=0; i<wbs.length; i++) {
- Workbook wb = wbs[i];
- CreationHelper createHelper = wb.getCreationHelper();
-
- // create a new sheet
- Sheet s = wb.createSheet();
- // declare a row object reference
- Row r = null;
- // declare a cell object reference
- Cell c = null;
- // create 2 cell styles
- CellStyle cs = wb.createCellStyle();
- CellStyle cs2 = wb.createCellStyle();
- DataFormat df = wb.createDataFormat();
-
- // create 2 fonts objects
- Font f = wb.createFont();
- Font f2 = wb.createFont();
-
- // Set font 1 to 12 point type, blue and bold
- f.setFontHeightInPoints((short) 12);
- f.setColor( IndexedColors.RED.getIndex() );
- f.setBoldweight(Font.BOLDWEIGHT_BOLD);
-
- // Set font 2 to 10 point type, red and bold
- f2.setFontHeightInPoints((short) 10);
- f2.setColor( IndexedColors.RED.getIndex() );
- f2.setBoldweight(Font.BOLDWEIGHT_BOLD);
-
- // Set cell style and formatting
- cs.setFont(f);
- cs.setDataFormat(df.getFormat("#,##0.0"));
-
- // Set the other cell style and formatting
- cs2.setBorderBottom(cs2.BORDER_THIN);
- cs2.setDataFormat(df.getFormat("text"));
- cs2.setFont(f2);
-
-
- // Define a few rows
- for(int rownum = 0; rownum < 30; rownum++) {
- Row r = s.createRow(rownum);
- for(int cellnum = 0; cellnum < 10; cellnum += 2) {
- Cell c = r.createCell(cellnum);
- Cell c2 = r.createCell(cellnum+1);
-
- c.setCellValue((double)rownum + (cellnum/10));
- c2.setCellValue(
- createHelper.createRichTextString("Hello! " + cellnum)
- );
- }
- }
-
- // Save
- String filename = "workbook.xls";
- if(wb instanceof XSSFWorkbook) {
- filename = filename + "x";
- }
-
- FileOutputStream out = new FileOutputStream(filename);
- wb.write(out);
- out.close();
-}
- </pre>
+<div class="code">
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"></span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">// import org.apache.poi.ss.usermodel.*;</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"></span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">Workbook[] wbs = new Workbook[] { new HSSFWorkbook(), new XSSFWorkbook() };</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">for(int i=0; i<wbs.length; i++) {</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"> Workbook wb = wbs[i];</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"> CreationHelper createHelper = wb.getCreationHelper();</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"></span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"> // create a new sheet</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"> Sheet s = wb.createSheet();</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"> // declare a row object reference</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"> Row r = null;</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"> // declare a cell object reference</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"> Cell c = null;</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"> // create 2 cell styles</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"> CellStyle cs = wb.createCellStyle();</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"> CellStyle cs2 = wb.createCellStyle();</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"> DataFormat df = wb.createDataFormat();</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"></span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"> // create 2 fonts objects</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"> Font f = wb.createFont();</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"> Font f2 = wb.createFont();</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"></span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"> // Set font 1 to 12 point type, blue and bold</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"> f.setFontHeightInPoints((short) 12);</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"> f.setColor( IndexedColors.RED.getIndex() );</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"> f.setBoldweight(Font.BOLDWEIGHT_BOLD);</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"></span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"> // Set font 2 to 10 point type, red and bold</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"> f2.setFontHeightInPoints((short) 10);</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"> f2.setColor( IndexedColors.RED.getIndex() );</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"> f2.setBoldweight(Font.BOLDWEIGHT_BOLD);</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"></span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"> // Set cell style and formatting</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"> cs.setFont(f);</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"> cs.setDataFormat(df.getFormat("#,##0.0"));</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"></span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"> // Set the other cell style and formatting</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"> cs2.setBorderBottom(cs2.BORDER_THIN);</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"> cs2.setDataFormat(df.getFormat("text"));</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"> cs2.setFont(f2);</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"></span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"></span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"> // Define a few rows</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"> for(int rownum = 0; rownum < 30; rownum++) {</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"> Row r = s.createRow(rownum);</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"> for(int cellnum = 0; cellnum < 10; cellnum += 2) {</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"> Cell c = r.createCell(cellnum);</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"> Cell c2 = r.createCell(cellnum+1);</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"> </span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"> c.setCellValue((double)rownum + (cellnum/10));</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"> c2.setCellValue(</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"> createHelper.createRichTextString("Hello! " + cellnum)</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"> );</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"> }</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"> }</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"> </span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"> // Save</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"> String filename = "workbook.xls";</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"> if(wb instanceof XSSFWorkbook) {</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"> filename = filename + "x";</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"> }</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"> </span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"> FileOutputStream out = new FileOutputStream(filename);</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"> wb.write(out);</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"> out.close();</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">}</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"> </span>
+</div>
+</div>
</div>
<p align="right">
Modified: poi/site/publish/components/spreadsheet/eval-devguide.html
URL: http://svn.apache.org/viewvc/poi/site/publish/components/spreadsheet/eval-devguide.html?rev=1867502&r1=1867501&r2=1867502&view=diff
==============================================================================
--- poi/site/publish/components/spreadsheet/eval-devguide.html (original)
+++ poi/site/publish/components/spreadsheet/eval-devguide.html Tue Sep 24 23:26:00 2019
@@ -358,14 +358,32 @@ document.write("Last Published: " + docu
see <a href="#appendixA">Appendix A</a> for the full list.
You can programmatically list supported / unsupported functions using the following helper methods:
</p>
-<pre class="code">
- // list of functions that POI can evaluate
- Collection<String> supportedFuncs = WorkbookEvaluator.getSupportedFunctionNames();
-
- // list of functions that are not supported by POI
- Collection<String> unsupportedFuncs = WorkbookEvaluator.getNotSupportedFunctionNames();
-
- </pre>
+<div class="code">
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"></span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">// list of functions that POI can evaluate</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">Collection<String> supportedFuncs = WorkbookEvaluator.getSupportedFunctionNames();</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"></span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">// list of functions that are not supported by POI</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">Collection<String> unsupportedFuncs = WorkbookEvaluator.getNotSupportedFunctionNames();</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"></span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"> </span>
+</div>
+</div>
</div>
@@ -392,13 +410,29 @@ document.write("Last Published: " + docu
You are about to implement a function XXX and don't know which interface to start from: Function or FreeRefFunction.
Use the following code to check whether your function is from the excel Analysis Toolpack:
</p>
-<pre class="code">
- if(AnalysisToolPack.isATPFunction(functionName)){
- // the function implements org.apache.poi.hssf.record.formula.functions.Function
- } else {
- // the function implements org.apache.poi.hssf.record.formula.functions.FreeRefFunction
- }
- </pre>
+<div class="code">
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"></span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">if(AnalysisToolPack.isATPFunction(functionName)){</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"> // the function implements org.apache.poi.hssf.record.formula.functions.Function</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">} else {</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"> // the function implements org.apache.poi.hssf.record.formula.functions.FreeRefFunction</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">}</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"> </span>
+</div>
+</div>
</div>
@@ -431,39 +465,119 @@ document.write("Last Published: " + docu
<p>
Since SQRTPI takes exactly one argument we start our implementation from org.apache.poi.hssf.record.formula.functions.Fixed1ArgFunction:
</p>
-<pre class="code">
- Function SQRTPI = new Fixed1ArgFunction() {
- public ValueEval evaluate(int srcRowIndex, int srcColumnIndex, ValueEval arg0) {
- try {
- // Retrieves a single value from a variety of different argument types according to standard
- // Excel rules. Does not perform any type conversion.
- ValueEval ve = OperandResolver.getSingleValue(arg0, srcRowIndex, srcColumnIndex);
-
- // Applies some conversion rules if the supplied value is not already a number.
- // Throws EvaluationException(#VALUE!) if the supplied parameter is not a number
- double arg = OperandResolver.coerceValueToDouble(ve);
-
- // this where all the heavy-lifting happens
- double result = Math.sqrt(arg*Math.PI);
-
- // Excel uses the error code #NUM! instead of IEEE <em>NaN</em> and <em>Infinity</em>,
- // so when a numeric function evaluates to Double.NaN or Double.Infinity,
- // be sure to translate the result to the appropriate error code
- if (Double.isNaN(result) || Double.isInfinite(result)) {
- throw new EvaluationException(ErrorEval.NUM_ERROR);
- }
-
- return new NumberEval(result);
- } catch (EvaluationException e){
- return e.getErrorEval();
- }
- }
- }
- </pre>
+<div class="code">
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"></span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">Function SQRTPI = new Fixed1ArgFunction() {</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"> public ValueEval evaluate(int srcRowIndex, int srcColumnIndex, ValueEval arg0) {</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"> try {</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"> // Retrieves a single value from a variety of different argument types according to standard</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"> // Excel rules. Does not perform any type conversion.</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"> ValueEval ve = OperandResolver.getSingleValue(arg0, srcRowIndex, srcColumnIndex);</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"></span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"> // Applies some conversion rules if the supplied value is not already a number.</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"> // Throws EvaluationException(#VALUE!) if the supplied parameter is not a number</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"> double arg = OperandResolver.coerceValueToDouble(ve);</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"></span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"> // this where all the heavy-lifting happens</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"> double result = Math.sqrt(arg*Math.PI);</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"></span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"> // Excel uses the error code #NUM! instead of IEEE </span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"></span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"> </span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">nity</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"></span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"> // so when a numeric function evaluates to Double.NaN or Double.Infinity,</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"> // be sure to translate the result to the appropriate error code</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"> if (Double.isNaN(result) || Double.isInfinite(result)) {</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"> throw new EvaluationException(ErrorEval.NUM_ERROR);</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"> }</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"></span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"> return new NumberEval(result);</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"> } catch (EvaluationException e){</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"> return e.getErrorEval();</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"> }</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"> }</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">}</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"> </span>
+</div>
+</div>
<p>Now when the implementation is ready we need to register it in the formula evaluator:</p>
-<pre class="code">
- WorkbookEvaluator.registerFunction("SQRTPI", SQRTPI);
- </pre>
+<div class="code">
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"></span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">WorkbookEvaluator.registerFunction("SQRTPI", SQRTPI);</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"> </span>
+</div>
+</div>
<p>Voila! The formula evaluator now recognizes SQRTPI! </p>
</div>
@@ -518,184 +632,542 @@ document.write("Last Published: " + docu
<h2 class="boxed">Appendix A</h2>
<div class="section">
<p>Functions supported by POI (as of September 2017)</p>
-<pre class="code">
- ABS
- ACOS
- ACOSH
- ADDRESS
- AND
- ASIN
- ASINH
- ATAN
- ATAN2
- ATANH
- AVEDEV
- AVERAGE
- BIN2DEC
- CEILING
- CHAR
- CHOOSE
- CLEAN
- CODE
- COLUMN
- COLUMNS
- COMBIN
- COMPLEX
- CONCATENATE
- COS
- COSH
- COUNT
- COUNTA
- COUNTBLANK
- COUNTIF
- COUNTIFS
- DATE
- DAY
- DAYS360
- DEC2BIN
- DEC2HEX
- DEGREES
- DELTA
- DEVSQ
- DGET
- DMIN
- DOLLAR
- EDATE
- EOMONTH
- ERROR.TYPE
- EVEN
- EXACT
- EXP
- FACT
- FACTDOUBLE
- FALSE
- FIND
- FIXED
- FLOOR
- FV
- HEX2DEC
- HLOOKUP
- HOUR
- HYPERLINK
- IF
- IFERROR
- IMAGINARY
- IMREAL
- INDEX
- INDIRECT
- INT
- INTERCEPT
- IPMT
- IRR
- ISBLANK
- ISERR
- ISERROR
- ISEVEN
- ISLOGICAL
- ISNA
- ISNONTEXT
- ISNUMBER
- ISODD
- ISREF
- ISTEXT
- LARGE
- LEFT
- LEN
- LN
- LOG
- LOG10
- LOOKUP
- LOWER
- MATCH
- MAX
- MAXA
- MDETERM
- MEDIAN
- MID
- MIN
- MINA
- MINUTE
- MINVERSE
- MIRR
- MMULT
- MOD
- MODE
- MONTH
- MROUND
- NA
- NETWORKDAYS
- NOT
- NOW
- NPER
- NPV
- OCT2DEC
- ODD
- OFFSET
- OR
- PERCENTILE
- PI
- PMT
- POISSON
- POWER
- PPMT
- PRODUCT
- PROPER
- PV
- QUOTIENT
- RADIANS
- RAND
- RANDBETWEEN
- RANK
- RATE
- REPLACE
- REPT
- RIGHT
- ROMAN
- ROUND
- ROUNDDOWN
- ROUNDUP
- ROW
- ROWS
- SEARCH
- SECOND
- SIGN
- SIN
- SINH
- SLOPE
- SMALL
- SQRT
- STDEV
- SUBSTITUTE
- SUBTOTAL
- SUM
- SUMIF
- SUMIFS
- SUMPRODUCT
- SUMSQ
- SUMX2MY2
- SUMX2PY2
- SUMXMY2
- T
- TAN
- TANH
- TEXT
- TIME
- TODAY
- TRANSPOSE
- TRIM
- TRUE
- TRUNC
- UPPER
- VALUE
- VAR
- VARP
- VLOOKUP
- WEEKDAY
- WEEKNUM
- WORKDAY
- YEAR
- YEARFRAC
- </pre>
+<div class="code">
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"></span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">ABS</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">ACOS</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">ACOSH</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">ADDRESS</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">AND</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">ASIN</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">ASINH</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">ATAN</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">ATAN2</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">ATANH</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">AVEDEV</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">AVERAGE</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">BIN2DEC</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">CEILING</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">CHAR</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">CHOOSE</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">CLEAN</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">CODE</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">COLUMN</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">COLUMNS</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">COMBIN</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">COMPLEX</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">CONCATENATE</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">COS</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">COSH</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">COUNT</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">COUNTA</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">COUNTBLANK</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">COUNTIF</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">COUNTIFS</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">DATE</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">DAY</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">DAYS360</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">DEC2BIN</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">DEC2HEX</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">DEGREES</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">DELTA</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">DEVSQ</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">DGET</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">DMIN</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">DOLLAR</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">EDATE</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">EOMONTH</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">ERROR.TYPE</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">EVEN</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">EXACT</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">EXP</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">FACT</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">FACTDOUBLE</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">FALSE</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">FIND</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">FIXED</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">FLOOR</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">FV</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">HEX2DEC</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">HLOOKUP</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">HOUR</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">HYPERLINK</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">IF</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">IFERROR</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">IMAGINARY</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">IMREAL</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">INDEX</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">INDIRECT</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">INT</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">INTERCEPT</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">IPMT</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">IRR</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">ISBLANK</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">ISERR</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">ISERROR</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">ISEVEN</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">ISLOGICAL</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">ISNA</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">ISNONTEXT</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">ISNUMBER</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">ISODD</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">ISREF</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">ISTEXT</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">LARGE</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">LEFT</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">LEN</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">LN</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">LOG</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">LOG10</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">LOOKUP</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">LOWER</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">MATCH</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">MAX</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">MAXA</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">MDETERM</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">MEDIAN</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">MID</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">MIN</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">MINA</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">MINUTE</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">MINVERSE</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">MIRR</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">MMULT</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">MOD</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">MODE</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">MONTH</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">MROUND</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">NA</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">NETWORKDAYS</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">NOT</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">NOW</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">NPER</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">NPV</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">OCT2DEC</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">ODD</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">OFFSET</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">OR</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">PERCENTILE</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">PI</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">PMT</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">POISSON</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">POWER</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">PPMT</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">PRODUCT</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">PROPER</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">PV</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">QUOTIENT</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">RADIANS</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">RAND</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">RANDBETWEEN</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">RANK</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">RATE</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">REPLACE</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">REPT</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">RIGHT</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">ROMAN</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">ROUND</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">ROUNDDOWN</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">ROUNDUP</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">ROW</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">ROWS</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">SEARCH</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">SECOND</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">SIGN</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">SIN</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">SINH</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">SLOPE</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">SMALL</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">SQRT</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">STDEV</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">SUBSTITUTE</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">SUBTOTAL</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">SUM</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">SUMIF</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">SUMIFS</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">SUMPRODUCT</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">SUMSQ</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">SUMX2MY2</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">SUMX2PY2</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">SUMXMY2</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">T</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">TAN</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">TANH</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">TEXT</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">TIME</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">TODAY</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">TRANSPOSE</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">TRIM</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">TRUE</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">TRUNC</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">UPPER</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">VALUE</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">VAR</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">VARP</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">VLOOKUP</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">WEEKDAY</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">WEEKNUM</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">WORKDAY</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">YEAR</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">YEARFRAC</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"> </span>
+</div>
+</div>
</div>
<p align="right">
Modified: poi/site/publish/components/spreadsheet/eval.html
URL: http://svn.apache.org/viewvc/poi/site/publish/components/spreadsheet/eval.html?rev=1867502&r1=1867501&r2=1867502&view=diff
==============================================================================
--- poi/site/publish/components/spreadsheet/eval.html (original)
+++ poi/site/publish/components/spreadsheet/eval.html Tue Sep 24 23:26:00 2019
@@ -346,39 +346,107 @@ document.write("Last Published: " + docu
<h3 class="boxed">Using FormulaEvaluator.evaluate(Cell cell)</h3>
<p>This evaluates a given cell, and returns the new value,
without affecting the cell</p>
-<pre class="code">
-FileInputStream fis = new FileInputStream("c:/temp/test.xls");
-Workbook wb = new HSSFWorkbook(fis); //or new XSSFWorkbook("c:/temp/test.xls")
-Sheet sheet = wb.getSheetAt(0);
-FormulaEvaluator evaluator = wb.getCreationHelper().createFormulaEvaluator();
-
-// suppose your formula is in B3
-CellReference cellReference = new CellReference("B3");
-Row row = sheet.getRow(cellReference.getRow());
-Cell cell = row.getCell(cellReference.getCol());
-
-CellValue cellValue = evaluator.evaluate(cell);
-
-switch (cellValue.getCellType()) {
- case Cell.CELL_TYPE_BOOLEAN:
- System.out.println(cellValue.getBooleanValue());
- break;
- case Cell.CELL_TYPE_NUMERIC:
- System.out.println(cellValue.getNumberValue());
- break;
- case Cell.CELL_TYPE_STRING:
- System.out.println(cellValue.getStringValue());
- break;
- case Cell.CELL_TYPE_BLANK:
- break;
- case Cell.CELL_TYPE_ERROR:
- break;
-
- // CELL_TYPE_FORMULA will never happen
- case Cell.CELL_TYPE_FORMULA:
- break;
-}
- </pre>
+<div class="code">
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"></span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">FileInputStream fis = new FileInputStream("c:/temp/test.xls");</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">Workbook wb = new HSSFWorkbook(fis); //or new XSSFWorkbook("c:/temp/test.xls")</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">Sheet sheet = wb.getSheetAt(0);</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">FormulaEvaluator evaluator = wb.getCreationHelper().createFormulaEvaluator();</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"></span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">// suppose your formula is in B3</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">CellReference cellReference = new CellReference("B3"); </span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">Row row = sheet.getRow(cellReference.getRow());</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">Cell cell = row.getCell(cellReference.getCol()); </span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"></span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">CellValue cellValue = evaluator.evaluate(cell);</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"></span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">switch (cellValue.getCellType()) {</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"> case Cell.CELL_TYPE_BOOLEAN:</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"> System.out.println(cellValue.getBooleanValue());</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"> break;</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"> case Cell.CELL_TYPE_NUMERIC:</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"> System.out.println(cellValue.getNumberValue());</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"> break;</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"> case Cell.CELL_TYPE_STRING:</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"> System.out.println(cellValue.getStringValue());</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"> break;</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"> case Cell.CELL_TYPE_BLANK:</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"> break;</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"> case Cell.CELL_TYPE_ERROR:</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"> break;</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"></span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"> // CELL_TYPE_FORMULA will never happen</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"> case Cell.CELL_TYPE_FORMULA: </span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"> break;</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">} </span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"> </span>
+</div>
+</div>
<p>Thus using the retrieved value (of type
FormulaEvaluator.CellValue - a nested class) returned
by FormulaEvaluator is similar to using a Cell object
@@ -386,7 +454,7 @@ switch (cellValue.getCellType()) {
a simple value object and does not maintain reference
to the original cell.
</p>
-<a name="EvaluateFormulaCell" id="EvaluateFormulaCell"></a><a name="Using+FormulaEvaluator.-N1006C"></a>
+<a name="EvaluateFormulaCell" id="EvaluateFormulaCell"></a><a name="Using+FormulaEvaluator.-N1014D"></a>
<h3 class="boxed">Using FormulaEvaluator.evaluateFormulaCell(Cell cell)</h3>
<p>
<strong>evaluateFormulaCell</strong>(Cell cell)
@@ -397,41 +465,111 @@ switch (cellValue.getCellType()) {
formula remains in the cell, just with a new value</p>
<p>The return of the function is the type of the
formula result, such as Cell.CELL_TYPE_BOOLEAN</p>
-<pre class="code">
-FileInputStream fis = new FileInputStream("/somepath/test.xls");
-Workbook wb = new HSSFWorkbook(fis); //or new XSSFWorkbook("/somepath/test.xls")
-Sheet sheet = wb.getSheetAt(0);
-FormulaEvaluator evaluator = wb.getCreationHelper().createFormulaEvaluator();
-
-// suppose your formula is in B3
-CellReference cellReference = new CellReference("B3");
-Row row = sheet.getRow(cellReference.getRow());
-Cell cell = row.getCell(cellReference.getCol());
-
-if (cell!=null) {
- switch (evaluator.evaluateFormulaCell(cell)) {
- case Cell.CELL_TYPE_BOOLEAN:
- System.out.println(cell.getBooleanCellValue());
- break;
- case Cell.CELL_TYPE_NUMERIC:
- System.out.println(cell.getNumericCellValue());
- break;
- case Cell.CELL_TYPE_STRING:
- System.out.println(cell.getStringCellValue());
- break;
- case Cell.CELL_TYPE_BLANK:
- break;
- case Cell.CELL_TYPE_ERROR:
- System.out.println(cell.getErrorCellValue());
- break;
-
- // CELL_TYPE_FORMULA will never occur
- case Cell.CELL_TYPE_FORMULA:
- break;
- }
-}
- </pre>
-<a name="EvaluateInCell" id="EvaluateInCell"></a><a name="Using+FormulaEvaluator.-N10084"></a>
+<div class="code">
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"></span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">FileInputStream fis = new FileInputStream("/somepath/test.xls");</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">Workbook wb = new HSSFWorkbook(fis); //or new XSSFWorkbook("/somepath/test.xls")</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">Sheet sheet = wb.getSheetAt(0);</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">FormulaEvaluator evaluator = wb.getCreationHelper().createFormulaEvaluator();</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"></span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">// suppose your formula is in B3</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">CellReference cellReference = new CellReference("B3"); </span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">Row row = sheet.getRow(cellReference.getRow());</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">Cell cell = row.getCell(cellReference.getCol()); </span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"></span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">if (cell!=null) {</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"> switch (evaluator.evaluateFormulaCell(cell)) {</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"> case Cell.CELL_TYPE_BOOLEAN:</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"> System.out.println(cell.getBooleanCellValue());</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"> break;</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"> case Cell.CELL_TYPE_NUMERIC:</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"> System.out.println(cell.getNumericCellValue());</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"> break;</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"> case Cell.CELL_TYPE_STRING:</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"> System.out.println(cell.getStringCellValue());</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"> break;</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"> case Cell.CELL_TYPE_BLANK:</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"> break;</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"> case Cell.CELL_TYPE_ERROR:</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"> System.out.println(cell.getErrorCellValue());</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"> break;</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"></span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"> // CELL_TYPE_FORMULA will never occur</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"> case Cell.CELL_TYPE_FORMULA: </span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"> break;</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"> }</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">}</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"> </span>
+</div>
+</div>
+<a name="EvaluateInCell" id="EvaluateInCell"></a><a name="Using+FormulaEvaluator.-N1024E"></a>
<h3 class="boxed">Using FormulaEvaluator.evaluateInCell(Cell cell)</h3>
<p>
<strong>evaluateInCell</strong>(Cell cell) will check to
@@ -439,57 +577,165 @@ if (cell!=null) {
then no changes will be made to it. If it is, then the
formula is evaluated, and the new value saved into the cell,
in place of the old formula.</p>
-<pre class="code">
-FileInputStream fis = new FileInputStream("/somepath/test.xls");
-Workbook wb = new HSSFWorkbook(fis); //or new XSSFWorkbook("/somepath/test.xls")
-Sheet sheet = wb.getSheetAt(0);
-FormulaEvaluator evaluator = wb.getCreationHelper().createFormulaEvaluator();
-
-// suppose your formula is in B3
-CellReference cellReference = new CellReference("B3");
-Row row = sheet.getRow(cellReference.getRow());
-Cell cell = row.getCell(cellReference.getCol());
-
-if (cell!=null) {
- switch (evaluator.<strong>evaluateInCell</strong>(cell).getCellType()) {
- case Cell.CELL_TYPE_BOOLEAN:
- System.out.println(cell.getBooleanCellValue());
- break;
- case Cell.CELL_TYPE_NUMERIC:
- System.out.println(cell.getNumericCellValue());
- break;
- case Cell.CELL_TYPE_STRING:
- System.out.println(cell.getStringCellValue());
- break;
- case Cell.CELL_TYPE_BLANK:
- break;
- case Cell.CELL_TYPE_ERROR:
- System.out.println(cell.getErrorCellValue());
- break;
-
- // CELL_TYPE_FORMULA will never occur
- case Cell.CELL_TYPE_FORMULA:
- break;
- }
-}
-
- </pre>
+<div class="code">
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"></span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">FileInputStream fis = new FileInputStream("/somepath/test.xls");</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">Workbook wb = new HSSFWorkbook(fis); //or new XSSFWorkbook("/somepath/test.xls")</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">Sheet sheet = wb.getSheetAt(0);</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">FormulaEvaluator evaluator = wb.getCreationHelper().createFormulaEvaluator();</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"></span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">// suppose your formula is in B3</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">CellReference cellReference = new CellReference("B3");</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">Row row = sheet.getRow(cellReference.getRow());</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">Cell cell = row.getCell(cellReference.getCol()); </span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"></span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">if (cell!=null) {</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"> switch (evaluator.</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">evaluateInCell</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">(cell).getCellType()) {</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"> case Cell.CELL_TYPE_BOOLEAN:</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"> System.out.println(cell.getBooleanCellValue());</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"> break;</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"> case Cell.CELL_TYPE_NUMERIC:</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"> System.out.println(cell.getNumericCellValue());</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"> break;</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"> case Cell.CELL_TYPE_STRING:</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"> System.out.println(cell.getStringCellValue());</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"> break;</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"> case Cell.CELL_TYPE_BLANK:</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"> break;</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"> case Cell.CELL_TYPE_ERROR:</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"> System.out.println(cell.getErrorCellValue());</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"> break;</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"></span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"> // CELL_TYPE_FORMULA will never occur</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"> case Cell.CELL_TYPE_FORMULA:</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"> break;</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"> }</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">}</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"></span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"> </span>
+</div>
+</div>
<a name="EvaluateAll" id="EvaluateAll"></a><a name="Re-calculating+all+formulas+in+a+Workbook"></a>
<h3 class="boxed">Re-calculating all formulas in a Workbook</h3>
-<pre class="code">
-FileInputStream fis = new FileInputStream("/somepath/test.xls");
-Workbook wb = new HSSFWorkbook(fis); //or new XSSFWorkbook("/somepath/test.xls")
-FormulaEvaluator evaluator = wb.getCreationHelper().createFormulaEvaluator();
-for (Sheet sheet : wb) {
- for (Row r : sheet) {
- for (Cell c : r) {
- if (c.getCellType() == Cell.CELL_TYPE_FORMULA) {
- evaluator.evaluateFormulaCell(c);
- }
- }
- }
-}
- </pre>
+<div class="code">
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"></span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">FileInputStream fis = new FileInputStream("/somepath/test.xls");</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">Workbook wb = new HSSFWorkbook(fis); //or new XSSFWorkbook("/somepath/test.xls")</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">FormulaEvaluator evaluator = wb.getCreationHelper().createFormulaEvaluator();</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">for (Sheet sheet : wb) {</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"> for (Row r : sheet) {</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"> for (Cell c : r) {</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"> if (c.getCellType() == Cell.CELL_TYPE_FORMULA) {</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"> evaluator.evaluateFormulaCell(c);</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"> }</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"> }</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"> }</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">}</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"> </span>
+</div>
+</div>
<p>Alternately, if you know which of HSSF or XSSF you're working
with, then you can call the static
<strong>evaluateAllFormulaCells</strong> method on the appropriate
@@ -512,16 +758,38 @@ for (Sheet sheet : wb) {
<p>
Now modify the workbook with POI:
</p>
-<pre class="code">
- Workbook wb = WorkbookFactory.create(new FileInputStream("workbook.xls"));
-
- Sheet sh = wb.getSheetAt(0);
- sh.getRow(0).getCell(0).setCellValue(2); // set A1=2
-
- FileOutputStream out = new FileOutputStream("workbook2.xls");
- wb.write(out);
- out.close();
- </pre>
+<div class="code">
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"></span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">Workbook wb = WorkbookFactory.create(new FileInputStream("workbook.xls"));</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"></span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">Sheet sh = wb.getSheetAt(0);</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">sh.getRow(0).getCell(0).setCellValue(2); // set A1=2</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"></span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">FileOutputStream out = new FileOutputStream("workbook2.xls");</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">wb.write(out);</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">out.close();</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"> </span>
+</div>
+</div>
<p>
Now open workbook2.xls in Excel and the value in C1 is still 2 while you expected 3. Wrong? No!
The point is that Excel caches previously calculated results and you need to trigger recalculation to updated them.
@@ -531,25 +799,61 @@ for (Sheet sheet : wb) {
<p>
1. Re-evaluate formulas with POI's FormulaEvaluator:
</p>
-<pre class="code">
- Workbook wb = WorkbookFactory.create(new FileInputStream("workbook.xls"));
-
- Sheet sh = wb.getSheetAt(0);
- sh.getRow(0).getCell(0).setCellValue(2); // set A1=2
-
- wb.getCreationHelper().createFormulaEvaluator().evaluateAll();
- </pre>
+<div class="code">
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"></span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">Workbook wb = WorkbookFactory.create(new FileInputStream("workbook.xls"));</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"></span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">Sheet sh = wb.getSheetAt(0);</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">sh.getRow(0).getCell(0).setCellValue(2); // set A1=2</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"></span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">wb.getCreationHelper().createFormulaEvaluator().evaluateAll();</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"> </span>
+</div>
+</div>
<p>
2. Delegate re-calculation to Excel. The application will perform a full recalculation when the workbook is opened:
</p>
-<pre class="code">
- Workbook wb = WorkbookFactory.create(new FileInputStream("workbook.xls"));
-
- Sheet sh = wb.getSheetAt(0);
- sh.getRow(0).getCell(0).setCellValue(2); // set A1=2
-
- wb.setForceFormulaRecalculation(true);
- </pre>
+<div class="code">
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"></span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">Workbook wb = WorkbookFactory.create(new FileInputStream("workbook.xls"));</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"></span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">Sheet sh = wb.getSheetAt(0);</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">sh.getRow(0).getCell(0).setCellValue(2); // set A1=2</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"></span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">wb.setForceFormulaRecalculation(true);</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"> </span>
+</div>
+</div>
</div>
@@ -561,11 +865,23 @@ for (Sheet sheet : wb) {
refer to a Named Range or Cell in a different workbook.
These cross-workbook references are normally called <em>External
References</em>. These are formulas which look something like:</p>
-<pre class="code">
- =SUM([Finances.xlsx]Numbers!D10:D25)
- =SUM('C:\Data\[Finances.xlsx]Numbers'!D10:D25)
- =SUM([Finances.xlsx]Range20)
- </pre>
+<div class="code">
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"></span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">=SUM([Finances.xlsx]Numbers!D10:D25)</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">=SUM('C:\Data\[Finances.xlsx]Numbers'!D10:D25)</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">=SUM([Finances.xlsx]Range20)</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"> </span>
+</div>
+</div>
<p>If you don't have access to these other workbooks, then you
should call
<a href="../../apidocs/dev/org/apache/poi/ss/usermodel/FormulaEvaluator.html#setIgnoreMissingWorkbooks(boolean)">setIgnoreMissingWorkbooks(true)</a>
@@ -578,24 +894,62 @@ for (Sheet sheet : wb) {
the
<a href="../../apidocs/dev/org/apache/poi/ss/usermodel/FormulaEvaluator.html#setupReferencedWorkbooks(java.util.Map)">setupReferencedWorkbooks(java.util.Map<java.lang.String,FormulaEvaluator> workbooks)</a>
method. You should normally do something like:</p>
-<pre class="code">
-// Create a FormulaEvaluator to use
-FormulaEvaluator mainWorkbookEvaluator = workbook.getCreationHelper().createFormulaEvaluator();
-
-// Track the workbook references
-Map<String,FormulaEvaluator> workbooks = new HashMap<String, FormulaEvaluator>();
-// Add this workbook
-workbooks.put("report.xlsx", mainWorkbookEvaluator);
-// Add two others
-workbooks.put("input.xls", WorkbookFactory.create("C:\\temp\\input22.xls").getCreationHelper().createFormulaEvaluator());
-workbooks.put("lookups.xlsx", WorkbookFactory.create("/home/poi/data/tmp-lookups.xlsx").getCreationHelper().createFormulaEvaluator());
-
-// Attach them
-mainWorkbookEvaluator.setupReferencedWorkbooks(workbooks);
-
-// Evaluate
-mainWorkbookEvaluator.evaluateAll();
- </pre>
+<div class="code">
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"></span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">// Create a FormulaEvaluator to use</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">FormulaEvaluator mainWorkbookEvaluator = workbook.getCreationHelper().createFormulaEvaluator();</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"></span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">// Track the workbook references</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">Map<String,FormulaEvaluator> workbooks = new HashMap<String, FormulaEvaluator>();</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">// Add this workbook</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">workbooks.put("report.xlsx", mainWorkbookEvaluator);</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">// Add two others</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">workbooks.put("input.xls", WorkbookFactory.create("C:\\temp\\input22.xls").getCreationHelper().createFormulaEvaluator());</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">workbooks.put("lookups.xlsx", WorkbookFactory.create("/home/poi/data/tmp-lookups.xlsx").getCreationHelper().createFormulaEvaluator());</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"></span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">// Attach them</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">mainWorkbookEvaluator.setupReferencedWorkbooks(workbooks);</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"></span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">// Evaluate</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">mainWorkbookEvaluator.evaluateAll();</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"> </span>
+</div>
+</div>
</div>
@@ -653,23 +1007,59 @@ mainWorkbookEvaluator.evaluateAll();
<p>The output of this logging may be very large (depends on your EXCEL), so this logging has to be explicitly enabled
for each single formula evaluation. Should not be used in production - only for specific development use.</p>
<p>Example use:</p>
-<pre class="code">
- // activate logging to console
- System.setProperty("org.apache.poi.util.POILogger", "org.apache.poi.util.SystemOutLogger");
- System.setProperty("poi.log.level", POILogger.INFO + "");
-
- // open your file
- Workbook wb = new HSSFWorkbook(new FileInputStream("foobar.xls"));
- FormulaEvaluator evaluator = wb.getCreationHelper().createFormulaEvaluator();
-
- // get your cell
- Cell cell = wb.getSheet(0).getRow(0).getCell(0); // just a dummy example
-
- // perform debug output for the next evaluate-call only
- evaluator.setDebugEvaluationOutputForNextEval(true);
- evaluator.evaluateFormulaCell(cell);
- evaluator.evaluateFormulaCell(cell); // no logging performed for this next evaluate-call
- </pre>
+<div class="code">
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"></span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">// activate logging to console</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">System.setProperty("org.apache.poi.util.POILogger", "org.apache.poi.util.SystemOutLogger");</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">System.setProperty("poi.log.level", POILogger.INFO + "");</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"></span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">// open your file</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">Workbook wb = new HSSFWorkbook(new FileInputStream("foobar.xls"));</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"> FormulaEvaluator evaluator = wb.getCreationHelper().createFormulaEvaluator();</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"></span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">// get your cell</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">Cell cell = wb.getSheet(0).getRow(0).getCell(0); // just a dummy example</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"></span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">// perform debug output for the next evaluate-call only</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"> evaluator.setDebugEvaluationOutputForNextEval(true);</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">evaluator.evaluateFormulaCell(cell);</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">evaluator.evaluateFormulaCell(cell); // no logging performed for this next evaluate-call</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"> </span>
+</div>
+</div>
<p>The special Logger called "POI.FormulaEval" is used (useful if you use the CommonsLogger and a detailed logging configuration).
The used log levels are WARN and INFO (for detailed parameter info and results) - the level are so high to allow this
special logging without being disturbed by the bunch of DEBUG log entries from other classes.</p>
Modified: poi/site/publish/components/spreadsheet/examples.html
URL: http://svn.apache.org/viewvc/poi/site/publish/components/spreadsheet/examples.html?rev=1867502&r1=1867501&r2=1867502&view=diff
==============================================================================
--- poi/site/publish/components/spreadsheet/examples.html (original)
+++ poi/site/publish/components/spreadsheet/examples.html Tue Sep 24 23:26:00 2019
@@ -293,11 +293,23 @@ document.write("Last Published: " + docu
can generate either *.xls or *.xlsx output just by setting a
command-line argument:
</p>
-<pre class="code">
- BusinessPlan -xls
- or
- BusinessPlan -xlsx
- </pre>
+<div class="code">
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"></span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">BusinessPlan -xls </span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"> or</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">BusinessPlan -xlsx</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"></span>
+</div>
+</div>
<p>All sample source is available in <a href="http://svn.apache.org/repos/asf/poi/trunk/src/examples/src/org/apache/poi/ss/examples/">SVN</a>
</p>
<p>In addition, there are a handful of
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@poi.apache.org
For additional commands, e-mail: commits-help@poi.apache.org