📄 poixlsdb.java
字号:
package com.abc.poixls;
import java.io.FileInputStream;
import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.StringTokenizer;
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;
/**
* 支持xls的2003的xls版本。office2007不支持。
* @author Administrator
*
*/
public class PoiXlsDb {
public static boolean isdb = false;
public static void main(String[] args) {
PoiXlsDb pxd = new PoiXlsDb();
try {
pxd.dealxls();
} catch (Exception ex) {
ex.printStackTrace();
}
}
public void dealxls() throws Exception {
long currenttimeStart=System.currentTimeMillis();
int rightnum=0;
int errornum=0;
String brancode="";
POIFSFileSystem fs = null;
HSSFWorkbook wb = null;
try {
fs = new POIFSFileSystem(new FileInputStream("d://a.xls"));
wb = new HSSFWorkbook(fs);
} catch (IOException e) {
e.printStackTrace();
}
// sheet=0 sheet=1
//test
HSSFSheet sheet = wb.getSheetAt(0);
//HSSFSheet sheet = wb.getSheetAt(1);
int rows = sheet.getPhysicalNumberOfRows();// 行数
System.out.println("test.xml rows=" + rows);
Statement stmt = null;
PreparedStatement ps=null;
if (isdb) {
String sql = "insert into EMAP_MGR_MARKING_POINT(" +
"mark_code,mark_name,city,point,mark_type, " +
" bran_code,self_equi_stat,mark_addr)"+
" values (?,?,?,db2gse.ST_Point(cast(? as double),cast(? as double), 1003),?,?,?,?)";
Connection conn = this.getConnection();
ps = conn.prepareStatement(sql);
ps.clearParameters();
}
try {
String[] value=null;
for (int r = 0; r < rows; r++) {
double dlon=0,dlat=0;
HSSFRow row = sheet.getRow(r);
if (row != null) {
int cells = row.getLastCellNum();
// 每行单元格数
value = new String[cells];
//System.out.println("此行单元格数：" + cells);
String num = "";
for (short c = 0; c < cells; c++) {
HSSFCell cell = row.getCell(c);// 单元格
if (cell != null) {
switch (cell.getCellType()) {
case HSSFCell.CELL_TYPE_FORMULA:
value[c] += cell.getStringCellValue() + "\t";
break;
case HSSFCell.CELL_TYPE_NUMERIC:
value[c] += (long) cell.getNumericCellValue()
+ "\t";
break;
case HSSFCell.CELL_TYPE_STRING:
value[c] += cell.getStringCellValue() + "\t";
break;
default:
value[c] += "\t";
}
if (value[c] != null) {
value[c] = value[c].substring(4).trim();
if (value[c].indexOf(" ") > 0)
value[c] = value[c].substring(0, value[c]
.indexOf(" ") - 1);
}
}else{
if (value[c] == null) {
value[c] = " ";
}
}
// int k = c + 1;
}
// 下面可以将查找到的行内容用SQL语句INSERT到sqlserver数据库
String lin="";
//清楚名称中的回车字符
if (!value[2].equals("")){
String temp=value[2];
temp=temp.replaceAll("\r\n", "");
// temp=temp.replaceAll(" ", "");
// temp=temp.replaceAll("?", "");
value[2]=temp;
}
//清楚中文中的回车字符
if (!value[4].equals("")){
String temp=value[4];
temp=temp.replaceAll("\r\n", "");
// temp=temp.replaceAll(" ", "");
// temp=temp.replaceAll("?", "");
value[4]=temp;
}
if (cells==10){
lin ="cells="+cells+":"+r+"->"+ value[0]+"##"+value[1] + "##" + value[2] + "##" + value[3]+ "##" + value[4]+"##"+ value[5] + "##" +
value[6] + "##" + value[7] + "##" + value[8]+ "##" + value[9];
}
else if (cells==11){
lin ="cells="+cells+":"+r+"->"+ value[0]+"##"+value[1] + "##" + value[2] + "##" + value[3]+ "##" + value[4]+"##"+ value[5] + "##" +
value[6] + "##" + value[7] + "##" + value[8]+ "##" + value[9]+"##" + value[10];// +
}else if (cells==12){
lin ="cells="+cells+":"+r+"->"+ value[0]+"##"+value[1] + "##" + value[2] + "##" + value[3]+ "##" + value[4]+"##"+ value[5] + "##" +
value[6] + "##" + value[7] + "##" + value[8]+ "##" + value[9]+"##" + value[10]+"##" +value[11];
if (!value[11].trim().equals("") && r>0){
String strlonlat=value[11].trim();
StringTokenizer st=new StringTokenizer(strlonlat,",");
System.out.println("aaa"+strlonlat);
dlon=Double.parseDouble(st.nextToken());
dlat=Double.parseDouble(st.nextToken());
System.out.println(strlonlat+" "+dlon+" "+dlat);
}
}
//过滤商户编号为空，商户名称为空，有效日期为空，城市名称为空，分行名称为空
if (value[1].trim().equals("")||value[2].equals("")||value[7].trim().equals("")|| value[9].trim().equals("")|| value[10].trim().equals("")){
errornum++;
}
else{
//判断商户编号不为空，但不是数字类型
String id=value[1];
String s="";
int i = -1 ;
if (!id.equals("")){
//System.out.print(id);
if (id.length()>=2){
s=id.substring(0,2);
}
try{
i = Integer.parseInt(s);
}
catch(NumberFormatException numex){
//如果转换失败，那么你的处理如下
errornum++;
}
}
//判断id编号为数字类型
if (i>=0){
//判断城市是否满足。
boolean iscity=iscity2(value[9]);
if (!iscity){
errornum++;
}else{
brancode=getbrancode(value[10]);
if (brancode.equals("")){
errornum++;
}else{
System.out.println(lin);
if (isdb){
//stmt.executeUpdate(sql);
ps.setString(1,"TS"+String.valueOf(r));
ps.setString(2,value[2].trim());
ps.setString(3,value[9].trim()+"市");
ps.setDouble(4,dlon);
ps.setDouble(5,dlat);
ps.setString(6,"10");
ps.setString(7,brancode);
ps.setString(8,"0");
ps.setString(9,value[5].trim());
int insertret=ps.executeUpdate();
if (insertret>0){
rightnum++;
}
}
}
}
}
}
//System.out.println(sql);
}
}
long currenttimeEnd=System.currentTimeMillis();
System.out.println("---------------------------");
//System.out.println("开始时间："+currenttimeStart);
System.out.println("总行数：" + rows+"\r\n插入数据："+rightnum +"\r\n错误记录数：" +errornum+"" );
System.out.println("运行秒数："+(currenttimeEnd-currenttimeStart)/1000);
System.out.println("---------------------------");
//System.out.println("结束时间："+currenttimeEnd);
}
catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
} finally {
if (isdb){
try {
stmt.close();
stmt = null;
} catch (Exception e) {
}
try {
conn.close();
conn = null;
} catch (Exception e) {
}
}
}
}
private Connection conn;
public static Connection getConnection() throws Exception {
Connection conn = null;
Class.forName("com.ibm.db2.jcc.DB2Driver");
conn = DriverManager.getConnection(
"jdbc:db2://127.0.0.1:60000/bc", "bc", "bc");
return conn;
}
public void deal() {
try {
conn = this.getConnection();
this.TranBranchPy(conn);
} catch (SQLException e) {
e.printStackTrace();
} catch (Exception e) {
e.printStackTrace();
}
}
/**
* 添加标注点
*
* @param poi -
* 标注点
* @throws com.lingtu.emap.mgr.DataAccessException
*/
public void TranBranchPy(Connection conn) throws Exception {
String sql = "select bran_code,bran_name from EMAP_MGR_BRANCH ";
String sql2 = "select py,py_code from zh_py where zh=?";
PreparedStatement ps = null;
PreparedStatement ps2 = null;
ArrayList alist = new ArrayList();
// String sql2="update EMAP_MGR_BRANCH set py1=";
ResultSet rs = null;
try {
// ps=dataCon.getcon().prepareStatement(sql);
ps = conn.prepareStatement(sql);
rs = ps.executeQuery();
String zhstr = "";
String py2 = "";
String py1 = "";
String tmp = "";
String temp = "";
String temp2 = "";
while (rs.next()) {
temp = "";
temp2 = "";
// alist.add(rs.getString("bran_name"));
zhstr = rs.getString("bran_name").trim();
String bran_code = rs.getString("bran_code");
// System.out.println("zhstr="+zhstr);
for (int kkk = 0; kkk < zhstr.length(); kkk++) {
String zh = zhstr.substring(kkk, kkk + 1);
// System.out.println("zh="+zh);
tmp = this.getPy(conn, zh);
// System.out.println("py1="+py1);
temp = temp + tmp.substring(0, 1);
temp2 = temp2 + tmp;
}
py1 = temp;
py2 = temp2;
// System.out.println("zhstr="+zhstr+" py1="+py1+" py2="+py2);
sql2 = "update EMAP_MGR_BRANCH set py1='" + py1 + "'"
+ " ,py2='" + py2 + "' where bran_code='" + bran_code
+ "'";
System.out.println(sql2);
// ps2=conn.prepareStatement(sql2);
// ps2.executeUpdate();
// conn.commit();
}
} catch (SQLException ex) {
// logger.error(ex.getMessage());
throw new Exception("添加入库错误");
} finally {
if (ps != null)
ps.close();
if (ps2 != null)
ps2.close();
if (conn != null)
conn.close();
}
}
public String getPy(Connection conn, String zh) throws Exception {
// String sql="select bran_name from EMAP_MGR_BRANCH ";
String sql = "select py_code from zh_py where zh='" + zh + "'";
PreparedStatement ps = null;
ArrayList alist = new ArrayList();
String py1 = "";
ResultSet rs = null;
try {
// ps=dataCon.getcon().prepareStatement(sql);
ps = conn.prepareStatement(sql);
⌨️ 快捷键说明
复制代码
Ctrl + C
搜索代码
Ctrl + F
全屏模式
F11
切换主题
Ctrl + Shift + D
显示快捷键
?
增大字号
Ctrl + =
减小字号
Ctrl + -