waynelone revised this gist 5 months ago. Go to revision
2 files changed, 105 insertions
MergeCellRangeWriteHandler.java(file created)
| @@ -0,0 +1,32 @@ | |||
| 1 | + | import com.alibaba.excel.write.handler.SheetWriteHandler; | |
| 2 | + | import com.alibaba.excel.write.metadata.holder.WriteSheetHolder; | |
| 3 | + | import com.alibaba.excel.write.metadata.holder.WriteWorkbookHolder; | |
| 4 | + | import org.apache.poi.ss.usermodel.Sheet; | |
| 5 | + | import org.apache.poi.ss.util.CellRangeAddress; | |
| 6 | + | ||
| 7 | + | import java.util.ArrayList; | |
| 8 | + | import java.util.List; | |
| 9 | + | ||
| 10 | + | /** | |
| 11 | + | * 根据单元格区域合并单元格 | |
| 12 | + | **/ | |
| 13 | + | public class MergeCellRangeWriteHandler implements SheetWriteHandler { | |
| 14 | + | ||
| 15 | + | private final List<CellRangeAddress> rangeCellList; | |
| 16 | + | ||
| 17 | + | public MergeCellRangeWriteHandler(List<CellRangeAddress> rangeCellList) { | |
| 18 | + | this.rangeCellList = (rangeCellList == null) ? new ArrayList<>() : rangeCellList; | |
| 19 | + | } | |
| 20 | + | ||
| 21 | + | @Override | |
| 22 | + | public void beforeSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) { | |
| 23 | + | } | |
| 24 | + | ||
| 25 | + | @Override | |
| 26 | + | public void afterSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) { | |
| 27 | + | Sheet sheet = writeSheetHolder.getSheet(); | |
| 28 | + | for (CellRangeAddress cellRangeAddress : rangeCellList) { | |
| 29 | + | sheet.addMergedRegionUnsafe(cellRangeAddress); | |
| 30 | + | } | |
| 31 | + | } | |
| 32 | + | } | |
PersonController.java(file created)
| @@ -0,0 +1,73 @@ | |||
| 1 | + | import com.alibaba.excel.EasyExcelFactory; | |
| 2 | + | import com.alibaba.excel.ExcelWriter; | |
| 3 | + | import com.alibaba.excel.write.metadata.WriteSheet; | |
| 4 | + | import com.alibaba.excel.write.metadata.fill.FillConfig; | |
| 5 | + | import org.apache.poi.ss.util.CellRangeAddress; | |
| 6 | + | ||
| 7 | + | import org.springframework.core.io.ClassPathResource; | |
| 8 | + | import org.springframework.http.HttpHeaders; | |
| 9 | + | import org.springframework.http.HttpStatus; | |
| 10 | + | import org.springframework.http.MediaType; | |
| 11 | + | import org.springframework.http.ResponseEntity; | |
| 12 | + | import org.springframework.web.bind.annotation.RequestMapping; | |
| 13 | + | import org.springframework.web.bind.annotation.RestController; | |
| 14 | + | ||
| 15 | + | ||
| 16 | + | import java.io.ByteArrayOutputStream; | |
| 17 | + | import java.io.InputStream; | |
| 18 | + | import java.net.URLEncoder; | |
| 19 | + | import java.util.*; | |
| 20 | + | import java.util.stream.Collectors; | |
| 21 | + | ||
| 22 | + | @RestController | |
| 23 | + | @RequestMapping("person") | |
| 24 | + | public class DemoController { | |
| 25 | + | ||
| 26 | + | @GetMapping("export") | |
| 27 | + | public ResponseEntity<Object> export() throws Exception { | |
| 28 | + | List<Person> list = personService.queryList(); | |
| 29 | + | ByteArrayOutputStream outputStream = new ByteArrayOutputStream(); | |
| 30 | + | InputStream templateInputStream = new ClassPathResource("templates/PersonListTemplate.xlsx").getInputStream(); | |
| 31 | + | ||
| 32 | + | List<CellRangeAddress> cellRangeAddressList = buildMergeCellRangeList(list, 1, 4); | |
| 33 | + | MergeCellRangeWriteHandler mergeStrategy = new MergeCellRangeWriteHandler(cellRangeAddressList); | |
| 34 | + | ||
| 35 | + | ExcelWriter writer = EasyExcelFactory.write(outputStream).withTemplate(templateInputStream).build(); | |
| 36 | + | WriteSheet sheet = EasyExcelFactory.writerSheet().registerWriteHandler(mergeStrategy).sheetName("公司员工表").build(); | |
| 37 | + | Map<String, String> headerMap = new HashMap<>(); | |
| 38 | + | headerMap.put("companyName", "xxx 公司"); | |
| 39 | + | writer.fill(headerMap, sheet); | |
| 40 | + | ||
| 41 | + | FillConfig fillConfig = FillConfig.builder() | |
| 42 | + | .forceNewRow(true) | |
| 43 | + | .build(); | |
| 44 | + | writer.fill(list, fillConfig, sheet); | |
| 45 | + | writer.finish(); | |
| 46 | + | ||
| 47 | + | String encFileName = URLEncoder.encode("公司员工表", "UTF-8"); | |
| 48 | + | HttpHeaders headers = new HttpHeaders(); | |
| 49 | + | headers.add(HttpHeaders.CONTENT_DISPOSITION, "attachment;filename=" + encFileName + ".xlsx"); | |
| 50 | + | headers.setContentType(MediaType.parseMediaType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet")); | |
| 51 | + | ||
| 52 | + | return new ResponseEntity<>(outputStream.toByteArray(), headers, HttpStatus.OK); | |
| 53 | + | } | |
| 54 | + | ||
| 55 | + | private List<CellRangeAddress> buildMergeCellRangeList( | |
| 56 | + | List<Person> statsList, | |
| 57 | + | int columnIndex, | |
| 58 | + | int startRowIndex | |
| 59 | + | ) { | |
| 60 | + | List<CellRangeAddress> rangeCellList = new ArrayList<>(); | |
| 61 | + | LinkedHashMap<String, Long> groupMap = statsList.stream() | |
| 62 | + | .collect(Collectors.groupingBy(Person::getDepartmentName, LinkedHashMap::new, Collectors.counting())); | |
| 63 | + | for (Map.Entry<String, Long> entry : groupMap.entrySet()) { | |
| 64 | + | int count = entry.getValue().intValue(); | |
| 65 | + | if (count > 1) { | |
| 66 | + | int endRowIndex = startRowIndex + count - 1; | |
| 67 | + | rangeCellList.add(new CellRangeAddress(startRowIndex, endRowIndex, columnIndex, columnIndex)); | |
| 68 | + | } | |
| 69 | + | startRowIndex += count; | |
| 70 | + | } | |
| 71 | + | return rangeCellList; | |
| 72 | + | } | |
| 73 | + | } | |
Newer
Older