导出Excel模板
动态生成下拉列表，并进行省市区控制选择
1.controller
@SuppressWarnings("deprecation")
@RequestMapping("exportEmployee")
@ResponseBody
public ModelAndView initBatchContractEmployeeExcel(HttpServletRequest request, ModelMap model, HttpSession session) {
String contractIds = request.getParameter("contractIds");
String decodeFileName = CommonUtil.decode("雇员基本信息导入.xls", "gb2312", "ISO8859-1");
HSSFWorkbook wb = empInfoService.exportEmployee(contractIds);
ViewExcel viewExcel = new ViewExcel(decodeFileName, wb);
return new ModelAndView(viewExcel, model);
}
2.service
@SuppressWarnings("deprecation")
@RequestMapping(value = "exportEmployee")
@ResponseBody
public HSSFWorkbook exportEmployee(String contractIds) {
String orgId=SessionUtil.getUserOfficeId();
String path = ServerDirUtil.dataTemplettRoot;
String inpath = path + "/" + "雇员基本信息导入模板.xls";
FileInputStream in = null;
HSSFWorkbook wb = null;
String nameAndIdstr = ""; //合同名称_id
List<ConInfo> conList=conInfoBDao.selectContractListByIds(contractIds.split(","));//查看共选择了几个合同
for (ConInfo conInfo : conList) {
nameAndIdstr += conInfo.getName() + ",";
}
try {
in = new FileInputStream(inpath);
wb = new HSSFWorkbook(new BufferedInputStream(in));
Sheet sheet=wb.getSheetAt(0);
int cellNums=sheet.getRow(0).getLastCellNum();
HSSFSheet hssfsheet = wb.getSheet("雇员信息模板");
HSSFCellStyle style = wb.createCellStyle();
style.setBorderTop(HSSFCellStyle.BORDER_THIN);
style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
style.setBorderRight(HSSFCellStyle.BORDER_THIN);
style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
style.setWrapText(true);
Map<String, Integer> resMap = ExcelUtils.getSecondMap(hssfsheet);
/** 生成普通下拉框 （合同列表） */
// 生成下拉列表
// 只对(0，0)单元格有效
CellRangeAddressList hetongregions = new CellRangeAddressList(2, 1000, 0, 0);//起始行号，终止行号， 起始列号，终止列号
List<String> list = new ArrayList<String>();
for (ConInfo conInfo : conList) {
list.add(conInfo.getName()+","+conInfo.getId());
}
for (int i = 2; i < 1000; i++) {
ExcelUtils.writeHideSheetDate(wb, "hideDateSheet", i, 0, list);
}
// 生成下拉框内容
DVConstraint hetongconstraint = DVConstraint.createExplicitListConstraint(nameAndIdstr.split(","));//下拉框
// 绑定下拉框和作用区域
HSSFDataValidation hetong_data_validation = new HSSFDataValidation(hetongregions, hetongconstraint);
// 对sheet页生效
hssfsheet.addValidationData(hetong_data_validation);
/** 生成普通下拉框 （合同列表） */
/** 获取省市数据 ,给隐藏域做准备-----------开始 */
List<OrdBatchModel> ProvinceIncludCityList =selectProvinceIncludCity();
List<OrdBatchModel> areaList = selectProvinceIncludArea();
creatHideSheetArea(wb, "hideSheetName", areaList);// 写入隐藏域
creatHideSheet(wb, "hideSheetName", ProvinceIncludCityList);// 写入隐藏域
/** 获取省市数据 ,给隐藏域做准备-----------结束 */
/** 第六列G 写入省份下拉框------开始 */
//创建名称管理器
ExcelUtils.createName(wb, "省", "hideSheetName!$A$1:$A$" + ProvinceIncludCityList.size());
HSSFDataValidation valicounty = ExcelUtils.setDataValidation("省", 2, 1000, resMap.get("employee_residence_province"), resMap.get("employee_residence_province"));// 第六列G，2至1000行
hssfsheet.addValidationData(valicounty);
/** 第六列G 写入省份下拉框------结束 */
/** 第7列H 写入城市下拉框和省份级联------开始 */
// 循环创建市和区份对应名称
List<String> provinceList2=new ArrayList<>();
for (OrdBatchModel dictAreaBean : ProvinceIncludCityList) {
ExcelUtils.createName(wb, (dictAreaBean.getDictAreaName()),
"hideSheetName!$C$" + dictAreaBean.getSta() + ":$C$" + dictAreaBean.getEnd());
provinceList2.add(dictAreaBean.getDictAreaName()+","+dictAreaBean.getDictAreaId());
}
List<String> cityList2=new ArrayList<>();
for (OrdBatchModel dictAreaBean2 : areaList) {
ExcelUtils.createName(wb, (dictAreaBean2.getDictAreaName()),
"hideSheetName!$E$" + dictAreaBean2.getSta() + ":$E$" + dictAreaBean2.getEnd());
cityList2.add(dictAreaBean2.getDictAreaName()+","+dictAreaBean2.getDictAreaId());
}
for (int i = 2; i < 1000; i++) {
ExcelUtils.writeHideSheetDate(wb, "hideDateSheet", i, resMap.get("employee_residence_province"), provinceList2);
ExcelUtils.writeHideSheetDate(wb, "hideDateSheet", i, resMap.get("employee_residence_city"), cityList2);
}
// 循环创建有效数据 铺下拉框
for (int i = 2; i <= 1000; i++) {
CellRangeAddressList regions = new CellRangeAddressList(i, i, resMap.get("employee_residence_city"), resMap.get("employee_residence_city"));// 第二行开始至1000行，第7列H
DVConstraint constraint = DVConstraint.createFormulaListConstraint("INDIRECT($Q$" + (i + 1) + ")");//indirect($Q$i)引用Q行的文本,然后名称管理器会找
HSSFDataValidation validation = new HSSFDataValidation(regions, constraint);
hssfsheet.addValidationData(validation);
}
/** 第7列H 写入城市下拉框和省份级联------结束 */
// 循环创建有效数据 铺下拉框
for (int i = 2; i <= 1000; i++) {
CellRangeAddressList regions = new CellRangeAddressList(i, i, resMap.get("employee_residence_area"), resMap.get("employee_residence_area"));// 第二行开始至1000行，第7列H
DVConstraint constraint = DVConstraint.createFormulaListConstraint("INDIRECT($R$" + (i + 1) + ")");
HSSFDataValidation validation = new HSSFDataValidation(regions, constraint);
hssfsheet.addValidationData(validation);
}
//开户银行
List<Dict> banlist = DictUtil.getDictList("bank_account");//MyUtil.getPropertiesByKind("dict.properties", "country_type");
List<String> bankList = new ArrayList<>();
List<String> bankList2=new ArrayList<>();
for (Dict model : banlist) {
bankList.add(model.getLabel());
bankList2.add(model.getLabel()+","+model.getValue());
}
for (int i = 2; i < 1000; i++) {
ExcelUtils.writeHideSheetDate(wb, "hideDateSheet", i,resMap.get("ce_bank_name"), bankList2);
}
CellRangeAddressList bankRegions = new CellRangeAddressList(2, 1000, resMap.get("ce_bank_name"), resMap.get("ce_bank_name"));
// 生成下拉框内容
DVConstraint bankConstraint = DVConstraint.createExplicitListConstraint(bankList.toArray(new String[bankList.size()]));
// 绑定下拉框和作用区域
HSSFDataValidation bankValidation = new HSSFDataValidation(bankRegions, bankConstraint);
// 对sheet页生效
hssfsheet.addValidationData(bankValidation);
//国籍
List<Dict> conlist = DictUtil.getDictList("country");//MyUtil.getPropertiesByKind("dict.properties", "country_type");
List<String> countryList = new ArrayList<>();
List<String> countryList2 = new ArrayList<>();
for (Dict model : conlist) {
countryList.add(model.getLabel());
countryList2.add(model.getLabel()+","+model.getValue());
}
for (int i = 2; i < 1000; i++) {
ExcelUtils.writeHideSheetDate(wb, "hideDateSheet", i, resMap.get("country_type"), countryList2);
}
CellRangeAddressList countryRegions = new CellRangeAddressList(2, 1000, resMap.get("country_type"), resMap.get("country_type"));
// 生成下拉框内容
DVConstraint countryConstraint = DVConstraint.createExplicitListConstraint(countryList.toArray(new String[countryList.size()]));
// 绑定下拉框和作用区域
HSSFDataValidation countryValidation = new HSSFDataValidation(countryRegions, countryConstraint);
// 对sheet页生效
hssfsheet.addValidationData(countryValidation);
//证件类型
List<String> ectList = new ArrayList<>();
List<String> ectList2 = new ArrayList<>();
List<Dict> ctlist = DictUtil.getDictList( "id_type");
for (Dict model : ctlist) {
ectList.add(model.getLabel());
ectList2.add(model.getLabel()+","+model.getValue());
}
for (int i = 2; i < 1000; i++) {
ExcelUtils.writeHideSheetDate(wb, "hideDateSheet", i,resMap.get("employee_certificate_type"), ectList2);
}
CellRangeAddressList ectRegions = new CellRangeAddressList(2, 1000, resMap.get("employee_certificate_type"), resMap.get("employee_certificate_type"));
// 生成下拉框内容
DVConstraint ectConstraint = DVConstraint.createExplicitListConstraint(ectList.toArray(new String[ectList.size()]));
// 绑定下拉框和作用区域
HSSFDataValidation ectValidation = new HSSFDataValidation(ectRegions, ectConstraint);
// 对sheet页生效
hssfsheet.addValidationData(ectValidation);
// 学历
// 生成下拉框内容
List<String> degreeList = new ArrayList<>();
List<String> degreeList2 = new ArrayList<>();
List<Dict> degreeRes =DictUtil.getDictList("degree");
for (Dict model : degreeRes) {
degreeList.add(model.getLabel());
degreeList2.add(model.getLabel() + "," + model.getValue());
}
for (int i = 2; i < 1000; i++) {
ExcelUtils.writeHideSheetDate(wb, "hideDateSheet", i,resMap.get("employee_degree"), degreeList2);
}
CellRangeAddressList degreeRegions = new CellRangeAddressList(2, 1000, resMap.get("employee_degree"), resMap.get("employee_degree"));
// 生成下拉框内容
DVConstraint degreeConstraint = DVConstraint.createExplicitListConstraint(degreeList.toArray(new String[degreeList.size()]));
// 绑定下拉框和作用区域
HSSFDataValidation degreeValidation = new HSSFDataValidation(degreeRegions, degreeConstraint);
// 对sheet页生效
hssfsheet.addValidationData(degreeValidation);
/** 隐藏第二行 */
HSSFRow rowTow = hssfsheet.getRow(1);
if(orgId==null){
hssfsheet.setColumnHidden(9, true);//隐藏列 10 11
hssfsheet.setColumnHidden(10, true);
hssfsheet.setColumnHidden(11, true);
}
rowTow.setZeroHeight(true);
wb.setSheetHidden(wb.getSheetIndex("hideSheetName"), true);
} catch (Exception e) {
logger.error("生成雇员基本信息导入模板出错", e);
} finally {
try {
if (in != null) {
in.close();
}
} catch (IOException e) {
logger.error("生成雇员基本信息导入模板出错", e);
}
}
return wb;
}
/**
* 创建隐藏页和数据域
*
* @param workbook
* @param hideSheetName
*/
public void creatHideSheet(HSSFWorkbook workbook, String hideSheetName, List<OrdBatchModel> provinceIncludCityList) {
HSSFSheet hideselectinfosheet = workbook.getSheet(hideSheetName);// 隐藏一些信息
/** 写下拉数据 开始 */
int tolrows = 0;
// 写数据到隐藏域
for (int k = 0; k < provinceIncludCityList.size(); k++) {
OrdBatchModel dictAreaBean = provinceIncludCityList.get(k);
for (int i = 0; i < dictAreaBean.getItems().size(); i++) {
int columnNum = 0;
HSSFRow row = hideselectinfosheet.getRow(tolrows);
row.createCell(columnNum++);
row.createCell(columnNum++).setCellValue(dictAreaBean.getDictAreaName());
row.createCell(columnNum++).setCellValue(dictAreaBean.getItems().get(i).getDictAreaName());
tolrows++;
}
hideselectinfosheet.getRow(k).getCell(0).setCellValue(dictAreaBean.getDictAreaName());
}
/** 写下拉数据 结束 */
// 设置隐藏页标志
workbook.setSheetHidden(workbook.getSheetIndex(hideSheetName), false);
}
public void creatHideSheetArea(HSSFWorkbook workbook, String hideSheetName, List<OrdBatchModel> areaList) {
int tolrows = 0;
HSSFSheet hideselectinfosheet = workbook.createSheet(hideSheetName);
for (int k = 0; k < areaList.size(); k++) {
OrdBatchModel dictAreaBean = areaList.get(k);
for (int i = 0; i < dictAreaBean.getItems().size(); i++) {
HSSFRow row = hideselectinfosheet.createRow(tolrows);
row.createCell(3).setCellValue(dictAreaBean.getDictAreaName());
row.createCell(4).setCellValue(dictAreaBean.getItems().get(i).getDictAreaName());
tolrows++;
}
}
workbook.setSheetHidden(workbook.getSheetIndex(hideSheetName), false);
}