Java操作Microsoft Office(Excel) Apache POI

Java操作Microsoft Office用到的专业的类库为Apache POI。Apache POI是Apache软件基金会的开放源码函式库，POI提供API给Java程序对Microsoft Office格式档案读和写的功能。此篇文章只介绍对Excel（.xls/.xlsx）的操作，包括：创建、查询、修改、删除、更新等。

1 Excel的创建

Excel的创建可以分为2007版本及其以上（.xlsx）和2007版本一下的（.xls），其完整的Excel创建代码如下：

import java.io.File;import java.io.FileNotFoundException;import java.io.FileOutputStream;import java.io.IOException;import java.util.List;import javax.swing.JOptionPane;import org.apache.poi.ss.usermodel.Sheet;import org.apache.poi.hssf.usermodel.HSSFWorkbook;import org.apache.poi.ss.usermodel.Cell;import org.apache.poi.ss.usermodel.Workbook;import org.apache.poi.xssf.usermodel.XSSFWorkbook;import org.apache.poi.ss.usermodel.Row;/** * 创建Excel的操作 * @author liguohui * */public class ExcelCreater {private String filePath = null; // Excel文件的路径（包含文件名）private List<String> definition = null;/** * 构造方法 * @param filePath 要创建的Excel文件的路径（包含文件名） * @param definition 该表格的定义。（即表格第一行的内容） */public ExcelCreater(String filePath, List<String> definition) {this.filePath = filePath;this.definition = definition;}/** * 创建 * @return */public Workbook create() {File file = new File(filePath);if(file.exists()) { //文件已经存在int op = JOptionPane.showConfirmDialog(null, "文件：" + filePath + " 已经存在，是否覆盖？", "提示信息", 0);if(op == 0) { //选择了：是file.delete(); //必须先删除，要不然下面就是对Excel表格进行append，而不是覆盖添加} else { //选择了：否或直接点Xreturn null;}}Workbook workbook = null;if(filePath.endsWith(".xlsx") || filePath.endsWith(".XLSX")) { //2007版本及以上workbook = new XSSFWorkbook();} else if(filePath.endsWith(".xls") || filePath.endsWith(".XLS")){ //97-03版本workbook = new HSSFWorkbook();} else {throw new RuntimeException("文件格式错误");}Sheet sheet = workbook.createSheet(); //创建一个Sheetif(definition != null && definition.size() > 0) {//添加表格的定义，即：写第一行Row row = sheet.createRow(0);Cell cell;for(int i=0; i<definition.size(); i++) {cell = row.createCell(i);cell.setCellValue(definition.get(i));}}writeToFile(workbook);return workbook;}/** * 写入到硬盘 */private synchronized void writeToFile(Workbook workbook) {FileOutputStream fOut = null;try {File f = new File(filePath);if(!f.getParentFile().exists()) {f.getParentFile().mkdirs();}fOut = new FileOutputStream(filePath);workbook.write(fOut); // 把相应的Excel 工作簿存盘fOut.flush();} catch (FileNotFoundException e) {e.printStackTrace();} catch (IOException e) {e.printStackTrace();} finally {try {fOut.close(); // 操作结束，关闭文件} catch (IOException e) {e.printStackTrace();}}}}

测试代码如下：

public void testCreateExcel() {List<String> defination = new ArrayList<String>();defination.add("id号");defination.add("名称");ExcelCreater ec = new ExcelCreater("F:\\dd.xlsx", defination);ec.create();}

运行结果如下：

2 Excel的操作（查询、修改、删除、更新）

2.1  插入

/** * 插入一行到指定的位置，该行下面的所有行向下移动一个 * @param rowIndex 带插入的行（Excel表中，行是从0起算的，但该值不能为0，因为：不能插入在第0行，因为第0行为用来定义字段的！） * @param objs 待插入的值 */public synchronized void insert(int rowIndex, String[] objs) {if(rowIndex == 0) {throw new IllegalArgumentException("不能插入在第0行，因为第0行为用来定义字段的！");}if(rowIndex > sheet.getLastRowNum() + 1) {throw new IllegalArgumentException("最多只能插入在最后一行的后面，不能再往后插入了");}int referRowIndex = -1; //参考行的行号。if(sheet.getPhysicalNumberOfRows() <= 1) { //原始的表格里面没有值referRowIndex = rowIndex - 1;} else {referRowIndex = rowIndex - 1;if(rowIndex == sheet.getLastRowNum() + 1) { //是插入最后一行//什么都不做} else {sheet.shiftRows(rowIndex, sheet.getLastRowNum(), 1, true, false); //下移动一位}}Row targetRow = sheet.createRow(rowIndex);Row referRow = sheet.getRow(referRowIndex); // 参考行Cell targetCell, referCell;for (int i = 0; i < objs.length; i++) {targetCell = targetRow.createCell(i);referCell = referRow.getCell(i);targetCell.setCellStyle(referCell.getCellStyle());targetCell.setCellType(referCell.getCellType());targetCell.setCellValue(objs[i]);// 设置值}writeToFile();}

2.2 更新

public synchronized void update(int rowIndex, String[] objs) {Row row = sheet.getRow(rowIndex);for(int i=0; i<objs.length; i++) {row.getCell(i).setCellValue(objs[i]);}writeToFile();}

2.3 删除

/** * 删除一行数据（Excel表中，行是从0起算的） * @param rowIndex 要删除的行 */public synchronized void delete(int rowIndex) {if(rowIndex == sheet.getLastRowNum()) { //删除的是最后一行sheet.removeRow(sheet.getRow(sheet.getLastRowNum()));} else { //删除的不是最后一行sheet.shiftRows(rowIndex + 1, sheet.getLastRowNum(), -1, true, false);sheet.removeRow(sheet.getRow(sheet.getLastRowNum() + 1));}writeToFile();}

2.4 读取

/** * 读取Excel中的【所有】二维表数据 *  * @throws IOException */public synchronized String[][] read() throws IOException {String[][] objs = new String[sheet.getPhysicalNumberOfRows()][];Row row = sheet.getRow(sheet.getFirstRowNum());int firstCellNum = row.getFirstCellNum();int lastCellNum = row.getLastCellNum();int r = 0, c = 0;Cell cell = null;for(int i=sheet.getFirstRowNum(); i<=sheet.getLastRowNum(); i++) {row = sheet.getRow(i);if(row == null) continue;objs[r] = new String[lastCellNum - firstCellNum];for(int j=firstCellNum; j<lastCellNum; j++) {cell = row.getCell(j);objs[r][c] = (cell == null ? null : cell.toString());c++;}r++;c = 0;}return objs;}

其完整的Excel代码如下：

import java.io.FileInputStream;import java.io.FileNotFoundException;import java.io.FileOutputStream;import java.io.IOException;import java.io.InputStream;import java.lang.reflect.Field;import java.lang.reflect.InvocationTargetException;import java.lang.reflect.Method;import java.util.LinkedHashMap;import org.apache.poi.openxml4j.exceptions.InvalidFormatException;import org.apache.poi.ss.usermodel.Cell;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;import org.apache.poi.xssf.usermodel.XSSFCell;import cn.edu.njnu.pm.dao.Blackboard;import cn.edu.njnu.pm.dao.PMP;/** * Excel的CRUD操作，但没有Excel文件的创建的操作，其创建操作专门放在ExcelCreater类里 *  * @author liguohui * @version 2013/03/14 */public class ExcelEditor {    private String filePath = null; // Excel文件的路径（包含文件名）//    private int sheetIndex = -1; // 要读取的sheet的序号    Workbook workbook = null;    Sheet sheet = null;    /**     * 构造函数。操作了该构造函数后，表格里面的所有项都会设置为String类型     *      * @param filePath     *            Excel文件的路径（包含文件名）     * @param sheetIndex     *            要读取的sheet的序号     */    public ExcelEditor(String filePath, int sheetIndex) {        this.filePath = filePath;//        this.sheetIndex = sheetIndex;        try {            InputStream inp = new FileInputStream(filePath);            workbook = WorkbookFactory.create(inp);            sheet = workbook.getSheetAt(sheetIndex);        } catch (InvalidFormatException e) {            e.printStackTrace();        } catch (IOException e) {            e.printStackTrace();        }                //将所有的都设为String类型，不管事数字形式的还是日期形式的        Row row = sheet.getRow(sheet.getFirstRowNum());        Cell cell = null;        for(int i=sheet.getFirstRowNum(); i<=sheet.getLastRowNum(); i++) {            row = sheet.getRow(i);            if(row == null) continue;            for(int j=row.getFirstCellNum(); j<row.getLastCellNum(); j++) {                cell = row.getCell(j);                if(cell != null)                     cell.setCellType(XSSFCell.CELL_TYPE_STRING);            }        }                writeToFile();    }    /**     * 获取每一列的宽度     *      * @return 每一列的宽度     */    public int[] getColumnWidths() {        int[] widths = new int[sheet.getRow(0).getPhysicalNumberOfCells()];        for (int i = 0; i < widths.length; i++) {            widths[i] = sheet.getColumnWidth(i);        }        return widths;    }        /**     * 在表格中添加一行数据到表格的最后     * @param objs     */    public synchronized void add(String[] objs) {        insert(sheet.getLastRowNum() + 1, objs);    }    /**     * 插入一行到指定的位置，该行下面的所有行向下移动一个     * @param rowIndex 带插入的行（Excel表中，行是从0起算的，但该值不能为0，因为：不能插入在第0行，因为第0行为用来定义字段的！）     * @param objs 待插入的值     */    public synchronized void insert(int rowIndex, String[] objs) {        if(rowIndex == 0) {            throw new IllegalArgumentException("不能插入在第0行，因为第0行为用来定义字段的！");        }        if(rowIndex > sheet.getLastRowNum() + 1) {            throw new IllegalArgumentException("最多只能插入在最后一行的后面，不能再往后插入了");        }        int referRowIndex = -1; //参考行的行号。        if(sheet.getPhysicalNumberOfRows() <= 1) { //原始的表格里面没有值            referRowIndex = rowIndex - 1;        } else {            referRowIndex = rowIndex - 1;            if(rowIndex == sheet.getLastRowNum() + 1) { //是插入最后一行                //什么都不做            } else {                sheet.shiftRows(rowIndex, sheet.getLastRowNum(), 1, true, false); //下移动一位            }        }        Row targetRow = sheet.createRow(rowIndex);        Row referRow = sheet.getRow(referRowIndex); // 参考行        Cell targetCell, referCell;        for (int i = 0; i < objs.length; i++) {            targetCell = targetRow.createCell(i);            referCell = referRow.getCell(i);            targetCell.setCellStyle(referCell.getCellStyle());            targetCell.setCellType(referCell.getCellType());            targetCell.setCellValue(objs[i]);// 设置值        }        writeToFile();    }    /**     * 更新指定的一行     * @param rowIndex 待更行的行（Excel表中，行是从0起算的）     * @param objs 更新的数据     */    public synchronized void update(int rowIndex, String[] objs) {        Row row = sheet.getRow(rowIndex);        for(int i=0; i<objs.length; i++) {            row.getCell(i).setCellValue(objs[i]);        }        writeToFile();    }        /**     * 删除一行数据（Excel表中，行是从0起算的）     * @param rowIndex 要删除的行     */    public synchronized void delete(int rowIndex) {        if(rowIndex == sheet.getLastRowNum()) { //删除的是最后一行            sheet.removeRow(sheet.getRow(sheet.getLastRowNum()));        } else { //删除的不是最后一行            sheet.shiftRows(rowIndex + 1, sheet.getLastRowNum(), -1, true, false);            sheet.removeRow(sheet.getRow(sheet.getLastRowNum() + 1));        }        writeToFile();    }        /**     * 更新列表宽度，自动调整的适合的宽度     */    public synchronized void autoSizeColumns() {        Row firstRow = sheet.getRow(sheet.getFirstRowNum());        for(int i=firstRow.getFirstCellNum(); i<firstRow.getLastCellNum(); i++) {            sheet.autoSizeColumn(i);        }                writeToFile();    }    /**     * 读取Excel中的【所有】二维表数据     *      * @throws IOException     */    public synchronized String[][] read() throws IOException {        String[][] objs = new String[sheet.getPhysicalNumberOfRows()][];                Row row = sheet.getRow(sheet.getFirstRowNum());        int firstCellNum = row.getFirstCellNum();        int lastCellNum = row.getLastCellNum();        int r = 0, c = 0;        Cell cell = null;        for(int i=sheet.getFirstRowNum(); i<=sheet.getLastRowNum(); i++) {            row = sheet.getRow(i);            if(row == null) continue;            objs[r] = new String[lastCellNum - firstCellNum];            for(int j=firstCellNum; j<lastCellNum; j++) {                cell = row.getCell(j);                objs[r][c] = (cell == null ? null : cell.toString());                c++;            }            r++;            c = 0;        }        return objs;    }        /**     * 更新完成后，写入到硬盘     */    private synchronized void writeToFile() {        FileOutputStream fOut = null;        try {            fOut = new FileOutputStream(filePath);            workbook.write(fOut); // 把相应的Excel 工作簿存盘            fOut.flush();        } catch (FileNotFoundException e) {            e.printStackTrace();        } catch (IOException e) {            e.printStackTrace();        } finally {            try {                fOut.close(); // 操作结束，关闭文件            } catch (IOException e) {                e.printStackTrace();            }        }    }        /**     * 根据传入的值找到其所在的列     * @param name     * @return 列的序号，从0起算     */    public int columnIndex(String name) {        Row row;        Cell cell;        for(int i=sheet.getFirstRowNum(); i<=sheet.getLastRowNum(); i++) {            row = sheet.getRow(i);            for(int j=row.getFirstCellNum(); j<row.getLastCellNum(); j++) {                cell = row.getCell(j);                if(cell.getStringCellValue().equals(name)) {                    return j;                }            }        }        return -1;    }}