poi读取Excel总结

package eiis.controller.app.sbz;import eiis.app.sbz.entity.AppWatStationEntity;import eiis.app.sbz.service.IAppWatStationService;import eiis.app.sbz.util.ReadAndWriteExcelUtil;import jxl.read.biff.BiffException;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.poifs.filesystem.POIFSFileSystem;import org.apache.poi.ss.usermodel.*;import org.apache.poi.xssf.usermodel.XSSFRow;import org.apache.poi.xssf.usermodel.XSSFWorkbook;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.ResponseBody;import javax.servlet.http.HttpServletRequest;import java.io.*;import java.math.BigDecimal;import java.time.Instant;import java.util.*;/** * Created by Administrator on 2017/3/8. */@RequestMapping("/sbz/")@Controller("/controller/app/sbz/AppWatStationController")public class AppWatStationController {    @Autowired    private IAppWatStationService iAppWatStationService;    public List<AppWatStationEntity> appWatStationEntity = new ArrayList();    @ResponseBody    @RequestMapping("appWatStation_readExcel")    public String readFile(AppWatStationEntity appWatStationEntity, String data) throws IOException, BiffException {        return "SUCCESS";    }  /*  @RequestMapping(value = "readExcel", method = RequestMethod.POST, params = {"filename", "suffix"})    @ResponseBody*/    public String readexcel(HttpServletRequest request) throws Exception {        Workbook wb = null;        String filename = request.getParameter("filename");        String suffix = request.getParameter("suffix");        String filepath = "D://"+filename;        InputStream is = new FileInputStream(filepath);        if(".xls".equals(suffix)){             wb = new HSSFWorkbook(is);            System.out.println("文件格式为.xls");        }else if(".xlsx".equals(suffix)){            wb = new XSSFWorkbook(is);            System.out.println("文件格式为.xlsx");        }else {            System.out.println("文件格式错误！");        }        List<List<Map<String, String>>> result = new ArrayList<List<Map<String,String>>>();//对应excel文件        int sheetSize = wb.getNumberOfSheets();        System.out.println(sheetSize);        for (int i = 0; i < sheetSize; i++) {//遍历sheet页            Sheet sheet = wb.getSheetAt(i);            List<Map<String, String>> sheetList = new ArrayList<Map<String, String>>();//对应sheet页            List<String> titles = new ArrayList<String>();//放置所有的标题            int rowSize = sheet.getLastRowNum() + 1;            for (int j = 1; j < rowSize; j++) {//遍历行                Row row = sheet.getRow(j);                if (row == null) {//略过空行                    continue;                }                int cellSize = row.getLastCellNum();//行中有多少个单元格，也就是有多少列                if (j == 0 || j==1) {//第一行是标题行                    for (int k = 0; k < cellSize; k++) {                        Cell cell = row.getCell(k);                        titles.add(cell.toString());                    }                } else {//其他行是数据行                    Map<String, String> rowMap = new HashMap<String, String>();//对应一个数据行                    for (int k = 0; k < titles.size(); k++) {                        Cell cell = row.getCell(k);                        String key = titles.get(k);                        String value = null;                        if (cell != null) {                            value = cell.toString();                        }                        rowMap.put(key, value);                    }                    sheetList.add(rowMap);                }            }            System.out.println(sheetList);            result.add(sheetList);        }        return null;    }    @RequestMapping(value = "readExcel", method = RequestMethod.POST, params = {"filename", "suffix"})    @ResponseBody    public String  readExcelWithoutTitle(HttpServletRequest request) throws Exception{        Workbook wb = null;        String filename = request.getParameter("filename");        String suffix = request.getParameter("suffix");        String filepath = "D://"+filename;        InputStream is = new FileInputStream(filepath);        if(".xls".equals(suffix)){            wb = new HSSFWorkbook(is);            System.out.println("文件格式为.xls");        }else if(".xlsx".equals(suffix)){            wb = new XSSFWorkbook(is);            System.out.println("文件格式为.xlsx");        }else {            System.out.println("文件格式错误！");        }        List<List<List<String>>> result = new ArrayList<List<List<String>>>();//对应excel文件        int sheetSize = wb.getNumberOfSheets();        for (int i = 0; i < sheetSize; i++) {//遍历sheet页            Sheet sheet = wb.getSheetAt(i);            List<List<String>> sheetList = new ArrayList<List<String>>();//对应sheet页            int rowSize = sheet.getLastRowNum() + 1;            for (int j = 3; j < rowSize; j++) {//遍历行                Row row = sheet.getRow(j);                if (row == null) {//略过空行                    continue;                }                int cellSize = row.getLastCellNum();//行中有多少个单元格，也就是有多少列                List<String> rowList = new ArrayList<String>();//对应一个数据行                for (int k = 0; k < cellSize; k++) {                    Cell cell = row.getCell(k);                    String value = null;                    if (cell != null) {                        value = cell.toString();                        if (k==2 && value!=null){                          String string =  row.getCell(2).getCellComment().toString();                        }                    }                    rowList.add(value);                }                sheetList.add(rowList);            }            System.out.println(sheetList);            result.add(sheetList);        }            return null;    }}