POI 实现指定列指定行的导出 筛选列导出
- 开始
- 1、导入依赖
- 2、工具类
- 3、路径包
- 4、XML配置文件
- 5、业务层
- 6、关于格式的问题
开始
本篇文章介绍使用POI 实现筛选字段进行导出的操作。
1、导入依赖
<!--***********-->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>4.0.1</version>
</dependency>
<!--处理excel依赖-->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>4.0.1</version>
</dependency>
2、工具类
实际项目中用到的,更改你需要的地方就好了。
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.VerticalAlignment;
import java.io.FileOutputStream;
import java.lang.reflect.Method;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.List;
import java.util.Map;
public class ExportUtil {
/**
* @param title 表格的名字
* @param hearIds 前台选中要导出的字段
* @param mapList 转下拉框之后的map数据集合
* @Param entityList 对应实体
* @Param rootPath 根路径
* @Param exportFilePath 导出excel路径
* @Param type 通过类型区分导出实体
* @Param fieldsMap 字段对应中文
* @throws Exception
*/
public String exportUtilS(String title, String[] hearIds, List<Map<String,String>> mapList,List<?> entityList, String rootPath, String exportFilePath, Integer type, Map<String,String> fieldsMap) throws Exception {
// 创建excel工作薄
HSSFWorkbook workbook = new HSSFWorkbook();
// 创建excel表
HSSFSheet sheet = workbook.createSheet(title);
// 创建标题行
HSSFRow titleRow = sheet.createRow(0);
HSSFCellStyle style = workbook.createCellStyle();//单元格格式
HSSFFont font = workbook.createFont();//字体
font.setFontName("仿宋");//设置字体名称
font.setFontHeightInPoints((short)14);//设置字号
style.setFont(font);//设置字体
style.setWrapText(true);
style.setAlignment(HorizontalAlignment.CENTER);//左右居中
style.setVerticalAlignment(VerticalAlignment.CENTER);//上下居中
titleRow.setHeightInPoints(45);//标题行行高
// 标题行写内容
for (int i = 0; i < hearIds.length; i++) {
String s = hearIds[i];//获取数组中的字段
HSSFCell cell = titleRow.createCell(i);//创建行
HSSFFont font2 = workbook.createFont();//字体
font2.setFontName("IMPACT");//设置字体名称
HSSFCellStyle style2 = workbook.createCellStyle();//单元格格式
font2.setFontHeightInPoints((short)15);//设置字号
font2.setBold(true);//加粗
style2.setFont(font2);
style2.setAlignment(HorizontalAlignment.CENTER);//左右居中
style2.setVerticalAlignment(VerticalAlignment.CENTER);//上下居中
cell.setCellValue(fieldsMap.get(s));//根据数组的字段获得对应的键值
cell.setCellStyle(style2);//渲染样式
sheet.setColumnWidth(i,40*256);//列宽,31个字符
}
// 数据行,从1行开始,mapList.size行
for (int i = 1; i <= mapList.size(); i++) {
// 创建行
HSSFRow listRow = sheet.createRow(i);
listRow.setHeightInPoints(38);//标题行行高
// 数据行单元格,从0开始,共heards.length列
for (int j = 0; j < hearIds.length; j++) {
// 创建数据单元格
HSSFCell listCell = listRow.createCell(j);
listCell.setCellStyle(style);
sheet.setColumnWidth(j,40*256);
// 通过反射的方式,将heards元素通过字符串拼接的方式,拼接出实体类相对应的get方法;
String methodName = "get" + hearIds[j].substring(0, 1).toUpperCase() + hearIds[j].substring(1);
try {
// 通过反射拿到类对象,再获取类对象的额methodName这个方法
switch (type){
case 1://个人用户
Method declaredMethod1 = PersonalUser.class.getDeclaredMethod(methodName, null);
// 通过invoke提交对象,执行declaredMethod这个方法
// 从List<>集合中取出list.get(i - 1)的methodName属性的值;
Object result1 = declaredMethod1.invoke(entityList.get(i - 1));//(list.get(i-1).get(keyList.get(j)));
if(result1!=null){
//设置内容
listCell.setCellValue(mapList.get(i-1).get(hearIds[j]));//(list.get(i-1).get(hearIds[j]));//(list.get(i-1).get(keyList.get(j)).toString());
}else{
listCell.setCellValue(" ");
}
break;
case 2://企业用户
Method declaredMethod2 = EnterpriseUser.class.getDeclaredMethod(methodName, null);
// 通过invoke提交对象,执行declaredMethod这个方法
// 从List<Student>集合中取出list.get(i - 1)的methodName属性的值;
Object result2 = declaredMethod2.invoke(entityList.get(i - 1));
if(result2!=null){
//设置内容
listCell.setCellValue(mapList.get(i-1).get(hearIds[j]));
}else{
listCell.setCellValue(" ");
}
break;
case 3://招聘信息
Method declaredMethod3 = EnterpriseRecruitment.class.getDeclaredMethod(methodName, null);
// 通过invoke提交对象,执行declaredMethod这个方法
// 从List<Student>集合中取出list.get(i - 1)的methodName属性的值;
Object result3 = declaredMethod3.invoke(entityList.get(i - 1));
//判断返回值的类型
if(result3!=null){
//设置内容
listCell.setCellValue(mapList.get(i-1).get(hearIds[j]));
}else{
listCell.setCellValue(" ");
}
break;
case 4://应聘信息
Method declaredMethod4 = RecruitApply.class.getDeclaredMethod(methodName, null);
// 通过invoke提交对象,执行declaredMethod这个方法
// 从List<Student>集合中取出list.get(i - 1)的methodName属性的值;
Object result4 = declaredMethod4.invoke(entityList.get(i - 1));
//判断返回值的类型
if(result4!=null){
//设置内容
listCell.setCellValue(mapList.get(i-1).get(hearIds[j]));
}else{
listCell.setCellValue(" ");
}
break;
case 5://活动报名
Method declaredMethod5 = RegisterInfo.class.getDeclaredMethod(methodName, null);
// 通过invoke提交对象,执行declaredMethod这个方法
// 从List<Student>集合中取出list.get(i - 1)的methodName属性的值;
Object result5 = declaredMethod5.invoke(entityList.get(i - 1));
//判断返回值的类型
if(result5!=null){
//设置内容
listCell.setCellValue(mapList.get(i-1).get(hearIds[j]));
}else{
listCell.setCellValue(" ");
}
break;
}
} catch (NoSuchMethodException e) {
e.printStackTrace();
}
}
}
FileOutputStream outputStream = new FileOutputStream(rootPath + exportFilePath);
//数据通过流的方式填充上表格
workbook.write(outputStream);
//关闭流
outputStream.close();
//关闭表格
workbook.close();
return exportFilePath;
}
/**
* 日期格式字符串转换
* @param dateStr 字符串日期
*/
public String date2TimeStamp(String dateStr){
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
Date date = null;
try {
date = sdf.parse(dateStr);
} catch (Exception e) {
e.printStackTrace();
}
return sdf.format(date);
}
}
3、路径包
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.stereotype.Component;
@Component//声明为组件
@ConfigurationProperties(prefix = "upload-path-helper")//在配置文件中找到upload-path-helper
public class UploadPathHelper {
/**
* 根路径
*/
private String rootPath;
/**
* 导出excel路径
*/
private String exportExcelPath;
public String getRootPath() {
return rootPath;
}
public void setRootPath(String rootPath) {
this.rootPath = rootPath;
}
public String getExportExcelPath() {
return exportExcelPath;
}
public void setExportExcelPath(String exportExcelPath) {
this.exportExcelPath = exportExcelPath;
}
}
4、XML配置文件
upload-path-helper:
rootPath: D:/gangaoprojectFiles/
filePath: filePath/
exportExcelPath: exportExcelPath/
5、业务层
/**
*将对应字段中文用作表头
*/
public Map<String,String> getExcelMaps(){
Map<String,String> fieldsMap = new LinkedHashMap<>();
fieldsMap.put("name","企业名称");
fieldsMap.put("userAdmin","账号");
fieldsMap.put("legalPersonStatus","法人是否港澳人员");
fieldsMap.put("creditCode","信用代码");
fieldsMap.put("legalPerson","法定代表人");
fieldsMap.put("phone","联系电话");
fieldsMap.put("email","企业邮箱");
fieldsMap.put("address","联系地址");
fieldsMap.put("supporterInfoId","入驻的孵化器");
fieldsMap.put("gaInvest","企业有港澳投资");
fieldsMap.put("created","注册时间");
return fieldsMap;
}
/**
* 自选字段导出
* @return
*/
public String exprotS(HttpServletRequest request, String[] hearIds,String ids,EnterpriseUser entity,Date createdSta,Date createdEnd)throws Exception{
ExportUtil util = new ExportUtil();
//传入所有的id
List<EnterpriseUser> entityList = null;
if (!StringUtils.isEmpty(ids)) {
List<String> idList = JSON.parseArray(ids,String.class);//转为JPSN集合
if (idList.size()>0) {
entityList = new ArrayList<>();
for (String id:idList) {
EnterpriseUser entityS = this.getEntityById(id);
if (entityS!=null) {
entityList.add(entityS);
}
}
}
}
if (entityList==null){//如果不传ids,导出全部
Specification<EnterpriseUser> specification = Specification
.where(SpecificationFactory.containsLike("phone", entity.getPhone()))//联系电话
.and(SpecificationFactory.containsLike("name", entity.getName()))//企业名称
.and(SpecificationFactory.equal("issueState", "2"))//有效状态
.and(SpecificationFactory.containsLike("userAdmin", entity.getUserAdmin()))//账号
.and(SpecificationFactory.equal("legalPersonStatus", entity.getLegalPersonStatus()))//法定代表人
.and(SpecificationFactory.containsLike("creditCode", entity.getCreditCode()))//信用代码
.and(SpecificationFactory.containsLike("legalPerson", entity.getLegalPerson()))//法定代表人
.and(SpecificationFactory.containsLike("email", entity.getEmail()))//企业邮箱
.and(SpecificationFactory.containsLike("address", entity.getAddress()))//联系地址
.and(SpecificationFactory.equal("supporterInfoId", entity.getSupporterInfoId()))//选择入驻的孵化器
.and(SpecificationFactory.equal("gaInvest", entity.getGaInvest()))//是否港澳投资
.and(SpecificationFactory.isBetween("created", createdSta, createdEnd));
entityList = this.getRepository().findAll(specification,Sort.by(Sort.Direction.ASC,"created"));
}
//map类型用于填充id下拉
List<Map<String, String>> maps = new ArrayList<Map<String, String>>();
for (EnterpriseUser entityS : entityList) {
Map<String,String> fieldsMap = new HashMap<>();//map
//孵化器
String supporterInfoId = entityS.getSupporterInfoId();
if (!StringUtils.isEmpty(supporterInfoId)) {
supporterInfoId = supporterInfoService.getEntityById(supporterInfoId).getName();
}
//法人是否港澳人员
String legalPersonStatus = null;
if (entityS.getLegalPersonStatus()==null || entityS.getLegalPersonStatus()==0) {
legalPersonStatus = "否";
}else{
legalPersonStatus = "是";
}
//企业有港澳投资
String gaInvest = null;
if (entityS.getGaInvest()==null || entityS.getGaInvest()==0) {
gaInvest = "否";
}else{
gaInvest = "是";
}
String created = util.date2TimeStamp(entityS.getCreated().toString());
fieldsMap.put("name",entityS.getName());
fieldsMap.put("userAdmin",entityS.getUserAdmin());
fieldsMap.put("legalPersonStatus",legalPersonStatus);
fieldsMap.put("creditCode",entityS.getCreditCode());
fieldsMap.put("legalPerson",entityS.getLegalPerson());
fieldsMap.put("phone",entityS.getPhone());
fieldsMap.put("email",entityS.getEmail());
fieldsMap.put("address",entityS.getAddress());
fieldsMap.put("supporterInfoId",supporterInfoId);
fieldsMap.put("gaInvest",gaInvest);
fieldsMap.put("created",created);
maps.add(fieldsMap);
}
//零时文件夹
String excelPath = uploadPathHelper.getTemporaryPath();
//拼接临时文件夹下面的xls文件名称
String exportFilePath = excelPath +"港澳大陆企业信息" + new SimpleDateFormat("yyyyMMddHHmmss").format(new Date()).toString() +".xls";//最终生成文件路径
return Result.succeedResultByMsgAndData("导出成功"
,util.exportUtilS("sheet1", hearIds, maps,entityList,uploadPathHelper.getRootPath(),exportFilePath,2,this.getExcelMaps()));
}
6、关于格式的问题
前端传参格式
hearIds: name,userAdmin,legalPersonStatus,creditCode,legalPerson,phone,email,address,supporterInfoId,gaInvest,created
ids: [“4028827580e050c80180e05c23a20016”]
token: 8C56386A22FD68AC8EA8DF474F874BC73219ED707F3A64A829ADE943
OK 啦 记录到此