JAva excel読み出し
11916 ワード
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;
}
}