效果图：
package excel;
import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import java.util.List;
import java.util.Objects;
public class OperateExcel {
/**
* 创建WorkBook对象
* @param list 合并后的list集合
* @param labels 标签数组(文件第一行)
* @param title 左右语句类型数组(文件第一行)
* @return HSSFWorkbook对象
*/
public XSSFWorkbook getWorkBook(List list, JSONArray labels, JSONArray title) {
XSSFWorkbook xwk = new XSSFWorkbook();
XSSFSheet xssfSheet = xwk.createSheet("导出结果");
//√符号
int[] code = {0x2611};
//创建表头
Integer initLabel = 3;
createHeader(xssfSheet,labels,title,initLabel);
JSONObject oneRow;
int j = 0;
int size = list.size();
for (j = 0; j < size; j++) {
oneRow = list.get(j);
// 创建内容行：从第二行开始，跳过属性列
createContent(labels, xssfSheet, oneRow, j,initLabel,code);
oneRow = null;
}
return xwk;
}
/**
* 创建表头
* @param xssfSheet 工作薄
*/
private void createHeader(XSSFSheet xssfSheet,JSONArray labels,JSONArray title,Integer initLabel) {
Row rowIndex = xssfSheet.createRow(0);
Cell first = rowIndex.createCell(0);
first.setCellValue("序号");
if(title != null && title.size() != 0){
Cell second = rowIndex.createCell(1);
second.setCellValue(title.get(0).toString());
Cell third = rowIndex.createCell(2);
third.setCellValue(title.get(1).toString());
}
if(labels != null && labels.size() != 0){
for(Object obj : labels){
JSONObject newVo = (JSONObject) obj;
Cell fourAddCellIndex = rowIndex.createCell(initLabel++);
fourAddCellIndex.setCellValue(newVo.getString("name"));
}
}
xssfSheet.createFreezePane( 0, 1, 0, 1 );
}
/**
* 创建内容行：从第二行开始，跳过属性列
* @param labels 标签数组
* @param xssfSheet 工作薄
* @param oneRow 行对象内容
* @param j 列索引
*/
private void createContent(JSONArray labels, XSSFSheet xssfSheet, JSONObject oneRow, int j,Integer initLabel, int[] code) {
Row row = xssfSheet.createRow(j + 1);
//创建相对静态的内容，前三列为序号，左边语句，右边语句
createStaticContent(oneRow, j, row);
JSONArray signLabels = oneRow.getJSONArray("label");
//创建Label内容，在labels标签中的打√符号
createLabelContent(labels, row, initLabel, signLabels, code);
row = null;
signLabels = null;
}
/**
*创建Label内容，在labels标签中的打√符号
* @param labels 批次任务的标签
* @param row 行对象
* @param initLabelCell 前三列处理完成之后的列索引
* @param signLabels 一行所标记的label
* @param code √符号码。可扩展其他的符号
*/
private void createLabelContent(JSONArray labels, Row row, Integer initLabelCell, JSONArray signLabels, int[] code) {
JSONObject label;
String value;
if(signLabels != null && signLabels.size() != 0){
int i=0;
int size = labels.size();
for(i=0;i
label = (JSONObject) labels.get(i);
for (Object signLabel : signLabels) {
value = (String) signLabel;
if (value.equals(label.getString("value"))) {
Cell fourAddCell = row.createCell(initLabelCell + i);
fourAddCell.setCellValue(new String(code, 0, 1));
break;
}
}
}
}
value = null;
label = null;
}
/**
*创建相对静态的内容，前三列为序号，左边语句，右边语句
* @param oneRow 要写入Excel行对象的一行内容
* @param j Excel行号
* @param row Excel行对象
* @return 返回第四列索引
*/
private void createStaticContent(JSONObject oneRow, int j, Row row) {
Cell first = row.createCell(0);
first.setCellValue(j + 1);
Cell second = row.createCell(1);
second.setCellValue(Objects.nonNull(oneRow)?oneRow.getString("sentenceL"):"");
Cell third = row.createCell(2);
third.setCellValue(Objects.nonNull(oneRow)?oneRow.getString("sentenceR"):"");
//return 3;
}
}
StringEx工具类 newUUID方法
public static final String newUUID() {
return UUID.randomUUID().toString().replace("-", "").toUpperCase();
}
测试类
package excel;
import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import utils.StringEx;
import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.util.List;
public class App {
public static void main(String[] args){
//获取title
String titleStr = "[\"中文\",\"英文\"]";
JSONArray title = JSONArray.parseArray(titleStr);
//获取labels总数 [{"name":"古风","id":"1"},{"name":"仙女","id":"2"},{"name":"流氓","id":"3"}]
String labelsStr = "[{\"name\":\"古风\",\"value\":\"古风\"},{\"name\":\"仙女\",\"value\":\"仙女\"},{\"name\":\"流氓\",\"value\":\"流氓\"}]";
JSONArray labels = JSONArray.parseArray(labelsStr);
//[{"sentenceL":"李白乘舟将欲行","sentenceR":"Libai chengzhou jiangyu xing",label:["古风","仙女"]},{"sentenceL":"李白乘舟将欲行","sentenceR":"Libai chengzhou jiangyu xing",label:["仙女","流氓"]},{"sentenceL":"李白乘舟将欲行","sentenceR":"Libai chengzhou jiangyu xing",label:["古风","流氓"]}]
String listStr = "[{\"sentenceL\":\"李白乘舟将欲行\",\"sentenceR\":\"Libai chengzhou jiangyu xing\",label:[\"古风\",\"仙女\"]},{\"sentenceL\":\"李白乘舟将欲行\",\"sentenceR\":\"Libai chengzhou jiangyu xing\",label:[\"仙女\",\"流氓\"]},{\"sentenceL\":\"李白乘舟将欲行\",\"sentenceR\":\"Libai chengzhou jiangyu xing\",label:[\"古风\",\"流氓\"]}]";
List convertList = JSONArray.parseArray(listStr,JSONObject.class);
OperateExcel operateExcel = new OperateExcel();
XSSFWorkbook sb;
OutputStream out = null;
String path = App.class.getProtectionDomain().getCodeSource().getLocation().getPath() + File.separator+"excel" + File.separator;
try{
//合并后的结果写入到worbook对象中
sb = operateExcel.getWorkBook(convertList,labels,title);
//将workBook对象写入到字节数组中
out = new FileOutputStream(path + StringEx.newUUID() + ".xlsx");
sb.write(out);
}catch(Exception e){
e.printStackTrace();
}finally{
try {
if(out != null){
out.flush();
out.close();
}
} catch (IOException e) {
e.printStackTrace();
}
}
}
}
推荐一个公众号
号主为一线大厂架构师，CSDN博客专家，博客访问量突破一千万。主要分享Java、golang架构，源码，分布式，高并发等技术，用大厂程序员的视角来探讨技术进阶、面试指南、职业规划等。15W技术人的选择！
