<!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>Frequently Asked Questions</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="faq.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">Marketing</span>
<div class="menuItem">
<a href="casestudies.html">Case Studies</a>
</div>
</div>
<div class="menu">
<span class="menuLabel">Project</span>
<div class="menuItem">
<a href="overview.html">Overview</a>
</div>
<div class="menuItem">
<a href="poifs/index.html">POIFS</a>
</div>
<div class="menuItem">
<a href="hssf/index.html">HSSF</a>
</div>
<div class="menuItem">
<a href="hwpf/index.html">HWPF</a>
</div>
<div class="menuItem">
<a href="hpsf/index.html">HPSF</a>
</div>
<div class="menuItem">
<a href="hslf/index.html">HSLF</a>
</div>
<div class="menuItem">
<a href="hsmf/index.html">HSMF</a>
</div>
<div class="menuItem">
<a href="hdgf/index.html">HDGF</a>
</div>
<div class="menuItem">
<a href="poi-ruby.html">POI-Ruby</a>
</div>
<div class="menuItem">
<a href="utils/index.html">POI-Utils</a>
</div>
<div class="menuItem">
<a href="text-extraction.html">Text Extraction</a>
</div>
<div class="menuItem">
<a href="http://www.apache.org/dyn/closer.cgi/poi/">Download</a>
</div>
</div>
<div class="menu">
<span class="menuLabel">Community</span>
<div class="menuItem">
<a href="news.html">News</a>
</div>
<div class="menuItem">
<a href="mirrors.html">Mirrors</a>
</div>
<div class="menuItem">
<a href="changes.html">Changes</a>
</div>
<div class="menuItem">
<a href="todo.html">To Do</a>
</div>
<div class="menuItem">
<a href="getinvolved/index.html">Get Involved</a>
</div>
<div class="menuItem">
<a href="mailinglists.html">Mailing Lists</a>
</div>
<div class="menuItem">
<a href="plan/POI20Vision.html">Vision</a>
</div>
<div class="menuItem">
<a href="historyandfuture.html">History and Future</a>
</div>
<div class="menuItem">
<a href="who.html">Who We Are</a>
</div>
<div class="menuItem">
<a href="resolutions/index.html">Resolutions</a>
</div>
<div class="menuItem">
<a href="http://www.apache.org/foundation/thanks.html">Sponsors</a>
</div>
</div>
<div class="menu">
<span class="menuLabel">Docs</span>
<div class="menuItem">
<a href="apidocs/index.html">Javadocs</a>
</div>
<div class="menuItem">
<span class="menuSelected">FAQ</span>
</div>
<div class="menuItem">
<a href="legal.html">Legal</a>
</div>
<div class="menuItem">
<a href="references/index.html">References</a>
</div>
<div class="menuItem">
<a href="howtobuild.html">How to Build</a>
</div>
</div>
<div class="menu">
<span class="menuLabel">Translations</span>
<div class="menuItem">
<a href="trans/index.html">Index</a>
</div>
<div class="menuItem">
<a href="trans/guidelines.html">Guidelines</a>
</div>
<div class="menuItem">
<a href="trans/de/index.html">German (DE)</a>
</div>
<div class="menuItem">
<a href="trans/es/index.html">Spanish (ES)</a>
</div>
<div class="menuItem">
<a href="http://jakarta.terra-intl.com/poi/">Japanese (Web)</a>
</div>
<div class="menuItem">
<a href="http://jakarta.apache-korea.org/poi/">Korean (Web)</a>
</div>
</div>
<div class="menu">
<span class="menuLabel">Code</span>
<div class="menuItem">
<a href="subversion.html">Subversion (current source code)</a>
</div>
<div class="menuItem">
<a href="http://issues.apache.org/bugzilla/buglist.cgi?votes=1&product=POI&order=bugs.votes">Top Voted Bugs</a>
</div>
<div class="menuItem">
<a href="http://issues.apache.org/bugzilla/buglist.cgi?product=POI">Bug Database</a>
</div>
<div class="menuItem">
<a href="http://issues.apache.org/bugzilla/buglist.cgi?product=POI&short_desc=%5BPATCH%5D&short_desc_type=allwordssubstr">Patches</a>
</div>
<div class="menuItem">
<a href="junit/index.html">Junit Test Results</a>
</div>
<div class="menuItem">
<a href="jdepend/index.html">Dependency Metrics</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>Frequently Asked Questions</h1>
</div>
<div class="h3">
<div class="h3">
<h3>Questions</h3>
</div>
<ol>
<li>
<a name="faq-N10006-menu"></a><a href="#faq-N10006">
Why is reading a simple sheet taking so long?
</a>
</li>
<li>
<a name="faq-N10012-menu"></a><a href="#faq-N10012">
What is the HSSF "eventmodel"?
</a>
</li>
<li>
<a name="faq-N1001E-menu"></a><a href="#faq-N1001E">
Why can't read the document I created using Star Office 5.1?
</a>
</li>
<li>
<a name="faq-N1002A-menu"></a><a href="#faq-N1002A">
Why am I getting an exception each time I attempt to read my spreadsheet?
</a>
</li>
<li>
<a name="faq-N10036-menu"></a><a href="#faq-N10036">
Does HSSF support protected spreadsheets?
</a>
</li>
<li>
<a name="faq-N10042-menu"></a><a href="#faq-N10042">
How do you tell if a field contains a date with HSSF?
</a>
</li>
<li>
<a name="faq-N10052-menu"></a><a href="#faq-N10052">
I'm trying to stream an XLS file from a servlet and I'm having some trouble. What's the problem?
</a>
</li>
<li>
<a name="faq-N1007C-menu"></a><a href="#faq-N1007C">
I want to set a cell format (Data format of a cell) of a excel sheet as ###,###,###.#### or ###,###,###.0000. Is it possible using POI ?
</a>
</li>
<li>
<a name="faq-N1008C-menu"></a><a href="#faq-N1008C">
I want to set a cell format (Data format of a cell) of a excel sheet as text. Is it possible using POI ?
</a>
</li>
<li>
<a name="faq-N10098-menu"></a><a href="#faq-N10098">
How do I add a border around a merged cell?
</a>
</li>
<li>
<a name="faq-N100A4-menu"></a><a href="#faq-N100A4">
I tried to set cell values and Excel sheet name on my native language,
but I failed to do it. :(
</a>
</li>
<li>
<a name="faq-N100B7-menu"></a><a href="#faq-N100B7">
I'm having trouble creating a spreadsheet using POI using
websphere 3.5.
</a>
</li>
<li>
<a name="faq-N100C3-menu"></a><a href="#faq-N100C3">
I am using styles when creating a workbook in POI, but Excel refuses to open the file, complaining about "Too Many Styles".
</a>
</li>
<li>
<a name="faq-N100DD-menu"></a><a href="#faq-N100DD">
Will POI read any spreadsheet and rewrite it with modifications.
</a>
</li>
</ol>
<div class="h3">
<h3>Answers</h3>
</div>
<div class="h4">
<h4>1.
Why is reading a simple sheet taking so long?
</h4>
</div>
<p>You've probably enabled logging. Logging is intended only for
autopsie style debugging. Having it enabled will reduce performance
by a factor of at least 100. Logging is helpful for understanding
why POI can't read some file or developing POI itself. Important
errors are thrown as exceptions, which means you probably don't need
logging.</p>
<div class="h4">
<h4>2.
What is the HSSF "eventmodel"?
</h4>
</div>
<p>The HSSF eventmodel package is a new API for reading XLS files more efficiently. It does
require more knowledge on the part of the user, but reduces memory consumption by more than
tenfold. It is based on the AWT event model in combination with SAX. If you need read-only
access to a given XLS file, this is the best way to do it.</p>
<div class="h4">
<h4>3.
Why can't read the document I created using Star Office 5.1?
</h4>
</div>
<p>Star Office 5.1 writes some records using the older BIFF standard. This causes some problems
with POI which supports only BIFF8.</p>
<div class="h4">
<h4>4.
Why am I getting an exception each time I attempt to read my spreadsheet?
</h4>
</div>
<p>It's possible your spreadsheet contains a feature that is not currently supported by HSSF.
For example - spreadsheets containing cells with rich text are not currently supported.</p>
<div class="h4">
<h4>5.
Does HSSF support protected spreadsheets?
</h4>
</div>
<p>Protecting a spreadsheet encrypts it. We wont touch encryption because we're not legally educated
and don't understand the full implications of trying to implement this. If you wish to have a go
at this feel free to add it as a plugin module. We wont be hosting it here however.</p>
<div class="h4">
<h4>6.
How do you tell if a field contains a date with HSSF?
</h4>
</div>
<p>Excel stores dates as numbers therefore the only way to determine if a cell is
actually stored as a date is to look at the formatting. There is a helper method
in HSSFDateUtil (since the 1.7.0-dev release) that checks for this.
Thanks to Jason Hoffman for providing the solution.</p>
<pre class="code">
case HSSFCell.CELL_TYPE_NUMERIC:
double d = cell.getNumericCellValue();
// test if a date!
if (HSSFDateUtil.isCellDateFormatted(cell)) {
// format in form of M/D/YY
cal.setTime(HSSFDateUtil.getJavaDate(d));
cellText =
(String.valueOf(cal.get(Calendar.YEAR))).substring(2);
cellText = cal.get(Calendar.MONTH)+1 + "/" +
cal.get(Calendar.DAY_OF_MONTH) + "/" +
cellText;
} </pre>
<div class="h4">
<h4>7.
I'm trying to stream an XLS file from a servlet and I'm having some trouble. What's the problem?
</h4>
</div>
<p>
The problem usually manifests itself as the junk characters being shown on
screen. The problem persists even though you have set the correct mime type.
</p>
<p>
The short answer is, don't depend on IE to display a binary file type properly if you stream it via a
servlet. Every minor version of IE has different bugs on this issue.
</p>
<p>
The problem in most versions of IE is that it does not use the mime type on
the HTTP response to determine the file type; rather it uses the file extension
on the request. Thus you might want to add a
<strong>.xls</strong> to your request
string. For example
<em>http://yourserver.com/myServelet.xls?param1=xx</em>. This is
easily accomplished through URL mapping in any servlet container. Sometimes
a request like
<em>http://yourserver.com/myServelet?param1=xx&dummy=file.xls</em> is also
known to work.
</p>
<p>
To guarantee opening the file properly in Excel from IE, write out your file to a
temporary file under your web root from your servelet. Then send an http response
to the browser to do a client side redirection to your temp file. (Note that using a
server side redirect using RequestDispatcher will not be effective in this case)
</p>
<p>
Note also that when you request a document that is opened with an
external handler, IE sometimes makes two requests to the webserver. So if your
generating process is heavy, it makes sense to write out to a temporary file, so that multiple
requests happen for a static file.
</p>
<p>
None of this is particular to Excel. The same problem arises when you try to
generate any binary file dynamically to an IE client. For example, if you generate
pdf files using
<a href="http://xml.apache.org/fop">FOP</a>, you will come across many of the same issues.
</p>
<div class="h4">
<h4>8.
I want to set a cell format (Data format of a cell) of a excel sheet as ###,###,###.#### or ###,###,###.0000. Is it possible using POI ?
</h4>
</div>
<p>
Yes. You first need to get a HSSFDataFormat object from the workbook and call getFormat with the desired format. Some examples are <a href="hssf/quick-guide.html#DataFormats">here</a>.
</p>
<div class="h4">
<h4>9.
I want to set a cell format (Data format of a cell) of a excel sheet as text. Is it possible using POI ?
</h4>
</div>
<p>
Yes. This is a built-in format for excel that you can get from HSSFDataFormat object using the format string "@". Also, the string "text" will alias this format.
</p>
<div class="h4">
<h4>10.
How do I add a border around a merged cell?
</h4>
</div>
<p>Add blank cells around where the cells normally would have been and set the borders individually for each cell.
We will probably enhance HSSF in the future to make this process easier.</p>
<div class="h4">
<h4>11.
I tried to set cell values and Excel sheet name on my native language,
but I failed to do it. :(
</h4>
</div>
<p>By default HSSF uses cell values and sheet names as compressed unicode,
so to support localization you should use Unicode.
To do it you should set it manually:</p>
<pre class="code">
// for sheet name
HSSFWorkbook wb = new HSSFWorkbook();
HSSFSheet s = wb.createSheet();
wb.setSheetName( 0, "SomeUnicodeName", HSSFWorkbook.ENCODING_UTF_16 );
// for cell value
HSSFRow r = s.createRow( 0 );
HSSFCell c = r.createCell( (short)0 );
c.setCellType( HSSFCell.CELL_TYPE_STRING );
c.setEncoding( HSSFCell.ENCODING_UTF_16 );
c.setCellValue( "\u0422\u0435\u0441\u0442\u043E\u0432\u0430\u044F" ); </pre>
<p>
Make sure you make the call to setEncoding() before calling setCellValue(), otherwise what you pass in won't be interpreted properly.
</p>
<div class="h4">
<h4>12.
I'm having trouble creating a spreadsheet using POI using
websphere 3.5.
</h4>
</div>
<p>Make sure you have fix pack 4 installed.</p>
<div class="h4">
<h4>13.
I am using styles when creating a workbook in POI, but Excel refuses to open the file, complaining about "Too Many Styles".
</h4>
</div>
<p>You just create the styles OUTSIDE of the loop in which you create cells.</p>
<p>GOOD:</p>
<pre class="code">
HSSFWorkbook wb = new HSSFWorkbook();
HSSFSheet sheet = wb.createSheet("new sheet");
HSSFRow row = null;
// 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);
for (int x = 0; x < 1000; x++) {
// Create a row and put some cells in it. Rows are 0 based.
row = sheet.createRow((short) k);
for (int y = 0; y < 100; y++) {
cell = row.createCell((short) k);
cell.setCellValue("X");
cell.setCellStyle(style);
}
}
// Write the output to a file
FileOutputStream fileOut = new FileOutputStream("workbook.xls");
wb.write(fileOut);
fileOut.close(); </pre>
<p>BAD:</p>
<pre class="code">
HSSFWorkbook wb = new HSSFWorkbook();
HSSFSheet sheet = wb.createSheet("new sheet");
HSSFRow row = null;
for (int x = 0; x < 1000; x++) {
// 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);
// Create a row and put some cells in it. Rows are 0 based.
row = sheet.createRow((short) k);
for (int y = 0; y < 100; y++) {
cell = row.createCell((short) k);
cell.setCellValue("X");
cell.setCellStyle(style);
}
}
// Write the output to a file
FileOutputStream fileOut = new FileOutputStream("workbook.xls");
wb.write(fileOut);
fileOut.close(); </pre>
<div class="h4">
<h4>14.
Will POI read any spreadsheet and rewrite it with modifications.
</h4>
</div>
<p>POI is not guanteed to read the contents of any spreadsheet.
Certain features may cause spreadsheets to fail to read. More
problematic is rewriting spreadsheets. POI tried hard to
preserve the records of the original spreadsheet but some
features may cause problems. We advise that you limit the
formatting of spreadsheets you process so as to not be
unpleasantly suprised at a later stage.</p>
</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
>
10
