1.首先导入依赖
<dependencies>
<!-- xlx的依赖-->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>3.17</version>
</dependency>
<!-- xlsx的依赖-->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>3.17</version>
</dependency>
<dependency>
<groupId>junit</groupId>
<artifactId>junit</artifactId>
<version>4.12</version>
</dependency>
</dependencies>
2.两个依赖,分别是poi和poi-ooxml对应后缀名不同的excel xls和xlsx,这里默认使用xlsx的
import org.apache.poi.hssf.usermodel.HSSFCell; import org.apache.poi.hssf.usermodel.HSSFRow; import org.apache.poi.hssf.usermodel.HSSFSheet; import org.apache.poi.hssf.usermodel.HSSFWorkbook; import org.apache.poi.ss.usermodel.Cell; import org.apache.poi.ss.usermodel.CellType; import org.apache.poi.xssf.usermodel.XSSFCell; import org.apache.poi.xssf.usermodel.XSSFRow; import org.apache.poi.xssf.usermodel.XSSFSheet; import org.apache.poi.xssf.usermodel.XSSFWorkbook; import org.junit.Test; import java.io.*; import java.util.HashMap; import java.util.Map; public class ExcelTest { @Test public void creatAndWrite() { FileOutputStream out= null; try { out = new FileOutputStream("F:\\test.xlsx"); } catch (FileNotFoundException e) { e.printStackTrace(); } XSSFWorkbook book=new XSSFWorkbook(); //生成sheet页 XSSFSheet sheet= book.createSheet("sheet1"); //生成头部行 rownum对应行数-1 XSSFRow row = sheet.createRow(0); XSSFCell cell1 = row.createCell(0); cell1.setCellValue("编号"); XSSFCell cell2 = row.createCell(1); cell2.setCellValue("姓名"); XSSFCell cell3 = row.createCell(2); cell3.setCellValue("日期"); //从此行开始写入数据 //如有多行数据 循环即可 XSSFRow row1 = sheet.createRow(1); XSSFCell cell11 = row1.createCell(0); cell11.setCellValue(1); XSSFCell cell12 = row1.createCell(1); cell12.setCellValue("tom"); XSSFCell cell13 = row1.createCell(2); cell13.setCellValue("2012-02-15"); try { book.write(out); } catch (IOException e) { e.printStackTrace(); } } @Test public void read(){ Map<Integer,Object> map=new HashMap<>(); try { FileInputStream in=new FileInputStream("F:\\test.xlsx"); XSSFWorkbook book=new XSSFWorkbook(in); XSSFSheet sheet1 = book.getSheet("sheet1"); XSSFRow row = sheet1.getRow(1); for(int i=0;i<row.getLastCellNum();i++){ if(row.getCell(i).getCellType()==0){ //判断是否为数值格式 //System.out.println(row.getCell(i)); }else if(row.getCell(i).getCellType()==1){ //判断是否为字符串格式 //System.out.println(row.getCell(i)); } map.put(i,row.getCell(i)); } } catch (Exception e) { e.printStackTrace(); } System.out.println(map); } }
对应测试结果: