SpringBoot+poi实现excel文档导出(二)
使用SpringBoot+poi实现excel文档导出,打算分为三篇书写,第一篇基于if-else导出,第二篇提取部分方法作为抽象方法导出,第三篇为全抽象导出。
二、提取部分方法作为抽象方法导出
本章继续延续第一章并加以深化,大致思路是这样的:
- 抽取反射部分作为默认方法,每次导出必须实现;
- 需要反射的导出字段实体对象引用泛型“V”代替;
- 对于每个对象的数据,样式和列宽等都不一样,抽取成为抽象方法,不同对象都要实现;
- 查询的时候如果使用baseService,实体对象肯定和导出字段实体对象的顺序不一样,引用泛型“T”代替实体,具体处理方式在第三章。
1. 接口
import com.example.export.vo.ScheduleVo;
import javax.servlet.http.HttpServletResponse;
public interface IExportService {
/**
* @Description 通用导出/模板下载
*/
void exportExcel(ScheduleVo vo, HttpServletResponse response) throws Exception;
}
2. 实现类(相比于第一章,反射已经抽取为抽象方法)
import com.example.export.dispose.ExcelDispose;
import com.example.export.dispose.datadispose.UserDispose;
import com.example.export.service.IExportService;
import com.example.export.util.DataExportUtils;
import com.example.export.vo.ResultVo;
import com.example.export.vo.ScheduleVo;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.stereotype.Service;
import javax.servlet.http.HttpServletResponse;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.List;
@Service
public class ExportServiceImpl implements IExportService {
@Override
public void exportExcel(ScheduleVo vo, HttpServletResponse response) throws IllegalAccessException {
// 新行的索引
int i = 1;
List<String> cellNameList = new ArrayList<>();
Integer width = null;
List<ResultVo> scheduleByConditions = findScheduleByConditions(vo);
String path = "excel/user.xlsx";
InputStream inputStream = DataExportUtils.getStream(path);
XSSFWorkbook wb = DataExportUtils.getwb(inputStream);
SXSSFWorkbook swb = DataExportUtils.getswb(wb);
Sheet sheet = DataExportUtils.getSheet(wb, 0);
Row row = DataExportUtils.getRow(sheet, 0);
CellStyle cellStyle = DataExportUtils.getCellStyle(swb);
int cells = row.getPhysicalNumberOfCells();;
for (Cell cell : row) {
String stringCellValue = cell.getStringCellValue();
cellNameList.add(stringCellValue);
}
for (int k = 0; k < scheduleByConditions.size(); k++) {
List<String> valueList = new ArrayList<>();
//创建新行
ResultVo resultVo = scheduleByConditions.get(k);
ExcelDispose<ResultVo> lgjcDispose = new UserDispose();
lgjcDispose.resultDispose(valueList, resultVo);
row = sheet.createRow(i++);
int xh = k + 1;
valueList.add(0, Integer.toString(xh));
lgjcDispose.dataDispose(row,sheet,cellNameList,valueList,cellStyle,width,cells);
}
DataExportUtils.sxssfWriteAndClose(response, inputStream, wb, swb);
}
/**
* 假设这里是一个查询语句,得到查询结果
* @param scheduleVo
* @return
*/
private List<ResultVo> findScheduleByConditions(ScheduleVo scheduleVo) {
ResultVo resultVo1 = new ResultVo();
ResultVo resultVo2 = new ResultVo();
ResultVo resultVo3 = new ResultVo();
ResultVo resultVo4 = new ResultVo();
ResultVo resultVo5 = new ResultVo();
List<ResultVo> resultVos = new ArrayList<>();
resultVo1.setName("账伞").setAge("17").setSex("男").setAddress("北京市朝阳区上雕刻技法").setContactInformation("2342893472374");
resultVo2.setName("里斯").setAge("43").setSex("女").setAddress("上海市黄浦区的发射点").setContactInformation("34534453");
resultVo3.setName("汪芜").setAge("23").setSex("男").setAddress("陕西省西安市瓦基尔克").setContactInformation("3131231");
resultVo4.setName("马尔扎哈").setAge("534").setSex("男").setAddress("山西省太原市昆仑山地方").setContactInformation("23544343");
resultVo5.setName("古力娜扎").setAge("43").setSex("女").setAddress("广东省广州市的快速减肥").setContactInformation("1213124242");
resultVos.add(resultVo1);
resultVos.add(resultVo2);
resultVos.add(resultVo3);
resultVos.add(resultVo4);
resultVos.add(resultVo5);
return resultVos;
}
}
}
3. 工具类
import cn.hutool.core.io.resource.ClassPathResource;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletResponse;
import java.io.ByteArrayOutputStream;
import java.io.IOException;
import java.io.InputStream;
/**
* 导出工具类
* @date
*/
public class DataExportUtils {
public static void sxssfWriteAndClose(HttpServletResponse response, InputStream inputStream, XSSFWorkbook workbook, SXSSFWorkbook sworkbook) {
ServletOutputStream out = null;
try {
ByteArrayOutputStream os = new ByteArrayOutputStream();
sworkbook.write(os);
byte[] bytes = os.toByteArray();
response.addHeader("Content-Length", String.valueOf(bytes.length));
out = changeOutputStream(os, response);
} catch (IOException exception) {
exception.printStackTrace();
}finally {
try {
inputStream.close();
workbook.close();
out.close();
sworkbook.close();
} catch (IOException e) {
e.printStackTrace();
}
}
}
/**
* 转换输出流
* @param os
* @param response
* @return
* @throws IOException
*/
public static ServletOutputStream changeOutputStream(ByteArrayOutputStream os, HttpServletResponse response) throws IOException {
ServletOutputStream outputStream = response.getOutputStream();
os.writeTo(outputStream);
os.close();
return outputStream;
}
/**
* 导出excel配置样式
* @param swb
* @return
*/
public static CellStyle getCellStyle(SXSSFWorkbook swb) {
CellStyle cellStyle;
//创建单元格,并设置值表头 设置表头居中
cellStyle = swb.createCellStyle();
//水平居中
cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
// 横向居中
cellStyle.setAlignment(HorizontalAlignment.CENTER);
// 边框
cellStyle.setBorderBottom(BorderStyle.THIN);
cellStyle.setBorderLeft(BorderStyle.THIN);
cellStyle.setBorderRight(BorderStyle.THIN);
cellStyle.setBorderTop(BorderStyle.THIN);
return cellStyle;
}
/**
* 校验最大宽度是否超出
* @param num
* @return
*/
public static Integer checkNum(Integer num){
Integer maxNum = null;
if (num * 700 > 15000){
maxNum = 14999;
return maxNum;
}
maxNum = num * 700;
return maxNum;
}
/**
* 获取序列化
*/
public static InputStream getStream(String path){
ClassPathResource resource = new ClassPathResource(path);
return resource.getStream();
}
/**
* 获取xssfworkbook
*/
public static XSSFWorkbook getwb(InputStream inputStream){
XSSFWorkbook wb = null;
try {
wb = new XSSFWorkbook(inputStream);
} catch (IOException e) {
e.printStackTrace();
}
return wb;
}
/**
* 获取sxssfworkbook
*/
public static SXSSFWorkbook getswb(XSSFWorkbook wb){
return new SXSSFWorkbook(wb);
}
/**
* 获取sheet
*/
public static Sheet getSheet(XSSFWorkbook wb, int i){
// SXSSFSheet sheet = swb.getSheetAt(i);
XSSFSheet sheetAt = wb.getSheetAt(i);
return sheetAt;
}
/**
*
* @param sheet
* @param i 对应文件的表头索引
* @return
*/
public static Row getRow(Sheet sheet, int i){
Row row = sheet.getRow(i);
return row;
}
}
4. Vo实体,跟excel导出文档字段顺序保持一致
import lombok.Data;
import lombok.experimental.Accessors;
@Data
@Accessors(chain = true)
public class ResultVo {
private String id;
private String name;
private String age;
private String sex;
private String address;
private String contactInformation;
}
5. 抽象方法(抽取出来的反射默认方法,还有一个每个类型必须实现的抽象方法)
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import java.lang.reflect.Field;
import java.util.List;
/**
* 模板/数据导出抽象类
* @param <V> Vo
*/
public abstract class ExcelDispose<V> {
/**
* 反射
* @param valueList
* @param v
* @throws IllegalAccessException
*/
public void resultDispose(List<String> valueList, V v) throws IllegalAccessException {
// 将实体中的数据提取出来放到list容器中
Class<?> aClass = v.getClass();
Field[] declaredFields = aClass.getDeclaredFields();
for (int o = 1; o < declaredFields.length; o++) {
Field declaredField = declaredFields[o];
declaredField.setAccessible(true);
Object value = declaredField.get(v);
String s = null != value ? value.toString() : "";
valueList.add(s);
// 关闭除public外的访问权限
declaredField.setAccessible(false);
}
}
/**
* 对表数据处理并写入到单元格,每张表必须实现,预留抽象方法
* @param row
* @param sheet
* @param cellNameList
* @param valueList
* @param cellStyle
* @param width
* @param cells
*/
public abstract void dataDispose(Row row, Sheet sheet, List<String> cellNameList,
List<String> valueList, CellStyle cellStyle, Integer width, int cells);
}
6. 实现抽象类中的抽象方法,单元格插入、样式插入、列宽设置、边框等等设置
import com.example.export.dispose.ExcelDispose;
import com.example.export.util.DataExportUtils;
import com.example.export.vo.ResultVo;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import java.util.List;
/**
* 插入表格数据,设置表格样式
* @author wp
*/
public class UserDispose extends ExcelDispose<ResultVo> {
@Override
public void dataDispose(Row row, Sheet sheet, List<String> cellNameList,
List<String> valueList, CellStyle cellStyle, Integer width, int cells) {
for (int j = 0; j < cells; j++) {
Cell cell = row.createCell(j);
for (int z = 0; z < valueList.size(); z++) {
if (j == z) {
if (j == 0) {
// 插入序号,并按照表头对序号进行列宽设置
width = DataExportUtils.checkNum(cellNameList.get(0).length());
sheet.setColumnWidth(j, width);
cell.setCellValue(valueList.get(z));
cell.setCellStyle(cellStyle);
break;
}
cell.setCellValue(valueList.get(z));
cell.setCellStyle(cellStyle);
break;
}
}
}
}
}
7. Controller层
import com.example.export.service.IExportService;
import com.example.export.vo.ScheduleVo;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;
import javax.servlet.http.HttpServletResponse;
@RestController
@RequestMapping("/request")
public class ExportController {
@Autowired
private IExportService exportService;
@GetMapping("/export")
public void export(ScheduleVo scheduleVo, HttpServletResponse response) throws Exception {
exportService.exportExcel(scheduleVo, response);
}
}
8. 代码层级(pom及请求同第一章)
9. 生成结果,这里对单元格样式做了简单的优化
这里第二章部分抽象方法抽取就完成了,这里对反射做了抽象,每一种对象需要导出生成excel文档,面对不同的类型,实现抽象方法插入不同的样式,就能得到不同样式的文档。
相比于第一章,代码的灵活度增加,我们只需要对特定的单元格做出对应的改变,思路也清晰。但是,如果有多个不同类型的对象,重复的复制粘贴Service层代码也使得开发效率变慢。这样我们引出第三中方案,全抽象导出。在第三章,我会用三种不同字段的excel模板,导出生成三种不同样式的excel表格。
纯手打,技术有限,请各位大佬批评指正,谢谢!