easyexcel设置下拉框并突破255限制

easyexcel设置下拉框并突破255限制

1：实现接口

要实现SheetWriteHandler接口，在afterSheetCreate方法设置下拉框

import java.util.List;
import java.util.Map;

import org.apache.poi.ss.usermodel.DataValidation;
import org.apache.poi.ss.usermodel.DataValidationConstraint;
import org.apache.poi.ss.usermodel.DataValidationHelper;
import org.apache.poi.ss.usermodel.Name;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.util.CellRangeAddressList;

import com.alibaba.excel.write.handler.SheetWriteHandler;
import com.alibaba.excel.write.metadata.holder.WriteSheetHolder;
import com.alibaba.excel.write.metadata.holder.WriteWorkbookHolder;

import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.EqualsAndHashCode;
import lombok.NoArgsConstructor;

/**
 * 自定义下拉框
 */
@Data
@AllArgsConstructor
@NoArgsConstructor
@EqualsAndHashCode(callSuper = false)
public class SpinnerWriteHandler implements SheetWriteHandler {

	private List<Map<Integer, List<String>>> list;// 需要下拉框的列

	@Override
	public void afterSheetCreate(WriteWorkbookHolder arg0, WriteSheetHolder arg1) {
		// TODO Auto-generated method stub

		// 创建sheet，突破下拉框255的限制
		// 获取一个workbook
		Workbook workbook = arg0.getWorkbook();

		for (Map<Integer, List<String>> map : list) {
			Sheet sheet = arg1.getSheet();
			/// 开始设置下拉框
			DataValidationHelper helper = sheet.getDataValidationHelper();// 设置下拉框
			for (Map.Entry<Integer, List<String>> entry : map.entrySet()) {
				// 定义sheet的名称
				String sheetName = "SheetHidden" + entry.getKey();
				// 1.创建一个隐藏的sheet 名称为 proviceSheet
				Sheet proviceSheet = workbook.createSheet(sheetName);
				// 设置隐藏
				workbook.setSheetHidden(workbook.getSheetIndex(sheetName), true);
                // 2.循环赋值（为了防止下拉框的行数与隐藏域的行数相对应，将隐藏域加到结束行之后）
				// 设置下拉框数据
				List<String> values = entry.getValue();
				for (int i = 0, length = values.size(); i < length; i++) {
					// i:表示你开始的行数 0表示你开始的列数
					proviceSheet.createRow(i).createCell(0).setCellValue(values.get(i));
				}
				Name category1Name = workbook.createName();
				category1Name.setNameName(sheetName);
				// 4 $A$1:$A$N代表 以A列1行开始获取N行下拉数据
				category1Name.setRefersToFormula(sheetName + "!$A$1:$A$" + (values.size()));
				// 5 将刚才设置的sheet引用到你的下拉列表中 //起始行、终止行、起始列、终止列
				CellRangeAddressList addressList = new CellRangeAddressList(1, 65535, entry.getKey(), entry.getKey());
				DataValidationConstraint constraint8 = helper.createFormulaListConstraint(sheetName);
				DataValidation dataValidation3 = helper.createValidation(constraint8, addressList);
                arg1.getSheet().addValidationData(dataValidation3);
			}
		}
	}

	@Override
	public void beforeSheetCreate(WriteWorkbookHolder arg0, WriteSheetHolder arg1) {
		// TODO Auto-generated method stub

	}
}

2：定义实体类

import com.alibaba.excel.annotation.ExcelProperty;
import com.alibaba.excel.annotation.write.style.ColumnWidth;
import com.alibaba.excel.annotation.write.style.ContentFontStyle;
import com.alibaba.excel.annotation.write.style.HeadFontStyle;
import com.alibaba.excel.annotation.write.style.HeadRowHeight;
import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.EqualsAndHashCode;
import lombok.NoArgsConstructor;
import org.apache.poi.ss.usermodel.Font;

@Data
@AllArgsConstructor
@NoArgsConstructor
@EqualsAndHashCode(callSuper = false)
@ColumnWidth(15)//全局列管
@HeadFontStyle(color = Font.SS_NONE, fontName = "楷体")//全局标题颜色，标题字体
@HeadRowHeight(35)//标题行高
public class Test {
    @ExcelProperty(value = "*单号", index = 0)
    private String code;// 单号

    @HeadFontStyle(color = Font.COLOR_RED, fontName = "楷体", fontHeightInPoints = 14)//标题单独字体颜色，大小，字体
    @ContentFontStyle(color = Font.COLOR_RED, fontName = "宋体", fontHeightInPoints = 11)//内容字体颜色，大小，字体
    @ExcelProperty(value = "*部门单号", index = 1)
    private String deptCode;// 部门编号

    @ExcelProperty(value = "*部门名称", index = 2)
    private String deptName;// 部门名称

    @ExcelProperty(value = "*物资编码", index = 3)
    private String materialCode;// 物资编码
}

3：测试类

import java.io.IOException;
import java.io.UnsupportedEncodingException;
import java.net.URLEncoder;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RestController;

import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.support.ExcelTypeEnum;
import com.shanlitech.allements.easyExcel.Test;
import com.shanlitech.allements.handler.SpinnerWriteHandler;

import lombok.extern.slf4j.Slf4j;

@RestController
@Slf4j
public class TestExcelController {

	@GetMapping("/exportSelectedExcel")
	public void export(HttpServletRequest request, HttpServletResponse response) {
		try {
			List<Map<Integer, List<String>>> list = new ArrayList<>();
			Map<Integer, List<String>> map1 = new HashMap<>();
			List<String> list1 = new ArrayList<>();
			for (int i = 0; i < 1000; i++) {
				list1.add("部门" + i);
			}
			map1.put(2, list1);
			list.add(map1);
			Map<Integer, List<String>> map2 = new HashMap<>();
			List<String> list2 = new ArrayList<>();
			list2.add("物资编码1");
			list2.add("物资编码2");
			list2.add("物资编码3");
			list2.add("物资编码4");
            map2.put(3, list2);
			list.add(map2);
			SpinnerWriteHandler writeHandler = new SpinnerWriteHandler(list);

			List<Test> arrayList = new ArrayList<>();

			String fileName = URLEncoder.encode("测试", "UTF-8");
			response.setContentType("application/vnd.ms-excel");
			response.setCharacterEncoding("utf-8");
			response.setHeader("Content-disposition", "attachment;filename=" + fileName + ".xlsx");
			EasyExcel.write(response.getOutputStream(), Test.class).excelType(ExcelTypeEnum.XLSX)
					.registerWriteHandler(writeHandler).sheet("测试表").doWrite(arrayList);
            } catch (UnsupportedEncodingException e) {
			log.error("导出Excel编码异常", e);
		} catch (IOException e) {
			log.error("导出Excel文件异常", e);
		}
	}
}

3：实际效果

相关链接
在 easyexcel 2.0.5 下自定义实现下拉框
EaxyExcel设置下拉框并解决excel下拉框255限制