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);//有多少个sheetint 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条！";}//财务预算导入EXCELString entityName = "";String fieldNames[] = {};Map entityMap = null;if (type.equals("1")) {entityMap = getCwEntityByDircode(dircode);} else {//业务预算导入EXCELentityMap = 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;}}