java使用opi导出excel

1.导入jar包

        <!-- xls格式 -->
        <dependency>
		<groupId>org.apache.poi</groupId>
		<artifactId>poi</artifactId>
		<version>3.14</version>
	</dependency>
	<!-- xlsx格式 -->
	<dependency>
		<groupId>org.apache.poi</groupId>
		<artifactId>poi-ooxml</artifactId>
		<version>3.14</version>
	</dependency>

2.工具类

package com.hr.basic.utils;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;

/**
*
* @author gw
* @version 2017年3月10日 上午8:07:52
*/
public class ExcelUtil {
	
	/**
	 * 
	 * @methodName:getHSSFWorkbook
	 * * @param sheetName
	 * * @param title 表头
	 * * @param values 表格内容
	 * * @param wb
	 * * @return
	 * @return HSSFWorkbook
	 * @author gw 
	 * @date 2017年3月10日上午11:19:20
	 */
	public static HSSFWorkbook getHSSFWorkbook(String sheetName, String []title, String [][]values, HSSFWorkbook wb){
		//第一步，创建一个webbook,对应一个Excel文件
		if(wb == null){
			wb = new HSSFWorkbook();
		}
		//第二步，在webbook中添加一个sheet,对应excel文件中的sheet
		HSSFSheet sheet = wb.createSheet(sheetName);
		//第三步，在sheet中添加表头第0行
		HSSFRow row = sheet.createRow(0);
		//第四步，创建单元格，并设置表头，设置表头居中
		HSSFCellStyle style = wb.createCellStyle();
		style.setAlignment(HSSFCellStyle.ALIGN_CENTER);//创建一个居中格式
		
		HSSFCell cell = null;
		
		//创建标题
		for(int i=0;i<title.length;i++){
			cell = row.createCell(i);
			cell.setCellValue(title[i]);
			cell.setCellStyle(style);
		}
		
		//创建内容
		for(int i=0;i<values.length;i++){
			row = sheet.createRow(i+1);
			for(int j=0;j<values[i].length;j++){
				row.createCell(j).setCellValue(values[i][j]);
			}
		}
		return wb;
	}
}

3.导入表格信息

      @RequestMapping(value = "/exportExcel", method = RequestMethod.POST)
      @ResponseBody
      public String exportExcel(Map<String, Object> params, HttpServletRequest request, HttpServletResponse response) {
        List<Bz_Pay> list = payDao.getList(params);
	String fileName = "Excel"+System.currentTimeMillis()+".xls"; //文件名 
        String sheetName = "收款管理";//sheet名
        
        String []title = new String[]{"付款人","付款金额","上传日期","付款时间","确认人"};//标题
        
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
        
        String [][]values = new String[list.size()][];
        for(int i=0;i<list.size();i++){
            values[i] = new String[title.length];
            //将对象内容转换成string
            Bz_Pay obj = list.get(i);
            values[i][0] = obj.getPayer();//付款人
            values[i][1] = String.valueOf(obj.getPayment());//付款金额
            try {
            	values[i][2] = sdf.format(obj.getCreateTime());//上传日期
		values[i][5] = sdf.format(obj.getVerifyTime());//确认时间
	    } catch (NullPointerException e) {
            }
            values[i][3] = obj.getPayTime();//付款时间
            values[i][4] = (String) obj.get("salerName");//确认人
        }
        
        HSSFWorkbook wb = ExcelUtil.getHSSFWorkbook(sheetName, title, values, null);
        
        String  excelPath = request.getSession().getServletContext().getRealPath("res/upload/excel/export/");
        //request.getSession().getServletContext() 获取的是Servlet容器对象，
     //相当于tomcat容器了。getRealPath("/") 获取实际路径，“/”指代项目根目录，
     //所以代码返回的是项目在容器中的实际发布运行的根路径
       File path = new File(excelPath);
        //将文件存到指定位置  
        String exPath = "res/upload/excel/"+"export"+fileName;
        try {  
             this.setResponseHeader(response, fileName); 
             FileOutputStream os = new FileOutputStream(path+fileName);
             //OutputStream os = response.getOutputStream();  
             wb.write(os);  
             os.flush();  
             os.close();  
        } catch (Exception e) {  
             e.printStackTrace();  
        }  
        return exPath;
    }
    
     public void setResponseHeader(HttpServletResponse response, String fileName) {  
         try {  
              try {  
                   fileName = new String(fileName.getBytes(),"ISO8859-1");  
              } catch (UnsupportedEncodingException e) {  
                   // TODO Auto-generated catch block  
                   e.printStackTrace();  
              }  
              response.setContentType("application/x-msdownload;charset=ISO8859-1"); 
              response.setHeader("Content-Disposition", "attachment;filename="+ fileName);  
              response.addHeader("Pargam", "no-cache");  
              response.addHeader("Cache-Control", "no-cache");  
         } catch (Exception ex) {  
              ex.printStackTrace();  
         }  
    } 

其中

            try {
            	values[i][2] = sdf.format(obj.getCreateTime());//上传日期
		values[i][5] = sdf.format(obj.getVerifyTime());//确认时间
	    } catch (NullPointerException e) {
            }

是因为我的数据库中有数据为空，需要捕获一下

我是用的是，先在服务器上保存文件，然后使用浏览器的下载功能，下载到本地

调用方法返回url字符串，将字符串给浏览器，window.open(data.responseText);

exportAllExcel:function(){
        Ext.Msg.alert("信息提示","正在导出，请稍后！");
	Ext.Ajax.request({
          url:'business/pay/exportExcel',
          method:'POST',
          //params:{path:record.data.payId},
          success:function(data){
        	Ext.Msg.alert("信息提示","导出成功！");
        	window.open(data.responseText);
          },
	  failure : function(){
		Ext.Msg.alert("信息提示", "操作失败，请检查网络是否正常！");
	  }
	});
  }