SSM POI execle 导入篇 （含工具类）

Controller

package com.jk.poiExcel.controller;import java.io.IOException;import java.io.InputStream;import java.util.ArrayList;import java.util.List;import javax.servlet.http.HttpServletRequest;import javax.servlet.http.HttpServletResponse;import org.apache.commons.lang3.StringUtils;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.RequestParam;import org.springframework.web.multipart.MultipartFile;import com.jk.file.service.IFileService;import com.jk.login.bean.TAtyUser;import com.jk.poiExcel.excelDel.ReadExcel;@Controllerpublic class ImportExcel {@Autowiredprivate IFileService fileServiceImpl;@RequestMapping("/importExcel")public String importExcel(@RequestParam(value = "file_upload2", required = false) MultipartFile file,HttpServletRequest request, HttpServletResponse response) throws IOException {String filename = file.getOriginalFilename();if (StringUtils.isNotBlank(filename)) {String name = filename.substring(filename.lastIndexOf(".")).toLowerCase();// 截取后缀名，并全部转小写InputStream in = file.getInputStream();// 定义输入流；List<TAtyUser> userlist = new ArrayList<TAtyUser>();// 这里泛型TAtyUser即是Bean载体// 判断上传的execle文件的版本是xls还是xlsx格式，走不同读取流的方法if (".xlsx".equals(name)) {userlist = ReadExcel.readXlsx(in);// 从execle读取单元格数据，存入list}if (".xls".equals(name)) {userlist = ReadExcel.readXls(in);//通过readXls已经读取到execle中的值了，通过for循环输出验证一下//这里可以直接调用Service，将list对象持久化到数据库，就不演示了for (TAtyUser tAtyUser : userlist) {System.out.println(tAtyUser.getcName());}}}return "sy/weclome";//执行完毕跳页面，没什么特别意义}/*@RequestMapping("/importExcel1")public ModelAndView importExcel1(@RequestParam(value = "file1", required = false) MultipartFile file,HttpServletRequest request, HttpServletResponse response) throws IOException {// 判断文件的格式String filename = file.getOriginalFilename();//if (StringUtils.isNotBlank(filename)) {//String name = filename.substring(filename.lastIndexOf(".")).toLowerCase();// 将文件解析为流InputStream in = file.getInputStream();List<TAtyUser> userlist = new ArrayList<TAtyUser>();// 调用相应的解析方法if (".xlsx".equals(name)) {userlist = ReadExcel.readXlsx(in);}if (".xls".equals(name)) {userlist = ReadExcel.readXls(in);System.out.println(userlist.size());}}ModelAndView mav = new ModelAndView();mav.setViewName("sy/weclome");return mav;}*/}

Util

package com.jk.poiExcel.excelDel;import java.io.FileInputStream;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.HSSFRow;import org.apache.poi.hssf.usermodel.HSSFSheet;import org.apache.poi.hssf.usermodel.HSSFWorkbook;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 com.jk.login.bean.TAtyUser;import com.jk.poiExcel.common.Common;/**  * @author Hongten * @created 2014-5-20  */ public class ReadExcel {        /**32      * read the Excel file33      * @param path the path of the Excel file34      * @return35      * @throws IOException36      *///     public List<TAtyUser> readExcel(String path) throws IOException {//        if (path == null || Common.EMPTY.equals(path)) {//            return null;//         } else {//             String postfix = PathUtil.getPostfix(path);//             if (!Common.EMPTY.equals(postfix)) {//                if (Common.OFFICE_EXCEL_2003_POSTFIX.equals(postfix)) {//                    return readXls(path);//                } else if (Common.OFFICE_EXCEL_2010_POSTFIX.equals(postfix)) {//                   return readXlsx(path);//                }//             } else {//               System.out.println(path + Common.NOT_EXCEL_FILE);//           }//        }//        return null;//     }      /**      * Read the Excel 2010     * @param path the path of the excel file      * @return      * @throws IOException      */    public static List<TAtyUser> readXlsx( InputStream is) throws IOException {         XSSFWorkbook xssfWorkbook = new XSSFWorkbook(is);         TAtyUser user = null;//从哪一张表映射的实体类创建对象         List<TAtyUser> list = new ArrayList<TAtyUser>();        // Read the Sheet        for (int numSheet = 0; numSheet < xssfWorkbook.getNumberOfSheets(); numSheet++) {           //来获取sheet工作表根据下标        XSSFSheet xssfSheet = xssfWorkbook.getSheetAt(numSheet);            if (xssfSheet == null) {               continue;           }            //从单元格循环读取取值，并付给对象            for (int rowNum = 1; rowNum <= xssfSheet.getLastRowNum(); rowNum++) {                XSSFRow xssfRow = xssfSheet.getRow(rowNum);                if (xssfRow != null) {                user = new TAtyUser();                    XSSFCell name = xssfRow.getCell(0);                    XSSFCell loginID = xssfRow.getCell(1);                    XSSFCell email = xssfRow.getCell(2);                    user.setcName(getValue(name));                    user.setcLoginid(getValue(loginID));                    user.setcMail(getValue(email) );                    list.add(user);                 }             }        }       return list;    }    /**     * Read the Excel 2003-2007     * @param path the path of the Excel      * @return      * @throws IOException      */    public static List<TAtyUser> readXls(InputStream is) throws IOException {        HSSFWorkbook hssfWorkbook = new HSSFWorkbook(is);        TAtyUser user = null;        List<TAtyUser> list = new ArrayList<TAtyUser>();        for (int numSheet = 0; numSheet < hssfWorkbook.getNumberOfSheets(); numSheet++) {            HSSFSheet hssfSheet = hssfWorkbook.getSheetAt(numSheet);            if (hssfSheet == null) {                continue;            }           for (int rowNum = 1; rowNum <= hssfSheet.getLastRowNum(); rowNum++) {                HSSFRow hssfRow = hssfSheet.getRow(rowNum);                if (hssfRow != null) {                     user = new TAtyUser();                     HSSFCell name = hssfRow.getCell(0);                     HSSFCell loginID = hssfRow.getCell(1);                     HSSFCell email = hssfRow.getCell(2);                     user.setcName(getValue(name));                     user.setcLoginid(getValue(loginID));                     user.setcMail(getValue(email) );                     list.add(user);                }           }        }        return list;     }     @SuppressWarnings("static-access")     private static String getValue(XSSFCell xssfRow) {         if (xssfRow.getCellType() == xssfRow.CELL_TYPE_BOOLEAN) {           return String.valueOf(xssfRow.getBooleanCellValue());        } else if (xssfRow.getCellType() == xssfRow.CELL_TYPE_NUMERIC) {             return String.valueOf(xssfRow.getNumericCellValue());        } else {            return String.valueOf(xssfRow.getStringCellValue());        }    }     @SuppressWarnings("static-access")     private static String getValue(HSSFCell hssfCell) {        if (hssfCell.getCellType() == hssfCell.CELL_TYPE_BOOLEAN) {             return String.valueOf(hssfCell.getBooleanCellValue());        } else if (hssfCell.getCellType() == hssfCell.CELL_TYPE_NUMERIC) {            return String.valueOf(hssfCell.getNumericCellValue());         } else {             return String.valueOf(hssfCell.getStringCellValue());         }    }}