Vue+SpringBoot实现Excel导出
将页面展示表格中数据导出Excel
根据业务需要,页面展示的数据需要导出excel表格,方便应用管理,前端用vue实现,后端用pio实现。
js
//导出
export function exportExcel(data) {
return request({
url: SZZF_API + '/personweekdetails/exportExcel',
method: 'post',
responseType: 'blob',
data: data
})
}
vue
exportInfo() {
exportExcel({"searchable": this.searchable}).then((res) => {
const link = document.createElement('a');
let blob = new Blob([res.data], {type: 'application/vnd.ms-excel'});
link.style.display = 'none';
link.href = URL.createObjectURL(blob);
let num = '';
for (let i = 0; i < 10; i++) {
num += Math.ceil(Math.random() * 10)
}
link.setAttribute('download', '部门周报' + num + '.xls');
document.body.appendChild(link);
link.click();
document.body.removeChild(link)
})
}
注:
responseType: 'blob’和下面controller中response.setContentType(“application/octet-stream;charset=utf-8”);否则会乱码
js
//导出
export function exportExcel(data) {
return request({
url: SZZF_API + '/personweekdetails/exportExcel',
method: 'post',
responseType: 'blob',
data: data
})
}
pio依赖
<dependencies>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
</dependency>
</dependencies>
Controller
@PostMapping("exportExcel")
@RequestLog(name = "部门周报", description = "部门周报-导出部门周报")
public void exportExcel(@RequestModel("searchable") MapWapper<String, Object> searchParams,
HttpServletRequest request, HttpServletResponse response) throws Exception {
Map<String, Object> map = searchParams.getInnerMap();
//使用迭代器的remove(Json)方法删除value值为空或者为空字符串的元素
Iterator<Map.Entry<String, Object>> it = map.entrySet().iterator();
while (it.hasNext()) {
Map.Entry<String, Object> entry = it.next();
if (entry.getValue() == null || StringUtils.isBlank(entry.getValue().toString())) {
it.remove();
}
}
List<PersonWeekDeptDetailsVO> personWeekDetailsVOList = getService().findDetailsListByDept(map);
HSSFWorkbook wb = getService().export(personWeekDetailsVOList);
response.setContentType("application/vnd.ms-excel");
SimpleDateFormat df = new SimpleDateFormat("yyyyMMddHHmmss");//设置日期格式
String fileName = df.format(new Date());// new Date()为获取当前系统时间
response.setHeader("Content-disposition", "attachment;fileName=" + "人员周报" + fileName + ".xls");
response.setContentType("application/octet-stream;charset=utf-8");
OutputStream ouputStream = response.getOutputStream();
wb.write(ouputStream);
ouputStream.flush();
ouputStream.close();
}
Service
//导出
HSSFWorkbook export(List<PersonWeekDeptDetailsVO> list);
ServiceImpl
@Override
public HSSFWorkbook export(List<PersonWeekDeptDetailsVO> list) {
HSSFWorkbook wb = new HSSFWorkbook();
String[] excelHeader = {"周报人员", "工作内容", "状 态", "工作时长(天)",
"工作日期", "所属项目", "所属子系统", "工作类型",
"工作地点"};
HSSFSheet sheet = wb.createSheet("PersonWeekDeptDetailsVO");
HSSFRow row = sheet.createRow((int) 0);
CellStyle style = wb.createCellStyle();
HSSFCellStyle style2 = wb.createCellStyle();
Font fontStyle = wb.createFont(); // 字体样式
fontStyle.setBold(true); // 加粗
fontStyle.setFontName("黑体"); // 字体
fontStyle.setFontHeightInPoints((short) 10); // 大小
// 将字体样式添加到单元格样式中
style.setFont(fontStyle);
// 设置样式
style.setFillForegroundColor(IndexedColors.AQUA.getIndex());
style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
style.setAlignment(HorizontalAlignment.CENTER);
style.setBorderBottom(BorderStyle.THIN);
style.setBorderLeft(BorderStyle.THIN);
style.setBorderRight(BorderStyle.THIN);
style.setBorderTop(BorderStyle.THIN);
style.setTopBorderColor(HSSFColor.HSSFColorPredefined.BLACK.getIndex());
// 设置样式
style2.setAlignment(HorizontalAlignment.CENTER);
style2.setBorderBottom(BorderStyle.THIN);
style2.setBorderLeft(BorderStyle.THIN);
style2.setBorderRight(BorderStyle.THIN);
style2.setBorderTop(BorderStyle.THIN);
style2.setTopBorderColor(HSSFColor.HSSFColorPredefined.BLACK.getIndex());
style2.setWrapText(true);//文本换行
SimpleDateFormat simpleDate = new SimpleDateFormat("yyyy-MM-dd");
for (int i = 0; i < excelHeader.length; i++) {
HSSFCell cell = row.createCell(i);
cell.setCellValue(excelHeader[i]);
cell.setCellStyle(style);
sheet.autoSizeColumn(i);
if (i == 1) {
sheet.setColumnWidth(1, 100 * 256);
} else if (i == 5 || i == 6) {
sheet.setColumnWidth(i, 30 * 256);
} else if (i == 2 || i == 7 || i == 4) {
sheet.setColumnWidth(i, 15 * 256);
}
}
for (int i = 0; i < list.size(); i++) {
row = sheet.createRow(i + 1);
HSSFCell cells[] = new HSSFCell[9];
for (int j = 0; j < cells.length; j++) {
cells[j] = row.createCell(j);
cells[j].setCellStyle(style2);
}
PersonWeekDeptDetailsVO personWeekDeptDetailsVO = list.get(i);
if (personWeekDeptDetailsVO.getUserName() != null) {
cells[0].setCellValue(personWeekDeptDetailsVO.getUserName());
} else {
cells[0].setCellValue("");
}
if (personWeekDeptDetailsVO.getRealContent() != null) {
cells[1].setCellValue(personWeekDeptDetailsVO.getRealContent());
} else {
cells[1].setCellValue("");
}
if (personWeekDeptDetailsVO.getStatus() == null) {
cells[2].setCellValue("");
} else {
String status = dataDictionaryService.findOptionsTextByUniqueNameAndVelve("PER_ZBZT", personWeekDeptDetailsVO.getStatus().toString());
cells[2].setCellValue(status);
}
if (personWeekDeptDetailsVO.getRealTime() == null) {
cells[3].setCellValue("");
} else {
cells[3].setCellValue(personWeekDeptDetailsVO.getRealTime());
}
if (personWeekDeptDetailsVO.getStartDate() == null) {
cells[4].setCellValue("");
} else {
cells[4].setCellValue(simpleDate.format(personWeekDeptDetailsVO.getStartDate()));
}
if (personWeekDeptDetailsVO.getProjectName() == null) {
cells[5].setCellValue("");
} else {
cells[5].setCellValue(personWeekDeptDetailsVO.getProjectName());
}
if (personWeekDeptDetailsVO.getSubSystem() == null) {
cells[6].setCellValue("");
} else {
cells[6].setCellValue(personWeekDeptDetailsVO.getSubSystem());
}
if (personWeekDeptDetailsVO.getWorkType() == null) {
cells[7].setCellValue("");
} else {
String workType = dataDictionaryService.findOptionsTextByUniqueNameAndVelve("PRO_GZLX", personWeekDeptDetailsVO.getWorkType().toString());
cells[7].setCellValue(workType);
}
if (personWeekDeptDetailsVO.getWorkPlace() == null) {
cells[8].setCellValue("");
} else {
cells[8].setCellValue(personWeekDeptDetailsVO.getWorkPlace());
}
}
return wb;
}
单元格合并
根据需要,有时需要把某些单元格合并,于是对于ServiceImpl需要做如下处理:
//导出
public HSSFWorkbook export(List<DeptInputVO> list) {
HSSFWorkbook wb = new HSSFWorkbook();
String[][] excelHeader = {{"部门名称", "新研发产品型", "", "", "", "交付改造型", "", "", "", "维护型", "", "其他类型", "", "", "", "合计", ""},
{"", "实际(开发及实施)", "加权(开发及实施)", "实际(维护)", "加权(维护)", "实际(开发及实施)", "加权(开发及实施)", "实际(维护)", "加权(维护)", "实际", "加权", "实际(开发及实施)", "加权(开发及实施)", "实际(维护)", "加权(维护)", "实际", "加权"}};
HSSFSheet sheet = wb.createSheet("DeptInput");
HSSFRow row = sheet.createRow((int) 0);
CellStyle style = wb.createCellStyle();
CellStyle style2 = wb.createCellStyle();
Font fontStyle = wb.createFont(); // 字体样式
fontStyle.setBold(true); // 加粗
fontStyle.setFontName("黑体"); // 字体
fontStyle.setFontHeightInPoints((short) 10); // 大小
// 将字体样式添加到单元格样式中
style.setFont(fontStyle);
// 设置样式
style.setFillForegroundColor(IndexedColors.AQUA.getIndex());
style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
style.setAlignment(HorizontalAlignment.CENTER);
style.setBorderBottom(BorderStyle.THIN);
style.setBorderLeft(BorderStyle.THIN);
style.setBorderRight(BorderStyle.THIN);
style.setBorderTop(BorderStyle.THIN);
style.setTopBorderColor(HSSFColor.HSSFColorPredefined.BLACK.getIndex());
// 设置样式
style2.setAlignment(HorizontalAlignment.CENTER);
style2.setBorderBottom(BorderStyle.THIN);
style2.setBorderLeft(BorderStyle.THIN);
style2.setBorderRight(BorderStyle.THIN);
style2.setBorderTop(BorderStyle.THIN);
style2.setTopBorderColor(HSSFColor.HSSFColorPredefined.BLACK.getIndex());
int mergerNum = 0; //合并数
//给单元格设置值
for (int i = 0; i < excelHeader.length; i++) {
row = sheet.createRow(i);
sheet.autoSizeColumn(i);
row.setHeight((short) 500);
for (int j = 0; j < excelHeader[i].length; j++) {
HSSFCell cell = row.createCell(j);
cell.setCellStyle(style);
cell.setCellValue(excelHeader[i][j]);
sheet.autoSizeColumn(j);
}
}
Map<Integer, List<Integer>> map = new HashMap<Integer, List<Integer>>(); // 合并行时要跳过的行列
//合并列
for (int i = 0; i < excelHeader[excelHeader.length - 1].length; i++) {
if ("".equals(excelHeader[excelHeader.length - 1][i])) {
for (int j = excelHeader.length - 2; j >= 0; j--) {
if (!"".equals(excelHeader[j][i])) {
sheet.addMergedRegion(new CellRangeAddress(j, excelHeader.length - 1, i, i)); // 合并单元格
break;
} else {
if (map.containsKey(j)) {
List<Integer> list1 = map.get(j);
list1.add(i);
map.put(j, list1);
} else {
List<Integer> list1 = new ArrayList<Integer>();
list1.add(i);
map.put(j, list1);
}
}
}
}
}
//合并行
for (int i = 0; i < excelHeader.length - 1; i++) {
for (int j = 0; j < excelHeader[i].length; j++) {
List<Integer> list1 = map.get(i);
if (list1 == null || (list1 != null && !list1.contains(j))) {
if ("".equals(excelHeader[i][j])) {
mergerNum++;
if (mergerNum != 0 && j == (excelHeader[i].length - 1)) {
sheet.addMergedRegion(new CellRangeAddress(i, i, j - mergerNum, j)); // 合并单元格
mergerNum = 0;
}
} else {
if (mergerNum != 0) {
sheet.addMergedRegion(new CellRangeAddress(i, i, j - mergerNum - 1, j - 1)); // 合并单元格
mergerNum = 0;
}
}
}
}
}
for (int i = 0; i < list.size(); i++) {
row = sheet.createRow(i + 2);
DeptInputVO deptInputVO = list.get(i);
HSSFCell[] cells = new HSSFCell[17];
for (int j = 0; j < 17; j++) {
cells[j] = row.createCell(j);
cells[j].setCellStyle(style2);
}
if (StringUtils.isNotEmpty(deptInputVO.getDeptName())) {
cells[0].setCellValue(deptInputVO.getDeptName());
} else {
cells[0].setCellValue("");
}
cells[1].setCellValue(getRealAmount(deptInputVO.getProNewAmount()));
cells[2].setCellValue(getRealAmount(deptInputVO.getWProNewAmount()));
cells[3].setCellValue(getRealAmount(deptInputVO.getProNewMainTainAmount()));
cells[4].setCellValue(getRealAmount(deptInputVO.getWProNewMainTainAmount()));
cells[5].setCellValue(getRealAmount(deptInputVO.getProReformAmount()));
cells[6].setCellValue(getRealAmount(deptInputVO.getWProReformAmount()));
cells[7].setCellValue(getRealAmount(deptInputVO.getProReformMainTainAmount()));
cells[8].setCellValue(getRealAmount(deptInputVO.getWProReformMainTainAmount()));
cells[9].setCellValue(getRealAmount(deptInputVO.getProProductAmount()));
cells[10].setCellValue(getRealAmount(deptInputVO.getWProProductAmount()));
cells[11].setCellValue(getRealAmount(deptInputVO.getProOtherAmount()));
cells[12].setCellValue(getRealAmount(deptInputVO.getWProOtherAmount()));
cells[13].setCellValue(getRealAmount(deptInputVO.getProOtherMainTainAmount()));
cells[14].setCellValue(getRealAmount(deptInputVO.getWProOtherMainTainAmount()));
cells[15].setCellValue(getRealAmount(deptInputVO.getAllAmount()));
cells[16].setCellValue(getRealAmount(deptInputVO.getWAllAmount()));
}
return wb;
}
导出效果图: