You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@poi.apache.org by ki...@apache.org on 2019/09/24 23:26:00 UTC
svn commit: r1867502 [13/20] - in /poi/site/publish: ./ components/
components/hmef/ components/hpbf/ components/hpsf/ components/poifs/
components/slideshow/ components/spreadsheet/ devel/ devel/plan/
devel/references/ skin/ skin/images/
Modified: poi/site/publish/components/spreadsheet/quick-guide.html
URL: http://svn.apache.org/viewvc/poi/site/publish/components/spreadsheet/quick-guide.html?rev=1867502&r1=1867501&r2=1867502&view=diff
==============================================================================
--- poi/site/publish/components/spreadsheet/quick-guide.html (original)
+++ poi/site/publish/components/spreadsheet/quick-guide.html Tue Sep 24 23:26:00 2019
@@ -486,126 +486,360 @@ document.write("Last Published: " + docu
<h3 class="boxed">Features</h3>
<a name="NewWorkbook" id="NewWorkbook"></a><a name="New+Workbook"></a>
<h4>New Workbook</h4>
-<pre class="code">
- Workbook wb = new HSSFWorkbook();
- ...
- try (OutputStream fileOut = new FileOutputStream("workbook.xls")) {
- wb.write(fileOut);
- }
-
- Workbook wb = new XSSFWorkbook();
- ...
- try (OutputStream fileOut = new FileOutputStream("workbook.xlsx")) {
- wb.write(fileOut);
- }
- </pre>
+<div class="code">
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"></span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">Workbook wb = new HSSFWorkbook();</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">...</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">try (OutputStream fileOut = new FileOutputStream("workbook.xls")) {</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"> wb.write(fileOut);</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">}</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"></span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">Workbook wb = new XSSFWorkbook();</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">...</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">try (OutputStream fileOut = new FileOutputStream("workbook.xlsx")) {</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"> wb.write(fileOut);</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">}</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"> </span>
+</div>
+</div>
<a name="NewSheet" id="NewSheet"></a><a name="New+Sheet"></a>
<h4>New Sheet</h4>
-<pre class="code">
- Workbook wb = new HSSFWorkbook(); // or new XSSFWorkbook();
- Sheet sheet1 = wb.createSheet("new sheet");
- 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);
-
- try (OutputStream fileOut = new FileOutputStream("workbook.xls")) {
- wb.write(fileOut);
- }
- </pre>
+<div class="code">
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"></span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">Workbook wb = new HSSFWorkbook(); // or new XSSFWorkbook();</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">Sheet sheet1 = wb.createSheet("new sheet");</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">Sheet sheet2 = wb.createSheet("second sheet");</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"></span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">// Note that sheet name is Excel must not exceed 31 characters</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">// and must not contain any of the any of the following characters:</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">// 0x0000</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">// 0x0003</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">// colon (:)</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">// backslash (\)</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">// asterisk (*)</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">// question mark (?)</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">// forward slash (/)</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">// opening square bracket ([)</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">// closing square bracket (])</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"></span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">// You can use org.apache.poi.ss.util.WorkbookUtil#createSafeSheetName(String nameProposal)}</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">// for a safe way to create valid names, this utility replaces invalid characters with a space (' ')</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">String safeName = WorkbookUtil.createSafeSheetName("[O'Brien's sales*?]"); // returns " O'Brien's sales "</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">Sheet sheet3 = wb.createSheet(safeName);</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"></span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">try (OutputStream fileOut = new FileOutputStream("workbook.xls")) {</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"> wb.write(fileOut);</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">}</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"> </span>
+</div>
+</div>
<a name="CreateCells" id="CreateCells"></a><a name="Creating+Cells"></a>
<h4>Creating Cells</h4>
-<pre class="code">
- Workbook wb = new HSSFWorkbook();
- //Workbook wb = new XSSFWorkbook();
- 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 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
- try (OutputStream fileOut = new FileOutputStream("workbook.xls")) {
- wb.write(fileOut);
- }
- </pre>
+<div class="code">
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"></span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">Workbook wb = new HSSFWorkbook();</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">//Workbook wb = new XSSFWorkbook();</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">CreationHelper createHelper = wb.getCreationHelper();</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">Sheet sheet = wb.createSheet("new sheet");</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"></span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">// Create a row and put some cells in it. Rows are 0 based.</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">Row row = sheet.createRow(0);</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">// Create a cell and put a value in it.</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">Cell cell = row.createCell(0);</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">cell.setCellValue(1);</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"></span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">// Or do it on one line.</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">row.createCell(1).setCellValue(1.2);</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">row.createCell(2).setCellValue(</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"> createHelper.createRichTextString("This is a string"));</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">row.createCell(3).setCellValue(true);</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"></span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">// Write the output to a file</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">try (OutputStream fileOut = new FileOutputStream("workbook.xls")) {</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"> wb.write(fileOut);</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">}</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"> </span>
+</div>
+</div>
<a name="CreateDateCells" id="CreateDateCells"></a><a name="Creating+Date+Cells"></a>
<h4>Creating Date Cells</h4>
-<pre class="code">
- Workbook wb = new HSSFWorkbook();
- //Workbook wb = new XSSFWorkbook();
- 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
- try (OutputStream fileOut = new FileOutputStream("workbook.xls")) {
- wb.write(fileOut);
- }
- </pre>
+<div class="code">
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"></span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">Workbook wb = new HSSFWorkbook();</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">//Workbook wb = new XSSFWorkbook();</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">CreationHelper createHelper = wb.getCreationHelper();</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">Sheet sheet = wb.createSheet("new sheet");</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"></span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">// Create a row and put some cells in it. Rows are 0 based.</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">Row row = sheet.createRow(0);</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"></span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">// Create a cell and put a date value in it. The first cell is not styled</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">// as a date.</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">Cell cell = row.createCell(0);</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">cell.setCellValue(new Date());</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"></span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">// we style the second cell as a date (and time). It is important to</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">// create a new cell style from the workbook otherwise you can end up</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">// modifying the built in style and effecting not only this cell but other cells.</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">CellStyle cellStyle = wb.createCellStyle();</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">cellStyle.setDataFormat(</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"> createHelper.createDataFormat().getFormat("m/d/yy h:mm"));</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">cell = row.createCell(1);</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">cell.setCellValue(new Date());</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">cell.setCellStyle(cellStyle);</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"></span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">//you can also set date as java.util.Calendar</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">cell = row.createCell(2);</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">cell.setCellValue(Calendar.getInstance());</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">cell.setCellStyle(cellStyle);</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"></span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">// Write the output to a file</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">try (OutputStream fileOut = new FileOutputStream("workbook.xls")) {</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"> wb.write(fileOut);</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">}</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"> </span>
+</div>
+</div>
<a name="CellTypes" id="CellTypes"></a><a name="Working+with+different+types+of+cells"></a>
<h4>Working with different types of cells</h4>
-<pre class="code">
- Workbook wb = new HSSFWorkbook();
- Sheet sheet = wb.createSheet("new sheet");
- Row row = sheet.createRow(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(CellType.ERROR);
-
- // Write the output to a file
- try (OutputStream fileOut = new FileOutputStream("workbook.xls")) {
- wb.write(fileOut);
- }
- </pre>
+<div class="code">
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"></span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">Workbook wb = new HSSFWorkbook();</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">Sheet sheet = wb.createSheet("new sheet");</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">Row row = sheet.createRow(2);</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">row.createCell(0).setCellValue(1.1);</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">row.createCell(1).setCellValue(new Date());</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">row.createCell(2).setCellValue(Calendar.getInstance());</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">row.createCell(3).setCellValue("a string");</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">row.createCell(4).setCellValue(true);</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">row.createCell(5).setCellType(CellType.ERROR);</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"></span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">// Write the output to a file</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">try (OutputStream fileOut = new FileOutputStream("workbook.xls")) {</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"> wb.write(fileOut);</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">}</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"> </span>
+</div>
+</div>
<a name="FileInputStream" id="FileInputStream"></a><a name="Files+vs+InputStreams"></a>
<h4>Files vs InputStreams</h4>
<p>When opening a workbook, either a .xls HSSFWorkbook, or a .xlsx
@@ -615,116 +849,328 @@ document.write("Last Published: " + docu
memory as it has to buffer the whole file.</p>
<p>If using <em>WorkbookFactory</em>, it's very easy to use one or
the other:</p>
-<pre class="code">
- // Use a file
- Workbook wb = WorkbookFactory.create(new File("MyExcel.xls"));
-
- // Use an InputStream, needs more memory
- Workbook wb = WorkbookFactory.create(new FileInputStream("MyExcel.xlsx"));
- </pre>
+<div class="code">
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"></span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">// Use a file</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">Workbook wb = WorkbookFactory.create(new File("MyExcel.xls"));</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"></span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">// Use an InputStream, needs more memory</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">Workbook wb = WorkbookFactory.create(new FileInputStream("MyExcel.xlsx"));</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"> </span>
+</div>
+</div>
<p>If using <em>HSSFWorkbook</em> or <em>XSSFWorkbook</em> directly,
you should generally go through <em>POIFSFileSystem</em> or
<em>OPCPackage</em>, to have full control of the lifecycle (including
closing the file when done):</p>
-<pre class="code">
- // HSSFWorkbook, File
- POIFSFileSystem fs = new POIFSFileSystem(new File("file.xls"));
- HSSFWorkbook wb = new HSSFWorkbook(fs.getRoot(), true);
- ....
- fs.close();
-
- // HSSFWorkbook, InputStream, needs more memory
- POIFSFileSystem fs = new POIFSFileSystem(myInputStream);
- HSSFWorkbook wb = new HSSFWorkbook(fs.getRoot(), true);
-
- // XSSFWorkbook, File
- OPCPackage pkg = OPCPackage.open(new File("file.xlsx"));
- XSSFWorkbook wb = new XSSFWorkbook(pkg);
- ....
- pkg.close();
-
- // XSSFWorkbook, InputStream, needs more memory
- OPCPackage pkg = OPCPackage.open(myInputStream);
- XSSFWorkbook wb = new XSSFWorkbook(pkg);
- ....
- pkg.close();
- </pre>
+<div class="code">
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"></span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">// HSSFWorkbook, File</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">POIFSFileSystem fs = new POIFSFileSystem(new File("file.xls"));</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">HSSFWorkbook wb = new HSSFWorkbook(fs.getRoot(), true);</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">....</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">fs.close();</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"></span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">// HSSFWorkbook, InputStream, needs more memory</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">POIFSFileSystem fs = new POIFSFileSystem(myInputStream);</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">HSSFWorkbook wb = new HSSFWorkbook(fs.getRoot(), true);</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"></span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">// XSSFWorkbook, File</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">OPCPackage pkg = OPCPackage.open(new File("file.xlsx"));</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">XSSFWorkbook wb = new XSSFWorkbook(pkg);</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">....</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">pkg.close();</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"></span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">// XSSFWorkbook, InputStream, needs more memory</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">OPCPackage pkg = OPCPackage.open(myInputStream);</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">XSSFWorkbook wb = new XSSFWorkbook(pkg);</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">....</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">pkg.close();</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"> </span>
+</div>
+</div>
<a name="Alignment" id="Alignment"></a><a name="Demonstrates+various+alignment+options"></a>
<h4>Demonstrates various alignment options</h4>
-<pre class="code">
- public static void main(String[] args) throws Exception {
- Workbook wb = new XSSFWorkbook(); //or new HSSFWorkbook();
-
- Sheet sheet = wb.createSheet();
- Row row = sheet.createRow(2);
- row.setHeightInPoints(30);
-
- createCell(wb, row, 0, HorizontalAlignment.CENTER, VerticalAlignment.BOTTOM);
- createCell(wb, row, 1, HorizontalAlignment.CENTER_SELECTION, VerticalAlignment.BOTTOM);
- createCell(wb, row, 2, HorizontalAlignment.FILL, VerticalAlignment.CENTER);
- createCell(wb, row, 3, HorizontalAlignment.GENERAL, VerticalAlignment.CENTER);
- createCell(wb, row, 4, HorizontalAlignment.JUSTIFY, VerticalAlignment.JUSTIFY);
- createCell(wb, row, 5, HorizontalAlignment.LEFT, VerticalAlignment.TOP);
- createCell(wb, row, 6, HorizontalAlignment.RIGHT, VerticalAlignment.TOP);
-
- // Write the output to a file
- try (OutputStream fileOut = new FileOutputStream("xssf-align.xlsx")) {
- wb.write(fileOut);
- }
-
- wb.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.
- * @param valign the vertical alignment for the cell.
- */
- private static void createCell(Workbook wb, Row row, int column, HorizontalAlignment halign, VerticalAlignment valign) {
- Cell cell = row.createCell(column);
- cell.setCellValue("Align It");
- CellStyle cellStyle = wb.createCellStyle();
- cellStyle.setAlignment(halign);
- cellStyle.setVerticalAlignment(valign);
- cell.setCellStyle(cellStyle);
- }
- </pre>
+<div class="code">
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"></span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">public static void main(String[] args) throws Exception {</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"> Workbook wb = new XSSFWorkbook(); //or new HSSFWorkbook();</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"></span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"> Sheet sheet = wb.createSheet();</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"> Row row = sheet.createRow(2);</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"> row.setHeightInPoints(30);</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"></span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"> createCell(wb, row, 0, HorizontalAlignment.CENTER, VerticalAlignment.BOTTOM);</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"> createCell(wb, row, 1, HorizontalAlignment.CENTER_SELECTION, VerticalAlignment.BOTTOM);</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"> createCell(wb, row, 2, HorizontalAlignment.FILL, VerticalAlignment.CENTER);</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"> createCell(wb, row, 3, HorizontalAlignment.GENERAL, VerticalAlignment.CENTER);</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"> createCell(wb, row, 4, HorizontalAlignment.JUSTIFY, VerticalAlignment.JUSTIFY);</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"> createCell(wb, row, 5, HorizontalAlignment.LEFT, VerticalAlignment.TOP);</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"> createCell(wb, row, 6, HorizontalAlignment.RIGHT, VerticalAlignment.TOP);</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"></span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"> // Write the output to a file</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"> try (OutputStream fileOut = new FileOutputStream("xssf-align.xlsx")) {</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"> wb.write(fileOut);</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"> }</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"></span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"> wb.close();</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">}</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"></span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">/**</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"> * Creates a cell and aligns it a certain way.</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"> *</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"> * @param wb the workbook</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"> * @param row the row to create the cell in</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"> * @param column the column number to create the cell in</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"> * @param halign the horizontal alignment for the cell.</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"> * @param valign the vertical alignment for the cell.</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"> */</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">private static void createCell(Workbook wb, Row row, int column, HorizontalAlignment halign, VerticalAlignment valign) {</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"> Cell cell = row.createCell(column);</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"> cell.setCellValue("Align It");</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"> CellStyle cellStyle = wb.createCellStyle();</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"> cellStyle.setAlignment(halign);</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"> cellStyle.setVerticalAlignment(valign);</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"> cell.setCellStyle(cellStyle);</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">}</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"> </span>
+</div>
+</div>
<a name="Borders" id="Borders"></a><a name="Working+with+borders"></a>
<h4>Working with borders</h4>
-<pre class="code">
- Workbook wb = new HSSFWorkbook();
- Sheet sheet = wb.createSheet("new sheet");
-
- // 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(BorderStyle.THIN);
- style.setBottomBorderColor(IndexedColors.BLACK.getIndex());
- style.setBorderLeft(BorderStyle.THIN);
- style.setLeftBorderColor(IndexedColors.GREEN.getIndex());
- style.setBorderRight(BorderStyle.THIN);
- style.setRightBorderColor(IndexedColors.BLUE.getIndex());
- style.setBorderTop(BorderStyle.MEDIUM_DASHED);
- style.setTopBorderColor(IndexedColors.BLACK.getIndex());
- cell.setCellStyle(style);
-
- // Write the output to a file
- try (OutputStream fileOut = new FileOutputStream("workbook.xls")) {
- wb.write(fileOut);
- }
-
- wb.close();
- </pre>
+<div class="code">
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"></span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">Workbook wb = new HSSFWorkbook();</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">Sheet sheet = wb.createSheet("new sheet");</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"></span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">// Create a row and put some cells in it. Rows are 0 based.</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">Row row = sheet.createRow(1);</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"></span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">// Create a cell and put a value in it.</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">Cell cell = row.createCell(1);</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">cell.setCellValue(4);</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"></span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">// Style the cell with borders all around.</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">CellStyle style = wb.createCellStyle();</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">style.setBorderBottom(BorderStyle.THIN);</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">style.setBottomBorderColor(IndexedColors.BLACK.getIndex());</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">style.setBorderLeft(BorderStyle.THIN);</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">style.setLeftBorderColor(IndexedColors.GREEN.getIndex());</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">style.setBorderRight(BorderStyle.THIN);</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">style.setRightBorderColor(IndexedColors.BLUE.getIndex());</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">style.setBorderTop(BorderStyle.MEDIUM_DASHED);</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">style.setTopBorderColor(IndexedColors.BLACK.getIndex());</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">cell.setCellStyle(style);</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"></span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">// Write the output to a file</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">try (OutputStream fileOut = new FileOutputStream("workbook.xls")) {</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"> wb.write(fileOut);</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">}</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"> </span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">wb.close();</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"> </span>
+</div>
+</div>
<a name="Iterator" id="Iterator"></a><a name="Iterate+over+rows+and+cells"></a>
<h4>Iterate over rows and cells</h4>
<p>Sometimes, you'd like to just iterate over all the sheets in
@@ -735,15 +1181,35 @@ document.write("Last Published: " + docu
implicitly using a for-each loop.
Note that a rowIterator and cellIterator iterate over rows or
cells that have been created, skipping empty rows and cells.</p>
-<pre class="code">
- for (Sheet sheet : wb ) {
- for (Row row : sheet) {
- for (Cell cell : row) {
- // Do something here
- }
- }
- }
- </pre>
+<div class="code">
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"></span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">for (Sheet sheet : wb ) {</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"> for (Row row : sheet) {</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"> for (Cell cell : row) {</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"> // Do something here</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"> }</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"> }</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">}</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"></span>
+</div>
+</div>
<a name="Iterate+over+cells%2C+with+control+of+missing+%2F+blank+cells"></a>
<h4>Iterate over cells, with control of missing / blank cells</h4>
<p>In some cases, when iterating, you need full control over how
@@ -756,31 +1222,83 @@ document.write("Last Published: " + docu
to fetch the cell. Use a
<a href="../../apidocs/dev/org/apache/poi/ss/usermodel/Row.MissingCellPolicy.html">MissingCellPolicy</a>
to control how blank or null cells are handled.</p>
-<pre class="code">
- // Decide which rows to process
- int rowStart = Math.min(15, sheet.getFirstRowNum());
- int rowEnd = Math.max(1400, sheet.getLastRowNum());
-
- for (int rowNum = rowStart; rowNum < rowEnd; rowNum++) {
- Row r = sheet.getRow(rowNum);
- if (r == null) {
- // This whole row is empty
- // Handle it as needed
- continue;
- }
-
- int lastColumn = Math.max(r.getLastCellNum(), MY_MINIMUM_COLUMN_COUNT);
-
- for (int cn = 0; cn < lastColumn; cn++) {
- Cell c = r.getCell(cn, Row.RETURN_BLANK_AS_NULL);
- if (c == null) {
- // The spreadsheet is empty in this cell
- } else {
- // Do something useful with the cell's contents
- }
- }
- }
- </pre>
+<div class="code">
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"></span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">// Decide which rows to process</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">int rowStart = Math.min(15, sheet.getFirstRowNum());</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">int rowEnd = Math.max(1400, sheet.getLastRowNum());</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"></span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">for (int rowNum = rowStart; rowNum < rowEnd; rowNum++) {</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"> Row r = sheet.getRow(rowNum);</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"> if (r == null) {</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"> // This whole row is empty</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"> // Handle it as needed</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"> continue;</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"> }</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"></span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"> int lastColumn = Math.max(r.getLastCellNum(), MY_MINIMUM_COLUMN_COUNT);</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"></span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"> for (int cn = 0; cn < lastColumn; cn++) {</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"> Cell c = r.getCell(cn, Row.RETURN_BLANK_AS_NULL);</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"> if (c == null) {</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"> // The spreadsheet is empty in this cell</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"> } else {</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"> // Do something useful with the cell's contents</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"> }</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"> }</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">}</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"></span>
+</div>
+</div>
<a name="CellContents" id="CellContents"></a><a name="Getting+the+cell+contents"></a>
<h4>Getting the cell contents</h4>
<p>To get the contents of a cell, you first need to
@@ -792,63 +1310,173 @@ document.write("Last Published: " + docu
<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.*;
-
- DataFormatter formatter = new DataFormatter();
- 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(" - ");
-
- // get the text that appears in the cell by getting the cell value and applying any data formats (Date, 0.00, 1.23e9, $1.23, etc)
- String text = formatter.formatCellValue(cell);
- System.out.println(text);
-
- // Alternatively, get the value and format it yourself
- switch (cell.getCellType()) {
- case CellType.STRING:
- System.out.println(cell.getRichStringCellValue().getString());
- break;
- case CellType.NUMERIC:
- if (DateUtil.isCellDateFormatted(cell)) {
- System.out.println(cell.getDateCellValue());
- } else {
- System.out.println(cell.getNumericCellValue());
- }
- break;
- case CellType.BOOLEAN:
- System.out.println(cell.getBooleanCellValue());
- break;
- case CellType.FORMULA:
- System.out.println(cell.getCellFormula());
- break;
- case CellType.BLANK:
- System.out.println();
- break;
- default:
- System.out.println();
- }
- }
- }
- </pre>
+<div class="code">
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"></span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">// import org.apache.poi.ss.usermodel.*;</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"></span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">DataFormatter formatter = new DataFormatter();</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">Sheet sheet1 = wb.getSheetAt(0);</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">for (Row row : sheet1) {</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"> for (Cell cell : row) {</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"> CellReference cellRef = new CellReference(row.getRowNum(), cell.getColumnIndex());</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"> System.out.print(cellRef.formatAsString());</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"> System.out.print(" - ");</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"></span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"> // get the text that appears in the cell by getting the cell value and applying any data formats (Date, 0.00, 1.23e9, $1.23, etc)</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"> String text = formatter.formatCellValue(cell);</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"> System.out.println(text);</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"></span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"> // Alternatively, get the value and format it yourself</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"> switch (cell.getCellType()) {</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"> case CellType.STRING:</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"> System.out.println(cell.getRichStringCellValue().getString());</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"> break;</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"> case CellType.NUMERIC:</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"> if (DateUtil.isCellDateFormatted(cell)) {</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"> System.out.println(cell.getDateCellValue());</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"> } else {</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"> System.out.println(cell.getNumericCellValue());</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"> }</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"> break;</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"> case CellType.BOOLEAN:</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"> System.out.println(cell.getBooleanCellValue());</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"> break;</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"> case CellType.FORMULA:</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"> System.out.println(cell.getCellFormula());</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"> break;</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"> case CellType.BLANK:</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"> System.out.println();</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"> break;</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"> default:</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"> System.out.println();</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"> }</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"> }</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">}</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"></span>
+</div>
+</div>
<a name="TextExtraction" id="TextExtraction"></a><a name="Text+Extraction"></a>
<h4>Text Extraction</h4>
<p>For most text extraction requirements, the standard
ExcelExtractor class should provide all you need.</p>
-<pre class="code">
- try (InputStream inp = new FileInputStream("workbook.xls")) {
- HSSFWorkbook wb = new HSSFWorkbook(new POIFSFileSystem(inp));
- ExcelExtractor extractor = new ExcelExtractor(wb);
-
- extractor.setFormulasNotResults(true);
- extractor.setIncludeSheetNames(false);
- String text = extractor.getText();
- wb.close();
- }
- </pre>
+<div class="code">
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"></span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">try (InputStream inp = new FileInputStream("workbook.xls")) {</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"> HSSFWorkbook wb = new HSSFWorkbook(new POIFSFileSystem(inp));</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"> ExcelExtractor extractor = new ExcelExtractor(wb);</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"></span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"> extractor.setFormulasNotResults(true);</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"> extractor.setIncludeSheetNames(false);</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"> String text = extractor.getText();</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"> wb.close();</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">}</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"> </span>
+</div>
+</div>
<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>
@@ -856,92 +1484,262 @@ document.write("Last Published: " + docu
</p>
<a name="FillsAndFrills" id="FillsAndFrills"></a><a name="Fills+and+colors"></a>
<h4>Fills and colors</h4>
-<pre class="code">
- Workbook wb = new XSSFWorkbook();
- Sheet sheet = wb.createSheet("new sheet");
-
- // Create a row and put some cells in it. Rows are 0 based.
- Row row = sheet.createRow(1);
-
- // Aqua background
- CellStyle style = wb.createCellStyle();
- style.setFillBackgroundColor(IndexedColors.AQUA.getIndex());
- style.setFillPattern(FillPatternType.BIG_SPOTS);
- Cell cell = row.createCell(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(FillPatternType.SOLID_FOREGROUND);
- cell = row.createCell(2);
- cell.setCellValue("X");
- cell.setCellStyle(style);
-
- // Write the output to a file
- try (OutputStream fileOut = new FileOutputStream("workbook.xls")) {
- wb.write(fileOut);
- }
-
- wb.close();
- </pre>
+<div class="code">
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"></span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">Workbook wb = new XSSFWorkbook();</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">Sheet sheet = wb.createSheet("new sheet");</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"></span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">// Create a row and put some cells in it. Rows are 0 based.</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">Row row = sheet.createRow(1);</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"></span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">// Aqua background</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">CellStyle style = wb.createCellStyle();</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">style.setFillBackgroundColor(IndexedColors.AQUA.getIndex());</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">style.setFillPattern(FillPatternType.BIG_SPOTS);</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">Cell cell = row.createCell(1);</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">cell.setCellValue("X");</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">cell.setCellStyle(style);</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"></span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">// Orange "foreground", foreground being the fill foreground not the font color.</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">style = wb.createCellStyle();</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">style.setFillForegroundColor(IndexedColors.ORANGE.getIndex());</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">style.setFillPattern(FillPatternType.SOLID_FOREGROUND);</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">cell = row.createCell(2);</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">cell.setCellValue("X");</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">cell.setCellStyle(style);</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"></span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">// Write the output to a file</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">try (OutputStream fileOut = new FileOutputStream("workbook.xls")) {</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"> wb.write(fileOut);</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">}</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"></span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">wb.close();</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"> </span>
+</div>
+</div>
<a name="MergedCells" id="MergedCells"></a><a name="Merging+cells"></a>
<h4>Merging cells</h4>
-<pre class="code">
- Workbook wb = new HSSFWorkbook();
- Sheet sheet = wb.createSheet("new sheet");
-
- Row row = sheet.createRow(1);
- Cell cell = row.createCell(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
- try (OutputStream fileOut = new FileOutputStream("workbook.xls")) {
- wb.write(fileOut);
- }
-
- wb.close();
- </pre>
+<div class="code">
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"></span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">Workbook wb = new HSSFWorkbook();</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">Sheet sheet = wb.createSheet("new sheet");</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"></span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">Row row = sheet.createRow(1);</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">Cell cell = row.createCell(1);</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">cell.setCellValue("This is a test of merging");</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"></span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">sheet.addMergedRegion(new CellRangeAddress(</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"> 1, //first row (0-based)</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"> 1, //last row (0-based)</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"> 1, //first column (0-based)</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"> 2 //last column (0-based)</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">));</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"></span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">// Write the output to a file</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">try (OutputStream fileOut = new FileOutputStream("workbook.xls")) {</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"> wb.write(fileOut);</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">}</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"></span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">wb.close();</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"> </span>
+</div>
+</div>
<a name="WorkingWithFonts" id="WorkingWithFonts"></a><a name="Working+with+fonts"></a>
<h4>Working with fonts</h4>
-<pre class="code">
- Workbook wb = new HSSFWorkbook();
- Sheet sheet = wb.createSheet("new sheet");
-
- // 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
- try (OutputStream fileOut = new FileOutputStream("workbook.xls")) {
- wb.write(fileOut);
- }
-
- wb.close();
- </pre>
+<div class="code">
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"></span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">Workbook wb = new HSSFWorkbook();</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">Sheet sheet = wb.createSheet("new sheet");</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"></span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">// Create a row and put some cells in it. Rows are 0 based.</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">Row row = sheet.createRow(1);</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"></span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">// Create a new font and alter it.</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">Font font = wb.createFont();</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">font.setFontHeightInPoints((short)24);</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">font.setFontName("Courier New");</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">font.setItalic(true);</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">font.setStrikeout(true);</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"></span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">// Fonts are set into a style so create a new one to use.</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">CellStyle style = wb.createCellStyle();</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">style.setFont(font);</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"></span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">// Create a cell and put a value in it.</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">Cell cell = row.createCell(1);</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">cell.setCellValue("This is a test of fonts");</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">cell.setCellStyle(style);</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"></span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">// Write the output to a file</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">try (OutputStream fileOut = new FileOutputStream("workbook.xls")) {</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"> wb.write(fileOut);</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">}</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"></span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">wb.close();</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"></span>
+</div>
+</div>
<p>
Note, the maximum number of unique fonts in a workbook is limited to 32767. You should re-use fonts in your applications instead of
creating a font for each cell.
@@ -950,245 +1748,691 @@ Examples:
<p>
<strong>Wrong:</strong>
</p>
-<pre class="code">
- for (int i = 0; i < 10000; i++) {
- Row row = sheet.createRow(i);
- Cell cell = row.createCell(0);
-
- CellStyle style = workbook.createCellStyle();
- Font font = workbook.createFont();
- font.setBoldweight(Font.BOLDWEIGHT_BOLD);
- style.setFont(font);
- cell.setCellStyle(style);
- }
-</pre>
+<div class="code">
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"></span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">for (int i = 0; i < 10000; i++) {</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"> Row row = sheet.createRow(i);</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"> Cell cell = row.createCell(0);</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"></span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"> CellStyle style = workbook.createCellStyle();</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"> Font font = workbook.createFont();</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"> font.setBoldweight(Font.BOLDWEIGHT_BOLD);</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"> style.setFont(font);</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"> cell.setCellStyle(style);</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">}</span>
+</div>
+</div>
<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 < 10000; i++) {
- Row row = sheet.createRow(i);
- Cell cell = row.createCell(0);
- cell.setCellStyle(style);
- }
-</pre>
+<div class="code">
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"></span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">CellStyle style = workbook.createCellStyle();</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">Font font = workbook.createFont();</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">font.setBoldweight(Font.BOLDWEIGHT_BOLD);</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">style.setFont(font);</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">for (int i = 0; i < 10000; i++) {</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"> Row row = sheet.createRow(i);</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"> Cell cell = row.createCell(0);</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"> cell.setCellStyle(style);</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">}</span>
+</div>
+</div>
<a name="CustomColors" id="CustomColors"></a><a name="Custom+colors"></a>
<h4>Custom colors</h4>
<p>
<strong>HSSF:</strong>
</p>
-<pre class="code">
- HSSFWorkbook wb = new HSSFWorkbook();
- HSSFSheet sheet = wb.createSheet();
- HSSFRow row = sheet.createRow(0);
- HSSFCell cell = row.createCell(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(FillPatternType.SOLID_FOREGROUND);
-
- HSSFFont font = wb.createFont();
- font.setColor(HSSFColor.RED.index);
- style.setFont(font);
-
- cell.setCellStyle(style);
-
- //save with the default palette
- try (OutputStream out = new FileOutputStream("default_palette.xls")) {
- wb.write(out);
- }
-
- //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
- try (out = new FileOutputStream("modified_palette.xls")) {
- wb.write(out);
- }
- </pre>
+<div class="code">
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"></span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">HSSFWorkbook wb = new HSSFWorkbook();</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">HSSFSheet sheet = wb.createSheet();</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">HSSFRow row = sheet.createRow(0);</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">HSSFCell cell = row.createCell(0);</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">cell.setCellValue("Default Palette");</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"></span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">//apply some colors from the standard palette,</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">// as in the previous examples.</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">//we'll use red text on a lime background</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"></span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">HSSFCellStyle style = wb.createCellStyle();</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">style.setFillForegroundColor(HSSFColor.LIME.index);</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">style.setFillPattern(FillPatternType.SOLID_FOREGROUND);</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"></span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">HSSFFont font = wb.createFont();</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">font.setColor(HSSFColor.RED.index);</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">style.setFont(font);</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"></span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">cell.setCellStyle(style);</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"></span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">//save with the default palette</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">try (OutputStream out = new FileOutputStream("default_palette.xls")) {</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"> wb.write(out);</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">}</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"></span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">//now, let's replace RED and LIME in the palette</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">// with a more attractive combination</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">// (lovingly borrowed from freebsd.org)</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"></span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">cell.setCellValue("Modified Palette");</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"></span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">//creating a custom palette for the workbook</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">HSSFPalette palette = wb.getCustomPalette();</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"></span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">//replacing the standard red with freebsd.org red</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">palette.setColorAtIndex(HSSFColor.RED.index,</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"> (byte) 153, //RGB red (0-255)</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"> (byte) 0, //RGB green</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"> (byte) 0 //RGB blue</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">);</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">//replacing lime with freebsd.org gold</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">palette.setColorAtIndex(HSSFColor.LIME.index, (byte) 255, (byte) 204, (byte) 102);</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"></span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">//save with the modified palette</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">// note that wherever we have previously used RED or LIME, the</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">// new colors magically appear</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">try (out = new FileOutputStream("modified_palette.xls")) {</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"> wb.write(out);</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">}</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"> </span>
+</div>
+</div>
<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), new DefaultIndexedColorMap()));
- style1.setFillPattern(FillPatternType.SOLID_FOREGROUND);
- </pre>
+<div class="code">
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"></span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">XSSFWorkbook wb = new XSSFWorkbook();</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">XSSFSheet sheet = wb.createSheet();</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">XSSFRow row = sheet.createRow(0);</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">XSSFCell cell = row.createCell( 0);</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">cell.setCellValue("custom XSSF colors");</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"></span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">XSSFCellStyle style1 = wb.createCellStyle();</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">style1.setFillForegroundColor(new XSSFColor(new java.awt.Color(128, 0, 128), new DefaultIndexedColorMap()));</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">style1.setFillPattern(FillPatternType.SOLID_FOREGROUND);</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"> </span>
+</div>
+</div>
<a name="ReadWriteWorkbook" id="ReadWriteWorkbook"></a><a name="Reading+and+Rewriting+Workbooks"></a>
<h4>Reading and Rewriting Workbooks</h4>
-<pre class="code">
- try (InputStream inp = new FileInputStream("workbook.xls")) {
- //InputStream inp = new FileInputStream("workbook.xlsx");
-
- 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(CellType.STRING);
- cell.setCellValue("a test");
-
- // Write the output to a file
- try (OutputStream fileOut = new FileOutputStream("workbook.xls")) {
- wb.write(fileOut);
- }
- }
- </pre>
+<div class="code">
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"></span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">try (InputStream inp = new FileInputStream("workbook.xls")) {</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">//InputStream inp = new FileInputStream("workbook.xlsx");</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"></span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"> Workbook wb = WorkbookFactory.create(inp);</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"> Sheet sheet = wb.getSheetAt(0);</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"> Row row = sheet.getRow(2);</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"> Cell cell = row.getCell(3);</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"> if (cell == null)</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"> cell = row.createCell(3);</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"> cell.setCellType(CellType.STRING);</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"> cell.setCellValue("a test");</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"></span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"> // Write the output to a file</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"> try (OutputStream fileOut = new FileOutputStream("workbook.xls")) {</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"> wb.write(fileOut);</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"> }</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">}</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"> </span>
+</div>
+</div>
<a name="NewLinesInCells" id="NewLinesInCells"></a><a name="Using+newlines+in+cells"></a>
<h4>Using newlines in cells</h4>
-<pre class="code">
- Workbook wb = new XSSFWorkbook(); //or new HSSFWorkbook();
- Sheet sheet = wb.createSheet();
-
- 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 accommodate two lines of text
- row.setHeightInPoints((2*sheet.getDefaultRowHeightInPoints()));
-
- //adjust column width to fit the content
- sheet.autoSizeColumn(2);
-
- try (OutputStream fileOut = new FileOutputStream("ooxml-newlines.xlsx")) {
- wb.write(fileOut);
- }
-
- wb.close();
- </pre>
+<div class="code">
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"></span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">Workbook wb = new XSSFWorkbook(); //or new HSSFWorkbook();</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">Sheet sheet = wb.createSheet();</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"></span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">Row row = sheet.createRow(2);</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">Cell cell = row.createCell(2);</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">cell.setCellValue("Use \n with word wrap on to create a new line");</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"></span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">//to enable newlines you need set a cell styles with wrap=true</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">CellStyle cs = wb.createCellStyle();</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">cs.setWrapText(true);</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">cell.setCellStyle(cs);</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"></span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">//increase row height to accommodate two lines of text</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">row.setHeightInPoints((2*sheet.getDefaultRowHeightInPoints()));</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"></span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">//adjust column width to fit the content</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">sheet.autoSizeColumn(2);</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"></span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">try (OutputStream fileOut = new FileOutputStream("ooxml-newlines.xlsx")) {</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"> wb.write(fileOut);</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">}</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"></span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">wb.close();</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"> </span>
+</div>
+</div>
<a name="DataFormats" id="DataFormats"></a><a name="Data+Formats"></a>
<h4>Data Formats</h4>
-<pre class="code">
- Workbook wb = new HSSFWorkbook();
- Sheet sheet = wb.createSheet("format sheet");
- CellStyle style;
- DataFormat format = wb.createDataFormat();
- Row row;
- Cell cell;
- int rowNum = 0;
- int 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);
-
- try (OutputStream fileOut = new FileOutputStream("workbook.xls")) {
- wb.write(fileOut);
- }
-
- wb.close();
- </pre>
+<div class="code">
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"></span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">Workbook wb = new HSSFWorkbook();</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">Sheet sheet = wb.createSheet("format sheet");</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">CellStyle style;</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">DataFormat format = wb.createDataFormat();</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">Row row;</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">Cell cell;</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">int rowNum = 0;</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">int colNum = 0;</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"></span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">row = sheet.createRow(rowNum++);</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">cell = row.createCell(colNum);</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">cell.setCellValue(11111.25);</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">style = wb.createCellStyle();</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">style.setDataFormat(format.getFormat("0.0"));</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">cell.setCellStyle(style);</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"></span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">row = sheet.createRow(rowNum++);</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">cell = row.createCell(colNum);</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">cell.setCellValue(11111.25);</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">style = wb.createCellStyle();</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">style.setDataFormat(format.getFormat("#,##0.0000"));</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">cell.setCellStyle(style);</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"></span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">try (OutputStream fileOut = new FileOutputStream("workbook.xls")) {</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"> wb.write(fileOut);</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody">}</span>
+</div>
+<div class="codeline">
+<span class="lineno"></span><span class="codebody"></span>
+</div>
+<div class="codeline">
[... 4528 lines stripped ...]
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@poi.apache.org
For additional commands, e-mail: commits-help@poi.apache.org