Java将横板Excel题库表转换成纵版Word文件,并正确答案高亮显示
研究生毕业后,入职一家上市公司。最近开展岗位定级考试,拿到的题库是横板的。即这样:
序号 | 题目 | 类型 | 答案 |
---|
我想搞成 纵版的样子,按类型区分,且正确答案高亮显示,这样便于阅读记忆。
本次吸收了一个月之前的教训,没有尝试之前没有使用过的库,而是老老实实使用使用过的库。
我是用 阿里 的 EasyExcel 来读取 表格,再使用 阿帕奇 的 Poi 来将表格转换成纵版,外加高亮显示,输出为 Word文件。
主要是 三个 类 ,数据模型类 BankDataR.class , Excel文件解析逻辑类 BankDataListener.class 和 main方法存在的入口类 Excel2Word.class。
import com.alibaba.excel.annotation.ExcelProperty;
import lombok.Data;
import lombok.EqualsAndHashCode;
import lombok.Getter;
import lombok.Setter;
@Setter
@Data
@Getter
@EqualsAndHashCode
public class BankDataR {
@ExcelProperty("序号")
private String order;
@ExcelProperty("试题")
private String questionBody;
@ExcelProperty("类型")
private String type;
@ExcelProperty("选项(用'|'隔开)")
private String answers;
@ExcelProperty("答案")
private String rightAsw;
}
import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.metadata.CellExtra;
import com.alibaba.excel.read.listener.ReadListener;
import com.alibaba.excel.util.ListUtils;
import java.util.List;
import java.util.Map;
public class BankDataListener implements ReadListener {
/**
* 每隔5条存储数据库,实际使用中可以100条,然后清理list ,方便内存回收
*/
private static final int BATCH_COUNT = 100;
/**
* 缓存的数据
* 1 单选,2 多选,3 判断
*/
private List<BankDataR> cachedDataList1 = ListUtils.newArrayListWithExpectedSize(BATCH_COUNT);
private List<BankDataR> cachedDataList2 = ListUtils.newArrayListWithExpectedSize(BATCH_COUNT);
private List<BankDataR> cachedDataList3 = ListUtils.newArrayListWithExpectedSize(BATCH_COUNT);
/**
* 假设这个是一个DAO,当然有业务逻辑这个也可以是一个service。当然如果不用存储这个对象没用。
*/
//private DemoDAO demoDAO;
public BankDataListener() {
// 这里是demo,所以随便new一个。实际使用如果到了spring,请使用下面的有参构造函数
//demoDAO = new DemoDAO();
}
/**
* 如果使用了spring,请使用这个构造方法。每次创建Listener的时候需要把spring管理的类传进来
*
* @param
*/
/*public DemoDataListener(DemoDAO demoDAO) {
this.demoDAO = demoDAO;
}*/
@Override
public void onException(Exception exception, AnalysisContext context) throws Exception {
ReadListener.super.onException(exception, context);
}
@Override
public void invokeHead(Map headMap, AnalysisContext context) {
ReadListener.super.invokeHead(headMap, context);
}
@Override
public void invoke(Object data, AnalysisContext analysisContext) {
BankDataR data1 = (BankDataR) data;
data1.setAnswers(data1.getAnswers() == null ? null : data1.getAnswers().replaceAll("\\|", " | "));
if("单选".equals(data1.getType())){
cachedDataList1.add(data1);
}else if ("多选".equals(data1.getType())){
cachedDataList2.add(data1);
}else {
cachedDataList3.add(data1);
}
// 达到BATCH_COUNT了,需要去存储一次数据库,防止数据几万条数据在内存,容易OOM
if (cachedDataList1.size() >= BATCH_COUNT ) {
//存储到Excel2Word 的list中
Excel2Word.singlelist.addAll(cachedDataList1);
// 存储完成清理 list
cachedDataList1 = ListUtils.newArrayListWithExpectedSize(BATCH_COUNT);
}
if (cachedDataList2.size() >= BATCH_COUNT ) {
//存储到Excel2Word 的list中
Excel2Word.multiplelist.addAll(cachedDataList2);
// 存储完成清理 list
cachedDataList2 = ListUtils.newArrayListWithExpectedSize(BATCH_COUNT);
}
if (cachedDataList3.size() >= BATCH_COUNT ) {
//存储到Excel2Word 的list中
Excel2Word.judgelist.addAll(cachedDataList3);
// 存储完成清理 list
cachedDataList3 = ListUtils.newArrayListWithExpectedSize(BATCH_COUNT);
}
//这里1629是最后一条数据的序号,即最后一步操作
if("1629".equals(data1.getOrder() )){
Excel2Word.map.put("单选", Excel2Word.singlelist);
Excel2Word.map.put("多选", Excel2Word.multiplelist);
Excel2Word.map.put("判断", Excel2Word.judgelist);
}
}
@Override
public void extra(CellExtra extra, AnalysisContext context) {
ReadListener.super.extra(extra, context);
}
/**
* 所有数据解析完成了 都会来调用
*
* @param context
*/
@Override
public void doAfterAllAnalysed(AnalysisContext context) {
// 这里也要保存数据,确保最后遗留的数据也存储到数据库
//System.out.println("已经处理了" + BATCH_COUNT+ "条数据");
}
@Override
public boolean hasNext(AnalysisContext context) {
return ReadListener.super.hasNext(context);
}
/**
* 加上存储数据库
*/
private void saveData() {
//demoDAO.save(cachedDataList);
System.out.println("已经处理了" + BATCH_COUNT+ "条数据");
}
}
import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.util.ListUtils;
import org.apache.poi.xwpf.model.XWPFHeaderFooterPolicy;
import org.apache.poi.xwpf.usermodel.*;
import org.openxmlformats.schemas.wordprocessingml.x2006.main.*;
import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
public class Excel2Word {
static String excelPath = "D:\\旧F盘\\desktop\\附件5.2022基础运营人员题库.xlsx";
static String wordPath = "D:\\旧F盘\\desktop\\附件5.2022基础运营人员题库.docx";
//单选
static ArrayList<BankDataR> singlelist = ListUtils.newArrayListWithExpectedSize(1700);
//多选
static ArrayList<BankDataR> multiplelist = ListUtils.newArrayListWithExpectedSize(1700);
//判断
static ArrayList<BankDataR> judgelist = ListUtils.newArrayListWithExpectedSize(1700);
static Map<String, List<BankDataR>> map = new HashMap<>();
public static void main(String[] args) throws IOException {
EasyExcel.read(excelPath, BankDataR.class, new BankDataListener()).sheet().doRead();
XWPFDocument document= new XWPFDocument();
//Write the Document in file system
FileOutputStream out = new FileOutputStream(new File(wordPath));
//添加标题
XWPFParagraph titleParagraph = document.createParagraph();
//设置段落居中
titleParagraph.setAlignment(ParagraphAlignment.CENTER);
XWPFRun titleParagraphRun = titleParagraph.createRun();
titleParagraphRun.setText("岗位定级考试试题");
titleParagraphRun.setColor("000000");
titleParagraphRun.setFontSize(20);
//段落
XWPFParagraph firstParagraph = document.createParagraph();
XWPFRun run = firstParagraph.createRun();
run.setText("一、单选" );
run.setColor("696969");
run.setFontSize(16);
//设置段落背景颜色
CTShd cTShd = run.getCTR().addNewRPr().addNewShd();
cTShd.setVal(STShd.CLEAR);
cTShd.setFill("97FFFF");
for (BankDataR bankDataR : map.get("单选")){
XWPFParagraph bodySmall = document.createParagraph();
XWPFRun runSmall = bodySmall.createRun();
runSmall.setText(bankDataR.getOrder() + ". " + bankDataR.getQuestionBody());
runSmall.setColor("696969");
runSmall.setFontSize(14);
//换行
XWPFParagraph paragraph1 = document.createParagraph();
XWPFRun paragraphRun1 = paragraph1.createRun();
paragraphRun1.setText("\r");
XWPFParagraph aswsSmall = document.createParagraph();
String answers = bankDataR.getAnswers();
answers = answers.replaceAll(" ", "");
String[] strings = answers.split("\\|");
if(bankDataR.getRightAsw().indexOf("A") != -1){
XWPFRun runSmallASW2 = aswsSmall.createRun();
runSmallASW2.setText( strings[0]+" | ");
runSmallASW2.setColor("696969");
runSmallASW2.setFontSize(12);
runSmallASW2.getCTR().addNewRPr().addNewHighlight().setVal(STHighlightColor.YELLOW);
}else{
XWPFRun runSmallASW2 = aswsSmall.createRun();
runSmallASW2.setText( strings[0]+" | ");
runSmallASW2.setColor("696969");
runSmallASW2.setFontSize(12);
}
if(bankDataR.getRightAsw().indexOf("B") != -1){
XWPFRun runSmallASW2 = aswsSmall.createRun();
runSmallASW2.setText( strings[1]+" | ");
runSmallASW2.setColor("696969");
runSmallASW2.setFontSize(12);
runSmallASW2.getCTR().addNewRPr().addNewHighlight().setVal(STHighlightColor.YELLOW);
}else{
XWPFRun runSmallASW2 = aswsSmall.createRun();
runSmallASW2.setText( strings[1]+" | ");
runSmallASW2.setColor("696969");
runSmallASW2.setFontSize(12);
}
if(bankDataR.getRightAsw().indexOf("C") != -1){
XWPFRun runSmallASW2 = aswsSmall.createRun();
runSmallASW2.setText( strings[2]+" | ");
runSmallASW2.setColor("696969");
runSmallASW2.setFontSize(12);
runSmallASW2.getCTR().addNewRPr().addNewHighlight().setVal(STHighlightColor.YELLOW);
}else{
XWPFRun runSmallASW2 = aswsSmall.createRun();
runSmallASW2.setText( strings[2]+" | ");
runSmallASW2.setColor("696969");
runSmallASW2.setFontSize(12);
}
if(bankDataR.getRightAsw().indexOf("D") != -1){
XWPFRun runSmallASW2 = aswsSmall.createRun();
runSmallASW2.setText( strings[3]+" | ");
runSmallASW2.setColor("696969");
runSmallASW2.setFontSize(12);
runSmallASW2.getCTR().addNewRPr().addNewHighlight().setVal(STHighlightColor.YELLOW);
}else{
XWPFRun runSmallASW2 = aswsSmall.createRun();
runSmallASW2.setText( strings[3]);
runSmallASW2.setColor("696969");
runSmallASW2.setFontSize(12);
}
//换行
XWPFParagraph paragraph2 = document.createParagraph();
XWPFRun paragraphRun2 = paragraph2.createRun();
paragraphRun2.setText("\r");
XWPFParagraph rightAswSmall = document.createParagraph();
XWPFRun rightAswRunSmall = rightAswSmall.createRun();
rightAswRunSmall.setText( bankDataR.getRightAsw());
rightAswRunSmall.setColor("696969");
rightAswRunSmall.setFontSize(12);
}
//段落
XWPFParagraph secondParagraph = document.createParagraph();
XWPFRun run2 = secondParagraph.createRun();
run2.setText("二、多选" );
run2.setColor("696969");
run2.setFontSize(16);
//设置段落背景颜色
CTShd cTShd2 = run2.getCTR().addNewRPr().addNewShd();
cTShd2.setVal(STShd.CLEAR);
cTShd2.setFill("97FFFF");
for (BankDataR bankDataR : map.get("多选")){
XWPFParagraph bodySmall = document.createParagraph();
XWPFRun runSmall = bodySmall.createRun();
runSmall.setText(bankDataR.getOrder() + ". " + bankDataR.getQuestionBody());
runSmall.setColor("696969");
runSmall.setFontSize(14);
//换行
XWPFParagraph paragraph1 = document.createParagraph();
XWPFRun paragraphRun1 = paragraph1.createRun();
paragraphRun1.setText("\r");
XWPFParagraph aswsSmall = document.createParagraph();
String answers = bankDataR.getAnswers();
answers = answers.replaceAll(" ", "");
String[] strings = answers.split("\\|");
if(bankDataR.getRightAsw().indexOf("A") != -1){
XWPFRun runSmallASW2 = aswsSmall.createRun();
runSmallASW2.setText( strings[0]+" | ");
runSmallASW2.setColor("696969");
runSmallASW2.setFontSize(12);
runSmallASW2.getCTR().addNewRPr().addNewHighlight().setVal(STHighlightColor.YELLOW);
}else{
XWPFRun runSmallASW2 = aswsSmall.createRun();
runSmallASW2.setText( strings[0]+" | ");
runSmallASW2.setColor("696969");
runSmallASW2.setFontSize(12);
}
if(bankDataR.getRightAsw().indexOf("B") != -1){
XWPFRun runSmallASW2 = aswsSmall.createRun();
runSmallASW2.setText( strings[1]+" | ");
runSmallASW2.setColor("696969");
runSmallASW2.setFontSize(12);
runSmallASW2.getCTR().addNewRPr().addNewHighlight().setVal(STHighlightColor.YELLOW);
}else{
XWPFRun runSmallASW2 = aswsSmall.createRun();
runSmallASW2.setText( strings[1]+" | ");
runSmallASW2.setColor("696969");
runSmallASW2.setFontSize(12);
}
if(bankDataR.getRightAsw().indexOf("C") != -1){
XWPFRun runSmallASW2 = aswsSmall.createRun();
runSmallASW2.setText( strings[2]+" | ");
runSmallASW2.setColor("696969");
runSmallASW2.setFontSize(12);
runSmallASW2.getCTR().addNewRPr().addNewHighlight().setVal(STHighlightColor.YELLOW);
}else{
XWPFRun runSmallASW2 = aswsSmall.createRun();
runSmallASW2.setText( strings[2]+" | ");
runSmallASW2.setColor("696969");
runSmallASW2.setFontSize(12);
}
if(bankDataR.getRightAsw().indexOf("D") != -1){
XWPFRun runSmallASW2 = aswsSmall.createRun();
runSmallASW2.setText( strings[3]);
runSmallASW2.setColor("696969");
runSmallASW2.setFontSize(12);
runSmallASW2.getCTR().addNewRPr().addNewHighlight().setVal(STHighlightColor.YELLOW);
}else{
XWPFRun runSmallASW2 = aswsSmall.createRun();
runSmallASW2.setText( strings[3]);
runSmallASW2.setColor("696969");
runSmallASW2.setFontSize(12);
}
//换行
XWPFParagraph paragraph2 = document.createParagraph();
XWPFRun paragraphRun2 = paragraph2.createRun();
paragraphRun2.setText("\r");
XWPFParagraph rightAswSmall = document.createParagraph();
XWPFRun rightAswRunSmall = rightAswSmall.createRun();
rightAswRunSmall.setText( bankDataR.getRightAsw());
rightAswRunSmall.setColor("696969");
rightAswRunSmall.setFontSize(12);
}
//段落
XWPFParagraph thirdParagraph = document.createParagraph();
XWPFRun run3 = thirdParagraph.createRun();
run3.setText("三、判断" );
run3.setColor("696969");
run3.setFontSize(16);
//设置段落背景颜色
CTShd cTShd3 = run3.getCTR().addNewRPr().addNewShd();
cTShd3.setVal(STShd.CLEAR);
cTShd3.setFill("97FFFF");
for (BankDataR bankDataR : map.get("判断")){
XWPFParagraph bodySmall = document.createParagraph();
XWPFRun runSmall = bodySmall.createRun();
runSmall.setText(bankDataR.getOrder() + ". " + bankDataR.getQuestionBody());
runSmall.setColor("696969");
runSmall.setFontSize(14);
//换行
XWPFParagraph paragraph2 = document.createParagraph();
XWPFRun paragraphRun2 = paragraph2.createRun();
paragraphRun2.setText("\r");
XWPFParagraph rightAswSmall = document.createParagraph();
XWPFRun rightAswRunSmall = rightAswSmall.createRun();
rightAswRunSmall.setText( bankDataR.getRightAsw());
rightAswRunSmall.setColor("696969");
rightAswRunSmall.setFontSize(12);
}
//基本信息表格
/*
XWPFTable infoTable = document.createTable();
//去表格边框
infoTable.getCTTbl().getTblPr().unsetTblBorders();
//列宽自动分割
CTTblWidth infoTableWidth = infoTable.getCTTbl().addNewTblPr().addNewTblW();
infoTableWidth.setType(STTblWidth.DXA);
infoTableWidth.setW(BigInteger.valueOf(9072));*/
/* //表格第一行
XWPFTableRow infoTableRowOne = infoTable.getRow(0);
infoTableRowOne.getCell(0).setText("职位");
infoTableRowOne.addNewTableCell().setText(": Java 开发工程师");
//表格第二行
XWPFTableRow infoTableRowTwo = infoTable.createRow();
infoTableRowTwo.getCell(0).setText("姓名");
infoTableRowTwo.getCell(1).setText(": seawater");
//表格第三行
XWPFTableRow infoTableRowThree = infoTable.createRow();
infoTableRowThree.getCell(0).setText("生日");
infoTableRowThree.getCell(1).setText(": xxx-xx-xx");
//表格第四行
XWPFTableRow infoTableRowFour = infoTable.createRow();
infoTableRowFour.getCell(0).setText("性别");
infoTableRowFour.getCell(1).setText(": 男");
//表格第五行
XWPFTableRow infoTableRowFive = infoTable.createRow();
infoTableRowFive.getCell(0).setText("现居地");
infoTableRowFive.getCell(1).setText(": xx");*/
CTSectPr sectPr = document.getDocument().getBody().addNewSectPr();
XWPFHeaderFooterPolicy policy = new XWPFHeaderFooterPolicy(document, sectPr);
/* //添加页眉
CTP ctpHeader = CTP.Factory.newInstance();
CTR ctrHeader = ctpHeader.addNewR();
CTText ctHeader = ctrHeader.addNewT();
String headerText = "中国农业银行 ABC";
ctHeader.setStringValue(headerText);
XWPFParagraph headerParagraph = new XWPFParagraph(ctpHeader, document);
//设置为右对齐
headerParagraph.setAlignment(ParagraphAlignment.RIGHT);
XWPFParagraph[] parsHeader = new XWPFParagraph[1];
parsHeader[0] = headerParagraph;
policy.createHeader(XWPFHeaderFooterPolicy.DEFAULT, parsHeader);
//添加页脚
CTP ctpFooter = CTP.Factory.newInstance();
CTR ctrFooter = ctpFooter.addNewR();
CTText ctFooter = ctrFooter.addNewT();
String footerText = "中国农业银行 ABC";
ctFooter.setStringValue(footerText);
XWPFParagraph footerParagraph = new XWPFParagraph(ctpFooter, document);
headerParagraph.setAlignment(ParagraphAlignment.CENTER);
XWPFParagraph[] parsFooter = new XWPFParagraph[1];
parsFooter[0] = footerParagraph;
policy.createFooter(XWPFHeaderFooterPolicy.DEFAULT, parsFooter);
*/
document.write(out);
out.close();
}
}
最终文件如下所示, 另存为 PDF 即可 使用。