pldrimport.java

java操作excel数据批量导入

package com;

import com.jstrd.util.*;
import com.jstrd.gdzc.comm.*;
import com.jstrd.gdzc.swzj.Swjs;
import com.jstrd.dbbroke.*;
import com.jstrd.web.*;

import java.sql.*;
import java.util.ArrayList;

public class PLDRIMPORT {
	public void importData(String k_dwbh, String userName, String tableName,
			DBConnection db) throws Exception {
		String sql = "";
		ResultSet rs = null;
		Statement stmt = null;
		String kpbName = "gd_kpb_" + k_dwbh;
		int count = 0;
		ArrayList kpbhList = new ArrayList();
		Swjs swjs = new Swjs(k_dwbh);
		try {

			// 导入卡片表
			stmt = db.getConnection().createStatement();
			sql = " select count(k_xh) from " + tableName;
			rs = db.executeQuery(sql);
			if (rs.next()) {
				count = rs.getInt(1);
			}
			db.setAutoCommit(false);
			sql = " select k_kpbh1 from gd_kpbhb where k_dwbh like '" + k_dwbh
					+ "%'";
			rs = db.executeQuery(sql);
			rs.next();
			long maxKpbh = rs.getLong("k_kpbh1");

			sql = " update gd_kpbhb set k_kpbh1=" + (maxKpbh + count)
					+ " where k_dwbh='" + k_dwbh + "'";
			db.executeUpdate(sql);
			db.commit();
			db.setAutoCommit(true);
			db.setAutoCommit(false);
			sql = " select k_xh from " + tableName;
			rs = db.executeQuery(sql);
			long k_kpbh = maxKpbh;
			while (rs.next()) {
				k_kpbh++;
				kpbhList.add(String.valueOf(k_kpbh));
				long xh = rs.getLong(1);
				sql = // k_zyft,k_cpft,去除,不再放在kpb中
				" insert into "
						+ kpbName
						+ "(k_kpbh,k_dwbh,k_zcbh,k_zcml,k_zcfl,k_kmdh,"
						+ "k_zcgs,k_zygs,k_cpsx,k_kpxz,k_zgf,k_fz,"
						+ "k_fkpbh,k_glkpbh,k_zjlx,k_abc,k_jgggcs,k_sccj,k_sl,k_glbm,"
						+ "k_sybm,k_sydd,k_bgy,k_gmrq,k_jkrq,k_kssyrq,k_rzrq,k_zczt,"
						+ "k_zjnx,k_qbnynx, k_sksynx,k_yz,k_jz,k_jcz,k_ljzj,k_ljjz,"
						+ "k_yzjl,k_yzje,k_zjff,k_zjzt,k_sfjt,k_gcbh,k_gcmc,k_swzcbh,"
						+ "k_pzbh,k_khsx,k_bz,k_jslx,k_pyf,k_syr,k_czl,k_zcmc,"
						+ "k_jkr,k_shf,k_gly,k_zclx,k_qybm,k_sbwzh)"
						+ "   select "
						+ k_kpbh
						+ " ,k_dwbh,k_zcbh,k_zcml,k_zcfl,k_kmdh,"
						+ "k_zcgs,k_zygs,k_cpsx,k_kpxz,k_zgf,k_fz,"
						+ "k_fkpbh,k_glkpbh,k_zjlx,k_abc,k_jgggcs,k_sccj,k_sl,k_glbm,"
						+ "k_sybm,k_sydd,k_bgy,k_gmrq,k_jkrq,k_kssyrq,k_rzrq,k_zczt,"
						+ "k_zjnx,k_qbnynx, k_sksynx,k_yz,k_jz,k_jcz,k_ljzj,k_ljjz,"
						+ "k_yzjl,k_yzje,k_zjff,k_zjzt,k_sfjt,k_gcbh,k_gcmc,k_swzcbh,"
						+ "k_pzbh,k_khsx,k_bz,k_jslx,k_pyf,k_syr,k_czl,k_zcmc,"
						+ "k_jkr,k_shf,k_gly,k_zclx,k_qybm,k_sbwzh  from "
						+ tableName + " where k_xh=" + xh;

				stmt.executeUpdate(sql);
				// 更新临时表卡片编号
				sql = " update " + tableName + " set k_kpbh = " + k_kpbh
						+ " where k_xh=" + xh;
				stmt.executeUpdate(sql);
			}
			// 更新卡片表父卡片编号			
			sql = " select distinct(k_fkpbh) from " + tableName
					+ " where k_fkpbh != 0 ";
			rs = db.executeQuery(sql);
			while (rs.next()) {
				long fkpbh = rs.getLong(1);
				int kpxz = 0;
				sql = " select k_kpxz from " + kpbName + " where k_kpbh = "
						+ fkpbh;
				ResultSet rs2 = stmt.executeQuery(sql);
				rs2.next();
				kpxz = rs2.getInt(1);
				rs2.close();
				if (kpxz == 0) {
					UpdateFKP update = new UpdateFKP();
					update.updateFKP(k_dwbh, fkpbh, 0, db);
				}

			}
			rs.close();
			// 计算父卡片编号
			// 计算父卡片编号
			sql = " update " + tableName
					+ " a set (a.k_fkpbh) = (select b.k_kpbh from  "

					+ tableName + " b "
					+ " where a.k_fz =2 and a.k_fkpbh=0 and a.k_fkpxh=b.k_xh )"
					+ " where exists" + "  (select 1 from  " + tableName
					+ "  b where a.k_fkpxh=b.k_xh)";
			db.executeUpdate(sql);
			// 更新卡片表父卡片编号
			sql = " update " + kpbName
					+ " a  set (a.k_fkpbh) = (select b.k_fkpbh from  "

					+ tableName + " b where a.k_kpbh = b.k_kpbh and a.k_fz=2) "
					+ " where exists" + "  (select 1 from  " + tableName
					+ "  b where a.k_kpbh = b.k_kpbh)";
			db.executeUpdate(sql);
			db.commit();
			db.setAutoCommit(true);
			
	        //计算初始化税务折旧字段
	        for(int i=0;i<kpbhList.size();i++) {
				long kpid = Long.parseLong((String) kpbhList.get(i));
				if(kpid !=0) {
					swjs.initSwzd(kpid);
				}
			}
			swjs.commit();
		} catch (Exception e) {
			try {
				e.printStackTrace();
				db.rollback();
				db.setAutoCommit(true);
				throw new Exception(e.getMessage());
			} catch (Exception e1) {
				throw new Exception(e1.getMessage());

			}
		} finally {
			try {
				if (stmt != null) {
					stmt.close();
					stmt = null;
				}
			} catch (Exception e) {
				System.out.println("关闭数据库出错!");
				e.printStackTrace();
			}

		}

	}
}