Excel动态导出(按字段导出)
*[ 此处需要建立一张字段名称及传值的基础配置表:
例如:选择: 姓名,年龄 传值:name,age 即可,用“,”隔开
]:
## 添加依赖
<!--导出Excel使用-->
<dependency>
<groupId>com.github.crab2died</groupId>
<artifactId>Excel4J</artifactId>
<version>2.1.4-Final2</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>3.14</version>
</dependency>
controller层
@GetMapping("Export")
@ApiOperation("Excel导出")
public ResponseEntity<byte[]> exportExcel(HttpServletRequest request, HttpServletResponse response,@RequestParam String strsname) {
return shareTaskThemeBasicService.exportExcel(request,response,strsname);
}
serveceimpl层
@Override
public ResponseEntity<byte[]> exportExcel(HttpServletRequest request, HttpServletResponse response,String strsname) {
try {
List<Basic> list = BasicMapper.selectList(null);
return buildResponseEntity(export((List<Basic>) list,(String) strsname), "导出表的名称.xls");
} catch (Exception e) {
e.printStackTrace();
}
return null;
}
private ResponseEntity<byte[]> buildResponseEntity(InputStream is, String name) throws IOException {
final Logger logger = LoggerFactory.getLogger(this.getClass());
if (this.logger.isDebugEnabled())
this.logger.debug("download: " + name);
HttpHeaders header = new HttpHeaders();
String fileSuffix = name.substring(name.lastIndexOf('.') + 1);
fileSuffix = fileSuffix.toLowerCase();
Map<String, String> arguments = new HashMap<String, String>();
arguments.put("xlsx", "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
arguments.put("xls", "application/vnd.ms-excel");
String contentType = arguments.get(fileSuffix);
header.add("Content-Type", (org.springframework.util.StringUtils.hasText(contentType) ? contentType : "application/x-download"));
if (is != null && is.available() != 0) {
header.add("Content-Length", String.valueOf(is.available()));
header.add("Content-Disposition", "attachment;filename*=utf-8'zh_cn'" + URLEncoder.encode(name, "UTF-8"));
byte[] bs = IOUtils.toByteArray(is);
return new ResponseEntity<>(bs, header, HttpStatus.OK);
} else {
String string = "数据为空";
header.add("Content-Length", "0");
header.add("Content-Disposition", "attachment;filename*=utf-8'zh_cn'" + URLEncoder.encode(name, "UTF-8"));
return new ResponseEntity<>(string.getBytes(), header, HttpStatus.OK);
}
}
private InputStream export(List<Basic> list,String strsname) {
ByteArrayOutputStream output = null;
InputStream inputStream1 = null;
SXSSFWorkbook wb = new SXSSFWorkbook(1000);// 保留1000条数据在内存中
SXSSFSheet sheet = wb.createSheet("工作簿名称");
// 设置报表头样式
CellStyle header = ExcelFormatUtil.headSytle(wb);// cell样式
CellStyle content = ExcelFormatUtil.contentStyle(wb);// 报表体样式
String[] strs = strsname.split(",");
// 字段名所在表格的宽度
int[] ints = new int[100];
for(int i=0;i<100;i++){
ints[i] = (int)8000;
}
String strname=strsname;
String[] split1 = strname.split(",");
StringBuffer stringBuffer1 = new StringBuffer();
for (String s : split1) {
List<ExportTaskInfo> exportTaskInfos = exportTaskInfoMapper.selectList(null);
for (ExportTaskInfo info : exportTaskInfos) {
if (s.equals(info.getBasecode())){
stringBuffer1.append(info.getBasename()+",");
}
}
}
String[] split2 = stringBuffer1.toString().split(",");
// 设置表头样式
ExcelFormatUtil.initTitleEX(sheet, header, split2, ints);
List<UtilBasecodeInfo> polist;
List<UtilBasecodeInfo> aplist;
List<UtilBasecodeInfo> bulist;
List<UtilBasecodeInfo> selist;
List<UtilBasecodeInfo> halist;
List<UtilBasecodeInfo> lilist;
List<UtilBasecodeInfo> eslist1;
List<UtilBasecodeInfo> islist ;
List<UtilBasecodeInfo> uselist;
List<UtilBasecodeInfo> userlist;
List<UtilBasecodeInfo> corlist;
List<UtilBasecodeInfo> corplist;
if (list != null && list.size() > 0) {
//放 utilbasecode 数据
polist = selectCodeList("POWERSOURCE");
aplist = selectCodeList("APPROVELIMITDIVIDE");
bulist = selectCodeList("BUSINESSCHARACTER");
selist = selectCodeList("SERVERTYPE");
halist = selectCodeList("HANDLETYPE");
lilist = selectCodeList("LINKWAY");
eslist1 = selectCodeList("ESTABLISHTYPE");
islist = selectCodeList("ISSUPPORTAPP");
uselist = selectCodeList("USERTARGETTYPE");
userlist = selectCodeList("USERPERIODTYPE");
corlist = selectCodeList("CORPTARGETTYPE");
corplist = selectCodeList("CORPPERIODTYPE");
for (int i = 0; i < list.size(); i++) {
Basic basic = list.get(i);
SXSSFRow row = sheet.createRow(i + 1);
int j = 0;
for (String str : strs) {
SXSSFCell cell;
if (str.equals("ROWGUID")) {
cell = row.createCell(j++);
cell.setCellValue(basic .getRowguid());
cell.setCellStyle(content);
}
if (str.equals("TASKNAME")) {
cell = row.createCell(j++);
cell.setCellValue(basic .getTaskname()); //
cell.setCellStyle(content);
}
if (str.equals("EXTENDNAME")) {
cell = row.createCell(j++);
cell.setCellValue(basic .getExtendname()); //
cell.setCellStyle(content);
}
if (str.equals("CATALOGCODE")) {
cell = row.createCell(j++);
cell.setCellValue(basic .getCatalogcode()); //
cell.setCellStyle(content);
}
if (str.equals("LOCALCATALOGCODE")) {
cell = row.createCell(j++);
cell.setCellValue(basic .getLocalcatalogcode()); //
cell.setCellStyle(content);
}
if (str.equals("TASKTYPE")) {
cell = row.createCell(j++);
if ("32".equals(basic .getTasktype())){
cell.setCellValue("主题服务");
}
cell.setCellStyle(content);
}
//分类
if (str.equals("TASKTYPECLASSIFY")) {
cell = row.createCell(j++);
if (!StringUtils.isEmpty(basic .getTasktypeclassify())) {
if ("1".equals(basic .getTasktypeclassify())) {
cell.setCellValue("依申请"); //
} else {
cell.setCellValue("非依申请"); //
}
}
cell.setCellStyle(content);
}
//依据
if (str.equals("BYLAW")) {
cell = row.createCell(j++);
cell.setCellValue(basic .getBylaw());
cell.setCellStyle(content);
}
//来源
if (str.equals("POWERSOURCE")) {
cell = row.createCell(j++);
if (!StringUtils.isEmpty(basic .getPowersource())) {
for (UtilBasecodeInfo info : polist) {
if (info.getCodecode().equals(basic .getPowersource())) {
cell.setCellValue(info.getCodename()); //
}
}
}
cell.setCellStyle(content);
}
//层级
if (str.equals("USELEVEL")) {
cell = row.createCell(j++);
if ("3".equals(basic .getUselevel())){
cell.setCellValue("区级"); //
}
cell.setCellStyle(content);
}
//划分
if (str.equals("APPROVELIMITDIVIDE")) {
cell = row.createCell(j++);
if (!StringUtils.isEmpty(basic .getApprovelimitdivide())) {
for (UtilBasecodeInfo info : aplist) {
if (info.getCodecode().equals(basic .getApprovelimitdivide())) {
cell.setCellValue(info.getCodename()); //
}
}
cell.setCellStyle(content);
}
//行使内容
if (str.equals("APPROVELIMITDIVIDEEXP")) {
cell = row.createCell(j++);
cell.setCellValue(basic .getApprovelimitdivideexp()); //
cell.setCellStyle(content);
}
//编码
if (str.equals("TASKCODE")) {
cell = row.createCell(j++);
cell.setCellValue(basic .getTaskcode()); //
cell.setCellStyle(content);
}
//地方
if (str.equals("LOCALTASKCODE")) {
cell = row.createCell(j++);
cell.setCellValue(basic .getLocaltaskcode()); //
cell.setCellStyle(content);
}
//业务
if (str.equals("TASKHANDLEITEM")) {
cell = row.createCell(j++);
cell.setCellValue(basic .getTaskhandleitem());
cell.setCellStyle(content);
}//状态
if (str.equals("TASKSTATE")) {
cell = row.createCell(j++);
if (!StringUtils.isEmpty(basic .getTaskstate())) {
if ("1".equals(basic .getTaskstate())) {
cell.setCellValue("在用");
} else {
cell.setCellValue("取消");
}
}
cell.setCellStyle(content);
}
//版本
if (str.equals("TASKVERSION")) {
cell = row.createCell(j++);
cell.setCellValue(basic .getTaskversion()); //
cell.setCellStyle(content);
}
//特性
if (str.equals("BUSINESSCHARACTER")) {
cell = row.createCell(j++);
for (UtilBasecodeInfo info : bulist) {
if (info.getCodecode().equals(basic .getBusinesscharacter())) {
cell.setCellValue(info.getCodename());
}
}
cell.setCellStyle(content);
}//部门
if (str.equals("DEPARTMENT_CODE")) {
cell = row.createCell(j++);
cell.setCellValue(basic .getDepartmentCode()); //
cell.setCellStyle(content);
}
//主体
if (str.equals("DEPTNAME")) {
cell = row.createCell(j++);
cell.setCellValue(basic .getDeptname()); //
cell.setCellStyle(content);
}//性质
if (str.equals("DEPTTYPE")) {
cell = row.createCell(j++);
if ("1".equals(basic .getDepttype())){
cell.setCellValue("法定机关");
}
if ("2".equals(basic .getDepttype())){
cell.setCellValue("授权组织"); //
}/*else {
cell.setCellValue(shareTaskThemeBasic.getDepttype()); //
}*/
cell.setCellStyle(content);
}
//编码
if (str.equals("DEPTCODE")) {
cell = row.createCell(j++);
cell.setCellValue(basic .getDeptcode());
cell.setCellStyle(content);
}
}
}
}
try {
output = new ByteArrayOutputStream();
wb.write(output);
inputStream1 = new ByteArrayInputStream(output.toByteArray());
output.flush();
} catch (Exception e) {
e.printStackTrace();
} finally {
try {
if (output != null) {
output.close();
if (inputStream1 != null)
inputStream1.close();
}
} catch (IOException e) {
e.printStackTrace();
}
}
return inputStream1;
}
##导出工具类:
// A code block
var foo = 'bar';
/**
* excle样式工具类
*/
public class ExcelFormatUtil {
/**
* 设置报表头样式
* ding
*/
public static CellStyle headSytle(SXSSFWorkbook workbook){
// 设置style1的样式,此样式运用在第二行
CellStyle style1 = workbook.createCellStyle();// cell样式
// 设置单元格背景色,设置单元格背景色以下两句必须同时设置
style1.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);// 设置填充样式
// style1.setFillForegroundColor(HSSFColor.GREY_25_PERCENT.index);// 设置填充色
//设置蓝色
style1.setFillForegroundColor(HSSFColor.SKY_BLUE.index);// 设置填充色
// 设置单元格上、下、左、右的边框线
style1.setBorderBottom(HSSFCellStyle.BORDER_THIN);
style1.setBorderLeft(HSSFCellStyle.BORDER_THIN);
style1.setBorderRight(HSSFCellStyle.BORDER_THIN);
style1.setBorderTop(HSSFCellStyle.BORDER_THIN);
Font font1 = workbook.createFont();// 创建一个字体对象
font1.setBoldweight((short) 10);// 设置字体的宽度
font1.setFontHeightInPoints((short) 10);// 设置字体的高度
font1.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);// 粗体显示
style1.setFont(font1);// 设置style1的字体
style1.setWrapText(true);// 设置自动换行
style1.setAlignment(HSSFCellStyle.ALIGN_CENTER);// 设置单元格字体显示居中(左右方向)
style1.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);// 设置单元格字体显示居中(上下方向)
return style1;
}
/**
* 设置报表体样式
* @param wb
* @return
*/
public static CellStyle contentStyle(SXSSFWorkbook wb){
// 设置style1的样式,此样式运用在第二行
CellStyle style1 = wb.createCellStyle();// cell样式
// 设置单元格上、下、左、右的边框线
style1.setBorderBottom(HSSFCellStyle.BORDER_THIN);
style1.setBorderLeft(HSSFCellStyle.BORDER_THIN);
style1.setBorderRight(HSSFCellStyle.BORDER_THIN);
style1.setBorderTop(HSSFCellStyle.BORDER_THIN);
style1.setWrapText(false);// 设置自动换行
style1.setAlignment(HSSFCellStyle.ALIGN_LEFT);// 设置单元格字体显示居中(左右方向)
style1.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);// 设置单元格字体显示居中(上下方向)
return style1;
}
/**
* 设置报表标题样式
* @param workbook
* @return
*/
public static HSSFCellStyle titleSytle(HSSFWorkbook workbook, short color, short fontSize){
// 设置style1的样式,此样式运用在第二行
HSSFCellStyle style1 = workbook.createCellStyle();// cell样式
// 设置单元格背景色,设置单元格背景色以下两句必须同时设置
//style1.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);// 设置填充样式
//short fcolor = color;
if(color != HSSFColor.WHITE.index){
style1.setFillForegroundColor(color);// 设置填充色
}
// 设置单元格上、下、左、右的边框线
style1.setBorderBottom(HSSFCellStyle.BORDER_THIN);
style1.setBorderLeft(HSSFCellStyle.BORDER_THIN);
style1.setBorderRight(HSSFCellStyle.BORDER_THIN);
style1.setBorderTop(HSSFCellStyle.BORDER_THIN);
HSSFFont font1 = workbook.createFont();// 创建一个字体对象
font1.setBoldweight(fontSize);// 设置字体的宽度
font1.setFontHeightInPoints(fontSize);// 设置字体的高度
font1.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);// 粗体显示
style1.setFont(font1);// 设置style1的字体
style1.setWrapText(true);// 设置自动换行
style1.setAlignment(HSSFCellStyle.ALIGN_CENTER);// 设置单元格字体显示居中(左右方向)
style1.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);// 设置单元格字体显示居中(上下方向)
return style1;
}
/**
*设置表头
* @param sheet
*/
public static void initTitleEX(SXSSFSheet sheet, CellStyle header, String title[], int titleLength[]) {
SXSSFRow row0 = sheet.createRow(0);
row0.setHeight((short) 800);
for(int j = 0;j<title.length; j++) {
SXSSFCell cell = row0.createCell(j);
//设置每一列的字段名
cell.setCellValue(title[j]);
cell.setCellStyle(header);
sheet.setColumnWidth(j, titleLength[j]);
}
}
}