Excel工具类-导出

使用前需要导出以下依赖：

	<!-- poi支持xls格式的excel文件 -->
	<!-- https://mvnrepository.com/artifact/org.apache.poi/poi -->
    <dependency>
      <groupId>org.apache.poi</groupId>
      <artifactId>poi</artifactId>
      <version>3.17</version>
    </dependency>
    <!-- 让poi支持xlsx格式的excel文件 -->
    <!-- https://mvnrepository.com/artifact/org.apache.poi/poi-ooxml -->
    <dependency>
      <groupId>org.apache.poi</groupId>
      <artifactId>poi-ooxml</artifactId>
      <version>3.17</version>
    </dependency>
    <!-- https://mvnrepository.com/artifact/org.apache.poi/poi-ooxml-schemas -->
    <dependency>
      <groupId>org.apache.poi</groupId>
      <artifactId>poi-ooxml-schemas</artifactId>
      <version>3.17</version>
    </dependency>

excel工具类-导出 代码如下：

package cn.com.hellfs.util;

import org.apache.commons.lang3.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Component;

import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletResponse;
import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.UnsupportedEncodingException;
import java.net.URLEncoder;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

/**
 * Excel工具类-导出
 * 使用顺序:getDownLoadWorkBook -> logicData -> setDownLoadResponse -> downLoad
 * @author HELLFS
 * @createDate 2021-06-11
 */
@Component
public class ExcelUtil {

    private Logger logger = LoggerFactory.getLogger(ExcelUtil.class);

    @Autowired
    DefaultLogicData defaultLogicData;

    /**
     * 获取作用于导出的Workbook对象
     * @param fileName      文件名(包含文件后缀名,例:excel模板.xlsx)
     * @param filePath      文件路径(不包含文件名，例:c:\excel\)
     *                          注意：路径分隔符在linux和windows下的区别；
     *                              为null,代码生成excel文件，填充数据；
     *                              不为null，读入excel模板文件,填充数据
     * @return  Workbook
     */
    public Workbook getDownLoadWorkBook(String fileName,String filePath){
        File file = null;
        if(StringUtils.isNotEmpty(filePath)){
            String fileUrl = filePath + fileName;
            logger.info("excel文件路径:{}",fileUrl);
            file = new File(fileUrl);
            if(!file.exists()){
                logger.info("指定的excel文件不存在!");
            }
        }

        Workbook workbook = null;

        if(fileName.endsWith(".xlsx")){
            if(file == null){
                workbook = new XSSFWorkbook();
            }else{
                try {
                    workbook = new XSSFWorkbook(new FileInputStream(file));
                } catch (IOException e) {
                    logger.error("读取excel文件失败，原因:{}",e);
                }
            }
        }else if(fileName.endsWith(".xls")){
            if(file == null){
                workbook = new HSSFWorkbook();
            }else{
                try {
                    workbook = new HSSFWorkbook(new FileInputStream(file));
                } catch (IOException e) {
                    logger.error("读取excel文件失败，原因:{}",e);
                }
            }
        }else{
            logger.info("其他excel文件格式，暂不支持；如需支持，请自行开发");
        }
        return workbook;
    }

    /**
     * excel数据处理逻辑
     *  使用方法：
     *      默认处理：logicData(workbook,null,params)
     *      自定义处理：以下方式任选其一
     *      logicData(workbook,new ExcelUtil.LogicData(){
     *         @Override
     *         public void logicData(Workbook workbook, Object... params) {
     *              //...处理逻辑
     *         }
     *      },params)
     *      或者
     *      logicData(workbook,(Workbook workbook, Object... params) -> {
     *         //...处理逻辑
     *      },params)
     *      注意：LogicExcel方法的params参数对应LogicData接口中logic方法的params参数
     * @param workbook      excel对象
     * @param logicData     处理逻辑接口,为null，则默认处理；不为null，则自定义处理
     *                          默认处理：参数列表个数为1，参数类型:Map<String,Map<List<String>,List<Object[]>>>
     *                              泛型解释：外层key:sheet页名，内层key:列名，内层value:数据
     *                          自定义处理：实现LogicData接口
     * @param params        参数列表(用于处理逻辑相关参数)
     */
    public void logicData(Workbook workbook,LogicData logicData,Object... params){
        if(logicData != null){
            logicData.logicData(workbook, params);
        }else{
            defaultLogicData.logicData(workbook, params);
        }
    }

    /**
     * 设置下载时的响应头，使得文件存在浏览器设置的下载位置
     * @param response      响应对象
     * @param fileName      文件名(包含文件后缀名)
     */
    public void setDownLoadResponse(HttpServletResponse response,String fileName){
        try {
            response.setContentType("application/ms-excel;charset=UTF-8");
            response.setHeader("Content-Disposition",
                    "attachment;filename=".concat(String.valueOf(URLEncoder.encode(fileName,"UTF-8"))));
        } catch (UnsupportedEncodingException e) {
            logger.error("设置响应头报错，原因:{}",e);
        }
    }

    /**
     * 下载
     * @param workbook      excel对象
     * @param response      响应对象
     * @param fileName      文件名(包含文件后缀名)
     */
    public void downLoad(Workbook workbook,HttpServletResponse response,String fileName){
        ServletOutputStream outputStream = null;
        try {
            outputStream = response.getOutputStream();
        } catch (IOException e) {
            logger.error("获取输出流失败，原因:{}",e);
        }

        try {
            workbook.write(outputStream);
            logger.info("导出" + fileName + "成功");
        } catch (IOException e) {
            logger.error("导出" + fileName + "失败");
        } finally {
            try {
                if(workbook != null){
                    workbook.close();
                }
                if(outputStream != null){
                    outputStream.flush();
                    outputStream.close();
                }
            } catch (IOException e) {
                e.printStackTrace();
            }
        }
    }

    /**
     * excel处理逻辑接口
     */
    public interface LogicData{
        /**
         * 处理逻辑方法
         * @param workbook     excel对象
         * @param params       参数列表
         */
        void logicData(Workbook workbook,Object... params);
    }

    /**
     * 默认处理逻辑类
     */
    @Component
    public class DefaultLogicData implements LogicData{

        /**
         * 默认处理逻辑
         * @param workbook     excel对象
         * @param params       参数列表，参数列表个数为1，参数类型:Map<String,Map<List<String>,List<Object[]>>>
         *                          泛型解释：外层key:sheet页名，内层key:列名，内层value:数据
         */
        @Override
        public void logicData(Workbook workbook, Object... params) {
            //校验参数
            checkParams(params);

            Map<String, Map<List<String>, List<Object[]>>> map = 
                    (Map<String, Map<List<String>, List<Object[]>>>) params[0];

            for (String sheetName : map.keySet()) {
                Sheet sheet = workbook.createSheet(sheetName);
                Map<List<String>, List<Object[]>> titlesAndData = map.get(sheetName);
                Row rowByTitle = sheet.createRow(0);
                List<List<String>> titleList = new ArrayList<>(titlesAndData.keySet());

                //添加标题
                List<String> titles = titleList.get(0);
                for (int i = 0; i < titles.size(); i++) {
                    Cell cell = rowByTitle.createCell(i);
                    cell.setCellValue(titles.get(i));
                }

                //添加数据
                List<Object[]> datas = titlesAndData.get(titles);
                for (int i = 1; i < datas.size(); i++) {
                    Row rowByData = sheet.createRow(i);
                    Object[] data = datas.get(i - 1);
                    for (int j = 0; j < data.length; j++) {
                        Cell cell = rowByData.createCell(j);
                        cell.setCellValue(data[j].toString());
                    }
                }
            }
        }

        /**
         * 校验参数
         * @param params    参数列表
         */
        private void checkParams(Object... params){
            //长度判断
            if(params.length != 1){
                throw new RuntimeException("Parameter length greater than 1");
            }

            //参数类型校验
            if(params[0] instanceof Map){
                Map<Object, Object> map = (Map<Object, Object>) params[0];
                map.forEach((key,value) -> {
                    if(!(key instanceof String)){
                        throw new RuntimeException("The key data type of Map should be String");
                    }
                    
                    if(!(value instanceof Map)){
                        throw new RuntimeException("The Value data type of Map should be Map");
                    }
                    Map<Object, Object> valueByMap = (Map<Object, Object>) value;
                    valueByMap.forEach((nKey,nValue) -> {
                        if(!(nKey instanceof List)){
                            throw new RuntimeException("The key data type of inner Map should be List");
                        }
                        List<Object> nKeyList = (List<Object>) nKey;
                        if(!(nKeyList.get(0) instanceof String)){
                            throw new RuntimeException("The key data type of inner Map should be List<String>");
                        }
                        if(!(nValue instanceof List)){
                            throw new RuntimeException("The Value data type of inner Map should be List");
                        }
                        List<Object> nValueList = (List<Object>) nValue;
                        if(!(nValueList.get(0) instanceof Object[])){
                            throw new RuntimeException("The Value data type of inner Map should be List<Object[]>");
                        }
                    });
                });
            }else{
                throw new RuntimeException("The 'params[0]' data type should be map");
            }

        }
    }
}