java poi 读取excel工具类_POI读取excel工具类(xls,xlsx通用)

package com.boot.utils;

import java.io.File;

import java.io.FileInputStream;

import java.io.FileNotFoundException;

import java.io.IOException;

import java.util.ArrayList;

import java.util.HashMap;

import java.util.List;

import java.util.Map;

import org.apache.poi.hssf.usermodel.HSSFDataFormatter;

import org.apache.poi.openxml4j.exceptions.InvalidFormatException;

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

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

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

import org.apache.poi.ss.usermodel.WorkbookFactory;public classPOIExcel {private int totalRows = 0;//总行数

private int totalCells = 0;//总列数

public Map>>read(String fileName) {

Map>> maps = new HashMap>>();if (fileName == null || !fileName.matches("^.+\\.(?i)((xls)|(xlsx))$"))returnmaps;

File file= newFile(fileName);if (file == null || !file.exists())returnmaps;try{

Workbook wb= WorkbookFactory.create(newFileInputStream(file));

maps=read(wb);

}catch(FileNotFoundException e) {

e.printStackTrace();

}catch(InvalidFormatException e) {

e.printStackTrace();

}catch(IOException e) {

e.printStackTrace();

}returnmaps;

}public intgetTotalRows() {returntotalRows;

}public intgetTotalCells() {returntotalCells;

}private Map>>read(Workbook wb) {

Map>> maps = new HashMap>>();int number =wb.getNumberOfSheets();if (number > 0) {for (int i = 0; i < number; i++) { //循环每个工作表

List> list = new ArrayList>();int delnumber = 0;//第一页去除行数

Sheet sheet =wb.getSheetAt(i);this.totalRows = sheet.getPhysicalNumberOfRows() - delnumber; //获取工作表中行数

if (this.totalRows >= 1 && sheet.getRow(delnumber) != null) {this.totalCells = sheet.getRow(0)

.getPhysicalNumberOfCells();//得到当前行的所有单元格

for (int j = 0; j < totalRows; j++) {

List rowLst = new ArrayList();for (int f = 0; f < totalCells; f++) {if (totalCells > 0) {

String value=getCell(sheet.getRow(j).getCell(f));

rowLst.add(value);

}

}

list.add(rowLst);

}

}

maps.put(sheet.getSheetName(), list);

}

}returnmaps;

}/** private String getRightStr(String sNum) { DecimalFormat decimalFormat =

* new DecimalFormat("##.00"); String resultStr = decimalFormat.format(new

* Double(sNum)); if (resultStr.matches("^[-+]?\\d+\\.[0]+$")) { resultStr =

* resultStr.substring(0, sNum.indexOf(".")); } return resultStr; }*/

publicString getCell(Cell cell) {

String cellValue= null;/** if (Cell.CELL_TYPE_NUMERIC == cell.getCellType()) { if

* (HSSFDateUtil.isCellDateFormatted(cell)) { cellValue =

* getRightStr(cell.getDateCellValue() + ""); } else {

*

* cellValue = getRightStr(cell.getNumericCellValue() + ""); } } else if

* (Cell.CELL_TYPE_STRING == cell.getCellType()) { cellValue =

* cell.getStringCellValue(); } else if (Cell.CELL_TYPE_BOOLEAN ==

* cell.getCellType()) { cellValue = cell.getBooleanCellValue() + ""; }

* else { cellValue = cell.getStringCellValue(); }*/HSSFDataFormatter hSSFDataFormatter= newHSSFDataFormatter();

cellValue= hSSFDataFormatter.formatCellValue(cell); //使用EXCEL原来格式的方式取得值

returncellValue;

}public static voidmain(String[] args) {try{

Map>> map = newPOIExcel()

.read("d:\\user.xlsx");

System.out.println(map);

}catch(Exception e) {

e.printStackTrace();

}

}

}