<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<META http-equiv="Content-Type" content="text/html; charset=UTF-8">
<!--*** This is a generated file. Do not edit. ***-->
<link rel="stylesheet" href="../skin/tigris.css" type="text/css">
<link rel="stylesheet" href="../skin/mysite.css" type="text/css">
<link rel="stylesheet" href="../skin/site.css" type="text/css">
<link media="print" rel="stylesheet" href="../skin/print.css" type="text/css">
<title>Upgrading to POI 3.5, including converting existing HSSF Usermodel code to SS Usermodel (for XSSF and HSSF)</title>
</head>
<body bgcolor="white" class="composite">
<!--================= start Banner ==================-->
<div id="banner">
<table width="100%" cellpadding="8" cellspacing="0" summary="banner" border="0">
<tbody>
<tr>
<!--================= start Group Logo ==================-->
<td width="50%" align="left">
<div class="groupLogo">
<a href="http://poi.apache.org"><img border="0" class="logoImage" alt="Apache POI" src="../resources/images/group-logo.jpg"></a>
</div>
</td>
<!--================= end Group Logo ==================-->
<!--================= start Project Logo ==================--><td width="50%" align="right">
<div align="right" class="projectLogo">
<a href="http://poi.apache.org/"><img border="0" class="logoImage" alt="POI" src="../resources/images/project-logo.jpg"></a>
</div>
</td>
<!--================= end Project Logo ==================-->
</tr>
</tbody>
</table>
</div>
<!--================= end Banner ==================-->
<!--================= start Main ==================-->
<table width="100%" cellpadding="0" cellspacing="0" border="0" summary="nav" id="breadcrumbs">
<tbody>
<!--================= start Status ==================-->
<tr class="status">
<td>
<!--================= start BreadCrumb ==================--><a href="http://www.apache.org/">Apache</a> | <a href="http://poi.apache.org/">POI</a><a href=""></a>
<!--================= end BreadCrumb ==================--></td><td id="tabs">
<!--================= start Tabs ==================-->
<div class="tab">
<span class="selectedTab"><a class="base-selected" href="../index.html">Home</a></span> | <script language="Javascript" type="text/javascript">
function printit() {
if (window.print) {
window.print() ;
} else {
var WebBrowser = '<OBJECT ID="WebBrowser1" WIDTH="0" HEIGHT="0" CLASSID="CLSID:8856F961-340A-11D0-A96B-00C04FD705A2"></OBJECT>';
document.body.insertAdjacentHTML('beforeEnd', WebBrowser);
WebBrowser1.ExecWB(6, 2);//Use a 1 vs. a 2 for a prompting dialog box WebBrowser1.outerHTML = "";
}
}
</script><script language="Javascript" type="text/javascript">
var NS = (navigator.appName == "Netscape");
var VERSION = parseInt(navigator.appVersion);
if (VERSION > 3) {
document.write(' <a title="PRINT this page OUT" href="javascript:printit()">PRINT</a>');
}
</script>
</div>
<!--================= end Tabs ==================-->
</td>
</tr>
</tbody>
</table>
<!--================= end Status ==================-->
<table id="main" width="100%" cellpadding="8" cellspacing="0" summary="" border="0">
<tbody>
<tr valign="top">
<!--================= start Menu ==================-->
<td id="leftcol">
<div id="navcolumn">
<div class="menuBar">
<div class="menu">
<span class="menuLabel">Apache POI</span>
<div class="menuItem">
<a href="../index.html">Top</a>
</div>
</div>
<div class="menu">
<span class="menuLabel">HSSF+XSSF</span>
<div class="menuItem">
<a href="index.html">Overview</a>
</div>
<div class="menuItem">
<a href="quick-guide.html">Quick Guide</a>
</div>
<div class="menuItem">
<a href="how-to.html">HOWTO</a>
</div>
<div class="menuItem">
<span class="menuSelected">HSSF to SS Converting</span>
</div>
<div class="menuItem">
<a href="formula.html">Formula Support</a>
</div>
<div class="menuItem">
<a href="eval.html">Formula Evaluation</a>
</div>
<div class="menuItem">
<a href="eval-devguide.html">Eval Dev Guide</a>
</div>
<div class="menuItem">
<a href="examples.html">Examples</a>
</div>
<div class="menuItem">
<a href="use-case.html">Use Case</a>
</div>
<div class="menuItem">
<a href="diagrams.html">Pictorial Docs</a>
</div>
<div class="menuItem">
<a href="limitations.html">Limitations</a>
</div>
<div class="menuItem">
<a href="user-defined-functions.html">User Defined Functions</a>
</div>
<div class="menuItem">
<a href="excelant.html">ExcelAnt Tests</a>
</div>
</div>
<div class="menu">
<span class="menuLabel">Contributer's Guide</span>
<div class="menuItem">
<a href="hacking-hssf.html">Hacking HSSF</a>
</div>
<div class="menuItem">
<a href="record-generator.html">Record Generator</a>
</div>
<div class="menuItem">
<a href="chart.html">Charts</a>
</div>
</div>
</div>
</div>
<form target="_blank" action="http://www.google.com/search" method="get">
<table summary="search" border="0" cellspacing="0" cellpadding="0">
<tr>
<td><img height="1" width="1" alt="" src="../skin/images/spacer.gif" class="spacer"></td><td nowrap="nowrap">
Search Apache POI<br>
<input value="poi.apache.org" name="sitesearch" type="hidden"><input size="10" name="q" id="query" type="text"><img height="1" width="5" alt="" src="../skin/images/spacer.gif" class="spacer"><input name="Search" value="GO" type="submit"></td><td><img height="1" width="1" alt="" src="../skin/images/spacer.gif" class="spacer"></td>
</tr>
<tr>
<td colspan="3"><img height="7" width="1" alt="" src="../skin/images/spacer.gif" class="spacer"></td>
</tr>
<tr>
<td class="bottom-left-thick"></td><td bgcolor="#a5b6c6"><img height="1" width="1" alt="" src="../skin/images/spacer.gif" class="spacer"></td><td class="bottom-right-thick"></td>
</tr>
</table>
</form>
</td>
<!--================= end Menu ==================-->
<!--================= start Content ==================--><td>
<div id="bodycol">
<div class="app">
<div align="center">
<h1>Upgrading to POI 3.5, including converting existing HSSF Usermodel code to SS Usermodel (for XSSF and HSSF)</h1>
</div>
<div class="h3">
<a name="Things+that+have+to+be+changed+when+upgrading+to+POI+3.5"></a>
<div class="h3">
<h3>Things that have to be changed when upgrading to POI 3.5</h3>
</div>
<p>Wherever possible, we have tried to ensure that you can use your
existing POI code with POI 3.5 without requiring any changes. However,
Java doesn't always make that easy, and unfortunately there are a
few changes that may be required for some users.</p>
<a name="org.apache.poi.hssf.usermodel.HSSFFormulaEvaluator.CellValue"></a>
<div class="h4">
<h4>org.apache.poi.hssf.usermodel.HSSFFormulaEvaluator.CellValue</h4>
</div>
<p>Annoyingly, java will not let you access a static inner class via
a child of the parent one. So, all references to
<em>org.apache.poi.hssf.usermodel.HSSFFormulaEvaluator.CellValue</em>
will need to be changed to
<em>org.apache.poi.ss.usermodel.FormulaEvaluator.CellValue</em>
</p>
<a name="org.apache.poi.hssf.usermodel.HSSFRow.MissingCellPolicy"></a>
<div class="h4">
<h4>org.apache.poi.hssf.usermodel.HSSFRow.MissingCellPolicy</h4>
</div>
<p>Annoyingly, java will not let you access a static inner class via
a child of the parent one. So, all references to
<em>org.apache.poi.hssf.usermodel.HSSFRow.MissingCellPolicy</em>
will need to be changed to
<em>org.apache.poi.ss.usermodel.Row.MissingCellPolicy</em>
</p>
<a name="DDF+and+org.apache.poi.hssf.record.RecordFormatException"></a>
<div class="h4">
<h4>DDF and org.apache.poi.hssf.record.RecordFormatException</h4>
</div>
<p>Previously, record level errors within DDF would throw an
exception from the hssf class heirachy. Now, record level errors
within DDF will throw a more general RecordFormatException,
<em>org.apache.poi.util.RecordFormatException</em>
</p>
<p>In addition, org.apache.poi.hssf.record.RecordFormatException
has been changed to inherit from the new
<em>org.apache.poi.util.RecordFormatException</em>, so you may
wish to change catches of the hssf version to the new util version.
</p>
<a name="Converting+existing+HSSF+Usermodel+code+to+SS+Usermodel+%28for+XSSF+and+HSSF%29"></a>
<div class="h3">
<h3>Converting existing HSSF Usermodel code to SS Usermodel (for XSSF and HSSF)</h3>
</div>
<a name="Why+change%3F"></a>
<div class="h4">
<h4>Why change?</h4>
</div>
<p>If you have existing HSSF usermodel code that works just
fine, and you don't want to use the new OOXML XSSF support,
then you probably don't need to. Your existing HSSF only code
will continue to work just fine.</p>
<p>However, if you want to be able to work with both HSSF for
your .xls files, and also XSSF for .xslx files, then you will
need to make some slight tweaks to your code.</p>
<a name="org.apache.poi.ss.usermodel"></a>
<div class="h4">
<h4>org.apache.poi.ss.usermodel</h4>
</div>
<p>The new SS usermodel (org.apache.poi.ss.usermodel) is very
heavily based on the old HSSF usermodel
(org.apache.poi.hssf.usermodel). The main difference is that
the package name and class names have been tweaked to remove
HSSF from them. Otherwise, the new SS Usermodel interfaces
should provide the same functionality.</p>
<a name="Constructors"></a>
<div class="h4">
<h4>Constructors</h4>
</div>
<p>Calling the empty HSSFWorkbook remains as the way to
create a new, empty Workbook object. To open an existing
Worbook, you should now call WorkbookFactory.create(inp).</p>
<p>For all other cases when you would have called a
Usermodel constructor, such as 'new HSSFRichTextString()' or
'new HSSFDataFormat', you should instead use a CreationHelper.
There's a method on the Workbook to get a CreationHelper, and
the CreationHelper will then handle constructing new objects
for you.</p>
<a name="Other+Code"></a>
<div class="h4">
<h4>Other Code</h4>
</div>
<p>For all other code, generally change a reference from
org.apache.poi.hssf.usermodel.HSSFFoo to a reference to
org.apache.poi.ss.usermodel.Foo. Method signatures should
otherwise remain the same, and it should all then work for
both XSSF and HSSF.</p>
<a name="Worked+Examples"></a>
<div class="h3">
<h3>Worked Examples</h3>
</div>
<a name="Old+HSSF+Code"></a>
<div class="h4">
<h4>Old HSSF Code</h4>
</div>
<pre class="code">
// import org.apache.poi.hssf.usermodel.*;
HSSFWorkbook wb = new HSSFWorkbook();
// create a new sheet
HSSFSheet s = wb.createSheet();
// declare a row object reference
HSSFRow r = null;
// declare a cell object reference
HSSFCell c = null;
// create 2 cell styles
HSSFCellStyle cs = wb.createCellStyle();
HSSFCellStyle cs2 = wb.createCellStyle();
HSSFDataFormat df = wb.createDataFormat();
// create 2 fonts objects
HSSFFont f = wb.createFont();
HSSFFont f2 = wb.createFont();
// Set font 1 to 12 point type, blue and bold
f.setFontHeightInPoints((short) 12);
f.setColor( HSSFColor.RED.index );
f.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
// Set font 2 to 10 point type, red and bold
f2.setFontHeightInPoints((short) 10);
f2.setColor( HSSFFont.RED.index );
f2.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
// Set cell style and formatting
cs.setFont(f);
cs.setDataFormat(df.getFormat("#,##0.0"));
// Set the other cell style and formatting
cs2.setBorderBottom(cs2.BORDER_THIN);
cs2.setDataFormat(HSSFDataFormat.getBuiltinFormat("text"));
cs2.setFont(f2);
// Define a few rows
for(short rownum = (short)0; rownum < 30; rownum++) {
HSSFRow r = s.createRow(rownum);
for(short cellnum = (short)0; cellnum < 10; cellnum += 2) {
HSSFCell c = r.createCell(cellnum);
HSSFCell c2 = r.createCell(cellnum+1);
c.setCellValue((double)rownum + (cellnum/10));
c2.setCellValue(new HSSFRichTextString("Hello! " + cellnum);
}
}
// Save
FileOutputStream out = new FileOutputStream("workbook.xls");
wb.write(out);
out.close();
</pre>
<a name="New%2C+generic+SS+Usermodel+Code"></a>
<div class="h4">
<h4>New, generic SS Usermodel Code</h4>
</div>
<pre class="code">
// import org.apache.poi.ss.usermodel.*;
Workbook[] wbs = new Workbook[] { new HSSFWorkbook(), new XSSFWorkbook() };
for(int i=0; i<wbs.length; i++) {
Workbook wb = wbs[i];
CreationHelper createHelper = wb.getCreationHelper();
// create a new sheet
Sheet s = wb.createSheet();
// declare a row object reference
Row r = null;
// declare a cell object reference
Cell c = null;
// create 2 cell styles
CellStyle cs = wb.createCellStyle();
CellStyle cs2 = wb.createCellStyle();
DataFormat df = wb.createDataFormat();
// create 2 fonts objects
Font f = wb.createFont();
Font f2 = wb.createFont();
// Set font 1 to 12 point type, blue and bold
f.setFontHeightInPoints((short) 12);
f.setColor( IndexedColors.RED.getIndex() );
f.setBoldweight(Font.BOLDWEIGHT_BOLD);
// Set font 2 to 10 point type, red and bold
f2.setFontHeightInPoints((short) 10);
f2.setColor( IndexedColors.RED.getIndex() );
f2.setBoldweight(Font.BOLDWEIGHT_BOLD);
// Set cell style and formatting
cs.setFont(f);
cs.setDataFormat(df.getFormat("#,##0.0"));
// Set the other cell style and formatting
cs2.setBorderBottom(cs2.BORDER_THIN);
cs2.setDataFormat(df.getFormat("text"));
cs2.setFont(f2);
// Define a few rows
for(int rownum = 0; rownum < 30; rownum++) {
Row r = s.createRow(rownum);
for(int cellnum = 0; cellnum < 10; cellnum += 2) {
Cell c = r.createCell(cellnum);
Cell c2 = r.createCell(cellnum+1);
c.setCellValue((double)rownum + (cellnum/10));
c2.setCellValue(
createHelper.createRichTextString("Hello! " + cellnum)
);
}
}
// Save
String filename = "workbook.xls";
if(wb instanceof XSSFWorkbook) {
filename = filename + "x";
}
FileOutputStream out = new FileOutputStream(filename);
wb.write(out);
out.close();
}
</pre>
<div id="authors" align="right">by Nick Burch</div>
</div>
</div>
</div>
</td>
<!--================= end Content ==================-->
</tr>
</tbody>
</table>
<!--================= end Main ==================-->
<!--================= start Footer ==================-->
<div id="footer">
<table summary="footer" cellspacing="0" cellpadding="4" width="100%" border="0">
<tbody>
<tr>
<!--================= start Copyright ==================-->
<td colspan="2">
<div align="center">
<div class="copyright">
Copyright © 2002-2012 The Apache Software Foundation. All rights reserved.<br>
Apache POI, POI, Apache, the Apache feather logo, and the Apache
POI project logo are trademarks of The Apache Software Foundation.
</div>
</div>
</td>
<!--================= end Copyright ==================-->
</tr>
<tr>
<td align="left">
<!--================= start Host ==================-->
<!--================= end Host ==================--></td><td align="right">
<!--================= start Credits ==================-->
<div align="right">
<div class="credit"></div>
</div>
<!--================= end Credits ==================-->
</td>
</tr>
</tbody>
</table>
</div>
<!--================= end Footer ==================-->
</body>
</html>
