关于多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);
DataObj