java+layui的Excel导入导出

html：

<button class="layui-btn" onclick="exportData();">导出</button>
<button class="layui-btn" onclick="downloadTemplate();">模板下载</button>
<button id="importData" class="layui-btn" onclick="importData()">导入</button>

js：

java+layui的Excel导入导出_json 
//导入 用layui upload插件
layui.use([ "element", "laypage", "layer", "upload"], function() {
    var element = layui.element;
    var laypage = layui.laypage;
    var layer = layui.layer;
    var upload = layui.upload;//主要是这个
    layui.upload.render({
        elem: "#importData",//导入id
        url: "/xx/importData",
        size: '3072',
        accept: "file",
        exts: 'xls|xlsx|xlsm|xlt|xltx|xltm',
        done: function (result) {
            if (result.status == 0) {
                refreshTable()
            }
            if (result.message != null) {
                refreshTable();
                layer.msg(result.message)
            }
        }
    });
    refreshTable()
});
java+layui的Excel导入导出_json 
//导出
function exportData() {
    $.ajax({
        type: "post", url: "/xx/exportData", data: {}, success: function (result) {
            if (result.status == 0) {
                window.open(result.data)
            }
            if (result.message != null) {
                layer.msg(result.message)
            }
        }, error: function (XMLHttpRequest, textStatus, errorThrown) {
            layer.msg('{"status":"' + XMLHttpRequest.status + '","readyState":"' + XMLHttpRequest.readyState + '","textStatus":"' + textStatus + '","errorThrown":"' + errorThrown + '"}')
        }
    })
};

//模板下载
function downloadTemplate() {
$.ajax({
type: “post”, url: “/xx/downloadTemplate”, data: {}, success: function (result) {
if (result.status == 0) {
window.open(result.data)
}
if (result.message != null) {
layer.msg(result.message)
}
}, error: function (XMLHttpRequest, textStatus, errorThrown) {
layer.msg(’{“status”:"’ + XMLHttpRequest.status + ‘",“readyState”:"’ + XMLHttpRequest.readyState + ‘",“textStatus”:"’ + textStatus + ‘",“errorThrown”:"’ + errorThrown + ‘"}’)
}
})
}

java+layui的Excel导入导出_json 
 

 
 

 
 


 java+layui的Excel导入导出_json

 

java后端：
 


 

  java+layui的Excel导入导出_json
 
 
 
 // 导入
    @PostMapping(value = "importData")
    ResultJson importData(@RequestParam MultipartFile file) {
        ResultJson resultJson = new ResultJson();
        List<ProjectJson> importData = null;
        try {
            importData = ExcelUtil.importExcel(file.getInputStream(), ProjectJson.class);
        } catch (IOException e) {
            e.printStackTrace();
        }
        if (null == importData) {
            resultJson.setStatus(1);
            resultJson.setMessage("导入失败！");
            return resultJson;
        }
        int num = 0;

    </span><span style="color: #0000ff;">for</span><span style="color: #000000;"> (ProjectJson projectJson : importData) {
        ProjectManageEntity projectManageEntity </span>= <span style="color: #0000ff;">new</span><span style="color: #000000;"> ProjectManageEntity();
        num</span>++<span style="color: #000000;">;
        </span><span style="color: #0000ff;">if</span><span style="color: #000000;"> (CommonUtil.isEmpty(projectJson.getNumber())) {
            resultJson.setMessage(</span>"导入失败！第" + num + "行的编号不能为空"<span style="color: #000000;">);
            resultJson.setStatus(</span>1<span style="color: #000000;">);
            </span><span style="color: #0000ff;">return</span><span style="color: #000000;"> resultJson;
        }
        </span><span style="color: #0000ff;">if</span> (projectService.findByprojectNumber(projectJson.getNumber()) != <span style="color: #0000ff;">null</span><span style="color: #000000;">) {
            resultJson.setStatus(</span>1<span style="color: #000000;">);
            resultJson.setMessage(</span>"导入失败！第" + num + "行的编号重复"<span style="color: #000000;">);
            resultJson.setData(</span><span style="color: #0000ff;">null</span><span style="color: #000000;">);
            </span><span style="color: #0000ff;">return</span><span style="color: #000000;"> resultJson;
        }

        projectManageEntity.setCreateDate(</span><span style="color: #0000ff;">new</span><span style="color: #000000;"> Date());
        projectManageEntity.setUpdateDate(</span><span style="color: #0000ff;">new</span><span style="color: #000000;"> Date());
        projectManageEntity.setName(projectJson.getName());
        projectManageEntity.setNumber(projectJson.getNumber());
        projectManageEntity.setType(projectJson.getType());
        projectManageEntity.setDeleteMark(</span><span style="color: #0000ff;">false</span><span style="color: #000000;">);
        projectManageEntity.setCode(</span>"PROJECT_MANAGE" +<span style="color: #000000;"> UUID.randomUUID());
        projectService.save(projectManageEntity);
    }

    resultJson.setStatus(</span>0<span style="color: #000000;">);
    resultJson.setMessage(</span>"导入成功！"<span style="color: #000000;">);
    resultJson.setData(</span><span style="color: #0000ff;">null</span><span style="color: #000000;">);
    </span><span style="color: #0000ff;">return</span><span style="color: #000000;"> resultJson;
}

</span><span style="color: #008000;">//</span><span style="color: #008000;">导出</span>
@PostMapping(value = "exportData"<span style="color: #000000;">)
ResultJson exportData() {
    String excelTitle </span>= "项目管理"<span style="color: #000000;">;
    String path </span>= "/export/company/excel/" + DateUtil.getEightDateFormat().format(<span style="color: #0000ff;">new</span><span style="color: #000000;"> Date());
    String realPath </span>= <span style="color: #ff6600;">CommonUtil.createFolderPath</span>(propertyUtil.getUploadPath() +<span style="color: #000000;"> path);

    </span><span style="color: #0000ff;">return</span> <span style="color: #ff6600;">ExcelUtil.exportExcel</span>(excelTitle, realPath, path, ProjectJson.<span style="color: #0000ff;">class</span><span style="color: #000000;">, projectService.getAll());
}

</span><span style="color: #008000;">//</span><span style="color: #008000;">下载模板</span>
@PostMapping(value = "downloadTemplate"<span style="color: #000000;">)
</span><span style="color: #0000ff;">public</span><span style="color: #000000;"> ResultJson downloadTemplate() {
    String excelTitle </span>= "项目管理模板"<span style="color: #000000;">;
    String path </span>= "/export/company/excel/" + DateUtil.getEightDateFormat().format(<span style="color: #0000ff;">new</span><span style="color: #000000;"> Date());
    String realPath </span>= CommonUtil.createFolderPath(propertyUtil.getUploadPath() +<span style="color: #000000;"> path);
    </span><span style="color: #0000ff;">return</span> ExcelUtil.exportExcel(excelTitle, realPath, path, ProjectJson.<span style="color: #0000ff;">class</span><span style="color: #000000;">, projectService.getDownloadTemplate());
}</span></pre>







 java+layui的Excel导入导出_json

 


 

  java+layui的Excel导入导出_json
 
 
 
/**
     * 创建指定目录
     *
     * @param folderPath 目录地址
     * @return 目录地址
     */
    public static final String createFolderPath(String folderPath) {
        File file = new File(folderPath);
        if (System.getProperty("").toLowerCase().startsWith("win")) {
            String[] diskNames = {"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:"};
            for (int i = 0; i < diskNames.length; i++) {
                if (i > 0) {
                    folderPath = folderPath.replace(diskNames[i - 1], diskNames[i]);
                } else {
                    folderPath = diskNames[0] + folderPath;
                }
                file = new File(folderPath);
                if (!file.exists()) {
                    file.mkdirs();
                }
                if (file.exists()) {
                    return folderPath;
                }
            }
            return null;
        } else {
            if (!file.exists()) {
                file.mkdirs();
            }
            if (file.exists()) {
                return folderPath;
            }
            return null;
        }
    }
 
 

  java+layui的Excel导入导出_json
 


 


 

  java+layui的Excel导入导出_json
 
 
 
public class ExcelUtil {

</span><span style="color: #008000;">/**</span><span style="color: #008000;">
 * 导入Excel
 * 
 * </span><span style="color: #808080;">@param</span><span style="color: #008000;"> inputstream
 *            Excel数据流
 * </span><span style="color: #808080;">@param</span><span style="color: #008000;"> clazz
 *            注解的实体类
 * </span><span style="color: #808080;">@return</span><span style="color: #008000;"> 导入的数据
 </span><span style="color: #008000;">*/</span>
<span style="color: #0000ff;">public</span> <span style="color: #0000ff;">static</span> &lt;T&gt; List&lt;T&gt; importExcel(InputStream inputstream, Class&lt;T&gt;<span style="color: #000000;"> clazz) {
    ImportParams params </span>= <span style="color: #0000ff;">new</span><span style="color: #000000;"> ImportParams();
    params.setTitleRows(</span>1<span style="color: #000000;">);
    params.setNeedSave(</span><span style="color: #0000ff;">true</span><span style="color: #000000;">);
    </span><span style="color: #0000ff;">try</span><span style="color: #000000;"> {
        </span><span style="color: #0000ff;">return</span><span style="color: #000000;"> ExcelImportUtil.importExcel(inputstream, clazz, params);
    } </span><span style="color: #0000ff;">catch</span><span style="color: #000000;"> (Exception e) {
        e.printStackTrace();
        </span><span style="color: #0000ff;">return</span> <span style="color: #0000ff;">null</span><span style="color: #000000;">;
    }
}

</span><span style="color: #008000;">/**</span><span style="color: #008000;">
 * 导出Excel
 * 
 * </span><span style="color: #808080;">@param</span><span style="color: #008000;"> excelTitle
 *            Excel标题
 * </span><span style="color: #808080;">@param</span><span style="color: #008000;"> realPath
 *            真实路径
 * </span><span style="color: #808080;">@param</span><span style="color: #008000;"> path
 *            响应路径
 * </span><span style="color: #808080;">@param</span><span style="color: #008000;"> clazz
 *            注解的实体类
 * </span><span style="color: #808080;">@param</span><span style="color: #008000;"> excels
 *            注解的实体类集合
 * </span><span style="color: #808080;">@return</span><span style="color: #008000;"> 响应结果
 </span><span style="color: #008000;">*/</span>
<span style="color: #0000ff;">public</span> <span style="color: #0000ff;">static</span> &lt;T&gt; ResultJson exportExcel(String excelTitle, String realPath, String path, Class&lt;T&gt;<span style="color: #000000;"> clazz,
        List</span>&lt;T&gt;<span style="color: #000000;"> excels) {
    ResultJson resultJson </span>= <span style="color: #0000ff;">new</span><span style="color: #000000;"> ResultJson();
    </span><span style="color: #0000ff;">try</span><span style="color: #000000;"> {
        Workbook workbook </span>= ExcelExportUtil.exportExcel(<span style="color: #0000ff;">new</span><span style="color: #000000;"> ExportParams(excelTitle, excelTitle), clazz, excels);
        String filename </span>= excelTitle + "-" + DateUtil.getEightDateFormat().format(<span style="color: #0000ff;">new</span> Date()) + ".xls"<span style="color: #000000;">;
        File dirPath </span>= <span style="color: #0000ff;">new</span><span style="color: #000000;"> File(realPath);
        </span><span style="color: #0000ff;">if</span> (!<span style="color: #000000;">dirPath.exists()) {
            dirPath.mkdirs();
        }
        </span><span style="color: #0000ff;">try</span><span style="color: #000000;"> {
            FileOutputStream outputStream </span>= <span style="color: #0000ff;">new</span> FileOutputStream(realPath + "/" +<span style="color: #000000;"> filename);
            workbook.write(outputStream);
            resultJson.setStatus(</span>0<span style="color: #000000;">);
            resultJson.setData(path </span>+ "/" +<span style="color: #000000;"> filename);
        } </span><span style="color: #0000ff;">catch</span><span style="color: #000000;"> (Exception e) {
            e.printStackTrace();
            resultJson.setStatus(</span>1<span style="color: #000000;">);
            </span><span style="color: #0000ff;">return</span><span style="color: #000000;"> resultJson;
        }
    } </span><span style="color: #0000ff;">catch</span><span style="color: #000000;"> (Exception e) {
        e.printStackTrace();
        resultJson.setStatus(</span>1<span style="color: #000000;">);
        </span><span style="color: #0000ff;">return</span><span style="color: #000000;"> resultJson;
    }
    </span><span style="color: #0000ff;">return</span><span style="color: #000000;"> resultJson;
}






}
 


 java+layui的Excel导入导出_json

 

 


 

 
 

	</div>
	<div class="postDesc">posted @ <span id="post-date">2019-05-01 16:57</span> <a href='javascript:void(0)'>