CSDN链接：https://blog.csdn.net/sinat_27537929/article/details/98059599
需求
- 前端发送下载请求
- 后端接收请求，从数据库拿出数据，写成excel格式，传给前端
- 前端拿到excel进行下载
- 在excel中增加数据
- 将新的excel上传给服务器
环境
- 前端vue+ElementUI
- 后端springboot+mybatisplus+mysql
- 后端生成excel用到org.apache.poi
下载
html
<el-button type="primary" @click="exportWord" icon="el-icon-download" plain>导出</el-button>
js
exportWord () { this.$axios.post('/web/xxxxxx/export', {}, { responseType: 'blob' }).then(res => { let blob = new Blob([res.data], { type: 'application/ms-excel;charset=utf-8' }); let downloadElement = document.createElement('a'); let href = window.URL.createObjectURL(blob); //创建下载的链接 downloadElement.href = href; downloadElement.download = 'forbidden-words.xls'; //下载后文件名 document.body.appendChild(downloadElement); downloadElement.click(); //点击下载 document.body.removeChild(downloadElement); //下载完成移除元素 window.URL.revokeObjectURL(href); //释放掉blob对象 }) }
controller
@PostMapping("/export")
public void exportXXXXXXWords(HttpServletResponse response) {
List<ForbiddenWord> forbiddenList;
try {
// get your data
wordList = wordService.getWords();
// 设置excel第一行的标题
String[] titleRow = new String[]{"单词", "级别"};
List<String[]> data = new LinkedList<String[]>();
data.add(0, titleRow);
for (int i = 0; i < wordList.size(); i++) {
Word word = wordList.get(i);
data.add(new String[]{
word.getWord(),
word.getLevel().toString()
});
}
Map<String, List<String[]>> exportData = new HashMap<String, List<String[]>>();
// 设置sheet的名称
exportData.put("Your sheet name", data);
String strResult = FileUtils.createExcelFile(response, exportData);
} catch (Exception e) {
e.printStackTrace();
}
}
FileUtils
/** * 直接生成文件流返回给前端 * * @param response * @param exportData * @return */ public static String createExcelFile(HttpServletResponse response, Map<String, List<String[]>> exportData) { OutputStream outputStream = null; try { Workbook wb = new HSSFWorkbook(); for (String sheetName : exportData.keySet()) { Sheet sheet = wb.createSheet(sheetName); List<String[]> rowData = exportData.get(sheetName); for (int i = 0; i < rowData.size(); i++) { String[] cellData = rowData.get(i); Row row = sheet.createRow(i); for (int j = 0; j < cellData.length; j++) { Cell cell = row.createCell(j); cell.setCellValue(cellData[j]); } } } response.setContentType("application/vnd.ms-excel;charset=utf-8"); response.flushBuffer(); outputStream = response.getOutputStream(); wb.write(outputStream); } catch (IOException ex) { ex.printStackTrace(); return "failure"; } finally { try { if (outputStream != null) { outputStream.flush(); outputStream.close(); } } catch (IOException e) { e.printStackTrace(); } } return "success"; }
上传
首先，因为公司封装了axios，每次发送都需要带着token等数据，所以不能直接用ElementUI中el-upload组件的action。
el-upload中有个属性http-request：覆盖默认的上传行为，可以自定义上传的实现，接收类型是function，就是他了。
这里我用了auto-upload="false"这个属性，即“是否在选取文件后立即进行上传”选择不立即上传，所以多了一个按钮来实现点击触发上传。
html
<el-upload ref="upload" action :multiple="false" :file-list="fileList" :auto-upload="false" :limit="1" :http-request="importWordConfirm" > <el-button slot="trigger" size="small" type="primary" plain>选取文件</el-button> <el-button style="margin-left: 10px;" size="small" type="success" @click="submitUpload" plain >上传到服务器</el-button> </el-upload>
js
submitUpload () { this.$refs.upload.submit(); }, importWordConfirm (item) { const fileObj = item.file const formData = new FormData() formData.append('file', fileObj) this.$axios.post('/web/xxxxxx/import', formData, { headers: { 'Content-Type': 'multipart/form-data' } }).then(res => { // do something }) }
controller
@PostMapping("/import")
public ApiResult importXXXXXXWords(
@RequestParam("file") MultipartFile uploadFile,
HttpServletRequest request) throws Exception {
try {
if (uploadFile == null) {
//判断文件大小
return failure("-1", "文件不存在");
}
// 构造临时路径来存储上传的文件
// 这个路径相对当前应用的目录
// Constant.UPLOAD_DIRECTORY是你自己存放文件的文件夹
String uploadPath = request.getServletContext().getRealPath("/")
+ File.separator + Constant.UPLOAD_DIRECTORY;
//如果目录不存在则创建
File uploadDir = new File(uploadPath);
if (!uploadDir.exists()) {
uploadDir.mkdir();
}
String fileName = uploadFile.getOriginalFilename();
String originalFileName = fileName
.substring(0, fileName.lastIndexOf("."));
//获取文件名后缀
String suffix = fileName
.substring(fileName.lastIndexOf("."));
String newFileName = originalFileName
+ "_" + UUID.randomUUID().toString() + suffix;
File file = new File(uploadPath, newFileName);
try {
uploadFile.transferTo(file);
} catch (Exception e) {
e.printStackTrace();
}
List<String[]> fileData = null;
if (suffix.equals(".xls")) {
fileData = FileUtils.readXlsFile(file.getAbsolutePath());
} else if (suffix.equals(".xlsx")) {
fileData = FileUtils.readXlsxFile(file.getAbsolutePath());
} else {
return failure("-2", "文件格式不正确");
}
// do something
return success("解析文件成功");
} catch (Exception e) {
e.printStackTrace();
return failure("-1", "更新有误");
}
}
FileUtils
public static List<String[]> readXlsFile(String filePath) { HSSFWorkbook workbook = null; List<String[]> list = new LinkedList<String[]>(); try { workbook = new HSSFWorkbook(new FileInputStream(filePath)); HSSFSheet sheet = workbook.getSheetAt(0); int rowNumber = sheet.getLastRowNum(); for (int i = 0; i < rowNumber + 1; i++) { HSSFRow row = sheet.getRow(i); int lastCellNum = row.getLastCellNum(); String[] cells = new String[lastCellNum]; for (int j = 0; j < lastCellNum; j++) { HSSFCell cell = row.getCell(j); if (cell != null) { cells[j] = cell.toString(); } else { cells[j] = ""; } } list.add(cells); } } catch (Exception e) { e.printStackTrace(); } //删除标题 list.remove(0); return list; } public static List<String[]> readXlsxFile(String filePath) { XSSFWorkbook workbook = null; List<String[]> list = new LinkedList<String[]>(); try { workbook = new XSSFWorkbook(new FileInputStream(filePath)); XSSFSheet sheet = workbook.getSheetAt(0); int rowNumber = sheet.getLastRowNum(); for (int i = 0; i < rowNumber + 1; i++) { XSSFRow row = sheet.getRow(i); int lastCellNum = row.getLastCellNum(); String[] cells = new String[lastCellNum + 1]; for (int j = 0; j < lastCellNum; j++) { XSSFCell cell = row.getCell(j); cells[j] = cell.toString(); } list.add(cells); } } catch (Exception e) { e.printStackTrace(); } //删除标题 list.remove(0); return list; }
这里还要说两个小插曲...
- 当时我想console.log(formData)，结果发现console的结果是{}，我以为没有数据...后来看了别人的文章发现应该这么用：console.log(formData.get('xxx'))
- 由于公司封装了axios，然后每次post我都发现不太对...原来公司设置的http request拦截器默认把post的Content-Type都改成了'application/x-www-form-urlencoded; charset=UTF-8'...可是，我需要'Content-Type': 'multipart/form-data'啊！！！然后默默地在拦截器里加了个判断...
注意：还需要配置如下内容
import org.springframework.context.annotation.Bean; import org.springframework.context.annotation.Configuration; import org.springframework.web.multipart.MultipartResolver; import org.springframework.web.multipart.commons.CommonsMultipartResolver; @Configuration public class MultipartResolverConfig { @Bean(name = "multipartResolver") public MultipartResolver multipartResolver(){ CommonsMultipartResolver resolver = new CommonsMultipartResolver(); //上传文件大小 10M 10*1024*1024 resolver.setMaxUploadSize(10*1024*1024); resolver.setDefaultEncoding("UTF-8"); return resolver; } }
request.getServletContext().getRealPath("/") + File.separator + Constant.UPLOAD_DIRECTORY
C:\Users\Lenovo\AppData\Local\Temp\tomcat-docbase.4936602277910733153.9510\file
request.getContextPath() + File.separator + Constant.UPLOAD_DIRECTORY
D:\file
System.getProperty("user.dir") + File.separator + Constant.UPLOAD_DIRECTORY
D:\myfolder\code\ant-api\file
参考：
使用ElementUI中的upload组件上传Excel文件
vue项目中实现下载后端返回的excel数据表格
Spring boot实现导出数据生成excel文件返回
萌新用vue + axios + formdata 上传文件的爬坑之路
链接：https://www.jianshu.com/p/154ba49d9b7a
来源：简书
著作权归作者所有。商业转载请联系作者获得授权，非商业转载请注明出处。
