文件上传的工具类,支持.xls和.xlsx类型
调用inportExcel方法(我自己生成的uuid为文件名称,防止重复)
package com.mapuni.utils;
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 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.CellType;
import org.apache.poi.ss.util.CellRangeAddress;
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;
/*
* 读取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 userId) throws IOException {
InputStream inputStream = null;
List<Map<String, String>> listMap = new ArrayList<>();
try {
inputStream = file.getInputStream();
// inputStream = new FileInputStream(path);
//文件大小
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);
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;
}
String value = getValue(xssfRow.getCell(i));
if (value != null && value.contains("注:①√表示必填项")) {
// 重新对分页进行处理
bool = false;
break;
} else {
//如果不等于空加1
if(value!=null) {
isNullRow++;
}
map.put(String.valueOf(i), value);
}
}
if (bool == false) {
count = rowNum - index;
break;
}
// //增加通用参数
// map.put(String.valueOf(celNum), UUID.randomUUID().toString());
// SimpleDateFormat df = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");//设置日期格式
// map.put(String.valueOf(celNum+1), df.format(new Date()));
// map.put(String.valueOf(celNum+2), userId);
// map.put(String.valueOf(celNum+3),proCode);
//空行不进行插入
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 userId) throws IOException {
InputStream inputStream = null;
List<Map<String, String>> listMap = new ArrayList<>();
try {
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;
}
String value = getValue(hssfRow.getCell(i));
if (value != null && value.contains("注:①√表示必填项")) {
// 重新对分页进行处理
bool = false;
break;
} else {
//如果不等于空加1
if(value!=null) {
isNullRow++;
}
map.put(String.valueOf(i), value);
}
}
if (bool == false) {
count = rowNum - index;
break;
}
//增加通用参数
// map.put(String.valueOf(celNum), UUID.randomUUID().toString());
SimpleDateFormat df = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");//设置日期格式
map.put(String.valueOf(celNums+1), df.format(new Date()));
map.put(String.valueOf(celNums+2), userId);
// map.put(String.valueOf(celNum+3),proCode);
//空行不进行插入
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()== CellType.BOOLEAN) {
return String.valueOf(xssfRow.getBooleanCellValue());
} else if (xssfRow.getCellType() == CellType.NUMERIC) {
System.out.print("=======================>"+xssfRow.getNumericCellValue());
long longVal = Math.round(xssfRow.getNumericCellValue());
if (Double.parseDouble(longVal + ".0") == xssfRow.getNumericCellValue())
return longVal+"";
else
return xssfRow.getNumericCellValue()+"";
} 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() == CellType.BOOLEAN) {
return String.valueOf(hssfCell.getBooleanCellValue());
} else if (hssfCell.getCellType() == CellType.NUMERIC) {
if (String.valueOf(hssfCell.getNumericCellValue()).indexOf(".00")>=0){
return String.valueOf(hssfCell.getNumericCellValue()).trim();
}else {
return String.valueOf(hssfCell.getNumericCellValue()).replace(".0","").trim();
}
} 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;
}
/**
*
* @date 2019年7月13日 上午11:09:04
* @Description:导入excel
*/
public List<Map<String, String>> inportExcel(MultipartFile file, String tabName,int index,int celNum,String userId)throws IOException {
List<Map<String, String>> listMap = new ArrayList<>();
if (file != null) {
String postfix = getPostfix(file.getOriginalFilename());
if (!EMPTY.equals(postfix)) {
if (OFFICE_EXCEL_2003_POSTFIX.equals(postfix)) {
listMap = readXls(file, tabName, index, celNum,userId);
} else if (OFFICE_EXCEL_2010_POSTFIX.equals(postfix)) {
listMap = readXlsx(file, tabName,index,celNum,userId);
}
} 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(MultipartFile file) throws IOException {
InputStream inputStream = null;
List<Map<String, String>> listMap = new ArrayList<>();
try {
inputStream = file.getInputStream();
String postfix = getPostfix(file.getOriginalFilename());
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;
}
}
文件上传的实体类
package com.mapuni.entity.utilEntity;
import java.util.Date;
/**
* 文件上传的实体类
*/
public class FileUploadEntity {
/**文件ID*/
private Integer id;
/**文件原名称*/
private String origName;
/**文件新名称*/
private String newName;
/**文件上传后服务器访问地址*/
private String serverPath;
/**文件大小*/
private Double fileSize;
/**文件MD5*/
private String fileMd5;
/**文件上传时间*/
private Date uploadTime;
public Integer getId() {
return id;
}
public void setId(Integer id) {
this.id = id;
}
public String getOrigName() {
return origName;
}
public void setOrigName(String origName) {
this.origName = origName;
}
public String getNewName() {
return newName;
}
public void setNewName(String newName) {
this.newName = newName;
}
public String getServerPath() {
return serverPath;
}
public void setServerPath(String serverPath) {
this.serverPath = serverPath;
}
public Double getFileSize() {
return fileSize;
}
public void setFileSize(Double fileSize) {
this.fileSize = fileSize;
}
public String getFileMd5() {
return fileMd5;
}
public void setFileMd5(String fileMd5) {
this.fileMd5 = fileMd5;
}
public Date getUploadTime() {
return uploadTime;
}
public void setUploadTime(Date uploadTime) {
this.uploadTime = uploadTime;
}
}