poi 操作excel2007总结

这是两个例程,是演示如何使用Java读取和写入Excel 2007 文件。注释里有比较详细的开发环境说明,你只要在Eclipse里粘过去不可以运行了。
例程使用的是POI-XSSF,而不是HSSF,这个地方要注意一下,不过他们的用法很类似,如果你看明白一个了,应该很容易理解另一个
非常感谢大家能阅读我的文章。如果还有什么问题,请大家留言
http://www.gjrencai.com/newsShow.asp?dataID=1485

所用到的包可从这里下载 : http://down.51cto.com/data/428255

 最近使用POI中的XSSFWorkbook操作excel2007(xlsx)的时候抛出以下异常: 
严重: Servlet.service() for servlet StaffIpRestrictServlet threw exception 
java.lang.ClassNotFoundException: org.openxmlformats.schemas.spreadsheetml.x2006.main.CTSheet 
at org.apache.catalina.loader.WebappClassLoader.loadClass(WebappClassLoader.java:1680) 

解决方案: 
poi包中默认不支持excel2007,如果需要解析,则需要引入poi-ooxml-schemas-xx.jar包。 

引入此包后,可能还会有个异常,原因是找不到dom4j的jar包。 
因为poi-ooxml-schemas-xx.jar需要依赖dom4j-xxx.jar包

/******************************************************************************
 * 演示使用POI 写入 Excel 2007
 *
 * 关键字:Java Excel POI POI-HSSF POI-XSSF
 *
gjrencai.com
 * 注释:http://www.gjrencai.com/newsShow.asp?dataID=1485
 * 开发环境详细说明:
 * 1、java version "1.6.0_14"
 * 2、Java(TM) SE Runtime Environment (build 1.6.0_14-b08)
 * 3、Java HotSpot(TM) Client VM (build 14.0-b16, mixed mode, sharing)
 * 4、Microsoft Excel 2007
 * 5、Windows XP Home Edition Service Pack 3
 * 6、Apache POI 3.5
 *
 *****************************************************************************/
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;

import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CreationHelper;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

public class POIExcelWriteDemo1
{

    /**
     * @param args
     */
    public static void main(String[] args)
    {
        // TODO Auto-generated method stub
        try
        {
            Workbook wb = new XSSFWorkbook();
            CreationHelper createHelper = wb.getCreationHelper();
            Sheet sheet = wb.createSheet("这里是第一页");

            // 创建行
            Row row = sheet.createRow((short) 0);
            // 创建单元格,方法1
            Cell cell = row.createCell(0);
            cell.setCellValue(1);
            // 直接创建单元格,方法2
            row.createCell(1).setCellValue(1.2);
            row.createCell(2)
                    .setCellValue(createHelper.createRichTextString("大家好,我是高宏伟"));
            row.createCell(3).setCellValue(
                    createHelper.createRichTextString("QQ:21807822"));
            row.createCell(4).setCellValue(true);

            // 写入文件
            FileOutputStream fileOut;
            fileOut = new FileOutputStream("gaohw.xlsx");
            wb.write(fileOut);
            fileOut.close();
            System.out.println("写入成功,运行结束!");
        } catch (FileNotFoundException e)
        {
            e.printStackTrace();
        } catch (IOException e)
        {
            e.printStackTrace();
        }
    }
}
 

以下是读取Excel文件的例程

/******************************************************************************
 * 演示使用POI 读取 Excel 2007
 *
 * 关键字:Java Excel POI POI-HSSF POI-XSSF
 * 注释:http://www.gjrencai.com/newsShow.asp?dataID=1485 *
 * 开发环境详细说明:
 * 1、java version "1.6.0_14"
 * 2、Java(TM) SE Runtime Environment (build 1.6.0_14-b08)
 * 3、Java HotSpot(TM) Client VM (build 14.0-b16, mixed mode, sharing)
 * 4、Microsoft Excel 2007
 * 5、Windows XP Home Edition Service Pack 3
 * 6、Apache POI 3.5
 *
 *****************************************************************************/
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.InputStream;
import java.util.Iterator;

import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.DateUtil;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.WorkbookFactory;

public class POIExcelReadDemo1
{

    /**
     * @param args
     */
    public static void main(String[] args)
    {
        try
        {
        InputStream inp;
            inp = new FileInputStream("gaohw.xlsx");
            Workbook wb = WorkbookFactory.create(inp);
            Sheet sheet = wb.getSheetAt(0);
            for (Iterator rit = sheet.rowIterator(); rit.hasNext();)
            {
                // 迭代行
                Row row = (Row) rit.next();
                // 迭代单元格
                for (Iterator cit = row.cellIterator(); cit.hasNext();)
                {
                    Cell cell = (Cell) cit.next();
                    // 开始操作单元格
                    // 在每一行的输出都打印如 "5:6 例子字符串",5:6代表第5行,第6列
                    // 注意行和列是基于0索引的
                    System.out.print(cell.getRowIndex() + ":" + cell.getColumnIndex()
                            + " ");
                    // 打印单元格内的数据
                    switch (cell.getCellType())
                    {
                    case Cell.CELL_TYPE_STRING:
                        System.out.println(cell.getRichStringCellValue().getString());
                        break;
                    case Cell.CELL_TYPE_NUMERIC:
                        if (DateUtil.isCellDateFormatted(cell))
                        {
                            System.out.println(cell.getDateCellValue());
                        } else
                        {
                            System.out.println(cell.getNumericCellValue());
                        }
                        break;
                    case Cell.CELL_TYPE_BOOLEAN:
                        System.out.println(cell.getBooleanCellValue());
                        break;
                    case Cell.CELL_TYPE_FORMULA:
                        System.out.println(cell.getCellFormula());
                        break;
                    default:
                        System.out.println();
                    }
                }
            }
        } catch (FileNotFoundException e)
        {
            e.printStackTrace();
        } catch (InvalidFormatException e)
        {
            e.printStackTrace();
        } catch (IOException e)
        {
            e.printStackTrace();
        }
    }
}
 转载注明:http://www.gjrencai.com/newsShow.asp?dataID=1485

发布了430 篇原创文章 · 获赞 415 · 访问量 925万+
展开阅读全文

没有更多推荐了,返回首页

©️2019 CSDN 皮肤主题: 编程工作室 设计师: CSDN官方博客

分享到微信朋友圈

×

扫一扫,手机浏览