将不规则EXCEL表格以json形式导出数据——工具类（代码可用）

朴灿烈づ我的快乐病毒、 2023-02-24 03:59 61阅读 0赞

package com.sgcc.custom.common.utils.excel.yupont;
    
    import com.alibaba.fastjson.JSONArray;
    import com.alibaba.fastjson.JSONObject;
    import com.sgcc.custom.common.utils.ResultInDBSaver;
    import com.yupont.util.DbUtil;
    import lombok.extern.slf4j.Slf4j;
    import org.apache.poi.hssf.usermodel.*;
    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.springframework.web.multipart.MultipartFile;
    
    import java.io.File;
    import java.io.FileInputStream;
    import java.io.IOException;
    import java.io.InputStream;
    import java.sql.Connection;
    import java.text.DecimalFormat;
    import java.text.SimpleDateFormat;
    import java.util.*;
    @Slf4j
    public class POIExcelToHandsontable { 
    
        private Integer uploadlogid;
    
    
    
        public static Workbook getWorkBook(MultipartFile file) { 
    
            //获得文件名
    
            String fileName = file.getOriginalFilename();
    
            //创建Workbook工作薄对象，表示整个excel
    
            Workbook workbook = null;
    
            try { 
    
                //获取excel文件的io流
    
                InputStream is = file.getInputStream();
    
                //根据文件后缀名不同(xls和xlsx)获得不同的Workbook实现类对象
    
                if (fileName.endsWith("xls")) { 
    
                    //excel 2003版本
    
                    workbook = new HSSFWorkbook(is);
    
                } else if (fileName.endsWith("xlsx")) { 
    
                    //excel 2007版本
    
                    workbook = new XSSFWorkbook(is);
    
                }
    
            } catch (IOException e) { 
    
                log.info(e.getMessage());
    
            }
    
            return workbook;
    
        }
    
        /** * 程序入口方法 * @param file 文件 * @param isWithStyle 是否需要表格样式 包含 字体 颜色 边框 对齐方式 * @return <table>...</table> 字符串 */
        public JSONArray readExcelToHandson(MultipartFile file, boolean isWithStyle){ 
    
            InputStream is = null;
            JSONArray arr = new JSONArray();
            try { 
               // String absolutePath = new File(filePath).getAbsolutePath();
    
                Workbook wb = getWorkBook(file);
                if (wb instanceof XSSFWorkbook) { 
                    XSSFWorkbook xWb = (XSSFWorkbook) wb;
                    arr= getExcelInfo(xWb,isWithStyle);
                }else if(wb instanceof HSSFWorkbook){ 
                    HSSFWorkbook hWb = (HSSFWorkbook) wb;
                    arr = getExcelInfo(hWb,isWithStyle);
                }
            } catch (Exception e) { 
                e.printStackTrace();
            }finally{ 
                try { 
                    if(is != null) is.close();
                } catch (IOException e) { 
                    e.printStackTrace();
                }
            }
            return arr;
        }
    
    
    
        private JSONArray getExcelInfo(Workbook wb,boolean isWithStyle){ 
            /* 用于第二步中修改excel中的值后回显修改后的值 end */
            JSONArray arr = new JSONArray();
            for(int i = 0;i< wb.getNumberOfSheets();i++){ 
                JSONObject jsonObject = new JSONObject();
                List<List> datas = new ArrayList<>();//Excel数据
                JSONArray array = new JSONArray();//Excel样式
                Sheet sheet = wb.getSheetAt(i);//获取每一个Sheet页的内容
                int lastRowNum = sheet.getLastRowNum();
                //0是记录跨域内容
                Map<String, String> map[] = getRowSpanColSpanMap(sheet);//获取合并单元格的信息
                Row row = null;        //兼容
                Cell cell = null;    //兼容
                boolean isContainSerial = false;//是否含有序号标志（防止表格中的序号值变为double值）
                int num = 0;
                List<Map<String,Object>> mergeCell = new ArrayList<>();
                int maxLength =0;
                for (int rowNum = sheet.getFirstRowNum(); rowNum <= lastRowNum; rowNum++) { 
                    row = sheet.getRow(rowNum);
                    if (row == null) { 
                        continue;
                    }
    
                    JSONObject style= null;
                    List<String> content = new ArrayList<>();
                    int lastColNum = row.getLastCellNum();
                    if(lastColNum>maxLength){ 
                        maxLength=lastColNum;
                    }
                    for (int colNum = 0; colNum < lastColNum; colNum++) { 
                        cell = row.getCell(colNum);
                        if (cell == null) {     //特殊情况 空白的单元格会返回null
                            content.add("");
                            continue;
                        }
                        style = new JSONObject();
                        String stringValue = ParserUtil.getCellValue(cell);
                        String tmp = ParserUtil.getCellValue(cell);
                        /* 特殊形况，防止序号列的值变为double start */
                        if(isContainSerial && num == colNum){ 
                            try { 
                                stringValue = stringValue.substring(0,stringValue.indexOf("."));
                            } catch (Exception e){ 
                                stringValue = tmp;
                            }
                        }
                        if("序号".equals(cell.toString())){ 
                            isContainSerial = true;
                            num = colNum;
                        }
                        content.add(stringValue);
                        //如果当前cell是跨域
                        if (map[0].containsKey(rowNum + "," + colNum)) { 
                            String pointString = map[0].get(rowNum + "," + colNum);
                            map[0].remove(rowNum + "," + colNum);
    
                            int bottomeRow = Integer.valueOf(pointString.split(",")[0]);//最后一行
                            int bottomeCol = Integer.valueOf(pointString.split(",")[1]);//最后一列
                            int rowSpan = bottomeRow - rowNum + 1;//最后一行减去当前行
                            int colSpan = bottomeCol - colNum + 1;
                            Map<String,Object> mapcell = new HashMap<>();
                            mapcell.put("row",rowNum);
                            mapcell.put("col",colNum);
                            mapcell.put("rowspan",rowSpan);
                            mapcell.put("colspan",colSpan);
                            mergeCell.add(mapcell);
                        // style.put("mergeCells",mapcell);
                            List<Integer> position = new ArrayList<>();
                            position.add(rowNum);
                            position.add(colNum);
                            position.add(rowNum+1);
                            position.add(colNum+1);
                            //判断是否需要样式，给合并单元格添加样式
                            if(isWithStyle){ 
                                dealExcelStyle(wb, sheet, cell, style,position);//处理单元格样式
                            }
                        } else if (map[1].containsKey(rowNum + "," + colNum)) { 
                            map[1].remove(rowNum + "," + colNum);
                            continue;
                        } else { 
                            //判断是否需要样式，给非合并单元格添加样式
                            if(isWithStyle){ 
                                List<Integer> position = new ArrayList<>();
                                position.add(rowNum);
                                position.add(colNum);
                                position.add(rowNum+1);
                                position.add(colNum+1);
    
                                dealExcelStyle(wb, sheet, cell, style,position);//处理单元格样式
    
    
                            }
                        }
                        if (stringValue == null || "".equals(stringValue.trim())) { 
                            //sb.append("   ");
                        } else { 
                            // 将ascii码为160的空格转换为html下的空格（ ）
                            // sb.append(stringValue.replace(String.valueOf((char) 160)," "));
                        }
                        if(!style.isEmpty()){ 
                            array.add(style);
                            jsonObject.put("styles",array);
                        }
                    }
                    datas.add(content);
                }
               for(List list:datas){ 
                   if(list.size()<maxLength){ 
                       int  gapValue =maxLength-list.size();
                       for(int z=0;z<gapValue;z++){ 
                           list.add("");
                       }
                   }
               }
                jsonObject.put("mergeCells",mergeCell);
                jsonObject.put("data",datas);
                jsonObject.put("name",sheet.getSheetName());
                jsonObject.put("sheetNum",i);
                arr.add(jsonObject);
            }
            return arr;
        }
    
        private static Map<String, String>[] getRowSpanColSpanMap(Sheet sheet) { 
    
            Map<String, String> map0 = new HashMap<String, String>();
            Map<String, String> map1 = new HashMap<String, String>();
            int mergedNum = sheet.getNumMergedRegions();
            CellRangeAddress range = null;
            for (int i = 0; i < mergedNum; i++) { 
                range = sheet.getMergedRegion(i);//合并单元格
                int topRow = range.getFirstRow();//第一行
                int topCol = range.getFirstColumn();//第一列
                int bottomRow = range.getLastRow();//最后一行
                int bottomCol = range.getLastColumn();//最后一列
                map0.put(topRow + "," + topCol, bottomRow + "," + bottomCol);//key为顶部行+顶部列 ，值为底部列+底部行
                int tempRow = topRow;
                while (tempRow <= bottomRow) { //初始行小于底部行 正常
                    int tempCol = topCol;
                    while (tempCol <= bottomCol) { //初始列小于底部行 正常
                        map1.put(tempRow + "," + tempCol, "");//第一行，第一列
                        tempCol++;
                    }
                    tempRow++;
                }
                map1.remove(topRow + "," + topCol);//排出异常的值
            }
            Map[] map = {  map0, map1 };
            return map;
        }
    
    
        /** * 获取表格单元格Cell内容 * @param cell * @return */
        private static String getCellValue(Cell cell) { 
    
            String result = new String();
            if(cell.getCellType().equals(CellType.NUMERIC)){ // 数字类型
                if (HSSFDateUtil.isCellDateFormatted(cell)) { // 处理日期格式、时间格式
                    SimpleDateFormat sdf = null;
                    if (cell.getCellStyle().getDataFormat() == HSSFDataFormat.getBuiltinFormat("h:mm")) { 
                        sdf = new SimpleDateFormat("HH:mm");
                    } else { // 日期
                        sdf = new SimpleDateFormat("yyyy-MM-dd");
                    }
                    Date date = cell.getDateCellValue();
                    result = sdf.format(date);
                } else if (cell.getCellStyle().getDataFormat() == 58) { 
                    // 处理自定义日期格式：m月d日(通过判断单元格的格式id解决，id的值是58)
                    SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
                    double value = cell.getNumericCellValue();
                    Date date = DateUtil
                            .getJavaDate(value);
                    result = sdf.format(date);
                } else { 
                    double value = cell.getNumericCellValue();
                    CellStyle style = cell.getCellStyle();
                    DecimalFormat format = new DecimalFormat();
                        /*String temp = style.getDataFormatString(); // 单元格设置成常规 if (temp.equals("General")) { format.applyPattern("#"); }*/
                    //result = format.format(value);
                    result= String.valueOf(value);
                    return result;
                }
            }
            else if(cell.getCellType().equals(CellType.NUMERIC)){ 
                result = "";
                return result;
            }
            else { 
                result = "";
            }
            return result;
        }
    
        /** * 处理表格样式 * @param wb * @param sheet * @param cell * @param */
        private static void dealExcelStyle(Workbook wb,Sheet sheet,Cell cell,JSONObject style,List<Integer> index){ 
            CellStyle cellStyle = cell.getCellStyle();
            JSONObject obj = new JSONObject();
            if (cellStyle != null) { 
                obj.put("row",index.get(0));
                obj.put("col",index.get(1));
                obj.put("text-align",convertAlignToHtml(cellStyle.getAlignment().getCode()));
                obj.put("valign",convertVerticalAlignToHtml(cellStyle.getVerticalAlignment().getCode()));
                if (wb instanceof XSSFWorkbook) { 
                    XSSFFont xf = ((XSSFCellStyle) cellStyle).getFont();
                    obj.put("font-weight",(xf.getBold()?"bold":"none"));
                    obj.put("font-size",xf.getFontHeightInPoints() + PX);
                    obj.put("width",sheet.getColumnWidthInPixels(cell.getColumnIndex()) + PX);
                    XSSFColor xc = xf.getXSSFColor();
                    if (xc != null) { 
                        obj.put("font-color","#"+xc.getARGBHex().substring(2));
                    }
                    XSSFColor bgColor = (XSSFColor) cellStyle.getFillForegroundColorColor();
                    if (bgColor != null) { 
                        obj.put("background","#" + bgColor.getARGBHex().substring(2));
                    }
                    obj.put("border-top",getBorderStyle(0,cellStyle.getBorderTop().getCode(), ((XSSFCellStyle) cellStyle).getTopBorderXSSFColor()));
                    obj.put("border-right",getBorderStyle(1,cellStyle.getBorderRight().getCode(), ((XSSFCellStyle) cellStyle).getRightBorderXSSFColor()));
                    obj.put("border-bottom",getBorderStyle(2,cellStyle.getBorderBottom().getCode(), ((XSSFCellStyle) cellStyle).getBottomBorderXSSFColor()));
                    obj.put("border-left",getBorderStyle(3,cellStyle.getBorderLeft().getCode(), ((XSSFCellStyle) cellStyle).getLeftBorderXSSFColor()));
                }else if(wb instanceof HSSFWorkbook){ 
                    HSSFFont hf = ((HSSFCellStyle) cellStyle).getFont(wb);
                    short fontColor = hf.getColor();
                    HSSFPalette palette = ((HSSFWorkbook) wb).getCustomPalette(); // 类HSSFPalette用于求的颜色的国际标准形式
                    HSSFColor hc = palette.getColor(fontColor);
                    obj.put("font-weight",(hf.getBold()?"bold":"none"));
                    obj.put("font-size",hf.getFontHeightInPoints() + PX);
                    String fontColorStr = convertToStardColor(hc);
                    if (fontColorStr != null && !"".equals(fontColorStr.trim())) { 
                        obj.put("font-color",fontColorStr);
                    }
                    obj.put("width",sheet.getColumnWidthInPixels(cell.getColumnIndex()) + PX);
                    hc = palette.getColor(cellStyle.getFillForegroundColor());
                    String bgColorStr = convertToStardColor(hc);
                    if (bgColorStr != null && !"".equals(bgColorStr.trim())) { 
                        obj.put("background-color",bgColorStr);
                    }
                    obj.put("border-top",getBorderStyle(palette,0,cellStyle.getBorderTop().getCode(),cellStyle.getTopBorderColor()));
                    obj.put("border-right",getBorderStyle(palette,1,cellStyle.getBorderRight().getCode(),cellStyle.getRightBorderColor()));
                    obj.put("border-bottom", getBorderStyle(palette,2,cellStyle.getBorderBottom().getCode(),cellStyle.getBottomBorderColor()));
                    obj.put("border-left",getBorderStyle(palette,3,cellStyle.getBorderLeft().getCode(),cellStyle.getLeftBorderColor()));
                }
            }
            for(String key:obj.keySet()){ 
                obj.put(key,obj.get(key)+";");
            }
            style.put("style",obj);
        }
    
        /** * 单元格内容的水平对齐方式 * @param alignment * @return */
        private static String convertAlignToHtml(short alignment) { 
    
            String align = "left";
       /* switch (alignment) { case CellStyle.ALIGN_LEFT: align = "left"; break; case CellStyle.ALIGN_CENTER: align = "center"; break; case CellStyle.ALIGN_RIGHT: align = "right"; break; default: break; }*/
            if(HorizontalAlignment.LEFT.getCode()==alignment){ 
                align ="left";
            }
            if(HorizontalAlignment.CENTER.getCode()==alignment){ 
                align ="center";
            }
            if(HorizontalAlignment.RIGHT.getCode()==alignment){ 
                align ="right";
            }
            return align;
        }
    
        /** * 单元格中内容的垂直排列方式 * @param verticalAlignment * @return */
        private static String convertVerticalAlignToHtml(short verticalAlignment) { 
    
            String valign = "middle";
            /* switch (verticalAlignment) { case CellStyle.VERTICAL_BOTTOM: valign = "bottom"; break; case CellStyle.VERTICAL_CENTER: valign = "center"; break; case CellStyle.VERTICAL_TOP: valign = "top"; break; default: break; } */
    
            if(VerticalAlignment.BOTTOM.getCode()==verticalAlignment){ 
                valign ="bottom";
            }
            if(VerticalAlignment.CENTER.getCode()==verticalAlignment){ 
                valign ="center";
            }
            if(VerticalAlignment.TOP.getCode()==verticalAlignment){ 
                valign ="top";
            }
            return valign;
        }
    
        private static String convertToStardColor(HSSFColor hc) { 
    
            StringBuffer sb = new StringBuffer("");
            if (hc != null) { 
                if (HSSFColor.HSSFColorPredefined.AUTOMATIC.getIndex() == hc.getIndex()) { 
                    return null;
                }
                sb.append("#");
                for (int i = 0; i < hc.getTriplet().length; i++) { 
                    sb.append(fillWithZero(Integer.toHexString(hc.getTriplet()[i])));
                }
            }
    
            return sb.toString();
        }
    
        private static String fillWithZero(String str) { 
            if (str != null && str.length() < 2) { 
                return "0" + str;
            }
            return str;
        }
    
    // private static String[] bordesr={"border-top:","border-right:","border-bottom:","border-left:"};
        private static String[] borderStyles={ "solid ","solid ","solid ","solid ","solid ","solid ","solid ","solid ","solid ","solid","solid","solid","solid","solid"};
        private static final String PX = "px";
    
        private static  String getBorderStyle(  HSSFPalette palette ,int b,short s, short t){ 
            if(s==0)return  borderStyles[s]+"#d0d7e5 1px";
            String borderColorStr = convertToStardColor( palette.getColor(t));
            borderColorStr=borderColorStr==null|| borderColorStr.length()<1?"#000000":borderColorStr;
            return borderStyles[s]+borderColorStr+" 1px";
        }
    
        private static  String getBorderStyle(int b,short s, XSSFColor xc){ 
    
            if(s==0)return  borderStyles[s]+"#d0d7e5 1px";
            if (xc != null) { 
                String borderColorStr = xc.getARGBHex();//t.getARGBHex();
                borderColorStr=borderColorStr==null|| borderColorStr.length()<1?"#000000":borderColorStr.substring(2);
                return borderStyles[s]+borderColorStr+" 1px";
            }
    
            return "";
        }
    
        public Integer getUploadlogid() { 
            return uploadlogid;
        }
    
        public POIExcelToHandsontable setUploadlogid(Integer uploadlogid) { 
            this.uploadlogid = uploadlogid;
            return this;
        }
    }