poi表格读取

---

仅记录，供以后参考(专门为项目制作，请注意表格格式，仅供参考)

标题1 标题2 标题3 内容 内容 内容 内容 内容 内容 内容 内容 内容

读取区域范围实体

---

package gov.kchange.bean;import gov.kchange.config.ReadFileDefalutConfig;/** * 读取文件配置处理 */public class ReadFileBean {    private short titleLine;    private short startLine;    private short endLine;    private short fristRow;    private short endRow;    private short maxSize;    private boolean subSurfaceTetle;    public ReadFileBean(){        this.titleLine = ReadFileDefalutConfig.titleLine;        this.startLine = ReadFileDefalutConfig.startLine;        this.endLine = ReadFileDefalutConfig.endLine;        this.fristRow = ReadFileDefalutConfig.fristRow;        this.endRow = ReadFileDefalutConfig.endRow;        this.maxSize = ReadFileDefalutConfig.maxSize;        this.subSurfaceTetle = ReadFileDefalutConfig.subSurfaceTetle;    }    public ReadFileBean(short titleLine, short startLine, short endLine, short fristRow, short endRow,short maxSize,boolean subSurfaceTetle) {        this.titleLine = titleLine;        this.startLine = startLine;        this.endLine = endLine;        this.fristRow = fristRow;        this.endRow = endRow;        this.maxSize = maxSize;        this.subSurfaceTetle = subSurfaceTetle;    }    public short getTitleLine() {        return titleLine;    }    public void setTitleLine(short titleLine) {        this.titleLine = titleLine;    }    public short getStartLine() {        return startLine;    }    public void setStartLine(short startLine) {        this.startLine = startLine;    }    public short getEndLine() {        return endLine;    }    public void setEndLine(short endLine) {        this.endLine = endLine;    }    public short getFristRow() {        return fristRow;    }    public void setFristRow(short fristRow) {        this.fristRow = fristRow;    }    public short getEndRow() {        return endRow;    }    public void setEndRow(short endRow) {        this.endRow = endRow;    }    public short getMaxSize() {        return maxSize;    }    public void setMaxSize(short maxSize) {        this.maxSize = maxSize;    }    public boolean isSubSurfaceTetle() {        return subSurfaceTetle;    }    public void setSubSurfaceTetle(boolean subSurfaceTetle) {        this.subSurfaceTetle = subSurfaceTetle;    }}

excel读取类

package gov.kchange.util;import gov.kchange.bean.ReadFileBean;import org.apache.poi.openxml4j.exceptions.InvalidFormatException;import org.apache.poi.ss.usermodel.*;import java.io.IOException;import java.io.InputStream;import java.text.ParseException;import java.text.SimpleDateFormat;import java.util.*;import java.util.stream.Stream;public class ExcelConsole {//    //表格页面信息//    private Sheet sheet;//表格文件    private Workbook workbook;////    private Row row;//    private POIFSFileSystem fs;    //读取区域大小    private ReadFileBean readFileBean;    //当前文件的表头信息    private String[] title;    //当前表格页面数量和单页面行数    private int[] sheetNumber;    //获得剩余未读取数据总长度    private int dataNum;    @Override    public String toString() {        return "ExcelConsole{" +                "workbook=" + workbook +                ", readFileBean=" + readFileBean +                ", title=" + Arrays.toString(title) +                ", sheetNumber=" + Arrays.toString(sheetNumber) +                ", dataNum=" + dataNum +                '}';    }    /**     * 构造读取器(测试通过)     * inputStream:文件     * readFileBean：读取大小     * map:文件字段中英文对照名称     */    public ExcelConsole(InputStream inputStream, ReadFileBean readFileBean,Map<String,String> map) throws IOException, InvalidFormatException {        //存储表格区域信息        this.readFileBean=readFileBean;        //将表格读入缓存        this.workbook = WorkbookFactory.create(inputStream);        this.sheetNumber=new int[this.workbook.getNumberOfSheets()];        //记录表头信息        Row titlek=title();        //当没有表头时禁止继续执行        if(titlek==null||titlek.getPhysicalNumberOfCells()==0){            return;        }        this.title=new String[titlek.getPhysicalNumberOfCells()];        for(int i=0,len=this.title.length;i<len;i++){            if(title() == null || readCell(title().getCell(i)) == null || map.get(readCell(title().getCell(i))) == null){                this.title[i]="";                continue;            }            this.title[i]=map.get(readCell(title().getCell(i))).toString();        }        //记录数据总量        for(int i=0,len=sheetNumber.length;i<len;i++){            if(headerLeap(i)){                sheetNumber[i]=sheet(i).getLastRowNum()-readFileBean.getTitleLine()+1<0?0:sheet(i).getLastRowNum()-readFileBean.getTitleLine()+1;            }else{                sheetNumber[i]=sheet(i).getLastRowNum()+1;            }            this.dataNum+=sheetNumber[i];        }        this.dataNum+=readFileBean.getMaxSize();    }    /**     * 获取表头信息(测试通过)     */    private Row title(){        return sheet(0).getRow(readFileBean.getTitleLine()-1);    }    /**     * 获得第N页(测试通过)     */    private Sheet sheet(int i){        return workbook.getSheetAt(i);    }    /**     * 获取单行数据(测试通过)     * sheeti:页     * rowi:行     */    public Map<String, String> rowData(int sheeti,int rowi){        Sheet sheet=sheet(sheeti);        Row row = sheet.getRow(rowi);        Map<String, String> map = new HashMap<String, String>();        if(row==null){//判断本行是否为空            for (int fristRow = readFileBean.getFristRow(), endRow = title().getPhysicalNumberOfCells() - readFileBean.getEndRow();                 fristRow < endRow; fristRow++) {                map.put(title[fristRow], "");            }        }else {            for (int fristRow = readFileBean.getFristRow(), endRow = title().getPhysicalNumberOfCells() - readFileBean.getEndRow();                 fristRow < endRow; fristRow++) {                map.put(title[fristRow], readCell(row.getCell(fristRow)));            }        }        return map;    }    /**     *读取文件     */    public List<Map<String,String>> read(int sheeti,int starti,int maximum){        List<Map<String,String>> l=new ArrayList<Map<String,String>>();        //获取某一页面剩余条数        int surplus=surplus(sheeti,starti);        if(surplus>maximum){            Stream.iterate(starti, item -> item + 1).limit(maximum)                    .forEach(item->l.add(rowData(sheeti,item)));        }else{            Stream.iterate(starti, item -> item + 1).limit(surplus)                    .forEach(item->l.add(rowData(sheeti,item)));        }        return l;    }    /**     * 获取某页面剩余条数     */    public int surplus(int sheeti,int starti){        if(sheeti>=sheetNumber.length){            return 0;        }        if(headerLeap(sheeti)){            return sheetNumber[sheeti]-starti+readFileBean.getTitleLine();        }else{            return sheetNumber[sheeti]-starti;        }    }    /**     * 获取表格全部数据     *     */    public List<Map<String,String>>[] workbookAll(){        int k=dataNum;        List<Map<String,String>>[] list=new List[sheetNumber.length];        for(int i=0,len=list.length;i<len;i++){            list[i]=new ArrayList<Map<String,String>>();        }        while (iteration()){            list[readSheet()].addAll(next());        }        dataNum=k;        return list;    }    /**     * 迭代器     * 所有结果返回类型的基础方法（改动请慎重）     */    public boolean iteration() {        int sheeti=readSheet();        int starti=startRow();        int surplus=surplus(sheeti,starti);        if(surplus>readFileBean.getMaxSize()){            dataNum-=readFileBean.getMaxSize();        }else{            dataNum-=surplus;        }        if(dataNum>0){            dataNum=dataNum<0?0:dataNum;            return true;        }else{            return false;        }    }    public List<Map<String,String>> next(){        return read(readSheet(),startRow(),readFileBean.getMaxSize());    }    /**     * 获取已经读取的数据长度     */    public int readLen(){        int sun=0;        for(int i=0,len=sheetNumber.length;i<len;i++){            sun+=sheetNumber[i];        }        return sun-dataNum;    }    /**     * 获取读取页面     */    public int readSheet(){        int sheeti=0;        int sun=readLen();        for(int i=0,len=sheetNumber.length;i<len;i++,sheeti++){            sun-=sheetNumber[i];            if(sun<0){                break;            }        }        return sheeti;    }    /**     * 获取读取行     */    public int startRow(){        int sun=readLen();        for(int i=0,len=sheetNumber.length;i<len;i++){            sun-=sheetNumber[i];            if(sun<0){                if(headerLeap(readSheet())){                    return sun+sheetNumber[i]+readFileBean.getTitleLine();                }else{                    return sun+sheetNumber[i];                }            }        }        return 0;    }    /**     * 判断是否跳过表头     */    private boolean headerLeap(int sheeti){        if(sheeti>0&&!readFileBean.isSubSurfaceTetle())            return false;            return true;    }    /**     * 读取单元格数据的文本格式     */    private String readCell(Cell cell) {        String value = "";        if(cell==null){            return "";        }        switch (cell.getCellType()) {            case Cell.CELL_TYPE_NUMERIC: // 数字                //如果为时间格式的内容                if (DateUtil.isCellDateFormatted(cell)) {//                    SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd hh:mm:ss");//                    value = sdf.format(DateUtil.getJavaDate(cell.getNumericCellValue())).toString();//                    时间类型详细读取(表格中自定义时间类型表达方式)//                    cell.getCellStyle().getDataFormat()//                    只取前4种情况（格式代码超过176为自义定格式（暂不接受）)//                    HH:mm:ss----------21//                    yyyy/MM/dd-----14//                    //yyyy-MM-dd-----177，182，182//                    yyyy/MM/dd HH:mm:ss---22//                    yyyy-MM-dd HH:mm:ss---22//                    yyyy年m月-------    57//                    m月d日  ----------58//                    HH:mm-----------  20//                    h时mm分  -------    32                    switch (cell.getCellStyle().getDataFormat()){                        case 21:                            SimpleDateFormat sdf21 = new SimpleDateFormat("HH:mm:ss");                            value = sdf21.format(DateUtil.getJavaDate(cell.getNumericCellValue())).toString();                            break;                        case 14:                            SimpleDateFormat sdf14 = new SimpleDateFormat("yyyy-MM-dd");                            value = sdf14.format(DateUtil.getJavaDate(cell.getNumericCellValue())).toString();                            break;                        case 22:                            SimpleDateFormat sdf22 = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");                            value = sdf22.format(DateUtil.getJavaDate(cell.getNumericCellValue())).toString();                            break;                        default:                            SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");                            value = sdf.format(DateUtil.getJavaDate(cell.getNumericCellValue())).toString();                            break;                    }                    break;                } else {                    cell.setCellType(Cell.CELL_TYPE_STRING);                    value = cell.getStringCellValue();//                    value = new DecimalFormat("0").format(cell.getNumericCellValue());                }                break;            case Cell.CELL_TYPE_STRING: // 字符串                value = cell.getStringCellValue();                break;            case Cell.CELL_TYPE_BOOLEAN: // Boolean                value = cell.getBooleanCellValue() + "";                break;            case Cell.CELL_TYPE_FORMULA: // 公式                cell.setCellType(Cell.CELL_TYPE_STRING);                value = cell.getStringCellValue();//cell.getCellFormula() + "";                break;            case Cell.CELL_TYPE_BLANK: // 空值                value = "";                break;            case Cell.CELL_TYPE_ERROR: // 故障                value = "";                break;            default:                value = "";                break;        }        if(value==null){            return "";        }//        System.out.println(value+"="+cell.getCellStyle().getDataFormat());        return value.trim();    }}

使用方式

• 构造读取类

ExcelConsole excelConsole = null;        try {            excelConsole = new ExcelConsole(fileInput, new ReadFileBean() {{                setTitleLine((short) 2);//表头所占行数（表头不读）                setMaxSize((short) 3000);//每次迭代数据量                setSubSurfaceTetle(true);//第二页是否有表头                //其他参数参考实体自行实现（我偷懒没有实现其他参数）            }}, m);//m代表读取头部信息与需要的重命名方式        } catch (IOException e) {            e.printStackTrace();        } catch (InvalidFormatException e) {            e.printStackTrace();        }

• 分页读取

//因实现此方式时偷懒利用了迭代读取所以此读取方式只能在迭代之前执行List<Map<String, String>>[] ldata = excelConsole.workbookAll();

• 迭代读取

for(;excelConsole.iteration();){List<Map<String,String>> var=excelConsole.next();...}