java 导出excel 创建多级表头 Easyexcel web下载

使用Easyexcel
效果

maven依赖

 <!--操作excel -->
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>easyexcel</artifactId>
            <version>2.1.1</version>
            <scope>test</scope>
        </dependency>
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>easyexcel</artifactId>
            <version>2.1.1</version>
            <scope>compile</scope>
        </dependency>

使用对象创建

import com.alibaba.excel.annotation.ExcelIgnore;
import com.alibaba.excel.annotation.ExcelProperty;
import lombok.Data;

import java.util.Date;

//导出数据成excel  多级头导出  配合测试类
@Data
public class DemoData {

    @ExcelProperty({"序号"})
    private Integer orderNumber;
    @ExcelProperty({"一级","二三四级"})
    
    private String ceshi;
    @ExcelProperty({"一级","二级头", "三级头","四级"})

    private String string;

    @ExcelProperty({"一级","二级头", "三级头","四级"})

    private Date date;

    @ExcelProperty({"一级","二级头", "三级头","四级"})

    private Double doubleData;

    /**

     * 忽略这个字段

     */

    @ExcelIgnore

    private String ignore;

}

测试类

//多级输出excel
    @Test
    public void mergeWrite() {
        //创建数据
        List<DemoData> list = new ArrayList<DemoData>();

        for (int i = 0; i < 10; i++) {

            DemoData data = new DemoData();
            data.setOrderNumber(i);
            data.setCeshi("测试");

            data.setString("测试" + i);

            data.setDate(new Date());

            data.setDoubleData(0.820);

            list.add(data);

        }
        //文件名
        String fileName = System.currentTimeMillis()+".xlsx";
        // 每隔2行会合并 把eachColumn 设置成 3 也就是我们数据的长度，所以就第一列会合并。当然其他合并策略也可以自己写
        LoopMergeStrategy loopMergeStrategy = new LoopMergeStrategy(2, 1);
        // 这里 需要指定写用哪个class去写，然后写到第一个sheet，名字为模板 然后文件流会自动关闭
        EasyExcel.write(fileName, DemoData.class).registerWriteHandler(loopMergeStrategy).sheet("合并单元格")
                .doWrite(list);
    }

new LoopMergeStrategy(2, 1); 2代表合并2行，1是索引 ，从第二个字段开始合并 序号字段不合并。

web导出下载文件

import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.write.merge.LoopMergeStrategy;
import com.alibaba.excel.write.metadata.style.WriteCellStyle;
import com.alibaba.excel.write.style.HorizontalCellStyleStrategy;
import com.molomessage.message.excel.entity.DemoData;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;

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

@RestController
@RequestMapping("/export")
public class ExportExcelController {

    //使用easyExcel导出
    @GetMapping("/test")
    public void easyExcelExport(HttpServletResponse response){
        try {
            //创建数据
            List<DemoData> list = new ArrayList<DemoData>();

            for (int i = 0; i < 10; i++) {

                DemoData data = new DemoData();
                data.setOrderNumber(i);
                data.setCeshi("测试");

                data.setString("测试" + i);

                data.setDate(new Date());

                data.setDoubleData(0.820);

                list.add(data);

            }
            WriteCellStyle headWriteCellStyle = new WriteCellStyle();
            //设置头居中
            headWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);
            //内容策略
            WriteCellStyle contentWriteCellStyle = new WriteCellStyle();
            //设置 水平居中
            contentWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.LEFT);
            HorizontalCellStyleStrategy horizontalCellStyleStrategy = new HorizontalCellStyleStrategy(headWriteCellStyle, contentWriteCellStyle);
            response.setContentType("application/vnd.ms-excel;charset=utf-8");
            //response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
            response.setCharacterEncoding("utf-8");
            // 这里URLEncoder.encode可以防止中文乱码 当然和easyexcel没有关系
            String fileName = System.currentTimeMillis()+".xlsx";
            // 每隔2行会合并 把eachColumn 设置成 3 也就是我们数据的长度，所以就第一列会合并。当然其他合并策略也可以自己写
            LoopMergeStrategy loopMergeStrategy = new LoopMergeStrategy(2, 1);
            response.setHeader("Content-disposition", "attachment;filename=" + fileName + ".xls");
            // 这里需要设置不关闭流
            EasyExcel.write(response.getOutputStream(), DemoData.class).autoCloseStream(Boolean.FALSE).registerWriteHandler(horizontalCellStyleStrategy).sheet("用户信息表").doWrite(list);

        } catch (IOException e) {
            e.printStackTrace();
        }


    }




}