/**
* @Author 955
* @Date 2023-10-10 11:52
* @Description 错误批注信息对象
*/
@Data
public class ErrorTypeNewVo implements Serializable {
public ErrorTypeNewVo(String name, String message) {
this.name = name;
this.errors = message;
}
public ErrorTypeNewVo(String message, String name, int rownum, int cellnum) {
this.name = name;
this.errors = message;
this.lineNum = rownum;
this.cellnum = cellnum;
}
private String name;
private String errors;
private int lineNum;
private int cellnum;
}
/**
* 取得cell内的值
*
* @param cell
* @return
*/
public static String getStrByCell(Cell cell) {
if (cell == null)
return null;
String temp = null;
switch (cell.getCellType()) {
case BLANK:
case STRING:
temp = cell.getStringCellValue();
break;
case FORMULA:
try {
temp = cell.getStringCellValue();
} catch (IllegalStateException e) {
temp = cell.getNumericCellValue() + "";
}
break;
case NUMERIC:
boolean remar = false;
try {
remar = DateUtil.isCellDateFormatted(cell);
} catch (Exception e) {
// TODO: handle exception
}
if (remar) {
double d = cell.getNumericCellValue();
Date date = DateUtil.getJavaDate(d);
SimpleDateFormat a = new SimpleDateFormat("yyyy-MM-dd");
temp = a.format(date);
} else {
temp = String.valueOf(cell.getNumericCellValue());
}
break;
default:
temp = cell.getStringCellValue();
break;
}
if (temp.trim().equals(""))
temp = null;
else
temp = temp.trim();
return temp;
}
/**
* 设置单元格样式
*
* @param workbook
* @param cell
* @param col
* @param fg
*/
public void setStyle(XSSFWorkbook workbook, Cell cell, String col, XSSFColor fg) {
XSSFCellStyle style = workbook.createCellStyle();
style.setFillForegroundColor(fg);
// style.setFillPattern(CellStyle.SOLID_FOREGROUND);
cell.setCellStyle(style);
cell.setCellValue(col);
}
/**
* 表头样式
*
* @return
*/
public static WriteCellStyle getHeadWriteCellStyle() {
WriteCellStyle headWriteCellStyle = new WriteCellStyle();
headWriteCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
headWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);
headWriteCellStyle.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.index);
headWriteCellStyle.setBottomBorderColor(IndexedColors.BLACK.index);
headWriteCellStyle.setLeftBorderColor(IndexedColors.BLACK.index);
headWriteCellStyle.setRightBorderColor(IndexedColors.BLACK.index);
headWriteCellStyle.setTopBorderColor(IndexedColors.BLACK.index);
return headWriteCellStyle;
}
/**
* 内容样式
*
* @return
*/
public static WriteCellStyle getContentWriteCellStyle() {
WriteCellStyle contentWriteCellStyle = new WriteCellStyle();
contentWriteCellStyle.setWrapped(true);
// contentWriteCellStyle.setFillForegroundColor(IndexedColors.LEMON_CHIFFON.index);
// contentWriteCellStyle.setFillPatternType(FillPatternType.SOLID_FOREGROUND);
// contentWriteCellStyle.setWriteFont();
contentWriteCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
contentWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);
return contentWriteCellStyle;
}
/**
* 设置表头数据
*
* @return
*/
private List<List<String>> head(List<String> titleName) {
List<List<String>> list = new ArrayList<List<String>>();
if (CollUtil.isNotEmpty(titleName)) {
for (String s : titleName) {
List<String> head = new ArrayList<String>();
head.add(s);
list.add(head);
}
}
return list;
}
/**
* @content 得到不同的时间类型
* @author liuxd
* @time 2011/07/24 22:00
*/
public static String getNow(int type) {
SimpleDateFormat sdf = null;
switch (type) {
case 0:
sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss.SSS");
break;
case 1:
sdf = new SimpleDateFormat("yyyyMMdd");
break;
case 2:
sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
break;
case 3:
sdf = new SimpleDateFormat("yyyyMM");
break;
case 4:
sdf = new SimpleDateFormat("yyyy年MM月dd日");
break;
case 5:
sdf = new SimpleDateFormat("yyyy-MM-dd");
break;
case 6:
sdf = new SimpleDateFormat("yyyyMMddHHmmssSSS"); //取到毫秒
break;
case 7:
sdf = new SimpleDateFormat("yyyyMMddHHmmss"); //取到秒
break;
default:
sdf = new SimpleDateFormat("yyyy-M-D");
break;
}
return sdf.format(new Date());
}
import cn.hutool.core.collection.CollUtil;
import cn.hutool.core.util.ObjectUtil;
import cn.hutool.core.util.StrUtil;
import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.ExcelWriter;
import com.alibaba.excel.metadata.Head;
import com.alibaba.excel.support.ExcelTypeEnum;
import com.alibaba.excel.write.builder.ExcelWriterBuilder;
import com.alibaba.excel.write.metadata.WriteSheet;
import com.alibaba.excel.write.metadata.style.WriteCellStyle;
import com.alibaba.excel.write.style.HorizontalCellStyleStrategy;
import com.alibaba.excel.write.style.column.AbstractHeadColumnWidthStyleStrategy;
import lombok.AllArgsConstructor;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.*;
import org.springframework.stereotype.Service;
import java.io.*;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
private static int index = 500000;
/**
*
* @param titleName sheet名称
* @param tittleNames 标题集合
* @param vos 内容详情集合
* @param errorListVo 错误批注集合
* @return
*/
public String newCreateExcelTemple_Object(String titleName, List<String> tittleNames, List<List<Object>> vos, List<ErrorTypeNewVo> errorListVo) {
String fileName = OrgTools.getNow(6) + ".xlsx";
//本地文件名随意取
String locaPath = path + fileName;
OutputStream out = null;
try {
out = new FileOutputStream(locaPath);
} catch (FileNotFoundException e) {
e.printStackTrace();
}
try {
// 设置数据集合
List<List<String>> dataList = new ArrayList<List<String>>();
if (CollUtil.isNotEmpty(vos)) {
for (List<Object> objects : vos) {
List<String> strs = new ArrayList<>();
for (Object obj : objects) {
String str = null;
if (obj != null) {
if (obj instanceof Date) {
SimpleDateFormat ff = new SimpleDateFormat("yyyy-MM-dd");
str = ff.format(obj);
} else {
str = obj.toString();
}
}
strs.add(str);
}
dataList.add(strs);
}
ExcelWriterBuilder writerBuilder = EasyExcel.write();
writerBuilder.file(out);
writerBuilder.excelType(ExcelTypeEnum.XLSX);
writerBuilder.head(head(tittleNames));
ExcelWriter excelWriter = writerBuilder.build();
// 写第一个sheet, sheet1 数据全是List 无模型映射关系
long total = dataList.size();
long count = total / 500000;
if (total % 500000 > 0) {
count++;
}
WriteCellStyle headWriteCellStyle = getHeadWriteCellStyle();
WriteCellStyle contentWriteCellStyle = getContentWriteCellStyle();
//这个策略是 头是头的样式 内容是内容的样式
HorizontalCellStyleStrategy horizontalCellStyleStrategy =
new HorizontalCellStyleStrategy(headWriteCellStyle, contentWriteCellStyle);
for (int i = 0; i < count; i++) {
List<List<String>> temp = new ArrayList<List<String>>();
if (i == count - 1) {
temp = dataList.subList(i * index, dataList.size());
} else {
temp = dataList.subList(i * index, (i + 1) * index);
}
WriteSheet sheet1 = EasyExcel.writerSheet(i, titleName + i).registerWriteHandler(horizontalCellStyleStrategy).registerWriteHandler(new AbstractHeadColumnWidthStyleStrategy() {
@Override
protected Integer columnWidth(Head head, Integer columnIndex) {
if (head == null) {
return 17;
}
// 简单根据列头名称长度进行调整列的宽度
// 当遍历完所有指定的head列头后
// 第一行数据的第一列也会进入到这个逻辑， 此时head 为 null
return head.getHeadNameList().get(0).length() * 10;
}
}).build();
excelWriter.write(temp, sheet1);
}
excelWriter.finish();
}
} catch (Exception e) {
e.printStackTrace();
} finally {
try {
if (out != null) {
out.close();
}
} catch (IOException e) {
e.printStackTrace();
}
}
InputStream inputStream = null;
try {
inputStream = new FileInputStream(locaPath);
XSSFWorkbook wb = new XSSFWorkbook(inputStream);
XSSFSheet sheet = wb.getSheetAt(0);
for (ErrorTypeNewVo errorTypevo : errorListVo) {
Row row = sheet.getRow(errorTypevo.getLineNum());
Cell cell = row.getCell(errorTypevo.getCellnum());
if (cell == null) {
cell = row.getCell(errorTypevo.getCellnum() - 1);
}
String name = errorTypevo.getName();
String messageAll = errorTypevo.getErrors();
String message = "";
if (StrUtil.isNotEmpty(name)) {
message += "错误类型：" + name;
}
if (StrUtil.isNotEmpty(messageAll)) {
message += ",详细错误：" + messageAll;
}
String t1 = "";
try {
t1 = getStrByCell(cell);
} catch (Exception e) {
e.printStackTrace();
}
XSSFColor color = new XSSFColor(java.awt.Color.red);
if (t1 != null) {
setStyle(wb, cell, t1, color);
} else {
t1 = "";
if (StrUtil.isNotEmpty(name)) {
t1 += "错误类型：" + name;
}
if (StrUtil.isNotEmpty(messageAll)) {
t1 += ",详细错误：" + messageAll;
}
setStyle(wb, cell, t1, color);
}
if (cell != null) {
//设置批注:能解决Multiple cell comments in one cell are not allowed, cell: xx问题
Comment mainComment = cell.getCellComment();
if (ObjectUtil.isEmpty(mainComment)) {
mainComment = sheet.createDrawingPatriarch().createCellComment(
new XSSFClientAnchor(0, 0, 0, 0, cell.getColumnIndex(), cell.getRowIndex(), cell.getColumnIndex() + 2, cell.getRowIndex() + 3));
mainComment.setString(new XSSFRichTextString(message));
cell.setCellComment(mainComment);
sheet.autoSizeColumn(cell.getColumnIndex());
}
}
}
// 临时缓冲区
ByteArrayOutputStream out2 = new ByteArrayOutputStream();
// 创建临时文件
wb.write(out2);
byte[] bookByteAry = out2.toByteArray();
inputStream = new ByteArrayInputStream(bookByteAry);
try {
//只需替换这里的系统文件导出方法即可
fileTemplate.putObject("桶名", fileName, inputStream);
} catch (Exception e) {
e.printStackTrace();
}
} catch (FileNotFoundException e1) {
e1.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
} finally {
if (inputStream != null) {
try {
inputStream.close();
} catch (IOException e) {
e.printStackTrace();
} finally {
File file1 = new File(locaPath);
if (file1.exists() && file1.isFile()) {
file1.delete();
}
}
}
}
return fileName;
}
@Data
@ColumnWidth(value = 30)
public class ExamExcelVo {
@ExcelProperty(value = "考试题目", index = 0)
private String questionStem;
@ExcelProperty(value = "答题类型", converter = EasyExcelTopicTypeConverter.class, index = 1)
private String questionType;
@ExcelProperty(value = "正确答案", index = 2)
private String answer;
@ExcelProperty(value = "考题标签", index = 3)
private String major;
@ExcelProperty(value = "题要求", converter = EasyExcelDemandConverter.class, index = 4)
private String demand;
@ExcelProperty(value = "选项A", index = 5)
private String optionA;
@ExcelProperty(value = "选项B", index = 6)
private String optionB;
@ExcelProperty(value = "选项C", index = 7)
private String optionC;
@ExcelProperty(value = "选项D", index = 8)
private String optionD;
@ExcelProperty(value = "选项E", index = 9)
private String optionE;
/**
* 判断正确选项,反射得到字段值(非必要)
*
* @return
*/
public List<String> concatenateFields() {
List<String> values = new ArrayList<>();
try {
if (StrUtil.isNotEmpty(answer)) {
answer = answer.replaceAll(" ", "");
// 使用 HashSet 来存储不重复的字母
Set<Character> uniqueLetters = new HashSet<>();
// 使用正则表达式匹配 A 到 Z 和 a 到 z 的字母
Pattern pattern = Pattern.compile("[a-zA-Z]");
Matcher matcher = pattern.matcher(answer);
while (matcher.find()) {
char letter = matcher.group().charAt(0);
// 使用大写字母存储，以忽略大小写
uniqueLetters.add(Character.toUpperCase(letter));
}
List<String> r = new ArrayList<>();
for (char letter : uniqueLetters) {
r.add(letter + "");
}
for (String fieldName : r) {
Field field = this.getClass().getDeclaredField("option" + fieldName);
String value = (String) field.get(this);
values.add(value);
}
}
} catch (Exception e) {
}
return values;
}
}
/**
* @Author 955
* @Date 2023-09-21 18:28
* @Description 字段处理
*/
public class EasyExcelTopicTypeConverter implements Converter<String> {
@Override
public Class<String> supportJavaTypeKey() {
return String.class;
}
@Override
public CellDataTypeEnum supportExcelTypeKey() {
return CellDataTypeEnum.STRING;
}
@Override
public String convertToJavaData(CellData cellData, ExcelContentProperty contentProperty, GlobalConfiguration globalConfiguration) throws Exception {
if (StrUtil.isNotEmpty(cellData.getStringValue())) {
//处理逻辑。。。
//最终返回的值
return xxx;
}
return null;
}
@Override
public CellData convertToExcelData(String value, ExcelContentProperty contentProperty, GlobalConfiguration globalConfiguration) throws Exception {
if (StrUtil.isNotEmpty(value)) {
String name = CourseBaseEnum.ExamType.getNameById(value);
return new CellData(name);
}
return new CellData("");
}
}
/**
* 剔除全部字段为null数据对象
*
* @param excelVo
*/
private void eliminateNullList(List<ExamExcelVo> excelVo) {
if (excelVo != null) {
Iterator<ExamExcelVo> iterator = excelVo.iterator();
while (iterator.hasNext()) {
ExamExcelVo next = iterator.next();
if (isAllFieldNull(next)) {
iterator.remove();
}
}
}
}
/**
* 获取表头集合
* @param t
* @param objmap
* @return
*/
public Map<String, Integer> getFieldAnnotations(Class t, Map<String, Integer> objmap) {
try {
Map<String, Integer> heads = new LinkedHashMap<>();
Field[] fields = t.getDeclaredFields();
for (Field field : fields) {
ExcelProperty excelpro = (ExcelProperty) field.getAnnotation(ExcelProperty.class);
heads.put(excelpro.value()[0], excelpro.index());
// heads.add(excelpro.value()[0]);
objmap.put(field.getName(), excelpro.index());
}
return heads;
} catch (Exception e) {
e.printStackTrace();
}
return null;
}
public R<?> importExams(List<ExamExcelVo> excelVo) {
List<List<Object>> errorList = new ArrayList<List<Object>>();
eliminateNullList(excelVo);
try {
List<ErrorTypeNewVo> errorListVo = new ArrayList<>();
Map<String, Integer> objmap = new LinkedHashMap<>();
Map<String, Integer> heads = getFieldAnnotations(ExamExcelVo.class, objmap);
for (int i = 0; i < excelVo.size(); i++) {
List<ErrorTypeNewVo> errorTypeVos = new ArrayList<ErrorTypeNewVo>();
ExamExcelVo vo = excelVo.get(i);
String questionStem = vo.getQuestionStem();
ExaminationQuestion question = new ExaminationQuestion();
if (StrUtil.isEmpty(questionStem)) {
errorTypeVos.add(new ErrorTypeNewVo("题目内容为空", "请重新填写", errorList.size() + 1, heads.get("考试题目")));
} else {
question.setQuestionStem(questionStem);
}
//.......等等等等判断
if (CollUtil.isEmpty(errorTypeVos)) {
//没有错误信息操作.......
} else {
//有错误信息操作
List<Object> o = new ArrayList<>();
Map<String, Object> map = BeanUtil.beanToMap(vo);
for (Map.Entry<String, Integer> entry : objmap.entrySet()) {
if (map.get(entry.getKey()) != null) {
o.add(map.get(entry.getKey()));
} else {
o.add("");
}
}
errorList.add(o);
errorListVo.addAll(errorTypeVos);
}
}
if (errorListVo.size() > 0) {
//生成错误文件
String XSSFSheetName = "导入错误信息" + System.currentTimeMillis();
List<String> collect = heads.keySet().stream().collect(Collectors.toList());
//调用上方导出方法
String BUCKET_NAME = newCreateExcelTemple_Object(XSSFSheetName, collect, errorList, errorListVo);
return 导入有错误信息;
}
} catch (Exception e) {
e.printStackTrace();
}
return 导入成功信息;
}
导入接口示例：
@ApiOperation("批量导入试题")
@PostMapping(value = "/importExams")
public 返回参数 importExams(@RequestExcel List<ExamExcelVo> excelVo) {
return importExams(excelVo);
}