1.导出工具
模板名称、路径
1.1.ExportDate.java
import com.shinkeer.card.annotation.BeanMethodAnnotation;
import org.apache.poi.ss.usermodel.*;
import org.springframework.core.io.ClassPathResource;
import org.springframework.stereotype.Component;
import javax.servlet.http.HttpServletResponse;
import java.io.InputStream;
import java.io.Serializable;
import java.lang.reflect.Method;
import java.math.BigDecimal;
import java.util.*;
/**
* @Description 导出Excel数据
* @Date 2022/11/8
*/
@Component
public class ExportDate implements Serializable {
private static final long serialVersionUID = -5835412731350945326L;
/**
* @param response HttpServletResponse
* @param dataList 数据源<数据源对象.class>
* @param filePath excel模板地址
* @param fileName 文件名
* @param start 起始行
* @param resultMap 特有逻辑
* @Description: 表格工具方法
*/
public void exportExcel(HttpServletResponse response, Map<String, String> resultMap, List<?> dataList, String filePath, String fileName, int start, Class<?> clazz) {
try {
//1.加载下载模板
ClassPathResource cpr = new ClassPathResource(filePath);
InputStream is = cpr.getInputStream();
Workbook workbook = WorkbookFactory.create(is);//加载工作簿
Sheet sheet0 = workbook.getSheetAt(0);
//2.获取数据源对象方法
Method[] methods = clazz.getDeclaredMethods();
List<Method> methodList = getOrderedMethod(methods);
/**
* 特有逻辑解决方案:加入参MAP<坐标(x,y),参数>
*/
if (resultMap != null) {
int row01 = 0;
Row row = null;
for (String key : resultMap.keySet()) {
String[] split = key.split(",");
if (row01 != Integer.parseInt(split[0])) {
row01 = Integer.parseInt(split[0]);
row = sheet0.createRow(row01);
}
if (row != null) {
row.createCell(Integer.parseInt(split[1])).setCellValue(Double.parseDouble(resultMap.get(key)));
}
}
}
//3.组装数据到模板
int i = 0;//序号
for (Object data : dataList) {
Row row = sheet0.createRow(start + i++);
int j = 0;//excel模板起始列
for (Method method : methodList) {
Object invoke = method.invoke(data, null);//必须是null，是object就报错
Class<?> returnType = method.getReturnType();
Object value;
if (returnType.equals(BigDecimal.class) || returnType.equals(Integer.class) || returnType.equals(Double.class)){
value = invoke == null ? 0 : invoke ;
row.createCell(j++).setCellValue(Double.parseDouble(value.toString()));
}else {
value = invoke;
row.createCell(j++).setCellValue(value == null ? null : value.toString());
}
}
}
//3.下载excel文件
ImportAndExportUtil.downLoadExcel(fileName, response, workbook);
} catch (Exception e) {
e.printStackTrace();
}
}
/**
* @Description: 方法排序
* @Param: [methods]
* @Return: java.util.List<java.lang.reflect.Method>
* @Date: 2022/9/22
*/
private static List<Method> getOrderedMethod(Method[] methods) {
// 用来存放所有的方法域
List<Method> methodList = new ArrayList<>();
// 过滤带有注解的方法
for (Method m : methods) {
if (m.getAnnotation(BeanMethodAnnotation.class) != null) {
methodList.add(m);
}
}
// 这个比较排序的语法依赖于java 1.8
methodList.sort(Comparator.comparingInt(
m -> m.getAnnotation(BeanMethodAnnotation.class).order()));
return methodList;
}
/**
* @Description:表格工具方法
* @param response HttpServletResponse
* @param dataList 数据源<数据源对象.class>
* @param filePath excel模板地址
* @param fileName 文件名
* @param start 起始行
* @param resultMap 特有逻辑
* @Return: void
* @Date: 2022/9/22
* 是否第一列带序号
*/
public static boolean exportExcel(HttpServletResponse response,Map<String,String> resultMap, List<?> dataList, String filePath, String fileName, int start,Class<?> clazz,boolean serialNo) {
try {
//1.加载下载模板
ClassPathResource cpr = new ClassPathResource(filePath);
InputStream is = cpr.getInputStream();
Workbook workbook = WorkbookFactory.create(is);//加载工作簿
Sheet sheet0 = workbook.getSheetAt(0);
//2.获取数据源对象方法
Method[] methods = clazz.getDeclaredMethods();
List<Method> methodList = getOrderedMethod(methods);
/**
* 特有逻辑解决方案:加入参MAP<坐标,参数>
*/
if (resultMap != null){
int row01 = 0;
Row row = null;
for (String key : resultMap.keySet()){
String[] split = key.split(",");
if (row01 != Integer.parseInt(split[0])){
row01 = Integer.parseInt(split[0]);
row = sheet0.createRow(row01);
}
if (row != null)
row.createCell(Integer.parseInt(split[1])).setCellValue(Double.parseDouble(resultMap.get(key)));
}
}
//3.组装数据到模板
int i = 0;//序号
for (Object data : dataList) {
Row row = sheet0.createRow(start + i++);
int j = 0;//excel模板起始列
//是否需要序号
if (serialNo){
row.createCell(j).setCellValue(i);
}
for (Method method : methodList){
Object invoke = method.invoke(data, null);
Class<?> returnType = method.getReturnType();
Object value;
if (returnType.equals(BigDecimal.class) || returnType.equals(Integer.class) || returnType.equals(Double.class)){
value = invoke == null ? 0.00 : invoke ;
row.createCell(serialNo ? ++j : j++).setCellValue(new BigDecimal(value.toString()).setScale(2,BigDecimal.ROUND_HALF_UP).doubleValue());
}else {
value = invoke;
row.createCell(serialNo ? ++j : j++).setCellValue(value == null ? null : value.toString());
}
}
}
//3.下载excel文件
ImportAndExportUtil.downLoadExcel(fileName, response, workbook);
return true;
} catch (Exception e) {
e.printStackTrace();
return false;
}
}
}1.2.BeanMethodAnnotation.java
自定义注解
import java.lang.annotation.ElementType;
import java.lang.annotation.Retention;
import java.lang.annotation.RetentionPolicy;
import java.lang.annotation.Target;
@Target(ElementType.METHOD)
@Retention(RetentionPolicy.RUNTIME)
public @interface BeanMethodAnnotation {
/**
* 标注该属性的顺序
* @return 该属性的顺序
*/
int order();
}
1.3.ImportAndExportUtil.java
导入导出封装类(模板文件放在resouces)
import com.shinkeer.core.utils.TimeUtil;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.ss.usermodel.Workbook;
import org.springframework.core.io.ClassPathResource;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.net.URLEncoder;
import java.nio.charset.StandardCharsets;
import java.text.DecimalFormat;
/**
* @Date 2021/12/4 18:29
* @Description 导入导出封装类
*/
public class ImportAndExportUtil {
/**
* 导出头部设置
*/
public static final String CONTENT_TYPE = "content-Type";
/**
* 导出头部设置
* .xls 结尾的文件用 application/vnd.ms-excel
* .xlsx 结尾的文件用 application/vnd.openxmlformats-officedocument.spreadsheetml.sheet
*/
// public static final String APPLICATION_EXECL = "application/vnd.ms-excel";
public static final String APPLICATION_EXECL = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
/**
* 导出头部设置
*/
public static final String CONTENT_DISPOSITION = "Content-Disposition";
/**
* 导出头部设置
*/
public static final String ATTACHMENT_FILENAME = "attachment;filename=\"";
/**
* 导出头部设置
*/
public static final String END_STR = "\"";
/**
* 字符编码UTF-8
*/
public static final String CODE_UTF8 = "UTF-8";
/**
* 下载Excel文件
*
* @param fileName 文件名
* @param response response
* @param workbook 工作簿
*/
public static void downLoadExcel(String fileName, HttpServletResponse response, Workbook workbook) throws IOException {
response.setCharacterEncoding(CODE_UTF8);
response.setHeader(CONTENT_TYPE, APPLICATION_EXECL);
response.setHeader(CONTENT_DISPOSITION,
ATTACHMENT_FILENAME + URLEncoder.encode(fileName, CODE_UTF8) + END_STR);
OutputStream output = response.getOutputStream();
workbook.write(output);
output.close();
workbook.close();
}
/**
* @Description: 下载文件
* @Date: 2022/2/8
* @Param [request, response, filePath:"文件路径 "/businessMapper/downloadImportTemplate.xlsx";", fileName:"文件全部名称 "导入模板.xlsx";"]
* @Return: void
**/
public static void downloadTemplate(HttpServletRequest request, HttpServletResponse response, String filePath, String fileName) throws IOException {
ClassPathResource resource = new ClassPathResource(filePath);
//0.文件存在才下载
if (resource.exists()) {
OutputStream out = null;
InputStream in = null;
try {
//1.读取要下载的内容
in = resource.getInputStream();
//2.告诉浏览器下载的方式以及一些设置
//解决文件名乱码问题，获取浏览器类型，转换对应文件名编码格式，IE要求文件名必须是utf-8, firefox要求是iso-8859-1编码
String agent = request.getHeader("user-agent");
if (agent.contains("FireFox")) {
fileName = new String(fileName.getBytes(StandardCharsets.UTF_8), StandardCharsets.ISO_8859_1);
} else {
fileName = URLEncoder.encode(fileName, "UTF-8");
}
//3.设置下载文件的mineType，告诉浏览器下载文件类型
String mineType = request.getServletContext().getMimeType(fileName);
response.setContentType(mineType);
//4.设置一个响应头，无论是否被浏览器解析，都下载
response.setHeader("Content-disposition", "attachment; filename=" + fileName);
//5.将要下载的文件内容通过输出流写到浏览器
out = response.getOutputStream();
int len = 0;
byte[] buffer = new byte[1024];
while ((len = in.read(buffer)) > 0) {
out.write(buffer, 0, len);
}
} catch (IOException e) {
e.printStackTrace();
} finally {
if (out != null) {
out.close();
}
if (in != null) {
in.close();
}
}
}
}
/**
* 获取到对应的Cell的值
*
* @param cell 单元格
* @return 单元格信息
*/
public static String getCellValueByCell(Cell cell) {
if (cell != null && !"".equals(cell.toString().trim())) {
String cellValue = "";
CellType cellType = cell.getCellTypeEnum();
switch (cellType) {
case NUMERIC:
if (HSSFDateUtil.isCellDateFormatted(cell)) {
cellValue = TimeUtil.getDateStrbyFormat(cell.getDateCellValue(), "yyyy-MM-dd HH:mm:ss");
} else {
cellValue = (new DecimalFormat("#.######")).format(cell.getNumericCellValue());
}
break;
case STRING:
cellValue = cell.getStringCellValue().trim();
cellValue = StringUtils.isEmpty(cellValue) ? "" : cellValue;
break;
case FORMULA:
case BLANK:
default:
cellValue = "";
break;
case BOOLEAN:
cellValue = String.valueOf(cell.getBooleanCellValue());
}
return cellValue;
} else {
return "";
}
}
}
具体使用：
2.导入工具
示例：
/**
* @Description: 号导入接口
* @Param [file, response, request]
* @Return: com.shinkeer.core.common.Result<?>
**/
@Transactional(rollbackFor = Exception.class)
@Override
public Result<?> importSegmentNumber(Part file, HttpServletResponse response, HttpServletRequest request, String segmentNumberImportPath, String SegmentNumberImportName) {
//获取所有的省
QueryWrapper<SRegionalInformation> queryWrapper = new QueryWrapper<>();
queryWrapper.lambda().eq(SRegionalInformation::getRegionalType, STR_P);//地区类型（P:省,C：市,A：区)
List<SRegionalInformation> provinceList = sRegionalInformationMapper.selectList(queryWrapper);
//获取所有的市
QueryWrapper<SRegionalInformation> queryWrapper2 = new QueryWrapper<>();
queryWrapper2.lambda().eq(SRegionalInformation::getRegionalType, STR_C);//地区类型（P:省,C：市,A：区)
List<SRegionalInformation> cityList = sRegionalInformationMapper.selectList(queryWrapper2);
//时间 更新人
Date date = new Date();
String login = userUtils.getUserId();
//导入异常数据信息map
HashMap<Object, Object> errorInfo = new HashMap<>();
//准备插入的数据
List<CtSegmentNumberInfo> ctSegmentNumberInfoList = new ArrayList<>();
try {
//1.获取上传工作簿内容数据
//导入异常数据信息List
List<Object> errorRowList = new ArrayList<>();
//加载导出模板数据
ClassPathResource cpr = new ClassPathResource(segmentNumberImportPath);
InputStream is = cpr.getInputStream();
//创建异常工作簿
Workbook errorWorkbook = WorkbookFactory.create(is);
Sheet errorSheetAt = errorWorkbook.getSheetAt(0);
int errorRowNum = errorSheetAt.getLastRowNum();
for (int i = 1; i <= errorRowNum; i++) {
Row row = errorSheetAt.getRow(i);
errorSheetAt.removeRow(row);
}
//读取上传工作簿
Workbook workbook = new XSSFWorkbook(file.getInputStream());
Sheet sheetAt = workbook.getSheetAt(0);
int lastRowNum = sheetAt.getLastRowNum();
//判断首行是否为空
Row sheetAtTitle = sheetAt.getRow(0);
if (sheetAtTitle == null) {
return Result.Error(ReturnCode.THE_FIRST_ROW_TITLE_BAR_IS_EMPTY);
}
//获取模板首行所有标题
Row templateRow = errorSheetAt.getRow(0);
short lastCellNum = sheetAtTitle.getLastCellNum();
StringBuilder titleNameStr = new StringBuilder();
for (int i = 0; i < lastCellNum; i++) {
Cell titleCell = templateRow.getCell(i);
String titleName = StringUtils.deleteWhitespace(ImportAndExportUtil.getCellValueByCell(titleCell));
titleNameStr.append(titleName);
}
//获取导入模板首行所有标题
Row importRow = sheetAt.getRow(0);
short importNum = importRow.getLastCellNum();
StringBuilder importTitleName = new StringBuilder();
for (int i = 0; i < importNum; i++) {
Cell titleCell = sheetAtTitle.getCell(i);
String titleName = StringUtils.deleteWhitespace(ImportAndExportUtil.getCellValueByCell(titleCell));
importTitleName.append(titleName);
}
//判断导入模板和模板是否一致
if (!importTitleName.toString().equals(titleNameStr.toString())) {
return Result.Error(ReturnCode.IMPORT_TEMPLATE_ERROR);
}
//第一行，尾部加上错误信息
saveErrorTitle(templateRow, ERROR_MSG_TITLE);
//导出模板起始值
int j = 1;
//2.解析工作簿内容
for (int i = 1; i <= lastRowNum; i++) {
//错误信息
String errorMsg = "";
//map提示
HashMap<Object, Object> errorMap = new HashMap<>();
//导入失败行号
int failureRow = i + 1;
Row row = sheetAt.getRow(i);
Row errorRow = errorSheetAt.createRow(j);
//错误信息标志
boolean errorFlag = false;
if (row == null) {
saveErrorMap(errorRowList, errorMap, failureRow, DATA_IS_NULL);
}
//起始列
int column = 0;
/**
* ***********[号码段]**************
*/
String segmentNumber = null;
Cell cell1 = row.getCell(column++);
if (cell1 != null) {
segmentNumber = StringUtils.deleteWhitespace(ImportAndExportUtil.getCellValueByCell(cell1));
}
//判断是否为空
if (StringUtils.isBlank(segmentNumber)) {
errorFlag = true;
errorMsg = DATA_IS_NULL;
saveErrorMap(errorRowList, errorMap, failureRow, errorMsg);
} else {
//判断是否为纯数字
if (!StringUtils.isNumeric(segmentNumber)) {
errorFlag = true;
errorMsg = IT_S_NOT_PURE_NUMBERS;
saveErrorMap(errorRowList, errorMap, failureRow, errorMsg);
}
//是否重复
if (judgeSegmentNumberRepetition(segmentNumber)) {
errorFlag = true;
errorMsg = NUMBER_SEGMENT_REPETITION;
saveErrorMap(errorRowList, errorMap, failureRow, errorMsg);
}
//最长十位数
if (segmentNumber.length() != 7) {
errorFlag = true;
errorMsg = NUMBER_MORE_THAN_7;
saveErrorMap(errorRowList, errorMap, failureRow, errorMsg);
}
}
/**
* ***********[运营商信息（中国移动、中国电信、中国联通）]**************
*/
String operator = null;
Cell cell2 = row.getCell(column++);
if (cell2 != null) {
operator = StringUtils.deleteWhitespace(ImportAndExportUtil.getCellValueByCell(cell2));
}
//判断是否为空
if (StringUtils.isBlank(operator)) {
errorFlag = true;
errorMsg = NUMBER_MORE_THAN_10;
saveErrorMap(errorRowList, errorMap, failureRow, errorMsg);
} else {
//判断是否是运行商
if (StringUtils.isNotBlank(operator)) {
if (!(operator.equals(CHINA_MOBILE) || operator.equals(CHINA_TELECOM) || operator.equals(CHINA_UNICOM))) {
errorFlag = true;
errorMsg = OPERATOR_ERROR;
saveErrorMap(errorRowList, errorMap, failureRow, errorMsg);
}
}
}
/**
* ***********[省]**************
*/
String province = null;
Cell cell3 = row.getCell(column++);
if (cell3 != null) {
province = StringUtils.deleteWhitespace(ImportAndExportUtil.getCellValueByCell(cell3));
}
//判断是否为空
if (StringUtils.isBlank(province)) {
errorFlag = true;
errorMsg = DATA_IS_NULL;
saveErrorMap(errorRowList, errorMap, failureRow, errorMsg);
} else {
boolean judgeProvince = provinceCityAreaUtils.judgeProvinceTrue(provinceList, province);
//判断省信息
if (!judgeProvince) {
errorFlag = true;
errorMsg = PROVINCE_INFORMATION_ERROR;
saveErrorMap(errorRowList, errorMap, failureRow, errorMsg);
}
}
/**
* ***********[市]**************
*/
String city = null;
Cell cell4 = row.getCell(column++);
JudgeProvinceCityVO judgeProvinceCityVO = new JudgeProvinceCityVO();
if (cell4 != null) {
city = StringUtils.deleteWhitespace(ImportAndExportUtil.getCellValueByCell(cell4));
}
//判断是否为空
if (StringUtils.isBlank(operator)) {
errorFlag = true;
errorMsg = DATA_IS_NULL;
saveErrorMap(errorRowList, errorMap, failureRow, errorMsg);
} else {
//判断省市联动并判断信息
judgeProvinceCityVO = provinceCityAreaUtils.judgeProvinceAndCityTrue(provinceList, cityList, province, city);
Boolean flag = judgeProvinceCityVO.getFlag();
if (!flag) {
errorFlag = true;
errorMsg = PROVINCIAL_CITY_INFORMATION_ERROR;
saveErrorMap(errorRowList, errorMap, failureRow, errorMsg);
}
}
//返回异常数据到工作簿
if (errorFlag) {
j++;
//保存到错误row
saveErrorRow(errorRow, segmentNumber, operator, province, city, errorMsg);
} else {
//3.根据工作簿内容,保存正常无误数据
CtSegmentNumberInfo ctSegmentNumberInfo = new CtSegmentNumberInfo();
ctSegmentNumberInfo.setId(UuidUtils.generateId());
ctSegmentNumberInfo.setSegmentNumber(segmentNumber);
SRegionalInformation province1 = judgeProvinceCityVO.getProvince();
String provinceName = province1.getRegionalName();
ctSegmentNumberInfo.setProvince(provinceName);
SRegionalInformation city1 = judgeProvinceCityVO.getCity();
String cityName = city1.getRegionalName();
ctSegmentNumberInfo.setCity(cityName);
ctSegmentNumberInfo.setOperator(operator);
ctSegmentNumberInfo.setCreateUser(login);
ctSegmentNumberInfo.setCreateTime(date);
ctSegmentNumberInfo.setUpdateUser(login);
ctSegmentNumberInfo.setUpdateTime(date);
ctSegmentNumberInfo.setIsValid(CommonConstant.VALID);
//2.拼装
ctSegmentNumberInfoList.add(ctSegmentNumberInfo);
}
}
//2.向数据库插入数据
processInsert(ctSegmentNumberInfoList);
//异常数据提示
errorInfo.put(IMPORT_FAILED_DATA, errorRowList);
//4.如有错误数据,返回错误数据到工作簿,组装excel数据,下载excel文件
if (errorRowList.size() > 0) {
ImportAndExportUtil.downLoadExcel(SegmentNumberImportName, response, errorWorkbook);
log.error(errorInfo.toString());
return Result.Error(ReturnCode.IMPORT_FAILED, errorInfo);
}
} catch (
IOException e) {
e.printStackTrace();
log.error(errorInfo.toString());
return Result.Error(ReturnCode.IMPORT_FAILED, errorInfo);
}
//5.组装数据并返回
return Result.OK();
}版权声明：本文内容由互联网用户自发贡献，该文观点仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容， 请发送邮件至 举报，一经查实，本站将立刻删除。
文章由极客之音整理，本文链接：https://www.bmabk.com/index.php/post/192772.html
