<!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 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 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 align="right">
<div 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> | <a title="PDF file of this page" href="quick-guide.pdf">PDF</a>
</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</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="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="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>
<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"><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">
<br>
Search poi</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 Features</h1>
</div>
<div class="h3">
<div class="h3">
<h3>Busy Developers' Guide to Features</h3>
</div>
<p>
Want to use HSSF read and write spreadsheets in a hurry? This guide is for you. If you're after
more in-depth coverage of the HSSF user-API please consult the <a href="how-to.html">HOWTO</a>
guide as it contains actual descriptions of how to use this stuff.
</p>
<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="#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="#FrillsAndFills">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>
</ul>
<div class="h4">
<h4>Features</h4>
</div>
<a name="NewWorkbook"></a>
<div class="h2">
<h2>New Workbook</h2>
</div>
<pre class="code">
HSSFWorkbook wb = new HSSFWorkbook();
FileOutputStream fileOut = new FileOutputStream("workbook.xls");
wb.write(fileOut);
fileOut.close();
</pre>
<a name="NewSheet"></a>
<div class="h2">
<h2>New Sheet</h2>
</div>
<pre class="code">
HSSFWorkbook wb = new HSSFWorkbook();
HSSFSheet sheet1 = wb.createSheet("new sheet");
HSSFSheet sheet2 = wb.createSheet("second sheet");
FileOutputStream fileOut = new FileOutputStream("workbook.xls");
wb.write(fileOut);
fileOut.close();
</pre>
<a name="CreateCells"></a>
<div class="h2">
<h2>Creating Cells</h2>
</div>
<pre class="code">
HSSFWorkbook wb = new HSSFWorkbook();
HSSFSheet sheet = wb.createSheet("new sheet");
// Create a row and put some cells in it. Rows are 0 based.
HSSFRow row = sheet.createRow((short)0);
// Create a cell and put a value in it.
HSSFCell cell = row.createCell((short)0);
cell.setCellValue(1);
// Or do it on one line.
row.createCell((short)1).setCellValue(1.2);
row.createCell((short)2).setCellValue("This is a string");
row.createCell((short)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>
<div class="h2">
<h2>Creating Date Cells</h2>
</div>
<pre class="code">
HSSFWorkbook wb = new HSSFWorkbook();
HSSFSheet sheet = wb.createSheet("new sheet");
// Create a row and put some cells in it. Rows are 0 based.
HSSFRow row = sheet.createRow((short)0);
// Create a cell and put a date value in it. The first cell is not styled
// as a date.
HSSFCell cell = row.createCell((short)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.
HSSFCellStyle cellStyle = wb.createCellStyle();
cellStyle.setDataFormat(HSSFDataFormat.getBuiltinFormat("m/d/yy h:mm"));
cell = row.createCell((short)1);
cell.setCellValue(new Date());
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>
<div class="h2">
<h2>Working with different types of cells</h2>
</div>
<pre class="code">
HSSFWorkbook wb = new HSSFWorkbook();
HSSFSheet sheet = wb.createSheet("new sheet");
HSSFRow row = sheet.createRow((short)2);
row.createCell((short) 0).setCellValue(1.1);
row.createCell((short) 1).setCellValue(new Date());
row.createCell((short) 2).setCellValue("a string");
row.createCell((short) 3).setCellValue(true);
row.createCell((short) 4).setCellType(HSSFCell.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>
<div class="h2">
<h2>Demonstrates various alignment options</h2>
</div>
<pre class="code">
public static void main(String[] args)
throws IOException
{
HSSFWorkbook wb = new HSSFWorkbook();
HSSFSheet sheet = wb.createSheet("new sheet");
HSSFRow row = sheet.createRow((short) 2);
createCell(wb, row, (short) 0, HSSFCellStyle.ALIGN_CENTER);
createCell(wb, row, (short) 1, HSSFCellStyle.ALIGN_CENTER_SELECTION);
createCell(wb, row, (short) 2, HSSFCellStyle.ALIGN_FILL);
createCell(wb, row, (short) 3, HSSFCellStyle.ALIGN_GENERAL);
createCell(wb, row, (short) 4, HSSFCellStyle.ALIGN_JUSTIFY);
createCell(wb, row, (short) 5, HSSFCellStyle.ALIGN_LEFT);
createCell(wb, row, (short) 6, HSSFCellStyle.ALIGN_RIGHT);
// Write the output to a file
FileOutputStream fileOut = new FileOutputStream("workbook.xls");
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 align the alignment for the cell.
*/
private static void createCell(HSSFWorkbook wb, HSSFRow row, short column, short align)
{
HSSFCell cell = row.createCell(column);
cell.setCellValue("Align It");
HSSFCellStyle cellStyle = wb.createCellStyle();
cellStyle.setAlignment(align);
cell.setCellStyle(cellStyle);
}
</pre>
<a name="Borders"></a>
<div class="h2">
<h2>Working with borders</h2>
</div>
<pre class="code">
HSSFWorkbook wb = new HSSFWorkbook();
HSSFSheet sheet = wb.createSheet("new sheet");
// Create a row and put some cells in it. Rows are 0 based.
HSSFRow row = sheet.createRow((short) 1);
// Create a cell and put a value in it.
HSSFCell cell = row.createCell((short) 1);
cell.setCellValue(4);
// Style the cell with borders all around.
HSSFCellStyle style = wb.createCellStyle();
style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
style.setBottomBorderColor(HSSFColor.BLACK.index);
style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
style.setLeftBorderColor(HSSFColor.GREEN.index);
style.setBorderRight(HSSFCellStyle.BORDER_THIN);
style.setRightBorderColor(HSSFColor.BLUE.index);
style.setBorderTop(HSSFCellStyle.BORDER_MEDIUM_DASHED);
style.setTopBorderColor(HSSFColor.BLACK.index);
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>
<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. HSSFRow defines a
<em>CellIterator</em> inner class to handle iterating over
the cells (get one with a call to <em>row.cellIterator()</em>),
and HSSFSheet provides a <em>rowIterator()</em> method to
give an iterator over all the rows.</p>
<p>(Unfortunately, due to the broken and
backwards-incompatible way that Java 5 foreach loops were
implemented, it isn't possible to use them on a codebase
that supports Java 1.4, as POI does)</p>
<pre class="code">
HSSFSheet sheet = wb.getSheetAt(0);
for (Iterator rit = sheet.rowIterator(); rit.hasNext(); ) {
HSSFRow row = (HSSFRow)rit.next();
for (Iterator cit = row.cellIterator(); cit.hasNext(); ) {
HSSFCell cell = (HSSFCell)cit.next();
// Do something here
}
}
</pre>
<pre class="code">
HSSFSheet sheet = wb.getSheetAt(0);
for (Iterator<HSSFRow> rit = (Iterator<HSSFRow>)sheet.rowIterator(); rit.hasNext(); ) {
HSSFRow row = rit.next();
for (Iterator<HSSFCell> cit = (Iterator<HSSFCell>)row.cellIterator(); cit.hasNext(); ) {
HSSFCell cell = cit.next();
// Do something here
}
}
</pre>
<div class="h2">
<h2>Iterate over rows and cells using Java 1.5 foreach loops - OOXML Branch Only</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 HSSFSheet and HSSFRow
implement <em>java.lang.Iterable</em> to allow foreach
loops. For HSSFRow this allows access to the
<em>CellIterator</em> inner class to handle iterating over
the cells, and for HSSFSheet gives the
<em>rowIterator()</em> to iterator over all the rows.</p>
<pre class="code">
HSSFSheet sheet = wb.getSheetAt(0);
for (HSSFRow row : sheet.rowIterator()) {
for (HSSFCell cell : row.cellIterator()) {
// Do something here
}
}
</pre>
<div class="frame note">
<div class="label">Note</div>
<div class="content">This only works on the OOXML branch of POI</div>
</div>
<a name="TextExtraction"></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/scratchpad/examples/src/org/apache/poi/hssf/eventusermodel/examples/XLS2CSVmra.java</em>
</p>
<a name="FillsAndFrills"></a>
<div class="h2">
<h2>Fills and colors</h2>
</div>
<pre class="code">
HSSFWorkbook wb = new HSSFWorkbook();
HSSFSheet sheet = wb.createSheet("new sheet");
// Create a row and put some cells in it. Rows are 0 based.
HSSFRow row = sheet.createRow((short) 1);
// Aqua background
HSSFCellStyle style = wb.createCellStyle();
style.setFillBackgroundColor(HSSFColor.AQUA.index);
style.setFillPattern(HSSFCellStyle.BIG_SPOTS);
HSSFCell 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(HSSFColor.ORANGE.index);
style.setFillPattern(HSSFCellStyle.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>
<div class="h2">
<h2>Merging cells</h2>
</div>
<pre class="code">
HSSFWorkbook wb = new HSSFWorkbook();
HSSFSheet sheet = wb.createSheet("new sheet");
HSSFRow row = sheet.createRow((short) 1);
HSSFCell cell = row.createCell((short) 1);
cell.setCellValue("This is a test of merging");
sheet.addMergedRegion(new Region(1,(short)1,1,(short)2));
// Write the output to a file
FileOutputStream fileOut = new FileOutputStream("workbook.xls");
wb.write(fileOut);
fileOut.close();
</pre>
<a name="WorkingWithFonts"></a>
<div class="h2">
<h2>Working with fonts</h2>
</div>
<pre class="code">
HSSFWorkbook wb = new HSSFWorkbook();
HSSFSheet sheet = wb.createSheet("new sheet");
// Create a row and put some cells in it. Rows are 0 based.
HSSFRow row = sheet.createRow((short) 1);
// Create a new font and alter it.
HSSFFont 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.
HSSFCellStyle style = wb.createCellStyle();
style.setFont(font);
// Create a cell and put a value in it.
HSSFCell cell = row.createCell((short) 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 < 10000; i++) {
HSSFRow row = sheet.createRow(i);
HSSFCell cell = row.createCell((short) 0);
HSSFCellStyle style = workbook.createCellStyle();
HSSFFont font = workbook.createFont();
font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
style.setFont(font);
cell.setCellStyle(style);
}
</pre>
<p>
<strong>Correct:</strong>
</p>
<pre class="code">
HSSFCellStyle style = workbook.createCellStyle();
HSSFFont font = workbook.createFont();
font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
style.setFont(font);
for (int i = 0; i < 10000; i++) {
HSSFRow row = sheet.createRow(i);
HSSFCell cell = row.createCell((short) 0);
cell.setCellStyle(style);
}
</pre>
<a name="CustomColors"></a>
<div class="h2">
<h2>Custom colors</h2>
</div>
<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>
<a name="ReadWriteWorkbook"></a>
<div class="h2">
<h2>Reading and Rewriting Workbooks</h2>
</div>
<pre class="code">
POIFSFileSystem fs =
new POIFSFileSystem(new FileInputStream("workbook.xls"));
HSSFWorkbook wb = new HSSFWorkbook(fs);
HSSFSheet sheet = wb.getSheetAt(0);
HSSFRow row = sheet.getRow(2);
HSSFCell cell = row.getCell((short)3);
if (cell == null)
cell = row.createCell((short)3);
cell.setCellType(HSSFCell.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>
<div class="h2">
<h2>Using newlines in cells</h2>
</div>
<pre class="code">
HSSFWorkbook wb = new HSSFWorkbook();
HSSFSheet s = wb.createSheet();
HSSFRow r = null;
HSSFCell c = null;
HSSFCellStyle cs = wb.createCellStyle();
HSSFFont f = wb.createFont();
HSSFFont f2 = wb.createFont();
cs = wb.createCellStyle();
cs.setFont( f2 );
//Word Wrap MUST be turned on
cs.setWrapText( true );
r = s.createRow( (short) 2 );
r.setHeight( (short) 0x349 );
c = r.createCell( (short) 2 );
c.setCellType( HSSFCell.CELL_TYPE_STRING );
c.setCellValue( "Use \n with word wrap on to create a new line" );
c.setCellStyle( cs );
s.setColumnWidth( (short) 2, (short) ( ( 50 * 8 ) / ( (double) 1 / 20 ) ) );
FileOutputStream fileOut = new FileOutputStream( "workbook.xls" );
wb.write( fileOut );
fileOut.close();</pre>
<a name="DataFormats"></a>
<div class="h2">
<h2>Data Formats</h2>
</div>
<pre class="code">
HSSFWorkbook wb = new HSSFWorkbook();
HSSFSheet sheet = wb.createSheet("format sheet");
HSSFCellStyle style;
HSSFDataFormat format = wb.createDataFormat();
HSSFRow row;
HSSFCell 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>
<div class="h2">
<h2>Fit Sheet to One Page</h2>
</div>
<pre class="code">
HSSFWorkbook wb = new HSSFWorkbook();
HSSFSheet sheet = wb.createSheet("format sheet");
HSSFPrintSetup 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>
<div class="h2">
<h2>Set Print Area</h2>
</div>
<pre class="code">
HSSFWorkbook wb = new HSSFWorkbook();
HSSFSheet sheet = wb.createSheet("Sheet1");
wb.setPrintArea(0, "$A$1:$C$2");
//sets the print area for the first sheet
//Alternatively:
//wb.setPrintArea(0, 0, 1, 0, 0) is equivalent to using the name reference (See the JavaDocs for more details)
// Create various cells and rows for spreadsheet.
FileOutputStream fileOut = new FileOutputStream("workbook.xls");
wb.write(fileOut);
fileOut.close();
</pre>
<a name="FooterPageNumbers"></a>
<div class="h2">
<h2>Set Page Numbers on Footer</h2>
</div>
<pre class="code">
HSSFWorkbook wb = new HSSFWorkbook();
HSSFSheet sheet = wb.createSheet("format sheet");
HSSFFooter footer = sheet.getFooter()
footer.setRight( "Page " + HSSFFooter.page() + " of " + HSSFFooter.numPages() );
// Create various cells and rows for spreadsheet.
FileOutputStream fileOut = new FileOutputStream("workbook.xls");
wb.write(fileOut);
fileOut.close();
</pre>
<a name="ConvenienceFunctions"></a>
<div class="h2">
<h2>Using the Convenience Functions</h2>
</div>
<p>
The convenience functions live in contrib and provide
utility features such as setting borders around merged
regions and changing style attributes without explicitly
creating new styles.
</p>
<pre class="code">
HSSFWorkbook wb = new HSSFWorkbook();
HSSFSheet sheet1 = wb.createSheet( "new sheet" );
// Create a merged region
HSSFRow row = sheet1.createRow( (short) 1 );
HSSFRow row2 = sheet1.createRow( (short) 2 );
HSSFCell cell = row.createCell( (short) 1 );
cell.setCellValue( "This is a test of merging" );
Region region = new Region( 1, (short) 1, 4, (short) 4 );
sheet1.addMergedRegion( region );
// Set the border and border colors.
final short borderMediumDashed = HSSFCellStyle.BORDER_MEDIUM_DASHED;
HSSFRegionUtil.setBorderBottom( borderMediumDashed,
region, sheet1, wb );
HSSFRegionUtil.setBorderTop( borderMediumDashed,
region, sheet1, wb );
HSSFRegionUtil.setBorderLeft( borderMediumDashed,
region, sheet1, wb );
HSSFRegionUtil.setBorderRight( borderMediumDashed,
region, sheet1, wb );
HSSFRegionUtil.setBottomBorderColor(HSSFColor.AQUA.index, region, sheet1, wb);
HSSFRegionUtil.setTopBorderColor(HSSFColor.AQUA.index, region, sheet1, wb);
HSSFRegionUtil.setLeftBorderColor(HSSFColor.AQUA.index, region, sheet1, wb);
HSSFRegionUtil.setRightBorderColor(HSSFColor.AQUA.index, region, sheet1, wb);
// Shows some usages of HSSFCellUtil
HSSFCellStyle style = wb.createCellStyle();
style.setIndention((short)4);
HSSFCellUtil.createCell(row, 8, "This is the value of the cell", style);
HSSFCell cell2 = HSSFCellUtil.createCell( row2, 8, "This is the value of the cell");
HSSFCellUtil.setAlignment(cell2, wb, HSSFCellStyle.ALIGN_CENTER);
// Write out the workbook
FileOutputStream fileOut = new FileOutputStream( "workbook.xls" );
wb.write( fileOut );
fileOut.close();
</pre>
<a name="ShiftRows"></a>
<div class="h2">
<h2>Shift rows up or down on a sheet</h2>
</div>
<pre class="code">
HSSFWorkbook wb = new HSSFWorkbook();
HSSFSheet 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);
FileOutputStream fileOut = new FileOutputStream("workbook.xls");
wb.write(fileOut);
fileOut.close();
</pre>
<a name="SelectSheet"></a>
<div class="h2">
<h2>Set a sheet as selected</h2>
</div>
<pre class="code">
HSSFWorkbook wb = new HSSFWorkbook();
HSSFSheet sheet = wb.createSheet("row sheet");
sheet.setSelected(true);
// Create various cells and rows for spreadsheet.
FileOutputStream fileOut = new FileOutputStream("workbook.xls");
wb.write(fileOut);
fileOut.close();
</pre>
<a name="Zoom"></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">
HSSFWorkbook wb = new HSSFWorkbook();
HSSFSheet sheet1 = wb.createSheet("new sheet");
sheet1.setZoom(3,4); // 75 percent magnification
FileOutputStream fileOut = new FileOutputStream("workbook.xls");
wb.write(fileOut);
fileOut.close();
</pre>
<a name="Splits"></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, HSSFSheet.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 HSSFSheet.PANE_LOWER_LEFT,
PANE_LOWER_RIGHT, PANE_UPPER_RIGHT or PANE_UPPER_LEFT.
</p>
<pre class="code">
HSSFWorkbook wb = new HSSFWorkbook();
HSSFSheet sheet1 = wb.createSheet("new sheet");
HSSFSheet sheet2 = wb.createSheet("second sheet");
HSSFSheet sheet3 = wb.createSheet("third sheet");
HSSFSheet 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, HSSFSheet.PANE_LOWER_LEFT );
FileOutputStream fileOut = new FileOutputStream("workbook.xls");
wb.write(fileOut);
fileOut.close();
</pre>
<a name="Repeating"></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 setRepeatingRowsAndColumns()
function in the HSSFWorkbook class.
</p>
<p>
This function Contains 5 parameters.
The first parameter is the index to the sheet (0 = first sheet).
The second and third parameters specify the range for the columns to repreat.
To stop the columns from repeating pass in -1 as the start and end column.
The fourth and fifth parameters specify the range for the rows to repeat.
To stop the columns from repeating pass in -1 as the start and end rows.
</p>
<pre class="code">
HSSFWorkbook wb = new HSSFWorkbook();
HSSFSheet sheet1 = wb.createSheet("new sheet");
HSSFSheet sheet2 = wb.createSheet("second sheet");
// Set the columns to repeat from column 0 to 2 on the first sheet
wb.setRepeatingRowsAndColumns(0,0,2,-1,-1);
// Set the the repeating rows and columns on the second sheet.
wb.setRepeatingRowsAndColumns(1,4,5,1,2);
FileOutputStream fileOut = new FileOutputStream("workbook.xls");
wb.write(fileOut);
fileOut.close();
</pre>
<a name="HeaderFooter"></a>
<div class="h2">
<h2>Headers and Footers</h2>
</div>
<p>
Example is for headers but applies directly to footers.
</p>
<pre class="code">
HSSFWorkbook wb = new HSSFWorkbook();
HSSFSheet sheet = wb.createSheet("new sheet");
HSSFHeader 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>
<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>
<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>
<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>
<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">
HSSFWorkbook wb = new HSSFWorkbook();
HSSFSheet 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>
<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 the drawing patriarch. This is the top level container for
// all shapes. This will clear out any existing shapes for that sheet.
HSSFPatriarch patriarch = sheet5.createDrawingPatriarch();
HSSFClientAnchor anchor;
anchor = new HSSFClientAnchor(0,0,0,255,(short)2,2,(short)4,7);
anchor.setAnchorType( 2 );
patriarch.createPicture(anchor, loadPicture( "src/resources/logos/logoKarmokar4.png", wb ));
</pre>
<p>Creating an image and setting its anchor to the actual width and height:</p>
<pre class="code">
HSSFPatriarch patriarch = sheet5.createDrawingPatriarch();
HSSFPicture picture = patriarch.createPicture(new HSSFClientAnchor(), loadPicture( "src/resources/logos/logoKarmokar4.png", wb ));
picture.resize();
</pre>
<p>or</p>
<pre class="code">
HSSFPatriarch patriarch = sheet5.createDrawingPatriarch();
HSSFPicture picture = patriarch.createPicture(new HSSFClientAnchor(), loadPicture( "src/resources/logos/logoKarmokar4.png", wb ));
HSSFClientAnchor preferredSize = picture.getPreferredSize();
picture.setAnchor(preferredSize);
</pre>
<div class="frame warning">
<div class="label">Warning</div>
<div class="content">
HSSFPicture.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">
HSSFWorkbook wb;
List lst = wb.getAllPictures();
for (Iterator it = lst.iterator(); it.hasNext(); ) {
HSSFPictureData pict = (HSSFPictureData)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>
<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
& org.apache.poi.hssf.util.AreaReference are used.
</p>
<p>
Creating Named Range / Named Cell
</p>
<pre class="code">
// setup code
String sname = "TestSheet", cname = "TestName", cvalue = "TestVal";
HSSFWorkbook wb = new HSSFWorkbook();
HSSFSheet sheet = wb.createSheet(sname);
sheet.createRow(0).createCell((short) 0).setCellValue(cvalue);
// 1. create named range for a single cell using areareference
HSSFName namedCell = wb.createName();
namedCell.setNameName(cname);
String reference = sname+"!A1:A1"; // area reference
namedCell.setReference(reference);
// 2. create named range for a single cell using cellreference
HSSFName namedCell = wb.createName();
namedCell.setNameName(cname);
String reference = sname+"!A1"; // cell reference
namedCell.setReference(reference);
// 3. create named range for an area using AreaReference
HSSFName namedCell = wb.createName();
namedCell.setNameName(cname);
String reference = sname+"!A1:C5"; // area reference
namedCell.setReference(reference);
</pre>
<p>
Reading from Named Range / Named Cell
</p>
<pre class="code">
// setup code
String cname = "TestName";
HSSFWorkbook wb = getMyWorkbook(); // retrieve workbook
// retrieve the named range
int namedCellIdx = wb.getNameIndex(cellName);
HSSFName aNamedCell = wb.getNameAt(namedCellIdx);
// retrieve the cell at the named range and test its contents
AreaReference aref = new AreaReference(aNamedCell.getReference());
CellReference[] crefs = aref.getAllReferencedCells();
for (int i=0; i<crefs.length; i++) {
HSSFSheet s = wb.getSheet(crefs[i].getSheetName());
HSSFRow r = sheet.getRow(crefs[i].getRow());
HSSFCell 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";
HSSFWorkbook wb = getMyWorkbook(); // retrieve workbook
// Retrieve the named range
// Will be something like "$C$10,$D$12:$D$14";
int namedCellIdx = wb.getNameIndex(cellName);
HSSFName 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.getReference());
for (int i=0; i<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<crefs.length; j++) {
// Check it turns into real stuff
HSSFSheet s = wb.getSheet(crefs[j].getSheetName());
HSSFRow r = s.getRow(crefs[j].getRow());
HSSFCell 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(hssfName.isDeleted()){
//named range points to a deleted cell.
} else {
AreaReference ref = new AreaReference(hssfName.getReference());
}
</pre>
<a name="CellComments"></a>
<div class="h3">
<h3>Cell Comments</h3>
</div>
<p>
In Excel a comment is a kind of a text shape,
so inserting a comment is very similar to placing a text box in a worksheet:
</p>
<pre class="code">
HSSFWorkbook wb = new HSSFWorkbook();
HSSFSheet sheet = wb.createSheet("Cell comments in POI HSSF");
// Create the drawing patriarch. This is the top level container for all shapes including cell comments.
HSSFPatriarch patr = sheet.createDrawingPatriarch();
//create a cell in row 3
HSSFCell cell1 = sheet.createRow(3).createCell((short)1);
cell1.setCellValue(new HSSFRichTextString("Hello, World"));
//anchor defines size and position of the comment in worksheet
HSSFComment comment1 = patr.createComment(new HSSFClientAnchor(0, 0, 0, 0, (short)4, 2, (short) 6, 5));
// set text in the comment
comment1.setString(new HSSFRichTextString("We can set comments in POI"));
//set comment author.
//you can see it in the status bar when moving mouse over the commented cell
comment1.setAuthor("Apache Software Foundation");
// The first way to assign comment to a cell is via HSSFCell.setCellComment method
cell1.setCellComment(comment1);
//create another cell in row 6
HSSFCell cell2 = sheet.createRow(6).createCell((short)1);
cell2.setCellValue(36.6);
HSSFComment comment2 = patr.createComment(new HSSFClientAnchor(0, 0, 0, 0, (short)4, 8, (short) 6, 11));
//modify background color of the comment
comment2.setFillColor(204, 236, 255);
HSSFRichTextString string = new HSSFRichTextString("Normal body temperature");
//apply custom font to the text in the comment
HSSFFont font = wb.createFont();
font.setFontName("Arial");
font.setFontHeightInPoints((short)10);
font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
font.setColor(HSSFColor.RED.index);
string.applyFont(font);
comment2.setString(string);
//by default comments are hidden. This one is always visible.
comment2.setVisible(true);
comment2.setAuthor("Bill Gates");
/**
* The second way to assign comment to a cell is to implicitly specify its row and column.
* Note, it is possible to set row and column of a non-existing cell.
* It works, the commnet is visible.
*/
comment2.setRow(6);
comment2.setColumn((short)1);
FileOutputStream out = new FileOutputStream("poi_comment.xls");
wb.write(out);
out.close();
</pre>
<p>
Reading cell comments
</p>
<pre class="code">
HSSFCell cell = sheet.get(3).getColumn((short)1);
HSSFComment comment = cell.getCellComment();
if (comment != null) {
HSSFRichTextString 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>
<div class="h3">
<h3>Adjust column width to fit the contents</h3>
</div>
<pre class="code">
HSSFSheet sheet = workbook.getSheetAt(0);
sheet.autoSizeColumn((short)0); //adjust width of the first column
sheet.autoSizeColumn((short)1); //adjust width of the second column
</pre>
<div class="frame warning">
<div class="label">Warning</div>
<div class="content">
To calculate column width HSSFSheet.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>
(either via <span class="codefrag">-Djava.awt.headless=true</span> startup parameter or via <span class="codefrag">System.setProperty("java.awt.headless", "true")</span>).
</div>
</div>
<a name="Hyperlinks"></a>
<div class="h3">
<h3>How to read hyperlinks</h3>
</div>
<pre class="code">
HSSFSheet sheet = workbook.getSheetAt(0);
HSSFCell cell = sheet.getRow(0).getCell((short)0);
HSSFHyperlink link = cell.getHyperlink();
if(link != null){
System.out.println(link.getAddress());
}
</pre>
<div class="h3">
<h3>How to create hyperlinks</h3>
</div>
<pre class="code">
HSSFWorkbook wb = new HSSFWorkbook();
//cell style for hyperlinks
//by default hypelrinks are blue and underlined
HSSFCellStyle hlink_style = wb.createCellStyle();
HSSFFont hlink_font = wb.createFont();
hlink_font.setUnderline(HSSFFont.U_SINGLE);
hlink_font.setColor(HSSFColor.BLUE.index);
hlink_style.setFont(hlink_font);
HSSFCell cell;
HSSFSheet sheet = wb.createSheet("Hyperlinks");
//URL
cell = sheet.createRow(0).createCell((short)0);
cell.setCellValue("URL Link");
HSSFHyperlink link = new HSSFHyperlink(HSSFHyperlink.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 = new HSSFHyperlink(HSSFHyperlink.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 = new HSSFHyperlink(HSSFHyperlink.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
HSSFSheet 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");
link = new HSSFHyperlink(HSSFHyperlink.LINK_DOCUMENT);
link.setAddress("'Target Sheet'!A1");
cell.setHyperlink(link);
cell.setCellStyle(hlink_style);
FileOutputStream out = new FileOutputStream("hssf-links.xls");
wb.write(out);
out.close();
</pre>
<div id="authors" align="right">by Glen Stampoultzis, Yegor Kozlov</div>
</div>
</div>
</div>
</td>
<!--================= end Content ==================-->
</tr>
</tbody>
</table>
<!--================= end Main ==================-->
<!--================= start Footer ==================-->
<div id="footer">
<table summary="footer" cellspacing="0" cellpadding="4" width="100%" border="0">
<tbody>
<tr>
<!--================= start Copyright ==================-->
<td colspan="2">
<div align="center">
<div class="copyright">
Copyright © 2002-2007 The Apache Software Foundation. All rights reserved.
</div>
</div>
</td>
<!--================= end Copyright ==================-->
</tr>
<tr>
<td align="left">
<!--================= start Host ==================-->
<!--================= end Host ==================--></td><td align="right">
<!--================= start Credits ==================-->
<div align="right">
<div class="credit"></div>
</div>
<!--================= end Credits ==================-->
</td>
</tr>
</tbody>
</table>
</div>
<!--================= end Footer ==================-->
</body>
</html>
distrib
>
Mandriva
>
2010.0
>
i586
>
media
>
contrib-release
>
by-pkgid
>
0a38b107381e947533adbb55ab5f647c
>
files
>
67
