Java++：POI 导入|导出 工具类（一）
没有十全十美的程序
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
