package com.xinpoint.erp;
import java.io.File;
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.text.DecimalFormat;
import jxl.Workbook;
import jxl.write.Label;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;
import jxl.write.WriteException;
import jxl.write.biff.RowsExceededException;
public class TiptopSlipCountUtil {
/**
* Tiptop5.x 上線每日單據新舊系統差異統計
* @author 李藝輝 2011/09/10
*/
public static void main(String[] args) {
//新系統oracle資料庫各營運中心連接信息
final String ORACLEDBDRIVER = "oracle.jdbc.driver.OracleDriver";
final String ORACLEDBURL = "jdbc:oracle:thin:@192.168.5.102:1521:topprod";
//按電線、資科、造粒、伸銅、菲太、臺太 順序讀取數據
final String[] ORACLEDBUSER = new String[]{"hzjm"};
final String[] ORACLEDBPASSWORD = new String[]{"hzjm"};
//用於Oracel讀取數據記錄的3個對象
Connection connOracle = null; //得到資料庫的連接
PreparedStatement pstmOracle = null; //執行CRUD記錄操作
ResultSet rsOracle = null; //保持CRUD操作后的結果集
//創建Excel文檔，用於將統計后的數據信息自動保存至該Excel文檔中(C://TiptopSlipCount.xls)
WritableWorkbook workbook = null;
WritableSheet sheet = null;
WritableSheet aimtSheet = null;
try {
workbook = Workbook.createWorkbook(new File("C://TiptopSlipCount.xls"));
//迴圈，依據有多少工廠別就產生該Excel有相應數目的sheet
for(int i=0;i<ORACLEDBUSER.length;i++){
//創建Excel文檔中的工廠信息
sheet = workbook.createSheet(ORACLEDBUSER[i], i);
Label factoryLable = new Label(0, 0, ORACLEDBUSER[i]);
sheet.addCell(factoryLable);
//創建Excel文檔橫列欄位信息
Label confirmLableNew = new Label(2, 1, "確認(新)");
sheet.addCell(confirmLableNew);
Label notConfirmLableNew = new Label(3, 1, "未確認(新)");
sheet.addCell(notConfirmLableNew);
Label notPostNew = new Label(4, 1, "未過帳(新)");
sheet.addCell(notPostNew);
Label voidLableNew = new Label(5, 1, "作廢(新)");
sheet.addCell(voidLableNew);
Label countTiptopLableNew = new Label(6, 1, "總計(新)");
sheet.addCell(countTiptopLableNew);
Label confirmLableOld = new Label(8, 1, "確認(舊)");
sheet.addCell(confirmLableOld);
Label notConfirmLableOld = new Label(9, 1, "未確認(舊)");
sheet.addCell(notConfirmLableOld);
Label notPostOld = new Label(10, 1, "未過帳(舊)");
sheet.addCell(notPostOld);
Label voidLableOld = new Label(11, 1, "作廢(舊)");
sheet.addCell(voidLableOld);
Label countTiptopLableOld = new Label(12, 1, "總計(舊)");
sheet.addCell(countTiptopLableOld);
//創建Excel文檔縱列tiptop單據程式信息
Label axmt410Lable = new Label(0, 2, "訂單(axmt410)");
sheet.addCell(axmt410Lable);
Label axmt410FileLable = new Label(1, 2, "oea_file");
sheet.addCell(axmt410FileLable);
Label axmt620Lable = new Label(0, 3, "出貨單(axmt620)");
sheet.addCell(axmt620Lable);
Label axmt620FileLable = new Label(1, 3, "oga_file");
sheet.addCell(axmt620FileLable);
Label apmt420Lable = new Label(0, 4, "請購單(apmt420)");
sheet.addCell(apmt420Lable);
Label apmt420FileLable = new Label(1, 4, "pmk_file");
sheet.addCell(apmt420FileLable);
Label apmt540Lable = new Label(0, 5, "採購單(apmt540)");
sheet.addCell(apmt540Lable);
Label apmt540FileLable = new Label(1, 5, "pmm_file");
sheet.addCell(apmt540FileLable);
Label apmt110Lable = new Label(0, 6, "收貨單(apmt110)");
sheet.addCell(apmt110Lable);
Label apmt110FileLable = new Label(1, 6, "rva_file");
sheet.addCell(apmt110FileLable);
Label aqct110Lable = new Label(0, 7, "IQC(aqct110)");
sheet.addCell(aqct110Lable);
Label aqct110FileLable = new Label(1, 7, "qcs_file");
sheet.addCell(aqct110FileLable);
Label apmt720Lable = new Label(0, 8, "入庫單(apmt720)");
sheet.addCell(apmt720Lable);
Label apmt720FileLable = new Label(1, 8, "rvu_file");
sheet.addCell(apmt720FileLable);
Label asfi301Lable = new Label(0, 10, "工單(asfi301)");
sheet.addCell(asfi301Lable);
Label asfi301FileLable = new Label(1, 10, "sfb_file");
sheet.addCell(asfi301FileLable);
Label aimt324Lable = new Label(0, 11, "調撥單(aimt324)");
sheet.addCell(aimt324Lable);
Label aimt324FileLable = new Label(1, 11, "imm_file");
sheet.addCell(aimt324FileLable);
Label asfi511Lable = new Label(0, 12, "發料單(asfi511)");
sheet.addCell(asfi511Lable);
Label asfi511FileLable = new Label(1, 12, "sfp_file");
sheet.addCell(asfi511FileLable);
Label aqct410Lable = new Label(0, 13, "FQC(aqct410)");
sheet.addCell(aqct410Lable);
Label aqct410FileLable = new Label(1, 13, "qcf_file");
sheet.addCell(aqct410FileLable);
Label asft620Lable = new Label(0, 14, "入庫單(asft620)");
sheet.addCell(asft620Lable);
Label asft620FileLable = new Label(1, 14, "sfu_file");
sheet.addCell(asft620FileLable);
Label asft300Lable = new Label(0, 15, "工單生產報工(asft300)");
sheet.addCell(asft300Lable);
Label asft300FileLable = new Label(1, 15, "srf_file");
sheet.addCell(asft300FileLable);
Label axmt700Lable = new Label(0, 17, "銷退單(axmt700)");
sheet.addCell(axmt700Lable);
Label axmt700FileLable = new Label(1, 17, "oha_file");
sheet.addCell(axmt700FileLable);
Label aimt302Lable = new Label(0, 18, "倉庫雜收單(aimt302)");
sheet.addCell(aimt302Lable);
Label aimt302FileLable = new Label(1, 18, "ina_file");
sheet.addCell(aimt302FileLable);
Label aimt312Lable = new Label(0, 19, "WIP雜收單(aimt312)");
sheet.addCell(aimt312Lable);
Label aimt312FileLable = new Label(1, 19, "ina_file");
sheet.addCell(aimt312FileLable);
Label aimt301Lable = new Label(0, 20, "倉庫雜發單(aimt301)");
sheet.addCell(aimt301Lable);
Label aimt301FileLable = new Label(1, 20, "ina_file");
sheet.addCell(aimt301FileLable);
Label aimt311Lable = new Label(0, 21, "WIP雜發單(aimt311)");
sheet.addCell(aimt311Lable);
Label aimt311FileLable = new Label(1, 21, "ina_file");
sheet.addCell(aimt311FileLable);
//二維String對象陣列，保存預執行的SQL及該SQL執行結果寫入Excel Sheet中的行、列位置座標
String[][] oracleSql = new String[][]{
{"select count(*) from oea_file where oeaconf='Y' and oea02 >= to_date('2012/12/01','YYYY/MM/DD') and oea02 <= to_date('2012/12/18','YYYY/MM/DD')","3","2"},
{"select count(*) from oea_file where oeaconf='N' and oea02 >= to_date('2012/12/01','YYYY/MM/DD') and oea02 <= to_date('2012/12/18','YYYY/MM/DD')","3","3"},
{"select count(*) from oea_file where oeaconf='X' and oea02 >= to_date('2012/12/01','YYYY/MM/DD') and oea02 <= to_date('2012/12/18','YYYY/MM/DD')","3","5"},
{"select count(*) from oea_file where oea02 >= to_date('2012/12/01','YYYY/MM/DD') and oea02 <= to_date('2012/12/18','YYYY/MM/DD')","3","6"},
{"select count(*) from oga_file where ogaconf='Y' and ogapost='Y' AND (oga09='2' OR oga09='4' OR oga09='6') and oga02 >= to_date('2012/12/01','YYYY/MM/DD')and oga02 <= to_date('2012/12/18','YYYY/MM/DD')","4","2"},
{"select count(*) from oga_file where ogaconf='N' and (oga09='2' OR oga09='4' OR oga09='6') and oga69 >= to_date('2012/12/01','YYYY/MM/DD') and oga69 <= to_date('2012/12/18','YYYY/MM/DD')","4","3"},
{"select count(*) from oga_file where ogaconf='Y' and ogapost='N' AND (oga09='2' OR oga09='4' OR oga09='6') and oga69 >= to_date('2012/12/01','YYYY/MM/DD')and oga69 <= to_date('2012/12/18','YYYY/MM/DD')","4","4"},
{"select count(*) from oga_file where ogaconf='X' and (oga09='2' OR oga09='4' OR oga09='6') and oga69 >= to_date('2012/12/01','YYYY/MM/DD') and oga69 <= to_date('2012/12/18','YYYY/MM/DD')","4","5"},
{"select count(*) from oga_file where oga69 >= to_date('2012/12/01','YYYY/MM/DD') and oga69 <= to_date('2012/12/18','YYYY/MM/DD') and (oga09='2' OR oga09='4' OR oga09='6')","4","6"},
{"select count(*) from pmk_file where pmk18='Y' and pmk04 >= to_date('2012/12/01','YYYY/MM/DD') and pmk04<= to_date('2012/12/18','YYYY/MM/DD')","5","2"},
{"select count(*) from pmk_file where pmk18='N' and pmk04 >= to_date('2012/12/01','YYYY/MM/DD') and pmk04<= to_date('2012/12/18','YYYY/MM/DD')","5","3"},
{"select count(*) from pmk_file where pmk18='X' and pmk04 >= to_date('2012/12/01','YYYY/MM/DD') and pmk04<= to_date('2012/12/18','YYYY/MM/DD')","5","5"},
{"select count(*) from pmk_file where pmk04 >= to_date('2012/12/01','YYYY/MM/DD') and pmk04 <= to_date('2012/12/18','YYYY/MM/DD')","5","6"},
{"select count(*) from pmm_file where pmm25>='1' and pmm04 >= to_date('2012/12/01','YYYY/MM/DD') and pmm04<= to_date('2012/12/18','YYYY/MM/DD')","6","2"},
{"select count(*) from pmm_file where pmm18='N' and pmm04 >= to_date('2012/12/01','YYYY/MM/DD') and pmm04<= to_date('2012/12/18','YYYY/MM/DD')","6","3"},
{"select count(*) from pmm_file where pmm18='X' and pmm04 >= to_date('2012/12/01','YYYY/MM/DD') and pmm04<= to_date('2012/12/18','YYYY/MM/DD')","6","5"},
{"select count(*) from pmm_file where pmm04 >= to_date('2012/12/01','YYYY/MM/DD') and pmm04<= to_date('2012/12/18','YYYY/MM/DD')","6","6"},
{"select count(*) from rva_file where rvaconf='Y' and rva06 >= to_date('2012/12/01','YYYY/MM/DD') and rva06<= to_date('2012/12/18','YYYY/MM/DD')","7","2"},
{"select count(*) from rva_file where rvaconf='N' and rva06 >= to_date('2012/12/01','YYYY/MM/DD') and rva06<= to_date('2012/12/18','YYYY/MM/DD')","7","3"},
{"select count(*) from rva_file where rvaconf='X' and rva06 >= to_date('2012/12/01','YYYY/MM/DD') and rva06<= to_date('2012/12/18','YYYY/MM/DD')","7","5"},
{"select count(*) from rva_file where rva06 >= to_date('2012/12/01','YYYY/MM/DD') and rva06<= to_date('2012/12/18','YYYY/MM/DD')","7","6"},
{"select count(*) from qcs_file where qcs14='Y' and (qcs00='1' OR qcs00='2') and qcs04 >= to_date('2012/12/01','YYYY/MM/DD') and qcs04 <= to_date('2012/12/18','YYYY/MM/DD')","8","2"},
{"select count(*) from qcs_file where qcs14='N' and (qcs00='1' OR qcs00='2') and qcs04 >= to_date('2012/12/01','YYYY/MM/DD') and qcs04<= to_date('2012/12/18','YYYY/MM/DD')","8","3"},
{"select count(*) from qcs_file where qcs14='X' and (qcs00='1' OR qcs00='2') and qcs04 >= to_date('2012/12/01','YYYY/MM/DD') and qcs04 <= to_date('2012/12/18','YYYY/MM/DD')","8","5"},
{"select count(*) from qcs_file where qcs04 >= to_date('2012/12/01','YYYY/MM/DD') and qcs04 <= to_date('2012/12/18','YYYY/MM/DD') and (qcs00='1' OR qcs00='2')","8","6"},
{"select count(*) from rvu_file where rvuconf='Y' and rvu03 >= to_date('2012/12/01','YYYY/MM/DD') and rvu03 <= to_date('2012/12/18','YYYY/MM/DD')","9","2"},
{"select count(*) from rvu_file where rvuconf='N' and rvu03 >= to_date('2012/12/01','YYYY/MM/DD') and rvu03 <= to_date('2012/12/18','YYYY/MM/DD')","9","3"},
{"select count(*) from rvu_file where rvuconf='X' and rvu03 >= to_date('2012/12/01','YYYY/MM/DD') and rvu03 <= to_date('2012/12/18','YYYY/MM/DD')","9","5"},
{"select count(*) from rvu_file where rvu03 >= to_date('2012/12/01','YYYY/MM/DD') and rvu03 <= to_date('2012/12/18','YYYY/MM/DD')","9","6"},
{"select count(*) from sfb_file where sfb87='Y' and sfb81 >= to_date('2012/12/01','YYYY/MM/DD') and sfb81 <= to_date('2012/12/18','YYYY/MM/DD')","11","2"},
{"select count(*) from sfb_file where sfb87='N' and sfb81 >= to_date('2012/12/01','YYYY/MM/DD') and sfb81 <= to_date('2012/12/18','YYYY/MM/DD')","11","3"},
{"select count(*) from sfb_file where sfb87='X' and sfb81 >= to_date('2012/12/01','YYYY/MM/DD') and sfb81 <= to_date('2012/12/18','YYYY/MM/DD')","11","5"},
{"select count(*) from sfb_file where sfb81 >= to_date('2012/12/01','YYYY/MM/DD') and sfb81 <= to_date('2012/12/18','YYYY/MM/DD')","11","6"},
{"select count(*) from imm_file where immconf='Y' and imm03='Y' and imm02 >= to_date('2012/12/01','YYYY/MM/DD') and imm02 <= to_date('2012/12/18','YYYY/MM/DD')","12","2"},
{"select count(*) from imm_file where immconf='N' and imm02 >= to_date('2012/12/01','YYYY/MM/DD') and imm02 <= to_date('2012/12/18','YYYY/MM/DD')","12","3"},
{"select count(*) from imm_file where immconf='Y' and imm03='N' and imm02 >= to_date('2012/12/01','YYYY/MM/DD') and imm02 <= to_date('2012/12/18','YYYY/MM/DD')","12","4"},
{"select count(*) from imm_file where imm02 >= to_date('2012/12/01','YYYY/MM/DD') and imm02 <= to_date('2012/12/18','YYYY/MM/DD')","12","6"},
{"select count(*) from sfp_file where sfp04='Y' and sfp02 >= to_date('2012/12/01','YYYY/MM/DD')and sfp02 <= to_date('2012/12/18','YYYY/MM/DD')","13","2"},
{"select count(*) from sfp_file where sfpconf='N' and sfp02 >= to_date('2012/12/01','YYYY/MM/DD') and sfp02 <= to_date('2012/12/18','YYYY/MM/DD')","13","3"},
{"select count(*) from sfp_file where sfpconf='Y' and sfp04='N' and sfp02 >= to_date('2012/12/01','YYYY/MM/DD') and sfp02 <= to_date('2012/12/18','YYYY/MM/DD')","13","4"},
{"select count(*) from sfp_file where sfpconf='X' and sfp02 >= to_date('2012/12/01','YYYY/MM/DD') and sfp02 <= to_date('2012/12/18','YYYY/MM/DD')","13","5"},
{"select count(*) from sfp_file where sfp02 >= to_date('2012/12/01','YYYY/MM/DD') and sfp02 <= to_date('2012/12/18','YYYY/MM/DD')","13","6"},
{"select count(*) from qcf_file where qcf14='Y' and qcf04 >= to_date('2012/12/01','YYYY/MM/DD') and qcf04 <= to_date('2012/12/18','YYYY/MM/DD')","14","2"},
{"select count(*) from qcf_file where qcf14='N' and qcf04 >= to_date('2012/12/01','YYYY/MM/DD') and qcf04 <= to_date('2012/12/18','YYYY/MM/DD')","14","3"},
{"select count(*) from qcf_file where qcf14='X' and qcf04 >= to_date('2012/12/01','YYYY/MM/DD') and qcf04 <= to_date('2012/12/18','YYYY/MM/DD')","14","5"},
{"select count(*) from qcf_file where qcf04 >= to_date('2012/12/01','YYYY/MM/DD') and qcf04 <= to_date('2012/12/18','YYYY/MM/DD')","14","6"},
{"select count(*) from sfu_file where sfuconf='Y' AND sfupost='Y' and sfu02 >= to_date('2012/12/01','YYYY/MM/DD') and sfu02 <= to_date('2012/12/18','YYYY/MM/DD')","15","2"},
{"select count(*) from sfu_file where sfuconf='N' and sfu14 >= to_date('2012/12/01','YYYY/MM/DD') and sfu14 <= to_date('2012/12/18','YYYY/MM/DD')","15","3"},
{"select count(*) from sfu_file where sfuconf='Y' AND sfupost='N' and sfu14 >= to_date('2012/12/01','YYYY/MM/DD') and sfu14 <= to_date('2012/12/18','YYYY/MM/DD')","15","4"},
{"select count(*) from sfu_file where sfuconf='X' and sfu14 >= to_date('2012/12/01','YYYY/MM/DD') and sfu14 <= to_date('2012/12/18','YYYY/MM/DD')","15","5"},
{"select count(*) from sfu_file where sfu02 >= to_date('2012/12/01','YYYY/MM/DD') and sfu02 <= to_date('2012/12/18','YYYY/MM/DD')","15","6"},
{"select count(*) from srf_file where srfconf='Y' and srf07='2' and srf02 >= to_date('2012/12/01','YYYY/MM/DD') and srf02 <= to_date('2012/12/18','YYYY/MM/DD')","16","2"},
{"select count(*) from srf_file where srfconf='N' and srf07='2' and srf02 >= to_date('2012/12/01','YYYY/MM/DD') and srf02 <= to_date('2012/12/18','YYYY/MM/DD')","16","3"},
{"select count(*) from srf_file where srfconf='X' and srf07='2' and srf02 >= to_date('2012/12/01','YYYY/MM/DD') and srf02 <= to_date('2012/12/18','YYYY/MM/DD')","16","5"},
{"select count(*) from srf_file where srf02 >= to_date('2012/12/01','YYYY/MM/DD') and srf02 <= to_date('2012/12/18','YYYY/MM/DD')","16","6"},
{"select count(*) from oha_file where ohaconf='Y' and oha02 >= to_date('2012/12/01','YYYY/MM/DD') and oha02 <= to_date('2012/12/18','YYYY/MM/DD')","18","2"},
{"select count(*) from oha_file where ohaconf='N' and oha02 >= to_date('2012/12/01','YYYY/MM/DD') and oha02 <= to_date('2012/12/18','YYYY/MM/DD')","18","3"},
{"select count(*) from oha_file where ohapost='N' and oha02 >= to_date('2012/12/01','YYYY/MM/DD') and oha02 <= to_date('2012/12/18','YYYY/MM/DD')","18","4"},
{"select count(*) from oha_file where ohaconf='X' and oha02 >= to_date('2012/12/01','YYYY/MM/DD') and oha02 <= to_date('2012/12/18','YYYY/MM/DD')","18","5"},
{"select count(*) from oha_file where oha02 >= to_date('2012/12/01','YYYY/MM/DD') and oha02 <= to_date('2012/12/18','YYYY/MM/DD')","18","6"},
{"select count(*) from ina_file where inaconf='Y' and inapost='Y' and ina00='3' and ina02 >= to_date('2012/12/01','YYYY/MM/DD') and ina02 <= to_date('2012/12/18','YYYY/MM/DD')","19","2"},
{"select count(*) from ina_file where inaconf='N' and ina00='3' and ina03 >= to_date('2012/12/01','YYYY/MM/DD') and ina03 <= to_date('2012/12/18','YYYY/MM/DD')","19","3"},
{"select count(*) from ina_file where inaconf='Y' and inapost='N' and ina00='3' and ina03 >= to_date('2012/12/01','YYYY/MM/DD') and ina03 <= to_date('2012/12/18','YYYY/MM/DD')","19","4"},
{"select count(*) from ina_file where inaconf='X' and ina00='3' and ina03 >= to_date('2012/12/01','YYYY/MM/DD') and ina03 <= to_date('2012/12/18','YYYY/MM/DD')","19","5"},
{"select count(*) from ina_file where ina02 >= to_date('2012/12/01','YYYY/MM/DD') and ina02 <= to_date('2012/12/18','YYYY/MM/DD') and ina00='3'","19","6"},
{"select count(*) from ina_file where inaconf='Y' and inapost='Y' and ina00='4' and ina02 >= to_date('2012/12/01','YYYY/MM/DD') and ina02 <= to_date('2012/12/18','YYYY/MM/DD')","20","2"},
{"select count(*) from ina_file where inaconf='N' and ina00='4' and ina03 >= to_date('2012/12/01','YYYY/MM/DD') and ina03 <= to_date('2012/12/18','YYYY/MM/DD')","20","3"},
{"select count(*) from ina_file where inaconf='Y' and inapost='N' and ina00='4' and ina03 >= to_date('2012/12/01','YYYY/MM/DD') and ina03 <= to_date('2012/12/18','YYYY/MM/DD')","20","4"},
{"select count(*) from ina_file where inaconf='X' and ina00='4' and ina03 >= to_date('2012/12/01','YYYY/MM/DD') and ina03 <= to_date('2012/12/18','YYYY/MM/DD')","20","5"},
{"select count(*) from ina_file where ina02 >= to_date('2012/12/01','YYYY/MM/DD') and ina02 <= to_date('2012/12/18','YYYY/MM/DD') and ina00='4'","20","6"},
{"select count(*) from ina_file where inaconf='Y' and inapost='Y' and ina00='1' and ina02 >= to_date('2012/12/01','YYYY/MM/DD') and ina02 <= to_date('2012/12/18','YYYY/MM/DD')","21","2"},
{"select count(*) from ina_file where inaconf='N' and ina00='1' and ina03 >= to_date('2012/12/01','YYYY/MM/DD') and ina03 <= to_date('2012/12/18','YYYY/MM/DD')","21","3"},
{"select count(*) from ina_file where inaconf='Y' and inapost='N' and ina00='1' and ina03 >= to_date('2012/12/01','YYYY/MM/DD') and ina03 <= to_date('2012/12/18','YYYY/MM/DD')","21","4"},
{"select count(*) from ina_file where inaconf='X' and ina00='1' and ina03 >= to_date('2012/12/01','YYYY/MM/DD') and ina03 <= to_date('2012/12/18','YYYY/MM/DD')","21","5"},
{"select count(*) from ina_file where ina02 >= to_date('2012/12/01','YYYY/MM/DD') and ina02 <= to_date('2012/12/18','YYYY/MM/DD') and ina00='1'","21","6"},
{"select count(*) from ina_file where inaconf='Y' and inapost='Y' and ina00='2' and ina02 >= to_date('2012/12/01','YYYY/MM/DD') and ina02 <= to_date('2012/12/18','YYYY/MM/DD')","22","2"},
{"select count(*) from ina_file where inaconf='N' and ina00='2' and ina03 >= to_date('2012/12/01','YYYY/MM/DD') and ina03 <= to_date('2012/12/18','YYYY/MM/DD')","22","3"},
{"select count(*) from ina_file where inaconf='Y' and inapost='N' and ina00='2' and ina03 >= to_date('2012/12/01','YYYY/MM/DD') and ina03 <= to_date('2012/12/18','YYYY/MM/DD')","22","4"},
{"select count(*) from ina_file where inaconf='X' and ina00='2' and ina03 >= to_date('2012/12/01','YYYY/MM/DD') and ina03 <= to_date('2012/12/18','YYYY/MM/DD')","22","5"},
{"select count(*) from ina_file where ina02 >= to_date('2012/12/01','YYYY/MM/DD') and ina02 <= to_date('2012/12/18','YYYY/MM/DD') and ina00='2'","22","6"}};
//連接新系統Oracle資料庫統計每日單據數量信息
//載入Java連接Oracel的JDBC驅動
Class.forName(ORACLEDBDRIVER);
//依驅動、用戶名、密碼得到Oracle的連接
connOracle = DriverManager.getConnection(ORACLEDBURL, ORACLEDBUSER[i], ORACLEDBPASSWORD[i]);
//迴圈String二維陣列中保存的SQL語句
for(int j=0;j<oracleSql.length;j++){
System.out.println(oracleSql[j][0]);
pstmOracle = connOracle.prepareStatement(oracleSql[j][0]);
rsOracle = pstmOracle.executeQuery();
//將統計結果寫入Excel文檔中相應的位置中
while(rsOracle.next()){
int sheetRow = Integer.parseInt(oracleSql[j][1]);
int sheetCol = Integer.parseInt(oracleSql[j][2]);
String sheetCellCount = Integer.valueOf(rsOracle.getInt(1)).toString();
System.out.println(rsOracle.getInt(1));
Label cellCountLable = new Label(sheetCol,sheetRow-1,sheetCellCount);
sheet.addCell(cellCountLable);
}
}
}
} catch (IOException e) {
e.printStackTrace();
} catch (RowsExceededException e) {
e.printStackTrace();
} catch (WriteException e) {
e.printStackTrace();
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
}finally{
//最後關閉Oracel與Informix資料庫的連接
try {
if(rsOracle != null){
rsOracle.close();
rsOracle = null;
}
if(pstmOracle != null){
pstmOracle.close();
pstmOracle = null;
}
if(connOracle != null){
connOracle.close();
connOracle = null;
}
} catch (SQLException e) {
e.printStackTrace();
}
}
try {
workbook.write();
workbook.close();
} catch (IOException e) {
e.printStackTrace();
} catch (WriteException e) {
e.printStackTrace();
}
}
}
扫一扫
评论
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
查看更多评论
添加红包
