前端
<el-button class="export" type="primary" icon="el-icon-download" @click="exportData()">导出</el-button>exportData() {//data 操作data 变成后端需要的格式let data = {capacityVos: resultVo}this.$confirm("是否确认导出曲线信息?", "警告", {confirmButtonText: "确定",cancelButtonText: "取消",type: "warning",}).then(response => {exportExcelData(data).then(res => {if (res.code == 200) {this.jnpf.downloadFile(res.data.url) }})})}
前端跳后端
// 导出
export function exportExcelData(data) {return request({url: define.api + '/user/export',method: 'post',data:data,});
}
模板excel
后端接口
//xAxixAndData 导出的数据@ApiOperation("导出Excel")@PostMapping("/export")public ActionResult export(@RequestBody XAxixAndData xAxixAndData){Map<String, Object> map = new HashMap<>();List<Map<String, Object>> vos = hisCapacityService.getByOne(xAxixAndData);List<Map<String,String>> names = hisCapacityService.getDataName(xAxixAndData);map.put("data", vos);map.put("names", names);//1.获取导出模板地址CurveClassPathResource classPathResource = new ClassPathResource("static/CurveModel.xlsx");String path = classPathResource.getPath();TemplateExportParams templateExportParams = new TemplateExportParams(path);// 2.执行excel导出Workbook workbook = ExcelExportUtil.exportExcel(templateExportParams, map);//3.获取到第0个sheetSheet sheet = workbook.getSheetAt(0);//4.创建标题行 数据有几列--标题合并居中有几列List<Map<String,String>> names1 = (List) map.get("names");int num = names1.size();Row titleRow = sheet.createRow(0);Cell titleCell = titleRow.createCell(0);if(ObjectUtil.isNotNull(xAxixAndData.getVos())){//风电titleCell.setCellValue("风电出力曲线");}else if(ObjectUtil.isNotNull(xAxixAndData.getCapacityVos())){//光伏titleCell.setCellValue("光伏出力曲线");}else if(ObjectUtil.isNotNull(xAxixAndData.getCurveVos())){//曲线目录titleCell.setCellValue(xAxixAndData.getCurveVos().get(0).getName());}CellRangeAddress range = new CellRangeAddress(0, // first row (0-based)0, // last row0, // first column (0-based)num // last column);sheet.addMergedRegion(range);Font font = workbook.createFont();Font font2 = workbook.createFont();font.setFontHeightInPoints((short) 14); // 设置字体大小font.setBold(true); // 设置加粗font2.setBold(true);CellStyle style = workbook.createCellStyle();CellStyle style2 = workbook.createCellStyle();style.setAlignment(HorizontalAlignment.CENTER);// 设置单元格格式为居中style2.setAlignment(HorizontalAlignment.CENTER);style.setFont(font);style2.setFont(font2);titleCell.setCellStyle(style);//5.创建列名所在的行Row columnRow = sheet.createRow(1);Cell columnRowCell = columnRow.createCell(0);columnRowCell.setCellValue("时刻");columnRowCell.setCellStyle(style2);for (int i = 1; i <= names1.size(); i++) {Cell columnRowCell1 = columnRow.createCell(i);columnRowCell1.setCellValue(names1.get(i-1).get("name"));columnRowCell1.setCellStyle(style2);}//6.循环row设置行高自适应for(int i = 0;i <= sheet.getLastRowNum();i ++){XSSFRow xssfRow = (XSSFRow) sheet.getRow(i);CTRow ctRow = xssfRow.getCTRow();ctRow.setCustomHeight(false);}//7.设置列宽List<Map<String, Object>> data = (List) map.get("data");for (int i = 1; i < data.size(); i++) {sheet.setColumnWidth(i,10 * 256);}//8.重命名第0个sheet名称workbook.setSheetName(0, "曲线数据");DownloadVO vo = this.creatModelExcel(fileApi.getPath(FileTypeEnum.TEMPORARY), userInfo, workbook);return ActionResult.success(vo);}
后端--导出表格
/**** 导出表格* @Author lixi* @date 2023-09-14* @return jnpf.base.vo.DownloadVO**/public DownloadVO creatModelExcel(String path, UserInfo userInfo, Workbook workbook) {DownloadVO vo = DownloadVO.builder().build();String sheetName = "名字";ExportParams exportParams = new ExportParams(null, sheetName);exportParams.setType(ExcelType.XSSF);try {String name = sheetName + ".xlsx";@Cleanup ByteArrayOutputStream output = new ByteArrayOutputStream();workbook.write(output);byte[] barray = output.toByteArray();//将Excel文件二进制转为inputstream对象@Cleanup InputStream ins = new ByteArrayInputStream(barray);//上传文件GetStrategy.getInstance(fileCoreProperties.getDefaultPlatform()).uploadFile(path.toLowerCase(), FileTypeEnum.TEMPORARY, name, ins);vo.setName(name);vo.setUrl(UploaderUtil.uploaderFile(userInfo.getId() + "#" + name + "#" + "Temporary"));} catch (Exception e) {log.error("信息导出Excel错误:{}", e);}return vo;}
前端将导出的结果显示在网页上
exportExcelData(data).then(res => {if (res.code == 200) {
//调用this.jnpf.downloadFile方法即可this.jnpf.downloadFile(res.data.url) }})