1 前台
1.1 导出代码
download() {
this.dialogVisible = true
this.showFileName = true
this.operationType = 'download'
}
1.2 前后台对接代码
export function exportResult(data) {
return request({
url: 'export/exportResult',
method: 'post',
data: data
})
}
1.3 页面代码
handleExport(data){
let param = [];
this.$confirm('是否确认导出?', "系统提示", {
confirmButtonText: "确定",
cancelButtonText: "取消",
type: "warning"
}).then(function () {
return exportResult(param);
}).then(response => {
this.download(response.msg);
}).catch(function () {
});
}
2 后台
2.1 引入依赖
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml-lite</artifactId>
<version>5.2.2</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>5.2.2</version>
</dependency>
2.2 接口代码
2.2.1 实体类
2.2.1.1 数据实体
import java.util.List;
import java.util.Map;
public class ExcelEntity {
private String sheetName;
private List<Map<String,Object>> dataList;
private List<ExcelTitle> excelTitleList;
public String getSheetName() {
return sheetName;
}
public void setSheetName(String sheetName) {
this.sheetName = sheetName;
}
public List<Map<String, Object>> getDataList() {
return dataList;
}
public void setDataList(List<Map<String, Object>> dataList) {
this.dataList = dataList;
}
public List<ExcelTitle> getExcelTitleList() {
return excelTitleList;
}
public void setExcelTitleList(List<ExcelTitle> excelTitleList) {
this.excelTitleList = excelTitleList;
}
}
2.2.1.1 表头实体
import java.util.List;
public class ExcelTitle {
private String prop;
private String label;
private List<ExcelTitle> children;
private int level;
public String getProp() {
return prop;
}
public void setProp(String prop) {
this.prop = prop;
}
public String getLabel() {
return label;
}
public void setLabel(String label) {
this.label = label;
}
public List<ExcelTitle> getChildren() {
return children;
}
public void setChildren(List<ExcelTitle> children) {
this.children = children;
}
public int getLevel() {
return level;
}
public void setLevel(int level) {
this.level = level;
}
}
2.2.2 接口入口
@RequestMapping (value = "/exportResult",method = RequestMethod.POST)
@ResponseBody
public AjaxResult exportResult(@RequestBody List<ExcelEntity> excelEntityList) throws IOException {
return AjaxResult.success(exportService.exportCurrent(excelEntityList));
}
2.2.3 主要代码
public String exportCurrent( List<ExcelEntity> excelEntityList) throws IOException {
String filename = "表格.xlsx";
XSSFWorkbook wb = new XSSFWorkbook();
CellStyle dataCellStyle = wb.createCellStyle();
dataCellStyle.setBorderBottom(BorderStyle.THIN);
dataCellStyle.setBorderLeft(BorderStyle.THIN);
dataCellStyle.setBorderRight(BorderStyle.THIN);
dataCellStyle.setBorderTop(BorderStyle.THIN);
dataCellStyle.setAlignment(HorizontalAlignment.CENTER);
dataCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
CellStyle titleCellStyle = wb.createCellStyle();
titleCellStyle.setBorderBottom(BorderStyle.THIN);
titleCellStyle.setBorderLeft(BorderStyle.THIN);
titleCellStyle.setBorderRight(BorderStyle.THIN);
titleCellStyle.setBorderTop(BorderStyle.THIN);
titleCellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
titleCellStyle.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
titleCellStyle.setFillBackgroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
titleCellStyle.setAlignment(HorizontalAlignment.CENTER);
titleCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
for (ExcelEntity entity:excelEntityList){
List<Map<String,Object>> mapList = entity.getDataList();
XSSFSheet sheet = wb.createSheet(entity.getSheetName());
List<ExcelTitle> excelTitleList = new ArrayList<>();
int rowLen = 0;
int cellNum = 0;
if (StringUtils.isNotEmpty(entity.getExcelTitleList())){
excelTitleList = entity.getExcelTitleList();
Map<String,Object> map = getNeedData(excelTitleList,0,0);
excelTitleList = (List<ExcelTitle>) map.get("excelTitleList");
List<ExcelTitle> mergeTitles = (List<ExcelTitle>) map.get("mergeExcelTitle");
rowLen = (int) map.get("rowLen");
XSSFRow row = sheet.createRow(0);
XSSFRow nextRow = sheet.createRow(1);
for (int i = 0; i < mergeTitles.size(); i++) {
CellRangeAddress cellAddresses;
int startCellNum = cellNum;
if (StringUtils.isEmpty(mergeTitles.get(i).getChildren())) {
cellAddresses = new CellRangeAddress(0, rowLen, startCellNum, startCellNum);
cellNum = startCellNum + 1;
} else {
cellNum = this.setNext(mergeTitles.get(i), sheet, 1, startCellNum, nextRow, rowLen,titleCellStyle);
cellAddresses = new CellRangeAddress(0, 0, startCellNum, cellNum - 1);
}
sheet.addMergedRegion(cellAddresses);
XSSFCell cell = row.createCell(startCellNum);
cell.setCellStyle(titleCellStyle);
cell.setCellValue(mergeTitles.get(i).getLabel());
RegionUtil.setBorderBottom(BorderStyle.THIN,cellAddresses,sheet);
RegionUtil.setBorderRight(BorderStyle.THIN,cellAddresses,sheet);
RegionUtil.setBorderLeft(BorderStyle.THIN,cellAddresses,sheet);
RegionUtil.setBorderTop(BorderStyle.THIN,cellAddresses,sheet);
}
}else {
for (String key:mapList.get(0).keySet()) {
ExcelTitle title = new ExcelTitle();
title.setLabel(key);
title.setProp(key);
excelTitleList.add(title);
}
XSSFRow row = sheet.createRow(rowLen);
for (int i=0;i<excelTitleList.size();i++){
XSSFCell cell = row.createCell(i);
cell.setCellStyle(titleCellStyle);
cell.setCellValue(excelTitleList.get(i).getLabel());
}
}
XSSFRow rows;
XSSFCell cells;
for (int i=0;i<mapList.size();i++){
rows = sheet.createRow(i + rowLen+1);
for (int j=0;j<excelTitleList.size();j++){
cells = rows.createCell(j);
cells.setCellStyle(dataCellStyle);
cells.setCellValue(mapList.get(i).get(excelTitleList.get(j).getProp()) != null?mapList.get(i).get(excelTitleList.get(j).getProp()).toString():"");
}
}
}
String downloadPath = AspDevConfig.getDownloadPath() + filename;
File desc = new File(downloadPath);
if (!desc.getParentFile().exists()) {
desc.getParentFile().mkdirs();
}
OutputStream out = new FileOutputStream(downloadPath);
wb.write(out);
out.flush();
out.close();
return filename;
}
2.2.4 自定义工具类
public Map<String, Object> getNeedData(List<ExcelTitle> excelTitleList,int level,int len) {
Map<String, Object> result = new HashMap<>();
List<ExcelTitle> excelTitles = new ArrayList<>();
List<ExcelTitle> mergeExcelTitle = new ArrayList<>();
for (ExcelTitle excelTitle : excelTitleList) {
if (StringUtils.isNotEmpty(excelTitle.getChildren())) {
Map<String,Object> map = this.getNeedData(excelTitle.getChildren(),level+1,len);
if ((int)map.get("rowLen")>len){
len = (int)map.get("rowLen");
}
excelTitles.addAll((List<ExcelTitle>)map.get("excelTitleList"));
excelTitle.setLevel(level);
} else {
excelTitles.add(excelTitle);
excelTitle.setLevel(level);
}
if (level>len){
len = level;
}
mergeExcelTitle.add(excelTitle);
}
result.put("rowLen", len);
result.put("excelTitleList", excelTitles);
result.put("mergeExcelTitle",mergeExcelTitle);
return result;
}
public int setNext(ExcelTitle excelTitle,XSSFSheet sheet,int rowIndex,int cellIndex,XSSFRow row,int rowLen,CellStyle cellStyle){
for (ExcelTitle title : excelTitle.getChildren()){
int startCellIndex = cellIndex;
if (StringUtils.isNotEmpty(title.getChildren())){
int nextRowIndex = rowIndex+1;
XSSFRow nextRow = sheet.getRow(nextRowIndex);
if (nextRow==null){
nextRow = sheet.createRow(nextRowIndex) ;
}
cellIndex = this.setNext(title,sheet,nextRowIndex,cellIndex,nextRow,rowLen,cellStyle);
CellRangeAddress cellAddresses = new CellRangeAddress(rowIndex,rowIndex,startCellIndex,cellIndex-1);
sheet.addMergedRegion(cellAddresses);
RegionUtil.setBorderBottom(BorderStyle.THIN,cellAddresses,sheet);
RegionUtil.setBorderRight(BorderStyle.THIN,cellAddresses,sheet);
RegionUtil.setBorderLeft(BorderStyle.THIN,cellAddresses,sheet);
RegionUtil.setBorderTop(BorderStyle.THIN,cellAddresses,sheet);
}else {
if (rowLen > title.getLevel()){
CellRangeAddress cellAddresses = new CellRangeAddress(rowIndex,rowLen,startCellIndex,startCellIndex);
sheet.addMergedRegion(cellAddresses);
RegionUtil.setBorderBottom(BorderStyle.THIN,cellAddresses,sheet);
RegionUtil.setBorderRight(BorderStyle.THIN,cellAddresses,sheet);
RegionUtil.setBorderLeft(BorderStyle.THIN,cellAddresses,sheet);
RegionUtil.setBorderTop(BorderStyle.THIN,cellAddresses,sheet);
}
cellIndex = startCellIndex+1;
}
XSSFCell cell = row.createCell(startCellIndex);
cell.setCellStyle(cellStyle);
cell.setCellValue(title.getLabel());
}
return cellIndex;
}
3 测试数据格式
3.1 复杂表头数据格式
{
'sheetName': '列表1',
'dataList': [{
'上缴国库': '865',
'具体问题': '**问题',
'党纪政纪': '****纪律',
'项数': '2',
'调账金额': '953',
'补缴税费': '294',
'未整改原因': '****原因',
'责任单位': '上海**公司',
'涉及金额': '145',
'损失金': '265',
'制度名称': '***制度',
'人数': '6'
}, {
'上缴国库': '978',
'具体问题': '**问题',
'党纪政纪': '****纪律',
'项数': '9',
'调账金额': '646',
'补缴税费': '98',
'未整改原因': '****原因',
'责任单位': '武汉**公司',
'涉及金额': '111',
'损失金': '64',
'制度名称': '***制度',
'人数': '9'
}, {
'上缴国库': '654',
'具体问题': '**问题',
'党纪政纪': '****纪律',
'项数': '8',
'调账金额': '895',
'补缴税费': '78',
'未整改原因': '****原因',
'责任单位': '辽宁**公司',
'涉及金额': '98',
'损失金': '216',
'制度名称': '***制度',
'人数': '15'
}, {
'上缴国库': '689',
'具体问题': '**问题',
'党纪政纪': '****纪律',
'项数': '2',
'调账金额': '1230',
'补缴税费': '41',
'未整改原因': '****原因',
'责任单位': '山东**公司',
'涉及金额': '98',
'损失金': '55',
'制度名称': '***制度',
'人数': '6'
}, {
'上缴国库': '545',
'具体问题': '**问题',
'党纪政纪': '****纪律',
'项数': '6',
'调账金额': '1524',
'补缴税费': '212',
'未整改原因': '****原因',
'责任单位': '湖南**公司',
'涉及金额': '452',
'损失金': '654',
'制度名称': '***制度',
'人数': '11'
}, {
'上缴国库': '456',
'具体问题': '**问题',
'党纪政纪': '****纪律',
'项数': '6',
'调账金额': '1123',
'补缴税费': '444',
'未整改原因': '****原因',
'责任单位': '黑龙江**公司',
'涉及金额': '211',
'损失金': '321',
'制度名称': '***制度',
'人数': '9'
}, {
'上缴国库': '459',
'具体问题': '**问题',
'党纪政纪': '****纪律',
'项数': '2',
'调账金额': '864',
'补缴税费': '253',
'未整改原因': '****原因',
'责任单位': '江苏**公司',
'涉及金额': '243',
'损失金': '368',
'制度名称': '***制度',
'人数': '6'
}, {
'上缴国库': '452',
'具体问题': '**问题',
'党纪政纪': '****纪律',
'项数': '8',
'调账金额': '555',
'补缴税费': '216',
'未整改原因': '****原因',
'责任单位': '洛阳**公司',
'涉及金额': '186',
'损失金': '312',
'制度名称': '***制度',
'人数': '10'
}, {
'上缴国库': '654',
'具体问题': '**问题',
'党纪政纪': '****纪律',
'项数': '1',
'调账金额': '864',
'补缴税费': '127',
'未整改原因': '****原因',
'责任单位': '济南**公司',
'涉及金额': '89',
'损失金': '164',
'制度名称': '***制度',
'人数': '7'
}, {
'上缴国库': '564',
'具体问题': '**问题',
'党纪政纪': '****纪律',
'项数': '8',
'调账金额': '645',
'补缴税费': '296',
'未整改原因': '****原因',
'责任单位': '山西**公司',
'涉及金额': '357',
'损失金': '344',
'制度名称': '***制度',
'人数': '19'
}],
'excelTitleList': [
{
'label': '责任单位',
'prop': '责任单位'
},
{
'label': '具体问题',
'prop': '具体问题'
},
{
'label': '涉及金额',
'prop': '涉及金额'
},
{
'label': '整改落实情况',
'prop': '整改落实情况',
'children': [
{
'label': '调账金额',
'prop': '调账金额'
},
{
'label': '上缴国库',
'prop': '上缴国库'
},
{
'label': '损失金',
'prop': '损失金'
},
{
'label': '补缴税费',
'prop': '补缴税费'
},
{
'label': '建设健全制度',
'prop': '建设健全制度',
'children': [
{
'label': '制度名称',
'prop': '制度名称'
},
{
'label': '项数',
'prop': '项数'
}
]
},
{
'label': '追责问',
'prop': '追责问',
'children': [
{
'label': '人数',
'prop': '人数'
},
{
'label': '党纪政纪',
'prop': '党纪政纪'
}
]
}
]
},
{
'label': '未整改问题',
'prop': '未整改问题',
'children': [
{
'label': '未整改原因',
'prop': '未整改原因'
},
{
'label': '金额',
'prop': '金额'
}
]
}
]
}
3.2 普通表头数据格式
{
'sheetName': '列表2', 'dataList': [
{ '姓名': '张三', '年龄': '50', '性别': '男' },
{ '姓名': '李四', '年龄': '21', '性别': '男' },
{ '姓名': '王五', '年龄': '30', '性别': '女' }
]
}
4 导出展示效果
4.1 普通表头
4.2 复杂表头