最近用POI做了一段时间的报表和Excel导入导出,主要就是对HSSFWorkbook,HSSFSheet,HSSFRow, HSSFCell的操作了,掌握了对这四个东西的控制,你想怎么写就怎么写。
基本配置和使用说明:
其实JAVA在处理Window OLE 2复合文档格式操作的API,还有很多POI、JExcelApi、IText等,而在实际的个人感觉POI确实是一个不错的选择。下面就POI开发的相关内容进行简要的介绍下,包括POI组成、开发步骤、具体示例、知识点汇总等内容进行讲解。POI组成
POI组成
官方网站: http://jakarta.apache.org/poi/,这个是POI的官方网站,用户可以先从这里下载。最新的版本3.7,下载解压后里面有三个jar包
(poi-3.7-FINAL-20080204.jar/
poi-contrib-3.7-FINAL-20080204.jar/
poi-scratchpad-3.7-FINAL-20080204.jar),
而在这里包中主要含:
org.apache.poi.hssf.eventmodel 处理在读取和写入Excel文档中生成的各种动作.
org.apache.poi.hssf.eventusermodel 提供读取Excel文档的各个类.
org.apache.poi.hssf.record.formula 包含公式处理类, 这些类使用在Excel文档中.
org.apache.poi.hssf.usermodel 包含生成Excel文档的各个类.
org.apache.poi.hssf.util 提供处理Excel文档各个属性的工具类.
开发步骤
1:先下载POI包;
2:解压后将里面的3个jar文件拷贝至WEB-INF\lib中。
3:刷新工程后,即可以使用jar包中的内容了。
读取POI文档
一:以文件名创建一个InputStream
二:以上面的InputStream实例创建一个HSSFWorkbook.
三:HSSFWorkbook的getSheetAt(index)可获取相应的工作页
四:工作页调用方法获取行
五:行再获取相应的格,调用单元格的方法,然后取出对应数据。
简单配置简介完毕!
使用Demo 案例一:
// 使用Java POI
// 把要两个JAR文件放到lib/ext下
// code run against the jakarta-poi-1.5.0-FINAL-20020506.jar.
// and commons-logging-1.0.jar
例子程序:
import org.apache.poi.hssf.usermodel.*;
import java.io.FileOutputStream;
// code run against the jakarta-poi-1.5.0-FINAL-20020506.jar.
// and commons-logging-1.0.jar
public class PoiTest {
static public void main(String[] args) throws Exception {
FileOutputStream fos = new FileOutputStream("d:\foo.xls");
HSSFWorkbook wb = new HSSFWorkbook();
HSSFSheet s = wb.createSheet();
wb.setSheetName(0, "Matrix");
for(short i=0; i<50; i++) {
HSSFRow row = s.createRow(i);
for(short j=0; j<50; j++) {
HSSFCell cell = row.createCell(j);
cell.setCellValue(""+i+","+j);
}
}
wb.write(fos);
fos.close();
}
}
使用Demo 案例二(复杂):
1,首先写一个abstract class用来overwrite HSSF。
package com.eagle.excel;
import java.util.ArrayList;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
/**
* @author eyas
* @version 1.0
* @date Apr 28, 2007 10:41:26 PM
*/
public abstract class ExportInfoToExcel
{
/**
* create report header
*/
protected abstract void createHead();
/**
*create report body
*columns:table header
*itemList:row data
*/
protected abstract void createBody(String[] columns, ArrayList itemlist);
/**
*create report footer, ex:someone total information.
*/
protected abstract void createFoot();
/**
* write data to a Excel cell.this method is overloadabled.
*/
public void createCell(HSSFCellStyle cellStyle, HSSFCell cell, String value)
{
cell.setEncoding(HSSFCell.ENCODING_UTF_16);
cell.setCellStyle(cellStyle);
cell.setCellValue(value);
}
}
2,再写一个interface 用来给Excel 传递数据,并生成Excel file
package com.eagle.excel;
import java.io.File;
import java.util.ArrayList;
import java.util.Calendar;
/**
* @author eyas
* @version 1.0
* @date Apr 28, 2007 10:41:26 PM
*/
public interface ExportInfoInterface
{
public void setDate(Calendar startDate); // report print date
public void setColumns(String[] columns); // report columns
public void setDataList(ArrayList itemList); //data
public void produceExportFile(File exportfile) throws Exception;//create excel file
public void setOtherArg(Object arg1);// other arguments
}
3,然后就可以写一个类,用来接收数据,extends ,implements 上面的abstract class and interface.
package com.eagle.excel;
import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.util.ArrayList;
import java.util.Calendar;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.Region;
/**
* @author eyas
* @version 1.0
* @date Apr 28, 2007 10:43:26 PM
*/
public class CreateExcelFile extends ExportInfoToExcel implements ExportInfoInterface
{
private HSSFWorkbook workbook;
private HSSFSheet sheet;
private ArrayList dataList;
private Calendar date;
private String[] columns;
private int lineX = 0;
public CreateExcelFile ()
{
//initialized someone arguments
}
@Override
protected void createBody()
{
HSSFRow bodyTile = sheet.createRow(lineX++);
for (int i = 0; i < columns.length; i++)
{
createCell(bodyTile.createCell((short) i), columns[i]);
}
HSSFRow bodyRow;
for (int i = 0; i < dataList.size(); i++)
{
bodyRow = sheet.createRow(lineX++);
createCell(null,bodyRow.createCell((short) 0),(i+1));
createCell(null,bodyRow.createCell((short) 1),"eyas");
createCell(null,bodyRow.createCell((short) 2),"software");
...
}
}
@Override
protected void createFoot()
{
HSSFRow totalRow = sheet.createRow(lineX++);
createCell(null,bodyRow.createCell((short) 0),"total data");
}
@Override
protected void createHead()
{
lineX = 0;
sheet.addMergedRegion(new Region(0, (short) 0, 0, (short) 12));
HSSFRow headRow0 = sheet.createRow(lineX++);
createCell(null,headRow0.createCell((short) 0), "My Excel Report");
}
public void produceExportFile(File exportfile) throws Exception
{
try
{
workbook = new HSSFWorkbook();
sheet = workbook.createSheet("First Page");
createHead();
createBody();
FileOutputStream fw = new FileOutputStream(exportfile);
workbook.write(fw);
fw.flush();
fw.close();
workbook = null;
sheet = null;
fw = null;
System.out.println("Export success!");
} catch (IOException ioe)
{
ioe.printStackTrace();
}catch (Exception e)
{
e.printStackTrace();
}
}
public void setColumns(String[] columns)
{
this.columns = columns;
}
public void setDataList(ArrayList dataList)
{
this.dataList = dataList;
}
public void setOtherArg(Object arg1)
{
}
public void setDate(Calendar date)
{
this.date = date;
}
}
仅对产生Excel报表,使用POI几个步骤的总结.欢迎探讨