Modified: poi/site/publish/components/spreadsheet/eval.html URL: http://svn.apache.org/viewvc/poi/site/publish/components/spreadsheet/eval.html?rev=1836244&r1=1836243&r2=1836244&view=diff ============================================================================== --- poi/site/publish/components/spreadsheet/eval.html (original) +++ poi/site/publish/components/spreadsheet/eval.html Wed Jul 18 23:27:21 2018 @@ -1,218 +1,309 @@ -<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd";> +<!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"> +<meta content="Apache Forrest" name="Generator"> +<meta name="Forrest-version" content="0.9"> +<meta name="Forrest-skin-name" content="pelt"> <title>Formula Evaluation</title> +<link type="text/css" href="../../skin/basic.css" rel="stylesheet"> +<link media="screen" type="text/css" href="../../skin/screen.css" rel="stylesheet"> +<link media="print" type="text/css" href="../../skin/print.css" rel="stylesheet"> +<link type="text/css" href="../../skin/profile.css" rel="stylesheet"> +<script src="../../skin/getBlank.js" language="javascript" type="text/javascript"></script><script src="../../skin/getMenu.js" language="javascript" type="text/javascript"></script><script src="../../skin/fontsize.js" language="javascript" type="text/javascript"></script> +<link rel="shortcut icon" href="../../images/favicon.ico"> </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="https://www.apache.org";><img border="0" class="logoImage" alt="The Apache Software Foundation" src="../resources/images/asf_logo.png"></a> -</div> -</td> -<!--================= end Group Logo ==================--> -<!--================= start Project Logo ==================--><td width="50%" align="right"> -<div align="right" class="projectLogo"> -<a href="https://donate.apache.org/";><img border="0" class="logoImage" alt="Support Apache" src="../resources/images/support-asf.png" width="120" height="120" title="Your donation helps The Apache Software Foundation ensure its community-driven software remains available to billions of users at absolutely no cost."></a><a href="https://poi.apache.org/";><img border="0" class="logoImage" alt="Apache POI" src="../resources/images/pb-poi.png" width="120" height="120" title="Apache POI - the Java API for Microsoft Documents"></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="https://www.apache.org/";>Apache</a> | <a href="https://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> +<body onload="init()"> +<script type="text/javascript">ndeSetTextSize();</script> +<div id="top"> +<!--+ + |breadtrail + +--> +<div class="breadtrail"> +<a href="https://www.apache.org";>Apache Software Foundation</a> > <a href="https://poi.apache.org";>Apache POI</a><script src="../../skin/breadcrumbs.js" language="JavaScript" type="text/javascript"></script> +</div> +<!--+ + |header + +--> +<div class="header"> +<!--+ + |start group logo + +--> +<div class="grouplogo"> +<a href="https://www.apache.org";><img class="logoImage" alt="Apache Software Foundation" src="../../images/group-logo.png" title="The Apache Software Foundation is a cornerstone of the modern Open Source software ecosystem – supporting some of the most widely used and important software solutions powering today's Internet economy."></a> +</div> +<!--+ + |end group logo + +--> +<!--+ + |start Project Logo + +--> +<div class="projectlogo"> +<a href="https://poi.apache.org";><img class="logoImage" alt="Apache POI" src="../../images/project-logo.png" title="Apache POI is well-known in the Java field as a library for reading and writing Microsoft Office file formats, such as Excel, PowerPoint, Word, Visio, Publisher and Outlook. It supports both the older (OLE2) and new (OOXML - Office Open XML) formats."></a> +</div> +<!--+ + |end Project Logo + +--> +<!--+ + |start Search + +--> +<div class="searchbox"> +<form action="http://www.google.com/search"; method="get" class="roundtopsmall"> +<input value="poi.apache.org" name="sitesearch" type="hidden"><input onFocus="getBlank (this, 'Search the site with google');" size="25" name="q" id="query" type="text" value="Search the site with google"> + <input name="Search" value="Search" type="submit"> +</form> </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> +<!--+ + |end search + +--> +<!--+ + |start Tabs + +--> +<ul id="tabs"> +<li> +<a class="unselected" href="../../index.html">Home</a> +</li> +<li> +<a class="unselected" href="../../help/index.html">Help</a> +</li> +<li class="current"> +<a class="selected" href="../../components/index.html">Component APIs</a> +</li> +<li> +<a class="unselected" href="../../devel/index.html">Getting Involved</a> +</li> +</ul> +<!--+ + |end Tabs + +--> </div> - </div> -<div class="menu"> -<span class="menuLabel">HSSF+XSSF</span> - -<div class="menuItem"> -<a href="index.html">Overview</a> +<div id="main"> +<div id="publishedStrip"> +<!--+ + |start Subtabs + +--> +<div id="level2tabs"></div> +<!--+ + |end Endtabs + +--> +<script type="text/javascript"><!-- +document.write("Last Published: " + document.lastModified); +// --></script> </div> - -<div class="menuItem"> -<a href="quick-guide.html">Quick Guide</a> +<!--+ + |breadtrail + +--> +<div class="breadtrail"> + + + </div> +<!--+ + |start Menu, mainarea + +--> +<!--+ + |start Menu + +--> +<div id="menu"> +<div onclick="SwitchMenu('menu_selected_1.1', '../../skin/')" id="menu_selected_1.1Title" class="menutitle" style="background-image: url('../../skin/images/chapter_open.gif');">Component APIs</div> +<div id="menu_selected_1.1" class="selectedmenuitemgroup" style="display: block;"> +<div class="menuitem"> +<a href="../../components/index.html">Overview</a> </div> - -<div class="menuItem"> -<a href="how-to.html">HOWTO</a> +<div class="menuitem"> +<a href="../../apidocs/index.html">Javadocs</a> </div> - -<div class="menuItem"> -<a href="converting.html">HSSF to SS Converting</a> +<div onclick="SwitchMenu('menu_selected_1.1.3', '../../skin/')" id="menu_selected_1.1.3Title" class="menutitle" style="background-image: url('../../skin/images/chapter_open.gif');">Excel (HSSF/XSSF)</div> +<div id="menu_selected_1.1.3" class="selectedmenuitemgroup" style="display: block;"> +<div class="menuitem"> +<a href="../../components/spreadsheet/index.html">Overview</a> </div> - -<div class="menuItem"> -<a href="formula.html">Formula Support</a> +<div class="menuitem"> +<a href="../../components/spreadsheet/quick-guide.html">Quick Guide</a> </div> - -<div class="menuItem"> -<span class="menuSelected">Formula Evaluation</span> +<div class="menuitem"> +<a href="../../components/spreadsheet/how-to.html">HOWTO</a> </div> - -<div class="menuItem"> -<a href="eval-devguide.html">Eval Dev Guide</a> +<div class="menuitem"> +<a href="../../components/spreadsheet/converting.html">HSSF to SS Converting</a> </div> - -<div class="menuItem"> -<a href="examples.html">Examples</a> +<div class="menuitem"> +<a href="../../components/spreadsheet/formula.html">Formula Support</a> </div> - -<div class="menuItem"> -<a href="use-case.html">Use Case</a> +<div class="menupage"> +<div class="menupagetitle">Formula Evaluation</div> </div> - -<div class="menuItem"> -<a href="diagrams.html">Pictorial Docs</a> +<div class="menuitem"> +<a href="../../components/spreadsheet/eval-devguide.html">Eval Dev Guide</a> </div> - -<div class="menuItem"> -<a href="limitations.html">Limitations</a> +<div class="menuitem"> +<a href="../../components/spreadsheet/examples.html">Examples</a> </div> - -<div class="menuItem"> -<a href="user-defined-functions.html">User Defined Functions</a> +<div class="menuitem"> +<a href="../../components/spreadsheet/use-case.html">Use Case</a> </div> - -<div class="menuItem"> -<a href="excelant.html">ExcelAnt Tests</a> +<div class="menuitem"> +<a href="../../components/spreadsheet/diagrams.html">Pictorial Docs</a> </div> - +<div class="menuitem"> +<a href="../../components/spreadsheet/limitations.html">Limitations</a> </div> -<div class="menu"> -<span class="menuLabel">Contributer's Guide</span> - -<div class="menuItem"> -<a href="hacking-hssf.html">Hacking HSSF</a> +<div class="menuitem"> +<a href="../../components/spreadsheet/user-defined-functions.html">User Defined Functions</a> </div> - -<div class="menuItem"> -<a href="record-generator.html">Record Generator</a> +<div class="menuitem"> +<a href="../../components/spreadsheet/excelant.html">ExcelAnt Tests</a> </div> - -<div class="menuItem"> -<a href="chart.html">Charts</a> +<div class="menuitem"> +<a href="../../components/spreadsheet/hacking-hssf.html">Hacking HSSF</a> </div> - +<div class="menuitem"> +<a href="../../components/spreadsheet/record-generator.html">Record Generator</a> </div> -<center> -<a href="https://www.apache.org/events/current-event.html";><img src="https://www.apache.org/events/current-event-125x125.png";></a> -</center> -</div> -</div> -<form target="_blank" action="https://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 class="menuitem"> +<a href="../../components/spreadsheet/chart.html">Charts</a> </div> -<div class="h3"> - - +</div> +<div onclick="SwitchMenu('menu_1.1.4', '../../skin/')" id="menu_1.1.4Title" class="menutitle">PowerPoint (HSLF/XSLF)</div> +<div id="menu_1.1.4" class="menuitemgroup"> +<div class="menuitem"> +<a href="../../components/slideshow/index.html">Overview</a> +</div> +<div class="menuitem"> +<a href="../../components/slideshow/quick-guide.html">Quick Guide</a> +</div> +<div class="menuitem"> +<a href="../../components/slideshow/how-to-shapes.html">HSLF Cookbook</a> +</div> +<div class="menuitem"> +<a href="../../components/slideshow/xslf-cookbook.html">XSLF Cookbook</a> +</div> +<div class="menuitem"> +<a href="../../components/slideshow/ppt-file-format.html">PPT File Format</a> +</div> +</div> +<div onclick="SwitchMenu('menu_1.1.5', '../../skin/')" id="menu_1.1.5Title" class="menutitle">Word (HWPF/XWPF)</div> +<div id="menu_1.1.5" class="menuitemgroup"> +<div class="menuitem"> +<a href="../../components/document/index.html">Overview</a> +</div> +<div class="menuitem"> +<a href="../../components/document/quick-guide.html">HWPF Quick Guide</a> +</div> +<div class="menuitem"> +<a href="../../components/document/quick-guide-xwpf.html">XWPF Quick Guide</a> +</div> +<div class="menuitem"> +<a href="../../components/document/docoverview.html">HWPF Format</a> +</div> +<div class="menuitem"> +<a href="../../components/document/projectplan.html">HWPF Project plan</a> +</div> +</div> +<div class="menuitem"> +<a href="../../components/hsmf/index.html">Outlook (HSMF)</a> +</div> +<div class="menuitem"> +<a href="../../components/diagram/index.html">Visio (HDGF+XDGF)</a> +</div> +<div onclick="SwitchMenu('menu_1.1.8', '../../skin/')" id="menu_1.1.8Title" class="menutitle">Publisher (HPBF)</div> +<div id="menu_1.1.8" class="menuitemgroup"> +<div class="menuitem"> +<a href="../../components/hpbf/index.html">Overview</a> +</div> +<div class="menuitem"> +<a href="../../components/hpbf/file-format.html">File Format</a> +</div> +</div> +<div onclick="SwitchMenu('menu_1.1.9', '../../skin/')" id="menu_1.1.9Title" class="menutitle">OLE2 Filesystem (POIFS)</div> +<div id="menu_1.1.9" class="menuitemgroup"> +<div class="menuitem"> +<a href="../../components/poifs/index.html">Overview</a> +</div> +<div class="menuitem"> +<a href="../../components/poifs/how-to.html">How To</a> +</div> +<div class="menuitem"> +<a href="../../components/poifs/embeded.html">Embedded Documents</a> +</div> +<div class="menuitem"> +<a href="../../components/poifs/fileformat.html">File System Documentation</a> +</div> +<div class="menuitem"> +<a href="../../components/poifs/usecases.html">Use Cases</a> +</div> +<div class="menuitem"> +<a href="../../components/poifs/design.html">Design</a> +</div> +</div> +<div onclick="SwitchMenu('menu_1.1.10', '../../skin/')" id="menu_1.1.10Title" class="menutitle">OLE2 Document Props (HPSF)</div> +<div id="menu_1.1.10" class="menuitemgroup"> +<div class="menuitem"> +<a href="../../components/hpsf/index.html">Overview</a> +</div> +<div class="menuitem"> +<a href="../../components/hpsf/how-to.html">How To</a> +</div> +<div class="menuitem"> +<a href="../../components/hpsf/thumbnails.html">Thumbnails</a> +</div> +<div class="menuitem"> +<a href="../../components/hpsf/internals.html">Internals</a> +</div> +<div class="menuitem"> +<a href="../../components/hpsf/todo.html">To Do</a> +</div> +</div> +<div class="menuitem"> +<a href="../../components/hmef/index.html">TNEF (HMEF) for winmail.dat</a> +</div> +<div class="menuitem"> +<a href="../../components/oxml4j/index.html">OpenXML4J (OOXML)</a> +</div> +<div class="menuitem"> +<a href="../../components/logging.html">Logging framework</a> +</div> +</div> +<div id="credit"></div> +<div id="roundbottom"> +<img style="display: none" class="corner" height="15" width="15" alt="" src="../../skin/images/rc-b-l-15-1body-2menu-3menu.png"></div> +<!--+ + |alternative credits + +--> +<div id="credit2"> +<a href="https://donate.apache.org/";><img border="0" title="Support Apache" alt="Support Apache - logo" src="../../images/support-asf.png" style="width: 125px;height: 125px;"></a> +</div> +</div> +<!--+ + |end Menu + +--> +<!--+ + |start content + +--> +<div id="content"> +<h1>Formula Evaluation</h1> +<div id="front-matter"></div> <a name="Introduction"></a> -<div class="h3"> -<h3>Introduction<a title="Permanent link" class="headerlink" href="#Introduction">#</a> -</h3> -</div> - +<h2 class="boxed">Introduction</h2> +<div class="section"> <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> -<a name="WhyEvaluate"></a> +<a name="WhyEvaluate" id="WhyEvaluate"></a> <a name="Why+do+I+need+to+evaluate+formulas%3F"></a> -<div class="h3"> -<h3>Why do I need to evaluate formulas?<a title="Permanent link" class="headerlink" href="#Why+do+I+need+to+evaluate+formulas%3F">#</a> -</h3> -</div> - +<h2 class="boxed">Why do I need to evaluate formulas?</h2> +<div class="section"> <p>The Excel file format (both .xls and .xlsx) stores a "cached" result for every formula along with the formula itself. This means that when the file is opened, it can be quickly displayed, without needing to spend a long time calculating all of the formula results. It also means that when reading a file through Apache POI, the result is quickly available to you too! </p> - <p>After making changes with Apache POI to either Formula Cells themselves, or those that they depend on, you should normally perform a Formula Evaluation to have these "cached" results updated. This is normally done @@ -224,54 +315,37 @@ if (VERSION > 3) { using volatile functions it's generally best to trigger a <a href="#recalculation">Recalulation</a> through POI) </p> - +</div> -<a name="Status"></a> - <a name="Status-N1002C"></a> -<div class="h3"> -<h3>Status<a title="Permanent link" class="headerlink" href="#Status-N1002C">#</a> -</h3> -</div> - +<a name="Status" id="Status"></a> + <a name="Status-N1002D"></a> +<h2 class="boxed">Status</h2> +<div class="section"> <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> + 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> User-defined functions are <a href="user-defined-functions.html">supported</a>, but must be rewritten in Java and registered with the macro-enabled workbook in order to be evaluated. </p> - +</div> <a name="User+API+How-TO"></a> -<div class="h3"> -<h3>User API How-TO<a title="Permanent link" class="headerlink" href="#User+API+How-TO">#</a> -</h3> -</div> - +<h2 class="boxed">User API How-TO</h2> +<div class="section"> <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)<a title="Permanent link" class="headerlink" href="#Using+FormulaEvaluator.">#</a> -</h4> -</div> - +<a name="Evaluate" id="Evaluate"></a><a name="Using+FormulaEvaluator."></a> +<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") @@ -305,7 +379,6 @@ switch (cellValue.getCellType()) { 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 @@ -313,16 +386,8 @@ switch (cellValue.getCellType()) { a simple value object and does not maintain reference to the original cell. </p> - - - -<a name="EvaluateFormulaCell"></a> - <a name="Using+FormulaEvaluator.-N1006B"></a> -<div class="h4"> -<h4>Using FormulaEvaluator.evaluateFormulaCell(Cell cell)<a title="Permanent link" class="headerlink" href="#Using+FormulaEvaluator.-N1006B">#</a> -</h4> -</div> - +<a name="EvaluateFormulaCell" id="EvaluateFormulaCell"></a><a name="Using+FormulaEvaluator.-N1006C"></a> +<h3 class="boxed">Using FormulaEvaluator.evaluateFormulaCell(Cell cell)</h3> <p> <strong>evaluateFormulaCell</strong>(Cell cell) will check to see if the supplied cell is a formula cell. @@ -330,10 +395,8 @@ switch (cellValue.getCellType()) { 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") @@ -368,23 +431,14 @@ if (cell!=null) { } } </pre> - - - -<a name="EvaluateInCell"></a> - <a name="Using+FormulaEvaluator.-N10083"></a> -<div class="h4"> -<h4>Using FormulaEvaluator.evaluateInCell(Cell cell)<a title="Permanent link" class="headerlink" href="#Using+FormulaEvaluator.-N10083">#</a> -</h4> -</div> - +<a name="EvaluateInCell" id="EvaluateInCell"></a><a name="Using+FormulaEvaluator.-N10084"></a> +<h3 class="boxed">Using FormulaEvaluator.evaluateInCell(Cell cell)</h3> <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") @@ -420,16 +474,8 @@ if (cell!=null) { } </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<a title="Permanent link" class="headerlink" href="#Re-calculating+all+formulas+in+a+Workbook">#</a> -</h4> -</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") @@ -444,37 +490,28 @@ for (Sheet sheet : wb) { } } </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> - - +</div> -<a name="recalculation"></a> +<a name="recalculation" id="recalculation"></a> <a name="Recalculation+of+Formulas"></a> -<div class="h3"> -<h3>Recalculation of Formulas<a title="Permanent link" class="headerlink" href="#Recalculation+of+Formulas">#</a> -</h3> -</div> - +<h2 class="boxed">Recalculation of Formulas</h2> +<div class="section"> <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> <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> <p> Now modify the workbook with POI: </p> - <pre class="code"> Workbook wb = WorkbookFactory.create(new FileInputStream("workbook.xls")); @@ -484,19 +521,16 @@ for (Sheet sheet : wb) { FileOutputStream out = new FileOutputStream("workbook2.xls"); wb.write(out); out.close(); - </pre> - + </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> <p> 1. Re-evaluate formulas with POI's FormulaEvaluator: - </p> - + </p> <pre class="code"> Workbook wb = WorkbookFactory.create(new FileInputStream("workbook.xls")); @@ -504,12 +538,10 @@ for (Sheet sheet : wb) { sh.getRow(0).getCell(0).setCellValue(2); // set A1=2 wb.getCreationHelper().createFormulaEvaluator().evaluateAll(); - </pre> - + </pre> <p> 2. Delegate re-calculation to Excel. The application will perform a full recalculation when the workbook is opened: - </p> - + </p> <pre class="code"> Workbook wb = WorkbookFactory.create(new FileInputStream("workbook.xls")); @@ -517,42 +549,35 @@ for (Sheet sheet : wb) { sh.getRow(0).getCell(0).setCellValue(2); // set A1=2 wb.setForceFormulaRecalculation(true); - </pre> - + </pre> +</div> -<a name="external"></a> +<a name="external" id="external"></a> <a name="External+%28Cross-Workbook%29+references"></a> -<div class="h3"> -<h3>External (Cross-Workbook) references<a title="Permanent link" class="headerlink" href="#External+%28Cross-Workbook%29+references">#</a> -</h3> -</div> - +<h2 class="boxed">External (Cross-Workbook) references</h2> +<div class="section"> <p>It is possible for a formula in an Excel spreadsheet to 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> - <p>If you don't have access to these other workbooks, then you should call - <a href="../apidocs/org/apache/poi/ss/usermodel/FormulaEvaluator.html#setIgnoreMissingWorkbooks(boolean)">setIgnoreMissingWorkbooks(true)</a> + <a href="../../apidocs/org/apache/poi/ss/usermodel/FormulaEvaluator.html#setIgnoreMissingWorkbooks(boolean)">setIgnoreMissingWorkbooks(true)</a> to tell the Formula Evaluator to skip evaluating any external references it can't look up.</p> - <p>In order for POI to be able to evaluate external references, it needs access to the workbooks in question. As these don't necessarily have the same names on your system as in the workbook, you need to give POI a map of external references to open workbooks, through the - <a href="../apidocs/org/apache/poi/ss/usermodel/FormulaEvaluator.html#setupReferencedWorkbooks(java.util.Map)">setupReferencedWorkbooks(java.util.Map<java.lang.String,FormulaEvaluator> workbooks)</a> + <a href="../../apidocs/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(); @@ -571,16 +596,13 @@ mainWorkbookEvaluator.setupReferencedWor // Evaluate mainWorkbookEvaluator.evaluateAll(); </pre> - +</div> -<a name="Performance"></a> +<a name="Performance" id="Performance"></a> <a name="Performance+Notes"></a> -<div class="h3"> -<h3>Performance Notes<a title="Permanent link" class="headerlink" href="#Performance+Notes">#</a> -</h3> -</div> - +<h2 class="boxed">Performance Notes</h2> +<div class="section"> <ul> <li>Generally you should have to create only one FormulaEvaluator @@ -620,23 +642,17 @@ mainWorkbookEvaluator.evaluateAll(); </li> </ul> - +</div> <a name="Formula+Evaluation+Debugging"></a> -<div class="h3"> -<h3>Formula Evaluation Debugging<a title="Permanent link" class="headerlink" href="#Formula+Evaluation+Debugging">#</a> -</h3> -</div> - +<h2 class="boxed">Formula Evaluation Debugging</h2> +<div class="section"> <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"); @@ -654,80 +670,58 @@ mainWorkbookEvaluator.evaluateAll(); 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 being disturbed by the bunch of DEBUG log entries from other classes.</p> - +</div> -<a name="sxssf"></a> +<a name="sxssf" id="sxssf"></a> <a name="Formula+Evaluation+and+SXSSF"></a> -<div class="h3"> -<h3>Formula Evaluation and SXSSF<a title="Permanent link" class="headerlink" href="#Formula+Evaluation+and+SXSSF">#</a> -</h3> -</div> - +<h2 class="boxed">Formula Evaluation and SXSSF</h2> +<div class="section"> <p>For versions before 3.13 final, no formula evaluation is possible with SXSSF.</p> - <p>If you are using POI 3.13 final or newer, formula evaluation is possible with SXSSF, but with some caveats.</p> - <p>The biggest restriction is that, since evaluating a cell needs that cell in memory and any others it depends on, only pure-function formulas and formulas referencing nearby cells can be evaluated with SXSSF. If a formula references a cell that hasn't yet been written, or one which has already been flushed to disk, then it won't be possible to evaluate it.</p> - <p>Because of this, a call to <em>wb.getCreationHelper().createFormulaEvaluator().evaluateAll();</em> will very rarely work on SXSSF, as it's very rare that all the cells wil be available and in memory at any time! Instead, it is suggested to evaluate formula cells just after writing them, or shortly after when cells they depend on are added. Just make sure that all cells needing or needed for evaluation are inside the window.</p> - - - </div> + </div> +<!--+ + |end content + +--> +<div class="clearboth"> </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"> +<!--+ + |start bottomstrip + +--> +<div class="lastmodified"> +<script type="text/javascript"><!-- +document.write("Last Published: " + document.lastModified); +// --></script> +</div> <div class="copyright"> - Copyright © 2002-2018 The Apache Software Foundation. All rights reserved.<br> - Apache, Apache POI, and the Apache POI logo are registered trademarks or - trademarks of The Apache Software Foundation in the U.S. and/or other countries. - </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> + Copyright © + 2001-2018 <a href="https://www.apache.org/";>The Apache Software Foundation</a> +</div> +<div id="feedback"> + Send feedback about the website to: + <a id="feedbackto" href="mailto:[email protected]?subject=Feedback%C2%A0components/spreadsheet/eval.html";>[email protected]</a> +</div> +<!--+ + |end bottomstrip + +--> </div> -<!--================= end Footer ==================--> </body> </html>
Modified: poi/site/publish/components/spreadsheet/examples.html URL: http://svn.apache.org/viewvc/poi/site/publish/components/spreadsheet/examples.html?rev=1836244&r1=1836243&r2=1836244&view=diff ============================================================================== --- poi/site/publish/components/spreadsheet/examples.html (original) +++ poi/site/publish/components/spreadsheet/examples.html Wed Jul 18 23:27:21 2018 @@ -1,229 +1,314 @@ -<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd";> +<!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"> +<meta content="Apache Forrest" name="Generator"> +<meta name="Forrest-version" content="0.9"> +<meta name="Forrest-skin-name" content="pelt"> <title>HSSF and XSSF Examples</title> +<link type="text/css" href="../../skin/basic.css" rel="stylesheet"> +<link media="screen" type="text/css" href="../../skin/screen.css" rel="stylesheet"> +<link media="print" type="text/css" href="../../skin/print.css" rel="stylesheet"> +<link type="text/css" href="../../skin/profile.css" rel="stylesheet"> +<script src="../../skin/getBlank.js" language="javascript" type="text/javascript"></script><script src="../../skin/getMenu.js" language="javascript" type="text/javascript"></script><script src="../../skin/fontsize.js" language="javascript" type="text/javascript"></script> +<link rel="shortcut icon" href="../../images/favicon.ico"> </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="https://www.apache.org";><img border="0" class="logoImage" alt="The Apache Software Foundation" src="../resources/images/asf_logo.png"></a> -</div> -</td> -<!--================= end Group Logo ==================--> -<!--================= start Project Logo ==================--><td width="50%" align="right"> -<div align="right" class="projectLogo"> -<a href="https://donate.apache.org/";><img border="0" class="logoImage" alt="Support Apache" src="../resources/images/support-asf.png" width="120" height="120" title="Your donation helps The Apache Software Foundation ensure its community-driven software remains available to billions of users at absolutely no cost."></a><a href="https://poi.apache.org/";><img border="0" class="logoImage" alt="Apache POI" src="../resources/images/pb-poi.png" width="120" height="120" title="Apache POI - the Java API for Microsoft Documents"></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="https://www.apache.org/";>Apache</a> | <a href="https://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> +<body onload="init()"> +<script type="text/javascript">ndeSetTextSize();</script> +<div id="top"> +<!--+ + |breadtrail + +--> +<div class="breadtrail"> +<a href="https://www.apache.org";>Apache Software Foundation</a> > <a href="https://poi.apache.org";>Apache POI</a><script src="../../skin/breadcrumbs.js" language="JavaScript" type="text/javascript"></script> +</div> +<!--+ + |header + +--> +<div class="header"> +<!--+ + |start group logo + +--> +<div class="grouplogo"> +<a href="https://www.apache.org";><img class="logoImage" alt="Apache Software Foundation" src="../../images/group-logo.png" title="The Apache Software Foundation is a cornerstone of the modern Open Source software ecosystem – supporting some of the most widely used and important software solutions powering today's Internet economy."></a> +</div> +<!--+ + |end group logo + +--> +<!--+ + |start Project Logo + +--> +<div class="projectlogo"> +<a href="https://poi.apache.org";><img class="logoImage" alt="Apache POI" src="../../images/project-logo.png" title="Apache POI is well-known in the Java field as a library for reading and writing Microsoft Office file formats, such as Excel, PowerPoint, Word, Visio, Publisher and Outlook. It supports both the older (OLE2) and new (OOXML - Office Open XML) formats."></a> +</div> +<!--+ + |end Project Logo + +--> +<!--+ + |start Search + +--> +<div class="searchbox"> +<form action="http://www.google.com/search"; method="get" class="roundtopsmall"> +<input value="poi.apache.org" name="sitesearch" type="hidden"><input onFocus="getBlank (this, 'Search the site with google');" size="25" name="q" id="query" type="text" value="Search the site with google"> + <input name="Search" value="Search" type="submit"> +</form> </div> - +<!--+ + |end search + +--> +<!--+ + |start Tabs + +--> +<ul id="tabs"> +<li> +<a class="unselected" href="../../index.html">Home</a> +</li> +<li> +<a class="unselected" href="../../help/index.html">Help</a> +</li> +<li class="current"> +<a class="selected" href="../../components/index.html">Component APIs</a> +</li> +<li> +<a class="unselected" href="../../devel/index.html">Getting Involved</a> +</li> +</ul> +<!--+ + |end Tabs + +--> </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 id="main"> +<div id="publishedStrip"> +<!--+ + |start Subtabs + +--> +<div id="level2tabs"></div> +<!--+ + |end Endtabs + +--> +<script type="text/javascript"><!-- +document.write("Last Published: " + document.lastModified); +// --></script> </div> - -<div class="menuItem"> -<a href="how-to.html">HOWTO</a> +<!--+ + |breadtrail + +--> +<div class="breadtrail"> + + + </div> +<!--+ + |start Menu, mainarea + +--> +<!--+ + |start Menu + +--> +<div id="menu"> +<div onclick="SwitchMenu('menu_selected_1.1', '../../skin/')" id="menu_selected_1.1Title" class="menutitle" style="background-image: url('../../skin/images/chapter_open.gif');">Component APIs</div> +<div id="menu_selected_1.1" class="selectedmenuitemgroup" style="display: block;"> +<div class="menuitem"> +<a href="../../components/index.html">Overview</a> </div> - -<div class="menuItem"> -<a href="converting.html">HSSF to SS Converting</a> +<div class="menuitem"> +<a href="../../apidocs/index.html">Javadocs</a> </div> - -<div class="menuItem"> -<a href="formula.html">Formula Support</a> +<div onclick="SwitchMenu('menu_selected_1.1.3', '../../skin/')" id="menu_selected_1.1.3Title" class="menutitle" style="background-image: url('../../skin/images/chapter_open.gif');">Excel (HSSF/XSSF)</div> +<div id="menu_selected_1.1.3" class="selectedmenuitemgroup" style="display: block;"> +<div class="menuitem"> +<a href="../../components/spreadsheet/index.html">Overview</a> </div> - -<div class="menuItem"> -<a href="eval.html">Formula Evaluation</a> +<div class="menuitem"> +<a href="../../components/spreadsheet/quick-guide.html">Quick Guide</a> </div> - -<div class="menuItem"> -<a href="eval-devguide.html">Eval Dev Guide</a> +<div class="menuitem"> +<a href="../../components/spreadsheet/how-to.html">HOWTO</a> </div> - -<div class="menuItem"> -<span class="menuSelected">Examples</span> +<div class="menuitem"> +<a href="../../components/spreadsheet/converting.html">HSSF to SS Converting</a> </div> - -<div class="menuItem"> -<a href="use-case.html">Use Case</a> +<div class="menuitem"> +<a href="../../components/spreadsheet/formula.html">Formula Support</a> </div> - -<div class="menuItem"> -<a href="diagrams.html">Pictorial Docs</a> +<div class="menuitem"> +<a href="../../components/spreadsheet/eval.html">Formula Evaluation</a> </div> - -<div class="menuItem"> -<a href="limitations.html">Limitations</a> +<div class="menuitem"> +<a href="../../components/spreadsheet/eval-devguide.html">Eval Dev Guide</a> </div> - -<div class="menuItem"> -<a href="user-defined-functions.html">User Defined Functions</a> +<div class="menupage"> +<div class="menupagetitle">Examples</div> </div> - -<div class="menuItem"> -<a href="excelant.html">ExcelAnt Tests</a> +<div class="menuitem"> +<a href="../../components/spreadsheet/use-case.html">Use Case</a> </div> - +<div class="menuitem"> +<a href="../../components/spreadsheet/diagrams.html">Pictorial Docs</a> </div> -<div class="menu"> -<span class="menuLabel">Contributer's Guide</span> - -<div class="menuItem"> -<a href="hacking-hssf.html">Hacking HSSF</a> +<div class="menuitem"> +<a href="../../components/spreadsheet/limitations.html">Limitations</a> </div> - -<div class="menuItem"> -<a href="record-generator.html">Record Generator</a> +<div class="menuitem"> +<a href="../../components/spreadsheet/user-defined-functions.html">User Defined Functions</a> </div> - -<div class="menuItem"> -<a href="chart.html">Charts</a> +<div class="menuitem"> +<a href="../../components/spreadsheet/excelant.html">ExcelAnt Tests</a> </div> - +<div class="menuitem"> +<a href="../../components/spreadsheet/hacking-hssf.html">Hacking HSSF</a> </div> -<center> -<a href="https://www.apache.org/events/current-event.html";><img src="https://www.apache.org/events/current-event-125x125.png";></a> -</center> -</div> -</div> -<form target="_blank" action="https://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 and XSSF Examples</h1> +<div class="menuitem"> +<a href="../../components/spreadsheet/record-generator.html">Record Generator</a> +</div> +<div class="menuitem"> +<a href="../../components/spreadsheet/chart.html">Charts</a> +</div> +</div> +<div onclick="SwitchMenu('menu_1.1.4', '../../skin/')" id="menu_1.1.4Title" class="menutitle">PowerPoint (HSLF/XSLF)</div> +<div id="menu_1.1.4" class="menuitemgroup"> +<div class="menuitem"> +<a href="../../components/slideshow/index.html">Overview</a> +</div> +<div class="menuitem"> +<a href="../../components/slideshow/quick-guide.html">Quick Guide</a> +</div> +<div class="menuitem"> +<a href="../../components/slideshow/how-to-shapes.html">HSLF Cookbook</a> +</div> +<div class="menuitem"> +<a href="../../components/slideshow/xslf-cookbook.html">XSLF Cookbook</a> +</div> +<div class="menuitem"> +<a href="../../components/slideshow/ppt-file-format.html">PPT File Format</a> +</div> +</div> +<div onclick="SwitchMenu('menu_1.1.5', '../../skin/')" id="menu_1.1.5Title" class="menutitle">Word (HWPF/XWPF)</div> +<div id="menu_1.1.5" class="menuitemgroup"> +<div class="menuitem"> +<a href="../../components/document/index.html">Overview</a> +</div> +<div class="menuitem"> +<a href="../../components/document/quick-guide.html">HWPF Quick Guide</a> </div> -<div class="h3"> - - +<div class="menuitem"> +<a href="../../components/document/quick-guide-xwpf.html">XWPF Quick Guide</a> +</div> +<div class="menuitem"> +<a href="../../components/document/docoverview.html">HWPF Format</a> +</div> +<div class="menuitem"> +<a href="../../components/document/projectplan.html">HWPF Project plan</a> +</div> +</div> +<div class="menuitem"> +<a href="../../components/hsmf/index.html">Outlook (HSMF)</a> +</div> +<div class="menuitem"> +<a href="../../components/diagram/index.html">Visio (HDGF+XDGF)</a> +</div> +<div onclick="SwitchMenu('menu_1.1.8', '../../skin/')" id="menu_1.1.8Title" class="menutitle">Publisher (HPBF)</div> +<div id="menu_1.1.8" class="menuitemgroup"> +<div class="menuitem"> +<a href="../../components/hpbf/index.html">Overview</a> +</div> +<div class="menuitem"> +<a href="../../components/hpbf/file-format.html">File Format</a> +</div> +</div> +<div onclick="SwitchMenu('menu_1.1.9', '../../skin/')" id="menu_1.1.9Title" class="menutitle">OLE2 Filesystem (POIFS)</div> +<div id="menu_1.1.9" class="menuitemgroup"> +<div class="menuitem"> +<a href="../../components/poifs/index.html">Overview</a> +</div> +<div class="menuitem"> +<a href="../../components/poifs/how-to.html">How To</a> +</div> +<div class="menuitem"> +<a href="../../components/poifs/embeded.html">Embedded Documents</a> +</div> +<div class="menuitem"> +<a href="../../components/poifs/fileformat.html">File System Documentation</a> +</div> +<div class="menuitem"> +<a href="../../components/poifs/usecases.html">Use Cases</a> +</div> +<div class="menuitem"> +<a href="../../components/poifs/design.html">Design</a> +</div> +</div> +<div onclick="SwitchMenu('menu_1.1.10', '../../skin/')" id="menu_1.1.10Title" class="menutitle">OLE2 Document Props (HPSF)</div> +<div id="menu_1.1.10" class="menuitemgroup"> +<div class="menuitem"> +<a href="../../components/hpsf/index.html">Overview</a> +</div> +<div class="menuitem"> +<a href="../../components/hpsf/how-to.html">How To</a> +</div> +<div class="menuitem"> +<a href="../../components/hpsf/thumbnails.html">Thumbnails</a> +</div> +<div class="menuitem"> +<a href="../../components/hpsf/internals.html">Internals</a> +</div> +<div class="menuitem"> +<a href="../../components/hpsf/todo.html">To Do</a> +</div> +</div> +<div class="menuitem"> +<a href="../../components/hmef/index.html">TNEF (HMEF) for winmail.dat</a> +</div> +<div class="menuitem"> +<a href="../../components/oxml4j/index.html">OpenXML4J (OOXML)</a> +</div> +<div class="menuitem"> +<a href="../../components/logging.html">Logging framework</a> +</div> +</div> +<div id="credit"></div> +<div id="roundbottom"> +<img style="display: none" class="corner" height="15" width="15" alt="" src="../../skin/images/rc-b-l-15-1body-2menu-3menu.png"></div> +<!--+ + |alternative credits + +--> +<div id="credit2"> +<a href="https://donate.apache.org/";><img border="0" title="Support Apache" alt="Support Apache - logo" src="../../images/support-asf.png" style="width: 125px;height: 125px;"></a> +</div> +</div> +<!--+ + |end Menu + +--> +<!--+ + |start content + +--> +<div id="content"> +<h1>HSSF and XSSF Examples</h1> +<div id="front-matter"></div> <a name="HSSF+and+XSSF+common+examples"></a> -<div class="h3"> -<h3>HSSF and XSSF common examples<a title="Permanent link" class="headerlink" href="#HSSF+and+XSSF+common+examples">#</a> -</h3> -</div> - +<h2 class="boxed">HSSF and XSSF common examples</h2> +<div class="section"> <p>Apache POI comes with a number of examples that demonstrate how you can use the POI API to create documents from "real life". The examples below based on common XSSF-HSSF interfaces so that you can generate either *.xls or *.xlsx output just by setting a command-line argument: </p> - <pre class="code"> BusinessPlan -xls or BusinessPlan -xlsx </pre> - <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 <a href="#hssf-only">HSSF only</a> and <a href="#xssf-only">XSSF only</a> examples as well. </p> - - <a name="Available+Examples"></a> -<div class="h4"> -<h4>Available Examples<a title="Permanent link" class="headerlink" href="#Available+Examples">#</a> -</h4> -</div> - +<h3 class="boxed">Available Examples</h3> <p> The following examples are available: </p> - <ul> <li> @@ -294,94 +379,48 @@ if (VERSION > 3) { </li> </ul> - - - -<a name="ss-common"></a> - <a name="business-plan"></a> - <a name="Business+Plan"></a> -<div class="h4"> -<h4>Business Plan<a title="Permanent link" class="headerlink" href="#Business+Plan">#</a> -</h4> -</div> - +<a name="ss-common" id="ss-common"></a><a name="business-plan" id="business-plan"></a><a name="Business+Plan"></a> +<h3 class="boxed">Business Plan</h3> <p> The <a href="http://svn.apache.org/repos/asf/poi/trunk/src/examples/src/org/apache/poi/ss/examples/BusinessPlan.java";>BusinessPlan</a> application creates a sample business plan with three phases, weekly iterations and time highlighting. Demonstrates advanced cell formatting (number and date formats, alignments, fills, borders) and various settings for organizing data in a sheet (freezed panes, grouped rows). </p> - <p> -<img alt="business plan demo" src="../resources/images/businessplan.jpg"> +<img alt="business plan demo" src="images/businessplan.jpg"> </p> - - - -<a name="calendar"></a> - <a name="Calendar"></a> -<div class="h4"> -<h4>Calendar<a title="Permanent link" class="headerlink" href="#Calendar">#</a> -</h4> -</div> - +<a name="calendar" id="calendar"></a><a name="Calendar"></a> +<h3 class="boxed">Calendar</h3> <p> The <a href="http://svn.apache.org/repos/asf/poi/trunk/src/examples/src/org/apache/poi/ss/examples/CalendarDemo.java";>Calendar</a> demo creates a multi sheet calendar. Each month is on a separate sheet. </p> - <p> -<img alt="calendar demo" src="../resources/images/calendar.jpg"> +<img alt="calendar demo" src="images/calendar.jpg"> </p> - - - -<a name="loan-calculator"></a> - <a name="Loan+Calculator"></a> -<div class="h4"> -<h4>Loan Calculator<a title="Permanent link" class="headerlink" href="#Loan+Calculator">#</a> -</h4> -</div> - +<a name="loan-calculator" id="loan-calculator"></a><a name="Loan+Calculator"></a> +<h3 class="boxed">Loan Calculator</h3> <p> The <a href="http://svn.apache.org/repos/asf/poi/trunk/src/examples/src/org/apache/poi/ss/examples/LoanCalculator.java";>LoanCalculator</a> demo creates a simple loan calculator. Demonstrates advance usage of cell formulas and named ranges. </p> - <p> -<img alt="loan calculator demo" src="../resources/images/loancalc.jpg"> +<img alt="loan calculator demo" src="images/loancalc.jpg"> </p> - - - -<a name="timesheet"></a> - <a name="Timesheet"></a> -<div class="h4"> -<h4>Timesheet<a title="Permanent link" class="headerlink" href="#Timesheet">#</a> -</h4> -</div> - +<a name="timesheet" id="timesheet"></a><a name="Timesheet"></a> +<h3 class="boxed">Timesheet</h3> <p> The <a href="http://svn.apache.org/repos/asf/poi/trunk/src/examples/src/org/apache/poi/ss/examples/TimesheetDemo.java";>Timesheet</a> demo creates a weekly timesheet with automatic calculation of total hours. Demonstrates advance usage of cell formulas. </p> - <p> -<img alt="timesheet demo" src="../resources/images/timesheet.jpg"> +<img alt="timesheet demo" src="images/timesheet.jpg"> </p> - - - -<a name="conditional-formats"></a> - <a name="Conditional+Formats"></a> -<div class="h4"> -<h4>Conditional Formats<a title="Permanent link" class="headerlink" href="#Conditional+Formats">#</a> -</h4> -</div> - +<a name="conditional-formats" id="conditional-formats"></a><a name="Conditional+Formats"></a> +<h3 class="boxed">Conditional Formats</h3> <p> The <a href="http://svn.apache.org/repos/asf/poi/trunk/src/examples/src/org/apache/poi/ss/examples/ConditionalFormats.java";>ConditionalFormats</a> demo is a collection of short examples showing what you can do with Excel conditional formatting in POI: </p> - <ul> <li>Highlight cells based on their values</li> @@ -403,138 +442,66 @@ if (VERSION > 3) { <li>Shade bands of rows on the worksheet</li> </ul> - - - -<a name="common-formulas"></a> - <a name="Formula+Examples"></a> -<div class="h4"> -<h4>Formula Examples<a title="Permanent link" class="headerlink" href="#Formula+Examples">#</a> -</h4> -</div> - +<a name="common-formulas" id="common-formulas"></a><a name="Formula+Examples"></a> +<h3 class="boxed">Formula Examples</h3> <p>The <a href="https://svn.apache.org/repos/asf/poi/trunk/src/examples/src/org/apache/poi/ss/examples/formula/CalculateMortgage.java";>CalculateMortgage</a> example demonstrates a simple user-defined function to calculate principal and interest.</p> - <p>The <a href="https://svn.apache.org/repos/asf/poi/trunk/src/examples/src/org/apache/poi/ss/examples/formula/CheckFunctionsSupported.java";>CheckFunctionsSupported</a> example shows how to test what functions and formulas aren't supported from a given file.</p> - <p>The <a href="https://svn.apache.org/repos/asf/poi/trunk/src/examples/src/org/apache/poi/ss/examples/formula/SettingExternalFunction.java";>SettingExternalFunction</a> example demonstrates how to use externally provided (third-party) formula add-ins.</p> - <p>The <a href="https://svn.apache.org/repos/asf/poi/trunk/src/examples/src/org/apache/poi/ss/examples/formula/UserDefinedFunctionExample.java";>UserDefinedFunctionExample</a> example demonstrates how to invoke a User Defined Function for a given Workbook instance using POI's UDFFinder implementation.</p> - - - -<a name="add-dimensioned-image"></a> - <a name="Add+Dimensioned+Image"></a> -<div class="h4"> -<h4>Add Dimensioned Image<a title="Permanent link" class="headerlink" href="#Add+Dimensioned+Image">#</a> -</h4> -</div> - +<a name="add-dimensioned-image" id="add-dimensioned-image"></a><a name="Add+Dimensioned+Image"></a> +<h3 class="boxed">Add Dimensioned Image</h3> <p>The <a href="https://svn.apache.org/repos/asf/poi/trunk/src/examples/src/org/apache/poi/ss/examples/AddDimensionedImage.java";>AddDimensionedImage</a> example demonstrates how to add an image to a worksheet and set that images size to a specific number of millimetres irrespective of the width of the columns or height of the rows.</p> - - - -<a name="aligned-cells"></a> - <a name="Aligned+Cells"></a> -<div class="h4"> -<h4>Aligned Cells<a title="Permanent link" class="headerlink" href="#Aligned+Cells">#</a> -</h4> -</div> - +<a name="aligned-cells" id="aligned-cells"></a><a name="Aligned+Cells"></a> +<h3 class="boxed">Aligned Cells</h3> <p>The <a href="https://svn.apache.org/repos/asf/poi/trunk/src/examples/src/org/apache/poi/ss/examples/AligningCells.java";>AligningCells</a> example demonstrates how various alignment options work.</p> - - - -<a name="cell-style-details"></a> - <a name="Cell+Style+Details"></a> -<div class="h4"> -<h4>Cell Style Details<a title="Permanent link" class="headerlink" href="#Cell+Style+Details">#</a> -</h4> -</div> - +<a name="cell-style-details" id="cell-style-details"></a><a name="Cell+Style+Details"></a> +<h3 class="boxed">Cell Style Details</h3> <p>The <a href="https://svn.apache.org/repos/asf/poi/trunk/src/examples/src/org/apache/poi/ss/examples/CellStyleDetails.java";>CellStyleDetails</a> example demonstrates how to read excel styles for cells.</p> - - - -<a name="linked-dropdown"></a> - <a name="Linked+Dropdown+Lists"></a> -<div class="h4"> -<h4>Linked Dropdown Lists<a title="Permanent link" class="headerlink" href="#Linked+Dropdown+Lists">#</a> -</h4> -</div> - +<a name="linked-dropdown" id="linked-dropdown"></a><a name="Linked+Dropdown+Lists"></a> +<h3 class="boxed">Linked Dropdown Lists</h3> <p>The <a href="https://svn.apache.org/repos/asf/poi/trunk/src/examples/src/org/apache/poi/ss/examples/LinkedDropDownLists.java";>LinkedDropDownLists</a> example demonstrates one technique that may be used to create linked or dependent drop down lists.</p> - - - -<a name="performance-test"></a> - <a name="Common+SS+Performance+Test"></a> -<div class="h4"> -<h4>Common SS Performance Test<a title="Permanent link" class="headerlink" href="#Common+SS+Performance+Test">#</a> -</h4> -</div> - +<a name="performance-test" id="performance-test"></a><a name="Common+SS+Performance+Test"></a> +<h3 class="boxed">Common SS Performance Test</h3> <p>The <a href="https://svn.apache.org/repos/asf/poi/trunk/src/examples/src/org/apache/poi/ss/examples/SSPerformanceTest.java";>SSPerformanceTest</a> example provides a way to create simple example files of varying sizes, and to calculate how long they take. Useful for benchmarking your system, and to also test if slow performance is due to Apache POI itself or to your own code.</p> - - - -<a name="to-html"></a> - <a name="ToHtml"></a> -<div class="h4"> -<h4>ToHtml<a title="Permanent link" class="headerlink" href="#ToHtml">#</a> -</h4> -</div> - +<a name="to-html" id="to-html"></a><a name="ToHtml"></a> +<h3 class="boxed">ToHtml</h3> <p> The <a href="http://svn.apache.org/repos/asf/poi/trunk/src/examples/src/org/apache/poi/ss/examples/html/ToHtml.java";>ToHtml</a> example shows how to display a spreadsheet in HTML using the classes for spreadsheet display. </p> - - - -<a name="to-csv"></a> - <a name="ToCSV"></a> -<div class="h4"> -<h4>ToCSV<a title="Permanent link" class="headerlink" href="#ToCSV">#</a> -</h4> -</div> - +<a name="to-csv" id="to-csv"></a><a name="ToCSV"></a> +<h3 class="boxed">ToCSV</h3> <p>The <a href="http://svn.apache.org/repos/asf/poi/trunk/src/examples/src/org/apache/poi/ss/examples/ToCSV.java";>ToCSV</a> example demonstrates <em>one</em> way to convert an Excel spreadsheet into a CSV file. </p> - - +</div> -<a name="hssf-only"></a> +<a name="hssf-only" id="hssf-only"></a> <a name="HSSF-only+Examples"></a> -<div class="h3"> -<h3>HSSF-only Examples<a title="Permanent link" class="headerlink" href="#HSSF-only+Examples">#</a> -</h3> -</div> - +<h2 class="boxed">HSSF-only Examples</h2> +<div class="section"> <p>All the HSSF-only examples can be found in <a href="http://svn.apache.org/repos/asf/poi/trunk/src/examples/src/org/apache/poi/hssf/examples/";>SVN</a> </p> - <ul> <li> @@ -706,20 +673,16 @@ if (VERSION > 3) { </li> </ul> - +</div> -<a name="xssf-only"></a> +<a name="xssf-only" id="xssf-only"></a> <a name="XSSF-only+Examples"></a> -<div class="h3"> -<h3>XSSF-only Examples<a title="Permanent link" class="headerlink" href="#XSSF-only+Examples">#</a> -</h3> -</div> - +<h2 class="boxed">XSSF-only Examples</h2> +<div class="section"> <p>All the XSSF-only examples can be found in <a href="http://svn.apache.org/repos/asf/poi/trunk/src/examples/src/org/apache/poi/xssf/examples/";>SVN</a> </p> - <ul> <li> @@ -851,50 +814,37 @@ if (VERSION > 3) { </li> </ul> - - - -<div id="authors" align="right">by Yegor Kozlov</div> </div> + +<p align="right"> +<font size="-2">by Yegor Kozlov</font> +</p> </div> +<!--+ + |end content + +--> +<div class="clearboth"> </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"> +<!--+ + |start bottomstrip + +--> +<div class="lastmodified"> +<script type="text/javascript"><!-- +document.write("Last Published: " + document.lastModified); +// --></script> +</div> <div class="copyright"> - Copyright © 2002-2018 The Apache Software Foundation. All rights reserved.<br> - Apache, Apache POI, and the Apache POI logo are registered trademarks or - trademarks of The Apache Software Foundation in the U.S. and/or other countries. - </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> + Copyright © + 2001-2018 <a href="https://www.apache.org/";>The Apache Software Foundation</a> +</div> +<div id="feedback"> + Send feedback about the website to: + <a id="feedbackto" href="mailto:[email protected]?subject=Feedback%C2%A0components/spreadsheet/examples.html";>[email protected]</a> +</div> +<!--+ + |end bottomstrip + +--> </div> -<!--================= end Footer ==================--> </body> </html> --------------------------------------------------------------------- To unsubscribe, e-mail: [email protected] For additional commands, e-mail: [email protected]
![https://www.apache.org/events/current-event-125x125.png"](https://www.apache.org/events/current-event-125x125.png"){kind=link}