需求描述：项目中需用到一个模板下载，并且需要链接数据库读取数据做到级联动，过程坎坷，记录分享一下实现方法：Java + POI + 转成流返回 拼接Excel主体 public Workbook spliceWorkbook ()
需求描述：项目中需用到一个模板下载，并且需要链接数据库读取数据做到级联动，过程坎坷，记录分享一下 实现方法：Java + POI + 转成流返回
拼接Excel主体
public Workbook spliceWorkbook() {// 创建一个excel
@SuppressWarnings("resource")
Workbook workbook = new XSSFWorkbook();
// 创建需要用户填写的sheet
XSSFSheet sheet = (XSSFSheet) workbook.createSheet("物流导入");
//设置列宽
sheet.setDefaultColumnWidth((int) ExportExcelCellConstants.DATE_CELL_WIDTH);
Row row0 = sheet.createRow(0);
row0.createCell(0).setCellValue("物流名称");
row0.createCell(1).setCellValue("物流编码");
List<LogisticsCompany> logisticsCompanies = logisticsCompanyDao.findByStatus(true);
if (logisticsCompanies == null || logisticsCompanies.size() == 0) {
return workbook;
}
//设置单元格格式
row0.getCell(0).setCellStyle(createCellStyle((XSSFWorkbook) workbook));
row0.getCell(1).setCellStyle(createCellStyle((XSSFWorkbook) workbook));
//得到物流公司名称
List<String> logisticsNameList = logisticsCompanies.stream().map(LogisticsCompany::getLogisticsCompanyName).collect(Collectors.toList());
String[] logisticsName = new String[logisticsNameList.size()];
logisticsNameList.toArray(logisticsName);
Map<String, List<String>> map = new HashMap<>();
//组装物流公司名称,物流编码
logisticsCompanies.stream().map(logisticsCompany -> {
map.put(logisticsCompany.getLogisticsCompanyName(), Arrays.asList(logisticsCompany.getCustomer()));
return map;
}).collect(Collectors.toList());
//创建一个专门用来存放地区信息的隐藏sheet页
//因此也不能在现实页之前创建，否则无法隐藏。
Sheet hideSheet = workbook.createSheet("area");
//这一行作用是将此sheet隐藏，功能未完成时注释此行,可以查看隐藏sheet中信息是否正确
workbook.setSheetHidden(workbook.getSheetIndex(hideSheet), true);
int rowId = 0;
// 设置第一行，存
Row logisticsNameRow = hideSheet.createRow(rowId++);
logisticsNameRow.createCell(0).setCellValue("物流名称");
for (int i = 0; i < logisticsNameList.size(); i++) {
Cell provinceCell = logisticsNameRow.createCell(i + 1);
provinceCell.setCellValue(logisticsNameList.get(i));
}
// 将具体的数据写入到每一行中，行开头为物流名称，后面是物流编码。
for (int i = 0; i < logisticsNameList.size(); i++) {
String key = logisticsNameList.get(i);
List<String> son = map.get(key);
Row row1 = hideSheet.createRow(rowId++);
row1.createCell(0).setCellValue(key);
for (int j = 0; j < son.size(); j++) {
Cell cell0 = row1.createCell(j + 1);
cell0.setCellValue(son.get(j));
cell0.setCellStyle(createCellStyle((XSSFWorkbook) workbook));
}
// 添加名称管理器
String range = getRange(1, rowId, son.size());
Name name = workbook.createName();
//key不可重复
name.setNameName(key);
String formula = "area!" + range;
name.setRefersToFormula(formula);
}
XSSFDataValidationHelper dvHelper = new XSSFDataValidationHelper((XSSFSheet) sheet);
// 物流名称
DataValidationConstraint provConstraint = dvHelper.createExplicitListConstraint(logisticsName);
// 四个参数分别是：起始行、终止行、起始列、终止列
CellRangeAddressList provRangeAddressList = new CellRangeAddressList(1, 1000, 2, 2);
DataValidation logisticsNameDataValidation = dvHelper.createValidation(provConstraint, provRangeAddressList);
//验证
logisticsNameDataValidation.createErrorBox("error", "请选择正确的物流名称");
logisticsNameDataValidation.setShowErrorBox(true);
logisticsNameDataValidation.setSuppressDropDownArrow(true);
sheet.addValidationData(logisticsNameDataValidation);
//对前1000行设置有效性
for (int i = 2; i < 1000; i++) {
setDataValidation("C", sheet, i, 4);
}
return workbook;
}
依次给下拉框赋值
/*** 加载下拉列表内容
*
* @param formulaString
* @param naturalRowIndex
* @param naturalColumnIndex
* @param dvHelper
* @return
*/
private DataValidation getDataValidationByFormula(
String formulaString, int naturalRowIndex, int naturalColumnIndex, XSSFDataValidationHelper dvHelper) {
// 加载下拉列表内容
// 举例：若formulaString = "INDIRECT($A$2)" 表示规则数据会从名称管理器中获取key与单元格 A2 值相同的数据，
//如果A2是京东快递，那么此处就是京东快递对应的快递编码。
XSSFDataValidationConstraint dvConstraint = (XSSFDataValidationConstraint) dvHelper.createFormulaListConstraint(formulaString);
// 设置数据有效性加载在哪个单元格上。
// 四个参数分别是：起始行、终止行、起始列、终止列
int firstRow = naturalRowIndex - 1;
int lastRow = naturalRowIndex - 1;
int firstCol = naturalColumnIndex - 1;
int lastCol = naturalColumnIndex - 1;
CellRangeAddressList regions = new CellRangeAddressList(firstRow,
lastRow, firstCol, lastCol);
// 数据有效性对象
// 绑定
XSSFDataValidation dataValidationList = (XSSFDataValidation) dvHelper.createValidation(dvConstraint, regions);
dataValidationList.setEmptyCellAllowed(false);
if (dataValidationList instanceof XSSFDataValidation) {
dataValidationList.setSuppressDropDownArrow(true);
dataValidationList.setShowErrorBox(true);
} else {
dataValidationList.setSuppressDropDownArrow(false);
}
// 设置输入信息提示信息
dataValidationList.createPromptBox("下拉选择提示", "请使用下拉方式选择合适的值！");
// 设置输入错误提示信息
//data_validation_list.createErrorBox("选择错误提示", "你输入的值未在备选列表中，请下拉选择合适的值！");
return dataValidationList;
}
计算下拉框公示
/*** 计算formula
*
* @param offset 偏移量，如果给0，表示从A列开始，1，就是从B列
* @param rowId 第几行
* @param colCount 一共多少列
* @return 如果给入参 1,1,10. 表示从B1-K1。最终返回 $B$1:$K$1
*/
public String getRange(int offset, int rowId, int colCount) {
char start = (char) ('A' + offset);
if (colCount <= 25) {
char end = (char) (start + colCount - 1);
return "$" + start + "$" + rowId + ":$" + end + "$" + rowId;
} else {
char endPrefix = 'A';
char endSuffix = 'A';
// 26-51之间，包括边界（仅两次字母表计算）
if ((colCount - 25) / 26 == 0 || colCount == 51) {
// 边界值
if ((colCount - 25) % 26 == 0) {
endSuffix = (char) ('A' + 25);
} else {
endSuffix = (char) ('A' + (colCount - 25) % 26 - 1);
}
} else {// 51以上
if ((colCount - 25) % 26 == 0) {
endSuffix = (char) ('A' + 25);
endPrefix = (char) (endPrefix + (colCount - 25) / 26 - 1);
} else {
endSuffix = (char) ('A' + (colCount - 25) % 26 - 1);
endPrefix = (char) (endPrefix + (colCount - 25) / 26);
}
}
return "$" + start + "$" + rowId + ":$" + endPrefix + endSuffix + "$" + rowId;
}
}
下拉框做绑定关系
/*** 设置有效性
*
* @param offset 主影响单元格所在列，即此单元格由哪个单元格影响联动
* @param sheet
* @param rowNum 行数
* @param colNum 列数
*/
public void setDataValidation(String offset, XSSFSheet sheet, int rowNum, int colNum) {
XSSFDataValidationHelper dvHelper = new XSSFDataValidationHelper(sheet);
DataValidation dataValidationList;
dataValidationList = getDataValidationByFormula(
"INDIRECT($" + offset + (rowNum) + ")", rowNum, colNum, dvHelper);
sheet.addValidationData(dataValidationList);
}
设置单元格格式
/*** 设置单元格格式
*
* @param workbook
* @return
*/
public XSSFCellStyle createCellStyle(XSSFWorkbook workbook) {
XSSFCellStyle style = workbook.createCellStyle();
//设置下边框
style.setBorderBottom(BorderStyle.THIN);
//设置上边框
style.setBorderTop(BorderStyle.THIN);
//设置左边框
style.setBorderLeft(BorderStyle.THIN);
//设置右边框
style.setBorderRight(BorderStyle.THIN);
return style;
}