前言

hello,大家好,俗话说,上传下载是我们作为开发必不可少的业务,这里讲解比较常用的单纯上传下载功能,不涉及到很高级的比如幂等性、鉴权等业务

废话不多说,直接撸代码

一、上传

1. 接口

@RequestMapping(value = "/uploadFile.ajax", method = RequestMethod.POST)
    @ResponseBody
    public Result uploads(HttpServletRequest request, @RequestParam(required = true) MultipartFile file,
                          @ApiParam(value = "文件类型") @RequestParam(defaultValue = "1") Integer type) {
        try {
            if (file == null) {
                throw new ServiceException("上传文件不能为空");
            }
            // 文件名校验
            String fileName = file.getOriginalFilename();
            if (!fileName.matches(GenConstants.LOWEXCEL) && !fileName.matches(GenConstants.HIGHEXCEL)) {
                throw new ServiceException(ResultEnum.FILE_INCORRENT.getMessage());
            }
            // 文件大小校验
            if (!(file.getSize() / CommonConstant.FILE_FORMAT_SIZE / CommonConstant.FILE_FORMAT_SIZE < 10)) {
                throw new ServiceException("文件过大,不能超过10MB");
            }
            commonService.uploadFile(request, file, type);
        } catch (Exception e) {
            throw new ServiceException("上传失败,文件过大或格式有误");
        }
        return ResultGenerator.genSuccessResult("上传成功");
    }

多个文件的话直接数组接收参数再循环即可

2. 逻辑

public void uploadFile(HttpServletRequest request, MultipartFile file, Integer type) throws Exception {
        // 上传目录地址
        String uploadDir = request.getSession().getServletContext().getRealPath("/") + "upload/type" + type + "/";
        // 如果目录不存在,自动创建文件夹
        File dir = new File(uploadDir);
        if (!dir.exists()) {
            dir.mkdir();
        }
        // 调用上传方法
        HashMap<String, String> uploadInfo = executeUpload(uploadDir, file);
}

 public HashMap<String, String> executeUpload(String uploadDir, MultipartFile file) throws Exception {
        HashMap<String, String> upload = new HashMap<>();
        // 文件后缀名
        String suffix = file.getOriginalFilename().substring(file.getOriginalFilename().lastIndexOf("."));
        String name = file.getOriginalFilename().substring(0, file.getOriginalFilename().lastIndexOf("."));
        // 上传文件名
        String fileName = name + suffix;

        // 服务器端保存的文件对象
        File serverFile = new File(uploadDir + fileName);
        // 将上传的文件写入到服务器端文件内
        FileUtils.copyInputStreamToFile(file.getInputStream(), serverFile);
        long attachmentSize = file.getSize();

        FTPClientUtils ftp = FTPClientUtils.init();
        String contextPath = "qmp";
        String result = "上传至Ftp完成";
        boolean ret = ftp.put(contextPath + "/" + serverFile.getName(), serverFile.getAbsolutePath());

        if (ret) {
            String url = FTP_URL + "/" + contextPath + "/" + serverFile.getName();
            upload.put("fileName", fileName);
            upload.put("attachmentPath", url);
            upload.put("attachmentSize", String.valueOf(attachmentSize));
        } else {
            result = ", 上传至Ftp失败";
        }
        upload.put("result", result);
        return upload;
    }

FTPClientUtils 工具



import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.net.SocketException;

import org.apache.commons.net.ftp.FTPClient;
import org.apache.commons.net.ftp.FTPReply;
import org.apache.log4j.Logger;

/**
 * @author Terry FTP客户端
 * @creation 2017年8月18日-下午7:25:32
 */
public class FTPClientUtils {
	// ---------------------------------------------------------------------
	// Instance data
	// ---------------------------------------------------------------------
	/** logger */
	protected final Logger log = Logger.getLogger(getClass());
	private ThreadLocal<FTPClient> ftpClientThreadLocal = new ThreadLocal<FTPClient>();

	private String host;
	private int port;
	private String username;
	private String password;

	private boolean binaryTransfer = true;
	private boolean passiveMode = true;
	private String encoding = "UTF-8";
	private int clientTimeout = 1000 * 30;
	private int bufSize = 1024 * 1024 * 10;// 10M缓冲区10M

	public String getHost() {
		return host;
	}

	public void setHost(String host) {
		this.host = host;
	}

	public int getPort() {
		return port;
	}

	public void setPort(int port) {
		this.port = port;
	}

	public String getUsername() {
		return username;
	}

	public void setUsername(String username) {
		this.username = username;
	}

	public String getPassword() {
		return password;
	}

	public void setPassword(String password) {
		this.password = password;
	}

	public boolean isBinaryTransfer() {
		return binaryTransfer;
	}

	public void setBinaryTransfer(boolean binaryTransfer) {
		this.binaryTransfer = binaryTransfer;
	}

	public boolean isPassiveMode() {
		return passiveMode;
	}

	public void setPassiveMode(boolean passiveMode) {
		this.passiveMode = passiveMode;
	}

	public String getEncoding() {
		return encoding;
	}

	public void setEncoding(String encoding) {
		this.encoding = encoding;
	}

	public int getClientTimeout() {
		return clientTimeout;
	}

	public void setClientTimeout(int clientTimeout) {
		this.clientTimeout = clientTimeout;
	}

	// ---------------------------------------------------------------------
	// private method
	// ---------------------------------------------------------------------
	/**
	 * 返回一个FTPClient实例
	 * 
	 * @throws Exception
	 */
	private FTPClient getFTPClient() {
		if (ftpClientThreadLocal.get() != null && ftpClientThreadLocal.get().isConnected()) {
			return ftpClientThreadLocal.get();
		} else {
			FTPClient ftpClient = new FTPClient(); // 构造一个FtpClient实例
			ftpClient.setControlEncoding(encoding); // 设置字符集
			ftpClient.setBufferSize(bufSize);

			try {
				// 连接到ftp服务器
				connect(ftpClient);
			} catch (Exception e1) {
				e1.printStackTrace();
			}

			// 设置为passive模式
			if (passiveMode) {
				ftpClient.enterLocalPassiveMode();
			}
			try {
				// 设置文件传输类型
				setFileType(ftpClient);
			} catch (Exception e1) {
				e1.printStackTrace();
			}

			try {
				ftpClient.setSoTimeout(clientTimeout);
			} catch (SocketException e) {
				e.printStackTrace();
			}
			ftpClientThreadLocal.set(ftpClient);
			return ftpClient;
		}
	}

	/**
	 * 设置文件传输类型
	 * 
	 * @throws Exception
	 * @throws IOException
	 */
	private void setFileType(FTPClient ftpClient) throws Exception {
		try {
			if (binaryTransfer) {
				ftpClient.setFileType(FTPClient.BINARY_FILE_TYPE);
			} else {
				ftpClient.setFileType(FTPClient.ASCII_FILE_TYPE);
			}
		} catch (IOException e) {
			throw new Exception("Could not to set file type.", e);
		}
	}

	/**
	 * 连接到ftp服务器
	 * 
	 * @param ftpClient
	 * @return 连接成功返回true,否则返回false
	 * @throws Exception
	 */
	private boolean connect(FTPClient ftpClient) throws Exception {
		try {
			ftpClient.connect(host, port);

			// 连接后检测返回码来校验连接是否成功
			int reply = ftpClient.getReplyCode();

			if (FTPReply.isPositiveCompletion(reply)) {
				// 登陆到ftp服务器
				if (ftpClient.login(username, password)) {
					setFileType(ftpClient);
					return true;
				}
			} else {
				ftpClient.disconnect();
				throw new Exception("FTP server refused connection.");
			}
		} catch (IOException e) {
			if (ftpClient.isConnected()) {
				try {
					ftpClient.disconnect(); // 断开连接
				} catch (IOException e1) {
					throw new Exception("Could not disconnect from server.", e1);
				}

			}
			throw new Exception("Could not connect to server.", e);
		}
		return false;
	}

	// ---------------------------------------------------------------------
	// public method
	// ---------------------------------------------------------------------
	/**
	 * 断开ftp连接
	 * 
	 * @throws Exception
	 */
	public void disconnect() throws Exception {
		try {
			FTPClient ftpClient = getFTPClient();
			ftpClient.logout();
			if (ftpClient.isConnected()) {
				ftpClient.disconnect();
				ftpClient = null;
			}
			log.info("连接关闭");
		} catch (IOException e) {
			throw new Exception("Could not disconnect from server.", e);
		}
	}

	public boolean mkdir(String pathname) throws Exception {
		return mkdir(pathname, null);
	}

	/**
	 * 在ftp服务器端创建目录(不支持一次创建多级目录)
	 * 
	 * 该方法执行完后将自动关闭当前连接
	 * 
	 * @param pathname
	 * @return
	 * @throws Exception
	 */
	public boolean mkdir(String pathname, String workingDirectory) throws Exception {
		return mkdir(pathname, workingDirectory, true);
	}

	/**
	 * 在ftp服务器端创建目录(不支持一次创建多级目录)
	 * 
	 * @param pathname
	 * @param autoClose
	 *            是否自动关闭当前连接
	 * @return
	 * @throws Exception
	 */
	public boolean mkdir(String pathname, String workingDirectory, boolean autoClose) throws Exception {
		try {
			getFTPClient().changeWorkingDirectory(workingDirectory);
			return getFTPClient().makeDirectory(pathname);
		} catch (IOException e) {
			throw new Exception("Could not mkdir.", e);
		} finally {
			if (autoClose) {
				disconnect(); // 断开连接
			}
		}
	}

	/**
	 * 上传一个本地文件到远程指定文件
	 * 
	 * @param remoteAbsoluteFile
	 *            远程文件名(包括完整路径)
	 * @param localAbsoluteFile
	 *            本地文件名(包括完整路径)
	 * @return 成功时,返回true,失败返回false
	 * @throws Exception
	 */
	public boolean put(String remoteAbsoluteFile, String localAbsoluteFile) throws Exception {
		return put(remoteAbsoluteFile, localAbsoluteFile, true);
	}

	/**
	 * 上传一个本地文件到远程指定文件
	 * 
	 * @param remoteAbsoluteFile
	 *            远程文件名(包括完整路径)
	 * @param localAbsoluteFile
	 *            本地文件名(包括完整路径)
	 * @param autoClose
	 *            是否自动关闭当前连接
	 * @return 成功时,返回true,失败返回false
	 * @throws Exception
	 */
	public boolean put(String remoteAbsoluteFile, String localAbsoluteFile, boolean autoClose) throws Exception {
		InputStream input = null;
		try {
			// 处理传输
			input = new FileInputStream(localAbsoluteFile);
			System.out.println("上传的文件为:" + localAbsoluteFile);
			getFTPClient().storeFile(remoteAbsoluteFile, input);
			log.debug("put " + localAbsoluteFile);
			return true;
		} catch (FileNotFoundException e) {
			throw new Exception("local file not found.", e);
		} catch (IOException e) {
			throw new Exception("Could not put file to server.", e);
		} finally {
			try {
				if (input != null) {
					input.close();
				}
			} catch (Exception e) {
				throw new Exception("Couldn't close FileInputStream.", e);
			}
			if (autoClose) {
				disconnect(); // 断开连接
			}
		}
	}
	/**
	 * 下载一个远程文件到本地的指定文件
	 * 
	 * @param remoteAbsoluteFile
	 *            远程文件名(包括完整路径)
	 * @param localAbsoluteFile
	 *            本地文件名(包括完整路径)
	 * @return 成功时,返回true,失败返回false
	 * @throws Exception
	 */
	public boolean get(String remoteAbsoluteFile, String localAbsoluteFile) throws Exception {
		return get(remoteAbsoluteFile, localAbsoluteFile, true);
	}

	/**
	 * 下载一个远程文件到本地的指定文件
	 * 
	 * @param remoteAbsoluteFile
	 *            远程文件名(包括完整路径)
	 * @param localAbsoluteFile
	 *            本地文件名(包括完整路径)
	 * @param autoClose
	 *            是否自动关闭当前连接
	 * 
	 * @return 成功时,返回true,失败返回false
	 * @throws Exception
	 */
	public boolean get(String remoteAbsoluteFile, String localAbsoluteFile, boolean autoClose) throws Exception {
		OutputStream output = null;
		try {
			output = new FileOutputStream(localAbsoluteFile);
			return get(remoteAbsoluteFile, output, autoClose);
		} catch (FileNotFoundException e) {
			throw new Exception("local file not found.", e);
		} finally {
			try {
				if (output != null) {
					output.close();
				}
			} catch (IOException e) {
				throw new Exception("Couldn't close FileOutputStream.", e);
			}
		}
	}

	/**
	 * 下载一个远程文件到指定的流 处理完后记得关闭流
	 * 
	 * @param remoteAbsoluteFile
	 * @param output
	 * @return
	 * @throws Exception
	 */
	public boolean get(String remoteAbsoluteFile, OutputStream output) throws Exception {
		return get(remoteAbsoluteFile, output, true);
	}

	/**
	 * 下载一个远程文件到指定的流 处理完后记得关闭流
	 * 
	 * @param remoteAbsoluteFile
	 * @param output
	 * @param delFile
	 * @return
	 * @throws Exception
	 */
	public boolean get(String remoteAbsoluteFile, OutputStream output, boolean autoClose) throws Exception {
		try {
			FTPClient ftpClient = getFTPClient();
			// 处理传输
			return ftpClient.retrieveFile(remoteAbsoluteFile, output);
		} catch (IOException e) {
			throw new Exception("Couldn't get file from server.", e);
		} finally {
			if (autoClose) {
				disconnect(); // 关闭链接
			}
		}
	}

	/**
	 * 从ftp服务器上删除一个文件 该方法将自动关闭当前连接
	 * 
	 * @param delFile
	 * @return
	 * @throws Exception
	 */
	public boolean delete(String delFile) throws Exception {
		return delete(delFile, true);
	}

	/**
	 * 从ftp服务器上删除一个文件
	 * 
	 * @param delFile
	 * @param autoClose
	 *            是否自动关闭当前连接
	 * 
	 * @return
	 * @throws Exception
	 */
	public boolean delete(String delFile, boolean autoClose) throws Exception {
		try {
			getFTPClient().deleteFile(delFile);
			return true;
		} catch (IOException e) {
			throw new Exception("Couldn't delete file from server.", e);
		} finally {
			if (autoClose) {
				disconnect(); // 关闭链接
			}
		}
	}

	/**
	 * 批量删除 该方法将自动关闭当前连接
	 * 
	 * @param delFiles
	 * @return
	 * @throws Exception
	 */
	public boolean delete(String[] delFiles) throws Exception {
		return delete(delFiles, true);
	}

	/**
	 * 批量删除
	 * 
	 * @param delFiles
	 * @param autoClose
	 *            是否自动关闭当前连接
	 * 
	 * @return
	 * @throws Exception
	 */
	public boolean delete(String[] delFiles, boolean autoClose) throws Exception {
		try {
			FTPClient ftpClient = getFTPClient();
			for (String s : delFiles) {
				ftpClient.deleteFile(s);
			}
			return true;
		} catch (IOException e) {
			throw new Exception("Couldn't delete file from server.", e);
		} finally {
			if (autoClose) {
				disconnect(); // 关闭链接
			}
		}
	}

	/**
	 * 列出远程默认目录下所有的文件
	 * 
	 * @return 远程默认目录下所有文件名的列表,目录不存在或者目录下没有文件时返回0长度的数组
	 * @throws Exception
	 */
	public String[] listNames() throws Exception {
		return listNames(null, true);
	}

	public String[] listNames(boolean autoClose) throws Exception {
		return listNames(null, autoClose);
	}

	/**
	 * 列出远程目录下所有的文件
	 * 
	 * @param remotePath
	 *            远程目录名
	 * @param autoClose
	 *            是否自动关闭当前连接
	 * 
	 * @return 远程目录下所有文件名的列表,目录不存在或者目录下没有文件时返回0长度的数组
	 * @throws Exception
	 */
	public String[] listNames(String remotePath, boolean autoClose) throws Exception {
		try {
			String[] listNames = getFTPClient().listNames(remotePath);
			return listNames;
		} catch (IOException e) {
			throw new Exception("列出远程目录下所有的文件时出现异常", e);
		} finally {
			if (autoClose) {
				disconnect(); // 关闭链接
			}
		}
	}

	public static FTPClientUtils init() {
		FTPClientUtils ftp = new FTPClientUtils();
		
		ftp.setHost("XXX");
		ftp.setPort(2121);
		ftp.setUsername("user");
		ftp.setPassword("pass");
		ftp.setBinaryTransfer(true);
		ftp.setPassiveMode(false);
		ftp.setEncoding("utf-8");
		return ftp;
	}

	public static void main(String[] args) throws Exception, InterruptedException {
		FTPClientUtils ftp = FTPClientUtils.init();
//		ftp.mkdir("TBD");
		System.out.print("开始上传...");
		File file = new File("F:/test/");
		for (File file2 : file.listFiles()) {
			boolean ret = ftp.put(file2.getName(), file2.getAbsolutePath());
			if (ret) {
				String result = "上传完成";
				System.out.println(file2.getName() + result);
			} else {
				String result = "上传失败";
				System.out.println(file2.getName() + result);
			}
		}
		System.out.println("上传全部完成");
	}
}

二、下载

下载一般用在下载模板、下载固定文件,其实有好几种方案,这里一个个说

  • 前端使用url下载
    使用windows下载

  • 前端js下载
    这里就不讲这一个了,随便百度找下即可

– 下载静态文件

<el-link type="primary" target="_blank" :href="`${path}static/template/${tempName}`">
   点击下载
</el-link>
data() {
    return {
      path: process.env.BASE_URL
    }
},
  • 后端下载
 @PostMapping("/downTemplate.ajax")
    @ApiOperation("下载模板")
    public void exportItemCheck(HttpServletResponse response) {
        response.setContentType("application/vnd.ms-excel;charset=utf-8");
        response.setCharacterEncoding("utf-8");
       try {
            String filename = CustomFieldEnum.getByType(type).getName() + "模板.xls";
            filename = new String(filename.getBytes("gb2312"), "ISO8859-1");
            response.setHeader("Content-disposition", "attachment;filename=" + filename);
            // 表头
            HashMap<String, String> headers = new LinkedHashMap<>();
            getExpostHeader(headers, type, true);
            ExcelUtil.exportExcel(headers, new ArrayList<>(), response.getOutputStream());
        } catch (Exception e) {
            e.printStackTrace();
        }
 }
    
 protected void getExpostHeader(Map<String, String> headers, Integer type,Boolean flagCreate){
        CustomFieldReq customFieldReq = new CustomFieldReq ();
        customFieldReq.setUserName(CookieUtils.getCurrentUser());
        customFieldReq.setType(type);
        List<DTO> dtos = 查询
        for (DTO dto:dtos){
        	headers.put(req.getFieldKey(), fieldName);
        }
    }   

ExcelUtil 工具

package com.qa.test.system.util.excelutil;

import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import org.apache.commons.beanutils.BeanComparator;
import org.apache.commons.collections.CollectionUtils;
import org.apache.commons.collections.ComparatorUtils;
import org.apache.commons.collections.comparators.ComparableComparator;
import org.apache.commons.collections.comparators.ComparatorChain;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.common.usermodel.HyperlinkType;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.ss.util.CellReference;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import java.io.*;
import java.lang.reflect.Field;
import java.text.MessageFormat;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.*;

/**
 * The <code>ExcelUtil</code> 与 {@link ExcelCell}搭配使用
 *
 * @author sargeras.wang
 * @version 1.0, Created at 2013年9月14日
 */
public class ExcelUtil {

    private static Logger LG = LoggerFactory.getLogger(ExcelUtil.class);

    /**
     * 用来验证excel与Vo中的类型是否一致 <br>
     * Map<栏位类型,只能是哪些Cell类型>
     */
    private static Map<Class<?>, CellType[]> validateMap = new HashMap<>();

    static {
        validateMap.put(String[].class, new CellType[]{CellType.STRING});
        validateMap.put(Double[].class, new CellType[]{CellType.NUMERIC});
        validateMap.put(String.class, new CellType[]{CellType.STRING});
        validateMap.put(Double.class, new CellType[]{CellType.NUMERIC});
        validateMap.put(Date.class, new CellType[]{CellType.NUMERIC, CellType.STRING});
        validateMap.put(Integer.class, new CellType[]{CellType.NUMERIC});
        validateMap.put(Float.class, new CellType[]{CellType.NUMERIC});
        validateMap.put(Long.class, new CellType[]{CellType.NUMERIC});
        validateMap.put(Boolean.class, new CellType[]{CellType.BOOLEAN});
    }

    /**
     * 获取cell类型的文字描述
     *
     * @param cellType <pre>
     *                 CellType.BLANK
     *                 CellType.BOOLEAN
     *                 CellType.ERROR
     *                 CellType.FORMULA
     *                 CellType.NUMERIC
     *                 CellType.STRING
     *                 </pre>
     * @return
     */
    private static String getCellTypeByInt(CellType cellType) {
        if(cellType == CellType.BLANK)
            return "Null type";
        else if(cellType == CellType.BOOLEAN)
            return "Boolean type";
        else if(cellType == CellType.ERROR)
            return "Error type";
        else if(cellType == CellType.FORMULA)
            return "Formula type";
        else if(cellType == CellType.NUMERIC)
            return "Numeric type";
        else if(cellType == CellType.STRING)
            return "String type";
        else
            return "Unknown type";
    }

    /**
     * 获取单元格值
     *
     * @param cell
     * @return
     */
    public static String getCellValue(Cell cell) {

        if (cell == null
                || (cell.getCellTypeEnum() == CellType.STRING && StringUtils.isBlank(cell
                .getStringCellValue()))) {
            return null;
        }
        CellType cellType = cell.getCellTypeEnum();
        if(cellType == CellType.BLANK)
            return null;
        else if(cellType == CellType.BOOLEAN)
            return String.valueOf(cell.getBooleanCellValue());
        else if(cellType == CellType.ERROR)
            return String.valueOf(cell.getErrorCellValue());
        else if(cellType == CellType.FORMULA) {
            try {
                if (HSSFDateUtil.isCellDateFormatted(cell)) {
                    return String.valueOf(cell.getDateCellValue());
                } else {
                    return String.valueOf(cell.getNumericCellValue());
                }
            } catch (IllegalStateException e) {
                return String.valueOf(cell.getRichStringCellValue());
            }
        }
        else if(cellType == CellType.NUMERIC){
            if (DateUtil.isCellDateFormatted(cell)) {
                return String.valueOf(cell.getDateCellValue());
            } else {
                return String.valueOf(cell.getNumericCellValue());
            }
        }
        else if(cellType == CellType.STRING)
            return cell.getStringCellValue();
        else
            return null;
    }

    /**
     * 利用JAVA的反射机制,将放置在JAVA集合中并且符号一定条件的数据以EXCEL 的形式输出到指定IO设备上<br>
     * 用于单个sheet
     *
     * @param <T>
     * @param headers 表格属性列名数组
     * @param dataset 需要显示的数据集合,集合中一定要放置符合javabean风格的类的对象。此方法支持的
     *                javabean属性的数据类型有基本数据类型及String,Date,String[],Double[]
     * @param out     与输出设备关联的流对象,可以将EXCEL文档导出到本地文件或者网络中
     */
    public static <T> void exportExcel(Map<String,String> headers, Collection<T> dataset, OutputStream out) {
        exportExcel(headers, dataset, out, null);
    }

    /**
     * 利用JAVA的反射机制,将放置在JAVA集合中并且符号一定条件的数据以EXCEL 的形式输出到指定IO设备上<br>
     * 用于单个sheet
     *
     * @param <T>
     * @param headers 表格属性列名数组
     * @param dataset 需要显示的数据集合,集合中一定要放置符合javabean风格的类的对象。此方法支持的
     *                javabean属性的数据类型有基本数据类型及String,Date,String[],Double[]
     * @param out     与输出设备关联的流对象,可以将EXCEL文档导出到本地文件或者网络中
     * @param pattern 如果有时间数据,设定输出格式。默认为"yyy-MM-dd"
     */
    @SuppressWarnings("resource")
    public static <T> void exportExcel(Map<String,String> headers, Collection<T> dataset, OutputStream out,
                                       String pattern) {
        // 声明一个工作薄
        HSSFWorkbook workbook = new HSSFWorkbook();
        // 生成一个表格
        HSSFSheet sheet = workbook.createSheet();

        write2Sheet(sheet, headers, dataset, pattern,workbook);
        try {
            workbook.write(out);
        } catch (IOException e) {
            LG.error(e.toString(), e);
        }
    }

    public static List<Map<String,String>> getProjectKeyParamList(Workbook wb ,int colNum) {

        Sheet sheet = null;
        Row row = null;

        String cellData = null;

        //用来存放表中数据
        List <Map<String,String>> list = new ArrayList<Map<String,String>>();

        if(wb != null){

            //获取第一个sheet
            sheet = wb.getSheetAt(0);

            int startRow =0;
            if(sheet.getMergedRegion(0) != null){
                //表头占得行数
                startRow =sheet.getMergedRegion(0).getLastRow();
            }

            //效验表头模板
            CellRangeAddress ca = sheet.getMergedRegion(0);
            int heardMaxRow = 0;
            if(ca != null){
                heardMaxRow = ca.getLastRow();
            }

            //获取最大行数
            int rowNum = sheet.getPhysicalNumberOfRows();
            //获取第一行
            row = sheet.getRow(0);
            //获取最大列数
           // int colNum = row.getLastCellNum();

            //key是所在的行列,value是单元格的值
            Map<String,String> map = null;
            for (int i = 0; i < rowNum; i++) {

                map = new LinkedHashMap<String,String>();
                row = sheet.getRow(i);
                if(row != null ){

                    //从第0列开始
                    for(int j = 0; j < (colNum+1); j++){
                        cellData = (String) getCellValue(row.getCell(j));

                        if(cellData==null || "".equals(cellData)){
                            continue;
                        }
                        map.put(i+"-"+j,cellData);
                    }

                }
                list.add(map);
            }
        }

        return  list;
    }

    @SuppressWarnings("resource")
    public static void exportExcel(String[][] datalist, OutputStream out) {
        try {
            // 声明一个工作薄
            HSSFWorkbook workbook = new HSSFWorkbook();
            // 生成一个表格
            HSSFSheet sheet = workbook.createSheet();

            for (int i = 0; i < datalist.length; i++) {
                String[] r = datalist[i];
                HSSFRow row = sheet.createRow(i);
                for (int j = 0; j < r.length; j++) {
                    HSSFCell cell = row.createCell(j);
                    //cell max length 32767
                    if (r[j].length() > 32767) {
                        r[j] = "--此字段过长(超过32767),已被截断--" + r[j];
                        r[j] = r[j].substring(0, 32766);
                    }
                    cell.setCellValue(r[j]);
                }
            }
            //自动列宽
            if (datalist.length > 0) {
                int colcount = datalist[0].length;
                for (int i = 0; i < colcount; i++) {
                    sheet.autoSizeColumn(i);
                }
            }
            workbook.write(out);
        } catch (IOException e) {
            LG.error(e.toString(), e);
        }
    }

    /**
     * 利用JAVA的反射机制,将放置在JAVA集合中并且符号一定条件的数据以EXCEL 的形式输出到指定IO设备上<br>
     * 用于多个sheet
     *
     * @param <T>
     * @param sheets {@link ExcelSheet}的集合
     * @param out    与输出设备关联的流对象,可以将EXCEL文档导出到本地文件或者网络中
     */
    public static <T> void exportExcel(List<ExcelSheet<T>> sheets, OutputStream out) {
        exportExcel(sheets, out, null);
    }

    /**
     * 利用JAVA的反射机制,将放置在JAVA集合中并且符号一定条件的数据以EXCEL 的形式输出到指定IO设备上<br>
     * 用于多个sheet
     *
     * @param <T>
     * @param sheets  {@link ExcelSheet}的集合
     * @param out     与输出设备关联的流对象,可以将EXCEL文档导出到本地文件或者网络中
     * @param pattern 如果有时间数据,设定输出格式。默认为"yyy-MM-dd"
     */
    @SuppressWarnings("resource")
    public static <T> void exportExcel(List<ExcelSheet<T>> sheets, OutputStream out, String pattern) {
        if (CollectionUtils.isEmpty(sheets)) {
            return;
        }
        // 声明一个工作薄
        HSSFWorkbook workbook = new HSSFWorkbook();
        for (ExcelSheet<T> sheet : sheets) {
            // 生成一个表格
            HSSFSheet hssfSheet = workbook.createSheet(sheet.getSheetName());
            write2Sheet(hssfSheet, sheet.getHeaders(), sheet.getDataset(), pattern,workbook);
        }
        try {
            workbook.write(out);
        } catch (IOException e) {
            LG.error(e.toString(), e);
        }
    }

    public static <T> void writeKeyParamTemplate(Map<String,Object> headers,  List<HashMap<String,String>> data, OutputStream out) {

        // 声明一个工作薄
        HSSFWorkbook workbook = new HSSFWorkbook();
        // 生成一个表格
        HSSFSheet sheet = workbook.createSheet();

        // 产生表格标题行
        HSSFRow row = sheet.createRow(0);


        LinkedHashSet<String> headerIDList = getHeadData(headers, workbook, sheet, row);

        sheet.setDefaultRowHeight((short)300.0);
        sheet.setDefaultColumnWidth(20);
        try {
            workbook.write(out);
            out.flush();
            out.close();
        } catch (IOException e) {
            LG.error(e.toString(), e);
        }

    }
    public static <T> void writeKeyParamExcel(Map<String,Object> headers,  List<HashMap<String,String>> data, OutputStream out) {

        // 声明一个工作薄
        HSSFWorkbook workbook = new HSSFWorkbook();
        // 生成一个表格
        HSSFSheet sheet = workbook.createSheet();

        // 产生表格标题行
        HSSFRow row = sheet.createRow(0);


        LinkedHashSet<String> headerIDList = getHeadData(headers, workbook, sheet, row);
        int c;
        HSSFRichTextString text;
        HSSFCell cell;

        c = 1;
        //打印表格中的数据
        if(headerIDList != null && !headerIDList.isEmpty()){

            int rowNum  = sheet.getLastRowNum();

            for(HashMap<String,String> keyValue : data){
                rowNum++;
                row = sheet.createRow(rowNum);
                c = 0;

                //打印机型
                if(keyValue.get("projectModel")!=null){
                    text = new HSSFRichTextString(keyValue.get("projectModel"));
                    cell = row.createCell(c);
                    cell.setCellValue(text);
                    c++;
                }

                if(keyValue.get("keyParam")==null){
                    continue;
                }
                //打印中间动态的数据
                String[] rowObject = keyValue.get("keyParam").split(",");

                for (String id :headerIDList) {
                    int flag =0 ;
                   for(String cellObject : rowObject){

                       String[] cellValue = cellObject.split(";");
                       if(cellValue[0].equals(id)){
                           flag =1;
                           text = new HSSFRichTextString(cellValue[1]);
                           cell = row.createCell(c);
                           cell.setCellValue(text);
                           c++;
                           break;
                       }
                   }
                   if(flag ==0){
                       cell = row.createCell(c);
                       cell.setCellValue("");
                       c++;
                   }
                }
                //打印后两列
                if(keyValue.get("creator")!=null){
                    text = new HSSFRichTextString(keyValue.get("creator"));
                    cell = row.createCell(c);
                    cell.setCellValue(text);
                    c++;
                }
                if(keyValue.get("gmtCreate")!=null){
                    text = new HSSFRichTextString(keyValue.get("gmtCreate"));
                    cell = row.createCell(c);
                    cell.setCellValue(text);
                    c++;
                }

            }

        }

        sheet.setDefaultRowHeight((short)300.0);
        sheet.setDefaultColumnWidth(20);
        try {
            workbook.write(out);
            out.flush();
            out.close();
        } catch (IOException e) {
            LG.error(e.toString(), e);
        }
    }

    private static LinkedHashSet<String> getHeadData(Map<String, Object> headers, HSSFWorkbook workbook, HSSFSheet sheet, HSSFRow row) {
        HSSFFont titleFont = workbook.createFont();

        titleFont.setFontHeightInPoints((short) 14);
        CellStyle titleStyle = workbook.createCellStyle();
        titleStyle.setFont(titleFont);

        titleStyle.setAlignment(HorizontalAlignment.CENTER);

        // 标题行转中文
        Set<String> keys = headers.keySet();

        Iterator<String> it1 = keys.iterator();
        String key = "";    //存放临时键变量
        int c = 0;   //标题列数

        LinkedHashSet<String> headerIDList =  new LinkedHashSet<>();

        HSSFCell cell = null;
        HSSFRichTextString text = null;

        while (it1.hasNext()) {
            key = it1.next();

            //打印表头
            if("array".equals(key)){
                JSONArray array = (JSONArray) headers.get(key);
                c = setKeyParamCellValue(0,c,row, cell, array,sheet,titleStyle,headerIDList);

            }else {
                text = new HSSFRichTextString(headers.get(key).toString());
                cell = row.createCell(c);
                cell.setCellValue(text);
                cell.setCellStyle(titleStyle);
                c++;
            }
        }

        int startCol = 1;
        int overCol =  1;

        //合并表头单元格
        JSONArray array = (JSONArray) headers.get("array");
        mergedKeyParamCellValue(startCol,overCol,array,sheet);
        return headerIDList;
    }

    private static void mergedKeyParamCellValue( int startCol,int overCol, JSONArray array,HSSFSheet sheet ) {

        int maxColumn = 0;
        for(int i=0;i<=sheet.getLastRowNum();i++){
            int size = sheet.getRow(i).getLastCellNum();
            if(size>maxColumn){
                maxColumn=size;
            }
        }
        //合并列
        HashMap<Integer,Integer> map = new HashMap<>();
        //合并行
        for(int i = 0; i< maxColumn; i++) {
            int startRow=0;
            for(int j = 1; j<  sheet.getLastRowNum()+1; j++){
                Object cellValue = sheet.getRow(j).getCell(i);
                if(cellValue != null && !"".equals(cellValue) || j==(sheet.getLastRowNum())){
                    if(cellValue == null){
                        if((j) > startRow) {
                            sheet.addMergedRegion(new CellRangeAddress(startRow, j, i,i));
                            map.put(i,j);
                        }
                    }
                    startRow = j;
                }
            }
        }
        for(int i = 0; i<= sheet.getLastRowNum(); i++) {
            for(int j = 1; j<  maxColumn-1; j++){
                Object cellValue = sheet.getRow(i).getCell(j);
                if(cellValue != null && !"".equals(cellValue) || j==(maxColumn)){
                    if((j-1) > startCol) {
                        if(map.containsKey(j-1)){
                            int val = map.get(j-1);
                            if(i==val){
                                startCol = j;
                            }else{
                                if((j-1) > startCol  && cellValue == null) {
                                    sheet.addMergedRegion(new CellRangeAddress(i, i, startCol, j -1));
                                }
                                else{
                                    startCol = j;
                                }
                            }
                        }else{
                            if((j-1) > startCol && cellValue == null) {
                                sheet.addMergedRegion(new CellRangeAddress(i, i, startCol, j -1));
                            } else{
                                startCol = j;
                            }
                        }
                    }
                    startCol = j;
                }
            }
        }
    }


    private static int setKeyParamCellValue(int rowNum,int c,HSSFRow row, HSSFCell cell, JSONArray array,HSSFSheet sheet,CellStyle titleStyle,LinkedHashSet<String> headerIDList ) {

        HSSFRow rowTmp = sheet.getRow(rowNum);
        if(rowTmp != null){
            row = rowTmp;
        }
        else{
            row = sheet.createRow(rowNum);
        }
        rowNum  ++ ;

        HSSFRichTextString text;

        for(int i = 0; i< array.size(); i++){

            JSONObject json = (JSONObject)array.get(i);

            String fieldName = json.get("fieldName").toString();
            text = new HSSFRichTextString(fieldName);
            cell = row.createCell(c);
            cell.setCellValue(text);
            cell.setCellStyle(titleStyle);

            JSONArray sendArray = (JSONArray)json.get("children");
            if(sendArray == null){
                headerIDList.add(json.get("id").toString());
                c ++;
                continue;
            }
            c = setKeyParamCellValue( rowNum,c,row,cell,sendArray,sheet,titleStyle,headerIDList);
        }

        return c;
    }

    public static <T> void writeProjectItemExcel(String projectModel,Map<String,String> headers, List<LinkedHashSet<JSONObject>> itemTotalList,
                                                 OutputStream out,Map<String,String> allResultHeaders,JSONArray allResultArray,String categoryID) throws FileNotFoundException {

        // 声明一个工作薄
        HSSFWorkbook workbook = new HSSFWorkbook();
        // 生成一个表格
        HSSFSheet sheet = workbook.createSheet(projectModel+"总检点结果");
        write2Sheet(sheet, allResultHeaders, allResultArray, null,workbook);
        String label = "";
        for (LinkedHashSet<JSONObject> results : itemTotalList) {
            for(JSONObject result : results){
                label = String.valueOf(result.get("label"));
            }
            sheet = workbook.createSheet(label +"阶段检点结果");
            //生成sheet内容
            if(Objects.equals(CategoryEnum.Europe.getType(),Integer.valueOf(categoryID))){
                getSheet(headers, results, 1, workbook, sheet);
            }else{
                getSheetCheckResult(headers, results, 1, workbook, sheet);
            }
        }

        sheet.setDefaultRowHeight((short)300.0);
        sheet.setDefaultColumnWidth(30);

        try {
            workbook.write(out);
            out.flush();
            out.close();
        } catch (IOException e) {
            LG.error(e.toString(), e);
        }

    }

    public static <T> void writeProjectItemCheckResultExcel(Map<String,String> headers, LinkedHashSet<JSONObject> results, OutputStream out) throws Exception {
        // 声明一个工作薄
        HSSFWorkbook workbook = new HSSFWorkbook();
        HSSFSheet sheet = workbook.createSheet("检点结果");
        getSheetCheckResult(headers, results, 1, workbook, sheet);
        sheet.setDefaultRowHeight((short)300.0);
        sheet.setDefaultColumnWidth(30);

        try {
            workbook.write(out);
            out.flush();
            out.close();
        } catch (IOException e) {
            LG.error(e.toString(), e);
        }
    }


    public static <T> void getExportNetQualityTargetDetail(Map<String, String> headers,
        List<NetQualityTargetDetailReq> templateReqList, OutputStream out) {

        try {
            // 声明一个工作薄
            HSSFWorkbook workbook = new HSSFWorkbook();
            // 生成一个表格
            HSSFSheet sheet = workbook.createSheet("质量目标");
            HSSFFont titleFont = workbook.createFont();

            titleFont.setFontHeightInPoints((short)14);
            CellStyle titleStyle = workbook.createCellStyle();
            titleStyle.setFont(titleFont);

            titleStyle.setAlignment(HorizontalAlignment.CENTER);

            // 产生表格标题行
            HSSFRow row = sheet.createRow(0);
            // 标题行转中文
            Set<String> keys = headers.keySet();
            Iterator<String> it1 = keys.iterator();
            String key = ""; // 存放临时键变量
            int c = 0; // 标题列数
            while (it1.hasNext()) {
                key = it1.next();
                if (headers.containsKey(key)) {
                    HSSFCell cell = row.createCell(c);
                    cell.setCellStyle(titleStyle);
                    HSSFRichTextString text = new HSSFRichTextString(headers.get(key));
                    cell.setCellValue(text);
                    c++;
                }
            }

            HSSFCellStyle linkStyle = workbook.createCellStyle();
            HSSFFont linkFont = workbook.createFont();
            linkFont.setUnderline(HSSFFont.U_SINGLE);
            linkFont.setColor(HSSFColor.HSSFColorPredefined.BLUE.getIndex());
            linkStyle.setFont(linkFont);

            titleFont.setFontHeightInPoints((short)10);
            titleStyle = workbook.createCellStyle();
            titleStyle.setFont(titleFont);
            titleStyle.setAlignment(HorizontalAlignment.CENTER);

            HSSFCellStyle rowStyle = workbook.createCellStyle();
            // 遍历集合数据,产生数据行
            Iterator<NetQualityTargetDetailReq> it = templateReqList.iterator();
            HSSFCell cell = null;

            int index = 0;
            while (it.hasNext()) {
                index++;
                row = sheet.createRow(index);
                NetQualityTargetDetailReq t = it.next();
                try {

                    int cellNum = 0;
                    // 遍历列名
                    Iterator<String> it2 = keys.iterator();
                    while (it2.hasNext()) {
                        key = it2.next();
                        if (!headers.containsKey(key)) {
                            continue;
                        }
                        Object value = null;
                        if (QualityTemplateEnum.YEAR.getName().equals(key)) {
                            value = t.getYear().getLevelValue();
                        } else if (QualityTemplateEnum.FIRST.getName().equals(key)) {
                            value = t.getFirstLevel().getLevelValue();
                            if(StringUtils.isNotEmpty(String.valueOf(value))){
                                value = String.valueOf(value).split("_")[0];
                            }
                        } else if (QualityTemplateEnum.SECOND.getName().equals(key)) {
                            value = t.getSecondLevel().getLevelValue();
                            if(StringUtils.isNotEmpty(String.valueOf(value))){
                                value = String.valueOf(value).split("_")[0];
                            }
                        } else if (QualityTemplateEnum.THIRD.getName().equals(key)) {
                            value = t.getThirdLevel().getLevelValue();
                            if(StringUtils.isNotEmpty(String.valueOf(value))){
                                value = String.valueOf(value).split("_")[0];
                            }
                        } else if (QualityTemplateEnum.FOURTH.getName().equals(key)) {
                            value = t.getFourthLevel().getLevelValue();
                            if(StringUtils.isNotEmpty(String.valueOf(value))){
                                value = String.valueOf(value).split("_")[0];
                            }
                        } else if (QualityTemplateEnum.FIFTH.getName().equals(key)) {
                            value = t.getFifthLevel().getLevelValue();
                            if(StringUtils.isNotEmpty(String.valueOf(value))){
                                value = String.valueOf(value).split("_")[0];
                            }
                        } else if (QualityTemplateEnum.SIXTH.getName().equals(key)) {
                            value = t.getSixthLevel().getLevelValue();
                            if(StringUtils.isNotEmpty(String.valueOf(value))){
                                value = String.valueOf(value).split("_")[0];
                            }
                        } else if (QualityTemplateEnum.SEVENTH.getName().equals(key)) {
                            value = t.getSeventhLevel().getLevelValue();
                            if(StringUtils.isNotEmpty(String.valueOf(value))){
                                value = String.valueOf(value).split("_")[0];
                            }
                        } else if (QualityTemplateEnum.EIGHTH.getName().equals(key)) {
                            value = t.getEighthLevel().getLevelValue();
                            if(StringUtils.isNotEmpty(String.valueOf(value))){
                                value = String.valueOf(value).split("_")[0];
                            }
                        } else if (QualityTemplateEnum.PROJECT_NAME.getName().equals(key)) {
                            value = t.getProject().get("projectName");
                        } else if (QualityTemplateEnum.YEAR_TARGET_VALUE.getName().equals(key)) {
                            value = t.getYearTargetValue();
                        } else if (QualityTemplateEnum.Q1_TARGET_VALUE.getName().equals(key)) {
                            value = t.getQ1TargetValue();
                        } else if (QualityTemplateEnum.Q2_TARGET_VALUE.getName().equals(key)) {
                            value = t.getQ2TargetValue();
                        } else if (QualityTemplateEnum.Q3_TARGET_VALUE.getName().equals(key)) {
                            value = t.getQ3TargetValue();
                        } else if (QualityTemplateEnum.Q4_TARGET_VALUE.getName().equals(key)) {
                            value = t.getQ4TargetValue();
                        }

                        cell = row.createCell(cellNum);
                        cell.setCellStyle(titleStyle);
                        cellNum = setCellValue(cell, value, null, cellNum, null, row);
                        cellNum++;
                    }

                } catch (Exception e) {
                    LG.error(e.toString(), e);
                }
            }

            Iterator<String> it2 = keys.iterator();
            index = 0;
            while (it2.hasNext()) {
                key = it2.next();

                if (!headers.containsKey(key)) {
                    continue;
                }

                if (QualityTemplateEnum.YEAR.getName().equals(key)
                ||QualityTemplateEnum.PROJECT_NAME.getName().equals(key)
                ||QualityTemplateEnum.FIRST.getName().equals(key)
                ||QualityTemplateEnum.SECOND.getName().equals(key)
                ||QualityTemplateEnum.THIRD.getName().equals(key)
                ||QualityTemplateEnum.FOURTH.getName().equals(key)
                ||QualityTemplateEnum.FIFTH.getName().equals(key)
                ||QualityTemplateEnum.SIXTH.getName().equals(key)
                ||QualityTemplateEnum.SEVENTH.getName().equals(key)
                ) {
                   mergeSpecifiedColumn(sheet,index,rowStyle);
                }
                index++;
            }

            sheet.setDefaultColumnWidth(30);
            try {
                workbook.write(out);
                out.flush();
                out.close();
            } catch (IOException e) {
                LG.error(e.toString(), e);
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
    }


    public static void mergeSpecifiedColumn(HSSFSheet sheet, int column,HSSFCellStyle titleStyle) {
        int totalRows = sheet.getLastRowNum(), firstRow = 0, lastRow = 0;

        boolean isLastCompareSame = false;//上一次比较是否相同

        //这里第一行是表头,从第三行开始判断是否相同
        if (totalRows >= 2) {
            for (int i = 2; i <= totalRows; i++) {
                String lastRowCellContent = sheet.getRow(i - 1).getCell(column).getStringCellValue();

                String curRowCellContent = sheet.getRow(i).getCell(column).getStringCellValue();

                if(StringUtils.isEmpty(lastRowCellContent)&& StringUtils.isEmpty(curRowCellContent)){
                    continue;
                }
                if (curRowCellContent.equals(lastRowCellContent)) {
                    if (!isLastCompareSame) {
                        firstRow = i - 1;

                    }

                    lastRow = i;

                    isLastCompareSame = true;

                } else {
                    isLastCompareSame = false;

                    if (lastRow > firstRow) {
                       // sheet.addMergedRegion(new CellRangeAddress(firstRow, lastRow, column, column));

                    }
                }
                //最后一行时判断是否有需要合并的行
                if ((i == totalRows) && (lastRow > firstRow)) {
                    sheet.addMergedRegion(new CellRangeAddress(firstRow, lastRow, column, column));

                    titleStyle.setAlignment(HorizontalAlignment.CENTER);
                    titleStyle.setVerticalAlignment(VerticalAlignment.CENTER);
                    sheet.getRow(firstRow).getCell(column).setCellStyle(titleStyle);
                }
            }
        }

    }
    
    private static void getSheetCheckResult(Map<String, String> headers, LinkedHashSet<JSONObject> array,
                                            int flag, HSSFWorkbook workbook, HSSFSheet sheet){

        // 产生表格标题行
        HSSFRow row = sheet.createRow(0);

        // 标题行转中文
        Set<String> keys = headers.keySet();

        Iterator<String> it1 = keys.iterator();
        String key = "";    //存放临时键变量
        int c= 0;   //标题列数
        while (it1.hasNext()){
            key = it1.next();
            if (headers.containsKey(key)) {
                HSSFCell cell = row.createCell(c);
                HSSFRichTextString text = new HSSFRichTextString(headers.get(key));
                cell.setCellValue(text);
                c++;
            }
        }


        // 遍历集合数据,产生数据行
        int index = 0;

        for(JSONObject jsonObject :array){

            String label = String.valueOf(jsonObject.get("label"));

            index++;
            row = sheet.createRow(index);

            int cellNum = 0;

            //阶段
            HSSFCell cell = row.createCell(cellNum);
            cellNum = setCellValue(cell,label,null,cellNum,null,row);

            cellNum++;

            //获取阶段下的部门列表
            LinkedHashSet<JSONObject> it2 =(LinkedHashSet<JSONObject>)jsonObject.get("children");
            if(it2 == null || it2.isEmpty()){
                return ;
            }
            int departmentIndex =0;
            //往excel里写值
            for (JSONObject department : it2){

                //获取部门
                label = String.valueOf(department.get("label"));
                cell = row.createCell(cellNum);

                cellNum = setCellValue(cell,label,null,cellNum,null,row);

                cellNum++;

                LinkedHashSet<JSONObject> centerList = (LinkedHashSet<JSONObject>)department.get("children");

                if(centerList==null || centerList.isEmpty()){
                    index++;
                    cellNum = cellNum -1;
                    //row = sheet.createRow(index);

                    continue;
                }
                int centerIndex =0;
                for (JSONObject center : centerList){

                    //获取中心
                    label = String.valueOf(center.get("label"));
                    cell = row.createCell(cellNum);

                    LinkedHashSet<JSONObject> groupList = (LinkedHashSet<JSONObject>)center.get("children");

                    cellNum = setCellValue(cell,label,null,cellNum,null,row);

                    cellNum++;

                    if(groupList==null || groupList.isEmpty()){
                        index++;
                        cellNum = cellNum -1;
                        row = sheet.createRow(index);

                        continue;
                    }
                    int groupIndex =0;
                    for (JSONObject group : groupList){

                        //获取小组
                        label = String.valueOf(group.get("label"));
                        cell = row.createCell(cellNum);

                        cellNum = setCellValue(cell,label,null,cellNum,null,row);

                        cellNum++;

                        LinkedHashSet<JSONObject> itemList = (LinkedHashSet<JSONObject>)group.get("children");

                        if(itemList==null || itemList.isEmpty()){
                            index++;
                            cellNum = cellNum -1;
                            row = sheet.createRow(index);

                            continue;
                        }
                        int itemIndex =0;
                        for (JSONObject item : itemList){

                            //点检项次
                            label = String.valueOf(item.get("label"));
                            cell = row.createCell(cellNum);
                            cellNum = setCellValue(cell,label,null,cellNum,null,row);
                            cellNum++;

                            //点检结果
                            label = String.valueOf(item.get("result"));
                            cell = row.createCell(cellNum);
                            cellNum = setCellValue(cell,label,null,cellNum,null,row);
                            cellNum++;

                            //点检人
                            label = String.valueOf(item.get("responMan"));
                            cell = row.createCell(cellNum);
                            cellNum = setCellValue(cell,label,null,cellNum,null,row);
                            cellNum++;

                            //附件
                            label = String.valueOf(item.get("attachmentName"));
                            cell = row.createCell(cellNum);
                            cellNum = setCellValue(cell,label,null,cellNum,null,row);
                            cellNum++;

                            //备注
                            label = String.valueOf(item.get("resultComment"));
                            cell = row.createCell(cellNum);
                            cellNum = setCellValue(cell,label,null,cellNum,null,row);
                            cellNum++;

                            LinkedHashSet<JSONObject> midList = (LinkedHashSet<JSONObject>)item.get("children");
                            if(midList == null || midList.isEmpty()){
                                index++;
                                cellNum = cellNum -1;
                                row = sheet.createRow(index);

                                continue;
                            }

                            int midIndex = 0;
                            for(JSONObject mid : midList){
                                //不合格问题及跟进
                                label = String.valueOf(mid.get("problemFollow"));
                                cell = row.createCell(cellNum);
                                cellNum = setCellValue(cell,label,null,cellNum,null,row);
                                cellNum++;

                                LinkedHashSet<JSONObject> detailList = (LinkedHashSet<JSONObject>)mid.get("children");
                                if(detailList == null || detailList.isEmpty()){
                                    index++;
                                    cellNum = cellNum -1;
                                    row = sheet.createRow(index);

                                    continue;
                                }

                                int detailIndex = 0;
                                for(JSONObject detail : detailList){
                                    //对应BUG/问题/风险链接
                                    label = String.valueOf(detail.get("originProblemDesc"));
                                    cell = row.createCell(cellNum);
                                    cellNum = setCellValue(cell,label,null,cellNum,null,row);
                                    cellNum++;

                                    //处理人
                                    label = String.valueOf(detail.get("handlerName"));
                                    cell = row.createCell(cellNum);
                                    cellNum = setCellValue(cell,label,null,cellNum,null,row);
                                    cellNum++;

                                    //测试工程师
                                    label = String.valueOf(detail.get("engineerName"));
                                    cell = row.createCell(cellNum);
                                    cellNum = setCellValue(cell,label,null,cellNum,null,row);
                                    cellNum++;

                                    //状态
                                    label = String.valueOf(detail.get("status"));
                                    cell = row.createCell(cellNum);
                                    cellNum = setCellValue(cell,label,null,cellNum,null,row);
                                    cellNum++;

                                    //优先级
                                    label = String.valueOf(detail.get("priority"));
                                    cell = row.createCell(cellNum);
                                    cellNum = setCellValue(cell,label,null,cellNum,null,row);
                                    cellNum++;

                                    //BPM文档链接
                                    label = String.valueOf(detail.get("documentUrl"));
                                    cell = row.createCell(cellNum);
                                    cellNum = setCellValue(cell,label,null,cellNum,null,row);

                                    cellNum = cellNum -1;
                                    cellNum = cellNum -1;
                                    cellNum = cellNum -1;
                                    cellNum = cellNum -1;
                                    cellNum = cellNum -1;

                                    if((detailList.size()-1)==detailIndex
                                            &&(midList.size()-1)==midIndex
                                            &&(itemList.size()-1)==itemIndex
                                            &&(groupList.size()-1)==groupIndex
                                            &&(centerList.size()-1)==centerIndex
                                            &&(it2.size() -1)==departmentIndex){
                                        continue;
                                    }
                                    detailIndex++;
                                    index++;
                                    row = sheet.createRow(index);
                                }
                                midIndex++;
                                cellNum = cellNum -1;
                            }

                            itemIndex++;
                            cellNum = cellNum -1;
                            cellNum = cellNum -1;
                            cellNum = cellNum -1;
                            cellNum = cellNum -1;
                            cellNum = cellNum -1;
                        }
                        groupIndex++;
                        cellNum = cellNum -1;
                    }
                    centerIndex++;
                    cellNum = cellNum -1;
                }
                departmentIndex++;
                cellNum = cellNum -1;
            }
        }
        Integer startRow = 1;
        Integer overRow = 0;
        int preRow = 1;
        for(JSONObject jsonObject :array) {

            int departmentSize = 0;
            int centerSize = 0;
            int groupSize = 0;
            int itemSize = 0;
            int midSize = 0;
            int detailSize = 0;

            Integer startCol = 0;
            Integer overCol = 0;

            //获取阶段下的部门列表
            LinkedHashSet<JSONObject> it2 =(LinkedHashSet<JSONObject>)jsonObject.get("children");
            if(it2 == null){
                return ;
            }
            //合并单元格
            //往excel里写值
            for (JSONObject department : it2) {

                LinkedHashSet<JSONObject> centerList = (LinkedHashSet<JSONObject>) department.get("children");
                if (centerList == null || centerList.isEmpty()) {
                    detailSize++;
                    midSize++;
                    groupSize++;
                    itemSize++;
                    centerSize++;
                    departmentSize++;
                    continue;
                }
                for (JSONObject center : centerList) {
                    //获取小组
                    LinkedHashSet<JSONObject> groupList = (LinkedHashSet<JSONObject>) center.get("children");
                    if (groupList == null || groupList.isEmpty()) {
                        detailSize++;
                        midSize++;
                        groupSize++;
                        itemSize++;
                        continue;
                    }
                    for (JSONObject group : groupList) {

                        LinkedHashSet<JSONObject> itemList = (LinkedHashSet<JSONObject>) group.get("children");
                        if(itemList==null || itemList.isEmpty()){
                            detailSize++;
                            midSize++;
                            itemSize++;
                            continue;
                        }

                        for(JSONObject item : itemList){
                            LinkedHashSet<JSONObject> midList = (LinkedHashSet<JSONObject>) item.get("children");
                            if(midList == null || midList.isEmpty()){
                                detailSize++;
                                midSize++;
                                continue;
                            }

                            for(JSONObject mid : midList){
                                LinkedHashSet<JSONObject> detailList = (LinkedHashSet<JSONObject>) mid.get("children");
                                if(detailList == null || detailList.isEmpty()){
                                    continue;
                                }
                                //对应BUG/问题/风险链接
                                if (detailList != null && !detailList.isEmpty()) {
                                    for (JSONObject detail : detailList) {
                                        detailSize++;
                                    }
                                } else {
                                    detailSize++;
                                }

                                //不合格问题及跟进
                                startRow = preRow + midSize;
                                overRow = preRow + detailSize-1;
                                startCol = 9;
                                overCol = 9;
                                if (overRow > startRow) {
                                    sheet.addMergedRegion(new CellRangeAddress(startRow, overRow, startCol, overCol));
                                }

                                midSize = detailSize;
                            }

                            startRow = preRow + itemSize;
                            overRow = preRow +detailSize-1;
                            if (overRow > startRow) {
                                //点检项次
                                startCol = 4;
                                overCol = 4;
                                sheet.addMergedRegion(new CellRangeAddress(startRow, overRow, startCol, overCol));
                                //点检结果
                                startCol = 5;
                                overCol = 5;
                                sheet.addMergedRegion(new CellRangeAddress(startRow, overRow, startCol, overCol));
                                //点检人
                                startCol = 6;
                                overCol = 6;
                                sheet.addMergedRegion(new CellRangeAddress(startRow, overRow, startCol, overCol));
                                //附件
                                startCol = 7;
                                overCol = 7;
                                sheet.addMergedRegion(new CellRangeAddress(startRow, overRow, startCol, overCol));
                                //备注
                                startCol = 8;
                                overCol = 8;
                                sheet.addMergedRegion(new CellRangeAddress(startRow, overRow, startCol, overCol));
                            }

                            itemSize = detailSize;
                        }

                        //小组
                        startRow = preRow + groupSize;
                        overRow = preRow + detailSize-1;
                        startCol = 3;
                        overCol = 3;
                        if (overRow > startRow) {
                            sheet.addMergedRegion(new CellRangeAddress(startRow, overRow, startCol, overCol));
                        }

                        groupSize = detailSize;
                    }

                    //中心
                    startRow = preRow + centerSize;
                    overRow = preRow +detailSize-1;
                    startCol = 2;
                    overCol = 2;
                    if (overRow > startRow) {
                        sheet.addMergedRegion(new CellRangeAddress(startRow, overRow, startCol, overCol));
                    }

                    centerSize = detailSize;
                }

                //部门
                startRow = preRow + departmentSize;
                overRow = preRow + detailSize-1;
                startCol = 1;
                overCol = 1;
                if (overRow > startRow) {
                    sheet.addMergedRegion(new CellRangeAddress(startRow, overRow, startCol, overCol));
                }

                departmentSize = detailSize;
            }

            //阶段
            startRow = preRow ;
            overRow = preRow + detailSize-1;
            startCol = 0;
            overCol = 0;
            if (overRow > startRow) {
                sheet.addMergedRegion(new CellRangeAddress(startRow, overRow, startCol, overCol));
            }
            preRow = preRow + detailSize;
            startRow = preRow + detailSize-1;
        }












    }




    public static <T> void writeItemExcel(Map<String,String> headers, LinkedHashSet<JSONObject> array, OutputStream out,int flag) {

        // 声明一个工作薄
        HSSFWorkbook workbook = new HSSFWorkbook();
        // 生成一个表格
        HSSFSheet sheet = workbook.createSheet();

        //生成sheet内容
        getSheet(headers, array, flag, workbook, sheet);

        sheet.setDefaultRowHeight((short)300.0);
        sheet.setDefaultColumnWidth(30);

        try {
            workbook.write(out);
            out.flush();
            out.close();
        } catch (IOException e) {
            LG.error(e.toString(), e);
        }

    }

    private static void getSheet(Map<String, String> headers, LinkedHashSet<JSONObject>  array, int flag, HSSFWorkbook workbook, HSSFSheet sheet) {
        // 产生表格标题行
        HSSFRow row = sheet.createRow(0);

        // 标题行转中文
        Set<String> keys = headers.keySet();

        Iterator<String> it1 = keys.iterator();
        String key = "";    //存放临时键变量
        int c= 0;   //标题列数
        while (it1.hasNext()){
            key = it1.next();
            if (headers.containsKey(key)) {
                HSSFCell cell = row.createCell(c);
                HSSFRichTextString text = new HSSFRichTextString(headers.get(key));
                cell.setCellValue(text);
                c++;
            }
        }


        // 遍历集合数据,产生数据行
        int index = 0;

        for(JSONObject jsonObject :array){

            String label = String.valueOf(jsonObject.get("label"));

            index++;
            row = sheet.createRow(index);

            int cellNum = 0;

            //阶段
            HSSFCell cell = row.createCell(cellNum);
            cellNum = setCellValue(cell,label,null,cellNum,null,row);

            cellNum++;

            //获取阶段下的部门列表
            LinkedHashSet<JSONObject> it2 =(LinkedHashSet<JSONObject>)jsonObject.get("children");
            if(it2 == null || it2.isEmpty()){
                return ;
            }
            int departmentIndex =0;
            //往excel里写值
            for (JSONObject department : it2){

                //获取部门
                label = String.valueOf(department.get("label"));
                cell = row.createCell(cellNum);

                cellNum = setCellValue(cell,label,null,cellNum,null,row);

                cellNum++;

                LinkedHashSet<JSONObject> centerList = (LinkedHashSet<JSONObject>)department.get("children");

                if(centerList==null || centerList.isEmpty()){
                    index++;
                    cellNum = cellNum -1;
                    //row = sheet.createRow(index);

                    continue;
                }
                int centerIndex =0;
                for (JSONObject center : centerList){

                    //获取中心
                    label = String.valueOf(center.get("label"));
                    cell = row.createCell(cellNum);

                    LinkedHashSet<JSONObject> groupList = (LinkedHashSet<JSONObject>)center.get("children");

                    cellNum = setCellValue(cell,label,null,cellNum,null,row);

                    cellNum++;

                    if(groupList==null || groupList.isEmpty()){
                        index++;
                        cellNum = cellNum -1;
                        row = sheet.createRow(index);

                        continue;
                    }
                    int groupIndex =0;
                    for (JSONObject group : groupList){

                        //获取小组
                        label = String.valueOf(group.get("label"));
                        cell = row.createCell(cellNum);

                        cellNum = setCellValue(cell,label,null,cellNum,null,row);

                        cellNum++;

                        LinkedHashSet<JSONObject> itemList = (LinkedHashSet<JSONObject>)group.get("children");

                        if(itemList==null || itemList.isEmpty()){
                            index++;
                            cellNum = cellNum -1;
                            row = sheet.createRow(index);

                            continue;
                        }
                        int itemIndex =0;
                        for (JSONObject item : itemList){

                            //获取项次
                            label = String.valueOf(item.get("label"));
                            cell = row.createCell(cellNum);

                            cellNum = setCellValue(cell,label,null,cellNum,null,row);

                            cellNum++;
                            if(flag == 0){

                                label = String.valueOf(item.get("comment"));
                                cell = row.createCell(cellNum);

                                cellNum = setCellValue(cell,label,null,cellNum,null,row);
                            }
                            else if(flag == 1){
                                label = String.valueOf(item.get("result"));
                                cell = row.createCell(cellNum);
                                cellNum = setCellValue(cell,label,null,cellNum,null,row);
                                cellNum++;

                                label = String.valueOf(item.get("responMan"));
                                cell = row.createCell(cellNum);
                                cellNum = setCellValue(cell,label,null,cellNum,null,row);
                                cellNum++;


                                label = String.valueOf(item.get("attachmentName"));
                                cell = row.createCell(cellNum);
                                cellNum = setCellValue(cell,label,null,cellNum,null,row);
                                cellNum++;

                                label = String.valueOf(item.get("resultComment"));
                                cell = row.createCell(cellNum);
                                cellNum = setCellValue(cell,label,null,cellNum,null,row);

                                cellNum = cellNum-1;
                                cellNum = cellNum-1;
                                cellNum = cellNum-1;
                            }
                            cellNum = cellNum-1;

                            if(itemIndex == (itemList.size()-1) && (groupList.size()-1) ==groupIndex
                                    && centerIndex== (centerList.size()-1)
                                    && departmentIndex == (it2.size() -1)){
                                continue;
                            }
                            itemIndex++;
                            index++;
                            row = sheet.createRow(index);

                        }
                        groupIndex++;
                        cellNum = cellNum -1;
                    }
                    centerIndex++;
                    cellNum = cellNum -1;
                }
                departmentIndex++;
                cellNum = cellNum -1;
            }
        }
        Integer startRow = 1;
        Integer overRow = 0;
        int preRow = 1;
        for(JSONObject jsonObject :array) {

            int departmentSize = 0;
            int centerSize = 0;
            int groupSize = 0;


            //获取阶段下的部门列表
            int itemSize = 0;

            Integer startCol = 0;
            Integer overCol = 0;
            int emptyIndex = 0;

            //获取阶段下的部门列表
            LinkedHashSet<JSONObject> it2 =(LinkedHashSet<JSONObject>)jsonObject.get("children");
            if(it2 == null){
                return ;
            }
            //合并单元格
            //往excel里写值
            for (JSONObject department : it2) {

                LinkedHashSet<JSONObject> centerList = (LinkedHashSet<JSONObject>) department.get("children");
                if (centerList == null || centerList.isEmpty()) {
                    //emptyIndex ++;
                    groupSize++;
                    itemSize++;
                    centerSize++;
                    departmentSize++;
                    continue;
                }
                for (JSONObject center : centerList) {
                    //获取小组
                    LinkedHashSet<JSONObject> groupList = (LinkedHashSet<JSONObject>) center.get("children");
                    if (groupList == null || groupList.isEmpty()) {
                        //emptyIndex ++;
                        groupSize++;
                        itemSize++;

                        continue;
                    }
                    for (JSONObject group : groupList) {

                        LinkedHashSet<JSONObject> itemList = (LinkedHashSet<JSONObject>) group.get("children");

                        if(itemList==null || itemList.isEmpty()){

                            continue;
                        }

                        if (itemList != null && !itemList.isEmpty()) {
                            for (JSONObject item : itemList) {
                                itemSize++;
                            }
                        } else {
                            itemSize++;
                        }

                        startRow = preRow + groupSize;
                        overRow = preRow + itemSize-1;
                        startCol = 3;
                        overCol = 3;
                        if (overRow > startRow) {
                           sheet.addMergedRegion(new CellRangeAddress(startRow, overRow, startCol, overCol));
                        }

                        groupSize = itemSize;
                    }
                    startRow = preRow + centerSize;
                    overRow = preRow +itemSize-1;
                    startCol = 2;
                    overCol = 2;
                    if (overRow > startRow) {
                       sheet.addMergedRegion(new CellRangeAddress(startRow, overRow, startCol, overCol));
                    }

                    centerSize = itemSize;
                }


                startRow = preRow + departmentSize;
                overRow = preRow + itemSize-1;
                startCol = 1;
                overCol = 1;
                if (overRow > startRow) {
                    sheet.addMergedRegion(new CellRangeAddress(startRow, overRow, startCol, overCol));
                }

                departmentSize = itemSize;
            }

            startRow = preRow ;
            overRow = preRow + itemSize-1;
            startCol = 0;
            overCol = 0;
            if (overRow > startRow) {
                sheet.addMergedRegion(new CellRangeAddress(startRow, overRow, startCol, overCol));
            }
            preRow = preRow + itemSize;
            startRow = preRow + itemSize-1;
        }
    }

    /**
     * 每个sheet的写入
     *
     * @param sheet   页签
     * @param headers 表头
     * @param dataset 数据集合
     * @param pattern 日期格式
     */
    private static <T> void write2Sheet(HSSFSheet sheet, Map<String,String> headers, Collection<T> dataset,
                                        String pattern,HSSFWorkbook workbook) {
        //时间格式默认"yyyy-MM-dd"
        if (StringUtils.isEmpty(pattern)){
            pattern = "yyyy-MM-dd";
        }
        HSSFFont titleFont = workbook.createFont();

        titleFont.setFontHeightInPoints((short) 14);
        CellStyle titleStyle = workbook.createCellStyle();
        titleStyle.setFont(titleFont);

        titleStyle.setAlignment(HorizontalAlignment.CENTER);

        // 产生表格标题行
        HSSFRow row = sheet.createRow(0);
        // 标题行转中文
        Set<String> keys = headers.keySet();
        Iterator<String> it1 = keys.iterator();
        String key = "";    //存放临时键变量
        int c= 0;   //标题列数
        while (it1.hasNext()){
            key = it1.next();
            if (headers.containsKey(key)) {
                HSSFCell cell = row.createCell(c);
                cell.setCellStyle(titleStyle);
                HSSFRichTextString text = new HSSFRichTextString(headers.get(key));
                if("问题来源".equals(headers.get(key))|| "专业组".equals(headers.get(key)) || "严重等级".equals(headers.get(key))
                        || "问题状态".equals(headers.get(key))|| "当前处理人".equals(headers.get(key))|| "文档编号".equals(headers.get(key))
                        || "问题重要度".equals(headers.get(key))
                        || "文档状态".equals(headers.get(key)) || "项目编号".equals(headers.get(key))|| "故障类别".equals(headers.get(key))
                        || "功能模块".equals(headers.get(key))){
                    sheet.setColumnWidth(c,  (int)((50 + 0.72) * 60));
                }

                cell.setCellValue(text);
                c++;
            }
        }

        HSSFCellStyle hlink_style = workbook.createCellStyle();
        HSSFFont hlink_font = workbook.createFont();
        hlink_font.setUnderline(HSSFFont.U_SINGLE);
        hlink_font.setColor(HSSFColor.HSSFColorPredefined.BLUE.getIndex());
        hlink_style.setFont(hlink_font);

        titleFont.setFontHeightInPoints((short) 10);
        titleStyle = workbook.createCellStyle();
        titleStyle.setFont(titleFont);
        titleStyle.setAlignment(HorizontalAlignment.CENTER);
        CreationHelper createHelper = workbook.getCreationHelper();
        // 遍历集合数据,产生数据行
        Iterator<T> it = dataset.iterator();
        HSSFCell cell = null;
        Field field = null;
        int index = 0;
        while (it.hasNext()) {
            index++;
            row = sheet.createRow(index);
            T t = it.next();
            try {
                if (t instanceof Map) {
                    Map<String, Object> map = (Map<String, Object>) t;
                    int cellNum = 0;
                    //遍历列名
                    Iterator<String> it2 = keys.iterator();
                    while (it2.hasNext()){
                        key = it2.next();
                        if (!headers.containsKey(key)) {
                            LG.error("Map 中 不存在 key [" + key + "]");
                            continue;
                        }
                        Object value = map.get(key);
                        cell = row.createCell(cellNum);
                        cell.setCellStyle(titleStyle);

                        //文档编号加上链接
                        if("documentNum".equals(key)){
                            String[] strArray = String.valueOf(value).split("\\+++");
                            if(strArray.length>1){

                                org.apache.poi.hssf.usermodel.HSSFHyperlink  link = (org.apache.poi.hssf.usermodel.HSSFHyperlink) createHelper.createHyperlink(HyperlinkType.URL);

                                link.setAddress(strArray[1]);
                                cell.setHyperlink(link);
                                cell.setCellStyle(hlink_style);
                            }
                            if(strArray!=null && strArray.length>=1){

                                cell.setCellValue(strArray[0]);
                            }
                            else{
                                cell.setCellValue("");
                            }
                        }else{
                            cellNum = setCellValue(cell,value,pattern,cellNum,null,row);
                        }


                        cellNum++;
                    }
                } else {
                    List<FieldForSortting> fields = sortFieldByAnno(t.getClass());
                    int cellNum = 0;
                    for (int i = 0; i < fields.size(); i++) {
                        cell = row.createCell(cellNum);
                        field = fields.get(i).getField();
                        field.setAccessible(true);
                        Object value = field.get(t);
                        cellNum = setCellValue(cell,value,pattern,cellNum,field,row);
                        cellNum++;
                    }
                }
            } catch (Exception e) {
                LG.error(e.toString(), e);
            }
        }
        sheet.setDefaultColumnWidth(30);
    }


    /**
     * 每个sheet的写入
     *
     * @param sheet   页签
     * @param headers 表头
     * @param dataset 数据集合
     * @param pattern 日期格式
     */
    private static <T> void write4Sheet(HSSFSheet sheet, Map<String,String> headers, List<Map<String, Object>> dataset,
                                        String pattern,HSSFWorkbook workbook,List<ProjectTypeStageAllOutVO> list,ProjectTypeStageInVO vo) throws ParseException {
        //时间格式默认"yyyy-MM-dd"
        if (StringUtils.isEmpty(pattern)){
            pattern = "yyyy-MM-dd";
        }
        HSSFFont titleFont = workbook.createFont();

        titleFont.setFontHeightInPoints((short) 14);
        CellStyle titleStyle = workbook.createCellStyle();
        titleStyle.setFont(titleFont);

        titleStyle.setAlignment(HorizontalAlignment.CENTER);

        // 产生表格标题行
        HSSFRow row = sheet.createRow(0);
        // 标题行转中文
        Set<String> keys = headers.keySet();
        Iterator<String> it1 = keys.iterator();
        String key = "";    //存放临时键变量
        int c= 0;   //标题列数
        while (it1.hasNext()){
            key = it1.next();
            if (headers.containsKey(key)) {
                HSSFCell cell = row.createCell(c);
                cell.setCellStyle(titleStyle);
                HSSFRichTextString text = new HSSFRichTextString(headers.get(key));
                cell.setCellValue(text);
                c++;
            }
        }

        titleFont.setFontHeightInPoints((short) 10);
        titleStyle = workbook.createCellStyle();
        titleStyle.setFont(titleFont);
        titleStyle.setAlignment(HorizontalAlignment.CENTER);

        // 遍历集合数据,产生数据行
         headers =  headers;


        Row row3;
        int kk=0;
        row3 = sheet.createRow((short) 0);
        for (String key1 : headers.keySet()) {
            row3.createCell((short) kk).setCellValue(String.valueOf(headers.get(key1)));
            kk=kk+1;
        }
        for(int i=0;i<dataset.size();i++) {
            row3 = sheet.createRow((short) i+1);
            List rowData = new ArrayList();
            Map<String, Object> hashMap = dataset.get(i);
            kk=0;
            for (String key1 : headers.keySet()) {
                if (dataset.get(i).get(key1) == null) {
                    rowData.add("");
                    row3.createCell((short) kk).setCellValue("");
                } else {
//                    rowData.add(dataset.get(i).get(key1));
                    String name =  String.valueOf(hashMap.get(key1));
                    if(name!=null){
                        int as = 1;
                    }
                    if("项目编号".equals(key1)){
                        String[] names = (String[]) hashMap.get(key1);
                        String namez = "";
                        for(int m=0;m<names.length;m++){
                            namez = namez +","+names[m];
                        }
                        if("".equals(namez)){

                        }else{
                            namez = namez.substring(1);
                        }
                        row3.createCell((short) kk).setCellValue(namez);
                    }else{
                        row3.createCell((short) kk).setCellValue(name);
                    }
                }
                kk=kk+1;
            }
        }



        sheet.setDefaultColumnWidth(30);

        List<ProjectTypeStageAllOutVO> lists = list;

        SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd");
        Date stDate = vo.getStartDate();
        Date endDate = vo.getEndDate();
        long from1 = stDate.getTime();
        long to1 = endDate.getTime();
        int days = (int) ((to1 - from1) / (1000 * 60 * 60 * 24));
        //导出标题头 根据筛选条件循环复制标题头
        LinkedHashMap<String, Integer> linkMap = new LinkedHashMap<>();
        int num = 5;
        for(int k=0;k<days;k++){
            String kDate = "";
            Calendar calendar = Calendar.getInstance();
            calendar.setTime(stDate);
            calendar.add(Calendar.DAY_OF_MONTH, +k);//+1今天的时间加一天
            kDate = dateFormat.format(calendar.getTime());
            headers.put(kDate, kDate);
            linkMap.put(kDate,num+k);
        }



        List<Short> listColor = new ArrayList<>();
        listColor.add(HSSFColor.HSSFColorPredefined.GOLD.getIndex());
        listColor.add(HSSFColor.HSSFColorPredefined.YELLOW.getIndex());
        listColor.add(HSSFColor.HSSFColorPredefined.BRIGHT_GREEN.getIndex());
        listColor.add(HSSFColor.HSSFColorPredefined.TURQUOISE.getIndex());
        listColor.add(HSSFColor.HSSFColorPredefined.DARK_RED.getIndex());
        listColor.add(HSSFColor.HSSFColorPredefined.SKY_BLUE.getIndex());
        listColor.add(HSSFColor.HSSFColorPredefined.PLUM.getIndex());
        listColor.add(HSSFColor.HSSFColorPredefined.GREY_25_PERCENT.getIndex());
        listColor.add(HSSFColor.HSSFColorPredefined.ROSE.getIndex());
        listColor.add(HSSFColor.HSSFColorPredefined.LIGHT_YELLOW.getIndex());
        listColor.add(HSSFColor.HSSFColorPredefined.LIGHT_GREEN.getIndex());

        listColor.add(HSSFColor.HSSFColorPredefined.LIGHT_TURQUOISE.getIndex());
        listColor.add(HSSFColor.HSSFColorPredefined.PALE_BLUE.getIndex());
        listColor.add(HSSFColor.HSSFColorPredefined.LAVENDER.getIndex());
        listColor.add(HSSFColor.HSSFColorPredefined.CORNFLOWER_BLUE.getIndex());
        listColor.add(HSSFColor.HSSFColorPredefined.LEMON_CHIFFON.getIndex());



        for(int i=0;i<list.size();i++){
            int rNum = i+1;
            Row row2 = sheet.getRow(rNum);
            CellStyle cellStyle = workbook.createCellStyle();

            List<ProjectTypeStageOutVO> listInfoStage = list.get(i).getListInfoStage();
            HashMap<String,Integer> numMap = new HashMap<>();
            for(int m=0;m<listInfoStage.size();m++){
                ProjectTypeStageOutVO voN = listInfoStage.get(m);
                Date stDateN = dateFormat.parse(voN.getStartDate());
                Date endDateN = dateFormat.parse(voN.getEndDate());
                long from1N = stDateN.getTime();
                long to1N = endDateN.getTime();
                int daysN = (int) ((to1N - from1N) / (1000 * 60 * 60 * 24));
                //根据日期 一个单元格一个单元格赋值,同时记录下位置
                for(int k=0;k<daysN;k++){
                    String kDate = "";
                    Calendar calendar = Calendar.getInstance();
                    calendar.setTime(stDateN);
                    calendar.add(Calendar.DAY_OF_MONTH, +k);//+1今天的时间加一天
                    Integer startNum = linkMap.get(dateFormat.format(stDateN));
                    if(startNum==null){
                        kDate = dateFormat.format(stDate);
                    }else{
                        kDate = dateFormat.format(calendar.getTime());
                    }
                    Integer nums = linkMap.get(kDate);
                    if (k > listColor.size()) {
                        cellStyle.setFillForegroundColor(HSSFColor.HSSFColorPredefined.LAVENDER.getIndex());
                    } else {
                        cellStyle.setFillForegroundColor(listColor.get(k));
                    }
                    cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
                    cellStyle.setAlignment(HorizontalAlignment.CENTER);
                    if(nums!=null){
                        row2.getCell(nums).setCellStyle(cellStyle);
                    }
                }
                Integer startNum = linkMap.get(dateFormat.format(stDateN));
                if(startNum==null){
                    startNum = 5;
                }
                Integer endNum = linkMap.get(dateFormat.format(endDateN));
                if(endNum==null){
                    endNum = 5+linkMap.size()-1;
                }
                int maxNum = numMap.get("max")==null?0:numMap.get("max");
                //如果有重合部分 则从前一个合并单元格的下一列开始
                if(startNum<maxNum){
                    startNum = maxNum +1;
                }
                CellRangeAddress region = new CellRangeAddress(rNum, rNum, startNum, endNum);
                if(startNum==endNum){

                }else{
                    sheet.addMergedRegion(region);
                }
                HSSFCellStyle style = workbook.createCellStyle();
                style.setAlignment(HorizontalAlignment.CENTER);	//左右居中
                    /*var cell = sheet.getRow(rNum).getCell(index["firstCol"]);
                    cell.CellStyle = cellstyle;*/
                if(endNum>maxNum){
                    maxNum=endNum;
                }
                numMap.put("max",maxNum);
            }
        }

    }

    private static int setCellValue(HSSFCell cell,Object value,String pattern,int cellNum,Field field,HSSFRow row){
        String textValue = null;
        if (value instanceof Integer) {
            int intValue = (Integer) value;
            cell.setCellValue(intValue);
        } else if (value instanceof Float) {
            float fValue = (Float) value;
            cell.setCellValue(fValue);
        } else if (value instanceof Double) {
            double dValue = (Double) value;
            cell.setCellValue(dValue);
        } else if (value instanceof Long) {
            long longValue = (Long) value;
            cell.setCellValue(longValue);
        } else if (value instanceof Boolean) {
            boolean bValue = (Boolean) value;
            cell.setCellValue(bValue);
        } else if (value instanceof Date) {
            Date date = (Date) value;
            SimpleDateFormat sdf = new SimpleDateFormat(pattern);
            textValue = sdf.format(date);
        } else if (value instanceof String[]) {
            String[] strArr = (String[]) value;
            for (int j = 0; j < strArr.length; j++) {
                String str = strArr[j];
                cell.setCellValue(str);
                if (j != strArr.length - 1) {
                    cellNum++;
                    cell = row.createCell(cellNum);
                }
            }
        } else if (value instanceof Double[]) {
            Double[] douArr = (Double[]) value;
            for (int j = 0; j < douArr.length; j++) {
                Double val = douArr[j];
                // 值不为空则set Value
                if (val != null) {
                    cell.setCellValue(val);
                }

                if (j != douArr.length - 1) {
                    cellNum++;
                    cell = row.createCell(cellNum);
                }
            }
        } else {
            // 其它数据类型都当作字符串简单处理
            String empty = StringUtils.EMPTY;
            if(field != null) {
                ExcelCell anno = field.getAnnotation(ExcelCell.class);
                if (anno != null) {
                    empty = anno.defaultValue();
                }
            }
            textValue = value == null ? empty : value.toString();
        }
        if (textValue != null) {
            HSSFRichTextString richString = new HSSFRichTextString(textValue);
            if("null".equals(textValue)){
                cell.setCellValue("");
            }
            else{
                cell.setCellValue(richString);
            }

        }
        return cellNum;
    }

    /**
     * 把Excel的数据封装成voList
     *
     * @param clazz       vo的Class
     * @param inputStream excel输入流
     * @param pattern     如果有时间数据,设定输入格式。默认为"yyy-MM-dd"
     * @param logs        错误log集合
     * @param arrayCount  如果vo中有数组类型,那就按照index顺序,把数组应该有几个值写上.
     * @return voList
     * @throws RuntimeException
     */
    @SuppressWarnings("unchecked")
    public static <T> Collection<T> importExcel(Class<T> clazz, InputStream inputStream,
                                                String pattern, ExcelLogs logs, Integer... arrayCount) {
        Workbook workBook;
        try {
            workBook = WorkbookFactory.create(inputStream);
        } catch (Exception e) {
            LG.error("load excel file error",e);
            return null;
        }
        List<T> list = new ArrayList<>();
        Sheet sheet = workBook.getSheetAt(0);
        Iterator<Row> rowIterator = sheet.rowIterator();
        try {
            List<ExcelLog> logList = new ArrayList<>();
            // Map<title,index>
            Map<String, Integer> titleMap = new HashMap<>();

            while (rowIterator.hasNext()) {
                Row row = rowIterator.next();
                if (row.getRowNum() == 0) {
                    if (clazz == Map.class) {
                        // 解析map用的key,就是excel标题行
                        Iterator<Cell> cellIterator = row.cellIterator();
                        Integer index = 0;
                        while (cellIterator.hasNext()) {
                            String value = cellIterator.next().getStringCellValue();
                            titleMap.put(value, index);
                            index++;
                        }
                    }
                    continue;
                }
                // 整行都空,就跳过
                boolean allRowIsNull = true;
                Iterator<Cell> cellIterator = row.cellIterator();
                while (cellIterator.hasNext()) {
                    Object cellValue = getCellValue(cellIterator.next());
                    if (cellValue != null) {
                        allRowIsNull = false;
                        break;
                    }
                }
                if (allRowIsNull) {
                    LG.warn("Excel row " + row.getRowNum() + " all row value is null!");
                    continue;
                }
                StringBuilder log = new StringBuilder();
                if (clazz == Map.class) {
                    Map<String, Object> map = new HashMap<>();
                    for (String k : titleMap.keySet()) {
                        Integer index = titleMap.get(k);
                        Cell cell = row.getCell(index);
                        // 判空
                        if (cell == null) {
                            map.put(k, null);
                        } else {
                            cell.setCellType(CellType.STRING);
                            String value = cell.getStringCellValue();
                            map.put(k, value);
                        }
                    }
                    list.add((T) map);

                } else {
                    T t = clazz.newInstance();
                    int arrayIndex = 0;// 标识当前第几个数组了
                    int cellIndex = 0;// 标识当前读到这一行的第几个cell了
                    List<FieldForSortting> fields = sortFieldByAnno(clazz);
                    for (FieldForSortting ffs : fields) {
                        Field field = ffs.getField();
                        field.setAccessible(true);
                        if (field.getType().isArray()) {
                            Integer count = arrayCount[arrayIndex];
                            Object[] value;
                            if (field.getType().equals(String[].class)) {
                                value = new String[count];
                            } else {
                                // 目前只支持String[]和Double[]
                                value = new Double[count];
                            }
                            for (int i = 0; i < count; i++) {
                                Cell cell = row.getCell(cellIndex);
                                String errMsg = validateCell(cell, field, cellIndex);
                                if (StringUtils.isBlank(errMsg)) {
                                    value[i] = getCellValue(cell);
                                } else {
                                    log.append(errMsg);
                                    log.append(";");
                                    logs.setHasError(true);
                                }
                                cellIndex++;
                            }
                            field.set(t, value);
                            arrayIndex++;
                        } else {
                            Cell cell = row.getCell(cellIndex);
                            String errMsg = validateCell(cell, field, cellIndex);
                            if (StringUtils.isBlank(errMsg)) {
                                Object value = null;
                                // 处理特殊情况,Excel中的String,转换成Bean的Date
                                if (field.getType().equals(Date.class)
                                        && cell.getCellTypeEnum() == CellType.STRING) {
                                    Object strDate = getCellValue(cell);
                                    try {
                                        value = new SimpleDateFormat(pattern).parse(strDate.toString());
                                    } catch (ParseException e) {

                                        errMsg =
                                                MessageFormat.format("the cell [{0}] can not be converted to a date ",
                                                        CellReference.convertNumToColString(cell.getColumnIndex()));
                                    }
                                } else {
                                    value = getCellValue(cell);
                                    // 处理特殊情况,excel的value为String,且bean中为其他,且defaultValue不为空,那就=defaultValue
                                    ExcelCell annoCell = field.getAnnotation(ExcelCell.class);
                                    if (value instanceof String && !field.getType().equals(String.class)
                                            && StringUtils.isNotBlank(annoCell.defaultValue())) {
                                        value = annoCell.defaultValue();
                                    }
                                }
                                field.set(t, value);
                            }
                            if (StringUtils.isNotBlank(errMsg)) {
                                log.append(errMsg);
                                log.append(";");
                                logs.setHasError(true);
                            }
                            cellIndex++;
                        }
                    }
                    list.add(t);
                    logList.add(new ExcelLog(t, log.toString(), row.getRowNum() + 1));
                }
            }
            logs.setLogList(logList);
        } catch (InstantiationException e) {
            throw new RuntimeException(MessageFormat.format("can not instance class:{0}",
                    clazz.getSimpleName()), e);
        } catch (IllegalAccessException e) {
            throw new RuntimeException(MessageFormat.format("can not instance class:{0}",
                    clazz.getSimpleName()), e);
        }
        return list;
    }

    /**
     * 驗證Cell類型是否正確
     *
     * @param cell    cell單元格
     * @param field   欄位
     * @param cellNum 第幾個欄位,用於errMsg
     * @return
     */
    private static String validateCell(Cell cell, Field field, int cellNum) {
        String columnName = CellReference.convertNumToColString(cellNum);
        String result = null;
        CellType[] cellTypeArr = validateMap.get(field.getType());
        if (cellTypeArr == null) {
            result = MessageFormat.format("Unsupported type [{0}]", field.getType().getSimpleName());
            return result;
        }
        ExcelCell annoCell = field.getAnnotation(ExcelCell.class);
        if (cell == null
                || (cell.getCellTypeEnum() == CellType.STRING && StringUtils.isBlank(cell
                .getStringCellValue()))) {
            if (annoCell != null && annoCell.valid().allowNull() == false) {
                result = MessageFormat.format("the cell [{0}] can not null", columnName);
            }
            ;
        } else if (cell.getCellTypeEnum() == CellType.BLANK && annoCell.valid().allowNull()) {
            return result;
        } else {
            List<CellType> cellTypes = Arrays.asList(cellTypeArr);

            // 如果類型不在指定範圍內,並且沒有默認值
            if (!(cellTypes.contains(cell.getCellTypeEnum()))
                    || StringUtils.isNotBlank(annoCell.defaultValue())
                    && cell.getCellTypeEnum() == CellType.STRING) {
                StringBuilder strType = new StringBuilder();
                for (int i = 0; i < cellTypes.size(); i++) {
                    CellType cellType = cellTypes.get(i);
                    strType.append(getCellTypeByInt(cellType));
                    if (i != cellTypes.size() - 1) {
                        strType.append(",");
                    }
                }
                result =
                        MessageFormat.format("the cell [{0}] type must [{1}]", columnName, strType.toString());
            } else {
                // 类型符合验证,但值不在要求范围内的
                // String in
                if (annoCell.valid().in().length != 0 && cell.getCellTypeEnum() == CellType.STRING) {
                    String[] in = annoCell.valid().in();
                    String cellValue = cell.getStringCellValue();
                    boolean isIn = false;
                    for (String str : in) {
                        if (str.equals(cellValue)) {
                            isIn = true;
                        }
                    }
                    if (!isIn) {
                        result = MessageFormat.format("the cell [{0}] value must in {1}", columnName, in);
                    }
                }
                // 数字型
                if (cell.getCellTypeEnum() == CellType.NUMERIC) {
                    double cellValue = cell.getNumericCellValue();
                    // 小于
                    if (!Double.isNaN(annoCell.valid().lt())) {
                        if (!(cellValue < annoCell.valid().lt())) {
                            result =
                                    MessageFormat.format("the cell [{0}] value must less than [{1}]", columnName,
                                            annoCell.valid().lt());
                        }
                    }
                    // 大于
                    if (!Double.isNaN(annoCell.valid().gt())) {
                        if (!(cellValue > annoCell.valid().gt())) {
                            result =
                                    MessageFormat.format("the cell [{0}] value must greater than [{1}]", columnName,
                                            annoCell.valid().gt());
                        }
                    }
                    // 小于等于
                    if (!Double.isNaN(annoCell.valid().le())) {
                        if (!(cellValue <= annoCell.valid().le())) {
                            result =
                                    MessageFormat.format("the cell [{0}] value must less than or equal [{1}]",
                                            columnName, annoCell.valid().le());
                        }
                    }
                    // 大于等于
                    if (!Double.isNaN(annoCell.valid().ge())) {
                        if (!(cellValue >= annoCell.valid().ge())) {
                            result =
                                    MessageFormat.format("the cell [{0}] value must greater than or equal [{1}]",
                                            columnName, annoCell.valid().ge());
                        }
                    }
                }
            }
        }
        return result;
    }

    /**
     * 根据annotation的seq排序后的栏位
     *
     * @param clazz
     * @return
     */
    private static List<FieldForSortting> sortFieldByAnno(Class<?> clazz) {
        Field[] fieldsArr = clazz.getDeclaredFields();
        List<FieldForSortting> fields = new ArrayList<>();
        List<FieldForSortting> annoNullFields = new ArrayList<>();
        for (Field field : fieldsArr) {
            ExcelCell ec = field.getAnnotation(ExcelCell.class);
            if (ec == null) {
                // 没有ExcelCell Annotation 视为不汇入
                continue;
            }
            int id = ec.index();
            fields.add(new FieldForSortting(field, id));
        }
        fields.addAll(annoNullFields);
        sortByProperties(fields, true, false, "index");
        return fields;
    }

    @SuppressWarnings({ "rawtypes", "unchecked" })
    private static void sortByProperties(List<? extends Object> list, boolean isNullHigh,
                                         boolean isReversed, String... props) {
        if (CollectionUtils.isNotEmpty(list)) {
            Comparator<?> typeComp = ComparableComparator.getInstance();
            if (isNullHigh == true) {
                typeComp = ComparatorUtils.nullHighComparator(typeComp);
            } else {
                typeComp = ComparatorUtils.nullLowComparator(typeComp);
            }
            if (isReversed) {
                typeComp = ComparatorUtils.reversedComparator(typeComp);
            }

            List<Object> sortCols = new ArrayList<Object>();

            if (props != null) {
                for (String prop : props) {
                    sortCols.add(new BeanComparator(prop, typeComp));
                }
            }
            if (sortCols.size() > 0) {
                Comparator<Object> sortChain = new ComparatorChain(sortCols);
                Collections.sort(list, sortChain);
            }
        }
    }

}

三、导入

 @PostMapping(value = "/import.ajax")
    @ResponseBody
    @ApiOperation("导入")
    public Result importKeyParam(@RequestParam(required = true) MultipartFile file,
                                 @RequestParam(required = true) Integer importType,
                                 @RequestParam(required = true) Integer type) {
        // 文件名校验
        String fileName = file.getOriginalFilename();
        if (!fileName.matches(GenConstants.LOWEXCEL) && !fileName.matches(GenConstants.HIGHEXCEL)) {
            return ResultGenerator.genFailResult(ResultEnum.FILE_INCORRENT.getMessage());
        }
        // 文件大小校验
        if (file.getSize() / CommonConstant.FILE_FORMAT_SIZE / CommonConstant.FILE_FORMAT_SIZE > 0.1) {
            return ResultGenerator.genFailResult("文件过大,不能超过" + CommonConstant.BIG_EXCEL_SIZE + "KB");
        }

        Collection<Map> maps;
        try {
            maps = ExcelUtil.importExcel(Map.class, file.getInputStream(), "yyyy-MM-dd HH:mm:ss", new ExcelLogs(), 0);
        } catch (Exception e) {
            return ResultGenerator.genFailResult("请求或者文件有问题!");
        }
        if (maps == null || maps.size() == 0) {
            return ResultGenerator.genFailResult(ResultEnum.EMPTY_EXCEL_DATA.getMessage());
        }
        if (CustomFieldEnum.getByType(type) != null) {
            HashMap<String, String> headers = new LinkedHashMap<>();
            getExpostHeader(headers, type, true);
            try {
                List<Map> mapList = Arrays.asList(maps.toArray(new Map[0]));
                // 保存
                HashMap<String, Object> ret = getInterComService(type).importData(headers, mapList, importType, CustomFieldEnum.getByType(type));
                return ResultGenerator.genSuccessResult(ret);
            } catch (Exception e) {
                return ResultGenerator.genFailResult("导入失败:逻辑有误!");
            }
        } else {
            return ResultGenerator.genFailResult("导入失败:模块类型有误!");
        }
    }
上一篇 下一篇