1.前言
阿里的开源 EXCEL 处理工具类 EasyExcel 在 CSDN上的文章比较多。官方文档上也有描述。 但是具体的很多实现都是需要自己取琢磨。
2.代码实现
动态表头需要将前端传到的表头信息封装成EasyExcel 所需要的 List<List<String>> 形式
1.准备一个NoModelWriteData 实体类 (因项目问题, 没有用lombok)
import com.alibaba.fastjson.JSONObject;
import java.io.Serializable;
import java.util.List;
public class NoModelWriteData implements Serializable {
private String fileName;
private List<List<String>> headList;
private List<String> keyList;
private List<JSONObject> dataList;
private List<String> dataTypeList;
private String exportId;
private Integer xh;
public Integer getXh() {
return xh;
}
public void setXh(Integer xh) {
this.xh = xh;
}
public String getExportId() {
return exportId;
}
public void setExportId(String exportId) {
this.exportId = exportId;
}
public String getFileName() {
return fileName;
}
public void setFileName(String fileName) {
this.fileName = fileName;
}
public List<List<String>> getHeadList() {
return headList;
}
public void setHeadList(List<List<String>> headList) {
this.headList = headList;
}
public List<JSONObject> getDataList() {
return dataList;
}
public void setDataList(List<JSONObject> dataList) {
this.dataList = dataList;
}
public List<String> getKeyList() {
return keyList;
}
public void setKeyList(List<String> keyList) {
this.keyList = keyList;
}
public List<String> getDataTypeList() {
return dataTypeList;
}
public void setDataTypeList(List<String> dataTypeList) {
this.dataTypeList = dataTypeList;
}
}
2.准备一个导出的工具类
public class GeneralExportUtilV2 {
/**
* 构造数据, 生成符合导出的数据
* @param columns 前台传过来的
* @param sheetName
* @param exportId 通过uuid 生成临时文件的名称, 前台通过UUID 下载文件, 和删除文件,
*/
public static void buildDataAndExportExcel(BaseVO vo, String columns, String sheetName, String exportId, HashMap input,
String configStr,XmlNode tempnode,String daoTaskName) {
List<JSONObject> columnList = JSONObject.parseArray(columns, JSONObject.class);
List<List<String>> titleList = new ArrayList<>();
List<String> keyList = new ArrayList<>(); //数据库字段名称
List<String> dataTypeList = new ArrayList<>();//数据类型 兼容老板系统导出
Integer total = Integer.parseInt(vo.getRequestParameter("total"));
int col2=0;
int col3=0;
for (JSONObject jsonObject : columnList) {
List<String> head0 = new ArrayList<>();
String header1 = jsonObject.getString("header");
if (jsonObject.containsKey("rowspan")&& jsonObject.getString("rowspan").equals("2")) {
head0.add(jsonObject.getString("header"));
head0.add(jsonObject.getString("header"));
}else if (jsonObject.containsKey("rowspan")&& jsonObject.getString("rowspan").equals("3")){
head0.add(jsonObject.getString("header"));
head0.add(jsonObject.getString("header"));
head0.add(jsonObject.getString("header"));
}else{
head0.add(jsonObject.getString("header"));
}
if (jsonObject.containsKey("field")) {
keyList.add(jsonObject.getString("field"));
if (jsonObject.containsKey("dataType")){
dataTypeList.add(jsonObject.getString("dataType"));
}else{
dataTypeList.add("text");
}
}
if (jsonObject.containsKey("columns")) {
JSONArray secondRow = jsonObject.getJSONArray("columns");
for (Object secCol : secondRow) {
JSONObject secObject = (JSONObject) secCol;
String secHeader = secObject.getString("header");
if (secObject.containsKey("columns")) {
col3=1;
JSONArray secondThree = secObject.getJSONArray("columns");
for (Object threeCol : secondThree) {
JSONObject threeObject = (JSONObject) threeCol;
head0 = new ArrayList<>();
head0.add(header1);
head0.add(secHeader);
head0.add(threeObject.getString("header"));
titleList.add(head0);
if (secObject.containsKey("field")) {
keyList.add(secObject.getString("field"));
if (jsonObject.containsKey("dataType")){
dataTypeList.add(jsonObject.getString("dataType"));
}else{
dataTypeList.add("text");
}
}
}
}else{
col2=1;
head0 = new ArrayList<>();
head0.add(header1);
if (secObject.containsKey("rowspan")&& secObject.getString("rowspan").equals("2")) {
head0.add(secObject.getString("header"));
head0.add(secObject.getString("header"));
}else{
head0.add(secObject.getString("header"));
}
titleList.add(head0);
if (secObject.containsKey("field")) {
keyList.add(secObject.getString("field"));
if (jsonObject.containsKey("dataType")){
dataTypeList.add(jsonObject.getString("dataType"));
}else{
dataTypeList.add("text");
}
}
}
}
}else{
titleList.add(head0);
}
}
NoModelWriteData writeData = new NoModelWriteData();
writeData.setFileName(sheetName);
writeData.setHeadList(titleList);
writeData.setKeyList(keyList);
writeData.setDataTypeList(dataTypeList);
writeData.setExportId(exportId);
commonExportExcel(writeData,total,dataTypeList);
}
private static void commonExportExcel(NoModelWriteData writeData, Integer total, List<String> dataTypeList) {
String tempPath = FxConstant.BASEPATH + "components" + File.separator + "fileio" + File.separator + "expfile";
String fileName = tempPath + File.separator + writeData.getExportId()+".xlsx";
writeData.setXh(1);
int expPageSize = 10000;
int totalPage = 1;
if (total>10000){
totalPage = total/expPageSize+1;
}
try (ExcelWriter excelWriter = EasyExcel.write(fileName).head(writeData.getHeadList()).build()) {
SyjkExQueryLogicHandler syjkExQueryLogicHandler = new SyjkExQueryLogicHandler();
for (int pageIndex = 1; pageIndex <= totalPage; pageIndex++) {
// 此处根据需求, 觉得是否要根据 pageSize 分页查询数据库, 分批写入到excel 中
WriteSheet writeSheet = EasyExcel.writerSheet(0, writeData.getFileName()+pageIndex)
.registerWriteHandler(CellStyleStrategy.getCellStyle())
.registerWriteHandler(new CellTypeSettingHandler())
.build();
List<JSONObject> jsonObjects = DataConvertUtil.dataToListjson(daoResult);
excelWriter.write(dataList(jsonObjects,writeData), writeSheet);
}
}
}
//设置导出的数据内容
private static List<List<Object>> dataList(List<JSONObject> dataList, NoModelWriteData writeData) {
List<List<Object>> list = new ArrayList<List<Object>>();
List<String> keyList = writeData.getKeyList();
List<String> dataTypeList = writeData.getDataTypeList();
int xh = writeData.getXh();
for (JSONObject jsonObject : dataList) {
List<Object> data = new ArrayList<Object>();
data.add(xh);
for (int i = 0; i < keyList.size(); i++) {
data.add(jsonObject.get(keyList.get(i)));
}
list.add(data);
xh++;
}
writeData.setXh(xh);
return list;
}
public static void setCellStyle(String data,Workbook workbook,Cell cell){
Boolean isNum = false;//data是否为数值型
Boolean isInteger=false;//data是否为整数
Boolean isPercent=false;//data是否为百分数
if (StringUtils.isNotEmpty(data)) {
String[] split = data.split("\\.");
String jd = "03-06-09-12";
String year = "20";
if (!(split.length ==2 && jd.contains(split[1]) && split[0].length()==4 && split[0].substring(0,2).equals(year))) {
//判断data是否为数值型
isNum = data.matches("^(-?\\d+)(\\.\\d+)?$");
}
//判断data是否为整数（小数部分是否为0）
isInteger=data.matches("^[-\\+]?[\\d]*$");
//判断data是否为百分数（是否包含“%”）
isPercent=data.contains("%");
}
CellStyle contextstyle = cell.getCellStyle();
//如果单元格内容是数值类型，涉及到金钱（金额、本、利），则设置cell的类型为数值型，设置data的类型为数值类型
cell.setCellFormula(null);
if (isNum && !isPercent) {
DataFormat df = workbook.createDataFormat();// 此处设置数据格式
if (isInteger) {
contextstyle.setDataFormat(df.getFormat("General"));
// 设置单元格格式
cell.setCellStyle(contextstyle);
// 设置单元格内容为整型
cell.setCellValue(data);
}else{
contextstyle.setDataFormat(df.getFormat("#,##0.00"));//保留两位小数点
// 设置单元格格式
cell.setCellStyle(contextstyle);
// 设置单元格内容为double类型
cell.setCellValue(Double.parseDouble(new BigDecimal(data).setScale(4,BigDecimal.ROUND_HALF_UP).toString()));
}
} else {
cell.setCellStyle(contextstyle);
// 设置单元格内容为字符型
cell.setCellValue(StringUtils.isNotEmpty(data) && !"null".equals(data) ? data : "");
}
}
public static void setCellValue(Workbook workbook,Cell cell,int index,String key,List<JSONObject> dataList){
String value = dataList.get(index).getString(key);
try{
if (value.contains("E") && value.contains(".")){
BigDecimal bigDecimal = dataList.get(index).getBigDecimal(key).setScale(4,BigDecimal.ROUND_HALF_UP);
value = bigDecimal.toString();
setCellStyle(value,workbook,cell);
}else{
setCellStyle(value,workbook,cell);
}
}catch (NumberFormatException ex){
cell.setCellValue(value);
}
}
}前端传来的JSON 参数
[{
"name": "",
"header": "序号",
"rowspan": 2
},
{
"name": "",
"header": "基本信息",
"colspan": 4,
"columns": [{
"header": "名字",
"name": "name",
"field": "name"
},
{
"header": "年龄",
"name": "age",
"field": "age"
},
{
"header": "地址",
"name": "add",
"field": "add"
},
{
"header": "电话",
"name": "tel",
"field": "tel"
}
]
},
{
"name": "",
"header": "附加信息",
"colspan": 3,
"columns": [{
"header": "爱好",
"name": "aih",
"field": "aih"
},
{
"header": "xx",
"name": "xx",
"field": "xx"
},
{
"header": "xx",
"name": "xx",
"field": "xx"
}
]
}
]
1万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
