package com.jdxx.gxqprj.gxqmgr.controller;
import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.metadata.CellData;
import com.alibaba.excel.metadata.Head;
import com.alibaba.excel.write.metadata.holder.WriteSheetHolder;
import com.alibaba.excel.write.metadata.style.WriteCellStyle;
import com.alibaba.excel.write.style.HorizontalCellStyleStrategy;
import com.alibaba.excel.write.style.column.AbstractColumnWidthStyleStrategy;
import com.alibaba.fastjson.JSON;
import com.jdxx.gxqprj.gxqmgr.activiti.service.IWorkflowService;
import com.jdxx.gxqprj.gxqmgr.model.BuApply;
import com.jdxx.gxqprj.gxqmgr.model.TaskInfo;
import com.jdxx.gxqprj.gxqmgr.util.StringUtil;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.RequestMapping;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.io.UnsupportedEncodingException;
import java.net.URLEncoder;
import java.text.SimpleDateFormat;
import java.util.*;
@Controller
@RequestMapping("/execl")
public class ExeclDemoController {
@Autowired
IWorkflowService workflowService;
/**
* 业务总览execl动态下载
* @param response
*/
@RequestMapping("/execl")
public void reportExcelOut(HttpServletResponse response) {
response.setContentType("application/vnd.ms-excel");
response.setCharacterEncoding("UTF-8");
String fileName = "业务全览.xls";
try {
response.setHeader("Content-Disposition", "attachment; filename=" + URLEncoder.encode(fileName, "UTF-8"));
} catch (UnsupportedEncodingException e) {
e.printStackTrace();
}
//response.setHeader("Content-disposition", "attachment;filename=业务全览.xls");
// 这里注意 有同学反应使用swagger 会导致各种问题，请直接用浏览器或者用postman
try {
// 这里需要设置不关闭流
WriteCellStyle headWriteCellStyle = new WriteCellStyle();
//设置背景颜色
headWriteCellStyle.setFillForegroundColor(IndexedColors.WHITE.getIndex());
//内容策略
WriteCellStyle contentWriteCellStyle = new WriteCellStyle();
//设置 水平居中
contentWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);
HorizontalCellStyleStrategy horizontalCellStyleStrategy =
new HorizontalCellStyleStrategy(headWriteCellStyle, contentWriteCellStyle);
//定义导出数据
Map<String, Object> result = workflowService.getHistoryByApplyId(6204L);
BuApply buApply = (BuApply) result.get("buApply");
List<TaskInfo> taskInfos = buApply.getTaskInfo();
/*List<Map<String,String>> mapList = new ArrayList<Map<String,String>>();
Map<String,String> stringMap = new HashMap<String,String>();
for (int i = 0; i < taskInfos.size(); i++) {
String comment = taskInfos.get(i).getComment();
String applyUserName = taskInfos.get(i).getApplyUserName();
String name = taskInfos.get(i).getName();
stringMap.put("comment",comment);
stringMap.put("applyUserName",applyUserName);
stringMap.put("name",name);
mapList.add(stringMap);
}*/
//导出数据list
List<List<String>> myriadPeopleData = new ArrayList<List<String>>();
List<String> data0 = new ArrayList<String>();
//申请名称
String applyName = buApply.getApplyName();
data0.add(applyName);
//单位名称
String enterName = buApply.getEnterName();
data0.add(enterName);
//联系人
String applyUser = buApply.getApplyUser();
data0.add(applyUser);
//联系电话
String applyPhone = buApply.getApplyPhone();
data0.add(applyPhone);
//业务状态
String dealState = buApply.getDealState();
if(!StringUtil.isEmpty(dealState)){
if("0".equals(dealState)){
data0.add("办理中");
}else if("1".equals(dealState)){
data0.add("已完结");
}
}
//申请时间
String subTime = dateToString(buApply.getSubTime());
data0.add(subTime);
//完成答批时间
String endTime = dateToString(buApply.getEndTime());
data0.add(endTime);
//自定义值
for (int i = 0; i < taskInfos.size(); i++) {
String comment = taskInfos.get(i).getComment();
data0.add(comment);
}
//办理进度
String auditState = buApply.getAuditState();
if(!StringUtil.isEmpty(auditState)){
if("0".equals(auditState)){
data0.add("未提交");
}else if("1".equals(auditState)){
data0.add("待审核");
}else if("2".equals(auditState)){
data0.add("审核通过");
}else if("3".equals(auditState)){
data0.add("审核不通过");
}else if("4".equals(auditState)){
data0.add("终止");
}
}
myriadPeopleData.add(data0);
EasyExcel.write(response.getOutputStream()).autoCloseStream(Boolean.FALSE)
.registerWriteHandler(new CustomizeColumnWidth()).registerWriteHandler(horizontalCellStyleStrategy)
.head(myriadPeopleHead(taskInfos)).sheet("业务全览")
//获取数据填充
.doWrite(myriadPeopleData);
} catch (Exception e) {
// 重置response
response.reset();
response.setContentType("application/json");
response.setCharacterEncoding("utf-8");
Map<String, String> map = new HashMap<String, String>();
map.put("status", "failure");
map.put("message", "下载文件失败" + e.getMessage());
try {
response.getWriter().println(JSON.toJSONString(map));
} catch (IOException e1) {
e1.printStackTrace();
}
}
}
public String dateToString(Date date) {
SimpleDateFormat sformat = new SimpleDateFormat("yyyy-MM-dd");//日期格式
String tiem = sformat.format(date);
return tiem;
}
/**
* 自定义表头
* @return
*/
private List<List<String>> myriadPeopleHead(List<TaskInfo> mapList) {
List<List<String>> list = new ArrayList<List<String>>();
List<String> head0 = new ArrayList<String>();
head0.add("企业请示报告落实进展情况表");
head0.add("申请名称");
/*List<String> head1 = new ArrayList<String>();
head1.add("企业请示报告落实进展情况表");
head1.add("申请单位");*/
//三级表头
List<String> head1_0 = new ArrayList<String>();
head1_0.add("企业请示报告落实进展情况表");
head1_0.add("申请单位");
head1_0.add("单位名称");
List<String> head1_1 = new ArrayList<String>();
head1_1.add("企业请示报告落实进展情况表");
head1_1.add("申请单位");
head1_1.add("联系人");
List<String> head1_2 = new ArrayList<String>();
head1_2.add("企业请示报告落实进展情况表");
head1_2.add("申请单位");
head1_2.add("联系电话");
List<String> head2 = new ArrayList<String>();
head2.add("企业请示报告落实进展情况表");
head2.add("业务状态");
List<String> head3 = new ArrayList<String>();
head3.add("企业请示报告落实进展情况表");
head3.add("申请时间");
List<String> head4 = new ArrayList<String>();
head4.add("企业请示报告落实进展情况表");
head4.add("完成答批时间");
List<List<String>> headList = new ArrayList<List<String>>();
//动态表头赋值
for (int i = 0; i < mapList.size(); i++) {
List<String> heading = new ArrayList<String>();
String applyUserName = mapList.get(i).getApplyUserName();
String name = mapList.get(i).getName();
heading.add("企业请示报告落实进展情况表");
heading.add(name+applyUserName+"签批意见");
headList.add(heading);
}
List<String> head5 = new ArrayList<String>();
head5.add("企业请示报告落实进展情况表");
head5.add("办理进度");
list.add(head0);
//list.add(head1);
list.add(head1_0);
list.add(head1_1);
list.add(head1_2);
list.add(head2);
list.add(head3);
list.add(head4);
for (int i = 0; i < headList.size(); i++) {
list.add(headList.get(i));
}
list.add(head5);
return list;
}
/**
* 自定义头部的 列的宽度设置 策略. .
*/
class CustomizeColumnWidth extends AbstractColumnWidthStyleStrategy {
@Override
protected void setColumnWidth(WriteSheetHolder writeSheetHolder, List<CellData> list, Cell cell, Head head, Integer integer, Boolean isHead) {
// 测试为 COLUMN 宽度定制.
if (isHead && cell.getRowIndex() == 1) {
int columnWidth = cell.getStringCellValue().getBytes().length;
int cellIndex = cell.getColumnIndex();
switch (cellIndex) {
case 0:
columnWidth = 80;
break;
case 1:
columnWidth = 20;
break;
case 2:
columnWidth = 20;
break;
case 3:
columnWidth = 20;
break;
case 4:
columnWidth = 20;
break;
case 5:
columnWidth = 20;
break;
default:
columnWidth = 20;
break;
}
if (columnWidth > 255) {
columnWidth = 255;
}
writeSheetHolder.getSheet().setColumnWidth(cellIndex, columnWidth * 256);
}
}
}
}