package com.gci.boot.modules.demo.controller;

import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.ExcelReader;
import com.alibaba.excel.ExcelWriter;
import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.exception.ExcelDataConvertException;
import com.alibaba.excel.read.listener.ReadListener;
import com.alibaba.excel.read.metadata.ReadSheet;
import com.alibaba.excel.util.ListUtils;
import com.alibaba.excel.write.metadata.WriteSheet;
import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import com.gci.boot.common.vo.Result;
import com.gci.boot.modules.system.entity.SysUser;
import com.gci.boot.modules.system.service.ISysUserService;
import lombok.extern.slf4j.Slf4j;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.cache.annotation.Cacheable;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.PostMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;
import org.springframework.web.multipart.MultipartFile;
import org.springframework.web.multipart.MultipartHttpServletRequest;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.io.UnsupportedEncodingException;
import java.net.URLEncoder;
import java.util.List;
import java.util.Map;

/**
 * <p>
 * 用户表 前端控制器
 * </p>
 *
 * @author chenzhiwei
 * @since 2023-08-14
 */
@Slf4j
@RestController
@RequestMapping("/demo/excel")
public class ExcelDemoController {

	@Autowired
	private ISysUserService sysUserService;

	/**
	 *  simpleWrite在数据量不大的情况下可以使用（5000以内，具体也要看实际情况），数据量大时要 重复多次写入
	 * @param response
	 */
	@GetMapping("/simple/export")
	public Result<?> export(HttpServletResponse response){
		try (OutputStream output = response.getOutputStream()){

			String fileName = "用户信息.xlsx";
			try {
				fileName = URLEncoder.encode(fileName, "UTF-8");
			} catch (UnsupportedEncodingException e) {
				e.printStackTrace();
			}
			response.setHeader("Content-disposition", "attachment;filename=" + fileName + ";" + "filename*=utf-8''" + fileName);

			List<SysUser> list = sysUserService.list();
			EasyExcel.write(output, SysUser.class).sheet("sheet1").doWrite(list);

		} catch (IOException e) {
			return Result.error("导出错误！");
		}
		return Result.ok();
	}

	/**
	 * 多次写入，防止单次数据量过大内存溢出
	 * @param response
	 * @return
	 */
	@GetMapping("/multi/export")
	public Result<?> multiExport(HttpServletResponse response){
		try (OutputStream output = response.getOutputStream()){
			String fileName = "用户信息.xlsx";
			try {
				fileName = URLEncoder.encode(fileName, "UTF-8");
			} catch (UnsupportedEncodingException e) {
				e.printStackTrace();
			}
			response.setHeader("Content-disposition", "attachment;filename=" + fileName + ";" + "filename*=utf-8''" + fileName);

			Page<SysUser> page = sysUserService.page(new Page(1, 10));
			try (ExcelWriter excelWriter = EasyExcel.write(output, SysUser.class).build()) {
				WriteSheet writeSheet = EasyExcel.writerSheet("sheet1").build();
				for (int i = 0; i < page.getPages(); i++) {
					page = sysUserService.page(new Page(i+1, 10));
					List<SysUser> data = page.getRecords();
					excelWriter.write(data, writeSheet);
				}
			}
		} catch (IOException e) {
			return Result.error("导出错误！");
		}
		return Result.ok();
	}

	/**
	 * 单次读入，数据量小的时候使用
	 * @param request
	 * @return
	 */
	@PostMapping("/simple/import")
	public Result<?> simpleImport(HttpServletRequest request) {
		MultipartHttpServletRequest multipartRequest = (MultipartHttpServletRequest) request;
		Map<String, MultipartFile> fileMap = multipartRequest.getFileMap();
		for (Map.Entry<String, MultipartFile> entity : fileMap.entrySet()) {
			MultipartFile file = entity.getValue();// 获取上传文件对象
			try(InputStream input = file.getInputStream()) {
				List<SysUser> list = EasyExcel.read(input).head(SysUser.class).sheet().doReadSync();
				log.info("数据量：{}",list.size());
				//sysUserService.saveBatch(list);
			}catch (IOException e){
				return Result.error("导入错误！");
			}
		}
		return Result.ok();
	}
	/**
	 * 多次读入，防止内存溢出
	 * @param request
	 * @return
	 */
	@PostMapping("/multi/import")
	public Result<?> multiImport(HttpServletRequest request){
		MultipartHttpServletRequest multipartRequest = (MultipartHttpServletRequest) request;
		Map<String, MultipartFile> fileMap = multipartRequest.getFileMap();
		for (Map.Entry<String, MultipartFile> entity : fileMap.entrySet()) {
			MultipartFile file = entity.getValue();// 获取上传文件对象

			try(InputStream input = file.getInputStream()) {
				EasyExcel.read(input, SysUser.class, new ReadListener<SysUser>() {
					/**
					 * 单次缓存的数据量
					 */
					public static final int BATCH_COUNT = 100;
					/**
					 * 临时存储
					 */
					private List<SysUser> cachedDataList = ListUtils.newArrayListWithExpectedSize(BATCH_COUNT);

					/**
					 * 每读取一行都会执行
					 */
					@Override
					public void invoke(SysUser data, AnalysisContext context) {
						cachedDataList.add(data);
						if (cachedDataList.size() >= BATCH_COUNT) {
							saveData();
							// 存储完成清理 list
							cachedDataList = ListUtils.newArrayListWithExpectedSize(BATCH_COUNT);
						}
					}

					/**
					 * 读取全部完成执行
					 */
					@Override
					public void doAfterAllAnalysed(AnalysisContext context) {
						saveData();
					}

					/**
					 * 在转换异常 获取其他异常下会调用本接口。抛出异常则停止读取。如果这里不抛出异常则 继续读取下一行。
					 *
					 * @param exception
					 * @param context
					 * @throws Exception
					 */
					@Override
					public void onException(Exception exception, AnalysisContext context) {
						log.error("解析失败，但是继续解析下一行:{}", exception.getMessage());
						// 如果是某一个单元格的转换异常 能获取到具体行号
						// 如果要获取头的信息 配合invokeHeadMap使用
						if (exception instanceof ExcelDataConvertException) {
							ExcelDataConvertException excelDataConvertException = (ExcelDataConvertException)exception;
							log.error("第{}行，第{}列解析异常，数据为:{}", excelDataConvertException.getRowIndex(),
									excelDataConvertException.getColumnIndex(), excelDataConvertException.getCellData());
						}
					}
					/**
					 * 加上存储数据库
					 */
					private void saveData() {
						log.info("{}条数据，开始存储数据库！", cachedDataList.size());
						//sysUserService.saveBatch(cachedDataList);
						log.info("存储数据库成功！");
					}
				}).sheet().doRead();
			}catch (IOException e){
				return Result.error("导入错误！");
			}
		}
		return Result.ok();
	}


	/**
	 * 多个sheet读入
	 * @param request
	 * @return
	 */
	@PostMapping("/multisheet/import")
	public Result<?> multisheetImport(HttpServletRequest request){
		MultipartHttpServletRequest multipartRequest = (MultipartHttpServletRequest) request;
		Map<String, MultipartFile> fileMap = multipartRequest.getFileMap();
		for (Map.Entry<String, MultipartFile> entity : fileMap.entrySet()) {
			MultipartFile file = entity.getValue();// 获取上传文件对象

			try(InputStream input = file.getInputStream()) {
				ReadListener listener = new ReadListener<SysUser>() {
					/**
					 * 单次缓存的数据量
					 */
					public static final int BATCH_COUNT = 100;
					/**
					 * 临时存储
					 */
					private List<SysUser> cachedDataList = ListUtils.newArrayListWithExpectedSize(BATCH_COUNT);

					/**
					 * 每读取一行都会执行
					 */
					@Override
					public void invoke(SysUser data, AnalysisContext context) {
						cachedDataList.add(data);
						if (cachedDataList.size() >= BATCH_COUNT) {
							saveData();
							// 存储完成清理 list
							cachedDataList = ListUtils.newArrayListWithExpectedSize(BATCH_COUNT);
						}
					}

					/**
					 * 读取全部完成执行
					 */
					@Override
					public void doAfterAllAnalysed(AnalysisContext context) {
						saveData();
						cachedDataList.clear();
					}

					/**
					 * 在转换异常 获取其他异常下会调用本接口。抛出异常则停止读取。如果这里不抛出异常则 继续读取下一行。
					 *
					 * @param exception
					 * @param context
					 * @throws Exception
					 */
					@Override
					public void onException(Exception exception, AnalysisContext context) {
						log.error("解析失败，但是继续解析下一行:{}", exception.getMessage());
						// 如果是某一个单元格的转换异常 能获取到具体行号
						// 如果要获取头的信息 配合invokeHeadMap使用
						if (exception instanceof ExcelDataConvertException) {
							ExcelDataConvertException excelDataConvertException = (ExcelDataConvertException)exception;
							log.error("第{}行，第{}列解析异常，数据为:{}", excelDataConvertException.getRowIndex(),
									excelDataConvertException.getColumnIndex(), excelDataConvertException.getCellData());
						}
					}
					/**
					 * 加上存储数据库
					 */
					private void saveData() {
						log.info("{}条数据，开始存储数据库！", cachedDataList.size());
						//sysUserService.saveBatch(cachedDataList);
						log.info("存储数据库成功！");
					}
				};

				try (ExcelReader excelReader = EasyExcel.read(input).build()) {
					ReadSheet readSheet1 =
							EasyExcel.readSheet(0).head(SysUser.class).registerReadListener(listener).build();
					ReadSheet readSheet2 =
							EasyExcel.readSheet(1).head(SysUser.class).registerReadListener(listener).build();
					// 这里注意 一定要把sheet1 sheet2 一起传进去，不然有个问题就是03版的excel 会读取多次，浪费性能
					excelReader.read(readSheet1, readSheet2);
				}
			}catch (IOException e){
				return Result.error("导入错误！");
			}
		}
		return Result.ok();
	}



}
