JAVA操作Excel的方法

 

此操作使用POI类库，请到以下URL下载：

 

http://apache.justdn.org/jakarta/poi/release/bin/poi-bin-<chsdate w:st="on" year="1899" month="12" day="30" islunardate="False" isrocdate="False"></chsdate>2.5.1-final-20040804.zip

 

以下是源码：

 

package com.test;

 

 

import java.io.FileOutputStream;

 

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

 

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

 

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

 

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

 

import java.io.FileInputStream;

 

import javax.swing.JOptionPane;

 

/**

 

 * 一个Excel文件的层次：Excel文件->工作表->行->单元格

 

 * 对应到POI中，为：workbook->sheet->row->cell

 

 */

 

public class POITest

 

{

 

    public static String outputFile = "e:\\test.xls";

 

public static String fileToBeRead = "e:\\test.xls";

 

 

    public void createExcel()

 

    {

 

        try

 

        {

 

            // 创建新的Excel 工作簿

 

            HSSFWorkbook workbook = new HSSFWorkbook();

 

            // 在Excel工作簿中建一工作表，其名为缺省值

 

            // 如要新建一名为"效益指标"的工作表，其语句为：

 

            // HSSFSheet sheet = workbook.createSheet("效益指标");

 

            HSSFSheet sheet = workbook.createSheet("效益指标");

 

            // 在索引0的位置创建行（最顶端的行）

 

            HSSFRow row = sheet.createRow((short)0);

 

            //在索引0的位置创建单元格（左上端）

 

            HSSFCell cell = row.createCell((short)0);

 

            // 定义单元格为字符串类型

 

            cell.setCellType(HSSFCell.CELL_TYPE_STRING);

 

            // 在单元格中输入一些内容

 

            cell.setCellValue("POI Excel Model");

 

            //下一行

 

            row=sheet.createRow( (short)1);

 

            cell=row.createCell( (short)0);

 

            //设置单元格内容

 

            cell.setCellValue( "Powered by 夏天");

 

            // 新建一输出文件流

 

            FileOutputStream fOut = new FileOutputStream(outputFile);

 

            // 把相应的Excel 工作簿存盘

 

            workbook.write(fOut);

 

            fOut.flush();

 

            // 操作结束，关闭文件

 

            fOut.close();

 

            System.out.println("文件生成...");

 

           

 

        }

 

        catch(Exception e)

 

        {

 

            System.out.println("已运行 xlCreate() : " + e);

 

        }

 

    }

 

 

    public void readExcel()

 

    {

 

        try

 

        {

 

            // 创建对Excel工作簿文件的引用

 

            HSSFWorkbook workbook = new HSSFWorkbook(new FileInputStream(fileToBeRead));

 

            //System.out

 

            //  .println("===SheetsNum===" + workbook.getNumberOfSheets());//获取sheet数

 

            for(int numSheets = 0; numSheets < workbook.getNumberOfSheets(); numSheets++)

 

            {

 

                if(null != workbook.getSheetAt(numSheets))

 

                {

 

                    HSSFSheet aSheet = workbook.getSheetAt(numSheets); //获得一个sheet

 

 

                    for(int rowNumOfSheet = 0; rowNumOfSheet <= aSheet.getLastRowNum(); rowNumOfSheet++)

 

                    {

 

                        if(null != aSheet.getRow(rowNumOfSheet))

 

                        {

 

                            HSSFRow aRow = aSheet.getRow(rowNumOfSheet);

 

 

                            for(short cellNumOfRow = 0; cellNumOfRow <= aRow.getLastCellNum(); cellNumOfRow++)

 

                            {

 

 

                                if(null != aRow.getCell(cellNumOfRow))

 

                                {

 

                                    HSSFCell aCell = aRow.getCell(cellNumOfRow);

 

 

                                    System.out.println(aCell.getStringCellValue());

 

 

                                }

 

 

                            }

 

                        }

 

 

                    }

 

 

                }

 

 

            }

 

 

        }

 

        catch(Exception e)

 

        {

 

            System.out.println("ReadExcelError" + e);

 

        }

 

 

    }

 

 

    public static void main(String[] args)

 

    {

 

        POITest poi = new POITest();

 

        poi.createExcel();

 

        JOptionPane.showMessageDialog(null,"文件写入成功","POI",1);

 

        poi.readExcel();

 

        JOptionPane.showMessageDialog(null,"文件读取成功","POI",1);

 

    }

 

}