JAVA导出EXCEL

 

public ActionForward outInckAdslDevNeLogExcel(ActionMapping mapping, ActionForm form,

HttpServletRequest request, HttpServletResponse response) throws Exception{

String title = java.net.URLEncoder.encode("EXCEL标题", "UTF-8")+ ".xls";

response.setHeader("Content-disposition", "attachment;filename="+title);

String dslamip = request.getParameter("a");

String basip = request.getParameter("b");

String basport = request.getParameter("c");

String pvlan = request.getParameter("d");

String cvlan1 = request.getParameter("e");

String cvlan2 = request.getParameter("f");

String account1 = request.getParameter("g");

String account2 = request.getParameter("h");

String operaid = request.getParameter("OPERAID");

String operatime1 = request.getParameter("OPERATIME1");

String operaname = java.net.URLDecoder.decode(request.getParameter("OPERANAME"),"utf-8");

String operatime2 = request.getParameter("OPERATIME2");

String loginid = request.getParameter("LOGINID");

String operatype = java.net.URLDecoder.decode(request.getParameter("OPERATYPE"),"utf-8");

List titleList = new ArrayList();

titleList.add("a);

titleList.add("b");

titleList.add("c");

titleList.add("d");

titleList.add("e");

titleList.add("f");

titleList.add("g");

titleList.add("h");

titleList.add("时间");

titleList.add("操作员工号");

titleList.add("操作员登录ID");

titleList.add("操作员名称");

titleList.add("操作类型");

account2,operaid,operatime1,operatime2,loginid,operatype,operaname);

ResultWrap rw = dao.outInckAdslDevNeLogExcel(a, b, c, d, e, f,h,operaid,operatime1,operatime2,loginid,operatype,operaname);

if (rw != null){

TPubFunction.WriteExcel(titleList,rw,20, response);

        } 

return null;

}

 

 

TPubFunction处理类

 

package com.regaltec.inss.inck.inckutil;

 

import java.io.ByteArrayOutputStream;

import java.io.IOException;

import java.io.InputStream;

import java.io.PrintWriter;

import java.io.Reader;

import java.sql.Clob;

import java.text.ParseException;

import java.text.SimpleDateFormat;

import java.util.ArrayList;

import java.util.Collection;

import java.util.Date;

import java.util.Iterator;

import java.util.List;

import java.util.zip.GZIPOutputStream;

 

import javax.servlet.http.HttpServletRequest;

import javax.servlet.http.HttpServletResponse;

 

import jxl.Workbook;

import jxl.write.Label;

import jxl.write.WritableSheet;

import jxl.write.WritableWorkbook;

 

import com.regaltec.ccatsida.common.database.CommonRecord;

import com.regaltec.ccatsida.common.database.ResultWrap;

 

public class TPubFunction {

/**

* 每页显示数据的大小

*/

public static int iPageSize= 1000;

 

/**

* 将Clob字段转换成String

* @param clob

* @return

*/

public final static String clob2String(Clob clob) {

if (clob == null) {

return "";

}

 

StringBuffer sb = new StringBuffer(65535);// 64K

Reader clobStream = null;

try {

clobStream = clob.getCharacterStream();

char[] b = new char[60000];// 每次获取60K

int i = 0;

while ((i = clobStream.read(b)) != -1) {

sb.append(b, 0, i);

}

} catch (Exception ex) {

sb = null;

} finally {

try {

if (clobStream != null)

clobStream.close();

} catch (Exception e) {

}

}

if (sb == null)

return "";

else

return sb.toString();

}

 

 

/**

* 将字符 null 用空格替换

* 

* @param AString

*            传入字符

* @return

*/

public static String formatStringNull(String AString) {

if (AString == null) {

return "";

} else {

return AString;

}

}

 

/**

* 将输入流转换成字节

* 

* @param is

*            输入流

* @return

* @throws Exception

*/

public static byte[] getBytes(InputStream is) throws Exception {

byte[] data = null;

 

Collection chunks = new ArrayList();

byte[] buffer = new byte[1024 * 1000];

int read = -1;

int size = 0;

 

while ((read = is.read(buffer)) != -1) {

if (read > 0) {

byte[] chunk = new byte[read];

System.arraycopy(buffer, 0, chunk, 0, read);

chunks.add(chunk);

size += chunk.length;

}

}

 

if (size > 0) {

ByteArrayOutputStream bos = null;

try {

bos = new ByteArrayOutputStream(size);

for (Iterator itr = chunks.iterator(); itr.hasNext();) {

byte[] chunk = (byte[]) itr.next();

bos.write(chunk);

}

data = bos.toByteArray();

} finally {

if (bos != null) {

bos.close();

}

}

}

return data;

}

 

/**

* 将日期时间型转换成日期型字符

* 

* @param ADateStr

* @return

*/

public static String DateTimeToDateStr(String ADateStr) {

return ADateStr.substring(0, 10);

}

 

/**

* 格式化字符串为日期时间型

* 

* @param ADateStr

* @return

*/

public static String formatStringDateTime(String ADateStr) {

return ADateStr.substring(0, 16);

}

 

/**

* 将8859_1的字符串转换成GBK字符

* 

* @param s

* @return

*/

public static String toChinese(String s) {

String sRet = null;

if (s == null) {

sRet = "";

} else {

try {

sRet = new String(s.getBytes("ISO8859-1"), "gb2312");

} catch (Exception e) {

System.err.println("字符ISO8859-1转换成gb2312出错");

}

}

return sRet;

}

 

/**

* 判断浏览器是否支持压缩的Web页面

* @param request

* @return

*/

public static boolean isGzipSupported(HttpServletRequest request) {

String encodings = request.getHeader("Accept-Encoding");

return ((encodings != null) && (encodings.indexOf("gzip") != -1));

}

 

/**

* 返回压缩输出流

* 

* @param response

* @return

* @throws IOException

*/

public static PrintWriter getGzipWriter(HttpServletResponse response)

throws IOException {

return (new PrintWriter(

new GZIPOutputStream(response.getOutputStream())));

}

/**

* 获取系统当前时间

* @return

*/

public static String getSystemDateTime() {

Date dt = new Date();

SimpleDateFormat sftime = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");

return sftime.format(dt);

}

 

/**

* 获取系统当前日期

* @return

*/

public static String getSystemDate() {

Date dt = new Date();

SimpleDateFormat sftime = new SimpleDateFormat("yyyy-MM-dd");

return sftime.format(dt);

}

/**

* 返回指定时间加分钟后的时间

* @param aDateTime 指定时间

* @param aMinute 加指定分钟数

* @return

* @throws ParseException 

*/

public static String addDateTime(String aDateTime,int aMinute) throws ParseException {

SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm");

Date aTaskStartTime = sdf.parse(aDateTime);

long Time=(aTaskStartTime.getTime()/1000)+aMinute*60;

aTaskStartTime.setTime(Time*1000);

return sdf.format(aTaskStartTime);

}

/**

* 判断两个时间的大小

* @param sDateStart

* @param sDateEnd

* @return false表示sDateStart>=sDateEnd,true表示sDateEnd>sDateStart

* @throws ParseException

*/

public static boolean equalsDate(String sDateStart,String sDateEnd) throws ParseException{

boolean aFalg = false;

SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm");

Date aDateStart = sdf.parse(sDateStart);

Date aDateEnd = sdf.parse(sDateEnd);

int result = aDateStart.compareTo(aDateEnd);

if (result > 0) aFalg = true;

return aFalg;

}

/**

* 导出Excel

* @param ATitle标题

* @param AQueryResult 数据集

* @param response 当前会话

* @throws Exception

*/

public synchronized static void WriteExcel(List ATitle,ResultWrap AQueryResult,int aColumnWidth,

HttpServletResponse response) throws Exception{

WritableWorkbook writbook = null;

 

        writbook = Workbook.createWorkbook(response.getOutputStream());

 

        WritableSheet sheet = writbook.createSheet("Sheet1", 0);

 

        Label label = null;

        boolean bFalg =  false;

 

        if (ATitle.size() > 0) bFalg = true;

 

        for (int i = 0; i < ATitle.size(); i++){

         label = new Label(i, 0, ATitle.get(i).toString());

            sheet.addCell(label);

            sheet.setColumnView(i, aColumnWidth);

        }

 

        int iStart = 0;

 

        if (bFalg) iStart = 1; 

 

        for(int i = 0; i < AQueryResult.getrsTotalrows(); i++){

         CommonRecord cr = AQueryResult.getRecord(i);

         for (int j = 0; j < cr.getFieldCount(); j++) {

         int type = cr.getType(j+1);

         if (type == 12)

         label = new Label(j, i + iStart, cr.getString(j+1));

         if (type == 2)

         label = new Label(j, i + iStart, Integer.toString(cr.getInt(j+1)));

                sheet.addCell(label);

         }

        }

 

        writbook.write();

        writbook.close();

 

        writbook = null;

        AQueryResult = null;

}

}

outInckAdslDevNeLogExcel方法:

public ResultWrap outInckAdslDevNeLogExcel(String dslamip,String basip,String basport,

String pvlan,String cvlan1,String cvlan2,String account1,String account2,String operaid,String operatime1,String operatime2,String loginid,String operatype,String operaname)throws Exception{

StringBuffer aSql = new StringBuffer();

aSql.append("Select t.a,t.b,t.c,");

aSql.append("t.d,t.e,f,t.g,t.h,t.operaTime,t.operaId,t.loginID,t.operaName,t.operaType  ");

aSql.append("from table_to_excel  t Where 1=1");

aSql.append( getInckAdslDevNeLogParam(operaid,operaname,loginid,operatime1,operatime2,operatype));

aSql.append(" order by t.bas_ip,t.bas_port,t.pvlan");

return dbInck.querySql(aSql.toString());

}

getInckAdslDevNeLogParam条件查询方法:

private String getInckAdslDevNeLogParam(String operaid,String operaname,String loginid,String operatime1,

String operatime2,String operatype){

StringBuffer aSqlParam = new StringBuffer();

if (!"".equals(TPubFunction.formatStringNull(operaid)))

aSqlParam.append(" and OPERAID='"+operaid+"'");

if (!"".equals(TPubFunction.formatStringNull(operaname)))

aSqlParam.append(" or OPERANAME like '%"+operaname+"%'");

if (!"".equals(TPubFunction.formatStringNull(loginid)))

aSqlParam.append(" or LOGINID='"+loginid+"'");

if (!"".equals(TPubFunction.formatStringNull(operatime1)))

aSqlParam.append(" and to_date(t.operatime,'yyyy-mm-dd hh24:mi:ss') >= to_date('"+operatime1+"','yyyy-mm-dd hh24:mi:ss')");

if (!"".equals(TPubFunction.formatStringNull(operatime2)))

// aSqlParam.append(" and to_date('"+operatime2+"','yyyy-MM-dd')");

aSqlParam.append(" and to_date(t.operatime,'yyyy-mm-dd hh24:mi:ss')<=to_date('"+operatime2+"','yyyy-mm-dd hh24:mi:ss')");

if (!"".equals(TPubFunction.formatStringNull(operatype)))

try {

aSqlParam.append(" and OPERATYPE like '%"+java.net.URLDecoder.decode(operatype,"utf-8")+"%'");

} catch (UnsupportedEncodingException e) {

// TODO Auto-generated catch block

e.printStackTrace();

}

return aSqlParam.toString();

}