import com.alibaba.excel.EasyExcelFactory;
import com.alibaba.excel.ExcelWriter;
import com.alibaba.excel.write.metadata.WriteSheet;
import com.alibaba.excel.write.metadata.fill.FillConfig;
import org.apache.poi.ss.util.CellRangeAddress;

import org.springframework.core.io.ClassPathResource;
import org.springframework.http.HttpHeaders;
import org.springframework.http.HttpStatus;
import org.springframework.http.MediaType;
import org.springframework.http.ResponseEntity;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;


import java.io.ByteArrayOutputStream;
import java.io.InputStream;
import java.net.URLEncoder;
import java.util.*;
import java.util.stream.Collectors;

@RestController
@RequestMapping("person")
public class DemoController {

	@GetMapping("export")
	public ResponseEntity<Object> export() throws Exception {
		List<Person> list = personService.queryList();
		ByteArrayOutputStream outputStream = new ByteArrayOutputStream();
		InputStream templateInputStream = new ClassPathResource("templates/PersonListTemplate.xlsx").getInputStream();

		List<CellRangeAddress> cellRangeAddressList = buildMergeCellRangeList(list, 1, 4);
		MergeCellRangeWriteHandler mergeStrategy = new MergeCellRangeWriteHandler(cellRangeAddressList);

		ExcelWriter writer = EasyExcelFactory.write(outputStream).withTemplate(templateInputStream).build();
		WriteSheet sheet = EasyExcelFactory.writerSheet().registerWriteHandler(mergeStrategy).sheetName("公司员工表").build();
		Map<String, String> headerMap = new HashMap<>();
		headerMap.put("companyName", "xxx 公司");
		writer.fill(headerMap, sheet);

		FillConfig fillConfig = FillConfig.builder()
				.forceNewRow(true)
				.build();
		writer.fill(list, fillConfig, sheet);
		writer.finish();

		String encFileName = URLEncoder.encode("公司员工表", "UTF-8");
		HttpHeaders headers = new HttpHeaders();
		headers.add(HttpHeaders.CONTENT_DISPOSITION, "attachment;filename=" + encFileName + ".xlsx");
		headers.setContentType(MediaType.parseMediaType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"));

		return new ResponseEntity<>(outputStream.toByteArray(), headers, HttpStatus.OK);
	}

	private List<CellRangeAddress> buildMergeCellRangeList(
			List<Person> statsList,
			int columnIndex,
			int startRowIndex
	) {
		List<CellRangeAddress> rangeCellList = new ArrayList<>();
		LinkedHashMap<String, Long> groupMap = statsList.stream()
				.collect(Collectors.groupingBy(Person::getDepartmentName, LinkedHashMap::new, Collectors.counting()));
		for (Map.Entry<String, Long> entry : groupMap.entrySet()) {
			int count = entry.getValue().intValue();
			if (count > 1) {
				int endRowIndex = startRowIndex + count - 1;
				rangeCellList.add(new CellRangeAddress(startRowIndex, endRowIndex, columnIndex, columnIndex));
			}
			startRowIndex += count;
		}
		return rangeCellList;
	}
}