您好,登錄后才能下訂單哦!
今天小編給大家分享一下Java怎么導(dǎo)出Excel增加下拉框選項(xiàng)的相關(guān)知識(shí)點(diǎn),內(nèi)容詳細(xì),邏輯清晰,相信大部分人都還太了解這方面的知識(shí),所以分享這篇文章給大家參考一下,希望大家閱讀完這篇文章后有所收獲,下面我們一起來了解一下吧。
excel對(duì)于下拉框較多選項(xiàng)的,需要使用隱藏工作簿來解決,使用函數(shù)取值來做選項(xiàng)
選項(xiàng)較少(一般少于5個(gè)):
private static DataValidation setFewDataValidation(Sheet sheet, String[] textList, int firstRow, int endRow, int firstCol, int endCol) { DataValidationHelper helper = sheet.getDataValidationHelper(); //加載下拉列表內(nèi)容 DataValidationConstraint constraint = helper.createExplicitListConstraint(textList); constraint.setExplicitListValues(textList); //設(shè)置數(shù)據(jù)有效性加載在哪個(gè)單元格上。四個(gè)參數(shù)分別是:起始行、終止行、起始列、終止列 CellRangeAddressList regions = new CellRangeAddressList((short) firstRow, (short) endRow, (short) firstCol, (short) endCol); //數(shù)據(jù)有效性對(duì)象 return helper.createValidation(constraint, regions); }
選項(xiàng)較多
創(chuàng)建隱藏工作簿:
Sheet sheetHidden = wb.createSheet("Sheet2"); wb.setSheetHidden(1, true);
每一個(gè)列表占用一列
當(dāng)然也可以每個(gè)列表使用一張工作簿,只用第一列。 這里是使用一個(gè)工作簿使用每個(gè)列,先26個(gè)字母,一般夠用了
String[] arr = {"A", "B", "C", "D", "E", "F", "G", "H", "I", "J", "K", "L", "M", "N", "O", "P", "Q", "R", "S", "T", "U", "V", "W", "X", "Y", "Z"};
for (int j = 0; j < dataList.size(); j++) { if (index == 0) { //第1個(gè)下拉選項(xiàng),直接創(chuàng)建行、列 row = sheetHidden.createRow(j); //創(chuàng)建數(shù)據(jù)行 // sheetHidden.setColumnWidth(j, 4000); //設(shè)置每列的列寬 row.createCell(0).setCellValue(dataList.get(j)); //設(shè)置對(duì)應(yīng)單元格的值 } else { //非第1個(gè)下拉選項(xiàng) int rowCount = sheetHidden.getLastRowNum(); if (j <= rowCount) { //前面創(chuàng)建過的行,直接獲取行,創(chuàng)建列 //獲取行,創(chuàng)建列 sheetHidden.getRow(j).createCell(index).setCellValue(dataList.get(j)); //設(shè)置對(duì)應(yīng)單元格的值 } else { //未創(chuàng)建過的行,直接創(chuàng)建行、創(chuàng)建列 // sheetHidden.setColumnWidth(j, 4000); //設(shè)置每列的列寬 //創(chuàng)建行、創(chuàng)建列 sheetHidden.createRow(j).createCell(index).setCellValue(dataList.get(j)); //設(shè)置對(duì)應(yīng)單元格的值 } } }
index 代表第幾個(gè)下拉框,也就是在隱藏工作簿的第幾列,dataList表示下拉框的內(nèi)容
創(chuàng)建公式:
String strFormula = "Sheet2!$" + arr[index] + "$1:$" + arr[index] + "$" + dataList.size();
Sheet2第A1到A5000作為下拉列表來源數(shù)據(jù)
xls和xlsx生成下拉框的選項(xiàng)不一樣
private static DataValidation setMoreDataValidation(Workbook wb, Sheet sheet, String strFormula, int startRow, int endRow, int startColumn, int endColumn) { DataValidation dataValidation; // 設(shè)置數(shù)據(jù)有效性加載在哪個(gè)單元格上,四個(gè)參數(shù)分別是:起始行、終止行、起始列、終止列 CellRangeAddressList regions = new CellRangeAddressList(startRow, endRow, startColumn, endColumn); if (wb instanceof XSSFWorkbook) { //獲取新sheet頁內(nèi)容 XSSFDataValidationConstraint constraint = new XSSFDataValidationConstraint(DataValidationConstraint.ValidationType.LIST, strFormula); // 設(shè)置數(shù)據(jù)有效性加載在哪個(gè)單元格上,四個(gè)參數(shù)分別是:起始行、終止行、起始列、終止列 // 數(shù)據(jù)有效性對(duì)象 DataValidationHelper help = new XSSFDataValidationHelper((XSSFSheet) sheet); dataValidation = help.createValidation(constraint, regions); dataValidation.setSuppressDropDownArrow(true); dataValidation.setShowErrorBox(true); } else { // 設(shè)置數(shù)據(jù)有效性加載在哪個(gè)單元格上。四個(gè)參數(shù)分別是:起始行、終止行、起始列、終止列 DVConstraint constraint = DVConstraint.createFormulaListConstraint(strFormula); dataValidation = new HSSFDataValidation(regions, constraint); dataValidation.setSuppressDropDownArrow(false); } dataValidation.setEmptyCellAllowed(true); dataValidation.setShowPromptBox(true); dataValidation.createErrorBox("Error", "請(qǐng)選擇下拉框中的數(shù)據(jù)"); dataValidation.createPromptBox("提示", "只能選擇下拉框里面的數(shù)據(jù)"); return dataValidation; }
加入工作簿:
sheet.addValidationData()
完整代碼:
private static void setValidationDate(Workbook wb, Sheet sheet, List<DataValidationCell> dataValidationCellList) { if (dataValidationCellList.isEmpty()) { return; } String[] arr = {"A", "B", "C", "D", "E", "F", "G", "H", "I", "J", "K", "L", "M", "N", "O", "P", "Q", "R", "S", "T", "U", "V", "W", "X", "Y", "Z"}; int index = 0; Row row; Sheet sheetHidden = wb.createSheet("Sheet2"); wb.setSheetHidden(1, true); for (DataValidationCell dataValidationCell : dataValidationCellList) { List<String> dataList = dataValidationCell.getDataList(); if (CollectionUtils.isEmpty(dataList)) { continue; } if (dataList.size() <= 5) { sheet.addValidationData(setFewDataValidation(sheet, dataList.toArray(new String[0]), dataValidationCell.getStartRow(), dataValidationCell.getEndRow(), dataValidationCell.getStartColumn(), dataValidationCell.getEndColumn())); //超過255個(gè)報(bào)錯(cuò) } else { //String strFormula = "Sheet2!$A$1:$A$5000" ; //Sheet2第A1到A5000作為下拉列表來源數(shù)據(jù) String strFormula = "Sheet2!$" + arr[index] + "$1:$" + arr[index] + "$" + dataList.size(); //Sheet2第A1到A5000作為下拉列表來源數(shù)據(jù) sheet.addValidationData(setMoreDataValidation(wb, sheet, strFormula, dataValidationCell.getStartRow(), dataValidationCell.getEndRow(), dataValidationCell.getStartColumn(), dataValidationCell.getEndColumn())); //下拉列表元素很多的情況 //2、生成sheet2內(nèi)容 for (int j = 0; j < dataList.size(); j++) { if (index == 0) { //第1個(gè)下拉選項(xiàng),直接創(chuàng)建行、列 row = sheetHidden.createRow(j); //創(chuàng)建數(shù)據(jù)行 // sheetHidden.setColumnWidth(j, 4000); //設(shè)置每列的列寬 row.createCell(0).setCellValue(dataList.get(j)); //設(shè)置對(duì)應(yīng)單元格的值 } else { //非第1個(gè)下拉選項(xiàng) int rowCount = sheetHidden.getLastRowNum(); if (j <= rowCount) { //前面創(chuàng)建過的行,直接獲取行,創(chuàng)建列 //獲取行,創(chuàng)建列 sheetHidden.getRow(j).createCell(index).setCellValue(dataList.get(j)); //設(shè)置對(duì)應(yīng)單元格的值 } else { //未創(chuàng)建過的行,直接創(chuàng)建行、創(chuàng)建列 // sheetHidden.setColumnWidth(j, 4000); //設(shè)置每列的列寬 //創(chuàng)建行、創(chuàng)建列 sheetHidden.createRow(j).createCell(index).setCellValue(dataList.get(j)); //設(shè)置對(duì)應(yīng)單元格的值 } } } index++; } } }
public static class DataValidationCell{ private int startRow; private int endRow; private int startColumn; private int endColumn; private List<String> dataList; }
以上就是“Java怎么導(dǎo)出Excel增加下拉框選項(xiàng)”這篇文章的所有內(nèi)容,感謝各位的閱讀!相信大家閱讀完這篇文章都有很大的收獲,小編每天都會(huì)為大家更新不同的知識(shí),如果還想學(xué)習(xí)更多的知識(shí),請(qǐng)關(guān)注億速云行業(yè)資訊頻道。
免責(zé)聲明:本站發(fā)布的內(nèi)容(圖片、視頻和文字)以原創(chuàng)、轉(zhuǎn)載和分享為主,文章觀點(diǎn)不代表本網(wǎng)站立場,如果涉及侵權(quán)請(qǐng)聯(lián)系站長郵箱:is@yisu.com進(jìn)行舉報(bào),并提供相關(guān)證據(jù),一經(jīng)查實(shí),將立刻刪除涉嫌侵權(quán)內(nèi)容。