You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@poi.apache.org by ye...@apache.org on 2012/12/19 10:27:39 UTC
svn commit: r1423805 [13/18] - in /poi/site/publish: ./ hdgf/ hmef/ hpbf/
hpsf/ hsmf/ hwpf/ images/ oxml4j/ poifs/ poifs/images/ resources/
resources/images/ skin/ skin/css/ skin/images/ slideshow/ spreadsheet/
spreadsheet/images/
Added: poi/site/publish/spreadsheet/converting.html
URL: http://svn.apache.org/viewvc/poi/site/publish/spreadsheet/converting.html?rev=1423805&view=auto
==============================================================================
--- poi/site/publish/spreadsheet/converting.html (added)
+++ poi/site/publish/spreadsheet/converting.html Wed Dec 19 09:27:20 2012
@@ -0,0 +1,495 @@
+<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
+<html>
+<head>
+<META http-equiv="Content-Type" content="text/html; charset=UTF-8">
+<!--*** This is a generated file. Do not edit. ***-->
+<link rel="stylesheet" href="../skin/tigris.css" type="text/css">
+<link rel="stylesheet" href="../skin/mysite.css" type="text/css">
+<link rel="stylesheet" href="../skin/site.css" type="text/css">
+<link media="print" rel="stylesheet" href="../skin/print.css" type="text/css">
+<title>Upgrading to POI 3.5, including converting existing HSSF Usermodel code to SS Usermodel (for XSSF and HSSF)</title>
+</head>
+<body bgcolor="white" class="composite">
+<!--================= start Banner ==================-->
+<div id="banner">
+<table width="100%" cellpadding="8" cellspacing="0" summary="banner" border="0">
+<tbody>
+<tr>
+<!--================= start Group Logo ==================-->
+<td width="50%" align="left">
+<div class="groupLogo">
+<a href="http://poi.apache.org"><img border="0" class="logoImage" alt="Apache POI" src="../resources/images/group-logo.jpg"></a>
+</div>
+</td>
+<!--================= end Group Logo ==================-->
+<!--================= start Project Logo ==================--><td width="50%" align="right">
+<div align="right" class="projectLogo">
+<a href="http://poi.apache.org/"><img border="0" class="logoImage" alt="POI" src="../resources/images/project-logo.jpg"></a>
+</div>
+</td>
+<!--================= end Project Logo ==================-->
+</tr>
+</tbody>
+</table>
+</div>
+<!--================= end Banner ==================-->
+<!--================= start Main ==================-->
+<table width="100%" cellpadding="0" cellspacing="0" border="0" summary="nav" id="breadcrumbs">
+<tbody>
+<!--================= start Status ==================-->
+<tr class="status">
+<td>
+<!--================= start BreadCrumb ==================--><a href="http://www.apache.org/">Apache</a> | <a href="http://poi.apache.org/">POI</a><a href=""></a>
+<!--================= end BreadCrumb ==================--></td><td id="tabs">
+<!--================= start Tabs ==================-->
+<div class="tab">
+<span class="selectedTab"><a class="base-selected" href="../index.html">Home</a></span> | <script language="Javascript" type="text/javascript">
+function printit() {
+if (window.print) {
+ window.print() ;
+} else {
+ var WebBrowser = '<OBJECT ID="WebBrowser1" WIDTH="0" HEIGHT="0" CLASSID="CLSID:8856F961-340A-11D0-A96B-00C04FD705A2"></OBJECT>';
+document.body.insertAdjacentHTML('beforeEnd', WebBrowser);
+ WebBrowser1.ExecWB(6, 2);//Use a 1 vs. a 2 for a prompting dialog box WebBrowser1.outerHTML = "";
+}
+}
+</script><script language="Javascript" type="text/javascript">
+var NS = (navigator.appName == "Netscape");
+var VERSION = parseInt(navigator.appVersion);
+if (VERSION > 3) {
+ document.write(' <a title="PRINT this page OUT" href="javascript:printit()">PRINT</a>');
+}
+</script>
+</div>
+<!--================= end Tabs ==================-->
+</td>
+</tr>
+</tbody>
+</table>
+<!--================= end Status ==================-->
+<table id="main" width="100%" cellpadding="8" cellspacing="0" summary="" border="0">
+<tbody>
+<tr valign="top">
+<!--================= start Menu ==================-->
+<td id="leftcol">
+<div id="navcolumn">
+<div class="menuBar">
+<div class="menu">
+<span class="menuLabel">Apache POI</span>
+
+<div class="menuItem">
+<a href="../index.html">Top</a>
+</div>
+
+</div>
+<div class="menu">
+<span class="menuLabel">HSSF+XSSF</span>
+
+<div class="menuItem">
+<a href="index.html">Overview</a>
+</div>
+
+<div class="menuItem">
+<a href="quick-guide.html">Quick Guide</a>
+</div>
+
+<div class="menuItem">
+<a href="how-to.html">HOWTO</a>
+</div>
+
+<div class="menuItem">
+<span class="menuSelected">HSSF to SS Converting</span>
+</div>
+
+<div class="menuItem">
+<a href="formula.html">Formula Support</a>
+</div>
+
+<div class="menuItem">
+<a href="eval.html">Formula Evaluation</a>
+</div>
+
+<div class="menuItem">
+<a href="eval-devguide.html">Eval Dev Guide</a>
+</div>
+
+<div class="menuItem">
+<a href="examples.html">Examples</a>
+</div>
+
+<div class="menuItem">
+<a href="use-case.html">Use Case</a>
+</div>
+
+<div class="menuItem">
+<a href="diagrams.html">Pictorial Docs</a>
+</div>
+
+<div class="menuItem">
+<a href="limitations.html">Limitations</a>
+</div>
+
+<div class="menuItem">
+<a href="user-defined-functions.html">User Defined Functions</a>
+</div>
+
+<div class="menuItem">
+<a href="excelant.html">ExcelAnt Tests</a>
+</div>
+
+</div>
+<div class="menu">
+<span class="menuLabel">Contributer's Guide</span>
+
+<div class="menuItem">
+<a href="hacking-hssf.html">Hacking HSSF</a>
+</div>
+
+<div class="menuItem">
+<a href="record-generator.html">Record Generator</a>
+</div>
+
+<div class="menuItem">
+<a href="chart.html">Charts</a>
+</div>
+
+</div>
+</div>
+</div>
+<form target="_blank" action="http://www.google.com/search" method="get">
+<table summary="search" border="0" cellspacing="0" cellpadding="0">
+<tr>
+<td><img height="1" width="1" alt="" src="../skin/images/spacer.gif" class="spacer"></td><td nowrap="nowrap">
+ Search Apache POI<br>
+<input value="poi.apache.org" name="sitesearch" type="hidden"><input size="10" name="q" id="query" type="text"><img height="1" width="5" alt="" src="../skin/images/spacer.gif" class="spacer"><input name="Search" value="GO" type="submit"></td><td><img height="1" width="1" alt="" src="../skin/images/spacer.gif" class="spacer"></td>
+</tr>
+<tr>
+<td colspan="3"><img height="7" width="1" alt="" src="../skin/images/spacer.gif" class="spacer"></td>
+</tr>
+<tr>
+<td class="bottom-left-thick"></td><td bgcolor="#a5b6c6"><img height="1" width="1" alt="" src="../skin/images/spacer.gif" class="spacer"></td><td class="bottom-right-thick"></td>
+</tr>
+</table>
+</form>
+</td>
+<!--================= end Menu ==================-->
+<!--================= start Content ==================--><td>
+<div id="bodycol">
+<div class="app">
+<div align="center">
+<h1>Upgrading to POI 3.5, including converting existing HSSF Usermodel code to SS Usermodel (for XSSF and HSSF)</h1>
+</div>
+<div class="h3">
+
+
+
+<a name="Things+that+have+to+be+changed+when+upgrading+to+POI+3.5"></a>
+<div class="h3">
+<h3>Things that have to be changed when upgrading to POI 3.5</h3>
+</div>
+
+<p>Wherever possible, we have tried to ensure that you can use your
+ existing POI code with POI 3.5 without requiring any changes. However,
+ Java doesn't always make that easy, and unfortunately there are a
+ few changes that may be required for some users.</p>
+
+<a name="org.apache.poi.hssf.usermodel.HSSFFormulaEvaluator.CellValue"></a>
+<div class="h4">
+<h4>org.apache.poi.hssf.usermodel.HSSFFormulaEvaluator.CellValue</h4>
+</div>
+
+<p>Annoyingly, java will not let you access a static inner class via
+ a child of the parent one. So, all references to
+ <em>org.apache.poi.hssf.usermodel.HSSFFormulaEvaluator.CellValue</em>
+ will need to be changed to
+ <em>org.apache.poi.ss.usermodel.FormulaEvaluator.CellValue</em>
+
+</p>
+
+
+<a name="org.apache.poi.hssf.usermodel.HSSFRow.MissingCellPolicy"></a>
+<div class="h4">
+<h4>org.apache.poi.hssf.usermodel.HSSFRow.MissingCellPolicy</h4>
+</div>
+
+<p>Annoyingly, java will not let you access a static inner class via
+ a child of the parent one. So, all references to
+ <em>org.apache.poi.hssf.usermodel.HSSFRow.MissingCellPolicy</em>
+ will need to be changed to
+ <em>org.apache.poi.ss.usermodel.Row.MissingCellPolicy</em>
+
+</p>
+
+
+<a name="DDF+and+org.apache.poi.hssf.record.RecordFormatException"></a>
+<div class="h4">
+<h4>DDF and org.apache.poi.hssf.record.RecordFormatException</h4>
+</div>
+
+<p>Previously, record level errors within DDF would throw an
+ exception from the hssf class heirachy. Now, record level errors
+ within DDF will throw a more general RecordFormatException,
+ <em>org.apache.poi.util.RecordFormatException</em>
+</p>
+
+<p>In addition, org.apache.poi.hssf.record.RecordFormatException
+ has been changed to inherit from the new
+ <em>org.apache.poi.util.RecordFormatException</em>, so you may
+ wish to change catches of the hssf version to the new util version.
+ </p>
+
+
+
+<a name="Converting+existing+HSSF+Usermodel+code+to+SS+Usermodel+%28for+XSSF+and+HSSF%29"></a>
+<div class="h3">
+<h3>Converting existing HSSF Usermodel code to SS Usermodel (for XSSF and HSSF)</h3>
+</div>
+
+
+<a name="Why+change%3F"></a>
+<div class="h4">
+<h4>Why change?</h4>
+</div>
+
+<p>If you have existing HSSF usermodel code that works just
+ fine, and you don't want to use the new OOXML XSSF support,
+ then you probably don't need to. Your existing HSSF only code
+ will continue to work just fine.</p>
+
+<p>However, if you want to be able to work with both HSSF for
+ your .xls files, and also XSSF for .xslx files, then you will
+ need to make some slight tweaks to your code.</p>
+
+
+<a name="org.apache.poi.ss.usermodel"></a>
+<div class="h4">
+<h4>org.apache.poi.ss.usermodel</h4>
+</div>
+
+<p>The new SS usermodel (org.apache.poi.ss.usermodel) is very
+ heavily based on the old HSSF usermodel
+ (org.apache.poi.hssf.usermodel). The main difference is that
+ the package name and class names have been tweaked to remove
+ HSSF from them. Otherwise, the new SS Usermodel interfaces
+ should provide the same functionality.</p>
+
+
+<a name="Constructors"></a>
+<div class="h4">
+<h4>Constructors</h4>
+</div>
+
+<p>Calling the empty HSSFWorkbook remains as the way to
+ create a new, empty Workbook object. To open an existing
+ Worbook, you should now call WorkbookFactory.create(inp).</p>
+
+<p>For all other cases when you would have called a
+ Usermodel constructor, such as 'new HSSFRichTextString()' or
+ 'new HSSFDataFormat', you should instead use a CreationHelper.
+ There's a method on the Workbook to get a CreationHelper, and
+ the CreationHelper will then handle constructing new objects
+ for you.</p>
+
+
+<a name="Other+Code"></a>
+<div class="h4">
+<h4>Other Code</h4>
+</div>
+
+<p>For all other code, generally change a reference from
+ org.apache.poi.hssf.usermodel.HSSFFoo to a reference to
+ org.apache.poi.ss.usermodel.Foo. Method signatures should
+ otherwise remain the same, and it should all then work for
+ both XSSF and HSSF.</p>
+
+
+
+<a name="Worked+Examples"></a>
+<div class="h3">
+<h3>Worked Examples</h3>
+</div>
+
+<a name="Old+HSSF+Code"></a>
+<div class="h4">
+<h4>Old HSSF Code</h4>
+</div>
+
+<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>
+
+
+<a name="New%2C+generic+SS+Usermodel+Code"></a>
+<div class="h4">
+<h4>New, generic SS Usermodel Code</h4>
+</div>
+
+<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 id="authors" align="right">by Nick Burch</div>
+</div>
+</div>
+</div>
+</td>
+<!--================= end Content ==================-->
+</tr>
+</tbody>
+</table>
+<!--================= end Main ==================-->
+<!--================= start Footer ==================-->
+<div id="footer">
+<table summary="footer" cellspacing="0" cellpadding="4" width="100%" border="0">
+<tbody>
+<tr>
+<!--================= start Copyright ==================-->
+<td colspan="2">
+<div align="center">
+<div class="copyright">
+ Copyright © 2002-2012 The Apache Software Foundation. All rights reserved.<br>
+ Apache POI, POI, Apache, the Apache feather logo, and the Apache
+ POI project logo are trademarks of The Apache Software Foundation.
+ </div>
+</div>
+</td>
+<!--================= end Copyright ==================-->
+</tr>
+<tr>
+<td align="left">
+<!--================= start Host ==================-->
+<!--================= end Host ==================--></td><td align="right">
+<!--================= start Credits ==================-->
+<div align="right">
+<div class="credit"></div>
+</div>
+<!--================= end Credits ==================-->
+</td>
+</tr>
+</tbody>
+</table>
+</div>
+<!--================= end Footer ==================-->
+</body>
+</html>
Propchange: poi/site/publish/spreadsheet/converting.html
------------------------------------------------------------------------------
svn:executable = *
Added: poi/site/publish/spreadsheet/diagram1.html
URL: http://svn.apache.org/viewvc/poi/site/publish/spreadsheet/diagram1.html?rev=1423805&view=auto
==============================================================================
--- poi/site/publish/spreadsheet/diagram1.html (added)
+++ poi/site/publish/spreadsheet/diagram1.html Wed Dec 19 09:27:20 2012
@@ -0,0 +1,243 @@
+<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
+<html>
+<head>
+<META http-equiv="Content-Type" content="text/html; charset=UTF-8">
+<!--*** This is a generated file. Do not edit. ***-->
+<link rel="stylesheet" href="../skin/tigris.css" type="text/css">
+<link rel="stylesheet" href="../skin/mysite.css" type="text/css">
+<link rel="stylesheet" href="../skin/site.css" type="text/css">
+<link media="print" rel="stylesheet" href="../skin/print.css" type="text/css">
+<title>HSSF</title>
+</head>
+<body bgcolor="white" class="composite">
+<!--================= start Banner ==================-->
+<div id="banner">
+<table width="100%" cellpadding="8" cellspacing="0" summary="banner" border="0">
+<tbody>
+<tr>
+<!--================= start Group Logo ==================-->
+<td width="50%" align="left">
+<div class="groupLogo">
+<a href="http://poi.apache.org"><img border="0" class="logoImage" alt="Apache POI" src="../resources/images/group-logo.jpg"></a>
+</div>
+</td>
+<!--================= end Group Logo ==================-->
+<!--================= start Project Logo ==================--><td width="50%" align="right">
+<div align="right" class="projectLogo">
+<a href="http://poi.apache.org/"><img border="0" class="logoImage" alt="POI" src="../resources/images/project-logo.jpg"></a>
+</div>
+</td>
+<!--================= end Project Logo ==================-->
+</tr>
+</tbody>
+</table>
+</div>
+<!--================= end Banner ==================-->
+<!--================= start Main ==================-->
+<table width="100%" cellpadding="0" cellspacing="0" border="0" summary="nav" id="breadcrumbs">
+<tbody>
+<!--================= start Status ==================-->
+<tr class="status">
+<td>
+<!--================= start BreadCrumb ==================--><a href="http://www.apache.org/">Apache</a> | <a href="http://poi.apache.org/">POI</a><a href=""></a>
+<!--================= end BreadCrumb ==================--></td><td id="tabs">
+<!--================= start Tabs ==================-->
+<div class="tab">
+<span class="selectedTab"><a class="base-selected" href="../index.html">Home</a></span> | <script language="Javascript" type="text/javascript">
+function printit() {
+if (window.print) {
+ window.print() ;
+} else {
+ var WebBrowser = '<OBJECT ID="WebBrowser1" WIDTH="0" HEIGHT="0" CLASSID="CLSID:8856F961-340A-11D0-A96B-00C04FD705A2"></OBJECT>';
+document.body.insertAdjacentHTML('beforeEnd', WebBrowser);
+ WebBrowser1.ExecWB(6, 2);//Use a 1 vs. a 2 for a prompting dialog box WebBrowser1.outerHTML = "";
+}
+}
+</script><script language="Javascript" type="text/javascript">
+var NS = (navigator.appName == "Netscape");
+var VERSION = parseInt(navigator.appVersion);
+if (VERSION > 3) {
+ document.write(' <a title="PRINT this page OUT" href="javascript:printit()">PRINT</a>');
+}
+</script>
+</div>
+<!--================= end Tabs ==================-->
+</td>
+</tr>
+</tbody>
+</table>
+<!--================= end Status ==================-->
+<table id="main" width="100%" cellpadding="8" cellspacing="0" summary="" border="0">
+<tbody>
+<tr valign="top">
+<!--================= start Menu ==================-->
+<td id="leftcol">
+<div id="navcolumn">
+<div class="menuBar">
+<div class="menu">
+<span class="menuLabel">Apache POI</span>
+
+<div class="menuItem">
+<a href="../index.html">Top</a>
+</div>
+
+</div>
+<div class="menu">
+<span class="menuLabel">HSSF+XSSF</span>
+
+<div class="menuItem">
+<a href="index.html">Overview</a>
+</div>
+
+<div class="menuItem">
+<a href="quick-guide.html">Quick Guide</a>
+</div>
+
+<div class="menuItem">
+<a href="how-to.html">HOWTO</a>
+</div>
+
+<div class="menuItem">
+<a href="converting.html">HSSF to SS Converting</a>
+</div>
+
+<div class="menuItem">
+<a href="formula.html">Formula Support</a>
+</div>
+
+<div class="menuItem">
+<a href="eval.html">Formula Evaluation</a>
+</div>
+
+<div class="menuItem">
+<a href="eval-devguide.html">Eval Dev Guide</a>
+</div>
+
+<div class="menuItem">
+<a href="examples.html">Examples</a>
+</div>
+
+<div class="menuItem">
+<a href="use-case.html">Use Case</a>
+</div>
+
+<div class="menuItem">
+<a href="diagrams.html">Pictorial Docs</a>
+</div>
+
+<div class="menuItem">
+<a href="limitations.html">Limitations</a>
+</div>
+
+<div class="menuItem">
+<a href="user-defined-functions.html">User Defined Functions</a>
+</div>
+
+<div class="menuItem">
+<a href="excelant.html">ExcelAnt Tests</a>
+</div>
+
+</div>
+<div class="menu">
+<span class="menuLabel">Contributer's Guide</span>
+
+<div class="menuItem">
+<a href="hacking-hssf.html">Hacking HSSF</a>
+</div>
+
+<div class="menuItem">
+<a href="record-generator.html">Record Generator</a>
+</div>
+
+<div class="menuItem">
+<a href="chart.html">Charts</a>
+</div>
+
+</div>
+</div>
+</div>
+<form target="_blank" action="http://www.google.com/search" method="get">
+<table summary="search" border="0" cellspacing="0" cellpadding="0">
+<tr>
+<td><img height="1" width="1" alt="" src="../skin/images/spacer.gif" class="spacer"></td><td nowrap="nowrap">
+ Search Apache POI<br>
+<input value="poi.apache.org" name="sitesearch" type="hidden"><input size="10" name="q" id="query" type="text"><img height="1" width="5" alt="" src="../skin/images/spacer.gif" class="spacer"><input name="Search" value="GO" type="submit"></td><td><img height="1" width="1" alt="" src="../skin/images/spacer.gif" class="spacer"></td>
+</tr>
+<tr>
+<td colspan="3"><img height="7" width="1" alt="" src="../skin/images/spacer.gif" class="spacer"></td>
+</tr>
+<tr>
+<td class="bottom-left-thick"></td><td bgcolor="#a5b6c6"><img height="1" width="1" alt="" src="../skin/images/spacer.gif" class="spacer"></td><td class="bottom-right-thick"></td>
+</tr>
+</table>
+</form>
+</td>
+<!--================= end Menu ==================-->
+<!--================= start Content ==================--><td>
+<div id="bodycol">
+<div class="app">
+<div align="center">
+<h1>HSSF</h1>
+</div>
+<div class="h3">
+
+
+
+
+<a name="Usermodel+Class+Diagram+by+Matthew+Young"></a>
+<div class="h3">
+<h3>Usermodel Class Diagram by Matthew Young</h3>
+</div>
+
+
+<p>
+
+<img alt="Usermodel" src="images/usermodel.gif">
+ </p>
+
+
+
+<div id="authors" align="right">by Andrew C. Oliver, Nicola Ken Barozzi</div>
+</div>
+</div>
+</div>
+</td>
+<!--================= end Content ==================-->
+</tr>
+</tbody>
+</table>
+<!--================= end Main ==================-->
+<!--================= start Footer ==================-->
+<div id="footer">
+<table summary="footer" cellspacing="0" cellpadding="4" width="100%" border="0">
+<tbody>
+<tr>
+<!--================= start Copyright ==================-->
+<td colspan="2">
+<div align="center">
+<div class="copyright">
+ Copyright © 2002-2012 The Apache Software Foundation. All rights reserved.<br>
+ Apache POI, POI, Apache, the Apache feather logo, and the Apache
+ POI project logo are trademarks of The Apache Software Foundation.
+ </div>
+</div>
+</td>
+<!--================= end Copyright ==================-->
+</tr>
+<tr>
+<td align="left">
+<!--================= start Host ==================-->
+<!--================= end Host ==================--></td><td align="right">
+<!--================= start Credits ==================-->
+<div align="right">
+<div class="credit"></div>
+</div>
+<!--================= end Credits ==================-->
+</td>
+</tr>
+</tbody>
+</table>
+</div>
+<!--================= end Footer ==================-->
+</body>
+</html>
Propchange: poi/site/publish/spreadsheet/diagram1.html
------------------------------------------------------------------------------
svn:executable = *
Added: poi/site/publish/spreadsheet/diagrams.html
URL: http://svn.apache.org/viewvc/poi/site/publish/spreadsheet/diagrams.html?rev=1423805&view=auto
==============================================================================
--- poi/site/publish/spreadsheet/diagrams.html (added)
+++ poi/site/publish/spreadsheet/diagrams.html Wed Dec 19 09:27:20 2012
@@ -0,0 +1,263 @@
+<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
+<html>
+<head>
+<META http-equiv="Content-Type" content="text/html; charset=UTF-8">
+<!--*** This is a generated file. Do not edit. ***-->
+<link rel="stylesheet" href="../skin/tigris.css" type="text/css">
+<link rel="stylesheet" href="../skin/mysite.css" type="text/css">
+<link rel="stylesheet" href="../skin/site.css" type="text/css">
+<link media="print" rel="stylesheet" href="../skin/print.css" type="text/css">
+<title>HSSF</title>
+</head>
+<body bgcolor="white" class="composite">
+<!--================= start Banner ==================-->
+<div id="banner">
+<table width="100%" cellpadding="8" cellspacing="0" summary="banner" border="0">
+<tbody>
+<tr>
+<!--================= start Group Logo ==================-->
+<td width="50%" align="left">
+<div class="groupLogo">
+<a href="http://poi.apache.org"><img border="0" class="logoImage" alt="Apache POI" src="../resources/images/group-logo.jpg"></a>
+</div>
+</td>
+<!--================= end Group Logo ==================-->
+<!--================= start Project Logo ==================--><td width="50%" align="right">
+<div align="right" class="projectLogo">
+<a href="http://poi.apache.org/"><img border="0" class="logoImage" alt="POI" src="../resources/images/project-logo.jpg"></a>
+</div>
+</td>
+<!--================= end Project Logo ==================-->
+</tr>
+</tbody>
+</table>
+</div>
+<!--================= end Banner ==================-->
+<!--================= start Main ==================-->
+<table width="100%" cellpadding="0" cellspacing="0" border="0" summary="nav" id="breadcrumbs">
+<tbody>
+<!--================= start Status ==================-->
+<tr class="status">
+<td>
+<!--================= start BreadCrumb ==================--><a href="http://www.apache.org/">Apache</a> | <a href="http://poi.apache.org/">POI</a><a href=""></a>
+<!--================= end BreadCrumb ==================--></td><td id="tabs">
+<!--================= start Tabs ==================-->
+<div class="tab">
+<span class="selectedTab"><a class="base-selected" href="../index.html">Home</a></span> | <script language="Javascript" type="text/javascript">
+function printit() {
+if (window.print) {
+ window.print() ;
+} else {
+ var WebBrowser = '<OBJECT ID="WebBrowser1" WIDTH="0" HEIGHT="0" CLASSID="CLSID:8856F961-340A-11D0-A96B-00C04FD705A2"></OBJECT>';
+document.body.insertAdjacentHTML('beforeEnd', WebBrowser);
+ WebBrowser1.ExecWB(6, 2);//Use a 1 vs. a 2 for a prompting dialog box WebBrowser1.outerHTML = "";
+}
+}
+</script><script language="Javascript" type="text/javascript">
+var NS = (navigator.appName == "Netscape");
+var VERSION = parseInt(navigator.appVersion);
+if (VERSION > 3) {
+ document.write(' <a title="PRINT this page OUT" href="javascript:printit()">PRINT</a>');
+}
+</script>
+</div>
+<!--================= end Tabs ==================-->
+</td>
+</tr>
+</tbody>
+</table>
+<!--================= end Status ==================-->
+<table id="main" width="100%" cellpadding="8" cellspacing="0" summary="" border="0">
+<tbody>
+<tr valign="top">
+<!--================= start Menu ==================-->
+<td id="leftcol">
+<div id="navcolumn">
+<div class="menuBar">
+<div class="menu">
+<span class="menuLabel">Apache POI</span>
+
+<div class="menuItem">
+<a href="../index.html">Top</a>
+</div>
+
+</div>
+<div class="menu">
+<span class="menuLabel">HSSF+XSSF</span>
+
+<div class="menuItem">
+<a href="index.html">Overview</a>
+</div>
+
+<div class="menuItem">
+<a href="quick-guide.html">Quick Guide</a>
+</div>
+
+<div class="menuItem">
+<a href="how-to.html">HOWTO</a>
+</div>
+
+<div class="menuItem">
+<a href="converting.html">HSSF to SS Converting</a>
+</div>
+
+<div class="menuItem">
+<a href="formula.html">Formula Support</a>
+</div>
+
+<div class="menuItem">
+<a href="eval.html">Formula Evaluation</a>
+</div>
+
+<div class="menuItem">
+<a href="eval-devguide.html">Eval Dev Guide</a>
+</div>
+
+<div class="menuItem">
+<a href="examples.html">Examples</a>
+</div>
+
+<div class="menuItem">
+<a href="use-case.html">Use Case</a>
+</div>
+
+<div class="menuItem">
+<span class="menuSelected">Pictorial Docs</span>
+</div>
+
+<div class="menuItem">
+<a href="limitations.html">Limitations</a>
+</div>
+
+<div class="menuItem">
+<a href="user-defined-functions.html">User Defined Functions</a>
+</div>
+
+<div class="menuItem">
+<a href="excelant.html">ExcelAnt Tests</a>
+</div>
+
+</div>
+<div class="menu">
+<span class="menuLabel">Contributer's Guide</span>
+
+<div class="menuItem">
+<a href="hacking-hssf.html">Hacking HSSF</a>
+</div>
+
+<div class="menuItem">
+<a href="record-generator.html">Record Generator</a>
+</div>
+
+<div class="menuItem">
+<a href="chart.html">Charts</a>
+</div>
+
+</div>
+</div>
+</div>
+<form target="_blank" action="http://www.google.com/search" method="get">
+<table summary="search" border="0" cellspacing="0" cellpadding="0">
+<tr>
+<td><img height="1" width="1" alt="" src="../skin/images/spacer.gif" class="spacer"></td><td nowrap="nowrap">
+ Search Apache POI<br>
+<input value="poi.apache.org" name="sitesearch" type="hidden"><input size="10" name="q" id="query" type="text"><img height="1" width="5" alt="" src="../skin/images/spacer.gif" class="spacer"><input name="Search" value="GO" type="submit"></td><td><img height="1" width="1" alt="" src="../skin/images/spacer.gif" class="spacer"></td>
+</tr>
+<tr>
+<td colspan="3"><img height="7" width="1" alt="" src="../skin/images/spacer.gif" class="spacer"></td>
+</tr>
+<tr>
+<td class="bottom-left-thick"></td><td bgcolor="#a5b6c6"><img height="1" width="1" alt="" src="../skin/images/spacer.gif" class="spacer"></td><td class="bottom-right-thick"></td>
+</tr>
+</table>
+</form>
+</td>
+<!--================= end Menu ==================-->
+<!--================= start Content ==================--><td>
+<div id="bodycol">
+<div class="app">
+<div align="center">
+<h1>HSSF</h1>
+</div>
+<div class="h3">
+
+
+
+
+<a name="Overview"></a>
+<div class="h3">
+<h3>Overview</h3>
+</div>
+
+<p>
+ This section is intended for diagrams (UML/etc) that help
+ explain HSSF.
+ </p>
+
+<ul>
+
+<li>
+
+<a href="diagram1.html">HSSF usermodel class diagram</a> -
+ by Matthew Young (myoung at westernasset dot com)
+ </li>
+
+</ul>
+
+<p>
+ Have more? Add a new "bug" to the bug database with [DOCUMENTATION]
+ prefacing the description and a link to the file on an http server
+ somewhere. If you don't have your own webserver, then you can email it
+ to (acoliver at apache dot org) provided its < 5MB. Diagrams should be
+ in some format that can be read at least on Linux and Windows. Diagrams
+ that can be edited are preferrable, but lets face it, there aren't too
+ many good affordable UML tools yet! And no they don't HAVE to be UML...
+ just useful.
+ </p>
+
+
+
+<div id="authors" align="right">by Andrew C. Oliver, Nicola Ken Barozzi</div>
+</div>
+</div>
+</div>
+</td>
+<!--================= end Content ==================-->
+</tr>
+</tbody>
+</table>
+<!--================= end Main ==================-->
+<!--================= start Footer ==================-->
+<div id="footer">
+<table summary="footer" cellspacing="0" cellpadding="4" width="100%" border="0">
+<tbody>
+<tr>
+<!--================= start Copyright ==================-->
+<td colspan="2">
+<div align="center">
+<div class="copyright">
+ Copyright © 2002-2012 The Apache Software Foundation. All rights reserved.<br>
+ Apache POI, POI, Apache, the Apache feather logo, and the Apache
+ POI project logo are trademarks of The Apache Software Foundation.
+ </div>
+</div>
+</td>
+<!--================= end Copyright ==================-->
+</tr>
+<tr>
+<td align="left">
+<!--================= start Host ==================-->
+<!--================= end Host ==================--></td><td align="right">
+<!--================= start Credits ==================-->
+<div align="right">
+<div class="credit"></div>
+</div>
+<!--================= end Credits ==================-->
+</td>
+</tr>
+</tbody>
+</table>
+</div>
+<!--================= end Footer ==================-->
+</body>
+</html>
Propchange: poi/site/publish/spreadsheet/diagrams.html
------------------------------------------------------------------------------
svn:executable = *
Added: poi/site/publish/spreadsheet/eval-devguide.html
URL: http://svn.apache.org/viewvc/poi/site/publish/spreadsheet/eval-devguide.html?rev=1423805&view=auto
==============================================================================
--- poi/site/publish/spreadsheet/eval-devguide.html (added)
+++ poi/site/publish/spreadsheet/eval-devguide.html Wed Dec 19 09:27:20 2012
@@ -0,0 +1,656 @@
+<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
+<html>
+<head>
+<META http-equiv="Content-Type" content="text/html; charset=UTF-8">
+<!--*** This is a generated file. Do not edit. ***-->
+<link rel="stylesheet" href="../skin/tigris.css" type="text/css">
+<link rel="stylesheet" href="../skin/mysite.css" type="text/css">
+<link rel="stylesheet" href="../skin/site.css" type="text/css">
+<link media="print" rel="stylesheet" href="../skin/print.css" type="text/css">
+<title>Developing Formula Evaluation</title>
+</head>
+<body bgcolor="white" class="composite">
+<!--================= start Banner ==================-->
+<div id="banner">
+<table width="100%" cellpadding="8" cellspacing="0" summary="banner" border="0">
+<tbody>
+<tr>
+<!--================= start Group Logo ==================-->
+<td width="50%" align="left">
+<div class="groupLogo">
+<a href="http://poi.apache.org"><img border="0" class="logoImage" alt="Apache POI" src="../resources/images/group-logo.jpg"></a>
+</div>
+</td>
+<!--================= end Group Logo ==================-->
+<!--================= start Project Logo ==================--><td width="50%" align="right">
+<div align="right" class="projectLogo">
+<a href="http://poi.apache.org/"><img border="0" class="logoImage" alt="POI" src="../resources/images/project-logo.jpg"></a>
+</div>
+</td>
+<!--================= end Project Logo ==================-->
+</tr>
+</tbody>
+</table>
+</div>
+<!--================= end Banner ==================-->
+<!--================= start Main ==================-->
+<table width="100%" cellpadding="0" cellspacing="0" border="0" summary="nav" id="breadcrumbs">
+<tbody>
+<!--================= start Status ==================-->
+<tr class="status">
+<td>
+<!--================= start BreadCrumb ==================--><a href="http://www.apache.org/">Apache</a> | <a href="http://poi.apache.org/">POI</a><a href=""></a>
+<!--================= end BreadCrumb ==================--></td><td id="tabs">
+<!--================= start Tabs ==================-->
+<div class="tab">
+<span class="selectedTab"><a class="base-selected" href="../index.html">Home</a></span> | <script language="Javascript" type="text/javascript">
+function printit() {
+if (window.print) {
+ window.print() ;
+} else {
+ var WebBrowser = '<OBJECT ID="WebBrowser1" WIDTH="0" HEIGHT="0" CLASSID="CLSID:8856F961-340A-11D0-A96B-00C04FD705A2"></OBJECT>';
+document.body.insertAdjacentHTML('beforeEnd', WebBrowser);
+ WebBrowser1.ExecWB(6, 2);//Use a 1 vs. a 2 for a prompting dialog box WebBrowser1.outerHTML = "";
+}
+}
+</script><script language="Javascript" type="text/javascript">
+var NS = (navigator.appName == "Netscape");
+var VERSION = parseInt(navigator.appVersion);
+if (VERSION > 3) {
+ document.write(' <a title="PRINT this page OUT" href="javascript:printit()">PRINT</a>');
+}
+</script>
+</div>
+<!--================= end Tabs ==================-->
+</td>
+</tr>
+</tbody>
+</table>
+<!--================= end Status ==================-->
+<table id="main" width="100%" cellpadding="8" cellspacing="0" summary="" border="0">
+<tbody>
+<tr valign="top">
+<!--================= start Menu ==================-->
+<td id="leftcol">
+<div id="navcolumn">
+<div class="menuBar">
+<div class="menu">
+<span class="menuLabel">Apache POI</span>
+
+<div class="menuItem">
+<a href="../index.html">Top</a>
+</div>
+
+</div>
+<div class="menu">
+<span class="menuLabel">HSSF+XSSF</span>
+
+<div class="menuItem">
+<a href="index.html">Overview</a>
+</div>
+
+<div class="menuItem">
+<a href="quick-guide.html">Quick Guide</a>
+</div>
+
+<div class="menuItem">
+<a href="how-to.html">HOWTO</a>
+</div>
+
+<div class="menuItem">
+<a href="converting.html">HSSF to SS Converting</a>
+</div>
+
+<div class="menuItem">
+<a href="formula.html">Formula Support</a>
+</div>
+
+<div class="menuItem">
+<a href="eval.html">Formula Evaluation</a>
+</div>
+
+<div class="menuItem">
+<span class="menuSelected">Eval Dev Guide</span>
+</div>
+
+<div class="menuItem">
+<a href="examples.html">Examples</a>
+</div>
+
+<div class="menuItem">
+<a href="use-case.html">Use Case</a>
+</div>
+
+<div class="menuItem">
+<a href="diagrams.html">Pictorial Docs</a>
+</div>
+
+<div class="menuItem">
+<a href="limitations.html">Limitations</a>
+</div>
+
+<div class="menuItem">
+<a href="user-defined-functions.html">User Defined Functions</a>
+</div>
+
+<div class="menuItem">
+<a href="excelant.html">ExcelAnt Tests</a>
+</div>
+
+</div>
+<div class="menu">
+<span class="menuLabel">Contributer's Guide</span>
+
+<div class="menuItem">
+<a href="hacking-hssf.html">Hacking HSSF</a>
+</div>
+
+<div class="menuItem">
+<a href="record-generator.html">Record Generator</a>
+</div>
+
+<div class="menuItem">
+<a href="chart.html">Charts</a>
+</div>
+
+</div>
+</div>
+</div>
+<form target="_blank" action="http://www.google.com/search" method="get">
+<table summary="search" border="0" cellspacing="0" cellpadding="0">
+<tr>
+<td><img height="1" width="1" alt="" src="../skin/images/spacer.gif" class="spacer"></td><td nowrap="nowrap">
+ Search Apache POI<br>
+<input value="poi.apache.org" name="sitesearch" type="hidden"><input size="10" name="q" id="query" type="text"><img height="1" width="5" alt="" src="../skin/images/spacer.gif" class="spacer"><input name="Search" value="GO" type="submit"></td><td><img height="1" width="1" alt="" src="../skin/images/spacer.gif" class="spacer"></td>
+</tr>
+<tr>
+<td colspan="3"><img height="7" width="1" alt="" src="../skin/images/spacer.gif" class="spacer"></td>
+</tr>
+<tr>
+<td class="bottom-left-thick"></td><td bgcolor="#a5b6c6"><img height="1" width="1" alt="" src="../skin/images/spacer.gif" class="spacer"></td><td class="bottom-right-thick"></td>
+</tr>
+</table>
+</form>
+</td>
+<!--================= end Menu ==================-->
+<!--================= start Content ==================--><td>
+<div id="bodycol">
+<div class="app">
+<div align="center">
+<h1>Developing Formula Evaluation</h1>
+</div>
+<div class="h3">
+
+
+
+<a name="Introduction"></a>
+<div class="h3">
+<h3>Introduction</h3>
+</div>
+
+<p>This document is for developers wishing to contribute to the
+ FormulaEvaluator API functionality.</p>
+
+<p>When evaluating workbooks you may encounter a org.apache.poi.ss.formula.eval.NotImplementedException
+ which indicates that a function is not (yet) supported by POI. Is there a workaround?
+ Yes, the POI framework makes it easy to add implementation of new functions. Prior to POI-3.8
+ you had to checkout the source code from svn and make a custom build with your function implementation.
+ Since POI-3.8 you can register new functions in run-time.
+ </p>
+
+<p>Currently, contribution is desired for implementing the standard MS
+ excel functions. Place holder classes for these have been created,
+ contributors only need to insert implementation for the
+ individual "evaluate()" methods that do the actual evaluation.</p>
+
+
+
+<a name="Overview+of+FormulaEvaluator+"></a>
+<div class="h3">
+<h3>Overview of FormulaEvaluator </h3>
+</div>
+
+<p>Briefly, a formula string (along with the sheet and workbook that
+ form the context in which the formula is evaluated) is first parsed
+ into RPN tokens using the FormulaParser class .
+ (If you dont know what RPN tokens are, now is a good time to
+ read <a href="http://www-stone.ch.cam.ac.uk/documentation/rrf/rpn.html">
+ this</a>.)
+ </p>
+
+<a name="+The+big+picture"></a>
+<div class="h4">
+<h4> The big picture</h4>
+</div>
+
+<p>RPN tokens are mapped to Eval classes. (Class hierarchy for the Evals
+ is best understood if you view the class diagram in a class diagram
+ viewer.) Depending on the type of RPN token (also called as Ptgs
+ henceforth since that is what the FormulaParser calls the classes) a
+ specific type of Eval wrapper is constructed to wrap the RPN token and
+ is pushed on the stack.... UNLESS the Ptg is an OperationPtg. If it is an
+ OperationPtg, an OperationEval instance is created for the specific
+ type of OperationPtg. And depending on how many operands it takes,
+ that many Evals are popped of the stack and passed in an array to
+ the OperationEval instance's evaluate method which returns an Eval
+ of subtype ValueEval.Thus an operation in the formula is evaluated. </p>
+
+<div class="frame note">
+<div class="label">Note</div>
+<div class="content"> An Eval is of subinterface ValueEval or OperationEval.
+ Operands are always ValueEvals, Operations are always OperationEvals.</div>
+</div>
+
+<p>
+<span class="codefrag">OperationEval.evaluate(Eval[])</span> returns an Eval which is supposed
+ to be of type ValueEval (actually since ValueEval is an interface,
+ the return value is instance of one of the implementations of
+ ValueEval). The valueEval resulting from evaluate() is pushed on the
+ stack and the next RPN token is evaluated.... this continues till
+ eventually there are no more RPN tokens at which point, if the formula
+ string was correctly parsed, there should be just one Eval on the
+ stack - which contains the result of evaluating the formula.</p>
+
+<p>Of course I glossed over the details of how AreaPtg and ReferencePtg
+ are handled a little differently, but the code should be self
+ explanatory for that. Very briefly, the cells included in AreaPtg and
+ RefPtg are examined and their values are populated in individual
+ ValueEval objects which are set into the AreaEval and RefEval (ok,
+ since AreaEval and RefEval are interfaces, the implementations of
+ AreaEval and RefEval - but you'll figure all that out from the code)</p>
+
+<p>OperationEvals for the standard operators have been implemented and tested.</p>
+
+
+
+
+<a name="What+functions+are+supported%3F"></a>
+<div class="h3">
+<h3>What functions are supported?</h3>
+</div>
+
+<p>
+ As of Feb 2012, POI supports about 140 built-in functions,
+ see <a href="#appendixA">Appendix A</a> for the full list.
+ You can programmatically list supported / unsuported 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>
+
+
+
+<a name="Two+base+interfaces+to+start+your+implementation"></a>
+<div class="h3">
+<h3>Two base interfaces to start your implementation</h3>
+</div>
+
+<p>
+ All Excel formula function classes implement either
+ org.apache.poi.hssf.record.formula.functions.Function or
+ org.apache.poi.hssf.record.formula.functions.FreeRefFunction interface.
+
+ Function is a commonn interface for the functions defined in the binary Excel format (BIFF8): these are "classic" Excel functions like SUM, COUNT, LOOKUP, etc.
+ FreeRefFunction is a common interface for the functions from the Excel Analysis Toolpack and for User-Defined Functions.
+ In the future these two interfaces are expected be unified into one, but for now you have to start your implementation from two slightly different roots.
+
+ </p>
+
+
+
+<a name="Which+interface+to+start+from%3F"></a>
+<div class="h3">
+<h3>Which interface to start from?</h3>
+</div>
+
+<p>
+ 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>
+
+
+
+
+<a name="Walkthrough+of+an+%22evaluate%28%29%22+implementation."></a>
+<div class="h3">
+<h3>Walkthrough of an "evaluate()" implementation.</h3>
+</div>
+
+<p>Here is the fun part: lets walk through the implementation of the excel function <strong>SQRT()</strong>
+
+</p>
+
+<p>
+ AnalysisToolPack.isATPFunction("SQRTPI") returns false so the base interface is Function.
+
+ There are sub-interfaces that make life easier when implementing numeric functions or functions
+ with fixed number of arguments, 1-arg, 2-arg and 3-arg function:
+ </p>
+
+<ul>
+
+<li>org.apache.poi.hssf.record.formula.functions.NumericFunction</li>
+
+<li>org.apache.poi.hssf.record.formula.functions.Fixed1ArgFunction</li>
+
+<li>org.apache.poi.hssf.record.formula.functions.Fixed2ArgFunction</li>
+
+<li>org.apache.poi.hssf.record.formula.functions.Fixed3ArgFunction</li>
+
+<li>org.apache.poi.hssf.record.formula.functions.Fixed4ArgFunction</li>
+
+</ul>
+
+<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>
+
+
+<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>
+
+
+<p>Voila! The formula evaluator now recognizes SQRTPI! </p>
+
+
+
+
+<a name="Floating-point+Arithmetic+in+Excel"></a>
+<div class="h3">
+<h3>Floating-point Arithmetic in Excel</h3>
+</div>
+
+<p>Excel uses the IEEE Standard for Double Precision Floating Point numbers
+ except two cases where it does not adhere to IEEE 754:
+ </p>
+
+<ol>
+
+<li>Positive/Negative Infinities: Infinities occur when you divide by 0.
+ Excel does not support infinities, rather, it gives a #DIV/0! error in these cases.
+ </li>
+
+<li>Not-a-Number (NaN): NaN is used to represent invalid operations
+ (such as infinity/infinity, infinity-infinity, or the square root of -1).
+ NaNs allow a program to continue past an invalid operation.
+ Excel instead immediately generates an error such as #NUM! or #DIV/0!.
+ </li>
+
+</ol>
+
+<p>Be aware of these two cases when saving results of your scientific calculations in Excel:
+ “where are my Infinities and NaNs? They are gone!”
+ </p>
+
+
+
+<a name="Testing+Framework"></a>
+<div class="h3">
+<h3>Testing Framework</h3>
+</div>
+
+<p>Automated testing of the implemented Function is easy.
+ The source code for this is in the file: o.a.p.h.record.formula.GenericFormulaTestCase.java
+ This class has a reference to the test xls file (not /a/ test xls, /the/ test xls :)
+ which may need to be changed for your environment. Once you do that, in the test xls,
+ locate the entry for the function that you have implemented and enter different tests
+ in a cell in the FORMULA row. Then copy the "value of" the formula that you entered in the
+ cell just below it (this is easily done in excel as:
+ [copy the formula cell] > [go to cell below] > Edit > Paste Special > Values > "ok").
+ You can enter multiple such formulas and paste their values in the cell below and the
+ test framework will automatically test if the formula evaluation matches the expected
+ value (Again, hard to put in words, so if you will, please take time to quickly look
+ at the code and the currently entered tests in the patch attachment "FormulaEvalTestData.xls"
+ file).
+ </p>
+
+
+
+<a name="appendixA"></a>
+ <a name="Appendix+A"></a>
+<div class="h3">
+<h3>Appendix A</h3>
+</div>
+
+
+<p>Functions supported by POI ( as of Feb 2012)</p>
+
+<pre class="code">
+ ABS
+ ACOS
+ ACOSH
+ ADDRESS
+ AND
+ ASIN
+ ASINH
+ ATAN
+ ATAN2
+ ATANH
+ AVEDEV
+ AVERAGE
+ CEILING
+ CHAR
+ CHOOSE
+ CLEAN
+ COLUMN
+ COLUMNS
+ COMBIN
+ CONCATENATE
+ COS
+ COSH
+ COUNT
+ COUNTA
+ COUNTBLANK
+ COUNTIF
+ DATE
+ DAY
+ DAYS360
+ DEGREES
+ DEVSQ
+ DOLLAR
+ ERROR.TYPE
+ EVEN
+ EXACT
+ EXP
+ FACT
+ FALSE
+ FIND
+ FLOOR
+ FV
+ HLOOKUP
+ HOUR
+ HYPERLINK
+ IF
+ INDEX
+ INDIRECT
+ INT
+ IRR
+ ISBLANK
+ ISERROR
+ ISEVEN
+ ISLOGICAL
+ ISNA
+ ISNONTEXT
+ ISNUMBER
+ ISODD
+ ISREF
+ ISTEXT
+ LARGE
+ LEFT
+ LEN
+ LN
+ LOG
+ LOG10
+ LOOKUP
+ LOWER
+ MATCH
+ MAX
+ MAXA
+ MEDIAN
+ MID
+ MIN
+ MINA
+ MINUTE
+ MOD
+ MODE
+ MONTH
+ MROUND
+ NA
+ NETWORKDAYS
+ NOT
+ NOW
+ NPER
+ NPV
+ ODD
+ OFFSET
+ OR
+ PI
+ PMT
+ POISSON
+ POWER
+ PRODUCT
+ PV
+ RADIANS
+ RAND
+ RANDBETWEEN
+ RANK
+ RATE
+ REPLACE
+ RIGHT
+ ROUND
+ ROUNDDOWN
+ ROUNDUP
+ ROW
+ ROWS
+ SEARCH
+ SECOND
+ SIGN
+ SIN
+ SINH
+ SMALL
+ SQRT
+ STDEV
+ SUBSTITUTE
+ SUBTOTAL
+ SUM
+ SUMIF
+ SUMIFS
+ SUMPRODUCT
+ SUMSQ
+ SUMX2MY2
+ SUMX2PY2
+ SUMXMY2
+ T
+ TAN
+ TANH
+ TEXT
+ TIME
+ TODAY
+ TRIM
+ TRUE
+ TRUNC
+ UPPER
+ VALUE
+ VAR
+ VARP
+ VLOOKUP
+ WEEKDAY
+ WORKDAY
+ YEAR
+ YEARFRAC
+ </pre>
+
+
+
+<div id="authors" align="right">by Amol Deshmukh, Yegor Kozlov</div>
+</div>
+</div>
+</div>
+</td>
+<!--================= end Content ==================-->
+</tr>
+</tbody>
+</table>
+<!--================= end Main ==================-->
+<!--================= start Footer ==================-->
+<div id="footer">
+<table summary="footer" cellspacing="0" cellpadding="4" width="100%" border="0">
+<tbody>
+<tr>
+<!--================= start Copyright ==================-->
+<td colspan="2">
+<div align="center">
+<div class="copyright">
+ Copyright © 2002-2012 The Apache Software Foundation. All rights reserved.<br>
+ Apache POI, POI, Apache, the Apache feather logo, and the Apache
+ POI project logo are trademarks of The Apache Software Foundation.
+ </div>
+</div>
+</td>
+<!--================= end Copyright ==================-->
+</tr>
+<tr>
+<td align="left">
+<!--================= start Host ==================-->
+<!--================= end Host ==================--></td><td align="right">
+<!--================= start Credits ==================-->
+<div align="right">
+<div class="credit"></div>
+</div>
+<!--================= end Credits ==================-->
+</td>
+</tr>
+</tbody>
+</table>
+</div>
+<!--================= end Footer ==================-->
+</body>
+</html>
Propchange: poi/site/publish/spreadsheet/eval-devguide.html
------------------------------------------------------------------------------
svn:executable = *
Added: poi/site/publish/spreadsheet/eval.html
URL: http://svn.apache.org/viewvc/poi/site/publish/spreadsheet/eval.html?rev=1423805&view=auto
==============================================================================
--- poi/site/publish/spreadsheet/eval.html (added)
+++ poi/site/publish/spreadsheet/eval.html Wed Dec 19 09:27:20 2012
@@ -0,0 +1,622 @@
+<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
+<html>
+<head>
+<META http-equiv="Content-Type" content="text/html; charset=UTF-8">
+<!--*** This is a generated file. Do not edit. ***-->
+<link rel="stylesheet" href="../skin/tigris.css" type="text/css">
+<link rel="stylesheet" href="../skin/mysite.css" type="text/css">
+<link rel="stylesheet" href="../skin/site.css" type="text/css">
+<link media="print" rel="stylesheet" href="../skin/print.css" type="text/css">
+<title>Formula Evaluation</title>
+</head>
+<body bgcolor="white" class="composite">
+<!--================= start Banner ==================-->
+<div id="banner">
+<table width="100%" cellpadding="8" cellspacing="0" summary="banner" border="0">
+<tbody>
+<tr>
+<!--================= start Group Logo ==================-->
+<td width="50%" align="left">
+<div class="groupLogo">
+<a href="http://poi.apache.org"><img border="0" class="logoImage" alt="Apache POI" src="../resources/images/group-logo.jpg"></a>
+</div>
+</td>
+<!--================= end Group Logo ==================-->
+<!--================= start Project Logo ==================--><td width="50%" align="right">
+<div align="right" class="projectLogo">
+<a href="http://poi.apache.org/"><img border="0" class="logoImage" alt="POI" src="../resources/images/project-logo.jpg"></a>
+</div>
+</td>
+<!--================= end Project Logo ==================-->
+</tr>
+</tbody>
+</table>
+</div>
+<!--================= end Banner ==================-->
+<!--================= start Main ==================-->
+<table width="100%" cellpadding="0" cellspacing="0" border="0" summary="nav" id="breadcrumbs">
+<tbody>
+<!--================= start Status ==================-->
+<tr class="status">
+<td>
+<!--================= start BreadCrumb ==================--><a href="http://www.apache.org/">Apache</a> | <a href="http://poi.apache.org/">POI</a><a href=""></a>
+<!--================= end BreadCrumb ==================--></td><td id="tabs">
+<!--================= start Tabs ==================-->
+<div class="tab">
+<span class="selectedTab"><a class="base-selected" href="../index.html">Home</a></span> | <script language="Javascript" type="text/javascript">
+function printit() {
+if (window.print) {
+ window.print() ;
+} else {
+ var WebBrowser = '<OBJECT ID="WebBrowser1" WIDTH="0" HEIGHT="0" CLASSID="CLSID:8856F961-340A-11D0-A96B-00C04FD705A2"></OBJECT>';
+document.body.insertAdjacentHTML('beforeEnd', WebBrowser);
+ WebBrowser1.ExecWB(6, 2);//Use a 1 vs. a 2 for a prompting dialog box WebBrowser1.outerHTML = "";
+}
+}
+</script><script language="Javascript" type="text/javascript">
+var NS = (navigator.appName == "Netscape");
+var VERSION = parseInt(navigator.appVersion);
+if (VERSION > 3) {
+ document.write(' <a title="PRINT this page OUT" href="javascript:printit()">PRINT</a>');
+}
+</script>
+</div>
+<!--================= end Tabs ==================-->
+</td>
+</tr>
+</tbody>
+</table>
+<!--================= end Status ==================-->
+<table id="main" width="100%" cellpadding="8" cellspacing="0" summary="" border="0">
+<tbody>
+<tr valign="top">
+<!--================= start Menu ==================-->
+<td id="leftcol">
+<div id="navcolumn">
+<div class="menuBar">
+<div class="menu">
+<span class="menuLabel">Apache POI</span>
+
+<div class="menuItem">
+<a href="../index.html">Top</a>
+</div>
+
+</div>
+<div class="menu">
+<span class="menuLabel">HSSF+XSSF</span>
+
+<div class="menuItem">
+<a href="index.html">Overview</a>
+</div>
+
+<div class="menuItem">
+<a href="quick-guide.html">Quick Guide</a>
+</div>
+
+<div class="menuItem">
+<a href="how-to.html">HOWTO</a>
+</div>
+
+<div class="menuItem">
+<a href="converting.html">HSSF to SS Converting</a>
+</div>
+
+<div class="menuItem">
+<a href="formula.html">Formula Support</a>
+</div>
+
+<div class="menuItem">
+<span class="menuSelected">Formula Evaluation</span>
+</div>
+
+<div class="menuItem">
+<a href="eval-devguide.html">Eval Dev Guide</a>
+</div>
+
+<div class="menuItem">
+<a href="examples.html">Examples</a>
+</div>
+
+<div class="menuItem">
+<a href="use-case.html">Use Case</a>
+</div>
+
+<div class="menuItem">
+<a href="diagrams.html">Pictorial Docs</a>
+</div>
+
+<div class="menuItem">
+<a href="limitations.html">Limitations</a>
+</div>
+
+<div class="menuItem">
+<a href="user-defined-functions.html">User Defined Functions</a>
+</div>
+
+<div class="menuItem">
+<a href="excelant.html">ExcelAnt Tests</a>
+</div>
+
+</div>
+<div class="menu">
+<span class="menuLabel">Contributer's Guide</span>
+
+<div class="menuItem">
+<a href="hacking-hssf.html">Hacking HSSF</a>
+</div>
+
+<div class="menuItem">
+<a href="record-generator.html">Record Generator</a>
+</div>
+
+<div class="menuItem">
+<a href="chart.html">Charts</a>
+</div>
+
+</div>
+</div>
+</div>
+<form target="_blank" action="http://www.google.com/search" method="get">
+<table summary="search" border="0" cellspacing="0" cellpadding="0">
+<tr>
+<td><img height="1" width="1" alt="" src="../skin/images/spacer.gif" class="spacer"></td><td nowrap="nowrap">
+ Search Apache POI<br>
+<input value="poi.apache.org" name="sitesearch" type="hidden"><input size="10" name="q" id="query" type="text"><img height="1" width="5" alt="" src="../skin/images/spacer.gif" class="spacer"><input name="Search" value="GO" type="submit"></td><td><img height="1" width="1" alt="" src="../skin/images/spacer.gif" class="spacer"></td>
+</tr>
+<tr>
+<td colspan="3"><img height="7" width="1" alt="" src="../skin/images/spacer.gif" class="spacer"></td>
+</tr>
+<tr>
+<td class="bottom-left-thick"></td><td bgcolor="#a5b6c6"><img height="1" width="1" alt="" src="../skin/images/spacer.gif" class="spacer"></td><td class="bottom-right-thick"></td>
+</tr>
+</table>
+</form>
+</td>
+<!--================= end Menu ==================-->
+<!--================= start Content ==================--><td>
+<div id="bodycol">
+<div class="app">
+<div align="center">
+<h1>Formula Evaluation</h1>
+</div>
+<div class="h3">
+
+
+
+<a name="Introduction"></a>
+<div class="h3">
+<h3>Introduction</h3>
+</div>
+
+<p>The POI formula evaluation code enables you to calculate the result of
+ formulas in Excels sheets read-in, or created in POI. This document explains
+ how to use the API to evaluate your formulas.
+ </p>
+
+<div class="frame note">
+<div class="label">Note</div>
+<div class="content">.xlsx format is suported since POI 3.5, make sure yoy upgraded to that version before experimenting with this
+ code. Users of all versions of POI may wish to make use of a recent
+ SVN checkout, as new functions are currently being added fairly frequently.
+ </div>
+</div>
+
+
+
+<a name="Status"></a>
+ <a name="Status-N10024"></a>
+<div class="h3">
+<h3>Status</h3>
+</div>
+
+<p> The code currently provides implementations for all the arithmatic operators.
+ It also provides implementations for approx. 140 built in
+ functions in Excel. The framework however makes it easy to add
+ implementation of new functions. See the <a href="eval-devguide.html"> Formula
+ evaluation development guide</a> and <a href="../apidocs/org/apache/poi/hssf/record/formula/functions/package-summary.html">javadocs</a>
+ for details. </p>
+
+<p> Both HSSFWorkbook and XSSFWorkbook are supported, so you can
+ evaluate formulas on both .xls and .xlsx files.</p>
+
+<p> Note that user-defined functions are not supported, and is not likely to done
+ any time soon... at least, not till there is a VB implementation in Java!
+ </p>
+
+
+<a name="User+API+How-TO"></a>
+<div class="h3">
+<h3>User API How-TO</h3>
+</div>
+
+<p>The following code demonstrates how to use the FormulaEvaluator
+ in the context of other POI excel reading code.
+ </p>
+
+<p>There are several ways in which you can use the FormulaEvalutator API.</p>
+
+
+<a name="Evaluate"></a>
+ <a name="Using+FormulaEvaluator."></a>
+<div class="h4">
+<h4>Using FormulaEvaluator.evaluate(Cell cell)</h4>
+</div>
+
+<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>
+
+<p>Thus using the retrieved value (of type
+ FormulaEvaluator.CellValue - a nested class) returned
+ by FormulaEvaluator is similar to using a Cell object
+ containing the value of the formula evaluation. CellValue is
+ a simple value object and does not maintain reference
+ to the original cell.
+ </p>
+
+
+
+<a name="EvaluateFormulaCell"></a>
+ <a name="Using+FormulaEvaluator.-N1005F"></a>
+<div class="h4">
+<h4>Using FormulaEvaluator.evaluateFormulaCell(Cell cell)</h4>
+</div>
+
+<p>
+<strong>evaluateFormulaCell</strong>(Cell cell)
+ will check to see if the supplied cell is a formula cell.
+ If it isn't, then no changes will be made to it. If it is,
+ then the formula is evaluated. The value for the formula
+ is saved alongside it, to be displayed in excel. The
+ 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"></a>
+ <a name="Using+FormulaEvaluator.-N10077"></a>
+<div class="h4">
+<h4>Using FormulaEvaluator.evaluateInCell(Cell cell)</h4>
+</div>
+
+<p>
+<strong>evaluateInCell</strong>(Cell cell) will check to
+ see if the supplied cell is a formula cell. If it isn't,
+ 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>
+
+
+
+<a name="EvaluateAll"></a>
+ <a name="Re-calculating+all+formulas+in+a+Workbook"></a>
+<div class="h4">
+<h4>Re-calculating all formulas in a Workbook</h4>
+</div>
+
+<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(int sheetNum = 0; sheetNum < wb.getNumberOfSheets(); sheetNum++) {
+ Sheet sheet = wb.getSheetAt(sheetNum);
+ for(Row r : sheet) {
+ for(Cell c : r) {
+ if(c.getCellType() == Cell.CELL_TYPE_FORMULA) {
+ evaluator.evaluateFormulaCell(c);
+ }
+ }
+ }
+}
+ </pre>
+
+
+<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
+ HSSFFormulaEvaluator or XSSFFormulaEvaluator class.</p>
+
+
+
+
+<a name="recalculation"></a>
+ <a name="Recalculation+of+Formulas"></a>
+<div class="h3">
+<h3>Recalculation of Formulas</h3>
+</div>
+
+<p>
+ In certain cases you may want to force Excel to re-calculate formulas when the workbook is opened.
+ Consider the following example:
+ </p>
+
+<p>
+ Open Excel and create a new workbook. On the first sheet set A1=1, B1=1, C1=A1+B1.
+ Excel automatically calculates formulas and the value in C1 is 2. So far so good.
+ </p>
+
+<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>
+
+<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.
+ It is not an issue when you are creating new workbooks from scratch, but important to remember when you are modifing
+ existing workbooks with formulas. This can be done in two ways:
+ </p>
+
+<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>
+
+<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>
+
+
+
+<a name="Performance"></a>
+ <a name="Performance+Notes"></a>
+<div class="h3">
+<h3>Performance Notes</h3>
+</div>
+
+<ul>
+
+<li>Generally you should have to create only one FormulaEvaluator
+ instance per Workbook. The FormulaEvaluator will cache
+ evaluations of dependent cells, so if you have multiple
+ formulas all depending on a cell then subsequent evaluations
+ will be faster.
+ </li>
+
+<li>You should normally perform all of your updates to cells,
+ before triggering the evaluation, rather than doing one
+ cell at a time. By waiting until all the updates/sets are
+ performed, you'll be able to take best advantage of the caching
+ for complex formulas.
+ </li>
+
+<li>If you do end up making changes to cells part way through
+ evaluation, you should call <em>notifySetFormula</em> or
+ <em>notifyUpdateCell</em> to trigger suitable cache clearance.
+ Alternately, you could instantiate a new FormulaEvaluator,
+ which will start with empty caches.
+ </li>
+
+<li>Also note that FormulaEvaluator maintains a reference to
+ the sheet and workbook, so ensure that the evaluator instance
+ is available for garbage collection when you are done with it
+ (in other words don't maintain long lived reference to
+ FormulaEvaluator if you don't really need to - unless
+ all references to the sheet and workbook are removed, these
+ don't get garbage collected and continue to occupy potentially
+ large amounts of memory).
+ </li>
+
+<li>CellValue instances however do not maintain reference to the
+ Cell or the sheet or workbook, so these can be long-lived
+ objects without any adverse effect on performance.
+ </li>
+
+</ul>
+
+
+<a name="Formula+Evaluation+Debugging"></a>
+<div class="h3">
+<h3>Formula Evaluation Debugging</h3>
+</div>
+
+<p>POI is not perfect and you may stumble across formula evaluation problems (Java exceptions
+ or just different results) in your special use case. To support an easy detailed analysis, a special
+ logging of the full evaluation is provided.</p>
+
+<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>
+
+<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 beeing disturbed by the bunch of DEBUG log entries from other classes.</p>
+
+
+
+<div id="authors" align="right">by Amol Deshmukh</div>
+</div>
+</div>
+</div>
+</td>
+<!--================= end Content ==================-->
+</tr>
+</tbody>
+</table>
+<!--================= end Main ==================-->
+<!--================= start Footer ==================-->
+<div id="footer">
+<table summary="footer" cellspacing="0" cellpadding="4" width="100%" border="0">
+<tbody>
+<tr>
+<!--================= start Copyright ==================-->
+<td colspan="2">
+<div align="center">
+<div class="copyright">
+ Copyright © 2002-2012 The Apache Software Foundation. All rights reserved.<br>
+ Apache POI, POI, Apache, the Apache feather logo, and the Apache
+ POI project logo are trademarks of The Apache Software Foundation.
+ </div>
+</div>
+</td>
+<!--================= end Copyright ==================-->
+</tr>
+<tr>
+<td align="left">
+<!--================= start Host ==================-->
+<!--================= end Host ==================--></td><td align="right">
+<!--================= start Credits ==================-->
+<div align="right">
+<div class="credit"></div>
+</div>
+<!--================= end Credits ==================-->
+</td>
+</tr>
+</tbody>
+</table>
+</div>
+<!--================= end Footer ==================-->
+</body>
+</html>
Propchange: poi/site/publish/spreadsheet/eval.html
------------------------------------------------------------------------------
svn:executable = *
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@poi.apache.org
For additional commands, e-mail: commits-help@poi.apache.org