yhlxj/netflix-web/src/main/java/com/cyksj/web/util/EasyExcelUtils.java

package com.cyksj.web.util;

import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.ExcelWriter;
import com.alibaba.excel.support.ExcelTypeEnum;
import com.alibaba.excel.write.metadata.WriteSheet;
import com.cyksj.common.exception.BusinessRuntimeException;
import com.cyksj.model.excel.ExcelSheetAndData;
import com.cyksj.model.excel.ExportExcelError;
import org.apache.commons.lang3.StringUtils;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.util.CollectionUtils;

import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.util.ArrayList;
import java.util.List;
import java.util.Optional;

/**
 * @ClassName EasyExcelUtils
 * @author chan
 * @Date 2019/11/1
 * @Description 封装的EasyExcel导出工具类
 */
public class EasyExcelUtils {

	private static final Logger LOGGER = LoggerFactory.getLogger(EasyExcelUtils.class);

	/**
	 *
	 * 写回客户端
	 * 
	 * @author chan
	 * @param response
	 * @param fileName
	 * @param type
	 */
	@SuppressWarnings("rawtypes")
	public static void createExcelStreamMutilByEasyExcel(HttpServletResponse response, Class tClass,
                                                         List<?> sheetNameAndDateList, String sheetName, String fileName, ExcelTypeEnum type, String charset) {
		final String methodName = "createExcelStreamMutilByEasyExcel";
		LOGGER.info("{},excel导出开始[end]",methodName);
		if (checkParam(sheetNameAndDateList, type)) {
			LOGGER.warn("{},excel导出 数据源为空！",methodName);
			throw BusinessRuntimeException.getInstance("暂无数据！");

		}
		try {
			if(StringUtils.isBlank(charset)){
				charset = "iso-8859-1";
			}
			response.setContentType("application/x-download");
			response.setContentType("application/vnd.ms-excel");
			response.setCharacterEncoding("utf-8");
			response.setHeader("Content-disposition",
					"attachment;filename=" + new String(fileName.getBytes("utf-8"), "ISO-8859-1")+ type.getValue());

			EasyExcel.write(response.getOutputStream(), tClass).sheet(sheetName).doWrite(sheetNameAndDateList);
			LOGGER.info("{},excel导出结束[end]",methodName);// 方法3 如果写到不同的sheet 不同的对象
		} catch (IOException e) {
			LOGGER.warn("{},excel导出 异常：{}",methodName,e.getMessage());
		}
	}

	/**
	 *
	 * 校验
	 * 
	 * @author chan
	 * @param type
	 * @return
	 */
	private static boolean checkParam(List<?> sheetNameAndDateList, ExcelTypeEnum type) {
		final String methodName = "Excel工具类 checkParam方法";
		if (CollectionUtils.isEmpty(sheetNameAndDateList)) {
			LOGGER.warn(methodName, "SheetNameAndDateList不能为空");
			return true;
		} else if (type == null) {
			LOGGER.warn(methodName, "导出的excel类型不能为空");
			return true;
		}
		return false;
	}

	/**
	 *
	 * 写回客户端
	 *
	 * @author chan
	 * @param response
	 * @param fileName
	 * @param type
	 */
	@SuppressWarnings("rawtypes")
	public static void createExcelStreamMutilByEasyExcel(HttpServletResponse response,
														 List<ExcelSheetAndData> sheetNameAndDateList, String fileName, ExcelTypeEnum type, String charset) {
		final String methodName = "createExcelStreamMutilByEasyExcel";
		LOGGER.info("{},excel导出开始[end]",methodName);
		if (checkParam(sheetNameAndDateList, type)) {
			LOGGER.warn("{},excel导出 数据源为空！",methodName);
			throw BusinessRuntimeException.getInstance("暂无数据！");

		}
		try {
			if(StringUtils.isBlank(charset)){
				charset = "iso-8859-1";
			}
			response.setContentType("application/x-download");
			response.setContentType("application/vnd.ms-excel");
			response.setCharacterEncoding("utf-8");
			response.setHeader("Content-disposition",
					"attachment;filename=" + new String(fileName.getBytes("utf-8"), "ISO-8859-1")+ type.getValue());
			ExcelWriter excelWriter = EasyExcel.write(response.getOutputStream()).build();
			int i = 0;
			for (ExcelSheetAndData excelSheetAndData : sheetNameAndDateList) {
				WriteSheet writeSheet = EasyExcel.writerSheet(i, excelSheetAndData.getSheetName()).head(excelSheetAndData.getClazz()).build();
				excelWriter.write(excelSheetAndData.getData(),writeSheet);
				i++;
			}

			excelWriter.finish();
			LOGGER.info("{},excel导出结束[end]",methodName);
			response.getOutputStream().close();

		} catch (IOException e) {
			LOGGER.warn("{},excel导出 异常：{}",methodName,e.getMessage());
		}
	}

	/**
	 * 获取模板 Excel
	 */
	public static void createTemplateExcel(HttpServletResponse response, Class tClass, String sheetName, String fileName, ExcelTypeEnum type, String charset) {
		final String methodName = "createTemplateExcel";
		LOGGER.info("{},excel导出开始[end]",methodName);
		try {
			response.setContentType("application/x-download");
			response.setContentType("application/vnd.ms-excel");
			response.setCharacterEncoding("utf-8");
			response.setHeader("Content-disposition",
					"attachment;filename=" + new String(fileName.getBytes("utf-8"), "ISO-8859-1")+ type.getValue());

			EasyExcel.write(response.getOutputStream(), tClass).sheet(sheetName).doWrite(null);
			LOGGER.info("{},excel导出结束[end]",methodName);
		} catch (IOException e) {
			LOGGER.warn("{},excel导出 异常：{}",methodName,e.getMessage());
		}
	}



	/**
	 *
	 * 错误信息写回
	 *
	 * @author chan
	 * @param code 错误代码
	 * @param msg  错误信息
	 * @param response
	 */
	public static void error(String code , String msg, String remark, HttpServletResponse response){
		ExportExcelError exportExcelError = new ExportExcelError();

		Optional<String> codeOpt = Optional.ofNullable(code);
		Optional<String> msgOpt = Optional.ofNullable(msg);
		Optional<String> remarkOpt = Optional.ofNullable(remark);

		exportExcelError.setCode(codeOpt.orElse("99999"));
		exportExcelError.setMsg(msgOpt.orElse("系统错误"));
		exportExcelError.setRemark(remarkOpt.orElse("系统错误"));

		List<ExportExcelError> errorList = new ArrayList<>();
		errorList.add(exportExcelError);

		createExcelStreamMutilByEasyExcel(response,ExportExcelError.class,errorList,"Excel导出异常","Excel导出异常", ExcelTypeEnum.XLSX,null);
	}
}