package cn.com.countrygarden.bi.main.util;

import org.apache.commons.lang3.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.BorderStyle;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.ss.usermodel.DateUtil;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.VerticalAlignment;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFCellStyle;
import org.apache.poi.xssf.usermodel.XSSFFont;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.web.multipart.MultipartFile;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.io.UnsupportedEncodingException;
import java.net.URLEncoder;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.UUID;
import java.util.zip.ZipEntry;
import java.util.zip.ZipOutputStream;

/**
 * 城市公司数据上报模块导入导出
 */
public class ExcelCompanyDataUtil {
	/**
	 * 最大行数65536
	 */
	public static final int MAX_NUM = 65530;
	
	/**
	 * 
	 * @param fileName   文件名称
	 * @param sheetName  区间名
	 * @param data       数据
	 * @param response
	 * @author Song.xy
	 * @throws Exception
	 */
	public static void exportCompanyData(String fileName, String sheetName,
			List<ArrayList<Object>> data,Map<String,Integer> sizeMap, HttpServletResponse response) throws Exception {
		/** 创建空的工作簿 */
		XSSFWorkbook workbook = new XSSFWorkbook();
		/** 创建工作表 */
		XSSFSheet sheet = workbook.createSheet(sheetName);

		//第一行标题
		String[] cellTitles = {"城市公司名称","城市公司编号","省级行政区","地级行政区域","县级行政区域","乡级行政区域","营业执照的物业公司全称","NC账套全称",
				"NC账套编码","NC人力组织编码","接管时间","接管时项目数量","接管时在管项目总建筑面积"};
		String[] cellTitles1 = {"股东方对接人信息","联系方式"};
		String[] cellTitles2 = {"对赌条款约定1","对赌条款约定2","对赌条款约定3"};

		//第二行标题
		String[] ccsrTitle= new String[sizeMap.get("theMaxSizeCcsr")];
		for(int i = 0;i<sizeMap.get("theMaxSizeCcsr");i++){
			ccsrTitle[i]="股东"+(i+1)+"占比情况";
		}
		String[] yearTitle = {"第一年","第二年","第三年","第四年","第五年"};
		String[] cclTitles = {"姓名","联系方式"};

		//第三行标题
		String[] ccsrNextTitle = {"股东名称","占比"};
		String[] lastTitle = {"可收费管理面积（㎡）","扣除非经常性损益净利润（元）","营业收入（元）","其他"};

		//设置宽度
		for (int i = 0; i < cellTitles.length; i++) {
			sheet.setColumnWidth(i,17 * 256);
		}

		//第一行合并单元格
		for(int i=0;i<=12;i++){
			sheet.addMergedRegion(new CellRangeAddress(0, 2, i, i));
		}
		int n = sizeMap.get("theMaxSizeCcsr");
		//int n= 3;
		sheet.addMergedRegion(new CellRangeAddress(0, 0, 13, 13+2*n-1));
		int endCol = cellTitles.length + 2*n;
		int m = sizeMap.get("theMaxSizeCcsj");
		//int m=2;
		for(int i = endCol;i<endCol+2*m;i++){
			sheet.addMergedRegion(new CellRangeAddress(0, 2, i, i));
		}
		endCol = endCol+2*m;
		sheet.addMergedRegion(new CellRangeAddress(0, 0, endCol+4, endCol+4+19));
		sheet.addMergedRegion(new CellRangeAddress(0, 2, endCol+24, endCol+24));
		sheet.addMergedRegion(new CellRangeAddress(0, 2, endCol+25, endCol+25));
		sheet.addMergedRegion(new CellRangeAddress(0, 2, endCol+26, endCol+26));

		int o = sizeMap.get("theMaxSizeCcl");
		//int o = 2;
		sheet.addMergedRegion(new CellRangeAddress(0, 0, endCol+27, endCol+27+2*o-1));


		//第二行单元格合并
		for(int i = cellTitles.length;i < cellTitles.length + 2*n;i++){
			sheet.addMergedRegion(new CellRangeAddress(1, 1, i, i+1));
			i++;
		}
		for(int i = endCol;i<endCol+24;i++){
			sheet.addMergedRegion(new CellRangeAddress(1, 1, i, i+3));
			i+=3;
		}
		for(int i = endCol+27;i<endCol+27+2*o;i++){
			sheet.addMergedRegion(new CellRangeAddress(1, 2, i, i));
		}

			/** 第一行列标题的处理  */
			// 创建单元格样式
			XSSFCellStyle cellStyle02 = createCellStyle(workbook,"Arial",(short)10,true,true);
			XSSFRow row01 = sheet.createRow(0);
			XSSFRow row02 = sheet.createRow(1);
			XSSFRow row03 = sheet.createRow(2);
			// for循环创建单元格并赋值和设置样式
			for (int i = 0; i < cellTitles.length; i++) {
				XSSFCell titleCell = row01.createCell(i);
				titleCell.setCellValue(cellTitles[i]);
				titleCell.setCellStyle(cellStyle02);
				row02.createCell(i).setCellStyle(cellStyle02);
				row03.createCell(i).setCellStyle(cellStyle02);
			}
		XSSFCell titleCell1 = row01.createCell(cellTitles.length);
		titleCell1.setCellValue("股东占比情况");
		titleCell1.setCellStyle(cellStyle02);
		for(int i=0;i < 2*m;i++){
			for(int j=0;j<cellTitles1.length;j++){
				XSSFCell titleCell2 = row01.createCell(i+j+(cellTitles.length+2*n));
				titleCell2.setCellValue(cellTitles1[j]);
				titleCell2.setCellStyle(cellStyle02);
				row02.createCell(i+j+(cellTitles.length+2*n)).setCellStyle(cellStyle02);
				row03.createCell(i+j+(cellTitles.length+2*n)).setCellStyle(cellStyle02);
			}
			i++;
		}
		XSSFCell titleCell3 = row01.createCell(endCol+4);
		row02.createCell(endCol+4).setCellStyle(cellStyle02);
		row03.createCell(endCol+4).setCellStyle(cellStyle02);
		titleCell3.setCellValue("投决会经营指标");
		titleCell3.setCellStyle(cellStyle02);
		for (int i = 1; i < 20; i++) {
			row01.createCell(endCol+4+i).setCellStyle(cellStyle02);
		}

		for (int i = 0; i <cellTitles2.length; i++) {
			XSSFCell titleCell4 = row01.createCell(i+(endCol+24));
			titleCell4.setCellValue(cellTitles2[i]);
			titleCell4.setCellStyle(cellStyle02);
			row02.createCell(i+(endCol+24)).setCellStyle(cellStyle02);
			row03.createCell(i+(endCol+24)).setCellStyle(cellStyle02);
		}
		XSSFCell titleCell5 = row01.createCell(endCol+27);
		row02.createCell(endCol+27).setCellStyle(cellStyle02);
		row03.createCell(endCol+27).setCellStyle(cellStyle02);
		titleCell5.setCellValue("城市公司负责人信息");
		titleCell5.setCellStyle(cellStyle02);


		/** 第二行列标题的处理  */
		// 创建单元格样式
		XSSFCellStyle cellStyle03 = createCellStyle(workbook,"Arial",(short)10,false,true);
		// for循环创建单元格并赋值和设置样式(股东占比)
		for (int i = 0; i <= n; i++) {
			XSSFCell titleCell01 = row02.createCell(2*i+13);
			titleCell01.setCellValue("股东"+(i+1)+"占比情况");
			titleCell01.setCellStyle(cellStyle03);
			row03.createCell(2*i+13).setCellStyle(cellStyle02);
		}
		XSSFCell titleCell02 = row02.createCell(endCol);
		row03.createCell(endCol).setCellStyle(cellStyle02);
		row01.createCell(endCol).setCellStyle(cellStyle02);
		titleCell02.setCellValue("对赌基数");
		titleCell02.setCellStyle(cellStyle03);
		for (int i = 1; i < 4; i++) {
			row01.createCell(endCol+i).setCellStyle(cellStyle02);
		}

		//年份
		for(int i = 0;i<yearTitle.length;i++){
			XSSFCell titleCell03 = row02.createCell(endCol+4*(i+1));
			row03.createCell(endCol+4*(i+1)).setCellStyle(cellStyle02);
			titleCell03.setCellValue(yearTitle[i]);
			titleCell03.setCellStyle(cellStyle03);
		}

		//城市公司负责人
		for(int i = 0; i < o; i++){
			for(int j = 0 ;j < cclTitles.length;j++){
				int i1 = j + i * 2 + cellTitles.length + n * 2 + m * 2 + lastTitle.length * 6 + cellTitles2.length;
				XSSFCell titleCell04 = row02.createCell(i1);
				row03.createCell(i1).setCellStyle(cellStyle02);
				titleCell04.setCellValue(cclTitles[j]);
				titleCell04.setCellStyle(cellStyle03);
			}
		}

		/** 第三行列标题的处理  */
		// 创建单元格样式
		XSSFCellStyle cellStyle04 = createCellStyle(workbook,"Arial",(short)10,false,true);
		//股东占比
		for(int i=0;i<n;i++){
			for(int j=0;j<ccsrNextTitle.length;j++){
				XSSFCell titleCell001 = row03.createCell(13+j+i*2);
				titleCell001.setCellValue(ccsrNextTitle[j]);
				titleCell001.setCellStyle(cellStyle02);
			}
		}
		//对赌基数和投决会经营指标
		for(int i=0;i<6;i++){
			for(int j=0;j<lastTitle.length;j++){
				XSSFCell titleCell002 = row03.createCell(endCol+j+4*i);
				titleCell002.setCellValue(lastTitle[j]);
				titleCell002.setCellStyle(cellStyle02);
			}
		}

		//中间行的处理
		// 创建单元格样式
		XSSFCellStyle cellStyle = createCellStyle(workbook,"Arial",(short)10,false,true);
		int temp = 3;
		// for循环处理
		for (int i = 0; i < data.size(); i++) {
			XSSFRow row = sheet.createRow(i + temp);
			for(int j = 0; j < data.get(i).size(); j++) {
				XSSFCell cell = row.createCell(j);
				Object value = data.get(i).get(j);
				cell.setCellValue(value == null ? "" : value.toString());
				cell.setCellStyle(cellStyle);
			}
		}
		
		/** 下载时文件名为中文：乱码解决 */
		// 响应的过程：
		// 服务器： utf-8|gbk --> iso8859-1    浏览器: iso8859-1 --> utf-8|gbk (firefox、chrome)
		// 服务器： gbk --> iso8859-1    浏览器: iso8859-1 --> gbk (msie)
		fileName = new String(fileName.getBytes("gbk"), "iso8859-1");
		/** 设置下载的文件名 */
		response.setHeader("content-disposition", "attachment;filename=" + fileName + ".xlsx");
        /** 向浏览器输出Excel文件 */
		workbook.write(response.getOutputStream());
		/** 关闭工作簿 */
		workbook.close();
	}
	
	/**
	 * 导出zip
	 * @param fileName   文件名称
	 * @param sheetName  区间名
	 * @param title      标题
	 * @param cellTitles 列名
	 * @param data       数据
	 * @param response
	 * @throws Exception
	 */
	public static String exportAssetsRegisterZip(String fileName,
			String sheetName, String title, String cellTitles[],
			List<ArrayList<Object>> data, HttpServletResponse response)
			throws Exception {

		// 计算导出excel数量
		int listSize = data.size();
		int excelNum = listSize % MAX_NUM == 0 ? listSize / MAX_NUM : listSize
				/ MAX_NUM + 1;

		File[] files = new File[excelNum];

		for (int i = 0; i < excelNum; i++) {
			try {
				files[i] = new File(System.getProperty("java.io.tmpdir"),fileName+"_"+(i+1) + ".xls");
				XSSFWorkbook workbook = new XSSFWorkbook();

				int start = i * MAX_NUM;
				int end = (start + MAX_NUM) > listSize ? listSize: (start + MAX_NUM);

				System.out.println("=====>start" + start + "end==>" + end);
				createAssetsRegisterExcel(workbook, sheetName,title,cellTitles, data,start,end);
				
				FileOutputStream out = new FileOutputStream(files[i]);
				workbook.write(out);
				out.close();
			} catch (Exception e) {
				deleteFiles(files);
				throw new Exception(e);

			}

		}
		File zipFile = new File(System.getProperty("java.io.tmpdir"), UUID.randomUUID().toString() + "assets.zip");
		FileOutputStream out = new FileOutputStream(zipFile);
		ZipOutputStream zipOutputStream = new ZipOutputStream(out);

		ZipEntry zipEntry = null;
		byte[] buffer = new byte[1024];
		int readtotal;

		for (int j = 0; j < files.length; j++) {
			zipEntry = new ZipEntry(files[j].getName());

			zipOutputStream.putNextEntry(zipEntry);

			FileInputStream in = new FileInputStream(files[j]);
			while ((readtotal = in.read(buffer)) > 0) {
				zipOutputStream.write(buffer, 0, readtotal);
			}
			in.close();
		}
		zipOutputStream.close();
		out.close();
		
		
		return zipFile.toString();
	}
	
	private static XSSFWorkbook createAssetsRegisterExcel(XSSFWorkbook workbook, String sheetName, String title, String cellTitles[],
                                                          List<ArrayList<Object>> data, int start, int end) throws IOException{
		/** 创建工作表 */
		XSSFSheet sheet = workbook.createSheet(sheetName);
		
		
		//设置宽度
		for (int i = 0; i < cellTitles.length; i++) {
			sheet.setColumnWidth(i,16 * 256);
		}
		
		
		if (StringUtils.isNotBlank(title)) {
			
			// 合并单元格
			if(cellTitles.length==1) {
				sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, cellTitles.length));
			}else {
				sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, cellTitles.length-1));
			}
			
			/** 第一行标题处理  */
			XSSFRow row01 = sheet.createRow(0);
			XSSFCell cell01 = row01.createCell(0);
			// 设置单元格内容
			cell01.setCellValue(title);
			// 创建单元格样式
			XSSFCellStyle cellStyle01 = createCellStyle(workbook,"Arial",(short)18,true,true);
			// 设置单元格背景样式颜色
			//cellStyle01.setFillPattern(FillPatternType.SOLID_FOREGROUND);// 普通颜色填充
			//cellStyle01.setFillForegroundColor(HSSFColor.HSSFColorPredefined.LIGHT_CORNFLOWER_BLUE.getIndex());// 灰色背景
			// 设置单元格样式
			cell01.setCellStyle(cellStyle01);
		}
		
		/** 第二行列标题的处理  */
		// 创建单元格样式
		XSSFCellStyle cellStyle02 = createCellStyle(workbook,"Arial",(short)10,true,true);
		XSSFRow row02 = sheet.createRow(StringUtils.isNotBlank(title)?1:0);
		// for循环创建单元格并赋值和设置样式
		for (int i = 0; i < cellTitles.length; i++) {
			XSSFCell titleCell = row02.createCell(i);
			titleCell.setCellValue(cellTitles[i]);
			titleCell.setCellStyle(cellStyle02);
		}
		
		/** 中间行的处理  */
		// 创建单元格样式
		XSSFCellStyle cellStyle03 = createCellStyle(workbook,"Arial",(short)10,false,true);
		int temp = StringUtils.isNotBlank(title)?2:1;
		// for循环处理 
		for (int i = start; i < end; i++) {
			XSSFRow row = sheet.createRow(i-start + temp);
			for(int j = 0; j < data.get(i).size(); j++) {
				XSSFCell cell = row.createCell(j);
				Object value = data.get(i).get(j);
				cell.setCellValue(value == null ? "" : value.toString());
				cell.setCellStyle(cellStyle03);
			}
		}
		
		return workbook;
	}
	
	private static XSSFCellStyle createCellStyle(XSSFWorkbook workbook, String fontName, short fontSize,
                                                 boolean isBlod, boolean isBorder) {
		// 通过工作簿创建样式
		XSSFCellStyle cellStyle = workbook.createCellStyle();
		// 设置水平和垂直居中
		cellStyle.setAlignment(HorizontalAlignment.CENTER);
		cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
		
		if (isBorder) {
			cellStyle.setBorderBottom(BorderStyle.THIN); // 下边框
			cellStyle.setBorderLeft(BorderStyle.THIN);// 左边框
			cellStyle.setBorderTop(BorderStyle.THIN);// 上边框
			cellStyle.setBorderRight(BorderStyle.THIN);// 右边框
		}
		// 通过工作簿创建字体
		XSSFFont font = workbook.createFont();
		// 设置字体
		if (null != fontName && !"".equals(fontName)) {
			font.setFontName(fontName);
		}
		// 设置字体大小
		font.setFontHeightInPoints(fontSize);
		// 设置字体是否加粗
		font.setBold(isBlod);
		// 把字体set到样式中
		cellStyle.setFont(font);

		return cellStyle;
	}
	
	/**
	 * 将导入信息中异常的数据存入errorList
	 */
	public static List<Map<String, String>> addErrorList(List<ArrayList<String>> temp,
			String[] column) {
		List<Map<String, String>> errorList = new ArrayList<Map<String, String>>();
		for (int i = 0; i < temp.size(); i++) {
			ArrayList<String> oneList = temp.get(i);
			Map<String, String> row = new HashMap<String, String>();
			for (int j = 0; j < oneList.size(); j++) {
				row.put(column[j],"".equals(oneList.get(j)) ? "" : String.valueOf(oneList.get(j)));
			}
			errorList.add(row);
		}
		return errorList;
	}
	
	/**
	 * 删除临时文件
	 * @param files
	 */
	public static void deleteFiles(File[] files){
		if(null != files){
			for(int i=0;i<files.length;i++){
				if(null != files[i]){
					 files[i].delete();
				}
			}
		}
	}
	
	 /**
     * 部门：软件开发事业部
     * 功能：下载压缩文件
     * 描述：略
     * 作成者：盛婷婷
     * 作成时间：2018-10-23
     */
    public static void download(HttpServletRequest request, HttpServletResponse response, String filePath, String backFilname) {
        File file = null;
        FileInputStream inputStream = null;
        OutputStream outputStream = null;
        try {
            // Linux系统用
            if (filePath.lastIndexOf("/") > 0) {
                file = new File(filePath);
            } else {
                // windows系统用
                file = new File(System.getProperty("java.io.tmpdir"), filePath);
            }

            inputStream = new FileInputStream(file);
            outputStream = response.getOutputStream();
            
            /** 下载时文件名为中文：乱码解决 */
    		// 响应的过程：
    		// 服务器： utf-8|gbk --> iso8859-1    浏览器: iso8859-1 --> utf-8|gbk (firefox、chrome)
    		// 服务器： gbk --> iso8859-1    浏览器: iso8859-1 --> gbk (msie)
            backFilname = new String(backFilname.getBytes("gbk"), "iso8859-1");
    		/** 设置下载的文件名 */
    		response.setHeader("content-disposition", "attachment;filename=" + backFilname);
            byte[] buffer = new byte[1024];
            int total;

            while ((total = inputStream.read(buffer)) > 0) {
                outputStream.write(buffer, 0, total);
            }

            outputStream.flush();
        } catch (FileNotFoundException e) {
            e.printStackTrace();
        } catch (IOException e) {
            e.printStackTrace();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                if (null != outputStream) {
                    outputStream.close();
                }

                if (null != inputStream) {
                    inputStream.close();
                }

            } catch (IOException e) {
                e.printStackTrace();
            }
            file.delete();
        }
    }
    

    
    /**
     * 部门：软件开发事业部
     * 功能：区分浏览器
     * 描述：略
     * 作成者：盛婷婷
     * 作成时间：2018-10-23
     */
    private static void setContentDisposition(HttpServletRequest request,
                                              HttpServletResponse response, String showName)
            throws UnsupportedEncodingException {
        String userAgent = request.getHeader("USER-AGENT").toLowerCase();
        String contentDisposition = "";
        if (userAgent != null) {
            if (userAgent.indexOf("msie") >= 0) {
                contentDisposition = "attachment; filename="
                        + URLEncoder.encode(showName, "UTF-8").replace("+",
                        "%20");

            } else if (userAgent.indexOf("mozilla") >= 0) {
                contentDisposition = "attachment; filename*=UTF-8''"
                        + URLEncoder.encode(showName, "UTF-8").replace("+",
                        "%20");

            } else if (userAgent.indexOf("applewebkit") >= 0) {
                contentDisposition = "attachment; filename="
                        + URLEncoder.encode(showName, "UTF-8").replace("+",
                        "%20");
            } else if (userAgent.indexOf("safari") >= 0) {
                contentDisposition = "attachment; filename="
                        + new String(showName.getBytes("UTF-8"), "ISO8859-1");

            } else if (userAgent.indexOf("opera") >= 0) {
                contentDisposition = "attachment; filename*=UTF-8''"
                        + URLEncoder.encode(showName, "UTF-8").replace("+",
                        "%20");

            } else {
                contentDisposition = "attachment; filename*=UTF-8''"
                        + URLEncoder.encode(showName, "UTF-8").replace("+",
                        "%20");
            }
        }
        response.setHeader("Content-Disposition", contentDisposition);
    }

	/**
	 *
	 * @param mfile  文件对象
	 * @return
	 * @throws IOException
	 */
	public static List<ArrayList<String>> importData(MultipartFile mfile) throws IOException {
		InputStream stream = mfile.getInputStream();
		// 判断文件格式
		boolean isExcel03 = mfile.getOriginalFilename().matches(".+\\.(?i)(xls)");//这里的(?i)代表忽略大小写
		Workbook wb = isExcel03 ? new HSSFWorkbook(stream) : new XSSFWorkbook(stream);
		// 读取工作表
		Sheet sheet = wb.getSheetAt(0);	// sheet1
		List<ArrayList<String>> resultData = new ArrayList<ArrayList<String>>();	//存放返回数据
		// 开始每行的读取
		//System.out.println(sheet.getPhysicalNumberOfRows());
		int maxRowLength = 0;
		if(0 != sheet.getLastRowNum()){
			if(0!=sheet.getRow(0).getLastCellNum()){
				maxRowLength = sheet.getRow(0).getLastCellNum();
			}
		}
		for (int i = 0; i <= sheet.getLastRowNum(); i++) {
			Row row = sheet.getRow(i);
			if(row==null) {
				break;
			}
			ArrayList<String> rowData = new ArrayList<>();
			for(int j =0;j<maxRowLength;j++) {	// for循环可读取 null 值
				Cell cell = row.getCell(j);
				String cellValue = "";
				if(null!=cell){
					switch (cell.getCellType()) {
						case Cell.CELL_TYPE_BLANK:	// 空白  //Cell.CELL_TYPE_STRING:		// 文本
								cellValue = "";
							break;
						case Cell.CELL_TYPE_STRING:
							cellValue = cell.getStringCellValue();
							break;
						case Cell.CELL_TYPE_NUMERIC: //Cell.CELL_TYPE_NUMERIC:	// 数字或者日期
							if(DateUtil.isCellDateFormatted(cell)){		// 是否是日期
								Date date = cell.getDateCellValue();
								cellValue = date == null ? null : new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(date);
							} else {
								cell.setCellType(CellType.STRING);
								cellValue = cell.getStringCellValue();
							}
							break;
						case Cell.CELL_TYPE_BOOLEAN:  //Cell.CELL_TYPE_BOOLEAN:	// 布尔型
							cellValue = String.valueOf(cell.getBooleanCellValue());
							break;
						default:
							cellValue = null;
							break;
					}
				}
				rowData.add(cellValue); // 每个单元格数据存入行数据集合
			}

			resultData.add(rowData);	// 每行数据存入返回数据集合
		}

		// 关流
		wb.close();
		stream.close();
		return resultData;
	}

	/**
	 * 常规管家服务数据导入（3级表头）
	 * @param mfile  文件对象
	 * @return
	 * @throws IOException
	 */
	public static List<ArrayList<String>> importData2(MultipartFile mfile) throws IOException {
		InputStream stream = mfile.getInputStream();
		// 判断文件格式
		boolean isExcel03 = mfile.getOriginalFilename().matches(".+\\.(?i)(xls)");//这里的(?i)代表忽略大小写
		Workbook wb = isExcel03 ? new HSSFWorkbook(stream) : new XSSFWorkbook(stream);
		// 读取工作表
		Sheet sheet = wb.getSheetAt(0);	// sheet1
		List<ArrayList<String>> resultData = new ArrayList<ArrayList<String>>();	//存放返回数据
		// 开始每行的读取
		//System.out.println(sheet.getPhysicalNumberOfRows());
		int maxRowLength = 0;
		if(0 != sheet.getLastRowNum()){
			if(0!=sheet.getRow(0).getLastCellNum()){
				maxRowLength = sheet.getRow(2).getLastCellNum();
			}
		}
		for (int i = 0; i <= sheet.getLastRowNum(); i++) {
			Row row = sheet.getRow(i);
			if(row==null) {
				break;
			}
			ArrayList<String> rowData = new ArrayList<>();
			for(int j =0;j<maxRowLength;j++) {	// for循环可读取 null 值
				Cell cell = row.getCell(j);
				String cellValue = "";
				if(null!=cell){
					switch (cell.getCellType()) {
						case Cell.CELL_TYPE_BLANK:	// 空白  //Cell.CELL_TYPE_STRING:		// 文本
							cellValue = "";
							break;
						case Cell.CELL_TYPE_STRING:
							cellValue = cell.getStringCellValue();
							break;
						case Cell.CELL_TYPE_NUMERIC: //Cell.CELL_TYPE_NUMERIC:	// 数字或者日期
							if(DateUtil.isCellDateFormatted(cell)){		// 是否是日期
								Date date = cell.getDateCellValue();
								cellValue = date == null ? null : new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(date);
							} else {
								cell.setCellType(CellType.STRING);
								cellValue = cell.getStringCellValue();
							}
							break;
						case Cell.CELL_TYPE_BOOLEAN:  //Cell.CELL_TYPE_BOOLEAN:	// 布尔型
							cellValue = String.valueOf(cell.getBooleanCellValue());
							break;
						default:
							cellValue = null;
							break;
					}
				}
				rowData.add(cellValue); // 每个单元格数据存入行数据集合
			}

			resultData.add(rowData);	// 每行数据存入返回数据集合
		}

		// 关流
		wb.close();
		stream.close();
		return resultData;
	}

	

}
