场景：从Excel中读取数据，报错到数据库表中
代码如下：
package com.smartwork.excel.analysis;
import java.text.DecimalFormat;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFDataFormat;
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.DataFormatter;
import org.apache.poi.ss.usermodel.FormulaEvaluator;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.hibernate.SQLQuery;
import org.hibernate.Session;
import org.hibernate.SessionFactory;
import org.hibernate.Transaction;
import org.hibernate.cfg.Configuration;
import com.smartwork.esb.dict.LogTypes;
import com.smartwork.esb.dict.ReceiveResults;
import com.smartwork.esb.transaction.configurator.IfConfigures;
import com.smartwork.esb.transaction.service.QueryLogService;
import com.smartwork.esb.transaction.timer.AbsTimerService;
import com.smartwork.esb.util.DataUtil;
import com.smartwork.esb.util.SessionUtil;
import com.smartwork.esb.util.SpringBeanUtil;
import jcifs.smb.NtlmPasswordAuthentication;
import jcifs.smb.SmbFile;
import jcifs.smb.SmbFileInputStream;
/**
* 物料基本信息接口(读取文件)
* @author Tzj
*
*/
public class IfRpa01 extends AbsTimerService{
private QueryLogService queryLogService;
String url = DataUtil.getStr(IfConfigures.getQueryParam("READ_EXCEL_URL"));
//String url = "smb://10.45.97.142/KM Dihon Sharing/BESFILESHARING/In//COOISPI/";
String outUrl = DataUtil.getStr(IfConfigures.getQueryParam("DELETE_EXCEL_URL"));
//MVQWK
String userName = DataUtil.getStr(IfConfigures.getQueryParam("USER_NAME"));
//Asd#0987
String password = DataUtil.getStr(IfConfigures.getQueryParam("PASSWORD"));
NtlmPasswordAuthentication auth = new NtlmPasswordAuthentication("", userName, password);
/**
* 根据指定目录，获取Excel文件List
*
*/
@SuppressWarnings("unused")
public List<String> getFileList(){
try {
List<String> fileList = new ArrayList<String>();
if(url == null) {
queryLogService.add(null, ReceiveResults.FAILURE, "文件路径参数:READ_EXCEL_URL为空", LogTypes.ENTRY);
return null;
}
SmbFile smbFile= new SmbFile(url,auth);
if(smbFile.isDirectory()){
SmbFile[] fileArr = smbFile.listFiles();
if(fileArr == null) {
queryLogService.add(null, ReceiveResults.FAILURE, "文件路径参数:READ_EXCEL_URL配置有误", LogTypes.ENTRY);
return null;
}
for (int i = 0; i < fileArr.length; i++) {
String fileName = fileArr[i].getName();
if(fileName.startsWith("Excel") && (fileName.endsWith(".xls") || fileName.endsWith(".xlsx")
||fileName.endsWith(".XLS") || fileName.endsWith(".XLSX") )) {
fileList.add(fileName);
}
}
return fileList;
}else {
queryLogService.add(null, ReceiveResults.FAILURE, "文件路径参数:READ_EXCEL_URL需要配置为文件夹", LogTypes.ENTRY);
return null;
}
} catch (Exception e) {
queryLogService.add(null, ReceiveResults.FAILURE, "SmbFile网络路径异常", LogTypes.ENTRY);
return null;
}
}
/**
* 读取Excel文件List
*
*/
public List<List<String[]>> getReadExcelMsg() {
List<List<String[]>> resultFileList = new ArrayList<List<String[]>>();
List<String[]> dataList = new ArrayList<String[]>();
List<String> fileList = getFileList();
try {
if(fileList != null && fileList.size() > 0 ) {
for (int i = 0; i < fileList.size(); i++) {
SmbFile excel= new SmbFile(url+fileList.get(i),auth);
String[] split = excel.getName().split("\\.");
Workbook wb;
if("xls".equals(split[1].toLowerCase())) {
SmbFileInputStream fis = new SmbFileInputStream(excel);
wb = new HSSFWorkbook(fis);
}else if("xlsx".equals(split[1].toLowerCase())){
SmbFileInputStream fis = new SmbFileInputStream(excel);
wb = new XSSFWorkbook(fis);
}else {
queryLogService.add(null, ReceiveResults.FAILURE, "文件类型错误", LogTypes.ENTRY);
return null;
}
Sheet sheet = wb.getSheetAt(0);
int firstRowIndex = sheet.getFirstRowNum();
int lastRowIndex = sheet.getLastRowNum();
DataFormatter formatter = new DataFormatter();
FormulaEvaluator evaluator = wb.getCreationHelper().createFormulaEvaluator();
for(int rIndex = firstRowIndex; rIndex <= lastRowIndex; rIndex++) {
String[] dataArray = new String[19];
Row row = sheet.getRow(rIndex);
if(row != null) {
int firstCellIndex = row.getFirstCellNum();
int lastCellIndex = row.getLastCellNum();
for(int cIndex = firstCellIndex; cIndex <= lastCellIndex; cIndex ++) {
Cell cell = row.getCell(cIndex);
if(cell != null) {
switch(cell.getCellType()) {
case HSSFCell.CELL_TYPE_NUMERIC:
if (HSSFDateUtil.isCellDateFormatted(cell)) {
dataArray[cIndex] = dateToString(cell);
}else {
String value = formatter.formatCellValue(cell, evaluator);
dataArray[cIndex] = value;
}
break;
default :
String value = formatter.formatCellValue(cell, evaluator);
dataArray[cIndex] = value;
break;
}
}
}
}
dataList.add(dataArray);
}
boolean flag = moveFile(excel,outUrl);
if(flag) {
queryLogService.add(null, ReceiveResults.INFO, "文件移动成功", LogTypes.PROCESS);
resultFileList.add(dataList);
}
}
return resultFileList;
}else {
return null;
}
} catch (Exception e) {
return null;
}
}
/**
* 日期转换
*
*/
public Date stringToDate(String str) {
SimpleDateFormat sdf = new SimpleDateFormat("yyyy/MM/dd");
try {
return sdf.parse(str);
} catch (ParseException e) {
return null;
}
}
/**
* 日期转换
*
*/
public String dateToString(Cell cell) {
SimpleDateFormat sdf = null;
if (cell.getCellStyle().getDataFormat() == HSSFDataFormat
.getBuiltinFormat("h:mm")) {
sdf = new SimpleDateFormat("HH:mm");
} else {// 日期
sdf = new SimpleDateFormat("yyyy/MM/dd");
}
Date date = cell.getDateCellValue();
return sdf.format(date);
}
// public void insertLog(String msg) {
// Configuration configuration = new Configuration().configure();
// SessionFactory sessionFactory = configuration.buildSessionFactory();
// Session session = sessionFactory.openSession();
// Transaction ts= session.beginTransaction();
//
// String insertSql = "insert into IF_TEST_LOG(MSG,CREATE_TIME) VALUES(?,getdate())";
// SQLQuery queryInsert = session.createSQLQuery(insertSql);
// queryInsert.setString(0, msg);
// queryInsert.executeUpdate();
// SessionUtil.commit(session, ts);
// }
/**
* 数据存储数据库
*
*/
public void insertData(StringBuffer sql, StringBuffer querySql, String[] dataArray) {
// Configuration configuration = new Configuration().configure();
// SessionFactory sessionFactory = configuration.buildSessionFactory();
// Session session = sessionFactory.openSession();
// Transaction ts= session.beginTransaction();
Session session = SessionUtil.getPublicIndependentSession();
Transaction ts= session.beginTransaction();
SQLQuery query = null;
querySql = querySql.append("SELECT * FROM IF_RPA_BASIC_MATERIAL A WHERE MATERIAL_NO = :materialNo ");
SQLQuery sqlQuery = session.createSQLQuery(querySql.toString());
sqlQuery.setString("materialNo", dataArray[0]);
List<Object[]> list = sqlQuery.list();
if(list!= null && list.size() > 0 ) {
Date lastModifyTimeNew = stringToDate(dataArray[11]);
Date lastModifyTime = stringToDate(list.get(0)[5].toString());
if(lastModifyTimeNew!= null && lastModifyTime!=null && lastModifyTimeNew.compareTo(lastModifyTime) <= 0 ) {
return;
}
sql = sql .append("update IF_RPA_BASIC_MATERIAL set MATERIAL_TYPE = :materialType,"
+ " DESCRIPTION = :description, UOM = :uom, "
+ " DELETION_INDICATOR = :deletionIndicator, LAST_MODIFY_TIME = :lastModifyTime, "
+ " UPDATE_TIME = getdate() "
+ " WHERE MATERIAL_NO = :materialNo ");
query = session.createSQLQuery(sql.toString());
query.setString("materialNo", dataArray[0]);
query.setString("materialType", dataArray[7]);
query.setString("description", dataArray[18]);
query.setString("uom", dataArray[9]);
query.setString("deletionIndicator", dataArray[17]);
query.setString("lastModifyTime", dataArray[11]);
}else {
sql = sql.append("insert into IF_RPA_BASIC_MATERIAL(MATERIAL_NO,MATERIAL_TYPE,"
+ "DESCRIPTION,UOM,DELETION_INDICATOR,"
+ "LAST_MODIFY_TIME)"
+" select '"
+ dataArray[0]+"','"
+ dataArray[7]+"','"
+ dataArray[18]+"','"
+ dataArray[9]+"','"
+ dataArray[17]+"','"
+ dataArray[11]+"'");
query = session.createSQLQuery(sql.toString());
}
query.executeUpdate();
SessionUtil.commit(session, ts);
}
/**
* 文件移动到指定目录
*
*/
public boolean moveFile(SmbFile inFile,String outFileUrl) {
try {
SmbFile outFileDir = new SmbFile(outFileUrl,auth);
if(!outFileDir.exists()) {
System.out.println("删除文件路径为空，请确认");
queryLogService.add(null, ReceiveResults.FAILURE, "文件路径参数:OUT_EXCEL_URL为空", LogTypes.ENTRY);
return false;
}
SmbFile outFile = new SmbFile(outFileUrl+inFile.getName(),auth);
//如果同名文件先删除，再移动
if(outFile.exists()) {
outFile.delete();
}
inFile.renameTo(outFile);
return true;
} catch (Exception e) {
queryLogService.add(null, ReceiveResults.FAILURE, "文件移动异常", LogTypes.ENTRY);
return false;
}
}
@Override
public void on() {
if(queryLogService == null) {
queryLogService = SpringBeanUtil.getQueryLogService();
}
List<List<String[]>> dataList = getReadExcelMsg();
if(dataList != null && dataList.size() > 0 ) {
StringBuffer sql = new StringBuffer();
StringBuffer querySql = new StringBuffer();
for (List<String[]> list : dataList) {
for (String[] dataArray : list) {
if(dataArray[0].contains("Material")) {
continue;
}
insertData(sql,querySql,dataArray);
queryLogService.add(null, ReceiveResults.SUCCESS, "接口运行成功", LogTypes.ENTRY);
sql.delete(0, sql.length());
querySql.delete(0, querySql.length());
}
}
}
}
}
