package com.icss.mdm.maintain.data;
import java.io.BufferedReader;
import java.io.FileInputStream;
import java.io.FileReader;
import java.io.IOException;
import java.io.InputStream;
import java.lang.reflect.Field;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import org.apache.commons.beanutils.BeanUtils;
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 com.icss.mdm.vo.MdmCodepropVO;
import com.icss.pangu.db.DBBeanBase;
import com.icss.pangu.logging.Log;
import com.icss.pangu.logging.LogFactory;
public class ReadExcel {
private static Log log = LogFactory.getLog(ReadTxt.class);
public static Object[] getExcelList(String id, DBBeanBase dbBase, String roleCode,String status, String ignore, Object[] addTag,String filePath,String personName) {
int count = 1;
int propLength = 0;
String codeID = "";
String code = "";
String value = "";
String s1 = null;
FileReader reader = null;
FileReader reader1 = null;
BufferedReader br = null;
BufferedReader br1 = null;
List list = new ArrayList();
String[] columnValue = null;
String[] propValue = null;
String[] codeValue = new String[50];
int row = 0 ;
// 记录应该添加几次
int timer = 0 ;
// 记录是第几次执行添加
int timers = 0;
// 记录如果不能被整除的时候最后一次是多少条数据
int timecount = 0 ;
try {
InputStream is = new FileInputStream(filePath);
HSSFWorkbook wb = new HSSFWorkbook(new POIFSFileSystem(is));
HSSFSheet sheet = wb.getSheetAt(0);
int firstRowNum = sheet.getFirstRowNum();
int lastRowNum = sheet.getLastRowNum();
// 取得文件行数
row = lastRowNum + 1;
if((row - 3)% ErrorTypes.NUM_COUNT == 0)
timer = (row - 3) / ErrorTypes.NUM_COUNT;
else{
timer = (row - 3) / ErrorTypes.NUM_COUNT + 1;
timecount = (row - 3 )% ErrorTypes.NUM_COUNT;
}
// 读取文件
HSSFCell cell = null;
for ( int rowIndex = firstRowNum; rowIndex <= lastRowNum; rowIndex++ ) {
Map map = null;
HSSFRow currentRow = sheet.getRow(rowIndex);
// 第一行放表名
if (rowIndex == 0) {
cell = currentRow.getCell((short)0);
codeID = cell.getStringCellValue();
if (!codeID.equals(id)) {
((Map)addTag[2]).put("1",ErrorTypes.ID_ERROR);
return addTag;
}
//第二行放属性中文名称
} else if (rowIndex == 1) {
//第三行放属性
} else if (rowIndex == 2) {
columnValue = new String[sheet.getRow(rowIndex).getPhysicalNumberOfCells()];
for (int i = 0; i < sheet.getRow(rowIndex).getPhysicalNumberOfCells(); i++) {
if ("".equals(currentRow.getCell((short)i))) {
((Map)addTag[2]).put("2",ErrorTypes.PROP_NULL);
return addTag;
} else {
columnValue[i] = (currentRow.getCell((short)i).getStringCellValue());
}
}
//TODO 字段名转换为属性名
propValue = getQueryList(id,dbBase,columnValue,sheet.getRow(rowIndex).getPhysicalNumberOfCells());
//TODO
propLength = propValue.length;
// 验证属性
if (!JudgeValidate.judgeProp(propValue)) {
((Map) addTag[2]).put("2", ErrorTypes.PROP_NAME_ERROR);
return addTag;
}
} else {
codeValue = new String[sheet.getRow(rowIndex).getPhysicalNumberOfCells()];
for (int i = 0; i < sheet.getRow(rowIndex).getPhysicalNumberOfCells(); i++) {
try{
codeValue[i] = currentRow.getCell((short)i).getStringCellValue();
}catch(Exception e){
codeValue[i] = currentRow.getCell((short)i).getNumericCellValue()+"";
}
}
// 属性与相应的值不对应
if (propLength < codeValue.length) {
((Map)addTag[2]).put((new Integer(count)).toString(),ErrorTypes.VALUE_LENGTH_MATCH);
if(ignore.equals("0")){
continue;
}else{
return addTag;
}
}
map = new HashMap();
for (int i = 0; i < propValue.length; i++) {
// 把属性对应的value放到map中
if (map.get(propValue[i]) == null) {
map.put(propValue[i], codeValue[i].trim());
} else {
((Map)addTag[2]).put("2",ErrorTypes.PROP_REPEAT);
return addTag;
}
}
list.add(map);
}
if((count - 3) % ErrorTypes.NUM_COUNT != 0 && count != 3){
addTag = JudgeValidate.judgeValue(list, dbBase, codeID, roleCode, status, ignore, addTag,personName,"xls",timers);
list.removeAll(list);
timers++;
// 如果本次执行出错，且不忽略错误就直接返回
if(((Map)addTag[2]).size() != 0 && "1".equals(ignore))
return addTag;
}else if((count - 3) % ErrorTypes.NUM_COUNT != timecount && (timers + 1 == timer)){
//timer记录应该添加几次 timers = 0 记录是第几次执行添加timecount 记录如果不能被整除的时候最后一次是多少条数据
addTag = JudgeValidate.judgeValue(list, dbBase, codeID, roleCode, status, ignore, addTag,personName,"xls",timers);
list.removeAll(list);
}
count++;
}
return addTag;
} catch (Exception e) {
log.error(e);
return addTag;
}finally{
try {
if(br!=null)
br.close();
if(br1!=null)
br1.close();
if(reader!=null)
reader.close();
if(reader1!=null)
reader1.close();
} catch (IOException e) {
log.error(e);
}
}
}
/**
* 字段名称转化为属性名称
* @param id
* @param dbBase
* @param columnValue
* @param maxCols
* @return
* @throws Exception
*/
public static String[] getQueryList(String id, DBBeanBase dbBase, String[] columnValue,int maxCols) throws Exception {
List list = new ArrayList();
String[] aStr = new String[columnValue.length];
Statement state = dbBase.getPrivateCon().createStatement();
// ResultSet rs = null;
String selectProp = "";
selectProp ="SELECT COL_SEQUENCE,COL_NAME FROM MDM_CODEPROP WHERE CODE_ID = '" + id.trim() + "'";
/*for (int i = 0; i < columnValue.length; i++) {
selectProp ="SELECT COL_SEQUENCE FROM MDM_CODEPROP WHERE CODE_ID = '" + id.trim() + "' AND COL_NAME = '" + columnValue[i] + "'";
rs = state.executeQuery(selectProp);
}*/
ResultSet rs = state.executeQuery(selectProp);
list = populate(rs,MdmCodepropVO.class);
//用columnValue匹配结果集COL_NAME，取得COL_SEQUENCE
//转换成PropXX（COL_SEQUENCE<10? "Prop0"+String.valueof(COL_SEQUENCE):"Prop"+String.valueof(COL_SEQUENCE)）
//转换结果以String[]方式返回
MdmCodepropVO mdmCodeprop = null;
for (int m = 0; m < columnValue.length; m++) {
for (int i = 0; i < list.size(); i++) {
mdmCodeprop = new MdmCodepropVO();
mdmCodeprop = (MdmCodepropVO)list.get(i);
if (columnValue[m].equalsIgnoreCase(mdmCodeprop.getColName())) {
if (mdmCodeprop.getColSequence() < 10){
aStr[m] = "Prop0" + mdmCodeprop.getColSequence().toString();
} else {
aStr[m] = "Prop" + mdmCodeprop.getColSequence().toString();
}
}
}
}
if(log.isDebugEnabled()){
log.debug("selectPropSql:"+selectProp);
}
return aStr;
}
/***********把resultset结果集转化为list集合************/
public static List populate(ResultSet rs, Class clazz) throws Exception {
ResultSetMetaData metaData = rs.getMetaData(); // 取得结果集的元元素
int colCount = metaData.getColumnCount(); // 取得所有列的个数
List ret = new ArrayList(); // 存放返回结果的容器
Field[] fields = clazz.getDeclaredFields(); // 取得业务对象的属性
while (rs.next()) {
Object newInstance = clazz.newInstance(); // 构造业务对象实例
// 将结果集中每一条记录，每一个字段取出，根据命名规则，对对应的业务对象的属性进行赋值
for (int i = 1; i <= colCount; i++) { // 对于该记录的每一列
try {
Object value = rs.getObject(i);
for (int j = 0; j < fields.length; j++) {
Field f = fields[j];
if (f.getName().equalsIgnoreCase(metaData.getColumnName(i).replaceAll("_", ""))) {
BeanUtils.copyProperty(newInstance, f.getName(), value);
}
}
} catch (Exception e) {
// TODO: handle exception
e.printStackTrace();
}
}
ret.add(newInstance);
}
return ret;
}
}