/***************************************************************************************************
*類表述信息:針對用戶對象操作進行的一系列操作分派
*@author 利安寧
*@version 1.0 2008.5.20
*@since jdk1.4.0_06
***************************************************************************************************/
package org.bussiness.utility.subjectByformula;
import java.io.File;
import java.io.IOException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Date;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import java.util.Set;
import java.util.regex.Pattern;
import jxl.*;
import jxl.read.biff.BiffException;
import jxl.Sheet;
public class FormulaLoad2Db {
private static final String filePath = "d:\\健康保險統計制度統計指標.xls";
/**
* 根據給定的路徑讀入excel文件
* @param path是文件路徑
* @return List是對bean的封裝
* @author 利安寧
* @version 1.0 2008.5.26
*/
private List readExcelContext(String path) {
List list=new ArrayList();
Map markermap=new HashMap();
File file = new File(path);
Workbook wb = null;
Sheet arySheet=null;
try {
wb = Workbook.getWorkbook(file);
Sheet[] arySheets = wb.getSheets();
for(int i=0; i<arySheets.length; i++){
arySheet = arySheets[i];
int startRow=1000;
int endRow=0;
int startColumn=0;
for(int ii=0;ii<arySheet.getRows();ii++){
for(int j=0;j<arySheet.getColumns();j++){
String strCell=arySheet.getCell(j, ii).getContents().trim();
if(strCell.length()>0 && strCell.matches("[a-z]?\\d{8,10}")){
if(startRow>ii){
startRow=ii;
}
endRow=ii;
startColumn=j;
}
}
}
endRow++;
// System.out.println(startRow+" "+endRow+" "+startColumn);
if(startRow>endRow){
System.out.println("此為空sheet");
}else{
// System.out.println(excel2List(arySheet,startRow,endRow,startColumn).size());
list=Excel2List(arySheet,startRow,endRow,startColumn);
loadDataToDb(list);
insertByName("all");
}
}
} catch (BiffException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
} catch (Exception e) {
e.printStackTrace();
} finally {
wb.close();
}
return list;
}
/**
* 根據給定的Sheet,解析excel,封裝成bean放到List中
* @param arySheet是給定的Sheet
* @param startColumns是讀取Sheet的開始行
* @param endColumns是讀取Sheet的結束行
* @return List是對bean的封裝
* @author 利安寧
* @version 1.0 2008.5.26
*/
private List Excel2List(Sheet arySheet,int startColumns,int endColumns,int columns ){
List list=new ArrayList();
String matchStr="acegikmo";
IndexBean ib = null;
Cell[] cell1=arySheet.getColumn(1);
int endRows = arySheet.getRows();
String roleId="0" ;
String pp;
String s="-1";
for (int startRow =startColumns; startRow <endColumns; startRow++) {
String a=arySheet.getCell(columns, startRow).getContents().trim();
String b=arySheet.getCell(columns+1, startRow).getContents().trim();
String c=arySheet.getCell(columns+2, startRow).getContents().trim();
String d=arySheet.getCell(columns+3, startRow).getContents().trim().equals("產")?"1":"-1";
String e=arySheet.getCell(columns+4, startRow).getContents().trim().equals("壽")?"1":"-1";
String f=arySheet.getCell(columns+5, startRow).getContents().trim().equals("再")?"1":"-1";
String g=arySheet.getCell(columns+6, startRow).getContents().trim().equals("集")?"1":"-1";
String h=arySheet.getCell(columns+7, startRow).getContents().trim().equals("資")?"1":"-1";
String i=arySheet.getCell(columns+8, startRow).getContents().trim().equals("快")?"1":"-1";
String j=arySheet.getCell(columns+9, startRow).getContents().trim().equals("月")?"1":"-1";
String k=arySheet.getCell(columns+10, startRow).getContents().trim().equals("季")?"1":"-1";
String l=arySheet.getCell(columns+11, startRow).getContents().trim().equals("半年")?"1":"-1";
String m=arySheet.getCell(columns+12, startRow).getContents().trim().equals("年")?"1":"-1";
String n=arySheet.getCell(columns+13, startRow).getContents().trim().equals("年度")?"1":"-1";
String o=arySheet.getCell(columns+14, startRow).getContents().trim().equals("1")?"1":"-1";
String p=arySheet.getCell(columns+15, startRow).getContents().trim();
String q=arySheet.getCell(columns+16, startRow).getContents().trim();
String r=arySheet.getCell(columns+17, startRow).getContents().trim();
if(arySheet.getColumns()>=19){
s=arySheet.getCell(columns+18, startRow).getContents().trim().equals("1")?"1":"-1";
}
if(arySheet.getCell(0, startRow).getType()==CellType.EMPTY||!arySheet.getCell(0, startRow).getContents().trim().matches("[a-z]?\\d{8,10}")||!a.matches("[u4e00-u9fa5]+")){
continue;
}
if(c.equals("負債")){
c="1";
roleId="1002";
}
if(c.equals("權益")){
c="2";
roleId="1002";
}
if(c.equals("損益")){
c="3";
roleId="1002";
}
if(c.equals("現金流")){
c="4";
roleId="1002";
}
if(c.equals("資產")){
c="5";
roleId="1002";
}
if(c.equals("資金")){
c="6";
roleId="1002";
}
if(c.equals("統計")){
c="7";
roleId="1003";
if(f.equals("再")){
roleId="1005";
}
if(a.substring(0, 4).matches("\\d{4}")){
if(6118<=Integer.parseInt(a.substring(0, 4))&&Integer.parseInt(a.substring(0, 4))>=6121&&Integer.parseInt(a.substring(0, 4))==6151){
roleId="1004";
}
}
}
if(roleId.equals("0")){
System.out.println(startRow+"驗證錯誤!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!");
}
if(p.equals("0")||p.equals("1")||p.equals("2")||p.equals("9")){
pp=p;
}else{
pp="-1";
}
if(r.equals("總公司報送")){
r="1";
}
if(r.length()==0){
r="3";
}
ib=new IndexBean();
ib.setInfoID(a);
ib.setDefName(b);
ib.setDefType(c);
ib.setRole_Id( Integer.parseInt(roleId));
ib.setCompanyWealth(d);
ib.setCompanyLife(e);
ib.setCompanyReinsurance(f);
ib.setCompanyGroup(g);
ib.setCompanyAssets(h);
ib.setReportF(i);
ib.setReportM(j);
ib.setReportQ(k);
ib.setReportH(l);
ib.setReportY(m);
ib.setReportN(n);
ib.setIfCollect(Integer.parseInt(o));
ib.setIfGroup(Integer.parseInt(pp));
ib.setRptCode(q);
ib.setLevels(r);
ib.setIfReport(s);
ib.setOperate("利安寧");
ib.setOprDate( new Date( new java.util.Date().getTime()));
ib.setRemark("待寫");
int cou=b.lastIndexOf("－");
if(cou!=-1){
String paterstring=b.substring(0, cou).trim();
L2: for(int ii=4;ii<cell1.length;ii++){
if(paterstring.equalsIgnoreCase(cell1[ii].getContents().trim())){
String scell=arySheet.getCell(0, cell1[ii].getRow()).getContents().trim();
ib.setParentId(scell);
break L2;
}
else{
ib.setParentId(a);
}
}
}else{
ib.setParentId(a);
}
list.add(ib);
}
return list;
}
/**
*把list中的bean插入到數據庫
* @param list
* @return beanloon是判斷是否全都讀入到數據庫中
* @author 利安寧
* @version 1.0 2008.5.20
* @throws Exception
*/
private boolean loadDataToDb(List list) throws Exception {
Connection conn = null;
PreparedStatement pstm = null;
int [] r ;
int count=0;
try {
conn = JdbcUtil.getConnection();
String sql = "insert into ins_info_def_temp(info_id,def_name,parent_id,role_id ,def_type ,company_wealth ,company_life,company_reinsurance,company_group ,company_assets ,report_f,report_m,report_q,report_h,report_n ,report_a,if_collect,if_group,rpt_code,levels,if_report,def_flag,operate,opr_date,remark) values(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
pstm = conn.prepareStatement(sql);
Iterator it = list.iterator();
while (it.hasNext()) {
count++;
IndexBean ib = (IndexBean) it.next();
pstm.setString(1,ib.getInfoID());
pstm.setString(2,ib.getDefName());
pstm.setString(3,ib.getParentId());
pstm.setInt(4,ib.getRoleId());
pstm.setString(5, ib.getDefType());
pstm.setString(6,ib.getCompanyWealth());
pstm.setString(7,ib.getCompanyLife());
pstm.setString(8,ib.getCompanyReinsurance());
pstm.setString(9,ib.getCompanyGroup());
pstm.setString(10,ib.getCompanyAssets());
pstm.setString(11,ib.getReportF());
pstm.setString(12,ib.getReportM());
pstm.setString(13,ib.getReportQ());
pstm.setString(14,ib.getReportH());
pstm.setString(15,ib.getReportY());
pstm.setString(16,ib.getReportN());
pstm.setInt(17,ib.getIfCollect());
pstm.setInt(18,ib.getIfGroup());
pstm.setString(19,ib.getRptCode());
pstm.setString(20,ib.getLevels());
pstm.setString(21,ib.getIfReport());
pstm.setString(22,"1");
pstm.setString(23,ib.getOperate());
pstm.setDate(24,ib.getOprDate());
pstm.setString(25,ib.getRemark());
// System.out.println(ib.getInfoID());
pstm.executeUpdate();
}
conn.commit();
} catch (SQLException e) {
e.printStackTrace();
conn.rollback();
} finally {
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
return false;
}
}
return true;
}
/**
*把用戶要求的記錄插入到數據庫中，如：產、壽、再、集、資。
* @param str
* @return beanloon是判斷是否全都讀入到數據庫中
* @author 利安寧
* @version 1.0 2008.6.6
* @throws Exception
*/
public boolean insertByName(String str) throws Exception{
Connection conn = null;
Statement pstm = null;
Statement pstm1 = null;
String delsql=null;
String insertsql=null;
String deletesql=null;
try {
conn = JdbcUtil.getConnection();
if(str.equals("all")){
delsql="delete from ins_info_def where info_id in (select info_id from ins_info_def_temp ) ";
insertsql = "insert into ins_info_def select * from ins_info_def_temp";
}
if(str.equals("產")){
delsql="delete from ins_info_def where info_id in (select info_id from ins_info_def_temp where COMPANY_WEALTH='1') ";
insertsql = "insert into ins_info_def select * from ins_info_def_temp where COMPANY_WEALTH='1' ";
}
if(str.equals("壽")){
delsql="delete from ins_info_def where info_id in (select info_id from ins_info_def_temp where COMPANY_LIFE='1') ";
insertsql = "insert into ins_info_def select * from ins_info_def_temp where COMPANY_LIFE='1' ";
}
if(str.equals("再")){
delsql="delete from ins_info_def where info_id in (select info_id from ins_info_def_temp where COMPANY_REINSURANCE='1') ";
insertsql = "insert into ins_info_def select * from ins_info_def_temp where COMPANY_REINSURANCE='1' ";
}
if(str.equals("集")){
delsql="delete from ins_info_def where info_id in (select info_id from ins_info_def_temp where COMPANY_GROUP='1') ";
insertsql = "insert into ins_info_def select * from ins_info_def_temp where COMPANY_GROUP='1'";
}
if(str.equals("資")){
delsql="delete from ins_info_def where info_id in (select info_id from ins_info_def_temp where COMPANY_ASSETS='1') ";
insertsql = "insert into ins_info_def select * from ins_info_def_temp where COMPANY_ASSETS='1'";
}
pstm = conn.createStatement();
pstm.executeUpdate(delsql);
pstm.executeUpdate(insertsql);
deletesql="delete ins_info_def_temp";
pstm1=conn.createStatement();
pstm1.executeUpdate(deletesql);
conn.commit();
} catch (SQLException e) {
conn.rollback();
e.printStackTrace();
} finally {
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
return false;
}
}
return true;
}
//測試代碼
public static void main(String args[]){
FormulaLoad2Db fl=new FormulaLoad2Db();
System.out.println(new java.util.Date());
List list=fl.readExcelContext(filePath);
System.out.println(list.size());
System.out.println(new java.util.Date());
System.out.println("完");
}
}