import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.InputStream;
import java.util.List;
public interface UserExcelService {
void export(HttpServletResponse response, HttpServletRequest request, String deptId, String deptName);
List importExcel(String type, InputStream inputStream);
}
import com.netintech.api.domain.operationsystemapplication.operation.jggl.jgbmgl.Jgbmxxb;
import com.netintech.api.domain.operationsystemapplication.system.CommonDict;
import com.netintech.api.domain.operationsystemapplication.system.Dept;
import com.netintech.api.domain.operationsystemapplication.system.DictData;
import com.netintech.api.domain.operationsystemapplication.system.Role;
import com.netintech.common.core.utils.StringUtils;
import com.netintech.core.utils.file.DownloadFileUtil;
import com.netintech.core.utils.poi.PoiUtil;
import com.netintech.operation.jggl.jgbmgl.mapper.JgbmxxbMapper;
import com.netintech.operation.jggl.jgbmgl.service.IJgbmxxbService;
import com.netintech.operation.rolegroup.mapper.RoleGroupMapper;
import com.netintech.project.system.commondict.mapper.CommonDictMapper;
import com.netintech.project.system.dept.mapper.DeptMapper;
import com.netintech.project.system.dict.mapper.DictDataMapper;
import com.netintech.project.system.role.mapper.RoleMapper;
import com.netintech.project.system.role.service.IRoleService;
import com.netintech.project.system.user.service.UserExcelService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.InputStream;
import java.text.SimpleDateFormat;
import java.util.*;
import static com.netintech.project.system.role.service.RoleServiceImpl.NOACCESS;
@Service
public class UserExcelServiceImpl implements UserExcelService {
@Autowired
private JgbmxxbMapper jgbmxxbMapper;
@Autowired
private RoleMapper roleMapper;
@Autowired
private RoleGroupMapper roleGroupMapper;
@Autowired
private DictDataMapper dictDataMapper;
@Autowired
private CommonDictMapper commonDictMapper;
//可以转成你想要的格式 yyyy/MM/dd HH:mm:ss 等等
private final SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd-HH-mm-ss");
@Override
public void export(HttpServletResponse response, HttpServletRequest request, String deptId, String deptName) {
//获取当前部门的所属区域
// Jgbmxxb jgbmxxb = jgbmxxbMapper.selectJgbmxxbById(deptId);
// String ssquy = jgbmxxb.getSsquy();
// String ssquyName = jgbmxxb.getSsquyName();
//获取当前部门下的角色
List<Role> roles = roleMapper.selectRoleList(Role.builder().deptId(deptId).build());
for (Role ro : roles)
{
ro.setSfjsz(roleGroupMapper.selectCountByRoleId(ro.getRoleId())==0 ? StringUtils.EMPTY : NOACCESS);
}
List<String> headJgbmxxbFirst = new ArrayList<>();
List<String> headJgbmxxbSecond = new ArrayList<>();
//关系map
Map<String, List<String>> map = new HashMap<>();
//所属区镇（一级）
Jgbmxxb jgbmxxbFirst = jgbmxxbMapper.selectJgbmxxbById(deptId);
headJgbmxxbFirst.add(jgbmxxbFirst.getBmmc() + "_" + jgbmxxbFirst.getId().replace("-", "_"));
//所属村社区/办事处（二级）
List<Jgbmxxb> jgbmxxbSecond = jgbmxxbMapper.selectJgbmListBySjbm(deptId);
//所属村社区/网格（三级）
if(jgbmxxbSecond != null && jgbmxxbSecond.size() > 0){
for (Jgbmxxb jgbm : jgbmxxbSecond) {
headJgbmxxbSecond.add(jgbm.getBmmc() + "_" + jgbm.getId().replace("-", "_"));
List<Jgbmxxb> jgbmxxbListThird = jgbmxxbMapper.selectJgbmListBySjbm(jgbm.getId());
if(jgbmxxbListThird != null && jgbmxxbListThird.size() > 0){
List<String> headJgbmxxbThird = new ArrayList<>();
for (Jgbmxxb jgbmxxb : jgbmxxbListThird) {
headJgbmxxbThird.add(jgbmxxb.getBmmc() + "_" + jgbmxxb.getId().replace("-", "_"));
}
map.put(jgbm.getBmmc() + "_" + jgbm.getId().replace("-", "_"), headJgbmxxbThird);
}
}
}
map.put(headJgbmxxbFirst.get(0), headJgbmxxbSecond);
//性别
List<DictData> sexList = dictDataMapper.selectDictDataByType("sys_user_sex");
List<String> sexSelect = new ArrayList<>();
if(sexList != null && sexList.size() > 0){
for (DictData dictData : sexList) {
sexSelect.add(dictData.getDictLabel() + "_" + dictData.getDictValue().replace("-", "_"));
}
}
//用户类型
List<CommonDict> account_accountType = commonDictMapper.selectChildernCommonListByGroup(CommonDict.builder().optionGroup("Account_AccountType").build());
List<String> userTypeSelect = new ArrayList<>();
if(account_accountType != null && account_accountType.size() > 0){
for (CommonDict commonDict : account_accountType) {
userTypeSelect.add(commonDict.getDictName() + "_" + commonDict.getId().replace("-", "_"));
}
}
//证件类型
List<CommonDict> zjType = commonDictMapper.selectChildernCommonListByGroup(CommonDict.builder().optionGroup("ZJLX").build());
List<String> zjSelect = new ArrayList<>();
if(zjType != null && zjType.size() > 0){
for (CommonDict commonDict : zjType) {
zjSelect.add(commonDict.getDictName() + "_" + commonDict.getId().replace("-", "_"));
}
}
//派出所名称
List<CommonDict> psType = commonDictMapper.selectChildernCommonListByGroup(CommonDict.builder().optionGroup("policeStationType").build());
List<String> psSelect = new ArrayList<>();
if(psType != null && psType.size() > 0){
for (CommonDict commonDict : psType) {
psSelect.add(commonDict.getDictName() + "_" + commonDict.getId().replace("-", "_"));
}
}
//民族
List<CommonDict> mzType = commonDictMapper.selectChildernCommonListByGroup(CommonDict.builder().optionGroup("nation").build());
List<String> mzSelect = new ArrayList<>();
if(mzType != null && mzType.size() > 0){
for (CommonDict commonDict : mzType) {
mzSelect.add(commonDict.getDictName() + "_" + commonDict.getId().replace("-", "_"));
}
}
//国籍
List<CommonDict> gjType = commonDictMapper.selectChildernCommonListByGroup(CommonDict.builder().optionGroup("nationality").build());
List<String> gjSelect = new ArrayList<>();
if(gjType != null && gjType.size() > 0){
for (CommonDict commonDict : gjType) {
gjSelect.add(commonDict.getDictName() + "_" + commonDict.getId().replace("-", "_"));
}
}
//是否领导，是否开通账号,是否启用自动添加所属区划的数据权限,是否移动端人员
List<DictData> ynList = dictDataMapper.selectDictDataByType("sys_yes_no");
List<String> ynSelect = new ArrayList<>();
if(ynList != null && ynList.size() > 0){
for (DictData dictData : ynList) {
ynSelect.add(dictData.getDictLabel() + "_" + dictData.getDictValue().replace("-", "_"));
}
}
//设置状态
List<DictData> szztList = dictDataMapper.selectDictDataByType("sys_normal_disable");
List<String> szztSelect = new ArrayList<>();
if(szztList != null && szztList.size() > 0){
for (DictData dictData : szztList) {
szztSelect.add(dictData.getDictLabel() + "_" + dictData.getDictValue().replace("-", "_"));
}
}
//账号类型
List<CommonDict> zhType = commonDictMapper.selectChildernCommonListByGroup(CommonDict.builder().optionGroup("ZHLX").build());
List<String> zhSelect = new ArrayList<>();
if(zhType != null && zhType.size() > 0){
for (CommonDict commonDict : zhType) {
zhSelect.add(commonDict.getDictName() + "_" + commonDict.getId().replace("-", "_"));
}
}
//人员类别
List<CommonDict> commonDicts = commonDictMapper.selectCommonDictList(CommonDict.builder().parentId("DA9535386FCC4D15AC94CA20549B4F98").build());
List<String> rylbFirstList = new ArrayList<>();
Map<String, List<String>> rylbMap = new HashMap<>();
if(commonDicts != null && commonDicts.size()>0){
for (CommonDict commonDict : commonDicts) {
rylbFirstList.add(commonDict.getDictName() + "_" + commonDict.getDictCode().replace("-", "_"));
}
for (CommonDict commonDict : commonDicts) {
List<CommonDict> commonDicts1 = commonDictMapper.selectCommonDictList(CommonDict.builder().parentId(commonDict.getId()).build());
List<String> rylbSecondList = new ArrayList<>();
if(commonDicts1 != null && commonDicts1.size()>0){
for (CommonDict dict : commonDicts1) {
rylbSecondList.add(dict.getDictName() + "_" + dict.getDictCode().replace("-", "_"));
}
}
rylbMap.put(commonDict.getDictName() + "_" + commonDict.getDictCode().replace("-", "_"), rylbSecondList);
}
}
String dateString = format.format(new Date());
//文件地址名称
String filePath = "D:/usr/UserImport_" + dateString + ".xls";
//excel 表头
List<String> headers = Arrays.asList("所属区镇（一级）", "所属村社区/办事处（二级）", "所属村社区/网格（三级）", "姓名", "性别", "用户类型"
, "职务", "证件类型", "证件号码", "派出所名称", "警务编号", "派出所编号", "民族", "国籍", "手机号码", "电话号码", "邮件"
, "是否领导", "备注", "是否开通账号", "上传头像", "用户名称", "绑定IP", "绑定MAC", "设置状态", "账号类型", "是否启用自动添加所属区划的数据权限"
, "是否移动端人员", "人员类别一级", "人员类别二级", "工卡号", "角色");
//需要显示在excel的信息
List list = new ArrayList();
PoiUtil.export(filePath, headers, headJgbmxxbFirst, map, sexSelect, userTypeSelect, zjSelect, psSelect, mzSelect, gjSelect,
ynSelect, szztSelect, zhSelect, rylbFirstList, rylbMap, list);
System.out.println(headJgbmxxbFirst);
System.out.println(map);
//获取文件流返回给客户端
DownloadFileUtil.downloadFile(response, request, filePath);
}
@Override
public List importExcel(String type, InputStream inputStream) {
List list = new ArrayList();
try {
list = PoiUtil.importExcel(inputStream);
} catch (Throwable t) {
t.printStackTrace();
}
return list;
}
}
import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.*;
/**
* @description:
* @author: SUNHONGJIANG
* @time: 2022/6/8 9:56
*/
public class DownloadFileUtil {
/**
* 下载文件
*
* @param response
* @param request
* @param filePath 文件地址
* @throws Exception
*/
public static void downloadFile(HttpServletResponse response, HttpServletRequest request, String filePath) {
try {
//获取文件
File file = new File(filePath);
String fileName = file.getName();
response.reset();
ServletOutputStream out = response.getOutputStream();
request.setCharacterEncoding("UTF-8");
int BUFFER = 1024 * 10;
byte data[] = new byte[BUFFER];
BufferedInputStream bis = null;
//获取文件输入流
InputStream inputStream = new BufferedInputStream(new FileInputStream(filePath));
// 以流的形式下载文件。
DataInputStream fis = new DataInputStream(inputStream);
int read;
bis = new BufferedInputStream(fis, BUFFER);
response.setContentType("application/OCTET-STREAM");
response.setHeader("Content-Disposition", "attachment; filename=" + fileName);
while ((read = bis.read(data)) != -1) {
out.write(data, 0, read);
}
fis.close();
bis.close();
} catch (Exception e) {
e.printStackTrace();
} finally {
// File file = new File(filePath);
// //删除临时文件
// if (file.exists()) {
// file.delete();
// }
}
}
}
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddressList;
import org.apache.poi.util.IOUtils;
import java.io.File;
import java.io.FileOutputStream;
import java.io.InputStream;
import java.lang.reflect.Field;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
/**
* @description:
* @author: SUNHONGJIANG
* @time: 2022/6/8 9:56
*/
public class PoiUtil {
private static final String MAIN_SHEET = "信息";
private static final String SHEET_MAP = "map";
private static final String RYLB_MAP = "人员类别";
private static final String SEX_STATUS = "性别";
private static final String USER_TYPE = "用户类型";
private static final String ZJ_TYPE = "证件类型";
private static final String PS_TYPE = "派出所名称";
private static final String MZ_TYPE = "民族";
private static final String GJ_TYPE = "国籍";
private static final String YN_TYPE = "是否";
private static final String SZZT_TYPE = "设置状态";
private static final String ZH_TYPE = "账号类型";
/**
* 计算formula
*
* @param offset 偏移量，如果给0，表示从A列开始，1，就是从B列
* @param rowId 第几行
* @param colCount 一共多少列
* @return 如果给入参 1,1,10. 表示从B1-K1。最终返回 $B$1:$K$1
*/
private 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;
}
}
//-----------------------------------------------------------
/**
* 导出三级联动and单个下拉框的excel
*
* @param filePath 文件输出地址
* @param headers 表头
* @param mapOneList 一级所有内容
* @param map 三级联动对应内容
* @param list 导出数据
*/
public static void export(String filePath, List<String> headers, List<String> mapOneList, Map<String, List<String>> map,
List<String> sexSelect, List<String> userTypeSelect, List<String> zjSelect, List<String> psSelect,
List<String> mzSelect, List<String> gjSelect, List<String> ynSelect, List<String> szztSelect,
List<String> zhSelect, List<String> rylbFirstList, Map<String, List<String>> rylbMap,List list) {
// 2.创建Excel
// 1)创建workbook
HSSFWorkbook hssfWorkBook = new HSSFWorkbook();
// 2)创建sheet
HSSFSheet mainSheet = hssfWorkBook.createSheet(MAIN_SHEET);// 主sheet
// 用于展示
//2.1 创建表头，供用户输入
initHeaders(hssfWorkBook, mainSheet, headers);
//导出数据到主sheet
setMainSheet(mainSheet, list);
// attribute 下拉框 sheet
HSSFSheet sexSheet = hssfWorkBook.createSheet(SEX_STATUS);
HSSFSheet userTypeSheet = hssfWorkBook.createSheet(USER_TYPE);
HSSFSheet zjSheet = hssfWorkBook.createSheet(ZJ_TYPE);
HSSFSheet psSheet = hssfWorkBook.createSheet(PS_TYPE);
HSSFSheet mzSheet = hssfWorkBook.createSheet(MZ_TYPE);
HSSFSheet gjSheet = hssfWorkBook.createSheet(GJ_TYPE);
HSSFSheet ynSheet = hssfWorkBook.createSheet(YN_TYPE);
HSSFSheet szztSheet = hssfWorkBook.createSheet(SZZT_TYPE);
HSSFSheet zhSheet = hssfWorkBook.createSheet(ZH_TYPE);
//三级联动 sheet
HSSFSheet mapSheet = hssfWorkBook.createSheet(SHEET_MAP);
//二级联动 sheet
HSSFSheet rylbMapSheet = hssfWorkBook.createSheet(RYLB_MAP);
// true:隐藏/false:显示
//省市区关系sheet
hssfWorkBook.setSheetHidden(hssfWorkBook.getSheetIndex(mapSheet), false);// 设置sheet是否隐藏
//单个下拉框sheet
hssfWorkBook.setSheetHidden(hssfWorkBook.getSheetIndex(sexSheet), false);// 设置sheet是否隐藏
hssfWorkBook.setSheetHidden(hssfWorkBook.getSheetIndex(userTypeSheet), false);// 设置sheet是否隐藏
hssfWorkBook.setSheetHidden(hssfWorkBook.getSheetIndex(zjSheet), false);// 设置sheet是否隐藏
hssfWorkBook.setSheetHidden(hssfWorkBook.getSheetIndex(psSheet), false);// 设置sheet是否隐藏
hssfWorkBook.setSheetHidden(hssfWorkBook.getSheetIndex(mzSheet), false);// 设置sheet是否隐藏
hssfWorkBook.setSheetHidden(hssfWorkBook.getSheetIndex(gjSheet), false);// 设置sheet是否隐藏
hssfWorkBook.setSheetHidden(hssfWorkBook.getSheetIndex(ynSheet), false);// 设置sheet是否隐藏
hssfWorkBook.setSheetHidden(hssfWorkBook.getSheetIndex(szztSheet), false);// 设置sheet是否隐藏
hssfWorkBook.setSheetHidden(hssfWorkBook.getSheetIndex(zhSheet), false);// 设置sheet是否隐藏
// 3.写入数据
writeData(hssfWorkBook, mapSheet, mapOneList, map);// 将数据写入隐藏的sheet中并做好关联关系
writeData(hssfWorkBook, rylbMapSheet, rylbFirstList, rylbMap);// 将数据写入隐藏的sheet中并做好关联关系
//3.1 写入单个下拉数据
writeDropDownData(hssfWorkBook, sexSheet, sexSelect, SEX_STATUS);
writeDropDownData(hssfWorkBook, userTypeSheet, userTypeSelect, USER_TYPE);
writeDropDownData(hssfWorkBook, zjSheet, zjSelect, ZJ_TYPE);
writeDropDownData(hssfWorkBook, psSheet, psSelect, PS_TYPE);
writeDropDownData(hssfWorkBook, mzSheet, mzSelect, MZ_TYPE);
writeDropDownData(hssfWorkBook, gjSheet, gjSelect, GJ_TYPE);
writeDropDownData(hssfWorkBook, ynSheet, ynSelect, YN_TYPE);
writeDropDownData(hssfWorkBook, szztSheet, szztSelect, SZZT_TYPE);
writeDropDownData(hssfWorkBook, zhSheet, zhSelect, ZH_TYPE);
// 4.设置数据有效性
setDataValid(hssfWorkBook, mainSheet, mapOneList, map, rylbFirstList, rylbMap);
FileOutputStream os = null;
try {
String exisname = filePath.substring(0, filePath.lastIndexOf("/"));
File f = new File(exisname);
if (!f.exists()) {
f.mkdirs();//创建目录
}
// 创建可写入的Excel工作簿
File file = new File(filePath);
if (!file.exists()) {
boolean bool = file.createNewFile();
System.out.println(bool);
} else {
file.delete();
file.createNewFile();
}
os = new FileOutputStream(filePath);
hssfWorkBook.write(os);
} catch (Exception e) {
e.printStackTrace();
} finally {
IOUtils.closeQuietly(os);
}
}
/**
* 生成主页面表头
*
* @param wb
* @param mainSheet
* @param headers
*/
private static void initHeaders(HSSFWorkbook wb, HSSFSheet mainSheet, List<String> headers) {
//生成主内容
HSSFRow rowFirst = mainSheet.createRow(0);//第一个sheet的第一行为标题
mainSheet.createFreezePane(0, 1, 0, 1); //冻结第一行
//写标题
for (int i = 0; i < headers.size(); i++) {
HSSFCell cell = rowFirst.createCell(i); //获取第一行的每个单元格
mainSheet.setColumnWidth(i, 4000); //设置每列的列宽
//表头样式
HSSFCellStyle style = wb.createCellStyle();
style.setAlignment(HorizontalAlignment.CENTER); // 创建一个居中格式
//字体样式
HSSFFont fontStyle = wb.createFont();
fontStyle.setFontName("微软雅黑");
fontStyle.setFontHeightInPoints((short) 12);
if("所属区镇（一级）".equals(headers.get(i))){
fontStyle.setColor(HSSFFont.COLOR_RED);
}else if("姓名".equals(headers.get(i))){
fontStyle.setColor(HSSFFont.COLOR_RED);
}else if("手机号码".equals(headers.get(i))){
fontStyle.setColor(HSSFFont.COLOR_RED);
}else{
fontStyle.setColor(HSSFFont.COLOR_NORMAL);
}
style.setFont(fontStyle);
cell.setCellStyle(style); //加样式
cell.setCellValue(headers.get(i)); //往单元格里写数据
}
}
private static void setMainSheet(HSSFSheet mainSheet, List list) {
for (int j = 0; j < list.size(); j++) {
//行
Row row = mainSheet.createRow(j + 1);
Object obj = list.get(j);
if (obj != null) {
Field[] fields = obj.getClass().getDeclaredFields();
try {
for (int i = 0; i < fields.length; i++) {
if (i == 0) {
//excel 第一列 为序号
Cell cell = row.createCell(i);
cell.setCellValue(j + 1);
} else {
Field f = fields[i];
f.setAccessible(true);
String name = f.getName();
Object value = f.get(obj);
if (value == null) {
value = "";
}
//列
Cell cell = row.createCell(i);
cell.setCellValue(value.toString());
}
}
} catch (IllegalAccessException e) {
e.printStackTrace();
}
}
}
}
private static void setDataValid(HSSFWorkbook HSSFWorkBook, HSSFSheet mainSheet, List<String> provinceList, Map<String, List<String>> siteMap, List<String> rylbFirstList, Map<String, List<String>> rylbMap) {
//设置一级区镇下拉
HSSFDataValidationHelper dvHelper = new HSSFDataValidationHelper((HSSFSheet) mainSheet);
String[] dataArray = provinceList.toArray(new String[0]);
HSSFSheet hidden = HSSFWorkBook.createSheet("hidden");
HSSFCell cell = null;
for (int i = 0, length = dataArray.length; i < length; i++) {
String name = dataArray[i];
HSSFRow row = hidden.createRow(i);
cell = row.createCell(0);
cell.setCellValue(name);
}
Name namedCell = HSSFWorkBook.createName();
namedCell.setNameName("hidden");
namedCell.setRefersToFormula("hidden!$A$1:$A$" + dataArray.length);
//加载数据,将名称为hidden的
DVConstraint constraint = DVConstraint.createFormulaListConstraint("hidden");
// 四个参数分别是：起始行、终止行、起始列、终止列.
// 1 (省份下拉框代表从excel第1+1行开始) 10(省份下拉框代表从excel第1+10行结束) 1(代表第几列开始，0是第一列，1是第二列) 1(代表第几列结束，0是第一列，1是第二列)
CellRangeAddressList provinceRangeAddressList = new CellRangeAddressList(1, 9999, 0, 0);
DataValidation provinceDataValidation = dvHelper.createValidation(constraint, provinceRangeAddressList);
provinceDataValidation.createErrorBox("error", "请选择正确");
provinceDataValidation.setShowErrorBox(true);
// provinceDataValidation.setSuppressDropDownArrow(true);
//将第二个sheet设置为隐藏
// true:隐藏/false:显示
HSSFWorkBook.setSheetHidden(HSSFWorkBook.getSheetIndex(hidden), true);// 设置sheet是否隐藏
mainSheet.addValidationData(provinceDataValidation);
//人员类别
String[] rylbDataArray = rylbFirstList.toArray(new String[0]);
HSSFSheet hiddenSheet = HSSFWorkBook.createSheet("hidden1");
HSSFCell rylbCell = null;
for (int i = 0, length = rylbDataArray.length; i < length; i++) {
String name = rylbDataArray[i];
HSSFRow row = hiddenSheet.createRow(i);
rylbCell = row.createCell(0);
rylbCell.setCellValue(name);
}
Name rylbNamedCell = HSSFWorkBook.createName();
rylbNamedCell.setNameName("hidden1");
rylbNamedCell.setRefersToFormula("hidden1!$A$1:$A$" + rylbDataArray.length);
//加载数据,将名称为hidden的
DVConstraint rylbConstraint = DVConstraint.createFormulaListConstraint("hidden1");
// 四个参数分别是：起始行、终止行、起始列、终止列.
// 1 (省份下拉框代表从excel第1+1行开始) 10(省份下拉框代表从excel第1+10行结束) 1(代表第几列开始，0是第一列，1是第二列) 1(代表第几列结束，0是第一列，1是第二列)
CellRangeAddressList rylbRangeAddressList = new CellRangeAddressList(1, 9999, 28, 28);
DataValidation rylbDataValidation = dvHelper.createValidation(rylbConstraint, rylbRangeAddressList);
rylbDataValidation.createErrorBox("error", "请选择正确");
rylbDataValidation.setShowErrorBox(true);
// provinceDataValidation.setSuppressDropDownArrow(true);
//将第二个sheet设置为隐藏
// true:隐藏/false:显示
HSSFWorkBook.setSheetHidden(HSSFWorkBook.getSheetIndex(hiddenSheet), true);// 设置sheet是否隐藏
mainSheet.addValidationData(rylbDataValidation);
//设置属性下拉 --性别
DataValidationConstraint attributeConstraint = dvHelper.createFormulaListConstraint(SEX_STATUS);
// 四个参数分别是：起始行、终止行、起始列、终止列
// 1 (下拉框代表从excel第1+1行开始) 10(下拉框代表从excel第1+10行结束) 5(代表第几列开始，0是第一列，1是第二列) 5(代表第几列结束，0是第一列，1是第二列)
CellRangeAddressList attributeRangeAddressList = new CellRangeAddressList(1, 9999, 4, 4);
HSSFDataValidation attributeDataValidation = (HSSFDataValidation) dvHelper.createValidation(attributeConstraint, attributeRangeAddressList);
attributeDataValidation.createErrorBox("Error", "请选择或输入有效的选项，或下载最新重试！");
// genderDataValidation.setSuppressDropDownArrow(true);
mainSheet.addValidationData(attributeDataValidation);
//设置属性下拉 --用户类型
DataValidationConstraint userTypeConstraint = dvHelper.createFormulaListConstraint(USER_TYPE);
// 四个参数分别是：起始行、终止行、起始列、终止列
// 1 (下拉框代表从excel第1+1行开始) 10(下拉框代表从excel第1+10行结束) 5(代表第几列开始，0是第一列，1是第二列) 5(代表第几列结束，0是第一列，1是第二列)
CellRangeAddressList userTypeRangeAddressList = new CellRangeAddressList(1, 9999, 5, 5);
HSSFDataValidation userTypeDataValidation = (HSSFDataValidation) dvHelper.createValidation(userTypeConstraint, userTypeRangeAddressList);
userTypeDataValidation.createErrorBox("Error", "请选择或输入有效的选项，或下载最新重试！");
mainSheet.addValidationData(userTypeDataValidation);
//设置属性下拉 --证件类型
DataValidationConstraint zjConstraint = dvHelper.createFormulaListConstraint(ZJ_TYPE);
// 四个参数分别是：起始行、终止行、起始列、终止列
// 1 (下拉框代表从excel第1+1行开始) 10(下拉框代表从excel第1+10行结束) 5(代表第几列开始，0是第一列，1是第二列) 5(代表第几列结束，0是第一列，1是第二列)
CellRangeAddressList zjRangeAddressList = new CellRangeAddressList(1, 9999, 7, 7);
HSSFDataValidation zjDataValidation = (HSSFDataValidation) dvHelper.createValidation(zjConstraint, zjRangeAddressList);
zjDataValidation.createErrorBox("Error", "请选择或输入有效的选项，或下载最新重试！");
mainSheet.addValidationData(zjDataValidation);
//设置属性下拉 --派出所名称
DataValidationConstraint psConstraint = dvHelper.createFormulaListConstraint(PS_TYPE);
// 四个参数分别是：起始行、终止行、起始列、终止列
// 1 (下拉框代表从excel第1+1行开始) 10(下拉框代表从excel第1+10行结束) 5(代表第几列开始，0是第一列，1是第二列) 5(代表第几列结束，0是第一列，1是第二列)
CellRangeAddressList psRangeAddressList = new CellRangeAddressList(1, 9999, 9, 9);
HSSFDataValidation psDataValidation = (HSSFDataValidation) dvHelper.createValidation(psConstraint, psRangeAddressList);
psDataValidation.createErrorBox("Error", "请选择或输入有效的选项，或下载最新重试！");
mainSheet.addValidationData(psDataValidation);
//设置属性下拉 --民族
DataValidationConstraint mzConstraint = dvHelper.createFormulaListConstraint(MZ_TYPE);
// 四个参数分别是：起始行、终止行、起始列、终止列
// 1 (下拉框代表从excel第1+1行开始) 10(下拉框代表从excel第1+10行结束) 5(代表第几列开始，0是第一列，1是第二列) 5(代表第几列结束，0是第一列，1是第二列)
CellRangeAddressList mzRangeAddressList = new CellRangeAddressList(1, 9999, 12, 12);
HSSFDataValidation mzDataValidation = (HSSFDataValidation) dvHelper.createValidation(mzConstraint, mzRangeAddressList);
mzDataValidation.createErrorBox("Error", "请选择或输入有效的选项，或下载最新重试！");
mainSheet.addValidationData(mzDataValidation);
//设置属性下拉 --国籍
DataValidationConstraint gjConstraint = dvHelper.createFormulaListConstraint(GJ_TYPE);
// 四个参数分别是：起始行、终止行、起始列、终止列
// 1 (下拉框代表从excel第1+1行开始) 10(下拉框代表从excel第1+10行结束) 5(代表第几列开始，0是第一列，1是第二列) 5(代表第几列结束，0是第一列，1是第二列)
CellRangeAddressList gjRangeAddressList = new CellRangeAddressList(1, 9999, 13, 13);
HSSFDataValidation gjDataValidation = (HSSFDataValidation) dvHelper.createValidation(gjConstraint, gjRangeAddressList);
gjDataValidation.createErrorBox("Error", "请选择或输入有效的选项，或下载最新重试！");
mainSheet.addValidationData(gjDataValidation);
//设置属性下拉 --是否领导
DataValidationConstraint sfldConstraint = dvHelper.createFormulaListConstraint(YN_TYPE);
// 四个参数分别是：起始行、终止行、起始列、终止列
// 1 (下拉框代表从excel第1+1行开始) 10(下拉框代表从excel第1+10行结束) 5(代表第几列开始，0是第一列，1是第二列) 5(代表第几列结束，0是第一列，1是第二列)
CellRangeAddressList sfldRangeAddressList = new CellRangeAddressList(1, 9999, 17, 17);
HSSFDataValidation sfldDataValidation = (HSSFDataValidation) dvHelper.createValidation(sfldConstraint, sfldRangeAddressList);
sfldDataValidation.createErrorBox("Error", "请选择或输入有效的选项，或下载最新重试！");
mainSheet.addValidationData(sfldDataValidation);
//设置属性下拉 --是否开通账号
DataValidationConstraint sfktzhConstraint = dvHelper.createFormulaListConstraint(YN_TYPE);
// 四个参数分别是：起始行、终止行、起始列、终止列
// 1 (下拉框代表从excel第1+1行开始) 10(下拉框代表从excel第1+10行结束) 5(代表第几列开始，0是第一列，1是第二列) 5(代表第几列结束，0是第一列，1是第二列)
CellRangeAddressList sfktzhRangeAddressList = new CellRangeAddressList(1, 9999, 19, 19);
HSSFDataValidation sfktzhDataValidation = (HSSFDataValidation) dvHelper.createValidation(sfktzhConstraint, sfktzhRangeAddressList);
sfktzhDataValidation.createErrorBox("Error", "请选择或输入有效的选项，或下载最新重试！");
mainSheet.addValidationData(sfktzhDataValidation);
//设置属性下拉 --设置状态
DataValidationConstraint szztConstraint = dvHelper.createFormulaListConstraint(SZZT_TYPE);
// 四个参数分别是：起始行、终止行、起始列、终止列
// 1 (下拉框代表从excel第1+1行开始) 10(下拉框代表从excel第1+10行结束) 5(代表第几列开始，0是第一列，1是第二列) 5(代表第几列结束，0是第一列，1是第二列)
CellRangeAddressList szztRangeAddressList = new CellRangeAddressList(1, 9999, 24, 24);
HSSFDataValidation szztDataValidation = (HSSFDataValidation) dvHelper.createValidation(szztConstraint, szztRangeAddressList);
szztDataValidation.createErrorBox("Error", "请选择或输入有效的选项，或下载最新重试！");
mainSheet.addValidationData(szztDataValidation);
//设置属性下拉 --账号类型
DataValidationConstraint zhConstraint = dvHelper.createFormulaListConstraint(ZH_TYPE);
// 四个参数分别是：起始行、终止行、起始列、终止列
// 1 (下拉框代表从excel第1+1行开始) 10(下拉框代表从excel第1+10行结束) 5(代表第几列开始，0是第一列，1是第二列) 5(代表第几列结束，0是第一列，1是第二列)
CellRangeAddressList zhRangeAddressList = new CellRangeAddressList(1, 9999, 25, 25);
HSSFDataValidation zhDataValidation = (HSSFDataValidation) dvHelper.createValidation(zhConstraint, zhRangeAddressList);
zhDataValidation.createErrorBox("Error", "请选择或输入有效的选项，或下载最新重试！");
mainSheet.addValidationData(zhDataValidation);
//设置属性下拉 --是否启用自动添加所属区划的数据权限
DataValidationConstraint qxConstraint = dvHelper.createFormulaListConstraint(YN_TYPE);
// 四个参数分别是：起始行、终止行、起始列、终止列
// 1 (下拉框代表从excel第1+1行开始) 10(下拉框代表从excel第1+10行结束) 5(代表第几列开始，0是第一列，1是第二列) 5(代表第几列结束，0是第一列，1是第二列)
CellRangeAddressList qxRangeAddressList = new CellRangeAddressList(1, 9999, 26, 26);
HSSFDataValidation qxDataValidation = (HSSFDataValidation) dvHelper.createValidation(qxConstraint, qxRangeAddressList);
qxDataValidation.createErrorBox("Error", "请选择或输入有效的选项，或下载最新重试！");
mainSheet.addValidationData(qxDataValidation);
//设置属性下拉 --是否移动端人员
DataValidationConstraint yydConstraint = dvHelper.createFormulaListConstraint(YN_TYPE);
// 四个参数分别是：起始行、终止行、起始列、终止列
// 1 (下拉框代表从excel第1+1行开始) 10(下拉框代表从excel第1+10行结束) 5(代表第几列开始，0是第一列，1是第二列) 5(代表第几列结束，0是第一列，1是第二列)
CellRangeAddressList yydRangeAddressList = new CellRangeAddressList(1, 9999, 27, 27);
HSSFDataValidation yydDataValidation = (HSSFDataValidation) dvHelper.createValidation(yydConstraint, yydRangeAddressList);
yydDataValidation.createErrorBox("Error", "请选择或输入有效的选项，或下载最新重试！");
mainSheet.addValidationData(yydDataValidation);
// 设置市、区下拉
// i <= 10 ,10代表市、区下拉框到10+1行结束
for (int i = 0; i <= 10; i++) {
setDataValidation('A', mainSheet, i + 1, 1);// "B"是指父类所在的列，i+1初始值为1代表从第2行开始，2要与“B”对应，为B的列号加1，假如第一个参数为“C”，那么最后一个参数就3
}
for (int i = 0; i <= 10; i++) {
setRylbDataValidation("AC", mainSheet, i + 1, 29);// "B"是指父类所在的列，i+1初始值为1代表从第2行开始，2要与“B”对应，为B的列号加1，假如第一个参数为“C”，那么最后一个参数就3
}
}
/**
* 设置有效性
*
* @param offset 主影响单元格所在列，即此单元格由哪个单元格影响联动
* @param sheet
* @param rowNum 行数
* @param colNum 列数
*/
private static void setDataValidation(char offset, HSSFSheet sheet, int rowNum, int colNum) {
HSSFDataValidationHelper dvHelper = new HSSFDataValidationHelper(sheet);
DataValidation dataValidationList1;
DataValidation dataValidationList2;
dataValidationList1 = getDataValidationByFormula("INDIRECT($" + offset + (rowNum) + ")", rowNum, colNum, dvHelper);
dataValidationList2 = getDataValidationByFormula("INDIRECT($" + (char)(offset + 1) + (rowNum) + ")", rowNum, colNum + 1, dvHelper);
sheet.addValidationData(dataValidationList1);
sheet.addValidationData(dataValidationList2);
}
/**
* 设置有效性
*
* @param offset 主影响单元格所在列，即此单元格由哪个单元格影响联动
* @param sheet
* @param rowNum 行数
* @param colNum 列数
*/
private static void setRylbDataValidation(String offset, HSSFSheet sheet, int rowNum, int colNum) {
HSSFDataValidationHelper dvHelper = new HSSFDataValidationHelper(sheet);
DataValidation dataValidationList1;
dataValidationList1 = getDataValidationByFormula("INDIRECT($" + offset + (rowNum) + ")", rowNum, colNum, dvHelper);
sheet.addValidationData(dataValidationList1);
}
private static DataValidation getDataValidationByFormula(String formulaString, int naturalRowIndex, int naturalColumnIndex, HSSFDataValidationHelper dvHelper) {
DataValidationConstraint dvConstraint = dvHelper.createFormulaListConstraint(formulaString);
CellRangeAddressList regions = new CellRangeAddressList(naturalRowIndex, 65535, naturalColumnIndex, naturalColumnIndex);
HSSFDataValidation data_validation_list = (HSSFDataValidation) dvHelper.createValidation(dvConstraint, regions);
data_validation_list.setEmptyCellAllowed(false);
if (data_validation_list instanceof HSSFDataValidation) {
// data_validation_list.setSuppressDropDownArrow(true);
data_validation_list.setShowErrorBox(true);
} else {
// data_validation_list.setSuppressDropDownArrow(false);
}
// 设置输入信息提示信息
data_validation_list.createPromptBox("下拉选择提示", "请使用下拉方式选择合适的值！");
return data_validation_list;
}
/**
* 循环单个下拉框的数据写入genderSheet的第A列中
*
* @param hssfWorkBook
* @param sheet
* @param list
* @param name sheet名称
*/
private static void writeDropDownData(HSSFWorkbook hssfWorkBook, HSSFSheet sheet, List<String> list, String name) {
//循环单个下拉框的数据写入genderSheet的第A列中
for (int i = 0; i < list.size(); i++) {
HSSFRow genderRow = sheet.createRow(i);
genderRow.createCell(0).setCellValue(list.get(i));
}
initGenderMapping(hssfWorkBook, sheet.getSheetName(), list.size(), name);// 创建数据规则
}
private static void writeData(HSSFWorkbook hssfWorkBook, HSSFSheet mapSheet, List<String> provinceList, Map<String, List<String>> siteMap) {
//循环将父数据写入siteSheet的第1行中
int siteRowId = 0;
HSSFRow provinceRow = mapSheet.createRow(siteRowId++);
provinceRow.createCell(0).setCellValue("父列表");
for (int i = 0; i < provinceList.size(); i++) {
provinceRow.createCell(i + 1).setCellValue(provinceList.get(i));
}
// 将具体的数据写入到每一行中，行开头为父级区域，后面是子区域。
Iterator<String> keyIterator = siteMap.keySet().iterator();
while (keyIterator.hasNext()) {
String key = keyIterator.next();
List<String> son = siteMap.get(key);
HSSFRow siteRow = mapSheet.createRow(siteRowId++);
siteRow.createCell(0).setCellValue(key);
for (int i = 0; i < son.size(); i++) {
siteRow.createCell(i + 1).setCellValue(son.get(i));
}
// 添加名称管理器
String range = getRange(1, siteRowId, son.size());
Name name = hssfWorkBook.createName();
name.setNameName(key);
String formula = mapSheet.getSheetName() + "!" + range;
name.setRefersToFormula(formula);
}
}
/**
* 创建下拉数据规则
*
* @param workbook
* @param genderSheetName
* @param genderQuantity
* @param name
*/
private static void initGenderMapping(HSSFWorkbook workbook, String genderSheetName, int genderQuantity, String name) {
Name genderName = workbook.createName();
genderName.setNameName(name);
genderName.setRefersToFormula(genderSheetName + "!$A$1:$A$" + genderQuantity);
}
// 测试时间格式
private static void setDateFormat(HSSFWorkbook HSSFWorkbook, HSSFSheet mainsheet) {
HSSFDataValidationHelper dvHelper = new HSSFDataValidationHelper(mainsheet);
// 四个参数分别是：起始行、终止行、起始列、终止列
CellRangeAddressList regions = new CellRangeAddressList(1, 60, 4, 4);
DataValidationConstraint dvConstraint = dvHelper.createDateConstraint(DataValidationConstraint.OperatorType.BETWEEN, "1900-01-01", "5000-12-31", "yyyy-MM-dd");
HSSFDataValidation dataValidation = (HSSFDataValidation) dvHelper.createValidation(dvConstraint, regions);
// dataValidation.setSuppressDropDownArrow(false);
dataValidation.createPromptBox("输入提示", "请填写日期格式'yyyy-mm-dd'");
dataValidation.setShowPromptBox(true);
dataValidation.createErrorBox("日期格式错误提示", "你输入的日期格式不符合'yyyy-mm-dd'格式规范，请重新输入！");
dataValidation.setShowErrorBox(true);
mainsheet.addValidationData(dataValidation);
}
// 测试数字规则
private static void setNumberFormat(HSSFWorkbook HSSFWorkbook, HSSFSheet mainsheet, List<Integer> colNum) {
if (colNum.size() > 0) {
for (Integer index : colNum) {
HSSFDataValidationHelper dvHelper = new HSSFDataValidationHelper(mainsheet);
// 四个参数分别是：起始行、终止行、起始列、终止列
CellRangeAddressList regions = new CellRangeAddressList(1, 60, index, index);
DataValidationConstraint dvConstraint = dvHelper.createNumericConstraint(DataValidationConstraint.ValidationType.DECIMAL, DataValidationConstraint.OperatorType.BETWEEN, "0.0001", "100000");
HSSFDataValidation dataValidation = (HSSFDataValidation) dvHelper.createValidation(dvConstraint, regions);
// dataValidation.setSuppressDropDownArrow(false);
dataValidation.createPromptBox("", "请填写数字(至多4位小数)!");
dataValidation.setShowPromptBox(true);
dataValidation.createErrorBox("数字格式错误提示", "你输入的数字不正确,请重新输入!");
dataValidation.setShowErrorBox(true);
mainsheet.addValidationData(dataValidation);
}
}
}
/**
* 导入文件
*
* @param inputStream
* @return
* @throws Exception
*/
public static List importExcel(InputStream inputStream) throws Exception {
List returnList = new ArrayList();
//根据指定的文件输入流导入Excel从而产生Workbook对象
Workbook wb0 = new HSSFWorkbook(inputStream);
//获取Excel文档中的第一个表单
Sheet sht0 = wb0.getSheetAt(0);
//对Sheet中的每一行进行迭代
List rowList = null;
for (Row r : sht0) {
//如果当前行的行号（从0开始）未达到2（第三行）则从新循环
if (r.getRowNum() < 1) {
continue;
}
rowList = new ArrayList();
//非空判断
Cell c0 = r.getCell(0, Row.MissingCellPolicy.RETURN_BLANK_AS_NULL);
if (c0 != null) {
// 将区域编号的cell中的内容当做字符串处理
r.getCell(0).setCellType(HSSFCell.CELL_TYPE_STRING);
rowList.add(r.getCell(0).getStringCellValue());
}
Cell c1 = r.getCell(1, Row.MissingCellPolicy.RETURN_BLANK_AS_NULL);
if (c1 != null) {
r.getCell(1).setCellType(HSSFCell.CELL_TYPE_STRING);
rowList.add(r.getCell(1).getStringCellValue());
} else {
break;
}
Cell c2 = r.getCell(2, Row.MissingCellPolicy.RETURN_BLANK_AS_NULL);
if (c2 != null) {
r.getCell(2).setCellType(HSSFCell.CELL_TYPE_STRING);
rowList.add(r.getCell(2).getStringCellValue());
}
Cell c3 = r.getCell(3, Row.MissingCellPolicy.RETURN_BLANK_AS_NULL);
if (c3 != null) {
r.getCell(3).setCellType(HSSFCell.CELL_TYPE_STRING);
rowList.add(r.getCell(3).getStringCellValue());
}
Cell c4 = r.getCell(4, Row.MissingCellPolicy.RETURN_BLANK_AS_NULL);
if (c4 != null) {
r.getCell(4).setCellType(HSSFCell.CELL_TYPE_STRING);
rowList.add(r.getCell(4).getStringCellValue());
}
Cell c5 = r.getCell(5, Row.MissingCellPolicy.RETURN_BLANK_AS_NULL);
if (c5 != null) {
r.getCell(5).setCellType(HSSFCell.CELL_TYPE_STRING);
rowList.add(r.getCell(5).getStringCellValue());
}
returnList.add(rowList);
}
return returnList;
}
}
4147
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
