poi-commits mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From ye...@apache.org
Subject svn commit: r1423805 [16/18] - in /poi/site/publish: ./ hdgf/ hmef/ hpbf/ hpsf/ hsmf/ hwpf/ images/ oxml4j/ poifs/ poifs/images/ resources/ resources/images/ skin/ skin/css/ skin/images/ slideshow/ spreadsheet/ spreadsheet/images/
Date Wed, 19 Dec 2012 09:27:39 GMT
Added: poi/site/publish/spreadsheet/quick-guide.html
URL: http://svn.apache.org/viewvc/poi/site/publish/spreadsheet/quick-guide.html?rev=1423805&view=auto
==============================================================================
--- poi/site/publish/spreadsheet/quick-guide.html (added)
+++ poi/site/publish/spreadsheet/quick-guide.html Wed Dec 19 09:27:20 2012
@@ -0,0 +1,2876 @@
+<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
+<html>
+<head>
+<META http-equiv="Content-Type" content="text/html; charset=UTF-8">
+<!--*** This is a generated file.  Do not edit.  ***-->
+<link rel="stylesheet" href="../skin/tigris.css" type="text/css">
+<link rel="stylesheet" href="../skin/mysite.css" type="text/css">
+<link rel="stylesheet" href="../skin/site.css" type="text/css">
+<link media="print" rel="stylesheet" href="../skin/print.css" type="text/css">
+<title>Busy Developers' Guide to HSSF and XSSF Features</title>
+</head>
+<body bgcolor="white" class="composite">
+<!--================= start Banner ==================-->
+<div id="banner">
+<table width="100%" cellpadding="8" cellspacing="0" summary="banner" border="0">
+<tbody>
+<tr>
+<!--================= start Group Logo ==================-->
+<td width="50%" align="left">
+<div class="groupLogo">
+<a href="http://poi.apache.org"><img border="0" class="logoImage" alt="Apache POI" src="../resources/images/group-logo.jpg"></a>
+</div>
+</td>
+<!--================= end Group Logo ==================-->
+<!--================= start Project Logo ==================--><td width="50%" align="right">
+<div align="right" class="projectLogo">
+<a href="http://poi.apache.org/"><img border="0" class="logoImage" alt="POI" src="../resources/images/project-logo.jpg"></a>
+</div>
+</td>
+<!--================= end Project Logo ==================-->
+</tr>
+</tbody>
+</table>
+</div>
+<!--================= end Banner ==================-->
+<!--================= start Main ==================-->
+<table width="100%" cellpadding="0" cellspacing="0" border="0" summary="nav" id="breadcrumbs">
+<tbody>
+<!--================= start Status ==================-->
+<tr class="status">
+<td>
+<!--================= start BreadCrumb ==================--><a href="http://www.apache.org/">Apache</a> | <a href="http://poi.apache.org/">POI</a><a href=""></a>
+<!--================= end BreadCrumb ==================--></td><td id="tabs">
+<!--================= start Tabs ==================-->
+<div class="tab">
+<span class="selectedTab"><a class="base-selected" href="../index.html">Home</a></span> | <script language="Javascript" type="text/javascript">
+function printit() {  
+if (window.print) {
+    window.print() ;  
+} else {
+    var WebBrowser = '<OBJECT ID="WebBrowser1" WIDTH="0" HEIGHT="0" CLASSID="CLSID:8856F961-340A-11D0-A96B-00C04FD705A2"></OBJECT>';
+document.body.insertAdjacentHTML('beforeEnd', WebBrowser);
+    WebBrowser1.ExecWB(6, 2);//Use a 1 vs. a 2 for a prompting dialog box    WebBrowser1.outerHTML = "";  
+}
+}
+</script><script language="Javascript" type="text/javascript">
+var NS = (navigator.appName == "Netscape");
+var VERSION = parseInt(navigator.appVersion);
+if (VERSION > 3) {
+    document.write('  <a title="PRINT this page OUT" href="javascript:printit()">PRINT</a>');
+}
+</script>
+</div>
+<!--================= end Tabs ==================-->
+</td>
+</tr>
+</tbody>
+</table>
+<!--================= end Status ==================-->
+<table id="main" width="100%" cellpadding="8" cellspacing="0" summary="" border="0">
+<tbody>
+<tr valign="top">
+<!--================= start Menu ==================-->
+<td id="leftcol">
+<div id="navcolumn">
+<div class="menuBar">
+<div class="menu">
+<span class="menuLabel">Apache POI</span>
+        
+<div class="menuItem">
+<a href="../index.html">Top</a>
+</div>
+    
+</div>
+<div class="menu">
+<span class="menuLabel">HSSF+XSSF</span>
+        
+<div class="menuItem">
+<a href="index.html">Overview</a>
+</div>
+        
+<div class="menuItem">
+<span class="menuSelected">Quick Guide</span>
+</div>
+        
+<div class="menuItem">
+<a href="how-to.html">HOWTO</a>
+</div>
+        
+<div class="menuItem">
+<a href="converting.html">HSSF to SS Converting</a>
+</div>
+        
+<div class="menuItem">
+<a href="formula.html">Formula Support</a>
+</div>
+        
+<div class="menuItem">
+<a href="eval.html">Formula Evaluation</a>
+</div>
+		    
+<div class="menuItem">
+<a href="eval-devguide.html">Eval Dev Guide</a>
+</div>
+        
+<div class="menuItem">
+<a href="examples.html">Examples</a>
+</div>
+        
+<div class="menuItem">
+<a href="use-case.html">Use Case</a>
+</div>
+        
+<div class="menuItem">
+<a href="diagrams.html">Pictorial Docs</a>
+</div>
+        
+<div class="menuItem">
+<a href="limitations.html">Limitations</a>
+</div>
+        
+<div class="menuItem">
+<a href="user-defined-functions.html">User Defined Functions</a>
+</div>
+        
+<div class="menuItem">
+<a href="excelant.html">ExcelAnt Tests</a>
+</div>
+    
+</div>
+<div class="menu">
+<span class="menuLabel">Contributer's Guide</span>
+        
+<div class="menuItem">
+<a href="hacking-hssf.html">Hacking HSSF</a>
+</div>
+        
+<div class="menuItem">
+<a href="record-generator.html">Record Generator</a>
+</div>
+        
+<div class="menuItem">
+<a href="chart.html">Charts</a>
+</div>
+    
+</div>
+</div>
+</div>
+<form target="_blank" action="http://www.google.com/search" method="get">
+<table summary="search" border="0" cellspacing="0" cellpadding="0">
+<tr>
+<td><img height="1" width="1" alt="" src="../skin/images/spacer.gif" class="spacer"></td><td nowrap="nowrap">
+                          Search Apache POI<br>
+<input value="poi.apache.org" name="sitesearch" type="hidden"><input size="10" name="q" id="query" type="text"><img height="1" width="5" alt="" src="../skin/images/spacer.gif" class="spacer"><input name="Search" value="GO" type="submit"></td><td><img height="1" width="1" alt="" src="../skin/images/spacer.gif" class="spacer"></td>
+</tr>
+<tr>
+<td colspan="3"><img height="7" width="1" alt="" src="../skin/images/spacer.gif" class="spacer"></td>
+</tr>
+<tr>
+<td class="bottom-left-thick"></td><td bgcolor="#a5b6c6"><img height="1" width="1" alt="" src="../skin/images/spacer.gif" class="spacer"></td><td class="bottom-right-thick"></td>
+</tr>
+</table>
+</form>
+</td>
+<!--================= end Menu ==================-->
+<!--================= start Content ==================--><td>
+<div id="bodycol">
+<div class="app">
+<div align="center">
+<h1>Busy Developers' Guide to HSSF and XSSF Features</h1>
+</div>
+<div class="h3">
+    
+    
+        
+<a name="Busy+Developers%27+Guide+to+Features"></a>
+<div class="h3">
+<h3>Busy Developers' Guide to Features</h3>
+</div>
+            
+<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</h4>
+</div>
+                
+<ul>
+                    
+<li>
+<a href="#NewWorkbook">How to create a new workbook</a>
+</li>
+                    
+<li>
+<a href="#NewSheet">How to create a sheet</a>
+</li>
+                    
+<li>
+<a href="#CreateCells">How to create cells</a>
+</li>
+                    
+<li>
+<a href="#CreateDateCells">How to create date cells</a>
+</li>
+                    
+<li>
+<a href="#CellTypes">Working with different types of cells</a>
+</li>
+                    
+<li>
+<a href="#Iterator">Iterate over rows and cells</a>
+</li>
+                    
+<li>
+<a href="#CellContents">Getting the cell contents</a>
+</li>
+                    
+<li>
+<a href="#TextExtraction">Text Extraction</a>
+</li>
+                    
+<li>
+<a href="#Alignment">Aligning cells</a>
+</li>
+                    
+<li>
+<a href="#Borders">Working with borders</a>
+</li>
+                    
+<li>
+<a href="#FillsAndFrills">Fills and color</a>
+</li>
+                    
+<li>
+<a href="#MergedCells">Merging cells</a>
+</li>
+                    
+<li>
+<a href="#WorkingWithFonts">Working with fonts</a>
+</li>
+                    
+<li>
+<a href="#CustomColors">Custom colors</a>
+</li>
+                    
+<li>
+<a href="#ReadWriteWorkbook">Reading and writing</a>
+</li>
+                    
+<li>
+<a href="#NewLinesInCells">Use newlines in cells.</a>
+</li>
+                    
+<li>
+<a href="#DataFormats">Create user defined data formats</a>
+</li>
+                    
+<li>
+<a href="#FitTo">Fit Sheet to One Page</a>
+</li>
+                    
+<li>
+<a href="#PrintArea2">Set print area for a sheet</a>
+</li>
+                    
+<li>
+<a href="#FooterPageNumbers">Set page numbers on the footer of a sheet</a>
+</li>
+                    
+<li>
+<a href="#ShiftRows">Shift rows</a>
+</li>
+                    
+<li>
+<a href="#SelectSheet">Set a sheet as selected</a>
+</li>
+                    
+<li>
+<a href="#Zoom">Set the zoom magnification for a sheet</a>
+</li>
+                    
+<li>
+<a href="#Splits">Create split and freeze panes</a>
+</li>
+                    
+<li>
+<a href="#Repeating">Repeating rows and columns</a>
+</li>
+                    
+<li>
+<a href="#HeaderFooter">Headers and Footers</a>
+</li>
+                    
+<li>
+<a href="#DrawingShapes">Drawing Shapes</a>
+</li>
+                    
+<li>
+<a href="#StylingShapes">Styling Shapes</a>
+</li>
+                    
+<li>
+<a href="#Graphics2d">Shapes and Graphics2d</a>
+</li>
+                    
+<li>
+<a href="#Outlining">Outlining</a>
+</li>
+                    
+<li>
+<a href="#Images">Images</a>
+</li>
+                    
+<li>
+<a href="#NamedRanges">Named Ranges and Named Cells</a>
+</li>
+                    
+<li>
+<a href="#CellComments">How to set cell comments</a>
+</li>
+                    
+<li>
+<a href="#Autofit">How to adjust column width to fit the contents</a>
+</li>
+                    
+<li>
+<a href="#Hyperlinks">Hyperlinks</a>
+</li>
+                    
+<li>
+<a href="#Validation">Data Validations</a>
+</li>
+                    
+<li>
+<a href="#Embedded">Embedded Objects</a>
+</li>
+                    
+<li>
+<a href="#Autofilter">Autofilters</a>
+</li>
+                    
+<li>
+<a href="#ConditionalFormatting">Conditional Formatting</a>
+</li>
+                    
+<li>
+<a href="#Hiding">Hiding and Un-Hiding Rows</a>
+</li>
+                
+</ul>
+            
+            
+<a name="Features"></a>
+<div class="h4">
+<h4>Features</h4>
+</div>
+                
+<a name="NewWorkbook"></a>
+                <a name="New+Workbook"></a>
+<div class="h2">
+<h2>New Workbook</h2>
+</div>
+                    
+<pre class="code">
+    Workbook wb = new HSSFWorkbook();
+    FileOutputStream fileOut = new FileOutputStream("workbook.xls");
+    wb.write(fileOut);
+    fileOut.close();
+
+    Workbook wb = new XSSFWorkbook();
+    FileOutputStream fileOut = new FileOutputStream("workbook.xlsx");
+    wb.write(fileOut);
+    fileOut.close();
+                    </pre>
+                
+                
+<a name="NewSheet"></a>
+                <a name="New+Sheet"></a>
+<div class="h2">
+<h2>New Sheet</h2>
+</div>
+                    
+<pre class="code">
+    Workbook wb = new HSSFWorkbook();  // or new XSSFWorkbook();
+    Sheet sheet1 = wb.createSheet("new sheet");
+    Sheet sheet2 = wb.createSheet("second sheet");
+
+    // Note that sheet name is Excel must not exceed 31 characters
+    // and must not contain any of the any of the following characters:
+    // 0x0000
+    // 0x0003
+    // colon (:)
+    // backslash (\)
+    // asterisk (*)
+    // question mark (?)
+    // forward slash (/)
+    // opening square bracket ([)
+    // closing square bracket (])
+
+    // You can use org.apache.poi.ss.util.WorkbookUtil#createSafeSheetName(String nameProposal)}
+    // for a safe way to create valid names, this utility replaces invalid characters with a space (' ')
+    String safeName = WorkbookUtil.createSafeSheetName("[O'Brien's sales*?]"); // returns " O'Brien's sales   "
+    Sheet sheet3 = wb.createSheet(safeName);
+
+    FileOutputStream fileOut = new FileOutputStream("workbook.xls");
+    wb.write(fileOut);
+    fileOut.close();
+                     </pre>
+                
+                
+<a name="CreateCells"></a>
+                <a name="Creating+Cells"></a>
+<div class="h2">
+<h2>Creating Cells</h2>
+</div>
+                    
+<pre class="code">
+    Workbook wb = new HSSFWorkbook();
+    //Workbook wb = new XSSFWorkbook();
+    CreationHelper createHelper = wb.getCreationHelper();
+    Sheet sheet = wb.createSheet("new sheet");
+
+    // Create a row and put some cells in it. Rows are 0 based.
+    Row row = sheet.createRow((short)0);
+    // Create a cell and put a value in it.
+    Cell cell = row.createCell(0);
+    cell.setCellValue(1);
+
+    // Or do it on one line.
+    row.createCell(1).setCellValue(1.2);
+    row.createCell(2).setCellValue(
+         createHelper.createRichTextString("This is a string"));
+    row.createCell(3).setCellValue(true);
+
+    // Write the output to a file
+    FileOutputStream fileOut = new FileOutputStream("workbook.xls");
+    wb.write(fileOut);
+    fileOut.close();
+                    </pre>
+                
+                
+<a name="CreateDateCells"></a>
+                <a name="Creating+Date+Cells"></a>
+<div class="h2">
+<h2>Creating Date Cells</h2>
+</div>
+                    
+<pre class="code">
+    Workbook wb = new HSSFWorkbook();
+    //Workbook wb = new XSSFWorkbook();
+    CreationHelper createHelper = wb.getCreationHelper();
+    Sheet sheet = wb.createSheet("new sheet");
+
+    // Create a row and put some cells in it. Rows are 0 based.
+    Row row = sheet.createRow(0);
+
+    // Create a cell and put a date value in it.  The first cell is not styled
+    // as a date.
+    Cell cell = row.createCell(0);
+    cell.setCellValue(new Date());
+
+    // we style the second cell as a date (and time).  It is important to
+    // create a new cell style from the workbook otherwise you can end up
+    // modifying the built in style and effecting not only this cell but other cells.
+    CellStyle cellStyle = wb.createCellStyle();
+    cellStyle.setDataFormat(
+        createHelper.createDataFormat().getFormat("m/d/yy h:mm"));
+    cell = row.createCell(1);
+    cell.setCellValue(new Date());
+    cell.setCellStyle(cellStyle);
+
+    //you can also set date as java.util.Calendar
+    cell = row.createCell(2);
+    cell.setCellValue(Calendar.getInstance());
+    cell.setCellStyle(cellStyle);
+
+    // Write the output to a file
+    FileOutputStream fileOut = new FileOutputStream("workbook.xls");
+    wb.write(fileOut);
+    fileOut.close();
+                    </pre>
+                
+                
+<a name="CellTypes"></a>
+                <a name="Working+with+different+types+of+cells"></a>
+<div class="h2">
+<h2>Working with different types of cells</h2>
+</div>
+                    
+<pre class="code">
+    Workbook wb = new HSSFWorkbook();
+    Sheet sheet = wb.createSheet("new sheet");
+    Row row = sheet.createRow((short)2);
+    row.createCell(0).setCellValue(1.1);
+    row.createCell(1).setCellValue(new Date());
+    row.createCell(2).setCellValue(Calendar.getInstance());
+    row.createCell(3).setCellValue("a string");
+    row.createCell(4).setCellValue(true);
+    row.createCell(5).setCellType(Cell.CELL_TYPE_ERROR);
+
+    // Write the output to a file
+    FileOutputStream fileOut = new FileOutputStream("workbook.xls");
+    wb.write(fileOut);
+    fileOut.close();
+                    </pre>
+                
+                
+<a name="Alignment"></a>
+                <a name="Demonstrates+various+alignment+options"></a>
+<div class="h2">
+<h2>Demonstrates various alignment options</h2>
+</div>
+                    
+<pre class="code">
+    public static void main(String[] args)  throws Exception {
+        Workbook wb = new XSSFWorkbook(); //or new HSSFWorkbook();
+
+        Sheet sheet = wb.createSheet();
+        Row row = sheet.createRow((short) 2);
+        row.setHeightInPoints(30);
+
+        createCell(wb, row, (short) 0, CellStyle.ALIGN_CENTER, CellStyle.VERTICAL_BOTTOM);
+        createCell(wb, row, (short) 1, CellStyle.ALIGN_CENTER_SELECTION, CellStyle.VERTICAL_BOTTOM);
+        createCell(wb, row, (short) 2, CellStyle.ALIGN_FILL, CellStyle.VERTICAL_CENTER);
+        createCell(wb, row, (short) 3, CellStyle.ALIGN_GENERAL, CellStyle.VERTICAL_CENTER);
+        createCell(wb, row, (short) 4, CellStyle.ALIGN_JUSTIFY, CellStyle.VERTICAL_JUSTIFY);
+        createCell(wb, row, (short) 5, CellStyle.ALIGN_LEFT, CellStyle.VERTICAL_TOP);
+        createCell(wb, row, (short) 6, CellStyle.ALIGN_RIGHT, CellStyle.VERTICAL_TOP);
+
+        // Write the output to a file
+        FileOutputStream fileOut = new FileOutputStream("xssf-align.xlsx");
+        wb.write(fileOut);
+        fileOut.close();
+
+    }
+
+    /**
+     * Creates a cell and aligns it a certain way.
+     *
+     * @param wb     the workbook
+     * @param row    the row to create the cell in
+     * @param column the column number to create the cell in
+     * @param halign the horizontal alignment for the cell.
+     */
+    private static void createCell(Workbook wb, Row row, short column, short halign, short valign) {
+        Cell cell = row.createCell(column);
+        cell.setCellValue("Align It");
+        CellStyle cellStyle = wb.createCellStyle();
+        cellStyle.setAlignment(halign);
+        cellStyle.setVerticalAlignment(valign);
+        cell.setCellStyle(cellStyle);
+    }
+                    </pre>
+                
+                
+<a name="Borders"></a>
+                <a name="Working+with+borders"></a>
+<div class="h2">
+<h2>Working with borders</h2>
+</div>
+                    
+<pre class="code">
+    Workbook wb = new HSSFWorkbook();
+    Sheet sheet = wb.createSheet("new sheet");
+
+    // Create a row and put some cells in it. Rows are 0 based.
+    Row row = sheet.createRow(1);
+
+    // Create a cell and put a value in it.
+    Cell cell = row.createCell(1);
+    cell.setCellValue(4);
+
+    // Style the cell with borders all around.
+    CellStyle style = wb.createCellStyle();
+    style.setBorderBottom(CellStyle.BORDER_THIN);
+    style.setBottomBorderColor(IndexedColors.BLACK.getIndex());
+    style.setBorderLeft(CellStyle.BORDER_THIN);
+    style.setLeftBorderColor(IndexedColors.GREEN.getIndex());
+    style.setBorderRight(CellStyle.BORDER_THIN);
+    style.setRightBorderColor(IndexedColors.BLUE.getIndex());
+    style.setBorderTop(CellStyle.BORDER_MEDIUM_DASHED);
+    style.setTopBorderColor(IndexedColors.BLACK.getIndex());
+    cell.setCellStyle(style);
+
+    // Write the output to a file
+    FileOutputStream fileOut = new FileOutputStream("workbook.xls");
+    wb.write(fileOut);
+    fileOut.close();
+                    </pre>
+                
+                
+<a name="Iterator"></a>
+                <a name="Iterate+over+rows+and+cells"></a>
+<div class="h2">
+<h2>Iterate over rows and cells</h2>
+</div>
+				
+<p>Sometimes, you'd like to just iterate over all the rows in
+				a sheet, or all the cells in a row. This is possible with
+				a simple for loop.</p>
+				
+<p>Luckily, this is very easy. Row defines a 
+				<em>CellIterator</em> inner class to handle iterating over 
+				the cells (get one with a call to <em>row.cellIterator()</em>),
+				and Sheet provides a <em>rowIterator()</em> method to
+				give an iterator over all the rows.</p>
+				
+<p>Alternately, Sheet and Row both implement java.lang.Iterable,
+                so using Java 1.5 you can simply take advantage
+                of the built in "foreach" support - see below.</p>
+				
+<pre class="code">
+    sheet sheet = wb.getsheetat(0);
+    for (iterator&lt;row&gt; rit = sheet.rowiterator(); rit.hasnext(); ) {
+      row row = rit.next();
+      for (iterator&lt;cell&gt; cit = row.celliterator(); cit.hasnext(); ) {
+        cell cell = cit.next();
+        // do something here
+      }
+    }
+        </pre>
+                
+                
+<a name="Iterate+over+rows+and+cells+using+Java+1.5+foreach+loops"></a>
+<div class="h2">
+<h2>Iterate over rows and cells using Java 1.5 foreach loops</h2>
+</div>
+				
+<p>Sometimes, you'd like to just iterate over all the rows in
+				a sheet, or all the cells in a row. If you are using Java
+				5 or later, then this is especially handy, as it'll allow the
+				new foreach loop support to work.</p>
+				
+<p>Luckily, this is very easy. Both Sheet and Row
+				implement <em>java.lang.Iterable</em> to allow foreach
+				loops. For Row this allows access to the 
+				<em>CellIterator</em> inner class to handle iterating over 
+				the cells, and for Sheet gives the 
+				<em>rowIterator()</em> to iterator over all the rows.</p>
+				
+<pre class="code">
+    Sheet sheet = wb.getSheetAt(0);
+    for (Row row : sheet) {
+      for (Cell cell : row) {
+        // Do something here
+      }
+    }
+				</pre>
+                
+
+                
+<a name="CellContents"></a>
+                <a name="Getting+the+cell+contents"></a>
+<div class="h2">
+<h2>Getting the cell contents</h2>
+</div>
+					
+<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.*;
+
+    Sheet sheet1 = wb.getSheetAt(0);
+    for (Row row : sheet1) {
+        for (Cell cell : row) {
+            CellReference cellRef = new CellReference(row.getRowNum(), cell.getColumnIndex());
+            System.out.print(cellRef.formatAsString());
+            System.out.print(" - ");
+
+            switch (cell.getCellType()) {
+                case Cell.CELL_TYPE_STRING:
+                    System.out.println(cell.getRichStringCellValue().getString());
+                    break;
+                case Cell.CELL_TYPE_NUMERIC:
+                    if (DateUtil.isCellDateFormatted(cell)) {
+                        System.out.println(cell.getDateCellValue());
+                    } else {
+                        System.out.println(cell.getNumericCellValue());
+                    }
+                    break;
+                case Cell.CELL_TYPE_BOOLEAN:
+                    System.out.println(cell.getBooleanCellValue());
+                    break;
+                case Cell.CELL_TYPE_FORMULA:
+                    System.out.println(cell.getCellFormula());
+                    break;
+                default:
+                    System.out.println();
+            }
+        }
+    }
+				</pre>
+                
+
+                
+<a name="TextExtraction"></a>
+                <a name="Text+Extraction"></a>
+<div class="h2">
+<h2>Text Extraction</h2>
+</div>
+					
+<p>For most text extraction requirements, the standard
+					ExcelExtractor class should provide all you need.</p>
+					
+<pre class="code">
+    InputStream inp = new FileInputStream("workbook.xls");
+    HSSFWorkbook wb = new HSSFWorkbook(new POIFSFileSystem(inp));
+    ExcelExtractor extractor = new ExcelExtractor(wb);
+
+    extractor.setFormulasNotResults(true);
+    extractor.setIncludeSheetNames(false);
+    String text = extractor.getText();
+					</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</h2>
+</div>
+                    
+<pre class="code">
+    Workbook wb = new XSSFWorkbook();
+    Sheet sheet = wb.createSheet("new sheet");
+
+    // Create a row and put some cells in it. Rows are 0 based.
+    Row row = sheet.createRow((short) 1);
+
+    // Aqua background
+    CellStyle style = wb.createCellStyle();
+    style.setFillBackgroundColor(IndexedColors.AQUA.getIndex());
+    style.setFillPattern(CellStyle.BIG_SPOTS);
+    Cell cell = row.createCell((short) 1);
+    cell.setCellValue("X");
+    cell.setCellStyle(style);
+
+    // Orange "foreground", foreground being the fill foreground not the font color.
+    style = wb.createCellStyle();
+    style.setFillForegroundColor(IndexedColors.ORANGE.getIndex());
+    style.setFillPattern(CellStyle.SOLID_FOREGROUND);
+    cell = row.createCell((short) 2);
+    cell.setCellValue("X");
+    cell.setCellStyle(style);
+
+    // Write the output to a file
+    FileOutputStream fileOut = new FileOutputStream("workbook.xls");
+    wb.write(fileOut);
+    fileOut.close();
+                    </pre>
+                
+                
+<a name="MergedCells"></a>
+                <a name="Merging+cells"></a>
+<div class="h2">
+<h2>Merging cells</h2>
+</div>
+                    
+<pre class="code">
+    Workbook wb = new HSSFWorkbook();
+    Sheet sheet = wb.createSheet("new sheet");
+
+    Row row = sheet.createRow((short) 1);
+    Cell cell = row.createCell((short) 1);
+    cell.setCellValue("This is a test of merging");
+
+    sheet.addMergedRegion(new CellRangeAddress(
+            1, //first row (0-based)
+            1, //last row  (0-based)
+            1, //first column (0-based)
+            2  //last column  (0-based)
+    ));
+
+    // Write the output to a file
+    FileOutputStream fileOut = new FileOutputStream("workbook.xls");
+    wb.write(fileOut);
+    fileOut.close();
+                    </pre>
+                
+                
+<a name="WorkingWithFonts"></a>
+                <a name="Working+with+fonts"></a>
+<div class="h2">
+<h2>Working with fonts</h2>
+</div>
+                    
+<pre class="code">
+    Workbook wb = new HSSFWorkbook();
+    Sheet sheet = wb.createSheet("new sheet");
+
+    // Create a row and put some cells in it. Rows are 0 based.
+    Row row = sheet.createRow(1);
+
+    // Create a new font and alter it.
+    Font font = wb.createFont();
+    font.setFontHeightInPoints((short)24);
+    font.setFontName("Courier New");
+    font.setItalic(true);
+    font.setStrikeout(true);
+
+    // Fonts are set into a style so create a new one to use.
+    CellStyle style = wb.createCellStyle();
+    style.setFont(font);
+
+    // Create a cell and put a value in it.
+    Cell cell = row.createCell(1);
+    cell.setCellValue("This is a test of fonts");
+    cell.setCellStyle(style);
+
+    // Write the output to a file
+    FileOutputStream fileOut = new FileOutputStream("workbook.xls");
+    wb.write(fileOut);
+    fileOut.close();
+  </pre>
+
+<p>
+  Note, the maximum number of unique fonts in a workbook is limited to 32767 (
+  the maximum positive short). You should re-use fonts in your apllications instead of
+  creating a font for each cell.
+Examples:
+</p>
+
+<p>
+<strong>Wrong:</strong>
+</p>
+
+<pre class="code">
+    for (int i = 0; i &lt; 10000; i++) {
+        Row row = sheet.createRow(i);
+        Cell cell = row.createCell((short) 0);
+
+        CellStyle style = workbook.createCellStyle();
+        Font font = workbook.createFont();
+        font.setBoldweight(Font.BOLDWEIGHT_BOLD);
+        style.setFont(font);
+        cell.setCellStyle(style);
+    }
+</pre>
+
+<p>
+<strong>Correct:</strong>
+</p>
+
+<pre class="code">
+
+    CellStyle style = workbook.createCellStyle();
+    Font font = workbook.createFont();
+    font.setBoldweight(Font.BOLDWEIGHT_BOLD);
+    style.setFont(font);
+    for (int i = 0; i &lt; 10000; i++) {
+        Row row = sheet.createRow(i);
+        Cell cell = row.createCell((short) 0);
+        cell.setCellStyle(style);
+    }
+</pre>
+  
+                
+                
+<a name="CustomColors"></a>
+                <a name="Custom+colors"></a>
+<div class="h2">
+<h2>Custom colors</h2>
+</div>
+                  
+<p>
+<strong>HSSF:</strong>
+</p>
+                    
+<pre class="code">
+    HSSFWorkbook wb = new HSSFWorkbook();
+    HSSFSheet sheet = wb.createSheet();
+    HSSFRow row = sheet.createRow((short) 0);
+    HSSFCell cell = row.createCell((short) 0);
+    cell.setCellValue("Default Palette");
+
+    //apply some colors from the standard palette,
+    // as in the previous examples.
+    //we'll use red text on a lime background
+
+    HSSFCellStyle style = wb.createCellStyle();
+    style.setFillForegroundColor(HSSFColor.LIME.index);
+    style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
+
+    HSSFFont font = wb.createFont();
+    font.setColor(HSSFColor.RED.index);
+    style.setFont(font);
+
+    cell.setCellStyle(style);
+
+    //save with the default palette
+    FileOutputStream out = new FileOutputStream("default_palette.xls");
+    wb.write(out);
+    out.close();
+
+    //now, let's replace RED and LIME in the palette
+    // with a more attractive combination
+    // (lovingly borrowed from freebsd.org)
+
+    cell.setCellValue("Modified Palette");
+
+    //creating a custom palette for the workbook
+    HSSFPalette palette = wb.getCustomPalette();
+
+    //replacing the standard red with freebsd.org red
+    palette.setColorAtIndex(HSSFColor.RED.index,
+            (byte) 153,  //RGB red (0-255)
+            (byte) 0,    //RGB green
+            (byte) 0     //RGB blue
+    );
+    //replacing lime with freebsd.org gold
+    palette.setColorAtIndex(HSSFColor.LIME.index, (byte) 255, (byte) 204, (byte) 102);
+
+    //save with the modified palette
+    // note that wherever we have previously used RED or LIME, the
+    // new colors magically appear
+    out = new FileOutputStream("modified_palette.xls");
+    wb.write(out);
+    out.close();
+                    </pre>
+                    
+<p>
+<strong>XSSF:</strong>
+</p>
+                    
+<pre class="code">
+    XSSFWorkbook wb = new XSSFWorkbook();
+    XSSFSheet sheet = wb.createSheet();
+    XSSFRow row = sheet.createRow(0);
+    XSSFCell cell = row.createCell( 0);
+    cell.setCellValue("custom XSSF colors");
+
+    XSSFCellStyle style1 = wb.createCellStyle();
+    style1.setFillForegroundColor(new XSSFColor(new java.awt.Color(128, 0, 128)));
+    style1.setFillPattern(CellStyle.SOLID_FOREGROUND);
+                    </pre>
+                
+                
+<a name="ReadWriteWorkbook"></a>
+                <a name="Reading+and+Rewriting+Workbooks"></a>
+<div class="h2">
+<h2>Reading and Rewriting Workbooks</h2>
+</div>
+                    
+<pre class="code">
+    InputStream inp = new FileInputStream("workbook.xls");
+    //InputStream inp = new FileInputStream("workbook.xlsx");
+
+    Workbook wb = WorkbookFactory.create(inp);
+    Sheet sheet = wb.getSheetAt(0);
+    Row row = sheet.getRow(2);
+    Cell cell = row.getCell(3);
+    if (cell == null)
+        cell = row.createCell(3);
+    cell.setCellType(Cell.CELL_TYPE_STRING);
+    cell.setCellValue("a test");
+
+    // Write the output to a file
+    FileOutputStream fileOut = new FileOutputStream("workbook.xls");
+    wb.write(fileOut);
+    fileOut.close();
+                    </pre>
+                
+                
+<a name="NewLinesInCells"></a>
+                <a name="Using+newlines+in+cells"></a>
+<div class="h2">
+<h2>Using newlines in cells</h2>
+</div>
+                    
+<pre class="code">
+    Workbook wb = new XSSFWorkbook();   //or new HSSFWorkbook();
+    Sheet sheet = wb.createSheet();
+
+    Row row = sheet.createRow(2);
+    Cell cell = row.createCell(2);
+    cell.setCellValue("Use \n with word wrap on to create a new line");
+
+    //to enable newlines you need set a cell styles with wrap=true
+    CellStyle cs = wb.createCellStyle();
+    cs.setWrapText(true);
+    cell.setCellStyle(cs);
+
+    //increase row height to accomodate two lines of text
+    row.setHeightInPoints((2*sheet.getDefaultRowHeightInPoints()));
+
+    //adjust column width to fit the content
+    sheet.autoSizeColumn((short)2);
+
+    FileOutputStream fileOut = new FileOutputStream("ooxml-newlines.xlsx");
+    wb.write(fileOut);
+    fileOut.close();
+                  </pre>
+                
+                
+<a name="DataFormats"></a>
+                <a name="Data+Formats"></a>
+<div class="h2">
+<h2>Data Formats</h2>
+</div>
+                    
+<pre class="code">
+    Workbook wb = new HSSFWorkbook();
+    Sheet sheet = wb.createSheet("format sheet");
+    CellStyle style;
+    DataFormat format = wb.createDataFormat();
+    Row row;
+    Cell cell;
+    short rowNum = 0;
+    short colNum = 0;
+
+    row = sheet.createRow(rowNum++);
+    cell = row.createCell(colNum);
+    cell.setCellValue(11111.25);
+    style = wb.createCellStyle();
+    style.setDataFormat(format.getFormat("0.0"));
+    cell.setCellStyle(style);
+
+    row = sheet.createRow(rowNum++);
+    cell = row.createCell(colNum);
+    cell.setCellValue(11111.25);
+    style = wb.createCellStyle();
+    style.setDataFormat(format.getFormat("#,##0.0000"));
+    cell.setCellStyle(style);
+
+    FileOutputStream fileOut = new FileOutputStream("workbook.xls");
+    wb.write(fileOut);
+    fileOut.close();
+                    </pre>
+                
+                
+<a name="FitTo"></a>
+                <a name="Fit+Sheet+to+One+Page"></a>
+<div class="h2">
+<h2>Fit Sheet to One Page</h2>
+</div>
+                    
+<pre class="code">
+    Workbook wb = new HSSFWorkbook();
+    Sheet sheet = wb.createSheet("format sheet");
+    PrintSetup ps = sheet.getPrintSetup();
+
+    sheet.setAutobreaks(true);
+
+    ps.setFitHeight((short)1);
+    ps.setFitWidth((short)1);
+
+
+    // Create various cells and rows for spreadsheet.
+
+    FileOutputStream fileOut = new FileOutputStream("workbook.xls");
+    wb.write(fileOut);
+    fileOut.close();
+                    </pre>
+                
+                
+<a name="PrintArea2"></a>
+                <a name="Set+Print+Area"></a>
+<div class="h2">
+<h2>Set Print Area</h2>
+</div>
+                    
+<pre class="code">
+    Workbook wb = new HSSFWorkbook();
+    Sheet sheet = wb.createSheet("Sheet1");
+    //sets the print area for the first sheet
+    wb.setPrintArea(0, "$A$1:$C$2");
+    
+    //Alternatively:
+    wb.setPrintArea(
+            0, //sheet index
+            0, //start column
+            1, //end column
+            0, //start row
+            0  //end row
+    );
+
+    FileOutputStream fileOut = new FileOutputStream("workbook.xls");
+    wb.write(fileOut);
+    fileOut.close();
+                    </pre>
+                
+
+                
+<a name="FooterPageNumbers"></a>
+                <a name="Set+Page+Numbers+on+Footer"></a>
+<div class="h2">
+<h2>Set Page Numbers on Footer</h2>
+</div>
+                    
+<pre class="code">
+    Workbook wb = new HSSFWorkbook(); // or new XSSFWorkbook();
+    Sheet sheet = wb.createSheet("format sheet");
+    Footer footer = sheet.getFooter();
+
+    footer.setRight( "Page " + HeaderFooter.page() + " of " + HeaderFooter.numPages() );
+
+
+
+    // Create various cells and rows for spreadsheet.
+
+    FileOutputStream fileOut = new FileOutputStream("workbook.xls");
+    wb.write(fileOut);
+    fileOut.close();
+                    </pre>
+                
+
+                
+<a name="ConvenienceFunctions"></a>
+                <a name="Using+the+Convenience+Functions"></a>
+<div class="h2">
+<h2>Using the Convenience Functions</h2>
+</div>
+                    
+<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" );
+
+    // Create a merged region
+    Row row = sheet1.createRow( 1 );
+    Row row2 = sheet1.createRow( 2 );
+    Cell cell = row.createCell( 1 );
+    cell.setCellValue( "This is a test of merging" );
+    CellRangeAddress region = CellRangeAddress.valueOf("B2:E5");
+    sheet1.addMergedRegion( region );
+
+    // Set the border and border colors.
+    final short borderMediumDashed = CellStyle.BORDER_MEDIUM_DASHED;
+    RegionUtil.setBorderBottom( borderMediumDashed,
+        region, sheet1, wb );
+    RegionUtil.setBorderTop( borderMediumDashed,
+        region, sheet1, wb );
+    RegionUtil.setBorderLeft( borderMediumDashed,
+        region, sheet1, wb );
+    RegionUtil.setBorderRight( borderMediumDashed,
+        region, sheet1, wb );
+    RegionUtil.setBottomBorderColor(IndexedColors.AQUA.getIndex(), region, sheet1, wb);
+    RegionUtil.setTopBorderColor(IndexedColors.AQUA.getIndex(), region, sheet1, wb);
+    RegionUtil.setLeftBorderColor(IndexedColors.AQUA.getIndex(), region, sheet1, wb);
+    RegionUtil.setRightBorderColor(IndexedColors.AQUA.getIndex(), region, sheet1, wb);
+
+    // Shows some usages of HSSFCellUtil
+    CellStyle style = wb.createCellStyle();
+    style.setIndention((short)4);
+    CellUtil.createCell(row, 8, "This is the value of the cell", style);
+    Cell cell2 = CellUtil.createCell( row2, 8, "This is the value of the cell");
+    CellUtil.setAlignment(cell2, wb, CellStyle.ALIGN_CENTER);
+
+    // Write out the workbook
+    FileOutputStream fileOut = new FileOutputStream( "workbook.xls" );
+    wb.write( fileOut );
+    fileOut.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</h2>
+</div>
+                    
+<pre class="code">
+        Workbook wb = new HSSFWorkbook();
+        Sheet sheet = wb.createSheet("row sheet");
+
+        // Create various cells and rows for spreadsheet.
+
+        // Shift rows 6 - 11 on the spreadsheet to the top (rows 0 - 5)
+        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</h2>
+</div>
+                    
+<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</h2>
+</div>
+                    
+<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(3,4);   // 75 percent magnification
+                    </pre>
+                
+
+                
+<a name="Splits"></a>
+                <a name="Splits+and+freeze+panes"></a>
+<div class="h2">
+<h2>Splits and freeze panes</h2>
+</div>
+                    
+<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 pains appear differently.  The split area is
+                        divided into four separate work area's.  The split
+                        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.
+                        This is in 1/20th of a point.  A point in this case
+                        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");
+    Sheet sheet2 = wb.createSheet("second sheet");
+    Sheet sheet3 = wb.createSheet("third sheet");
+    Sheet sheet4 = wb.createSheet("fourth sheet");
+
+    // Freeze just one row
+    sheet1.createFreezePane( 0, 1, 0, 1 );
+    // Freeze just one column
+    sheet2.createFreezePane( 1, 0, 1, 0 );
+    // Freeze the columns and rows (forget about scrolling position of the lower right quadrant).
+    sheet3.createFreezePane( 2, 2 );
+    // Create a split with the lower left side being the active quadrant
+    sheet4.createSplitPane( 2000, 2000, 0, 0, Sheet.PANE_LOWER_LEFT );
+
+    FileOutputStream fileOut = new FileOutputStream("workbook.xls");
+    wb.write(fileOut);
+    fileOut.close();
+                    </pre>
+                
+
+                
+<a name="Repeating"></a>
+                <a name="Repeating+rows+and+columns"></a>
+<div class="h2">
+<h2>Repeating rows and columns</h2>
+</div>
+                    
+<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 
+                        repeat. 
+                        For setRepeatingRows(), it should specify a range of 
+                        rows to repeat, with the column part spanning all 
+                        columns. 
+                        For setRepeatingColums(), it should specify a range of 
+                        columns to repeat, with the row part spanning all 
+                        rows. 
+                        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");
+    Sheet sheet2 = wb.createSheet("Sheet2");
+
+    // Set the rows to repeat from row 4 to 5 on the first sheet.
+    sheet1.setRepeatingRows(CellRangeAddress.valueOf("4:5"));
+    // Set the columns to repeat from column A to C on the second sheet
+    sheet2.setRepeatingColumns(CellRangeAddress.valueOf("A:C"));
+
+    FileOutputStream fileOut = new FileOutputStream("workbook.xls");
+    wb.write(fileOut);
+    fileOut.close();
+                    </pre>
+                
+                
+<a name="HeaderFooter"></a>
+                <a name="Headers+and+Footers"></a>
+<div class="h2">
+<h2>Headers and Footers</h2>
+</div>
+                    
+<p>
+                        Example is for headers but applies directly to footers.
+                    </p>
+                    
+<pre class="code">
+    Workbook wb = new HSSFWorkbook();
+    Sheet sheet = wb.createSheet("new sheet");
+
+    Header header = sheet.getHeader();
+    header.setCenter("Center Header");
+    header.setLeft("Left Header");
+    header.setRight(HSSFHeader.font("Stencil-Normal", "Italic") +
+                    HSSFHeader.fontSize((short) 16) + "Right w/ Stencil-Normal Italic font and size 16");
+
+    FileOutputStream fileOut = new FileOutputStream("workbook.xls");
+    wb.write(fileOut);
+    fileOut.close();
+                    </pre>
+                
+
+                
+<a name="DrawingShapes"></a>
+                <a name="Drawing+Shapes"></a>
+<div class="h2">
+<h2>Drawing Shapes</h2>
+</div>
+                    
+<p>
+                        POI supports drawing shapes using the Microsoft Office
+                        drawing tools.  Shapes on a sheet are organized in a
+                        hiearchy of groups and and shapes.  The top-most shape
+                        is the patriarch.  This is not visisble on the sheet
+                        at all.  To start drawing you need to call <span class="codefrag">createPatriarch</span>
+                        on the <span class="codefrag">HSSFSheet</span> class.  This has the
+                        effect erasing any other shape information stored
+                        in that sheet.  By default POI will leave shape
+                        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>
+                        
+<li>Create an anchor to position the shape on the sheet.</li>
+                        
+<li>Ask the patriarch to create the shape.</li>
+                        
+<li>Set the shape type (line, oval, rectangle etc...)</li>
+                        
+<li>Set any other style details converning the shape. (eg:
+                        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);
+    font.setUnderline(HSSFFont.U_DOUBLE);
+    HSSFRichTextString string = new HSSFRichTextString("Woo!!!");
+    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="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(
+            new HSSFClientAnchor(0,0,900,200,(short)2,2,(short)2,2));
+
+    // Create a couple of lines in the group.
+    HSSFSimpleShape shape1 = group.createShape(new HSSFChildAnchor(3,3,500,500));
+    shape1.setShapeType(HSSFSimpleShape.OBJECT_TYPE_LINE);
+    ( (HSSFChildAnchor) shape1.getAnchor() ).setAnchor((short)3,3,500,500);
+    HSSFSimpleShape shape2 = group.createShape(new HSSFChildAnchor((short)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:
+                        the <span class="codefrag">HSSFChildAnchor</span>.  What happens is that
+                        the created group has it's own coordinate space for
+                        shapes that are placed into it.  POI defaults this to
+                        (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</h2>
+</div>
+                    
+<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>
+                        
+<li>Make a shape with no fill color.</li>
+                        
+<li>Change the thickness of the lines.</li>
+                        
+<li>Change the style of the lines. Eg: dashed, dotted.</li>
+                        
+<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);
+    s.setLineStyleColor(10,10,10);
+    s.setFillColor(90,10,200);
+    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</h2>
+</div>
+                    
+<p>
+                        While the native POI shape drawing commands are the
+                        recommended way to draw shapes in a shape it's sometimes
+                        desirable to use a standard API for compatibility with
+                        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="label">Warning</div>
+<div class="content">
+                        It's important to not however before continuing that
+                        <span class="codefrag">Graphics2d</span> is a poor match to the capabilities
+                        of the Microsoft Office drawing commands.  The older
+                        <span class="codefrag">Graphics</span> class offers a closer match but is
+                        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 );
+    group.setCoordinates( 0, 0, 80 * 4 , 12 * 23  );
+    float verticalPointsPerPixel = a.getAnchorHeightInPoints(sheet) / (float)Math.abs(group.getY2() - group.getY1());
+    g = new EscherGraphics( group, wb, Color.black, verticalPointsPerPixel );
+    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
+                        fontSizeMultipler then create the EscherGraphics object.
+                        Since what we really want is a <span class="codefrag">Graphics2d</span>
+                        object we create an EscherGraphics2d object and pass in
+                        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
+                        into escher drawing calls is that Excel does not have
+                        the concept of absolute pixel positions.  It measures
+                        it's cell widths in 'characters' and the cell heights in points.
+                        Unfortunately it's not defined exactly what type of character it's
+                        measuring.  Presumably this is due to the fact that the Excel will be
+                        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>
+                        
+<li>fillOval()</li>
+                        
+<li>drawString()</li>
+                        
+<li>drawOval()</li>
+                        
+<li>drawLine()</li>
+                        
+<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-N103A8"></a>
+<div class="h2">
+<h2>Outlining</h2>
+</div>
+                    
+                    
+<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");
+
+    sheet1.groupRow( 5, 14 );
+    sheet1.groupRow( 7, 14 );
+    sheet1.groupRow( 16, 19 );
+
+    sheet1.groupColumn( (short)4, (short)7 );
+    sheet1.groupColumn( (short)9, (short)12 );
+    sheet1.groupColumn( (short)10, (short)11 );
+
+    FileOutputStream fileOut = new FileOutputStream(filename);
+    wb.write(fileOut);
+    fileOut.close();
+                    </pre>
+                    
+<p>
+                        To collapse (or expand) an outline use the following calls:
+                    </p>
+                    
+<pre class="code">
+    sheet1.setRowGroupCollapsed( 7, true );
+    sheet1.setColumnGroupCollapsed( (short)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-N103C5"></a>
+<div class="h3">
+<h3>Images</h3>
+</div>
+            
+            
+<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>
+                
+<li>JPG</li>
+                
+<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();
+
+    //add picture data to this workbook.
+    InputStream is = new FileInputStream("image1.jpeg");
+    byte[] bytes = IOUtils.toByteArray(is);
+    int pictureIdx = wb.addPicture(bytes, Workbook.PICTURE_TYPE_JPEG);
+    is.close();
+
+    CreationHelper helper = wb.getCreationHelper();
+
+    //create sheet
+    Sheet sheet = wb.createSheet();
+
+    // Create the drawing patriarch.  This is the top level container for all shapes. 
+    Drawing drawing = sheet.createDrawingPatriarch();
+
+    //add a picture shape
+    ClientAnchor anchor = helper.createClientAnchor();
+    //set top-left corner of the picture,
+    //subsequent call of Picture#resize() will operate relative to it
+    anchor.setCol1(3);
+    anchor.setRow1(2);
+    Picture pict = drawing.createPicture(anchor, pictureIdx);
+
+    //auto-size picture relative to its top-left corner
+    pict.resize();
+
+    //save workbook
+    String file = "picture.xls";
+    if(wb instanceof XSSFWorkbook) file += "x";
+    FileOutputStream fileOut = new FileOutputStream(file);
+    wb.write(fileOut);
+    fileOut.close();
+        </pre>
+        
+<div class="frame 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();
+    for (Iterator it = lst.iterator(); it.hasNext(); ) {
+        PictureData pict = (PictureData)it.next();
+        String ext = pict.suggestFileExtension();
+        byte[] data = pict.getData();
+        if (ext.equals("jpeg")){
+          FileOutputStream out = new FileOutputStream("pict.jpg");
+          out.write(data);
+          out.close();
+        }
+    }
+      </pre>
+        
+        
+<a name="NamedRanges"></a>
+        <a name="Named+Ranges+and+Named+Cells"></a>
+<div class="h3">
+<h3>Named Ranges and Named Cells</h3>
+</div>
+            
+            
+<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
+                cell. You can create as well as refer to cells in a workbook by their named range.
+                When working with Named Ranges, the classes: org.apache.poi.hssf.util.CellReference and
+                &amp; org.apache.poi.hssf.util.AreaReference are used (these
+                work for both XSSF and HSSF, despite the package name).
+            </p>
+            
+<p>
+            Creating Named Range / Named Cell
+            </p>
+            
+<pre class="code">
+    // setup code
+    String sname = "TestSheet", cname = "TestName", cvalue = "TestVal";
+    Workbook wb = new HSSFWorkbook();
+    Sheet sheet = wb.createSheet(sname);
+    sheet.createRow(0).createCell((short) 0).setCellValue(cvalue);
+
+    // 1. create named range for a single cell using areareference
+    Name namedCell = wb.createName();
+    namedCell.setNameName(cname);
+    String reference = sname+"!A1:A1"; // area reference
+    namedCell.setRefersToFormula(reference);
+
+    // 2. create named range for a single cell using cellreference
+    Name namedCel2 = wb.createName();
+    namedCel2.setNameName(cname);
+    String reference = sname+"!A1"; // cell reference
+    namedCel2.setRefersToFormula(reference);
+
+    // 3. create named range for an area using AreaReference
+    Name namedCel3 = wb.createName();
+    namedCel3.setNameName(cname);
+    String reference = sname+"!A1:C5"; // area reference
+    namedCel3.setRefersToFormula(reference);
+
+    // 4. create named formula
+    Name namedCel4 = wb.createName();
+    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";
+    Workbook wb = getMyWorkbook(); // retrieve workbook
+
+    // retrieve the named range
+    int namedCellIdx = wb.getNameIndex(cellName);
+    Name aNamedCell = wb.getNameAt(namedCellIdx);
+
+    // retrieve the cell at the named range and test its contents
+    AreaReference aref = new AreaReference(aNamedCell.getRefersToFormula());
+    CellReference[] crefs = aref.getAllReferencedCells();
+    for (int i=0; i&lt;crefs.length; i++) {
+        Sheet s = wb.getSheet(crefs[i].getSheetName());
+        Row r = sheet.getRow(crefs[i].getRow());
+        Cell c = r.getCell(crefs[i].getCol());
+        // 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";
+    Workbook wb = getMyWorkbook(); // retrieve workbook
+
+    // Retrieve the named range
+    // Will be something like "$C$10,$D$12:$D$14";
+    int namedCellIdx = wb.getNameIndex(cellName);
+    Name aNamedCell = wb.getNameAt(namedCellIdx);
+
+    // Retrieve the cell at the named range and test its contents
+    // Will get back one AreaReference for C10, and
+    //  another for D12 to D14
+    AreaReference[] arefs = AreaReference.generateContiguous(aNamedCell.getRefersToFormula());
+    for (int i=0; i&lt;arefs.length; i++) {
+        // Only get the corners of the Area
+        // (use arefs[i].getAllReferencedCells() to get all cells)
+        CellReference[] crefs = arefs[i].getCells();
+        for (int j=0; j&lt;crefs.length; j++) {
+            // Check it turns into real stuff
+            Sheet s = wb.getSheet(crefs[j].getSheetName());
+            Row r = s.getRow(crefs[j].getRow());
+            Cell c = r.getCell(crefs[j].getCol());
+            // Do something with this corner cell
+        }
+    }
+            </pre>
+            
+<p>
+            Note, when a cell is deleted, Excel does not delete the 
+            attached named range. As result, workbook can contain 
+            named ranges that point to cells that no longer exist.
+            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. 
+    } else {
+      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</h3>
+</div>
+        
+<p>
+          A comment is a rich text note that is attached to &amp;
+          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();
+
+    CreationHelper factory = wb.getCreationHelper();
+
+    Sheet sheet = wb.createSheet();
+    
+    Rowl row  = sheet.createRow(3);
+    Cell cell = row.createCell(5);
+    cell.setCellValue("F4");
+    
+    Drawing drawing = sheet.createDrawingPatriarch();
+
+    // When the comment box is visible, have it show in a 1x3 space
+    ClientAnchor anchor = factory.createClientAnchor();
+    anchor.setCol1(cell.getColumnIndex());
+    anchor.setCol2(cell.getColumnIndex()+1);
+    anchor.setRow1(row.getRowNul());
+    anchor.setRow2(row.getRowNul()+3);
+
+    // Create the comment and set the text+author
+    Comment comment = drawing.createCellComment(anchor);
+    RichTextString str = factory.createRichTextString("Hello, World!");
+    comment.setString(str);
+    comment.setAuthor("Apache POI");
+
+    // Assign the comment to the cell
+    cell.setCellComment(comment);
+
+    String fname = "comment-xssf.xls";
+    if(wb instanceof XSSFWorkbook) fname += "x";
+    FileOutputStream out = new FileOutputStream(fname);
+    wb.write(out);
+    out.close();
+        </pre>
+         
+<p>
+  Reading cell comments
+        </p>
+  
+<pre class="code">
+    Cell cell = sheet.get(3).getColumn((short)1);
+    Comment comment = cell.getCellComment();
+    if (comment != null) {
+      RichTextString str = comment.getString();
+      String author = comment.getAuthor();
+    }
+    //  alternatively you can retrieve cell comments by (row, column)
+    comment = sheet.getCellComment(3, 1);
+  </pre>
+     
+
+     
+<a name="Autofit"></a>
+     <a name="Adjust+column+width+to+fit+the+contents"></a>
+<div class="h3">
+<h3>Adjust column width to fit the contents</h3>
+</div>
+        
+<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>
+      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="label">Warning</div>
+<div class="content">
+    To calculate column width Sheet.autoSizeColumn uses Java2D classes
+    that throw exception if graphical environment is not available. In case if graphical environment
+    is not available, you must tell Java that you are running in headless mode and
+    set the following system property: <span class="codefrag"> java.awt.headless=true </span>.
+        </div>
+</div>
+     
+     
+<a name="Hyperlinks"></a>
+     <a name="How+to+read+hyperlinks"></a>
+<div class="h3">
+<h3>How to read hyperlinks</h3>
+</div>
+        
+<pre class="code">
+    Sheet sheet = workbook.getSheetAt(0);
+
+    Cell cell = sheet.getRow(0).getCell((short)0);
+    Hyperlink link = cell.getHyperlink();
+    if(link != null){
+        System.out.println(link.getAddress());
+    }
+      </pre>
+     
+     
+<a name="How+to+create+hyperlinks"></a>
+<div class="h3">
+<h3>How to create hyperlinks</h3>
+</div>
+        
+<pre class="code">
+    Workbook wb = new XSSFWorkbook(); //or new HSSFWorkbook();
+    CreationHelper createHelper = wb.getCreationHelper();
+
+    //cell style for hyperlinks
+    //by default hyperlinks are blue and underlined
+    CellStyle hlink_style = wb.createCellStyle();
+    Font hlink_font = wb.createFont();
+    hlink_font.setUnderline(Font.U_SINGLE);
+    hlink_font.setColor(IndexedColors.BLUE.getIndex());
+    hlink_style.setFont(hlink_font);
+
+    Cell cell;
+    Sheet sheet = wb.createSheet("Hyperlinks");
+    //URL
+    cell = sheet.createRow(0).createCell((short)0);
+    cell.setCellValue("URL Link");
+
+    Hyperlink link = createHelper.createHyperlink(Hyperlink.LINK_URL);
+    link.setAddress("http://poi.apache.org/");
+    cell.setHyperlink(link);
+    cell.setCellStyle(hlink_style);
+
+    //link to a file in the current directory
+    cell = sheet.createRow(1).createCell((short)0);
+    cell.setCellValue("File Link");
+    link = createHelper.createHyperlink(Hyperlink.LINK_FILE);
+    link.setAddress("link1.xls");
+    cell.setHyperlink(link);
+    cell.setCellStyle(hlink_style);
+
+    //e-mail link
+    cell = sheet.createRow(2).createCell((short)0);
+    cell.setCellValue("Email Link");
+    link = createHelper.createHyperlink(Hyperlink.LINK_EMAIL);
+    //note, if subject contains white spaces, make sure they are url-encoded
+    link.setAddress("mailto:poi@apache.org?subject=Hyperlinks");
+    cell.setHyperlink(link);
+    cell.setCellStyle(hlink_style);
+
+    //link to a place in this workbook
+
+    //create a target sheet and cell
+    Sheet sheet2 = wb.createSheet("Target Sheet");
+    sheet2.createRow(0).createCell((short)0).setCellValue("Target Cell");
+
+    cell = sheet.createRow(3).createCell((short)0);
+    cell.setCellValue("Worksheet Link");
+    Hyperlink link2 = createHelper.createHyperlink(Hyperlink.LINK_DOCUMENT);
+    link2.setAddress("'Target Sheet'!A1");
+    cell.setHyperlink(link2);
+    cell.setCellStyle(hlink_style);
+
+    FileOutputStream out = new FileOutputStream("hyperinks.xlsx");
+    wb.write(out);
+    out.close();
+        </pre>
+     
+     
+<a name="Validation"></a>
+     <a name="Data+Validations"></a>
+<div class="h3">
+<h3>Data Validations</h3>
+</div>
+      
+<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)</h4>
+</div>
+        
+        
+<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");
+  CellRangeAddressList addressList = new CellRangeAddressList(
+    0, 0, 0, 0);
+  DVConstraint dvConstraint = DVConstraint.createExplicitListConstraint(
+    new String[]{"10", "20", "30"});
+  DataValidation dataValidation = new HSSFDataValidation
+    (addressList, dvConstraint);
+  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");
+  CellRangeAddressList addressList = new CellRangeAddressList(
+    0, 0, 0, 0);
+  DVConstraint dvConstraint = DVConstraint.createExplicitListConstraint(
+    new String[]{"10", "20", "30"});
+  DataValidation dataValidation = new HSSFDataValidation
+    (addressList, dvConstraint);
+  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>        
+      
+<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");
+  namedRange.setRefersToFormula("'Sheet1'!$A$1:$A$3");
+  dvConstraint = DVConstraint.createFormulaListConstraint("list1");
+      </pre>
+<p>and nor will this:</p>
+<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)</h4>
+</div>
+      
+
+<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, 
+in a few places as much of the support for data validations in the xssf stream was 
+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");
+  XSSFDataValidationHelper dvHelper = new XSSFDataValidationHelper(sheet);
+  XSSFDataValidationConstraint dvConstraint = (XSSFDataValidationConstraint)
+    dvHelper.createExplicitListConstraint(new String[]{"11", "21", "31"});
+  CellRangeAddressList addressList = new CellRangeAddressList(0, 0, 0, 0);
+  XSSFDataValidation validation =(XSSFDataValidation)dvHelper.createValidation(
+    dvConstraint, addressList);
+
+  // Here the boolean value false is passed to the setSuppressDropDownArrow()
+  // method. In the hssf.usermodel examples above, the value passed to this
+  // method is true.            
+  validation.setSuppressDropDownArrow(false);
+
+  // Note this extra method call. If this method call is omitted, or if the
+  // boolean value false is passed, then Excel will not validate the value the
+  // user enters into the cell.
+  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");
+  XSSFDataValidationHelper dvHelper = new XSSFDataValidationHelper(sheet);
+  XSSFDataValidationConstraint dvConstraint = (XSSFDataValidationConstraint)
+    dvHelper.createExplicitListConstraint(new String[]{"11", "21", "31"});
+  CellRangeAddressList addressList = new CellRangeAddressList(0, 0, 0, 0);
+  XSSFDataValidation validation = (XSSFDataValidation)dvHelper.createValidation(
+    dvConstraint, addressList);
+  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)
+    dvHelper.createNumericConstraint(
+      XSSFDataValidationConstraint.ValidationType.INTEGER,
+      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(
+      XSSFDataValidationConstraint.ValidationType.INTEGER,
+      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");
+  name.setRefersToFormula("$B$1:$F$1");
+  XSSFDataValidationHelper dvHelper = new XSSFDataValidationHelper(sheet);
+  XSSFDataValidationConstraint dvConstraint = (XSSFDataValidationConstraint)
+    dvHelper.createFormulaListConstraint("data");
+  CellRangeAddressList addressList = new CellRangeAddressList(
+    0, 0, 0, 0);
+  XSSFDataValidation validation = (XSSFDataValidation)
+    dvHelper.createValidation(dvConstraint, addressList);
+  validation.setSuppressDropDownArrow(true);
+  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</h4>
+</div>
+
+<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");
+  DataValidationHelper dvHelper = sheet.getDataValidationHelper();
+  DataValidationConstraint dvConstraint = dvHelper.createExplicitListConstraint(
+    new String[]{"13", "23", "33"});
+  CellRangeAddressList addressList = new CellRangeAddressList(0, 0, 0, 0);            
+  DataValidation validation = dvHelper.createValidation(
+    dvConstraint, addressList);
+  // Note the check on the actual type of the DataValidation object.
+  // If it is an instance of the XSSFDataValidation class then the
+  // boolean value 'false' must be passed to the setSuppressDropDownArrow()
+  // method and an explicit call made to the setShowErrorBox() method.
+  if(validation instanceof XSSFDataValidation) {
+    validation.setSuppressDropDownArrow(false);
+    validation.setShowErrorBox(true);
+  }
+  else {
+    // If the Datavalidation contains an instance of the HSSFDataValidation
+    // class then 'true' should be passed to the setSuppressDropDownArrow()
+    // method and the call to setShowErrorBox() is not necessary.
+    validation.setSuppressDropDownArrow(true);
+  }
+  sheet.addValidationData(validation);
+</pre>
+
+
+<p>
+<strong>Drop Down Lists:</strong>
+</p>
+
+

[... 381 lines stripped ...]


---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@poi.apache.org
For additional commands, e-mail: commits-help@poi.apache.org


Mime
View raw message