EasyExcel自定义多级表头导出
easyexcel
快速、简洁、解决大文件内存溢出的java处理Excel工具
项目地址:https://gitcode.com/gh_mirrors/ea/easyexcel
免费下载资源
·
主要是封装好表头的list和数据的list。注意数据列的属性顺序和表头一致
package com.tgpms.web.common.controller;
import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.write.metadata.style.WriteCellStyle;
import com.alibaba.excel.write.style.HorizontalCellStyleStrategy;
import com.alibaba.excel.write.style.column.LongestMatchColumnWidthStyleStrategy;
import com.baomidou.mybatisplus.core.conditions.query.LambdaQueryWrapper;
import com.tgpms.web.excelTable.entity.ExcelTableColumn;
import com.tgpms.web.excelTable.entity.ExcelTableTemplate;
import com.tgpms.web.excelTable.service.IExcelTableColumnService;
import com.tgpms.web.excelTable.service.IExcelTableTemplateService;
import com.tgpms.web.system.entity.SmDatasource;
import com.tgpms.web.system.service.SmDatasourceService;
import io.swagger.annotations.Api;
import io.swagger.annotations.ApiOperation;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.bind.annotation.RestController;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.net.URLEncoder;
import java.util.*;
import java.util.stream.Collectors;
/**
* @author jiangli
* @since 2021/2/20 11:12
* 通用Excel导出接口
*/
@Api(value = "ExcelController", tags = "通用Excel导出接口")
@RestController
@RequestMapping(value = "/ev/excel")
public class ExcelController {
@Autowired
private IExcelTableTemplateService templateService;
@Autowired
private IExcelTableColumnService columnService;
@Autowired
private SmDatasourceService smDatasourceService;
@ApiOperation(value = "通过业务编码code导出数据", httpMethod = "GET")
@GetMapping("/export")
public void export(@RequestParam String code, @RequestParam String condition, HttpServletResponse response) throws IOException {
ExcelTableTemplate one = templateService.getOne(new LambdaQueryWrapper<ExcelTableTemplate>().eq(ExcelTableTemplate::getCode, code));
if (one == null) {
return;
}
String ttId = one.getTtId();
String dataSourceCode = one.getDataSourceCode();
// 查询表格列
List<ExcelTableColumn> excelTableColumns = columnService.list(new LambdaQueryWrapper<ExcelTableColumn>().eq(ExcelTableColumn::getTtId, ttId).orderByAsc(ExcelTableColumn::getOrderNo));
Map<String, ExcelTableColumn> mapColumn = excelTableColumns.stream().collect(Collectors.toMap(ExcelTableColumn::getTcId, e -> e));
List<ExcelTableColumn> leftList = excelTableColumns.stream().filter(e -> e.getLeaf().equals("1")).collect(Collectors.toList());
List<List<String>> headList = new ArrayList<>();
for (ExcelTableColumn excelTableColumn : leftList) {
List<String> list = new ArrayList<>();
headList(excelTableColumn,list,mapColumn);
Collections.reverse(list);
headList.add(list);
}
// 查询数据列表
SmDatasource byDsCode = smDatasourceService.findByDsCode(dataSourceCode);
if (null == byDsCode) {
return;
}
String selectClause = byDsCode.getSelectClause().replace("@condition", condition);
Map<String, String> params = new HashMap<>();
params.put("sql", selectClause);
List<Map<String, String>> list = smDatasourceService.executionSql(params);
List<List<String>> data = new ArrayList<>();
for (Map<String, String> map : list) {
List<String> child = new ArrayList<>();
for (ExcelTableColumn excelTableColumn : leftList) {
String columnCode = excelTableColumn.getColumnCode();
child.add(map.get(columnCode));
}
data.add(child);
}
// 头的策略
WriteCellStyle headWriteCellStyle = new WriteCellStyle();
// 背景设置为白色
headWriteCellStyle.setFillForegroundColor(IndexedColors.WHITE1.getIndex());
HorizontalCellStyleStrategy horizontalCellStyleStrategy =
new HorizontalCellStyleStrategy(headWriteCellStyle,new ArrayList<>());
// String path = "C:\\Users\\admin\\Desktop\\test.xlsx";
// EasyExcel.write(path).head(headList).registerWriteHandler(new LongestMatchColumnWidthStyleStrategy()).sheet("sheet1").doWrite(data);
// 设置 ContentType
response.setContentType("application/vnd.ms-excel");
// 设置字符集
response.setCharacterEncoding("utf-8");
// 防止中文乱码
String fileName = URLEncoder.encode(one.getDescription(), "UTF-8");
// 设置 header
response.setHeader("Content-disposition", "attachment;filename=" + fileName + ".xlsx");
// 写入 自定义表头
EasyExcel.write(response.getOutputStream()).head(headList).registerWriteHandler(new LongestMatchColumnWidthStyleStrategy()).sheet("sheet1").doWrite(data);
// 写入 使用实体类的注解
// EasyExcel.write(response.getOutputStream(), User.class).sheet("知识追寻者").doWrite(DataUtils.getUserData());
}
private void headList(ExcelTableColumn column, List<String> list, Map<String, ExcelTableColumn> mapColumn) {
list.add(column.getLabel());
String parentId = column.getParentId();
if ("0".equals(parentId)) {
return;
}
ExcelTableColumn tableColumn = mapColumn.get(parentId);
headList(tableColumn,list,mapColumn);
}
}
@Data
public class User {
// id
@ExcelProperty(index = 0,value = "编号")
private Long id;
// 名称
@ExcelProperty(index = 1,value = "名称")
private String name;
// 创建时间
@ExcelProperty(index = 2,value = "创建时间")
private String createTime;
// 描述
@ExcelProperty(index = 3,value = "描述")
private String description;
}
前端代码
outExcel() {
this.$message.success("正在下载,请稍候");
const data = {code: this.code, condition: condition};
this.axios.post('/ev/excel/export', qs.stringify(data), {responseType: 'blob'}).then(data => {
if (data.status === 200) {
const blob = new Blob([data.data]);
const url = window.URL.createObjectURL(blob);
const link = document.createElement("a");
link.style.display = "none";
link.href = url;
link.setAttribute("download", this.description + '.xlsx');
document.body.appendChild(link);
link.click();
} else {
this.$message.error("网络错误:" + data.status);
}
})
},
GitHub 加速计划 / ea / easyexcel
31.63 K
7.47 K
下载
快速、简洁、解决大文件内存溢出的java处理Excel工具
最近提交(Master分支:1 个月前 )
c42183df
Bugfix 1 个月前
efa7dff6 * 重新加回 `commons-io`
1 个月前
更多推荐
已为社区贡献10条内容
所有评论(0)