package com.gree;

import java.io.IOException;

import java.io.OutputStream;

import java.io.PrintWriter;

import java.io.UnsupportedEncodingException;

import java.sql.PreparedStatement;

import java.util.ArrayList;

import java.util.List;

import java.util.Locale;

import javax.servlet.ServletOutputStream;

import javax.servlet.http.Cookie;

import javax.servlet.http.HttpServletResponse;

import javax.servlet.http.HttpServletRequest;

import jxl.Workbook;

import jxl.format.Colour;

import jxl.format.UnderlineStyle;

import jxl.write.Label;

import jxl.write.WritableCellFormat;

import jxl.write.WritableFont;

import jxl.write.WritableSheet;

import jxl.write.WritableWorkbook;

import jxl.write.WriteException;

import jxl.write.biff.RowsExceededException;

public class CreateExcel {

    public void createxcel(HttpServletResponse res,HttpServletRequest req) throws Exception{

        OutputStream os=null;

        WritableWorkbook wwb=null;

        ConnDB db=new ConnDB();

        String str="select * from user";

        ArrayList<Student> list=db.getList(str);

        try {

            java.text.SimpleDateFormat df = new java.text.SimpleDateFormat("yyyy-MM-dd");

            String today = df.format(new java.util.Date());

     String fileName="改模时间报表-"+today;

            //System.out.println(fileName);

            fileName=new String(fileName.getBytes("gb2312"), "ISO8859_1");

            //System.out.println(fileName);

            HttpServletResponse response=res;

            HttpServletRequest request=req;

            response.reset();

            response.setContentType("APPLICATION/OCTET-STREAM");

            if (request.getHeader("User-Agent").indexOf("MSIE 5.5") != -1) { // MS

                response.setHeader("Content-Disposition", "filename="+fileName+".xls");

            } else {

                response.addHeader("Content-Disposition","attachment;filename="+fileName+".xls");

            }

            /**/

            os = response.getOutputStream();

            wwb = Workbook.createWorkbook(os);

            WritableSheet sheet = wwb.createSheet("sheet1", 0);

            jxl.write.WritableCell cell = null;

            WritableFont wf = new WritableFont(WritableFont.ARIAL,12,WritableFont.NO_BOLD,false,

            UnderlineStyle.NO_UNDERLINE,Colour.BLUE);

            WritableCellFormat formatH =  new WritableCellFormat(wf);

            formatH.setAlignment(jxl.format.Alignment.CENTRE);

            cell = new Label(0, 0,"用户表", formatH);

            sheet.addCell(cell);

            sheet.mergeCells(0,0,14,0);

            formatH = getFormat("BOLD", 9, Colour.BLACK);

            formatH.setAlignment(jxl.format.Alignment.CENTRE);

            formatH.setBorder(jxl.format.Border.ALL,jxl.format.BorderLineStyle.THIN);

            WritableCellFormat format = getFormat("NOBOLD", 9, Colour.BLACK);

            //format.setAlignment(jxl.format.Alignment.LEFT);

            format.setBorder(jxl.format.Border.ALL,jxl.format.BorderLineStyle.THIN);

            //

            cell = new Label(0, 1,"名字", formatH);

            sheet.addCell(cell);

            cell = new Label(1, 1,"密码", formatH);

            sheet.addCell(cell);

            cell = new Label(2, 1,"最后登录时间", formatH);

            sheet.addCell(cell);

            /**/

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

                cell = new Label(0, i+2, list.get(i).getName(), format);

                sheet.addCell(cell);

                cell = new Label(1, i+2, list.get(i).getPassword(), format);

                sheet.addCell(cell);

                cell = new Label(2, i+2, list.get(i).getLastlogintime(), format);

                sheet.addCell(cell);

sheet.setColumnView(0,10);

sheet.setColumnView(1,20);

sheet.setColumnView(2,20);

    }

wwb.write();

            wwb.close();

            os.close();

            response.setStatus(response.SC_OK);

            response.flushBuffer();

        } catch (RowsExceededException e) {

            // TODO Auto-generated catch block

            e.printStackTrace();

        } catch (UnsupportedEncodingException e) {

            // TODO Auto-generated catch block

            e.printStackTrace();

        } catch (WriteException e) {

            // TODO Auto-generated catch block

            e.printStackTrace();

        } catch (IOException e) {

            // TODO Auto-generated catch block

            e.printStackTrace();

        }

    }

 WritableCellFormat getFormat(String style, int size, Colour color)

    {

    WritableFont wfc = null;

    if("BOLD".equals(style))

        wfc = new WritableFont(WritableFont.ARIAL, size, WritableFont.BOLD, false, UnderlineStyle.NO_UNDERLINE, color);

    else

    if("NOBOLD".equals(style))

        wfc = new WritableFont(WritableFont.ARIAL, size, WritableFont.NO_BOLD, false, UnderlineStyle.NO_UNDERLINE, color);

    return new WritableCellFormat(wfc);

}

}

<%

  int opt;

  if(request.getParameter("opt")==null) opt=0;

  else opt=Integer.parseInt(request.getParameter("opt"));

  if(opt==1){

   CreateExcel excel=new CreateExcel();

   excel.createxcel(response, request);

   }

   else{

    %>

   <a href="index.jsp?opt=1">导出excel表格</a> &nbsp;

   <a href="importexcel.jsp">导入excel表格</a>&nbsp;

   <%}

   }

    %>