original
expact
the core busiession code from the CSDN reference link blog.csdn.net/AD_Marcelo/…
package com.shiwu.starbucks.cess;
import cn.hutool.core.collection.CollUtil;
import cn.hutool.json.JSONArray;
import cn.hutool.json.JSONObject;
import cn.hutool.json.JSONUtil;
import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.ExcelWriter;
import com.alibaba.excel.metadata.Head;
import com.alibaba.excel.write.builder.ExcelWriterBuilder;
import com.alibaba.excel.write.builder.ExcelWriterSheetBuilder;
import com.alibaba.excel.write.merge.AbstractMergeStrategy;
import com.alibaba.excel.write.metadata.WriteSheet;
import com.shiwu.starbucks.cess.model.Question;
import com.shiwu.util.read.ReadFileTool;
import lombok.AllArgsConstructor;
import lombok.Getter;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.util.CellRangeAddress;
import java.util.*;
import java.util.concurrent.atomic.AtomicInteger;
import java.util.regex.Matcher;
import java.util.regex.Pattern;
import java.util.stream.Collectors;
import java.util.stream.Stream;
/**
* @author sqiang
**/
public class ParseSurveyDetail {
@Getter
@AllArgsConstructor
enum QuestionType {
RADIO("radio", "单选题", true), CHECKBOX("checkbox", "多选题", true),
SCALE("scale", "量表题", true), TEXT("text", "文本题", false),
LBS("lbs", "地图题", false), SECTION("section", "段落说明", false),
PAGE("page", "分页栏", false);
public final String code;
public final String name;
public final Boolean isOption;
public static QuestionType getName(String code) {
QuestionType[] values = QuestionType.values();
for (QuestionType value : values) {
if (value.code.equals(code)) {
return value;
}
}
return null;
}
}
/**
* Reads the content of a file
* @param filetPath path to the file to read
* @return content of the file as String
*/
public String readFile(String filetPath) {
try {
return ReadFileTool.readFile(filetPath);
} catch (Exception e) {
throw new RuntimeException(filetPath + "not found");
}
}
/**
* Parses survey content into a list of Question objects
* @param conext JSON string containing survey data
* @return List of parsed Question objects
*/
public List<Question> parse(String conext) {
Pattern pattern = Pattern.compile("[\\u4e00-\\u9fa5]");
JSONObject jsonObject = JSONUtil.parseObj(conext);
JSONObject detailInfo = jsonObject.getJSONObject("data").getJSONObject("detailInfo");
JSONArray questions = detailInfo.getJSONArray("questions");
List<Question> list = questions.stream().map(each -> {
JSONObject item = (JSONObject) each;
if (QuestionType.PAGE.getCode().equals(item.getStr("questionType")) || QuestionType.SECTION.getCode().equals(item.getStr("questionType"))) {
return null;
}
QuestionType questionType = QuestionType.getName(item.getStr("questionType"));
assert questionType != null;
String str = item.getJSONObject("title").getStr("zh");
Matcher matcher = pattern.matcher(str);
StringBuilder title = new StringBuilder();
while (matcher.find()) {
title.append(matcher.group());
}
if (questionType.isOption) {
JSONArray jsonArray = item.getJSONObject("questionConfig").getJSONArray("options");
return jsonArray.stream().map(optionEach -> {
JSONObject optionItem = (JSONObject) optionEach;
Question question = new Question();
question.setQuestionId(item.getStr("questionId"));
question.setQuestionTitle(title.toString());
question.setQuestionType(questionType.getName());
question.setOptionId(optionItem.getStr("optionId"));
question.setOptionName(optionItem.getJSONObject("text").getStr("zh"));
if (questionType.getName().equals(QuestionType.SCALE.getName())) {
question.setScore(optionItem.getInt("score"));
}
return question;
}).filter(Objects::nonNull).toList();
} else {
Question question = new Question();
question.setQuestionId(item.getStr("questionId"));
question.setQuestionTitle(title.toString());
question.setQuestionType(questionType.getName());
List<Question> options = new ArrayList<>();
options.add(question);
return options;
}
}).filter(Objects::nonNull).flatMap(List::stream).toList();
return list;
}
/**
* Writes survey data to Excel files
* @param data Map containing survey data grouped by sheet name
* @param fileName base path for output Excel files
*/
public void write(Map<String, List<Question>> data, String fileName) {
data.forEach((sheetName, list) -> {
ExcelWriterBuilder write = EasyExcel.write(fileName + sheetName + ".xlsx", Question.class);
List<Integer> mergeColIndexList = Stream.of(0, 1).collect(Collectors.toList());
List<List<String>> mergeColDataList = Stream.of(list.stream().map(Question::getQuestionId).collect(Collectors.toList()), list.stream().map(Question::getQuestionTitle).collect(Collectors.toList())).collect(Collectors.toList());
write.registerWriteHandler(new MergeSameColumnStrategy(mergeColDataList, mergeColIndexList)).sheet(sheetName).doWrite(list);
});
}
public static void main(String[] args) {
ParseSurveyDetail parseSurveyDetail = new ParseSurveyDetail();
Map<String, List<Question>> write = new HashMap<>();
String instore = parseSurveyDetail.readFile("C:\\Users\\sqiang\\Desktop\\SITC\\CESS\\正式问卷-instore.json");
parseSurveyDetail.parse(instore);
write.put("instore", parseSurveyDetail.parse(instore));
String mod = parseSurveyDetail.readFile("C:\\Users\\sqiang\\Desktop\\SITC\\CESS\\正式问卷-MOD.json");
parseSurveyDetail.parse(mod);
write.put("MOD", parseSurveyDetail.parse(mod));
String mop = parseSurveyDetail.readFile("C:\\Users\\sqiang\\Desktop\\SITC\\CESS\\正式问卷-MOP.json");
parseSurveyDetail.parse(mop);
write.put("MOP", parseSurveyDetail.parse(mop));
String roastery = parseSurveyDetail.readFile("C:\\Users\\sqiang\\Desktop\\SITC\\CESS\\正式问卷-烘焙工坊.json");
parseSurveyDetail.parse(roastery);
write.put("Roastery", parseSurveyDetail.parse(roastery));
String fileName = "C:\\Users\\sqiang\\Desktop\\SITC\\CESS\\";
parseSurveyDetail.write(write, fileName);
}
}
class MergeSameColumnStrategy extends AbstractMergeStrategy {
/**
* 分组，每几行合并一次
*/
private List<List<Integer>> mergeColDataGroupCountList;
/**
* 目标合并列index
*/
private List<Integer> targetColumnIndex;
/**
* 需要开始合并单元格的首行index
*/
private Integer rowIndex;
/**
* mergeColDataList为待合并目标列的值
*/
public MergeSameColumnStrategy(List<List<String>> mergeColDataList, List<Integer> targetColumnIndex) {
this.mergeColDataGroupCountList = getGroupCountList(mergeColDataList);
this.targetColumnIndex = targetColumnIndex;
}
@Override
protected void merge(Sheet sheet, Cell cell, Head head, Integer relativeRowIndex) {
if (null == rowIndex) {
rowIndex = cell.getRowIndex();
}
// 仅从首行以及目标列的单元格开始合并，忽略其他
if (cell.getRowIndex() == rowIndex && targetColumnIndex.contains(cell.getColumnIndex())) {
//找到对应的需要合并的列
AtomicInteger i = new AtomicInteger(0);
Optional<Integer> first = targetColumnIndex.stream().filter(col -> {
i.getAndIncrement();
return col == cell.getColumnIndex();
}).findFirst();
mergeGroupColumn(sheet, first.get());
}
}
private void mergeGroupColumn(Sheet sheet, Integer index) {
int rowCount = rowIndex;
for (Integer count : mergeColDataGroupCountList.get(index)) {
if (count == 1) {
rowCount += count;
continue;
}
// 合并单元格
CellRangeAddress cellRangeAddress = new CellRangeAddress(rowCount, rowCount + count - 1, targetColumnIndex.get(index), targetColumnIndex.get(index));
sheet.addMergedRegionUnsafe(cellRangeAddress);
rowCount += count;
}
}
/**
* 该方法将目标列根据值是否相同连续可合并，存储可合并的行数
*/
private List<List<Integer>> getGroupCountList(List<List<String>> exportDataList) {
if (CollUtil.isEmpty(exportDataList)) {
return new ArrayList<>();
}
List<List<Integer>> groupCountListList = new ArrayList<>();
exportDataList.forEach(dataList -> {
List<Integer> groupCountList = new ArrayList<>();
int count = 1;
for (int i = 1; i < dataList.size(); i++) {
if (dataList.get(i).equals(dataList.get(i - 1))) {
count++;
} else {
groupCountList.add(count);
count = 1;
}
}
// 处理完最后一条后
groupCountList.add(count);
groupCountListList.add(groupCountList);
});
return groupCountListList;
}
}
