[置顶] (1) 如何用Apache POI操作Excel文件-----入门

Apache POI项目的目标就是可以用Java API来创建和维护各种Office文件（MS Word，MS PowerPoint和MS Excel). 本系列文章主要是针对Excel文件的操作。对于Excel的操作，Apache POI提供两种模型：HSSF和XSSF。 其区别，请参考下面的图1。截止到2015年5月28日，当前的最新版本是3.12. 具体信息，我们可以访问其官方网站：http://poi.apache.org/

1.下面是用Apache POI创建一个工作薄的基本的代码

import org.apache.poi.hssf.usermodel.HSSFWorkbook;

import java.io.FileOutputStream;
import java.io.IOException;

public class NewWorkbookTest
{
    public static void main(String[] args)
        throws IOException
    {
        HSSFWorkbook wb = new HSSFWorkbook();
        FileOutputStream fileOut = new FileOutputStream("helloworld.xls");
        wb.write(fileOut);
        fileOut.close();
    }
}

2.下面是用Apache POI创建一个Excel单元表单的代码

import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.ss.util.WorkbookUtil;
import java.io.IOException;
import java.io.FileOutputStream;


public class NewSheet {
    public static void main(String[] args) throws IOException {
        HSSFWorkbook wb = new HSSFWorkbook();
        HSSFSheet sheet1 = wb.createSheet("Sheet1");
        HSSFSheet sheet2 = wb.createSheet(); // create with default name
        final String name = "Sheet2";
        wb.setSheetName(1, WorkbookUtil.createSafeSheetName(name)); // setting sheet name later
        FileOutputStream fileOut = new FileOutputStream("workbooktest.xls");
        wb.write(fileOut);
        fileOut.close();
    }
}

3. 对于Excel 2007 我们需要用XSSFWorkbook对象去创建，而不是HSSFWorkbook，否则会抛出下面的异常。

org.apache.poi.poifs.filesystem.OfficeXmlFileException: The supplied data appears to be in the Office 2007+ XML. You are calling the part of POI that deals with OLE2 Office Documents. You need to call a different part of POI to process this data (eg XSSF instead of HSSF)
at org.apache.poi.poifs.storage.HeaderBlock.<init>(HeaderBlock.java:152)
at org.apache.poi.poifs.storage.HeaderBlock.<init>(HeaderBlock.java:140)
at org.apache.poi.poifs.filesystem.NPOIFSFileSystem.<init>(NPOIFSFileSystem.java:302)
at org.apache.poi.hssf.usermodel.HSSFWorkbook.<init>(HSSFWorkbook.java:398)
at org.apache.poi.hssf.usermodel.HSSFWorkbook.<init>(HSSFWorkbook.java:379)

import java.io.FileInputStream;
import java.io.IOException;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
public class ExcelTest{

  public XSSFWorkbook readSummaryExcel(String filename) throws IOException{
    FileInputStream fis=null;
    try {
         fis = new FileInputStream(filename);
        return new XSSFWorkbook(fis);
    } finally {
        if(fis!=null){
           fis.close();
        }
    }
  }

}