【EasyExcel 教程】详解写入Excel -- 写入

 
愿你如阳光，明媚不忧伤。

 

---

4. 详解写入Excel

4.1 简单写入Excel

• EasyExcelData.java
  创建与Excel标题相对应的实体类，和读取稍有不同，写入需要加上注解，设定写入的标题。
  @ExcelIgnore 可以忽略单个字段。
  

public class EasyExcelData {
    @ExcelProperty("字符串标题")
    private String stringData;
    @ExcelProperty("日期标题")
    private Date dateData;
    @ExcelProperty("数字标题")
    private Double doubleData;
    // 忽略这个字段
    @ExcelIgnore
    private String ignore;

    // 省略get和set
}

• EasyExcelDataController.java
  写入Excel，这里提供两种方式写入

@RestController
@RequestMapping("/easyExcel")
public class EasyExcelDataController extends BaseController {
    @Resource
    EasyExcelDataListener easyExcelDataListener;
    // 指定需要读的excel文件
    String fileName = "C:/Users/ISCCF_A/Desktop/EasyExcelData.xlsx";

	// 省略读取部分
	
    @RequestMapping("/write")
    public JsonResult simpleWrite(int mode) {
        log.info(startLog(Thread.currentThread().getStackTrace()[1].getMethodName()));
        // 写入Excel的数据
        List<EasyExcelData> list = EasyExcelUtil.testWriteData();
        if (mode == 1) {
            // 写法1（默认第一个Sheet）：
            // 这里需要指定写用哪个class去写，然后写到第一个sheet，名字为模板 然后文件流会自动关闭
            // 如果这里想使用03 则 传入excelType参数即可
            EasyExcel.write(fileName, EasyExcelData.class).sheet("写入方法一").doWrite(list);
        } else {
            ExcelWriter excelWriter = null;
            try {
                // 写法2（默认第一个Sheet）：
                // 这里需要指定写用哪个class去写
                excelWriter = EasyExcel.write(fileName, EasyExcelData.class).build();
                WriteSheet writeSheet = EasyExcel.writerSheet("写入方法二").build();
                excelWriter.write(list, writeSheet);
            } finally {
                if (excelWriter != null) {
                    // 千万别忘记finish关闭流
                    excelWriter.finish();
                }
            }
        }
        log.info(endLog(Thread.currentThread().getStackTrace()[1].getMethodName()));
        return new JsonResult(list, "Excel写入数据成功！");
    }
}

• EasyExcelUtil.java
  生产写入测试数据

public class EasyExcelUtil {

    public static List<EasyExcelData> testWriteData() {
        List<EasyExcelData> list = new ArrayList<>();
        for (int i = 0; i < 10; i++) {
            EasyExcelData data = new EasyExcelData();
            data.setStringData("字符串" + i);
            data.setDateData(new Date());
            data.setDoubleData(0.56);
            list.add(data);
        }
        return list;
    }

}

浏览器访问 http://localhost:8080/easyExcel/write?mode=1

• EasyExcelData.xlsx

4.2 根据参数导出指定列（排除或指定）

• EasyExcelDataWriteController.java

@RestController
@RequestMapping("/easyExcelWrite")
public class EasyExcelDataWriteController extends BaseController {
    // 指定需要写的excel文件
    String fileName = "C:/Users/ISCCF_A/Desktop/EasyExcelData.xlsx";
    // 写入Excel的数据
    List<EasyExcelData> list = EasyExcelUtil.testWriteData();

    @RequestMapping("/excludeOrIncludeWrite")
    public void excludeOrIncludeWrite(int mode) {
        log.info(startLog(Thread.currentThread().getStackTrace()[1].getMethodName()));
        if (mode == 1) {
            // 忽略传入字段，假设我们要忽略 date
            Set<String> excludeColumnFiledNames = new HashSet<String>();
            excludeColumnFiledNames.add("dateData");
            // 这里需要指定写用哪个class去写，然后写到第一个sheet，名字为模板，文件流会自动关闭
            EasyExcel.write(fileName, EasyExcelData.class).excludeColumnFiledNames(excludeColumnFiledNames).sheet("模板")
                    .doWrite(list);
        } else {
            // 指定传入字段，假设我们只想导出 date
            Set<String> includeColumnFiledNames = new HashSet<String>();
            includeColumnFiledNames.add("dateData");
            EasyExcel.write(fileName, EasyExcelData.class).includeColumnFiledNames(includeColumnFiledNames).sheet("模板")
                    .doWrite(list);
        }
        log.info(endLog(Thread.currentThread().getStackTrace()[1].getMethodName()));
    }
}

浏览器访问 http://localhost:8080/easyExcelWrite/excludeOrIncludeWrite?mode=1

浏览器访问 http://localhost:8080/easyExcelWrite/excludeOrIncludeWrite?mode=10

4.3 指定写入的列

• EasyExcelDataIndex.java
  创建指定列的实体类

public class EasyExcelDataIndex {
    // 这里需要注意在使用ExcelProperty注解的使用，如果想不空列则需要加入order字段，order会忽略空列，然后继续往后，
    // 而index，不会忽略空列，在第几列就是第几列。
    @ExcelProperty(value = "字符串标题", index = 0)
    private String stringData;
    @ExcelProperty(value = "日期标题", index = 1)
    private Date dateData;
    @ExcelProperty(value = "数字标题", index = 3)
    private Double doubleData;
    
    // 省略get和set
}

• EasyExcelDataWriteController.java

@RestController
@RequestMapping("/easyExcelWrite")
public class EasyExcelDataWriteController extends BaseController {
    // 指定需要写的excel文件
    String fileName = "C:/Users/ISCCF_A/Desktop/EasyExcelData.xlsx";
    // 写入Excel的数据
    List<EasyExcelData> list = EasyExcelUtil.testWriteData();

     @RequestMapping("/indexWrite")
    public void indexWrite() {
        log.info(startLog(Thread.currentThread().getStackTrace()[1].getMethodName()));
        EasyExcel.write(fileName, EasyExcelDataIndex.class).sheet("IndexWrite").doWrite(list);
        log.info(endLog(Thread.currentThread().getStackTrace()[1].getMethodName()));
    }
}

浏览器访问 http://localhost:8080/easyExcelWrite/indexWrite

4.4 复杂头写入

• EasyExcelDataComplexHead.java
  创建复杂头实体类

public class EasyExcelDataComplexHead {
    @ExcelProperty({"主标题", "字符串标题"})
    private String string;
    @ExcelProperty({"主标题", "日期标题"})
    private Date date;
    @ExcelProperty({"主标题", "数字标题"})
    private Double doubleData;
    
    // 省略get和set
}

• EasyExcelDataWriteController.java

@RestController
@RequestMapping("/easyExcelWrite")
public class EasyExcelDataWriteController extends BaseController {
    // 指定需要写的excel文件
    String fileName = "C:/Users/ISCCF_A/Desktop/EasyExcelData.xlsx";
    // 写入Excel的数据
    List<EasyExcelData> list = EasyExcelUtil.testWriteData();

    @RequestMapping("/complexHeadWrite")
    public void complexHeadWrite() {
        log.info(startLog(Thread.currentThread().getStackTrace()[1].getMethodName()));
        EasyExcel.write(fileName, EasyExcelDataComplexHead.class).sheet("IndexWrite").doWrite(list);
        log.info(endLog(Thread.currentThread().getStackTrace()[1].getMethodName()));
    }
}

浏览器访问 http://localhost:8080/easyExcelWrite/complexHeadWrite

4.5 日期，数字或者自定义格式的转换

• EasyExcelDataWriteConverter.java
  创建复杂头实体类

public class EasyExcelDataWriteConverter {
    @ExcelProperty(value = "字符串标题", converter = CustomStringStringConverter.class)
    private String string;
    @DateTimeFormat("yyyy年MM月dd日HH时mm分ss秒")
    @ExcelProperty("日期标题")
    private Date date;
    @NumberFormat("#.##%")
    @ExcelProperty(value = "数字标题")
    private Double doubleData;
    
    // 省略get和set
}

• EasyExcelDataWriteController.java
  生产写入测试数据，只是改变了泛型，不再展示。

@RestController
@RequestMapping("/easyExcelWrite")
public class EasyExcelDataWriteController extends BaseController {
    // 指定需要写的excel文件
    String fileName = "C:/Users/ISCCF_A/Desktop/EasyExcelData.xlsx";
    // 写入Excel的数据
    List<EasyExcelData> list = EasyExcelUtil.testWriteData();
    // 写入Excel格式化的数据
    List<EasyExcelDataWriteConverter> listFormat = EasyExcelUtil.testWriteDataFormat();

    @RequestMapping("/converterWrite")
    public void converterWrite() {
        log.info(startLog(Thread.currentThread().getStackTrace()[1].getMethodName()));
        EasyExcel.write(fileName, EasyExcelDataWriteConverter.class).sheet("converterWrite").doWrite(listFormat);
        log.info(endLog(Thread.currentThread().getStackTrace()[1].getMethodName()));
    }
}

浏览器访问 http://localhost:8080/easyExcelWrite/converterWrite

4.6 图片导出（列宽，行高注解）

• EasyExcelDataImage.java
  创建图片实体类

@ContentRowHeight(100)
@ColumnWidth(100 / 8)
public class EasyExcelDataImage {
    private File file;
    private InputStream inputStream;
    // 如果string类型 必须指定转换器，string默认转换成string
    @ExcelProperty(converter = StringImageConverter.class)
    private String string;
    private byte[] byteArray;
    // 根据url导出
    private URL url;
    
    // 省略get和set
}

• EasyExcelDataWriteController.java

@RestController
@RequestMapping("/easyExcelWrite")
public class EasyExcelDataWriteController extends BaseController {
    // 指定需要写的excel文件
    String fileName = "C:/Users/ISCCF_A/Desktop/EasyExcelData.xlsx";

    @RequestMapping("/imageWrite")
    public void imageWrite() throws IOException {
        log.info(startLog(Thread.currentThread().getStackTrace()[1].getMethodName()));
        // 如果使用流 记得关闭
        InputStream inputStream = null;
        try {
            List<EasyExcelDataImage> list = new ArrayList<EasyExcelDataImage>();
            EasyExcelDataImage imageData = new EasyExcelDataImage();
            list.add(imageData);
            String imagePath = "F:\\IDEAWorkSpace\\ITGodRoad\\src\\main\\resources\\static\\image\\Mayday.jpg";
            // 放入五种类型的图片 根据实际使用只要选一种即可
            imageData.setByteArray(FileUtils.readFileToByteArray(new File(imagePath)));
            imageData.setFile(new File(imagePath));
            imageData.setString(imagePath);
            inputStream = FileUtils.openInputStream(new File(imagePath));
            imageData.setInputStream(inputStream);
            imageData.setUrl(new URL(
                    "https://raw.githubusercontent.com/alibaba/easyexcel/master/src/test/resources/converter/img.jpg"));
            EasyExcel.write(fileName, EasyExcelDataImage.class).sheet().doWrite(list);
        } catch (MalformedURLException e) {
            e.printStackTrace();
        } finally {
            if (inputStream != null) {
                inputStream.close();
            }
        }
        log.info(endLog(Thread.currentThread().getStackTrace()[1].getMethodName()));
    }
}

浏览器访问 http://localhost:8080/easyExcelWrite/imageWrite

4.7 注解形式自定义样式

• package com.alibaba.excel.annotation.write.style;
  设定样式的注解都在这个包下，有兴趣可以自己点开查看

• EasyExcelDataStyle.java
  创建自定义样式实体类

// 头背景设置成钻石填充，红色 IndexedColors.RED.getIndex()
@HeadStyle(fillPatternType = FillPatternType.DIAMONDS, fillForegroundColor = 10)
// 头字体设置成20
@HeadFontStyle(fontHeightInPoints = 20)
// 内容的背景设置成绿色 IndexedColors.GREEN.getIndex()
@ContentStyle(fillPatternType = FillPatternType.SOLID_FOREGROUND, fillForegroundColor = 17)
// 内容字体设置成16
@ContentFontStyle(fontHeightInPoints = 16)
@ColumnWidth(40)
public class EasyExcelDataStyle {
    // 字符串的头背景设置成粉红 IndexedColors.PINK.getIndex()
    @HeadStyle(fillPatternType = FillPatternType.SOLID_FOREGROUND, fillForegroundColor = 14)
    // 字符串的头字体设置成20
    @HeadFontStyle(fontHeightInPoints = 30)
    // 字符串的内容的背景设置成天蓝 IndexedColors.SKY_BLUE.getIndex()
    @ContentStyle(fillPatternType = FillPatternType.SOLID_FOREGROUND, fillForegroundColor = 40)
    // 字符串的内容字体设置成20
    @ContentFontStyle(fontHeightInPoints = 30)
    @ExcelProperty("字符串标题")
    private String string;
    @ExcelProperty("日期标题")
    private Date date;
    @ExcelProperty("数字标题")
    private Double doubleData;
    
    // 省略get和set
}

• EasyExcelDataWriteController.java

@RestController
@RequestMapping("/easyExcelWrite")
public class EasyExcelDataWriteController extends BaseController {
    // 指定需要写的excel文件
    String fileName = "C:/Users/ISCCF_A/Desktop/EasyExcelData.xlsx";
    // 写入Excel格式化的数据
    List<EasyExcelDataWriteConverter> listFormat = EasyExcelUtil.testWriteDataFormat();

    @RequestMapping("/annotationStyleWrite")
    public void annotationStyleWrite() {
        log.info(startLog(Thread.currentThread().getStackTrace()[1].getMethodName()));
        EasyExcel.write(fileName, EasyExcelDataStyle.class).sheet("annotationStyleWrite").doWrite(listFormat);
        log.info(endLog(Thread.currentThread().getStackTrace()[1].getMethodName()));
    }
}

浏览器访问 http://localhost:8080/easyExcelWrite/annotationStyleWrite

4.8 合并单元格（注解方式）

• EasyExcelDataImage.java
  创建合并单元格实体类

public class EasyExcelDataMerge {
    // 将第6-7行的2-3列合并成一个单元格
    // @OnceAbsoluteMerge(firstRowIndex = 5, lastRowIndex = 6, firstColumnIndex = 1, lastColumnIndex = 2)
    // 这一列 每隔2行 合并单元格
    @ContentLoopMerge(eachRow = 2)
    @ExcelProperty("字符串标题")
    private String string;
    @ExcelProperty("日期标题")
    private Date date;
    @ExcelProperty("数字标题")
    private Double doubleData;
    
    // 省略get和set
}

• EasyExcelDataWriteController.java

@RestController
@RequestMapping("/easyExcelWrite")
public class EasyExcelDataWriteController extends BaseController {
    // 指定需要写的excel文件
    String fileName = "C:/Users/ISCCF_A/Desktop/EasyExcelData.xlsx";
    // 写入Excel格式化的数据
    List<EasyExcelDataWriteConverter> listFormat = EasyExcelUtil.testWriteDataFormat();
    
    @RequestMapping("/mergeWrite")
    public void mergeWrite() {
        log.info(startLog(Thread.currentThread().getStackTrace()[1].getMethodName()));
        EasyExcel.write(fileName, EasyExcelDataMerge.class).sheet("mergeWrite").doWrite(listFormat);
        log.info(endLog(Thread.currentThread().getStackTrace()[1].getMethodName()));
    }
}

浏览器访问 http://localhost:8080/easyExcelWrite/mergeWrite

4.9 动态头，实时生成头写入

• EasyExcelUtil.java
  创建动态头数据

public class EasyExcelUtil {
    public static List<List<String>> testWriteDynamicHeadData() {
        List<List<String>> list = new ArrayList<List<String>>();
        List<String> head0 = new ArrayList<String>();
        head0.add("字符串" + System.currentTimeMillis());
        List<String> head1 = new ArrayList<String>();
        head1.add("数字" + System.currentTimeMillis());
        List<String> head2 = new ArrayList<String>();
        head2.add("日期" + System.currentTimeMillis());
        list.add(head0);
        list.add(head1);
        list.add(head2);
        return list;
    }
}

• EasyExcelDataWriteController.java

@RestController
@RequestMapping("/easyExcelWrite")
public class EasyExcelDataWriteController extends BaseController {
    // 指定需要写的excel文件
    String fileName = "C:/Users/ISCCF_A/Desktop/EasyExcelData.xlsx";
    // 写入Excel的数据
    List<EasyExcelData> list = EasyExcelUtil.testWriteData();
    // 写入Excel的动态头数据
    List<List<String>> dynamicHeadList = EasyExcelUtil.testWriteDynamicHeadData();
    
    @RequestMapping("/dynamicHeadWrite")
    public void dynamicHeadWrite() {
        log.info(startLog(Thread.currentThread().getStackTrace()[1].getMethodName()));
        EasyExcel.write(fileName)
                // 这里放入动态头数据
                .head(dynamicHeadList).sheet("dynamicHeadWrite").doWrite(list);
        log.info(endLog(Thread.currentThread().getStackTrace()[1].getMethodName()));
    }
}

浏览器访问 http://localhost:8080/easyExcelWrite/dynamicHeadWrite

4.10 web中的写

• EasyExcelDataWriteController.java

@RestController
@RequestMapping("/easyExcelWrite")
public class EasyExcelDataWriteController extends BaseController {
    // 写入Excel的数据
    List<EasyExcelData> list = EasyExcelUtil.testWriteData();
    
    @RequestMapping("/multipartFileWrite")
    public void multipartFileWrite(HttpServletResponse response) throws IOException {
        log.info(startLog(Thread.currentThread().getStackTrace()[1].getMethodName()));
        response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
        response.setCharacterEncoding("utf-8");
        // 这里URLEncoder.encode可以防止中文乱码
        String fileName = URLEncoder.encode("ITGodRoad", "UTF-8").replaceAll("\\+", "%20");
        response.setHeader("Content-disposition", "attachment;filename*=utf-8''" + fileName + ".xlsx");
        EasyExcel.write(response.getOutputStream(), EasyExcelData.class).sheet("multipartFileWrite").doWrite(list);
        log.info(endLog(Thread.currentThread().getStackTrace()[1].getMethodName()));
    }
}

浏览器访问 http://localhost:8080/easyExcelWrite/multipartFileWrite