/**
*
*/
package com.tydic.commons.readExcel;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.List;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
public class ReadExcel {
/**
* Read the Excel 2010
* @param path the path of the excel file
* @return
* @throws IOException
*/
public static List<MvnoInfo> readXlsx() throws IOException {
String path = "E:\\123.xlsx";
InputStream is = new FileInputStream(path);
XSSFWorkbook xssfWorkbook = new XSSFWorkbook(is);
MvnoInfo mvnoInfo = null;
List<MvnoInfo> list = new ArrayList<MvnoInfo>();
// Read the Sheet
for (int numSheet = 0; numSheet < xssfWorkbook.getNumberOfSheets(); numSheet++) {
XSSFSheet xssfSheet = xssfWorkbook.getSheetAt(numSheet);
if (xssfSheet == null) {
continue;
}
// Read the Row
for (int rowNum = 1; rowNum <= xssfSheet.getLastRowNum(); rowNum++) {
XSSFRow xssfRow = xssfSheet.getRow(rowNum);
if (xssfRow.getCell(0) == null) {
break;
}
if (xssfRow != null) {
mvnoInfo = new MvnoInfo();
XSSFCell mvnofullname = xssfRow.getCell(0);
XSSFCell mvnobusinessname = xssfRow.getCell(1);
XSSFCell busitype = xssfRow.getCell(2);
XSSFCell busitypeopenstatus = xssfRow.getCell(3);
XSSFCell firstagreementsigntime = xssfRow.getCell(4);
XSSFCell firstapprovepapertime = xssfRow.getCell(5);
XSSFCell firstapproveexpirydate = xssfRow.getCell(6);
XSSFCell accesstestpasstime = xssfRow.getCell(7);
XSSFCell servicevalidatepasstime = xssfRow.getCell(8);
XSSFCell trialbusinesstime = xssfRow.getCell(9);
XSSFCell serviceassessapplytime = xssfRow.getCell(10);
XSSFCell serviceassesspasstime = xssfRow.getCell(11);
XSSFCell serviceassessresult = xssfRow.getCell(12);
XSSFCell formalbusitime = xssfRow.getCell(13);
XSSFCell suspendprotocoltime = xssfRow.getCell(14);
XSSFCell recoverprotocoltime = xssfRow.getCell(15);
XSSFCell exitprotocoltime = xssfRow.getCell(16);
mvnoInfo.setMvnofullname(getValue(mvnofullname));
mvnoInfo.setMvnobusinessname(getValue(mvnobusinessname));
mvnoInfo.setBusitype(getValue(busitype));
mvnoInfo.setBusitypeopenstatus(getValue(busitypeopenstatus));
mvnoInfo.setFirstagreementsigntime(getValue(firstagreementsigntime));
mvnoInfo.setFirstapprovepapertime(getValue(firstapprovepapertime));
mvnoInfo.setFirstapproveexpirydate(getValue(firstapproveexpirydate));
mvnoInfo.setAccesstestpasstime(getValue(accesstestpasstime));
mvnoInfo.setServicevaliStringpasstime(getValue(servicevalidatepasstime));
mvnoInfo.setTrialbusinesstime(getValue(trialbusinesstime));
mvnoInfo.setServiceassessapplytime(getValue(serviceassessapplytime));
mvnoInfo.setServiceassesspasstime(getValue(serviceassesspasstime));
mvnoInfo.setServiceassessresult(getValue(serviceassessresult));
mvnoInfo.setFormalbusitime(getValue(formalbusitime));
mvnoInfo.setSuspendprotocoltime(getValue(suspendprotocoltime));
mvnoInfo.setRecoverprotocoltime(getValue(recoverprotocoltime));
mvnoInfo.setExitprotocoltime(getValue(exitprotocoltime));
System.out.println(mvnoInfo.toString());
list.add(mvnoInfo);
}
}
}
return list;
}
@SuppressWarnings("static-access")
private static String getValue(XSSFCell xssfRow) {
if (xssfRow.getCellType() == xssfRow.CELL_TYPE_BOOLEAN) {
return String.valueOf(xssfRow.getBooleanCellValue());
} else if (xssfRow.getCellType() == xssfRow.CELL_TYPE_NUMERIC) {
if (HSSFDateUtil.isCellDateFormatted(xssfRow)) {
SimpleDateFormat df =new SimpleDateFormat("yyyy-MM-dd");
return df.format(HSSFDateUtil.getJavaDate(xssfRow.getNumericCellValue())).toString();
}
DecimalFormat df = new DecimalFormat("####");
return String.valueOf(df.format(xssfRow.getNumericCellValue()));
}else {
return String.valueOf(xssfRow.getStringCellValue());
}
}
public static void main(String[] args) throws IOException, SQLException {
List<MvnoInfo> list = readXlsx();
Connection connection = null;
String url = "jdbc:oracle:thin:@131.213.123.123:1521:SODF";
String username = "dp";
String password = "123";
String sql1 = "123123123 "
String sql2 = "123123123 "
connection = DriverManager.getConnection(url, username,password);
PreparedStatement ps1 = connection.prepareStatement(sql1);
PreparedStatement ps2 = connection.prepareStatement(sql2);
Long a = null;
int n = 0;
for (MvnoInfo mvnoInfo : list) {
ps2.setString(1, mvnoInfo.getMvnobusinessname());
ResultSet ex = ps2.executeQuery();
while(ex.next()){
if (ex.getString(1) != null && !ex.getString(1).equals("")) {
a = Long.parseLong(ex.getString(1));
//a=Integer.parseInt(ex.getString(1));
}
ps1.setLong(1, a);
ps1.setString(2, mvnoInfo.getMvnofullname());
ps1.setString(3, mvnoInfo.getMvnobusinessname());
ps1.setString(4, mvnoInfo.getBusitype());
ps1.setString(5, mvnoInfo.getBusitypeopenstatus());
ps1.setString(6, mvnoInfo.getFirstagreementsigntime());
ps1.setString(7, mvnoInfo.getFirstapprovepapertime());
ps1.setString(8, mvnoInfo.getFirstapproveexpirydate());
ps1.setString(9, mvnoInfo.getAccesstestpasstime());
ps1.setString(10, mvnoInfo.getServicevaliStringpasstime());
ps1.setString(11, mvnoInfo.getTrialbusinesstime());
ps1.setString(12, mvnoInfo.getServiceassessapplytime());
ps1.setString(13, mvnoInfo.getServiceassesspasstime());
ps1.setString(14, mvnoInfo.getServiceassessresult());
ps1.setString(15, mvnoInfo.getFormalbusitime());
ps1.setString(16, mvnoInfo.getSuspendprotocoltime());
ps1.setString(17, mvnoInfo.getRecoverprotocoltime());
ps1.setString(18, mvnoInfo.getExitprotocoltime());
ps1.executeUpdate();
n++;
}
}
if (connection != null) {
connection.close();
}
System.out.println(n+"\n"+list.size());
}
/**
* Read the Excel 2003-2007
* @param path the path of the Excel
* @return
* @throws IOException
*/
/* public static List<MvnoInfo> readXls() throws IOException {
String path = "E:\\123.xls";
InputStream is = new FileInputStream(path);
HSSFWorkbook hssfWorkbook = new HSSFWorkbook(is);
MvnoInfo mvnoInfo = null;
List<MvnoInfo> list = new ArrayList<MvnoInfo>();
// Read the Sheet
for (int numSheet = 0; numSheet < hssfWorkbook.getNumberOfSheets(); numSheet++) {
HSSFSheet hssfSheet = hssfWorkbook.getSheetAt(numSheet);
if (hssfSheet == null) {
continue;
}
// Read the Row
for (int rowNum = 1; rowNum <= hssfSheet.getLastRowNum(); rowNum++) {
HSSFRow hssfRow = hssfSheet.getRow(rowNum);
if (hssfRow != null) {
mvnoInfo = new MvnoInfo();
HSSFCell no = hssfRow.getCell(0);
HSSFCell name = hssfRow.getCell(1);
HSSFCell age = hssfRow.getCell(2);
HSSFCell score = hssfRow.getCell(3);
String v1 = getValue(no);
String v2 = getValue(name);
String v3 = getValue(age);
String v4 = getValue(score);
System.out.println(v1+"----"+v2+"----"+v3+"-----"+v4);
list.add(mvnoInfo);
}
}
}
return list;
}*/
/* @SuppressWarnings("static-access")
private static String getValue(HSSFCell hssfCell) {
if (hssfCell.getCellType() == hssfCell.CELL_TYPE_BOOLEAN) {
return String.valueOf(hssfCell.getBooleanCellValue());
} else if (hssfCell.getCellType() == hssfCell.CELL_TYPE_NUMERIC) {
return String.valueOf(hssfCell.getNumericCellValue());
} else {
return String.valueOf(hssfCell.getStringCellValue());
}
}*/
}
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
