# exam

**Repository Path**: ltdev/exam

## Basic Information

- **Project Name**: exam
- **Description**: EXAM
- **Primary Language**: Java
- **License**: MIT
- **Default Branch**: master
- **Homepage**: None
- **GVP Project**: No

## Statistics

- **Stars**: 0
- **Forks**: 0
- **Created**: 2021-11-24
- **Last Updated**: 2022-10-14

## Categories & Tags

**Categories**: Uncategorized

**Tags**: None

## README

## 平台简介

https://blog.51cto.com/u_12302929/3289969

https://www.jianshu.com/p/30d21dba7cb4

package com.sjky.platform.myapp.importData.utils;

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

import org.apache.poi.hssf.eventusermodel.EventWorkbookBuilder.SheetRecordCollectingListener;
import org.apache.poi.hssf.eventusermodel.FormatTrackingHSSFListener;
import org.apache.poi.hssf.eventusermodel.HSSFEventFactory;
import org.apache.poi.hssf.eventusermodel.HSSFListener;
import org.apache.poi.hssf.eventusermodel.HSSFRequest;
import org.apache.poi.hssf.eventusermodel.MissingRecordAwareHSSFListener;
import org.apache.poi.hssf.eventusermodel.dummyrecord.LastCellOfRowDummyRecord;
import org.apache.poi.hssf.eventusermodel.dummyrecord.MissingCellDummyRecord;
import org.apache.poi.hssf.model.HSSFFormulaParser;
import org.apache.poi.hssf.record.BOFRecord;
import org.apache.poi.hssf.record.BlankRecord;
import org.apache.poi.hssf.record.BoolErrRecord;
import org.apache.poi.hssf.record.BoundSheetRecord;
import org.apache.poi.hssf.record.FormulaRecord;
import org.apache.poi.hssf.record.LabelRecord;
import org.apache.poi.hssf.record.LabelSSTRecord;
import org.apache.poi.hssf.record.NumberRecord;
import org.apache.poi.hssf.record.Record;
import org.apache.poi.hssf.record.SSTRecord;
import org.apache.poi.hssf.record.StringRecord;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;

import com.sjky.platform.myapp.importData.service.impl.importDataServiceimpl;

public  class Excel2003Reader implements HSSFListener{
    private int minColumns = -1;
    private POIFSFileSystem fs;
    private int lastRowNumber;
    private int lastColumnNumber;
    /** Should we output the formula, or the value it has? */
    private boolean outputFormulaValues = true;
    /** For parsing Formulas */
    private SheetRecordCollectingListener workbookBuildingListener;
    //excel2003工作薄
    private HSSFWorkbook stubWorkbook;
    // Records we pick up as we process
    private SSTRecord sstRecord;
    private FormatTrackingHSSFListener formatListener;
    //表索引
    private int sheetIndex = -1;
    private BoundSheetRecord[] orderedBSRs;
    @SuppressWarnings("unchecked")
    private ArrayList boundSheetRecords = new ArrayList();
    // For handling formulas with string results
    private int nextRow;
    private int nextColumn;
    private boolean outputNextStringRecord;
    //当前行
    private int curRow = 0;
    //存储行记录的容器
    private List<String> rowlist = new ArrayList<String>();;
    @SuppressWarnings( "unused")
    private String sheetName;
    /**
     * 遍历excel下所有的sheet
     * @throws IOException
     */
    public void process(String fileName) throws IOException {
           this.fs = new POIFSFileSystem(new FileInputStream(fileName));
           MissingRecordAwareHSSFListener listener = new MissingRecordAwareHSSFListener(
                           this);
           formatListener = new FormatTrackingHSSFListener(listener);
           HSSFEventFactory factory = new HSSFEventFactory();
           HSSFRequest request = new HSSFRequest();
           if (outputFormulaValues) {
                   request.addListenerForAllRecords(formatListener);
           } else {
                   workbookBuildingListener = new SheetRecordCollectingListener(
                                   formatListener);
                   request.addListenerForAllRecords(workbookBuildingListener);
           }
           factory.processWorkbookEvents(request, fs);
    }
    /**
     * HSSFListener 监听方法，处理 Record
     */
    @SuppressWarnings("unchecked")
    public void processRecord(Record record) {
           int thisRow = -1;
           int thisColumn = -1;
           String thisStr = null;
           String value = null;
           switch (record.getSid()) {
                   case BoundSheetRecord.sid:
                           boundSheetRecords.add(record);
                           break;
                   case BOFRecord.sid:
                           BOFRecord br = (BOFRecord) record;
                           if (br.getType() == BOFRecord.TYPE_WORKSHEET) {
                                  // 如果有需要，则建立子工作薄
                                  if (workbookBuildingListener != null && stubWorkbook == null) {
                                          stubWorkbook = workbookBuildingListener
                                                         .getStubHSSFWorkbook();
                                  }
                                  sheetIndex++;
                                  if (orderedBSRs == null) {
                                          orderedBSRs = BoundSheetRecord
                                                         .orderByBofPosition(boundSheetRecords);
                                  }
                                  sheetName = orderedBSRs[sheetIndex].getSheetname();
                           }
                           break;
                   case SSTRecord.sid:
                           sstRecord = (SSTRecord) record;
                           break;
                   case BlankRecord.sid:
                           BlankRecord brec = (BlankRecord) record;
                           thisRow = brec.getRow();
                           thisColumn = brec.getColumn();
                           thisStr = "";
                           rowlist.add(thisColumn, thisStr);
                           break;
                   case BoolErrRecord.sid: //单元格为布尔类型
                           BoolErrRecord berec = (BoolErrRecord) record;
                           thisRow = berec.getRow();
                           thisColumn = berec.getColumn();
                           thisStr = berec.getBooleanValue()+"";
                           rowlist.add(thisColumn, thisStr);
                           break;
                   case FormulaRecord.sid: //单元格为公式类型
                           FormulaRecord frec = (FormulaRecord) record;
                           thisRow = frec.getRow();
                           thisColumn = frec.getColumn();
                           if (outputFormulaValues) {
                                  if (Double.isNaN(frec.getValue())) {
                                          // Formula result is a string
                                          // This is stored in the next record
                                          outputNextStringRecord = true;
                                          nextRow = frec.getRow();
                                          nextColumn = frec.getColumn();
                                  } else {
                                          thisStr = formatListener.formatNumberDateCell(frec);
                                  }
                           } else {
                                  thisStr = '"' + HSSFFormulaParser.toFormulaString(stubWorkbook,
                                                 frec.getParsedExpression()) + '"';
                           }
                           rowlist.add(thisColumn,thisStr);
                           break;
                   case StringRecord.sid://单元格中公式的字符串
                           if (outputNextStringRecord) {
                                  // String for formula
                                  StringRecord srec = (StringRecord) record;
                                  thisStr = srec.getString();
                                  thisRow = nextRow;
                                  thisColumn = nextColumn;
                                  outputNextStringRecord = false;
                           }
                           break;
                   case LabelRecord.sid:
                           LabelRecord lrec = (LabelRecord) record;
                           curRow = thisRow = lrec.getRow();
                           thisColumn = lrec.getColumn();
                           value = lrec.getValue().trim();
                           value = value.equals("")?" ":value;
                           this.rowlist.add(thisColumn, value);
                           break;
                   case LabelSSTRecord.sid:  //单元格为字符串类型
                           LabelSSTRecord lsrec = (LabelSSTRecord) record;
                           curRow = thisRow = lsrec.getRow();
                           thisColumn = lsrec.getColumn();
                           if (sstRecord == null) {
                                  rowlist.add(thisColumn, " ");
                           } else {
                                  value =  sstRecord
                                  .getString(lsrec.getSSTIndex()).toString().trim();
                                  value = value.equals("")?" ":value;
                                  rowlist.add(thisColumn,value);
                           }
                           break;
                   case NumberRecord.sid:  //单元格为数字类型
                           NumberRecord numrec = (NumberRecord) record;
                           curRow = thisRow = numrec.getRow();
                           thisColumn = numrec.getColumn();
                           value = formatListener.formatNumberDateCell(numrec).trim();
                           value = value.equals("")?" ":value;
                           // 向容器加入列值
                           rowlist.add(thisColumn, value);
                           break;
                   default:
                           break;
           }
           // 遇到新行的操作
           if (thisRow != -1 && thisRow != lastRowNumber) {
                   lastColumnNumber = -1;
           }
           // 空值的操作
           if (record instanceof MissingCellDummyRecord) {
                   MissingCellDummyRecord mc = (MissingCellDummyRecord) record;
                   curRow = thisRow = mc.getRow();
                   thisColumn = mc.getColumn();
                   rowlist.add(thisColumn," ");
           }
           // 更新行和列的值
           if (thisRow > -1)
                   lastRowNumber = thisRow;
           if (thisColumn > -1)
                   lastColumnNumber = thisColumn;
           // 行结束时的操作
           if (record instanceof LastCellOfRowDummyRecord) {
                   if (minColumns > 0) {
                           // 列值重新置空
                           if (lastColumnNumber == -1) {
                                  lastColumnNumber = 0;
                           }
                   }
                   lastColumnNumber = -1;
                           // 每行结束时， 调用getRows() 方法
                   importDataServiceimpl importMbrServiceimpl = new importDataServiceimpl();
                   try {
                    //importMbrServiceimpl.getRows(sheetIndex,curRow, rowlist);
                } catch (Exception e) {
                    e.printStackTrace();
                }
                   // 清空容器
                   rowlist.clear();
           }
    }
}

package com.sjky.platform.myapp.importData.utils;

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

import org.apache.poi.openxml4j.opc.OPCPackage;
import org.apache.poi.xssf.eventusermodel.XSSFReader;
import org.apache.poi.xssf.model.SharedStringsTable;
import org.apache.poi.xssf.usermodel.XSSFRichTextString;
import org.springframework.web.multipart.MultipartFile;
import org.xml.sax.Attributes;
import org.xml.sax.InputSource;
import org.xml.sax.SAXException;
import org.xml.sax.XMLReader;
import org.xml.sax.helpers.DefaultHandler;
import org.xml.sax.helpers.XMLReaderFactory;

import com.sjky.platform.myapp.importData.service.impl.importDataServiceimpl;

/**
 * 抽象Excel2007读取器，excel2007的底层数据结构是xml文件，采用SAX的事件驱动的方法解析
 * xml，需要继承DefaultHandler，在遇到文件内容时，事件会触发，这种做法可以大大降低
 * 内存的耗费，特别使用于大数据量的文件。
 *
 */
public class Excel2007Reader extends DefaultHandler {
    //共享字符串表
    private SharedStringsTable sst;
    //上一次的内容
    private String lastContents;
    //判断是否是String
    private boolean nextIsString;
    //记录行数
    private int sheetIndex = -1;
    //每行结果集
    private List<String> rowlist = new ArrayList<String>();
    //整个Excel的集合
    private List<List<String>> excelList = new ArrayList<List<String>>();
    //判断是否是空单元格
    private boolean cellNull;
    //当前行
    private int curRow = 0;
    //当前列
    private int curCol = 0;
    //有效数据矩形区域,A1:Y2
    private String dimension;
    //根据dimension得出每行的数据长度
    private int longest;
    //上个有内容的单元格id，判断空单元格
    private String lastCellid;
    //处理单行数据的类
    private importDataServiceimpl importService;
    private boolean isTElement;
    public void setImpotService(importDataServiceimpl importService){
        
           this.importService = importService;
    }
    /**只遍历一个电子表格，其中sheetId为要遍历的sheet索引，从1开始，1-3
     * @param filename
     * @param sheetId
     * @throws Exception
     */
    public void processOneSheet(String filename,int sheetId) throws Exception {
           OPCPackage pkg = OPCPackage.open(filename);
           XSSFReader r = new XSSFReader(pkg);
           SharedStringsTable sst = r.getSharedStringsTable();
           XMLReader parser = fetchSheetParser(sst);
           // 根据 rId# 或 rSheet# 查找sheet
           InputStream sheet2 = r.getSheet("rId"+sheetId);
           sheetIndex++;
           InputSource sheetSource = new InputSource(sheet2);
           parser.parse(sheetSource);
           sheet2.close();
    }
    /**
     * 遍历工作簿中所有的电子表格
     * @param multipartFile
     * @throws Exception
     */
    public void process(MultipartFile multipartFile) throws Exception {
  //或得文件流
        InputStream in = multipartFile.getInputStream();
        //获取实例对象
          OPCPackage pkg = OPCPackage.open(in);
          XSSFReader r = new XSSFReader(pkg);
           SharedStringsTable sst = r.getSharedStringsTable();
           XMLReader parser = fetchSheetParser(sst);
           Iterator<InputStream> sheets = r.getSheetsData();
           while (sheets.hasNext()) {
                   curRow = 0;
                   sheetIndex++;
                   InputStream sheet = sheets.next();
                  //查看转换的xml原始文件，方便理解后面解析时的处理,
            // 注意：如果打开注释，下面parse()就读不到流的内容了
             // this.streamOut(in);
                   InputSource sheetSource = new InputSource(sheet);
                  //据说当执行这个方法时  自动触发 startElement(开始的元素)    endElement(结束的元素)
                   parser.parse(sheetSource);
                   sheet.close();
           }
    }
   //读取流，查看文件内容
        public static void streamOut(InputStream in) throws Exception{
            byte[] buf = new byte[1024];
            int len;
            while ((len=in.read(buf))!=-1){
                System.out.write(buf,0,len);
            }
        }

    public XMLReader fetchSheetParser(SharedStringsTable sst)
                   throws SAXException {
           XMLReader parser = XMLReaderFactory
                           .createXMLReader("org.apache.xerces.parsers.SAXParser");
           this.sst = sst;
           parser.setContentHandler(this);
           return parser;
    }
/*
name  Excel转xml后的开始标签   这个方法 可以把参数都打出来看看
*/
    public void startElement(String uri, String localName, String name,
                   Attributes attributes) throws SAXException {
         if (name.equals("dimension")){
                dimension = attributes.getValue("ref");
                longest = covertRowIdtoInt(dimension.substring(dimension.indexOf(":")+1) );
            }
           // c => 表示是不是单元格  row=><row>:开始处理某一行   isTextTag(name)<v>:单元格值
           if ("c".equals(name)) {
                //当前单元格的位置
               String cellId = attributes.getValue("r");
               //空单元判断，添加空字符到list
                if (lastCellid!=null)
                {
                    int gap = covertRowIdtoInt(cellId)-covertRowIdtoInt(lastCellid);
                    for(int i=0;i<gap-1;i++)
                    {
                          rowlist.add(curCol, "");
                          curCol++;
                    }
                }else{
                    //第一个单元格可能不是在第一列
                    if (!"A1".equals(cellId))
                    {
                        for(int i=0;i<covertRowIdtoInt(cellId)-1;i++)
                        {
                              rowlist.add(curCol, "");
                              curCol++;
                        }
                    }
                }
                lastCellid = cellId;
            // 如果下一个元素是 SST 的索引，则将nextIsString标记为true
                   //判断单元格的值是SST 的索引，不能直接characters方法取值
                if (attributes.getValue("t")!=null && attributes.getValue("t").equals("s"))
                {
                    nextIsString = true;
                    cellNull=false;
                }else{
                    nextIsString = false;
                    cellNull=true;
                }
           }
           //当元素为t时
           if("t".equals(name)){
                   isTElement = true;
           } else {
                   isTElement = false;
           }
           // 置空
           lastContents = "";
    }
    public void endElement(String uri, String localName, String name)
                   throws SAXException {
           // 根据SST的索引值的到单元格的真正要存储的字符串
           // 这时characters()方法可能会被调用多次
           if (nextIsString) {
               try {
                       int idx = Integer.parseInt(lastContents);
                       lastContents = new XSSFRichTextString(sst.getEntryAt(idx)).toString().trim();
               } catch (Exception e) {
               }
           }
           //t元素也包含字符串
           if(isTElement){
                   String value = lastContents;
                   value = value.equals("")?" ":value;
                   rowlist.add(curCol, value);
                   curCol++;
                   isTElement = false;
                   // v => 单元格的值，如果单元格是字符串则v标签的值为该字符串在SST中的索引
                   // 将单元格内容加入rowlist中，在这之前先去掉字符串前后的空白符
           } else if ("v".equals(name)) {
                   String value = lastContents;
                   value = value.equals("")?" ":value;
                   cellNull=false;
                   rowlist.add(curCol, value);
                   curCol++;
           }
           else if("c".equals(name) && cellNull == true){  
               rowlist.add(curCol, "");  
               curCol++;  
               cellNull = false;  
           }else {
                   //如果标签名称为 row ，这说明已到行尾，调用 optRows() 方法
                   if (name.equals("row")) {
                          //大于0的判断主要是筛除表头信息  这里具体看你想要的信息在哪一行开始
                       if(curRow>0){
                           //判断最后一个单元格是否在最后，补齐列数
                            if(covertRowIdtoInt(lastCellid)<longest){
                               for(int i=0;i<longest- covertRowIdtoInt(lastCellid);i++)
                                {
                                 rowlist.add(curCol, "");  
                                 curCol++; 
                                }
                            }
                           //将此行放入一个大集合
                           excelList.add(rowlist);
                       }
                       rowlist=new ArrayList<String>();
                       curRow++;
                       curCol = 0;
                   }else if(name.equals("worksheet")){
                          //结束标签为worksheet说明工作簿sheet读取完成
                          //调用要处理的方法
                       importService.getRows(excelList);
                   }
           }
    }
    public void characters(char[] ch, int start, int length)
                   throws SAXException {
           //得到单元格内容的值
           lastContents += new String(ch, start, length);
    }
    /**
     * 列号转数字   AB7-->28 第28列
     * @param cellId
     * @return
     */
    public static int covertRowIdtoInt(String cellId){
        int firstDigit = -1;
        for (int c = 0; c < cellId.length(); ++c) {
            if (Character.isDigit(cellId.charAt(c))) {
                firstDigit = c;
                break;
            }
        }
        //AB7-->AB
        //AB是列号, 7是行号
        String newRowId = cellId.substring(0,firstDigit);
        int num = 0;
        int result = 0;
        int length = newRowId.length();
        for(int i = 0; i < length; i++) {
            //先取最低位，B
            char ch = newRowId.charAt(length - i - 1);
            //B表示的十进制2，ascii码相减，以A的ascii码为基准，A表示1，B表示2
            num = (int)(ch - 'A' + 1) ;
            //列号转换相当于26进制数转10进制
            num *= Math.pow(26, i);
            result += num;
        }
        return result;

    }
}