JAVA实现easyExcel动态生成excel

添加pom依赖

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

 <!--工具类-->
<dependency>
    <groupId>cn.hutool</groupId>
    <artifactId>hutool-all</artifactId>
    <version>5.3.1</version>
</dependency>
<dependency>
   <groupId>com.alibaba</groupId>
    <artifactId>fastjson</artifactId>
    <version>1.2.21</version>
</dependency>

实体类

package com.example.mybatismysql8demo.vo;


import lombok.*;
import lombok.experimental.Accessors;

import java.math.BigDecimal;
import java.util.Date;

@NoArgsConstructor
@AllArgsConstructor
@Data
@Accessors(chain = true)
public class EasyExcelExportVo {

    private String name;

    private Integer age;

    private Integer height;

    private BigDecimal weight;

    private Date birthday;

}

执行方法

package com.example.mybatismysql8demo.controller;

import cn.hutool.core.bean.BeanUtil;
import com.alibaba.excel.EasyExcel;
import com.alibaba.fastjson.JSONObject;
import com.example.mybatismysql8demo.vo.EasyExcelExportVo;
import com.google.common.collect.Lists;
import lombok.extern.slf4j.Slf4j;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestMethod;
import org.springframework.web.bind.annotation.RestController;

import javax.servlet.http.HttpServletResponse;
import java.io.*;
import java.math.BigDecimal;
import java.net.URLEncoder;
import java.nio.charset.StandardCharsets;
import java.util.*;
import java.util.stream.Collectors;


@Slf4j
@RestController
public class EasyExcelController {

    @RequestMapping(value = "easyExcelExport", method = RequestMethod.GET)
    public void easyExcelExport(HttpServletResponse response) {
        dynamic(response,2);
    }

    public void dynamic(HttpServletResponse response,Integer state){
        if (state == 1){
            //动态生成excel
            try {
                //动态列值
                List<String> headName = Arrays.asList("姓名","性别","身高","年龄");
                List<List<String>> result = new ArrayList<>();
                headName.forEach(data-> result.add(Collections.singletonList(data)));
                //告诉浏览器数据格式,将头和数据传到前台
                response.setContentType("application/vnd.ms-excel");
                response.setCharacterEncoding("utf-8");
                response.setHeader("Content-disposition", "attachment;filename=" +  URLEncoder.encode("用户信息表.xls", StandardCharsets.UTF_8));
                EasyExcel.write(response.getOutputStream())
                        .autoCloseStream(Boolean.FALSE)
                        .head(result)
                        .sheet("信息表")
                        .doWrite(Collections.EMPTY_LIST);
                //关闭流
                response.getOutputStream().close();
            } catch (IOException e) {
                e.printStackTrace();
            }
        }else {
            //动态导出excel
            try {
                //动态列值
                Map<Integer,Object> map = getField();
                List<String> headName = (List<String>) map.get(1);
                List<List<String>> result = new ArrayList<>();
                headName.forEach(data->{
                    //添加标题
                    result.add(Lists.newArrayList("用户信息",data));
                });
                //数据
                List<List<Object>> data = (List<List<Object>>) map.get(2);
                //告诉浏览器数据格式,将头和数据传到前台
                response.setContentType("application/vnd.ms-excel");
                response.setCharacterEncoding("utf-8");
                response.setHeader("Content-disposition", "attachment;filename=" +  URLEncoder.encode("用户信息表.xls", StandardCharsets.UTF_8));
                EasyExcel.write(response.getOutputStream())
                        .autoCloseStream(Boolean.FALSE)
                        .head(result)
                        .sheet("信息表")
                        .doWrite(data);
                //关闭流
                response.getOutputStream().close();
            } catch (IOException e) {
                e.printStackTrace();
            }
        }

    }

    public static Map<Integer,Object> getField(){
        //前端配置动态字段,存储数据库
        String fields = "[{\"field\":\"age\",\"text\":\"年龄\"},{\"field\":\"height\",\"text\":\"身高\"},{\"field\":\"name\",\"text\":\"姓名\"},{\"field\":\"weight\",\"text\":\"体重\"}]";
        List<Map> mapList = JSONObject.parseArray(fields,Map.class);
        //字段名称
        List<String> fieldList = mapList.stream().map(v -> v.get("text").toString()).collect(Collectors.toList());
        //字段属性名称
        List<String> fieldNames = mapList.stream().map(v -> v.get("field").toString()).collect(Collectors.toList());
        //获取数据
        List<EasyExcelExportVo> data = getDateList();
        //数据处理
        List<List<Object>> list = new ArrayList<>();
        data.forEach(value->{
            //将对象转换为map
            Map<String, Object> mapInfo = BeanUtil.beanToMap(value);
            //遍历动态字段值
            List<Object> objectList = new ArrayList<>();
            fieldNames.forEach(key->{
                objectList.add(mapInfo.get(key));
            });
            list.add(objectList);
        });
        Map<Integer,Object> map = new HashMap<>(2);
        map.put(1,fieldList);
        map.put(2,list);
        return map;
    }

    public static void main(String[] args) {
        System.out.println(getField());
    }

    public static List<EasyExcelExportVo> getDateList(){
        List<EasyExcelExportVo> info = new ArrayList<>();
        Collections.addAll(info,
                new EasyExcelExportVo().setName("张三").setAge(18).setHeight(160).setWeight(new BigDecimal(100)).setBirthday(new Date()),
                new EasyExcelExportVo().setName("李四").setAge(18).setHeight(160).setWeight(new BigDecimal(100)).setBirthday(new Date()),
                new EasyExcelExportVo().setName("王五").setAge(18).setHeight(160).setWeight(new BigDecimal(100)).setBirthday(new Date()),
                new EasyExcelExportVo().setName("小明").setAge(20).setHeight(180).setWeight(new BigDecimal(120)).setBirthday(new Date())
        );
        return info;
    }

}

在这里插入图片描述
在这里插入图片描述

  • 3
    点赞
  • 0
    收藏
    觉得还不错? 一键收藏
  • 1
    评论
要将数据库中的数据导出到Excel文件中,可以使用Java中的Apache POI库来实现。以下是一个例子: ```java import java.io.FileOutputStream; import java.io.IOException; import java.sql.Connection; import java.sql.DriverManager; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Statement; import org.apache.poi.ss.usermodel.Cell; import org.apache.poi.ss.usermodel.Row; import org.apache.poi.xssf.usermodel.XSSFSheet; import org.apache.poi.xssf.usermodel.XSSFWorkbook; public class ExportToExcel { public static void main(String[] args) { String jdbcUrl = "jdbc:mysql://localhost:3306/testdb?useSSL=false"; String username = "root"; String password = "password"; String query = "SELECT * FROM users"; try (Connection conn = DriverManager.getConnection(jdbcUrl, username, password); Statement stmt = conn.createStatement(); ResultSet rs = stmt.executeQuery(query)) { // 创建工作簿和工作表 XSSFWorkbook workbook = new XSSFWorkbook(); XSSFSheet sheet = workbook.createSheet("Users"); // 创建表头 Row headerRow = sheet.createRow(0); String[] columns = {"ID", "Name", "Email", "Phone"}; for (int i = 0; i < columns.length; i++) { Cell cell = headerRow.createCell(i); cell.setCellValue(columns[i]); } // 填充数据 int rowNum = 1; while (rs.next()) { Row row = sheet.createRow(rowNum++); row.createCell(0).setCellValue(rs.getInt("id")); row.createCell(1).setCellValue(rs.getString("name")); row.createCell(2).setCellValue(rs.getString("email")); row.createCell(3).setCellValue(rs.getString("phone")); } // 将工作簿写入文件 FileOutputStream outputStream = new FileOutputStream("users.xlsx"); workbook.write(outputStream); workbook.close(); } catch (SQLException | IOException e) { e.printStackTrace(); } } } ``` 在上面的代码中,我们首先连接到数据库,然后创建一个工作簿和工作表。接下来,我们创建表头并填充数据。最后,我们将工作簿写入文件。 请注意,此代码使用了XSSF工作簿,这意味着生成Excel文件是基于XML的,并且只能在Excel 2007及更高版本中打开。如果您需要支持早期版本的Excel,可以使用HSSF工作簿代替。

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论 1
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值