CSDN鏈接：https://blog.csdn.net/sinat_27537929/article/details/98059599
需求
- 前端發(fā)送下載請求
- 后端接收請求问词，從數(shù)據(jù)庫拿出數(shù)據(jù)九榔，寫成excel格式，傳給前端
- 前端拿到excel進(jìn)行下載
- 在excel中增加數(shù)據(jù)
- 將新的excel上傳給服務(wù)器
環(huán)境
- 前端vue+ElementUI
- 后端springboot+mybatisplus+mysql
- 后端生成excel用到org.apache.poi
下載
html
<el-button type="primary" @click="exportWord" icon="el-icon-download" plain>導(dǎo)出</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); //創(chuàng)建下載的鏈接
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();
// 設(shè)置excel第一行的標(biāo)題
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[]>>();
// 設(shè)置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窿冯，每次發(fā)送都需要帶著token等數(shù)據(jù)枣抱，所以不能直接用ElementUI中el-upload組件的action解愤。
el-upload中有個屬性http-request：覆蓋默認(rèn)的上傳行為骤素，可以自定義上傳的實現(xiàn)家淤，接收類型是function异剥，就是他了。
這里我用了auto-upload="false"這個屬性絮重，即“是否在選取文件后立即進(jìn)行上傳”選擇不立即上傳冤寿，所以多了一個按鈕來實現(xiàn)點擊觸發(fā)上傳歹苦。
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
>上傳到服務(wù)器</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", "文件不存在");
}
// 構(gòu)造臨時路徑來存儲上傳的文件
// 這個路徑相對當(dāng)前應(yīng)用的目錄
// Constant.UPLOAD_DIRECTORY是你自己存放文件的文件夾
String uploadPath = request.getServletContext().getRealPath("/")
+ File.separator + Constant.UPLOAD_DIRECTORY;
//如果目錄不存在則創(chuàng)建
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();
}
//刪除標(biāo)題
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();
}
//刪除標(biāo)題
list.remove(0);
return list;
}
這里還要說兩個小插曲...
- 當(dāng)時我想console.log(formData)，結(jié)果發(fā)現(xiàn)console的結(jié)果是{}督怜，我以為沒有數(shù)據(jù)...后來看了別人的文章發(fā)現(xiàn)應(yīng)該這么用：console.log(formData.get('xxx'))
- 由于公司封裝了axios殴瘦，然后每次post我都發(fā)現(xiàn)不太對...原來公司設(shè)置的http request攔截器默認(rèn)把post的Content-Type都改成了'application/x-www-form-urlencoded; charset=UTF-8'...可是，我需要'Content-Type': 'multipart/form-data'昂鸥堋ｒ揭浮！姨蟋！然后默默地在攔截器里加了個判斷...
參考：
使用ElementUI中的upload組件上傳Excel文件
vue項目中實現(xiàn)下載后端返回的excel數(shù)據(jù)表格
Spring boot實現(xiàn)導(dǎo)出數(shù)據(jù)生成excel文件返回
萌新用vue + axios + formdata 上傳文件的爬坑之路
