java

前言:

前言:

主要功能:做EXECL文件解析、EXECL数据单独校验(EXECL文档格式及主体数据的逻辑校验)及EXECL和TXT的数据联合校验。

   
之所以前面做了一个POI的用户模式解析execl的介绍,是因为该模式对于开发时的灵活性,可操作性上较为方便,且该模式对于复杂的EXECL操作有明显的优势。POI用户模式对于EXECL的写操作(创建固定格式的复杂的交叉报表),可以轻松完成,因此会先简单介绍。但是,凡事都有例外,有优点固然也会有缺点。例如:当对于一个开发系统有性能上的要求时,尤其是对于大数据量的性能要求时,POI的用户模式,显然会比较吃力,下面就可以采用SAX——POI事件模式替代。

1、POI 环境

    下面进入正文,依然采用的是MAVEN+SPRING
BOOT+MYBATIS jdk1.8软件环境,WINDOW系统

1.1、软件

一,需要导的MAVEN SAX包

MAVEN+SPRING BOOT+MYBATIS jdk1.8

<dependency>
            <groupId>sax</groupId>
            <artifactId>sax</artifactId>
            <version>2.0.1</version>
        </dependency>

        <!-- SAX驱动 -->
        <dependency>
            <groupId>xerces</groupId>
            <artifactId>xercesImpl</artifactId>
            <version>2.10.0</version>
        </dependency>

1.2、操作系统

下面先贴代码,再做说明:

Window X

public class SaxCell {
    /**
     * 
     * 记录当前循环行的每一个单元格的值的开始列和字符长度
     * 
     */
    private int start;
    private int length;
    private CellDataType nextDataType;
    private char[] ch;
    private String lastIndex;

    public String getLastIndex(){
        return lastIndex;
    }

    public void setLastIndex(String lastIndex){
        this.lastIndex = lastIndex;
    }

    public char[] getCh(){
        return ch;
    }

    public void setCh(char[] ch){
        this.ch = ch;
    }

    public CellDataType getCellDataType(){
        return nextDataType;
    }

    public void setCextDataType(CellDataType nextDataType){
        this.nextDataType = nextDataType;
    }
    public int getStart() {
        return start;
    }
    public void setStart(int start) {
        this.start = start;
    }
    public int getLength() {
        return length;
    }
    public void setLength(int length) {
        this.length = length;
    }

    public SaxCell(){}

    //改构造方法会将execl中的每一行的具体信息一同存储下来
    public SaxCell(int start,int lengxth,CellDataType nextDataType,char[] ch,String lastIndex){
        this.start = start;
        this.length = lengxth;
        this.nextDataType = nextDataType;
        this.ch = ch;
        this.lastIndex = lastIndex;
    }

    //改构造方法只会存储当前单元格的开始列,指的索引索引位置,单元格的格式,单元格的值的长度
    public SaxCell(int start,int lengxth,CellDataType nextDataType,String lastIndex){
        this.start = start;
        this.length = lengxth;
        this.nextDataType = nextDataType;
        this.lastIndex = lastIndex;
    }
}

import java.io.InputStream;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;
import java.util.concurrent.ConcurrentHashMap;

import org.apache.poi.openxml4j.opc.OPCPackage;
import org.apache.poi.ss.usermodel.BuiltinFormats;
import org.apache.poi.ss.usermodel.DataFormatter;
import org.apache.poi.xssf.eventusermodel.XSSFReader;
import org.apache.poi.xssf.model.SharedStringsTable;
import org.apache.poi.xssf.model.StylesTable;
import org.apache.poi.xssf.usermodel.XSSFCellStyle;
import org.apache.poi.xssf.usermodel.XSSFRichTextString;
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 cn.stylefeng.roses.core.util.ToolUtil;

import com.nssolsh.boot.modular.system.model.CellDataType;
import com.nssolsh.boot.modular.system.model.ExeclDataSiteAndValue;
import com.nssolsh.boot.modular.system.model.SaxCell;

/**
 * @author qjwyss
 * @date 2018/12/19
 * @description 读取EXCEL辅助类
 */
public class ExcelXlsxReaderWithDefaultHandler extends DefaultHandler {

    public ExcelXlsxReaderWithDefaultHandler() {}


    /**
     * 共享字符串表
     */
    private SharedStringsTable sst;

    /**
     * 上一次的索引值
     */
    private String lastIndex;

    /**
     * 文件的绝对路径
     */
    private String filePath = "";

    /**
     * 工作表索引
     */
    private int sheetIndex = 0;

    /**
     * sheet名
     */
    private String sheetName = "";

    /**
     * 总行数
     */
    private int totalRows = 0;

    /**
     * 存放第7含的列数
     * 
     */
    private int tempTotalCells = 0;
    /**
     * 一行内cell集合
     */
    private List<String> cellList = new ArrayList<String>();

    /**
     * 判断整行是否为空行的标记
     */
    private boolean flag = false;

    /**
     * 当前行
     */
    private int curRow = 1;

    /**
     * 当前列
     */
    private int curCol = 0;

    /**
     * 临时list下标
     */
    private int tempCurCol = 0;

    /**
     * T元素标识
     */
    private boolean isTElement;

    /**
     * 单元格数据类型,默认为字符串类型
     */
    private CellDataType nextDataType = CellDataType.SSTINDEX;

    private CellDataType tempNextDataType;

    private final DataFormatter formatter = new DataFormatter();

    /**
     * 单元格日期格式的索引
     */
    private short formatIndex;

    /**
     * 日期格式字符串
     */
    private String formatString;

    //定义前一个元素和当前元素的位置,用来计算其中空的单元格数量,如A6和A8等
    private String preRef = null, ref = null;

    //记录每行第一个单元格
    private String tempFirstRef=null;
    //定义该文档一行最大的单元格数,用来补全一行最后可能缺失的单元格
    private String maxRef = null;

    /**
     * 单元格
     */
    private StylesTable stylesTable;

    /**
     * 总行号
     */
    private Integer totalRowCount;

    private static Map<String,List<String>> map = new ConcurrentHashMap<String, List<String>>();
    List<String> list = new ArrayList<String>();
    /**
     * 时间范围
     */
    List<String> dateList = new ArrayList<String>();

    private String tempCellStr = "";

    private String tempLastIndex;

    private static List<String> regxList = new ArrayList<>();
    static{
        regxList.add("(");
        regxList.add(")");
        regxList.add(""");
    }
    //临时存一行中的每一个单元的开始位置以及单元格中的字符的长度
    private List<SaxCell> saxCellList = new ArrayList<SaxCell>();
    /**
     * 遍历工作簿中所有的电子表格
     * 并缓存在mySheetList中
     *
     * @param filename
     * @throws Exception
     */
    public Map<String,List<String>> process(String filename) throws Exception {
        filePath = filename;
        OPCPackage pkg = OPCPackage.open(filename);
        XSSFReader xssfReader = new XSSFReader(pkg);
        stylesTable = xssfReader.getStylesTable();
        SharedStringsTable sst = xssfReader.getSharedStringsTable();
        XMLReader parser = XMLReaderFactory.createXMLReader("org.apache.xerces.parsers.SAXParser");
        this.sst = sst;
        parser.setContentHandler(this);
        XSSFReader.SheetIterator sheets = (XSSFReader.SheetIterator) xssfReader.getSheetsData();
        InputStream psiDataSheet = null;
        while (sheets.hasNext()) { //遍历sheet
            curRow = 1; //标记初始行为第一行
            sheetIndex++;
            psiDataSheet = sheets.next(); //sheets.next()和sheets.getSheetName()不能换位置,否则sheetName报错
            sheetName = sheets.getSheetName();
            if("PSI Data".equals(sheetName)){
                InputSource sheetSource = new InputSource(psiDataSheet);
                parser.parse(sheetSource); //解析excel的每条记录,在这个过程中startElement()、characters()、endElement()这三个函数会依次执行
            }
            psiDataSheet.close();
        }
       //由于是全局变量,下次请求数据可能还在,所以这里清除掉所有全局变量的值
       cellList = new ArrayList<String>();
       sst = new SharedStringsTable();
       lastIndex = null;
       filePath = "";
       sheetIndex = 0;
       sheetName = "";
       totalRows = 0;
       tempTotalCells = 0;
       flag = false;
       curRow = 1;
       curCol = 0;
       tempCurCol = 0;
       formatString = "";
       preRef = null;
       ref = null;
       tempFirstRef=null;
       maxRef = null;
       totalRowCount = null;
       list = new ArrayList<String>();
       dateList = new ArrayList<String>();
       tempCellStr = "";
       tempLastIndex = "";
       regxList = new ArrayList<>();
       saxCellList = new ArrayList<SaxCell>();
       Map<String,List<String>> returnMap = map;
       map = new ConcurrentHashMap<String, List<String>>();
       return returnMap; //返回已经读取的一整行数据
    }
    public static int excelColStrToNum(String colStr, int length) {
        int num = 0;
        int result = 0;
        for(int i = 0; i < length; i++) {
            char ch = colStr.charAt(length - i - 1);
            num = (int)(ch - 'A' + 1);
            num *= Math.pow(26, i);
            result += num;
        }
        return result;
    }

    /**
     * 第一个执行
     *
     * @param uri
     * @param localName
     * @param name
     * @param attributes
     * @throws SAXException
     */
    @Override
    public void startElement(String uri, String localName, String name, Attributes attributes) throws SAXException {

        // 获取总行号  格式: A1:B5    取最后一个值即可
        if("dimension".equals(name)) {
            String dimensionStr = attributes.getValue("ref");
            String colNum = dimensionStr.substring(dimensionStr.lastIndexOf(":")+1);

            try {
                //获取总行数
                totalRowCount = Integer.parseInt(StringUtil.getIndexEndStr(colNum));

            } catch (Exception e) {
                System.out.println(dimensionStr);
                // TODO: handle exception
            }

        }

        //c => 单元格
        if ("c".equals(name)) {
            //前一个单元格的位置
            if (preRef == null) {
                preRef = attributes.getValue("r");
            } else {
                if(!ToolUtil.isEmpty(lastIndex)){
                    preRef = ref;
                }
            }

            //当前单元格的位置
            ref = attributes.getValue("r");
            //设定单元格类型
            this.setNextDataType(attributes);
        }

        //当元素为t时
        if ("t".equals(name)) {
            isTElement = true;
        } else {
            isTElement = false;
        }
        //置空
        lastIndex = "";
        tempLastIndex = "";

        if("H43".equals(ref)){
            System.out.println("ref:"+ref);
        }
    }


    /**
     * 第二个执行
     * 得到单元格对应的索引值或是内容值
     * 如果单元格类型是字符串、INLINESTR、数字、日期,lastIndex则是索引值
     * 如果单元格类型是布尔值、错误、公式,lastIndex则是内容值
     *
     * @param ch
     * @param start
     * @param length
     * @throws SAXException
     */
    @Override
    public void characters(char[] ch, int start, int length) throws SAXException {
        if(start == 0){
            //标记每一行的开始,清空saxCellList,tempCurCol是想saxCellList插入值时的集合下标
            saxCellList = new ArrayList<SaxCell>();
            saxCellList.clear();
            tempCurCol = 0;
        }

        lastIndex += new String(ch, start, length);

        SaxCell sc = new SaxCell(start,length, nextDataType,ch,lastIndex);
        saxCellList.add(tempCurCol,sc);

        SaxCell tempSc = null;
        if(saxCellList.size()>1){
            /**
             * 数组下标比数组长度小1,取倒数第二个值就减去2,倒数第二个就是上一个非空单元格的值的索引
             */
            tempSc = saxCellList.get(saxCellList.size()-2); 
            int tempStart = tempSc.getStart();
            int tempLength = tempSc.getLength();
            char[] tempCh = tempSc.getCh();
            String tempLastIndex1 = tempSc.getLastIndex();

            tempNextDataType = tempSc.getCellDataType();
            tempLastIndex += tempLastIndex1;
        }
        tempCurCol++;
    }

    public void getCellList(){
        //用空字符串补充前面缺失的单元格
        if(!ToolUtil.isEmpty(ref)&&cellList.size()==0){
            String tempRef = StringUtil.getStartIndexStr(ref, 0);
            String firstCell = "A"+curRow;
            int len = countNullCell(ref ,firstCell);
            this.tempFirstRef = ref;
            for(int i=0;i<len+1;i++){
                cellList.add(curCol,"");
                curCol++;
            }
        }
    }

    /**
     * 第三个执行
     *
     * @param uri
     * @param localName
     * @param name
     * @throws SAXException
     */
    @Override
    public void endElement(String uri, String localName, String name) throws SAXException {
        ExeclDataSiteAndValue edsav = null;

        //t元素也包含字符串
        if (isTElement) {//这个程序没经过
            //将单元格内容加入rowlist中,在这之前先去掉字符串前后的空白符
            String value = lastIndex.trim();

            value = value == null?"":String.valueOf(value);

            cellList.add(value == null?"":String.valueOf(value));
            curCol++;
            isTElement = false;
            //如果里面某个单元格含有值,则标识该行不为空行
            if (value != null && !"".equals(value)) {
                flag = true;
            }
        } else if ("v".equals(name)) {
            //v => 单元格的值,如果单元格是字符串,则v标签的值为该字符串在SST中的索引
            getCellList();
            String value = null;
            try {
                if(ToolUtil.isEmpty(cellList)){

                    //设置需要强制修改单元格取值类型为字符串的单元格
                    String tempPer = cellList.get(cellList.size()-1);
                    switch(tempPer){
                    case "Period":
                        nextDataType = CellDataType.SSTINDEX;
                        break;
                    }
                }
                value = this.getDataValue(lastIndex.trim(), "");
            } catch (Exception e1) {
                // TODO Auto-generated catch block
                e1.printStackTrace();
            }

            //首次循环获取单元格值时tempLastIndex 为空,curRow 时当前行的索引,对7行包含7行之下的所有单元格,空白单元格使用空字符串代替
            if(ToolUtil.isEmpty(tempLastIndex)||curRow>=7) {
                tempCellStr = "";
            }else {
                //根据索引值获取对应的单元格值
                //单单元格列标不一致时,取上一个不为空的单元格的值,赋值给临时存储字符串
                if(!ref.equals(preRef)){
                    try{
                        if(ToolUtil.isNum(tempLastIndex)){
                            try {
                                nextDataType = tempNextDataType;
                                tempCellStr = this.getDataValue(tempLastIndex.trim(), "");

                            } catch (Exception e) {

                                e.printStackTrace();

                            }
                        }else{
                            tempCellStr = value;
                        }
                    }catch(java.lang.NumberFormatException e){
                        e.printStackTrace();
                    }
                }
            }

            //在向空单元格填充值时,使用临时字段
            //补全单元格之间的空单元格
            //补全单元格时,第一个单元格不需要补
            if (!ref.equals(preRef)&&!tempFirstRef.equals(ref)) {
                int len = countNullCell(ref, preRef);

                for (int i = 0; i < len; i++) {
                    cellList.add(curCol, tempCellStr);
                    curCol++;
                }
            }

            cellList.add(curCol, value);
            curCol++;
            //如果里面某个单元格含有值,则标识该行不为空行
            if (value != null && !"".equals(value)) {
                flag = true;
            }
        } else {
            //如果标签名称为row,这说明已到行尾,调用optRows()方法
            if ("row".equals(name)) {
                //默认第一行为表头,以该行单元格数目为最大数目
                if (curRow == 1) {
                    maxRef = ref;
                }
                //补全一行尾部可能缺失的单元格
                if (maxRef != null) {
                    int len = countNullCell(maxRef, ref);
                    for (int i = 0; i <= len; i++) {

                        //edsav = new ExeclDataSiteAndValue(ref,value);
                        cellList.add(curCol, cellList.get(curCol-1));
                        curCol++;
                    }
                }


                //当7行一下的列数和第7行的列数不一致时,少的用空格补齐
                if(curRow > 7 && cellList.size()<tempTotalCells) {
                    int tempCountCell = tempTotalCells - cellList.size();
                    for(int i = curCol;i<tempCountCell ; i++) {
                        cellList.add(i,"");
                    }
                }

                //记录第7行的列数
                if( curRow == 7) {
                    tempTotalCells = cellList.size();
                    List<String> tempFiveRow = map.get("5");
                    addList(tempFiveRow,"5");
                    List<String> tempSixRow = map.get("6");
                    addList(tempSixRow,"6");

                }

                if (flag) { //该行不为空行且该行不是第一行,则发送(第一行为列名,不需要)
                    if(!map.containsKey(curRow)){
                        map.put(String.valueOf(curRow), cellList);
                        cellList = new ArrayList<String>();
                    }
                    totalRows++;
                }
                cellList = new ArrayList<String>();
                saxCellList = new ArrayList<SaxCell>();
                curRow++;
                tempCellStr = null;
                tempCurCol = 0;
                curCol = 0;
                preRef = null;
                ref = null;
                flag = false;
            }
        }
    }

    /**
     * 更新第5行和第六行最后一列的合并单元格缺少的值
     * @param list
     * @param row
     */
    public void addList(List<String> list,String row){

        String tempCellValue = list.get(list.size()-1);
        int cz = tempTotalCells - list.size();
        for(int i=0;i<cz;i++) {
            list.add(list.size(),tempCellValue);
            curCol ++;
        }
        map.put(row,list);
    }
    /** 
     * 处理数据类型
     *
     * @param attributes
     */
    public void setNextDataType(Attributes attributes) {
        nextDataType = CellDataType.NUMBER; //cellType为空,则表示该单元格类型为数字
        formatIndex = -1;
        formatString = null;
        String cellType = attributes.getValue("t"); //单元格类型
        String cellStyleStr = attributes.getValue("s"); //
        String columnData = attributes.getValue("r"); //获取单元格的位置,如A1,B1

        if ("b".equals(cellType)) { //处理布尔值
            nextDataType = CellDataType.BOOL;
        } else if ("e".equals(cellType)) {  //处理错误
            nextDataType = CellDataType.ERROR;
        } else if ("inlineStr".equals(cellType)) {
            nextDataType = CellDataType.INLINESTR;
        } else if ("s".equals(cellType)) { //处理字符串
            nextDataType = CellDataType.SSTINDEX;
        } else if ("str".equals(cellType)) {
            nextDataType = CellDataType.FORMULA;
        }

        if (cellStyleStr != null) { //处理日期
            int styleIndex = Integer.parseInt(cellStyleStr);
            XSSFCellStyle style = stylesTable.getStyleAt(styleIndex);
            formatIndex = style.getDataFormat();
            formatString = style.getDataFormatString();
            if (formatString.contains("m/d/yy") || formatString.contains("yyyy/mm/dd") || formatString.contains("yyyy/m/d")) {
                nextDataType = CellDataType.DATE;
                formatString = "yyyy-MM-dd hh:mm:ss";
            }

            if (formatString == null) {
                nextDataType = CellDataType.NULL;
                formatString = BuiltinFormats.getBuiltinFormat(formatIndex);
            }
        }
    }

    /**
     * 对解析出来的数据进行类型处理
     *
     * @param value   单元格的值,
     *                value代表解析:BOOL的为0或1, ERROR的为内容值,FORMULA的为内容值,INLINESTR的为索引值需转换为内容值,
     *                SSTINDEX的为索引值需转换为内容值, NUMBER为内容值,DATE为内容值
     * @param thisStr 一个空字符串
     * @return
     */
    @SuppressWarnings("deprecation")
    public String getDataValue(String value, String thisStr) throws Exception {
        switch (nextDataType) {
            // 这几个的顺序不能随便交换,交换了很可能会导致数据错误
            case BOOL: //布尔值
                char first = value.charAt(0);
                thisStr = first == '0' ? "FALSE" : "TRUE";
                break;
            case ERROR: //错误
                thisStr = ""ERROR:" + value.toString() + '"';
                break;
            case FORMULA: //公式
                thisStr = "" + value.toString() + "";
                break;
            case INLINESTR:
                XSSFRichTextString rtsi = new XSSFRichTextString(value.toString());
                thisStr = rtsi.toString();
                rtsi = null;
                break;
            case SSTINDEX: //字符串
                String sstIndex = value.toString();
                int idx = 0 ;
                XSSFRichTextString rtss = null;
                try {
                    idx = Integer.parseInt(sstIndex);
                    rtss = new XSSFRichTextString(sst.getEntryAt(idx));//根据idx索引值获取内容值
                    thisStr = StringUtil.replaceStr(rtss.toString(), regxList);
                    rtss = null;
                } catch (Exception ex) {
                    thisStr = value.toString();
                }
                break;
            case NUMBER: //数字
                thisStr = value;
                break;
            case DATE: //日期
                thisStr = formatter.formatRawCellContents(Double.parseDouble(value), formatIndex, formatString);
                // 对日期字符串作特殊处理,去掉T
                thisStr = thisStr.replace("T", " ");
                break;
            default:
                thisStr = " ";
                break;
        }
        return thisStr;
    }

    public int countNullCell(String ref, String preRef) {
        //excel2007最大行数是1048576,最大列数是16384,最后一列列名是XFD
        String xfd = ref.replaceAll("\d+", "");
        String xfd_1 = preRef.replaceAll("\d+", "");

        xfd = fillChar(xfd, 3, '@', true);
        xfd_1 = fillChar(xfd_1, 3, '@', true);

        char[] letter = xfd.toCharArray();
        char[] letter_1 = xfd_1.toCharArray();
        int res = (letter[0] - letter_1[0]) * 26 * 26 + (letter[1] - letter_1[1]) * 26 + (letter[2] - letter_1[2]);
        return res - 1;
    }

    public String fillChar(String str, int len, char let, boolean isPre) {
        int len_1 = str.length();
        if (len_1 < len) {
            if (isPre) {
                for (int i = 0; i < (len - len_1); i++) {
                    str = let + str;
                }
            } else {
                for (int i = 0; i < (len - len_1); i++) {
                    str = str + let;
                }
            }
        }
        return str;
    }

}



import java.util.List;
import java.util.Map;

/**
 * @author qjwyss
 * @date 2018/12/19
 * @description 读取EXCEL工具类
 */
public class ExcelReaderUtil {


    public static Map<String,List<String>> readExcel(String filePath) throws Exception {
        Map<String,List<String>> map = null;
        if (filePath.endsWith(ExcelConstant.EXCEL07_EXTENSION)) {
            ExcelXlsxReaderWithDefaultHandler excelXlsxReader = new ExcelXlsxReaderWithDefaultHandler();
            map = excelXlsxReader.process(filePath);
        } else {
            throw new Exception("文件格式错误,fileName的扩展名只能是xlsx!");
        }
        return map;
    }

}

2、jar包

说明:

2.1、poi.jar

        EXECL原始文件的说明:

<dependency>
      <groupId>org.apache.poi</groupId>
      <artifactId>poi</artifactId>
      <version>3.16</version>
</dependency>
<dependency>
      <groupId>org.apache.poi</groupId>
      <artifactId>poi-ooxml</artifactId>
      <version>3.14</version>
</dependency>

        1.
随机创建一个EXECL原文件,右键重命名,将后缀改为rar,然后解压,发现可以解压出三个文件夹(_rels、docProps、xl)和一个同目录的xml([Content_Types].xml),这里我们需要关心文件在xl文件夹中,所以对该文件进行详细说明。

     

        1.1
xl文件夹包含的文件也是有多层的分别是文件夹(_rels、drawings、theme、worksheets)和同级的xml文件(comments1.xml、sharedStrings.xml、styles.xml、workbook.xml)

2.2、文件上传下载

澳门新萄京官方网站,        1.2 主要文件所对应的execl中的数据

<dependency>
      <groupId>commons-fileupload</groupId>
      <artifactId>commons-fileupload</artifactId>
      <version>1.4</version>
</dependency>
<!--文件工具包-->
<dependency>
    <groupId>commons-io</groupId>
    <artifactId>commons-io</artifactId>
    <version>2.6</version>
</dependency>

        1.2.1 sharedStrings.xml

3、解析步奏

        对应的是execl中的所有字符串型数据

3.1、文件上传需要表单异步提交

        1.2.2 styles.xml

3.1.1、画面代码

        对应的是execl中的所有样式

<form action="${ctxPath}/merge/psiUpload" method="POST" enctype="mulitpart/form-data" id="upload_form_psi">
<input class="input-file-remove-style" type="file" name="psiFile" id="psiFile" onchange="addPsiFile(this)"/>
</form>

        1.2.4 workbook.xml

3.1.2后台代码

       
对应的是execl中的所有sheet的名称,位置以及某个sheet引用的外部数据的位置

        3.1.2.1 、controller代码

        1.2.5 xl中文件夹中的文件说明

@RequestMapping(value = "/Convert/upload", method = RequestMethod.POST, produces="application/json;charset=UTF-8")
    public void checkHead(@RequestParam(value = "filename") MultipartFile file, String fileType,
            HttpServletResponse resp) throws Exception {
        Date now = new Date();
        SimpleDateFormat dateFormat = new SimpleDateFormat("yyyyMMddHHmmssSSS");

        String tmpName = dateFormat.format(now);

        String fileName = file.getOriginalFilename();  
        String originalFilename = fileName.substring(fileName.lastIndexOf("\")+1);  

        String fileId = originalFilename.substring(0, originalFilename.lastIndexOf("."))+"_"+tmpName;
        String path = new UploadFile().writeFile(file, fileId);

        Map<String, String> map = new HashMap<>();
        if (path != null) {
            map.put("fileId", fileId);
        }
        JSONObject result = JSONObject.parseObject(JSON.toJSONString(map));
        resp.getWriter().print(result);
    }

        1.2.5.1 worksheets文件夹

        3.1.2.2 、上传文件工具类UploadFile

       
打开文件夹,发现里面还是由文件夹和xml文件构成,这里直接说明主体,多个xml就是我们创建的EXECL中所包含的sheet数量(说白了就是你创建了几个sheet,这里就会有几个xml文件),然后我们的EXECL数据就存在这几个xml中。

public class UploadFile{
/**
     * 
     * @param upf
     * @param fileid 命名为源文件名+"_"+时间戳
     * @return
     */
    public String writeFile(MultipartFile upf,String fileid){
        String address = null;
        String fileName = upf.getOriginalFilename();
        fileName = fileName.substring(fileName.lastIndexOf("\")+1);
        String suffixName = ToolUtil.getFileSuffix(fileName);

        String tmp[] = fileid.split("_");

        String folderName=ShiroKit.getUser().getAccount();
        if("txt".equals(suffixName)){
            //folderName = tmp[tmp.length-1].substring(0, 8);
            address = tmp[tmp.length-1].substring(0, 8)+"/"+fileid+".txt";

        }else{
            if(fileid.contains("bak")){
                address = tmp[tmp.length-2].substring(0, 8)+"/"+fileid+".xlsx";
            }else{
                address = tmp[tmp.length-1].substring(0, 8)+"/"+fileid+".xlsx";
            }
        }
        try {
            //原目录
            File file = new  File("D:/psi/temp/"+folderName+"/");
            if (!file.exists()) file.mkdirs();
            File file2 = new  File("D:/psi/temp/"+folderName+"/"+ fileid + "."+suffixName);
            FileUtils.copyInputStreamToFile(upf.getInputStream(), file2); 

        } catch (IOException e1) {
            e1.printStackTrace();
        }
           return address;
    }
}

        2.
当采用SAX解析EXECL时,需要知道的是该模式通过继承DefaultHandler,然后实现其的startElement、characters、endElement,三个方法,SAX
API会一次按顺序执行该三个方法,该事件模式读取EXECL方式是将EXECL压缩成压缩包,然后再解压,获取EXECL的XML下的实际内容,然后按照XML标签按照对应的标签获取不同的数据

3.2、文件下载

        2.1 下面对不同的标签包含的对应的信息简要说明一下

3.2.1、画面代码

        下面以实际xml的格式简要说明xml中的标签及对应的作用

<div class="col-sm-12 custom-alerts alert alert-warning fade in display-none" id="dowerrormessage">
                        <button type="button" class="close" data-dismiss="alert" aria-hidden="true"></button>
                        <i class="fa-lg fa fa-warning"></i>&nbsp;PSI file convert failed. You can check error message from <a onclick=downFile(‘100101’)>here</a>.
                    </div>
<!--在做一个隐藏表单-->
 <form id="downForm" action="reference/down" method='get'>   
        <input type="hidden" name="fileId" id="fileId" value="" />
</form>
<script>
<!--通过js提交表单-->
function downFile(fileId){
    $("#fileId").val(fileId);
    $("#downForm").submit();
}
</script>
<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
 <worksheet xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main" 
    xmlns:r="http://schemas.openxmlformats.org/officeDocument/2006/relationships" 
    xmlns:xdr="http://schemas.openxmlformats.org/drawingml/2006/spreadsheetDrawing" 
    xmlns:x14="http://schemas.microsoft.com/office/spreadsheetml/2009/9/main" 
    xmlns:mc="http://schemas.openxmlformats.org/markup-compatibility/2006" 
    xmlns:etc="http://www.wps.cn/officeDocument/2017/etCustomData">
    <sheetPr>
        <pageSetUpPr fitToPage="1"/>
    </sheetPr>
    <dimension ref="A1:CD43"/>
     <sheetViews>
      <sheetView showGridLines="0" tabSelected="1" zoomScale="90" zoomScaleNormal="90" workbookViewId="0">
          <pane xSplit="15" ySplit="7" topLeftCell="P8" activePane="bottomRight" state="frozen"/>
          <selection/><selection pane="topRight"/><selection pane="bottomLeft"/><selection pane="bottomRight" activeCell="K2" sqref="K2"/>
      </sheetView>
    </sheetViews>
    <sheetFormatPr defaultColWidth="9" defaultRowHeight="12.75" customHeight="1"/>
    <cols>
      <col min="1" max="1" width="1.36666666666667" style="197" customWidth="1" outlineLevel="1"/>
      <col min="2" max="2" width="1.36666666666667" style="197" customWidth="1"/>
     </cols>
     <sheetData>
        <row r="1" ht="5.15" customHeight="1" spans="34:82"><c r="AH1" s="201"/><c r="AI1" s="201"/><c r="AJ1" s="201"/><c r="AL1" s="200"/><c r="AM1" s="200"/><c r="AN1" s="200"/><c r="BC1" s="201"/><c r="BD1" s="201"/><c r="BE1" s="201"/><c r="BG1" s="200"/><c r="BH1" s="200"/><c r="BI1" s="200"/><c r="BX1" s="201"/><c r="BY1" s="201"/><c r="BZ1" s="201"/><c r="CB1" s="200"/><c r="CC1" s="200"/><c r="CD1" s="200"/></row>  
        <row r="2" ht="20.15" customHeight="1" spans="4:82">
          <c r="D2" s="202" t="s"><v>0</v></c>
          <c r="E2" s="203" t="s"><v>1</v></c>
          <c r="F2" s="202" t="s"><v>2</v></c>
          <c r="G2" s="204" t="s"><v>3</v></c>
          <c r="H2" s="205" t="s"><v>4</v></c>
          <c r="I2" s="241" t="s"><v>5</v></c>
          <c r="J2" s="242" t="s"><v>6</v></c>
          <c r="K2" s="243" t="s"><v>7</v></c>
          <!-- 其他内容忽略 -->
        </row>
        <row r="3" ht="20.15" customHeight="1" spans="4:82">
          <c r="D3" s="202" t="s"><v>9</v></c>
          <c r="E3" s="203" t="s"><v>10</v></c>
          <c r="F3" s="202" t="s"><v>11</v></c>
          <c r="G3" s="206"><v>43565</v></c>
          <c r="H3" s="202" t="s"><v>12</v></c>
          <c r="I3" s="206" t="s"><v>13</v></c>
          <!--其他单元格忽略-->
        </row>
        <row r="5" ht="20.15" customHeight="1" spans="2:82">
          <c r="B5" s="208"/>
          <c r="C5" s="209" t="s"><v>19</v></c>
          <c r="D5" s="210"/>
          <c r="E5" s="209" t="s"><v>20</v></c>
          <!--其他单元和那个忽略-->
          <c r="T5" s="256" t="s"><v>24</v></c><c r="U5" s="256"/><c r="V5" s="256"/>
          <c r="W5" s="256" t="s"><v>24</v></c><c r="X5" s="256"/><c r="Y5" s="256"/>
        </row>
        <row r="7" ht="21.75" customHeight="1" spans="2:82">
          <c r="T7" s="260" t="s"><v>56</v></c>
        </row>
        </row>
        <row r="8" s="196" customFormat="1" customHeight="1" spans="2:82">
          <c r="T8" s="260" t="s"><v>0</v></c>
        </row>
        <row r="9" s="196" customFormat="1" customHeight="1" spans="2:82">
          <c r="T9" s="260" t="s"><v>21280</v></c>
        </row>
        <!--其他行忽略-->
        <row r="28" s="196" customFormat="1" customHeight="1" spans="2:82">
            <c r="T28" s="268"><f t="shared" ref="T28:BI28" si="5">SUM(T9:T27)</f><v>345480</v></c>
        </row>
        <row r="29" s="196" customFormat="1" customHeight="1" spans="2:82">
            <c r="W29" s="270"><f>T29+W8-W28</f><v>289720</v></c>
        </row>
     </sheetData>
     <mergeCells count="42">
       <mergeCell ref="T5:V5"/><mergeCell ref="W5:Y5"/><mergeCell ref="Z5:AB5"/><mergeCell ref="AC5:AE5"/>
     </mergeCells>
     <dataValidations count="18">
       <dataValidation type="list" allowBlank="1" showInputMessage="1" showErrorMessage="1" sqref="E2">
        <formula1>&quot;TELS,TMAL&quot;</formula1>
       </dataValidation>
     </dataValidations>
    <pageMargins left="0.708661417322835" right="0.708661417322835" top="0.748031496062992" 
        bottom="0.748031496062992" header="0.31496062992126" footer="0.31496062992126"/>
    <pageSetup paperSize="8" fitToHeight="0" orientation="landscape"/><headerFooter/><legacyDrawing r:id="rId2"/>
</worksheet>

3.2.2、后台代码

       2.2结合上面的xml对其中的几个关键标签说明:

3.2.2.1、controller代码

       2.2.1  该xml会以worksheet标签开始结束

@Permission
    @RequestMapping(value = "/down")
    @ResponseBody
    public void down(@RequestParam(required = false) String fileId,HttpServletResponse response) {
        String path = referenceService.down(fileId);
        File file = new File(path);
         if(!file.exists()){
             throw new ServiceException(BizExceptionEnum.DOWN_FILE);
         }

        //创建输出流
        OutputStream out = null;
        try {
            out = response.getOutputStream();
            response.reset();
            response.setContentType(ToolUtil.getFileSuffix(path));
            String fileName = path.substring(path.lastIndexOf("\")+1);
            File tempFile =new File(fileName);  
            fileName = java.net.URLEncoder.encode(tempFile.getName(), "UTF-8");
            fileName = fileName.replace("+", "%20");  //encode后替换  解决空格问题
            response.setHeader("Content-Disposition", "attachment;filename="+ fileName);

        } catch (IOException e) {
            e.printStackTrace();
        }
        FileUtil.downloadFile(file, out);

    }

       2.2.2  sheetData标签用于包含execl的主体数据

3.2.2.2、工具类FileUtil

       2.2.2.1 sheetData的子标签row

Public class FileUtil{
public static void downloadFile(File file, OutputStream output) {
            FileInputStream fileInput = null;
            BufferedInputStream inputStream = null;
            try {
                fileInput = new FileInputStream(file);
                inputStream = new BufferedInputStream(fileInput);
                byte[] buffer = new byte[8192];//1024*8
                int i;
                while ((i = inputStream.read(buffer)) != -1) {
                    output.write(buffer,0,i);
                }
            } catch (IOException e) {
                e.printStackTrace();
            } finally {
                try {
                    if (inputStream != null)
                        inputStream.close();
                    if (fileInput != null)
                        fileInput.close();
                }catch (IOException e) {
                    e.printStackTrace();
                }
            }
        }
}

        说明:该标签用于标记一整行

4、特殊操作

        属性说明:r(行号)

4.1、获取合并单元格位置

       2.2.2.1.1 row 子标签 c 

//第五行的合并单元格
Map<Integer, Integer> map = new TreeMap<Integer, Integer>();
int sheetmergerCount = xssfSheet.getNumMergedRegions();
for (int i = 0; i < sheetmergerCount; i++) {
    CellRangeAddress ca = xssfSheet.getMergedRegion(i);
    if (ca.getFirstRow() == 5) {
        map.put(ca.getFirstColumn(), ca.getLastColumn() - ca.getFirstColumn() + 1);
    }
}

        说明:该标签表示每一个单元格

网站地图xml地图