数据导出成12,33,3格式:
1、jsp
<%@ include file="../common.jsp" %>
<%@page language="java" import="java.sql.*" %>
<jsp:useBean id="conn" scope="page" class="htjs.database.DB_link"/>
<jsp:useBean id="jsp" scope="page" class="htjs.basic.OutString"></jsp:useBean>
<html>
<head>
<title>Untitled Document</title>
<meta http-equiv="Content-Type" content="text/html; charset=gb2312">
<LINK href="/include/htgl_css.css" REL="stylesheet" type="text/css">
<script language="Javascript" src="/include/mydate.js"></script>
</head>
<% int i=0;
ResultSet rsA;
String strSql="";
String strSqlTemp="";
String strSqlCd=""; //传递用 String strskgk =request.getParameter("skgk"); //收款国库
String strmdgk =request.getParameter("mdgk"); //目的国库
String strssgk =request.getParameter("ssgk"); //所属国库
String strysjc =request.getParameter("ysjc"); //预算级次
String stryskm =request.getParameter("yskm"); //预算科目
String stryszl =request.getParameter("yszl"); //预算种类 1预算内,2预算外
String strbbfw =request.getParameter("bbfw"); //报表范围 0本级 1全辖
String strzwrq_s=request.getParameter("zwrq_s"); //帐务日期 S
String strzwrq_e=request.getParameter("zwrq_e"); //帐务日期 E
java.util.Date date=new java.util.Date();
java.text.SimpleDateFormat sdf = new java.text.SimpleDateFormat("yyyy-MM-dd");
strSql="select a.*,b.gkf_mc as skgk,c.gkf_mc as mdgk,d.gkf_mc as ssgk,e.yskm_mc from sk_bb_7 a,dm_gkbm b,dm_gkbm c,dm_gkbm d,dm_yskm e where a.skgkdm=b.gkf_dm and a.mdgkdm=c.gkf_dm and a.ssgkdm=d.gkf_dm and a.kmdm=e.yskm_dm(+)"; strSqlTemp+=" and a.skgkdm='"+strskgk+"'";
strSqlTemp+=""; //预留附属条件
//out.println(strSql);
if (strmdgk!=null && !strmdgk.equalsIgnoreCase("all")) strSqlTemp+=" and a.mdgkdm='"+strmdgk+"'";
if (strssgk!=null && !strssgk.equalsIgnoreCase("all")) strSqlTemp+=" and a.mdgkdm='"+strssgk+"'";
if (strysjc!=null && !strysjc.equalsIgnoreCase("all")) strSqlTemp+=" and a.ysjc='"+strysjc+"'";
if (stryskm!=null && !stryskm.equalsIgnoreCase("all")) strSqlTemp+=" and a.kmdm='"+stryskm+"'";
if (stryszl!=null && !stryszl.equalsIgnoreCase("all")) strSqlTemp+=" and a.yszl="+stryszl+"";
if (strbbfw!=null && !strbbfw.equalsIgnoreCase("all")) strSqlTemp+=" and a.bbfw="+strbbfw+"";
if (strzwrq_s!=null && strzwrq_s.length()>0 && strzwrq_e!=null && strzwrq_e.length()>0) strSqlTemp+=" and a.zwrq between to_date('"+strzwrq_s+"','yyyy-mm-dd') and to_date('"+strzwrq_e+"','yyyy-mm-dd')";
strSql+=strSqlTemp;
strSqlCd="select a.ssgkdm||','||a.mdgkdm||','||a.skgkdm||','||a.ysjc||','||a.jgdm||','||a.kmdm||','||to_char(a.zwrq||','||'YYYY-MM-DD')||','||a.yszl||','||a.rlj||','||a.ylj||','||a.nlj from sk_bb_7 a where 1=1" + strSqlTemp;
//out.println(strSql);
//out.println(strSqlCd);
rsA=conn.executeQuery(strSql);
%>
<body bgcolor="#FFFFFF" text="#000000">
<table border="1" width="98%" bordercolorlight="#000000" bordercolordark="#000000" cellspacing="0" cellpadding="0" align="center">
<tr><th colspan="10"><H3 ALIGN="CENTER">预算收入对帐日报表查询结果</H3></th></tr>
<tr class="tr" align="center">
<th>收款国库</th>
<th>目的国库</th>
<th>所属国库</th>
<th>预算级次</th>
<th>预算科目</th>
<th>预算种类</th>
<th>帐务日期</th>
<th>日累计</th>
<th>月累计</th>
<th>年累计</th>
</tr>
<% while (rsA.next()) { i=i+1; %>
<tr>
<td nowrap><%=jsp.convert(rsA.getString("skgk"))%></td>
<td nowrap><%=jsp.convert(rsA.getString("mdgk"))%></td>
<td nowrap><%=jsp.convert(rsA.getString("ssgk"))%></td>
<td nowrap ALIGN="CENTER"><%=rsA.getString("ysjc")%></td>
<td nowrap><%=rsA.getString("kmdm")+" "+jsp.convert(rsA.getString("yskm_mc"))%></td>
<td nowrap ALIGN="CENTER"><%=(rsA.getInt("yszl")==1?"预算内":"预算外")%></td>
<td nowrap ALIGN="CENTER"><%=rsA.getDate("zwrq")%></td>
<td nowrap ALIGN="RIGHT"><%=rsA.getDouble("rlj")%></td>
<td nowrap ALIGN="RIGHT"><%=rsA.getDouble("ylj")%></td>
<td nowrap ALIGN="RIGHT"><%=rsA.getDouble("nlj")%></td>
</tr>
<%}%>
</table>
<%
rsA.close();
conn.close();
%>
<FORM METHOD=POST ACTION="export.jsp" target="frameExp">
<input type="hidden" value="<%=strbbfw%>" name="fw">
<input type="hidden" value="<%=strskgk%>" name="dw">
<input type="hidden" value="<%=strSqlCd%>" name="sql">
<p align="center"><input type="submit" value="下 载" > <input type="button" value="返 回" onclick="history.back();"></p>
</FORM>
<iframe name="frameExp" width=200 height=200 style="display:none" border=0 frameborder=0 framespacing=0 marginheight=0 marginwidth=0></iframe>
</body>
</html>
2.jsp
<%@ page import="java.sql.*"%>
<%@ page import=" java.io.*"%>
<jsp:useBean id="Exp" scope="page" class="dsgl.file.ExportFile"/>
<%
String strSql=request.getParameter("sql");
String res=Exp.ExportNr(strSql);
String strskgk=request.getParameter("dw");
String strbbfw=request.getParameter("fw");
String headpath=request.getRealPath("/");
java.util.Date date=new java.util.Date();
java.text.SimpleDateFormat rq = new java.text.SimpleDateFormat("yyyyMMdd"); String filename = "s"+strskgk; // 收款国库
//报表定义
//0、总额分成报表
//1、预算收入报表
//2、支出报表
//3、调拨收入报表
filename+="_7"; // 报表名称
filename+=strbbfw; // 报表范围
filename+=rq.format(date); // 日期
filename+=".txt";
File file=new File(headpath+"/tempfile/"+filename);
file.delete();
if (res!=null) {
FileWriter fw=new FileWriter(headpath+"/tempfile/"+filename,true);
String writeStr="";
writeStr=res;
fw.write(writeStr,0,writeStr.length());
fw.flush();
fw.close();
response.reset()
response.setContentType("application/octet-stream");
response.setHeader("Content-disposition", "attachment; filename=" + filename);
BufferedInputStream bis = null;
BufferedOutputStream bos = null;
try
{
FileInputStream fis = new FileInputStream(headpath+"/tempfile/"+filename);
bis = new BufferedInputStream(fis);
bos = new BufferedOutputStream(response.getOutputStream());
byte abyte0[] = new byte[2048];
int i;
while((i = bis.read(abyte0, 0, abyte0.length)) >0)
bos.write(abyte0, 0, i);
}
catch(IOException ioexception)
{
ioexception.getMessage();
throw ioexception;
}
finally
{
if(bis != null)
bis.close();
if(bos != null)
bos.close();
file.delete();
}
}else{
out.println("<SCRIPT LANGUAGE=\"JavaScript\">");
out.println("alert('Error');");
out.println("</SCRIPT>");
}
%>
package dsgl.file;import java.sql.*;
import java.util.*;
import dsgl.basic.*;
import dsgl.database.*;
import dsgl.include.*;
public class ExportFile extends dsgl.database.LinkSQL {
private String strNr=new String();
ResultSet rs=null;
OutString os=new OutString();
public ExportFile() throws SQLException { }
public String ExportNr(String sql) {
try {
openConnect();
rs=executeQuery(sql);
String strTemp="";
while (rs.next())
{
//for (int i=1;i<=intCols;i++)
//{
//switch (rsmd.getColumnType(i))
//{
//case Types.DATE:
// strTemp=rs.getDate(i).toString();
// break;
//default:
strTemp=rs.getString(1);
//} //end switch
strNr+=strTemp;
//if (i==intCols)
//{
strNr+="\r\n";
//}else{
//strNr+=",";
//} //end if
//}//end for
} //end while
rs.close();
} catch (Exception e) {
System.out.println(sql);
} finally {
return strNr;
}
} }
1、jsp
<%@ include file="../common.jsp" %>
<%@page language="java" import="java.sql.*" %>
<jsp:useBean id="conn" scope="page" class="htjs.database.DB_link"/>
<jsp:useBean id="jsp" scope="page" class="htjs.basic.OutString"></jsp:useBean>
<html>
<head>
<title>Untitled Document</title>
<meta http-equiv="Content-Type" content="text/html; charset=gb2312">
<LINK href="/include/htgl_css.css" REL="stylesheet" type="text/css">
<script language="Javascript" src="/include/mydate.js"></script>
</head>
<% int i=0;
ResultSet rsA;
String strSql="";
String strSqlTemp="";
String strSqlCd=""; //传递用 String strskgk =request.getParameter("skgk"); //收款国库
String strmdgk =request.getParameter("mdgk"); //目的国库
String strssgk =request.getParameter("ssgk"); //所属国库
String strysjc =request.getParameter("ysjc"); //预算级次
String stryskm =request.getParameter("yskm"); //预算科目
String stryszl =request.getParameter("yszl"); //预算种类 1预算内,2预算外
String strbbfw =request.getParameter("bbfw"); //报表范围 0本级 1全辖
String strzwrq_s=request.getParameter("zwrq_s"); //帐务日期 S
String strzwrq_e=request.getParameter("zwrq_e"); //帐务日期 E
java.util.Date date=new java.util.Date();
java.text.SimpleDateFormat sdf = new java.text.SimpleDateFormat("yyyy-MM-dd");
strSql="select a.*,b.gkf_mc as skgk,c.gkf_mc as mdgk,d.gkf_mc as ssgk,e.yskm_mc from sk_bb_7 a,dm_gkbm b,dm_gkbm c,dm_gkbm d,dm_yskm e where a.skgkdm=b.gkf_dm and a.mdgkdm=c.gkf_dm and a.ssgkdm=d.gkf_dm and a.kmdm=e.yskm_dm(+)"; strSqlTemp+=" and a.skgkdm='"+strskgk+"'";
strSqlTemp+=""; //预留附属条件
//out.println(strSql);
if (strmdgk!=null && !strmdgk.equalsIgnoreCase("all")) strSqlTemp+=" and a.mdgkdm='"+strmdgk+"'";
if (strssgk!=null && !strssgk.equalsIgnoreCase("all")) strSqlTemp+=" and a.mdgkdm='"+strssgk+"'";
if (strysjc!=null && !strysjc.equalsIgnoreCase("all")) strSqlTemp+=" and a.ysjc='"+strysjc+"'";
if (stryskm!=null && !stryskm.equalsIgnoreCase("all")) strSqlTemp+=" and a.kmdm='"+stryskm+"'";
if (stryszl!=null && !stryszl.equalsIgnoreCase("all")) strSqlTemp+=" and a.yszl="+stryszl+"";
if (strbbfw!=null && !strbbfw.equalsIgnoreCase("all")) strSqlTemp+=" and a.bbfw="+strbbfw+"";
if (strzwrq_s!=null && strzwrq_s.length()>0 && strzwrq_e!=null && strzwrq_e.length()>0) strSqlTemp+=" and a.zwrq between to_date('"+strzwrq_s+"','yyyy-mm-dd') and to_date('"+strzwrq_e+"','yyyy-mm-dd')";
strSql+=strSqlTemp;
strSqlCd="select a.ssgkdm||','||a.mdgkdm||','||a.skgkdm||','||a.ysjc||','||a.jgdm||','||a.kmdm||','||to_char(a.zwrq||','||'YYYY-MM-DD')||','||a.yszl||','||a.rlj||','||a.ylj||','||a.nlj from sk_bb_7 a where 1=1" + strSqlTemp;
//out.println(strSql);
//out.println(strSqlCd);
rsA=conn.executeQuery(strSql);
%>
<body bgcolor="#FFFFFF" text="#000000">
<table border="1" width="98%" bordercolorlight="#000000" bordercolordark="#000000" cellspacing="0" cellpadding="0" align="center">
<tr><th colspan="10"><H3 ALIGN="CENTER">预算收入对帐日报表查询结果</H3></th></tr>
<tr class="tr" align="center">
<th>收款国库</th>
<th>目的国库</th>
<th>所属国库</th>
<th>预算级次</th>
<th>预算科目</th>
<th>预算种类</th>
<th>帐务日期</th>
<th>日累计</th>
<th>月累计</th>
<th>年累计</th>
</tr>
<% while (rsA.next()) { i=i+1; %>
<tr>
<td nowrap><%=jsp.convert(rsA.getString("skgk"))%></td>
<td nowrap><%=jsp.convert(rsA.getString("mdgk"))%></td>
<td nowrap><%=jsp.convert(rsA.getString("ssgk"))%></td>
<td nowrap ALIGN="CENTER"><%=rsA.getString("ysjc")%></td>
<td nowrap><%=rsA.getString("kmdm")+" "+jsp.convert(rsA.getString("yskm_mc"))%></td>
<td nowrap ALIGN="CENTER"><%=(rsA.getInt("yszl")==1?"预算内":"预算外")%></td>
<td nowrap ALIGN="CENTER"><%=rsA.getDate("zwrq")%></td>
<td nowrap ALIGN="RIGHT"><%=rsA.getDouble("rlj")%></td>
<td nowrap ALIGN="RIGHT"><%=rsA.getDouble("ylj")%></td>
<td nowrap ALIGN="RIGHT"><%=rsA.getDouble("nlj")%></td>
</tr>
<%}%>
</table>
<%
rsA.close();
conn.close();
%>
<FORM METHOD=POST ACTION="export.jsp" target="frameExp">
<input type="hidden" value="<%=strbbfw%>" name="fw">
<input type="hidden" value="<%=strskgk%>" name="dw">
<input type="hidden" value="<%=strSqlCd%>" name="sql">
<p align="center"><input type="submit" value="下 载" > <input type="button" value="返 回" onclick="history.back();"></p>
</FORM>
<iframe name="frameExp" width=200 height=200 style="display:none" border=0 frameborder=0 framespacing=0 marginheight=0 marginwidth=0></iframe>
</body>
</html>
2.jsp
<%@ page import="java.sql.*"%>
<%@ page import=" java.io.*"%>
<jsp:useBean id="Exp" scope="page" class="dsgl.file.ExportFile"/>
<%
String strSql=request.getParameter("sql");
String res=Exp.ExportNr(strSql);
String strskgk=request.getParameter("dw");
String strbbfw=request.getParameter("fw");
String headpath=request.getRealPath("/");
java.util.Date date=new java.util.Date();
java.text.SimpleDateFormat rq = new java.text.SimpleDateFormat("yyyyMMdd"); String filename = "s"+strskgk; // 收款国库
//报表定义
//0、总额分成报表
//1、预算收入报表
//2、支出报表
//3、调拨收入报表
filename+="_7"; // 报表名称
filename+=strbbfw; // 报表范围
filename+=rq.format(date); // 日期
filename+=".txt";
File file=new File(headpath+"/tempfile/"+filename);
file.delete();
if (res!=null) {
FileWriter fw=new FileWriter(headpath+"/tempfile/"+filename,true);
String writeStr="";
writeStr=res;
fw.write(writeStr,0,writeStr.length());
fw.flush();
fw.close();
response.reset()
response.setContentType("application/octet-stream");
response.setHeader("Content-disposition", "attachment; filename=" + filename);
BufferedInputStream bis = null;
BufferedOutputStream bos = null;
try
{
FileInputStream fis = new FileInputStream(headpath+"/tempfile/"+filename);
bis = new BufferedInputStream(fis);
bos = new BufferedOutputStream(response.getOutputStream());
byte abyte0[] = new byte[2048];
int i;
while((i = bis.read(abyte0, 0, abyte0.length)) >0)
bos.write(abyte0, 0, i);
}
catch(IOException ioexception)
{
ioexception.getMessage();
throw ioexception;
}
finally
{
if(bis != null)
bis.close();
if(bos != null)
bos.close();
file.delete();
}
}else{
out.println("<SCRIPT LANGUAGE=\"JavaScript\">");
out.println("alert('Error');");
out.println("</SCRIPT>");
}
%>
package dsgl.file;import java.sql.*;
import java.util.*;
import dsgl.basic.*;
import dsgl.database.*;
import dsgl.include.*;
public class ExportFile extends dsgl.database.LinkSQL {
private String strNr=new String();
ResultSet rs=null;
OutString os=new OutString();
public ExportFile() throws SQLException { }
public String ExportNr(String sql) {
try {
openConnect();
rs=executeQuery(sql);
String strTemp="";
while (rs.next())
{
//for (int i=1;i<=intCols;i++)
//{
//switch (rsmd.getColumnType(i))
//{
//case Types.DATE:
// strTemp=rs.getDate(i).toString();
// break;
//default:
strTemp=rs.getString(1);
//} //end switch
strNr+=strTemp;
//if (i==intCols)
//{
strNr+="\r\n";
//}else{
//strNr+=",";
//} //end if
//}//end for
} //end while
rs.close();
} catch (Exception e) {
System.out.println(sql);
} finally {
return strNr;
}
} }
package dsgl.file;import java.sql.*;
import java.util.*;
import dsgl.basic.*;
import dsgl.database.*;
import dsgl.include.*;
public class ExportFile extends dsgl.database.LinkSQL {
private String strNr=new String();
ResultSet rs=null;
OutString os=new OutString();
public ExportFile() throws SQLException { }
public String ExportNr(String sql) {
try {
openConnect();
rs=executeQuery(sql);
String strTemp="";
while (rs.next())
{
//for (int i=1;i<=intCols;i++)
//{
//switch (rsmd.getColumnType(i))
//{
//case Types.DATE:
// strTemp=rs.getDate(i).toString();
// break;
//default:
strTemp=rs.getString(1);
//} //end switch
strNr+=strTemp;
//if (i==intCols)
//{
strNr+="\r\n";
//}else{
//strNr+=",";
//} //end if
//}//end for
} //end while
rs.close();
} catch (Exception e) {
System.out.println(sql);
} finally {
return strNr;
}
} }