excel 解析

excel解析使用的是POI技术

Apache POI是Apache软件基金会的开放源码函式库，POI提供API给Java程序对Microsoft Office格式档案读和写的功能。

maven坐标依赖

<dependency>

<groupId>org.apache.poi</groupId>

<artifactId>poi-ooxml</artifactId>

<version>3.17</version>

</dependency>

 

student.xls

 

 

 

读excel案例1：

package com.test.excel;

import org.apache.poi.ss.usermodel.*;

import java.io.FileInputStream;
import java.io.FileNotFoundException;

public class ExcelReadDemo {
public static void main(String[] args) throws Exception {
//excel 读取
//1、加载excel文件
FileInputStream fis = new FileInputStream("src/test/resources/student.xlsx");
//多态
//2、打开excel
//3、获取所有的sheet对象
Workbook sheets = WorkbookFactory.create(fis);
//4、获取指定的sheet
Sheet sheet = sheets.getSheetAt(0);
//5、获取指定的行
Row row = sheet.getRow(1);
//6、获取指定单元格 new Cell(); null
// System.out.println(row.getLastCellNum());
Cell cell = row.getCell(30, Row.MissingCellPolicy.CREATE_NULL_AS_BLANK);
cell.setCellType(CellType.STRING); //CellType.STRING 枚举：可以穷举
// method(int a) 1-7 枚举
//7、获取单元格内容
System.out.println(cell.getStringCellValue());
fis.close();

}
}

读excel案例2：

package com.test.excel;

import org.apache.poi.ss.usermodel.*;

import java.io.FileInputStream;

public class ExcelReadDemo2 {
public static void main(String[] args) throws Exception {
//excel 读取
//1、加载excel文件
FileInputStream fis = new FileInputStream("src/test/resources/student.xls");
//多态
//2、打开excel
//3、获取所有的sheet对象
Workbook sheets = WorkbookFactory.create(fis);
//4、获取指定的sheet
// Sheet sheet = sheets.getSheetAt(0);
Sheet sheet = sheets.getSheet("学生");
//5、获取指定的行
// for (Row row : sheet) {
// for (Cell cell : row) {
// cell.setCellType(CellType.STRING);
// System.out.print(cell.getStringCellValue() + " ");
// }
// System.out.println();
// }
int lastRowNum = sheet.getLastRowNum();
for(int i=0;i<=lastRowNum;i++) {
Row row = sheet.getRow(i);
int lastCellNum = row.getLastCellNum();
for(int j=0;j<lastCellNum;j++) {
Cell cell = row.getCell(j);
cell.setCellType(CellType.STRING);
System.out.print(cell.getStringCellValue() + " ");
}
System.out.println();
}

fis.close();

}
}

写excel案例：

package com.test.excel;

import org.apache.poi.ss.usermodel.*;

import java.io.FileInputStream;
import java.io.FileOutputStream;

public class ExcelWriteDemo {
public static void main(String[] args) throws Exception {
//excel 写 (修改 = 读取 写回去)
//1、加载excel文件
FileInputStream fis = new FileInputStream("src/test/resources/student.xlsx");
//用输出流怼到文件上，默认会情况文件内容
//FileOutputStream fos = new FileOutputStream("src/test/resources/student.xlsx");
//多态
//2、打开excel
//3、获取所有的sheet对象
Workbook sheets = WorkbookFactory.create(fis);
//4、获取指定的sheet
Sheet sheet = sheets.getSheetAt(0);
//5、获取指定的行

Row row = sheet.getRow(1);
//6、获取指定单元格 new Cell(); null
Cell cell = row.getCell(1, Row.MissingCellPolicy.CREATE_NULL_AS_BLANK);
cell.setCellType(CellType.STRING);
//7、获取单元格内容
cell.setCellValue("尼古拉斯赵四11");

Row row2 = sheet.getRow(2);
//6、获取指定单元格 new Cell(); null
Cell cell2 = row2.getCell(1, Row.MissingCellPolicy.CREATE_NULL_AS_BLANK);
cell2.setCellType(CellType.STRING);
//7、获取单元格内容
cell2.setCellValue("尼古拉斯赵四22");

Row row3 = sheet.getRow(3);
//6、获取指定单元格 new Cell(); null
Cell cell3 = row3.getCell(1, Row.MissingCellPolicy.CREATE_NULL_AS_BLANK);
cell3.setCellType(CellType.STRING);
//7、获取单元格内容
cell3.setCellValue("尼古拉斯赵四33");

//8、创建输出流
FileOutputStream fos = new FileOutputStream("src/test/resources/student.xlsx");
//9、把java内存中的内容回写到excel文件中。
sheets.write(fos);
fis.close();
fos.close();
}
}