Revision of EasyExcel 使用模板导出

waynelone revidoval tento gist 5 months ago. Přejít na revizi

2 files changed, 105 insertions

MergeCellRangeWriteHandler.java(vytvořil soubor)

		@@ -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(vytvořil soubor)

		@@ -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	+	}

Novější Starší