Apache的POI常用api

目前常见读写Excel的工具类开源javaAPI有两种方式，
一个是JXL（Java Excel API） 官网地址：http://jexcelapi.sourceforge.net/
一个是Apache的POI（Poor Obfuscation Implementation）官网地址：http://poi.apache.org/

POI支持微软的OLE2格式文件Office 2003及以下版本；同时支持微软的OOXML（Office Open XML）标准，也就是Office 2007以上版本。JXL只能实现对Excel 2003以下版本的支持。

POI使用HSSF对象操作OLE2格式Excel，文件后缀为.xls的；使用XSSF、SXSSF对象操作OOXML格式Excel，文件后缀为.xlsx的。

对于OLE2版本的Excel，一个Sheet工作表它的行最多支持到65536行，列支持到256列；
对于OOXML版本的Excel，一个Sheet工作表它的行支持到1048576行，列支持到16384列。

核心API：

数据限制：

Excel2003 2007、2010
列： 255 16384
行： 65535 1048576

=================== 基础 ===================

// 创建excel（工作簿） 使用接口的方式来创建
Workbook wb = new HSSFWorkbook();

新建工作簿：
HSSFWorkbook wb = new HSSFWorkbook();

打开工作簿：
HSSFWorkbook wb = new HSSFWorkbook(new FileInputStream(xlsFile));

建立新的sheet对象：
HSSFSheet sheet = wb.createSheet("我的第一个工作簿");

选择第一个工作簿：
HSSFSheet sheet = wb.getSheetAt(0);

设置工作簿的名称：
wb.setSheetName(0, "我的第一个工作簿");

创建行对象：
HSSFRow nRow = null;
nRow = sheet.createRow(1); //第2行

指定列 创建单元格对象：
HSSFCell nCell = null;
nCell = nRow.createCell((short)(2)); //第3列

指定列 创建单元格对象：
nCell.setCellValue("我是单元格");

// 获取到样式的对象
CellStyle style = wb.createCellStyle();

// 创建字体对象
Font font = wb.createFont();
// 设置字体大小
font.setFontHeightInPoints((short) 16);
// 设置字体的名称
font.setFontName("楷体");
// 设置字体
style.setFont(font);

设置样式 注意：样式不能重复设置
nCell.setCellStyle(leftStyle(wb));

文件下载方法1：

先在服务器产生临时文件，再下载临时文件。

关闭保存excel文件
FileOutputStream fOut = new FileOutputStream(xlsFile); //创建xls文件，无内容 0字节
wb.write(fOut); //写内容，xls文件已经可以打开
fOut.flush(); //刷新缓冲区
fOut.close(); //关闭

文件下载方法2：

//7.生成excel文件
ByteArrayOutputStream byteArrayOutputStream = new ByteArrayOutputStream(); //生成流对象
wb.write(byteArrayOutputStream); //将excel写入流

//工具类，封装弹出下载框：
String outFile = "生产厂家通讯录.xls";
DownloadBaseAction down = new DownloadBaseAction();
down.download(byteArrayOutputStream, response, outFile);

文件下载方法3：（适用于struts2）

ServletActionContext.getResponse().setContentType("application/octet-stream");
String returnName = ServletActionContext.getResponse().encodeURL( new String("购销合同.xls".getBytes(), "ISO-8859-1"));
ServletActionContext.getResponse().addHeader("Content-Disposition", "attachment;filename=" + returnName);

wb.write(ServletActionContext.getResponse().getOutputStream());

文件下载方法4：

//下载文件
response.setContentType("application/octet-stream");
String returnName = response.encodeURL( new String("生产厂家通讯录.xls".getBytes(), "ISO-8859-1"));
response.addHeader("Content-Disposition", "attachment;filename=" + returnName);

wb.write(response.getOutputStream());

字体修饰：

//设置单元格样式
private HSSFCellStyle leftStyle(HSSFWorkbook wb){
HSSFCellStyle curStyle = wb.createCellStyle();
HSSFFont curFont = wb.createFont(); //设置字体
//curFont.setFontName("Times New Roman"); //设置英文字体
curFont.setFontName("微软雅黑"); //设置英文字体
curFont.setCharSet(HSSFFont.DEFAULT_CHARSET); //设置中文字体，那必须还要再对单元格进行编码设置
curFont.setFontHeightInPoints((short)10); //字体大小
curFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); //加粗

curStyle.setFont(curFont);

curStyle.setBorderTop(HSSFCellStyle.BORDER_THICK); //粗实线
curStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN); //实线
curStyle.setBorderLeft(HSSFCellStyle.BORDER_MEDIUM); //比较粗实线
curStyle.setBorderRight(HSSFCellStyle.BORDER_THIN); //实线

curStyle.setWrapText(true); //换行
curStyle.setAlignment(HSSFCellStyle.ALIGN_RIGHT); //横向具右对齐
curStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); //单元格垂直居中

return curStyle;
}

=================== web环境 ===================

设置打印方向：默认纵向

PrintSetup ps = sheet.getPrintSetup();
ps.setLandscape(true); //横向打印

自适应列宽：
//bug 对中文支持不好，列宽不够宽
for(int i=0 ;i<titles.length;i++){
sheet.autoSizeColumn((short)i);
}

设置行高：
nRow.setHeightInPoints(18);

设置列宽：
sheet.setColumnWidth((short)colNo, (short)(256*8));

设置每列默认宽度：
sheet.setDefaultColumnWidth((short) 20);

设置标题：
将第一行作为标题，即每页都打印此行 sheetN,startCol,stopCol,startRow,stopRow

wb.setRepeatingRowsAndColumns(0,1,8,0,1);

页脚：
HSSFFooter footer = sheet.getFooter();
footer.setRight("第"+HSSFFooter.page()+"页 共"+HSSFFooter.numPages()+"页 "); //页数

工具类-单元格自适应高度：
float height = pioUtil.getCellAutoHeight(extcproducts, 12f);
nRow.setHeightInPoints(height); //(一行字+行之间的间隙)*行数

分页：
// POI分页符有BUG，必须在模板文件中插入一个分页符，然后再此处删除预设的分页符；最后在下面重新设置分页符。
// sheet.setAutobreaks(false);
// int iRowBreaks[] = sheet.getRowBreaks();
// sheet.removeRowBreak(3);
// sheet.removeRowBreak(4);
// sheet.removeRowBreak(5);
// sheet.removeRowBreak(6);

sheet.setRowBreak(行数); //在第startRow行设置分页符

==出货表：

合并单元格：
//纵向合并单元格
Region region = null;
region = new Region(curRow-1, (short)(1), curRow-1+3, (short)1);
sheet.addMergedRegion(region);

//横向合并单元格
CellRangeAddress
sheet.addMergedRegion(new CellRangeAddress(开始行，结束行，开始列，结束列));

// 横向居中
style.setAlignment(CellStyle.ALIGN_CENTER);
// 纵向居中
style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);

文件直接输出：

ByteArrayOutputStream byteArrayOutputStream = new ByteArrayOutputStream(); //生成流对象
wb.write(byteArrayOutputStream); //将excel写入流

HttpServletResponse response = ServletActionContext.getResponse();

//工具类，封装弹出下载框：
DownloadBaseAction down = new DownloadBaseAction();
down.download(byteArrayOutputStream, response, outFile);

获取模板：

int curRow = 0; //当前行
int colNo = 1; //当前列

//得到模板路径
String rootPath = UtilFuns.getROOTPath();
String xlsFile = rootPath + "/make/xlsprint/tOUTPRODUCT.xls";

//新建临时目录，存放excel /root/web/tmpfile/yyyy-mm-dd/...
String filePath = "/web/tmpfile/" + UtilFuns.sysDate()+"/";
File tmpDir = new File(rootPath + filePath);
if(!tmpDir.exists()){
tmpDir.mkdirs(); //创建多级目录
}

FileUtil fu = new FileUtil();
String sFile = fu.newFile(rootPath+filePath, "outproduct.xls"); //防止文件并发访问

String outFile = rootPath+filePath+sFile; //输出文件

==合同打印：

1、 分页

sheet.setRowBreak(当前行); //设置分页符

2、怎么插入一个图片
HSSFPatriarch patriarch = sheet.createDrawingPatriarch(); //add picture

pioUtil.setPicture(wb, patriarch, rootPath+"make/xlsprint/logo.jpg", curRow, 2, curRow+4, 2);

3、怎么插入一条线

pioUtil.setLine(wb, patriarch, curRow, 2, curRow, 8); //draw line

4、设置数值类型
nCell.setCellType(HSSFCell.CELL_TYPE_NUMERIC);

5、设置前导符

HSSFDataFormat format = wb.createDataFormat();
return format.getFormat("\"￥\"#,###,##0.00"); // 设置格式

6、设置公式

nCell.setCellType(HSSFCell.CELL_TYPE_FORMULA);
nCell.setCellFormula("F11*H11");
nCell.setCellFormula("F"+String.valueOf(curRow)+"*H"+String.valueOf(curRow));

nCell.setCellFormula("SUM(I"+String.valueOf(curRow-4)+":I"+String.valueOf(curRow-1)+")");

7、工具类：替换等量空格

fixSpaceStr(String str,int len)

8、业务要求：
1）同一个厂家的货物才能打印到同一个页面

List<ContractProduct> oList = oDao.find("from ContractProduct o where o.contract.id=‘"+contractId+"‘ order by o.factory.id,o.orderNo");

//厂家不同另起新页打印，除去第一次的比较
if(oProduct.getFactory().getFactoryName().equals(oldFactory)){
}

2）打印可以选择打印一款货物，还是两款货物
if(contract.getPrintStyle().equals("2")){
}

9、数据和业务分离

//填写每页的内容，之后在循环每页读取打印
Map<String,String> pageMap = null;
List<Map> pageList = new ArrayList(); //打印页

==报运打印：

wb.cloneSheet(0); //复制sheet0工作簿,名字会自动重命名

package cn.itcast.export.util;

import java.io.ByteArrayOutputStream;
import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.net.URLEncoder;

import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletResponse;

import org.apache.struts2.ServletActionContext;

import sun.misc.BASE64Encoder;

public class DownloadUtil {
    
    /**
     * @param filePath 要下载的文件路径
     * @param returnName 返回的文件名
     * @param response HttpServletResponse
     * @param delFlag 是否删除文件
     */
    protected void download(String filePath,String returnName,HttpServletResponse response,boolean delFlag){
        this.prototypeDownload(new File(filePath), returnName, response, delFlag);
    }


    /**
     * @param file 要下载的文件
     * @param returnName 返回的文件名
     * @param response HttpServletResponse
     * @param delFlag 是否删除文件
     */
    protected void download(File file,String returnName,HttpServletResponse response,boolean delFlag){
        this.prototypeDownload(file, returnName, response, delFlag);
    }
    
    /**
     * @param file 要下载的文件
     * @param returnName 返回的文件名
     * @param response HttpServletResponse
     * @param delFlag 是否删除文件
     */
    public void prototypeDownload(File file,String returnName,HttpServletResponse response,boolean delFlag){
        // 下载文件
        FileInputStream inputStream = null;
        ServletOutputStream outputStream = null;
        try {
            if(!file.exists()) return;
            response.reset();
            //设置响应类型    PDF文件为"application/pdf"，WORD文件为："application/msword"， EXCEL文件为："application/vnd.ms-excel"。  
            response.setContentType("application/octet-stream;charset=utf-8");

            //设置响应的文件名称,并转换成中文编码
            //returnName = URLEncoder.encode(returnName,"UTF-8");
            returnName = response.encodeURL(new String(returnName.getBytes(),"iso8859-1"));    //保存的文件名,必须和页面编码一致,否则乱码
            
            //attachment作为附件下载；inline客户端机器有安装匹配程序，则直接打开；注意改变配置，清除缓存，否则可能不能看到效果
            response.addHeader("Content-Disposition",   "attachment;filename="+returnName);  
            
            //将文件读入响应流
            inputStream = new FileInputStream(file);
            outputStream = response.getOutputStream();
            int length = 1024;
            int readLength=0;
            byte buf[] = new byte[1024];
            readLength = inputStream.read(buf, 0, length);
            while (readLength != -1) {
                outputStream.write(buf, 0, readLength);
                readLength = inputStream.read(buf, 0, length);
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                outputStream.flush();
            } catch (IOException e) {
                e.printStackTrace();
            }
            try {
                outputStream.close();
            } catch (IOException e) {
                e.printStackTrace();
            }
            try {
                inputStream.close();
            } catch (IOException e) {
                e.printStackTrace();
            }
            //删除原文件
            
            if(delFlag) {                
                file.delete();
            }
        }
    }

    /**
     * by tony 2013-10-17
     * @param byteArrayOutputStream 将文件内容写入ByteArrayOutputStream
     * @param response HttpServletResponse    写入response
     * @param returnName 返回的文件名
     */
    public void download(ByteArrayOutputStream byteArrayOutputStream, HttpServletResponse response, String returnName) throws IOException{
        response.setContentType("application/octet-stream;charset=utf-8");
        //returnName = response.encodeURL(new String(returnName.getBytes(),"iso8859-1"));            //保存的文件名,必须和页面编码一致,否则乱码
        String agent = ServletActionContext.getRequest().getHeader("user-agent");//客户端浏览器版本
        returnName = encodeDownloadFilename(returnName,agent );
        
        response.addHeader("Content-Disposition",   "attachment;filename=" + returnName);  
        response.setContentLength(byteArrayOutputStream.size());
        
        ServletOutputStream outputstream = response.getOutputStream();    //取得输出流
        byteArrayOutputStream.writeTo(outputstream);                    //写到输出流
        byteArrayOutputStream.close();                                    //关闭
        outputstream.flush();                                            //刷数据
    }
    
    /**
     * 下载文件时，针对不同浏览器，进行附件名的编码
     * @param filename 下载文件名
     * @param agent 客户端浏览器
     * @return 编码后的下载附件名
     * @throws IOException
     */
    public String encodeDownloadFilename(String filename, String agent) throws IOException{
        if(agent.contains("Firefox")){ // 火狐浏览器
            filename = "=?UTF-8?B?"+new BASE64Encoder().encode(filename.getBytes("utf-8"))+"?=";
        }else{ // IE及其他浏览器
            filename = URLEncoder.encode(filename,"utf-8");
        }
        return filename;
    }
}

poi下载