产品供销存-后端
blame | 历史 | 补丁 | 提交 | 提交对比 | 忽略空白
RuoYi
2024-03-22 6e5b0e711c9e7924428f2e43825bd4599d73ccd8 
 src/main/java/com/ruoyi/common/utils/poi/ExcelUtil.java


@@ -39,6 +39,7 @@


import org.apache.poi.ss.usermodel.CellStyle;



import org.apache.poi.ss.usermodel.CellType;



import org.apache.poi.ss.usermodel.ClientAnchor;



import org.apache.poi.ss.usermodel.DataFormat;



import org.apache.poi.ss.usermodel.DataValidation;



import org.apache.poi.ss.usermodel.DataValidationConstraint;



import org.apache.poi.ss.usermodel.DataValidationHelper;



@@ -48,6 +49,7 @@


import org.apache.poi.ss.usermodel.Font;



import org.apache.poi.ss.usermodel.HorizontalAlignment;



import org.apache.poi.ss.usermodel.IndexedColors;



import org.apache.poi.ss.usermodel.Name;



import org.apache.poi.ss.usermodel.PictureData;



import org.apache.poi.ss.usermodel.Row;



import org.apache.poi.ss.usermodel.Sheet;



@@ -96,6 +98,11 @@


    public static final String FORMULA_REGEX_STR = "=|-|\\+|@";







    public static final String[] FORMULA_STR = { "=", "-", "+", "@" };







    /**



     * 用于dictType属性数据存储，避免重复查缓存



     */



    public Map<String, String> sysDictMap = new HashMap<String, String>();







    /**



     * Excel sheet最大行数，默认65536



@@ -282,9 +289,23 @@


     * @param is 输入流



     * @return 转换后集合



     */



    public List<T> importExcel(InputStream is) throws Exception



    public List<T> importExcel(InputStream is)



    {



        return importExcel(is, 0);



        List<T> list = null;



        try



        {



            list = importExcel(is, 0);



        }



        catch (Exception e)



        {



            log.error("导入Excel异常{}", e.getMessage());



            throw new UtilException(e.getMessage());



        }



        finally



        {



            IOUtils.closeQuietly(is);



        }



        return list;



    }







    /**



@@ -330,7 +351,6 @@


        }



        // 获取最后一个非空行的行下标，比如总行数为n，则返回的为n-1



        int rows = sheet.getLastRowNum();







        if (rows > 0)



        {



            // 定义一个map用于存放excel列的序号和field.



@@ -445,7 +465,7 @@


                        {



                            propertyName = field.getName() + "." + attr.targetAttr();



                        }



                        else if (StringUtils.isNotEmpty(attr.readConverterExp()))



                        if (StringUtils.isNotEmpty(attr.readConverterExp()))



                        {



                            val = reverseByExp(Convert.toStr(val), attr.readConverterExp(), attr.separator());



                        }



@@ -455,7 +475,7 @@


                        }



                        else if (!attr.handler().equals(ExcelHandlerAdapter.class))



                        {



                            val = dataFormatHandlerAdapter(val, attr);



                            val = dataFormatHandlerAdapter(val, attr, null);



                        }



                        else if (ColumnType.IMAGE == attr.cellType() && StringUtils.isNotEmpty(pictures))



                        {



@@ -764,6 +784,8 @@


        titleFont.setFontHeightInPoints((short) 16);



        titleFont.setBold(true);



        style.setFont(titleFont);



        DataFormat dataFormat = wb.createDataFormat();



        style.setDataFormat(dataFormat.getFormat("@"));



        styles.put("title", style);







        style = wb.createCellStyle();



@@ -826,6 +848,9 @@


                headerFont.setBold(true);



                headerFont.setColor(excel.headerColor().index);



                style.setFont(headerFont);



                // 设置表格头单元格文本形式



                DataFormat dataFormat = wb.createDataFormat();



                style.setDataFormat(dataFormat.getFormat("@"));



                headerStyles.put(key, style);



            }



        }



@@ -843,32 +868,64 @@


        Map<String, CellStyle> styles = new HashMap<String, CellStyle>();



        for (Object[] os : fields)



        {



            Field field = (Field) os[0];



            Excel excel = (Excel) os[1];



            String key = StringUtils.format("data_{}_{}_{}", excel.align(), excel.color(), excel.backgroundColor());



            if (!styles.containsKey(key))



            if (Collection.class.isAssignableFrom(field.getType()))



            {



                CellStyle style = wb.createCellStyle();



                style.setAlignment(excel.align());



                style.setVerticalAlignment(VerticalAlignment.CENTER);



                style.setBorderRight(BorderStyle.THIN);



                style.setRightBorderColor(IndexedColors.GREY_50_PERCENT.getIndex());



                style.setBorderLeft(BorderStyle.THIN);



                style.setLeftBorderColor(IndexedColors.GREY_50_PERCENT.getIndex());



                style.setBorderTop(BorderStyle.THIN);



                style.setTopBorderColor(IndexedColors.GREY_50_PERCENT.getIndex());



                style.setBorderBottom(BorderStyle.THIN);



                style.setBottomBorderColor(IndexedColors.GREY_50_PERCENT.getIndex());



                style.setFillPattern(FillPatternType.SOLID_FOREGROUND);



                style.setFillForegroundColor(excel.backgroundColor().getIndex());



                Font dataFont = wb.createFont();



                dataFont.setFontName("Arial");



                dataFont.setFontHeightInPoints((short) 10);



                dataFont.setColor(excel.color().index);



                style.setFont(dataFont);



                styles.put(key, style);



                ParameterizedType pt = (ParameterizedType) field.getGenericType();



                Class<?> subClass = (Class<?>) pt.getActualTypeArguments()[0];



                List<Field> subFields = FieldUtils.getFieldsListWithAnnotation(subClass, Excel.class);



                for (Field subField : subFields)



                {



                    Excel subExcel = subField.getAnnotation(Excel.class);



                    annotationDataStyles(styles, subField, subExcel);



                }



            }



            else



            {



                annotationDataStyles(styles, field, excel);



            }



        }



        return styles;



    }







    /**



     * 根据Excel注解创建表格列样式



     * 


     * @param styles 自定义样式列表



     * @param field  属性列信息



     * @param excel  注解信息



     */



    public void annotationDataStyles(Map<String, CellStyle> styles, Field field, Excel excel)



    {



        String key = StringUtils.format("data_{}_{}_{}_{}", excel.align(), excel.color(), excel.backgroundColor(), excel.cellType());



        if (!styles.containsKey(key))



        {



            CellStyle style = wb.createCellStyle();



            style.setAlignment(excel.align());



            style.setVerticalAlignment(VerticalAlignment.CENTER);



            style.setBorderRight(BorderStyle.THIN);



            style.setRightBorderColor(IndexedColors.GREY_50_PERCENT.getIndex());



            style.setBorderLeft(BorderStyle.THIN);



            style.setLeftBorderColor(IndexedColors.GREY_50_PERCENT.getIndex());



            style.setBorderTop(BorderStyle.THIN);



            style.setTopBorderColor(IndexedColors.GREY_50_PERCENT.getIndex());



            style.setBorderBottom(BorderStyle.THIN);



            style.setBottomBorderColor(IndexedColors.GREY_50_PERCENT.getIndex());



            style.setFillPattern(FillPatternType.SOLID_FOREGROUND);



            style.setFillForegroundColor(excel.backgroundColor().getIndex());



            Font dataFont = wb.createFont();



            dataFont.setFontName("Arial");



            dataFont.setFontHeightInPoints((short) 10);



            dataFont.setColor(excel.color().index);



            style.setFont(dataFont);



            if (ColumnType.TEXT == excel.cellType())



            {



                DataFormat dataFormat = wb.createDataFormat();



                style.setDataFormat(dataFormat.getFormat("@"));



            }



            styles.put(key, style);



        }



    }







    /**



@@ -885,7 +942,7 @@


        if (isSubList())



        {



            // 填充默认样式，防止合并单元格样式失效



            sheet.setDefaultColumnStyle(column, styles.get(StringUtils.format("data_{}_{}_{}", attr.align(), attr.color(), attr.backgroundColor())));



            sheet.setDefaultColumnStyle(column, styles.get(StringUtils.format("data_{}_{}_{}_{}", attr.align(), attr.color(), attr.backgroundColor(), attr.cellType())));



            if (attr.needMerge())



            {



                sheet.addMergedRegion(new CellRangeAddress(rownum - 1, rownum, column, column));



@@ -903,7 +960,7 @@


     */



    public void setCellVo(Object value, Excel attr, Cell cell)



    {



        if (ColumnType.STRING == attr.cellType())



        if (ColumnType.STRING == attr.cellType() || ColumnType.TEXT == attr.cellType())



        {



            String cellValue = Convert.toStr(value);



            // 对于任何以表达式触发字符 =-+@开头的单元格，直接使用tab字符作为前缀，防止CSV注入。



@@ -982,8 +1039,16 @@


        }



        if (StringUtils.isNotEmpty(attr.prompt()) || attr.combo().length > 0)



        {



            // 提示信息或只能选择不能输入的列内容.



            setPromptOrValidation(sheet, attr.combo(), attr.prompt(), 1, 100, column, column);



            if (attr.combo().length > 15 || StringUtils.join(attr.combo()).length() > 255)



            {



                // 如果下拉数大于15或字符串长度大于255，则使用一个新sheet存储，避免生成的模板下拉值获取不到



                setXSSFValidationWithHidden(sheet, attr.combo(), attr.prompt(), 1, 100, column, column);



            }



            else



            {



                // 提示信息或只能选择不能输入的列内容.



                setPromptOrValidation(sheet, attr.combo(), attr.prompt(), 1, 100, column, column);



            }



        }



    }







@@ -1007,7 +1072,7 @@


                    CellRangeAddress cellAddress = new CellRangeAddress(subMergedFirstRowNum, subMergedLastRowNum, column, column);



                    sheet.addMergedRegion(cellAddress);



                }



                cell.setCellStyle(styles.get(StringUtils.format("data_{}_{}_{}", attr.align(), attr.color(), attr.backgroundColor())));



                cell.setCellStyle(styles.get(StringUtils.format("data_{}_{}_{}_{}", attr.align(), attr.color(), attr.backgroundColor(), attr.cellType())));







                // 用于读取对象中的属性



                Object value = getTargetValue(vo, field, attr);



@@ -1025,7 +1090,12 @@


                }



                else if (StringUtils.isNotEmpty(dictType) && StringUtils.isNotNull(value))



                {



                    cell.setCellValue(convertDictByExp(Convert.toStr(value), dictType, separator));



                    if (!sysDictMap.containsKey(dictType + value))



                    {



                        String lable = convertDictByExp(Convert.toStr(value), dictType, separator);



                        sysDictMap.put(dictType + value, lable);



                    }



                    cell.setCellValue(sysDictMap.get(dictType + value));



                }



                else if (value instanceof BigDecimal && -1 != attr.scale())



                {



@@ -1033,7 +1103,7 @@


                }



                else if (!attr.handler().equals(ExcelHandlerAdapter.class))



                {



                    cell.setCellValue(dataFormatHandlerAdapter(value, attr));



                    cell.setCellValue(dataFormatHandlerAdapter(value, attr, cell));



                }



                else



                {



@@ -1085,6 +1155,58 @@


            dataValidation.setSuppressDropDownArrow(false);



        }



        sheet.addValidationData(dataValidation);



    }







    /**



     * 设置某些列的值只能输入预制的数据,显示下拉框（兼容超出一定数量的下拉框）.



     * 


     * @param sheet 要设置的sheet.



     * @param textlist 下拉框显示的内容



     * @param promptContent 提示内容



     * @param firstRow 开始行



     * @param endRow 结束行



     * @param firstCol 开始列



     * @param endCol 结束列



     */



    public void setXSSFValidationWithHidden(Sheet sheet, String[] textlist, String promptContent, int firstRow, int endRow, int firstCol, int endCol)



    {



        String hideSheetName = "combo_" + firstCol + "_" + endCol;



        Sheet hideSheet = wb.createSheet(hideSheetName); // 用于存储 下拉菜单数据



        for (int i = 0; i < textlist.length; i++)



        {



            hideSheet.createRow(i).createCell(0).setCellValue(textlist[i]);



        }



        // 创建名称，可被其他单元格引用



        Name name = wb.createName();



        name.setNameName(hideSheetName + "_data");



        name.setRefersToFormula(hideSheetName + "!$A$1:$A$" + textlist.length);



        DataValidationHelper helper = sheet.getDataValidationHelper();



        // 加载下拉列表内容



        DataValidationConstraint constraint = helper.createFormulaListConstraint(hideSheetName + "_data");



        // 设置数据有效性加载在哪个单元格上,四个参数分别是：起始行、终止行、起始列、终止列



        CellRangeAddressList regions = new CellRangeAddressList(firstRow, endRow, firstCol, endCol);



        // 数据有效性对象



        DataValidation dataValidation = helper.createValidation(constraint, regions);



        if (StringUtils.isNotEmpty(promptContent))



        {



            // 如果设置了提示信息则鼠标放上去提示



            dataValidation.createPromptBox("", promptContent);



            dataValidation.setShowPromptBox(true);



        }



        // 处理Excel兼容性问题



        if (dataValidation instanceof XSSFDataValidation)



        {



            dataValidation.setSuppressDropDownArrow(true);



            dataValidation.setShowErrorBox(true);



        }



        else



        {



            dataValidation.setSuppressDropDownArrow(false);



        }







        sheet.addValidationData(dataValidation);



        // 设置hiddenSheet隐藏



        wb.setSheetHidden(wb.getSheetIndex(hideSheet), true);



    }







    /**



@@ -1194,13 +1316,13 @@


     * @param excel 数据注解



     * @return



     */



    public String dataFormatHandlerAdapter(Object value, Excel excel)



    public String dataFormatHandlerAdapter(Object value, Excel excel, Cell cell)



    {



        try



        {



            Object instance = excel.handler().newInstance();



            Method formatMethod = excel.handler().getMethod("format", new Class[] { Object.class, String[].class });



            value = formatMethod.invoke(instance, value, excel.args());



            Method formatMethod = excel.handler().getMethod("format", new Class[] { Object.class, String[].class, Cell.class, Workbook.class });



            value = formatMethod.invoke(instance, value, excel.args(), cell, this.wb);



        }



        catch (Exception e)



        {



@@ -1260,7 +1382,7 @@


     */



    public String encodingFilename(String filename)



    {



        filename = UUID.randomUUID().toString() + "_" + filename + ".xlsx";



        filename = UUID.randomUUID() + "_" + filename + ".xlsx";



        return filename;



    }







@@ -1379,7 +1501,8 @@


                    Excel[] excels = attrs.value();



                    for (Excel attr : excels)



                    {



                        if (attr != null && (attr.type() == Type.ALL || attr.type() == type))



                        if (!ArrayUtils.contains(this.excludeFields, field.getName() + "." + attr.targetAttr())



                                && (attr != null && (attr.type() == Type.ALL || attr.type() == type)))



                        {



                            field.setAccessible(true);



                            fields.add(new Object[] { field, attr });



@@ -1537,7 +1660,7 @@


                    HSSFPicture pic = (HSSFPicture) shape;



                    int pictureIndex = pic.getPictureIndex() - 1;



                    HSSFPictureData picData = pictures.get(pictureIndex);



                    String picIndex = String.valueOf(anchor.getRow1()) + "_" + String.valueOf(anchor.getCol1());



                    String picIndex = anchor.getRow1() + "_" + anchor.getCol1();



                    sheetIndexPicMap.put(picIndex, picData);



                }



            }