2013年12月 那年的数据导出

	public String downloadAll(String[] _keys, Map<String, Object> maps)
			throws FileNotFoundException {
		// String url = "/";
		ServletContext sc = ServletActionContext.getServletContext();
		// String url = sc.getRealPath("/common/systemparameter/company.ini");//
		// 定义临时文件夹
		String path = sc.getRealPath("/temp");// 定义临时文件夹
		Map<String, Object> request = (Map) ActionContext.getContext().get(
				"request");
		String down_dealdate = this.getStartDate();
		//System.out.println(down_dealdate);
		SimpleDateFormat sd = new SimpleDateFormat("yyyy-MM-dd");
		if (down_dealdate == null || down_dealdate.equals("")) {
			down_dealdate = sd.format(new Date());
		}
		request.put("down_dealdate", down_dealdate);
		// -----------根据时间创建对应Excel==文档----------------------------------
		String dateTime = (String) DateFormat.getDateTimeInstance(
				DateFormat.LONG, DateFormat.LONG, Locale.CHINESE).format(
				new java.util.Date());
		String fileName = "" + dateTime + ".xls";
		File file = new File(path + "/" + fileName);
		HSSFWorkbook workbook = new HSSFWorkbook();
		// ----------------------------------读取所有部门信息根据部门创建对应的工作表-------------------------------------------
		// Properties pp = new Properties();
		// Map<String, Object> maps = new HashMap<String, Object>();
		// String[] _keys = null;
		// try {
		// pp.load(new FileInputStream(url));
		// String strValue = "";
		// if (!pp.isEmpty()) {
		// String[] ss = UtilItem.getPropertiesResult(pp, 1);
		// _keys = new String[ss.length];
		// for (int i = 0; i < ss.length; i++) {
		// strValue = ss[i];
		// if (strValue.equals(""))
		// continue;
		// maps.put(strValue.split("=")[0], strValue.split("=")[1]);
		// _keys[i] = strValue.split("=")[0];
		// }
		// }
		// } catch (Exception e) {
		// System.out.println("1");
		// e.printStackTrace();
		// }
		// *************************************查询部分**************************************
		DBUtil db = new DBUtil();
		Map<String, Object> in_map = new HashMap<String, Object>();
		Map<String, Object> out_map = new HashMap<String, Object>();
		Map<String, Object> seremit_map = new HashMap<String, Object>();
		Map<String, Object> seaccept_map = new HashMap<String, Object>();
		Map<String, Object> start_map = new HashMap<String, Object>();
		double seremit_t, seremit_f, seaccept_t, seaccept_f;//累计收入
		double seremit_o, seaccept_o;//支出
		double seremitt, seremitf, seacceptt, seacceptf;// 期初余额
		StringBuilder in = new StringBuilder();
		StringBuilder ou = new StringBuilder();
		StringBuilder io_sum_i = new StringBuilder();
		StringBuilder io_sum_o = new StringBuilder();
		StringBuilder star = new StringBuilder();
		in.append("SELECT iono,ioname,cflag FROM tb_Inputoutout WHERE iotype='1' ORDER BY iono ");
		ou.append("SELECT iono,ioname,cflag FROM tb_Inputoutout WHERE iotype='0' ORDER BY iono ");
		List<String> in_arr_t = new ArrayList<String>();
		List<String> in_arr_f = new ArrayList<String>();
		List<String> ou_arr_t = new ArrayList<String>();
		List<String> ou_arr_f = new ArrayList<String>();
		ResultSet rs = db.executeQuery(in.toString(), null);
		try {
			if (rs != null && rs.next()) {
				in_map.put(rs.getString("iono"), rs.getString("ioname"));
				if (rs.getInt("cflag") == 1) {
					in_arr_t.add(rs.getString("iono"));
				} else {
					in_arr_f.add(rs.getString("iono"));
				}
				while (rs.next()) {
					in_map.put(rs.getString("iono"), rs.getString("ioname"));
					if (rs.getInt("cflag") == 1) {
						in_arr_t.add(rs.getString("iono"));
					} else {
						in_arr_f.add(rs.getString("iono"));
					}
				}
			}
		} catch (SQLException e1) {
			e1.printStackTrace();
		}
		rs = db.executeQuery(ou.toString(), null);
		try {
			if (rs != null && rs.next()) {
				out_map.put(rs.getString("iono"), rs.getString("ioname"));
				if (rs.getInt("cflag") == 1) {
					ou_arr_t.add(rs.getString("iono"));
				} else {
					ou_arr_f.add(rs.getString("iono"));
				}
				while (rs.next()) {
					out_map.put(rs.getString("iono"), rs.getString("ioname"));
					if (rs.getInt("cflag") == 1) {
						ou_arr_t.add(rs.getString("iono"));
					} else {
						ou_arr_f.add(rs.getString("iono"));
					}
				}
			}
		} catch (SQLException e1) {
			e1.printStackTrace();
		}
		String _key = "";
		for (int _t = 0; _t < _keys.length; _t++) {
			_key = _keys[_t];
			star.delete(0, star.length());
			io_sum_i.delete(0, io_sum_i.length());
			io_sum_o.delete(0, io_sum_o.length());
			// where a.deptclass IN(SELECT b.sID FROM tb_compney b WHERE
			// b.pID='1') OR deptclass='2'
			// SELECT a.inandextype,SUM(a.ss) ss1,SUM(a.bb) ss2 ,SUM(a.dd) ss3
			// from v_sum_3 a
			// WHERE CONVERT(VARCHAR, a.adddate, 23) = '2014-03-29' AND
			// (a.deptclass IN(SELECT b.sID FROM tb_compney b WHERE b.pID='1')
			// OR a.deptclass='2') and iotype='1'
			// GROUP BY a.inandextype,a.cflag;
			io_sum_i.append("SELECT a.inandextype,SUM(a.ss) ss1,SUM(a.bb) ss2 from v_sum_2 a  WHERE CONVERT(VARCHAR, a.adddate, 23) = '"
					+ down_dealdate
					+ "' AND a.deptclass IN  "
					+ maps.get("d" + _key).toString()
					+ " and iotype='1' GROUP BY a.inandextype,a.cflag;");

			io_sum_o.append("SELECT a.inandextype,SUM(a.ss) ss1,SUM(a.bb) ss2 from v_sum_2 a  WHERE CONVERT(VARCHAR, a.adddate, 23) = '"
					+ down_dealdate
					+ "' AND a.deptclass IN  "
					+ maps.get("d" + _key).toString()
					+ " and  iotype='0' GROUP BY a.inandextype,a.cflag;");

			star.append("SELECT SUM(seremit_t) as seremit_t,SUM(seremit_f) as  seremit_f,SUM(seaccept_t) as  seaccept_t,SUM(seaccept_f)  as  seaccept_f FROM tb_Periodstart WHERE deptclass IN "
					+ maps.get("d" + _key).toString()
					+ "and CONVERT(VARCHAR, dateadd(dd,1,ctdate), 23)='"
					+ down_dealdate + "' GROUP BY ctdate");
			// -------------------------------------------收入支出类型名称-------------------------------------------------
			
//			System.out.println(io_sum_i.toString());
//			System.out.println(io_sum_o.toString());
//			System.out.println(star.toString());
			
			// -------------------------------------------初始值用于汇总数据-------------------------------
			rs = db.executeQuery(star.toString(), null);
			seremit_t = 0.0;
			seaccept_t = 0.0;
			seremit_f = 0.0;
			seaccept_f = 0.0;
			System.out.println("seremit_f---1"+seremit_f);
			seremitt = 0.0;
			seacceptt = 0.0;
			seremitf = 0.0;
			seacceptf = 0.0;
			
			start_map.clear();
			try {
				if (rs != null && rs.next()) {
					start_map.put("seremit_t", rs.getDouble("seremit_t"));
					start_map.put("seaccept_t", rs.getDouble("seaccept_t"));
					start_map.put("seremit_f", rs.getDouble("seremit_f"));
					start_map.put("seaccept_f", rs.getDouble("seaccept_f"));
					seremitt = rs.getDouble("seremit_t");
					seacceptt = rs.getDouble("seaccept_t");
					seremitf = rs.getDouble("seremit_f");
					seacceptf = rs.getDouble("seaccept_f");
					while (rs.next()) {
						start_map.put("seremit_t", rs.getDouble("seremit_t"));
						start_map.put("seaccept_t", rs.getDouble("seaccept_t"));
						start_map.put("seremit_f", rs.getDouble("seremit_f"));
						start_map.put("seaccept_f", rs.getDouble("seaccept_f"));
						seremitt = rs.getDouble("seremit_t");
						seacceptt = rs.getDouble("seaccept_t");
						seremitf = rs.getDouble("seremit_f");
						seacceptf = rs.getDouble("seaccept_f");
					}
				} else {
					start_map.put("seremit_t", 0.0);
					start_map.put("seaccept_t", 0.0);
					start_map.put("seremit_f", 0.0);
					start_map.put("seaccept_f", 0.0);
					seremitt = 0.0;
					seacceptt = 0.0;
					seremitf = 0.0;
					seacceptf = 0.0;
				}
			} catch (SQLException e1) {
				System.out.println("e1"+e1);
				
//				e1.printStackTrace();
			}
			// -------------------------------------------收入支出明细----------------------------------
			// a.inandextype,SUM(a.ss) ss1,SUM(a.bb) ss2 ,SUM(a.dd) ss3
			// 1 seremit_map ,2 seaccept_map ,3 secash_map
			rs = db.executeQuery(io_sum_i.toString(), null);
			if (seremit_map.size() > 0 || seaccept_map.size() > 0) {
				seremit_map.clear();
				seaccept_map.clear();
			}

			try {
				if (rs != null && rs.next()) {
					seremit_map.put(rs.getString("inandextype"),
							rs.getDouble("ss1"));
					seaccept_map.put(rs.getString("inandextype"),
							rs.getDouble("ss2"));
					while (rs.next()) {
						seremit_map.put(rs.getString("inandextype"),
								rs.getDouble("ss1"));
						seaccept_map.put(rs.getString("inandextype"),
								rs.getDouble("ss2"));
					}
					for (String str : in_map.keySet()) {
						if (!seremit_map.keySet().contains(str)) {
							seremit_map.put(str, 0.0);
						}
						if (!seaccept_map.keySet().contains(str)) {
							seaccept_map.put(str, 0.0);
						}
					}
				} else {
					for (String str : in_map.keySet()) {
						seremit_map.put(str, 0.0);
						seaccept_map.put(str, 0.0);
					}

				}
			} catch (SQLException e1) {
				e1.printStackTrace();
			}
			rs = db.executeQuery(io_sum_o.toString(), null);
			try {
				if (rs != null && rs.next()) {

					seremit_map.put(rs.getString("inandextype"),
							rs.getDouble("ss1"));

					seaccept_map.put(rs.getString("inandextype"),
							rs.getDouble("ss2"));

					while (rs.next()) {

						seremit_map.put(rs.getString("inandextype"),
								rs.getDouble("ss1"));

						seaccept_map.put(rs.getString("inandextype"),
								rs.getDouble("ss2"));

					}
					// -------添加没有数据的空项默认值------------------------------------
					for (String str : out_map.keySet()) {
						if (!seremit_map.keySet().contains(str)) {
							seremit_map.put(str, 0.0);
						}
						if (!seaccept_map.keySet().contains(str)) {
							seaccept_map.put(str, 0.0);
						}
					}
				} else {
					for (String str : out_map.keySet()) {
						seremit_map.put(str, 0.0);

						seaccept_map.put(str, 0.0);
					}
				}
			} catch (SQLException e1) {
				e1.printStackTrace();
			}

			try {
				file.createNewFile();
			} catch (IOException e) {
				e.printStackTrace();
			}

			HSSFSheet sheet = workbook.createSheet("Sheet" + (1 + _t));
			workbook.setSheetName(_t, maps.get(_keys[_t]).toString());
			sheet.setColumnWidth(0, 8000);// 第一列加宽
			// 创建行
			int rows = 0;
			sheet.createRow(rows++)
					.createCell(0)
					.setCellValue(
							new HSSFRichTextString(down_dealdate + "资金收支明细表"));
			// sheet.createRow(rows++).createCell(0)
			// .setCellValue(new HSSFRichTextString("项目"));

			HSSFRow row;
			row = sheet.createRow(rows++);
			row.createCell(0).setCellValue(new HSSFRichTextString("项目"));
			row.createCell(1).setCellValue(new HSSFRichTextString("现汇"));
			row.createCell(2).setCellValue(new HSSFRichTextString("承兑"));
			row.createCell(3).setCellValue(new HSSFRichTextString("小计"));
			/*****************************************************************/
			// --------------------初始值-----------------------
			row = sheet.createRow(rows++);
			HSSFCellStyle style = workbook.createCellStyle();
			Font font = workbook.createFont();
			font.setFontHeightInPoints((short) 10); // 字体大小
			font.setFontName("宋体");
			font.setBoldweight(Font.BOLDWEIGHT_BOLD); // 粗体
			font.setColor(HSSFColor.RED.index);
			style.setFont(font);
			style.setFillForegroundColor(HSSFColor.WHITE.index);
			HSSFCell cell;
			cell = row.createCell(0);
			cell.setCellStyle(style);
			cell.setCellValue(new HSSFRichTextString("一、期初余额"));
			row.createCell(1).setCellValue(seremitt + seremitf);
			row.createCell(2).setCellValue(seacceptt + seacceptf);
			row.createCell(3).setCellValue( seremitt + seremitf + seacceptt + seacceptf  );
			row = sheet.createRow(rows++);
			cell = row.createCell(0);
			cell.setCellStyle(style);
			cell.setCellValue(new HSSFRichTextString("其中:保证金及其他不可使用资金"));
			row.createCell(1).setCellValue(seremitf);
			row.createCell(2).setCellValue(seacceptf);
			row.createCell(3).setCellValue(seremitf + seacceptf);

			row = sheet.createRow(rows++);
			cell = row.createCell(0);
			cell.setCellStyle(style);
			cell.setCellValue(new HSSFRichTextString("      期初可用资金余额"));
			row.createCell(1).setCellValue(seremitt);
			row.createCell(2).setCellValue(seacceptt);
			row.createCell(3).setCellValue(seremitt + seacceptt);

			// ----------------添加收入行------
			int j = 1;
			for (int _i = 0; _i < in_arr_t.size(); _i++) {
				row = sheet.createRow(rows++);
				row.createCell(0).setCellValue(
						(j++)
								+ "."
								+ in_map.get(in_arr_t.get(_i).toString())
										.toString());
				row.createCell(1).setCellValue(
						Double.parseDouble(seremit_map.get(in_arr_t.get(_i))
								.toString()));
				row.createCell(2).setCellValue(
						Double.parseDouble(seaccept_map.get(in_arr_t.get(_i))
								.toString()));
				row.createCell(3).setCellFormula(
						"B" + rows + "+C" + rows);

				seremit_t += Double.parseDouble(seremit_map.get(in_arr_t.get(_i)).toString());
				seaccept_t += Double.parseDouble(seaccept_map.get(in_arr_t.get(_i)).toString());
			}

			for (int _i = 0; _i < in_arr_f.size(); _i++) {

				row = sheet.createRow(rows++);
				row.createCell(0).setCellValue(
						(j++) + "." + in_map.get(in_arr_f.get(_i)).toString());
				row.createCell(1).setCellValue(
						Double.parseDouble(seremit_map.get(in_arr_f.get(_i))
								.toString()));
				row.createCell(2).setCellValue(
						Double.parseDouble(seaccept_map.get(in_arr_f.get(_i))
								.toString()));
				row.createCell(3).setCellFormula(
						"B" + rows + "+C" + rows );
				seremit_f += (Double) seremit_map.get(in_arr_f.get(_i));
				seaccept_f += (Double) seaccept_map.get(in_arr_f.get(_i));
				
			}
			// -------收入汇总----------
			row = sheet.createRow(rows++);
			cell = row.createCell(0);
			cell.setCellStyle(style);
			cell.setCellValue("二、本期收入");
			row.createCell(1).setCellValue(seremit_t + seremit_f);
			row.createCell(2).setCellValue(seaccept_t + seaccept_f);
			row.createCell(3).setCellValue(seremit_t + seremit_f + seaccept_t + seaccept_f);
			row = sheet.createRow(rows++);
			cell = row.createCell(0);
			cell.setCellStyle(style);
			cell.setCellValue("三、可供使用资金");
			row.createCell(1).setCellValue(
					seremit_t
							+ Double.parseDouble(start_map.get("seremit_t")
									.toString()));
			row.createCell(2).setCellValue(
					seaccept_t
							+ Double.parseDouble(start_map.get("seaccept_t")
									.toString()));
			row.createCell(3).setCellValue(
					seremit_t
							+ Double.parseDouble(start_map.get("seremit_t")
									.toString())
							+ seaccept_t
							+ Double.parseDouble(start_map.get("seaccept_t")
									.toString()));

			// --------------添加支出行-----------------
			j = 1;
			seremit_o = 0.0;
			seaccept_o = 0.0;
			for (int _i = 0; _i < ou_arr_t.size(); _i++) {
				row = sheet.createRow(rows++);
				row.createCell(0).setCellValue(
						(j++) + "." + out_map.get(ou_arr_t.get(_i)).toString());
				row.createCell(1).setCellValue(
						(Double) seremit_map.get(ou_arr_t.get(_i)));
				row.createCell(2).setCellValue(
						(Double) seaccept_map.get(ou_arr_t.get(_i)));
				row.createCell(3).setCellFormula(
						"B" + rows + "+C" + rows );
				seremit_o += (Double) seremit_map.get(ou_arr_t.get(_i));
				seaccept_o += (Double) seaccept_map.get(ou_arr_t.get(_i));
			}
			for (int _i = 0; _i < ou_arr_f.size(); _i++) {
				row = sheet.createRow(rows++);
				row.createCell(0).setCellValue(
						(j++) + "." + out_map.get(ou_arr_f.get(_i)).toString());
				row.createCell(1).setCellValue(
						(Double) seremit_map.get(ou_arr_f.get(_i)));
				row.createCell(2).setCellValue(
						(Double) seaccept_map.get(ou_arr_f.get(_i)));
				row.createCell(3).setCellFormula(
						"B" + rows + "+C" + rows);
				seremit_o += (Double) seremit_map.get(ou_arr_f.get(_i));
				seaccept_o += (Double) seaccept_map.get(ou_arr_f.get(_i));
			}

			row = sheet.createRow(rows++);
			cell = row.createCell(0);
			cell.setCellStyle(style);
			cell.setCellValue("四、本期支出");
			row.createCell(1).setCellValue(seremit_o);
			row.createCell(2).setCellValue(seaccept_o);
			row.createCell(3).setCellValue(seremit_o + seaccept_o);
			row = sheet.createRow(rows++);
			cell = row.createCell(0);
			cell.setCellStyle(style);
			cell.setCellValue("五、期末结余");
			row.createCell(1).setCellValue(
					seremit_t + seremit_f - seremit_o
							+ Double.parseDouble(start_map.get("seremit_t")
									.toString())
							+ Double.parseDouble(start_map.get("seremit_t")
									.toString()));
			row.createCell(2).setCellValue(
					seaccept_t + seaccept_f - seaccept_o
							+ Double.parseDouble(start_map.get("seaccept_t")
									.toString())
							+ Double.parseDouble(start_map.get("seaccept_f")
									.toString()));
			row.createCell(3).setCellValue(
					seremit_t 	+ seremit_f - seremit_o
					+ seaccept_t + seaccept_f - seaccept_o
							+ Double.parseDouble(start_map.get("seremit_t")
									.toString())
							+ Double.parseDouble(start_map.get("seremit_t")
									.toString())
							+ Double.parseDouble(start_map.get("seaccept_t")
									.toString())
							+ Double.parseDouble(start_map.get("seaccept_f")
									.toString()));
			/****************************************************************/

		}
		OutputStream fOut = new FileOutputStream(file);
		try {
			fOut = new FileOutputStream(file);
		} catch (FileNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		try {
			workbook.write(fOut);
		} catch (IOException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		try {
			fOut.flush();
		} catch (IOException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		try {
			fOut.close();
		} catch (IOException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return fileName;
	}

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包

打赏作者

彳卸风

你的鼓励将是我创作的最大动力

¥1 ¥2 ¥4 ¥6 ¥10 ¥20
扫码支付:¥1
获取中
扫码支付

您的余额不足,请更换扫码支付或充值

打赏作者

实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

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

余额充值