没有十全十美的程序
1、封装工具类
package com.osrmt.util;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.usermodel.*;
import java.io.File;
import java.io.FileOutputStream;
import java.io.OutputStream;
import java.text.SimpleDateFormat;
import java.util.*;
/**
* Excel处理工具类
*
*/
public class ExcelUtil {
/**
* 导出excel
* @param headNameList 文件字段头显示名字
* @param headField 文件字段头数据字段
* @param listData 数据集合
* @param stream 流
* @throws Exception 异常
*/
public static void exportExcel(List<String> headNameList,List<String> headField,List<Map<String,String>> listData,OutputStream stream) throws Exception{
try (HSSFWorkbook wb = new HSSFWorkbook()) {
HSSFSheet sheet = wb.createSheet("Sheet1");
HSSFCellStyle titleStyle = wb.createCellStyle(); // 标题样式
titleStyle.setAlignment(HorizontalAlignment.CENTER);
HSSFFont titleFont = wb.createFont(); // 创建字体样式
titleFont.setBold(true); // 字体加粗
titleFont.setFontName("Times New Roman"); // 设置字体类型
titleFont.setFontHeightInPoints((short) 18); // 设置字体大小
titleStyle.setFont(titleFont); // 为标题样式设置字体样式
HSSFCellStyle headerXStyle = wb.createCellStyle();
headerXStyle.setBorderTop(BorderStyle.THIN);
headerXStyle.setBorderBottom(BorderStyle.THIN);
headerXStyle.setBorderLeft(BorderStyle.THIN);
headerXStyle.setBorderRight(BorderStyle.THIN);
HSSFFont headerFont = wb.createFont(); // 创建字体样式
headerFont.setBold(true); // 字体加粗
headerXStyle.setFont(headerFont);
headerXStyle.setAlignment(HorizontalAlignment.CENTER);
//获取表头显示名称
String[] titles = new String[headNameList.size()];
for (int i = 0; i < headNameList.size(); i++) {
titles[i] = headNameList.get(i);
}
int rowInd = 0;
HSSFRow headerRow = sheet.createRow(rowInd);
headerRow.setHeightInPoints(16);
for (int i = 0; i < titles.length; i++) {
HSSFCell cell = headerRow.createCell(i);
cell.setCellValue(titles[i]);
cell.setCellStyle(headerXStyle);
}
HSSFCellStyle headerCStyle = wb.createCellStyle();
headerCStyle.setBorderTop(BorderStyle.THIN);
headerCStyle.setBorderBottom(BorderStyle.THIN);
headerCStyle.setBorderLeft(BorderStyle.THIN);
headerCStyle.setBorderRight(BorderStyle.THIN);
titles = new String[headField.size()];
for (int i = 0; i < headField.size(); i++) {
titles[i] = headField.get(i).toLowerCase();
}
if (listData != null) {
for (Map<String, String> map : listData) {
rowInd++;
HSSFRow _row = sheet.createRow(rowInd);
_row.setHeightInPoints(16);
for (int j = 0; j < titles.length; j++) {
HSSFCell cell = _row.createCell(j);
cell.setCellStyle(headerCStyle);
cell.setCellValue(StringUtil.toNotNullString(map.get(titles[j])));
}
}
}
wb.write(stream);
/*OutputStream out = new FileOutputStream("F:/" + "xls.xlsx");
wb.write(out);*/
}
}
/**
* 检查导入excel字段头格式是否正确
* @param wb 工作簿
* @param colName 列名列表
* @return 是：一致
*/
public static boolean checkFormat(HSSFWorkbook wb,List<String> colName) {
HSSFSheet sheet=wb.getSheetAt(0);
int lastRow = sheet.getLastRowNum();
if (lastRow <1) {
return false;
}
//检查格式，表头是否一致
Row row = sheet.getRow(0);
int cellF = row.getFirstCellNum();
int cellL = row.getLastCellNum();
if (cellF >= 0 && cellL >= colName.size()) {
for(int i=0;i<cellL;i++){
String xlsColString=row.getCell(i).getStringCellValue();
String colString=colName.get(i);
if (!xlsColString.equals(colString)) {
return false;
}
}
return true;
}
return false;
}
/**
* 解析excel数据
* @param wb excel文件对象
* @param headNameList 文件字段头显示名字
* @param headFieldList 文件字段头数据字段
* @return 数据集合
*/
public static List<Map<String,Object>> analysisExcel(HSSFWorkbook wb,List<String> headNameList,List<String> headFieldList) {
HSSFSheet sheet=wb.getSheetAt(0);
int lastRow = sheet.getLastRowNum();
List<Map<String, Object>> list = new ArrayList<>();
for (int i = lastRow; i >= 1; i--) {
Row row = sheet.getRow(i);
if (row == null) {
continue;
}
int firstCell = row.getFirstCellNum();
int lastCell = row.getLastCellNum();
if (firstCell != 0 &&lastCell > headNameList.size()) {
continue;
}
Map<String, Object> map = new HashMap<>();
for (int j = firstCell; j < lastCell; j++) {
Cell cell = row.getCell(j);
if (cell == null) {
continue;
}
CellType style = cell.getCellTypeEnum();
String cellString = "";
if (style == CellType.BOOLEAN) {
cellString = String.valueOf(row.getCell(j).getBooleanCellValue());
} else if (style == CellType.NUMERIC) {
short format = cell.getCellStyle().getDataFormat();
SimpleDateFormat sdf = null;
if (format == 14 || format == 31 || format == 57 || format == 58) {
sdf = new SimpleDateFormat("yyyy-MM-dd");
}else if (format == 20 || format == 32) {
sdf = new SimpleDateFormat("HH:mm");
}else if (format == 21) {
sdf = new SimpleDateFormat("HH:mm:ss");
}
if (sdf != null) {
//日期
double value = cell.getNumericCellValue();
Date date = DateUtil.getJavaDate(value);
cellString = StringUtil.toNotNullString(sdf.format(date));
}else {
String value = StringUtil.toNotNullString(row.getCell(j).getNumericCellValue());
if (value.contains(".")) {
//判断是否是整形
String[] aa = value.split("//.");
if (aa.length == 2 && aa[1].equals("0")) {
cellString = aa[0];
}else {
cellString = String.valueOf(row.getCell(j).getNumericCellValue());
}
}
}
} else {
cellString = row.getCell(j).getStringCellValue();
}
map.put(headFieldList.get(j), cellString);
}
list.add(map);
}
return list;
}
}
2，实体类：
package com.osrmt.entity;
import com.baomidou.mybatisplus.annotation.IdType;
import com.baomidou.mybatisplus.annotation.TableField;
import com.baomidou.mybatisplus.annotation.TableId;
import com.baomidou.mybatisplus.annotation.TableName;
import io.swagger.annotations.ApiModelProperty;
import lombok.Data;
@Data
@TableName(value = "xq_nenglishuxing")
public class XqNengliShuxing {
@TableId(type= IdType.INPUT)
@ApiModelProperty("主键id")
private Long id;
@TableField(value="code")
@ApiModelProperty("目标编号")
private String code;
@TableField(value="description")
@ApiModelProperty("能力属性描述信息")
private String description;
@TableField(value="priority")
@ApiModelProperty("优先级(0低 1中 2高)")
private Integer priority;
@TableField(value="is_disable")
@ApiModelProperty("是否禁用(0启用 1禁用)")
private Integer isDisable;
@TableField(value="creator")
@ApiModelProperty("创建人")
private String creator;
@TableField(value = "createtime")
@ApiModelProperty("创建时间")
private Long createTime;
@TableField(value="nlqd_id")
@ApiModelProperty("所属能力清单的id")
private Long nlqdId;
@TableField(value = "nengli_mubiao_value")
@ApiModelProperty("能力目标值")
private String nengliMubiaoValue;
}
3，接口实现
@GetMapping("/exportExcel")
@ApiOperation("导出能力清单能力属性excel")
public Response exportExcel(@RequestParam("nlqdId") Long nlqdId,
HttpServletResponse response) throws Exception {
QueryWrapper<XqNengliShuxing> queryWrapper = new QueryWrapper<>();
queryWrapper.lambda().eq(XqNengliShuxing::getNlqdId, nlqdId);
List<XqNengliShuxing> list = nengliShuxingService.list(queryWrapper);
List<Map<String, String>> data = new ArrayList<>();
for (int i = 0; i < list.size(); i++) {
Map<String, String> map = JSON.parseObject(JSON.toJSONString(list.get(i)), Map.class);
data.add(map);
}
Properties properties = getApiModelProperty("com.osrmt.entity.XqNengliShuxing");
List<String> headNameList = properties.values().stream().map(String::valueOf).collect(Collectors.toList());
List<String> headFieldList = properties.keySet().stream().map(String::valueOf).collect(Collectors.toList());
System.out.println(properties);
try {
ExcelUtil.exportExcel(headNameList, headFieldList, data, response.getOutputStream());
} catch (Exception e) {
e.printStackTrace();
throw new Exception("导出excel失败");
}
return Response.success();
}
/** 功能描述:
* 获取类字段ApiModelProperty注解value值（中文）
* @param classPath: 类路径
* @author: zl
* @date: 2022/2/17 17:10
*/
private Properties getApiModelProperty(String classPath){
Properties p = new Properties();
try {
// 1.根据类路径获取类
Class<?> c = Class.forName(classPath);
// 2.获取类的属性
Field[] declaredFields = c.getDeclaredFields();
// 3.遍历属性，获取属性上ApiModelProperty的值，属性的名，存入Properties
if (declaredFields.length != 0) {
for (Field field : declaredFields) {
if (field.getAnnotation(ApiModelProperty.class) != null) {
// key和value可根据需求存
// 这存的key为类属性名，value为注解的值
p.put(field.getName(), field.getAnnotation(ApiModelProperty.class).value());
}
}
return p;
}
} catch (ClassNotFoundException e) {
e.printStackTrace();
}
return p;
}
@PostMapping("/importExcel")
@ApiOperation("导入能力清单能力属性excel")
public Response importExcel(MultipartFile file, Long nlqdId) {
Properties properties = getApiModelProperty("com.osrmt.entity.XqNengliShuxing");
List<String> headNameList = properties.values().stream().map(String::valueOf).collect(Collectors.toList());
List<String> headFieldList = properties.keySet().stream().map(String::valueOf).collect(Collectors.toList());
try {
HSSFWorkbook workbook =new HSSFWorkbook(new POIFSFileSystem(file.getInputStream()));
List<Map<String, Object>> maps = ExcelUtil.analysisExcel(workbook, headNameList, headFieldList);
for (int i = 0; i < maps.size(); i++) {
XqNengliShuxing xqNengliShuxing = JSON.parseObject(JSON.toJSONString(maps.get(i)), XqNengliShuxing.class);
NengliShuxingProperty nengliShuxingProperty = new NengliShuxingProperty();
BeanUtils.copyProperties(xqNengliShuxing, nengliShuxingProperty);
nengliShuxingProperty.setNlqdId(nlqdId);
addNengliShuxing(nengliShuxingProperty);
}
} catch (IOException e) {
e.printStackTrace();
}
return Response.success();
}
4，导出样例：
感谢楼主的封装
引文：https://www.cnblogs.com/zhulei2/p/15907855.html
原创文章，作者：ItWorker，如若转载，请注明出处：https://blog.ytso.com/282321.html