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;
}
2013年12月 那年的数据导出
最新推荐文章于 2022-12-31 17:50:11 发布