jxl实现excel导入导出的完整demo

@RequestMapping("/pointsImport.do")
public void StructureImport(HttpServletRequest request, HttpServletResponse response, Long driveId) {
    String msg = null;
    try {
        response.setContentType("text/html;charset=utf-8");
        MultipartHttpServletRequest re = (MultipartHttpServletRequest) request;
        MultipartFile fileM = re.getFile("upFile");
        CommonsMultipartFile cf = (CommonsMultipartFile) fileM;
        InputStream inputStream = cf.getInputStream();

        if (inputStream != null) {
            Workbook workbook = null;
            try {
                workbook = Workbook.getWorkbook(inputStream);
                processData(workbook, response, driveId);
            } catch (Exception e) {
                logger.error("上传文件错误:" + e);
            }
        }
    } catch (Exception e) {
        logger.error("上传文件错误:" + e);
    }

}

---

也可以用path来获取文件，可path大多时候，获取的是相对路径，具体为什么我也不清楚…..

            response.setContentType("text/html;charset=utf-8");
            MultipartHttpServletRequest re = (MultipartHttpServletRequest) request;
            CommonsMultipartFile file = (CommonsMultipartFile) re.getFile("upFile");
            DiskFileItem dfi = (DiskFileItem) file.getFileItem();
            String path = dfi.getStoreLocation().getPath().toString();
            File toFile = new File(path);

            if (toFile != null) {

                //file.transferTo(toFile);
                Workbook workbook = null;
                try {
                    // excel文件上传
                    workbook = Workbook.getWorkbook(toFile);
                    //处理数据，如果需要userid的话，这里可以追加
                    processData(workbook, response);
                } catch (Exception e) {
                    msg = "上传文件错误!";
                }
            }
        } catch (Exception e) {
            msg = "上传文件错误!";
        }

上传控件：

<form id="edit_form" action="<%=basePath%>tpManagerDownLoad/pointsImport.do" method="post" class="data-form" enctype="multipart/form-data">
    <input type="hidden" name="deviceStoreId" value="${deviceStoreId }" />
    <input type="hidden" name="id" value="${driveId}" />
    <input type="hidden" id="document" name="document" value="${document }" />
    <table width="100%">
        <tbody>
        <tr>
            <td class="field-lable"><label>驱动文件：</label></td>
            <td><input type="file" title="请选择文件" id="upFile"  name="upFile" onselect="getFileName()" onchange="getFileName()"/></td>
        </tr>
        </tbody>
    </table>
    <table class="btn-ct">
        <tbody><tr>
            <td>
                <button type="submit" id="save">确定</button>
            </td>
        </tr>
        </tbody></table>
    </div>
</form>

---

也可以这么写：

               <div class="panel-top-banner">
                        <form action="StructureExIm/StructureImport.do" id="form" method="post" enctype="multipart/form-data">
                            上传:
                            <input id="upFile" name="upFile" type="file" />
                            <input type="submit" value="提交"/>
                            <br/>
                        </form>
                </div>

所有上传和下载的代码demo：

package com.tdenergys.platform.web.controller;

import com.tdenergys.platform.core.model.Project;
import com.tdenergys.platform.core.model.Structure;
import com.tdenergys.platform.core.service.IProjectService;
import com.tdenergys.platform.core.service.IStructureService;
import jxl.*;
import jxl.format.Colour;
import jxl.format.UnderlineStyle;
import jxl.write.*;
import jxl.write.biff.RowsExceededException;
import org.apache.commons.fileupload.disk.DiskFileItem;
import org.apache.commons.lang.StringUtils;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.multipart.MultipartHttpServletRequest;
import org.springframework.web.multipart.commons.CommonsMultipartFile;

import javax.annotation.Resource;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.File;
import java.lang.Boolean;
import java.util.*;
import java.util.regex.Matcher;

/**
* 结构导入
* Created by Jerry.ZR on 15-3-4.
*/
@Controller
@RequestMapping("/StructureExIm")
public class StructureExImController {

    @Resource(name = "structureService")
    private IStructureService structureService;

    @Resource(name = "projectService")
    private IProjectService projectService;


    private static final String IMPORT_TEMP_FILE_NAME = "Structure_import_temp.xls";

    private static final String[] STORE_HOUSE_IMPORT_TEMP_FILE_TITLE =
            {"项目名称", "父亲名称", "结构名称", "结构编号"};

    private static final String[] STORE_HOUSE_IMPORT_TEMP_FILE_TITLE_ID =
            {"project_id", "parent_id", "name", "code"};

    public static final String STORE_HOUSE_FAIL = "失败";

    public static final String STORE_HOUSE_SUCCESS = "成功";

    private static final String IMPORT_TEMP_RESULT_NAME = "Structure_import_result";

    //数据字典页的表头
    private static final String[] STORE_HOUSE_DICTIONARY_TITLE = {"项目名称"};
    //与数据字典表头对应的id号
    private static final String STORE_HOUSE_DICTIONARY_ID = "projectName";

    /**
     * 设备导入 模版下载
     *
     * @param request
     * @param response
     * @return
     */
    @RequestMapping("/StructureDownload.do")
    public void StructureDownload(HttpServletRequest request, HttpServletResponse response) {
        response.setCharacterEncoding("utf-8");
        response.setContentType("aplication/msexcel;charset=utf-8"); // 设置文件类型
        response.setHeader("Content-disposition", "attachment; filename=" + IMPORT_TEMP_FILE_NAME);

        WritableWorkbook workbook = null;
        String[] titles = STORE_HOUSE_IMPORT_TEMP_FILE_TITLE;
        String[] titleIds = STORE_HOUSE_IMPORT_TEMP_FILE_TITLE_ID;

        try {
            // 创建工作簿
            workbook = Workbook.createWorkbook(response.getOutputStream());
            // 创建导入数据页
            WritableSheet sheet0 = workbook.createSheet("导入数据", 0);

            CellView cellView = new CellView();
            // 设置自动大小
            cellView.setAutosize(true);
            // 设置字体 普通格式
            WritableFont font = new WritableFont(WritableFont.ARIAL, 10);
            WritableCellFormat cellFormat = new WritableCellFormat(font);
            // 设置背景颜色;
            cellFormat.setBackground(Colour.YELLOW);

            // 设置必填字段为红色字体 特殊字体
            WritableFont fontRed = new WritableFont(WritableFont.ARIAL, 10, WritableFont.NO_BOLD, false, UnderlineStyle.NO_UNDERLINE, Colour.RED);
            WritableCellFormat cellFormatRed = new WritableCellFormat(fontRed);
            // 设置背景色为黄色
            cellFormatRed.setBackground(Colour.YELLOW);

            // 定义单元格为文本样式
            WritableCellFormat wcf = new WritableCellFormat(NumberFormats.TEXT);
            // 定义一个列显示样式
            CellView cv = new CellView();
            // 把定义的单元格格式初始化进去
            cv.setFormat(wcf);
            // 设置列宽度（不设置的话是0，不会显示）
            cv.setSize(10 * 265);

            //excel写上注释
            WritableCellFeatures projectNameWCF = new WritableCellFeatures();
            projectNameWCF.setComment("项目名称不能为空");

            WritableCellFeatures structureNameWCF = new WritableCellFeatures();
            structureNameWCF.setComment("结构名称不能为空");


            if (titles != null) {

                for (int i = 0; i < titles.length; i++) {
                    // 将所有列设置为文本格式，且宽度为10 * 265
                    sheet0.setColumnView(i, cv);

                    String title = titles[i];
                    String titleId = titleIds[i];
                    if (StringUtils.isNotBlank(title)) {
                        // 列名
                        Label label = null;
                        // 设置必填字段为红色字体
                        if (i == 0 || i == 2) {
                            // Label(列,行,标题)
                            label = new Label(i, 0, titles[i], cellFormatRed);
                        } else {
                            label = new Label(i, 0, titles[i], cellFormat);
                        }
                        //编写excel注释
                        if ("project_id".equals(titleId)) {
                            label.setCellFeatures(projectNameWCF);
                        }
                        if ("name".equals(titleId)) {
                            label.setCellFeatures(structureNameWCF);
                        }
                        sheet0.addCell(label);
                    }
                }
            }

            // 创建数据辞典页
            WritableSheet sheet1 = workbook.createSheet("数据辞典", 1);

            Map<String, Object> result2 = new HashMap<String, Object>();

            Project p = new Project();
            List<Project> list = projectService.selectEntitys(p);
            result2.put("projectName", list);

            List<Object> listProject = new ArrayList<Object>();


            //将多个list的数据放入一个Map中
            if (list.size() != 0) {
                List<Project> valueProject = (List<Project>) result2.get("projectName");
                for (int j = 0; j < valueProject.size(); j++) {
                    listProject.add(valueProject.get(j).getName());
                }
            }

            Map<String, Object> result = new HashMap<String, Object>();
            result.put("projectName", listProject);

            String[] DataDictionary = STORE_HOUSE_DICTIONARY_ID.split(",");
            String[] dictionaryTitles = STORE_HOUSE_DICTIONARY_TITLE;

            //对应sheet0中的第几列
            int[] dictionaryId = {0};
            String key = "";
            String dictionaryTitle = "";
            int columnNum;

            //每列都执行
            for (int i = 0; i < DataDictionary.length; i++) {
                //key为第i列的列名id
                key = DataDictionary[i];
                //对应sheet0中的第几列
                columnNum = dictionaryId[i];
                //value接收的是result中第一列的值
                List<Object> value = (List<Object>) result.get(key);

                List<String> codeList = new ArrayList<String>();
                //获取第I列的文本信息
                dictionaryTitle = dictionaryTitles[i];
                //给第一列命名
                if (StringUtils.isNotBlank(dictionaryTitle)) {
                    Label label = new Label(i, 0, dictionaryTitles[i], cellFormat);
                    sheet1.addCell(label);
                }

                if (value != null) {
                    for (int j = 0; j < value.size(); j++) {
                        String manufactureValue = "";
                        Object manufactureValueMap = value.get(j);
                        manufactureValue = manufactureValueMap.toString();
                        Label label = new Label(i, j + 1, manufactureValue);
                        sheet1.addCell(label);
                        codeList.add(manufactureValue);
                    }

                    // 设置数据有效性,默认设置1000行
                    int[] target = new int[2];
                    int[] current = new int[2];
                    //将列表框的数据隐藏在22行附近，这个列数可以随意改，只要比当前列数大就行
                    int defultColumn = 22;
                    Label label = new Label(defultColumn + i, 0, dictionaryTitles[i]);
                    //从第几行开始展现下拉框
                    for (int j = 1; j < 1000; j++) {
                        //行
                        target[0] = columnNum;
                        target[1] = j;
                        //列
                        current[0] = defultColumn + i;
                        current[1] = 1;

                        buildDataValidationCell(sheet0, target, current, codeList);
                    }

                }

            }


            workbook.write();
            workbook.close();
        } catch (Exception e) {
            e.printStackTrace();
//            logger.error("导出excel文件错误!" + e);
        }
    }

    /**
     * 创建excel的数据有效性
     *
     * @param sheet
     * @param target  要设置数据验证性的单元格位置
     * @param current 要存放验证性数据的单元格位置
     * @param data
     * @return
     */
    private int[] buildDataValidationCell(WritableSheet sheet, int[] target, int[] current, List<String> data) {
        try {
            //data存储的是下拉框中的信息
            if (data.size() == 0)
                return current;
            //strings去掉data中空的数据
            List<String> strings = new ArrayList<String>();
            for (String d : data) {
                if (d != null && !"".equals(d.trim()))
                    strings.add(d);
            }
            //如果有65535行数据，那么报错
            if (strings.size() > 65535)
                throw new RuntimeException("excel2003单列上限,数据验证只能使用单列或单行");
            // excel2003上限是65535，所以隐藏的行数超过5w就换另外一列，
            // 列上限是255。暂时不考虑行列都到上限的情况
            if (current[1] > 50000 || (current[1] > 50000 && strings.size() > 15535)) {
                current[0] = current[0] + 1;
                current[1] = 0;
            }
            // 构建下拉数据范围
            buildDataValidationRange(sheet, target, current, strings);
            // 设置隐藏
            CellView cv = new CellView();
            cv.setHidden(true);
            sheet.setColumnView(current[0], cv);
            // 填充数据
            for (String s : strings) {
                Label b = new Label(current[0], current[1], s);
                sheet.addCell(b);
                current[1] += 1;
            }
        } catch (RowsExceededException e) {
            e.printStackTrace();
        } catch (WriteException e) {
            e.printStackTrace();
        }
        return current;

    }

    /**
     * 构建下拉数据范围
     *
     * @param sheet
     * @param target  要设置数据验证性的单元格位置
     * @param current 要存放验证性数据的单元格位置
     * @param strings
     * @throws WriteException
     * @throws RowsExceededException
     */
    private void buildDataValidationRange(WritableSheet sheet, int[] target, int[] current, List<String> strings) throws WriteException, RowsExceededException {

        WritableCellFeatures wcf = null;
        // 数据验证初始cell值，默认为空
        Label targetCell = new Label(target[0], target[1], "");
        wcf = new WritableCellFeatures();
        // 数据验证范围
        StringBuffer buff = new StringBuffer();
        // 起点
        CellReferenceHelper.getCellReference(current[0], true, current[1], true, buff);
        buff.append(":");
        // 终点
        CellReferenceHelper.getCellReference(current[0], true, current[1] + strings.size() - 1, true, buff);
        // 设置数据验证性
        wcf.setDataValidationRange(buff.toString());
        targetCell.setCellFeatures(wcf);
        sheet.addCell(targetCell);
    }


    /**
     * 对导入模版excel进行解析并插入数据库
     *
     * @param request
     * @param response
     * @return
     */

    @RequestMapping("/StructureImport.do")
    public void StructureImport(HttpServletRequest request, HttpServletResponse response) {
        String msg = null;
        try {
这里还可以用inputStream
===========================================================================================================
@RequestMapping("/pointsImport.do")
public void StructureImport(HttpServletRequest request, HttpServletResponse response, Long driveId) {
    String msg = null;
    try {
        response.setContentType("text/html;charset=utf-8");
        MultipartHttpServletRequest re = (MultipartHttpServletRequest) request;
        MultipartFile fileM = re.getFile("upFile");
        CommonsMultipartFile cf = (CommonsMultipartFile) fileM;
        InputStream inputStream = cf.getInputStream();

        if (inputStream != null) {
            Workbook workbook = null;
            try {
                workbook = Workbook.getWorkbook(inputStream);
                //处理数据，如果需要userId的话，这里可以追加
                processData(workbook, response, driveId);
            } catch (Exception e) {
                logger.error("上传文件错误:" + e);
            }
        }
    } catch (Exception e) {
        logger.error("上传文件错误:" + e);
    }

}
=============================================================



            response.setContentType("text/html;charset=utf-8");
            MultipartHttpServletRequest re = (MultipartHttpServletRequest) request;
            CommonsMultipartFile file = (CommonsMultipartFile) re.getFile("upFile");
            DiskFileItem dfi = (DiskFileItem) file.getFileItem();
            String path = dfi.getStoreLocation().getPath().toString();
            File toFile = new File(path);

            if (toFile != null) {

                //file.transferTo(toFile);
                Workbook workbook = null;
                try {
                    // excel文件上传
                    workbook = Workbook.getWorkbook(toFile);
                    //处理数据，如果需要userid的话，这里可以追加
                    processData(workbook, response);
                } catch (Exception e) {
                    msg = "上传文件错误!";
                }
            }
        } catch (Exception e) {
            msg = "上传文件错误!";
        }

        // return null;
    }


    private String processData(Workbook workbook, final HttpServletResponse response) {
        String msg = null;
        try {            // 获取第一页
            Sheet sheet = workbook.getSheet(0);
            // 获取行数
            int rows = sheet.getRows();
            if (rows > 1) {
                // 获取有多少列
                int columns = sheet.getColumns();
                int titleLength = STORE_HOUSE_IMPORT_TEMP_FILE_TITLE.length;
                // 比较title数量与sheet实际列数,切掉多余列
                if (columns > titleLength)
                    columns = titleLength;

                List<List<String>> resultList = new ArrayList<List<String>>();
                List<List<String>> successResultList = new ArrayList<List<String>>();
                //储存excel中所有行的数据，用来辨别找不到父亲的记录
                List<String[]> allDatas = getAllExcelData(rows, sheet);
                //检验excel中是否存在重复
                List<List<String>> checkResultList = new ArrayList<List<String>>();
//=========================================================
                //取出数据表中的所有记录，放入内存
                Structure a = new Structure();
                List<Structure> list = structureService.selectEntitys(a);
//=========================================================


                boolean checkAllSuccess = true;
                // 从第1行开始遍历，i=0时候是标题行
                for (int i = 1; i < rows; i++) {
                    // data存储每一行的所有数据（所有列）
                    String[] datas = new String[titleLength + 2];
                    // 如果名称（第一列）为空则不校验此行数据
                    if (StringUtils.isBlank(sheet.getCell(0, i).getContents()) && StringUtils.isBlank(sheet.getCell(2, i).getContents())) {
                        continue;
                    }
                    for (int j = 0; j < columns; j++) {
                        Cell cell = sheet.getCell(j, i);
                        if (StringUtils.isNotBlank(cell.getContents())) {
                            datas[j] = cell.getContents().trim();
                        } else {
                            datas[j] = "";
                        }
                    }

                    String[] datasClon = datas.clone();
                    if (columns == titleLength) {
                        //校验数据
                        String result = checkData(datas, datasClon, list);
                        //检验excel中是否有重复数据
                        result += isExistInExcel(checkResultList, datasClon);
                        checkResultList.add(Arrays.asList(datasClon));
                        //检测有没有找不到父亲的记录
                        result += findFather(datasClon, allDatas);


                        int leng = titleLength + 2;
                        if (StringUtils.isNotBlank(result)) {
                            datas[leng - 2] = STORE_HOUSE_FAIL;
                            datas[leng - 1] = result;
                            checkAllSuccess = false;
                        } else {
                            datas[leng - 2] = STORE_HOUSE_SUCCESS;
                            successResultList.add(Arrays.asList(datasClon));
                        }
                    }
                    resultList.add(Arrays.asList(datas));
                }

                // 只有所有的数据都校验成功后，再进行批量保存数据
                if (checkAllSuccess) {

                    cleanData(successResultList);

                }
                // 将结果数据导出
                if (resultList.size() > 0) {
                    String[] titles = new String[columns + 2];
                    for (int i = 0; i < columns; i++) {
                        titles[i] = null;
                    }
                    int leng = titleLength + 2;
                    titles[leng - 2] = "处理结果";
                    titles[leng - 1] = "原因";
                    //如果出错的话，返回一个excel。
                    commonExportExcel(IMPORT_TEMP_RESULT_NAME, titles, resultList, workbook, response);
                } else {
                    msg = "处理成功!";
                }
            } else {
                msg = "上传文件没有内容信息!";
            }
        } catch (Exception e) {
            msg = "读取excel异常!";
        } finally {
            if (workbook != null) {
                workbook.close();
            }
        }


        return msg;
    }


    private String checkData(String[] datas, String[] datasClon, List<Structure> list) {
        String error = "";

//        //检验数据是否重复
        error += isExistInDBBySelectAll(datasClon, list);
//
//        // 校验不能为空的字段：名称      编号
        int[] checkIsBlankId = {2};
        error += checkDataIsBlank(datasClon, checkIsBlankId, STORE_HOUSE_IMPORT_TEMP_FILE_TITLE);
//
//        // 校验期数字段为正整数,长度暂定为15，
//        // 2是地区编号 4是邮编 7是电话 8是oldId
//        int[] checkBatchNo = {4, 7};
//        for (int i = 0; i < checkBatchNo.length; i++) {
//            if (!StringUtils.isBlank(datas[i]))
//                error += checkNumber(datasClon, checkBatchNo[0], STORE_HOUSE_IMPORT_TEMP_FILE_TITLE, "+", 15);
//        }
        return error;
    }


    /**
     * 校验不能为空的字段
     *
     * @param datas      需要校验的数据
     * @param titleId    需要校验的字段在数据数组中的位置
     * @param titleNames 需要校验的数据的所有字段的中文名称
     * @return
     */
    private String checkDataIsBlank(String[] datas, int[] titleId, String[] titleNames) {
        String error = "";
        if (titleId != null && datas != null && titleNames != null) {
            for (int i = 0; i < titleId.length; i++) {
                int checkId = titleId[i];
                String checkValue = datas[checkId];
                if (StringUtils.isBlank(checkValue)) {
                    error += titleNames[checkId] + "为空，";
                }
            }
        }
        return error;
    }

    /**
     * @param datas      需要校验的数据
     * @param titleId    需要校验的字段在数据数组中的位置
     * @param titleNames 需要校验的数据的所有字段的中文名称
     * @param type       "0+":非负整数 "+":正整数 "-0":非正整数 "-":负整数 "":整数
     * @param MaxLength  数字的最大位数
     * @return
     */
    private String checkNumber(String[] datas, int titleId, String[] titleNames, String type, int MaxLength) {
        String error = "";
        String eL = "";
        String value = "";
        if (type.equals("0+")) {
            eL = "^\\d+$";// 非负整数
            value = "非负整数";
        } else if (type.equals("+")) {
            eL = "^\\d*[1-9]\\d*$";// 正整数
            value = "正整数";
        } else if (type.equals("-0")) {
            eL = "^((-\\d+)|(0+))$";// 非正整数
            value = "非正整数";
        } else if (type.equals("-")) {
            eL = "^-\\d*[1-9]\\d*$";// 负整数
            value = "负整数";
        } else {
            eL = "^-?\\d+$";// 整数
            value = "整数";
        }

        if (datas != null && titleNames != null) {
            String checkValue = datas[titleId];
            java.util.regex.Pattern p = java.util.regex.Pattern.compile(eL);
            Matcher m = p.matcher(checkValue);
            boolean b = m.matches();
            if (!b) {
                error += titleNames[titleId] + "不是" + value;
            } else {
                int length = checkValue.length();
                if (length > 2 && "-".equals(checkValue.substring(0, 1))) {
                    length = length - 1;
                }
                if (length > MaxLength) {
                    error += titleNames[titleId] + "大于" + MaxLength + "位的" + value;
                }
            }

        }

        return error;
    }


    /**
     * 导出excel文件
     *
     * @param name     文件名
     * @param titles   标题
     * @param dataList 内容数据
     * @param response
     */
    public void commonExportExcel(String name, String[] titles, List<List<String>> dataList, Workbook workbook, HttpServletResponse response) {
        try {

            response.setCharacterEncoding("utf-8");
            response.setContentType("aplication/msexcel;charset=utf-8"); // 设置文件类型
            response.setHeader("Content-disposition", "attachment; filename=" + name + ".xls");

            WritableWorkbook book = null;
            WritableSheet sheet = null;

            WorkbookSettings settings = new WorkbookSettings();
            settings.setWriteAccess(null);


            if (workbook != null) {
                book = Workbook.createWorkbook(response.getOutputStream(), workbook, settings); // 创建文件
                sheet = book.getSheet(0);
            } else {
                book = Workbook.createWorkbook(response.getOutputStream());
                sheet = book.createSheet("sheet", 0);
            }

            if (titles != null) {
                Cell cell = sheet.getCell(0, 0);
                for (int i = 0; i < titles.length; i++) {
                    String title = titles[i];
                    if (StringUtils.isNotBlank(title)) {
                        Label label = new Label(i, 0, titles[i]);
                        label.setCellFormat(cell.getCellFormat());
                        sheet.addCell(label);
                    }
                }
            }

            for (int i = 0; i < dataList.size(); i++) {
                List<String> list = dataList.get(i);
                for (int j = 0; j < list.size(); j++) {
                    Label label = new Label(j, i + 1, list.get(j));
                    sheet.addCell(label);
                }
            }
            book.write();
            book.close();
        } catch (Exception e) {

        }
    }

    /**
     * 判断excel中是否有重复数据
     *
     * @param datas
     * @param current
     * @return
     */
    private String isExistInExcel(List<List<String>> datas, String[] current) {
        String error = "";
        for (int i = 0; i < datas.size(); i++) {
            Boolean check = true;
            for (int j = 0; j < datas.get(i).size() - 2; j++) {
                if (!current[j].equals(datas.get(i).get(j))) {
                    check = false;
                    break;
                }
            }
            if (check) {
                error += "与excel第" + (i + 1) + "行重复;";
            }
        }
        return error;
    }

    /**
     * 判断此记录数据库中是否已经存在，目前是根据设备所属项目，设备名称，两个来确定一条记录
     * <p/>
     * 这种方式是先将数据表中所有数据都取出来放入内存中，然后每一条数据都到这个list中进行匹配，这样
     * 只访问一次数据库，其他全在内存中操作，速度会非常快，但有可能会消耗大量内存，目前没有观察到内存方面都压力
     *
     * @param datas
     * @return
     */

    private String isExistInDBBySelectAll(String[] datas, List<Structure> list) {
        String error = "";
        Long projectId = new Long(-1);
        //
        if (StringUtils.isNotBlank(datas[0])) {
            projectId = selectProjectName(datas[0]).getId();
        }

        //循环从内存中取出结果
        for (int i = 0; i < list.size(); i++) {
            Structure structure = new Structure();
            structure = list.get(i);

            if ( structure.getProjectId()==projectId
                    && datas[2].equals(structure.getName())) {
                error += "此条记录数据库已存在;";
                break;
            }
            }

        return error;
    }

    /**
     * 根据project名称名称取id
     *
     * @param
     * @return
     */
    private Project selectProjectName(String projectName) {
        Project project = new Project();
        project.setName(projectName);
        return projectService.selectEntity(project);
    }

    /**
     * 根据parentName取ID
     *
     */
    private Structure selectStructureParentId(String ParentName) {
        Structure structure = new Structure();
        structure.setName(ParentName);
        return structureService.selectEntity(structure);
    }

    /**
     * 保存数据
     *
     * 这里的保存数据比较特殊，必须按层级来保存，先保存祖先，再保存第二辈祖先
     * 保证有祖先的都能在数据库里面找到id号，而且能走到这一步的数据，都是肯定能找到祖先的
     *
     * @param dataList 入库数据
     */
    private void saveData(List<List<String>> dataList) {

        List<Structure> structures = new ArrayList<Structure>();

        for (List<String> data : dataList) {
            Structure structure = new Structure();
            Long projectId = null;
            Long structureId = null;
            //项目id,项目名称不为空时候，用它去取对应的id
            if (StringUtils.isNotBlank(data.get(0)))
                projectId = selectProjectName(data.get(0)).getId();
            //parentId
            if (StringUtils.isNotBlank(data.get(1)))
               structureId = selectStructureParentId(data.get(1)).getId();

            structure.setProjectId(projectId);
            structure.setParentId(structureId);
            structure.setName(data.get(2));
            structure.setCode(data.get(3));

            structures.add(structure);
        }
        structureService.insertBatch(structures);
    }

    /**
     * 整理数据，把数据分层
     */
    private List<List<String>> cleanData(List<List<String>> dataList){
        Map<String,Object> a = new HashMap<String, Object>();
        //先把父亲为空的放进去
        a = findRoot(dataList);
        List<List<String>> paixu = (List<List<String>>) a.get("root");
        dataList = (List<List<String>>) a.get("dataList");
        a.clear();
        saveData(paixu);
        //递归，一级级走下去
        while (dataList.size()!=0){
        //再把第二级放进去
        a = findNextRoot(dataList,paixu);
        List<List<String>> paixuN = (List<List<String>>) a.get("root");
        dataList = (List<List<String>>) a.get("dataList");
        a.clear();
        saveData(paixuN);
        paixu=paixuN;
        }
        return null;
    }

    /**
     * 寻找第一层的祖先
     */
    private Map<String,Object> findRoot(List<List<String>> dataList){
        List<List<String>> root = new ArrayList<List<String>>();
        Map<String,Object> a = new HashMap<String, Object>();
        //祖先加入root
        for (List<String> data : dataList) {
            if (!StringUtils.isNotBlank(data.get(1))){
                root.add(data);
            }
        }
        //祖先加完后，从总数据中移除
        for(List<String> data : root){
            dataList.remove(data);
        }
        a.put("root",root);
        a.put("dataList",dataList);
        return a;
    }
    /**
     * 寻找第二级祖先,
     */
    private Map<String,Object> findNextRoot(List<List<String>> dataList,List<List<String>> paixu){
        List<List<String>> root = new ArrayList<List<String>>();
        Map<String,Object> a = new HashMap<String, Object>();
        for (List<String> data : dataList) {
            for (List<String> dataCompare : paixu){
                if (data.get(1).equals(dataCompare.get(2))){
                    root.add(data);
                    break;
                }
            }
        }

        //祖先加完后，从总数据中移除
        for(List<String> data : root){
            dataList.remove(data);
        }

        a.put("root",root);
        a.put("dataList",dataList);
        return a;
    }


    /**
     * 获取excel所有行数据
     */
    private List<String[]> getAllExcelData(int rows, Sheet sheet) {

        List<String[]> allData = new ArrayList<String[]>();
        int titleLength = STORE_HOUSE_IMPORT_TEMP_FILE_TITLE.length;
        int columns = titleLength;

        for (int i = 1; i < rows; i++) {
            // data存储每一行的所有数据（所有列）
            String[] datas = new String[titleLength + 2];
            // 如果名称（第一列）为空则不校验此行数据
            if (StringUtils.isBlank(sheet.getCell(0, i).getContents()) && StringUtils.isBlank(sheet.getCell(2, i).getContents())) {
                continue;
            }
            for (int j = 0; j < columns; j++) {
                Cell cell = sheet.getCell(j, i);
                if (StringUtils.isNotBlank(cell.getContents())) {
                    datas[j] = cell.getContents().trim();
                } else {
                    datas[j] = "";
                }
            }
            allData.add(datas);
        }

        return allData;
    }

    /**
     * 检测有没有找不到父亲的孩子
     */
    private String findFather(String[] data, List<String[]> allDatas) {
        String error = "";
        Boolean findFather = false;
        //data[1]是父亲,data[2]是name,父亲不为空的话，就去找父亲
        if (StringUtils.isNotBlank(data[5])) {
            for (String[] datalinshi : allDatas)
                if (datalinshi[6].equals(data[5])) {
                    findFather = true;
                    break;
                }
//============================================================

//如果说在excel中没有父亲但是在数据库里面有父亲的话，那么也应该算是正常记录，我们也应该找到，这个后期做

//===============================================
            if (findFather==false)
                error +="这条记录的父亲不存在;";
        }
        return error;
    }

}