package com.cim.idm.utils;

import org.apache.commons.lang3.StringUtils;
import org.apache.log4j.Logger;
import org.apache.poi.ss.usermodel.DateUtil;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.jdom.Element;
import org.json.JSONArray;
import org.json.JSONObject;

import java.io.IOException;
import java.io.InputStream;
import java.text.NumberFormat;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.List;

public class ExcelUtils {
	private static Logger logger = Logger.getLogger(ExcelUtils.class);
	
	public static Element excelToDocument(InputStream is) throws Exception {
		XSSFWorkbook work = null;
		Element body = new Element(MessageUtil.Body_Tag);
		try {
			work = new XSSFWorkbook(is);
			int sheets = work.getNumberOfSheets();
			for(int k = 0;k<sheets;k++) {
				XSSFSheet sheet = work.getSheetAt(k);
				XSSFRow first = sheet.getRow(0);//第一行是字段名
				if(first != null) {
					int rows = sheet.getLastRowNum();
					short cells = first.getLastCellNum();
					for(int i = 2;i<=rows;i++) {//从第三行开始读取数据,第二行是字段备注
						Element data = new Element(sheet.getSheetName());
						XSSFRow row = sheet.getRow(i);
						if(row!=null) {
							if(StringUtils.isBlank(getCellValue(row.getCell(0)))) {
								continue;
							}
							for(short j = 0;j<cells;j++) {
								Element e = new Element(getCellValue(first.getCell(j)));
								e.setText(getCellValue(row.getCell(j)));
								data.addContent(e);
							}
							body.addContent(data);
						}
					}
				}
			}
		} catch (Exception e) {
			logger.error(e);
			throw new Exception("文件格式错误");
		} finally {
			try {
				if(work != null) {
					work.close();
				}
			} catch (IOException e) {
				logger.error(e);
			}
		}
		return body;
	}
	
	public static JSONObject excelToJSON(InputStream is) throws Exception {
		XSSFWorkbook work = null;
		JSONArray jo = new JSONArray();
		JSONObject jso = new JSONObject();
		//Element body = new Element(MessageUtil.Body_Tag);
		try {
			work = new XSSFWorkbook(is);
			int sheets = work.getNumberOfSheets();
			for(int k = 0;k<sheets;k++) {
				XSSFSheet sheet = work.getSheetAt(k);
				XSSFRow first = sheet.getRow(0);//第一行是字段名
				if(first != null) {
					int rows = sheet.getLastRowNum();
					short cells = first.getLastCellNum();
					for(int i = 2;i<=rows;i++) {//从第三行开始读取数据,第二行是字段备注
						JSONObject jorow = new JSONObject();
						XSSFRow row = sheet.getRow(i);
						if(row!=null) {
							if(StringUtils.isBlank(getCellValue(row.getCell(0)))) {
								continue;
							}
							for(short j = 0;j<cells;j++) {
								//Map<String, Object> map = new HashMap<>();	
								//JSONObject jocell = new JSONObject();
								String value = getCellValue(row.getCell(j));
								jorow.accumulate(getCellValue(first.getCell(j)),value);
								//jorow.append(getCellValue(first.getCell(j)),value);
								//map.put(getCellValue(first.getCell(j)),getCellValue(row.getCell(j)).toString());
								//JSONObject JSONObject jocell = new JSONObject(map); = new JSONObject(map);
								//jorow.put(jocell);
							}
							
						}
						jo.put(jorow);
					}
				}
			}
		} catch (Exception e) {
			logger.error(e);
			throw new Exception("文件格式错误");
		} finally {
			try {
				if(work != null) {
					work.close();
				}
			} catch (IOException e) {
				logger.error(e);
			}
		}
		logger.info("jo:"+jo);
		jso.accumulate("DATA", jo);
		return jso;
	}
	
	private static String getCellValue(XSSFCell cell) {
		
		if(cell == null) {
			return "";
		}
		
		String result;
		switch(cell.getCellType()) {
			case XSSFCell.CELL_TYPE_BOOLEAN:
				result = String.valueOf(cell.getBooleanCellValue());
				break;
			case XSSFCell.CELL_TYPE_NUMERIC:
				if(DateUtil.isCellDateFormatted(cell)) {
					Date date = DateUtil.getJavaDate(cell.getNumericCellValue());
					SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
					result = sdf.format(date);
				}else {
					NumberFormat nf = NumberFormat.getInstance();
					nf.setGroupingUsed(false);
					result = nf.format(cell.getNumericCellValue());
				}
				break;
			case XSSFCell.CELL_TYPE_FORMULA:
				result = cell.getStringCellValue();
				break;
			case XSSFCell.CELL_TYPE_STRING:
				result = cell.getStringCellValue();
				break;
			default:
				result = "";
				break;
		}
		return result;
	}
	
	public static XSSFWorkbook createXlsx(List<String> sheetNames,List<List<String>> columnNames,List<List<List<String>>> columnDatas) {
		XSSFWorkbook work = new XSSFWorkbook();//创建工作簿
		for(int i = 0; i < sheetNames.size() ;i++) {
			XSSFSheet sheet = work.createSheet(sheetNames.get(i));//创建sheet
			List<String> cName = columnNames.get(i);
			List<List<String>> cDatas = columnDatas.get(i);
			XSSFRow row = sheet.createRow(0);
			XSSFCell cell = null;
			//标题
			for(int j = 0;j<cName.size();j++) {
				cell = row.createCell(j);
				cell.setCellValue(cName.get(j));
			}
			//内容
			for(int k = 0;k<cDatas.size();k++) {
				row = sheet.createRow(k+1);
				List<String> cData = cDatas.get(k);
				for(int m = 0;m<cData.size();m++) {
					row.createCell(m).setCellValue(cData.get(m));
				}
			}
		}
		return work;
	}
}
