使用开源Luckysheet表格，需要读取xls或者xlsx文件为luckysheet的json格式，java版本，项目中已使用。
package com.util.excel;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.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
*/
@Slf4j
public class ColorUtil {
private static final String S = "0123456789ABCDEF";
public static Short getColorByStr(String colorStr){
HSSFWorkbook workbook = new HSSFWorkbook();
HSSFPalette palette = workbook.getCustomPalette();
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 2021/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: 2021/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;
}
}
package com.util.excel;
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.*;
/**
*
* @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 Verdana
ff_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 Verdana
ff_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't
if (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 representation
dayAdjust = 0;
}
calendar.set(startYear,0, wholeDays + dayAdjust, 0, 0, 0);
calendar.set(GregorianCalendar.MILLISECOND, millisecondsInDay);
}
}
package com.util.excel;
/**
* @author zhouhang
* @description 禁用状态
* @date 2021/2/1
*/
public enum DisabledTypeEnum {
/**
* 启用
*/
ENABLE(0, "启用"),
/**
* 禁用
*/
DISABLE(1, "禁用"),
;
DisabledTypeEnum(Integer index, String name) {
this.index = index;
this.name = name;
}
/**
* index
*/
private final Integer index;
/**
* 名称
*/
private final String name;
public Integer getIndex() {
return index;
}
public String getName() {
return name;
}
}
package com.util.excel;
/**
* @author zhouhang
* @description EXCEL居中方式
* @date 2021/4/26
*/
public enum ExcelCenterStyleEnum {
/**
* 左对齐
*/
LEFT((short) 1, 1, "左对齐"),
/**
* 右对齐
*/
RIGHT((short) 3, 2, "右对齐"),
/**
* 居中
*/
CENTER((short) 2, 0, "居中"),
;
/**
* excel居中code
*/
private final short excelCode;
/**
* 在线文档居中code
*/
private final Integer onlineExcelCode;
/**
* 名称
*/
private final String name;
public Integer getOnlineExcelCode() {
return onlineExcelCode;
}
public String getName() {
return name;
}
public short getExcelCode() {
return excelCode;
}
ExcelCenterStyleEnum(short excelCode, Integer onlineExcelCode, String name) {
this.excelCode = excelCode;
this.onlineExcelCode = onlineExcelCode;
this.name = name;
}
/**
* @param code excel居中样式code
* @return Enum_ExcelCenterStyle
* @description 根据excel居中样式获取在线文档居中样式
* @author zhouhang
* @date 2021/4/26
*/
public static ExcelCenterStyleEnum getExcelCenterStyleByExcelCenterCode(short code) {
for (ExcelCenterStyleEnum value : ExcelCenterStyleEnum.values()) {
if (code == value.getExcelCode()) {
return value;
}
}
return CENTER;
}
}
package com.util.excel;
import com.alibaba.fastjson.JSONObject;
import com.taiji.common.result.TaijiResultException;
import org.apache.commons.collections4.CollectionUtils;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.WorkbookFactory;
import java.io.BufferedInputStream;
import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.util.*;
/**
* @author: hbf
* @Date: 2021/12/16 15:53
*/
public class excelUtils {
/* public static void main(String[] args) {
File file = new File("D://132624109350848131.xlsx");
JSONObject jsonObject = parseExcel(file);
System.out.println(jsonObject);
}*/
public static JSONObject parseExcel(File file) {
List<GridRecordDataModel> modelList;
JSONObject result = new JSONObject();
String fileName = file.getName();
try (BufferedInputStream bufferedInputStream = new BufferedInputStream(new FileInputStream(file))) {
String suffix = fileName.substring(fileName.lastIndexOf("."));
if (Objects.requireNonNull(suffix).endsWith(".xls") || suffix.endsWith(".xlsx")) {
Workbook workbook = WorkbookFactory.create(bufferedInputStream);
//读取EXCEL
modelList = XlsUtil.readExcel(workbook);
// 获取文件名称
String docCode = TimeUtil.getTodayBeginTime() + "#-" + (int) (Math.random() * 100) + "#-" + UUID.randomUUID().toString().replace("-", "");
//插入文档数据
JSONObject jsonObject = new JSONObject();
jsonObject.put("name",fileName);
result.put("info",jsonObject);
result.put("sheets",modelList);
initParseExcel(modelList, docCode);
} else {
throw new TaijiResultException("文件无效");
}
} catch (IOException | InvalidFormatException e) {
e.printStackTrace();
throw new TaijiResultException("Excel 文件解析失败，请检查文件！");
}
return result;
}
private static void initParseExcel(List<GridRecordDataModel> modelList, String docCode) {
int index = 1;
List<GridRecordDataModel> addList = new ArrayList<>();
for (GridRecordDataModel model : modelList) {
model.setList_id(docCode);
if (CollectionUtils.isNotEmpty(model.getCelldata())) {
Map<String, List<JSONObject>> map = new HashMap<>(model.getCelldata().size() / 5);
for (JSONObject data : model.getCelldata()) {
String blockId = JfGridConfigModel.getRange(data.getIntValue("r"), data.getIntValue("c"), model.getRow_col());
List<JSONObject> list = map.get(blockId);
if (Objects.isNull(list)) {
list = new ArrayList<>();
list.add(data);
map.put(blockId, list);
} else {
list.add(data);
}
}
//生成GridRecordDataModel对象
for (Map.Entry<String, List<JSONObject>> entry : map.entrySet()) {
GridRecordDataModel newDataModel = new GridRecordDataModel();
newDataModel.setList_id(docCode);
newDataModel.setBlock_id(entry.getKey());
newDataModel.setIndex(index + "");
newDataModel.setStatus(0);
newDataModel.setIs_delete(DisabledTypeEnum.ENABLE.getIndex());
JSONObject jsonObject = new JSONObject();
jsonObject.put("celldata", entry.getValue());
newDataModel.setJson_data(jsonObject);
addList.add(newDataModel);
}
}
index++;
}
//插入数据
addList.addAll(modelList);
}
}
package com.util.excel;
import com.alibaba.fastjson.JSONObject;
import lombok.Data;
import java.util.List;
/**
* 存储对象类
* @author Administrator
*/
@Data
public class GridRecordDataModel {
/**
* 记录序列
*/
Long id;
/**
* 文档ID
*/
String list_id;
/**
* sheet名称
*/
String name;
/**
* 本记录的行_列
*/
String row_col;
/**
* sheet序号
*/
String index;
/**
* 状态是否当前sheet页
*/
Integer status;
/**
* 块编号 第一块 fblock
*/
String block_id;
/**
* json串
*/
JSONObject json_data;
/**
* 排序位置
*/
Integer order;
/**
* 是否删除
*/
Integer is_delete;
/**
* sheet页数据 未编号分组
*/
List<JSONObject> celldata;
Integer rh_height;
Integer ch_width;
Integer defaultColWidth;
Integer defaultRowHeight;
JSONObject config;
}
package com.piweb.util.excel;
import java.util.Calendar;
import java.util.Date;
/**
* @author zhouhang
* @description TimeUtil
* @date 2021/5/10
*/
public class TimeUtil {
public static int getTodayBeginTime() {
Calendar calendar = Calendar.getInstance();
calendar.setTime(new Date());
calendar.set(Calendar.HOUR_OF_DAY, 0);
calendar.set(Calendar.MINUTE, 0);
calendar.set(Calendar.SECOND, 0);
return (int) (calendar.getTime().getTime() / 1000);
}
}
package com.util.excel;
import com.alibaba.fastjson.JSONObject;
import lombok.extern.slf4j.Slf4j;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import java.util.*;
/**
* sheet操作
*
* @author Administrator
*/
@Slf4j
public 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_json
if (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");
//合并单元格
//参数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");
Short _color = ColorUtil.getColorByStr(_v);
if (_color != null) {
style.setFillBackgroundColor(_color);
}
}
//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：setTextWrapped 0和1：IsTextWrapped = true
if (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");
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);
}
}
}
}
}
}
/**
* 设置单元格格式 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=unknown
String 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;
}
}
}
package com.util.excel;
import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import org.apache.commons.collections4.CollectionUtils;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFCellStyle;
import org.apache.poi.xssf.usermodel.XSSFColor;
import org.apache.poi.xssf.usermodel.XSSFFont;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.jetbrains.annotations.NotNull;
import java.io.IOException;
import java.io.OutputStream;
import java.util.*;
/**
* 使用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}}}";
/**
* 默认行数
*/
private static final int DEFAULT_ROW_INDEX = 84;
/**
* 默认列数
*/
private static final int DEFAULT_COLUMN_INDEX = 64;
/**
* 输出文件流
*
* @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);
}
/**
* @param workbook 工作簿
* @return Map
* @description 读取excel
* @author zhouhang
* @date 2021/4/20
*/
public static List<GridRecordDataModel> readExcel(Workbook workbook) {
List<GridRecordDataModel> list = new ArrayList<>();
Iterator<Sheet> sheetIterator = workbook.sheetIterator();
int sheetIndex = 0;
while (sheetIterator.hasNext()) {
Sheet sheet = sheetIterator.next();
//生成默认MODEL
GridRecordDataModel model;
if (Objects.equals(0, sheetIndex)) {
model = strToModel("", (sheetIndex + 1) + "", 1, sheetIndex);
} else {
model = strToModel("", (sheetIndex + 1) + "", 0, sheetIndex);
}
sheetIndex++;
//读取sheet页
readSheet(sheet, model, workbook);
model.setName(sheet.getSheetName());
model.setConfig(model.getJson_data().getJSONObject("config"));
model.setJson_data(null);
list.add(model);
}
return list;
}
public static GridRecordDataModel strToModel(String list_id, String index, int status, int order) {
String strSheet = "{\"row\":84,\"name\":\"reSheetName\",\"chart\":[],\"color\":\"\",\"index\":\"reIndex\",\"order\":reOrder,\"column\":60,\"config\":{},\"status\":reStatus,\"celldata\":[],\"ch_width\":4347,\"rowsplit\":[],\"rh_height\":2048,\"scrollTop\":0,\"scrollLeft\":0,\"visibledatarow\":[],\"visibledatacolumn\":[],\"jfgird_select_save\":[],\"jfgrid_selection_range\":{}}";
strSheet = strSheet.replace("reSheetName", "Sheet" + index).replace("reIndex", index).replace("reOrder", order + "").replace("reStatus", status + "");
JSONObject bson = JSONObject.parseObject(strSheet);
GridRecordDataModel model = new GridRecordDataModel();
model.setBlock_id("fblock");
model.setRow_col("5_5");
model.setIndex(index);
model.setIs_delete(0);
model.setJson_data(bson);
model.setStatus(status);
model.setOrder(order);
model.setList_id(list_id);
model.setCh_width(4347);
model.setRh_height(2048);
model.setDefaultColWidth(70);
model.setDefaultRowHeight(18);
return model;
}
/**
* @param sheet sheet页
* @param model 数据存储
* @param workbook excel
* @description 读取单个sheet页
* @author zhouhang
* @date 2021/4/20
*/
private static void readSheet(Sheet sheet, GridRecordDataModel model, Workbook workbook) {
//excel数据集合
List<JSONObject> dataList = new ArrayList<>();
model.setCelldata(dataList);
//获取行迭代器
Iterator<Row> rowIterator = sheet.rowIterator();
//获取合并单元格信息
Map<String, String> rangeMap = getRangeMap(sheet);
//记录最大列
int maxCellNumber = 0;
int maxRowNumber = 0;
//列宽
JSONObject columnLenObj = new JSONObject();
//行高
JSONObject rowLenObj = new JSONObject();
//读取文档
while (rowIterator.hasNext()) {
Row row = rowIterator.next();
int rowLen = ((int) row.getHeight()) / 20;
if (rowLen == 0) {
rowLen = 30;
}
rowLenObj.put(row.getRowNum() + "", rowLen);
Iterator<Cell> cellIterator = row.cellIterator();
maxRowNumber = row.getRowNum();
while (cellIterator.hasNext()) {
//"{\"c\":0,\"r\":0,\"v\":{\"m\":\"模板\",\"v\":\"模板\",\"bl\":1,\"ct\":{\"t\":\"g\",\"fa\":\"General\"}}}";
JSONObject dataModel = JSONObject.parseObject(MODEL);
//初始化默认单元格内容
Cell cell = cellIterator.next();
int columnLen = sheet.getColumnWidth(cell.getColumnIndex()) / 25;
if (columnLen == 0) {
columnLen = 73;
}
columnLenObj.put(cell.getColumnIndex() + "", columnLen);
//修改最大列
maxCellNumber = Math.max(cell.getColumnIndex(), maxCellNumber);
//设置行列
dataModel.put("c", cell.getColumnIndex());
dataModel.put("r", row.getRowNum());
//获取单元格内容
switch (cell.getCellTypeEnum()) {
case STRING:
dataModel.getJSONObject("v").put("m", cell.getStringCellValue());
dataModel.getJSONObject("v").put("v", cell.getStringCellValue());
break;
case NUMERIC:
dataModel.getJSONObject("v").put("m", cell.getNumericCellValue());
dataModel.getJSONObject("v").put("v", cell.getNumericCellValue());
break;
case BLANK:
dataModel.getJSONObject("v").put("m", "");
dataModel.getJSONObject("v").put("v", "");
break;
case BOOLEAN:
dataModel.getJSONObject("v").put("m", cell.getBooleanCellValue());
dataModel.getJSONObject("v").put("v", cell.getBooleanCellValue());
break;
case ERROR:
dataModel.getJSONObject("v").put("m", cell.getErrorCellValue());
dataModel.getJSONObject("v").put("v", cell.getErrorCellValue());
break;
default:
dataModel.getJSONObject("v").put("m", "");
dataModel.getJSONObject("v").put("v", "");
}
//设置单元格合并标记
dealWithCellMarge(rangeMap, row, cell, dataModel);
//设置单元格样式、合并单元格信息
dealWithExcelStyle(model, dataModel, cell, sheet, workbook);
dataList.add(dataModel);
}
}
//设置最大行、列
model.getJson_data().put("column", Math.max(maxCellNumber, DEFAULT_COLUMN_INDEX));
model.getJson_data().put("row", Math.max(maxRowNumber, DEFAULT_ROW_INDEX));
//设置行高、列宽
model.getJson_data().getJSONObject("config").put("columnlen", columnLenObj);
model.getJson_data().getJSONObject("config").put("rowlen", rowLenObj);
}
/**
* @param sheet sheet页信息
* @return Map<String, String> 单元格合并信息
* @description 获取合并单元格信息 所有合并单元的MAP
* @author zhouhang
* @date 2021/4/21
*/
@NotNull
private static Map<String, String> getRangeMap(Sheet sheet) {
List<CellRangeAddress> rangeAddressList = sheet.getMergedRegions();
Map<String, String> rangeMap = new HashMap<>(rangeAddressList.size() * 5);
for (CellRangeAddress cellAddresses : rangeAddressList) {
for (int i = cellAddresses.getFirstRow(); i <= cellAddresses.getLastRow(); i++) {
for (int j = cellAddresses.getFirstColumn(); j <= cellAddresses.getLastColumn(); j++) {
if (i == cellAddresses.getFirstRow() && j == cellAddresses.getFirstColumn()) {
//单元格合并初始值特殊标记
rangeMap.put(i + "_" + j, cellAddresses.getFirstRow() + "_" + cellAddresses.getFirstColumn() + "_" + cellAddresses.getLastRow() + "_" + cellAddresses.getLastColumn());
} else {
rangeMap.put(i + "_" + j, cellAddresses.getFirstRow() + "_" + cellAddresses.getFirstColumn());
}
}
}
}
return rangeMap;
}
/**
* @param rangeMap 合并信息
* @param row 行信息
* @param cell 单元格
* @param dataModel 单元格数据存储信息
* @description 设置单元格合并标记
* @author zhouhang
* @date 2021/4/21
*/
private static void dealWithCellMarge(Map<String, String> rangeMap, Row row, Cell cell, JSONObject dataModel) {
if (rangeMap.containsKey(row.getRowNum() + "_" + cell.getColumnIndex())) {
String margeValue = rangeMap.get(row.getRowNum() + "_" + cell.getColumnIndex());
JSONObject mcData = new JSONObject();
String[] s = margeValue.split("_");
mcData.put("r", Integer.parseInt(s[0]));
mcData.put("c", Integer.parseInt(s[1]));
if (s.length == 4) {
mcData.put("rs", Integer.parseInt(s[2]) - Integer.parseInt(s[0]) + 1);
mcData.put("cs", Integer.parseInt(s[3]) - Integer.parseInt(s[1]) + 1);
}
dataModel.getJSONObject("v").put("mc", mcData);
}
}
/**
* @param model sheet页信息
* @param dataModel 单元格信息
* @param cell 单元格
* @param sheet sheet页数据
* @param workbook excel
* @description 获取单元格样式，设置单元格样式
* @author zhouhang
* @date 2021/4/21
*/
private static void dealWithExcelStyle(GridRecordDataModel model, JSONObject dataModel, Cell cell, Sheet sheet, Workbook workbook) {
//设置单元格合并信息
dealWithExcelMerge(model, sheet);
//设置字体样式
setFontStyle(dataModel, workbook, cell);
//设置单元格样式
dealWithBorderStyle(model, cell, workbook);
}
/**
* @param model 在线表格存储单元
* @param cell cell
* @param workbook workbook
* @description 设置单元格样式
* @author zhouhang
* @date 2021/4/22
*/
private static void dealWithBorderStyle(GridRecordDataModel model, Cell cell, Workbook workbook) {
CellStyle cellStyle = cell.getCellStyle();
//判断是否存在边框
if (cellStyle.getBorderBottomEnum().getCode() > 0 || cellStyle.getBorderBottomEnum().getCode() > 0 ||
cellStyle.getBorderRightEnum().getCode() > 0 || cellStyle.getBorderRightEnum().getCode() > 0) {
JSONObject border = JSONObject.parseObject(BORDER_MODEL);
border.getJSONObject("value").put("row_index", cell.getRowIndex());
border.getJSONObject("value").put("col_index", cell.getColumnIndex());
//xlsx
if (cellStyle instanceof XSSFCellStyle) {
XSSFCellStyle xssfCellStyle = (XSSFCellStyle) cellStyle;
if (Objects.equals((short) 0, cellStyle.getBorderTopEnum().getCode())) {
border.getJSONObject("value").remove("t");
} else {
border.getJSONObject("value").getJSONObject("t").put("color", dealWithRbg(xssfCellStyle.getTopBorderXSSFColor().getRGB()));
}
if (Objects.equals((short) 0, cellStyle.getBorderRightEnum().getCode())) {
border.getJSONObject("value").remove("r");
} else {
border.getJSONObject("value").getJSONObject("r").put("color", dealWithRbg(xssfCellStyle.getRightBorderXSSFColor().getRGB()));
}
if (Objects.equals((short) 0, cellStyle.getBorderLeftEnum().getCode())) {
border.getJSONObject("value").remove("l");
} else {
border.getJSONObject("value").getJSONObject("l").put("color", dealWithRbg(xssfCellStyle.getLeftBorderXSSFColor().getRGB()));
}
if (Objects.equals((short) 0, cellStyle.getBorderBottomEnum().getCode())) {
border.getJSONObject("value").remove("b");
} else {
border.getJSONObject("value").getJSONObject("b").put("color", dealWithRbg(xssfCellStyle.getBottomBorderXSSFColor().getRGB()));
}
} else if (cellStyle instanceof HSSFCellStyle) {
//xls
HSSFWorkbook hssfWorkbook = (HSSFWorkbook) workbook;
HSSFCellStyle hssfCellStyle = (HSSFCellStyle) cellStyle;
if (Objects.equals((short) 0, cellStyle.getBorderTopEnum().getCode())) {
border.getJSONObject("value").remove("t");
} else {
HSSFColor color = hssfWorkbook.getCustomPalette().getColor(hssfCellStyle.getTopBorderColor());
border.getJSONObject("value").getJSONObject("t").put("color", dealWithRbgShort(color.getTriplet()));
}
if (Objects.equals((short) 0, cellStyle.getBorderRightEnum().getCode())) {
border.getJSONObject("value").remove("r");
} else {
HSSFColor color = hssfWorkbook.getCustomPalette().getColor(hssfCellStyle.getRightBorderColor());
border.getJSONObject("value").getJSONObject("r").put("color", dealWithRbgShort(color.getTriplet()));
}
if (Objects.equals((short) 0, cellStyle.getBorderLeftEnum().getCode())) {
border.getJSONObject("value").remove("l");
} else {
HSSFColor color = hssfWorkbook.getCustomPalette().getColor(hssfCellStyle.getLeftBorderColor());
border.getJSONObject("value").getJSONObject("l").put("color", dealWithRbgShort(color.getTriplet()));
}
if (Objects.equals((short) 0, cellStyle.getBorderBottomEnum().getCode())) {
border.getJSONObject("value").remove("b");
} else {
HSSFColor color = hssfWorkbook.getCustomPalette().getColor(hssfCellStyle.getBottomBorderColor());
border.getJSONObject("value").getJSONObject("b").put("color", dealWithRbgShort(color.getTriplet()));
}
}
JSONArray borderInfo = model.getJson_data().getJSONObject("config").getJSONArray("borderInfo");
if (Objects.isNull(borderInfo)) {
borderInfo = new JSONArray();
model.getJson_data().getJSONObject("config").put("borderInfo", borderInfo);
}
borderInfo.add(border);
}
}
/**
* @param rgb RBG short
* @return rbg(0, 0, 0)
* @description 转换RBG rbg(0,0,0)
* @author zhouhang
* @date 2021/4/26
*/
private static String dealWithRbgShort(short[] rgb) {
return getRbg(Objects.nonNull(rgb), rgb[0], rgb[1], rgb[2]);
}
@NotNull
private static String getRbg(boolean b2, short r, short b, short g) {
if (b2) {
return "rgb(" + (r & 0xFF) + ", " + (b & 0xFF) + ", " + (g & 0xFF) + ")";
} else {
return "rgb(0, 0, 0)";
}
}
/**
* @param rgb RBG byte
* @return rbg(0, 0, 0)
* @description 转换RBG rbg(0,0,0)
* @author zhouhang
* @date 2021/4/26
*/
private static String dealWithRbg(byte[] rgb) {
if (Objects.isNull(rgb)) {
return "rgb(0, 0, 0)";
}
short[] shorts = new short[]{rgb[0], rgb[1], rgb[2]};
return getRbg(true, shorts[0], shorts[1], shorts[2]);
}
/**
* @param dataModel 单元格内容
* @param workbook workbook
* @param cell cell
* @description s设置字体样式
* @author zhouhang
* @date 2021/4/21
*/
private static void setFontStyle(JSONObject dataModel, Workbook workbook, Cell cell) {
CellStyle cellStyle = cell.getCellStyle();
Font font = workbook.getFontAt(cellStyle.getFontIndex());
JSONObject v = dataModel.getJSONObject("v");
//ht 水平对齐 水平对齐方式（0=居中，1=左对齐，2=右对齐） excel:左：1 中：2 右：3 未设置：0
v.put("ht", ExcelCenterStyleEnum.getExcelCenterStyleByExcelCenterCode(cellStyle.getAlignmentEnum().getCode()).getOnlineExcelCode());
//bl 字体加粗设置
v.put("bl", font.getBold() ? 1 : 0);
//lt 斜体
v.put("it", font.getItalic() ? 1 : 0);
//ff 字体
v.put("ff", font.getFontName());
//fc 字体颜色
if (font instanceof HSSFFont) {
HSSFFont hssfFont = (HSSFFont) font;
HSSFColor hssfColor = hssfFont.getHSSFColor((HSSFWorkbook) workbook);
if (Objects.nonNull(hssfColor)) {
v.put("fc", ColorUtil.convertRGBToHex(hssfColor.getTriplet()[0], hssfColor.getTriplet()[1], hssfColor.getTriplet()[2]));
}
} else {
XSSFFont xssfFont = (XSSFFont) font;
XSSFColor xssfColor = xssfFont.getXSSFColor();
if (Objects.nonNull(xssfColor)) {
v.put("fc", "#" + xssfColor.getARGBHex().substring(2));
}
}
//fs 字体大小
v.put("fs", font.getFontHeightInPoints());
//cl 删除线
v.put("cl", font.getStrikeout() ? 1 : 0);
//ul 下划线
v.put("un", font.getUnderline());
//背景色
String fillColorHex = ColorUtil.getFillColorHex(cell);
if (Objects.nonNull(fillColorHex)) {
v.put("bg", fillColorHex);
}
}
/**
* @param model sheet页信息
* @param sheet sheet页
* @description 设置单元格合并信息
* @author zhouhang
* @date 2021/4/21
*/
private static void dealWithExcelMerge(GridRecordDataModel model, Sheet sheet) {
if (CollectionUtils.isNotEmpty(sheet.getMergedRegions())) {
//{"color":"","list_id":"","column":60,"index":"1","jfgird_select_save":[],"rh_height":1790,"visibledatacolumn":[],"scrollTop":0,"block_id":"fblock","rowsplit":[],"visibledatarow":[],"jfgrid_selection_range":{},"name":"Sheet1","celldata":[],"ch_width":4748,"row":84,"scrollLeft":0,"id":364598,"chart":[],"config":{},"order":0,"status":1}
JSONObject jsonObject = model.getJson_data();
JSONObject config = jsonObject.getJSONObject("config");
JSONObject merge = new JSONObject();
for (CellRangeAddress mergedRegion : sheet.getMergedRegions()) {
JSONObject mergeBase = new JSONObject();
mergeBase.put("r", mergedRegion.getFirstRow());
mergeBase.put("c", mergedRegion.getFirstColumn());
mergeBase.put("rs", mergedRegion.getLastRow() - mergedRegion.getFirstRow() + 1);
mergeBase.put("cs", mergedRegion.getLastColumn() - mergedRegion.getFirstColumn() + 1);
merge.put(mergedRegion.getFirstRow() + "_" + mergedRegion.getFirstColumn(), mergeBase);
}
config.put("merge", merge);
}
}
}
package com.util.excel;
import com.alibaba.fastjson.JSONObject;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.lang3.StringUtils;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
/**
* @author Administrator
*/
@Slf4j
public class JfGridConfigModel {
/**
* 表名
*/
public static final String TABLENAME="luckysheet";
/**
* 每一块的行、列范围
*/
public static Integer row_size;
public static Integer col_size;
/**
* 第一块只保存二维数据以外的东西，其他“列号_行号”
*/
public static final String FirstBlockID="fblock";
/**
* 默认第一块的编号
*/
private static String FirstBlockId="";
static {
try {
//获取默认第一块的编号
FirstBlockId=JfGridConfigModel.FirstBlockID;
} catch (Exception e) {
log.error(e.getMessage());
}
}
/**
* 返回设置的块范围
* @return
*/
public static String getRowCol(){
return row_size+"_"+col_size;
}
private static Integer getRow(String rowCol){
if(StringUtils.isBlank(rowCol)){
return row_size;
}
try{
return Integer.parseInt(rowCol.split("_")[0]);
}catch (Exception ex){
return row_size;
}
}
private static Integer getCol(String rowCol){
if(StringUtils.isBlank(rowCol)){
return col_size;
}
try{
return Integer.parseInt(rowCol.split("_")[1]);
}catch (Exception ex){
return col_size;
}
}
/**
* 获取块的范围
* @param r 当前行
* @param c 当前列
* @param rowSize 行范围
* @param colSize 列范围
* @return
*/
public static String getRange(Integer r,Integer c,Integer rowSize,Integer colSize){
String _r=r/rowSize+"";
String _c=c/colSize+"";
String _result=_r+"_"+_c;
return _result;
}
public static String getRange(Integer r,Integer c,String rowCol){
return getRange(r,c,getRow(rowCol),getCol(rowCol));
}
/**
* 获取块的范围
* @param bson
* @param rowSize
* @param colSize
* @return
*/
private static String getRange(JSONObject bson, Integer rowSize, Integer colSize){
if(bson.containsKey("r") && bson.containsKey("c")){
try{
//单元格的行号
Integer _r=Integer.parseInt(bson.get("r").toString());
//单元格的列号
Integer _c=Integer.parseInt(bson.get("c").toString());
return getRange(_r,_c,rowSize,colSize);
}catch (Exception ex){
log.error(ex.toString());
return null;
}
}
return null;
}
/**
* 单个sheet数据拆分成多个(使用默认块大小)
* @param sheet 一个sheet
*/
public static List<JSONObject> toDataSplit(String rowCol, JSONObject sheet) {
return toDataSplit(getRow(rowCol),getCol(rowCol),sheet);
}
public static Integer getSheetCount(List<JSONObject> dbObject){
int i=0;
if(dbObject!=null && dbObject.size()>0){
for(JSONObject b:dbObject){
if(b.containsKey("block_id") && FirstBlockID.equals(b.get("block_id"))){
i++;
}
}
}
return i;
}
/**
* 单个sheet数据拆分成多个
* @param rowSize 行数量
* @param colSize 列数量
* @param sheet 一个sheet
*/
private static List<JSONObject> toDataSplit(Integer rowSize,Integer colSize,JSONObject sheet){
List<JSONObject> list=new ArrayList<JSONObject>();
if(sheet!=null && sheet.containsKey("celldata")){
//单元格数据
List<JSONObject> celldata=(List<JSONObject>)sheet.get("celldata");
//相同的索引
Object index=sheet.get("index");
//序号
Object list_id=null;
if(sheet.containsKey("list_id")){
list_id=sheet.get("list_id");
}
//Object order=sheet.get("order");//相同的位置
//k 行号+列号 v 位置_datas下标
Map<String,Integer> pos=new HashMap<String, Integer>();
//分组的数据
List<List<JSONObject>> datas=new ArrayList<List<JSONObject>>();
if(celldata!=null && celldata.size()>0){
for(JSONObject bson:celldata){
//获取到位置
String _pos=getRange(bson,rowSize,colSize);
if(_pos!=null){
//获取到数据集合
List<JSONObject> _data=null;
if(pos.containsKey(_pos)){
//获取对应集合
_data=datas.get(pos.get(_pos));
}else{
_data=new ArrayList<JSONObject>();
//保存位置信息
pos.put(_pos,datas.size());
//添加集合
datas.add(_data);
}
//添加新数据
_data.add(bson);
}
}
}
//替换原始的数据
//if(pos.containsKey(FirstBlockID)){
// sheet.put("celldata",datas.get(pos.get(FirstBlockID)));
//}
if(sheet.containsKey("_id")){
sheet.remove("_id");
}
sheet.put("celldata",new ArrayList());
list.add(sheet);
for(String _pos:pos.keySet()){
//if(_pos.equals(FirstBlockID)){
// continue;
//}
//获取对应集合
List<JSONObject> _data=datas.get(pos.get(_pos));
JSONObject _sheet=new JSONObject();
_sheet.put("block_id",_pos);
_sheet.put("celldata",_data);
_sheet.put("index",index);
if(list_id!=null){
_sheet.put("list_id",list_id);
}
list.add(_sheet);
//_sheet.put("order",order);
}
}else{
list.add(sheet);
}
return list;
}
}
1万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
