项目上这种功能很多，写了一个工具类，代码有点垃圾，大神勿喷
导入导出poi组件Excel
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>3.9</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>3.9</version>
</dependency>
package com.tc.util
import org.apache.poi.hssf.usermodel.*
import org.apache.poi.ss.usermodel.Footer
import org.apache.poi.ss.usermodel.Header
import org.apache.poi.ss.util.CellRangeAddress
import org.apache.poi.xssf.usermodel.*
import org.slf4j.Logger
import org.slf4j.LoggerFactory
import org.springframework.stereotype.Component
import javax.servlet.http.HttpServletResponse
import java.io.OutputStream
import java.lang.reflect.Field
import java.net.URLEncoder
import java.text.SimpleDateFormat
import java.util.HashMap
import java.util.List
import java.util.Map
/**
* 导出工具类 <p>07导出不限制行数，03有66535局限性</p>
*
* @author Fyg_gm
* @since 2021-05-12 21:01:20
*/
@Component
public class ExcelUtil {
Logger logger = LoggerFactory.getLogger(ExcelUtil.class)
/**
* 07 无限制导出
*
* @param response
* @param list 导出的数据
* @param filename 导出文件名
* @param title 导出表头
*/
public void exportMultiToDownFile(HttpServletResponse response, List<?> list, String filename, String[] title) {
Logger logger = LoggerFactory.getLogger(ExcelUtil.class)
filename += new SimpleDateFormat("yyyy-MM-dd").format(System.currentTimeMillis())
//创建工作薄
XSSFWorkbook workbook = new XSSFWorkbook()
//创建表单
XSSFSheet sheet = genSheet(workbook, filename)
//创建表单样式
Map<String, XSSFCellStyle> tableStyle = createStyle(workbook)//创建表头样式
Map<String, XSSFCellStyle> titleStyle = createStyle(workbook)//创建标题样式
Map<String, XSSFCellStyle> contextStyle = createStyle(workbook)//创建正文样式
//创建Excel
genExcel(filename, list, title, sheet, tableStyle.get("cellStyle2"), titleStyle.get("cellStyle"), contextStyle.get("cellStyle3"))
String suffix = ".xls"
filename += suffix
// 最终已流的形式返回
OutputStream out = null
try {
out = response.getOutputStream()
response.setHeader("content-type", "application/octet-stream")
response.setContentType("application/octet-stream")
response.addHeader("Content-Disposition", "attachment; filename=" + URLEncoder.encode(filename, "UTF-8"))
workbook.write(out)
out.flush()
out.close()
} catch (Exception e) {
e.printStackTrace()
logger.info(e.getMessage())
} finally {
try {
if (out != null) {
out.close()
}
} catch (Exception e) {
e.printStackTrace()
logger.info(e.getMessage())
}
}
}
/**
* 03 限制导出行数66535 有局限性
*
* @param response
* @param list 导出的数据
* @param filename 导出的文件名
* @param title 导出的表头
*/
public void exportToDownFile(HttpServletResponse response, List<?> list, String filename, String[] title) {
filename += new SimpleDateFormat("yyyy-MM-dd").format(System.currentTimeMillis())
String suffix = ".xls"
filename += suffix
//创建excel表
HSSFWorkbook workbook = new HSSFWorkbook()
//建立sheet对象
HSSFSheet sheet = workbook.createSheet(filename)
//设置默认行宽
sheet.setDefaultColumnWidth(20)
//创建样式
Map<String, HSSFCellStyle> style = createStyle(workbook)
//创建表头
HSSFRow row = sheet.createRow(0)
row.setHeightInPoints(20)//行高
HSSFCell cell = row.createCell(0)
cell.setCellValue(filename)
cell.setCellStyle(style.get("cellStyle"))
sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, (title.length - 1)))
//创建标题
HSSFRow rowTitle = sheet.createRow(1)
rowTitle.setHeightInPoints(20)
HSSFCell hc
for (int i = 0 i < title.length i++) {
hc = rowTitle.createCell(i)
hc.setCellValue(title[i])
//设置标题样式
hc.setCellStyle(style.get("cellStyle2"))
}
//创建表格数据
Field[] fields
int i = 2
int index = 0//记录额外创建的sheet数量
//数据源
for (Object obj : list) {
//反射获取到实体
fields = obj.getClass().getDeclaredFields()
HSSFRow rowBody = sheet.createRow(i)
rowBody.setHeightInPoints(20)
int j = 0
//遍历渲染表格
for (Field f : fields) {
f.setAccessible(true)
Object va = null
try {
va = f.get(obj)
} catch (IllegalAccessException e) {
e.printStackTrace()
}
if (null == va) {
va = "---"
}
//创建行数
hc = rowBody.createCell(j)
//赋值数据
hc.setCellValue(va.toString())
//设置表格样式
hc.setCellStyle(style.get("cellStyle3"))
j++
}
i++
}
//最终已流的形式返回
OutputStream out = null
try {
out = response.getOutputStream()
response.setHeader("content-type", "application/octet-stream")
response.setContentType("application/octet-stream")
response.addHeader("Content-Disposition", "attachment; filename=" + URLEncoder.encode(filename, "UTF-8"))
workbook.write(out)
out.flush()
out.close()
} catch (Exception e) {
e.printStackTrace()
logger.info(e.getMessage())
} finally {
try {
if (out != null) {
out.close()
}
} catch (Exception e) {
e.printStackTrace()
logger.info(e.getMessage())
}
}
}
//封装动态渲染excel
public static void genExcel(String filename, List<?> list, String[] title, XSSFSheet sheet, XSSFCellStyle tableStyle, XSSFCellStyle titleStyle, XSSFCellStyle contextStyle) {
//设置默认行宽
sheet.setDefaultColumnWidth(20)
//创建第一行，为标题，index从0开始
XSSFRow row = sheet.createRow(0)
row.setHeightInPoints(20)
//创建一列
XSSFCell cell = row.createCell(0)
//标题
cell.setCellValue(filename)
//设置标题样式
cell.setCellStyle(titleStyle)
//设置标题位置
sheet.addMergedRegion(new CellRangeAddress(
0, //first row
0, //last row
0, //first column
(title.length - 1) //last column
))
//创建第二行 表头
XSSFRow rowTitle = sheet.createRow(1)
rowTitle.setHeightInPoints(20)
XSSFCell xc
for (int i = 0 i < title.length i++) {
xc = rowTitle.createCell(i)
xc.setCellValue(title[i])
//设置表头样式
xc.setCellStyle(tableStyle)
}
//从数据库取数据填充到Excel，
Field[] fields
//i从2开始计数，因为上面已经创建了 0 1行
int i = 2
for (Object obj : list) {
//反射获取到实体
fields = obj.getClass().getDeclaredFields()
//从第三行动态去创建
XSSFRow rowBody = sheet.createRow(i)
rowBody.setHeightInPoints(20)
int j = 0
//遍历渲染表格
for (Field f : fields) {
f.setAccessible(true)
Object va = null
try {
va = f.get(obj)
} catch (IllegalAccessException e) {
e.printStackTrace()
}
if (null == va) {
va = "---"
}
//创建行数
xc = rowBody.createCell(j)
//赋值数据
xc.setCellValue(va.toString())
//设置表格样式
xc.setCellStyle(contextStyle)
j++
}
i++
}
}
//设置表单，并生成表单
public static XSSFSheet genSheet(XSSFWorkbook workbook, String sheetName) {
//生成表单
XSSFSheet sheet = workbook.createSheet(sheetName)
//设置表单文本居中
sheet.setHorizontallyCenter(true)
sheet.setFitToPage(false)
//打印时在底部右边显示文本页信息
Footer footer = sheet.getFooter()
footer.setRight("Page " + HeaderFooter.numPages() + " Of " + HeaderFooter.page())
//打印时在头部右边显示Excel创建日期信息
Header header = sheet.getHeader()
header.setRight("Create Date " + HeaderFooter.date() + " " + HeaderFooter.time())
//设置打印方式
XSSFPrintSetup ps = sheet.getPrintSetup()
ps.setLandscape(true) // true：横向打印，false：竖向打印 ，因为列数较多，推荐在打印时横向打印
ps.setPaperSize(HSSFPrintSetup.A4_PAPERSIZE) //打印尺寸大小设置为A4纸大小
return sheet
}
//设置exportToDownFile样式
public Map<String, HSSFCellStyle> createStyle(HSSFWorkbook workbook) {
Map<String, HSSFCellStyle> map = new HashMap<>()
//表头样式（加粗，水平居中，垂直居中）
HSSFCellStyle cellStyle = workbook.createCellStyle()
cellStyle.setAlignment(XSSFCellStyle.ALIGN_CENTER)//水平居中
// cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);//垂直居中
//设置边框样式
cellStyle.setBorderBottom(XSSFCellStyle.BORDER_THIN) //下边框
cellStyle.setBorderLeft(XSSFCellStyle.BORDER_THIN)//左边框
cellStyle.setBorderTop(XSSFCellStyle.BORDER_THIN)//上边框
cellStyle.setBorderRight(XSSFCellStyle.BORDER_THIN)//右边框
HSSFFont fontStyle = workbook.createFont()
fontStyle.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD)
cellStyle.setFont(fontStyle)
//标题样式（加粗，垂直居中）
HSSFCellStyle cellStyle2 = workbook.createCellStyle()
// cellStyle2.setVerticalAlignment(VerticalAlignment.CENTER);//垂直居中
cellStyle2.setFont(fontStyle)
//设置边框样式
cellStyle2.setBorderBottom(XSSFCellStyle.BORDER_THIN) //下边框
cellStyle2.setBorderLeft(XSSFCellStyle.BORDER_THIN)//左边框
cellStyle2.setBorderTop(XSSFCellStyle.BORDER_THIN)//上边框
cellStyle2.setBorderRight(XSSFCellStyle.BORDER_THIN)//右边框
//字段样式（垂直居中）
HSSFCellStyle cellStyle3 = workbook.createCellStyle()
//设置边框样式
cellStyle3.setBorderBottom(XSSFCellStyle.BORDER_THIN) //下边框
cellStyle3.setBorderLeft(XSSFCellStyle.BORDER_THIN)//左边框
cellStyle3.setBorderTop(XSSFCellStyle.BORDER_THIN)//上边框
cellStyle3.setBorderRight(XSSFCellStyle.BORDER_THIN)//右边框
map.put("cellStyle", cellStyle)
map.put("cellStyle2", cellStyle2)
map.put("cellStyle3", cellStyle3)
return map
}
//设置exportMultiToDownFile样式
public Map<String, XSSFCellStyle> createStyle(XSSFWorkbook workbook) {
Map<String, XSSFCellStyle> map = new HashMap<>()
//表头样式（加粗，水平居中，垂直居中）
XSSFCellStyle cellStyle = workbook.createCellStyle()
cellStyle.setAlignment(XSSFCellStyle.ALIGN_CENTER)//水平居中
// cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);//垂直居中
//设置边框样式
cellStyle.setBorderBottom(XSSFCellStyle.BORDER_THIN) //下边框
cellStyle.setBorderLeft(XSSFCellStyle.BORDER_THIN)//左边框
cellStyle.setBorderTop(XSSFCellStyle.BORDER_THIN)//上边框
cellStyle.setBorderRight(XSSFCellStyle.BORDER_THIN)//右边框
XSSFFont fontStyle = workbook.createFont()
fontStyle.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD)
cellStyle.setFont(fontStyle)
//标题样式（加粗，垂直居中）
XSSFCellStyle cellStyle2 = workbook.createCellStyle()
// cellStyle2.setVerticalAlignment(VerticalAlignment.CENTER);//垂直居中
cellStyle2.setFont(fontStyle)
//设置边框样式
cellStyle2.setBorderBottom(XSSFCellStyle.BORDER_THIN) //下边框
cellStyle2.setBorderLeft(XSSFCellStyle.BORDER_THIN)//左边框
cellStyle2.setBorderTop(XSSFCellStyle.BORDER_THIN)//上边框
cellStyle2.setBorderRight(XSSFCellStyle.BORDER_THIN)//右边框
//字段样式（垂直居中）
XSSFCellStyle cellStyle3 = workbook.createCellStyle()
// cellStyle3.setVerticalAlignment(VerticalAlignment.CENTER);//垂直居中
//设置边框样式
cellStyle3.setBorderBottom(XSSFCellStyle.BORDER_THIN) //下边框
cellStyle3.setBorderLeft(XSSFCellStyle.BORDER_THIN)//左边框
cellStyle3.setBorderTop(XSSFCellStyle.BORDER_THIN)//上边框
cellStyle3.setBorderRight(XSSFCellStyle.BORDER_THIN)//右边框
map.put("cellStyle", cellStyle)
map.put("cellStyle2", cellStyle2)
map.put("cellStyle3", cellStyle3)
return map
}
}
评论区