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_.xls");//ReadExcel.readExcel("test_.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();}