美文网首页我爱编程程序员
使用Java,Apache POI读写Excel

使用Java,Apache POI读写Excel

作者: 字伯约 | 来源:发表于2017-06-28 15:35 被阅读0次

    Maven依赖

    • Excel2003或更早的版本
    <dependency>
        <groupId>org.apache.poi</groupId>
        <artifactId>poi</artifactId>
        <version>VERSION</version>
    </dependency>
    
    • Excel2007或更新的版本
    <dependency>
        <groupId>org.apache.poi</groupId>
        <artifactId>poi-ooxml</artifactId>
        <version>VERSION</version>
    </dependency>
    

    Apache POI里面的基本概念

    HSSF开头的API在2003或跟老的版本使用
    XSSF开头的API在2007或更新的版本使用

    • workbook excel文件,分为 HSSFWorkbook 和XSSFWorkbook
    • sheet 页,分为HSSFSheet和XSSFSheet
    • row 行,分为HSSFRow和XSSFRow
    • cell 单元格,分为HSSFCell和XSSFCell

    下面给出一个基础的代码

    读Excel,一行一行,一格一格读取,假设你有一个这样的文件,有3列,书名,作者,价格


    import java.io.File;
    import java.io.FileInputStream;
    import java.io.IOException;
    import java.util.Iterator;
    
    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.xssf.usermodel.XSSFWorkbook;
    
    /**
     * A dirty simple program that reads an Excel file.
     * @author www.codejava.net
     *
     */
    public class SimpleExcelReaderExample {
        
        public static void main(String[] args) throws IOException {
            String excelFilePath = "Books.xlsx";
            FileInputStream inputStream = new FileInputStream(new File(excelFilePath));
            
            Workbook workbook = new XSSFWorkbook(inputStream);
            Sheet firstSheet = workbook.getSheetAt(0);
            Iterator<Row> iterator = firstSheet.iterator();
            
            while (iterator.hasNext()) {
                Row nextRow = iterator.next();
                Iterator<Cell> cellIterator = nextRow.cellIterator();
                
                while (cellIterator.hasNext()) {
                    Cell cell = cellIterator.next();
                    
                    switch (cell.getCellType()) {
                        case Cell.CELL_TYPE_STRING:
                            System.out.print(cell.getStringCellValue());
                            break;
                        case Cell.CELL_TYPE_BOOLEAN:
                            System.out.print(cell.getBooleanCellValue());
                            break;
                        case Cell.CELL_TYPE_NUMERIC:
                            System.out.print(cell.getNumericCellValue());
                            break;
                    }
                    System.out.print(" - ");
                }
                System.out.println();
            }
            
            workbook.close();
            inputStream.close();
        }
    
    }
    

    输出结果

    Head First Java - Kathy Serria - 79.0 -
    Effective Java - Joshua Bloch - 36.0 -
    Clean Code - Robert Martin - 42.0 -
    Thinking in Java - Bruce Eckel - 35.0 -
    

    优化代码,使用面向对象特性

    创建一个Book对象

    public class Book {
        private String title;
        private String author;
        private float price;
    
        public Book() {
        }
    
        public String toString() {
            return String.format("%s - %s - %f", title, author, price);
        }
    
        // getters and setters
    }
    

    重构出一个单独的方法获取单元格的值

    private Object getCellValue(Cell cell) {
        switch (cell.getCellType()) {
        case Cell.CELL_TYPE_STRING:
            return cell.getStringCellValue();
    
        case Cell.CELL_TYPE_BOOLEAN:
            return cell.getBooleanCellValue();
    
        case Cell.CELL_TYPE_NUMERIC:
            return cell.getNumericCellValue();
        }
    
        return null;
    }
    

    读取excel,返回一个List<Book>

    public List<Book> readBooksFromExcelFile(String excelFilePath) throws IOException {
        List<Book> listBooks = new ArrayList<>();
        FileInputStream inputStream = new FileInputStream(new File(excelFilePath));
    
        Workbook workbook = new XSSFWorkbook(inputStream);
        Sheet firstSheet = workbook.getSheetAt(0);
        Iterator<Row> iterator = firstSheet.iterator();
    
        while (iterator.hasNext()) {
            Row nextRow = iterator.next();
            Iterator<Cell> cellIterator = nextRow.cellIterator();
            Book aBook = new Book();
    
            while (cellIterator.hasNext()) {
                Cell nextCell = cellIterator.next();
                int columnIndex = nextCell.getColumnIndex();
    
                switch (columnIndex) {
                case 1:
                    aBook.setTitle((String) getCellValue(nextCell));
                    break;
                case 2:
                    aBook.setAuthor((String) getCellValue(nextCell));
                    break;
                case 3:
                    aBook.setPrice((double) getCellValue(nextCell));
                    break;
                }
    
    
            }
            listBooks.add(aBook);
        }
    
        workbook.close();
        inputStream.close();
    
        return listBooks;
    }
    

    测试代码

    public static void main(String[] args) throws IOException {
        String excelFilePath = "Books.xlsx";
        ExcelReaderExample2 reader = new ExcelReaderExample2();
        List<Book> listBooks = reader.readBooksFromExcelFile(excelFilePath);
        System.out.println(listBooks);
    }
    

    输出结果

    [Head First Java - Kathy Serria - 79.000000, Effective Java - Joshua Bloch - 36.000000,
        Clean Code - Robert Martin - 42.000000, Thinking in Java - Bruce Eckel - 35.000000]
    

    其他API方法

    获取一个指定的sheet

    Sheet thirdSheet = workbook.getSheetAt(2);
    

    获取sheet的名字

    String sheetName = sheet.getSheetName();
    

    获取sheet的个数

    int numberOfSheets = workbook.getNumberOfSheets();
    

    相关文章

      网友评论

        本文标题:使用Java,Apache POI读写Excel

        本文链接:https://www.haomeiwen.com/subject/tuuucxtx.html