java 统计TIPTOP单据信息

最新推荐文章于 2020-06-01 09:01:17 发布
最新推荐文章于 2020-06-01 09:01:17 发布
阅读量557 收藏 1
点赞数
分类专栏： MyEclipse
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/linjielong88/article/details/8332056
版权 
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();
		}
	}
}

林杰龙
关注
专栏目录
java 按照部门统计TIPTOP单据数量
linjielong88的专栏
01-14 1150
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; imp
TIPTOP javamail设定
01-17
鼎捷tiptop系统自动发邮件javamail设定，首先设定系统相关文件和系统参数，测试完成后，维护相关收件资料
tiptop调用java解析json案例
热门推荐
四方木
06-01 9万+
tiptop的4gl环境默认不支持解析jaon数据，通过导入java包，可以实现解析，以下是实际案例 一：环境 1：GP环境：TOPGP 5.2以上 2：jar包：fastjson.jar 二：导入方式 1：将fastjson.jar包下载下来，导入/u1/topprod/tiptop/ds4gl2/bin目录 2：打开/u1/topprod/tiptop/ds4gl2/bin/tiptop_env文件，在CLASSPATH上添加$TOP/ds4gl2/bin/fastjson.jar这一段（没有CLASS
发出商品管理与统计(java,附源码文档及测试数据)
06-20
将入库出库的电子表格导入数据库,生成<库存余额表>,<库位库龄分析汇总表>等,具有用户管理,结账,导入商品基本信息,审核与取消审核等功能. 附源码,文档及测试数据,供感兴趣的同学修改完备.
java实现的可视化统计商品销售
12-17
java实现的可视化统计商品销售，很方便可以统计，当你输入行数按回车，就可以进行统计
Tiptop&Easyflow集成说明_新单据集成样例.pdf
09-23
当在Tiptop系统中执行作业审核时，系统会将待签核的单据信息以XML格式发送到Easyflow，然后在Easyflow中呈现为电子表单，供指定的签核人员审批。一旦审批完成，Easyflow会将结果反馈回Tiptop，实现了双向的信息交互...
tiptop TOPGP-5.3数据字典.chm
06-01
鼎捷ERP tiptop TOPGP5.3数据库字典
鼎捷易拓TIPTOP开发视频详细讲解.zip
10-27
《鼎捷易拓TIPTOP开发视频详细讲解》是一份专为鼎捷易拓TIPTOP GP/T100系统运维者设计的教育资源。该压缩包包含了一系列的视频教程，旨在帮助用户深入理解和掌握该系统的开发与维护技能。 1. **水晶报表(Crystal ...
鼎捷易拓TIPTOP单档双档开发视频详细讲解
04-03
鼎捷易拓TIPTOP
鼎捷erp：TIPTOP GP5.3通用系统作业手册.rar
06-01
鼎捷ERP：TIPTOP GP5.3通用系统作业手册
TIPTOP與其他系統之WebService接口資料
09-05
TIPTOP與其他系統之WebService接口資料
TIPTOP GP 如何进行数据的导入、导出
05-22
TIPTOP GP 如何进行数据的导入、导出
Tiptop二次开发两大宝典
06-04
内含：Genero Studio 4GL官方开发手册/Genero BDL HB 2.0 简体中文版,打包分享！
TIPTOP JavaMail邮件通知功能
【小冯子】的专栏
09-21 4252
系统  TIPTOP GP 3.0 对于一些需要审核的单据，可以采用【邮件通知】的功能，知会审核人，详细介绍如下： 将发送邮件的处理写成一个共享函数，以便调用 *************************************************************************************************** 目录：/u1/topprod/t
tiptop使用java的poi包实现EXCEL导入导出功能
四方木
09-18 9万+
4gl可以调用java的poi包实现EXCEL的导入导出，今天分享一个EXCEL导入的功能！ 一：环境搭建 1：poi文件导入 首先下载POI文件（找不到的可以私信我），解压后上传到ERP的服务器中 一般位置放在$TOP/ds4gl2/bin/javaad/jar，当然，你也可以根据喜好自行定义！ 然后，在系统中编辑tiptop_env文件，路径在$TOP/bin/tiptop_env ......
TIPTOP内置函数
在人间
12-17 9547
函式名称：cl_about 程序名称：cl_about.4gl 用途：程序信息 传入参数：none 回传值：none 用法：CALL cl_about() 函式名称：cl_anykey 程序名称：cl_anykey.4gl 用途：请按任何键继续 传入参数：p_sw Not used 回传值：none 用法：CALL cl_anykey() 函式名称：cl_autotime 程序名称：cl_
TOPGP5.3：导入jar包并在4GL中引用
朝花夕拾
09-04 2143
TOPGP 5.3在导入jar包并在4GL中引用
4gl调用WEB API，实现JSON传递(Demo)
kuangkui的专栏
05-01 4860
测试环境: GP5.25 , fjs版本2.32,解析json所需要的jar依赖包 (PS: 如果没有记错是fjs2.32版本及以上才支持java bridge,所以GP 5.25以下的同学就不要用这种方式去测试)测试内容: 利用此fjs版本对java bridge的支持,实现4gl调用WEB API,实现json传递 测试步骤如下:1.下载本次测试需要的jar依赖包，百度搜索就有 “Java 解...
KKобтьу╬kkx.net.url
最新发布
09-14
KKобтьу╬kkx.net.url
TIPTOP内置函数详解与应用
以上是TIPTOP内置函数的一部分，它们涵盖了信息展示、用户交互、时间处理、界面生成和对象属性修改等多个方面。使用这些函数，开发者可以更加灵活地控制和优化TIPTOP系统中的应用程序行为，提高程序的用户体验和功能...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值