package XXXXX.manage.importexcel; import java.beans.IntrospectionException;
import java.io.BufferedReader;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.InputStreamReader;
import java.io.UnsupportedEncodingException;
import java.lang.reflect.InvocationTargetException;
import java.lang.reflect.Method;
import java.sql.Connection;
import java.sql.SQLException;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.HashMap;
import java.util.Iterator;
import java.util.Map;
import java.util.logging.Logger; import org.apache.commons.betwixt.io.BeanReader;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.xml.sax.SAXException; import XXXXX.cache.UserSession;
import xxxxxxxx.manage.importerror.BN_ImportError;
import xxxxxxxx.manage.importerror.DAO_ImportError;
import xxxxxxxx.supp.BN_Row;
import xxxxxxxx.supp.DAORunner;
import xxxxxxxx.supp.TransactionHandler;
import xxxxxxxx.util.ObjectUtil;
import xxxxxxxx.util.PathUtil;
import xxxxxxxx.util.StringUtil; public class DAO_ImportExcel { private String tableImport; //导入的临时表名
private String tableTarget; //导入的目标表名
private File excelFile; //excel数据文件
private int rows; //总行数
private int columns; //总列数
private Map<String, BN_Column> columnMap = new HashMap();// 列序号、字段定义映射
private String[] columnName; // excel列名 private BN_ImportExcel xmlBean;
private Map<String, BN_Column> excelColumn; // 列名称、字段定义映射
private Map<String, BN_Column> systemColumn;// 系统内置字段定义 Map<String, Object> resultMap = new HashMap<String, Object>();// 定义返回结果map private static Logger logger = Logger.getLogger(DAO_ImportExcel.class
.getName()); public DAO_ImportExcel(String tableTarget, File excelFile) {
this.tableTarget = tableTarget;
this.tableImport = tableTarget + "_import"; //导入临时表，固定以"_import"结尾
this.excelFile = excelFile;
loadXmlBean();
} private void loadXmlBean() {
BeanReader beanReader = new BeanReader();
// 配置BeanReader实例
beanReader.getXMLIntrospector().getConfiguration()
.setAttributesForPrimitives(false);
beanReader.getBindingConfiguration().setMapIDs(false); // 不自动生成ID
// 注册要转换对象的类，并指定根节点名称
BufferedReader br = null;
try {
beanReader.registerBeanClass("importExcel", BN_ImportExcel.class);
br = new BufferedReader(new InputStreamReader(
new FileInputStream(new File(
PathUtil.getPath("/WEB-INF/excel/" + tableImport + ".xml"))), "GBK"));
// 将XML解析Java Object
xmlBean = (BN_ImportExcel) beanReader.parse(br);
excelColumn = xmlBean.getExcelColumn();
systemColumn = xmlBean.getSystemColumn();
} catch (UnsupportedEncodingException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (FileNotFoundException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (IntrospectionException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (IOException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (SAXException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} finally {
if (br != null){
try {
br.close();
} catch (IOException e) {
}
}
}
} public Map doImport() {
Workbook workBook = null;
try {
workBook = new XSSFWorkbook(new FileInputStream(excelFile));
} catch (Exception ex) {
try {
workBook = new HSSFWorkbook(new FileInputStream(excelFile));
} catch (FileNotFoundException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (IOException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
if (workBook == null){
resultMap.put("result", false);
resultMap.put("message", "导入失败!无法加载该数据文件，请检查文件格式是否正确.");
return resultMap;
}
Sheet sheet = workBook.getSheetAt(0);
rows = sheet.getPhysicalNumberOfRows();
columns = sheet.getRow(0).getPhysicalNumberOfCells();
logger.info("rows=" + rows + " columns=" + columns);
if (rows <= 1 || columns == 0 ){
resultMap.put("result", true);
resultMap.put("error", false);
resultMap.put("message", "系统没有检测到需要导入的数据内容!");
return resultMap;
}
columnName = new String[columns]; //执行数据操作之前的初始化脚本initSQL
boolean initResult = runXMLSQL(xmlBean.getInitSQL());
if (!initResult){
resultMap.put("result", false);
resultMap.put("message", "系统初始化失败，请检查配置文件'"+tableImport+".xm'的initSQL是否正确!");
return resultMap;
} // 解析第一行表头列名
Iterator cellIterator = sheet.getRow(0).cellIterator();
StringBuffer memo = new StringBuffer();
while (cellIterator.hasNext()) {
Cell cell = (Cell) cellIterator.next();
// 读取当前单元格的值
String cellValue = cell.getStringCellValue();
logger.info("cell[0," + cell.getColumnIndex() + "]=" + cellValue);
columnName[cell.getColumnIndex()] = cellValue;
if (excelColumn.containsKey(cellValue)) {
columnMap.put(cell.getColumnIndex() + "",
(BN_Column) excelColumn.get(cellValue));
} else {
memo.append("列名[").append(cellValue).append("] ");
logger.info("cell[0," + cell.getColumnIndex() + "]列名["
+ cellValue + "]" + "未在xml的<excelColumn>节点内定义!无法解析导入");
}
}
if (memo.length() > 0) {// 有未在xml的<excelColumn>节点内定义的列
BN_ImportError errorBean = new BN_ImportError();
errorBean.setOp_id(StringUtil.getUUID());
errorBean.setOp_name(tableTarget);
errorBean.setRow_no("1");
errorBean.setMemo(memo.toString()+" 未在xml配置文件中定义，无法解析导入!");
errorBean.setCreate_id(UserSession.getOpId());
errorBean.setCreate_name(UserSession.getOpName());
errorBean.setCreate_time(BN_Row.getNowInput());
DAO_ImportError.doInsert(errorBean, null);
} // 解析数据行(从第2行开始)
for (int rowNum = 1; rowNum < rows; rowNum++) {
cellIterator = sheet.getRow(rowNum).cellIterator();
memo = new StringBuffer();
BN_Row rowBean = new BN_Row();
// 处理表格数据
while (cellIterator.hasNext()) {
Cell cell = (Cell) cellIterator.next();
String cellValue = getCellValue(cell);
logger.info("cell[" + rowNum + "," + cell.getColumnIndex() + "]=" + cellValue);
if (columnMap.containsKey(cell.getColumnIndex() + "")) {// 列名有在xml中有定义
BN_Column columnBean = columnMap.get(cell.getColumnIndex() + "");
Object[] parseResult = parseCell(columnBean, rowNum,
columnName[cell.getColumnIndex()], cellValue);
if (parseResult[0] != null) {
rowBean.setColumnValue(columnBean.getColumn(), parseResult[0]);
} else {
if (parseResult[1] != null) {
memo.append(parseResult[1]);
}
}
}
}
if (memo.length() > 0) {// 有不符合内容格式要求的字段
logger.info("第["+(rowNum + 1)+"行] "+memo.toString());
BN_ImportError errorBean = new BN_ImportError();
errorBean.setOp_id(StringUtil.getUUID());
errorBean.setOp_name(tableTarget);
errorBean.setRow_no((rowNum + 1) + "");
errorBean.setMemo(memo.toString());
errorBean.setCreate_id(UserSession.getOpId());
errorBean.setCreate_name(UserSession.getOpName());
errorBean.setCreate_time(BN_Row.getNowInput());
DAO_ImportError.doInsert(errorBean, null);
} else {
// 系统级字段
Iterator iterator = systemColumn.keySet().iterator();
while (iterator.hasNext()) {
String key = iterator.next().toString();
BN_Column sysColumnBean = systemColumn.get(key);
Object result = transfer(sysColumnBean.getDataType(), "",
sysColumnBean.getClassName(),
sysColumnBean.getMethod(), sysColumnBean.getParam());
if (result != null) {
rowBean.setColumnValue(sysColumnBean.getColumn(),
result);
}
}
int result = DAORunner.insertRow(tableImport, rowBean);
if (result == -1){//保存临时表失败，登记错误信息
BN_ImportError errorBean = new BN_ImportError();
errorBean.setOp_id(StringUtil.getUUID());
errorBean.setOp_name(tableTarget);
errorBean.setRow_no((rowNum + 1) + "");
errorBean.setMemo("数据保存失败");
errorBean.setCreate_id(UserSession.getOpId());
errorBean.setCreate_name(UserSession.getOpName());
errorBean.setCreate_time(BN_Row.getNowInput());
DAO_ImportError.doInsert(errorBean, null);
}
}
}
//计算自己导入成功的数据条数
int dataSuccNum = DAORunner.count("select count(*) from "+tableImport+" where create_id=?",
new Object[]{UserSession.getOpId()});
//计算自己文件解析失败的数据条数，排除首行列名解析问题
int dataFailNum = DAORunner.count("select count(*) from t_import_error where create_id=? and row_no != ?",
new Object[]{UserSession.getOpId(), 1});
int columnFailNum = DAORunner.count("select count(*) from t_import_error where create_id=? and row_no = ?",
new Object[]{UserSession.getOpId(), 1});
boolean result = DAORunner.runTransaction(new TransactionHandler() {
public void process(Connection connections) throws SQLException {
//执行数据转移前的脚本 beforeSaveSQL
if (!ObjectUtil.isNull(xmlBean.getBeforeSaveSQL())){
String[] sqlArr = xmlBean.getBeforeSaveSQL().split(";");
for (int i = 0 ; i < sqlArr.length ; i ++){
DAORunner.runUpdate(sqlArr[i].replaceAll("\\$\\{userId}", UserSession.getOpId()),
new Object[]{}, connections);
}
} //转移数据至tableTarget
DAORunner.runUpdate("insert into "+tableTarget+" select * from "+tableImport+" where create_id=?",
new Object[]{UserSession.getOpId()}, connections); //执行数据转移后的脚本 afterSaveSQL
if (!ObjectUtil.isNull(xmlBean.getBeforeSaveSQL())){
String[] sqlArr = xmlBean.getAfterSaveSQL().split(";");
for (int i = 0 ; i < sqlArr.length ; i ++){
DAORunner.runUpdate(sqlArr[i].replaceAll("\\$\\{userId}", UserSession.getOpId()),
new Object[]{}, connections);
}
}
}
});
if (result){
resultMap.put("result", true);
if ((dataFailNum + columnFailNum) > 0){//excel出现问题数据
resultMap.put("error", true);
}
StringBuffer message = new StringBuffer();
message.append("已成功导入").append(dataSuccNum).append("条数据!");
if (dataFailNum > 0){
message.append("有").append(dataFailNum).append("条数据因格式内容有问题无法导入!");
}
if (columnFailNum > 0){
message.append("个别列名未在xml配置文件中定义无法导入!");
}
resultMap.put("message", message.toString());
return resultMap;
}else{
resultMap.put("result", false);
resultMap.put("message", "数据导入失败，请重新尝试!");
}
return resultMap;
} private String getCellValue(Cell cell) {
int type = cell.getCellType();
switch (type) {
case Cell.CELL_TYPE_NUMERIC:// 数值、日期类型
double d = cell.getNumericCellValue();
if (HSSFDateUtil.isCellDateFormatted(cell)) {// 日期类型
Date date = HSSFDateUtil.getJavaDate(d);
return new SimpleDateFormat("yyyyMMdd").format(date);
} else {// 数值类型
return d + "";
}
case Cell.CELL_TYPE_BLANK:// 空白单元格
return null;
case Cell.CELL_TYPE_STRING:// 字符类型
return cell.getStringCellValue();
case Cell.CELL_TYPE_BOOLEAN:// 布尔类型
return cell.getBooleanCellValue() + "";
default:
logger.info("未处理类型["+type+"]");
break;
}
return null;
} private Object transfer(String dataType, String cellValue,
String className, String method, String param) {
try {
Class[] classArr = null;
Object[] valueArr = null;
if (ObjectUtil.isNull(param)) {
classArr = new Class[] {};
valueArr = new Object[] {};
} else {
String[] paramArr = param.split(",");
classArr = new Class[paramArr.length];
valueArr = new Object[paramArr.length];
for (int i = 0; i < paramArr.length; i++) {
if ("${self}".equals(paramArr[i])) {
if ("varchar".equals(dataType)
|| "date".equals(dataType)) {
classArr[i] = String.class;
valueArr[i] = cellValue;
} else if ("int".equals(param)) {
classArr[i] = int.class;
valueArr[i] = new Integer(cellValue);
} else if ("float".equals(param)) {
classArr[i] = float.class;
valueArr[i] = new Float(cellValue);
}
} else {
String typeStr = paramArr[i].split(":")[0];
String valueStr = paramArr[i].split(":")[1];
if ("varchar".equals(typeStr)) {
classArr[i] = String.class;
valueArr[i] = valueStr;
} else if ("int".equals(typeStr)) {
classArr[i] = int.class;
valueArr[i] = new Integer(valueStr);
} else if ("float".equals(typeStr)) {
classArr[i] = float.class;
valueArr[i] = new Float(valueStr);
}
}
}
}
Class tClass = Class.forName(className);
Object classInstance = tClass.newInstance();
Method classMethod = tClass.getMethod(method, classArr);
Object result = classMethod.invoke(classInstance, valueArr);
return result;
} catch (SecurityException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (IllegalArgumentException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (ClassNotFoundException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (InstantiationException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (IllegalAccessException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (NoSuchMethodException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (InvocationTargetException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return null;
} private Object[] parseCell(BN_Column columnBean, int rowNum,
String columnName, String cellValue) {
Object[] objectArr = new Object[2];
String dataType = columnBean.getDataType();
int length = columnBean.getLength();
boolean required = columnBean.isRequired();
boolean transfer = columnBean.isTransfer(); if (ObjectUtil.isNull(cellValue)) {
if (required) {
objectArr[0] = null;
objectArr[1] = "\"" + columnName + "\"不能为空! ";
return objectArr;
} else {
objectArr[0] = null;
objectArr[1] = null;
return objectArr;
}
} if (transfer) {// 需要转换内容
String className = columnBean.getClassName();
String method = columnBean.getMethod();
String param = columnBean.getParam();
Object result = transfer(dataType, cellValue, className, method,
param);
logger.info("[" + rowNum + "," + columnName + "]转换结果=" + result);
objectArr[0] = result;
objectArr[1] = null;
} else {// 不需要转换内容
// 根据数据类型检查长度和内容是否符合字段要求
if ("varchar".equals(dataType) || "date".equals(dataType)) {
if (length != 0 && cellValue.length() > length) {
objectArr[0] = null;
objectArr[1] = "\"" + columnName + "\"内容超过最大长度" + length + "的限制! ";
} else {
objectArr[0] = cellValue;
objectArr[1] = null;
}
} else if ("int".equals(dataType)) {
if (!StringUtil.isInteger(cellValue)) {
objectArr[0] = null;
objectArr[1] = "\"" + columnName + "\"内容应为整数! ";
} else {
objectArr[0] = new Integer(cellValue);
objectArr[1] = null;
}
} else if ("float".equals(dataType)) {
if (!StringUtil.isDouble(cellValue)) {
objectArr[0] = null;
objectArr[1] = "\"" + columnName + "\"内容应为浮点数! ";
} else {
objectArr[0] = new Float(cellValue);
objectArr[1] = null;
}
}
} return objectArr;
} private boolean runXMLSQL(final String sqlStr){
if (ObjectUtil.isNull(sqlStr)){
return true;
}
return DAORunner.runTransaction(new TransactionHandler() {
public void process(Connection connections) throws SQLException {
String[] sqlArr = sqlStr.split(";");
for (int i = 0 ; i < sqlArr.length ; i ++){
DAORunner.runUpdate(sqlArr[i].replaceAll("\\$\\{userId}", UserSession.getOpId()),
new Object[]{}, connections);
}
}
});
} public static void main(String[] args) throws Exception {
}
}