poi实现联动数据导出模板工具类
/**
* @throws IOException
* @throws UnsupportedEncodingException
* @Title: createExcelTemplate
* @Description: 生成Excel导入模板
* @param @param fileName Excel文件名称
* @param @param handers Excel列标题(数组)
* @param @param downData 下拉框数据(数组)
* @param @param downRows 下拉列的序号(数组,序号从0开始)
* @param areaFatherNameArr 一级数据
* @param areaFatherNameArrs 所有有子区域的父标题
* @param areaMap 子区域
* @param number 联动级数
* @return void
* @throws
*/
public static void createExcelTemplateByGanged(HttpServletResponse response,String fileName, String[] handers,
List<String[]> downData, List<Integer> downRows,String text,List<Integer> listred,String[] areaFatherNameArr,String[] areaFatherNameArrs,Map<String, String []> areaMap,int number) throws IOException{
XSSFWorkbook wb = new XSSFWorkbook();
XSSFSheet sheet = wb.createSheet("Sheet0");
XSSFRow head0 = sheet.createRow(0);
head0.setHeight((short) 1500);
XSSFFont font = (XSSFFont) wb.createFont();
font.setColor(HSSFColorPredefined.RED.getIndex());
font.setBold(true);
XSSFCellStyle style = wb.createCellStyle();
style.setFont(font);
style.setWrapText(true);
for(int i = 0;i < handers.length;i++){
XSSFCell cell = head0.createCell(i);
if(i==0) {
cell.setCellStyle(style);
cell.setCellValue(new XSSFRichTextString(text));
}
}
Integer num=handers.length-1;
if(num<3) {
num=4;
}
sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, num));
XSSFCellStyle style2 = wb.createCellStyle();
XSSFFont font2 = (XSSFFont) wb.createFont();
font2.setBold(true);
style2.setAlignment(HorizontalAlignment.CENTER);
style2.setFillPattern(FillPatternType.SOLID_FOREGROUND);
style2.setFillForegroundColor(HSSFColorPredefined.RED.getIndex());
style2.setFont(font2);
XSSFCellStyle style3 = wb.createCellStyle();
style3.setAlignment(HorizontalAlignment.CENTER);
style3.setFillPattern(FillPatternType.SOLID_FOREGROUND);
style3.setFillForegroundColor(HSSFColorPredefined.LIGHT_GREEN.getIndex());
// 设置头标签
XSSFRow head = sheet.createRow(1);
for(int i = 0;i < handers.length;i++){
sheet.setColumnWidth(i, 5000);
XSSFCell cell = head.createCell(i);
cell.setCellValue(handers[i]);
if(listred.contains(i)) {
cell.setCellStyle(style2);
}else {
cell.setCellStyle(style3);
}
}
//创建一个专门用来存放地区信息的隐藏sheet页
//因此也不能在现实页之前创建,否则无法隐藏。
Sheet hideSheet = wb.createSheet("sheet1");
//这一行作用是将此sheet隐藏,功能未完成时注释此行,可以查看隐藏sheet中信息是否正确
wb.setSheetHidden(wb.getSheetIndex(hideSheet), true);
int rowId = 0;
// 设置第一行,存省的信息
Row provinceRow = hideSheet.createRow(rowId++);
provinceRow.createCell(0).setCellValue("schoolMap");
for(int i = 0; i < handers.length; i ++){
Cell provinceCell = provinceRow.createCell(i + 1);
provinceCell.setCellValue(handers[i]);
}
// 将具体的数据写入到每一行中,行开头为父级区域,后面是子区域。
for(int i = 0;i < areaFatherNameArrs.length;i++){
String key = areaFatherNameArrs[i];
String[] son = areaMap.get(key);
Row row = hideSheet.createRow(rowId++);
row.createCell(0).setCellValue(key);
for(int j = 0; j < son.length; j ++){
Cell cell = row.createCell(j + 1);
cell.setCellValue(son[j]);
}
// 添加名称管理器
String range = getRange(1, rowId, son.length);
Name name = wb.createName();
//key不可重复
name.setNameName(key);
String formula = "sheet1!" + range;
name.setRefersToFormula(formula);
}
XSSFDataValidationHelper dvHelpers = new XSSFDataValidationHelper((XSSFSheet)sheet);
// 省规则
DataValidationConstraint provConstraint = dvHelpers.createExplicitListConstraint(areaFatherNameArr);
// 四个参数分别是:起始行、终止行、起始列、终止列
CellRangeAddressList provRangeAddressList = new CellRangeAddressList(2, 20000, 0, 0);
DataValidation provinceDataValidation = dvHelpers.createValidation(provConstraint, provRangeAddressList);
//验证
provinceDataValidation.createErrorBox("error", "请选择正确的数据");
provinceDataValidation.setShowErrorBox(true);
provinceDataValidation.setSuppressDropDownArrow(true);
sheet.addValidationData(provinceDataValidation);
//对前1000行设置有效性
for(int i = 2;i < 1000;i++){
for(int j=0;j<number;j++) {
setDataValidation(((char)(65+j)) ,sheet,i,2 + j);
}
}
if(downRows!=null&&downRows.size()>0) {
for(int i=0;i<downRows.size();i++) {
Integer firstcol=downRows.get(i);
String[] textList=downData.get(i);
XSSFDataValidationHelper dvHelper = new XSSFDataValidationHelper(sheet);
XSSFDataValidationConstraint dvConstraint = (XSSFDataValidationConstraint)dvHelper.createExplicitListConstraint(textList);
CellRangeAddressList addressList = null;
XSSFDataValidation validation = null;
addressList = new CellRangeAddressList(2,20000,firstcol,firstcol);
validation = (XSSFDataValidation)dvHelper.createValidation(dvConstraint, addressList);
validation.setSuppressDropDownArrow(true);
validation.setShowErrorBox(true);
sheet.addValidationData(validation);
}
}
String fileNameURL = java.net.URLEncoder.encode(fileName+".xlsx", "UTF-8");
response.setContentType("application/vnd.ms-excel;charset=utf-8");
response.setHeader("content-disposition", "attachment;filename="+fileNameURL+";"+"filename*=utf-8''"+fileNameURL);
response.flushBuffer();
response.setCharacterEncoding("UTF-8");
OutputStream outputStream = response.getOutputStream();
wb.write(response.getOutputStream());
wb.close();
outputStream.flush();
outputStream.close();
}
/**
* 设置有效性
* @param offset 主影响单元格所在列,即此单元格由哪个单元格影响联动
* @param sheet
* @param rowNum 行数
* @param colNum 列数
*/
public static void setDataValidation(char offset,XSSFSheet sheet, int rowNum,int colNum) {
XSSFDataValidationHelper dvHelper = new XSSFDataValidationHelper(sheet);
DataValidation data_validation_list;
data_validation_list = getDataValidationByFormula(
"INDIRECT($" + offset + (rowNum) + ")", rowNum, colNum,dvHelper);
sheet.addValidationData(data_validation_list);
}
/**
* 加载下拉列表内容
* @param formulaString
* @param naturalRowIndex
* @param naturalColumnIndex
* @param dvHelper
* @return
*/
private static 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 data_validation_list = (XSSFDataValidation) dvHelper.createValidation(dvConstraint, regions);
data_validation_list.setEmptyCellAllowed(false);
if (data_validation_list instanceof XSSFDataValidation) {
data_validation_list.setSuppressDropDownArrow(true);
data_validation_list.setShowErrorBox(true);
} else {
data_validation_list.setSuppressDropDownArrow(false);
}
// 设置输入信息提示信息
data_validation_list.createPromptBox("下拉选择提示", "请使用下拉方式选择合适的值!");
// 设置输入错误提示信息
//data_validation_list.createErrorBox("选择错误提示", "你输入的值未在备选列表中,请下拉选择合适的值!");
return data_validation_list;
}
/**
* 计算formula
* @param offset 偏移量,如果给0,表示从A列开始,1,就是从B列
* @param rowId 第几行
* @param colCount 一共多少列
* @return 如果给入参 1,1,10. 表示从B1-K1。最终返回 $B$1:$K$1
*
*/
public static 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';
if ((colCount - 25) / 26 == 0 || colCount == 51) {// 26-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;
}
}
测试代码
@GetMapping("/test")
public void test(HttpServletRequest req,HttpServletResponse resp) {
String fileName="classinfo"+System.currentTimeMillis();
String[] header={"*学校名称","*校区名称","*年级名称","*班级名称"};
//普通下拉框
List<Integer> listi=new ArrayList<Integer>();
List<String[]> list=new ArrayList<String[]>();
String text="填表说明。";
//标题加红
List<Integer> listred=new ArrayList<Integer>();
listred.add(0);
listred.add(1);
listred.add(2);
listred.add(3);
//学校名称
String [] xxmc = {"测试学校1","测试学校2","测试学校3"};
//校区名称
String[] xqmc1= {"测试学校1本校","测试学校1分校"};
String[] xqmc2= {"测试学校2本校","测试学校2分校"};
String[] xqmc3= {"测试学校3本校","测试学校3分校"};
//所有有子区域的父标题
String [] xxmc1= {"测试学校1","测试学校2","测试学校3","测试学校1本校"};
String [] njmc= {"一年级","二年级"};
String [] njmc1= {"七年级","八年级"};
//联动下拉框数据
Map<String, String []> areaMap = new HashMap<String, String[]>();
areaMap.put("测试学校1", xqmc1);
areaMap.put("测试学校2", xqmc2);
areaMap.put("测试学校3", xqmc3);
areaMap.put("测试学校1本校", njmc);
areaMap.put("测试学校2本校", njmc1);
try {
ExcelUtil.createExcelTemplateByGanged(resp, fileName, header, list, listi, text, listred, xxmc,xxmc1, areaMap,2);
} catch (IOException e) {
e.printStackTrace();
}
}