调用工具类的示例:
//接收读取sheet的表明
List<Map<String, String>> listMapName = ReadUtil.readXlsNames(path);
//读取数据
// 循环listMapName, 根据sheet名读取数据
// 参数说明 文件,sheet表名,行下标, 列下标 , 文件路径 (文件个文件路径传一个就行, 支持路径读取和文件读取)
List<Map<String, String>> listMap = ReadUtil.inportExcel(null, "sheet表名", 0, 0,path);
以下是工具类完整代码:(工具类中对excel数据类型进行了处理, 主要是处理日期类型的数据)
package com.zkyt.common;
import org.apache.commons.lang3.StringUtils;
import org.apache.log4j.Logger;
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.util.CellRangeAddress;
import org.apache.poi.util.StringUtil;
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.springframework.web.multipart.MultipartFile;
import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.math.BigDecimal;
import java.text.SimpleDateFormat;
import java.util.*;
import java.util.regex.Pattern;
/*
* 读取excel的工具类
* @Author xujiajia
* @Date 2019/7/19
**/
public class ReadExcelUtil {
private final Logger logger = Logger.getLogger(ReadExcelUtil.class);
private final String OFFICE_EXCEL_2003_POSTFIX = "xls";
private final String OFFICE_EXCEL_2010_POSTFIX = "xlsx";
private final String EMPTY = "";
private final String POINT = ".";
private final String LIB_PATH = "lib";
private final String STUDENT_INFO_XLS_PATH = LIB_PATH + "/student_info" + POINT + OFFICE_EXCEL_2003_POSTFIX;
private final String STUDENT_INFO_XLSX_PATH = LIB_PATH + "/student_info" + POINT + OFFICE_EXCEL_2010_POSTFIX;
private final String NOT_EXCEL_FILE = " : Not the Excel file!";
private final String PROCESSING = "Processing...";
/**
* 总页数
*/
private int count=0;
/**
*
* @date 2019年6月13日 下午4:47:43
* @Description:读取excel2010版后的
*/
public List<Map<String, String>> readXlsx(MultipartFile file, String tabName,int index,int celNum,String path) throws IOException {
InputStream inputStream = null;
List<Map<String, String>> listMap = new ArrayList<>();
try {
//路径不为空,根据路径读取文件
if(StringUtils.isNotEmpty(path)){
inputStream = new FileInputStream(path);
}else{
inputStream = file.getInputStream();
}
//文件大小
String fileSize=bytes2kb(inputStream.available());
XSSFWorkbook xssfWorkbook = new XSSFWorkbook(inputStream);
XSSFSheet xssfSheet = null;
int startRowNum = 0;
int endRowNum = 0;
int celNums=0;
// 总页数
count = 0;
boolean bool = true;
// 清空
listMap.clear();
int sheetNum=xssfWorkbook.getNumberOfSheets();
Map<String, String> talNameMap = new HashMap<>();
talNameMap.put("fileSize",fileSize);
for (int i=0; i<sheetNum;i++ ){
talNameMap.put(String.valueOf(i),xssfWorkbook.getSheetName(i));
}
listMap.add(talNameMap);
if (tabName.equals("")|| tabName==null){
xssfSheet = xssfWorkbook.getSheetAt(0);
}else {
xssfSheet = xssfWorkbook.getSheet(tabName);
}
if (xssfSheet == null) {
return listMap;
}
// 分页判断
startRowNum = index;
endRowNum = xssfSheet.getLastRowNum();
count = xssfSheet.getLastRowNum() - startRowNum;
// Read the Row
for (int rowNum = startRowNum; rowNum <= endRowNum; rowNum++) {
XSSFRow xssfRow = xssfSheet.getRow(rowNum);
//判断空行
if(xssfRow==null) {
continue;
}
// //判断是否是合并行
// if (isMergedRegion(xssfSheet, xssfRow.getRowNum(), 1)) {
// System.out.println("============>"+rowNum+":合并单元格,停止循环");
// break;
// }
if(celNum==0){ //
if(celNums<xssfRow.getPhysicalNumberOfCells()){
celNums=xssfRow.getPhysicalNumberOfCells();
}
}else {
celNums=celNum;
}
Map<String, String> map = new HashMap<>();
//判断是否一行全都是空
int isNullRow=0;
for (int i = 0; i <= celNums; i++) {
if(xssfRow.getCell(i)==null){ //当前列是否为空
map.put(String.valueOf(i), "");
continue;
}
// xssfRow.getCell(i).setCellType(Cell.CELL_TYPE_STRING);
String value = getValue(xssfRow.getCell(i));
//如果不等于空加1,//并且小于6即表头是空行也要插入
if(value!=null) {
isNullRow++;
}
map.put(String.valueOf(i), value);
}
if (bool == false) {
count = rowNum - index;
break;
}
//空行不进行插入
if(isNullRow>0) {
listMap.add(map);
}
}
} catch (Exception e) {
logger.error(e.getMessage(),e);
} finally {
// 无论如何关闭流
inputStream.close();
}
return listMap;
}
/**
*
*@Date: 2019/7/17 16:04
*@Description: Read the Excel 2003-2007
**/
public List<Map<String, String>> readXls(MultipartFile file, String tabName, int index, int celNum, String path) throws IOException {
InputStream inputStream = null;
List<Map<String, String>> listMap = new ArrayList<>();
try {
//路径不为空,根据路径读取文件
if(StringUtils.isNotEmpty(path)){
inputStream = new FileInputStream(path);
}else{
inputStream = file.getInputStream();
}
//文件大小
String fileSize=bytes2kb(inputStream.available());
HSSFWorkbook hssfWorkbook = new HSSFWorkbook(inputStream);
HSSFSheet hssfSheet=null;
int startRowNum = 0;
int endRowNum = 0;
int celNums=0;
// 总页数
count = 0;
boolean bool = true;
// 清空
listMap.clear();
if (tabName.equals("")|| tabName==null){
hssfSheet=hssfWorkbook.getSheetAt(0);
}else {
hssfSheet =hssfWorkbook.getSheet(tabName);
}
int sheetNum=hssfWorkbook.getNumberOfSheets();
Map<String, String> talNameMap = new HashMap<>();
for (int i=0; i<sheetNum;i++ ){
talNameMap.put(String.valueOf(i),hssfWorkbook.getSheetName(i));
}
talNameMap.put("fileSize",fileSize);
listMap.add(talNameMap);
// workbook.getSheetAt(1);//读取序号为1的sheet(第二张sheet)
if (hssfSheet == null) {
return listMap;
}
// 分页判断
startRowNum = index;
endRowNum = hssfSheet.getLastRowNum();
count = hssfSheet.getLastRowNum() - startRowNum;
// Read the Row
for (int rowNum = startRowNum; rowNum <= endRowNum; rowNum++) {
HSSFRow hssfRow = hssfSheet.getRow(rowNum);
//判断空行
if(hssfRow==null) {
continue;
}
if(celNum==0){ //
if(celNums<hssfRow.getPhysicalNumberOfCells()){
celNums=hssfRow.getPhysicalNumberOfCells();
}
}else {
celNums=celNum;
}
Map<String, String> map = new HashMap<>();
//判断是否一行全都是空
int isNullRow=0;
for (int i = 0; i <= celNums; i++) {
if(hssfRow.getCell(i)==null){
map.put(String.valueOf(i), "");
continue;
}
// hssfRow.getCell(i).setCellType(Cell.CELL_TYPE_STRING);
String value = getValue(hssfRow.getCell(i));
//如果不等于空加1;//并且小于6即表头是空行也要插入
if(value!=null) {
isNullRow++;
}
map.put(String.valueOf(i), value);
}
//空行不进行插入
if(isNullRow>0) {
listMap.add(map);
}
}
} catch (Exception e) {
logger.error(e.getMessage(),e);
} finally {
// 无论如何关闭流
inputStream.close();
}
return listMap;
}
/**
*
* @date 2019年7月13日 下午4:47:43
* @Description:获得表格值 2010版后
*/
@SuppressWarnings("static-access")
private String getValue(XSSFCell xssfRow) {
try {
if (xssfRow != null && xssfRow.toString().length() > 0) {
if (xssfRow.getCellType()== Cell.CELL_TYPE_BOOLEAN) {
return String.valueOf(xssfRow.getBooleanCellValue());
} else if (xssfRow.getCellType() == Cell.CELL_TYPE_NUMERIC) {
short format = xssfRow.getCellStyle().getDataFormat();
SimpleDateFormat sdf = null;
if (format == 14 || format == 31 || format == 57 || format == 58
|| (176<=format && format<=178) || (182<=format && format<=196)
|| (210<=format && format<=213) || (208==format ) ) { // 日期
sdf = new SimpleDateFormat("yyyy-MM-dd");
} else if (format == 20 || format == 32 || format==183 || (200<=format && format<=209) ) { // 时间
sdf = new SimpleDateFormat("HH:mm");
} else { // 不是日期格式
if (String.valueOf(xssfRow.getNumericCellValue()).indexOf(".00")>=0){
return String.valueOf(xssfRow.getNumericCellValue()).trim();
}else {
return String.valueOf(xssfRow.getNumericCellValue()).replace(".0","").trim();
}
}
double value = xssfRow.getNumericCellValue();
Date date = org.apache.poi.ss.usermodel.DateUtil.getJavaDate(value);
if(date==null || "".equals(date)){
return "";
}
String result="";
try {
result = sdf.format(date);
} catch (Exception e) {
e.printStackTrace();
return "";
}
return result;
} else {
return String.valueOf(xssfRow.getStringCellValue());
}
}
} catch (Exception e) {
logger.error(e.getMessage(),e);
return null;
}
return null;
}
/**
*
* @date 2019年6月13日 下午4:47:43
* @Description:获得表格值 2003版
*/
@SuppressWarnings("static-access")
private String getValue(HSSFCell hssfCell) {
if (hssfCell.getCellType() == Cell.CELL_TYPE_BOOLEAN) {
return String.valueOf(hssfCell.getBooleanCellValue());
} else if (hssfCell.getCellType() == Cell.CELL_TYPE_NUMERIC) {
// if (String.valueOf(hssfCell.getNumericCellValue()).indexOf(".00")>=0){
// return String.valueOf(hssfCell.getNumericCellValue()).trim();
// }else {
// return String.valueOf(hssfCell.getNumericCellValue()).replace(".0","").trim();
// }
short format = hssfCell.getCellStyle().getDataFormat();
SimpleDateFormat sdf = null;
if (format == 14 || format == 31 || format == 57 || format == 58
|| (176<=format && format<=178) || (182<=format && format<=196)
|| (210<=format && format<=213) || (208==format ) ) { // 日期
sdf = new SimpleDateFormat("yyyy-MM-dd");
} else if (format == 20 || format == 32 || format==183 || (200<=format && format<=209) ) { // 时间
sdf = new SimpleDateFormat("HH:mm");
} else { // 不是日期格式
if (String.valueOf(hssfCell.getNumericCellValue()).indexOf(".00")>=0){
return String.valueOf(hssfCell.getNumericCellValue()).trim();
}else {
return String.valueOf(hssfCell.getNumericCellValue()).replace(".0","").trim();
}
}
double value = hssfCell.getNumericCellValue();
Date date = org.apache.poi.ss.usermodel.DateUtil.getJavaDate(value);
if(date==null || "".equals(date)){
return "";
}
String result="";
try {
result = sdf.format(date);
} catch (Exception e) {
e.printStackTrace();
return "";
}
return result;
} else {
return String.valueOf(hssfCell.getStringCellValue());
}
}
/**
*
* @date 2019年7月13日 下午4:47:43
* @Description:判断导入excel类型
*/
public String getPostfix(String path) {
if (path == null || EMPTY.equals(path.trim())) {
return EMPTY;
}
if (path.contains(POINT)) {
return path.substring(path.lastIndexOf(POINT) + 1, path.length());
}
return EMPTY;
}
/**
*
* @param file 文件(为空根据path路径读取数据)
* @param tabName 表名
* @param index 行下标
* @param celNum 列下标
* @param path 文件路径(为空根据file读取数据)
* @return
* @throws IOException
*/
public List<Map<String, String>> inportExcel(MultipartFile file, String tabName,int index,int celNum,String path)throws IOException {
List<Map<String, String>> listMap = new ArrayList<>();
if (file != null || StringUtils.isNotEmpty(path)) {
String postfix ="";
if(StringUtils.isNotEmpty(path)){
postfix = getPostfix(path);
}else {
postfix = getPostfix(file.getOriginalFilename());
}
if (!EMPTY.equals(postfix)) {
if (OFFICE_EXCEL_2003_POSTFIX.equals(postfix)) {
listMap = readXls(file, tabName, index, celNum,path);
} else if (OFFICE_EXCEL_2010_POSTFIX.equals(postfix)) {
listMap = readXlsx(file, tabName,index,celNum,path);
}
} else {
System.out.println(file.getOriginalFilename() + NOT_EXCEL_FILE);
}
}
return listMap;
}
//判断是否存在合并行
public boolean isMergedRegion(XSSFSheet sheet, int row, int column) {
int sheetMergeCount = sheet.getNumMergedRegions();
for (int i = 0; i < sheetMergeCount; i++) {
CellRangeAddress ca = sheet.getMergedRegion(i);
int firstColumn = ca.getFirstColumn();
int lastColumn = ca.getLastColumn();
int firstRow = ca.getFirstRow();
int lastRow = ca.getLastRow();
if (row >= firstRow && row <= lastRow) {
if (column >= firstColumn && column <= lastColumn) {
return true;
}
}
}
return false;
}
/**
* byte(字节)根据长度转成kb(千字节)和mb(兆字节)
*
* @param bytes
* @return
*/
public String bytes2kb(long bytes) {
BigDecimal filesize = new BigDecimal(bytes);
BigDecimal megabyte = new BigDecimal(1024 * 1024);
float returnValue = filesize.divide(megabyte, 2, BigDecimal.ROUND_UP)
.floatValue();
if (returnValue > 1)
return (returnValue + "MB");
BigDecimal kilobyte = new BigDecimal(1024);
returnValue = filesize.divide(kilobyte, 2, BigDecimal.ROUND_UP)
.floatValue();
return (Math.round(returnValue) + "KB");
}
/**
*读取所有表名
**/
public List<Map<String, String>> readXlsNames(String path) throws IOException {
InputStream inputStream = new FileInputStream(path);
List<Map<String, String>> listMap = new ArrayList<>();
try {
// inputStream = file.getInputStream();
String postfix = getPostfix(path);
if (!EMPTY.equals(postfix)) {
if (OFFICE_EXCEL_2003_POSTFIX.equals(postfix)) {
HSSFWorkbook hssfWorkbook = new HSSFWorkbook(inputStream);
listMap.clear();
int sheetNum=hssfWorkbook.getNumberOfSheets();
Map<String, String> talNameMap = new HashMap<>();
for (int i=0; i<sheetNum;i++ ){
talNameMap.put(String.valueOf(i),hssfWorkbook.getSheetName(i));
}
listMap.add(talNameMap);
} else if (OFFICE_EXCEL_2010_POSTFIX.equals(postfix)) {
XSSFWorkbook xssfWorkbook = new XSSFWorkbook(inputStream);
XSSFSheet xssfSheet = null;
// 清空
listMap.clear();
int sheetNum=xssfWorkbook.getNumberOfSheets();
Map<String, String> talNameMap = new HashMap<>();
for (int i=0; i<sheetNum;i++ ){
talNameMap.put(String.valueOf(i),xssfWorkbook.getSheetName(i));
}
listMap.add(talNameMap);
}
}
} catch (Exception e) {
logger.error(e.getMessage(),e);
} finally {
// 无论如何关闭流
inputStream.close();
}
return listMap;
}
/**
* 验证是否是数值类型
* @param str
* @return
*/
public static boolean isInteger(String str) {
Pattern pattern = Pattern.compile("^[-\\+]?[\\d]*$");
return pattern.matcher(str).matches();
}
}