基于POI 封装导出工具类
由于业务需求的变化 又懒得导出的时候创建dto等文件
所以封装了这套代码
v1.0
- 解决导出因需求改变,样式变换问题.
- 解决序号,表头,头文件存在与否,生成是否影响问题
-
导入依赖
<!-- https://mvnrepository.com/artifact/org.apache.poi/poi --> <dependency> <groupId>org.apache.poi</groupId> <artifactId>poi</artifactId> <version>5.0.0</version> </dependency> <!-- https://mvnrepository.com/artifact/org.apache.poi/poi-ooxml --> <dependency> <groupId>org.apache.poi</groupId> <artifactId>poi-ooxml</artifactId> <version>5.0.0</version> </dependency> <dependency> <groupId>org.apache.poi</groupId> <artifactId>poi-scratchpad</artifactId> <version>5.0.0</version> </dependency>
2.封装工具类
package cn.xuxuweizhi.project.util;
import cn.xuxuweizhi.project.ExeclStyles.DefaultStyleByExecl;
import lombok.extern.slf4j.Slf4j;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.util.CellRangeAddress;
import org.springframework.stereotype.Component;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.io.OutputStream;
import java.net.URLEncoder;
import java.util.List;
/**
* @author xuyefang
* @date 2021/7/20 16:59
*/
@Component
@Slf4j
public class ExcelUtils {
/**
* 头部样式
*/
private final static String HEADER_STYLE = "getHeaderStyle";
/**
* 表头样式
*/
private final static String TITLE_STYLE = "getTitleStyle";
/**
* 行_列样式
*/
private final static String ROW_CELL_STYLE = "setRowOrCellStyle";
/**
* @param response 响应
* @param titles 表头
* @param data 数据 没有传null
* @param sheetName 表单名
* @param headerTitle 表头 没有就传null
* @param excelName 导出的名称
* @param cls 样式的类 没有重写就传null 使用默认的
* @param openNumber 是否设置序号 即第一列是否要序号
* @throws Exception 异常
*/
public void exportExcel(HttpServletResponse response, Object[] titles, List<Object[]> data, String sheetName, String headerTitle, String excelName, Class<?> cls, boolean openNumber) throws Exception {
if (cls == null) {
//获取默认样式---后续可以改成通过枚举 获取对应的样式 从而达到多样式自由切换
cls = DefaultStyleByExecl.class;
}
//反射获取类中的方法
final Object o = cls.newInstance();
//创建Excel工作薄对象
HSSFWorkbook workbook = new HSSFWorkbook();
//创建Excel工作表对象 // 生成一个表格
HSSFSheet sheet = workbook.createSheet(sheetName);
HSSFRow row = sheet.createRow(0);
HSSFCell cell;
//如果有大表头 则
if (headerTitle != null && !headerTitle.trim().isEmpty()) {
//有表头就设置在第一行 即 0行 此版本用sheet.getLastRowNum() 存在问题 所以直接写0
cell = row.createCell(0);
//表头样式 合并单元格 ---这里的合并是默认样式 特别需求是支持在头部样式中重写的
if (openNumber) {
sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, titles.length));
} else {
sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, titles.length - 1));
}
cell.setCellValue(headerTitle);
//获取头部样式
final CellStyle headerStyle = (CellStyle) getDeclareMethod(cls,HEADER_STYLE, HSSFWorkbook.class, HSSFSheet.class)
.invoke(o, workbook, sheet);
cell.setCellStyle(headerStyle);
//创建表头行
row = sheet.createRow(sheet.getLastRowNum() + 1);
}
//-------------------------设置表头----------------------------------
// 将第一列设为序号
if (openNumber) {
for (int i = 0; i <= titles.length; i++) {
cell = row.createCell(i);
if (i == 0) {
cell.setCellValue("序号");
} else {
cell.setCellValue(titles[i - 1].toString());
}
//获取标题样式
final CellStyle titleStyle = (CellStyle) getDeclareMethod(cls,TITLE_STYLE, HSSFWorkbook.class, HSSFSheet.class)
.invoke(o, workbook, sheet);
cell.setCellStyle(titleStyle);
}
} else {
for (int i = 0; i < titles.length; i++) {
cell = row.createCell(i);
cell.setCellValue(titles[i].toString());
//获取标题样式
final CellStyle titleStyle = (CellStyle) getDeclareMethod(cls,TITLE_STYLE, HSSFWorkbook.class, HSSFSheet.class)
.invoke(o, workbook, sheet);
cell.setCellStyle(titleStyle);
}
}
//--------------------------------------创建单元格--------------------------------------------------------
int j = 1;
if (data != null && data.size() > 0) {
for (Object[] obj : data) {
//创建行的单元格，从1开始
row = sheet.createRow(sheet.getLastRowNum() + 1);
if (openNumber) {
for (int i = 0; i <= obj.length; i++) {
cell = row.createCell(i);
// 将第一列设为序号
if (i == 0) {
cell.setCellValue(j++);
} else {
//由于设置了序号 所以-1
cell.setCellValue(obj[i - 1].toString());
}
}
} else {
for (int i = 0; i < obj.length; i++) {
cell = row.createCell(i);
//由于设置了序号 所以-1
cell.setCellValue(obj[i].toString());
}
}
}
//对列样式进行配置
getDeclareMethod(cls,ROW_CELL_STYLE, HSSFWorkbook.class, HSSFSheet.class).invoke(o, workbook, sheet);
}
this.export(response, excelName, workbook);
}
/**
* workbook 写入 response流 给前端
*
* @param response 响应
* @param excelName 文件名
* @param workbook excel 对象
* @throws IOException IO 异常
*/
private void export(HttpServletResponse response, String excelName, HSSFWorkbook workbook) throws IOException {
//文档输出
response.setCharacterEncoding("utf-8");
response.setContentType("application/msword");
String fileName = excelName + ".xls";
response.setHeader("Content-Disposition", "attachment;filename="
.concat(String.valueOf(URLEncoder.encode(fileName, "UTF-8"))));
try (OutputStream out = response.getOutputStream()) {
workbook.write(out);
} catch (IOException e) {
log.error("导出发生异常----------------", e);
}
}
/**
* 根据属性名查找对应的属性，子类没有的属性会到父类找
*
* @param methodName 方法名
* @return 方法
*/
public static Method getDeclareMethod(Class<?> cls, String methodName, Class<?>... var2) {
Method declaredMethod = null;
for (; cls != Object.class; cls = cls.getSuperclass()) {
try {
declaredMethod = cls.getDeclaredMethod(methodName, var2);
} catch (NoSuchMethodException e) {
log.error("获取方法失败");
}
}
return declaredMethod;
}
}封装样式类 --默认样式类
package cn.xuxuweizhi.project.ExeclStyles;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
/**
* 这个是excel样式 默认 excel 导出样式类
* 如非特别需求 建议不要动这个
* 如果样式需要修改 只需要重写这个类就行
* 此版本支持POI 5.0
* 样式设置可以参考官方文档:http://poi.apache.org/components/spreadsheet/quick-guide.html#FillsAndFrills
*
* @author xuyefang
* @version v1.0
* @date 2021/7/23 10:46
*/
public class DefaultStyleByExecl {
/**
* @param workbook workbook对象
* @return 表头样式
*/
public CellStyle getTitleStyle(HSSFWorkbook workbook, HSSFSheet sheet) {
//灰色背景
CellStyle style = workbook.createCellStyle();
// Create a new font and alter it.
Font font = workbook.createFont();
font.setFontHeightInPoints((short) 10);
//设置默认列宽
sheet.setDefaultColumnWidth(20);
font.setFontName("Courier New");
// Fonts are set into a style so create a new one to use.
style.setFont(font);
//设置水平对齐的样式为居中对齐;
style.setAlignment(HorizontalAlignment.CENTER);
//设置垂直对齐的样式为居中对齐;
style.setVerticalAlignment(VerticalAlignment.CENTER);
return style;
}
/**
* 头部样式
*
* @param workbook workbook对象
* @return 头部样式
*/
public CellStyle getHeaderStyle(HSSFWorkbook workbook, HSSFSheet sheet) {
CellStyle style = workbook.createCellStyle();
//设置水平对齐的样式为居中对齐;
style.setAlignment(HorizontalAlignment.CENTER);
//设置垂直对齐的样式为居中对齐;
style.setVerticalAlignment(VerticalAlignment.CENTER);
// Create a new font and alter it.
Font font = workbook.createFont();
font.setFontHeightInPoints((short) 15);
font.setFontName("Courier New");
// Fonts are set into a style so create a new one to use.
style.setFont(font);
return style;
}
/**
* 单独对行，列进行样式设置
*/
public void setRowOrCellStyle(HSSFWorkbook workbook, HSSFSheet sheet) {
HSSFCellStyle cellStyle = workbook.createCellStyle();
final int rowNum = sheet.getLastRowNum();
//设置默认列宽
sheet.setDefaultColumnWidth(20);
for (int i = 0; i <= rowNum; i++) {
final short lastCellNum = sheet.getRow(i).getLastCellNum();
//这里对行样式进行设置
for (int j = 0; j < lastCellNum; j++) {
// 这里对列样式进行设置
//自动换行
cellStyle.setWrapText(true);
//设置水平对齐的样式为居中对齐;
cellStyle.setAlignment(HorizontalAlignment.CENTER);
//设置垂直对齐的样式为居中对齐;
cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
sheet.getRow(i).getCell(j).setCellStyle(cellStyle);
}
}
}
}注意：本文归作者所有，未经作者允许，不得转载
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
