java操作poi生成excel（相关功能点）

缺乏、安全感 2021-12-15 09:21 561阅读 0赞

1.将一个excel文件的样式复制到另一个excel

Workbook wb = WorkbookFactory.create(new File("existing.xls"));
    CellStyle origStyle = wb.getCellStyleAt(1); // Or from a cell
    
    Workbook newWB = new XSSFWorkbook();
    Sheet sheet = newWB.createSheet();
    Row r1 = sheet.createRow(0);
    Cell c1 = r1.createCell(0);
    
    CellStyle newStyle = newWB.createCellStyle();
    newStyle.cloneStyleFrom(origStyle);
    c1.setCellStyle(newStyle);
    
    newWB.write(new FileOutpuStream("new.xlsx"));

2.给某一列添加筛选功能

Sheet sheetCreat = wbCreat.createSheet(sheet.getSheetName());
                    CellRangeAddress c = CellRangeAddress.valueOf(CELL_RANGE_ADDRESS);
                    sheetCreat.setAutoFilter(c);

3.给某一列添加取值的校验（限制某一列只能取某些值）

/**
         * <给某一列添加取值的校验（限制某一列只能取某些值）>
         *
         * @param sheet    sheet
         * @param firstRow firstRow
         * @param firstCol firstCol
         * @param endRow   endRow
         * @param endCol   endCol
         * @param strList  strList
         * @return
         * @throws
         */
        private static HSSFDataValidation getDataValidationList(HSSFSheet sheet, short firstRow,
                                                                short firstCol, short endRow,
                                                                short endCol, List<String> strList)
        {
            String[] dataArray = strList.toArray(new String[0]);
            HSSFDataValidationHelper dvHelper = new HSSFDataValidationHelper(sheet);
            DataValidationConstraint dvConstraint = (DataValidationConstraint)dvHelper
                .createExplicitListConstraint(dataArray);
            CellRangeAddressList addressList = new CellRangeAddressList(firstRow, endRow, firstCol,
                endCol);
            HSSFDataValidation validation = (HSSFDataValidation)dvHelper.createValidation(dvConstraint,
                addressList);
            return validation;
        }

if (!CollectionUtils.isEmpty(dataList))
                    {
                        HSSFDataValidation dataValidation4List = getDataValidationList(
                            (HSSFSheet)sheetCreat, (short)0, (short)3, (short)1000, (short)3,
                            dataList);
                        if (null != dataValidation4List)
                        {
                            sheetCreat.addValidationData(dataValidation4List);
                        }
                    }

4.数据验证-某列保证唯一性

/**
         * <数据验证-某列保证唯一性>
         *
         * @param sheet    sheet
         * @param firstRow firstRow
         * @param firstCol firstCol
         * @param endRow   endRow
         * @param endCol   endCol
         * @param formula  excel校验规则
         * @return
         * @throws
         */
        private static HSSFDataValidation getDataValidationCustomFormula(HSSFSheet sheet,
                                                                         short firstRow,
                                                                         short firstCol,
                                                                         short endRow, short endCol,
                                                                         String formula)
        {
            HSSFDataValidationHelper dvHelper = new HSSFDataValidationHelper(sheet);
            DataValidationConstraint dvConstraint = (DataValidationConstraint)dvHelper
                .createCustomConstraint(formula);
            CellRangeAddressList addressList = new CellRangeAddressList(firstRow, endRow, firstCol,
                endCol);
            HSSFDataValidation validation = (HSSFDataValidation)dvHelper.createValidation(dvConstraint,
                addressList);
    
            validation.createErrorBox("错误：", "赋值属性列不允许重复");
            validation.setShowErrorBox(true);
            validation.setEmptyCellAllowed(true);
            validation.setSuppressDropDownArrow(true);
            validation.setShowPromptBox(true);
            validation.setErrorStyle(DataValidation.ErrorStyle.STOP);
    
            return validation;
        }

HSSFDataValidation dataValidation4Formula = getDataValidationCustomFormula(
                        (HSSFSheet)sheetCreat, (short)0, (short)3, (short)(1000), (short)3,
                        "COUNTIF($D$2:D2,D2)=1");
                    if (null != dataValidation4Formula)
                    {
                        sheetCreat.addValidationData(dataValidation4Formula);
                    }

5.POI设置Excel单元格字体

/**
         * <设置单元格格式>
         *
         * @param wbCreat wbCreat
         * @return style style
         * @throws
         */
        private static CellStyle setCellFormat(HSSFWorkbook wbCreat)
        {
            Font font = wbCreat.createFont();
            font.setFontHeightInPoints((short)11);
            font.setBold(true);
    
            CellStyle style = wbCreat.createCellStyle();
            style.setFont(font);
    
            return style;
        }

6.设置单元格规则

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dhbmdjaGFveDEyMw_size_16_color_FFFFFF_t_70][]

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dhbmdjaGFveDEyMw_size_16_color_FFFFFF_t_70]: /images/20211214/d5d5045dcf004fae8db950d32359fc54.png