POI&&- the Java API for Microsoft Documents
1.在Apache官网下载Apache最新poi版本：poi-bin-3.11-.zip，解压；
这里要注意是在binary distribution 下载，binary distribution是jar包，source distribution是包含源文件的，可以打开java代码
2.在Eclipse中引入jar包：project&properties&Libraries&Add External JARs
Apache官网提供了一些例子：
注意：使用POI扩展包的时候出现&POI java.lang.NoClassDefFoundError: org/apache/xmlbeans/XmlException&异常，一般是由于POI的其中一个名为&xmlbeans-2.3.0.jar&(或其他版本)的扩展包没有添加到项目的Library里面。在poi-3.11中是在ooxml-lib
Example Code：（以读取桌面excel交易表为例，路径由dataPath传入）
public static Map&String, ArrayList&String&& readExcelData(String dataPath) throws IOException {
InputStream is = new FileInputStream(dataPath);
@SuppressWarnings("resource")
XSSFWorkbook wb = new XSSFWorkbook(is);
XSSFSheet sheet = wb.getSheetAt(0); // 获取第一个sheet表
XSSFRow rowD
Map&String, ArrayList&String&& resultData = new HashMap&String, ArrayList&String&&();
String trans_key = null;
for (int i = sheet.getTopRow(); i & sheet.getLastRowNum() + 1; i++) {
rowData = sheet.getRow(i);
if (i == sheet.getTopRow()) {
continue; //标题不读入
ArrayList&String& trans_item = new ArrayList&String&();
for (int j = rowData.getFirstCellNum(); j & rowData.getLastCellNum(); j++) {
if (j == rowData.getFirstCellNum()) {
trans_key = rowData.getCell(j).toString();
item = rowData.getCell(j).toString();
trans_item.add(item);
resultData.put(trans_key, trans_item);
is.close();
return resultD
TODO：有个小问题，
rowData.getLastCellNum()返回的时列数，比如里面有两列，则返回2；但是sheet.getLastRowNum()却不是行数，而是最后一行的位置，如果要活的确切行数的话，则需要+1；
阅读(...) 评论()当前访客身份：游客 [
求知若渴，虚心若愚
：引用来自“可苦可乐爱编程”的评论Base64Utils呢...
：Base64Utils呢，去哪了，狗日的垃圾代码
：这个方法不错, mark
：求解pkcs8encodedkeyspec中的PKCS8规范到底是什么...
：引用来自“ylxs90”的评论麻烦，直接bejson上面看...
：引用来自“ylxs90”的评论 麻烦，直接bejson上面...
：麻烦，直接bejson上面看一下，多省事啊
：最后一句说的挺好的。
：更多条形码控件代码请到条形码控件网www.tiaoma1...
：引用来自“zdc123”的评论import soja.base.Soja...
今日访问：42
昨日访问：40
本周访问：82
本月访问：42
所有访问：19163
POI创建Excel
发表于2年前( 14:41)&&
阅读（1525）&|&评论（）
0人收藏此文章,
Java 生成Excel文件 POI
这个是项目中用到的时候，做的一个类，
package&com.topwalk.iwp.
import&java.awt.C
import&java.io.F
import&java.io.FileOutputS
import&java.io.IOE
import&java.util.L
import&mons.lang3.RandomStringU
import&org.apache.poi.hssf.util.CellRangeA
import&org.apache.poi.ss.usermodel.BorderS
import&org.apache.poi.ss.usermodel.CellS
import&org.apache.poi.ss.usermodel.FillPatternT
import&org.apache.poi.ss.usermodel.F
import&org.apache.poi.ss.usermodel.HorizontalA
import&org.apache.poi.ss.usermodel.VerticalA
import&org.apache.poi.xssf.usermodel.XSSFC
import&org.apache.poi.xssf.usermodel.XSSFCellS
import&org.apache.poi.xssf.usermodel.XSSFC
import&org.apache.poi.xssf.usermodel.XSSFF
import&org.apache.poi.xssf.usermodel.XSSFR
import&org.apache.poi.xssf.usermodel.XSSFS
import&org.apache.poi.xssf.usermodel.XSSFW
import&org.apache.poi.xssf.usermodel.extensions.XSSFCellB
public&class&CreateExcel&{
private&static&XSSFFont&
private&static&XSSFWorkbook&
private&static&XSSFSheet&
&*&创建Excel文件，返回File文件对象
&*&@param&excelDataBean
public&static&File&createExcelReturnFile(ExcelDataBean&excelDataBean,String&IWPTitle){
//创建WorkBook对象
workbook=new&XSSFWorkbook();
font=createFont(workbook,(short)0);
FileOutputStream&out=
/*设置文件名*/
String&src="./ExcelFile/";
File&dir=new&File(src);
if(!dir.exists()){
dir.mkdir();
String&filename=RandomStringUtils.randomAlphanumeric(10);
filename=new&StringBuffer(src).append(filename).append(".xlsx").toString();
File&file=new&File(filename);
file.createNewFile();
//创建SHEET
sheet&=&workbook.createSheet();
workbook.setSheetName(0,&"查询结果");
//设置默认高度，默认宽度
sheet.setDefaultColumnWidth((short)25);
//sheet.setDefaultRowHeightInPoints(20);
sheet.setTabColor(123);
createHeadRow(workbook,&sheet,IWPTitle);
//写入IWP返回的查询信息
putValueToExcel(excelDataBean);
out=new&FileOutputStream(file);
}&catch&(Exception&e)&{
workbook.write(out);
out.close();
}&catch&(IOException&e)&{
e.printStackTrace();
&*&@param&excelDataBean
&*&@param&IWPTitle
&*&@return
public&static&XSSFWorkbook&createExcelReturnXSSFWorkbook(ExcelDataBean&excelDataBean,String&IWPTitle){
//创建WorkBook对象
workbook=new&XSSFWorkbook();
font=createFont(workbook,(short)0);
FileOutputStream&out=
/*设置文件名*/
String&src="";
String&filename=RandomStringUtils.randomAlphanumeric(10);
filename=new&StringBuffer(src).append(filename).append(".xlsx").toString();
File&file=new&File(filename);
//file.createNewFile();
//创建SHEET
sheet&=&workbook.createSheet();
workbook.setSheetName(0,&"查询结果");
//设置默认高度，默认宽度
sheet.setDefaultColumnWidth((short)25);
//sheet.setDefaultRowHeightInPoints(20);
sheet.setTabColor(123);
createHeadRow(workbook,&sheet,IWPTitle);
//写入IWP返回的查询信息
putValueToExcel(excelDataBean);
out=new&FileOutputStream(file);
}&catch&(Exception&e)&{
workbook.write(out);
out.close();
file.deleteOnExit();
}&catch&(Exception&e)&{
e.printStackTrace();
&*&设置CellStyle格式
&*&@param&workbook
&*&@return
public&static&XSSFCellStyle&createCellStyle(XSSFWorkbook&workbook){
XSSFCellStyle&cellStyle=workbook.createCellStyle();
//&设置单元格边框样式
&&&&//&CellStyle.BORDER_DOUBLE&&&&&&双边线
&&&&//&CellStyle.BORDER_THIN&&&&&&&&细边线
&&&&//&CellStyle.BORDER_MEDIUM&&&&&&中等边线
&&&&//&CellStyle.BORDER_DASHED&&&&&&虚线边线
&&&&//&CellStyle.BORDER_HAIR&&&&&&&&小圆点虚线边线
&&&&//&CellStyle.BORDER_THICK&&&&&&&粗边线
//getAllPersonBean
cellStyle.setBorderBottom(CellStyle.BORDER_THIN);
cellStyle.setBorderTop(CellStyle.BORDER_THIN);
cellStyle.setBorderLeft(CellStyle.BORDER_THIN);
cellStyle.setBorderRight(CellStyle.BORDER_THIN);
cellStyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
cellStyle.setAlignment(CellStyle.ALIGN_CENTER);
&//创建字体
&Font&fontHeader=workbook.createFont();
&//字体号码
&fontHeader.setFontHeightInPoints((short)10);
&//字体名称
&fontHeader.setFontName("宋体");
&cellStyle.setFont(fontHeader);
return&cellS
&*&竖向合并表格
&*&@param&sheet
&*&@param&rownumber
&*&@param&columnsize
public&static&void&&AddMergedRegion(XSSFSheet&sheet,int&columnsize,int&lastRowNum,int&rownum){
for&(int&i&=&0;&i&&=&i++)&{
sheet.addMergedRegion(new&CellRangeAddress(lastRowNum,lastRowNum+rownum-1,&i,i));
&*&功能：创建CellStyle样式
&*&@param& wb
XSSFWorkbook
&*&@param& backgroundColor 背景色
&*&@param& foregroundColor 前置色
&*&@param font
&*&@return CellStyle
public&static&CellStyle&createCellStyle(XSSFWorkbook&wb,short&backgroundColor,short&foregroundColor,short&halign,Font&font){
CellStyle&cs=wb.createCellStyle();
cs.setAlignment(halign);
cs.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
cs.setFillBackgroundColor(backgroundColor);
cs.setFillForegroundColor(foregroundColor);
cs.setFillPattern(CellStyle.SOLID_FOREGROUND);
cs.setFont(font);
&*&功能：创建带边框的CellStyle样式
&*&@param& wb
XSSFWorkbook
&*&@param& backgroundColor 背景色
&*&@param& foregroundColor 前置色
&*&@param font
&*&@return CellStyle
public&static&CellStyle&createBorderCellStyle(XSSFWorkbook&wb,short&backgroundColor,short&foregroundColor,short&halign,Font&font){
CellStyle&cs=wb.createCellStyle();
cs.setAlignment(halign);
cs.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
cs.setFillBackgroundColor(backgroundColor);
cs.setFillForegroundColor(foregroundColor);
cs.setFillPattern(CellStyle.SOLID_FOREGROUND);
cs.setFont(font);
cs.setBorderLeft(CellStyle.BORDER_DASHED);
cs.setBorderRight(CellStyle.BORDER_DASHED);
cs.setBorderTop(CellStyle.BORDER_DASHED);
cs.setBorderBottom(CellStyle.BORDER_DASHED);
&*&功能：创建CELL
&*&@param& row
&*&@param& cellNum int
&*&@param& style HSSFStyle
&*&@return HSSFCell
public&static&XSSFCell&createCell(XSSFRow&row,int&cellNum,CellStyle&style){
XSSFCell&cell=row.createCell(cellNum);
cell.setCellStyle(style);
&*&功能：合并单元格
&*&@param& sheet
&*&@param& firstRow int
&*&@param& lastRow
&*&@param& firstColumn int
&*&@param& lastColumn int
&*&@return int
合并区域号码
public&static&int&mergeCell(XSSFSheet&sheet,int&firstRow,int&lastRow,int&firstColumn,int&lastColumn){
return&sheet.addMergedRegion(new&CellRangeAddress(firstRow,lastRow,firstColumn,lastColumn));
&*&功能：创建字体
&*&@param& wb
XSSFWorkbook
&*&@param& boldweight short
&*&@param& color
&*&@return Font
public&static&Font&createFont(XSSFWorkbook&wb,short&boldweight,short&color,short&size){
Font&font=wb.createFont();
font.setBoldweight(boldweight);
font.setColor(color);
font.setFontHeightInPoints(size);
&*&创建Head
&*&@param&wb
&*&@param&sheet
private&&static&void&createHeadRow(XSSFWorkbook&wb,&&XSSFSheet&sheet,String&IWPTitle){
XSSFRow&row=sheet.createRow(0);
XSSFFont&&font=createFont(wb,&(short)18);
&&&&//&创建单元格样式
&&&&XSSFColor&color=new&XSSFColor(new&Color(36,&142,&195));
&&&&XSSFCellStyle&style&=createStyle(wb,&color);
&&&&style.setFont(font);//&设置字体
&&&&XSSFCell&cell=row.createCell(0);
&&&&cell.setCellType(XSSFCell.CELL_TYPE_STRING);
&&&&//cell.setCellStyle(style);
&&&&//合并单元个(第0行到第一行合并，)
&&&&sheet.addMergedRegion(new&CellRangeAddress(0,1,0,10));
//设置合并单元格的边框(合并一行用row(0)
//setCellBorder(0,10,row,style);
&&&&//合并两行，必须要用roe(1)才能设置,设置边框
setCellBorder(0,10,sheet.createRow(1),style);
setCellBorder(0,10,row,style);
//设置文件头
cell.setCellValue(IWPTitle);
cell.setCellStyle(style);
&*&合并单元格加边框&&水平
&*&@param&sheet
&*&@param&region
&*&@param&cs
public&static&void&setCellBorder(int&start,&int&end,&XSSFRow&row,&&&&XSSFCellStyle&style)&{
&&&&for(int&i=i&=i++){
&&&&&&&&XSSFCell&cell&=&row.createCell(i);
&&&&&&&&cell.setCellValue("");
&&&&&&&&cell.setCellStyle(style);
&*&创建字体
&*&@param&workbook
&*&@param&size&字体大小
&*&@return
private&static&XSSFFont&createFont(XSSFWorkbook&workbook,short&size){
XSSFFont&font=workbook.createFont();
//字体样式
font.setBoldweight(XSSFFont.BOLDWEIGHT_BOLD);
//字体颜色
font.setColor(XSSFFont.COLOR_NORMAL);
//字体大小
if(0==size){
font.setFontHeightInPoints(XSSFFont.DEFAULT_FONT_SIZE);
font.setFontHeightInPoints(size);
font.setFontName("微软雅黑");
&*&创建CellStyle
&*&@param&workbook
&*&@param&&&XSSFColor&color&&颜色
private&static&XSSFCellStyle&createStyle(XSSFWorkbook&workbook,XSSFColor&color){
XSSFCellStyle&cellStyle=workbook.createCellStyle();
//对齐样式
cellStyle.setAlignment(HorizontalAlignment.CENTER);
cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
//设置颜色
cellStyle.setFillForegroundColor(color);
cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
cellStyle.setBorderTop(BorderStyle.MEDIUM);
cellStyle.setBorderBottom(BorderStyle.MEDIUM);
cellStyle.setBorderLeft(BorderStyle.MEDIUM);
cellStyle.setBorderRight(BorderStyle.MEDIUM);
//设置边框颜色
cellStyle.setBorderColor(XSSFCellBorder.BorderSide.TOP,new&XSSFColor(Color.BLACK));
cellStyle.setBorderColor(XSSFCellBorder.BorderSide.BOTTOM,new&XSSFColor(Color.BLACK));
cellStyle.setBorderColor(XSSFCellBorder.BorderSide.LEFT,new&XSSFColor(Color.BLACK));
cellStyle.setBorderColor(XSSFCellBorder.BorderSide.RIGHT,new&XSSFColor(Color.BLACK));
//设置字体
cellStyle.setFont(font);
//设置自动换行
cellStyle.setWrapText(true);
return&cellS
&*&将数据填充到Excel中去
&*&@param&bean
private&static&void&putValueToExcel(ExcelDataBean&bean){
//查询时间
String&queryDate&=&bean.getQueryDate();
//查询参数
String&queryParam&=&bean.getQueryParam();
//查询结果
List&QueryResult&&queryResultList&=&bean.getQueryResult();
//写第一行信息，查询参数，查询时间&row&为2
writeParamRow(queryParam,&queryDate);
//遍历&&写查询获得是真正结果&空一行&&row从4开始
for&(int&i&=&0;&i&&&queryResultList.size();&i++)&{
QueryResult&queryResult&=&queryResultList.get(i);
//服务名称(空一行，另起一行&服务名称：达梦数据库查询)
//服务名称行样式
XSSFCellStyle&serviceNameRowStyle=createStyle(workbook,&new&XSSFColor(new&Color(255,255,255)));
//每个服务方法结果时间空一行，所以加2
XSSFRow&serviceNameRow&=&sheet.createRow(sheet.getLastRowNum()+2);
XSSFCell&keyCell&=&serviceNameRow.createCell(0);
keyCell.setCellValue("服务名称");
keyCell.setCellStyle(serviceNameRowStyle);
XSSFCell&valueCell=serviceNameRow.createCell(1);
valueCell.setCellValue(queryResult.getServiceName());
valueCell.setCellStyle(serviceNameRowStyle);
//开始写查询结果
List&List&String&&&resultList&=&queryResult.getList();
for&(int&j&=&0;&j&&&resultList.size();&j++)&{
//遍历，这里的每一个rowValueList就对应Excel文件上的一行
List&String&&rowValueList&=&resultList.get(j);
//如果是第一个对象，则是查询字段的字段名，表头，设置样式
XSSFRow&tableHeadRow=sheet.createRow(sheet.getLastRowNum()+1);
//创建Style&有边框，字体，单元格背景等
XSSFCellStyle&headRowStyle=createStyle(workbook,&new&XSSFColor(new&Color(155,187,89)));
for&(int&k&=&0;&k&&&rowValueList.size();&k++)&{
XSSFCell&cell=tableHeadRow.createCell(k);
cell.setCellType(XSSFCell.CELL_TYPE_STRING);
cell.setCellValue(rowValueList.get(k));
cell.setCellStyle(headRowStyle);
//如果不是第一个对象，则是服务查询返回的结果信息，需要判断结果中是不是有空信息，设置背景色为白色
XSSFRow&tableHeadRow=sheet.createRow(sheet.getLastRowNum()+1);
XSSFCellStyle&tableValueRowStyle=createStyle(workbook,&new&XSSFColor(new&Color(255,255,255)));
for&(int&k&=&0;&k&&&rowValueList.size();&k++)&{
XSSFCell&cell=tableHeadRow.createCell(k);
//用正则判断，如果值为数据，则将Cell的格式设置为数据格式，如果不能匹配，则设置成字符串格式
if((rowValueList.get(k)).matches("^[1-9]\\d*$")){
cell.setCellType(XSSFCell.CELL_TYPE_NUMERIC);
cell.setCellType(XSSFCell.CELL_TYPE_STRING);
//如果服务查询结果中，某一个字段信息是空值，则用""填充，避免有空指针错误出现
if(null==rowValueList.get(k)){
cell.setCellValue("");
cell.setCellValue(rowValueList.get(k));
cell.setCellStyle(tableValueRowStyle);
&*&写第一行信息
&*&@param&queryParam&&查询参数
&*&@param&queryDate&&&查询时间
private&static&void&writeParamRow(String&queryParam,String&queryDate){
XSSFCellStyle&paramRowStyle=createStyle(workbook,&new&XSSFColor(new&Color(255,235,156)));
XSSFCellStyle&valueRowStyle=createStyle(workbook,&new&XSSFColor(new&Color(255,199,206)));
//设置第三行显示查询参数和查询时间
XSSFRow&paramRow&=&sheet.createRow(2);
XSSFCell&paramKeyCell&=&paramRow.createCell(0);
paramKeyCell.setCellValue("查询参数");
paramKeyCell.setCellStyle(paramRowStyle);
XSSFCell&paramValueCell&=&paramRow.createCell(1);
paramValueCell.setCellValue(queryParam);
paramValueCell.setCellStyle(valueRowStyle);
XSSFCell&dateKeyCell&=&paramRow.createCell(2);
dateKeyCell.setCellValue("查询时间");
dateKeyCell.setCellStyle(paramRowStyle);
XSSFCell&dateValueCell&=&paramRow.createCell(3);
dateValueCell.setCellValue(queryDate);
dateValueCell.setCellStyle(valueRowStyle);
更多开发者职位上
1)">1)">1" ng-class="{current:{{currentPage==page}}}" ng-repeat="page in pages"><li class='page' ng-if="(endIndex<li class='page next' ng-if="(currentPage
相关文章阅读org.apache.poi.xssf.streaming.SXSSFWorkbook - 源码查看 - 拨云剑
/* ====================================================================
Licensed to the Apache Software Foundation (ASF) under one or more
contributor license agreements.
See the NOTICE file distributed with
this work for additional information regarding copyright ownership.
The ASF licenses this file to You under the Apache License, Version 2.0
(the "License"); you may not use this file except in compliance with
the License.
You may obtain a copy of the License at
http://www.apache.org/licenses/LICENSE-2.0
Unless required by applicable law or agreed to in writing, software
distributed under the License is distributed on an "AS IS" BASIS,
WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
See the License for the specific language governing permissions and
limitations under the License.
==================================================================== */
package org.apache.poi.xssf.
import org.apache.poi.ss.usermodel.W
import org.apache.poi.ss.usermodel.S
import org.apache.poi.ss.usermodel.F
import org.apache.poi.ss.usermodel.CellS
import org.apache.poi.ss.usermodel.N
import org.apache.poi.ss.usermodel.DataF
import org.apache.poi.ss.usermodel.PictureD
import org.apache.poi.ss.usermodel.CreationH
import org.apache.poi.xssf.usermodel.XSSFW
import org.apache.poi.xssf.usermodel.XSSFS
import java.io.IOE
import java.io.InputS
import java.io.InputStreamR
import java.io.OutputS
import java.io.OutputStreamW
import java.io.FileOutputS
import java.io.F
import java.util.*;
import java.util.zip.ZipF
import java.util.zip.ZipOutputS
import java.util.zip.ZipE
import org.apache.poi.ss.formula.udf.UDFF
import org.apache.poi.ss.usermodel.Row.MissingCellP
import org.apache.poi.ss.util.CellRangeA
* Streaming version of XSSFWorkbook implementing the "BigGridDemo" strategy.
* @author Alex Geller, Four J's Development Tools
public class SXSSFWorkbook implements Workbook
* Specifies how many rows can be accessed at most via getRow().
* When a new node is created via createRow() and the total number
* of unflushed records would exeed the specified value, then the
* row with the lowest index value is flushed and cannot be accessed
* via getRow() anymore.
public static final int DEFAULT_WINDOW_SIZE = 100;
XSSFWorkbook _
HashMap _sxFromXHash=new HashMap();
HashMap _xFromSxHash=new HashMap();
private int _randomAccessWindowSize = DEFAULT_WINDOW_SIZE;
* whetehr temp files should be compressed.
private boolean _compressTmpFiles =
* Construct a new workbook
public SXSSFWorkbook(){
this(null /*workbook*/);
* Construct a workbook from a template.
* There are three use-cases to use SXSSFWorkbook(XSSFWorkbook) :
Append new sheets to existing workbooks. You can open existing
workbook from a file or create on the fly with XSSF.
Append rows to existing sheets. The row number MUST be greater
than max(rownum) in the template sheet.
Use existing workbook as a template and re-use global objects such
as cell styles, formats, images, etc.
* All three use cases can work in a combination.
* What is not supported:
Access initial cells and rows in the template. After constructing
SXSSFWorkbook(XSSFWorkbook) all internal windows are empty and
SXSSFSheet@getRow and SXSSFRow#getCell return null.
Override existing cells and rows. The API silently allows that but
the output file is invalid and Excel cannot read it.
* @param workbook
the template workbook
public SXSSFWorkbook(XSSFWorkbook workbook){
this(workbook, DEFAULT_WINDOW_SIZE);
* Constructs an workbook from an existing workbook.
* When a new node is created via createRow() and the total number
* of unflushed records would exceed the specified value, then the
* row with the lowest index value is flushed and cannot be accessed
* via getRow() anymore.
* A value of -1 indicates unlimited access. In this case all
* records that have not been flushed by a call to flush() are available
* for random access.
* A value of 0 is not allowed because it would flush any newly created row
* without having a chance to specify any cells.
* @param rowAccessWindowSize
public SXSSFWorkbook(XSSFWorkbook workbook, int rowAccessWindowSize){
this(workbook,rowAccessWindowSize, false);
* Constructs an workbook from an existing workbook.
* When a new node is created via createRow() and the total number
* of unflushed records would exceed the specified value, then the
* row with the lowest index value is flushed and cannot be accessed
* via getRow() anymore.
* A value of -1 indicates unlimited access. In this case all
* records that have not been flushed by a call to flush() are available
* for random access.
* A value of 0 is not allowed because it would flush any newly created row
* without having a chance to specify any cells.
* @param rowAccessWindowSize
* @param compressTmpFiles whether to use gzip compression for temporary files
public SXSSFWorkbook(XSSFWorkbook workbook, int rowAccessWindowSize, boolean compressTmpFiles){
setRandomAccessWindowSize(rowAccessWindowSize);
setCompressTempFiles(compressTmpFiles);
if (workbook == null)
_wb=new XSSFWorkbook();
for ( int i = 0; i < _wb.getNumberOfSheets(); i++ )
XSSFSheet sheet = _wb.getSheetAt( i );
createAndRegisterSXSSFSheet( sheet );
* Construct an empty workbook and specify the window for row access.
* When a new node is created via createRow() and the total number
* of unflushed records would exceed the specified value, then the
* row with the lowest index value is flushed and cannot be accessed
* via getRow() anymore.
* A value of -1 indicates unlimited access. In this case all
* records that have not been flushed by a call to flush() are available
* for random access.
* A value of 0 is not allowed because it would flush any newly created row
* without having a chance to specify any cells.
* @param rowAccessWindowSize
public SXSSFWorkbook(int rowAccessWindowSize){
this(null /*workbook*/, rowAccessWindowSize);
public int getRandomAccessWindowSize()
return _randomAccessWindowS
private void setRandomAccessWindowSize(int rowAccessWindowSize)
if(rowAccessWindowSize == 0 || rowAccessWindowSize < -1) {
throw new IllegalArgumentException("rowAccessWindowSize must be greater than 0 or -1");
_randomAccessWindowSize = rowAccessWindowS
* Set whether temp files should be compressed.
SXSSF writes sheet data in temporary files (a temp file per-sheet)
and the size of these temp files can grow to to a very large size,
e.g. for a 20 MB csv data the size of the temp xml file become few GB large.
If the "compress" flag is set to true then the temporary XML is gzipped.
Please note the the "compress" option may cause performance penalty.
* @param compress whether to compress temp files
public void setCompressTempFiles(boolean compress){
_compressTmpFiles =
SheetDataWriter createSheetDataWriter() throws IOException {
if(_compressTmpFiles) {
return new GZIPSheetDataWriter();
return new SheetDataWriter();
XSSFSheet getXSSFSheet(SXSSFSheet sheet)
XSSFSheet result=_sxFromXHash.get(sheet);
SXSSFSheet getSXSSFSheet(XSSFSheet sheet)
SXSSFSheet result=_xFromSxHash.get(sheet);
void registerSheetMapping(SXSSFSheet sxSheet,XSSFSheet xSheet)
_sxFromXHash.put(sxSheet,xSheet);
_xFromSxHash.put(xSheet,sxSheet);
void deregisterSheetMapping(XSSFSheet xSheet)
SXSSFSheet sxSheet=getSXSSFSheet(xSheet);
_sxFromXHash.remove(sxSheet);
_xFromSxHash.remove(xSheet);
private XSSFSheet getSheetFromZipEntryName(String sheetRef)
for(XSSFSheet sheet : _sxFromXHash.values())
if(sheetRef.equals(sheet.getPackagePart().getPartName().getName().substring(1)))
private void injectData(File zipfile, OutputStream out) throws IOException
ZipFile zip = new ZipFile(zipfile);
ZipOutputStream zos = new ZipOutputStream(out);
@SuppressWarnings("unchecked")
Enumeration en = (Enumeration) zip.entries();
while (en.hasMoreElements())
ZipEntry ze = en.nextElement();
zos.putNextEntry(new ZipEntry(ze.getName()));
InputStream is = zip.getInputStream(ze);
XSSFSheet xSheet=getSheetFromZipEntryName(ze.getName());
if(xSheet!=null)
SXSSFSheet sxSheet=getSXSSFSheet(xSheet);
InputStream xis = sxSheet.getWorksheetXMLInputStream();
copyStreamAndInjectWorksheet(is,zos,xis);
xis.close();
copyStream(is, zos);
is.close();
zos.close();
zip.close();
private static void copyStream(InputStream in, OutputStream out) throws IOException {
byte[] chunk = new byte[1024];
while ((count = in.read(chunk)) >=0 ) {
out.write(chunk,0,count);
private static void copyStreamAndInjectWorksheet(InputStream in, OutputStream out, InputStream worksheetData) throws IOException {
InputStreamReader inReader=new InputStreamReader(in,"UTF-8"); //TODO: Is it always UTF-8 or do we need to read the xml encoding declaration in the file? If not, we should perhaps use a SAX reader instead.
OutputStreamWriter outWriter=new OutputStreamWriter(out,"UTF-8");
boolean needsStartTag =
int pos=0;
String s="<sheetData";
int n=s.length();
//Copy from "in" to "out" up to the string "" or "" (excluding).
while(((c=inReader.read())!=-1))
if(c==s.charAt(pos))
if(pos==n)
outWriter.write(s);
outWriter.write(c);
n = s.length();
needsStartTag =
if (c == '/')
// Found ')
outWriter.write(s);
outWriter.write('/');
outWriter.write(c);
outWriter.write(s);
outWriter.write('/');
outWriter.write(c);
if(pos>0) outWriter.write(s,0,pos);
if(c==s.charAt(0))
outWriter.write(c);
outWriter.flush();
if (needsStartTag)
outWriter.write("\n");
outWriter.flush();
//Copy the worksheet data to "out".
copyStream(worksheetData,out);
outWriter.write("");
outWriter.flush();
//Copy the rest of "in" to "out".
while(((c=inReader.read())!=-1))
outWriter.write(c);
outWriter.flush();
public XSSFWorkbook getXSSFWorkbook()
//start of interface implementation
* Convenience method to get the active sheet.
The active sheet is is the sheet
* which is currently displayed when the workbook is viewed in Excel.
* 'Selected' sheet(s) is a distinct concept.
* @return the index of the active sheet (0-based)
public int getActiveSheetIndex()
return _wb.getActiveSheetIndex();
* Convenience method to set the active sheet.
The active sheet is is the sheet
* which is currently displayed when the workbook is viewed in Excel.
* 'Selected' sheet(s) is a distinct concept.
* @param sheetIndex index of the active sheet (0-based)
public void setActiveSheet(int sheetIndex)
_wb.setActiveSheet(sheetIndex);
* Gets the first tab that is displayed in the list of tabs in excel.
* @return the first tab that to display in the list of tabs (0-based).
public int getFirstVisibleTab()
return _wb.getFirstVisibleTab();
* Sets the first tab that is displayed in the list of tabs in excel.
* @param sheetIndex the first tab that to display in the list of tabs (0-based)
public void setFirstVisibleTab(int sheetIndex)
_wb.setFirstVisibleTab(sheetIndex);
* Sets the order of appearance for a given sheet.
* @param sheetname the name of the sheet to reorder
* @param pos the position that we want to insert the sheet into (0 based)
public void setSheetOrder(String sheetname, int pos)
_wb.setSheetOrder(sheetname,pos);
* Sets the tab whose data is actually seen when the sheet is opened.
* This may be different from the "selected sheet" since excel seems to
* allow you to show the data of one sheet when another is seen "selected"
* in the tabs (at the bottom).
* @see Sheet#setSelected(boolean)
* @param index the index of the sheet to select (0 based)
public void setSelectedTab(int index)
_wb.setSelectedTab(index);
* Set the sheet name.
* @param sheet number (0 based)
* @throws IllegalArgumentException if the name is greater than 31 chars or contains /\?*[]
public void setSheetName(int sheet, String name)
_wb.setSheetName(sheet,name);
* Set the sheet name
* @param sheet sheet number (0 based)
* @return Sheet name
public String getSheetName(int sheet)
return _wb.getSheetName(sheet);
* Returns the index of the sheet by his name
* @param name the sheet name
* @return index of the sheet (0 based)
public int getSheetIndex(String name)
return _wb.getSheetIndex(name);
* Returns the index of the given sheet
* @param sheet the sheet to look up
* @return index of the sheet (0 based)
public int getSheetIndex(Sheet sheet)
return _wb.getSheetIndex(getXSSFSheet((SXSSFSheet)sheet));
* Sreate an Sheet for this Workbook, adds it to the sheets and returns
* the high level representation.
Use this to create new sheets.
* @return Sheet representing the new sheet.
public Sheet createSheet()
return createAndRegisterSXSSFSheet(_wb.createSheet());
SXSSFSheet createAndRegisterSXSSFSheet(XSSFSheet xSheet)
SXSSFSheet sxSheet=
sxSheet=new SXSSFSheet(this,xSheet);
catch (IOException ioe)
throw new RuntimeException(ioe);
registerSheetMapping(sxSheet,xSheet);
return sxS
* Create an Sheet for this Workbook, adds it to the sheets and returns
* the high level representation.
Use this to create new sheets.
* @param sheetname
sheetname to set for the sheet.
* @return Sheet representing the new sheet.
* @throws IllegalArgumentException if the name is greater than 31 chars or contains /\?*[]
public Sheet createSheet(String sheetname)
return createAndRegisterSXSSFSheet(_wb.createSheet(sheetname));
* Create an Sheet from an existing sheet in the Workbook.
* @return Sheet representing the cloned sheet.
public Sheet cloneSheet(int sheetNum)
throw new RuntimeException("NotImplemented");
* Get the number of spreadsheets in the workbook
* @return the number of sheets
public int getNumberOfSheets()
return _wb.getNumberOfSheets();
* Get the Sheet object at the given index.
* @param index of the sheet number (0-based physical & logical)
* @return Sheet at the provided index
public Sheet getSheetAt(int index)
return getSXSSFSheet(_wb.getSheetAt(index));
* Get sheet with the given name
* @param name of the sheet
* @return Sheet with the name provided or null if it does not exist
public Sheet getSheet(String name)
return getSXSSFSheet(_wb.getSheet(name));
* Removes sheet at the given index
* @param index of the sheet to remove (0-based)
public void removeSheetAt(int index)
XSSFSheet xSheet=_wb.getSheetAt(index);
_wb.removeSheetAt(index);
deregisterSheetMapping(xSheet);
* Sets the repeating rows and columns for a sheet (as found in
* File->PageSetup->Sheet).
This is function is included in the workbook
* because it creates/modifies name records which are stored at the
* workbook level.
* To set just repeating columns:
workbook.setRepeatingRowsAndColumns(0,0,1,-1-1);
* To set just repeating rows:
workbook.setRepeatingRowsAndColumns(0,-1,-1,0,4);
* To remove all repeating rows and columns for a sheet.
workbook.setRepeatingRowsAndColumns(0,-1,-1,-1,-1);
* @param sheetIndex
0 based index to sheet.
* @param startColumn
0 based start of repeating columns.
* @param endColumn
0 based end of repeating columns.
* @param startRow
0 based start of repeating rows.
* @param endRow
0 based end of repeating rows.
* @deprecated use {@link SXSSFSheet#setRepeatingRows(CellRangeAddress)}
or {@link SXSSFSheet#setRepeatingColumns(CellRangeAddress)}
public void setRepeatingRowsAndColumns(int sheetIndex, int startColumn, int endColumn, int startRow, int endRow)
_wb.setRepeatingRowsAndColumns(sheetIndex,startColumn,endColumn,startRow,endRow);
* Create a new Font and add it to the workbook's font table
* @return new font object
public Font createFont()
return _wb.createFont();
* Finds a font that matches the one with the supplied attributes
* @return the font with the matched attributes or null
public Font findFont(short boldWeight, short color, short fontHeight, String name, boolean italic, boolean strikeout, short typeOffset, byte underline)
return _wb.findFont(boldWeight, color, fontHeight, name, italic, strikeout, typeOffset, underline);
* Get the number of fonts in the font table
* @return number of fonts
public short getNumberOfFonts()
return _wb.getNumberOfFonts();
* Get the font at the given index number
* @param idx
index number (0-based)
* @return font at the index
public Font getFontAt(short idx)
return _wb.getFontAt(idx);
* Create a new Cell style and add it to the workbook's style table
* @return the new Cell Style object
public CellStyle createCellStyle()
return _wb.createCellStyle();
* Get the number of styles the workbook contains
* @return count of cell styles
public short getNumCellStyles()
return _wb.getNumCellStyles();
* Get the cell style object at the given index
* @param idx
index within the set of styles (0-based)
* @return CellStyle object at the index
public CellStyle getCellStyleAt(short idx)
return _wb.getCellStyleAt(idx);
* Write out this workbook to an Outputstream.
* @param stream - the java OutputStream you wish to write to
* @exception IOException if anything can't be written.
public void write(OutputStream stream) throws IOException
for (SXSSFSheet sheet : _xFromSxHash.values())
sheet.flushRows();
//Save the template
File tmplFile = File.createTempFile("poi-sxssf-template", ".xlsx");
FileOutputStream os = new FileOutputStream(tmplFile);
_wb.write(os);
os.close();
//Substitute the template entries with the generated sheet data files
injectData(tmplFile, stream);
tmplFile.delete();
* Dispose of temporary files backing this workbook on disk.
* Calling this method will render the workbook unusable.
* @return true if all temporary files were deleted successfully.
public boolean dispose()
boolean success =
for (SXSSFSheet sheet : _sxFromXHash.keySet())
success = sheet.dispose() &&
* @return the total number of defined names in this workbook
public int getNumberOfNames()
return _wb.getNumberOfNames();
* @param name the name of the defined name
* @return the defined name with the specified name. null if not found.
public Name getName(String name)
return _wb.getName(name);
* @param nameIndex position of the named range (0-based)
* @return the defined name at the specified index
* @throws IllegalArgumentException if the supplied index is invalid
public Name getNameAt(int nameIndex)
return _wb.getNameAt(nameIndex);
* Creates a new (uninitialised) defined name in this workbook
* @return new defined name object
public Name createName()
return _wb.createName();
* Gets the defined name index by name
* Note: Excel defined names are case-insensitive and
* this method performs a case-insensitive search.
* @param name the name of the defined name
* @return zero based index of the defined name. -1 if not found.
public int getNameIndex(String name)
return _wb.getNameIndex(name);
* Remove the defined name at the specified index
* @param index named range index (0 based)
public void removeName(int index)
_wb.removeName(index);
* Remove a defined name by name
* @param name the name of the defined name
public void removeName(String name)
_wb.removeName(name);
* Sets the printarea for the sheet provided
* i.e. Reference = $A$1:$B$2
* @param sheetIndex Zero-based sheet index (0 Represents the first sheet to keep consistent with java)
* @param reference Valid name Reference for the Print Area
public void setPrintArea(int sheetIndex, String reference)
_wb.setPrintArea(sheetIndex,reference);
* For the Convenience of Java Programmers maintaining pointers.
* @see #setPrintArea(int, String)
* @param sheetIndex Zero-based sheet index (0 = First Sheet)
* @param startColumn Column to begin printarea
* @param endColumn Column to end the printarea
* @param startRow Row to begin the printarea
* @param endRow Row to end the printarea
public void setPrintArea(int sheetIndex, int startColumn, int endColumn, int startRow, int endRow)
_wb.setPrintArea(sheetIndex, startColumn, endColumn, startRow, endRow);
* Retrieves the reference for the printarea of the specified sheet,
* the sheet name is appended to the reference even if it was not specified.
* @param sheetIndex Zero-based sheet index (0 Represents the first sheet to keep consistent with java)
* @return String Null if no print area has been defined
public String getPrintArea(int sheetIndex)
return _wb.getPrintArea(sheetIndex);
* Delete the printarea for the sheet specified
* @param sheetIndex Zero-based sheet index (0 = First Sheet)
public void removePrintArea(int sheetIndex)
_wb.removePrintArea(sheetIndex);
* Retrieves the current policy on what to do when
getting missing or blank cells from a row.
* The default is to return blank and null cells.
{@link MissingCellPolicy}
public MissingCellPolicy getMissingCellPolicy()
return _wb.getMissingCellPolicy();
* Sets the policy on what to do when
getting missing or blank cells from a row.
* This will then apply to all calls to
{@link org.apache.poi.ss.usermodel.Row#getCell(int)}. See
{@link MissingCellPolicy}
public void setMissingCellPolicy(MissingCellPolicy missingCellPolicy)
_wb.setMissingCellPolicy(missingCellPolicy);
* Returns the instance of DataFormat for this workbook.
* @return the DataFormat object
public DataFormat createDataFormat()
return _wb.createDataFormat();
* Adds a picture to the workbook.
* @param pictureData
The bytes of the picture
* @param format
The format of the picture.
* @return the index to this picture (1 based).
* @see #PICTURE_TYPE_EMF
* @see #PICTURE_TYPE_WMF
* @see #PICTURE_TYPE_PICT
* @see #PICTURE_TYPE_JPEG
* @see #PICTURE_TYPE_PNG
* @see #PICTURE_TYPE_DIB
public int addPicture(byte[] pictureData, int format)
return _wb.addPicture(pictureData,format);
* Gets all pictures from the Workbook.
* @return the list of pictures (a list of {@link PictureData} objects.)
public List getAllPictures()
return _wb.getAllPictures();
* Returns an object that handles instantiating concrete
* classes of the various instances one needs for
HSSF and XSSF.
public CreationHelper getCreationHelper()
return _wb.getCreationHelper();
* @return false if this workbook is not visible in the GUI
public boolean isHidden()
return _wb.isHidden();
* @param hiddenFlag pass false to make the workbook visible in the GUI
public void setHidden(boolean hiddenFlag)
_wb.setHidden(hiddenFlag);
* Check whether a sheet is hidden.
* Note that a sheet could instead be set to be very hidden, which is different
({@link #isSheetVeryHidden(int)})
* @param sheetIx Number
* @return true if sheet is hidden
public boolean isSheetHidden(int sheetIx)
return _wb.isSheetHidden(sheetIx);
* Check whether a sheet is very hidden.
* This is different from the normal hidden status
({@link #isSheetHidden(int)})
* @param sheetIx sheet index to check
* @return true if sheet is very hidden
public boolean isSheetVeryHidden(int sheetIx)
return _wb.isSheetVeryHidden(sheetIx);
* Hide or unhide a sheet
* @param sheetIx the sheet index (0-based)
* @param hidden True to mark the sheet as hidden, false otherwise
public void setSheetHidden(int sheetIx, boolean hidden)
_wb.setSheetHidden(sheetIx,hidden);
* Hide or unhide a sheet.
0 - visible.
1 - hidden.
2 - very hidden.
* @param sheetIx the sheet index (0-based)
* @param hidden one of the following Workbook constants:
Workbook.SHEET_STATE_VISIBLE,
Workbook.SHEET_STATE_HIDDEN, or
Workbook.SHEET_STATE_VERY_HIDDEN.
* @throws IllegalArgumentException if the supplied sheet index or state is invalid
public void setSheetHidden(int sheetIx, int hidden)
_wb.setSheetHidden(sheetIx,hidden);
* Register a new toolpack in this workbook.
* @param toopack the toolpack to register
public void addToolPack(UDFFinder toopack)
_wb.addToolPack(toopack);
* Whether the application shall perform a full recalculation when the workbook is opened.
* Typically you want to force formula recalculation when you modify cell formulas or values
* of a workbook previously created by Excel. When set to 0, this flag will tell Excel
* that it needs to recalculate all formulas in the workbook the next time the file is opened.
* @param value true if the application will perform a full recalculation of
* workbook values when the workbook is opened
* @since 3.8
public void setForceFormulaRecalculation(boolean value){
_wb.setForceFormulaRecalculation(value);
* Whether Excel will be asked to recalculate all formulas when the
workbook is opened.
public boolean getForceFormulaRecalculation(){
return _wb.getForceFormulaRecalculation();
//end of interface implementation