java使用JexcelApi和POI两种方法操作excel

在java里面如何操作excel，其中有两种方法：JexcelApi和POI两种方法，参考网上别人做的，发现对于简单的操作，两种效果都不错。在其中JexcelApi操作时,对于在已有表格中添加数据，没能成功实现。代码有些复制的。

先看POI,直接上代码：

import java.io.FileInputStream;
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 org.apache.poi.poifs.filesystem.POIFSFileSystem;  

public class HelloWord {
    public static String outputFile = "D:\\test1.xls";
    public static String fileToBeRead = "D:\\test1.xls";  

    public static void main(String args[]) {

        //新建excel
        try {

            // 创建新的Excel 工作簿
            HSSFWorkbook workbook = new HSSFWorkbook();

            // 在Excel工作簿中建一工作表，其名为缺省值
            // 如要新建一名为"效益指标"的工作表，其语句为：
            // HSSFSheet sheet = workbook.createSheet("效益指标");
            HSSFSheet sheet = workbook.createSheet("效益指标");
            // 在索引0的位置创建行（最顶端的行）
            HSSFRow row = sheet.createRow((short) 0);

            HSSFCell empCodeCell = row.createCell((short) 0);
            empCodeCell.setCellType(HSSFCell.CELL_TYPE_STRING);
            empCodeCell.setCellValue("员工代码");

            HSSFCell empNameCell = row.createCell((short) 1);
            empNameCell.setCellType(HSSFCell.CELL_TYPE_STRING);
            empNameCell.setCellValue("姓名");

            HSSFCell sexCell = row.createCell((short) 2);
            sexCell.setCellType(HSSFCell.CELL_TYPE_STRING);
            sexCell.setCellValue("性别");

            HSSFCell birthdayCell = row.createCell((short) 3);
            birthdayCell.setCellType(HSSFCell.CELL_TYPE_STRING);
            birthdayCell.setCellValue("出生日期");

            HSSFCell orgCodeCell = row.createCell((short) 4);
            orgCodeCell.setCellType(HSSFCell.CELL_TYPE_STRING);
            orgCodeCell.setCellValue("机构代码");

            HSSFCell orgNameCell = row.createCell((short) 5);
            orgNameCell.setCellType(HSSFCell.CELL_TYPE_STRING);
            orgNameCell.setCellValue("机构名称");

            HSSFCell contactTelCell = row.createCell((short) 6);
            contactTelCell.setCellType(HSSFCell.CELL_TYPE_STRING);
            contactTelCell.setCellValue("联系电话");

            HSSFCell zjmCell = row.createCell((short) 7);
            zjmCell.setCellType(HSSFCell.CELL_TYPE_STRING);
            zjmCell.setCellValue("助记码");
            for (int i = 1; i <= 10; i++) {
                row = sheet.createRow((short) i);
                empCodeCell = row.createCell((short) 0);
                empCodeCell.setCellType(HSSFCell.CELL_TYPE_STRING);
                empCodeCell.setCellValue("001_" + i);

                empNameCell = row.createCell((short) 1);
                empNameCell.setCellType(HSSFCell.CELL_TYPE_STRING);
                empNameCell.setCellValue("张三_" + i);

                sexCell = row.createCell((short) 2);
                sexCell.setCellType(HSSFCell.CELL_TYPE_STRING);
                sexCell.setCellValue("性别_" + i);

                birthdayCell = row.createCell((short) 3);
                birthdayCell.setCellType(HSSFCell.CELL_TYPE_STRING);
                birthdayCell.setCellValue("出生日期_" + i);

                orgCodeCell = row.createCell((short) 4);
                orgCodeCell.setCellType(HSSFCell.CELL_TYPE_STRING);
                orgCodeCell.setCellValue("机构代码_" + i);

                orgNameCell = row.createCell((short) 5);
                orgNameCell.setCellType(HSSFCell.CELL_TYPE_STRING);
                orgNameCell.setCellValue("机构名称_" + i);

                contactTelCell = row.createCell((short) 6);
                contactTelCell.setCellType(HSSFCell.CELL_TYPE_STRING);
                contactTelCell.setCellValue("联系电话_" + i);

                zjmCell = row.createCell((short) 7);
                zjmCell.setCellType(HSSFCell.CELL_TYPE_STRING);
                zjmCell.setCellValue("助记码_" + i);

            }
            // 新建一输出文件流
            FileOutputStream fOut = new FileOutputStream(outputFile);
            // 把相应的Excel 工作簿存盘
            workbook.write(fOut);
            fOut.flush();
            // 操作结束，关闭文件
            fOut.close();
            workbook.close();
            System.out.println("文件生成...");

        } catch (Exception e) {
            System.out.println("已运行 xlCreate() : " + e);
        }

        //更改数据
        try {
            FileInputStream fs = new FileInputStream("d://test1.xls"); // 获取d://test.xls
            POIFSFileSystem ps = new POIFSFileSystem(fs); // 使用POI提供的方法得到excel的信息
            HSSFWorkbook wb = new HSSFWorkbook(ps);
            HSSFSheet sheet = wb.getSheetAt(0); // 获取到工作表，因为一个excel可能有多个工作表
            HSSFRow row = sheet.getRow(0); // 获取第一行（excel中的行默认从0开始，所以这就是为什么，一个excel必须有字段列头），即，字段列头，便于赋值
            System.out.println(sheet.getLastRowNum() + " " + row.getLastCellNum()); // 分别得到最后一行的行号，和一条记录的最后一个单元格

            FileOutputStream out = new FileOutputStream("d://test1.xls"); // 向d://test.xls中写数据
            row = sheet.createRow((short) (sheet.getLastRowNum() + 1)); // 在现有行号后追加数据
            row.createCell(0).setCellValue("leilei"); // 设置第一个（从0开始）单元格的数据
            row.createCell(1).setCellValue(24); // 设置第二个（从0开始）单元格的数据

            out.flush();
            wb.write(out);
            out.close();
            // System.out.println(row.getPhysicalNumberOfCells() + "
            // " + row.getLastCellNum());
        } catch (Exception e) {
            System.out.println("已运行xlRead() : " + e);
        }


        //读取数据
        try {
            FileOutputStream fOut = new FileOutputStream("d:\\test2.xls");
            // 创建对Excel工作簿文件的引用
            HSSFWorkbook workbook = new HSSFWorkbook(new FileInputStream("d:\\test2.xls"));
            HSSFSheet sheet = workbook.getSheetAt(0);
            int i = 0;
            while (true) {
                HSSFRow row = sheet.getRow(i);
                if (row == null) {
                    break;
                }
                HSSFCell cell0 = row.getCell((short) 0);
                HSSFCell cell1 = row.getCell((short) 1);
                HSSFCell cell2 = row.getCell((short) 2);
                HSSFCell cell3 = row.getCell((short) 3);
                HSSFCell cell4 = row.getCell((short) 4);
                HSSFCell cell5 = row.getCell((short) 5);
                HSSFCell cell6 = row.getCell((short) 6);

                System.out.print(cell0.getStringCellValue());
                System.out.print("," + cell1.getStringCellValue());
                System.out.print("," + cell2.getStringCellValue());
                System.out.print("," + cell3.getStringCellValue());
                System.out.print("," + cell4.getStringCellValue());
                System.out.print("," + cell5.getStringCellValue());
                System.out.println("," + cell6.getStringCellValue());
                i++;
                row = sheet.createRow((short) i);
                HSSFCell empCodeCell = row.createCell((short) 0);
                empCodeCell = row.createCell((short) 0);
                empCodeCell.setCellType(HSSFCell.CELL_TYPE_STRING);
                empCodeCell.setCellValue("001_" + i + 9);
                workbook.write(fOut);
                fOut.flush();
                // 操作结束，关闭文件
                fOut.close();

            }
        } catch (Exception e) {
            System.out.println("已运行xlRead() : " + e);
        }
        System.exit(0);

    }
}

其中会有常见的错误，已运行xlRead() : org.apache.poi.poifs.filesystem.NotOLE2FileException: Invalid header signature; read 0x0000000000000000, expected 0xE11AB1A1E011CFD0 - Your file appears not to be a valid OLE2 document 这个一般有两种原因，有excel表格的首字段没设置，导致替换时出现错误，还有中原因是excel版本问题，这个只支持.xls格式的。

接下来，看JexcelApi操作，自己参考别人代码，构造一个类

/*
 * *   参考文档：http://lz881228.blog.163.com/blog/static/114197324201341755951817/
 */
package finance;

//生成Excel的类
import java.io.File;
import java.io.IOException;

import jxl.Workbook;
import jxl.Cell;
import jxl.JXLException;
import jxl.Sheet;
import jxl.write.Label;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;
import jxl.write.WriteException;

public class Excel
{
    private Workbook    rworkbook;
    private Sheet       rsheet;
    private WritableWorkbook wbook;
    private WritableSheet   wsheet;
    public String       FileName;
    Excel(String name){
        this.FileName   = name;
    }
    //创建excel文件或者
    public void CreateExcel(String Excelname,String Sheetname) 
    {
        try {
            File file = new File(Excelname);

            // 只获得文件名，无路径
            String fileName = file.getName();

            // 获取文件名和路径
            String fileFront = Excelname.substring(0, Excelname.lastIndexOf("."));

            // 获取文件后缀
            fileName = fileName.substring(fileName.lastIndexOf(".") + 1);

            if (!fileName.equals("xls")) {
                System.out.println("后缀名错误，一定要xls，不是" + fileName);
                return;
            }

            if (!file.exists()) {
                // 创建文件
                wbook = Workbook.createWorkbook(new File(Excelname));
            } else {
                rworkbook = Workbook.getWorkbook(new File(Excelname));
                wbook     = Workbook.createWorkbook(new File(fileFront + "_modified.xls"),rworkbook);
            }

            // 0表示第一个sheet,需首先创建sheet名字
            wsheet = wbook.createSheet(Sheetname, 0);
            // this.rsheet = rworkbook.getSheet(0);
        } catch (Exception e) {
            System.out.println(e);
        }
    }

    //修改当前sheet名字
    public void SetSheetName(String Name)
    {
        wsheet.setName(Name);
    }

    public void CreateNewSheet(String SheetName, int index) {
        try {
            wbook.createSheet(SheetName, index);
        } catch (Exception e) {
            System.out.println(e);
        }
    }

    // 关闭excel
    public void CloseExcel() {
        try {
            // write函数必须要调用
            wbook.write();
            wbook.close();
            rworkbook.close();
        } catch (Exception e) {
            System.out.println(e);
        }
    }

    // 在任意一行添加数据
    void AddCell(int row, int column, String content) {
        try {
            Label label = new Label(row, column, content);
            wsheet.addCell(label);
        } catch (Exception e) {
            System.out.println(e);
        }
    }

    // 在新的一行添加数据
    void AddDataToColumnLast(int row, String Data) {
        try {
            int column = wsheet.getRows();
            wsheet.addCell(new Label(row, column, Data));
        } catch (Exception e) {
            System.out.println(e);
        }
    }

    // 在新的一列添加数据
    void AddDataToRowLast(int column, String Data) {
        try {
            int row = wsheet.getColumns();
            wsheet.addCell(new Label(row, column, Data));
        } catch (Exception e) {
            System.out.println(e);
        }
    }

    void CorrectProperty() {
        try {
            // wbook.removeSheet(2); // 移除多余的标签页
            // wbook.removeSheet(3);
            //
            // wsheet.mergeCells(0, 0, 4, 0); // 合并单元格
            wsheet.setRowView(1, 600); // 设置行的高度
            wsheet.setColumnView(0, 30); // 设置列的宽度
            wsheet.setColumnView(1, 100); // 设置列的宽度
        } catch (Exception e) {
            System.out.println(e);
        }
    }

    void ReadExcel() {
        try {
            wbook.close();
            rworkbook.close();
            // 选取指定的excel
            rworkbook = Workbook.getWorkbook(new File(this.FileName));
            // 选取制定的sheet
            Sheet sheet = rworkbook.getSheet(0);
            // 选取指定的cell
            // 遍历循环得到所要的cell值
            for (int j = 0; j < sheet.getRows(); j++)
                for (int i = 0; i < sheet.getColumns(); i++) {
                    Cell cell = sheet.getCell(i, j);
                    // 获取该cell的值
                    String var1 = cell.getContents();
                    // 打印输出该值
                    System.out.println(var1);
                }
            rworkbook.close();
        } catch (Exception e) {
            System.out.println(e);
        }
    }
}

但对于这两行代码：

rworkbook = Workbook.getWorkbook(new File(Excelname));
wbook     = Workbook.createWorkbook(new File(fileFront + "_modified.xls"),rworkbook);

许多人说，后面添加一个参数，就能够覆盖已有的excel表格，但我在做的时候，没能实现。不是是不是新版本的问题

调用方式：

        Excel excel     = new Excel("D://b.xls");       
        try {
            excel.CreateExcel("D://b.xls", "Data");
        } catch (Exception e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
        excel.AddCell(0, 0, "good");
        excel.AddDataToColumnLast(0,"fwe");
        excel.AddDataToColumnLast(0,"I love you");
        excel.AddDataToRowLast(0,"fhrthrhtr");
    //  excel.ReadExcel();
    //  excel.CorrectProperty();
        excel.CloseExcel();

本来，想用POI也构造一个类，后来发现自己构造的还没有原本的简单，就没继续写下去，编程要敢于不断尝试，不尝试是永远不会的。