导出excle文件中的单元格有些需要合并如何操作
例如:左边的表格想合并单元格成右边的表格更加便于观看
一、依赖文件
<!-- excle操作--> <dependency> <groupId>cn.hutool</groupId> <artifactId>hutool-all</artifactId> <version>5.3.8</version> </dependency> <dependency> <groupId>net.sourceforge.jexcelapi</groupId> <artifactId>jxl</artifactId> <version>2.6.10</version> </dependency> <dependency> <groupId>org.apache.poi</groupId> <artifactId>poi</artifactId> <version>4.1.1</version> </dependency> <dependency> <groupId>org.apache.poi</groupId> <artifactId>poi-ooxml</artifactId> <version>4.1.1</version> </dependency>
二、代码
@RequestMapping("/merge") public class MergeController { @RequestMapping(value = "/excleExport" ) @ResponseBody public void csvExport(HttpServletResponse response){ //表头 List<String> headerList = Arrays.asList("编号", "姓名"); //通过工具类创建writer,默认创建xls格式 ExcelWriter writer = ExcelUtil.getWriter(); //写入表头 writer.writeHeadRow(headerList); //组织数据 // 组织数据 List<List<Object>> data = new ArrayList<>(); List list1 = Arrays.asList("001","张三"); List list2 = Arrays.asList("001","张三"); List list3 = Arrays.asList("001","张三"); List list4 = Arrays.asList("002","李四"); List list5 = Arrays.asList("002","李四"); List list6 = Arrays.asList("003","王五"); data.add(list1); data.add(list2); data.add(list3); data.add(list4); data.add(list5); data.add(list6); //一次性写出内容,使用默认样式,强制输出标题 writer.write(data,true); //设置表格宽度自动 writer.autoSizeColumnAll(); //获取底层的Workbook和Sheet对象 Workbook workbook = writer.getWorkbook(); Sheet sheet = workbook.getSheetAt(0); //合并相同编号的单元格 int startRow = 1; // 数据开始行(跳过表头) for (int i = 1; i < data.size(); i++) { if (!data.get(i).get(0).equals(data.get(i - 1).get(0))) { System.out.println(data.get(i).get(0)); System.out.println(data.get(i - 1).get(0)); if (startRow < i) { // 合并编号列 前两个参数起始行和结束行 后两个参数起始列和结束列 sheet.addMergedRegion(new CellRangeAddress(startRow, i , 0, 0)); // 合并姓名列 sheet.addMergedRegion(new CellRangeAddress(startRow, i , 1, 1)); } startRow = i+1; } } //response为HttpServeltReponse对象 response.setContentType("application/vnd.ms-excel;charset=utf-8"); response.setHeader("Content-Disposition","attachment;filename=1.xls"); ServletOutputStream out= null; try{ out = response.getOutputStream(); writer.flush(out,true); }catch (IOException e){ e.printStackTrace(); }finally { //关闭writer,释放内存 writer.close(); } IoUtil.close(out); } }
三、postman调用
下载后就是开头合并单元格后的效果
源码获取方式(免费):
(1)登录-注册:http://resources.kittytiger.cn/
(2)搜索:springboot导出excle+导出的excle单元格合并
标签:单元格,springboot,get,writer,List,excle,data,asList From: https://www.cnblogs.com/yclh/p/18500011