Excel解析为java对象

---

1 需要上传Excel例子

2 解析成功图片

3 上传页面

3.1 代码

pom.xml需要加入,文件上传需要的的file-upload.jar和解析Exl的poi.jar
pom.xml

<dependency>  
    <groupId>org.apache.poi</groupId>  
    <artifactId>poi</artifactId>  
    <version>3.8</version>  
    <exclusions>  
        <exclusion>  
            <artifactId>commons-codec</artifactId>  
            <groupId>commons-codec</groupId>  
        </exclusion>  
    </exclusions>  
</dependency>  
<dependency>  
    <groupId>org.apache.poi</groupId>  
    <artifactId>poi-ooxml</artifactId>  
    <version>3.8</version>  
</dependency>  
        <!-- https://mvnrepository.com/artifact/com.github.davidcarboni/encrypted-file-upload -->
<dependency>
    <groupId>com.github.davidcarboni</groupId>
    <artifactId>encrypted-file-upload</artifactId>
    <version>1.0.2</version>
</dependency>

UserAction.java

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestMethod;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.bind.annotation.ResponseBody;
import org.springframework.web.multipart.MultipartFile;

import com.ccd.user.service.UserService;

@Controller
@RequestMapping("user")
public class UserAction {

    @Autowired
    private UserService userService;

    @RequestMapping(value = "/upload", method = RequestMethod.POST)
    @ResponseBody
    public String upload(@RequestParam(value = "file", required = false) MultipartFile file, HttpServletRequest request,
            HttpServletResponse response) {
        // 这里今天controller层
        String result = userService.readExcelFile(file);
        return result;

    }
}

UserService.java

import org.springframework.web.multipart.MultipartFile;

public interface UserService {

    /** 
     * 读取excel中的数据,生成list 
     */  
    String readExcelFile( MultipartFile file);  
}

MeetingRoomServiceImpl.java

import java.util.List;

import org.springframework.stereotype.Service;
import org.springframework.web.multipart.MultipartFile;

import com.ccd.user.model.UserTest;
import com.ccd.user.service.UserService;
import com.ccd.util.ReadExcel;


@Service  
public class MeetingRoomServiceImpl implements UserService{
    @Override  
    public String readExcelFile(MultipartFile file) {  
        String result ="";  
        //创建处理EXCEL的类  
        ReadExcel readExcel=new ReadExcel();  
        //解析excel，获取上传的事件单  
        List<UserTest> useList = readExcel.getExcelInfo(file);  
        //至此已经将excel中的数据转换到list里面了,接下来就可以操作list,可以进行保存到数据库,或者其他操作,  
        //和你具体业务有关,这里不做具体的示范  
        if(useList != null && !useList.isEmpty()){  
            result = "chenggong";  
        }else{  
            result = "shibai";  
        }  
        return result;  
    }  

}

ReadExcel.java

import java.io.IOException;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.List;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.web.multipart.MultipartFile;

import com.ccd.user.model.UserTest;


public class ReadExcel {  
    //总行数  
    private int totalRows = 0;    
    //总条数  
    private int totalCells = 0;   
    //错误信息接收器  
    private String errorMsg;  
    //构造方法  
    public ReadExcel(){}  
    //获取总行数  
    public int getTotalRows()  { return totalRows;}   
    //获取总列数  
    public int getTotalCells() {  return totalCells;}   
    //获取错误信息  
    public String getErrorInfo() { return errorMsg; }    

  /** 
   * 读EXCEL文件，获取信息集合 
   * @param fielName 
   * @return 
   */  
    public List<UserTest> getExcelInfo(MultipartFile mFile) {  
        String fileName = mFile.getOriginalFilename();//获取文件名  
        List<UserTest> userList=null;
        try {  
            if (!validateExcel(fileName)) {// 验证文件名是否合格  
                return null;  
            }  
            boolean isExcel2003 = true;// 根据文件名判断文件是2003版本还是2007版本  
            if (isExcel2007(fileName)) {  
                isExcel2003 = false;  
            }  
            userList = createExcel(mFile.getInputStream(), isExcel2003);  
        } catch (Exception e) {  
            e.printStackTrace();  
        }  
        System.out.println("ReadExcel工具类类 文件名  :"+fileName);
        System.out.println(userList);
        return userList;  
    }  

  /** 
   * 根据excel里面的内容读取客户信息 
   * @param is 输入流 
   * @param isExcel2003 excel是2003还是2007版本 
   * @return 
   * @throws IOException 
   */  
    public List<UserTest> createExcel(InputStream is, boolean isExcel2003) {  
        List<UserTest> userList=null;
        try{  
            Workbook wb = null;  
            if (isExcel2003) {// 当excel是2003时,创建excel2003  
                wb = new HSSFWorkbook(is);  
            } else {// 当excel是2007时,创建excel2007  
                wb = new XSSFWorkbook(is);  
            }  
            userList = readExcelValue(wb);// 读取Excel里面客户的信息  
        } catch (IOException e) {  
            e.printStackTrace();  
        }  
        System.out.println("根据excel里面的内容读取客户信息 ReadExcel工具类类 文件名  :");
        System.out.println(userList);
        return userList;  
    }  

  /** 
   * 读取Excel里面客户的信息 
   * @param wb 
   * @return 
   */  
    private List<UserTest> readExcelValue(Workbook wb) {  
        // 得到第一个shell  
        Sheet sheet = wb.getSheetAt(0);  
        // 得到Excel的行数  
        this.totalRows = sheet.getPhysicalNumberOfRows();  
        // 得到Excel的列数(前提是有行数)  
        if (totalRows > 1 && sheet.getRow(0) != null) {  
            this.totalCells = sheet.getRow(0).getPhysicalNumberOfCells();  
        }  
        List<UserTest> userList = new ArrayList<UserTest>();  
        // 循环Excel行数  
        for (int r = 1; r < totalRows; r++) {  
            Row row = sheet.getRow(r);  
            if (row == null){  
                continue;  
            }  
            UserTest user = new UserTest();  
            // 循环Excel的列  
            for (int c = 0; c < this.totalCells; c++) {  
                Cell cell = row.getCell(c);  
                if (null != cell) {  
                    if (c == 0) {  
                        //如果是纯数字,比如你写的是25,cell.getNumericCellValue()获得是25.0,通过截取字符串去掉.0获得25  
                        if(cell.getCellType() == HSSFCell.CELL_TYPE_NUMERIC){  
                            String name = String.valueOf(cell.getNumericCellValue());  
                            user.setName(name.substring(0, name.length()-2>0?name.length()-2:1));//名称  
                        }else{  
                            user.setName(cell.getStringCellValue());//名称  
                        }  
                    } else if (c == 1) {  
                        if(cell.getCellType() == HSSFCell.CELL_TYPE_NUMERIC){  
                            String sex = String.valueOf(cell.getNumericCellValue());  
                            user.setSex(sex.substring(0, sex.length()-2>0?sex.length()-2:1));//性别  
                        }else{  
                            user.setSex(cell.getStringCellValue());//性别  
                        }  
                    } else if (c == 2){  
                        if(cell.getCellType() == HSSFCell.CELL_TYPE_NUMERIC){  
                            String age = String.valueOf(cell.getNumericCellValue());  
                            user.setAge(age.substring(0, age.length()-2>0?age.length()-2:1));//年龄  
                        }else{  
                            user.setAge(cell.getStringCellValue());//年龄  
                        }  
                    }  
                }  
            }  
            // 添加到list  
            userList.add(user);  
        }  
        return userList;  
    }  

    /** 
     * 验证EXCEL文件 
     *  
     * @param filePath 
     * @return 
     */  
    public boolean validateExcel(String filePath) {  
        if (filePath == null || !(isExcel2003(filePath) || isExcel2007(filePath))) {  
            errorMsg = "文件名不是excel格式";  
            return false;  
        }  
        return true;  
    }  

    // @描述：是否是2003的excel，返回true是2003   
    public static boolean isExcel2003(String filePath)  {    
         return filePath.matches("^.+\\.(?i)(xls)$");    
     }    

    //@描述：是否是2007的excel，返回true是2007   
    public static boolean isExcel2007(String filePath)  {    
         return filePath.matches("^.+\\.(?i)(xlsx)$");    
     }    
}