poi实现excel导出动态下拉框

poi实现excel导出动态下拉框

XSSFWorkbook实现excel导出动态下拉框

一、引入依赖

<dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi</artifactId>
            <version>4.0.1</version>
        </dependency>
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml</artifactId>
            <version>4.0.1</version>
        </dependency>
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml-schemas</artifactId>
            <version>4.0.1</version>
        </dependency>

二、测试代码

import org.apache.poi.ss.util.CellRangeAddressList;
import org.apache.poi.xssf.usermodel.*;

import java.io.FileOutputStream;

public class ExcelExportServiceTest {

    public static void main(String[] args) {
        try
        {
            dropDownList42007("C:\\Users\\abcxxx\\Desktop\\temp\\test.xlsx");
        }
        catch (Exception e) {

            e.printStackTrace();
        }
    }


    public static void dropDownList42007(String filePath)
            throws Exception {
        XSSFWorkbook workbook = new XSSFWorkbook();
        XSSFSheet sheet = workbook.createSheet("下拉列表测试");


        CellRangeAddressList addressList = null;
        XSSFDataValidation validation = null;
        for (int i = 0; i < 100; i++) {
            String[] datas = new String[] {"维持"+i,"恢复"+i,"调整"+i};
            XSSFDataValidationHelper dvHelper = new XSSFDataValidationHelper(sheet);
            XSSFDataValidationConstraint dvConstraint = (XSSFDataValidationConstraint) dvHelper
                    .createExplicitListConstraint(datas);

            addressList = new CellRangeAddressList(i+1, i+1, 8, 8);
            validation = (XSSFDataValidation) dvHelper.createValidation(
                    dvConstraint, addressList);
            // 07默认setSuppressDropDownArrow(true);
            // validation.setSuppressDropDownArrow(true);
            // validation.setShowErrorBox(true);
            sheet.addValidationData(validation);
        }
        FileOutputStream stream = new FileOutputStream(filePath);
        workbook.write(stream);
        stream.close();
        addressList = null;
        validation = null;
    }
}