Java操作Excel文件解析与读写方法详解

Excel文件是一种广泛应用于商业、金融等领域的数据处理工具。在Java开发中，操作Excel文件常常需要通过对Excel文件进行解析读取和写入操作。

Excel文件格式

Excel文件通常有两种常见的格式：xls和xlsx。其中，xls是Excel97-2003版本的二进制文件，而xlsx则是Excel 2007及以上版本所采用的基于XML的文件格式。在Java开发中，操作Excel文件需要根据实际需要选择相应的操作库进行开发。

jar包选择

• 对于xls格式文件，可以使用Apache POI操作库进行处理。
• 对于xlsx格式文件，可以使用Java Excel API（jxl）操作库，或者Apache POI中的XSSFWorkbook类进行处理。

解析Excel文件

解析xls格式文件

• 程序示例：

try {
    File file = new File("sample.xls");
    FileInputStream inputStream = new FileInputStream(file);
    POIFSFileSystem fs = new POIFSFileSystem(inputStream);
    HSSFWorkbook workbook = new HSSFWorkbook(fs);
    HSSFSheet sheet = workbook.getSheetAt(0);
    Iterator<Row> rowIterator = sheet.iterator();
    while (rowIterator.hasNext()) {
        Row row = rowIterator.next();
        Iterator<Cell> cellIterator = row.cellIterator();
        while (cellIterator.hasNext()) {
            Cell cell = cellIterator.next();
            switch (cell.getCellType()) {
                case Cell.CELL_TYPE_BOOLEAN:
                    System.out.print(cell.getBooleanCellValue() + "\t\t");
                    break;
                case Cell.CELL_TYPE_NUMERIC:
                    System.out.print(cell.getNumericCellValue() + "\t\t");
                    break;
                case Cell.CELL_TYPE_STRING:
                    System.out.print(cell.getStringCellValue() + "\t\t");
                    break;
            }
        }
        System.out.println("");
    }
    workbook.close();
    inputStream.close();
} catch (Exception ex) {
    ex.printStackTrace();
}

解析xlsx格式文件

• 程序示例：

try {
    File file = new File("sample.xlsx");
    FileInputStream inputStream = new FileInputStream(file);
    XSSFWorkbook workbook = new XSSFWorkbook(inputStream);
    XSSFSheet sheet = workbook.getSheetAt(0);
    Iterator<Row> rowIterator = sheet.iterator();
    while (rowIterator.hasNext()) {
        Row row = rowIterator.next();
        Iterator<Cell> cellIterator = row.cellIterator();
        while (cellIterator.hasNext()) {
            Cell cell = cellIterator.next();
            switch (cell.getCellTypeEnum()) {
                case BOOLEAN:
                    System.out.print(cell.getBooleanCellValue() + "\t\t");
                    break;
                case NUMERIC:
                    System.out.print(cell.getNumericCellValue() + "\t\t");
                    break;
                case STRING:
                    System.out.print(cell.getStringCellValue() + "\t\t");
                    break;
            }
        }
        System.out.println("");
    }
    workbook.close();
    inputStream.close();
} catch (Exception ex) {
    ex.printStackTrace();
}

写入Excel文件

写入xls格式文件

• 程序示例：

try {
    HSSFWorkbook workbook = new HSSFWorkbook();
    HSSFSheet sheet = workbook.createSheet("Sample sheet");
    Map<String, Object[]> data = new TreeMap<String, Object[]>();
    data.put("1", new Object[]{"ID", "NAME", "LASTNAME"});
    data.put("2", new Object[]{1, "Tom", "Nickson"});
    data.put("3", new Object[]{2, "Sally", "Brooks"});
    data.put("4", new Object[]{3, "Lisa", "Mayer"});
    Set<String> keyset = data.keySet();
    int rownum = 0;
    for (String key : keyset) {
        Row row = sheet.createRow(rownum++);
        Object[] objArr = data.get(key);
        int cellnum = 0;
        for (Object obj : objArr) {
            Cell cell = row.createCell(cellnum++);
            if (obj instanceof String)
                cell.setCellValue((String) obj);
            else if (obj instanceof Integer)
                cell.setCellValue((Integer) obj);
        }
    }
    FileOutputStream outputStream = new FileOutputStream("sample.xls");
    workbook.write(outputStream);
    workbook.close();
    outputStream.close();
} catch (Exception ex) {
    ex.printStackTrace();
}

写入xlsx格式文件

• 程序示例：

try {
    XSSFWorkbook workbook = new XSSFWorkbook();
    XSSFSheet sheet = workbook.createSheet("Sample sheet");
    Map<String, Object[]> data = new TreeMap<String, Object[]>();
    data.put("1", new Object[]{"ID", "NAME", "LASTNAME"});
    data.put("2", new Object[]{1, "Tom", "Nickson"});
    data.put("3", new Object[]{2, "Sally", "Brooks"});
    data.put("4", new Object[]{3, "Lisa", "Mayer"});
    Set<String> keyset = data.keySet();
    int rownum = 0;
    for (String key : keyset) {
        Row row = sheet.createRow(rownum++);
        Object[] objArr = data.get(key);
        int cellnum = 0;
        for (Object obj : objArr) {
            Cell cell = row.createCell(cellnum++);
            if (obj instanceof String)
                cell.setCellValue((String) obj);
            else if (obj instanceof Integer)
                cell.setCellValue((Integer) obj);
        }
    }
    FileOutputStream outputStream = new FileOutputStream("sample.xlsx");
    workbook.write(outputStream);
    workbook.close();
    outputStream.close();
} catch (Exception ex) {
    ex.printStackTrace();
}

以上是Java操作Excel文件解析与读写方法的详细攻略。