!!!不适合新手看!!!因为我只是为了记录下这种写法,所以我也没花很多力气,把这个复杂的解析改成简单的!所以不好意思!
@RequestMapping(value = "uploadData/{subjectId}", method = RequestMethod.POST, produces = "application/json; charset=utf-8")
@ResponseBody
public String distribution(HttpServletRequest request,@PathVariable int subjectId) throws IOException {
MessageInfo messageInfo=new MessageInfo();
long startTime=System.currentTimeMillis();
//将当前上下文初始化给 CommonsMutipartResolver (多部分解析器)
CommonsMultipartResolver multipartResolver=new CommonsMultipartResolver(
request.getSession().getServletContext());
//检查form中是否有enctype="multipart/form-data"
if(multipartResolver.isMultipart(request))
{
//将request变成多部分request
MultipartHttpServletRequest multiRequest=(MultipartHttpServletRequest)request;
//获取multiRequest 中所有的文件名
Iterator iter=multiRequest.getFileNames();
while(iter.hasNext())
{
//一次遍历所有文件
MultipartFile file=multiRequest.getFile(iter.next().toString());
if(file!=null)
{
messageInfo=getExcel(file);
if(messageInfo.getStatus()!=MessageStatus.OK.getStatus()){
return JSON.toJSONString(messageInfo);
}
List list=(List)messageInfo.getData();
messageInfo=subjectService.updateSmoothDate(list,subjectId,messageInfo.getMessage());
}
}
}
return JSON.toJSONString(messageInfo);
}
/**
* 将excel文件转化为List<List<String>>对象
* @param file
* @return
*/
public MessageInfo getExcel(MultipartFile file) throws IOException {
int computationTimeIndex=-1;//计算时间在第几列
int typeIndex=-1;//品类或者六位码在第几列
int modifyTimeIndex=-1;//修改时间在第几列
int num=0;
int numComputationTime=0;
int nummodifyTime=0;
List list=new ArrayList();
String condition="";
MessageInfo messageInfo=new MessageInfo();
try {
InputStream inputStream=file.getInputStream();
XSSFWorkbook hssfWorkbook=new XSSFWorkbook(inputStream);
List<List<String>> result=new ArrayList<List<String>>();
/*循环每一页,并处理当前循环页*/
for (int numSheet=0;numSheet<hssfWorkbook.getNumberOfSheets();numSheet++){
XSSFSheet hssfSheet=hssfWorkbook.getSheetAt(numSheet);//表示某一页
if(hssfSheet==null){
continue;
}
/*处理当前页,循环每一行*/
for(int rowNum=0;rowNum<=hssfSheet.getLastRowNum();rowNum++){
XSSFRow hssfRow=hssfSheet.getRow(rowNum);//表示行
int minColIx=hssfRow.getFirstCellNum();
int maxColIx=hssfRow.getLastCellNum();
PbmsOrderRst pbmsOrderRst=new PbmsOrderRst();
/*遍历该行,获取处理每个cell元素*/
for(int colIx=minColIx;colIx<maxColIx;colIx++){
/*判断单元格数据不能为空*/
if(null==hssfRow.getCell(colIx)||hssfRow.getCell(colIx).equals("")){
messageInfo.setStatusAndMessage(MessageStatus.ERROR.getStatus(),"单元格存在空数据!");
return messageInfo;
}
XSSFCell cell=hssfRow.getCell(colIx);
String value=getStringVal(cell,colIx,typeIndex);//每一个单元格的值
//截图小数点,取整数
if(colIx==typeIndex){
String[] valuses=value.split("\\.");
value=valuses[0];
}
if(rowNum==0){
if(value.equals("计算时间")){
computationTimeIndex=colIx;
numComputationTime++;
}
if(value.equals("品类")){
typeIndex=colIx;
condition="CLASSIFY";
num++;
}
if(value.equals("六位码")){
typeIndex=colIx;
condition="PROD_CLS_ID";
num++;
}
if(value.equals("更新时间")){
modifyTimeIndex=colIx;
nummodifyTime++;
}
if(colIx==maxColIx-1){
if(num!=1 || numComputationTime!=1 || nummodifyTime!=1){
messageInfo.setStatusAndMessage(MessageStatus.ERROR.getStatus(),"excel表格结构不符合要求,请仔细检查列名");
return messageInfo;
}
}
}else{
/*计算时间*/
if(colIx==computationTimeIndex){
Date resultDate=stringToDate(value);
pbmsOrderRst.setAllotDate(resultDate);
if(null==resultDate){
messageInfo.setStatusAndMessage(MessageStatus.ERROR.getStatus(),"excel表中存在非法时间格式");
return messageInfo;
}
}
/*品类或者六位码*/
if(colIx==typeIndex){
if(condition.equals("CLASSIFY")){
pbmsOrderRst.setClassify(value);
}
if(condition.equals("PROD_CLS_ID")){
pbmsOrderRst.setProdClsId(value);
}
}
/*更新时间*/
if(colIx==modifyTimeIndex){
Date resultDate=stringToDate(value);
pbmsOrderRst.setSmoothDate(resultDate);
if(null==resultDate){
messageInfo.setStatusAndMessage(MessageStatus.ERROR.getStatus(),"excel表中存在非法时间格式");
return messageInfo;
}
}
}
}
if(rowNum!=0){
list.add(pbmsOrderRst);
}
}
}
messageInfo.setData(list);
messageInfo.setMessage(condition);//传一个参数判断是六位码还是品类
return messageInfo;
} catch (Exception e) {
e.printStackTrace();
messageInfo.setStatusAndMessage(MessageStatus.ERROR.getStatus(),"解析excel表出现异常"+e.getMessage());
return messageInfo;
}
}
/**
* string转时间
* @param date
* @return
*/
public Date stringToDate(String date) {
SimpleDateFormat df =null;
Date resultDate=new Date();
if(date.indexOf("-")!=-1){
df = new SimpleDateFormat("yyyy-MM-dd");//设置日期格式
}else if(date.indexOf("/")!=-1){
df = new SimpleDateFormat("yyyy/MM/dd");//设置日期格式
}else{
return resultDate=null;
}
try {
return df.parse(date);
} catch (ParseException e) {
e.printStackTrace();
return resultDate=null;
}
}
/**
* 将excel中的数据格式化一下
* @param cell
* @return
*/
public String getStringVal(XSSFCell cell,int colIx,int typeIndex){
String resultCell="";
switch (cell.getCellType()) {
case Cell.CELL_TYPE_BOOLEAN:
return cell.getBooleanCellValue() ? "TRUE" : "FALSE";
case Cell.CELL_TYPE_FORMULA:
return cell.getCellFormula();
case Cell.CELL_TYPE_NUMERIC:
/*判断是否为时间格式*/
if (HSSFDateUtil.isCellDateFormatted(cell)) {
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
Date date = cell.getDateCellValue();
resultCell = sdf.format(date);
return resultCell;
}
cell.setCellType(cell.CELL_TYPE_STRING);
return cell.getStringCellValue();
case Cell.CELL_TYPE_STRING:
return cell.getStringCellValue();
default:
return "";
}
}