springboot使用jxls导出excel_（万能通用模板）--- SpringBoot导入、导出Excel文件_SpringBoot整合EasyExcel模板导出Excel

以你之姓@ 2023-10-01 10:09 28阅读 0赞

## springboot使用jxls导出excel ##

**实现思路：**  
首先在springBoot(或者SpringCloud)项目的默认templates目录放入提前定义好的Excel模板，然后在具体的导出接口业务代码里通过IO流加载到这个Excel模板文件，读取指定的工作薄(也就是excel左下角的Sheet)，接着给模板里的指定表头填充表头数据，接着读取数据库的相关数据用数据传输模型(DTO)封装数据，最后循坏填充excel的数据行(逐行逐列的填充数据)，最后把填充完数据的Excel文件流输出(下载)，即完成了数据库数据按照指定Excel模板导出Excel的完整过程。废话不多说，下面直接上代码。

1.引入依赖

<parent>
    		    <groupId>org.springframework.boot</groupId>
    		    <artifactId>spring-boot-starter-parent</artifactId>
    		    <version>2.0.6.RELEASE</version>
    		    <relativePath />
    	    </parent>
    		
            
    		<dependency>
    			<groupId>org.jxls</groupId>
    			<artifactId>jxls</artifactId>
    			<version>2.3.0</version>
    		</dependency>
     
    		<dependency>
    			<groupId>org.jxls</groupId>
    			<artifactId>jxls-poi</artifactId>
    			<version>1.0.9</version>
    		</dependency>
     
    		<dependency>
    			<groupId>net.sf.jxls</groupId>
    			<artifactId>jxls-core</artifactId>
    			<version>1.0.5</version>
    		</dependency>

2.excel工具类

import java.io.ByteArrayOutputStream;
    import java.io.IOException;
    import java.io.InputStream;
    import java.util.Map;
    import java.util.UUID;
     
    import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
    import org.apache.poi.ss.usermodel.Workbook;
    import org.springframework.http.HttpHeaders;
    import org.springframework.http.HttpStatus;
    import org.springframework.http.MediaType;
    import org.springframework.http.ResponseEntity;
     
    import net.sf.jxls.exception.ParsePropertyException;
    import net.sf.jxls.transformer.XLSTransformer;
     
    public class ExcelUtil {
        
     
    	/**
    	 * 下载excel
    	 *
    	 * @param sourcePath	模板路径
    	 * @param beanParams	excel内容
    	 * @return
    	 * @throws ParsePropertyException
    	 * @throws InvalidFormatException
    	 * @throws IOException
    	 */
    	public static ResponseEntity<byte[]> downLoadExcel(String sourcePath, Map<String, Object> beanParams)
    			throws ParsePropertyException, InvalidFormatException, IOException {
        
    	ByteArrayOutputStream os = new ByteArrayOutputStream();
    	//读取模板
    	InputStream is =ExcelUtil.class.getClassLoader().getResourceAsStream(sourcePath);
    	XLSTransformer transformer = new XLSTransformer();
    	//向模板中写入内容
    	Workbook workbook = transformer.transformXLS(is, beanParams);
    	//写入成功后转化为输出流
    	workbook.write(os);
    	//配置Response信息
    	HttpHeaders headers = new HttpHeaders();
    	String downloadFileName = UUID.randomUUID().toString() + ".xlsx";
    	//防止中文名乱码
    	downloadFileName = new String(downloadFileName.getBytes("UTF-8"), "ISO-8859-1");
    	headers.setContentDispositionFormData("attachment", downloadFileName);
    	headers.setContentType(MediaType.APPLICATION_OCTET_STREAM);
    	//返回
    	return new ResponseEntity<byte[]>(os.toByteArray(), headers, HttpStatus.CREATED);
    }

3.controller调用

@RequestMapping("/export")
    	//返回ResponseEntity<byte[]>使浏览器下载
    	public ResponseEntity<byte[]> exportExcel(HttpServletRequest request, HttpServletResponse response) throws Exception {
        
    		//查询参数
    		Map<String, Object> params = new HashMap<String, Object>();
    		//结果集
    		List<Aip_std> list = stdService.selectAllForExportExcel(params);
    		Map<String, Object> beanParams = new HashMap<String, Object>();
    		beanParams.put("list", list);
    		//下载表格
    		return ExcelUtil.downLoadExcel("static/excel/aaa.xlsx",beanParams);
    	}

4.模板存放位置

![img][]

5.模板

![img][img 1]

6.导出的excel

### ![img][img 2] ###

--------------------

## （万能通用模板）— SpringBoot导入、导出Excel文件 ##

#### 先把项目的demo发一下，看完文章可以看一下，[demo][] ####

> 前言：最近做项目过程中使用到了一个权限管理框架：[若依][Link 1]，使用过程中发现他的文件导入和导出功能非常的实用，在这里特此做一个小demo跟大家分享一下。

> 导出：将从数据库中查出的List列表，以参数的形式传入模板中，即可返回Excel文件。

@ApiOperation(value = "导出",produces="application/octet-stream")
        @GetMapping("/exportExcel")
        public void exportExcel(HttpServletResponse response) throws IOException {
        
        // 查询数据列表
            List<UserData> userData = dataService.selectUserData(); 
    	// 将list导入模板中
            ExcelUtil<UserData> util = new ExcelUtil<UserData>(UserData.class);
        //返回Excel文件
            util.exportExcel(response,userData , "角色数据");
        }

> 导入：将Excel文件上传，模板代码会将此Excel文件转换成List列表的形式返回，我们可以将返回的list列表插入到数据库中。

@ApiOperation("导入")
        @PostMapping("/importExcel")
        public int importExcel(MultipartFile file) throws Exception {
        
            ExcelUtil<UserData> util = new ExcelUtil<>(UserData.class);
            // 将文件以流的形式传入到模板代码中，返回List列表
            List<UserData> userDataList = util.importExcel(file.getInputStream());
            // 将list列表插入到数据库中
            int insertFlag = dataService.importUserData(userDataList);
            if (insertFlag == 0){
        
                throw new RuntimeException("导入失败");
            }
            return insertFlag;
        }

#### 一、效果图 ####

1.导出Excel

> ![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MjU1MTM2OQ_size_16_color_FFFFFF_t_70_pic_center]  
> 打开Excel效果：  
> ![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MjU1MTM2OQ_size_16_color_FFFFFF_t_70_pic_center 1]

2.导入Excel

> 1）创建Excel  
> ![在这里插入图片描述][20201125164532977.png_pic_center]  
> 2）使用swagger导入  
> ![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MjU1MTM2OQ_size_16_color_FFFFFF_t_70_pic_center 2]  
> 3）查看数据库数据  
> ![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MjU1MTM2OQ_size_16_color_FFFFFF_t_70_pic_center 3]  
> 导入成功

#### 二、实现 ####

1.数据库

CREATE TABLE `data` (
      `name` varchar(33) DEFAULT NULL,
      `project` varchar(33) DEFAULT NULL,
      `score` int(3) DEFAULT NULL,
      `id` int(10) NOT NULL AUTO_INCREMENT,
      PRIMARY KEY (`id`),
      KEY `indx_data_funshu` (`score`)
    ) ENGINE=InnoDB AUTO_INCREMENT=24 DEFAULT CHARSET=utf8;

![在这里插入图片描述][20201125162149674.png_pic_center]  
2. 代码（东西较多，这里只粘贴核心代码，详情请查看[详细代码][demo]）

> 1）实体类：在需要导出的字段上加入@Excel注解，name为导出后Excel的表头

@Data
    @Table(name = "data")
    public class UserData {
        
        @Id
        @Excel(name = "序号")
        Integer id;
        @Excel(name = "名字")
        String name;
        @Excel(name = "课程")
        String project;
        @Excel(name = "分数")
        Integer score;
    }

> 2）controller文件

package com.example.commonutils.controller;
    
    
    import com.example.commonutils.common.ExcelUtil;
    import com.example.commonutils.domain.UserData;
    import com.example.commonutils.service.DataService;
    import io.swagger.annotations.Api;
    import io.swagger.annotations.ApiOperation;
    import org.springframework.beans.factory.annotation.Autowired;
    import org.springframework.web.bind.annotation.GetMapping;
    import org.springframework.web.bind.annotation.PostMapping;
    import org.springframework.web.bind.annotation.RestController;
    import org.springframework.web.multipart.MultipartFile;
    
    import javax.servlet.http.HttpServletResponse;
    import java.io.IOException;
    import java.util.List;
    
    
    /**
     * @author tianzhuang
     */
    @Api(tags = "Excel的导入导出")
    @RestController
    public class ExportExcelController {
        
    
        @Autowired
        private DataService dataService;
    
        /**
         *   在浏览器地址栏输入此接口地址，即可导出Excel，用swagger导出是乱码，暂未解决
         * @param response
         * @throws IOException
         */
        @ApiOperation(value = "导出",produces="application/octet-stream")
        @GetMapping("/exportExcel")
        public void exportExcel(HttpServletResponse response) throws IOException {
        
            List<UserData> userData = dataService.selectUserData();
            ExcelUtil<UserData> util = new ExcelUtil<UserData>(UserData.class);
            util.exportExcel(response,userData , "角色数据");
        }
    
        /**
         *
         * @param file
         * @return
         * @throws Exception
         */
        @ApiOperation("导入")
        @PostMapping("/importExcel")
        public int importExcel(MultipartFile file) throws Exception {
        
            ExcelUtil<UserData> util = new ExcelUtil<>(UserData.class);
            List<UserData> userDataList = util.importExcel(file.getInputStream());
            int insertFlag = dataService.importUserData(userDataList);
            if (insertFlag == 0){
        
                throw new RuntimeException("导入失败");
            }
            return insertFlag;
        }
    }

> ExcelUtil代码

package com.example.commonutils.common;
    
    import com.example.commonutils.common.Excel.ColumnType;
    import com.example.commonutils.common.Excel.Type;
    import org.apache.poi.hssf.usermodel.HSSFDateUtil;
    import org.apache.poi.ss.usermodel.*;
    import org.apache.poi.ss.util.CellRangeAddressList;
    import org.apache.poi.xssf.streaming.SXSSFWorkbook;
    import org.apache.poi.xssf.usermodel.XSSFDataValidation;
    import org.slf4j.Logger;
    import org.slf4j.LoggerFactory;
    
    import javax.servlet.http.HttpServletResponse;
    import java.io.IOException;
    import java.io.InputStream;
    import java.io.OutputStream;
    import java.lang.reflect.Field;
    import java.math.BigDecimal;
    import java.text.DecimalFormat;
    import java.util.*;
    import java.util.stream.Collectors;
    
    
    /**
     * Excel相关处理
     * 
     * @author ruoyi
     */
    public class ExcelUtil<T>
    {
        
        private static final Logger log = LoggerFactory.getLogger(ExcelUtil.class);
    
        /**
         * Excel sheet最大行数，默认65536
         */
        public static final int sheetSize = 65536;
    
        /**
         * 工作表名称
         */
        private String sheetName;
    
        /**
         * 导出类型（EXPORT:导出数据；IMPORT：导入模板）
         */
        private Type type;
    
        /**
         * 工作薄对象
         */
        private Workbook wb;
    
        /**
         * 工作表对象
         */
        private Sheet sheet;
    
        /**
         * 样式列表
         */
        private Map<String, CellStyle> styles;
    
        /**
         * 导入导出数据列表
         */
        private List<T> list;
    
        /**
         * 注解列表
         */
        private List<Object[]> fields;
    
        /**
         * 统计列表
         */
        private Map<Integer, Double> statistics = new HashMap<Integer, Double>();
    
        /**
         * 数字格式
         */
        private static final DecimalFormat DOUBLE_FORMAT = new DecimalFormat("######0.00");
    
        /**
         * 实体对象
         */
        public Class<T> clazz;
    
        public ExcelUtil(Class<T> clazz)
        {
        
            this.clazz = clazz;
        }
    
        public void init(List<T> list, String sheetName, Type type)
        {
        
            if (list == null)
            {
        
                list = new ArrayList<T>();
            }
            this.list = list;
            this.sheetName = sheetName;
            this.type = type;
            createExcelField();
            createWorkbook();
        }
    
        /**
         * 对excel表单默认第一个索引名转换成list
         * 
         * @param is 输入流
         * @return 转换后集合
         */
        public List<T> importExcel(InputStream is) throws Exception
        {
        
            return importExcel(StringUtils.EMPTY, is);
        }
    
        /**
         * 对excel表单指定表格索引名转换成list
         * 
         * @param sheetName 表格索引名
         * @param is 输入流
         * @return 转换后集合
         */
        public List<T> importExcel(String sheetName, InputStream is) throws Exception
        {
        
            this.type = Type.IMPORT;
            this.wb = WorkbookFactory.create(is);
            List<T> list = new ArrayList<T>();
            Sheet sheet = null;
            if (StringUtils.isNotEmpty(sheetName))
            {
        
                // 如果指定sheet名,则取指定sheet中的内容.
                sheet = wb.getSheet(sheetName);
            }
            else
            {
        
                // 如果传入的sheet名不存在则默认指向第1个sheet.
                sheet = wb.getSheetAt(0);
            }
    
            if (sheet == null)
            {
        
                throw new IOException("文件sheet不存在");
            }
    
            int rows = sheet.getPhysicalNumberOfRows();
    
            if (rows > 0)
            {
        
                // 定义一个map用于存放excel列的序号和field.
                Map<String, Integer> cellMap = new HashMap<String, Integer>();
                // 获取表头
                Row heard = sheet.getRow(0);
                for (int i = 0; i < heard.getPhysicalNumberOfCells(); i++)
                {
        
                    Cell cell = heard.getCell(i);
                    if (StringUtils.isNotNull(cell))
                    {
        
                        String value = this.getCellValue(heard, i).toString();
                        cellMap.put(value, i);
                    }
                    else
                    {
        
                        cellMap.put(null, i);
                    }
                }
                // 有数据时才处理 得到类的所有field.
                Field[] allFields = clazz.getDeclaredFields();
                // 定义一个map用于存放列的序号和field.
                Map<Integer, Field> fieldsMap = new HashMap<Integer, Field>();
                for (int col = 0; col < allFields.length; col++)
                {
        
                    Field field = allFields[col];
                    Excel attr = field.getAnnotation(Excel.class);
                    if (attr != null && (attr.type() == Type.ALL || attr.type() == type))
                    {
        
                        // 设置类的私有字段属性可访问.
                        field.setAccessible(true);
                        Integer column = cellMap.get(attr.name());
                        if (column != null)
                        {
        
                            fieldsMap.put(column, field);
                        }
                    }
                }
                for (int i = 1; i < rows; i++)
                {
        
                    // 从第2行开始取数据,默认第一行是表头.
                    Row row = sheet.getRow(i);
                    T entity = null;
                    for (Map.Entry<Integer, Field> entry : fieldsMap.entrySet())
                    {
        
                        Object val = this.getCellValue(row, entry.getKey());
    
                        // 如果不存在实例则新建.
                        entity = (entity == null ? clazz.newInstance() : entity);
                        // 从map中得到对应列的field.
                        Field field = fieldsMap.get(entry.getKey());
                        // 取得类型,并根据对象类型设置值.
                        Class<?> fieldType = field.getType();
                        if (String.class == fieldType)
                        {
        
                            String s = Convert.toStr(val);
                            if (StringUtils.endsWith(s, ".0"))
                            {
        
                                val = StringUtils.substringBefore(s, ".0");
                            }
                            else
                            {
        
                                val = Convert.toStr(val);
                            }
                        }
                        else if ((Integer.TYPE == fieldType || Integer.class == fieldType) && StringUtils.isNumeric(Convert.toStr(val)))
                        {
        
                            val = Convert.toInt(val);
                        }
                        else if (Long.TYPE == fieldType || Long.class == fieldType)
                        {
        
                            val = Convert.toLong(val);
                        }
                        else if (Double.TYPE == fieldType || Double.class == fieldType)
                        {
        
                            val = Convert.toDouble(val);
                        }
                        else if (Float.TYPE == fieldType || Float.class == fieldType)
                        {
        
                            val = Convert.toFloat(val);
                        }
                        else if (BigDecimal.class == fieldType)
                        {
        
                            val = Convert.toBigDecimal(val);
                        }
                        else if (Date.class == fieldType)
                        {
        
                            if (val instanceof String)
                            {
        
                                val = DateUtils.parseDate(val);
                            }
                            else if (val instanceof Double)
                            {
        
                                val = DateUtil.getJavaDate((Double) val);
                            }
                        }
                        if (StringUtils.isNotNull(fieldType))
                        {
        
                            Excel attr = field.getAnnotation(Excel.class);
                            String propertyName = field.getName();
                            if (StringUtils.isNotEmpty(attr.targetAttr()))
                            {
        
                                propertyName = field.getName() + "." + attr.targetAttr();
                            }
                            else if (StringUtils.isNotEmpty(attr.readConverterExp()))
                            {
        
                                val = reverseByExp(Convert.toStr(val), attr.readConverterExp(), attr.separator());
                            }
                            ReflectUtils.invokeSetter(entity, propertyName, val);
                        }
                    }
                    list.add(entity);
                }
            }
            return list;
        }
    
        /**
         * 对list数据源将其里面的数据导入到excel表单
         * 
         * @param response 返回数据
         * @param list 导出数据集合
         * @param sheetName 工作表的名称
         * @return 结果
         * @throws IOException
         */
        public void exportExcel(HttpServletResponse response, List<T> list, String sheetName) throws IOException
        {
        
            response.setContentType("application/vnd.ms-excel");
            response.setCharacterEncoding("utf-8");
            this.init(list, sheetName, Type.EXPORT);
            exportExcel(response.getOutputStream());
        }
    
        /**
         * 对list数据源将其里面的数据导入到excel表单
         * 
         * @param sheetName 工作表的名称
         * @return 结果
         */
        public void importTemplateExcel(HttpServletResponse response, String sheetName) throws IOException
        {
        
            response.setContentType("application/vnd.ms-excel");
            response.setCharacterEncoding("utf-8");
            this.init(null, sheetName, Type.IMPORT);
            exportExcel(response.getOutputStream());
        }
    
        /**
         * 对list数据源将其里面的数据导入到excel表单
         * 
         * @return 结果
         */
        public void exportExcel(OutputStream outputStream)
        {
        
            try
            {
        
                // 取出一共有多少个sheet.
                double sheetNo = Math.ceil(list.size() / sheetSize);
                for (int index = 0; index <= sheetNo; index++)
                {
        
                    createSheet(sheetNo, index);
    
                    // 产生一行
                    Row row = sheet.createRow(0);
                    int column = 0;
                    // 写入各个字段的列头名称
                    for (Object[] os : fields)
                    {
        
                        Excel excel = (Excel) os[1];
                        this.createCell(excel, row, column++);
                    }
                    if (Type.EXPORT.equals(type))
                    {
        
                        fillExcelData(index, row);
                        addStatisticsRow();
                    }
                }
                wb.write(outputStream);
            }
            catch (Exception e)
            {
        
                log.error("导出Excel异常{}", e.getMessage());
            }
            finally
            {
        
                if (wb != null)
                {
        
                    try
                    {
        
                        wb.close();
                    }
                    catch (IOException e1)
                    {
        
                        e1.printStackTrace();
                    }
                }
                if (outputStream != null)
                {
        
                    try
                    {
        
                        outputStream.close();
                    }
                    catch (IOException e1)
                    {
        
                        e1.printStackTrace();
                    }
                }
            }
        }
    
        /**
         * 填充excel数据
         * 
         * @param index 序号
         * @param row 单元格行
         */
        public void fillExcelData(int index, Row row)
        {
        
            int startNo = index * sheetSize;
            int endNo = Math.min(startNo + sheetSize, list.size());
            for (int i = startNo; i < endNo; i++)
            {
        
                row = sheet.createRow(i + 1 - startNo);
                // 得到导出对象.
                T vo = (T) list.get(i);
                int column = 0;
                for (Object[] os : fields)
                {
        
                    Field field = (Field) os[0];
                    Excel excel = (Excel) os[1];
                    // 设置实体类私有属性可访问
                    field.setAccessible(true);
                    this.addCell(excel, row, vo, field, column++);
                }
            }
        }
    
        /**
         * 创建表格样式
         * 
         * @param wb 工作薄对象
         * @return 样式列表
         */
        private Map<String, CellStyle> createStyles(Workbook wb)
        {
        
            // 写入各条记录,每条记录对应excel表中的一行
            Map<String, CellStyle> styles = new HashMap<String, CellStyle>();
            CellStyle style = wb.createCellStyle();
            style.setAlignment(HorizontalAlignment.CENTER);
            style.setVerticalAlignment(VerticalAlignment.CENTER);
            style.setBorderRight(BorderStyle.THIN);
            style.setRightBorderColor(IndexedColors.GREY_50_PERCENT.getIndex());
            style.setBorderLeft(BorderStyle.THIN);
            style.setLeftBorderColor(IndexedColors.GREY_50_PERCENT.getIndex());
            style.setBorderTop(BorderStyle.THIN);
            style.setTopBorderColor(IndexedColors.GREY_50_PERCENT.getIndex());
            style.setBorderBottom(BorderStyle.THIN);
            style.setBottomBorderColor(IndexedColors.GREY_50_PERCENT.getIndex());
            Font dataFont = wb.createFont();
            dataFont.setFontName("Arial");
            dataFont.setFontHeightInPoints((short) 10);
            style.setFont(dataFont);
            styles.put("data", style);
    
            style = wb.createCellStyle();
            style.cloneStyleFrom(styles.get("data"));
            style.setAlignment(HorizontalAlignment.CENTER);
            style.setVerticalAlignment(VerticalAlignment.CENTER);
            style.setFillForegroundColor(IndexedColors.GREY_50_PERCENT.getIndex());
            style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
            Font headerFont = wb.createFont();
            headerFont.setFontName("Arial");
            headerFont.setFontHeightInPoints((short) 10);
            headerFont.setBold(true);
            headerFont.setColor(IndexedColors.WHITE.getIndex());
            style.setFont(headerFont);
            styles.put("header", style);
            
            style = wb.createCellStyle();
            style.setAlignment(HorizontalAlignment.CENTER);
            style.setVerticalAlignment(VerticalAlignment.CENTER);
            Font totalFont = wb.createFont();
            totalFont.setFontName("Arial");
            totalFont.setFontHeightInPoints((short) 10);
            style.setFont(totalFont);
            styles.put("total", style);
    
            return styles;
        }
    
        /**
         * 创建单元格
         */
        public Cell createCell(Excel attr, Row row, int column)
        {
        
            // 创建列
            Cell cell = row.createCell(column);
            // 写入列信息
            cell.setCellValue(attr.name());
            setDataValidation(attr, row, column);
            cell.setCellStyle(styles.get("header"));
            return cell;
        }
    
        /**
         * 设置单元格信息
         * 
         * @param value 单元格值
         * @param attr 注解相关
         * @param cell 单元格信息
         */
        public void setCellVo(Object value, Excel attr, Cell cell)
        {
        
            if (ColumnType.STRING == attr.cellType())
            {
        
                cell.setCellType(CellType.STRING);
                cell.setCellValue(StringUtils.isNull(value) ? attr.defaultValue() : value + attr.suffix());
            }
            else if (ColumnType.NUMERIC == attr.cellType())
            {
        
                cell.setCellType(CellType.NUMERIC);
                cell.setCellValue(StringUtils.contains(Convert.toStr(value), ".") ? Convert.toDouble(value) : Convert.toInt(value));
            }
        }
    
        /**
         * 创建表格样式
         */
        public void setDataValidation(Excel attr, Row row, int column)
        {
        
            if (attr.name().indexOf("注：") >= 0)
            {
        
                sheet.setColumnWidth(column, 6000);
            }
            else
            {
        
                // 设置列宽
                sheet.setColumnWidth(column, (int) ((attr.width() + 0.72) * 256));
                row.setHeight((short) (attr.height() * 20));
            }
            // 如果设置了提示信息则鼠标放上去提示.
            if (StringUtils.isNotEmpty(attr.prompt()))
            {
        
                // 这里默认设了2-101列提示.
                setXSSFPrompt(sheet, "", attr.prompt(), 1, 100, column, column);
            }
            // 如果设置了combo属性则本列只能选择不能输入
            if (attr.combo().length > 0)
            {
        
                // 这里默认设了2-101列只能选择不能输入.
                setXSSFValidation(sheet, attr.combo(), 1, 100, column, column);
            }
        }
    
        /**
         * 添加单元格
         */
        public Cell addCell(Excel attr, Row row, T vo, Field field, int column)
        {
        
            Cell cell = null;
            try
            {
        
                // 设置行高
                row.setHeight((short) (attr.height() * 20));
                // 根据Excel中设置情况决定是否导出,有些情况需要保持为空,希望用户填写这一列.
                if (attr.isExport())
                {
        
                    // 创建cell
                    cell = row.createCell(column);
                    cell.setCellStyle(styles.get("data"));
    
                    // 用于读取对象中的属性
                    Object value = getTargetValue(vo, field, attr);
                    String dateFormat = attr.dateFormat();
                    String readConverterExp = attr.readConverterExp();
                    String separator = attr.separator();
                    if (StringUtils.isNotEmpty(dateFormat) && StringUtils.isNotNull(value))
                    {
        
                        cell.setCellValue(DateUtils.parseDateToStr(dateFormat, (Date) value));
                    }
                    else if (StringUtils.isNotEmpty(readConverterExp) && StringUtils.isNotNull(value))
                    {
        
                        cell.setCellValue(convertByExp(Convert.toStr(value), readConverterExp, separator));
                    }
                    else if (value instanceof BigDecimal && -1 != attr.scale())
                    {
        
                        cell.setCellValue((((BigDecimal) value).setScale(attr.scale(), attr.roundingMode())).toString());
                    }
                    else
                    {
        
                        // 设置列类型
                        setCellVo(value, attr, cell);
                    }
                    addStatisticsData(column, Convert.toStr(value), attr);
                }
            }
            catch (Exception e)
            {
        
                log.error("导出Excel失败{}", e);
            }
            return cell;
        }
    
        /**
         * 设置 POI XSSFSheet 单元格提示
         * 
         * @param sheet 表单
         * @param promptTitle 提示标题
         * @param promptContent 提示内容
         * @param firstRow 开始行
         * @param endRow 结束行
         * @param firstCol 开始列
         * @param endCol 结束列
         */
        public void setXSSFPrompt(Sheet sheet, String promptTitle, String promptContent, int firstRow, int endRow,
                int firstCol, int endCol)
        {
        
            DataValidationHelper helper = sheet.getDataValidationHelper();
            DataValidationConstraint constraint = helper.createCustomConstraint("DD1");
            CellRangeAddressList regions = new CellRangeAddressList(firstRow, endRow, firstCol, endCol);
            DataValidation dataValidation = helper.createValidation(constraint, regions);
            dataValidation.createPromptBox(promptTitle, promptContent);
            dataValidation.setShowPromptBox(true);
            sheet.addValidationData(dataValidation);
        }
    
        /**
         * 设置某些列的值只能输入预制的数据,显示下拉框.
         * 
         * @param sheet 要设置的sheet.
         * @param textlist 下拉框显示的内容
         * @param firstRow 开始行
         * @param endRow 结束行
         * @param firstCol 开始列
         * @param endCol 结束列
         * @return 设置好的sheet.
         */
        public void setXSSFValidation(Sheet sheet, String[] textlist, int firstRow, int endRow, int firstCol, int endCol)
        {
        
            DataValidationHelper helper = sheet.getDataValidationHelper();
            // 加载下拉列表内容
            DataValidationConstraint constraint = helper.createExplicitListConstraint(textlist);
            // 设置数据有效性加载在哪个单元格上,四个参数分别是：起始行、终止行、起始列、终止列
            CellRangeAddressList regions = new CellRangeAddressList(firstRow, endRow, firstCol, endCol);
            // 数据有效性对象
            DataValidation dataValidation = helper.createValidation(constraint, regions);
            // 处理Excel兼容性问题
            if (dataValidation instanceof XSSFDataValidation)
            {
        
                dataValidation.setSuppressDropDownArrow(true);
                dataValidation.setShowErrorBox(true);
            }
            else
            {
        
                dataValidation.setSuppressDropDownArrow(false);
            }
    
            sheet.addValidationData(dataValidation);
        }
    
        /**
         * 解析导出值 0=男,1=女,2=未知
         * 
         * @param propertyValue 参数值
         * @param converterExp 翻译注解
         * @param separator 分隔符
         * @return 解析后值
         */
        public static String convertByExp(String propertyValue, String converterExp, String separator)
        {
        
            StringBuilder propertyString = new StringBuilder();
            String[] convertSource = converterExp.split(",");
            for (String item : convertSource)
            {
        
                String[] itemArray = item.split("=");
                if (StringUtils.containsAny(separator, propertyValue))
                {
        
                    for (String value : propertyValue.split(separator))
                    {
        
                        if (itemArray[0].equals(value))
                        {
        
                            propertyString.append(itemArray[1] + separator);
                            break;
                        }
                    }
                }
                else
                {
        
                    if (itemArray[0].equals(propertyValue))
                    {
        
                        return itemArray[1];
                    }
                }
            }
            return StringUtils.stripEnd(propertyString.toString(), separator);
        }
    
        /**
         * 反向解析值 男=0,女=1,未知=2
         * 
         * @param propertyValue 参数值
         * @param converterExp 翻译注解
         * @param separator 分隔符
         * @return 解析后值
         */
        public static String reverseByExp(String propertyValue, String converterExp, String separator)
        {
        
            StringBuilder propertyString = new StringBuilder();
            String[] convertSource = converterExp.split(",");
            for (String item : convertSource)
            {
        
                String[] itemArray = item.split("=");
                if (StringUtils.containsAny(separator, propertyValue))
                {
        
                    for (String value : propertyValue.split(separator))
                    {
        
                        if (itemArray[1].equals(value))
                        {
        
                            propertyString.append(itemArray[0] + separator);
                            break;
                        }
                    }
                }
                else
                {
        
                    if (itemArray[1].equals(propertyValue))
                    {
        
                        return itemArray[0];
                    }
                }
            }
            return StringUtils.stripEnd(propertyString.toString(), separator);
        }
    
        /**
         * 合计统计信息
         */
        private void addStatisticsData(Integer index, String text, Excel entity)
        {
        
            if (entity != null && entity.isStatistics())
            {
        
                Double temp = 0D;
                if (!statistics.containsKey(index))
                {
        
                    statistics.put(index, temp);
                }
                try
                {
        
                    temp = Double.valueOf(text);
                }
                catch (NumberFormatException e)
                {
        
                }
                statistics.put(index, statistics.get(index) + temp);
            }
        }
    
        /**
         * 创建统计行
         */
        public void addStatisticsRow()
        {
        
            if (statistics.size() > 0)
            {
        
                Cell cell = null;
                Row row = sheet.createRow(sheet.getLastRowNum() + 1);
                Set<Integer> keys = statistics.keySet();
                cell = row.createCell(0);
                cell.setCellStyle(styles.get("total"));
                cell.setCellValue("合计");
    
                for (Integer key : keys)
                {
        
                    cell = row.createCell(key);
                    cell.setCellStyle(styles.get("total"));
                    cell.setCellValue(DOUBLE_FORMAT.format(statistics.get(key)));
                }
                statistics.clear();
            }
        }
    
        /**
         * 获取bean中的属性值
         * 
         * @param vo 实体对象
         * @param field 字段
         * @param excel 注解
         * @return 最终的属性值
         * @throws Exception
         */
        private Object getTargetValue(T vo, Field field, Excel excel) throws Exception
        {
        
            Object o = field.get(vo);
            if (StringUtils.isNotEmpty(excel.targetAttr()))
            {
        
                String target = excel.targetAttr();
                if (target.indexOf(".") > -1)
                {
        
                    String[] targets = target.split("[.]");
                    for (String name : targets)
                    {
        
                        o = getValue(o, name);
                    }
                }
                else
                {
        
                    o = getValue(o, target);
                }
            }
            return o;
        }
    
        /**
         * 以类的属性的get方法方法形式获取值
         * 
         * @param o
         * @param name
         * @return value
         * @throws Exception
         */
        private Object getValue(Object o, String name) throws Exception
        {
        
            if (StringUtils.isNotEmpty(name))
            {
        
                Class<?> clazz = o.getClass();
                Field field = clazz.getDeclaredField(name);
                field.setAccessible(true);
                o = field.get(o);
            }
            return o;
        }
    
        /**
         * 得到所有定义字段
         */
        private void createExcelField()
        {
        
            this.fields = new ArrayList<Object[]>();
            List<Field> tempFields = new ArrayList<>();
            tempFields.addAll(Arrays.asList(clazz.getSuperclass().getDeclaredFields()));
            tempFields.addAll(Arrays.asList(clazz.getDeclaredFields()));
            for (Field field : tempFields)
            {
        
                // 单注解
                if (field.isAnnotationPresent(Excel.class))
                {
        
                    putToField(field, field.getAnnotation(Excel.class));
                }
    
                // 多注解
                if (field.isAnnotationPresent(Excels.class))
                {
        
                    Excels attrs = field.getAnnotation(Excels.class);
                    Excel[] excels = attrs.value();
                    for (Excel excel : excels)
                    {
        
                        putToField(field, excel);
                    }
                }
            }
            this.fields = this.fields.stream().sorted(Comparator.comparing(objects -> ((Excel) objects[1]).sort())).collect(Collectors.toList());
        }
    
        /**
         * 放到字段集合中
         */
        private void putToField(Field field, Excel attr)
        {
        
            if (attr != null && (attr.type() == Type.ALL || attr.type() == type))
            {
        
                this.fields.add(new Object[] {
         field, attr });
            }
        }
    
        /**
         * 创建一个工作簿
         */
        public void createWorkbook()
        {
        
            this.wb = new SXSSFWorkbook(500);
        }
    
        /**
         * 创建工作表
         * 
         * @param sheetNo sheet数量
         * @param index 序号
         */
        public void createSheet(double sheetNo, int index)
        {
        
            this.sheet = wb.createSheet();
            this.styles = createStyles(wb);
            // 设置工作表的名称.
            if (sheetNo == 0)
            {
        
                wb.setSheetName(index, sheetName);
            }
            else
            {
        
                wb.setSheetName(index, sheetName + index);
            }
        }
    
        /**
         * 获取单元格值
         * 
         * @param row 获取的行
         * @param column 获取单元格列号
         * @return 单元格值
         */
        public Object getCellValue(Row row, int column)
        {
        
            if (row == null)
            {
        
                return row;
            }
            Object val = "";
            try
            {
        
                Cell cell = row.getCell(column);
                if (StringUtils.isNotNull(cell))
                {
        
                    if (cell.getCellTypeEnum() == CellType.NUMERIC || cell.getCellTypeEnum() == CellType.FORMULA)
                    {
        
                        val = cell.getNumericCellValue();
                        if (HSSFDateUtil.isCellDateFormatted(cell))
                        {
        
                            val = DateUtil.getJavaDate((Double) val); // POI Excel 日期格式转换
                        }
                        else
                        {
        
                            if ((Double) val % 1 > 0)
                            {
        
                                val = new BigDecimal(val.toString());
                            }
                            else
                            {
        
                                val = new DecimalFormat("0").format(val);
                            }
                        }
                    }
                    else if (cell.getCellTypeEnum() == CellType.STRING)
                    {
        
                        val = cell.getStringCellValue();
                    }
                    else if (cell.getCellTypeEnum() == CellType.BOOLEAN)
                    {
        
                        val = cell.getBooleanCellValue();
                    }
                    else if (cell.getCellTypeEnum() == CellType.ERROR)
                    {
        
                        val = cell.getErrorCellValue();
                    }
    
                }
            }
            catch (Exception e)
            {
        
                return val;
            }
            return val;
        }
    }

> 3）Excel注解代码

package com.example.commonutils.common;
    
    import java.lang.annotation.ElementType;
    import java.lang.annotation.Retention;
    import java.lang.annotation.RetentionPolicy;
    import java.lang.annotation.Target;
    import java.math.BigDecimal;
    
    /**
     * 自定义导出Excel数据注解
     * 
     * @author ruoyi
     */
    @Retention(RetentionPolicy.RUNTIME)
    @Target(ElementType.FIELD)
    public @interface Excel
    {
        
        /**
         * 导出时在excel中排序
         */
        public int sort() default Integer.MAX_VALUE;
    
        /**
         * 导出到Excel中的名字.
         */
        public String name() default "";
    
        /**
         * 日期格式, 如: yyyy-MM-dd
         */
        public String dateFormat() default "";
    
        /**
         * 读取内容转表达式 (如: 0=男,1=女,2=未知)
         */
        public String readConverterExp() default "";
    
        /**
         * 分隔符，读取字符串组内容
         */
        public String separator() default ",";
    
        /**
         * BigDecimal 精度 默认:-1(默认不开启BigDecimal格式化)
         */
        public int scale() default -1;
    
        /**
         * BigDecimal 舍入规则 默认:BigDecimal.ROUND_HALF_EVEN
         */
        public int roundingMode() default BigDecimal.ROUND_HALF_EVEN;
    
        /**
         * 导出类型（0数字 1字符串）
         */
        public ColumnType cellType() default ColumnType.STRING;
    
        /**
         * 导出时在excel中每个列的高度 单位为字符
         */
        public double height() default 14;
    
        /**
         * 导出时在excel中每个列的宽 单位为字符
         */
        public double width() default 16;
    
        /**
         * 文字后缀,如% 90 变成90%
         */
        public String suffix() default "";
    
        /**
         * 当值为空时,字段的默认值
         */
        public String defaultValue() default "";
    
        /**
         * 提示信息
         */
        public String prompt() default "";
    
        /**
         * 设置只能选择不能输入的列内容.
         */
        public String[] combo() default {
        };
    
        /**
         * 是否导出数据,应对需求:有时我们需要导出一份模板,这是标题需要但内容需要用户手工填写.
         */
        public boolean isExport() default true;
    
        /**
         * 另一个类中的属性名称,支持多级获取,以小数点隔开
         */
        public String targetAttr() default "";
    
        /**
         * 是否自动统计数据,在最后追加一行统计数据总和
         */
        public boolean isStatistics() default false;
    
        /**
         * 字段类型（0：导出导入；1：仅导出；2：仅导入）
         */
        Type type() default Type.ALL;
    
        public enum Type
        {
        
            ALL(0), EXPORT(1), IMPORT(2);
            private final int value;
    
            Type(int value)
            {
        
                this.value = value;
            }
    
            public int value()
            {
        
                return this.value;
            }
        }
    
        public enum ColumnType
        {
        
            NUMERIC(0), STRING(1);
            private final int value;
    
            ColumnType(int value)
            {
        
                this.value = value;
            }
    
            public int value()
            {
        
                return this.value;
            }
        }
    }

> 代码量较多，本文只放了部分代码，可以进入[demo][demo 1]进行详细查看

--------------------

## SpringBoot整合EasyExcel模板导出Excel ##

## 创建`SpringBoot`项目 ##

## 导入`EasyExcel.jar` ##

<dependency>
    	<groupId>com.alibaba</groupId>
        <artifactId>easyexcel</artifactId>
        <version>2.1.6</version>
    </dependency>

## 创建Excel模板 ##

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQyNDI2OTM3_size_16_color_FFFFFF_t_70_pic_center]

## `实体类` ##

public class FillData {
        
    	private String name;
    	@NumberFormat("##.0")
    	private Double chinese;
    	@NumberFormat("##.0")
    	private Double math;
    	@NumberFormat("##.0")
    	private Double english;
    	@NumberFormat("##.0")
    	private Double number;
    	//getter setter
    }

## `Service实现` ##

@Service
    public class ExcelExportServiceImpl implements ExcelExportService{
        
    	//在springboot文件(application.properties)中配置模板所在的位置，例如
    	//excel.template=E:/SpringToolSuiteForEclipseWorkSpace/PrivateApplication/EasyExcel/src/main/resources/1.xlsx
    	@Value("${excel.template}")
    	private String templateFileName;
    	
    	@Override
    	public void excelExport(HttpServletResponse response) throws IOException {
        
    		// 模板注意 用{} 来表示你要用的变量 如果本来就有"{","}" 特殊字符 用"\{","\}"代替
    		System.out.println(templateFileName);
    		// 方案1 根据对象填充
    		String fileName = "simpleFill" + System.currentTimeMillis() + ".xlsx";
    		// 这里 会填充到第一个sheet， 然后文件流会自动关闭
    		List<FillData> l = new ArrayList<>();
    		for(Integer i = 0; i < 50; i++){
        
    			FillData fillData = new FillData();
    			fillData.setName("张三" + i+"号");
    			fillData.setChinese(120.0+i);
    			fillData.setEnglish(141.0+i);
    			fillData.setMath(119.0+i);
    			fillData.setNumber(fillData.getChinese()+fillData.getEnglish()+fillData.getMath());
    			l.add(fillData);
    		}
    		response.setContentType("application/vnd.ms-excel");
    		response.setCharacterEncoding("UTF-8");
    		response.setHeader("Content-disposition","attachment;filename="+new String(fileName.getBytes(),"iso-8859-1"));
    		EasyExcel.write(response.getOutputStream(),FillData.class).withTemplate(templateFileName).sheet().doFill(l);
    	}
    }

EasyExcel是import com.alibaba.excel.EasyExcel;

## `Service接口` ##

public interface ExcelExportService {
        
    
    	void excelExport(HttpServletResponse response) throws IOException;
    
    }
    12345

## `控制器Controller` ##

@RestController
    public class HelloController {
        
    	@Autowired
    	private ExcelExportService excelExportService;
    	
    	@GetMapping("/h")
    	public void h(HttpServletResponse response) throws IOException {
        
    		excelExportService.excelExport(response);
    	}
    }

## 然后访问`/h`接口就可以导出一个Excel文件 ##

[img]: https://img-blog.csdnimg.cn/20181206105413810.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzI5MTMwNjQ1,size_16,color_FFFFFF,t_70
[img 1]: https://img-blog.csdnimg.cn/2018120610580570.png
[img 2]: https://img-blog.csdnimg.cn/20181206105855540.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzI5MTMwNjQ1,size_16,color_FFFFFF,t_70
[demo]: https://gitee.com/TZWw/common-excel-utils
[Link 1]: https://gitee.com/y_project/RuoYi-Cloud
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MjU1MTM2OQ_size_16_color_FFFFFF_t_70_pic_center]: https://img-blog.csdnimg.cn/2020112516324697.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MjU1MTM2OQ==,size_16,color_FFFFFF,t_70#pic_center
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MjU1MTM2OQ_size_16_color_FFFFFF_t_70_pic_center 1]: https://img-blog.csdnimg.cn/2020112516425832.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MjU1MTM2OQ==,size_16,color_FFFFFF,t_70#pic_center
[20201125164532977.png_pic_center]: https://img-blog.csdnimg.cn/20201125164532977.png#pic_center
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MjU1MTM2OQ_size_16_color_FFFFFF_t_70_pic_center 2]: https://img-blog.csdnimg.cn/20201125164618639.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MjU1MTM2OQ==,size_16,color_FFFFFF,t_70#pic_center
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MjU1MTM2OQ_size_16_color_FFFFFF_t_70_pic_center 3]: https://img-blog.csdnimg.cn/20201125164715114.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MjU1MTM2OQ==,size_16,color_FFFFFF,t_70#pic_center
[20201125162149674.png_pic_center]: https://img-blog.csdnimg.cn/20201125162149674.png#pic_center
[demo 1]: https://gitee.com/TZWw/common-excel-utils.git
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQyNDI2OTM3_size_16_color_FFFFFF_t_70_pic_center]: https://img-blog.csdnimg.cn/20200814161810304.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQyNDI2OTM3,size_16,color_FFFFFF,t_70#pic_center