/*
 *      Copyright (c) 2018-2028, Chill Zhuang All rights reserved.
 *
 *  Redistribution and use in source and binary forms, with or without
 *  modification, are permitted provided that the following conditions are met:
 *
 *  Redistributions of source code must retain the above copyright notice,
 *  this list of conditions and the following disclaimer.
 *  Redistributions in binary form must reproduce the above copyright
 *  notice, this list of conditions and the following disclaimer in the
 *  documentation and/or other materials provided with the distribution.
 *  Neither the name of the dreamlu.net developer nor the names of its
 *  contributors may be used to endorse or promote products derived from
 *  this software without specific prior written permission.
 *  Author: Chill 庄骞 (smallchill@163.com)
 */
package org.springblade.common.utils;

import com.alibaba.excel.EasyExcel;
import org.apache.commons.lang.StringUtils;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springblade.bank.returns.entity.Returns;
import org.springblade.bank.returns.entity.ReturnsExcel;
import org.springblade.core.excel.support.ExcelException;
import org.springblade.core.mp.support.Condition;
import org.springblade.core.tool.api.R;
import org.springblade.core.tool.utils.DateUtil;
import org.springblade.core.tool.utils.StringUtil;
import org.springframework.web.multipart.MultipartFile;

import java.io.BufferedInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.math.BigDecimal;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

/**
 * 通用工具类
 *
 * @author Chill
 */
public class MyExcelUtil {

	public static List<ReturnsExcel> readReturnsExcel(MultipartFile file){
		InputStream is = null;
		List<ReturnsExcel> list = new ArrayList<>();
		try {
			String filename = file.getOriginalFilename();
			if (org.springframework.util.StringUtils.isEmpty(filename)) {
				throw new ExcelException("请上传文件!");
			} else if (!org.springframework.util.StringUtils.endsWithIgnoreCase(filename, ".xls") && !org.springframework.util.StringUtils.endsWithIgnoreCase(filename, ".xlsx")) {
				throw new ExcelException("请上传正确的excel文件!");
			} else {

				is = file.getInputStream();
				Workbook workbook = new XSSFWorkbook(is);
				//默认第一页

				int i = 0;
				Sheet sheet = workbook.getSheetAt(i);
				while (workbook.isSheetHidden(i)) {
					sheet = workbook.getSheetAt(++i);
				}
				//空白页或空白内容
				if (sheet.getPhysicalNumberOfRows() < 2 || sheet.getLastRowNum() < 1) {
				}

				int count = sheet.getLastRowNum();
				for (int j = 1; j <= count; j++) {
					ReturnsExcel returns = new ReturnsExcel();
					Row row = sheet.getRow(j);
					if (null == row) {
						continue;
					}
					String cellValue0 = getCellValue(row.getCell(0));
					String cellValue1 = getCellValue(row.getCell(1));
					String cellValue2 = getCellValue(row.getCell(2));
					String cellValue3 = getCellValue(row.getCell(3));
					String cellValue4 = getCellValue(row.getCell(4));
					String cellValue5 = getCellValue(row.getCell(5));
					String cellValue6 = getCellValue(row.getCell(6));
					String cellValue7 = getCellValue(row.getCell(7));
					String cellValue8 = getCellValue(row.getCell(8));
					String cellValue9 = getCellValue(row.getCell(9));
					String cellValue10 = getCellValue(row.getCell(10));
					String cellValue11 = getCellValue(row.getCell(11));
					String cellValue12 = getCellValue(row.getCell(12));
					String cellValue13 = getCellValue(row.getCell(13));

					returns.setIsPublic(cellValue0);
					returns.setBankNo(cellValue1);
					returns.setOrgNo(cellValue2);
					returns.setSerialNo(cellValue3);
					returns.setSubBank(cellValue4);
					returns.setCustomerName(cellValue5);
					returns.setStatus(cellValue6);
					returns.setReason(cellValue7);
					returns.setRemark(cellValue8);
					returns.setBarCode(cellValue9);
					returns.setHandlingBank(cellValue10);
					returns.setHandlingBankReview(cellValue11);
					returns.setHandlingDate(DateUtil.parse(cellValue12, "yyyy-MM-dd"));
					returns.setHandler(cellValue13);

					list.add(returns);
				}

			}
		} catch (IOException var6) {
			var6.printStackTrace();
			return null;
		} finally {
			try {
				if (null != is) {
					is.close();
				}
			} catch (IOException e) {
				e.printStackTrace();
			}
		}
		return list;
	}


	/**
	 * 功能描述:获取单元格中的对象
	 *
	 * @param cell 一格
	 * @return String
	 */
	private static String getCellValue(Cell cell) {
		if (cell == null) {
			return null;
		}
		switch (cell.getCellTypeEnum()) {
			case NUMERIC:
				String cellValue = "";
				if(cell.getCellType() == Cell.CELL_TYPE_NUMERIC){
					short format = cell.getCellStyle().getDataFormat();
					if (format == 0){
						DecimalFormat df = new DecimalFormat("#");
						cellValue = df.format(cell.getNumericCellValue());
					}else if (format == 14 || format == 31 || format == 57 || format == 58 || format == 20 || format == 32){
						SimpleDateFormat sdf = null;
						if(format == 14 || format == 31 || format == 57 || format == 58){
							//日期
							sdf = new SimpleDateFormat("yyyy-MM-dd");
						}else if (format == 20 || format == 32) {
							//时间
							sdf = new SimpleDateFormat("HH:mm");
						}
						double value = cell.getNumericCellValue();
						Date date = org.apache.poi.ss.usermodel.DateUtil.getJavaDate(value);
						cellValue = sdf.format(date);
					}else {
						cellValue = new BigDecimal(cell.getNumericCellValue()).toString().trim();
					}

				}
				return cellValue;
//				if (DateUtil.isCellDateFormatted(cell)) {
//					return new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(cell.getDateCellValue());
//				}
//				return new BigDecimal(cell.getNumericCellValue()).toString().trim();
			case STRING:
				return StringUtils.trimToEmpty(cell.getStringCellValue());
			case FORMULA:
				return StringUtils.trimToEmpty(cell.getCellFormula());
			case BLANK:
				return null;
			case BOOLEAN:
				return String.valueOf(cell.getBooleanCellValue());
			case ERROR:
				return null;
			default:
				return cell.toString().trim();
		}
	}

}
