正文
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：
//导入 用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()
});
//导出
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后端：
// 导入
@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; for (ProjectJson projectJson : importData) {
ProjectManageEntity projectManageEntity = new ProjectManageEntity();
num++;
if (CommonUtil.isEmpty(projectJson.getNumber())) {
resultJson.setMessage("导入失败！第" + num + "行的编号不能为空");
resultJson.setStatus(1);
return resultJson;
}
if (projectService.findByprojectNumber(projectJson.getNumber()) != null) {
resultJson.setStatus(1);
resultJson.setMessage("导入失败！第" + num + "行的编号重复");
resultJson.setData(null);
return resultJson;
} projectManageEntity.setCreateDate(new Date());
projectManageEntity.setUpdateDate(new Date());
projectManageEntity.setName(projectJson.getName());
projectManageEntity.setNumber(projectJson.getNumber());
projectManageEntity.setType(projectJson.getType());
projectManageEntity.setDeleteMark(false);
projectManageEntity.setCode("PROJECT_MANAGE" + UUID.randomUUID());
projectService.save(projectManageEntity);
} resultJson.setStatus(0);
resultJson.setMessage("导入成功！");
resultJson.setData(null);
return resultJson;
} //导出
@PostMapping(value = "exportData")
ResultJson exportData() {
String excelTitle = "项目管理";
String path = "/export/company/excel/" + DateUtil.getEightDateFormat().format(new Date());
String realPath = CommonUtil.createFolderPath(propertyUtil.getUploadPath() + path); return ExcelUtil.exportExcel(excelTitle, realPath, path, ProjectJson.class, projectService.getAll());
} //下载模板
@PostMapping(value = "downloadTemplate")
public ResultJson downloadTemplate() {
String excelTitle = "项目管理模板";
String path = "/export/company/excel/" + DateUtil.getEightDateFormat().format(new Date());
String realPath = CommonUtil.createFolderPath(propertyUtil.getUploadPath() + path);
return ExcelUtil.exportExcel(excelTitle, realPath, path, ProjectJson.class, projectService.getDownloadTemplate());
}
/**
* 创建指定目录
*
* @param folderPath 目录地址
* @return 目录地址
*/
public static final String createFolderPath(String folderPath) {
File file = new File(folderPath);
if (System.getProperty("os.name").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;
}
}
public class ExcelUtil {
/**
* 导入Excel
*
* @param inputstream
* Excel数据流
* @param clazz
* 注解的实体类
* @return 导入的数据
*/
public static <T> List<T> importExcel(InputStream inputstream, Class<T> clazz) {
ImportParams params = new ImportParams();
params.setTitleRows(1);
params.setNeedSave(true);
try {
return ExcelImportUtil.importExcel(inputstream, clazz, params);
} catch (Exception e) {
e.printStackTrace();
return null;
}
}
/**
* 导出Excel
*
* @param excelTitle
* Excel标题
* @param realPath
* 真实路径
* @param path
* 响应路径
* @param clazz
* 注解的实体类
* @param excels
* 注解的实体类集合
* @return 响应结果
*/
public static <T> ResultJson exportExcel(String excelTitle, String realPath, String path, Class<T> clazz,
List<T> excels) {
ResultJson resultJson = new ResultJson();
try {
Workbook workbook = ExcelExportUtil.exportExcel(new ExportParams(excelTitle, excelTitle), clazz, excels);
String filename = excelTitle + "-" + DateUtil.getEightDateFormat().format(new Date()) + ".xls";
File dirPath = new File(realPath);
if (!dirPath.exists()) {
dirPath.mkdirs();
}
try {
FileOutputStream outputStream = new FileOutputStream(realPath + "/" + filename);
workbook.write(outputStream);
resultJson.setStatus(0);
resultJson.setData(path + "/" + filename);
} catch (Exception e) {
e.printStackTrace();
resultJson.setStatus(1);
return resultJson;
}
} catch (Exception e) {
e.printStackTrace();
resultJson.setStatus(1);
return resultJson;
}
return resultJson;
}
}
