java poi very NB

package com;

import java.math.BigDecimal;
import java.sql.*;
import java.io.FileOutputStream;
import java.io.IOException;

import javax.swing.SwingUtilities;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFDataFormat;
import org.apache.poi.hssf.usermodel.HSSFFont;
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.hssf.util.HSSFColor;
import org.apache.poi.ss.util.Region; //import org.apache.poi.hssf.util.Region;
import org.apache.poi.ss.util.CellRangeAddress;

public class ttc {

	public static void main(String[] args) {
		GenTest p = new GenTest();
		Thread th = new Thread(p);
		// String url = "jdbc:oracle:thin:@192.168.0.118:1521:fingerdb";
		String url = "jdbc:oracle:thin:@11.54.32.11:1521:xcora";
		Statement sm = null;
		String command = null, month = null, year = null, yearq = null;
		ResultSet rs = null,rs1 = null;
		long dd, zr;
		BigDecimal E,I,YYE,NYE,ye[][] = new BigDecimal[7][17];
		leftPad ys = new leftPad();
		try {

			for (int i = 0; i < 7; i++) {

				for (int j = 0; j < 17; j++) {
					ye[i][j] = new BigDecimal(0.00);
				}
			}
		} catch (Exception ex) {
			ex.printStackTrace();
		}
		try {
			th.start();
			try {
				Class.forName("oracle.jdbc.driver.OracleDriver"); // 加载驱动
			} catch (ClassNotFoundException e) {
				System.out.println("Can not load Jdbc-Odbc Bridge Driver");
				System.err.print("ClassNotFoundException:");
				System.err.println(e.getMessage());
			}
			// Connection con = DriverManager.getConnection(url, "xxx",
			// "xxx");
			Connection con = DriverManager.getConnection(url, "xx", "xx");
			DatabaseMetaData dmd = con.getMetaData(); // DMD为连接的相应情况
			System.out.println("连接的数据库:" + dmd.getURL());
			System.out.println("驱动程序:" + dmd.getDriverName());
			sm = con.createStatement();
			System.out.println("输入字符展示报表:");
			java.text.SimpleDateFormat dformat = new java.text.SimpleDateFormat(
					"yyyy-MM-dd");
			command = "select max(fsrq) from zkmzz where fsrq<60000";
			rs1 = sm.executeQuery(command);
			rs1.next();
			// 数据库日期 (dd - 25569) *
			dd = rs1.getBigDecimal(1).longValue();
			zr = dd;
			System.out.println(dd);
			dd = (dd - 25568L) * 1000L * 60L * 60L * 24L - 8L * 1000L * 60L
					* 60L;
			java.util.Date e = new java.util.Date(dd);
			String dateandtime = dformat.format(e);
			System.out.println("科目总账日期" + dateandtime);
			java.util.Calendar c = java.util.Calendar.getInstance();
			c.setTime(e);
			Integer mon = c.get(java.util.Calendar.MONTH);
			Integer yea = c.get(java.util.Calendar.YEAR);
			year = yea.toString();
			Integer yeaq = c.get(java.util.Calendar.YEAR) - 1;
			yearq = yeaq.toString();
			month = mon.toString();
			year = yea.toString();

			// 本地系统日期
			java.util.Date d = new java.util.Date();
			String ddateandtime = dformat.format(d);

			HSSFWorkbook wb = new HSSFWorkbook();
			// 标题样式
			HSSFFont f = wb.createFont();
			f.setFontHeightInPoints((short) 25);// 字号
			f.setBoldweight(HSSFFont.BOLDWEIGHT_NORMAL);// 加粗
			HSSFCellStyle style = wb.createCellStyle();
			style.setFont(f);
			style.setAlignment(HSSFCellStyle.ALIGN_CENTER);// 左右居中
			style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);// 上下居中
			// 指标样式
			HSSFCellStyle style1 = wb.createCellStyle();
			style1.setAlignment(HSSFCellStyle.ALIGN_CENTER);// 左右居中
			style1.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);// 上下居中
			style1.setWrapText(true);

			// 建立新HSSFWorkbook对象
			HSSFSheet sheet = wb.createSheet("头寸日报" + ddateandtime);
			// 建立新的sheet对象
			HSSFRow row = sheet.createRow(0);
			row.setHeightInPoints(25);
			row.createCell(0).setCellValue("当前日期" + ddateandtime);
			row.createCell(3).setCellValue("科目总账日期" + dateandtime);
			System.out.println("当前日期" + ddateandtime + "  " + month);// 系统日期

			HSSFRow row1 = sheet.createRow(1);

			Region region = new Region(1, (short) 0, 1, (short) 14);
			sheet.addMergedRegion(region);
			// 得到所有区域
			sheet.getNumMergedRegions();
			HSSFCell cell = row1.createCell(0);
			cell.setCellValue("河南省许昌市农信办资金头寸日报表");
			cell.setCellStyle(style);

			HSSFRow row2 = sheet.createRow(2);
			row2.setHeightInPoints(50);
			Region region1 = new Region(2, (short) 0, 4, (short) 0);
			sheet.addMergedRegion(region1);
			// 得到所有区域
			sheet.getNumMergedRegions();
			HSSFCell cell0 = row2.createCell(0);
			cell0.setCellValue("机构");
			cell0.setCellStyle(style1);
			Region region2 = new Region(2, (short) 1, 4, (short) 1);
			sheet.addMergedRegion(region2);
			// 得到所有区域
			sheet.getNumMergedRegions();
			HSSFCell cell1 = row2.createCell(1);
			cell1.setCellValue("当期可用资金头寸");
			cell1.setCellStyle(style1);
			Region region3 = new Region(2, (short) 2, 4, (short) 2);
			sheet.addMergedRegion(region3);
			// 得到所有区域
			sheet.getNumMergedRegions();
			HSSFCell cell2 = row2.createCell(2);
			cell2.setCellValue("备付金率");
			cell2.setCellStyle(style1);
			Region region4 = new Region(2, (short) 3, 4, (short) 3);
			sheet.addMergedRegion(region4);
			// 得到所有区域
			sheet.getNumMergedRegions();
			HSSFCell cell3 = row2.createCell(3);
			cell3.setCellValue("库存现金");
			cell3.setCellStyle(style1);
			row1.createCell(15).setCellValue("单位：万元、%");
			Region region5 = new Region(2, (short) 4, 4, (short) 4);
			sheet.addMergedRegion(region5);
			// 得到所有区域
			sheet.getNumMergedRegions();
			HSSFCell cell4 = row2.createCell(4);
			cell4.setCellValue("超额准备金");
			cell4.setCellStyle(style1);
			Region region6 = new Region(2, (short) 5, 4, (short) 5);
			sheet.addMergedRegion(region6);
			// 得到所有区域
			sheet.getNumMergedRegions();
			HSSFCell cell5 = row2.createCell(5);
			cell5.setCellValue("存放同业款项");
			cell5.setCellStyle(style1);
			Region region7 = new Region(2, (short) 6, 4, (short) 6);
			sheet.addMergedRegion(region7);
			// 得到所有区域
			sheet.getNumMergedRegions();
			HSSFCell cell6 = row2.createCell(6);
			cell6.setCellValue("上存全国及省联社清算中心资金");
			cell6.setCellStyle(style1);

			Region region8 = new Region(2, (short) 7, 2, (short) 11);
			sheet.addMergedRegion(region8);
			// 得到所有区域
			sheet.getNumMergedRegions();
			HSSFCell cell7 = row2.createCell(7);
			cell7.setCellValue("各项存款");
			cell7.setCellStyle(style1);

			Region region9 = new Region(2, (short) 12, 2, (short) 16);
			sheet.addMergedRegion(region9);
			// 得到所有区域
			sheet.getNumMergedRegions();
			HSSFCell cell8 = row2.createCell(12);
			cell8.setCellValue("各项贷款");
			cell8.setCellStyle(style1);

			HSSFRow row3 = sheet.createRow(3);
			row3.setHeightInPoints(0);
			HSSFRow row4 = sheet.createRow(4);
			row4.setHeightInPoints(50);
			HSSFRow row5 = sheet.createRow(5);

			row5.createCell(0).setCellValue("合计");
			row4.createCell(7).setCellValue("余额");
			row4.createCell(8).setCellValue("较上日+-");
			row4.createCell(9).setCellValue("较月初+- ");
			row4.createCell(10).setCellValue("较年初+-");
			row4.createCell(11).setCellValue("年增长率");
			row4.createCell(12).setCellValue("余额");
			row4.createCell(13).setCellValue("较上日+-");
			row4.createCell(14).setCellValue("较月初+-");
			row4.createCell(15).setCellValue("较年初+-");
			row4.createCell(16).setCellValue("年增长率");

			// 表头

			try {

				for (int i = 0; i < 6; i++) {
					HSSFRow rowx = sheet.createRow(i + 6);
					switch (i) {
					case 0:
						ye[i][16] = new BigDecimal(0.1); // 准备金率
						rowx.createCell(0).setCellValue("禹州市");
						break;
					case 1:
						ye[i][16] = new BigDecimal(0.11);
						rowx.createCell(0).setCellValue("长葛市");
						break;
					case 2:
						ye[i][16] = new BigDecimal(0.1);
						rowx.createCell(0).setCellValue("许昌县");
						break;
					case 3:
						ye[i][16] = new BigDecimal(0.1);
						rowx.createCell(0).setCellValue("鄢陵");
						break;
					case 4:
						ye[i][16] = new BigDecimal(0.1);
						rowx.createCell(0).setCellValue("魏都区");
						break;
					case 5:
						ye[i][16] = new BigDecimal(0.11);
						rowx.createCell(0).setCellValue("襄城县");
						break;

					}
					// System.out.print(" "+i);
					// System.out.print(" "+dd);//检查语句

					// * 当
					// * 前
					// * 各
					// * 项
					// * 存
					// * 款
					command = "select sum(ye) from zkmzz z where jgm like '1613"
							+ (i + 1)
							+ "%' and z.kmh in (select k.kmh from cmp_bb_cwzbmx k where k.zbbh=2000)";
					rs1 = sm.executeQuery(command); // 执行查询
					rs1.next();
					ye[i][6] = rs1.getBigDecimal(1).negate();
					rowx.createCell(7).setCellValue(
							ye[i][6].divide(new BigDecimal(10000), 1,
									BigDecimal.ROUND_CEILING).toString());
					System.out.print(ys.make(ye[i][6]));
					// 较
					// 昨
					// 日
					// 增
					// 减
					command = "select sum(rcye-ye) from zkmzz z where jgm  like '1613"
							+ (i + 1)
							+ "%' and fsrq="
							+ zr
							+ " and z.kmh in (select k.kmh from cmp_bb_cwzbmx k where k.zbbh=2000)";
					rs1 = sm.executeQuery(command);
					rs1.next();
					ye[i][7] = rs1.getBigDecimal(1);
					rowx.createCell(8).setCellValue(
							ye[i][7].divide(new BigDecimal(10000), 1,
									BigDecimal.ROUND_CEILING).toString());
					System.out.print(ys.make(ye[i][7]));// 科目总账明细出数

					// * *各
					// * *项
					// * *存
					// * *款
					// * *较
					// * *月
					// * *初
					command = "select sum(ye) from zkmyzz z where jgm like '1613"
							+ (i + 1)
							+ "%' and nf="
							+ year
							+ " and yf="
							+ month
							+ " and z.kmh in (select k.kmh from cmp_bb_cwzbmx k where k.zbbh=2000)";
					rs1 = sm.executeQuery(command);
					rs1.next();
					YYE = rs1.getBigDecimal(1);
					ye[i][8] = ye[i][6].subtract(YYE.negate());
					rowx.createCell(9).setCellValue(
							ye[i][8].divide(new BigDecimal(10000), 1,
									BigDecimal.ROUND_CEILING).toString());
					System.out.print(ys.make(ye[i][8]));
					// * * *各
					// * * *项
					// * * *存
					// * * *款
					// * * *较
					// * * *年
					// * * *初
					command = "select sum(ye) from zkmyzz z where jgm like '1613"
							+ (i + 1)
							+ "%' and nf="
							+ yearq
							+ " and yf=12 and z.kmh in (select k.kmh from cmp_bb_cwzbmx k where k.zbbh=2000)";
					rs1 = sm.executeQuery(command); // 执行查询
					rs1.next();
					NYE = rs1.getBigDecimal(1);
					ye[i][9] = ye[i][6].subtract(NYE.negate());
					// System.out.println(DQYE.negate().subtract(NYE.negate()));
					rowx.createCell(10).setCellValue(
							ye[i][9].divide(new BigDecimal(10000), 1,
									BigDecimal.ROUND_CEILING).toString());
					ye[i][10] = ye[i][9].divide(NYE.negate(), 3,
							BigDecimal.ROUND_CEILING).movePointRight(2);
					rowx.createCell(11).setCellValue(ye[i][10].toString());
					System.out.print(ys.make(ye[i][9]));
					System.out.print(ys.make(ye[i][10]));

					// * 当
					// * 前
					// * 各
					// * 项
					// * 贷
					// * 款
					command = "select sum(ye) from zkmzz z where jgm like '1613"
							+ (i + 1)
							+ "%' and z.kmh in (select k.kmh from cmp_bb_cwzbmx k where k.zbbh=1000)";
					rs1 = sm.executeQuery(command); // 执行查询
					rs1.next();
					ye[i][11] = rs1.getBigDecimal(1);
					rowx.createCell(12).setCellValue(
							ye[i][11].divide(new BigDecimal(10000), 1,
									BigDecimal.ROUND_CEILING).toString());
					System.out.print(ys.make(ye[i][11]));
					// 较
					// 昨
					// 日
					// 增
					// 减

					command = "select sum(rcye-ye) from zkmzz z where jgm like '1613"
							+ (i + 1)
							+ "%' and fsrq="
							+ zr
							+ " and z.kmh in (select k.kmh from cmp_bb_cwzbmx k where k.zbbh=1000)";
					rs1 = sm.executeQuery(command);
					rs1.next();
					ye[i][12] = rs1.getBigDecimal(1);
					rowx.createCell(13).setCellValue(
							ye[i][12].divide(new BigDecimal(10000), 1,
									BigDecimal.ROUND_CEILING).toString());
					System.out.print(ys.make(ye[i][12]));

					// * *各
					// * *项
					// * *贷
					// * *款
					// * *较
					// * *月
					// * *初
					command = "select sum(ye) from zkmyzz z where jgm like '1613"
							+ (i + 1)
							+ "%' and nf="
							+ year
							+ " and yf="
							+ month
							+ " and z.kmh in (select k.kmh from cmp_bb_cwzbmx k where k.zbbh=1000)";
					rs1 = sm.executeQuery(command); // 执行查询
					rs1.next();
					YYE = rs1.getBigDecimal(1);
					ye[i][13] = ye[i][11].subtract(YYE);
					rowx.createCell(14).setCellValue(
							ye[i][13].divide(new BigDecimal(10000), 1,
									BigDecimal.ROUND_CEILING).toString());
					System.out.print(ys.make(ye[i][13]));

					// * * *各
					// * * *项
					// * * *贷
					// * * *款
					// * * *较
					// * * *年
					// * * *初

					command = "select sum(ye) from zkmyzz z where jgm like '1613"
							+ (i + 1)
							+ "%' and nf="
							+ yearq
							+ " and yf=12 and z.kmh in (select k.kmh from cmp_bb_cwzbmx k where k.zbbh=1000)";
					rs1 = sm.executeQuery(command); // 执行查询
					rs1.next();
					NYE = rs1.getBigDecimal(1);
					ye[i][14] = ye[i][11].subtract(NYE);

					rowx.createCell(15).setCellValue(
							ye[i][14].divide(new BigDecimal(10000), 1,
									BigDecimal.ROUND_CEILING).toString());
					ye[i][15] = ye[i][14].divide(NYE, 3,
							BigDecimal.ROUND_CEILING).movePointRight(2);
					rowx.createCell(16).setCellValue(ye[i][15].toString());
					System.out.print(ys.make(ye[i][14]));
					System.out.print(ys.make(ye[i][15]));

					/*
					 * 新增存贷比 各项贷款较年初+-/各项存款较年初+- XZCDB =
					 * ye[i][14].divide(ye[i][9], 3,
					 * BigDecimal.ROUND_CEILING).movePointRight(2);
					 * rowx.createCell(17).setCellValue(XZCDB.toString()); //
					 * 存贷比 当前贷款余额/当前存款余额 CDB = ye[i][11].divide(ye[i][6], 3,
					 * BigDecimal.ROUND_CEILING).movePointRight(2);
					 * rowx.createCell(18).setCellValue(CDB.toString());
					 */

					command = "select sum(ye) from zkmzz z where jgm like '1613"
							+ (i + 1)
							+ "%' and z.kmh in (select k.kmh from cmp_bb_cwzbmx k where k.zbbh=2070 or k.zbbh=2060)";
					rs1 = sm.executeQuery(command);
					rs1.next();
					ye[i][2] = rs1.getBigDecimal(1);
					rowx.createCell(3).setCellValue(
							ye[i][2].divide(new BigDecimal(10000), 1,
									BigDecimal.ROUND_CEILING).toString());
					System.out.print(ys.make(ye[i][2]));// 库存现金

					command = "select sum(ye) from zkmzz z where jgm like '1613"
							+ (i + 1)
							+ "%'  and z.kmh in (select k.kmh from cmp_bb_cwzbmx k where k.zbbh=2080)";
					rs = sm.executeQuery(command);
					rs.next();
					ye[i][4] = rs.getBigDecimal(1);
					rowx.createCell(5).setCellValue(
							ye[i][4].divide(new BigDecimal(10000), 1,
									BigDecimal.ROUND_CEILING).toString());
					System.out.print(ys.make(ye[i][4]));// 存放同业款项

					command = "select sum(ye) from zkmzz z where jgm like '1613"
							+ (i + 1)
							+ "%' and z.kmh in (select k.kmh from cmp_bb_cwzbmx k where k.zbbh=2090)";
					rs1 = sm.executeQuery(command);
					rs1.next();
					ye[i][5] = rs1.getBigDecimal(1);

					rowx.createCell(6).setCellValue(
							ye[i][5].divide(new BigDecimal(10000), 1,
									BigDecimal.ROUND_CEILING).toString());
					System.out.print(ys.make(ye[i][5]));// 上存全国及省联社清算中心资金

					command = "select sum(ye) from zkmzz z where jgm like '1613"
							+ (i + 1)
							+ "%'  and z.kmh in (select k.kmh from cmp_bb_cwzbmx k where k.zbbh=2051)";
					rs = sm.executeQuery(command);
					rs.next();
					E = rs.getBigDecimal(1);
					System.out.print(ys.make(E));// 准备金存款4存放中央银行款超额准备金（超出11%部分）

					command = "select sum(ye) from zkmzz z where jgm like '1613"
							+ (i + 1)
							+ "%'  and z.kmh like '2511%' or z.kmh like '2431%'";
					rs = sm.executeQuery(command);
					rs.next();
					I = rs.getBigDecimal(1).negate();
					System.out.print(ys.make(I) + "   |");// 保证金+应解汇款4存放中央银行款超额准备金（超出11%部分）

					ye[i][3] = (E.subtract((ye[i][6].subtract(I))
							.multiply(ye[i][16])));
					System.out.print(ye[i][3]);
					rowx.createCell(4).setCellValue(
							ye[i][3].divide(new BigDecimal(10000), 1,
									BigDecimal.ROUND_CEILING).toString());// 应解汇款4存放中央银行款超额准备金（超出11%部分）

					ye[i][0] = ye[i][2].add(ye[i][3]).add(ye[i][4]).add(
							ye[i][5]);
					rowx.createCell(1).setCellValue(
							ye[i][0].divide(new BigDecimal(10000), 1,
									BigDecimal.ROUND_CEILING).toString());// 当期可用资金头寸

					ye[i][1] = ye[i][0].divide(ye[i][6], 3,
							BigDecimal.ROUND_CEILING);
					rowx.createCell(2).setCellValue(
							ye[i][1].movePointRight(2).toString());// 备付金率

					System.out.println("  |");
					System.out.println(ys.make());
				}
				for (int i = 0; i < 16; i++) {
					ye[6][i] = new BigDecimal(0.00);

					for (int j = 0; j < 6; j++) {
						ye[6][i] = ye[6][i].add(ye[j][i]);

					}
					row5.createCell(i + 1).setCellValue(
							ye[6][i].divide(new BigDecimal(10000), 1,
									BigDecimal.ROUND_CEILING).toString());
				}

				ye[6][1] = ye[6][0].divide(ye[6][6], 3,
						BigDecimal.ROUND_CEILING).movePointRight(2);
				ye[6][10] = ye[6][9].divide(ye[6][6], 3,
						BigDecimal.ROUND_CEILING).movePointRight(2);
				ye[6][15] = ye[6][14].divide(ye[6][11], 3,
						BigDecimal.ROUND_CEILING).movePointRight(2);

				row5.createCell(2).setCellValue(ye[6][1].toString());
				row5.createCell(11).setCellValue(ye[6][10].toString());
				row5.createCell(16).setCellValue(ye[6][15].toString());
			} catch (Exception ex) {
				ex.printStackTrace();
			}

			FileOutputStream fileOut = new FileOutputStream("D:/经营日报/tc"
					+ ddateandtime + ".xls");
			wb.write(fileOut);
			fileOut.close();
			Runtime r = Runtime.getRuntime();
			r.exec("cmd   /c   start   " + "D:/经营日报/tc" + ddateandtime
							+ ".xls");

			System.out.println("结束");

		} catch (SQLException ex) {
			System.out.println("SQLException:");
			while (ex != null) {
				System.out.println("Message:" + ex.getMessage());
				ex = ex.getNextException();
			}
		} catch (Exception e) {
			System.out.println("IOException");
		}

	}
}