1、controller
@PostMapping("/exportExcel")
@ApiOperation("导出")
public void exportExcel(@RequestBody Map<String,Object> param, HttpServletResponse response) {
Service.exportExcel(response, param);
}
2、service
public void exportExcel(HttpServletResponse response, Map<String,Object> param);
3、serviceImp
@Override
public void exportExcel(HttpServletResponse response, Map<String,Object> param) {
String title = (String) param.get("title");
//sheet1列 头部信息
List<Map<String, Object>> sheet1List = (List<Map<String, Object>>)param.get("first");
//sheet2列 头部信息
List<Map<String, Object>> sheet2List = (List<Map<String, Object>>)param.get("second");
// 获取数据--sheet1
List<Map<String, Object>> dataList = new ArrayList<>();
// 获取数据--sheet2
List dataList2 = new ArrayList<>();
if(StringUtils.containsIgnoreCase(title,"姓名")){
//sheet1
dataList = findCountInfoByDiameter(param);
//sheet2
dataList2 = selectInfo(param);
}else if(StringUtils.containsIgnoreCase(title,"性别")){
dataList = findCountInfoByMaterial(param);
dataList2 = selectInfo(param);
}else if(StringUtils.containsIgnoreCase(title,"年龄")){
dataList = findByDrawCircularWithRoad(param);
dataList2 = selectInfo(param);
}else if(StringUtils.containsIgnoreCase(title,"户籍")){
dataList = findByDrawPolygonWithCaliber(param);
dataList2 = findList(param);
}else if(StringUtils.containsIgnoreCase(title,"身份证号")){
dataList = findAll(param);
}
// for (int i=0; i<dataList.size(); i++) {
// for (Map<String, Object> head : sheet1List) {
// String colName = (String) head.get("field");
// // 获取当前列的第i行的值
// Object colValue = dataList.get(i).get(colName);
// // 判断值是否为null
// if (colValue == null) {
// dataList.get(i).put(colName, 0);
// }
// else {
// dataList.get(i).put(colName, Double.parseDouble((String)colValue));
// }
// }
// }
try (ExcelWriter writer = ExcelUtil.getWriter()) {
//sheet1
setWorkSheet(writer,title,"列表页",sheet1List,dataList,0);
//sheet2
if(sheet2List != null && sheet2List.size()>0){
ExcelWriter excelWriter = writer.setSheet(1);
setWorkSheet(excelWriter,title,"明细",sheet2List,dataList2,1);
}
//自定义标题别名
//response为HttpServletResponse对象
response.setContentType("application/vnd.ms-excel;charset=utf-8");
response.setCharacterEncoding("UTF-8");
//test.xls是弹出下载对话框的文件名,不能为中文,中文请自行编码
response.setHeader("Content-Disposition", "attachment;filename="+ URLEncoder.encode(title, "UTF-8")+".xls");
ServletOutputStream out = response.getOutputStream();
writer.flush(out);
} catch (Exception e) {
e.printStackTrace();
// log.error("导出数据报表错误:", e);
}
}
public void setWorkSheet(ExcelWriter writer,
String title,
String sheetName,
List<Map<String,Object>>sheet1List,
List dataList,
int sheetIndex){
int cols = sheet1List.size() ;
int rows = dataList.size();
for(int i = 0;i<sheet1List.size();i++){
Map<String, Object> column = sheet1List.get(i);
String field = (String) column.get("field");
String columnTitle = (String) column.get("title");
writer.addHeaderAlias(field, columnTitle);
}
//合并单元格,设置标题
writer.merge(cols-1, title);
//设置是否只保留别名中的字段值,如果为true,则不设置alias的字段将不被输出,false表示原样输出
writer.setOnlyAlias(true);
writer.write(dataList, true);
writer.getWorkbook().getCreationHelper().createFormulaEvaluator().evaluateAll();
//重命名sheet名字
writer.getWorkbook().setSheetName(sheetIndex,sheetName);
writer.setRowHeight(1, 40);
for (int i = 0; i < cols; i++) {
writer.setColumnWidth(i, 45);
for(int j = 0 ;j < rows; j++){
Cell cell = writer.getCell(i, j+2);
CellType cellType = cell.getCellType();
if(cellType.name().equals("NUMERIC")){
cell.getCellStyle().setDataFormat((short) 0);
}
}
}
Cell cell = writer.getCell(0, 0);
CellStyle cellStyle = cell.getCellStyle();
cellStyle.setFillForegroundColor(IndexedColors.PALE_BLUE.getIndex());
cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
Font font = writer.createFont();
font.setBold(true);
font.setFontHeightInPoints((short) 16);
cellStyle.setFont(font);
cell.setCellStyle(cellStyle);
}
3、serviceImp的另一种方法
@Override
public void exportExcel(LabourOddVisaBO labourOddVisaBO, HttpServletResponse response) {
List<Map> list = getBaseMapper().selectListNoPage(labourOddVisaBO);
try (ExcelWriter writer = ExcelUtil.getWriter()) {
writer.addHeaderAlias("name","姓名");
writer.addHeaderAlias("tTime","时间");
writer.addHeaderAlias("reason","原因");
writer.setOnlyAlias(true);
writer.write(list, true);
writer.setRowHeight(0, 30);
writer.setRowHeight(1, 20);
for (int i = 0; i <= 9; i++) {
writer.setColumnWidth(i, 30);
}
Cell cell = writer.getCell(0, 0);
CellStyle cellStyle = cell.getCellStyle();
cellStyle.setFillForegroundColor(IndexedColors.SKY_BLUE.getIndex());
cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
Font font = writer.createFont();
font.setBold(true);
font.setFontHeightInPoints((short) 16);
cellStyle.setFont(font);
cellStyle.setWrapText(true);
cell.setCellStyle(cellStyle);
//自定义标题别名
//response为HttpServletResponse对象
response.setContentType("application/vnd.ms-excel;charset=utf-8");
response.setCharacterEncoding("UTF-8");
//test.xls是弹出下载对话框的文件名,不能为中文,中文请自行编码
String title = "零工签证导出数据";
response.setHeader("Content-Disposition", "attachment;filename="+ URLEncoder.encode(title, "UTF-8")+".xls");
ServletOutputStream out = response.getOutputStream();
writer.flush(out);
} catch (Exception e) {
log.error("导出错误:", e);
}
}
4、Vue 前端
//导出
export function exportExcel(param) {
return request({
url: 'xxx/xxxxxx/exportExcel',
method: 'post',
data: param,
headers: {
'Content-Type': 'application/json;charset=UTF-8'
},
responseType: 'blob',
timeout: 60000
}).then(res => {
// console.log(res)
downloadExcelWithResData(res)
}).catch((err) => {
console.log("====catch=="+JSON.stringify(err))
})
}
/**
* 下载excel
*/
export function downloadExcelWithResData(res) {
let fileName = res.headers['content-disposition'].split('filename=')[1]
if (!fileName) {
fileName = res.headers['content-disposition'].split('UTF-8\'\'')[1]
}
fileName = fileName ? fileName.replace(/\"/g, '') : 'file.xlsx'
fileName = decodeURI(fileName) || ''
downloadFileWithBuffer(res.data, fileName, 'application/vnd.ms-excel;charset=utf-8')
}
export function downloadFileWithBuffer(data, name, type) {
var blob = new Blob([data], {
type: type || ''
})
var downloadElement = document.createElement('a')
var href = window.URL.createObjectURL(blob) // 创建下载的链接
downloadElement.href = href
downloadElement.download = name // 下载后文件名
document.body.appendChild(downloadElement)
downloadElement.click() // 点击下载
document.body.removeChild(downloadElement) // 下载完成移除元素
window.URL.revokeObjectURL(href) // 释放掉blob对象
}