You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@poi.apache.org by ki...@apache.org on 2018/07/18 23:27:24 UTC
svn commit: r1836244 [22/31] - in /poi/site/publish: ./ components/
components/diagram/ components/document/ components/hmef/ components/hpbf/
components/hpsf/ components/hsmf/ components/oxml4j/ components/poifs/
components/poifs/images/ components/sl...
Modified: poi/site/publish/components/spreadsheet/quick-guide.html
URL: http://svn.apache.org/viewvc/poi/site/publish/components/spreadsheet/quick-guide.html?rev=1836244&r1=1836243&r2=1836244&view=diff
==============================================================================
--- poi/site/publish/components/spreadsheet/quick-guide.html (original)
+++ poi/site/publish/components/spreadsheet/quick-guide.html Wed Jul 18 23:27:21 2018
@@ -1,210 +1,300 @@
-<!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>Busy Developers' Guide to HSSF and XSSF Features</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">
-<span class="menuSelected">Quick Guide</span>
+<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="menupage">
+<div class="menupagetitle">Quick Guide</div>
</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">
-<a href="examples.html">Examples</a>
+<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="menuitem">
+<a href="../../components/spreadsheet/examples.html">Examples</a>
</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>Busy Developers' Guide to HSSF and XSSF Features</h1>
+<div class="menuitem">
+<a href="../../components/spreadsheet/record-generator.html">Record Generator</a>
</div>
-<div class="h3">
-
-
+<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="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>Busy Developers' Guide to HSSF and XSSF Features</h1>
+<div id="front-matter"></div>
<a name="Busy+Developers%27+Guide+to+Features"></a>
-<div class="h3">
-<h3>Busy Developers' Guide to Features<a title="Permanent link" class="headerlink" href="#Busy+Developers%27+Guide+to+Features">#</a>
-</h3>
-</div>
-
+<h2 class="boxed">Busy Developers' Guide to Features</h2>
+<div class="section">
<p>
Want to use HSSF and XSSF read and write spreadsheets in a hurry? This
guide is for you. If you're after more in-depth coverage of the HSSF and
XSSF user-APIs, please consult the <a href="how-to.html">HOWTO</a>
guide as it contains actual descriptions of how to use this stuff.
</p>
-
<a name="Index+of+Features"></a>
-<div class="h4">
-<h4>Index of Features<a title="Permanent link" class="headerlink" href="#Index+of+Features">#</a>
-</h4>
-</div>
-
+<h3 class="boxed">Index of Features</h3>
<ul>
<li>
@@ -392,21 +482,10 @@ if (VERSION > 3) {
</li>
</ul>
-
-
<a name="Features"></a>
-<div class="h4">
-<h4>Features<a title="Permanent link" class="headerlink" href="#Features">#</a>
-</h4>
-</div>
-
-<a name="NewWorkbook"></a>
- <a name="New+Workbook"></a>
-<div class="h2">
-<h2>New Workbook<a title="Permanent link" class="headerlink" href="#New+Workbook">#</a>
-</h2>
-</div>
-
+<h3 class="boxed">Features</h3>
+<a name="NewWorkbook" id="NewWorkbook"></a><a name="New+Workbook"></a>
+<h4>New Workbook</h4>
<pre class="code">
Workbook wb = new HSSFWorkbook();
...
@@ -420,15 +499,8 @@ if (VERSION > 3) {
wb.write(fileOut);
}
</pre>
-
-
-<a name="NewSheet"></a>
- <a name="New+Sheet"></a>
-<div class="h2">
-<h2>New Sheet<a title="Permanent link" class="headerlink" href="#New+Sheet">#</a>
-</h2>
-</div>
-
+<a name="NewSheet" id="NewSheet"></a><a name="New+Sheet"></a>
+<h4>New Sheet</h4>
<pre class="code">
Workbook wb = new HSSFWorkbook(); // or new XSSFWorkbook();
Sheet sheet1 = wb.createSheet("new sheet");
@@ -455,15 +527,8 @@ if (VERSION > 3) {
wb.write(fileOut);
}
</pre>
-
-
-<a name="CreateCells"></a>
- <a name="Creating+Cells"></a>
-<div class="h2">
-<h2>Creating Cells<a title="Permanent link" class="headerlink" href="#Creating+Cells">#</a>
-</h2>
-</div>
-
+<a name="CreateCells" id="CreateCells"></a><a name="Creating+Cells"></a>
+<h4>Creating Cells</h4>
<pre class="code">
Workbook wb = new HSSFWorkbook();
//Workbook wb = new XSSFWorkbook();
@@ -487,15 +552,8 @@ if (VERSION > 3) {
wb.write(fileOut);
}
</pre>
-
-
-<a name="CreateDateCells"></a>
- <a name="Creating+Date+Cells"></a>
-<div class="h2">
-<h2>Creating Date Cells<a title="Permanent link" class="headerlink" href="#Creating+Date+Cells">#</a>
-</h2>
-</div>
-
+<a name="CreateDateCells" id="CreateDateCells"></a><a name="Creating+Date+Cells"></a>
+<h4>Creating Date Cells</h4>
<pre class="code">
Workbook wb = new HSSFWorkbook();
//Workbook wb = new XSSFWorkbook();
@@ -530,15 +588,8 @@ if (VERSION > 3) {
wb.write(fileOut);
}
</pre>
-
-
-<a name="CellTypes"></a>
- <a name="Working+with+different+types+of+cells"></a>
-<div class="h2">
-<h2>Working with different types of cells<a title="Permanent link" class="headerlink" href="#Working+with+different+types+of+cells">#</a>
-</h2>
-</div>
-
+<a name="CellTypes" id="CellTypes"></a><a name="Working+with+different+types+of+cells"></a>
+<h4>Working with different types of cells</h4>
<pre class="code">
Workbook wb = new HSSFWorkbook();
Sheet sheet = wb.createSheet("new sheet");
@@ -555,25 +606,15 @@ if (VERSION > 3) {
wb.write(fileOut);
}
</pre>
-
-
-
-<a name="FileInputStream"></a>
- <a name="Files+vs+InputStreams"></a>
-<div class="h2">
-<h2>Files vs InputStreams<a title="Permanent link" class="headerlink" href="#Files+vs+InputStreams">#</a>
-</h2>
-</div>
-
+<a name="FileInputStream" id="FileInputStream"></a><a name="Files+vs+InputStreams"></a>
+<h4>Files vs InputStreams</h4>
<p>When opening a workbook, either a .xls HSSFWorkbook, or a .xlsx
XSSFWorkbook, the Workbook can be loaded from either a <em>File</em>
or an <em>InputStream</em>. Using a <em>File</em> object allows for
lower memory consumption, while an <em>InputStream</em> requires more
memory as it has to buffer the whole file.</p>
-
<p>If using <em>WorkbookFactory</em>, it's very easy to use one or
the other:</p>
-
<pre class="code">
// Use a file
Workbook wb = WorkbookFactory.create(new File("MyExcel.xls"));
@@ -581,12 +622,10 @@ if (VERSION > 3) {
// Use an InputStream, needs more memory
Workbook wb = WorkbookFactory.create(new FileInputStream("MyExcel.xlsx"));
</pre>
-
<p>If using <em>HSSFWorkbook</em> or <em>XSSFWorkbook</em> directly,
you should generally go through <em>NPOIFSFileSystem</em> or
<em>OPCPackage</em>, to have full control of the lifecycle (including
closing the file when done):</p>
-
<pre class="code">
// HSSFWorkbook, File
NPOIFSFileSystem fs = new NPOIFSFileSystem(new File("file.xls"));
@@ -610,16 +649,8 @@ if (VERSION > 3) {
....
pkg.close();
</pre>
-
-
-
-<a name="Alignment"></a>
- <a name="Demonstrates+various+alignment+options"></a>
-<div class="h2">
-<h2>Demonstrates various alignment options<a title="Permanent link" class="headerlink" href="#Demonstrates+various+alignment+options">#</a>
-</h2>
-</div>
-
+<a name="Alignment" id="Alignment"></a><a name="Demonstrates+various+alignment+options"></a>
+<h4>Demonstrates various alignment options</h4>
<pre class="code">
public static void main(String[] args) throws Exception {
Workbook wb = new XSSFWorkbook(); //or new HSSFWorkbook();
@@ -662,15 +693,8 @@ if (VERSION > 3) {
cell.setCellStyle(cellStyle);
}
</pre>
-
-
-<a name="Borders"></a>
- <a name="Working+with+borders"></a>
-<div class="h2">
-<h2>Working with borders<a title="Permanent link" class="headerlink" href="#Working+with+borders">#</a>
-</h2>
-</div>
-
+<a name="Borders" id="Borders"></a><a name="Working+with+borders"></a>
+<h4>Working with borders</h4>
<pre class="code">
Workbook wb = new HSSFWorkbook();
Sheet sheet = wb.createSheet("new sheet");
@@ -701,26 +725,16 @@ if (VERSION > 3) {
wb.close();
</pre>
-
-
-<a name="Iterator"></a>
- <a name="Iterate+over+rows+and+cells"></a>
-<div class="h2">
-<h2>Iterate over rows and cells<a title="Permanent link" class="headerlink" href="#Iterate+over+rows+and+cells">#</a>
-</h2>
-</div>
-
+<a name="Iterator" id="Iterator"></a><a name="Iterate+over+rows+and+cells"></a>
+<h4>Iterate over rows and cells</h4>
<p>Sometimes, you'd like to just iterate over all the sheets in
a workbook, all the rows in a sheet, or all the cells in a row.
This is possible with a simple for loop.</p>
-
<p>These iterators are available by calling <em>workbook.sheetIterator()</em>,
<em>sheet.rowIterator()</em>, and <em>row.cellIterator()</em>, or
implicitly using a for-each loop.
Note that a rowIterator and cellIterator iterate over rows or
cells that have been created, skipping empty rows and cells.</p>
-
-
<pre class="code">
for (Sheet sheet : wb ) {
for (Row row : sheet) {
@@ -730,26 +744,18 @@ if (VERSION > 3) {
}
}
</pre>
-
-
<a name="Iterate+over+cells%2C+with+control+of+missing+%2F+blank+cells"></a>
-<div class="h2">
-<h2>Iterate over cells, with control of missing / blank cells<a title="Permanent link" class="headerlink" href="#Iterate+over+cells%2C+with+control+of+missing+%2F+blank+cells">#</a>
-</h2>
-</div>
-
+<h4>Iterate over cells, with control of missing / blank cells</h4>
<p>In some cases, when iterating, you need full control over how
missing or blank rows and cells are treated, and you need to ensure
you visit every cell and not just those defined in the file. (The
CellIterator will only return the cells defined in the file, which
is largely those with values or stylings, but it depends on Excel).</p>
-
<p>In cases such as these, you should fetch the first and last column
information for a row, then call <em>getCell(int, MissingCellPolicy)</em>
to fetch the cell. Use a
- <a href="../apidocs/org/apache/poi/ss/usermodel/Row.MissingCellPolicy.html">MissingCellPolicy</a>
+ <a href="../../apidocs/org/apache/poi/ss/usermodel/Row.MissingCellPolicy.html">MissingCellPolicy</a>
to control how blank or null cells are handled.</p>
-
<pre class="code">
// Decide which rows to process
int rowStart = Math.min(15, sheet.getFirstRowNum());
@@ -775,27 +781,17 @@ if (VERSION > 3) {
}
}
</pre>
-
-
-
-<a name="CellContents"></a>
- <a name="Getting+the+cell+contents"></a>
-<div class="h2">
-<h2>Getting the cell contents<a title="Permanent link" class="headerlink" href="#Getting+the+cell+contents">#</a>
-</h2>
-</div>
-
+<a name="CellContents" id="CellContents"></a><a name="Getting+the+cell+contents"></a>
+<h4>Getting the cell contents</h4>
<p>To get the contents of a cell, you first need to
know what kind of cell it is (asking a string cell
for its numeric contents will get you a
NumberFormatException for example). So, you will
want to switch on the cell's type, and then call
the appropriate getter for that cell.</p>
-
<p>In the code below, we loop over every cell
in one sheet, print out the cell's reference
(eg A3), and then the cell's contents.</p>
-
<pre class="code">
// import org.apache.poi.ss.usermodel.*;
@@ -838,19 +834,10 @@ if (VERSION > 3) {
}
}
</pre>
-
-
-
-<a name="TextExtraction"></a>
- <a name="Text+Extraction"></a>
-<div class="h2">
-<h2>Text Extraction<a title="Permanent link" class="headerlink" href="#Text+Extraction">#</a>
-</h2>
-</div>
-
+<a name="TextExtraction" id="TextExtraction"></a><a name="Text+Extraction"></a>
+<h4>Text Extraction</h4>
<p>For most text extraction requirements, the standard
ExcelExtractor class should provide all you need.</p>
-
<pre class="code">
try (InputStream inp = new FileInputStream("workbook.xls")) {
HSSFWorkbook wb = new HSSFWorkbook(new POIFSFileSystem(inp));
@@ -862,21 +849,13 @@ if (VERSION > 3) {
wb.close();
}
</pre>
-
<p>For very fancy text extraction, XLS to CSV etc,
take a look at
<em>/src/examples/src/org/apache/poi/hssf/eventusermodel/examples/XLS2CSVmra.java</em>
</p>
-
-
-<a name="FillsAndFrills"></a>
- <a name="Fills+and+colors"></a>
-<div class="h2">
-<h2>Fills and colors<a title="Permanent link" class="headerlink" href="#Fills+and+colors">#</a>
-</h2>
-</div>
-
+<a name="FillsAndFrills" id="FillsAndFrills"></a><a name="Fills+and+colors"></a>
+<h4>Fills and colors</h4>
<pre class="code">
Workbook wb = new XSSFWorkbook();
Sheet sheet = wb.createSheet("new sheet");
@@ -907,15 +886,8 @@ if (VERSION > 3) {
wb.close();
</pre>
-
-
-<a name="MergedCells"></a>
- <a name="Merging+cells"></a>
-<div class="h2">
-<h2>Merging cells<a title="Permanent link" class="headerlink" href="#Merging+cells">#</a>
-</h2>
-</div>
-
+<a name="MergedCells" id="MergedCells"></a><a name="Merging+cells"></a>
+<h4>Merging cells</h4>
<pre class="code">
Workbook wb = new HSSFWorkbook();
Sheet sheet = wb.createSheet("new sheet");
@@ -938,15 +910,8 @@ if (VERSION > 3) {
wb.close();
</pre>
-
-
-<a name="WorkingWithFonts"></a>
- <a name="Working+with+fonts"></a>
-<div class="h2">
-<h2>Working with fonts<a title="Permanent link" class="headerlink" href="#Working+with+fonts">#</a>
-</h2>
-</div>
-
+<a name="WorkingWithFonts" id="WorkingWithFonts"></a><a name="Working+with+fonts"></a>
+<h4>Working with fonts</h4>
<pre class="code">
Workbook wb = new HSSFWorkbook();
Sheet sheet = wb.createSheet("new sheet");
@@ -977,17 +942,14 @@ if (VERSION > 3) {
wb.close();
</pre>
-
<p>
Note, the maximum number of unique fonts in a workbook is limited to 32767. You should re-use fonts in your applications instead of
creating a font for each cell.
Examples:
</p>
-
<p>
<strong>Wrong:</strong>
</p>
-
<pre class="code">
for (int i = 0; i < 10000; i++) {
Row row = sheet.createRow(i);
@@ -1000,11 +962,9 @@ Examples:
cell.setCellStyle(style);
}
</pre>
-
<p>
<strong>Correct:</strong>
</p>
-
<pre class="code">
CellStyle style = workbook.createCellStyle();
Font font = workbook.createFont();
@@ -1016,20 +976,11 @@ Examples:
cell.setCellStyle(style);
}
</pre>
-
-
-
-<a name="CustomColors"></a>
- <a name="Custom+colors"></a>
-<div class="h2">
-<h2>Custom colors<a title="Permanent link" class="headerlink" href="#Custom+colors">#</a>
-</h2>
-</div>
-
+<a name="CustomColors" id="CustomColors"></a><a name="Custom+colors"></a>
+<h4>Custom colors</h4>
<p>
<strong>HSSF:</strong>
</p>
-
<pre class="code">
HSSFWorkbook wb = new HSSFWorkbook();
HSSFSheet sheet = wb.createSheet();
@@ -1081,11 +1032,9 @@ Examples:
wb.write(out);
}
</pre>
-
<p>
<strong>XSSF:</strong>
</p>
-
<pre class="code">
XSSFWorkbook wb = new XSSFWorkbook();
XSSFSheet sheet = wb.createSheet();
@@ -1097,15 +1046,8 @@ Examples:
style1.setFillForegroundColor(new XSSFColor(new java.awt.Color(128, 0, 128)));
style1.setFillPattern(FillPatternType.SOLID_FOREGROUND);
</pre>
-
-
-<a name="ReadWriteWorkbook"></a>
- <a name="Reading+and+Rewriting+Workbooks"></a>
-<div class="h2">
-<h2>Reading and Rewriting Workbooks<a title="Permanent link" class="headerlink" href="#Reading+and+Rewriting+Workbooks">#</a>
-</h2>
-</div>
-
+<a name="ReadWriteWorkbook" id="ReadWriteWorkbook"></a><a name="Reading+and+Rewriting+Workbooks"></a>
+<h4>Reading and Rewriting Workbooks</h4>
<pre class="code">
try (InputStream inp = new FileInputStream("workbook.xls")) {
//InputStream inp = new FileInputStream("workbook.xlsx");
@@ -1125,15 +1067,8 @@ Examples:
}
}
</pre>
-
-
-<a name="NewLinesInCells"></a>
- <a name="Using+newlines+in+cells"></a>
-<div class="h2">
-<h2>Using newlines in cells<a title="Permanent link" class="headerlink" href="#Using+newlines+in+cells">#</a>
-</h2>
-</div>
-
+<a name="NewLinesInCells" id="NewLinesInCells"></a><a name="Using+newlines+in+cells"></a>
+<h4>Using newlines in cells</h4>
<pre class="code">
Workbook wb = new XSSFWorkbook(); //or new HSSFWorkbook();
Sheet sheet = wb.createSheet();
@@ -1159,15 +1094,8 @@ Examples:
wb.close();
</pre>
-
-
-<a name="DataFormats"></a>
- <a name="Data+Formats"></a>
-<div class="h2">
-<h2>Data Formats<a title="Permanent link" class="headerlink" href="#Data+Formats">#</a>
-</h2>
-</div>
-
+<a name="DataFormats" id="DataFormats"></a><a name="Data+Formats"></a>
+<h4>Data Formats</h4>
<pre class="code">
Workbook wb = new HSSFWorkbook();
Sheet sheet = wb.createSheet("format sheet");
@@ -1198,15 +1126,8 @@ Examples:
wb.close();
</pre>
-
-
-<a name="FitTo"></a>
- <a name="Fit+Sheet+to+One+Page"></a>
-<div class="h2">
-<h2>Fit Sheet to One Page<a title="Permanent link" class="headerlink" href="#Fit+Sheet+to+One+Page">#</a>
-</h2>
-</div>
-
+<a name="FitTo" id="FitTo"></a><a name="Fit+Sheet+to+One+Page"></a>
+<h4>Fit Sheet to One Page</h4>
<pre class="code">
Workbook wb = new HSSFWorkbook();
Sheet sheet = wb.createSheet("format sheet");
@@ -1226,15 +1147,8 @@ Examples:
wb.close();
</pre>
-
-
-<a name="PrintArea2"></a>
- <a name="Set+Print+Area"></a>
-<div class="h2">
-<h2>Set Print Area<a title="Permanent link" class="headerlink" href="#Set+Print+Area">#</a>
-</h2>
-</div>
-
+<a name="PrintArea2" id="PrintArea2"></a><a name="Set+Print+Area"></a>
+<h4>Set Print Area</h4>
<pre class="code">
Workbook wb = new HSSFWorkbook();
Sheet sheet = wb.createSheet("Sheet1");
@@ -1256,16 +1170,8 @@ Examples:
wb.close();
</pre>
-
-
-
-<a name="FooterPageNumbers"></a>
- <a name="Set+Page+Numbers+on+Footer"></a>
-<div class="h2">
-<h2>Set Page Numbers on Footer<a title="Permanent link" class="headerlink" href="#Set+Page+Numbers+on+Footer">#</a>
-</h2>
-</div>
-
+<a name="FooterPageNumbers" id="FooterPageNumbers"></a><a name="Set+Page+Numbers+on+Footer"></a>
+<h4>Set Page Numbers on Footer</h4>
<pre class="code">
Workbook wb = new HSSFWorkbook(); // or new XSSFWorkbook();
Sheet sheet = wb.createSheet("format sheet");
@@ -1283,23 +1189,14 @@ Examples:
wb.close();
</pre>
-
-
-
-<a name="ConvenienceFunctions"></a>
- <a name="Using+the+Convenience+Functions"></a>
-<div class="h2">
-<h2>Using the Convenience Functions<a title="Permanent link" class="headerlink" href="#Using+the+Convenience+Functions">#</a>
-</h2>
-</div>
-
+<a name="ConvenienceFunctions" id="ConvenienceFunctions"></a><a name="Using+the+Convenience+Functions"></a>
+<h4>Using the Convenience Functions</h4>
<p>
The convenience functions provide
utility features such as setting borders around merged
regions and changing style attributes without explicitly
creating new styles.
</p>
-
<pre class="code">
Workbook wb = new HSSFWorkbook(); // or new XSSFWorkbook()
Sheet sheet1 = wb.createSheet( "new sheet" );
@@ -1336,16 +1233,8 @@ Examples:
wb.close();
</pre>
-
-
-
-<a name="ShiftRows"></a>
- <a name="Shift+rows+up+or+down+on+a+sheet"></a>
-<div class="h2">
-<h2>Shift rows up or down on a sheet<a title="Permanent link" class="headerlink" href="#Shift+rows+up+or+down+on+a+sheet">#</a>
-</h2>
-</div>
-
+<a name="ShiftRows" id="ShiftRows"></a><a name="Shift+rows+up+or+down+on+a+sheet"></a>
+<h4>Shift rows up or down on a sheet</h4>
<pre class="code">
Workbook wb = new HSSFWorkbook();
Sheet sheet = wb.createSheet("row sheet");
@@ -1356,72 +1245,43 @@ Examples:
sheet.shiftRows(5, 10, -5);
</pre>
-
-
-
-<a name="SelectSheet"></a>
- <a name="Set+a+sheet+as+selected"></a>
-<div class="h2">
-<h2>Set a sheet as selected<a title="Permanent link" class="headerlink" href="#Set+a+sheet+as+selected">#</a>
-</h2>
-</div>
-
+<a name="SelectSheet" id="SelectSheet"></a><a name="Set+a+sheet+as+selected"></a>
+<h4>Set a sheet as selected</h4>
<pre class="code">
Workbook wb = new HSSFWorkbook();
Sheet sheet = wb.createSheet("row sheet");
sheet.setSelected(true);
</pre>
-
-
-
-<a name="Zoom"></a>
- <a name="Set+the+zoom+magnification"></a>
-<div class="h2">
-<h2>Set the zoom magnification<a title="Permanent link" class="headerlink" href="#Set+the+zoom+magnification">#</a>
-</h2>
-</div>
-
+<a name="Zoom" id="Zoom"></a><a name="Set+the+zoom+magnification"></a>
+<h4>Set the zoom magnification</h4>
<p>
The zoom is expressed as a fraction. For example to
express a zoom of 75% use 3 for the numerator and
4 for the denominator.
</p>
-
<pre class="code">
Workbook wb = new HSSFWorkbook();
Sheet sheet1 = wb.createSheet("new sheet");
sheet1.setZoom(75); // 75 percent magnification
</pre>
-
-
-
-<a name="Splits"></a>
- <a name="Splits+and+freeze+panes"></a>
-<div class="h2">
-<h2>Splits and freeze panes<a title="Permanent link" class="headerlink" href="#Splits+and+freeze+panes">#</a>
-</h2>
-</div>
-
+<a name="Splits" id="Splits"></a><a name="Splits+and+freeze+panes"></a>
+<h4>Splits and freeze panes</h4>
<p>
There are two types of panes you can create; freeze panes and split panes.
</p>
-
<p>
A freeze pane is split by columns and rows. You create
a freeze pane using the following mechanism:
</p>
-
<p>
sheet1.createFreezePane( 3, 2, 3, 2 );
</p>
-
<p>
The first two parameters are the columns and rows you
wish to split by. The second two parameters indicate
the cells that are visible in the bottom right quadrant.
</p>
-
<p>
Split panes appear differently. The split area is
@@ -1429,16 +1289,13 @@ Examples:
occurs at the pixel level and the user is able to
adjust the split by dragging it to a new position.
</p>
-
<p>
Split panes are created with the following call:
</p>
-
<p>
sheet2.createSplitPane( 2000, 2000, 0, 0, Sheet.PANE_LOWER_LEFT );
</p>
-
<p>
The first parameter is the x position of the split.
@@ -1446,13 +1303,11 @@ Examples:
seems to equate to a pixel. The second parameter is
the y position of the split. Again in 1/20th of a point.
</p>
-
<p>
The last parameter indicates which pane currently has
the focus. This will be one of Sheet.PANE_LOWER_LEFT,
PANE_LOWER_RIGHT, PANE_UPPER_RIGHT or PANE_UPPER_LEFT.
</p>
-
<pre class="code">
Workbook wb = new HSSFWorkbook();
Sheet sheet1 = wb.createSheet("new sheet");
@@ -1473,22 +1328,13 @@ Examples:
wb.write(fileOut);
}
</pre>
-
-
-
-<a name="Repeating"></a>
- <a name="Repeating+rows+and+columns"></a>
-<div class="h2">
-<h2>Repeating rows and columns<a title="Permanent link" class="headerlink" href="#Repeating+rows+and+columns">#</a>
-</h2>
-</div>
-
+<a name="Repeating" id="Repeating"></a><a name="Repeating+rows+and+columns"></a>
+<h4>Repeating rows and columns</h4>
<p>
It's possible to set up repeating rows and columns in
your printouts by using the setRepeatingRows() and
setRepeatingColumns() methods in the Sheet class.
</p>
-
<p>
These methods expect a CellRangeAddress parameter
which specifies the range for the rows or columns to
@@ -1502,7 +1348,6 @@ Examples:
If the parameter is null, the repeating rows or columns
will be removed.
</p>
-
<pre class="code">
Workbook wb = new HSSFWorkbook(); // or new XSSFWorkbook();
Sheet sheet1 = wb.createSheet("Sheet1");
@@ -1517,19 +1362,11 @@ Examples:
wb.write(fileOut);
}
</pre>
-
-
-<a name="HeaderFooter"></a>
- <a name="Headers+and+Footers"></a>
-<div class="h2">
-<h2>Headers and Footers<a title="Permanent link" class="headerlink" href="#Headers+and+Footers">#</a>
-</h2>
-</div>
-
+<a name="HeaderFooter" id="HeaderFooter"></a><a name="Headers+and+Footers"></a>
+<h4>Headers and Footers</h4>
<p>
Example is for headers but applies directly to footers.
</p>
-
<pre class="code">
Workbook wb = new HSSFWorkbook();
Sheet sheet = wb.createSheet("new sheet");
@@ -1544,21 +1381,13 @@ Examples:
wb.write(fileOut);
}
</pre>
-
-
-<a name="XSSFHeaderFooter"></a>
- <a name="XSSF+Enhancement+for+Headers+and+Footers"></a>
-<div class="h2">
-<h2>XSSF Enhancement for Headers and Footers<a title="Permanent link" class="headerlink" href="#XSSF+Enhancement+for+Headers+and+Footers">#</a>
-</h2>
-</div>
-
+<a name="XSSFHeaderFooter" id="XSSFHeaderFooter"></a><a name="XSSF+Enhancement+for+Headers+and+Footers"></a>
+<h4>XSSF Enhancement for Headers and Footers</h4>
<p>
Example is for headers but applies directly to footers. Note, the above example for
basic headers and footers applies to XSSF Workbooks as well as HSSF Workbooks. The HSSFHeader
stuff does not work for XSSF Workbooks.
- </p>
-
+ </p>
<p>
XSSF has the ability to handle First page headers and footers, as well as Even/Odd
headers and footers. All Header/Footer Property flags can be handled in XSSF as well.
@@ -1570,7 +1399,6 @@ Examples:
the even header/footer not existing. If the first page property does not exist, that is
the same as the first page header/footer not existing.
</p>
-
<pre class="code">
Workbook wb = new XSSFWorkbook();
XSSFSheet sheet = (XSSFSheet) wb.createSheet("new sheet");
@@ -1604,16 +1432,8 @@ Examples:
wb.write(fileOut);
}
</pre>
-
-
-
-<a name="DrawingShapes"></a>
- <a name="Drawing+Shapes"></a>
-<div class="h2">
-<h2>Drawing Shapes<a title="Permanent link" class="headerlink" href="#Drawing+Shapes">#</a>
-</h2>
-</div>
-
+<a name="DrawingShapes" id="DrawingShapes"></a><a name="Drawing+Shapes"></a>
+<h4>Drawing Shapes</h4>
<p>
POI supports drawing shapes using the Microsoft Office
drawing tools. Shapes on a sheet are organized in a
@@ -1626,12 +1446,10 @@ Examples:
records alone in the sheet unless you make a call to
this method.
</p>
-
<p>
To create a shape you have to go through the following
steps:
</p>
-
<ol>
<li>Create the patriarch.</li>
@@ -1646,29 +1464,24 @@ Examples:
line thickness, etc...)</li>
</ol>
-
<pre class="code">
HSSFPatriarch patriarch = sheet.createDrawingPatriarch();
a = new HSSFClientAnchor( 0, 0, 1023, 255, (short) 1, 0, (short) 1, 0 );
HSSFSimpleShape shape1 = patriarch.createSimpleShape(a1);
shape1.setShapeType(HSSFSimpleShape.OBJECT_TYPE_LINE);
</pre>
-
<p>
Text boxes are created using a different call:
</p>
-
<pre class="code">
HSSFTextbox textbox1 = patriarch.createTextbox(
new HSSFClientAnchor(0,0,0,0,(short)1,1,(short)2,2));
textbox1.setString(new HSSFRichTextString("This is a test") );
</pre>
-
<p>
It's possible to use different fonts to style parts of
the text in the textbox. Here's how:
</p>
-
<pre class="code">
HSSFFont font = wb.createFont();
font.setItalic(true);
@@ -1677,28 +1490,23 @@ Examples:
string.applyFont(2,5,font);
textbox.setString(string );
</pre>
-
<p>
Just as can be done manually using Excel, it is possible
to group shapes together. This is done by calling
<span class="codefrag">createGroup()</span> and then creating the shapes
using those groups.
</p>
-
<p>
It's also possible to create groups within groups.
</p>
-
-<div class="frame warning">
+<div class="warning">
<div class="label">Warning</div>
<div class="content">Any group you create should contain at least two
other shapes or subgroups.</div>
</div>
-
<p>
Here's how to create a shape group:
</p>
-
<pre class="code">
// Create a shape group.
HSSFShapeGroup group = patriarch.createGroup(
@@ -1711,7 +1519,6 @@ Examples:
HSSFSimpleShape shape2 = group.createShape(new HSSFChildAnchor(1,200,400,600));
shape2.setShapeType(HSSFSimpleShape.OBJECT_TYPE_LINE);
</pre>
-
<p>
If you're being observant you'll noticed that the shapes
that are added to the group use a new type of anchor:
@@ -1721,31 +1528,20 @@ Examples:
(0,0,1023,255) but you are able to change it as desired.
Here's how:
</p>
-
<pre class="code">
myGroup.setCoordinates(10,10,20,20); // top-left, bottom-right
</pre>
-
<p>
If you create a group within a group it's also going
to have it's own coordinate space.
</p>
-
-
-
-<a name="StylingShapes"></a>
- <a name="Styling+Shapes"></a>
-<div class="h2">
-<h2>Styling Shapes<a title="Permanent link" class="headerlink" href="#Styling+Shapes">#</a>
-</h2>
-</div>
-
+<a name="StylingShapes" id="StylingShapes"></a><a name="Styling+Shapes"></a>
+<h4>Styling Shapes</h4>
<p>
By default shapes can look a little plain. It's possible
to apply different styles to the shapes however. The
sorts of things that can currently be done are:
</p>
-
<ul>
<li>Change the fill color.</li>
@@ -1759,11 +1555,9 @@ Examples:
<li>Change the line color.</li>
</ul>
-
<p>
Here's an examples of how this is done:
</p>
-
<pre class="code">
HSSFSimpleShape s = patriarch.createSimpleShape(a);
s.setShapeType(HSSFSimpleShape.OBJECT_TYPE_OVAL);
@@ -1772,15 +1566,8 @@ Examples:
s.setLineWidth(HSSFShape.LINEWIDTH_ONE_PT * 3);
s.setLineStyle(HSSFShape.LINESTYLE_DOTSYS);
</pre>
-
-
-<a name="Graphics2d"></a>
- <a name="Shapes+and+Graphics2d"></a>
-<div class="h2">
-<h2>Shapes and Graphics2d<a title="Permanent link" class="headerlink" href="#Shapes+and+Graphics2d">#</a>
-</h2>
-</div>
-
+<a name="Graphics2d" id="Graphics2d"></a><a name="Shapes+and+Graphics2d"></a>
+<h4>Shapes and Graphics2d</h4>
<p>
While the native POI shape drawing commands are the
recommended way to draw shapes in a shape it's sometimes
@@ -1788,8 +1575,7 @@ Examples:
external libraries. With this in mind we created some
wrappers for <span class="codefrag">Graphics</span> and <span class="codefrag">Graphics2d</span>.
</p>
-
-<div class="frame warning">
+<div class="warning">
<div class="label">Warning</div>
<div class="content">
It's important to not however before continuing that
@@ -1799,12 +1585,10 @@ Examples:
still a square peg in a round hole.
</div>
</div>
-
<p>
All Graphics commands are issued into an <span class="codefrag">HSSFShapeGroup</span>.
Here's how it's done:
</p>
-
<pre class="code">
a = new HSSFClientAnchor( 0, 0, 1023, 255, (short) 1, 0, (short) 1, 0 );
group = patriarch.createGroup( a );
@@ -1814,7 +1598,6 @@ Examples:
g2d = new EscherGraphics2d( g );
drawChemicalStructure( g2d );
</pre>
-
<p>
The first thing we do is create the group and set it's coordinates
to match what we plan to draw. Next we calculate a reasonable
@@ -1824,7 +1607,6 @@ Examples:
the graphics object we created. Finally we call a routine
that draws into the EscherGraphics2d object.
</p>
-
<p>
The vertical points per pixel deserves some more explanation.
One of the difficulties in converting Graphics calls
@@ -1836,31 +1618,26 @@ Examples:
using different fonts on different platforms or even within the same
platform.
</p>
-
<p>
Because of this constraint we've had to implement the concept of a
verticalPointsPerPixel. This the amount the font should be scaled by when
you issue commands such as drawString(). To calculate this value
use the follow formula:
</p>
-
<pre class="code">
multipler = groupHeightInPoints / heightOfGroup
</pre>
-
<p>
The height of the group is calculated fairly simply by calculating the
difference between the y coordinates of the bounding box of the shape. The
height of the group can be calculated by using a convenience called
<span class="codefrag">HSSFClientAnchor.getAnchorHeightInPoints()</span>.
</p>
-
<p>
Many of the functions supported by the graphics classes
are not complete. Here's some of the functions that are known
to work.
</p>
-
<ul>
<li>fillRect()</li>
@@ -1876,27 +1653,17 @@ Examples:
<li>clearRect()</li>
</ul>
-
<p>
Functions that are not supported will return and log a message
using the POI logging infrastructure (disabled by default).
</p>
-
-
-<a name="Outlining"></a>
- <a name="Outlining-N10409"></a>
-<div class="h2">
-<h2>Outlining<a title="Permanent link" class="headerlink" href="#Outlining-N10409">#</a>
-</h2>
-</div>
-
-
+<a name="Outlining" id="Outlining"></a><a name="Outlining-N1040A"></a>
+<h4>Outlining</h4>
<p>
Outlines are great for grouping sections of information
together and can be added easily to columns and rows
using the POI API. Here's how:
</p>
-
<pre class="code">
Workbook wb = new HSSFWorkbook();
Sheet sheet1 = wb.createSheet("new sheet");
@@ -1913,38 +1680,28 @@ Examples:
wb.write(fileOut);
}
</pre>
-
<p>
To collapse (or expand) an outline use the following calls:
</p>
-
<pre class="code">
sheet1.setRowGroupCollapsed( 7, true );
sheet1.setColumnGroupCollapsed( 4, true );
</pre>
-
<p>
The row/column you choose should contain an already
created group. It can be anywhere within the group.
</p>
-
-
-
-
-<a name="Images"></a>
- <a name="Images-N10426"></a>
-<div class="h3">
-<h3>Images<a title="Permanent link" class="headerlink" href="#Images-N10426">#</a>
-</h3>
</div>
-
-
+
+<a name="Images" id="Images"></a>
+ <a name="Images-N10427"></a>
+<h2 class="boxed">Images</h2>
+<div class="section">
<p>
Images are part of the drawing support. To add an image just
call <span class="codefrag">createPicture()</span> on the drawing patriarch.
At the time of writing the following types are supported:
</p>
-
<ul>
<li>PNG</li>
@@ -1954,12 +1711,10 @@ Examples:
<li>DIB</li>
</ul>
-
<p>
It should be noted that any existing drawings may be erased
once you add a image to a sheet.
</p>
-
<pre class="code">
//create a new workbook
Workbook wb = new XSSFWorkbook(); //or new HSSFWorkbook();
@@ -1996,16 +1751,13 @@ Examples:
wb.write(fileOut);
}
</pre>
-
-<div class="frame warning">
+<div class="warning">
<div class="label">Warning</div>
<div class="content">
Picture.resize() works only for JPEG and PNG. Other formats are not yet supported.
</div>
</div>
-
<p>Reading images from a workbook:</p>
-
<pre class="code">
List lst = workbook.getAllPictures();
@@ -2020,16 +1772,12 @@ Examples:
}
}
</pre>
+</div>
-
-<a name="NamedRanges"></a>
+<a name="NamedRanges" id="NamedRanges"></a>
<a name="Named+Ranges+and+Named+Cells"></a>
-<div class="h3">
-<h3>Named Ranges and Named Cells<a title="Permanent link" class="headerlink" href="#Named+Ranges+and+Named+Cells">#</a>
-</h3>
-</div>
-
-
+<h2 class="boxed">Named Ranges and Named Cells</h2>
+<div class="section">
<p>
Named Range is a way to refer to a group of cells by a name. Named Cell is a
degenerate case of Named Range in that the 'group of cells' contains exactly one
@@ -2037,18 +1785,15 @@ Examples:
When working with Named Ranges, the classes <span class="codefrag">org.apache.poi.ss.util.CellReference</span>
and <span class="codefrag">org.apache.poi.ss.util.AreaReference</span> are used.
</p>
-
<p>
Note: Using relative values like 'A1:B1' can lead to unexpected moving of
the cell that the name points to when working with the workbook in Microsoft Excel,
usually using absolute references like '$A$1:$B$1' avoids this, see also
<a href="https://superuser.com/a/1031047/126954">this discussion</a>.
</p>
-
<p>
Creating Named Range / Named Cell
</p>
-
<pre class="code">
// setup code
String sname = "TestSheet", cname = "TestName", cvalue = "TestVal";
@@ -2079,11 +1824,9 @@ Examples:
namedCel4.setNameName("my_sum");
namedCel4.setRefersToFormula("SUM(" + sname + "!$I$2:$I$6)");
</pre>
-
<p>
Reading from Named Range / Named Cell
</p>
-
<pre class="code">
// setup code
String cname = "TestName";
@@ -2103,11 +1846,9 @@ Examples:
// extract the cell contents based on cell type etc.
}
</pre>
-
<p>
Reading from non-contiguous Named Ranges
</p>
-
<pre class="code">
// Setup code
String cname = "TestName";
@@ -2135,7 +1876,6 @@ Examples:
}
}
</pre>
-
<p>
Note, when a cell is deleted, Excel does not delete the
attached named range. As result, workbook can contain
@@ -2143,7 +1883,6 @@ Examples:
You should check the validity of a reference before
constructing AreaReference
</p>
-
<pre class="code">
if(name.isDeleted()){
//named range points to a deleted cell.
@@ -2151,22 +1890,18 @@ Examples:
AreaReference ref = new AreaReference(name.getRefersToFormula());
}
</pre>
-
-
-<a name="CellComments"></a>
- <a name="Cell+Comments+-+HSSF+and+XSSF"></a>
-<div class="h3">
-<h3>Cell Comments - HSSF and XSSF<a title="Permanent link" class="headerlink" href="#Cell+Comments+-+HSSF+and+XSSF">#</a>
-</h3>
</div>
+<a name="CellComments" id="CellComments"></a>
+ <a name="Cell+Comments+-+HSSF+and+XSSF"></a>
+<h2 class="boxed">Cell Comments - HSSF and XSSF</h2>
+<div class="section">
<p>
A comment is a rich text note that is attached to &
associated with a cell, separate from other cell content.
Comment content is stored separate from the cell, and is displayed in a drawing object (like a text box)
that is separate from, but associated with, a cell
</p>
-
<pre class="code">
Workbook wb = new XSSFWorkbook(); //or new HSSFWorkbook();
@@ -2204,11 +1939,9 @@ Examples:
wb.close();
</pre>
-
<p>
Reading cell comments
</p>
-
<pre class="code">
Cell cell = sheet.get(3).getColumn(1);
Comment comment = cell.getCellComment();
@@ -2219,10 +1952,7 @@ Examples:
// alternatively you can retrieve cell comments by (row, column)
comment = sheet.getCellComment(3, 1);
</pre>
-
-
<p>To get all the comments on a sheet:</p>
-
<pre class="code">
Map<CellAddress, Comment> comments = sheet.getCellComments();
Comment commentA1 = comments.get(new CellAddress(0, 0));
@@ -2234,28 +1964,23 @@ Examples:
"[" + comment.getAuthor() + "] " + comment.getString().getString());
}
</pre>
-
+</div>
-<a name="Autofit"></a>
+<a name="Autofit" id="Autofit"></a>
<a name="Adjust+column+width+to+fit+the+contents"></a>
-<div class="h3">
-<h3>Adjust column width to fit the contents<a title="Permanent link" class="headerlink" href="#Adjust+column+width+to+fit+the+contents">#</a>
-</h3>
-</div>
-
+<h2 class="boxed">Adjust column width to fit the contents</h2>
+<div class="section">
<pre class="code">
Sheet sheet = workbook.getSheetAt(0);
sheet.autoSizeColumn(0); //adjust width of the first column
sheet.autoSizeColumn(1); //adjust width of the second column
</pre>
-
<p>
For SXSSFWorkbooks only, because the random access window is likely to exclude most of the rows
in the worksheet, which are needed for computing the best-fit width of a column, the columns must
be tracked for auto-sizing prior to flushing any rows.
</p>
-
<pre class="code">
SXSSFWorkbook workbook = new SXSSFWorkbook();
SXSSFSheet sheet = workbook.createSheet();
@@ -2280,15 +2005,13 @@ Examples:
// Auto-size the columns.
sheet.autoSizeColumn(0);
sheet.autoSizeColumn(1);
- </pre>
-
+ </pre>
<p>
Note, that Sheet#autoSizeColumn() does not evaluate formula cells,
the width of formula cells is calculated based on the cached formula result.
If your workbook has many formulas then it is a good idea to evaluate them before auto-sizing.
</p>
-
-<div class="frame warning">
+<div class="warning">
<div class="label">Warning</div>
<div class="content">
To calculate column width Sheet.autoSizeColumn uses Java2D classes
@@ -2299,15 +2022,12 @@ Examples:
available to Java.
</div>
</div>
+</div>
-
-<a name="Hyperlinks"></a>
+<a name="Hyperlinks" id="Hyperlinks"></a>
<a name="How+to+read+hyperlinks"></a>
-<div class="h3">
-<h3>How to read hyperlinks<a title="Permanent link" class="headerlink" href="#How+to+read+hyperlinks">#</a>
-</h3>
-</div>
-
+<h2 class="boxed">How to read hyperlinks</h2>
+<div class="section">
<pre class="code">
Sheet sheet = workbook.getSheetAt(0);
@@ -2317,14 +2037,11 @@ Examples:
System.out.println(link.getAddress());
}
</pre>
-
+</div>
<a name="How+to+create+hyperlinks"></a>
-<div class="h3">
-<h3>How to create hyperlinks<a title="Permanent link" class="headerlink" href="#How+to+create+hyperlinks">#</a>
-</h3>
-</div>
-
+<h2 class="boxed">How to create hyperlinks</h2>
+<div class="section">
<pre class="code">
Workbook wb = new XSSFWorkbook(); //or new HSSFWorkbook();
CreationHelper createHelper = wb.getCreationHelper();
@@ -2384,32 +2101,21 @@ Examples:
wb.close();
</pre>
+</div>
-
-<a name="Validation"></a>
+<a name="Validation" id="Validation"></a>
<a name="Data+Validations"></a>
-<div class="h3">
-<h3>Data Validations<a title="Permanent link" class="headerlink" href="#Data+Validations">#</a>
-</h3>
-</div>
-
+<h2 class="boxed">Data Validations</h2>
+<div class="section">
<p>
As of version 3.8, POI has slightly different syntax to work with data validations with .xls and .xlsx formats.
</p>
-
<a name="hssf.usermodel+%28binary+.xls+format%29"></a>
-<div class="h4">
-<h4>hssf.usermodel (binary .xls format)<a title="Permanent link" class="headerlink" href="#hssf.usermodel+%28binary+.xls+format%29">#</a>
-</h4>
-</div>
-
-
+<h3 class="boxed">hssf.usermodel (binary .xls format)</h3>
<p>
<strong>Check the value a user enters into a cell against one or more predefined value(s).</strong>
</p>
-
<p>The following code will limit the value the user can enter into cell A1 to one of three integer values, 10, 20 or 30.</p>
-
<pre class="code">
HSSFWorkbook workbook = new HSSFWorkbook();
HSSFSheet sheet = workbook.createSheet("Data Validation");
@@ -2422,13 +2128,10 @@ Examples:
dataValidation.setSuppressDropDownArrow(true);
sheet.addValidationData(dataValidation);
</pre>
-
<p>
<strong> Drop Down Lists:</strong>
</p>
-
<p>This code will do the same but offer the user a drop down list to select a value from.</p>
-
<pre class="code">
HSSFWorkbook workbook = new HSSFWorkbook();
HSSFSheet sheet = workbook.createSheet("Data Validation");
@@ -2441,106 +2144,78 @@ Examples:
dataValidation.setSuppressDropDownArrow(false);
sheet.addValidationData(dataValidation);
</pre>
-
<p>
<strong>Messages On Error:</strong>
</p>
-
<p>To create a message box that will be shown to the user if the value they enter is invalid.</p>
-
<pre class="code">
dataValidation.setErrorStyle(DataValidation.ErrorStyle.STOP);
dataValidation.createErrorBox("Box Title", "Message Text");
</pre>
-
<p>Replace 'Box Title' with the text you wish to display in the message box's title bar
and 'Message Text' with the text of your error message.</p>
-
<p>
<strong>Prompts:</strong>
</p>
-
<p>To create a prompt that the user will see when the cell containing the data validation receives focus</p>
-
<pre class="code">
dataValidation.createPromptBox("Title", "Message Text");
dataValidation.setShowPromptBox(true);
</pre>
-
<p>The text encapsulated in the first parameter passed to the createPromptBox() method will appear emboldened
and as a title to the prompt whilst the second will be displayed as the text of the message.
The createExplicitListConstraint() method can be passed and array of String(s) containing interger, floating point, dates or text values.</p>
-
-
<p>
<strong>Further Data Validations:</strong>
</p>
-
<p>To obtain a validation that would check the value entered was, for example, an integer between 10 and 100,
use the DVConstraint.createNumericConstraint(int, int, String, String) factory method.</p>
-
<pre class="code">
dvConstraint = DVConstraint.createNumericConstraint(
DVConstraint.ValidationType.INTEGER,
DVConstraint.OperatorType.BETWEEN, "10", "100");
</pre>
-
<p>Look at the javadoc for the other validation and operator types; also note that not all validation
types are supported for this method. The values passed to the two String parameters can be formulas; the '=' symbol is used to denote a formula</p>
-
<pre class="code">
dvConstraint = DVConstraint.createNumericConstraint(
DVConstraint.ValidationType.INTEGER,
DVConstraint.OperatorType.BETWEEN, "=SUM(A1:A3)", "100");
</pre>
-
<p>It is not possible to create a drop down list if the createNumericConstraint() method is called,
the setSuppressDropDownArrow(false) method call will simply be ignored.</p>
-
<p>Date and time constraints can be created by calling the createDateConstraint(int, String, String, String)
or the createTimeConstraint(int, String, String). Both are very similar to the above and are explained in the javadoc. </p>
-
<p>
<strong>Creating Data Validations From Spreadsheet Cells.</strong>
</p>
-
<p>The contents of specific cells can be used to provide the values for the data validation
and the DVConstraint.createFormulaListConstraint(String) method supports this.
To specify that the values come from a contiguous range of cells do either of the following:</p>
-
<pre class="code">
dvConstraint = DVConstraint.createFormulaListConstraint("$A$1:$A$3");
</pre>
-
<p>or</p>
-
<pre class="code">
Name namedRange = workbook.createName();
namedRange.setNameName("list1");
namedRange.setRefersToFormula("$A$1:$A$3");
dvConstraint = DVConstraint.createFormulaListConstraint("list1");
</pre>
-
<p>and in both cases the user will be able to select from a drop down list containing the values from cells A1, A2 and A3.</p>
-
<p>The data does not have to be as the data validation. To select the data from a different sheet however, the sheet
- must be given a name when created and that name should be used in the formula. So assuming the existence of a sheet named 'Data Sheet' this will work:</p>
-
+ must be given a name when created and that name should be used in the formula. So assuming the existence of a sheet named 'Data Sheet' this will work:</p>
<pre class="code">
Name namedRange = workbook.createName();
namedRange.setNameName("list1");
namedRange.setRefersToFormula("'Data Sheet'!$A$1:$A$3");
dvConstraint = DVConstraint.createFormulaListConstraint("list1");
</pre>
-
<p>as will this:</p>
-
<pre class="code">
dvConstraint = DVConstraint.createFormulaListConstraint("'Data Sheet'!$A$1:$A$3");
</pre>
-
<p>whilst this will not:</p>
-
<pre class="code">
Name namedRange = workbook.createName();
namedRange.setNameName("list1");
@@ -2551,15 +2226,8 @@ Examples:
<pre class="code">
dvConstraint = DVConstraint.createFormulaListConstraint("'Sheet1'!$A$1:$A$3");
</pre>
-
-
<a name="xssf.usermodel+%28.xlsx+format%29"></a>
-<div class="h4">
-<h4>xssf.usermodel (.xlsx format)<a title="Permanent link" class="headerlink" href="#xssf.usermodel+%28.xlsx+format%29">#</a>
-</h4>
-</div>
-
-
+<h3 class="boxed">xssf.usermodel (.xlsx format)</h3>
<p>
Data validations work similarly when you are creating an xml based, SpreadsheetML,
workbook file; but there are differences. Explicit casts are required, for example,
@@ -2567,12 +2235,9 @@ in a few places as much of the support f
built into the unifying ss stream, of which more later. Other differences are
noted with comments in the code.
</p>
-
-
<p>
<strong>Check the value the user enters into a cell against one or more predefined value(s).</strong>
</p>
-
<pre class="code">
XSSFWorkbook workbook = new XSSFWorkbook();
XSSFSheet sheet = workbook.createSheet("Data Validation");
@@ -2594,14 +2259,10 @@ noted with comments in the code.
validation.setShowErrorBox(true);
sheet.addValidationData(validation);
</pre>
-
-
<p>
<strong>Drop Down Lists:</strong>
</p>
-
<p>This code will do the same but offer the user a drop down list to select a value from.</p>
-
<pre class="code">
XSSFWorkbook workbook = new XSSFWorkbook();
XSSFSheet sheet = workbook.createSheet("Data Validation");
@@ -2614,32 +2275,23 @@ noted with comments in the code.
validation.setShowErrorBox(true);
sheet.addValidationData(validation);
</pre>
-
<p>Note that the call to the setSuppressDropDowmArrow() method can either be simply excluded or replaced with:</p>
-
<pre class="code">
validation.setSuppressDropDownArrow(true);
</pre>
-
-
<p>
<strong>Prompts and Error Messages:</strong>
</p>
-
<p>
These both exactly mirror the hssf.usermodel so please refer to the 'Messages On Error:' and 'Prompts:' sections above.
</p>
-
-
<p>
<strong>Further Data Validations:</strong>
</p>
-
<p>
To obtain a validation that would check the value entered was, for example,
an integer between 10 and 100, use the XSSFDataValidationHelper(s) createNumericConstraint(int, int, String, String) factory method.
</p>
-
<pre class="code">
XSSFDataValidationConstraint dvConstraint = (XSSFDataValidationConstraint)
@@ -2648,12 +2300,10 @@ an integer between 10 and 100, use the X
XSSFDataValidationConstraint.OperatorType.BETWEEN,
"10", "100");
</pre>
-
<p>
The values passed to the final two String parameters can be formulas; the '=' symbol is used to denote a formula.
Thus, the following would create a validation the allows values only if they fall between the results of summing two cell ranges
</p>
-
<pre class="code">
XSSFDataValidationConstraint dvConstraint = (XSSFDataValidationConstraint)
dvHelper.createNumericConstraint(
@@ -2661,42 +2311,32 @@ Thus, the following would create a valid
XSSFDataValidationConstraint.OperatorType.BETWEEN,
"=SUM(A1:A10)", "=SUM(B24:B27)");
</pre>
-
<p>
It is not possible to create a drop down list if the createNumericConstraint() method is called,
the setSuppressDropDownArrow(true) method call will simply be ignored.
</p>
-
<p>
Please check the javadoc for other constraint types as examples for those will not be included here.
There are, for example, methods defined on the XSSFDataValidationHelper class allowing you to create
the following types of constraint; date, time, decimal, integer, numeric, formula, text length and custom constraints.
</p>
-
<p>
<strong>Creating Data Validations From Spread Sheet Cells:</strong>
</p>
-
<p>
One other type of constraint not mentioned above is the formula list constraint.
It allows you to create a validation that takes it value(s) from a range of cells. This code
</p>
-
<pre class="code">
XSSFDataValidationConstraint dvConstraint = (XSSFDataValidationConstraint)
dvHelper.createFormulaListConstraint("$A$1:$F$1");
</pre>
-
-
<p>
would create a validation that took it's values from cells in the range A1 to F1.
</p>
-
<p>
The usefulness of this technique can be extended if you use named ranges like this;
</p>
-
-
<pre class="code">
XSSFName name = workbook.createName();
name.setNameName("data");
@@ -2712,45 +2352,33 @@ The usefulness of this technique can be
validation.setShowErrorBox(true);
sheet.addValidationData(validation);
</pre>
-
<p>
OpenOffice Calc has slightly different rules with regard to the scope of names.
Excel supports both Workbook and Sheet scope for a name but Calc does not, it seems only to support Sheet scope for a name.
Thus it is often best to fully qualify the name for the region or area something like this;
</p>
-
<pre class="code">
XSSFName name = workbook.createName();
name.setNameName("data");
name.setRefersToFormula("'Data Validation'!$B$1:$F$1");
....
</pre>
-
<p>
This does open a further, interesting opportunity however and that is to place all of the data for the validation(s) into named ranges of cells on a hidden sheet within the workbook. These ranges can then be explicitly identified in the setRefersToFormula() method argument.
</p>
-
-
<a name="ss.usermodel"></a>
-<div class="h4">
-<h4>ss.usermodel<a title="Permanent link" class="headerlink" href="#ss.usermodel">#</a>
-</h4>
-</div>
-
+<h3 class="boxed">ss.usermodel</h3>
<p>
The classes within the ss.usermodel package allow developers to create code that can be used
to generate both binary (.xls) and SpreadsheetML (.xlsx) workbooks.
</p>
-
<p>
The techniques used to create data validations share much in common with the xssf.usermodel examples above.
As a result just one or two examples will be presented here.
</p>
-
<p>
<strong>Check the value the user enters into a cell against one or more predefined value(s).</strong>
</p>
-
<pre class="code">
Workbook workbook = new XSSFWorkbook(); // or new HSSFWorkbook
Sheet sheet = workbook.createSheet("Data Validation");
@@ -2776,16 +2404,10 @@ As a result just one or two examples wil
}
sheet.addValidationData(validation);
</pre>
-
-
<p>
<strong>Drop Down Lists:</strong>
</p>
-
-
<p>This code will do the same but offer the user a drop down list to select a value from.</p>
-
-
<pre class="code">
Workbook workbook = new XSSFWorkbook(); // or new HSSFWorkbook
Sheet sheet = workbook.createSheet("Data Validation");
@@ -2811,16 +2433,12 @@ As a result just one or two examples wil
}
sheet.addValidationData(validation);
</pre>
-
-
<p>
<strong>Prompts and Error Messages:</strong>
</p>
-
<p>
These both exactly mirror the hssf.usermodel so please refer to the 'Messages On Error:' and 'Prompts:' sections above.
</p>
-
<p>
As the differences between the ss.usermodel and xssf.usermodel examples are small -
restricted largely to the way the DataValidationHelper is obtained, the lack of any
@@ -2828,34 +2446,28 @@ need to explicitly cast data types and t
the hssf and xssf interpretation of the setSuppressDropDowmArrow() method,
no further examples will be included in this section.
</p>
-
<p>
<strong>Advanced Data Validations.</strong>
</p>
-
<p>
<strong>Dependent Drop Down Lists.</strong>
</p>
-
<p>
In some cases, it may be necessary to present to the user a sheet which contains more than one drop down list.
Further, the choice the user makes in one drop down list may affect the options that are presented to them in
the second or subsequent drop down lists. One technique that may be used to implement this behaviour will now be explained.
</p>
-
<p>
There are two keys to the technique; one is to use named areas or regions of cells to hold the data for the drop down lists,
the second is to use the INDIRECT() function to convert between the name and the actual addresses of the cells.
In the example section there is a complete working example- called LinkedDropDownLists.java -
that demonstrates how to create linked or dependent drop down lists. Only the more relevant points are explained here.
</p>
-
<p>
To create two drop down lists where the options shown in the second depend upon the selection made in the first,
begin by creating a named region of cells to hold all of the data for populating the first drop down list.
Next, create a data validation that will look to this named area for its data, something like this;
</p>
-
<pre class="code">
CellRangeAddressList addressList = new CellRangeAddressList(0, 0, 0, 0);
DataValidationHelper dvHelper = sheet.getDataValidationHelper();
@@ -2865,26 +2477,20 @@ Next, create a data validation that will
dvConstraint, addressList);
sheet.addValidationData(validation);
</pre>
-
<p>
Note that the name of the area - in the example above it is 'CHOICES' -
is simply passed to the createFormulaListConstraint() method. This is sufficient
to cause Excel to populate the drop down list with data from that named region.
</p>
-
<p>
Next, for each of the options the user could select in the first drop down list,
create a matching named region of cells. The name of that region should match the
text the user could select in the first drop down list. Note, in the example,
all upper case letters are used in the names of the regions of cells.
</p>
-
-
<p>
Now, very similar code can be used to create a second, linked, drop down list;
</p>
-
-
<pre class="code">
CellRangeAddressList addressList = new CellRangeAddressList(0, 0, 1, 1);
DataValidationConstraint dvConstraint = dvHelper.createFormulaListConstraint(
@@ -2893,8 +2499,6 @@ Now, very similar code can be used to cr
dvConstraint, addressList);
sheet.addValidationData(validation);
</pre>
-
-
<p>
The key here is in the following Excel function - INDIRECT(UPPER($A$1)) - which is used to populate the second,
linked, drop down list. Working from the inner-most pair of brackets, it instructs Excel to look
@@ -2902,23 +2506,17 @@ at the contents of cell A1, to convert w
in the names of each region - and then convert this name into the addresses of those cells that contain
the data to populate another drop down list.
</p>
-
-
+</div>
-<a name="Embedded"></a>
+<a name="Embedded" id="Embedded"></a>
<a name="Embedded+Objects"></a>
-<div class="h3">
-<h3>Embedded Objects<a title="Permanent link" class="headerlink" href="#Embedded+Objects">#</a>
-</h3>
-</div>
-
+<h2 class="boxed">Embedded Objects</h2>
+<div class="section">
<p>It is possible to perform more detailed processing of an embedded Excel, Word or PowerPoint document,
or to work with any other type of embedded object.</p>
-
<p>
<strong>HSSF:</strong>
</p>
-
<pre class="code">
POIFSFileSystem fs = new POIFSFileSystem(new File("excel_with_embeded.xls"));
HSSFWorkbook workbook = new HSSFWorkbook(fs);
@@ -2951,12 +2549,10 @@ the data to populate another drop down l
}
}
}
- </pre>
-
+ </pre>
<p>
<strong>XSSF:</strong>
</p>
-
<pre class="code">
XSSFWorkbook workbook = new XSSFWorkbook("excel_with_embeded.xlsx");
for (PackagePart pPart : workbook.getAllEmbeddedParts()) {
@@ -2994,33 +2590,27 @@ the data to populate another drop down l
InputStream inputStream = pPart.getInputStream();
}
}
- </pre>
-
+ </pre>
+</div>
-<a name="Autofilter"></a>
+<a name="Autofilter" id="Autofilter"></a>
<p>(Since POI-3.7)</p>
<a name="Autofilters"></a>
-<div class="h3">
-<h3>Autofilters<a title="Permanent link" class="headerlink" href="#Autofilters">#</a>
-</h3>
-</div>
-
+<h2 class="boxed">Autofilters</h2>
+<div class="section">
<pre class="code">
Workbook wb = new HSSFWorkbook(); //or new XSSFWorkbook();
Sheet sheet = wb.createSheet();
sheet.setAutoFilter(CellRangeAddress.valueOf("C5:F200"));
</pre>
-
+</div>
-<a name="ConditionalFormatting"></a>
+<a name="ConditionalFormatting" id="ConditionalFormatting"></a>
<a name="Conditional+Formatting"></a>
-<div class="h3">
-<h3>Conditional Formatting<a title="Permanent link" class="headerlink" href="#Conditional+Formatting">#</a>
-</h3>
-</div>
-
+<h2 class="boxed">Conditional Formatting</h2>
+<div class="section">
<pre class="code">
Workbook workbook = new HSSFWorkbook(); // or new XSSFWorkbook();
Sheet sheet = workbook.createSheet();
@@ -3053,52 +2643,41 @@ the data to populate another drop down l
sheetCF.addConditionalFormatting(regions, cfRules);
</pre>
-
<p> See more examples on Excel conditional formatting in
<a href="http://svn.apache.org/repos/asf/poi/trunk/src/examples/src/org/apache/poi/ss/examples/ConditionalFormats.java">ConditionalFormats.java</a>
</p>
-
-
+</div>
-<a name="Hiding"></a>
+<a name="Hiding" id="Hiding"></a>
<a name="Hiding+and+Un-Hiding+Rows"></a>
-<div class="h3">
-<h3>Hiding and Un-Hiding Rows<a title="Permanent link" class="headerlink" href="#Hiding+and+Un-Hiding+Rows">#</a>
-</h3>
-</div>
-
+<h2 class="boxed">Hiding and Un-Hiding Rows</h2>
+<div class="section">
<p>
Using Excel, it is possible to hide a row on a worksheet by selecting that row (or rows),
right clicking once on the right hand mouse button and selecting 'Hide' from the pop-up menu that appears.
</p>
-
<p>
To emulate this using POI, simply call the setZeroHeight() method on an instance of either
XSSFRow or HSSFRow (the method is defined on the ss.usermodel.Row interface that both classes implement), like this:
- </p>
-
+ </p>
<pre class="code">
Workbook workbook = new XSSFWorkbook(); // OR new HSSFWorkbook()
Sheet sheet = workbook.createSheet(0);
Row row = workbook.createRow(0);
row.setZeroHeight();
</pre>
-
<p>
If the file were saved away to disc now, then the first row on the first sheet would not be visible.
</p>
-
<p>
Using Excel, it is possible to unhide previously hidden rows by selecting the row above and the row below
the one that is hidden and then pressing and holding down the Ctrl key, the Shift and the pressing
the number 9 before releasing them all.
</p>
-
<p>
To emulate this behaviour using POI do something like this:
</p>
-
<pre class="code">
Workbook workbook = WorkbookFactory.create(new File(.......));
Sheet = workbook.getSheetAt(0);
@@ -3110,35 +2689,28 @@ the data to populate another drop down l
}
}
</pre>
-
<p>
If the file were saved away to disc now, any previously hidden rows on the first sheet of the workbook would now be visible.
</p>
-
<p>
The example illustrates two features. Firstly, that it is possible to unhide a row simply by calling the setZeroHeight()
method and passing the boolean value 'false'. Secondly, it illustrates how to test whether a row is hidden or not.
Simply call the getZeroHeight() method and it will return 'true' if the row is hidden, 'false' otherwise.
</p>
-
+</div>
-<a name="CellProperties"></a>
+<a name="CellProperties" id="CellProperties"></a>
<a name="Setting+Cell+Properties"></a>
-<div class="h3">
-<h3>Setting Cell Properties<a title="Permanent link" class="headerlink" href="#Setting+Cell+Properties">#</a>
-</h3>
-</div>
-
+<h2 class="boxed">Setting Cell Properties</h2>
+<div class="section">
<p>
Sometimes it is easier or more efficient to create a spreadsheet with basic styles and then apply special styles to certain cells
such as drawing borders around a range of cells or setting fills for a region. CellUtil.setCellProperties lets you do that without creating
a bunch of unnecessary intermediate styles in your spreadsheet.
</p>
-
<p>
Properties are created as a Map and applied to a cell in the following manner.
</p>
-
<pre class="code">
Workbook workbook = new XSSFWorkbook(); // OR new HSSFWorkbook()
Sheet sheet = workbook.createSheet("Sheet1");
@@ -3170,41 +2742,32 @@ the data to populate another drop down l
}
}
</pre>
-
-
<p>
NOTE: This does not replace the properties of the cell, it merges the properties you have put into the Map with the
cell's existing style properties. If a property already exists, it is replaced with the new property. If a property does not
exist, it is added. This method will not remove CellStyle properties.
</p>
-
+</div>
-<a name="DrawingBorders"></a>
+<a name="DrawingBorders" id="DrawingBorders"></a>
<a name="Drawing+Borders"></a>
-<div class="h3">
-<h3>Drawing Borders<a title="Permanent link" class="headerlink" href="#Drawing+Borders">#</a>
-</h3>
-</div>
-
-
+<h2 class="boxed">Drawing Borders</h2>
+<div class="section">
<p>
In Excel, you can apply a set of borders on an entire workbook region at the press of a button. The PropertyTemplate
object simulates this with methods and constants defined to allow drawing top, bottom, left, right, horizontal,
vertical, inside, outside, or all borders around a range of cells. Additional methods allow for applying colors
to the borders.
</p>
-
<p>
It works like this: you create a PropertyTemplate object which is a container for the borders you wish to apply to a
sheet. Then you add borders and colors to the PropertyTemplate, and finally apply it to whichever sheets you need
that set of borders on. You can create multiple PropertyTemplate objects and apply them to a single sheet, or you can
apply the same PropertyTemplate object to multiple sheets. It is just like a preprinted form.
</p>
-
<p>
Enums:
</p>
-
<dl>
<dt>BorderStyle</dt>
@@ -3225,7 +2788,6 @@ the data to populate another drop down l
</dd>
</dl>
-
<pre class="code">
// draw borders (three 3x3 grids)
PropertyTemplate pt = new PropertyTemplate();
@@ -3258,32 +2820,25 @@ the data to populate another drop down l
Sheet sh = wb.createSheet("Sheet1");
pt.applyBorders(sh);
</pre>
-
<p>
NOTE: The last pt.drawBorders() call removes the borders from the range by using BorderStyle.NONE. Like
[... 152 lines stripped ...]
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@poi.apache.org
For additional commands, e-mail: commits-help@poi.apache.org