使用Java导入导出Excel文档

ResourceUtil工具类

import java.io.ByteArrayOutputStream;
import java.io.IOException;
import java.io.InputStream;

public class ResourceUtil {
	//需要在ResourceUtil同级目录下创建excel包，然后把文件放进去
	public static final String EXCEL_MODEL = "excel/model.xlsx";
	
	public static InputStream getInputStream(String resource) {
		return ResourceUtil.class.getResourceAsStream(resource);
	}
	
	public static byte[] getBytes(String resource) throws IOException {
		InputStream is = null;
		try {
			is = ResourceUtil.class.getResourceAsStream(resource);
			ByteArrayOutputStream os = new ByteArrayOutputStream();
			byte[] temp = new byte[1024];
			int length = -1;
			while ((length = is.read(temp)) >= 0) {
				os.write(temp, 0, length);
			}
			return os.toByteArray();
		} finally {
			if (is != null) {
				is.close();
			}
		}
	}
}

导出前端

页面

<div >
	<button type="button" id="btn-export-excel" onclick = "toExport()">导出</button>
</div>

JS

function toExport() {
	var url = "xxoo.toExport";
	$.ajax({
        type: "POST",
        url: url,
        dataType: "json",
        success: function (data) {
        	alert("导出成功！");
        },
        error: function (data) {
        	alert("导出失败！");
        }
    });
}

导出后端

@RequestMapping("/toExport")
public void toExport(HttpServletRequest request, HttpServletResponse response){
		// 添加Worksheet（不添加sheet时生成的xls文件打开时会报错)
		XSSFWorkbook workbook = null;
		ServletOutputStream out = null;
		String fileName = "模板.xlsx";
		try {
			String modelFile = ResourceUtil.EXCEL_MODEL;
			URL url = ResourceUtil.class.getResource(modelFile);
			String path = url.toString().substring(6, url.toString().length());
			workbook = new XSSFWorkbook(new FileInputStream(path));
			// 新建文件
			response.setContentType("application/msexcel;charset=UTF-8");
			response.setHeader("Content-Disposition", "attachment; filename=" + URLEncoder.encode(fileName, "UTF-8"));
			out = response.getOutputStream();
			workbook.write(out);
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			 if (out != null) {
	                try {
	                    out.flush();
	                    out.close();
	                } catch (IOException e) {
	                    logger.debug("导出Excel异常");
//	                    resultMap.put("RESULT", "FAIL");
	                }
	            }
		}
	}

导入前端

页面

<div >
	<button type="button" id="btn-upload-excel">导入</button>
	<form method="post" id="file" action="" enctype="multipart/form-data">
		<input type="file" style='display:none' />
	</form>
</div>

JS

$("#btn-upload-excel").click(function() {
	$("#loaddiv").show();
	$("#filename").click();
});

$("#filename").change(function() {
	if($(this).val()==""){//未选择文件
		alert("请选择要上传的文件！");
		$("#loaddiv").hide();
		return;
	}
	var formData = new FormData($("#file")[0]);
	var f = $(this)[0].files[0];
	var fileSize = 0;
    fileSize = f["size"];
    var size = fileSize / 1024 /1024;
	//文件大小校验
	if(size > 10){
         alert("导入的文件不能大于10M!");
         $("#loaddiv").hide();
         $(this).val("");
         return;   
     }
	//文件类型校验
	var name = f["name"];
    var fileName = name.substring(name.lastIndexOf(".")+1).toLowerCase();
    if(fileName !="xlsx" && fileName !="xls"){
        alert("请选择Excel文件格式文件上传!");
        $("#loaddiv").hide();
        $(this).val("");
        return;
    }
     if(fileName.replace(/[^\u0000-\u00ff]/g,"aa").length>100){
        alert("“文件名”超长，英文字符不能超过100，中文字符不能超过50个！");
        ("#loaddiv").hide();
        $(this).val("");
        return;
	}
	formData.append('content', f);
	 $.ajax({
         type: "POST",
         url:"xxoo/getUpload",        
         data: formData,
         dataType: "json",
         processData:false, 
         contentType:false, 
          async:false,//是否同步
         success: function(res){
        	 $("#loaddiv").hide();
        	 if(res == "FAIL") {
        		 alert("导入模板有误！");
        	 } else {
        		 alert("导入成功！");
        	 }
      	},
   	 	error: function() {
			$("#loaddiv").hide();
			return;
	 	}
     });
});

导入后端

@RequestMapping(value = "/toUpload", method = RequestMethod.POST)
public String toUpload(HttpServletRequest request, Model model) throws Exception {
	MultipartHttpServletRequest multipartRequest = (MultipartHttpServletRequest) request;
	CommonsMultipartFile orginalFile = (CommonsMultipartFile) multipartRequest.getFile("file");
	byte[] content = orginalFile.getBytes();		
	String fileName = orginalFile.getOriginalFilename();
	try{
	//写取到文件名和内容之后的逻辑代码
	...
	//获取表里面内容
	InputStream inputStream = new ByteArrayInputStream(content);
	Workbook wb = WorkbookFactory.create(inputStream);
	if (wb != null) {
		Sheet sheet = wb.getSheetAt(0);
		int countRow = sheet.getLastRowNum();
		for (int rowNum = 0; rowNum <= countRow; rowNum++) {
		Row row = sheet.getRow(rowNum);
        	if (row != null) {
        		Cell cell = row.getCell(0);
			}
		}
	}
	} catch () {
		return "ERROR";
	}
     return "SUCCESS";
}```


[操作Excel部分可参考](https://blog.csdn.net/qq_37219543/article/details/105110823)