现有的导出excel一般用的比较多的是阿里的easyExcel或者apache的poi 但是这些都是封装好的导出插件 不利于一些定制化的导出 基础这个原因 这边写了一个比较简单的 动态的导出excel 里面注释还是比较清楚的 有哪些不好的地方 欢迎大家多多指教
package com.zbh.service.impl;
import com.zbh.domain.DataIndicators;
import com.zbh.mapper.ExportDynamicDataMapper;
import com.zbh.service.ExportDynamicDataService;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.FillPatternType;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.poi.xssf.streaming.SXSSFCell;
import org.apache.poi.xssf.streaming.SXSSFRow;
import org.apache.poi.xssf.streaming.SXSSFSheet;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.io.UnsupportedEncodingException;
import java.net.URLEncoder;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
/**
* @description:
* @Author: zbh
* @create: 2024/1/30 11:06
* @Version: 1.0
*/
@Service
public class ExportDynamicDataServiceImpl implements ExportDynamicDataService {
@Override
public void exportDynamicData(HttpServletResponse response) {
try {
List<String> list = new ArrayList<>();//模拟数据
Map<Integer,List<String>> map = new HashMap<>();
for (int i = 1; i <= 10; i++) {
list.add("这是模拟数据"+i);
map.put(i,list);
}
//动态导出excel
//初始化工作簿
SXSSFWorkbook workbook = initWorkbook();
//获取第一个sheet
SXSSFSheet sheet = workbook.getSheetAt(0);
sheet.autoSizeColumn(0);//自动调节列的宽度
//创建数据行
for (int i = 1; i < list.size(); i++) {
//创建表格中数据行,第一行是表头,所以从第二行开始
SXSSFRow row = sheet.createRow(i);
//导出固定数据
exportFixeData(list.get(i-1),row);
//导出动态数据
exportDynamicData(row,list);
}
//导出数据
String fileName = URLEncoder.encode("zbhexcel"+".xlsx","UTF-8");
response.addHeader("Content-Disposition","attachment;filename="+fileName);
response.setContentType("application/force-download");
response.setCharacterEncoding("utf-8");
ServletOutputStream outputStream = response.getOutputStream();
workbook.write(outputStream);
outputStream.flush();
} catch (UnsupportedEncodingException e) {
throw new RuntimeException(e);
} catch (IOException e) {
throw new RuntimeException(e);
}
}
/**
* 导出固定数据
*/
public void exportFixeData(String column,SXSSFRow row){
SXSSFCell cell = row.createCell(0);
cell.setCellValue(column);
}
/**
* 导出动态数据
*/
public void exportDynamicData(SXSSFRow row,List<String> list){
for (int i = 1; i < list.size(); i++) {
SXSSFCell cell = row.createCell(i);
cell.setCellValue(list.get(i-1));
}
}
/**
* 初始化工作簿
* @return
*/
public SXSSFWorkbook initWorkbook(){
//创建workbook
SXSSFWorkbook workbook = new SXSSFWorkbook(1000);
CellStyle style = workbook.createCellStyle();//创建单元格样式
style.setFillBackgroundColor(IndexedColors.GREY_50_PERCENT.getIndex());//第一行底色置灰
style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
//获取表头数据
String[] headers = getHeaders();
//创建sheets
SXSSFSheet sheet = workbook.createSheet();
//创建表头行
SXSSFRow row = sheet.createRow(0);
//自动调节列宽
sheet.trackAllColumnsForAutoSizing();
//获取动态表头长度
int headerLength = headers.length;
//填入表头行数据
for (int i = 0; i < headers.length; i++) {
SXSSFCell cell = row.createCell(i);//创建单元格
cell.setCellValue(headers[i]);//给每个单元格赋值
}
return workbook;
}
/**
* 获取表头数据
* @return
*/
public String[] getHeaders(){
//获取第一列固定数据
String[] fixHeaders = {"这是固定的第一列"};
//获取后面动态列
String[] dynamicHeaders = getDynamicHeaders();
//开始拼接 固定表头与动态表头 获取目标表头的长度
int headLength = fixHeaders.length + dynamicHeaders.length;
//构建目标表头
String[] targetHeaders = new String[headLength];
//先把固定表头数据赋值到目标表头
System.arraycopy(fixHeaders,0,targetHeaders,0,fixHeaders.length);//具体参数直接看源码,一看就懂
//在把动态表头数据赋值到目标表头
System.arraycopy(dynamicHeaders,0,targetHeaders,fixHeaders.length,dynamicHeaders.length);
return targetHeaders;
}
/**
* 获取动态表头列
* @return
*/
public String[] getDynamicHeaders(){
List<String> list = new ArrayList<>();
for (int i = 1; i < 10; i++) {
list.add("这是模拟的动态数据"+i);
}
//给动态的数据赋值给表头
String[] strings = new String[list.size()];
for (int i = 0; i < list.size(); i++) {
strings[i] = list.get(i);
}
return strings;
}
}