impexceltodoc.java

OBPM是一个开源

package cn.myapps.core.dynaform.dts.excelimport.config;

import java.io.BufferedInputStream;
import java.io.File;
import java.io.FileInputStream;
import java.io.InputStream;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Collection;
import java.util.HashMap;
import java.util.Iterator;
import java.util.LinkedHashMap;
import java.util.Map;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;

import cn.myapps.base.action.ParamsTable;
import cn.myapps.base.dao.PersistenceUtils;
import cn.myapps.core.dynaform.document.ejb.Document;
import cn.myapps.core.dynaform.document.ejb.DocumentProcess;
import cn.myapps.core.dynaform.dts.excelimport.AbstractSheet;
import cn.myapps.core.dynaform.dts.excelimport.Column;
import cn.myapps.core.dynaform.dts.excelimport.DetailSheet;
import cn.myapps.core.dynaform.dts.excelimport.ExcelMappingDiagram;
import cn.myapps.core.dynaform.dts.excelimport.Factory;
import cn.myapps.core.dynaform.dts.excelimport.LinkageKey;
import cn.myapps.core.dynaform.dts.excelimport.MasterSheet;
import cn.myapps.core.dynaform.dts.excelimport.config.ejb.IMPMappingConfigProcess;
import cn.myapps.core.dynaform.dts.excelimport.config.ejb.IMPMappingConfigVO;
import cn.myapps.core.dynaform.form.ejb.Form;
import cn.myapps.core.dynaform.form.ejb.FormField;
import cn.myapps.core.dynaform.form.ejb.FormProcess;
import cn.myapps.core.dynaform.form.ejb.ValidateMessage;
import cn.myapps.core.user.action.WebUser;
import cn.myapps.core.user.ejb.UserVO;
import cn.myapps.util.ProcessFactory;
import cn.myapps.util.sequence.Sequence;

public class ImpExcelToDoc {
	private HSSFWorkbook _workBook;

	private ExcelMappingDiagram _mappingConfig;

	public ImpExcelToDoc(InputStream is, ExcelMappingDiagram mapping)
			throws Exception {
		_workBook = new HSSFWorkbook(is);
		_mappingConfig = mapping;
	}

	public ImpExcelToDoc(String excelPath, ExcelMappingDiagram mapping)
			throws Exception {
		File file = new File(excelPath);
		FileInputStream fis = new FileInputStream(file);
		BufferedInputStream bis = new BufferedInputStream(fis);
		// _workBook = Workbook.getWorkbook(bis);

		_workBook = new HSSFWorkbook(bis);// 建立新HSSFWorkbook对象

		_mappingConfig = mapping;

	}

	/**
	 * @param args
	 * @throws Exception
	 */
	public static void main(String[] args) throws Exception {

		IMPMappingConfigProcess proxy = (IMPMappingConfigProcess) ProcessFactory
				.createProcess(IMPMappingConfigProcess.class);
		IMPMappingConfigVO vo = (IMPMappingConfigVO) proxy
				.doView("3afa446a-7fcc-4d7e-8b15-957bd96ee329");
		ExcelMappingDiagram em = Factory.trnsXML2Dgrm(vo.getXml());

		ImpExcelToDoc iet = new ImpExcelToDoc("C:\\test.xls", em);
		// iet.CreatDocument(new WebUser(new UserVO()));
	}

	public Map getMasterSheetRow(int row) {
		HashMap map = new HashMap();
		MasterSheet ms = _mappingConfig.getMasterSheet();

		HSSFSheet sheet = _workBook.getSheet(ms.name);
		HSSFRow sheetHeads = sheet.getRow(0);
		HSSFRow rowCells = sheet.getRow(row);

		boolean flag = false;
		for (short i = 0; i < sheetHeads.getLastCellNum(); i++) {
			if (sheetHeads.getCell(i) != null
					&& getCellStringValue(sheetHeads.getCell(i)) != null) {
				if (!map.containsKey(getCellStringValue(sheetHeads.getCell(i)))) {
					if (rowCells != null
							&& rowCells.getCell(i) != null
							&& rowCells.getCell(i).getCellType() != HSSFCell.CELL_TYPE_BLANK) {
						map.put(getCellStringValue(sheetHeads.getCell(i)),
								getCellStringValue(rowCells.getCell(i)));
						flag = true;

					} else {
						map
								.put(getCellStringValue(sheetHeads.getCell(i)),
										null);
					}
				}
			}
		}
		return flag ? map : null;

	}

	public int getMasterSheetRowCount() {
		MasterSheet ms = _mappingConfig.getMasterSheet();

		HSSFSheet sheet = _workBook.getSheet(ms.name);

		return sheet.getLastRowNum() + 1;
	}

	public Map getDetailSheetValueList(String sheetName, String columnName,
			String matchValue) {
		HSSFSheet sheet = _workBook.getSheet(sheetName);
		HSSFRow sheetHeads = sheet.getRow(0);

		LinkedHashMap map = new LinkedHashMap();
		for (short row = 1; row < sheet.getLastRowNum(); row++) {

			HSSFRow rowCells = sheet.getRow(row);

			for (short i = 0; i < sheetHeads.getLastCellNum(); i++) {
				if (sheetHeads.getCell(i) != null
						&& getCellStringValue(sheetHeads.getCell(i)) != null) {

					if (!columnName.equals(getCellStringValue(sheetHeads
							.getCell(i))))
						continue;

					if (matchValue != null
							&& matchValue.equals(getCellStringValue(rowCells
									.getCell(i)))) {

						for (short k = 0; k < sheetHeads.getLastCellNum(); k++) {
							String content = (String) map
									.get(getCellStringValue(sheetHeads
											.getCell(k)));

							String value = getCellStringValue(rowCells
									.getCell(k));
							if (value == null || value.length() <= 0) {
								value = " ";
							}

							if (content != null) {
								content += value + ";";
							} else {
								content = value + ";";
							}

							map.put(getCellStringValue(sheetHeads.getCell(k)),
									content);

						}
					}

				}
			}

		}
		return map;
	}

	public Collection getDetailSheetRowCollection(String sheetName,
			String columnName, String matchValue) throws Exception {
		ArrayList list = new ArrayList();
		HSSFSheet sheet = _workBook.getSheet(sheetName);
		HSSFRow sheetHeads = sheet.getRow(0);

		for (int row = 1; row < sheet.getLastRowNum() + 1; row++) {
			try {
				HSSFRow rowCells = sheet.getRow(row);

				for (short i = 0; i < sheetHeads.getLastCellNum(); i++) {
					try {
						if (sheetHeads.getCell(i) != null
								&& getCellStringValue(sheetHeads.getCell(i)) != null) {

							if (!columnName
									.equals(getCellStringValue(sheetHeads
											.getCell(i))))
								continue;

							HSSFCell cell = null;
							try {
								cell = rowCells.getCell(i);
							} catch (Exception e) {
								continue;
							}
							if (matchValue == null || cell == null
									|| getCellStringValue(cell) == null
									|| getCellStringValue(cell).length() <= 0) {
								continue;

							}

							if (matchValue.trim().equals(
									getCellStringValue(rowCells.getCell(i))
											.trim())) {

								LinkedHashMap map = new LinkedHashMap();
								for (short k = 0; k < sheetHeads
										.getLastCellNum(); k++) {

									String value = getCellStringValue(rowCells
											.getCell(k));
									if (value == null || value.length() <= 0) {
										value = "";
									}

									map.put(getCellStringValue(sheetHeads
											.getCell(k)), value);

								}
								list.add(map);
							}

						}

					} catch (Throwable e) {
						e.printStackTrace();
						throw new Exception("第 " + (i + 1) + " 列->"
								+ e.getMessage());
					}
				}

			} catch (Throwable e) {
				throw new Exception(sheetName + " 第 " + (row + 1) + " 行->"
						+ e.getMessage());
			}

		}
		return list;
	}

	private Map transExcelValueList2Params(AbstractSheet sheet, Map valueList) {
		HashMap map = new HashMap();
		Iterator iter = sheet.getColumns().iterator();
		while (iter.hasNext()) {
			Column clm = (Column) iter.next();
			if (clm.name != null && clm.fieldName != null) {
				if (sheet instanceof MasterSheet) {
					map.put(clm.fieldName, valueList.get(clm.name));
				} else if (sheet instanceof DetailSheet) {
					map.put(clm.fieldName, valueList.get(clm.name));
				}
			}
		}

		return map;
	}

	// private Map appendDocItemValue2Params(AbstractSheet sheet, Document
	// subdoc,
	// Map params) throws Exception {
	// Iterator iter = sheet.getColumns().iterator();
	// while (iter.hasNext()) {
	// Column clm = (Column) iter.next();
	// if (clm.name != null && clm.fieldName != null) {
	// String value = (String) params.get(clm.fieldName + "$");
	// if (value != null) {
	// value += subdoc.getItemValueAsString(clm.fieldName) + ";";
	// } else {
	// value = subdoc.getItemValueAsString(clm.fieldName) + ";";
	// }
	// params.put(clm.fieldName + "$", value);
	// }
	// }
	//
	// return params;
	// }

	public String CreatDocument(WebUser user) throws Exception {
		// //PersistenceUtils.getSessionSignal().sessionSignal++;
		FormProcess proxy = (FormProcess) ProcessFactory
				.createProcess(FormProcess.class);
		DocumentProcess docproxy = (DocumentProcess) ProcessFactory
				.createProcess(DocumentProcess.class);

		int rowCount = getMasterSheetRowCount();

		ArrayList parentDocList = new ArrayList();
		ArrayList subDocList = new ArrayList();

		Form form = (Form) proxy.doViewByFormName(_mappingConfig
				.getMasterSheet().formName, user.getApplicationid());

		form = rebuildFormFieldProperty(_mappingConfig.getMasterSheet(), form);
		Map errorField = null;
		boolean flag = false; // 标志MasterSheet出错还是detialSheet出错
		for (int i = 1; i < rowCount; i++) {
			try {
				flag = false;
				errorField = new HashMap();
				ParamsTable parentParams = new ParamsTable();

				Map values = getMasterSheetRow(i);
				if (values == null)
					continue;

				Map fieldValues = transExcelValueList2Params(_mappingConfig
						.getMasterSheet(), values);

				parentParams.putAll(fieldValues);

				Document parentDoc = form.createDocument(parentParams, user); // 创建主Document
				parentDoc.setId(Sequence.getSequence());
				parentDoc.setIstmp(false);
				parentDoc.setApplicationid(user.getApplicationid());
				// 对主文档的正确性进行校验
				Collection errs = form.validate(parentDoc, parentParams, user,
						false);
				if (errs != null && errs.size() > 0) {
					String msgStr = "";
					Iterator iter4 = errs.iterator();
					while (iter4.hasNext()) {
						ValidateMessage err = (ValidateMessage) iter4.next();
						System.out.println("err->" + err.getErrmessage());
						msgStr += "\n" + err.getFieldname() + "---"
								+ err.getErrmessage();
					}
					flag = true;
					errorField.putAll(fieldValues);
					throw new Exception(msgStr);
				}
				parentDocList.add(parentDoc);

				Iterator iter = _mappingConfig.getLinkageKeys().iterator();
				while (iter.hasNext()) {

					LinkageKey key = (LinkageKey) iter.next();

					String sheetName = key.getDetailSheet().name;
					String columnName = key.getDetailSheetKeyColumn().name;
					String matchValue = (String) values.get(key
							.getMasterSheetKeyColumn().name);

					Collection detailRows = getDetailSheetRowCollection(
							sheetName, columnName, matchValue);
					int detailRow = 0;
					try {
						Iterator iter2 = detailRows.iterator();
						while (iter2.hasNext()) {
							Form subform = (Form) proxy.doViewByFormName(key
									.getDetailSheet().formName, user
									.getApplicationid());
							subform = rebuildFormFieldProperty(key
									.getDetailSheet(), subform);

							Map tmp = transExcelValueList2Params(key
									.getDetailSheet(), (Map) iter2.next());
							ParamsTable subParams = new ParamsTable();
							subParams.putAll(tmp);
							Document subdoc = subform.createDocument(subParams,
									user);
							subdoc.setParent(parentDoc); // 设置parent Document
							subdoc.setId(Sequence.getSequence());
							subdoc.setIstmp(false);

							// 对子文档的正确性进行校验
							errs = subform.validate(subdoc, subParams, user,
									false);
							if (errs != null && errs.size() > 0) {
								String msgStr = "";
								Iterator iter4 = errs.iterator();
								while (iter4.hasNext()) {
									ValidateMessage err = (ValidateMessage) iter4
											.next();
									System.out.println("err->"
											+ err.getErrmessage());
									msgStr += "\n" + err.getFieldname() + "---"
											+ err.getErrmessage();
								}
								errorField.putAll(tmp);
								throw new Exception(msgStr);
							}
							subDocList.add(subdoc);
							detailRow++;
						}

					} catch (Throwable t) {
						throw new Exception(sheetName + " 第 " + (detailRow + 1)
								+ " 行出错！" + t.getMessage());
					}
				}
			} catch (Throwable e) {
				e.printStackTrace();
				String msg = "";
				if (flag)
					msg = _mappingConfig.getMasterSheet().formName + " 倒入第" + i
							+ "行时出错![" + e.getMessage() + "] :";
				else
					msg = e.getMessage() + "  :";
				for (Iterator iter = errorField.keySet().iterator(); iter
						.hasNext();) {
					String fieldName = (String) iter.next();
					String value = (String) errorField.get(fieldName);
					msg += fieldName + "  " + value + ",";
				}
				throw new Exception(msg);
			}
		}

		ArrayList allDocs = new ArrayList();
		allDocs.addAll(parentDocList);
		allDocs.addAll(subDocList);
		docproxy.doUpdate(allDocs);
		// //PersistenceUtils.getSessionSignal().sessionSignal--;
		// PersistenceUtils.closeSession();
		String msg = "Success! 一共导入了" + parentDocList.size()
				+ "个MasterDocuemnt和" + subDocList.size() + "个SubDocument";
		return msg;
	}

	private Form rebuildFormFieldProperty(AbstractSheet sheet, Form form) {
		Iterator iter = sheet.getColumns().iterator();
		while (iter.hasNext()) {
			Column clm = (Column) iter.next();
			FormField field = form.findField(clm.fieldName);

			// 替换 值脚本
			if (field != null && clm.valueScript != null
					&& clm.valueScript.trim().length() > 0) {
				field.setValueScript(clm.valueScript);
			}

			// 替换 校验
			if (field != null && clm.validateRule != null
					&& clm.validateRule.trim().length() > 0) {
				field.setValidateRule(clm.validateRule);
			}

		}
		return form;
	}

	private String getCellStringValue(HSSFCell cell) {
		try {
			switch (cell.getCellType()) {
			case HSSFCell.CELL_TYPE_BLANK:
				return "";
			case HSSFCell.CELL_TYPE_BOOLEAN:
				return cell.getBooleanCellValue() + "";
			case HSSFCell.CELL_TYPE_ERROR:
				return "error";
			case HSSFCell.CELL_TYPE_FORMULA:
				return cell.getCellFormula();
			case HSSFCell.CELL_TYPE_NUMERIC:

				if (HSSFDateUtil.isCellDateFormatted(cell)) {
					SimpleDateFormat formate = new SimpleDateFormat(
							"yyyy-MM-dd");
					return formate.format(cell.getDateCellValue());
				} else {
					double d = cell.getNumericCellValue();
					if (((int) d) == d) {
						return ((int) d) + "";
					} else {
						return d + "";
					}
				}

			case HSSFCell.CELL_TYPE_STRING:
				return cell.getStringCellValue().trim();

			default:
				return "";
			}
		} catch (Exception e) {
			return null;
		}
	}

}