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;leftPad ys = new leftPad();try {for (int i = 0; i < 7; i++) {for (int j = 0; j < 17; j++) {ye = 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 = new BigDecimal(0.1); // 准备金率rowx.createCell(0).setCellValue("禹州市");break;case 1:ye = new BigDecimal(0.11);rowx.createCell(0).setCellValue("长葛市");break;case 2:ye = new BigDecimal(0.1);rowx.createCell(0).setCellValue("许昌县");break;case 3:ye = new BigDecimal(0.1);rowx.createCell(0).setCellValue("鄢陵");break;case 4:ye = new BigDecimal(0.1);rowx.createCell(0).setCellValue("魏都区");break;case 5:ye = 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 = rs1.getBigDecimal(1).negate();rowx.createCell(7).setCellValue(ye.divide(new BigDecimal(10000), 1,BigDecimal.ROUND_CEILING).toString());System.out.print(ys.make(ye));// 较// 昨// 日// 增// 减command = "select sum(rcye-ye) from zkmzz z where jgmlike '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 = rs1.getBigDecimal(1);rowx.createCell(8).setCellValue(ye.divide(new BigDecimal(10000), 1,BigDecimal.ROUND_CEILING).toString());System.out.print(ys.make(ye));// 科目总账明细出数// * *各// * *项// * *存// * *款// * *较// * *月// * *初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 = ye.subtract(YYE.negate());rowx.createCell(9).setCellValue(ye.divide(new BigDecimal(10000), 1,BigDecimal.ROUND_CEILING).toString());System.out.print(ys.make(ye));// * * *各// * * *项// * * *存// * * *款// * * *较// * * *年// * * *初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 = ye.subtract(NYE.negate());// System.out.println(DQYE.negate().subtract(NYE.negate()));rowx.createCell(10).setCellValue(ye.divide(new BigDecimal(10000), 1,BigDecimal.ROUND_CEILING).toString());ye = ye.divide(NYE.negate(), 3,BigDecimal.ROUND_CEILING).movePointRight(2);rowx.createCell(11).setCellValue(ye.toString());System.out.print(ys.make(ye));System.out.print(ys.make(ye));// * 当// * 前// * 各// * 项// * 贷// * 款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 = rs1.getBigDecimal(1);rowx.createCell(12).setCellValue(ye.divide(new BigDecimal(10000), 1,BigDecimal.ROUND_CEILING).toString());System.out.print(ys.make(ye));// 较// 昨// 日// 增// 减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 = rs1.getBigDecimal(1);rowx.createCell(13).setCellValue(ye.divide(new BigDecimal(10000), 1,BigDecimal.ROUND_CEILING).toString());System.out.print(ys.make(ye));// * *各// * *项// * *贷// * *款// * *较// * *月// * *初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 = ye.subtract(YYE);rowx.createCell(14).setCellValue(ye.divide(new BigDecimal(10000), 1,BigDecimal.ROUND_CEILING).toString());System.out.print(ys.make(ye));// * * *各// * * *项// * * *贷// * * *款// * * *较// * * *年// * * *初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 = ye.subtract(NYE);rowx.createCell(15).setCellValue(ye.divide(new BigDecimal(10000), 1,BigDecimal.ROUND_CEILING).toString());ye = ye.divide(NYE, 3,BigDecimal.ROUND_CEILING).movePointRight(2);rowx.createCell(16).setCellValue(ye.toString());System.out.print(ys.make(ye));System.out.print(ys.make(ye));/* * 新增存贷比 各项贷款较年初+-/各项存款较年初+- XZCDB = * ye.divide(ye, 3, * BigDecimal.ROUND_CEILING).movePointRight(2); * rowx.createCell(17).setCellValue(XZCDB.toString()); // * 存贷比 当前贷款余额/当前存款余额 CDB = ye.divide(ye, 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 = rs1.getBigDecimal(1);rowx.createCell(3).setCellValue(ye.divide(new BigDecimal(10000), 1,BigDecimal.ROUND_CEILING).toString());System.out.print(ys.make(ye));// 库存现金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 = rs.getBigDecimal(1);rowx.createCell(5).setCellValue(ye.divide(new BigDecimal(10000), 1,BigDecimal.ROUND_CEILING).toString());System.out.print(ys.make(ye));// 存放同业款项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 = rs1.getBigDecimal(1);rowx.createCell(6).setCellValue(ye.divide(new BigDecimal(10000), 1,BigDecimal.ROUND_CEILING).toString());System.out.print(ys.make(ye));// 上存全国及省联社清算中心资金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 = (E.subtract((ye.subtract(I)).multiply(ye)));System.out.print(ye);rowx.createCell(4).setCellValue(ye.divide(new BigDecimal(10000), 1,BigDecimal.ROUND_CEILING).toString());// 应解汇款4存放中央银行款超额准备金（超出11%部分）ye = ye.add(ye).add(ye).add(ye);rowx.createCell(1).setCellValue(ye.divide(new BigDecimal(10000), 1,BigDecimal.ROUND_CEILING).toString());// 当期可用资金头寸ye = ye.divide(ye, 3,BigDecimal.ROUND_CEILING);rowx.createCell(2).setCellValue(ye.movePointRight(2).toString());// 备付金率System.out.println("|");System.out.println(ys.make());}for (int i = 0; i < 16; i++) {ye = new BigDecimal(0.00);for (int j = 0; j < 6; j++) {ye = ye.add(ye);}row5.createCell(i + 1).setCellValue(ye.divide(new BigDecimal(10000), 1,BigDecimal.ROUND_CEILING).toString());}ye = ye.divide(ye, 3,BigDecimal.ROUND_CEILING).movePointRight(2);ye = ye.divide(ye, 3,BigDecimal.ROUND_CEILING).movePointRight(2);ye = ye.divide(ye, 3,BigDecimal.ROUND_CEILING).movePointRight(2);row5.createCell(2).setCellValue(ye.toString());row5.createCell(11).setCellValue(ye.toString());row5.createCell(16).setCellValue(ye.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");}}}http://dl.iteye.com/upload/attachment/240392/3c80cdf7-12df-31ee-862b-fc8225996a38.jpg
页:
[1]