在Action中Excel导出

1.前台页面
    exportDeviceType : function (button){
            var userid = admin.id;
            var typename = encodeURI(Ext.getCmp('c_typename').getValue());
            var        usertype = encodeURI(Ext.getCmp('c_usertype').getValue());
            var       company = encodeURI(Ext.getCmp('c_company').getValue());
            var        inteltype = encodeURI(Ext.getCmp('c_inteltype').getValue());
            location.href=window.BIZCTX_PATH + '/setjson/exportDeviceTypeList.action?userid='+
                                userid+'&typename='+typename+'&usertype='+usertype+'&company='+company
                                +'&inteltype='+inteltype;     //请求路径
    }

2.后台代码   第一种可以自定义样式

/**

     * 用户登陆日志导出·

     * @return

     */

    public void loginLogExport() {

        

        try {

            initMap();

            if(loginLog == null){

                loginLog = new SysAuthLoginLog();

            }

            if(StringUtils.isNotEmty(loginname)){

                loginLog.setLoginname(URLDecoder.decode(loginname, "UTF-8"));

            }

            List<SysAuthLoginLog> list = userService.exportLoginLogList(loginLog);

            fileName = "用户登陆日志报表";

                HSSFWorkbook book = new HSSFWorkbook();

             Sheet sheet= book.createSheet(fileName);

             

                ExcelDownWay exceldownway= new ExcelDownWay();

                 

                 Row titleRow= sheet.createRow(0);

                 titleRow.setHeightInPoints(20);

                 

              // 设置列宽    

                 sheet.setColumnWidth(0, 2000);    

                 sheet.setColumnWidth(1, 3500);    

                 sheet.setColumnWidth(2, 3500);    

                 sheet.setColumnWidth(3, 6500);    

                 sheet.setColumnWidth(4, 5500);    

                 sheet.setColumnWidth(5, 3000);    

                 sheet.setColumnWidth(6, 4000);    

                 sheet.setColumnWidth(7, 4500);   

                 sheet.setColumnWidth(8, 5500);    

                 

                 // 字体样式    

                 HSSFFont columnHeadFont = book.createFont();    

                 columnHeadFont.setFontName("宋体");    

                 columnHeadFont.setFontHeightInPoints((short) 10);    

                 columnHeadFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); 

                 // 列头的样式    

                 HSSFCellStyle columnHeadStyle = book.createCellStyle();    

                 columnHeadStyle.setFont(columnHeadFont);    

                 columnHeadStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);// 左右居中    

                 columnHeadStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);// 上下居中    

                 columnHeadStyle.setLocked(true);    

                 columnHeadStyle.setWrapText(true);    

                 columnHeadStyle.setLeftBorderColor(HSSFColor.BLACK.index);// 左边框的颜色    

                 columnHeadStyle.setBorderLeft((short) 1);// 边框的大小    

                 columnHeadStyle.setRightBorderColor(HSSFColor.BLACK.index);// 右边框的颜色    

                 columnHeadStyle.setBorderRight((short) 1);// 边框的大小    

                 columnHeadStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN); // 设置单元格的边框为粗体    

                 columnHeadStyle.setBottomBorderColor(HSSFColor.BLACK.index); // 设置单元格的边框颜色    

                 // 设置单元格的背景颜色（单元格的样式会覆盖列或行的样式）    

                 columnHeadStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);

                 columnHeadStyle.setFillForegroundColor(HSSFColor.GREEN.index);    

                 

                 HSSFFont font = book.createFont();    

                 font.setFontName("宋体");    

                 font.setFontHeightInPoints((short) 10);    

                 // 普通单元格样式    

                 HSSFCellStyle style = book.createCellStyle();    

                 style.setFont(font);    

                 style.setAlignment(HSSFCellStyle.ALIGN_LEFT);// 左右居中    

                 style.setVerticalAlignment(HSSFCellStyle.VERTICAL_TOP);// 上下居中    

                 style.setWrapText(true);    

                 style.setLeftBorderColor(HSSFColor.BLACK.index);    

                 style.setBorderLeft((short) 1);    

                 style.setRightBorderColor(HSSFColor.BLACK.index);    

                 style.setBorderRight((short) 1);    

                 style.setBorderBottom(HSSFCellStyle.BORDER_THIN); // 设置单元格的边框为粗体    

                 style.setBottomBorderColor(HSSFColor.BLACK.index); // 设置单元格的边框颜色．    

                 style.setFillForegroundColor(HSSFColor.WHITE.index);// 设置单元格的背景颜色．    

                 

            titleRow.createCell(0).setCellValue("序号");

            titleRow.createCell(1).setCellValue("用户登陆名");

            titleRow.createCell(2).setCellValue("用户名");

            titleRow.createCell(3).setCellValue("集团");

            titleRow.createCell(4).setCellValue("登陆时间");

            titleRow.createCell(5).setCellValue("登陆IP");

            titleRow.createCell(6).setCellValue("登陆状态");

            titleRow.createCell(7).setCellValue("状态");

            titleRow.createCell(8).setCellValue("备注");

            

             for(int i=0;i<titleRow.getLastCellNum();i++){

                 titleRow.getCell(i).setCellStyle(columnHeadStyle);

             }

            

            if(list.size()>0){

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

                    int  index=i+1;

                    Row contentRow= sheet.createRow(index);

                    contentRow.setHeightInPoints(15);

                    contentRow.createCell(0).setCellValue(index);

                    SysAuthLoginLog data= (SysAuthLoginLog) list.get(i);

                    contentRow.createCell(1).setCellValue(data.getLoginname());

                    contentRow.createCell(2).setCellValue(data.getUsername());

                    contentRow.createCell(3).setCellValue(data.getBlocname());

                    contentRow.createCell(4).setCellValue(data.getLogindate());

                    contentRow.createCell(5).setCellValue(data.getLoginip());

                    contentRow.createCell(6).setCellValue(data.getLoginflag());

                    if (data.getStatus() == 2 || data.getStatus().equals(2)) {

                        contentRow.createCell(7).setCellValue("退出");

                    } else {

                        contentRow.createCell(7).setCellValue("登陆");

                    }

                    contentRow.createCell(8).setCellValue(data.getRemark());

                     for(int m=0;m<contentRow.getLastCellNum();m++){

//                         contentRow.getCell(m).setCellStyle(exceldownway.setBookListStyle(book));

                         contentRow.getCell(m).setCellStyle(style);

                     }

                }

            }

           new ExcelDownWay().getCommonExcelListWay(book,fileName);

            } catch (Exception e) {

                try {

                    getResponse().getWriter().print("<script language=javascript>alert('导出异常,请稍后再试!');history.back();</script>");

                } catch (IOException e1) {

                    e1.printStackTrace();

                }

            }

    }  

   

//第二种简单样式

    /**

     * Excel设备类型设置信息

     * @return

     */

    public void exportDeviceTypeList() {

        try {

            initMap();

            if(deviceType==null){

                deviceType=new DeviceType();

            }

            if(typename!=null&&!typename.equals("")&&!typename.equals("null")){

                deviceType.setTypename(URLDecoder.decode(typename,"UTF-8"));

            }

            List<DeviceType> list=setService.findPageDeviceTypeList(deviceType);

            String fileName = "终端类型设置报表";

                HSSFWorkbook book = new HSSFWorkbook();

             ExcelDownWay exceldownway= new ExcelDownWay();

             

             //2.设置列宽（列数要对应上）

             String str="7,20,25,15,15,20,25,20";

             List<String> numberList=Arrays.asList(str.split(","));

             Sheet sheet= book.createSheet(fileName);

             Row titleRow= sheet.createRow(0);

             sheet= exceldownway.setColumnWidth(sheet,numberList);

             sheet.setDefaultRowHeight((short) 18);

             titleRow.setHeightInPoints(20);

                

            titleRow.createCell(0).setCellValue("序号");

            titleRow.createCell(1).setCellValue("终端类型ID");

            titleRow.createCell(2).setCellValue("终端类型名称");

            titleRow.createCell(3).setCellValue("用户类型");

            titleRow.createCell(4).setCellValue("所属厂家");

            titleRow.createCell(5).setCellValue("终端网络类型");

            titleRow.createCell(6).setCellValue("创建时间");

            titleRow.createCell(7).setCellValue("操作员");

             for(int i=0;i<titleRow.getLastCellNum();i++){

                 titleRow.getCell(i).setCellStyle(exceldownway.setBookHeadStyle(book));

             }

            if(list.size()>0){

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

                    int  index=i+1;

                    Row contentRow= sheet.createRow(index);

                    contentRow.createCell(0).setCellValue(index);

                    DeviceType data= (DeviceType) list.get(i);

                    contentRow.createCell(1).setCellValue(data.getTypeid());

                    contentRow.createCell(2).setCellValue(data.getTypename());

                    contentRow.createCell(3).setCellValue(data.getUsertype());

                    contentRow.createCell(4).setCellValue(data.getCompany());

                    contentRow.createCell(5).setCellValue(data.getInteltype());

                    contentRow.createCell(6).setCellValue(data.getCreatetime());

                    contentRow.createCell(7).setCellValue(data.getUsername());

                     for(int m=0;m<contentRow.getLastCellNum();m++){

                         contentRow.getCell(m).setCellStyle(exceldownway.setBookListStyle(book));

                     }

                }

            }

           new ExcelDownWay().getCommonExcelListWay(book,fileName);

            } catch (Exception e) {

                try {

                    getResponse().getWriter().print("<script language=javascript>alert('导出异常,请稍后再试!');history.back();</script>");

                } catch (IOException e1) {

                    e1.printStackTrace();

                }

            }

    } 

package com.sf.fys.controller.recommendation;

import java.io.BufferedInputStream;
import java.io.BufferedOutputStream;
import java.io.ByteArrayInputStream;
import java.io.ByteArrayOutputStream;
import java.io.InputStream;
import java.io.OutputStream;
import java.util.List;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.Sheet;

/**
 *
 * @类名称：导出Excel
 * @类描述：
 * @创建时间：2015-3-10 下午03:42:58
 * @修改时间：2015-3-10 下午03:42:58
 * @修改备注：
 * @version 1.0
 */
public class ExcelDownWay {
    
    /**公共的Excel导出方法**/
    public  void  getCommonExcelListWay(HSSFWorkbook book,String fileName,HttpServletRequest request,HttpServletResponse response){
        try{
             fileName=javax.mail.internet.MimeUtility.encodeText(fileName,  "UTF-8" ,  "B" );
             ByteArrayOutputStream outStream = new ByteArrayOutputStream();
             book.write(outStream);
             InputStream excel=new ByteArrayInputStream(outStream.toByteArray());
             
             // 以流的形式下载文件。  
             InputStream fis = new BufferedInputStream(excel);  
             byte[] buffer = new byte[fis.available()];  
             fis.read(buffer);  
             fis.close();
             response.setCharacterEncoding("UTF-8");
             // 清空response  
             response.reset();  
             // 设置response的Header  
             response.addHeader("Content-Disposition", "attachment;filename="  
                     + new String(fileName+".xls"));  
             response.addHeader("Content-Length", "");  
             OutputStream toClient = new BufferedOutputStream(response.getOutputStream());  
             response.setContentType("application/vnd.ms-excel;charset=utf-8");  
             toClient.write(buffer);  
             toClient.flush();  
             toClient.close();  
    } catch (Exception e) {
    }
             
    }
    
    
    /**
     * 设置列宽
     * @param sheet
     * @param numberList
     * @return
     */
    public Sheet  setColumnWidth(Sheet sheet,List <String> numberList){
         for(int i=0;i<numberList.size();i++){
             sheet.setColumnWidth(i,200 *Integer.parseInt(numberList.get(i)));
         }
         return sheet;
    }
    
    /**
     * 设置标题部分
     * @param book
     * @return
     */
    public  HSSFCellStyle  setBookHeadStyle(HSSFWorkbook book){
         HSSFCellStyle headerStyle = (HSSFCellStyle) book.createCellStyle();// 设置字体样式  
        headerStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);  
        headerStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);  
        HSSFFont headerFont = (HSSFFont) book.createFont();  
        headerFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);// 字体加粗  
        headerFont.setFontName("Times New Roman");  
        headerFont.setFontHeightInPoints((short)10);  
        headerStyle.setFont(headerFont);  
        headerStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN); // 下边框  
        headerStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);// 左边框  
        headerStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);// 上边框  
        headerStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);// 右边框
        headerStyle.setFillBackgroundColor((short)10);
        headerStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
        headerStyle.setFillForegroundColor(new HSSFColor.GREEN().getIndex());
        headerStyle.setFillBackgroundColor(new HSSFColor.GREEN().getIndex());
        return headerStyle;
    }
    
    
    /**
     * 设置列表部分
     * @param book
     * @return
     */
    public  HSSFCellStyle  setBookListStyle(HSSFWorkbook book){
         HSSFCellStyle headerStyle = (HSSFCellStyle) book.createCellStyle();// 设置字体样式  
        headerStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);  
        headerStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);  
        HSSFFont headerFont = (HSSFFont) book.createFont();  
        headerFont.setFontName("Times New Roman");  
        headerFont.setFontHeightInPoints((short)10);  
        headerStyle.setFont(headerFont);  
        headerStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN); // 下边框  
        headerStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);// 左边框  
        headerStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);// 上边框  
        headerStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);// 右边框
        headerStyle.setFillBackgroundColor((short)8);
        return headerStyle;
    }
    
    
    
}