@GetMapping(value = "/excel_export")
@ApiOperation(value = "excel导出", notes = "excel导出", httpMethod = "GET")
public void excelExport(HttpServletRequest request, HttpServletResponse response,@RequestParam(value="projectId") Long projectId,@RequestParam(value="isMine",required=false) String isMine){
problemService.excelExport(request,response,projectId,isMine);
}
@PostMapping(value = "/excel_import")
@ApiOperation(value = "excel导入", notes = "excel导入")
public R<Boolean> excelImport(@RequestBody MultipartFile file,@RequestParam(value="projectId") Long projectId){
return problemService.excelImport(file,projectId);
}
//简单导出
@Override
public void excelExport(HttpServletRequest request, HttpServletResponse response, Long projectId,String isMine) {
QueryWrapper<AssessmentProjectProblem> qw = new QueryWrapper<>();
qw.lambda().eq(AssessmentProjectProblem::getProjectId, projectId);
if(StrUtil.equals("1", isMine)){
Integer userIdSession=Integer.valueOf(httpSessionService.getCurrentUserId());
qw.lambda().eq(AssessmentProjectProblem::getProblemCreator, userIdSession);
}
List<AssessmentProjectProblem> problems=this.list(qw);
if (CollUtil.isNotEmpty(problems)) {
List<List<String>> rows = new ArrayList<>();
Map<Integer, String> typeMap=typeService.getList(projectId).stream().collect(Collectors.toMap(AssessmentProjectProblemType::getId, AssessmentProjectProblemType::getTypeTitle));
Map<Integer, String> statusMap=new HashedMap<>(5);
statusMap.put(0, "未解决");
statusMap.put(1, "已解决");
statusMap.put(2, "已关闭");
List<String> header = new ArrayList<>();
header.add("问题标题");
header.add("问题状态");
header.add("问题类型");
header.add("责任部门");
header.add("问题描述");
header.add("问题溯源");
header.add("创建者");
header.add("创建时间");
rows.add(header);
Set<Integer> userIds=new HashSet<Integer>();
problems.forEach(p ->{
userIds.add(p.getProblemCreator());
});
Map<Integer, String> userMap=remoteUserService.getRealNames(userIds);
for (AssessmentProjectProblem p : problems) {
List<String> content = new ArrayList<>();
content.add(p.getProblemTitle());
content.add(statusMap.getOrDefault(p.getStatus(),"未解决"));
content.add(typeMap.getOrDefault(p.getProblemType(), "默认"));
content.add(p.getDutyDept());
content.add(p.getProblemDescribe());
content.add(p.getProblemSource());
content.add(userMap.getOrDefault(p.getProblemCreator(), ""));
content.add(DateUtil.format(p.getCreateTime(),"yyyy-MM-dd HH:mm:ss"));
rows.add(content);
}
// 通过工具类创建writer
ExcelWriter writer = ExcelUtil.getWriter();
// 一次性写出内容，强制输出标题
writer.write(rows, true);
try {
// response为HttpServletResponse对象
response.setContentType("application/vnd.ms-excel;charset=utf-8");
// test.xls是弹出下载对话框的文件名，不能为中文，中文请自行编码
response.setHeader("Content-Disposition", "attachment;filename=export.xls");
ServletOutputStream out = response.getOutputStream();
writer.flush(out, true);
// 关闭writer，释放内存
writer.close();
// 此处记得关闭输出Servlet流
IoUtil.close(out);
} catch (IOException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
@Override
public void excelAllExport(HttpServletRequest request, HttpServletResponse response, Integer resultId,Integer projectId) {
ResultMain bean = this.baseMapper.selectById(resultId);
if (bean != null) {
String assessBody = bean.getAssessBody();
List<List<String>> rows = new ArrayList<>();
List<List<String>> rowIds = new ArrayList<>();
List<String> header = new ArrayList<>();
//表头
Map<Integer, List<String>> headerNameMap = new HashMap<>();
Map<Integer, List<String>> headerIdMap = new HashMap<>();
JSONObject gradeQuotaObject = gradeQuotaService.scoringTreeList(resultId,projectId);
List<JSONObject> gradeQuotaList = JSONUtil.toList(gradeQuotaObject.getJSONArray("tableData"), JSONObject.class);
Integer leve = gradeQuotaObject.getInt("leve");
List<String> headerLevelOneId = new ArrayList<>();
List<String> headerLevelOne = new ArrayList<>();
headerLevelOne.add("一级指标");
headerLevelOneId.add("一级指标");
headerNameMap.put(1, headerLevelOne);
headerIdMap.put(1, headerLevelOneId);
//二级表头
List<String> headerLevelTwoId = new ArrayList<>();
List<String> headerLevelTwo = new ArrayList<>();
headerLevelTwo.add("二级指标");
headerLevelTwoId.add("二级指标");
headerNameMap.put(2, headerLevelTwo);
headerIdMap.put(2, headerLevelTwoId);
//三级表头
List<String> headerLevelThreeId = new ArrayList<>();
List<String> headerLevelThree = new ArrayList<>();
headerLevelThree.add("三级指标");
headerLevelThreeId.add("三级指标");
headerNameMap.put(3, headerLevelThree);
headerIdMap.put(3, headerLevelThreeId);
//部门集合
Map<Integer, List<String>> deptNumMap = new HashMap<>();
ResultContent resultContent = new ResultContent();
resultContent.setResultId(resultId);
List<ResultContentVO> pageListSort = contentService.getPageListSort(resultContent, null, assessBody);
// 通过工具类创建writer
ExcelWriter writer = ExcelUtil.getWriter();
if(CollUtil.isNotEmpty(gradeQuotaList)){
int deptNum = 1;
for(JSONObject quotaObject:gradeQuotaList){
if(quotaObject != null){
//一级指标id
for (int i = 1; i <= leve; i++) {
if(StrUtil.isNotBlank(quotaObject.getStr("id"+i))){
headerIdMap.get(i).add(quotaObject.getStr("id"+i));
headerNameMap.get(i).add(quotaObject.getStr("name"+i));
}
}
if(CollUtil.isNotEmpty(pageListSort)){
for(int j = 0;j < pageListSort.size();j++){
//第一次先增对象名称
if(deptNum == 1){
List<String> deptStringList = new ArrayList<>();
if(StrUtil.equals(assessBody, "1")){
deptStringList.add(pageListSort.get(j).getUserName());
}else{
deptStringList.add(pageListSort.get(j).getDeptName());
}
deptNumMap.put(j+1, deptStringList);
}
List<String> deptStringList = deptNumMap.get(j+1);
if(CollUtil.isEmpty(deptStringList)){
deptStringList = new ArrayList<>();
}
String content = pageListSort.get(j).getContent();
JSONObject jsonData = new JSONObject();
if(StrUtil.isNotBlank(content)){
jsonData = JSONUtil.parseObj(content);
}
String dataStr = jsonData.getStr(quotaObject.getStr("fieldUuid"));
//判断是否为数字类型
if(StrUtil.isNotBlank(dataStr) && dataStr.matches("^[-\\+]?([0-9]+\\.?)?[0-9]+$")){
BigDecimal bg = new BigDecimal(dataStr).setScale(2, BigDecimal.ROUND_HALF_UP);
deptStringList.add(String.valueOf(bg.doubleValue()));
}else{
deptStringList.add("");
}
}
}
}
deptNum++;
}
}
rows.add(headerLevelOne);
rowIds.add(headerLevelOneId);
rows.add(headerLevelTwo);
rowIds.add(headerLevelTwoId);
rows.add(headerLevelThree);
if(deptNumMap != null && !deptNumMap.isEmpty()){
for(Integer integer:deptNumMap.keySet()){
rows.add(deptNumMap.get(integer));
}
}
//合并单元格
if(CollUtil.isNotEmpty(rowIds)){
for (int i = 0; i < rowIds.size(); i++) {
List<String> stringList = rowIds.get(i);
//合并列的值
int firstColum = 0;
//上一列的值
int firstColumLast = 0;
String firstColumName = "";
String firstColumIdName = "";
for(int y = 0;y<stringList.size();y++){
//最后一列之前合并
if(StrUtil.isNotBlank(firstColumIdName)&&!StrUtil.equals(firstColumIdName,stringList.get(y))){
if(firstColumLast-firstColum>0){
writer.merge(i, i, firstColum, firstColumLast, firstColumName, false);
}
firstColum = y;
}else if(y == stringList.size()-1&&StrUtil.equals(firstColumIdName,stringList.get(y))){
//处理最后一列合并
if(firstColumLast-firstColum>0){
writer.merge(i, i, firstColum, y, firstColumName, false);
}
}
firstColumLast = y;
firstColumIdName = stringList.get(y);
firstColumName = rows.get(i).get(y);
}
}
}
// 一次性写出内容，强制输出标题
writer.write(rows, true);
//三级表头
List<String> styleList = new ArrayList<>();
if(CollUtil.isNotEmpty(headerLevelThree)&&headerLevelThree.size()>1){
styleList = headerLevelThree;
}else if(CollUtil.isNotEmpty(headerLevelTwo)&&headerLevelTwo.size()>1){
styleList = headerLevelTwo;
}else if(CollUtil.isNotEmpty(headerLevelOne)&&headerLevelOne.size()>1){
styleList = headerLevelOne;
}
for(int y = 0;y<3;y++){
////设置行高
writer.setRowHeight(y,30);
for(int x=0;x<styleList.size();x++){
CellStyle cellStyle = writer.createCellStyle();
Font font = writer.createFont();
font.setBold(Boolean.TRUE);
font.setFontName("宋体");
font.setFontHeightInPoints((short)11);
cellStyle.setFont(font);
HSSFWorkbook hssfworkbook = (HSSFWorkbook) writer.getWorkbook();
HSSFPalette palette = hssfworkbook.getCustomPalette();
//把预填充的HSSFColor.HSSFColorPredefined.LIME.getIndex()替换为期望RGB颜色
if(x == 0){
//预定义填充样式
cellStyle.setFillForegroundColor(HSSFColor.HSSFColorPredefined.GREY_25_PERCENT.getIndex());
//拿到palette颜色板
palette.setColorAtIndex(HSSFColor.HSSFColorPredefined.GREY_25_PERCENT.getIndex(), (byte) 231, (byte) 230, (byte) 230);
}else{
//预定义填充样式
cellStyle.setFillForegroundColor(HSSFColor.HSSFColorPredefined.LIME.getIndex());
//拿到palette颜色板
palette.setColorAtIndex(HSSFColor.HSSFColorPredefined.LIME.getIndex(), (byte) 217, (byte) 225, (byte) 242);
}
cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
// 设置底边框;
cellStyle.setBorderBottom(BorderStyle.THIN);
// 设置底边框颜色;
cellStyle.setBottomBorderColor(IndexedColors.BLACK.getIndex());
// 设置左边框;
cellStyle.setBorderLeft(BorderStyle.THIN);
// 设置左边框颜色;
cellStyle.setLeftBorderColor(IndexedColors.BLACK.getIndex());
// 设置右边框;
cellStyle.setBorderRight(BorderStyle.THIN);
// 设置右边框颜色;
cellStyle.setRightBorderColor(IndexedColors.BLACK.getIndex());
// 设置顶边框;
cellStyle.setBorderTop(BorderStyle.THIN);
// 设置顶边框颜色;
cellStyle.setTopBorderColor(IndexedColors.BLACK.getIndex());
// 设置垂直对齐的样式为居中对齐;
cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
if(y==2){
//设置列宽
if(StrUtil.isNotBlank(headerLevelOne.get(x))){
if(x == 0){
writer.setColumnWidth(x,headerLevelOne.get(x).length()+20);
}else{
writer.setColumnWidth(x,headerLevelOne.get(x).length()*2);
}
}
}
if(x == 0){
// 设置水平对齐的样式为左对齐;
cellStyle.setAlignment(HorizontalAlignment.LEFT);
}else{
// 设置水平对齐的样式为居中对齐;
cellStyle.setAlignment(HorizontalAlignment.CENTER);
}
writer.setStyle(cellStyle,x,y);
}
}
StyleSet style = writer.getStyleSet();
style.setBorder(BorderStyle.THIN, IndexedColors.BLACK);
try {
// response为HttpServletResponse对象
response.setContentType("application/vnd.ms-excel;charset=utf-8");
// test.xls是弹出下载对话框的文件名，不能为中文，中文请自行编码
response.setHeader("Content-Disposition", "attachment;filename="+bean.getResultName()+".xls");
ServletOutputStream out = response.getOutputStream();
writer.flush(out, true);
// 关闭writer，释放内存
writer.close();
// 此处记得关闭输出Servlet流
IoUtil.close(out);
} catch (IOException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
