1. 处理思路
- 下拉个数较少时直接创建下拉
- 下拉个数超过一定数量时候,直接创建下拉会有bug,导致展示异常。此时可以创建一个隐藏sheet,并添加单元格数据校验指向刚才创建的隐藏sheet中的数据
2. 源码
import com.hz.model.CommonException;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.ss.util.CellRangeAddressList;
import org.apache.poi.xssf.usermodel.*;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.util.CollectionUtils;
import org.springframework.util.StringUtils;
import java.io.BufferedReader;
import java.io.FileOutputStream;
import java.io.FileReader;
import java.util.*;
/**
* excel导出工具
*
* @author pp_lan
*/
public class ExcelExportUtils {
private static final Logger LOGGER = LoggerFactory.getLogger(ExcelExportUtils.class);
public String outputFile;
private List<String> headers;
private List<List<String>> dataList;
private Map<Integer, List<String>> dropdownMap;
private ExcelExportUtils() {
this.outputFile = null;
this.headers = null;
this.dataList = null;
this.dropdownMap = new HashMap<>();
}
public static final ExcelExportUtils getInstance() {
return new ExcelExportUtils();
}
public ExcelExportUtils buildOutput(String outputFile) {
this.outputFile = outputFile;
return this;
}
public ExcelExportUtils buildHeader(List<String> headers) {
this.headers = headers;
return this;
}
public ExcelExportUtils buildData(List<List<String>> dataList) {
this.dataList = dataList;
return this;
}
public ExcelExportUtils buildDropdown(int columnIndex, List<String> dropdownContent) {
this.dropdownMap.put(columnIndex, dropdownContent);
return this;
}
public void createExcel(String sheetName) {
// 校验数据
this.checkData();
try (FileOutputStream out = new FileOutputStream(outputFile)) {
XSSFWorkbook workbook = new XSSFWorkbook();
XSSFSheet sheet = workbook.createSheet(sheetName);
sheet.setDefaultColumnWidth(18);
sheet.setDefaultRowHeightInPoints(20);
XSSFCellStyle headerStyle = buildStyle(workbook, true);
XSSFCellStyle dataStyle = buildStyle(workbook, false);
int rowNumber = 0;
XSSFRow headerRow = sheet.createRow(rowNumber++);
headerRow.setHeightInPoints(23);
for (int i = 0; i < headers.size(); i++) {
XSSFCell cell = headerRow.createCell(i);
cell.setCellValue(headers.get(i));
cell.setCellStyle(headerStyle);
}
for (int i = 0; i < dataList.size(); i++) {
XSSFRow dataRow = sheet.createRow(rowNumber++);
dataRow.setHeightInPoints(20);
List<String> rowData = dataList.get(i);
for (int j = 0; j < rowData.size(); j++) {
XSSFCell cell = dataRow.createCell(j);
cell.setCellValue(rowData.get(j));
cell.setCellStyle(dataStyle);
}
}
// 创建下拉
for (Map.Entry<Integer, List<String>> entry : this.dropdownMap.entrySet()) {
Integer columnIndex = entry.getKey();
List<String> dropdownList = entry.getValue();
addDropDown(workbook, sheetName, columnIndex, dropdownList, dataList.size());
}
workbook.write(out);
out.flush();
} catch (Exception e) {
throw new CommonException("导出异常", e);
}
}
/**
* 合并单元格
*
* @param sheet
* @param row
* @param firstCol
* @param lastCol
*/
private void addMergeCell(XSSFSheet sheet, int row, int firstCol, int lastCol) {
CellRangeAddress cellAddresses = new CellRangeAddress(row, row, firstCol, lastCol);
sheet.addMergedRegion(cellAddresses);
}
/**
* 校验数据
*/
private void checkData() {
if (!StringUtils.hasText(outputFile)) {
throw new CommonException("输出文件未设置");
}
if (CollectionUtils.isEmpty(headers)) {
throw new CommonException("表头未设置");
}
if (CollectionUtils.isEmpty(dataList)) {
throw new CommonException("数据未设置");
}
int headerSize = headers.size();
for (List<String> data : dataList) {
if (data == null) {
continue;
}
int arrange = data.size();
if (headerSize != arrange) {
throw new CommonException("数据校验失败, 列和表头数量不一致");
}
}
}
/**
* 添加下拉
*
* @param workbook
* @param sheetName
* @param column
* @param dropdownList
*/
private void addDropDown(XSSFWorkbook workbook, String sheetName, int column, List<String> dropdownList, int dataSize) {
if (CollectionUtils.isEmpty(dropdownMap)) {
return;
}
if (dropdownList.size() <= 100) {
XSSFSheet sheet = workbook.getSheet(sheetName);
for (Map.Entry<Integer, List<String>> entry : dropdownMap.entrySet()) {
Integer columnIndex = entry.getKey();
List<String> dropDownList = entry.getValue();
String[] options = dropDownList.toArray(new String[]{});
XSSFDataValidationHelper dvHelper = new XSSFDataValidationHelper(sheet);
DataValidationConstraint dvConstraint = dvHelper.createExplicitListConstraint(options);
CellRangeAddressList addressList = new CellRangeAddressList(1, dataSize, columnIndex, columnIndex);
DataValidation validation = dvHelper.createValidation(dvConstraint, addressList);
sheet.addValidationData(validation);
}
} else {
// 下拉项太多会有bug,采用此方案可以解决
int sheetTotal = workbook.getNumberOfSheets();
String hiddenSheetName = "hiddenSheet" + sheetTotal;
XSSFSheet sheet = workbook.createSheet(hiddenSheetName);
for (int i = 0; i < dropdownList.size(); i++) {
XSSFRow row = sheet.createRow(i);
XSSFCell cell = row.createCell(0);
cell.setCellValue(dropdownList.get(i));
}
int dropdownSize = dropdownList.size();
// 使用单元格A的数据进行单元格校验
String strFormula = new StringBuilder().append(hiddenSheetName).append("!$A$1:$A$").append(dropdownSize).toString();
XSSFDataValidationConstraint constraint = new XSSFDataValidationConstraint(DataValidationConstraint.ValidationType.LIST, strFormula);
CellRangeAddressList cell = new CellRangeAddressList(1, dataSize, column, column);
XSSFDataValidationHelper help = new XSSFDataValidationHelper(sheet);
DataValidation validation = help.createValidation(constraint, cell);
XSSFSheet dataSheet = workbook.getSheet(sheetName);
if (dataSheet != null) {
dataSheet.addValidationData(validation);
}
workbook.setSheetHidden(sheetTotal, true);
}
}
/**
* 创建单元格样式
*
* @param workbook
* @param isHeader
* @return
*/
private static XSSFCellStyle buildStyle(XSSFWorkbook workbook, boolean isHeader) {
XSSFCellStyle cellStyle = workbook.createCellStyle();
cellStyle.setAlignment(HorizontalAlignment.CENTER);
cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
// 字体
XSSFFont font = workbook.createFont();
font.setFontName("宋体");
if (isHeader) {
font.setFontHeightInPoints((short) 12);
font.setBold(true);
} else {
font.setFontHeightInPoints((short) 10);
font.setBold(false);
}
cellStyle.setFont(font);
// 边框
cellStyle.setBorderTop(BorderStyle.THIN);
cellStyle.setBorderBottom(BorderStyle.THIN);
cellStyle.setBorderLeft(BorderStyle.THIN);
cellStyle.setBorderRight(BorderStyle.THIN);
return cellStyle;
}
}
3. 使用示例
public static void main(String[] args) {
List<String> headers = new ArrayList<>(Arrays.asList("银行名称", "列B", "列C"));
// 下拉数量相对较少
List<List<String>> dataList = new ArrayList<>();
dataList.add(new ArrayList<>(Arrays.asList("中国银行", "2111", "3111")));
dataList.add(new ArrayList<>(Arrays.asList("招商银行", "b111", "c111")));
List<String> dropdownList = new ArrayList<>();
for (int i = 0; i < 80; i++) {
dropdownList.add("选项" + i);
}
// 下拉数量大于100
ArrayList<String> dropdownList2 = new ArrayList<>();
for (int i = 0; i < 300; i++) {
dropdownList2.add("新选项" + i);
}
// 下拉数量较少
List<String> bankList = new ArrayList<>();
try (BufferedReader br = new BufferedReader(new FileReader("/home/xk/workspace/gitee/javarecord/doc/bankScrapy.txt"));) {
String bank;
while ((bank = br.readLine()) != null) {
bankList.add(bank);
}
} catch (Exception e) {
LOGGER.error("读取银行数据失败", e);
}
ExcelExportUtils excelExportUtils = ExcelExportUtils.getInstance();
excelExportUtils.buildOutput("/home/xk/workspace/gitee/javarecord/doc/导出示例new.xlsx")
.buildHeader(headers)
.buildData(dataList)
.buildDropdown(0, bankList)
.buildDropdown(1, dropdownList)
.buildDropdown(2, dropdownList2);
excelExportUtils.createExcel("数据");
}
4. 导出样例