excelservlet.java

用JAVA语言 对excel的操作。更多更多关注

import javax.servlet.ServletException;
import javax.servlet.http.*;
import java.io.*;
import jxl.*;
import jxl.write.*;
import jxl.format.*;
import java.util.*;
import java.sql.*;
import util.*;
import sql.*;
public class ExcelServlet extends HttpServlet
{
    public void service(HttpServletRequest request, HttpServletResponse response)
        throws IOException, ServletException
    {
	    try
	    {
	    	response.setContentType("application/vnd.ms-excel;charset=GB2312");
			WorkbookSettings ws = new WorkbookSettings();
			ws.setLocale(new Locale("zh", "GB2312"));
		    WritableWorkbook workbook = Workbook.createWorkbook(response.getOutputStream(),ws);
		    WritableSheet s1 = workbook.createSheet("订购成功", 0);
			WritableSheet s2 = workbook.createSheet("浏览记录", 1);
			s1.setColumnView(0, 50);
    		s1.setColumnView(1, 50);  
			s2.setColumnView(0, 50);
    		s2.setColumnView(1, 50);
    		s1.getSettings().setFitWidth(2);
    		s1.getSettings().setFitHeight(2);      		  
			Connection conn=null;
			String dbpc="SMS";
			String date=request.getParameter("day");
			String sid=request.getParameter("sid");
			if (date==null)  date="2005-10-21";
			if (sid==null)  sid="80010530";
			String sqlstr="SELECT distinct mdn,time FROM morder where uid='"+sid+"' and to_days(time)=to_days('"+date+"') group by mdn order by time";
			try
			{
				
				if(ConfigSvr.getInstance().getStr("SQLSvrDebug","FALSE").equalsIgnoreCase("TRUE"))
				{
					LogSvr.getInstance().println("dbpc:"+dbpc);
					LogSvr.getInstance().println("sqlstr:"+sqlstr);
				}
				conn=DbaSvr.getInstance().getConn(dbpc);
				Statement  stmt=conn.createStatement();
				ResultSet rs=stmt.executeQuery(sqlstr);
				int i=0;
				Label l=null;
				while(rs.next())
				{
					l=new Label(0,i,rs.getString(1));
					s1.addCell(l);
					l=new Label(1,i,rs.getString(2));
					s1.addCell(l);
					i++;								
				}				
				rs.close();
				sqlstr="SELECT distinct mdn,time FROM morder where uid='"+sid+"' and result='ok' and to_days(time)=to_days('"+date+"') group by mdn order by time";
				rs=stmt.executeQuery(sqlstr);
				i=0;
				while(rs.next())
				{
					l=new Label(0,i,rs.getString(1));
					s2.addCell(l);
					l=new Label(1,i,rs.getString(2));
					s2.addCell(l);
					i++;					
				}
				rs.close();				
				stmt.close();
				DbaSvr.getInstance().freeConn(dbpc,conn);
				conn=null;
			}
			catch(Exception e)
			{
				LogSvr.getInstance().println("dbpc:"+dbpc);
				LogSvr.getInstance().println("sqlstr:"+sqlstr);	
				LogSvr.getInstance().println("SQLProc runing occur:");
				e.printStackTrace(new Log());
			}
			finally
			{
				if(conn!=null)DbaSvr.getInstance().freeConn(dbpc,conn);
			}	    	
	    	workbook.write();
	    	workbook.close();
	    	
	    }
	    catch(Exception e)
	    {
	    	e.printStackTrace();
	    }
	    
    }
}