EasyExcel自定义样式生成Excel

偏执的太偏执、 2022-12-18 14:54 142阅读 0赞

> EasyExcel是一个基于Java的简单、省内存的读写excel的开源项目。在尽可能节约内存的情况下支持读写百M的excel.官方文档地址为：[https://www.yuque.com/easyexcel/doc/easyexcel][https_www.yuque.com_easyexcel_doc_easyexcel]

EasyExcel生成excel

1、定义实体类（模板）

import com.alibaba.excel.annotation.ExcelProperty;
    import com.alibaba.excel.annotation.write.style.ColumnWidth;
    import lombok.Data;
    
    @Data
    public class StudentTable {
    
        @ColumnWidth(20)
        @ExcelProperty(value="姓名",index=0)
        private String studentName;
    
        @ColumnWidth(20)
        @ExcelProperty(value = "年龄",index = 1)
        private int studentAge;
    
        @ColumnWidth(20)
        @ExcelProperty(value = "年级",index = 2)
        private String studentGrade;
    
        @ColumnWidth(20)
        @ExcelProperty(value = "家庭住址",index = 3)
        private String studentAdress;
    }

2、 excel生成方法

import com.alibaba.excel.EasyExcel;
    import com.alibaba.excel.write.metadata.style.WriteCellStyle;
    import com.alibaba.excel.write.metadata.style.WriteFont;
    import com.alibaba.excel.write.style.HorizontalCellStyleStrategy;
    import com.example.page.domain.StudentTable;
    import com.example.page.utils.FileUtil;
    import org.apache.poi.ss.usermodel.HorizontalAlignment;
    import org.apache.poi.ss.usermodel.IndexedColors;
    import org.apache.poi.ss.usermodel.VerticalAlignment;
    
    import java.util.ArrayList;
    import java.util.List;
    
    /**
     * easyexcel生成excel
     */
    public class StudentTableService {
    
        /**
         * 拦截器形式自定义样式
         */
        public static void ExcelWrite(List<StudentTable> list){
            //存储路径
            String fileName = FileUtil.getPath() + "student" + System.currentTimeMillis()+".xlsx";
            System.out.println(fileName);
            //设置excel的头策略
            WriteCellStyle headWriteCellStyle = new WriteCellStyle();
            //设置背景颜色
            headWriteCellStyle.setFillForegroundColor(IndexedColors.BLUE_GREY.getIndex());
            //设置字体大小
            WriteFont headWriteFont = new WriteFont();
            headWriteFont.setFontHeightInPoints((short)20);
            headWriteCellStyle.setWriteFont(headWriteFont);
            //设置内容策略
            WriteCellStyle contentWriteCellStyle = new WriteCellStyle();
            //设置字体
            WriteFont contentWriteFont = new WriteFont();
            contentWriteFont.setFontHeightInPoints((short)12);
            contentWriteCellStyle.setWriteFont(contentWriteFont);
            //设置自动换行
            contentWriteCellStyle.setWrapped(true);
            //设置垂直居中
            contentWriteCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
            //设置水平居中
            contentWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.LEFT);
            //分别实现头和正文的样式
            HorizontalCellStyleStrategy horizontalCellStyleStrategy = new
                    HorizontalCellStyleStrategy(headWriteCellStyle,contentWriteCellStyle);
            //这里需要指定用哪个class去写，然后写到第一个sheet上，名字为sheet1 然后文件流会自动关闭
            EasyExcel.write(fileName, StudentTable.class).registerWriteHandler(horizontalCellStyleStrategy).sheet("sheet1").doWrite(list);
        }
    
        public static List<StudentTable> data(){
            StudentTable studentTable = new StudentTable();
            List<StudentTable> list = new ArrayList<StudentTable>();
            //姓名
            studentTable.setStudentName("小明");
            //年龄
            studentTable.setStudentAge(10);
            //年级
            studentTable.setStudentGrade("三年级");
            //家庭住址
            studentTable.setStudentAdress("中国北京海淀区人民广场幸福里小区");
            list.add(studentTable);
            return list;
        }
    
        public static void main(String[] args){
            List<StudentTable> list = data();
            ExcelWrite(list);
        }
    }

其中用到一个getPath()方法，该方法在FileUtil中定义

import java.io.File;
    import java.io.InputStream;
    
    public class FileUtil {
    
        public static InputStream getResourcesFileInputStream(String fileName){
            return Thread.currentThread().getContextClassLoader().getResourceAsStream(""+fileName);
        }
    
        public static String getPath(){
            return FileUtil.class.getResource("/").getPath();
        }
    
        public static File createNewFile(String pathName){
            File file = new File(getPath()+pathName);
            if (file.exists()){
                file.delete();
            }else{
                if(!file.getParentFile().exists()){
                    file.getParentFile().mkdirs();
                }
            }
            return file;
        }
    
        public static File readFile(String pathName){
            return new File(getPath()+pathName);
        }
    
        public static File readUserHomeFile(String pathName){
            return new File(System.getProperty("user.home")+File.separator+pathName);
        }
    }

在target/class目录下生成后的excel：

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2xpX3dfY2g_size_16_color_FFFFFF_t_70][]

[https_www.yuque.com_easyexcel_doc_easyexcel]: https://www.yuque.com/easyexcel/doc/easyexcel
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2xpX3dfY2g_size_16_color_FFFFFF_t_70]: /images/20221122/13e7d40ce3d94aec9cb84a1d2978ad12.png