采用读取csv格式,类似读取txt文档

接收前端请求的controller层

前端代码使用的Vue upload主键,返回值一般就是读取成功或失败

@RequestMapping(value = "/importExcelDatas.do", method = RequestMethod.POST)
    @ResponseBody
    public ResultData importExcelDatas(HttpServletRequest request,HttpServletResponse response) {
        ResultData resultData = new ResultData();
        try {
            resultData = importDataService.importExcelDatas(request, response);
        } catch (Exception e) {
            e.printStackTrace();
            resultData.setResult(false);
            log.error(e.getMessage(), e);
        }
        return  resultData;
    }

处理数据Service层

public ResultData importExcelDatas(HttpServletRequest request, HttpServletResponse response) throws Exception {
        ResultData resultData = new ResultData();
        //转换请求
        MultipartHttpServletRequest multipartRequest = (MultipartHttpServletRequest) request;
                //获取请求中的上传文件名 迭代器
        Iterator<String> iter = multipartRequest.getFileNames();
        List<String[]> list  = new ArrayList<>();
        MultipartFile multipartFile=null;
        Map<String,List<Map<String, String>>> superMap = null;
        while (iter.hasNext()) {
            String filefiled = iter.next();
                          //或得上传文件
             multipartFile = multipartRequest.getFile(filefiled);
                          //因为事件模式下的读取是在工具类里盗用我们的处理方法所以把必要的数据传过去,这里可忽略给自己提醒看的
            /* importDataServiceimpl importService = new importDataServiceimpl();
             importService.response = response;
             importService.request=request;
             importService.tenantCode = tenantCode;
             importService.multipartFile = multipartFile;
             importService.importName = importName;
             importService.mbrMemberLocationServiceImpl=this.mbrMemberLocationServiceImpl;
             importService.mbrCategoryDao = this.mbrCategoryDao;
             importService.sysComboCodeDao=this.sysComboCodeDao;
             importService.handleLogService=this.handleLogService;
             importService.mbrMemberService=this.mbrMemberService;
             importService.mbrImportNotSuccessDao = this.mbrImportNotSuccessDao;
             importService.mbrMemberLocationDao=this.mbrMemberLocationDao;
            */
             try {
                                //读取的过程
                 ExcelReaderUtils.readExcel(importService,multipartFile);
            } catch (Exception e) {
                logger.error(e.getMessage());
                resultData.setResult(false);
                resultData.setMessage("读取文件失败,请检查文件!");
                return resultData;
            }
        }
        resultData.setResult(true);
        return resultData;
    }

识别文件调用处理类

public class ExcelReaderUtils {

    //excel2003扩展名
    public static final String EXCEL03_EXTENSION = ".xls";
    //excel2007扩展名
    public static final String EXCEL07_EXTENSION = ".xlsx";
    /**
     * 读取Excel文件，可能是03也可能是07版本
     * @param excel03
     * @param excel07
     * @throws Exception
     */
    public static void readExcel(importDataServiceimpl importService,MultipartFile multipartFile) throws Exception{
        //multipartFile获取文件名 判断文件类型
        String fileName = multipartFile.getOriginalFilename();
           // 处理excel2003文件
           if (fileName.endsWith(EXCEL03_EXTENSION)){
                   Excel2003Reader excel03 = new Excel2003Reader();
                   excel03.process(fileName);
           // 处理excel2007文件
           } else if (fileName.endsWith(EXCEL07_EXTENSION)){
                   Excel2007Reader excel07 = new Excel2007Reader();
                   excel07.process(multipartFile);
           } else {
                   throw new  Exception("文件格式错误，fileName的扩展名只能是xls或xlsx。");
           }
    }
}

处理Excel2003的工具类

这里只贴下代码没做研究

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();
           }
    }
}

处理Excel2007的工具类

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;

    }
}

浪客行1213的简书

---

xhh