返回
数据
分类

3.1、文件上传需要表单异步提交,之所以前面做了一个POI的用户模式解析execl的介绍

日期: 2020-03-16 20:27 浏览次数 : 112

前言:

前言:

  继续昨天的说,昨天说到用fullcalendar日期控件来显示日程,现在有一个需求是读取Excel中的日程信息,然后显示在日历上,继续记录备忘。

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

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

一、上传文件

  上传文件也困惑了我很久,今天一起记录一下。项目框架是SSH的,所以上传文件就使用了struts2的fileupload,所需要的jar包都在引入struts2的时候引入了,然后就是直接上代码操作了。

1、POI 环境

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

1.1 页面

<form id="excelform" action="。。。。。。。。。" method="post" enctype="multipart/form-data">
            <div class='excel-btn'>
                <!-- File upload field -->
                <div class="input-group input-group-sm">
                    <input id="source" type="file" name="excel" class="form-control" accept=".xlsx,.xls"/>

                        <button id="import" type="submit" class="btn btn-primary">
                            上传&nbsp;<i class="glyphicon glyphicon-upload"></i>
                        </button>

                </div>
                <!-- export agenda event field -->
                <div class="text-right" style="padding-top:10px">
                    <div class="btn-group btn-group-sm">
                        <button id="export" type="button" class="btn btn-warning">
                            导出日程&nbsp;<i class="glyphicon glyphicon-share"></i>
                        </button>
                        <a class="btn btn-success" href="/wldproject/model/events.xls">
                            模版下载&nbsp;<i class="glyphicon glyphicon-download"></i>
                        </a>
                    </div>
                </div>
            </div>
        </form>

上传文件的form必不可少的就是enctype="multipart/form-data",文件域中的accept=".xlsx,.xls"表示接收上传的文件类型,当然也可以在struts2的拦截器里面设置上传文件的大小、上传文件的类型等信息,我这里使用的是另一种方式:

<!-- 指定允许上传的文件最大字节数。默认值是2097152(2M) -->
    <constant name="struts.multipart.maxSize" value="1048576"/>
    <!-- 设置上传文件的临时文件夹,默认使用javax.servlet.context.tempdir -->
    <constant name="struts.multipart.saveDir " value="d:/tmp" />

文件接收类型在文件域中设置,允许上传文件的大小在struts2的配置文件中直接使用constant设置了,上面我标红的代码要注意一下,上次我在本地设置的D盘,但是放到服务器上的时候,服务器只有C盘没有D盘,然后就一直报错,害的我折腾了好久才看出来,这个是用来存储上传文件的临时文件夹。

1.1、软件

一,需要导的MAVEN SAX包

1.2 JS提交表单

使用ajaxForm的方式提交表单,因此要引入jquery和jquery  form的js文件

//提交表单
    $("#excelform").ajaxForm({
        beforeSubmit: showRequest, //表单验证
        success: showResponse //成功返回
    });

function showRequest(){
        var filename = $("#source").val();
        if(filename == null || filename == ''){
            alert("请选择文件...");
            $("#source").focus();
            return false;
        }
        $("#excelform").attr("action", "。。。。。。");
    }

    function showResponse(responseText, statusText){
        if(statusText=="success") {    
            if(responseText == "1") {
                alert("Excel文件导入成功");
                //重新获取所有事件数据
                $('#calendar').fullCalendar('refetchEvents');
            } else {
                alert(responseText);
            }
        } else {
            alert(statusText);
        }
    }

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.3 后台实现

private File excel;
    private String excelContentType;
    private String excelFileName;

fileupload上传文件时,先接收上面的三个参数,File 的名称要跟文件域的name属性一致,文件名称和文件类型前面要加上文件域的name属性。

public String importEvent() throws IOException {
        // 获取文件存储路径  
        // get the path to save the file
        String path = ServletActionContext.getRequest().getRealPath("/WEB-INF/upload");

        path += FileUtil.getPath();// child path
        // 获取文件存储名称
        // get the name save to
        String name = FileUtil.getName(excelFileName);
        // upload the file and return the target file object
        File file = FileUtil.upload(excel, path, name);

  在获取文件存储路径这里,我更喜欢使用String path = request.getSession().getServletContext().getRealPath("/WEB-INF/upload");因为ServletActionContext.getRequest().getRealPath("/WEB-INF/upload");现在已经不推荐使用了。

  为了读取时方便,因此当天上传的文件放在upload文件夹下面的以当天日期命名的文件夹中,为了避免重复,以当前日期时间对当前文件进行重命名。

public static String getPath(){
        Date date = new Date();
        sdf.applyPattern("/yyyy-MM-dd");
        return sdf.format(date);
    }

public static String getName(String fileName){
        Date date = new Date();
        sdf.applyPattern("HH-mm-ss");
        return sdf.format(date) + getSuffix(fileName);
    }
    /**
     * @param fileName
     * @return
     */
    public static String getSuffix(String fileName){
        int dotIndex = fileName.lastIndexOf('.');
        return fileName.substring(dotIndex);
    }

  因为主要目的是解析上传的Excel文件,因此,上传文件之后返回该文件进行解析,具体上传步骤:

/**
     * @param source
     * @param dest
     * @return 
     */
    public static File upload(File src, String path, String name) {
        File directory = new File(path);
        if(!directory.exists()){
            directory.mkdirs();
        }
        File dest = new File(path, name);
        if(upload(src, dest)){
            return dest;
        }
        return null;
    }

/**
     * @param src
     * @param dest
     */
    public static boolean upload(File src, File dest) {

        BufferedInputStream bis = null;
        BufferedOutputStream bos = null;
        byte[] buf = new byte[1024];
        int len = 0;
        try {
            bis = new BufferedInputStream(new FileInputStream(src));
            bos = new BufferedOutputStream(new FileOutputStream(dest));
            while (((len = bis.read(buf)) != -1)) {
                bos.write(buf, 0, len);
            }
        } catch (Exception e) {
            e.printStackTrace();
            return false;
        } finally {
            try {
                if (bos != null) {
                    bos.close();
                }
                if (bis != null) {
                    bis.close();
                }
            } catch (Exception e) {
                bos = null;
                bis = null;
            }
        }
        return true;
    }

上面就是文件上传的过程,上传成功后对该文件进行解析,解析Excel使用的JXL的方式,也可以使用POI的方式解析。

必赢手机登录网址 ,1.2、操作系统

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

二、JXL解析EXCEL

public boolean importEvent(File file) {

        ExcelUtil excel = new ExcelUtil();
        ExcelContent eContent = excel.getFromExcel(file);
        if(eContent == null){
            return false;
        }
        List<Agenda> alist = agendaDAO.getFromExcelContent(eContent);
        return agendaDAO.batchSave(alist);
    }

EXCEL表格可以理解为一个二维数组,因此使用List套List的方式来存储读取出来的内容;

public class ExcelContent {

    private List<String> title;//标题
    private List<List<String>> content;//内容

从excel文件中读取信息存储到ExcelContent 中:

/**
     * get contents from a excel file
     * @param file----the excel file path
     * @param hasTitle
     * @return
     */
    public ExcelContent getFromExcel(File file) {
        Workbook rwb = null;
        ExcelContent eContent = new ExcelContent();
        List<List<String>> datas = new ArrayList<List<String>>();
        try {
            rwb = Workbook.getWorkbook(file);
            Sheet sheet = rwb.getSheet(0);// deploy the first sheet
            int rows = sheet.getRows();
            // start to loop and get the datas
            for (int index = 0; index < rows; index++) {
                Cell[] cells = sheet.getRow(index);
                List<String> row = new ArrayList<String>();
                for (Cell cell : cells) {
                    row.add(getContent(cell));
                }
                if(index == 0){// title banner
                    eContent.setTitle(row);
                } else {
                    datas.add(row);
                }
            }
            eContent.setContent(datas);
        } catch (Exception e) {
            return null;
        }
        return eContent;
    }

首先需要构建一个workbook对象,也就是工作薄,可以是一个文件,也可以是一个输入流,

InputStream is = new FileInputStream(sourcefile);  

Workbook rwb = Workbook.getWorkbook(is); 

  获取到工作薄之后就是获取到工作表了,也就是sheet,这里只有一个工作表,所以使用了Sheet sheet = rwb.getSheet(0);如果一个工作薄里面有多个工作表,那么可以使用

Sheet[] sheets = rwb.getSheets();然后循环对每个sheet进行操作即可,int sheets = rwb.getNumberOfSheets();可以获取到sheet的数量。

  获取到sheet之后就可以对一个工作表进行操作了,int rows = sheet.getRows();表示获取到该sheet中的行数,int rsColumns = rs.getColumns();表示获取到总列数;

知道总行数之后循环取出每一行的数据 Cell[] cells = sheet.getRow(index);表示取出第index行的数据,取数据的时候,由于EXCEL表格中存在日期格式的,因此要对数据进行简单的处理:

/**
     * excel format
     * @param cell
     * @return
     */
    private String getContent(Cell cell){
        CellType type = cell.getType();
        if(type == CellType.DATE){
            DateCell c = (DateCell) cell;
            return sdf.format(c.getDate());
        }
        return cell.getContents();
    }

取出的第一行数据为标题,后面的为正式的数据,如果没有标题,那就不需要处理标题了。取出excel中的数据后,将其放在实现准备好的eContent 对象中返回,之后再从eContent 取出数据,存入数据库。

/** 
     * attention: no id include!!!
     * convert the Excel content to agenda objects without id included
     * @param eContent----the Excel content
     * @return a list of agenda objects
     */
    public List<Agenda> getFromExcelContent(ExcelContent eContent){

        List<String> title = eContent.getTitle();// excel title
        List<List<String>> contents = eContent.getContent();// excel rows

        List<Agenda> aList = new ArrayList<Agenda>();

        int len = title.size();
        // loop the all excel content
        for(List<String> row : contents){
            Agenda agenda = new Agenda();
            for(int i = 0; i < len; i++){
                String cell = row.get(i);
                String field = title.get(i);
                if(field.equalsIgnoreCase("title")){// title field
                    agenda.setTitle(cell.trim());
                } else if(field.equalsIgnoreCase("allday")){// all day field
                    if(cell.matches("[yY1]")){
                        agenda.setAllDay(true);
                    } else if(cell.matches("[nN0]")){
                        agenda.setAllDay(false);
                    }
                } else if(field.equalsIgnoreCase("starttime")){// start time field
                    if(!StringUtil.isSpace(cell)){
                        agenda.setStart(DateUtil.parse2Date(cell, format));
                    }
                } else if(field.equalsIgnoreCase("endtime")){// end time field
                    if(!StringUtil.isSpace(cell)){
                        agenda.setEnd(DateUtil.parse2Date(cell, format));
                    }
                } else if(field.equalsIgnoreCase("color")){// color field
                    agenda.setColor(cell.trim());
                } else if(field.equalsIgnoreCase("user")){// user field
                    agenda.setUser(cell.trim());
                } else if(field.equalsIgnoreCase("supporter")){// supporter field
                    agenda.setSupporter(cell.trim());
                }
            }
            aList.add(agenda);
        }
        return aList;
    }

这里面唯一要说的就是starttime和endtime,在excel文件中,这两个数值为时间戳,因此到这里之后需要对时间戳进行处理,转换成时间之后才能存入数据库;

 public static Date timeStamp2Date(String seconds,String format) {  
            if(seconds == null || seconds.isEmpty() || seconds.equals("null")){  
                return null;  
            }  
            if(format == null || format.isEmpty()) format = "yyyy-MM-dd HH:mm:ss";  
            SimpleDateFormat sdf = new SimpleDateFormat(format);  

            String str = sdf.format(new Date(Long.valueOf(seconds+"000")));
            return parse2Date(str,format);  
        } 

返回的alist在持久化层进行批量存储即可,这样读取EXCEL就完成了。

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

}

三、导出EXCEL

  页面在上传文件的时候已经给出了,导出启程就是查询数据库的日程,然后导出为一个excel文件即可。

2、jar包

说明:

3.1 JS实现

//export agenda
    $("#export").click(function(){
        $("#excelform").attr("action", "。。。。。。");
        document.forms[0].submit();
    });

将上面上传的form的action改成导出的action,提交表单即可。

2.1、poi.jar

        EXECL原始文件的说明:

3.2 后台实现

/**
     * @return
     * @throws IOException
     */
    public String exportEvent() throws IOException {
        // start to output
        response.addHeader("Content-Disposition", "attachment;filename=events.xls");
        response.setContentType("application/octet-stream");
        ServletOutputStream ss = response.getOutputStream();
        OutputStream stream = new BufferedOutputStream(ss);
        boolean success = excelServ.exportEvent(stream);
        if(!success){
            response.reset();
            response.setContentType("text/plain");
            PrintWriter out = response.getWriter();
            out.print("failed");
            out.flush();
            out.close();
        }
        return null;
    }

  response.addHeader("Content-Disposition", "attachment;filename=events.xls"); response.setContentType("application/octet-stream");这两句是用户点击下载按钮时,可以弹出提示框用户可以选择直接打开还是下载,牵扯到http协议的一些东西,我也不是太懂,只知道是这么写,但是太具体的我就不知道了,大家感兴趣的可以自己了解一下。

  下载设置好之后就是读取数据库数据,转换成excel格式了。

/**
     *  export events to Excel file
     * @return
     */
    public boolean exportEvent(OutputStream os) {
        List<Agenda> alist = agendaDAO.findAll();
        if(alist == null || alist.size() == 0){
            return false;
        }
        List<List<String>> content = new ArrayList<List<String>>();
        for(Agenda agenda : alist){
            // add the agenda property to a String row
            List<String> row = new ArrayList<String>();
            row = agenda.toListString();
            content.add(row);
        }
        ExcelUtil excel = new ExcelUtil();
        excel.exportToExcel(os, Agenda.getPrintHead(), content);
        return true;
    }

  前面说过excel数据就是一个二维数组,因此,可以先将查询出的日程列表进行处理,转换成List<List<String>>形式,为了实现这种功能,我在agenda中添加了toListString()方法:

 

/**
     * used to convert the Agenda object to String list
     * @return list of string array stands for every filed
     */
    public List<String> toListString(){
        // add the agenda property to a String row
        List<String> row = new ArrayList<String>();
        row.add(String.valueOf(id));
        row.add(title);

        String format = "yyyy-MM-dd";
        if(!allDay){
            format = "yyyy-MM-dd HH:mm";
        }
        row.add(DateUtil.parse2String(start, format));
        row.add(DateUtil.parse2String(end, format));
        row.add(StringUtil.bool2String(allDay));
        row.add(color);
        row.add(this.user + " ");
        row.add(this.supporter + " ");
        return row;
    }

 

返回一个String类型的list集合,添加到content中,之后再获取到要导出的数据的标题,也在agenda中实现:

/**
     * @return the String array used to export the agenda object to excel
     */
    public static String[] getPrintHead(){
        return new String[]{"ID", "title", "starttime", "endtime", "allday", "color", "user", "supporter"};
    }

这两个处理完成之后,再加上输出流即可开始导出excel文件:

/**
     * export to excel
     * @param os----the output stream of excel file to save
     * @param title----the array of the title banner
     * @param content----a array list of the data to save
     * @return
     */
    public void exportToExcel(OutputStream os, String[] title, List<List<String>> content) {
        WritableWorkbook workbook = null;//create the excel
        WritableSheet sheet = null;//create excel sheet
        // start
        try {
            workbook = Workbook.createWorkbook(os);
            sheet = workbook.createSheet("sheet1", 0);

            int rowNum = 0;
            // whether the title include in the source file
            if (title != null && title.length != 0) {
                /********** format the excel cell *************/
                WritableCellFormat title_style = cellFormat.getCellFormat(ExcelCellFormat.TITLE_CENTER);
                for (int i = 0; i < title.length; i++) {
                    sheet.addCell(new Label(i, 0, title[i], title_style));
                }
                rowNum++;
            }
            WritableCellFormat text_style = cellFormat.getCellFormat(ExcelCellFormat.TEXT_LEFT);
            for (List<String> rows : content) {
                int colNum = 0;
                for (String obj : rows) {
                    if (obj == null) {
                        obj = "";
                    }
                    Label la = new Label(colNum, rowNum, obj,text_style);
                    sheet.addCell(la);
                    colNum++;
                }
                rowNum++;
            }
            workbook.write();// write the content to the file stream
        } catch (Exception e) {
            e.printStackTrace();
        } finally {// close
            try {
                if (workbook != null) {
                    workbook.close();
                }
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
    }

  与读取excel文件类似,首先要使用workbook类的工厂方法创建一个可写入的工作薄,这里要注意的是,只能通过 API提供的工厂方法来创建Workbook,而不能使用WritableWorkbook的构造函数,因为类WritableWorkbook的构造函 数为protected类型。

  创建可写入的工作薄有两种方法,一种是file:

   jxl.write.WritableWorkbook wwb = Workbook.createWorkbook(new File(targetfile)); 

  一种是输出流:

  1.  OutputStream os = new FileOutputStream(targetfile); 
  2.     jxl.write.WritableWorkbook wwb = Workbook.createWorkbook(os);

 

 

本文使用输出流的方式进行导出,工作薄创建完成之后就需要创建工作表,使用sheet = workbook.createSheet("sheet1", 0);创建工作表,两个参数分别表示工作表的名称和工作表在工作薄中的位置。

工作薄和工作表设置好之后就是对内容进行设置了,jxl提供对单元格及其单元格中的内容进行设置的方式,比如设置字体、设置字体颜色等等。

public class ExcelCellFormat {

    public static int TITLE_CENTER = 0;
    public static int TEXT_LEFT = 1;
    public static int CELLFORMATE_TEXT_RIGHT = 2;

    public WritableCellFormat getCellFormat(int type) throws WriteException {
        WritableCellFormat cellFormat = null;
        if (TITLE_CENTER == type) {// 用于标题居中
            WritableFont BoldFont = new WritableFont(WritableFont.ARIAL,10, WritableFont.BOLD);
            cellFormat = new WritableCellFormat(BoldFont);
            cellFormat.setBorder(Border.ALL, BorderLineStyle.THIN);
            cellFormat.setVerticalAlignment(VerticalAlignment.CENTRE); // 用于文字垂直
            cellFormat.setAlignment(Alignment.CENTRE); // 文字水平对齐
            cellFormat.setWrap(false); // 文字是否换行
        } else if (TEXT_LEFT == type) {// 用于正文居左
            WritableFont NormalFont = new WritableFont(WritableFont.ARIAL, 10);
            cellFormat = new WritableCellFormat(NormalFont);
            cellFormat.setBorder(Border.NONE, BorderLineStyle.THIN); // 线条
            cellFormat.setVerticalAlignment(VerticalAlignment.CENTRE); // 文字垂直对齐
            cellFormat.setAlignment(Alignment.LEFT); // 文字水平对齐
            cellFormat.setWrap(false); // 文字是否换行
        } else if (CELLFORMATE_TEXT_RIGHT == type) {// 用于正文居左
            WritableFont NormalFont = new WritableFont(WritableFont.ARIAL, 10);
            cellFormat = new WritableCellFormat(NormalFont);
            cellFormat.setBorder(Border.NONE, BorderLineStyle.THIN); // 线条
            cellFormat.setVerticalAlignment(VerticalAlignment.CENTRE); // 文字垂直对齐
            cellFormat.setAlignment(Alignment.RIGHT); // 文字水平对齐
            cellFormat.setWrap(false); // 文字是否换行
        }
        return cellFormat;
    }
}

上面的内容表示对单元格中内容的一些设置,其他的设置大家可以查查API就可以了。

 

  上面就是使用JXL进行excel的导入导出的全部内容了,有说的不对的地方欢迎大家指正。

 

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

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

4.2、单元格数字列号转字母

        属性:r(单元格在EXECL中的编号),t(单元格数据类型),s()

public final class Columns {

    /**
     * POI读取EXECL第一行是0,第一列是0
     */
    private Columns() {
    }
    private static String[] sources = new String[] { "A", "B", "C", "D", "E",
        "F", "G", "H", "I", "J", "K", "L", "M", "N", "O", "P", "Q", "R",
        "S", "T", "U", "V", "W", "X", "Y", "Z" };

    /**
     * (256 for *.xls, 16384 for *.xlsx)
     * 
     * @param columnNum
     *            列的个数,从1开始
     * @throws IllegalArgumentException
     *             如果 columnNum 超出该范围 [1,16384]
     * @return 返回[1,columnNum]共columnNum个对应xls列字母的数组
     */
    public static String[] getColumnLabels(int columnNum) {
        if (columnNum < 1 || columnNum > 16384)
            throw new IllegalArgumentException();
        String[] columns = new String[columnNum];
        if (columnNum < 27) {
            System.arraycopy(sources, 0, columns, 0, columnNum);
            return columns;
        }
        int multiple = -1;
        int remainder;
        System.arraycopy(sources, 0, columns, 0, 26);
        int currentLoopIdx = 0;
        if (columnNum < 703) {
            for (int i = 26; i < columnNum; i++) {
                remainder = currentLoopIdx % 26;
                if (remainder == 0) {
                    multiple++;
                }
                columns[i] = sources[multiple] + columns[remainder];
                currentLoopIdx++;
            }
        } else {
            int currentLen = 26;
            int totalLen = 26;
            int lastLen = 0;
            for (int i = 26; i < columnNum; i++) {
                remainder = currentLoopIdx % currentLen;
                if (remainder == 0) {
                    multiple++;
                    int j = multiple % 26;
                    if (j == 0 && multiple != 0) {
                        lastLen = totalLen;
                        currentLen = 26 * currentLen;
                        totalLen = currentLen + lastLen;
                        currentLoopIdx = 0;
                    }
                }
                columns[i] = sources[multiple % 26]
                        + columns[remainder + lastLen];
                currentLoopIdx++;
            }
        }

        return columns;
    }

    /**
     * 返回该列号对应的字母
     * 
     * @param columnNo
     *            (xls的)第几列(从1开始)
     */
    private static String getCorrespondingLabel(int columnNo) {
        if (columnNo < 1/** ||columnNo>16384 **/
        )
            throw new IllegalArgumentException();

        StringBuilder sb = new StringBuilder(5);
        int remainder = columnNo % 26;
        if (remainder == 0) {
            sb.append("Z");
            remainder = 26;
        } else {
            sb.append(sources[remainder - 1]);
        }

        while ((columnNo = (columnNo - remainder) / 26 - 1) > -1) {
            remainder = columnNo % 26;
            sb.append(sources[remainder]);
        }

        return sb.reverse().toString();
    }

    /**
     * 列号转字母
     * 
     * @param columnIndex
     *            poi里xls的列号(从0开始)
     * @throws IllegalArgumentException
     *             if columnIndex less than 0
     * @return 该列对应的字母
     */
    public static String getIndexLabel(int columnIndex) {
        return getCorrespondingLabel(columnIndex + 1);
    }

    public static void main(String[] args) {
        System.out.println(getIndexLabel(254));
    }

        2.2.2.1.1.1 c标签的子标签 v

4.3、获取execl最大行数

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

Int maxRowNum = Sheet.getPhysicalNumberOfRows();

        2.2.2.1.1.2 c标签的字标签 f 

注:该方法可以获取sheet中的最大单元格行数,但是被编辑后,再删除的单元格也会被计算在内。

        说明:该标签表示单元格为公式型单元格

      4.4、获取execl最大列数

        属性:t(值权限:shared表示值共享)、ref(有效单元格范围)、

//需要循环每一行,比较每一行的最大列,比较后取最大的一个列作为sheet的最大列
int totalCells = 0;
int maxTotalCells = 0;
XSSFRow row=null;
For(int i=maxRowNum;i>0;i--){
row = sheet.getRow(i);
totalCells = row.getPhysicalNumberOfCells();
maxTotalCells = totalCells>maxTotalCells?totalCells:maxTotalCells;
}

        2.2.3 mergeCells标签

    4.5、获取单元格数据,并以字符串返回

        说明:sheet中所有合并单元格

/**
     * 获取cell的值
     * @param cell
     * @return
     */
    public static String getCellValue(XSSFCell cell){

        String value = "";

        if (ToolUtil.isEmpty(cell)) {
            return value;
        }else {
            if(XSSFCell.CELL_TYPE_FORMULA != cell.getCellType() && ToolUtil.isEmpty(cell.toString())){
                return value;
            }
        }

        switch (cell.getCellType()) {
        // 数值型
        case XSSFCell.CELL_TYPE_NUMERIC:
//          BigDecimal num = new BigDecimal(String.valueOf(cell.getNumericCellValue()));
//          value = String.valueOf(num);

            DecimalFormat  df  =  new DecimalFormat("##.###");
             value = df.format(cell.getNumericCellValue());
            if (null != value && !"".equals(value.trim())) {
                String item[] = value.split("\.");
                if (1 < item.length && "0".equals(item[1])) {
                    value = item[0];
                }
            }
            break;
        case XSSFCell.CELL_TYPE_STRING:
            value = cell.getStringCellValue().toString();
            break;

        case XSSFCell.CELL_TYPE_ERROR:
            value = "";
            break;
        case XSSFCell.CELL_TYPE_BLANK:
            value = "";
            break;
        case XSSFCell.CELL_TYPE_FORMULA:
            if (isNumeric(String.valueOf(cell.getNumericCellValue()))) {
                DecimalFormat dfa =  new DecimalFormat("##.###");
                 value = dfa.format(cell.getNumericCellValue());
                if (null != value && !"".equals(value.trim())) {
                    String item[] = value.split("\.");
                    if (1 < item.length && "0".equals(item[1])) {
                        value = item[0];
                    }
                }
            } else {
                value = cell.getStringCellValue().toString();
            }

            break;
        case XSSFCell.CELL_TYPE_BOOLEAN:
            value = String.valueOf(cell.getBooleanCellValue());
            break;
        default:
            value = cell.getStringCellValue().toString();
        }
        return value.trim();

    }

        属性:count合并单元格数量

5、隐含问题

        2.2.3.1 mergeCells 的子标签 mergeCell

问题1:上传文件乱码

        说明:每一个合并单元格的设置

解决方案:后台接收上传文件的表单数据时可以设置编码格式

        属性:ref(合并的单元格:将T5/U5/V5合并,ref的值为“T5:V5”),有多个合并单元格参考上面的XML

@RequestMapping(value = "/Convert/upload", method = RequestMethod.POST, produces="application/json;charset=UTF-8")

        2.2.4 dataValidations标签

问题2:方法间传递xssfsheet对象,占用过多系统资源

        说明:表示execl中的所有下拉款

解决方案:将数据取出后,存在java容器中,后续只对该容器操作

        属性:count(下拉框单元格的数量)

问题3:在循环中用集合下标获取集合对象,在取出对象属性,消耗内存

        2.2.4.1 dataValidations 标签的子标签dataValidation

解决方案:在循环外声明一个未初始化的对象接收每次循环获得的对象,然后使用这个对象再获取所需的属性;

        说明:表示每一个下拉框单元格

问题4:处理业务时使用过多list,实现逻辑消耗内存

        属性:type(数据类型)、sqref(单元格位置)

解决方案:必须使用时减少循环嵌套,可以结合map一起处理业务

        2.2.4.1.1 dataValidation标签的子标签formula1

问题5:List/ArrayList/HashMap 线程不安全

        说明:表示下拉框的值

说明:这几个容器当是局部标量时可以不考虑线程安全问题,当是当是成员变量时多并发时由于线程不安全会导致第一个请求可以正常执行,但是后面的请求操作失败(若没执行完一次把容器清空,会导致后面的数据错误;若置NULL,后面汇报NullPointException)

        2.3 对于不同单元格数据类型的处理

解决方案:List/ArrayList可以ThreadLocal<List<String>> tlCellList = new ThreadLocal<List<String>>();HashMap可以使用ConcurrentHashMap替换

        2.3.1 当c标签的属性t为s时表示字符串,此时c标签的字标签v中的值表示的是位置索引(既当v的值是6时,该单元格的值对应的是sharedStrings.xml文件中的第7个si标签的子标签的t的值)

问题6:读execl时需要考虑execl版本

*******************************时间原因,待完善,有好的建议还望提出****************************

6、注意事项

(1) 异步提交,做上传文件时,form表单中的属性需要设置处理提交方式“method="POST"”;数据类型enctype="mulitpart/form-data"

(2) 取时间戳时注意设置时区:

TimeZone timeZone = TimeZone.getTimeZone("GMT+8:00");

Date now = Calendar.getInstance(timeZone,Locale.CHINA).getTime();

SimpleDateFormat dateFormat = new SimpleDateFormat("yyyyMMddHHmmssSSS");

(3) 解析execl时创建的sheet对象、行对象、单元格对象时分别使用XSSFSheet/XSSFRow/XSSFCell对象

************有其他问题的解决方案,或是相同问题有更好的解决办法,欢迎提出****************

************下次发一个SAX解析EXECL的案例********************************************