📄 outputdataaction.java
字号:
/*
* Created on 2006-7-23
*
* TODO To change the template for this generated file go to
* Window - Preferences - Java - Code Style - Code Templates
*/
package org.ug.sztz.webview.structs.actions.oa;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.sql.*;
import org.apache.struts.action.ActionForm;
import org.apache.struts.action.ActionForward;
import org.apache.struts.action.ActionMapping;
import org.ug.sztz.domain.oa.Jdbc;
import org.ug.sztz.domain.assistant.JdbcFactory;
import org.ug.sztz.webview.structs.forms.OutputDataForm;
import org.apache.poi.hssf.usermodel.*;
import java.io.*;
import org.ug.sztz.domain.assistant.*;
/**
* @author Skywalker
*
* TODO To change the template for this generated type comment go to Window -
* Preferences - Java - Code Style - Code Templates
*/
public class OutputDataAction extends BaseAction {
/*
* (non-Javadoc)
*
* @see org.apache.struts.action.Action#execute(org.apache.struts.action.ActionMapping,
* org.apache.struts.action.ActionForm,
* javax.servlet.http.HttpServletRequest,
* javax.servlet.http.HttpServletResponse)
*/
public ActionForward execute(ActionMapping mapping, ActionForm form,
HttpServletRequest request, HttpServletResponse response)
throws Exception {
// TODO Auto-generated method stub
OutputDataForm workform = (OutputDataForm) form;
String sql = workform.getSql();
String outputAll = request.getParameter("submit2");
String output = request.getParameter("submit3");
String conti = request.getParameter("submit1");
if (conti != null && conti.equals("添加条件")) {
String tjz = workform.getTjz();
int tjzd = Integer.parseInt(workform.getTjzd());
int tj = Integer.parseInt(workform.getTj());
int tjgx = Integer.parseInt(workform.getTjgx());
if (sql == null || sql.length() < 1) {
sql = " where bysbase.xh=bysjy.xh and";
switch (tjzd) {
case 1:
sql = sql + " bysbase.xh";
break;
case 2:
sql = sql + " bysbase.xm";
break;
case 3:
sql = sql + " bysbase.zy";
break;
case 4:
sql = sql + " bysbase.xb";
break;
case 5:
sql = sql + " bysbase.mz";
break;
case 6:
sql = sql + " bysbase.zzmm";
break;
case 7:
sql = sql + " bysbase.pyfs";
break;
case 8:
sql = sql + " bysbase.xz";
break;
case 9:
sql = sql + " bysbase.xl";
break;
case 10:
sql = sql + " bysbase.syszd";
break;
case 11:
sql = sql + " bysbase.xjbd";
break;
case 12:
sql = sql + " bysbase.bj";
break;
case 13:
sql = sql + " bysbase.xy";
break;
case 14:
sql = sql + " bysbase.xslb";
break;
case 15:
sql = sql + " bysbase.wyyz";
break;
case 16:
sql = sql + " bysjy.byqx";
break;
case 17:
sql = sql + " bysjy.jyxs";
break;
case 18:
sql = sql + " bysjy.lcnr";
break;
case 19:
sql = sql + " bysjy.dwmc";
break;
case 20:
sql = sql + " bysjy.dwszd";
break;
case 21:
sql = sql + " bysjy.dwxz";
break;
case 22:
sql = sql + " bysjy.ljdlbz";
break;
case 23:
sql = sql + " bysjy.jyzt";
break;
case 24:
sql = sql + " bysjy.xysh";
break;
case 25:
sql = sql + " bysjy.jlsd";
break;
}
switch (tj) {
case 1:
sql = sql + " = '" + tjz + "'";
break;
case 2:
sql = sql + " like '%" + tjz + "%'";
break;
case 3:
sql = sql + " <> '" + tjz + "'";
break;
case 4:
sql = sql + " not like '%" + tjz + "%'";
break;
case 5:
sql = sql + " like '" + tjz + "%'";
break;
case 6:
sql = sql + " not like '" + tjz + "%'";
break;
}
} else {
switch (tjgx) {
case 1:
sql = sql + " and";
break;
case 2:
sql = sql + " or";
break;
}
switch (tjzd) {
case 1:
sql = sql + " bysbase.xh";
break;
case 2:
sql = sql + " bysbase.xm";
break;
case 3:
sql = sql + " bysbase.zy";
break;
case 4:
sql = sql + " bysbase.xb";
break;
case 5:
sql = sql + " bysbase.mz";
break;
case 6:
sql = sql + " bysbase.zzmm";
break;
case 7:
sql = sql + " bysbase.pyfs";
break;
case 8:
sql = sql + " bysbase.xz";
break;
case 9:
sql = sql + " bysbase.xl";
break;
case 10:
sql = sql + " bysbase.syszd";
break;
case 11:
sql = sql + " bysbase.xjbd";
break;
case 12:
sql = sql + " bysbase.bj";
break;
case 13:
sql = sql + " bysbase.xy";
break;
case 14:
sql = sql + " bysbase.xslb";
break;
case 15:
sql = sql + " bysbase.wyyz";
break;
case 16:
sql = sql + " bysjy.byqx";
break;
case 17:
sql = sql + " bysjy.jyxs";
break;
case 18:
sql = sql + " bysjy.lcnr";
break;
case 19:
sql = sql + " bysjy.dwmc";
break;
case 20:
sql = sql + " bysjy.dwszd";
break;
case 21:
sql = sql + " bysjy.dwxz";
break;
case 22:
sql = sql + " bysjy.ljdlbz";
break;
case 23:
sql = sql + " bysjy.jyzt";
break;
case 24:
sql = sql + " bysjy.xysh";
break;
case 25:
sql = sql + " bysjy.jlsd";
break;
}
switch (tj) {
case 1:
sql = sql + " = '" + tjz + "'";
break;
case 2:
sql = sql + " like '%" + tjz + "%'";
break;
case 3:
sql = sql + " <> '" + tjz + "'";
break;
case 4:
sql = sql + " not like '%" + tjz + "%'";
break;
case 5:
sql = sql + " like '" + tjz + "%'";
break;
case 6:
sql = sql + " not like '" + tjz + "%'";
break;
}
}
request.setAttribute("sql", sql);
return mapping.findForward("continue");
}
else if (output != null && output.equals("导出选定字段")) {
try{
outputData(workform.getDczd(),workform.getSql());
return mapping.findForward("success");
}catch(Exception e){
request.setAttribute("info", "导出数据失败！");
return mapping.findForward("failure");
}
}
else {
try {
outputData2(sql);
return mapping.findForward("success");
} catch (Exception e) {
request.setAttribute("info", "导出数据失败！");
return mapping.findForward("failure");
}
}
}
private void outputData(String[] col,String sqlend) throws Exception{
String sqlfront = "select ";
String[] column = null;
String columnsql = null;
for(int i=0;i<col.length;i++){
if(i==0){
column = col[i].split("_");
if(column[0].equals("1"))
{
columnsql = "bysbase.";
}else{
columnsql = "bysjy.";
}
columnsql = columnsql+column[1];
}else{
column = col[i].split("_");
if(column[0].equals("1"))
{
columnsql = ",bysbase.";
}else{
columnsql = ",bysjy.";
}
columnsql = columnsql+column[1];
}
sqlfront = sqlfront+columnsql;
}
sqlfront = sqlfront+" from bysbase,bysjy";
if(sqlend==null||sqlend.equals(""))
sqlend=" where bysbase.xh=bysjy.xh";
String sql = sqlfront+sqlend;
System.out.println(sql);
JdbcFactory factory = new JdbcFactory(this.getServlet()
.getServletConfig());
Jdbc jdbc = factory.getJdbcInfo();
Connection con = null;
Statement stmt = null;
ResultSet rs = null;
try {
Class.forName(jdbc.getDriver());
con = DriverManager.getConnection(jdbc.getUrl(),jdbc.getUsername(),jdbc.getPassword());
stmt = con.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,
ResultSet.CONCUR_READ_ONLY);
rs = stmt
.executeQuery(sql);
ResultSetMetaData metaData = rs.getMetaData();
int colnum = metaData.getColumnCount();
HSSFWorkbook wb = new HSSFWorkbook();
HSSFSheet sheet = wb.createSheet("data");
rs.last();
int rownum = rs.getRow();
rs.first();
HSSFRow titlerow = sheet.createRow((short)0);
for(int i=0;i<colnum;i++){
HSSFCell cell = titlerow.createCell((short)i);
cell.setEncoding(HSSFCell.ENCODING_UTF_16);
cell.setCellValue(metaData.getColumnName(i+1));
}
for(int i=1;i<=rownum;i++){
HSSFRow row = sheet.createRow((short)i);
for(int j=0;j<colnum;j++)
{
HSSFCell cell = row.createCell((short)j);
cell.setEncoding(HSSFCell.ENCODING_UTF_16);
cell.setCellValue(rs.getString(j+1));
}
rs.next();
}
UrlConvertor convertor=new UrlConvertor(this.getServlet().getServletConfig());
FileOutputStream fileOut = new FileOutputStream(convertor.getAbsoluteFile("/output/","data.xls"));
wb.write(fileOut);
fileOut.close();
} catch (Exception e) {
e.printStackTrace();
}
}
private void outputData2(String sqlend) throws Exception{
String sqlfront = "select bysbase.*,bysjy.* from bysbase,bysjy";
String columnsql = null;
if(sqlend==null||sqlend.equals(""))
sqlend=" where bysbase.xh=bysjy.xh";
String sql = sqlfront+sqlend;
System.out.println(sql);
JdbcFactory factory = new JdbcFactory(this.getServlet()
.getServletConfig());
Jdbc jdbc = factory.getJdbcInfo();
Connection con = null;
Statement stmt = null;
ResultSet rs = null;
try {
Class.forName(jdbc.getDriver());
con = DriverManager.getConnection(jdbc.getUrl(),jdbc.getUsername(),jdbc.getPassword());
stmt = con.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,
ResultSet.CONCUR_READ_ONLY);
rs = stmt
.executeQuery(sql);
ResultSetMetaData metaData = rs.getMetaData();
int colnum = metaData.getColumnCount();
HSSFWorkbook wb = new HSSFWorkbook();
HSSFSheet sheet = wb.createSheet("data");
rs.last();
int rownum = rs.getRow();
rs.first();
HSSFRow titlerow = sheet.createRow((short)0);
for(int i=0;i<colnum;i++){
HSSFCell cell = titlerow.createCell((short)i);
cell.setEncoding(HSSFCell.ENCODING_UTF_16);
cell.setCellValue(metaData.getColumnName(i+1));
}
for(int i=1;i<=rownum;i++){
HSSFRow row = sheet.createRow((short)i);
for(int j=0;j<colnum;j++)
{
HSSFCell cell = row.createCell((short)j);
cell.setEncoding(HSSFCell.ENCODING_UTF_16);
cell.setCellValue(rs.getString(j+1));
}
rs.next();
}
UrlConvertor convertor=new UrlConvertor(this.getServlet().getServletConfig());
FileOutputStream fileOut = new FileOutputStream(convertor.getAbsoluteFile("/output/","data.xls"));
wb.write(fileOut);
fileOut.close();
} catch (Exception e) {
e.printStackTrace();
}
}
private void outputAllData(String sqlend) throws Exception {
JdbcFactory factory = new JdbcFactory(this.getServlet()
.getServletConfig());
Jdbc jdbc = factory.getJdbcInfo();
Connection con = null;
Statement stmt = null;
ResultSet rs = null;
//String sql = "select bysbase.*,bysjy.* from bysbase,bysjy"+sqlend+" and bysbase.xh=bysjy.xh";
String sql = "select bysbase.*,bysjy.* from bysbase,bysjy where bysbase.xh=bysjy.xh";
//System.out.println(sql);
try {
Class.forName(jdbc.getDriver());
con = DriverManager.getConnection(jdbc.getUrl(),jdbc.getUsername(),jdbc.getPassword());
stmt = con.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,
ResultSet.CONCUR_READ_ONLY);
rs = stmt
.executeQuery(sql);
ResultSetMetaData metaData = rs.getMetaData();
int colnum = metaData.getColumnCount();
HSSFWorkbook wb = new HSSFWorkbook();
HSSFSheet sheet = wb.createSheet("alldata");
rs.last();
int rownum = rs.getRow();
rs.first();
HSSFRow titlerow = sheet.createRow((short)0);
for(int i=0;i<colnum;i++){
HSSFCell cell = titlerow.createCell((short)i);
cell.setEncoding(HSSFCell.ENCODING_UTF_16);
cell.setCellValue(metaData.getColumnName(i+1));
}
for(int i=1;i<=rownum;i++){
HSSFRow row = sheet.createRow((short)i);
for(int j=0;j<colnum;j++)
{
HSSFCell cell = row.createCell((short)j);
cell.setEncoding(HSSFCell.ENCODING_UTF_16);
cell.setCellValue(rs.getString(j+1));
}
rs.next();
}
UrlConvertor convertor=new UrlConvertor(this.getServlet().getServletConfig());
FileOutputStream fileOut = new FileOutputStream(convertor.getAbsoluteFile("/output/","data.xls"));
wb.write(fileOut);
fileOut.close();
} catch (Exception e) {
e.printStackTrace();
}
}
}⌨️ 快捷键说明
复制代码
Ctrl + C
搜索代码
Ctrl + F
全屏模式
F11
切换主题
Ctrl + Shift + D
显示快捷键
?
增大字号
Ctrl + =
减小字号
Ctrl + -