最近在做一个物业的系统，需要通过excel上传业主的信息，解析并入库。
参考：https://www.cnblogs.com/jyyjava/p/8074322.html
话不多说，直接上核心代码
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.stereotype.Component;
import org.springframework.util.StringUtils;
import org.springframework.web.multipart.MultipartFile;
import org.springframework.web.multipart.MultipartHttpServletRequest;
import javax.servlet.http.HttpServletRequest;
import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.LinkedList;
import java.util.List;
/**
* author
* <p>
* create 2019-01-21 15:57
* <p>
* desc
**/
@Component
public class ExcelUtil {
/**
* 标题样式
*/
private final static String STYLE_HEADER = "header";
/**
* 表头样式
*/
private final static String STYLE_TITLE = "title";
/**
* 数据样式
*/
private final static String STYLE_DATA = "data";
/**
* 存储样式
*/
private static final HashMap<String, CellStyle> cellStyleMap = new HashMap<>();
//解析 excel
public static List readExcel(HttpServletRequest request) throws IOException {
MultipartHttpServletRequest multipartRequest = (MultipartHttpServletRequest) request;
MultipartFile file = multipartRequest.getFile("filename");
// String[] IMAGE_TYPE = "xlsx,xls".split(",");
// String path = null;
// boolean flag = false;
// for (String type : IMAGE_TYPE) {
// if (StringUtils.endsWithIgnoreCase(file.getOriginalFilename(), type)) {
// flag = true;
// break;
// }
// }
Workbook wb = null;
/*MultipartFile转换File*/
File f = null;
if(file.equals("")||file.getSize()<=0){
file = null;
}else {
InputStream ins =file.getInputStream();
f = new File( file.getOriginalFilename() );
FileUtil.inputStreamToFile( ins, f );
}
// 获得文件名称
String fileName = f.getName();
// 获得后缀
String extName = fileName.substring(fileName.indexOf("."));
// 根据后缀名称判断excel的版本
if (ExcelVersion.V2003.getSuffix().equals(extName)) {
wb = new HSSFWorkbook(new FileInputStream(f));
} else if (ExcelVersion.V2007.getSuffix().equals(extName)) {
wb = new XSSFWorkbook(new FileInputStream(f));
} else {
// 无效后缀名称，这里之能保证excel的后缀名称，不能保证文件类型正确，不过没关系，在创建Workbook的时候会校验文件格式
throw new IllegalArgumentException("Invalid excel version");
}
// 开始读取数据
List<ExcelSheetPO> sheetPOs = new ArrayList<>();
// 解析sheet
for (int i = 0; i < wb.getNumberOfSheets(); i++) {
Sheet sheet = wb.getSheetAt(i);
List<List<Object>> dataList = new ArrayList<>();
ExcelSheetPO sheetPO = new ExcelSheetPO();
sheetPO.setSheetName(sheet.getSheetName());
sheetPO.setDataList(dataList);
int readRowCount = 0;
readRowCount = sheet.getPhysicalNumberOfRows();
// 解析sheet 的行
for (int j = sheet.getFirstRowNum(); j < readRowCount; j++) {
Row row = sheet.getRow(j);
if (row == null) {
continue;
}
if (row.getFirstCellNum() < 0) {
continue;
}
int readColumnCount = 0;
readColumnCount = (int) row.getLastCellNum();
List<Object> rowValue = new LinkedList<Object>();
// 解析sheet 的列
for (int k = 0; k < readColumnCount; k++) {
Cell cell = row.getCell(k);
rowValue.add(getCellValue(wb, cell));
}
dataList.add(rowValue);
}
sheetPOs.add(sheetPO);
}
return sheetPOs;
}
private static Object getCellValue(Workbook wb, Cell cell) {
Object columnValue = null;
if (cell != null) {
DecimalFormat df = new DecimalFormat("0");// 格式化 number
// String
// 字符
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");// 格式化日期字符串
DecimalFormat nf = new DecimalFormat("0.00");// 格式化数字
switch (cell.getCellType()) {
case Cell.CELL_TYPE_STRING:
columnValue = cell.getStringCellValue();
break;
case Cell.CELL_TYPE_NUMERIC:
if ("@".equals(cell.getCellStyle().getDataFormatString())) {
columnValue = df.format(cell.getNumericCellValue());
} else if ("General".equals(cell.getCellStyle().getDataFormatString())) {
columnValue = nf.format(cell.getNumericCellValue());
} else {
columnValue = sdf.format( HSSFDateUtil.getJavaDate(cell.getNumericCellValue()));
}
break;
case Cell.CELL_TYPE_BOOLEAN:
columnValue = cell.getBooleanCellValue();
break;
case Cell.CELL_TYPE_BLANK:
columnValue = "";
break;
case Cell.CELL_TYPE_FORMULA:
// 格式单元格
FormulaEvaluator evaluator = wb.getCreationHelper().createFormulaEvaluator();
evaluator.evaluateFormulaCell(cell);
CellValue cellValue = evaluator.evaluate(cell);
columnValue = cellValue.getNumberValue();
break;
default:
columnValue = cell.toString();
}
}
return columnValue;
}
}
/**
* author
* <p>
* create 2019-01-21 16:00
* <p>
* desc Excel版本枚举
**/
public enum ExcelVersion {
/**
* 虽然V2007版本支持最大支持1048575 * 16383 ，
* V2003版支持65535*255
* 但是在实际应用中如果使用如此庞大的对象集合会导致内存溢出，
* 因此这里限制最大为10000*100，如果还要加大建议先通过单元测试进行性能测试。
* 1000*100 全部导出预计时间为27s左右
*/
V2003(".xls", 10000, 100), V2007(".xlsx", 100, 100);
private String suffix;
private int maxRow;
private int maxColumn;
ExcelVersion(String suffix, int maxRow, int maxColumn) {
this.suffix = suffix;
this.maxRow = maxRow;
this.maxColumn = maxColumn;
}
public String getSuffix() {
return this.suffix;
}
public int getMaxRow() {
return maxRow;
}
public void setMaxRow(int maxRow) {
this.maxRow = maxRow;
}
public int getMaxColumn() {
return maxColumn;
}
public void setMaxColumn(int maxColumn) {
this.maxColumn = maxColumn;
}
public void setSuffix(String suffix) {
this.suffix = suffix;
}
}