poi解析excel文件（支持xls和xlsx）java学习版

有时候涉及到数据的批量导入导出，就需要用代码去操作excel了

---

• 首先去Apache 官网下载jar包并解压 ,导入到libs中 下载sdk
  
  • 项目结构如图
    
• 或者采用Maven的形式引入

<dependency>
		<groupId>org.apache.poi</groupId>
		<artifactId>poi</artifactId>
		<version>4.0.1</version>
	</dependency>
	<dependency>
		<groupId>org.apache.poi</groupId>
		<artifactId>poi-ooxml</artifactId>
		<version>4.0.1</version>
	</dependency>

• 读文件

public class ReadExcel {
    /**
     * 
     * @param path 传入文件路径
     */
    public static void readExcel(String path) {
        File file = new File(path);
        FileInputStream fis = null;
        Workbook workBook = null;
        if (file.exists()) {
            try {
                fis = new FileInputStream(file);
                //创建工作簿
                workBook = WorkbookFactory.create(fis);
                //获取共有多少张表
                int numberOfSheets = workBook.getNumberOfSheets();
                System.out.println("共有" + numberOfSheets + "张工作表");
                // sheet工作表
                for (int s = 0; s < numberOfSheets; s++) {
                    //获取当前需要操作的表
                    Sheet sheetAt = workBook.getSheetAt(s);
                    //获取工作表名称
                    String sheetName = sheetAt.getSheetName();
                    System.out.println("工作表名称：" + sheetName);

                    // 获取当前Sheet的总行数
                    int rowsOfSheet = sheetAt.getPhysicalNumberOfRows();
                    if (rowsOfSheet == 0) {
                        continue;
                    }

                 over:for (int r = 0; r < sheetAt.getLastRowNum(); r++) {
                        Row row = sheetAt.getRow(r);
                        if (row == null) {
                            continue;
                        }
                        if (r==0){
                            // 第一行
                            int physicalNumberOfCells = row.getPhysicalNumberOfCells();
                            String[] title = new String[physicalNumberOfCells];
                            for (int i = 0; i < physicalNumberOfCells; i++) {
                                title[i] = row.getCell(i).getStringCellValue();
                                if (i == 0) {
                                    System.out.print(title[i] + "|");
                                } else {
                                    System.out.println(title[i] + "|");
                                }
                            }
                            continue ;
                        }
                        for (int c= 0; c < row.getLastCellNum(); c++) {
                            // 总列(格)
                            Cell cell = row.getCell(c);
                            if (cell==null){
                              break over;
                            }
                            /**
                            * cell.getCellType() 获取每个单元格的数据类型，调用不同的方法
                             * public enum CellType {
                             *     _NONE(-1),
                             *     NUMERIC(0),
                             *     STRING(1),
                             *     FORMULA(2),
                             *     BLANK(3),
                             *     BOOLEAN(4),
                             *     ERROR(5);
                             *     }
                             */
                            switch (cell.getCellType()) {
                                case STRING:
                                    System.out.print(cell.getStringCellValue()+ "|");
                                    break;
                                case NUMERIC:
                                    long phoneNum = (long) cell.getNumericCellValue();
                                    System.out.println(phoneNum + "|");
                                    break;
                                    default:System.out.println("数据错误！");
                            }
                        }

                    }
                }
            } catch (Exception e) {
                e.printStackTrace();
            } finally {
                try {
                    if (fis != null) {
                        fis.close();
                    }
                    if (workBook != null) {
                        workBook.close();
                    }
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }
        } else {
            System.out.println("文件不存在!");
        }
    }
}
public class Main {

    public static void main(String[] args) {
      ReadExcel.readExcel("test_2003.xls");
      //ReadExcel.readExcel("test_2007.xlsx");
    }
}

执行结果如下：

---

• 写入数据到表格

---

 class WriteExcel {
    public static void writeExcel() {
        //创建工作簿
        Workbook wb = new HSSFWorkbook();
        //创建工作表
        Sheet sheet = wb.createSheet("通讯录");
        Row title = sheet.createRow(0);
        Cell titleCell = title.createCell(0);
        titleCell.setCellValue("通讯录");
        //创建表头
        Row titleRow = sheet.createRow(1);
        Cell cellTitle0 = titleRow.createCell(0);
        cellTitle0.setCellValue("用户名");
        Cell cellTitle1 = titleRow.createCell(1);
        cellTitle1.setCellValue("手机号");

        for (int i = 2; i < 6; i++) {
            //创建单元行
            Row row = sheet.createRow(i);
            for (int j = 0; j <2; j++) {
                Cell cell = row.createCell(j);
                if (j==0){
                    cell.setCellValue("ITplus"+i);
                }else {
                    cell.setCellValue("1330455123"+i);
                }

            }
        }
        try (FileOutputStream fos = new FileOutputStream("createExcel.xls")) {
            //路径需要存在
            wb.write(fos);
            wb.close();
            System.out.println("写数据结束！");
        } catch (IOException e) {
            System.out.println("写数据异常！");
            e.printStackTrace();
        }
    }
}
public class Main {

    public static void main(String[] args) {
        WriteExcel.writeExcel();
    }
}

执行效果如图：

---

纯属于个人学习用，具体拓展要根据需求而定；不过数据的写入和输出都在Cell接口中操作

public interface Cell {
    int getColumnIndex();

    int getRowIndex();

    Sheet getSheet();

    Row getRow();

    /** @deprecated */
    @Deprecated
    @Removal(
        version = "5.0"
    )
    void setCellType(CellType var1);

    void setBlank();

    CellType getCellType();

    /** @deprecated */
    @Deprecated
    @Removal(
        version = "4.2"
    )
    CellType getCellTypeEnum();

    CellType getCachedFormulaResultType();

    /** @deprecated */
    @Deprecated
    @Removal(
        version = "4.2"
    )
    CellType getCachedFormulaResultTypeEnum();

    void setCellValue(double var1);

    void setCellValue(Date var1);

    void setCellValue(LocalDateTime var1);

    default void setCellValue(LocalDate value) {
        this.setCellValue(value == null ? null : value.atStartOfDay());
    }

    void setCellValue(Calendar var1);

    void setCellValue(RichTextString var1);

    void setCellValue(String var1);

    void setCellFormula(String var1) throws FormulaParseException, IllegalStateException;

    void removeFormula() throws IllegalStateException;

    String getCellFormula();

    double getNumericCellValue();

    Date getDateCellValue();

    LocalDateTime getLocalDateTimeCellValue();

    RichTextString getRichStringCellValue();

    String getStringCellValue();

    void setCellValue(boolean var1);

    void setCellErrorValue(byte var1);

    boolean getBooleanCellValue();

    byte getErrorCellValue();

    void setCellStyle(CellStyle var1);

    CellStyle getCellStyle();

    void setAsActiveCell();

    CellAddress getAddress();

    void setCellComment(Comment var1);

    Comment getCellComment();

    void removeCellComment();

    Hyperlink getHyperlink();

    void setHyperlink(Hyperlink var1);

    void removeHyperlink();

    CellRangeAddress getArrayFormulaRange();

    boolean isPartOfArrayFormulaGroup();
}