luckySheet+POI+EasyExcel实现excel模版的导出和数据填充

luckySheet+POI+EasyExcel实现在线excel模版的导出和数据填充

业务需求关键字luckySheetPOIEasyExcel代码实现前端luckySheet配置的excel模版Json获取以及后端解析解析工具类测试类，***测试类中的str值为lucky的Json字符串,自行替换***结果展示

业务需求

前端使用在线LuckySheet配置一个模版Excel

后端实现使用配置的模版进行数据填充并导出

关键字

luckySheet

官方文档：https://mengshukeji.gitee.io/luckysheetdocs/zh/

POI

EasyExcel

官方文档：https://easyexcel./

代码实现

前端luckySheet配置的excel模版存入数据库，其实也就是一个大的Json字符串，mysql用“MEDIUMBLOB”，oracle用“CLOB”去存储

建表示例：mysql版本

CREATE TABLE IF NOT EXISTS `GF_EXCEL_TEMPLATE`(`ID` VARCHAR(32) NOT NULL COMMENT '主键' PRIMARY KEY,`TPLT_CODE` VARCHAR(256) NULL COMMENT '模版代码',`TPLT_NAME` VARCHAR(256) NULL COMMENT '模版名称',`TPLT_DATA` MEDIUMBLOB NULL COMMENT '模版数据',`TPLT_STATUS` CHAR(1) DEFAULT '0' NOT NULL COMMENT '状态(0:启用，1:停用)',`CRT_USER` VARCHAR(256) NULL COMMENT '创建人',`CRT_TS` DATETIME DEFAULT CURRENT_TIMESTAMP NOT NULL COMMENT '创建时间',`UPDATE_USER` VARCHAR(256) NULL COMMENT '更新人',`UPDATE_TS` DATETIME DEFAULT CURRENT_TIMESTAMP NULL ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',`IS_DEL` CHAR(1) DEFAULT '0' NOT NULL COMMENT '是否删除(0:未删, 1:已删)') ENGINE=INNODB DEFAULT CHARSET=UTF8 COMMENT 'EXCEL模版表';

后端需要引入poi和easyExcel的相关依赖

依赖示例：Gradle版本

//poiimplementation group: 'org.apache.poi', name: 'poi', version: '4.1.2'implementation group: 'org.apache.poi', name: 'poi-ooxml', version: '4.1.2'implementation group: 'org.apache.poi', name: 'poi-ooxml-schemas', version: '4.1.2'//easyexcelimplementation 'com.alibaba:easyexcel:3.1.1'

前端luckySheet配置的excel模版Json获取以及后端解析

前端Json示例：

此模版在luckySheet中配置，存到后台的Json字符串如下

解析工具类

import lombok.extern.slf4j.Slf4j;import mons.lang3.StringUtils;import org.apache.poi.hssf.usermodel.HSSFPalette;import org.apache.poi.hssf.usermodel.HSSFWorkbook;import org.apache.poi.hssf.util.HSSFColor;import org.apache.poi.ss.usermodel.Cell;import org.apache.poi.ss.usermodel.CellStyle;import org.apache.poi.ss.usermodel.Color;import org.apache.poi.xssf.usermodel.XSSFColor;/*** @author Administrator*/@Slf4jpublic class ColorUtil {private static final String S = "0123456789ABCDEF";public static Short getColorByStr(String colorStr){HSSFWorkbook workbook = new HSSFWorkbook();HSSFPalette palette = workbook.getCustomPalette();if(colorStr == null){return null;}if(colorStr.toLowerCase().startsWith("rgb")){colorStr=colorStr.toLowerCase().replace("rgb(","").replace(")","");String[] colors=colorStr.split(",");if(colors.length==3){try{int red = Integer.parseInt(colors[0].trim(),16);int green = Integer.parseInt(colors[1].trim(),16);int blue = Integer.parseInt(colors[2].trim(),16);HSSFColor hssfColor=palette.findSimilarColor(red,green,blue);return hssfColor.getIndex();}catch (Exception ex){log.error(ex.toString());return null;}}return null;}if(colorStr.equals("#000")){colorStr="#000000";}if(colorStr!=null && colorStr.length()>=6){try{if(colorStr.length()==8){colorStr=colorStr.substring(2);}if(colorStr.length()==7){colorStr=colorStr.substring(1);}String str2 = colorStr.substring(0,2);String str3 = colorStr.substring(2,4);String str4 = colorStr.substring(4,6);int red = Integer.parseInt(str2,16);int green = Integer.parseInt(str3,16);int blue = Integer.parseInt(str4,16);HSSFColor hssfColor=palette.findSimilarColor(red,green,blue);return hssfColor.getIndex();}catch (Exception ex){log.error(ex.toString());return null;}}return null;}/*** RGB转换成十六进制** @param r* @param g* @param b* @return*/public static String convertRGBToHex(short r, short g, short b) {String hex = "";if (r >= 0 && r < 256 && g >= 0 && g < 256 && b >= 0 && b < 256) {int x, y, z;x = r % 16;r = (short) ((r - x) / 16);y = g % 16;g = (short) ((g - y) / 16);z = b % 16;b = (short) ((b - z) / 16);hex = "#" + S.charAt(r) + S.charAt(x) + S.charAt(g) + S.charAt(y) + S.charAt(b) + S.charAt(z);}return hex;}/*** @param cell 单元格* @return 转换RGB颜色值* @description tint转换RBG* @author zhouhang* @date /4/26*/public static String getFillColorHex(Cell cell) {String fillColorString = null;if (cell != null) {CellStyle cellStyle = cell.getCellStyle();Color color = cellStyle.getFillForegroundColorColor();if (color instanceof XSSFColor) {XSSFColor xssfColor = (XSSFColor) color;byte[] argb = xssfColor.getARGB();fillColorString = convertRGBToHex((short) (argb[1] & 0xFF), (short) (argb[2] & 0xFF), (short) (argb[3] & 0xFF));// TODO: /4/26 添加透明度//if (xssfColor.hasTint()) {//fillColorString += " * " + xssfColor.getTint();//byte[] rgb = xssfColor.getRGBWithTint();//fillColorString += " = [" + (argb[0] & 0xFF) + ", " + (rgb[0] & 0xFF) + ", " + (rgb[1] & 0xFF) + ", " + (rgb[2] & 0xFF) + "]";//}} else if (color instanceof HSSFColor) {HSSFColor hssfColor = (HSSFColor) color;short[] rgb = hssfColor.getTriplet();fillColorString = convertRGBToHex((short) (rgb[0] & 0xFF), (short) (rgb[1] & 0xFF), (short) (rgb[2] & 0xFF));//去除黑色背景if (StringUtils.equals("#000000", fillColorString)) {return null;}}}return fillColorString;}}

import org.apache.poi.ss.usermodel.HorizontalAlignment;import org.apache.poi.ss.usermodel.VerticalAlignment;import java.text.ParseException;import java.text.SimpleDateFormat;import java.util.Calendar;import java.util.Date;import java.util.GregorianCalendar;import java.util.HashMap;import java.util.Map;/**** @author cr*/public class ConstantUtil {/*** 导出。字体转换*/public static Map<Integer,String> ff_IntegerToName=new HashMap<Integer,String>();/*** 导入。字体转换*/public static Map<String,Integer> ff_NameToInteger=new HashMap<String,Integer>();/*** 导入 36种数字格式。注意官方文档的编号不是连续的，22后面直接是37，所以数组中间补14个空值*/public static String[] number_type=null;/*** 导入 36种格式的定义字符串*/public static String[] number_format=null;/*** 数据类型*/public static Map<String,Integer> number_format_map=new HashMap<String,Integer>();static{//格式nf();//字体ff();}private static void nf(){number_type = new String[] {"General","Decimal","Decimal","Decimal","Decimal","Currency","Currency","Currency","Currency","Percentage","Percentage","Scientific","Fraction","Fraction","Date","Date","Date","Date","Time","Time","Time","Time","Time","","","","","","","","","","","","","","","Accounting","Accounting","Accounting","Accounting","Accounting","Currency","Accounting","Currency","Time","Time","Time","Scientific","Text"};number_format = new String[] {"General","0","0.00","#,##0","#,##0.00","$#,##0;($#,##0)","$#,##0;[Red]($#,##0)","$#,##0.00;($#,##0.00)","$#,##0.00;[Red]($#,##0.00)","0%","0.00%","0.00E+00","# ?/?","# ??/??","m/d/yyyy","d-mmm-yy","d-mmm","mmm-yy","h:mm AM/PM","h:mm:ss AM/PM","h:mm","h:mm:ss","m/d/yyyy h:mm","","","","","","","","","","","","","","","#,##0;(#,##0)","#,##0;[Red](#,##0)","#,##0.00;(#,##0.00)","#,##0.00;[Red](#,##0.00)","_ * #,##0_ ;_ * (#,##0)_ ;_ * \"-\"_ ;_ @_","_ $* #,##0_ ;_ $* (#,##0)_ ;_ $* \"-\"_ ;_ @_","_ * #,##0.00_ ;_ * (#,##0.00)_ ;_ * \"-\"??_ ;_ @_","_ $* #,##0.00_ ;_ $* (#,##0.00)_ ;_ $* \"-\"??_ ;_ @_","mm:ss","[h]:mm:ss","mm:ss.0","##0.0E+00","@"};for(int x=0;x<number_format.length;x++){if(number_format[x].length()>0){number_format_map.put(number_format[x].toLowerCase(),x);}}}private static void ff(){//0 微软雅黑、1 宋体（Song）、2 黑体（ST Heiti）、3 楷体（ST Kaiti）、 4仿宋（ST FangSong）、 5 新宋体（ST Song）、// 6 华文新魏、 7华文行楷、 8 华文隶书、 9 Arial、 10 Times New Roman 、11 Tahoma 、12 Verdanaff_IntegerToName.put(0,"微软雅黑");ff_IntegerToName.put(1,"宋体");ff_IntegerToName.put(2,"黑体");ff_IntegerToName.put(3,"楷体");ff_IntegerToName.put(4,"仿宋");ff_IntegerToName.put(5,"新宋体");ff_IntegerToName.put(6,"华文新魏");ff_IntegerToName.put(7,"华文行楷");ff_IntegerToName.put(8,"华文隶书");ff_IntegerToName.put(9,"Arial");ff_IntegerToName.put(10,"Times New Roman");ff_IntegerToName.put(11,"Tahoma");ff_IntegerToName.put(12,"Verdana");//0 微软雅黑、1 宋体（Song）、2 黑体（ST Heiti）、3 楷体（ST Kaiti）、 4仿宋（ST FangSong）、 5 新宋体（ST Song）、// 6 华文新魏、 7华文行楷、 8 华文隶书、 9 Arial、 10 Times New Roman 、11 Tahoma 、12 Verdanaff_NameToInteger.put("微软雅黑",0);ff_NameToInteger.put("宋体",1);ff_NameToInteger.put("Song",1);ff_NameToInteger.put("黑体",2); ff_NameToInteger.put("ST Heiti",2);ff_NameToInteger.put("楷体",3); ff_NameToInteger.put("ST Kaiti",3);ff_NameToInteger.put("仿宋",4); ff_NameToInteger.put("ST FangSong",4);ff_NameToInteger.put("新宋体",5); ff_NameToInteger.put("ST Song",5);ff_NameToInteger.put("华文新魏",6);ff_NameToInteger.put("华文行楷",7);ff_NameToInteger.put("华文隶书",8);ff_NameToInteger.put("Arial",9);ff_NameToInteger.put("Times New Roman",10);ff_NameToInteger.put("Tahoma",11);ff_NameToInteger.put("Verdana",12);}/*** 按自定义格式* @param fa* @return*/public static Integer getNumberFormatMap(String fa){if(number_format_map.containsKey(fa.toLowerCase())){return number_format_map.get(fa.toLowerCase());}return -1;}/*** 获取poi表格垂直对齐 0 中间、1 上、2下* @param i* @return*/public static VerticalAlignment getVerticalType(int i){if(0==i){return VerticalAlignment.CENTER;}else if(1==i){return VerticalAlignment.TOP;}else if(2==i){return VerticalAlignment.BOTTOM;}//默认居中return VerticalAlignment.CENTER;}/*** 获取poi表格水平对齐 0 居中、1 左、2右* @param i* @return*/public static HorizontalAlignment getHorizontaltype(int i){if(2==i){return HorizontalAlignment.RIGHT;}else if(1==i){return HorizontalAlignment.LEFT;}else if(0==i){return HorizontalAlignment.CENTER;}//默认右return HorizontalAlignment.RIGHT;}/*** 文字旋转* 文字旋转角度（0=0,1=45，2=-45，3=竖排文字，4=90，5=-90）* @param i* @return*/public static short getRotation(int i){short t=0;switch (i){case 1:t=45;break;case 2:t=-45;break;case 3:t=255;break;case 4:t=90;break;case 5:t=-90;break;default:t=0;}return t;}private static SimpleDateFormat df_DateTime = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");public static Date stringToDateTime(String date){if(date==null || date.length()==0){return null;}try {return df_DateTime.parse(date);} catch (ParseException e) {return null;}}private static SimpleDateFormat df_Date = new SimpleDateFormat("yyyy-MM-dd");public static Date stringToDate(String date){if(date==null || date.length()==0){return null;}try {return df_Date.parse(date);} catch (ParseException e) {return null;}}public static Date toDate(String numberString) {try{Double _d=Double.parseDouble(numberString);String _s=toDate(_d,"yyyy-MM-dd HH:mm:ss");if(numberString.indexOf(".")>-1){return stringToDate(_s);}else{return stringToDateTime(_s);}}catch (Exception ex){System.out.println(ex.toString()+" "+numberString);}return null;}private static final int SECONDS_PER_MINUTE = 60;private static final int MINUTES_PER_HOUR = 60;private static final int HOURS_PER_DAY = 24;private static final int SECONDS_PER_DAY = (HOURS_PER_DAY * MINUTES_PER_HOUR * SECONDS_PER_MINUTE);/**一天的毫秒数**/private static final long DAY_MILLISECONDS = SECONDS_PER_DAY * 1000L;/**转换方法@parma numberString 要转换的浮点数@parma format 要获得的格式例如"hh:mm:ss"**/public static String toDate(double numberString, String format) {SimpleDateFormat sdFormat = new SimpleDateFormat(format);int wholeDays = (int)Math.floor(numberString);int millisecondsInday = (int)((numberString - wholeDays) * DAY_MILLISECONDS + 0.5);Calendar calendar = new GregorianCalendar();setCalendar(calendar, wholeDays, millisecondsInday, false);return sdFormat.format(calendar.getTime());}private static void setCalendar(Calendar calendar, int wholeDays,int millisecondsInDay, boolean use1904windowing) {int startYear = 1900;int dayAdjust = -1; // Excel thinks 2/29/1900 is a valid date, which it isn'tif (use1904windowing) {startYear = 1904;dayAdjust = 1; // 1904 date windowing uses 1/2/1904 as the first day}else if (wholeDays < 61) {// Date is prior to 3/1/1900, so adjust because Excel thinks 2/29/1900 exists// If Excel date == 2/29/1900, will become 3/1/1900 in Java representationdayAdjust = 0;}calendar.set(startYear,0, wholeDays + dayAdjust, 0, 0, 0);calendar.set(GregorianCalendar.MILLISECOND, millisecondsInDay);}}

import com.alibaba.fastjson.JSONObject;import lombok.extern.slf4j.Slf4j;import org.apache.poi.ss.usermodel.BorderStyle;import org.apache.poi.ss.usermodel.Cell;import org.apache.poi.ss.usermodel.CellStyle;import org.apache.poi.ss.usermodel.CellType;import org.apache.poi.ss.usermodel.DataFormat;import org.apache.poi.ss.usermodel.FillPatternType;import org.apache.poi.ss.usermodel.Font;import org.apache.poi.ss.usermodel.RichTextString;import org.apache.poi.ss.usermodel.Row;import org.apache.poi.ss.usermodel.Sheet;import org.apache.poi.ss.usermodel.Workbook;import org.apache.poi.ss.util.CellRangeAddress;import java.util.ArrayList;import java.util.Calendar;import java.util.Date;import java.util.HashMap;import java.util.List;import java.util.Map;/*** sheet操作** @author Administrator*/@Slf4jpublic class XlsSheetUtil {/*** 导出sheet** @param wb* @param sheetNum* @param dbObject*/public static void exportSheet(Workbook wb, int sheetNum, JSONObject dbObject) {Sheet sheet = wb.createSheet();//设置sheet位置，名称if (dbObject.containsKey("name") && dbObject.get("name") != null) {wb.setSheetName(sheetNum, dbObject.get("name").toString());} else {wb.setSheetName(sheetNum, "sheet" + sheetNum);}//是否隐藏if (dbObject.containsKey("hide") && dbObject.get("hide").toString().equals("1")) {wb.setSheetHidden(sheetNum, true);}//是否当前选中页if (dbObject.containsKey("status") && dbObject.get("status").toString().equals("1")) {sheet.setSelected(true);}//循环数据if (dbObject.containsKey("celldata") && dbObject.get("celldata") != null) {//取到所有单元格集合List<JSONObject> cells_json = (List<JSONObject>) dbObject.get("celldata");Map<Integer, List<JSONObject>> cellMap = cellGroup(cells_json);//循环每一行for (Integer r : cellMap.keySet()) {Row row = sheet.createRow(r);//循环每一列for (JSONObject col : cellMap.get(r)) {createCell(wb, sheet, row, col);}}}setColumAndRow(dbObject, sheet);}/*** 每一个单元格** @param row* @param dbObject*/private static void createCell(Workbook wb, Sheet sheet, Row row, JSONObject dbObject) {if (dbObject.containsKey("c")) {Integer c = getStrToInt(dbObject.get("c"));if (c != null) {Cell cell = row.createCell(c);//取单元格中的v_jsonif (dbObject.containsKey("v")) {//获取v对象Object obj = dbObject.get("v");if (obj == null) {//没有内容return;}//如果v对象直接是字符串if (obj instanceof String) {if (((String) obj).length() > 0) {cell.setCellValue(obj.toString());}return;}//转换v为对象(v是一个对象)JSONObject v_json = (JSONObject) obj;//样式CellStyle style = wb.createCellStyle();cell.setCellStyle(style);/*//bs 边框样式 //bc 边框颜色setBorderStyle(style,v_json,"bs","bc");//bs_t 上边框样式 bc_t 上边框颜色setBorderStyle(style,v_json,"bs_t","bc_t");//bs_b 下边框样式 bc_b 下边框颜色setBorderStyle(style,v_json,"bs_b","bc_b");//bs_l 左边框样式 bc_l 左边框颜色setBorderStyle(style,v_json,"bs_l","bc_l");//bs_r 右边框样式 bc_r 右边框颜色setBorderStyle(style,v_json,"bs_r","bc_r");*//*** 不关注luckySheet是否配置有边框和颜色，默认设置黑色边框*/Integer custom_v = 1;style.setBorderTop(BorderStyle.valueOf(custom_v.shortValue()));style.setBorderBottom(BorderStyle.valueOf(custom_v.shortValue()));style.setBorderLeft(BorderStyle.valueOf(custom_v.shortValue()));style.setBorderRight(BorderStyle.valueOf(custom_v.shortValue()));Short custom_color = ColorUtil.getColorByStr("rgb(0,0,0)");style.setTopBorderColor(custom_color);style.setBottomBorderColor(custom_color);style.setLeftBorderColor(custom_color);style.setRightBorderColor(custom_color);//合并单元格//参数1：起始行参数2：终止行参数3：起始列参数4：终止列//CellRangeAddress region1 = new CellRangeAddress(rowNumber, rowNumber, (short) 0, (short) 11);//mc 合并单元格if (v_json.containsKey("mc")) {//是合并的单元格JSONObject mc = v_json.getJSONObject("mc");if (mc.containsKey("rs") && mc.containsKey("cs")) {//合并的第一个单元格if (mc.containsKey("r") && mc.containsKey("c")) {Integer _rs = getIntByDBObject(mc, "rs") - 1;Integer _cs = getIntByDBObject(mc, "cs") - 1;Integer _r = getIntByDBObject(mc, "r");Integer _c = getIntByDBObject(mc, "c");CellRangeAddress region = new CellRangeAddress(_r.shortValue(),(_r.shortValue() + _rs.shortValue()), _c.shortValue(),(_c.shortValue() + _cs.shortValue()));sheet.addMergedRegion(region);}} else {//不是合并的第一个单元格return;}}//取v值 (在数据类型中处理)//ct 单元格值格式 (fa,t)setFormatByCt(wb, cell, style, v_json);//font设置setCellStyleFont(wb, style, v_json);//bg 背景颜色if (v_json.containsKey("bg")) {String _v = getByDBObject(v_json, "bg");if (_v != null) {Short _color = ColorUtil.getColorByStr(_v);if (_color != null) {// style.setFillBackgroundColor(_color);style.setFillForegroundColor(_color);style.setFillPattern(FillPatternType.SOLID_FOREGROUND);}}}//vt 垂直对齐垂直对齐方式（0=居中，1=上，2=下）if (v_json.containsKey("vt")) {Integer _v = getIntByDBObject(v_json, "vt");if (_v != null && _v >= 0 && _v <= 2) {style.setVerticalAlignment(ConstantUtil.getVerticalType(_v));}}//ht 水平对齐水平对齐方式（0=居中，1=左对齐，2=右对齐）if (v_json.containsKey("ht")) {Integer _v = getIntByDBObject(v_json, "ht");if (_v != null && _v >= 0 && _v <= 2) {style.setAlignment(ConstantUtil.getHorizontaltype(_v));}}//tr 文字旋转文字旋转角度（0=0,1=45，2=-45，3=竖排文字，4=90，5=-90）if (v_json.containsKey("tr")) {Integer _v = getIntByDBObject(v_json, "tr");if (_v != null) {style.setRotation(ConstantUtil.getRotation(_v));}}//tb 文本换行 0 截断、1溢出、2 自动换行// 2：setTextWrapped0和1：IsTextWrapped = trueif (v_json.containsKey("tb")) {Integer _v = getIntByDBObject(v_json, "tb");if (_v != null) {if (_v >= 0 && _v <= 1) {style.setWrapText(false);} else {style.setWrapText(true);}}}//f 公式if (v_json.containsKey("f")) {String _v = getByDBObject(v_json, "f");if (_v.length() > 0) {try {if (_v.startsWith("=")) {cell.setCellFormula(_v.substring(1));} else {cell.setCellFormula(_v);}} catch (Exception ex) {log.error("公式 {};Error:{}", _v, ex.toString());}}}}}}}/*** 设置单元格，宽、高** @param dbObject* @param sheet*/private static void setColumAndRow(JSONObject dbObject, Sheet sheet) {if (dbObject.containsKey("config")) {JSONObject config = dbObject.getJSONObject("config");if (config.containsKey("columlen")) {JSONObject columlen = config.getJSONObject("columlen");if (columlen != null) {for (String k : columlen.keySet()) {Integer _i = getStrToInt(k);Integer _v = getStrToInt(columlen.get(k).toString());if (_i != null && _v != null) {//sheet.setColumnWidth(_i,MSExcelUtil.heightUnits2Pixel(_v.shortValue()))sheet.setColumnWidth(_i, _v.shortValue());}}}}if (config.containsKey("rowlen")) {JSONObject rowlen = config.getJSONObject("rowlen");if (rowlen != null) {for (String k : rowlen.keySet()) {Integer _i = getStrToInt(k);Integer _v = getStrToInt(rowlen.get(k).toString());if (_i != null && _v != null) {Row row = sheet.getRow(_i);if (row != null) {//row.setHeightInPoints(MSExcelUtil.pixel2WidthUnits(_v.shortValue()))row.setHeightInPoints(_v.shortValue());}}}}}}}/*** 单元格字体相关样式** @param wb* @param style* @param dbObject*/private static void setCellStyleFont(Workbook wb, CellStyle style, JSONObject dbObject) {Font font = wb.createFont();style.setFont(font);//ff 字体if (dbObject.containsKey("ff")) {if (dbObject.get("ff") instanceof Integer) {Integer _v = getIntByDBObject(dbObject, "ff");if (_v != null && ConstantUtil.ff_IntegerToName.containsKey(_v)) {font.setFontName(ConstantUtil.ff_IntegerToName.get(_v));}} else if (dbObject.get("ff") instanceof String) {font.setFontName(getByDBObject(dbObject, "ff"));}}//fc 字体颜色if (dbObject.containsKey("fc")) {String _v = getByDBObject(dbObject, "fc");if (_v != null) {Short _color = ColorUtil.getColorByStr(_v);if (_color != null) {font.setColor(_color);}}}//bl 粗体if (dbObject.containsKey("bl")) {Integer _v = getIntByDBObject(dbObject, "bl");if (_v != null) {if (_v.equals(1)) {//是否粗体显示font.setBold(true);} else {font.setBold(false);}}}//it 斜体if (dbObject.containsKey("it")) {Integer _v = getIntByDBObject(dbObject, "it");if (_v != null) {if (_v.equals(1)) {font.setItalic(true);} else {font.setItalic(false);}}}//fs 字体大小if (dbObject.containsKey("fs")) {Integer _v = getStrToInt(getObjectByDBObject(dbObject, "fs"));if (_v != null) {font.setFontHeightInPoints(_v.shortValue());}}//cl 删除线 (导入没有) 0 常规、 1 删除线if (dbObject.containsKey("cl")) {Integer _v = getIntByDBObject(dbObject, "cl");if (_v != null) {if (_v.equals(1)) {font.setStrikeout(true);}}}//ul 下划线if (dbObject.containsKey("ul")) {Integer _v = getIntByDBObject(dbObject, "ul");if (_v != null) {if (_v.equals(1)) {font.setUnderline(Font.U_SINGLE);} else {font.setUnderline(Font.U_NONE);}}}}/*** 设置cell边框颜色样式** @param style 样式* @param dbObject json对象* @param bs 样式* @param bc 样式*/private static void setBorderStyle(CellStyle style, JSONObject dbObject, String bs, String bc) {//bs 边框样式if (dbObject.containsKey(bs)) {Integer _v = getStrToInt(getByDBObject(dbObject, bs));if (_v != null) {//边框没有，不作改变if (bs.equals("bs") || bs.equals("bs_t")) {style.setBorderTop(BorderStyle.valueOf(_v.shortValue()));}if (bs.equals("bs") || bs.equals("bs_b")) {style.setBorderBottom(BorderStyle.valueOf(_v.shortValue()));}if (bs.equals("bs") || bs.equals("bs_l")) {style.setBorderLeft(BorderStyle.valueOf(_v.shortValue()));}if (bs.equals("bs") || bs.equals("bs_r")) {style.setBorderRight(BorderStyle.valueOf(_v.shortValue()));}//bc 边框颜色String _vcolor = getByDBObject(dbObject, bc);if (_vcolor != null) {Short _color = ColorUtil.getColorByStr(_vcolor);if (_color != null) {if (bc.equals("bc") || bc.equals("bc_t")) {style.setTopBorderColor(_color);}if (bc.equals("bc") || bc.equals("bc_b")) {style.setBottomBorderColor(_color);}if (bc.equals("bc") || bc.equals("bc_l")) {style.setLeftBorderColor(_color);}if (bc.equals("bc") || bc.equals("bc_r")) {style.setRightBorderColor(_color);}}}}}/*** 不关注luckySheet是否配置有边框和颜色，默认设置黑色边框*//*Integer custom_v = 1;style.setBorderTop(BorderStyle.valueOf(custom_v.shortValue()));style.setBorderBottom(BorderStyle.valueOf(custom_v.shortValue()));style.setBorderLeft(BorderStyle.valueOf(custom_v.shortValue()));style.setBorderRight(BorderStyle.valueOf(custom_v.shortValue()));Short custom_color = ColorUtil.getColorByStr("rgb(0,0,0)");style.setTopBorderColor(custom_color);style.setBottomBorderColor(custom_color);style.setLeftBorderColor(custom_color);style.setRightBorderColor(custom_color);*/}/*** 设置单元格格式 ct 单元格值格式 (fa,t)** @param cell* @param style* @param dbObject*/private static void setFormatByCt(Workbook wb, Cell cell, CellStyle style, JSONObject dbObject) {if (!dbObject.containsKey("v") && dbObject.containsKey("ct")) {/* 处理以下数据结构{"celldata": [{"c": 0,"r": 8,"v": {"ct": {"s": [{"v": "sdsdgdf\r\ndfgdfg\r\ndsfgdfgdf\r\ndsfgdfg"}],"t": "inlineStr","fa": "General"}}}]}*/JSONObject ct = dbObject.getJSONObject("ct");if (ct.containsKey("s")) {Object s = ct.get("s");if (s instanceof List && ((List) s).size() > 0) {JSONObject _s1 = (JSONObject) ((List) s).get(0);if (_s1.containsKey("v") && _s1.get("v") instanceof String) {dbObject.put("v", _s1.get("v"));style.setWrapText(true);}}}}//String v = ""; //初始化if (dbObject.containsKey("v")) {//v = v_json.get("v").toString();//取到v后，存到poi单元格对象//设置该单元格值//cell.setValue(v);//String v=getByDBObject(v_json,"v");//cell.setValue(v);Object obj = getObjectByDBObject(dbObject, "v");if (obj instanceof Number) {cell.setCellValue(Double.valueOf(obj.toString()));} else if (obj instanceof Double) {cell.setCellValue((Double) obj);} else if (obj instanceof Date) {cell.setCellValue((Date) obj);} else if (obj instanceof Calendar) {cell.setCellValue((Calendar) obj);} else if (obj instanceof RichTextString) {cell.setCellValue((RichTextString) obj);} else if (obj instanceof String) {cell.setCellValue((String) obj);} else {cell.setCellValue(obj.toString());}}if (dbObject.containsKey("ct")) {JSONObject ct = dbObject.getJSONObject("ct");if (ct.containsKey("fa") && ct.containsKey("t")) {//t 0=bool，1=datetime，2=error，3=null，4=numeric，5=string，6=unknownString fa = getByDBObject(ct, "fa"); //单元格格式format定义串String t = getByDBObject(ct, "t"); //单元格格式type类型Integer _i = ConstantUtil.getNumberFormatMap(fa);switch (t) {case "s": {//字符串if (_i >= 0) {style.setDataFormat(_i.shortValue());} else {style.setDataFormat((short) 0);}cell.setCellType(CellType.STRING);break;}case "d": {//日期Date _d = null;String v = getByDBObject(dbObject, "m");if (v.length() == 0) {v = getByDBObject(dbObject, "v");}if (v.length() > 0) {if (v.indexOf("-") > -1) {if (v.indexOf(":") > -1) {_d = ConstantUtil.stringToDateTime(v);} else {_d = ConstantUtil.stringToDate(v);}} else {_d = ConstantUtil.toDate(v);}}if (_d != null) {//能转换为日期cell.setCellValue(_d);DataFormat format = wb.createDataFormat();style.setDataFormat(format.getFormat(fa));} else {//不能转换为日期if (_i >= 0) {style.setDataFormat(_i.shortValue());} else {style.setDataFormat((short) 0);}}break;}case "b": {//逻辑cell.setCellType(CellType.BOOLEAN);if (_i >= 0) {style.setDataFormat(_i.shortValue());} else {DataFormat format = wb.createDataFormat();style.setDataFormat(format.getFormat(fa));}break;}case "n": {//数值cell.setCellType(CellType.NUMERIC);if (_i >= 0) {style.setDataFormat(_i.shortValue());} else {DataFormat format = wb.createDataFormat();style.setDataFormat(format.getFormat(fa));}break;}case "u":case "g": {//general 自动类型//cell.setCellType(CellType._NONE);if (_i >= 0) {style.setDataFormat(_i.shortValue());} else {DataFormat format = wb.createDataFormat();style.setDataFormat(format.getFormat(fa));}break;}case "e": {//错误cell.setCellType(CellType.ERROR);if (_i >= 0) {style.setDataFormat(_i.shortValue());} else {DataFormat format = wb.createDataFormat();style.setDataFormat(format.getFormat(fa));}break;}}}}}/*** 内容按行分组** @param cells* @return*/private static Map<Integer, List<JSONObject>> cellGroup(List<JSONObject> cells) {Map<Integer, List<JSONObject>> cellMap = new HashMap<>(100);for (JSONObject dbObject : cells) {//行号if (dbObject.containsKey("r")) {Integer r = getStrToInt(dbObject.get("r"));if (r != null) {if (cellMap.containsKey(r)) {cellMap.get(r).add(dbObject);} else {List<JSONObject> list = new ArrayList<>(10);list.add(dbObject);cellMap.put(r, list);}}}}return cellMap;}/*** 获取一个k的值** @param b* @param k* @return*/public static String getByDBObject(JSONObject b, String k) {if (b.containsKey(k)) {if (b.get(k) != null && b.get(k) instanceof String) {return b.getString(k);}}return null;}/*** 获取一个k的值** @param b* @param k* @return*/public static Object getObjectByDBObject(JSONObject b, String k) {if (b.containsKey(k)) {if (b.get(k) != null) {return b.get(k);}}return "";}/*** 没有/无法转换返回null** @param b* @param k* @return*/public static Integer getIntByDBObject(JSONObject b, String k) {if (b.containsKey(k)) {if (b.get(k) != null) {try {String _s = b.getString(k).replace("px", "");Double _d = Double.parseDouble(_s);return _d.intValue();} catch (Exception ex) {log.error(ex.getMessage());return null;}}}return null;}/*** 转int** @param str* @return*/private static Integer getStrToInt(Object str) {try {if (str != null) {return Integer.parseInt(str.toString());}return null;} catch (Exception ex) {log.error("String:{};Error:{}", str, ex.getMessage());return null;}}private static Short getStrToShort(Object str) {try {if (str != null) {return Short.parseShort(str.toString());}return null;} catch (Exception ex) {log.error("String:{};Error:{}", str, ex.getMessage());return null;}}}

import com.alibaba.fastjson.JSONObject;import org.apache.poi.hssf.usermodel.HSSFWorkbook;import org.apache.poi.ss.usermodel.Cell;import org.apache.poi.ss.usermodel.Row;import org.apache.poi.ss.usermodel.Sheet;import org.apache.poi.ss.usermodel.Workbook;import org.apache.poi.ss.util.CellRangeAddress;import org.apache.poi.xssf.usermodel.XSSFWorkbook;import org.jetbrains.annotations.NotNull;import java.io.IOException;import java.io.OutputStream;import java.util.HashMap;import java.util.List;import java.util.Map;import java.util.Objects;/*** 使用poi导出xls* @author Administrator*/public class XlsUtil {private final static String MODEL = "{\"c\":0,\"r\":0,\"v\":{\"m\":\"模板\",\"v\":\"模板\",\"bl\":1,\"ct\":{\"t\":\"g\",\"fa\":\"General\"}}}";private final static String BORDER_MODEL = "{\"rangeType\":\"cell\",\"value\":{\"b\":{\"color\":\"rgb(0, 0, 0)\",\"style\":1},\"r\":{\"color\":\"rgb(0, 0, 0)\",\"style\":1},\"col_index\":5,\"t\":{\"color\":\"rgb(0, 0, 0)\",\"style\":1},\"row_index\":7,\"l\":{\"color\":\"rgb(0, 0, 0)\",\"style\":1}}}";/*** 输出文件流* @param outputStream 流* @param isXlsx 是否是xlsx* @param dbObjectList 数据*/public static void exportXlsFile(OutputStream outputStream, Boolean isXlsx,List<JSONObject> dbObjectList) throws IOException {Workbook wb=null;if(isXlsx){wb=new XSSFWorkbook();}else{wb=new HSSFWorkbook();}if(dbObjectList!=null&&dbObjectList.size()>0){for(int x=0;x<dbObjectList.size();x++){XlsSheetUtil.exportSheet(wb,x,dbObjectList.get(x));}}wb.write(outputStream);}}

测试类，测试类中的str值为lucky的Json字符串,自行替换

import com.alibaba.excel.EasyExcel;import com.alibaba.excel.ExcelWriter;import com.alibaba.excel.write.metadata.WriteSheet;import com.alibaba.excel.write.metadata.fill.FillConfig;import com.alibaba.excel.write.style.column.LongestMatchColumnWidthStyleStrategy;import com.alibaba.fastjson.JSON;import com.alibaba.fastjson.JSONObject;import java.io.FileNotFoundException;import java.io.FileOutputStream;import java.io.IOException;import java.io.OutputStream;import java.util.ArrayList;import java.util.HashMap;import java.util.List;import java.util.Map;/*** 测试导出poi 导出 xls* @author Administrator*/public class Test {public static void main(String[] args) {String str = "";//DBObject dbObject=(DBObject) JSON.parse(str);List<JSONObject> lists = (List<JSONObject>) JSON.parse(str);OutputStream out = null;try {out = new FileOutputStream("D:\\JavaEnvironment\\temp\\test.xls");XlsUtil.exportXlsFile(out, false, lists);Map<String, Object> map = new HashMap<>();map.put("name", "小明");map.put("codeTwo", "小明codeTwo");map.put("codeThree", "小明codeThree");map.put("codeFour", "小明codeFour");map.put("codeFive", "小明codeFive");Map<String, Object> map2 = new HashMap<>();map2.put("name", "小花");map2.put("codeTwo", "小花codeTwo");map2.put("codeThree", "小花codeThree");map2.put("codeFour", "小花codeFour");map2.put("codeFive", "小花codeFive小花codeFive小花codeFive小花codeFive小花codeFive小花codeFive小花codeFive小花codeFive");List ls = new ArrayList();ls.add(map);ls.add(map2);List<TestModel> models = new ArrayList<>();TestModel tm1 = new TestModel();tm1.setName("实体");tm1.setCodeOne("实体1");tm1.setCodeTwo("实体2");tm1.setCodeThree("实体3");tm1.setCodeFour("实体4");tm1.setCodeFive("实体5");TestModel tm2 = new TestModel();tm2.setName("对象");tm2.setCodeOne("对象1");tm2.setCodeTwo("对象2");tm2.setCodeThree("对象3");tm2.setCodeFour("对象4");tm2.setCodeFive("对象5");models.add(tm1);models.add(tm2);try (ExcelWriter excelWriter = EasyExcel.write("D:\\JavaEnvironment\\temp\\test1.xls").withTemplate("D:\\JavaEnvironment\\temp\\test.xls").registerWriteHandler(new LongestMatchColumnWidthStyleStrategy()).build()) {WriteSheet writeSheet = EasyExcel.writerSheet().build();// 这里注意入参用了forceNewRow 代表在写入list的时候不管list下面有没有空行都会创建一行，然后下面的数据往后移动。默认是false，会直接使用下一行，如果没有则创建。// forceNewRow 如果设置了true,有个缺点就是他会把所有的数据都放到内存了，所以慎用// 简单的说如果你的模板有list,且list不是最后一行，下面还有数据需要填充就必须设置 forceNewRow=true 但是这个就会把所有数据放到内存会很耗内存// 如果数据量大 list不是最后一行参照下一个FillConfig fillConfig = FillConfig.builder().forceNewRow(Boolean.FALSE).build();excelWriter.fill(ls, fillConfig, writeSheet);excelWriter.fill(models, fillConfig, writeSheet);//excelWriter.fill(data(), fillConfig, writeSheet);//Map<String, Object> map = MapUtils.newHashMap();//map.put("date", "10月9日13:28:28");//map.put("total", 1000);//excelWriter.fill(map, writeSheet);} catch (Exception e) {e.printStackTrace();}} catch (FileNotFoundException e) {e.printStackTrace();} catch (IOException e) {e.printStackTrace();}System.out.println("start");}

测试类用到的实体类

import lombok.Data;/*** 描述** @author * @date /11/4 10:14*/@Datapublic class TestModel {private String name;private String codeOne;private String codeTwo;private String codeThree;private String codeFour;private String codeFive;}