EXCEL的多Sheet頁導入

關于多SHEET頁的導入

/*******************************************************************************
 * $Header$
 * $Revision$
 * $Date$
 *
 *==============================================================================
 *
 * Copyright (c) 2001-2006 Primeton Technologies, Ltd.
 * All rights reserved.
 * 
 * Created on 2016-3-14
 *******************************************************************************/

package com.hkrsoft.ysglpt.comm;

import java.io.FileInputStream;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Date;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import org.apache.poi.ss.usermodel.Cell;

import com.eos.das.entity.criteria.CriteriaType;
import com.eos.das.entity.criteria.impl.CriteriaTypeImpl;
import com.eos.data.datacontext.DataContextManager;
import com.eos.data.datacontext.UserObject;
import com.eos.foundation.data.DataObjectUtil;
import com.eos.foundation.database.DatabaseExt;
import com.eos.foundation.database.DatabaseUtil;
import com.eos.system.annotation.Bizlet;

import commonj.sdo.DataObject;

/**
 * TODO fill class info here
 *
 * @author 
 * @date 2016-03-14 15:43:18
 */
/*
 * Modify history
 * $Log$ 
 *//*******************************************************************************
 * $Header$
 * $Revision$
 * $Date$
 *
 *==============================================================================
 *
 * Copyright (c) 2001-2006 Primeton Technologies, Ltd.
 * All rights reserved.
 * 
 * Created on 2016-3-14
 *******************************************************************************/

@Bizlet("Excel多sheet頁導入")
public class ExcelImportUtil {

	/**
	 
	 * @param targetFile 導入的EXCEL路徑
	 * @param type 财務預算1，業務預算2
	 * @param half 導入區間，上半年1下半年2
	 * @param importopt 導入人員
	 * @return
	 * @throws Throwable
	 */
	@Bizlet("")
	public static String impData(String targetFile, String fileName,
			String type, String half, String importopt) throws Throwable {
		String msg = "";
		//linux下來解析檔案
		String targetFile1 = targetFile.replace("\\", "/");
		System.out.println("targetFile1==" + targetFile1);
		//解析檔案
		POIFSFileSystem fs = new POIFSFileSystem(new FileInputStream(
				targetFile1));

		//得到工作薄
		HSSFWorkbook hssfWorkbook = new HSSFWorkbook(fs);
		//有多少個sheet
		int sheetNumber = hssfWorkbook.getNumberOfSheets();

		//建立實體
		DataObject logEntity = DataObjectUtil
				.createDataObject("com.hkrsoft.ysglpt.ysbz.excelimort.ysbzexcel.YsYsbzYslog");
		//設定實體主鍵
		DatabaseExt.getPrimaryKey(logEntity);
		logEntity.set("importopt", importopt);
		logEntity.set("filename", fileName);
		logEntity.set("sheetcount", sheetNumber);
		logEntity.set("ystype", type);
		Date now = new Date();
		logEntity.set("importtime", now);
		//讀取第一個sheet頁
		HSSFSheet hssfSheet0 = hssfWorkbook.getSheetAt(0);
		//然後取得第一個sheet頁的年份資料,第二行
		HSSFRow hssfRow0 = hssfSheet0.getRow(1);
		HSSFCell hcell0 = hssfRow0.getCell(1);
		logEntity.set("year", getCellValue(hcell0)); //年份指派
		//插入日志表
		DatabaseUtil.insertEntity("default", logEntity);

		//查詢财務分類實體
		CriteriaType criteriaType = new CriteriaTypeImpl();
		String criteriaEntiy = "";
		//如果是财務預算類别
		if (type.equals("1")) {
			criteriaEntiy = "com.hkrsoft.ysglpt.financialBudgetDir.YsFinancialbudgetdir";
			//如果是業務預算類别
		} else if (type.equals("2")) {
			criteriaEntiy = "com.hkrsoft.ysglpt.operatingBudget.YsglObdir";
		}
		criteriaType.set_entity(criteriaEntiy);
		DataObject[] financialEntitys = DatabaseUtil
				.queryEntitiesByCriteriaEntity("default", criteriaType);
		Map resultMap = intersection(financialEntitys, hssfWorkbook);
		List resultList = (List) resultMap.get("resultList");

		if (resultMap.get("flag").equals(true)) {
			// 循環工作表Sheet  
			//從第二個sheet頁開始進行資料的寫入,到倒數第二個
			for (int numSheet = 1; numSheet < sheetNumber - 1; numSheet++) {
				HSSFSheet hssfSheet = hssfWorkbook.getSheetAt(numSheet);
				//建立實體
				DataObject logDetailEntity = DataObjectUtil
						.createDataObject("com.hkrsoft.ysglpt.ysbz.excelimort.ysbzexcel.YsYsbzYslogdetail");
				//設定實體主鍵
				DatabaseExt.getPrimaryKey(logDetailEntity);
				logDetailEntity.set("yslogid", logEntity.get("id"));
				//擷取sheet頁的sheetName，名稱
				hssfSheet.getSheetName();

				logDetailEntity.set("yslxname", hssfSheet.getSheetName());
				logDetailEntity.set("lineindex", numSheet);
				logDetailEntity.set("year", logEntity.get("year"));
				int rows = hssfSheet.getPhysicalNumberOfRows();
				logDetailEntity.set("rowcount", rows);
				//插入日志明細表
				DatabaseUtil.insertEntity("default", logDetailEntity);

				if (hssfSheet == null) {
					msg += "第" + (Integer.valueOf(numSheet) + 1)
							+ "個sheet頁，名為為空";
					break;
				}
				Map yslxMap = (Map) resultList.get(numSheet - 1);
				Object dircodeObj = yslxMap.get("dircode");
				Object excelstyleObj = yslxMap.get("excelstyle");
				String dircode = "";
				if (dircodeObj != null) {
					dircode = yslxMap.get("dircode").toString();
				}
				String excelstyle = "";
				if (excelstyleObj != null) {
					excelstyle = yslxMap.get("excelstyle").toString();
				}

				//導入到資料庫
				String error = getSheetContent(hssfSheet, numSheet, type,
						dircode, excelstyle, half);

				if (!"".equals(error) && !"null".equals(error)) {
					msg += "第" + (Integer.valueOf(numSheet) + 1) + "個sheet頁：";

				} else {
					error = "";
					//導入進度條的資料展示
					insertExcelPrecess(numSheet + 1, "success",
							(numSheet * 100) / (sheetNumber - 2), "第"
									+ (numSheet + 1) + "sheet頁資料導入成功！");
				}
				msg += error;
			}

		} else {
			Iterator iter1 = resultList.iterator();
			while (iter1.hasNext()) {
				Map obj = (Map) iter1.next();
				String numSheet = obj.get("numSheet").toString();
				String dirname = obj.get("dirname").toString();
				msg += "第" + (Integer.valueOf(numSheet) + 1) + "個sheet頁，名為"
						+ dirname + "，在系統中無配置";
			}

		}

		if ("".equals(msg)) {

			msg = "success";
		}
		return msg;

	}

	@SuppressWarnings("unchecked")
	private static Map intersection(DataObject[] financialEntitys,
			HSSFWorkbook hssfWorkbook) {
		Map resultMap = new HashMap();
		List<Map> sucessList = new ArrayList<Map>();
		List<Map> failList = new ArrayList<Map>();
		//首先得到兩個數組的長度
		int financialLen = financialEntitys.length;
		int sheetNumber = hssfWorkbook.getNumberOfSheets();

		if (financialEntitys == null || hssfWorkbook == null
				|| financialLen == 0 || sheetNumber == 0)
			return null;

		for (int numSheet = 1; numSheet < sheetNumber - 1; numSheet++) {
			HSSFSheet hssfSheet = hssfWorkbook.getSheetAt(numSheet);
			String sheetName = hssfSheet.getSheetName();
			Map<String, String> result = getDirInfo(financialEntitys, sheetName);
			// 如果能比對上
			if (result.get("flag").equals("true")) {
				result.put("numSheet", String.valueOf(numSheet));
				sucessList.add(result);
			} else {
				result.put("numSheet", String.valueOf(numSheet));
				result.put("dirname", sheetName);
				System.out.println(sheetName);
				failList.add(result);
			}
		}
		//如果完全比對
		if (sucessList.size() == sheetNumber - 2) {
			resultMap.put("flag", true);
			resultMap.put("resultList", sucessList);
		} else {
			resultMap.put("flag", false);
			resultMap.put("resultList", failList);
		}

		return resultMap;

	}

	/**
	 * 判斷是否比對
	 * @param financialEntitys
	 * @param sheetName
	 * @return
	 */
	private static Map<String, String> getDirInfo(
			DataObject[] financialEntitys, String sheetName) {
		Map<String, String> result = new HashMap<String, String>();
		boolean flag = false;
		String dircode = "";
		String dirname = "";
		String excelstyle = "";
		for (int i = 0; i < financialEntitys.length; i++) {
			DataObject financialEntity = financialEntitys[i];
			if (sheetName.contains(financialEntity.getString("dirname"))) {
				flag = true;
				dircode = financialEntity.getString("dircode");
				dirname = financialEntity.getString("dirname");
				excelstyle = financialEntity.getString("excelstyle");
				break;
			}
		}
		result.put("flag", String.valueOf(flag));
		result.put("dircode", dircode);
		result.put("dirname", dirname);
		result.put("excelstyle", excelstyle);
		return result;
	}

	@Bizlet("")
	private static String getSheetContent(HSSFSheet hssfSheet,
			int numSheetIndex, String type, String dircode, String excelstyle,
			String half) {
		String msg = "";
		//開始解析導入行
		int startRow = 4;
		//擷取整個行數
		int rows = hssfSheet.getPhysicalNumberOfRows();
		System.out.println("excel擷取行号rows=" + rows);
		//初始化sheet

		//每次資料導入現在100條
		if (rows > 10000) {
			msg = "導入資料不能超過10000條！";
		}
		//财務預算導入EXCEL
		String entityName = "";
		String fieldNames[] = {};
		Map entityMap = null;
		if (type.equals("1")) {
			entityMap = getCwEntityByDircode(dircode);
		} else {
			//業務預算導入EXCEL	
			entityMap = getYwEntityByDircode(dircode);
		}
		entityName = entityMap.get("entityName").toString();
		fieldNames = (String[]) entityMap.get("fieldNames");
		startRow = (Integer) entityMap.get("startRow");

		if (entityName != null && fieldNames.length > 0) {
			msg += excelToDatabase(hssfSheet, startRow, entityName, fieldNames,
					type, excelstyle, half);
		}
		return msg;

	}

	/**
	 * 通過dircode查詢财務預算分類對應實體和字段資訊
	 * @param dircode
	 * @return
	 */
	@SuppressWarnings("unchecked")
	private static Map getCwEntityByDircode(String dircode) {
		Map entityInfo = new HashMap();
		int code = Integer.valueOf(dircode);
		String entityName = "";
		String fieldNames[] = {};
		int startRow = 4;
		switch (code) {
		case 1:
			//開始行數
			startRow = 4;
			//實體字段，注意和EXCEL的列要對應
			String fieldNames1[] = { "zcfzxm", "lineno", "snyss", "sns",
					"bnyss", "zjl" };
			entityName = "com.hkrsoft.ysglpt.ysbz.excelimort.cwys.YsYsbzCwzcfz";
			fieldNames = fieldNames1;
			break;
		case 2:
			//開始行數
			startRow = 4;
			//實體字段，注意和EXCEL的列要對應
			String fieldNames2[] = { "pitem", "lineno", "lybudget", "lyno",
					"cybudget", "iodrate" };
			entityName = "com.hkrsoft.ysglpt.ysbz.excelimort.cwys.YsYsbzFinanceProfit";
			fieldNames = fieldNames2;
			break;
		case 3:
			//開始行數
			startRow = 4;
			//實體字段，注意和EXCEL的列要對應
			String fieldNames3[] = { "cfitem", "lineno", "lybudget", "lyno",
					"cybudget", "iodrate" };
			entityName = "com.hkrsoft.ysglpt.ysbz.excelimort.cwys.YsYsbzFinanceCfbudget";
			fieldNames = fieldNames3;
			break;
		case 4:
			//開始行數
			startRow = 3;
			//實體字段，注意和EXCEL的列要對應
			String fieldNames4[] = { "owneritem", "lineno", "lyno", "cybudget" };
			entityName = "com.hkrsoft.ysglpt.ysbz.excelimort.cwys.YsYsbzFinanceOwnerbudget";
			fieldNames = fieldNames4;
			break;
		case 5:
			//開始行數
			startRow = 6;
			//實體字段，注意和EXCEL的列要對應
			String fieldNames5[] = { "lineno", "inconame", "indconame",
					"inrate", "incost", "ybeginno", "netincome", "investment",
					"elsechange", "endno", "wrconame", "remark" };
			entityName = "com.hkrsoft.ysglpt.ysbz.excelimort.cwys.YsYsbzFinanceMininterest";
			fieldNames = fieldNames5;
			break;
	
		}
		entityInfo.put("entityName", entityName);
		entityInfo.put("fieldNames", fieldNames);
		entityInfo.put("startRow", startRow);
		return entityInfo;

	}

	/**
	 * 通過dircode查詢業務預算分類對應的實體和字段資訊
	 * @param dircode
	 * @return
	 */
	@SuppressWarnings("unchecked")
	private static Map getYwEntityByDircode(String dircode) {
		Map entityInfo = new HashMap();
		int code = Integer.valueOf(dircode);
		String entityName = "";
		String fieldNames[] = {};
		switch (code) {
		case 1:
			//按照順序
			String fieldNames1[] = { "LINENO", "DWMC", "XMMC", "CYLB",
					"TZLB", "NDJHTZE","YSHK" };
			entityName = "com.hkrsoft.ysglpt.ysbz.excelimort.ywys.YsYszbYwtzys";
			fieldNames = fieldNames1;
			break;
		//籌資預算資金運用
		case 2:
			//按照順序
			String fieldNames2[] = { "PROJECT", "LINENO", "TOTAL", "DQJK","JTNBTJ","DQRZJ","DQQT",
					"CQJK", "CQPJ","QYZ","RZZL","YXG","YXZ","ZCQQT","QTCZ","ZYZC","TZYSHJ","REMARK" };
			entityName = "com.hkrsoft.ysglpt.ysbz.excelimort.ywys.YsYszbYwczzjyy";
			fieldNames = fieldNames2;
			break;
		//籌資預算籌資來源
		case 3:
			//按照順序
			String fieldNames3[] = { "PROJECT", "LINENO", "SNS", "BNZJS","BNJSS","NMYSS","JZJE",
					"LXHJ", "FYH","ZBH","GXZC","SXFZC","CRZFYHJ"};
			entityName = "com.hkrsoft.ysglpt.ysbz.excelimort.ywys.YsYszbYwczczly";
			fieldNames = fieldNames3;
			break;
		
		
		}
		entityInfo.put("entityName", entityName);
		entityInfo.put("fieldNames", fieldNames);
		return entityInfo;

	}

	@Bizlet("")
	private static void deleteEntiyForExcel(String entityName, String type) {
		//建立模闆
		if (entityName != null && !entityName.equals("")) {
			DataObject templeEntity = DataObjectUtil
					.createDataObject(entityName);
			Calendar thisYear = Calendar.getInstance();
			templeEntity.set("year", thisYear.get(Calendar.YEAR));
			templeEntity.set("type", type);
			//删除滿足條件的記錄
			DatabaseUtil.deleteByTemplate("default", templeEntity);
		}
	}

	@Bizlet("")
	private static String excelToDatabase(HSSFSheet hssfSheet, int startRow,
			String entityName, String fieldNames[], String type,
			String excelstyle, String half) {
		if (excelstyle.trim().equals("1")) {
			return excel1ToDatabase(hssfSheet, startRow, entityName,
					fieldNames, type, half);
		} else if (excelstyle.trim().equals("2")) {
			return excel2ToDatabase(hssfSheet, startRow, entityName,
					fieldNames, type, half);
		} else {
			return excel1ToDatabase(hssfSheet, startRow, entityName,
					fieldNames, type, half);
		}
	}

	/**
	 * 單向樣式導入EXCEL方法
	 * @param hssfSheet
	 * @param startRow
	 * @param entityName
	 * @param fieldNames
	 * @param type
	 * @return
	 */
	@Bizlet("")
	private static String excel1ToDatabase(HSSFSheet hssfSheet, int startRow,
			String entityName, String fieldNames[], String type, String half) {
		String msg = "";
		//判斷是否存在，如果存在就先删除掉
		deleteEntiyForExcel(entityName, type);
		//循環行Row  
		for (int rowNum = startRow; rowNum < hssfSheet
				.getPhysicalNumberOfRows(); rowNum++) {
			HSSFRow hssfRow = hssfSheet.getRow(rowNum);
			if (hssfRow == null) {
				continue;
			}
			//建立實體
			DataObject importEntity = DataObjectUtil
					.createDataObject(entityName);
			//設定實體主鍵
			DatabaseExt.getPrimaryKey(importEntity);
			//設定是财務預算還是業務預算
			importEntity.set("type", type);
			//報送區間，1為上半年
			importEntity.set("bsqj", half);

			//得到年份
			Calendar thisYear = Calendar.getInstance();
			importEntity.set("year", thisYear.get(Calendar.YEAR));
			//定義中間變量
			String tempCellValue = "";

			//設定每個字段的值，按照每行的單元格循環查找資料
			for (int cellNum = 0; cellNum < fieldNames.length; cellNum++) {
				HSSFCell hcell = hssfRow.getCell(cellNum);

				hcell.setCellType(Cell.CELL_TYPE_STRING);
				boolean isMerge = ReadMergeRegionExcel.isMergedRegion(
						hssfSheet, rowNum, hcell.getColumnIndex());

				//判斷是否具有合并單元格
				if (isMerge) {
					tempCellValue = ReadMergeRegionExcel.getMergedRegionValue(
							hssfSheet, hssfRow.getRowNum(), hcell
									.getColumnIndex());

					System.out.print(tempCellValue + "  ");
				} else {
					tempCellValue = hcell.getRichStringCellValue().getString();
					System.out.print(hcell.getRichStringCellValue() + "  ");
				}
				importEntity.set(fieldNames[cellNum], tempCellValue); //指派 getCellValue(hcell)
			}
			System.out.println();

			try {
				DatabaseUtil.insertEntity("default", importEntity);
			} catch (Exception e) {
				e.printStackTrace();
				return "第" + (rowNum + 1) + "行資料出錯！<br>" + msg;
			}

		}
		return msg;
	}

	/**
	 * 雙向導入EXCEL方法
	 * 特殊sheet頁處理，兩次EXCEL導入，從 0到fieldNames.length,然後再從fieldNames.length*2
	 * @param hssfSheet
	 * @param startRow
	 * @param entityName
	 * @param fieldNames
	 * @param type
	 * @return
	 */
	@Bizlet("")
	private static String excel2ToDatabase(HSSFSheet hssfSheet, int startRow,
			String entityName, String fieldNames[], String type, String half) {
		String msg = "";
		//判斷是否存在，如果存在就先删除掉
		deleteEntiyForExcel(entityName, type);
		//列的基本數
		int len = fieldNames.length;
		//第二次導入的列的最大值
		int maxLen = len * 2;
		//循環行Row  
		for (int rowNum = startRow; rowNum < hssfSheet
				.getPhysicalNumberOfRows(); rowNum++) {
			HSSFRow hssfRow = hssfSheet.getRow(rowNum);
			if (hssfRow == null) {
				continue;
			}
			//建立實體
			DataObject importEntity = DataObjectUtil
					.createDataObject(entityName);
			//設定實體主鍵
			DatabaseExt.getPrimaryKey(importEntity);
			//設定是财務預算還是業務預算
			importEntity.set("type", type);
			//報送區間，2為上半年
			importEntity.set("bsqj", half);
			//得到年份
			Calendar thisYear = Calendar.getInstance();
			importEntity.set("year", thisYear.get(Calendar.YEAR));
			//設定每個字段的值，按照每行的單元格循環查找資料
			for (int cellNum = 0; cellNum < len; cellNum++) {
				HSSFCell hcell = hssfRow.getCell(cellNum);
				importEntity.set(fieldNames[cellNum], getCellValue(hcell)); //指派
			}

			try {
				DatabaseUtil.insertEntity("default", importEntity);
			} catch (Exception e) {
				e.printStackTrace();
				return "第" + (rowNum + 1) + "行資料出錯！<br>" + msg;
			}

		}

		//第二次導入
		for (int rowNum = startRow; rowNum < hssfSheet
				.getPhysicalNumberOfRows(); rowNum++) {
			HSSFRow hssfRow = hssfSheet.getRow(rowNum);
			if (hssfRow == null) {
				continue;
			}
			// 建立實體
			DataObject importEntity = DataObjectUtil
					.createDataObject(entityName);
			// 設定實體主鍵
			DatabaseExt.getPrimaryKey(importEntity);
			// 設定是财務預算還是業務預算
			importEntity.set("type", type);
			//報送區間，2為上半年
			importEntity.set("bsqj", half);
			// 得到年份
			Calendar thisYear = Calendar.getInstance();
			importEntity.set("year", thisYear.get(Calendar.YEAR));
			// 設定每個字段的值，按照每行的單元格循環查找資料
			for (int cellNum = len; cellNum < maxLen; cellNum++) {
				HSSFCell hcell = hssfRow.getCell(cellNum);
				importEntity
						.set(fieldNames[cellNum - len], getCellValue(hcell)); //指派
			}

			try {
				DatabaseUtil.insertEntity("default", importEntity);
			} catch (Exception e) {
				e.printStackTrace();
				return "第" + (rowNum + 1) + "行資料出錯！<br>" + msg;
			}

		}
		return msg;
	}

	/** 檢驗輸入是否為正确的日期格式(不含秒的任何情況),嚴格要求日期正确性,格式:yyyy-MM-dd
	 * @param sourceDate
	 * @return
	 */
	public static void insertExcelPrecess(int datamum, String nowrow,
			int datapre, String datamsg) {

		UserObject user = (UserObject) DataContextManager.current()
				.getMUODataContext().getUserObject();
		String userId = user.getUserId();
		DataObject obj = DataObjectUtil
				.createDataObject("com.hkr.spms.common.exceldc.excelManager.FigExportExcelInfo");
		obj.set("userid", userId);
		obj.set("datanum", datamum);
		obj.set("nowrow", nowrow);
		obj.set("datapre", datapre);
		obj.set("datamsg", datamsg);
		DatabaseUtil.saveEntity("default", obj);
	}

	/**
	 * 得到Excel表中的值  
	 * 
	 * @param cell
	 *         Excel中的每一個格子  
	 * @return 
	 *       根據單元格的類型來擷取相應類型的資料
	 */
	@Bizlet("")
	public static String getCellValue(HSSFCell cell) {
		String cellValue = "";
		if (cell == null)
			return cellValue;
		switch (cell.getCellType()) {
		case HSSFCell.CELL_TYPE_STRING:
			cellValue = cell.getStringCellValue().trim();
			break;
		case HSSFCell.CELL_TYPE_NUMERIC:
			double tempCellValue = cell.getNumericCellValue();
			cellValue = tempCellValue + "";
			break;
		case HSSFCell.CELL_TYPE_BOOLEAN:
			cellValue = String.valueOf(cell.getBooleanCellValue());
			break;
		case HSSFCell.CELL_TYPE_FORMULA:
			cellValue = cell.getCellFormula();
			break;
		default:
			cellValue = "";
		}
		return cellValue;
	}
}