POI操作Excel

1、导入依赖

你好！ 这是你第一次使用 Markdown编辑器 所展示的欢迎页。如果你想学习如何使用Markdown编辑器, 可以仔细阅读这篇文章，了解一下Markdown的基本语法知识。

<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi</artifactId>
    <version>3.9</version>
</dependency>
<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi-ooxml</artifactId>
    <version>3.9</version>
</dependency>
<dependency>
    <groupId>joda-time</groupId>
    <artifactId>joda-time</artifactId>
    <version>2.10.1</version>
</dependency>
<dependency>
    <groupId>org.projectlombok</groupId>
    <artifactId>lombok</artifactId>
    <version>RELEASE</version>
    <scope>provided</scope>
</dependency>

2、03版 / 07版 写文件

	// 03 工作簿
	//    Workbook workbook = new HSSFWorkbook();
	// 07 工作簿
	Workbook workbook = new XSSFWorkbook();
	
	// 工作表
	Sheet sheet = workbook.createSheet("table_xjc");
	
	// 创建一行
	Row row1 = sheet.createRow(0);
	
	// 创建一个单元格
	Cell cell11 = row1.createCell(0);
	cell11.setCellValue("=x11=");
	
	Cell cell12 = row1.createCell(1);
	cell12.setCellValue("=x12=");
	
	Row row2 = sheet.createRow(1);
	Cell cell21 = row2.createCell(0);
	cell21.setCellValue("=x21=");
	Cell cell22 = row2.createCell(1);
	cell22.setCellValue(new DateTime().toString("yyyy-MM-dd HH:mm:ss"));
	
	// 03 版本
	//        FileOutputStream fileOutputStream = new FileOutputStream(PATH + "\\test_03.xls");
	// 07 版本
	FileOutputStream fileOutputStream = new FileOutputStream(PATH + "\\test_07.xlsx");
	workbook.write(fileOutputStream);
	fileOutputStream.close();

3、03版本 / 07版本 读文件

	// 03 版本
	//    FileInputStream fileInputStream = new FileInputStream(PATH + "\\test_03.xls");
	//    Workbook workbookh = new HSSFWorkbook(fileInputStream);
	// 07 版本
	FileInputStream fileInputStream = new FileInputStream(PATH + "\\test_07.xlsx");
	Workbook workbookh = new XSSFWorkbook(fileInputStream);
	Sheet sheeth = workbookh.getSheetAt(0);
	Row rowh = sheeth.getRow(0);
	Cell cellh = rowh.getCell(0);
	String cellValue = cellh.getStringCellValue();
	System.out.println(cellValue);

4、07版本标准按类型读取文件数据

	// 07 版本 标准按类型读取数据
	FileInputStream inputStream = new FileInputStream(PATH + "\\test_07.xlsx");
	Workbook workbook = new XSSFWorkbook(inputStream);
	Sheet sheet = workbook.getSheetAt(0);
	int numberOfRows = sheet.getPhysicalNumberOfRows();
	for (int j = 0; j < numberOfRows; j++) {
	    Row row = sheet.getRow(j);
	    int numberOfCells = row.getPhysicalNumberOfCells();
	    for (int i = 0; i < numberOfCells; i++) {
	        Cell cell = row.getCell(i);
	        int cellType = cell.getCellType();
	        switch (cellType){
	            // case XSSFCell.
	            case XSSFCell.CELL_TYPE_STRING:
	                System.out.print(cell.getStringCellValue()+"| ");
	                break;
	            case XSSFCell.CELL_TYPE_BLANK:
	                System.out.print("空值| ");
	                break;
	            case XSSFCell.CELL_TYPE_BOOLEAN:
	                System.out.print(cell.getBooleanCellValue()+"| ");
	                break;
	            case XSSFCell.CELL_TYPE_FORMULA:
	                FormulaEvaluator formulaEvaluator = new XSSFFormulaEvaluator((XSSFWorkbook) workbook);
	                //获得计算公式
	                String cellFormula = cell.getCellFormula();
	                CellValue evaluate = formulaEvaluator.evaluate(cell);
	                String s = evaluate.formatAsString();
	                System.out.print(s+"| ");
	                break;
	            case XSSFCell.CELL_TYPE_NUMERIC:
	                if (HSSFDateUtil.isCellDateFormatted(cell)){
	                    System.out.print(cell.getDateCellValue()+"| ");
	                }else {
	                    cell.setCellType(XSSFCell.CELL_TYPE_STRING);
	                    System.out.print(cell.getStringCellValue()+"| ");
	                }
	                break;
	            case XSSFCell.CELL_TYPE_ERROR:
	                System.out.print("数据类型错误| ");
	                break;
	        }
	    }
	    System.out.println();
	}