Java easypoi 模板方式导出excel 并合并相关列 |
您所在的位置：网站首页 › java导出合并单元格合并列不显示 › Java easypoi 模板方式导出excel 并合并相关列 |
Java easypoi 模板方式导出excel 并合并相关列
|
在项目开发中经常会使用到合并列，格式如下： 1.引入easypoi cn.afterturn easypoi-annotation cn.afterturn easypoi-base2.定义一个实体类PersonDto import cn.afterturn.easypoi.excel.annotation.Excel; import lombok.Data; import java.io.Serializable; @Data public class PersonDto implements Serializable { private static final long serialVersionUID = 1L; @Excel(name = "省份", mergeVertical = true, width = 50) private String province; @Excel(name = "城市", mergeVertical = true, width = 50) private String city; @Excel(name = "民族", mergeVertical = true, width = 50) private String national; @Excel(name = "姓名", width = 20) private String userName; @Excel(name = "年龄", needMerge = true) private int age; @Excel(name = "地址", width = 50) private String address; }3.定义一个Excel模板test.xlsx（名称随意），模板格式如下： $fe:list
4.定义控制器ExportController，在控制器中需要注意的是map.put("名称")必须与上图模板中开头的fe:list名称一样
控制器完整代码： import cn.afterturn.easypoi.excel.ExcelExportUtil; import cn.afterturn.easypoi.excel.entity.TemplateExportParams; import cn.afterturn.easypoi.excel.entity.enmus.ExcelType; import cn.afterturn.easypoi.util.PoiMergeCellUtil; import com.boot.basic.annotation.security.PreAuth; import com.boot.web.vo.PersonDto; import io.swagger.annotations.Api; import io.swagger.annotations.ApiOperation; import lombok.RequiredArgsConstructor; import lombok.extern.slf4j.Slf4j; import org.apache.poi.ss.usermodel.Workbook; import org.springframework.beans.factory.annotation.Autowired; 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.net.URLEncoder; import java.util.*; @RestController @RequestMapping("/excel") public class ExcelController { @Autowired protected HttpServletResponse response; /** * 数据导出excel */ @ApiOperation(value = "Get方式导出Excel") @RequestMapping(value = "/export", method = RequestMethod.GET, produces = "application/octet-stream") protected void exportExcel() { try { Map map = new HashMap(); List list = new ArrayList(); for (int i = 0; i < 10; i++) { PersonDto entity = new PersonDto(); entity.setProvince("广东"); entity.setCity("广州"); entity.setNational("汉族"); entity.setUserName("张三_" + i); if (i > 4) { entity.setUserName("李四"); } entity.setAge(16 + i); entity.setAddress("广东省广州市_" + i); list.add(entity); } for (int i = 0; i < 10; i++) { PersonDto entity = new PersonDto(); entity.setProvince("广西"); entity.setCity("南宁"); entity.setNational("壮族"); entity.setUserName("王五_" + i); entity.setAddress("广西南宁_" + i); if (i > 2) { entity.setNational("汉族"); } if (i > 4) { entity.setCity("桂林"); entity.setUserName("王五"); entity.setAddress("广西桂林_" + i); } entity.setAge(21 + i); list.add(entity); } map.put("list", list); TemplateExportParams params = new TemplateExportParams("E:/test/test.xlsx"); map.put("type", ExcelType.HSSF); // 导出excel Workbook workbook = ExcelExportUtil.exportExcel(params, map); if (workbook == null) { throw new Exception("文件导出失败，workbook为空"); } PoiMergeCellUtil.mergeCells(workbook.getSheetAt(0), 1, 0, 1, 2); // 重置响应对象 response.reset(); // 指定下载的文件名--设置响应头 response.setHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode("人员信息表" + System.currentTimeMillis(), "UTF-8") + ".xlsx"); response.setContentType("application/vnd.ms-excel;charset=UTF-8"); response.setHeader("Pragma", "no-cache"); response.setHeader("Cache-Control", "no-cache"); response.setDateHeader("Expires", 0); workbook.write(response.getOutputStream()); } catch (Exception e) { e.printStackTrace(); log.error("数据导出失败：" + e.getMessage()); } } } |
【本文地址】
公司简介
联系我们
今日新闻 |
点击排行 |
|
推荐新闻 |
图片新闻 |
|
专题文章 |