最近做项目时用户不满于只适用office excel,并且锁定单元格格式的excel表格,无奈只能去做通用,以下贴出代码
public List<String> insertOrderInfoTest(MultipartFile multipartFile, long organId, long userId) {
String columns[]={"orderCode","productNumber","productName","orderPrice"};
List<Map<String,String>> list=ExecelUtil.importExcel(multipartFile,columns);
}
public class ExecelUtil {
/**
* excel 表格导入解析,可解析xls,xlsx
* @param multipartFile 文件类型
* @param columns 生成map key值
* @return
* @throws IOException
*/
public static List<Map<String,String>> importExcel(MultipartFile multipartFile,String columns[]) throws IOException {
String fineName=multipartFile.getOriginalFilename();
Workbook wb =null;
Sheet sheet1 = null;
Row row = null;
List<Map<String,String>> list20 = null;
InputStream is = null;
String cellData = null;
is=multipartFile.getInputStream();
if(fineName.indexOf(".xlsx")!=-1){
wb = new XSSFWorkbook(is);
}else {
wb = new HSSFWorkbook(is);
}
if(wb != null){
//用来存放表中数据
list20 = new ArrayList<Map<String,String>>();
//获取第一个sheet
sheet1 = wb.getSheetAt(0);
//获取最大行数
int rownum = sheet1.getPhysicalNumberOfRows();
//获取第一行
row = sheet1.getRow(0);
//获取最大列数
int colnum = row.getPhysicalNumberOfCells();
for (int i = 1; i<rownum; i++) {
Map<String,String> map = new LinkedHashMap<String,String>();
row = sheet1.getRow(i);
if(row !=null){
for (int j=0;j<colnum;j++){
cellData = (String) getCellFormatValue(row.getCell(j)); //全转为String接受
map.put(columns[j], cellData);
}
}else{
break;
}
list20.add(map);
}
}
return list20;
}
}
public static Object getCellFormatValue(Cell cell){
Object cellValue = null;
if(cell!=null){
//判断cell类型
switch(cell.getCellType()){
case Cell.CELL_TYPE_NUMERIC:{
cellValue = String.valueOf(cell.getNumericCellValue());
break;
}
case Cell.CELL_TYPE_FORMULA:{
//判断cell是否为日期格式
if(DateUtil.isCellDateFormatted(cell)){
//转换为日期格式YYYY-mm-dd
cellValue = cell.getDateCellValue();
}else{
//数字
cellValue = String.valueOf(cell.getNumericCellValue());
}
break;
}
case Cell.CELL_TYPE_STRING:{
cellValue = cell.getRichStringCellValue().getString();
break;
}
default:
cellValue = "";
}
}else{
cellValue = "";
}
return cellValue;
}
本想以泛型实体list返回,但没想明白如何具体实现,仅作记录留作以后研究