java 导出excel表格（.scv）

 
 //对于大数据量的导出，导出csv格式比较快
 jsp 页面
	首先需要引入jquery.redirect.js
	<button οnclick="exportExcel();">导出</button>

  js页面
 
 function exportExcel(){
		var param = {"userId":userId};
		$.redirect(path+ '/exportExcel.action',param);
	}
 

 后台：
 
 public class BjUserVO {
	
	 private String username;
	 private String sex ;
	 private String age;
	 private String department;// 单位
	 private String part;// 部门
	 
  }
  //需要注意的是headers 里面的字段的先后顺序要和BjUserVO里面的先后顺序保持一致
 public void exportExcel() throws Exception {
        List<Object> headList = new ArrayList<Object>(); 
		SimpleDateFormat sdf=new SimpleDateFormat("yyyyMMddHHmmss"); 
		List<BjUserVO> dataList = queryUserService.queryUserByCondition(userId);
		String time = sdf.format(new Date());
		String downloadFilePath = "xmgl" + File.separator + "download"+File.separator;
		String fileName = time+"_人员信息表"; 
		Object[] headers = {"姓名","性别","年龄","单位","部门"};
		headList = Arrays.asList(headers);
		CSVUtils<BjUserVO> cs = new CSVUtils<BjUserVO>();
		File file = cs.createCSVFileVo3(headList, dataList, downloadFilePath, fileName);
		try {
			FileUploadUtil.processFileDownloadAttach(file.getPath(), file.getName());
		} catch (UnsupportedEncodingException e) {
			e.printStackTrace();
		}
	}
 
 
 
//下面是导出的共用的模板(要是有样式的不统一的话就需要根据需求做相应的变化)
//处理文件上传的工具类和导出csv格式的工具类可以拿出来共用
 package com.bjygsoft.xmgl.util;

import java.io.BufferedWriter;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.io.OutputStreamWriter;
import java.lang.reflect.Field;
import java.lang.reflect.Method;
import java.net.URLEncoder;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.List;

import javax.servlet.http.HttpServletResponse;

/**
 * 导出csv格式工具类
 */
public class CSVUtils<T> {

    /** CSV文件列分隔符 */
    private static final String CSV_COLUMN_SEPARATOR = ",";

    /** CSV文件列分隔符 */
    private static final String CSV_RN = "\r\n";

    /**
     * CSV文件生成方法
     * @param head
     * @param dataList
     * @param outPutPath
     * @param filename
     * @return
     */
    public static File createCSVFile(List<Object> head, List<List<Object>> dataList,String outPutPath, String filename) {
        File csvFile = null;
        BufferedWriter csvWtriter = null;
        try {
            csvFile = new File(outPutPath + File.separator + filename + ".csv");
            File parent = csvFile.getParentFile();
            if (parent != null && !parent.exists()) {
                parent.mkdirs();
            }
            csvFile.createNewFile();
            
            // GB2312使正确读取分隔符","
            csvWtriter = new BufferedWriter(new OutputStreamWriter(new FileOutputStream(csvFile), "GB2312"), 1024);
            // 写入文件头部
            writeRow(head, csvWtriter);

            // 写入文件内容
            for (List<Object> row : dataList) {
                writeRow(row, csvWtriter);
            }
            csvWtriter.flush();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                csvWtriter.close();
            } catch (IOException e) {
                e.printStackTrace();
            }
        }
        return csvFile;
        
    }
    
    /**
     * CSV文件生成方法
     * @param head
     * @param dataList
     * @param outPutPath
     * @param filename
     * @return
     */
    public  File createCSVFileVo(List<Object> head, List dataList,String outPutPath, String filename, List<Object> footer) {
        File csvFile = null;
        BufferedWriter csvWtriter = null;
        try {
            csvFile = new File(outPutPath + File.separator + filename + ".csv");
            File parent = csvFile.getParentFile();
            if (parent != null && !parent.exists()) {
                parent.mkdirs();
            }
            csvFile.createNewFile();
            
            // GB2312使正确读取分隔符","
            csvWtriter = new BufferedWriter(new OutputStreamWriter(new FileOutputStream(csvFile), "GB2312"), 1024);
            // 写入文件头部
            writeRow(head, csvWtriter);

            
            Iterator<T> it =  (Iterator<T>) dataList.iterator();  
            while (it.hasNext()) {  
                T t = (T) it.next(); 
                // 利用反射，根据javabean属性的先后顺序，动态调用getXxx()方法得到属性值  
                Field[] fields = t.getClass().getDeclaredFields();  
                List<Object> row  = new ArrayList<Object>();;
                for(int i=0; i<head.size(); i++){
                	
                    String fieldName = fields[i].getName();
                    String getMethodName = "get" + fieldName.substring(0, 1).toUpperCase() + fieldName.substring(1);  
                    Class tCls = t.getClass();  
                    Method getMethod = tCls.getMethod(getMethodName, new Class[] {});  
                    Object value = getMethod.invoke(t, new Object[] {}); 
                    row.add(value);
                }
                writeRow2(row, csvWtriter);
            }
            
            // 写入文件头部
            writeRow(footer, csvWtriter);
            csvWtriter.flush();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                csvWtriter.close();
            } catch (IOException e) {
                e.printStackTrace();
            }
        }
        return csvFile;
        
    }

    /**
     * 写一行数据方法
     * @param row
     * @param csvWriter
     * @throws IOException
     */
    private static void writeRow(List<Object> row, BufferedWriter csvWriter) throws IOException {
        
        // 写入文件头部
        for (Object data : row) {
        	
        		StringBuffer buf = new StringBuffer();
                String rowStr =  buf.append("").append(data).append(",").toString();
                csvWriter.write(rowStr);
        	
            
        }
        csvWriter.newLine();
    }
    
    /**
     * 写一行数据方法
     * @param row
     * @param csvWriter
     * @throws IOException
     */
    private  void writeRow2(List<Object> row, BufferedWriter csvWriter) throws IOException {
        // 写入文件头部
        for (Object obj : row) {
        	
        	StringBuffer buf = new StringBuffer();
            
            String rowStr =  buf.append("").append(obj).append(",").toString();
            csvWriter.write(rowStr);
        }
        csvWriter.newLine();
    }

	public File createCSVFileVo2(List<Object> head, List dataList,String outPutPath, String filename, List<Object> footer, List<Object> head2) {
		File csvFile = null;
        BufferedWriter csvWtriter = null;
        try {
            csvFile = new File(outPutPath + File.separator + filename + ".csv");
            File parent = csvFile.getParentFile();
            if (parent != null && !parent.exists()) {
                parent.mkdirs();
            }
            csvFile.createNewFile();
            
            // GB2312使正确读取分隔符","
            csvWtriter = new BufferedWriter(new OutputStreamWriter(new FileOutputStream(csvFile), "GB2312"), 1024);
            // 写入文件头部
            writeRow(head, csvWtriter);

            writeRow(head2, csvWtriter);
            
            
            Iterator<T> it =  (Iterator<T>) dataList.iterator();  
            while (it.hasNext()) {  
                T t = (T) it.next(); 
                // 利用反射，根据javabean属性的先后顺序，动态调用getXxx()方法得到属性值  
                Field[] fields = t.getClass().getDeclaredFields();  
                List<Object> row  = new ArrayList<Object>();;
                for(int i=0; i<head.size(); i++){
                	
                    String fieldName = fields[i].getName();
                    String getMethodName = "get" + fieldName.substring(0, 1).toUpperCase() + fieldName.substring(1);  
                    Class tCls = t.getClass();  
                    Method getMethod = tCls.getMethod(getMethodName, new Class[] {});  
                    Object value = getMethod.invoke(t, new Object[] {}); 
                    row.add(value);
                }
                writeRow2(row, csvWtriter);
            }
            
            // 写入文件头部
            writeRow(footer, csvWtriter);
            csvWtriter.flush();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                csvWtriter.close();
            } catch (IOException e) {
                e.printStackTrace();
            }
        }
        return csvFile;
	}
	
	/**
     * CSV文件生成方法
     * @param head
     * @param dataList
     * @param outPutPath
     * @param filename
     * @return
     */
    public  File createCSVFileVo3(List<Object> head, List dataList,String outPutPath, String filename) {
        File csvFile = null;
        BufferedWriter csvWtriter = null;
        try {
            csvFile = new File(outPutPath + File.separator + filename + ".csv");
            File parent = csvFile.getParentFile();
            if (parent != null && !parent.exists()) {
                parent.mkdirs();
            }
            csvFile.createNewFile();
            
            // GB2312使正确读取分隔符","
            csvWtriter = new BufferedWriter(new OutputStreamWriter(new FileOutputStream(csvFile), "GB2312"), 1024);
            // 写入文件头部
            writeRow(head, csvWtriter);

            
            Iterator<T> it =  (Iterator<T>) dataList.iterator();  
            while (it.hasNext()) {  
                T t = (T) it.next(); 
                // 利用反射，根据javabean属性的先后顺序，动态调用getXxx()方法得到属性值  
                Field[] fields = t.getClass().getDeclaredFields();  
                List<Object> row  = new ArrayList<Object>();;
                for(int i=0; i<head.size(); i++){
                	
                    String fieldName = fields[i].getName();
                    String getMethodName = "get" + fieldName.substring(0, 1).toUpperCase() + fieldName.substring(1);  
                    Class tCls = t.getClass();  
                    Method getMethod = tCls.getMethod(getMethodName, new Class[] {});  
                    Object value = getMethod.invoke(t, new Object[] {}); 
                    row.add(value);
                }
                writeRow2(row, csvWtriter);
            }
            
            
            csvWtriter.flush();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                csvWtriter.close();
            } catch (IOException e) {
                e.printStackTrace();
            }
        }
        return csvFile;
        
    }

	
	public static File createCSVFile2(List<Object> head, List<List<Object>> dataList,String outPutPath, String filename,List<Object> headList2) {
        File csvFile = null;
        BufferedWriter csvWtriter = null;
        try {
            csvFile = new File(outPutPath + File.separator + filename + ".csv");
            File parent = csvFile.getParentFile();
            if (parent != null && !parent.exists()) {
                parent.mkdirs();
            }
            csvFile.createNewFile();
            
            // GB2312使正确读取分隔符","
            csvWtriter = new BufferedWriter(new OutputStreamWriter(new FileOutputStream(csvFile), "GB2312"), 1024);
            // 写入文件头部
            writeRow(head, csvWtriter);
         
            writeRow(headList2, csvWtriter);

            // 写入文件内容
            for (List<Object> row : dataList) {
                writeRow(row, csvWtriter);
            }
            csvWtriter.flush();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                csvWtriter.close();
            } catch (IOException e) {
                e.printStackTrace();
            }
        }
        return csvFile;
        
    }
	
	/**
	   * 下载文件
	   * @param response
	   * @param csvFilePath
	   *       文件路径
	   * @param fileName
	   *       文件名称
	   * @throws IOException
	   */
	  public static void exportFile(HttpServletResponse response, String csvFilePath, String fileName)
	                                                  throws IOException {
	    response.setContentType("application/csv;charset=UTF-8");
	    response.setHeader("Content-Disposition",
	      "attachment; filename=" + URLEncoder.encode(fileName, "UTF-8"));
	 
	    InputStream in = null;
	    try {
	      in = new FileInputStream(csvFilePath);
	      int len = 0;
	      byte[] buffer = new byte[1024];
	      response.setCharacterEncoding("UTF-8");
	      OutputStream out = response.getOutputStream();
	      while ((len = in.read(buffer)) > 0) {
	        out.write(new byte[] { (byte) 0xEF, (byte) 0xBB, (byte) 0xBF });
	        out.write(buffer, 0, len);
	      }
	    } catch (FileNotFoundException e) {
	      System.out.println(e);
	    } finally {
	      if (in != null) {
	        try {
	          in.close();
	        } catch (Exception e) {
	          throw new RuntimeException(e);
	        }
	      }
	    }
	  }
    
}



package com.bjygsoft.xmgl.util;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.io.UnsupportedEncodingException;

import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletResponse;

import org.apache.log4j.Logger;
import org.apache.struts2.ServletActionContext;


/**   
* @Title: FileUploadUtil 
* @Description: 处理上传的附件
*/
public class FileUploadUtil {
	private static Logger logger = Logger.getLogger(FileUploadUtil.class);//系统日志记录
	
	public static void uploadFile(File file,String fileFileName,String path){
		//DateUtil dateUtil = new DateUtil();
		try {
			
	        fileFileName = fileFileName.replace("&", "");
			File f = new File(path);
			// 如果文件夹不存在则创建
			if (!f.exists() && !f.isDirectory()) {
				f.mkdirs();
			}
			InputStream is = new FileInputStream(file);
			OutputStream os = new FileOutputStream(new File(path, fileFileName));

			// 因为file是存放在临时文件夹的文件，我们可以将其文件名和文件路径打印出来，看和之前的fileFileName是否相同
			logger.info("[上传附件公用类]fileFileName: " + fileFileName);
			logger.info("[上传附件公用类]文件路径: " + path);
			byte[] buffer = new byte[1024];
			int length = 0;
			while (-1 != (length = is.read(buffer, 0, buffer.length))) {
				os.write(buffer,0,length);
			}
			os.close();
			is.close();
			
} catch (Exception e) {
	e.printStackTrace();
}
	}
	

	/**
	 * 处理文件下载
	 * 
	 * @param filePath
	 * @param fileName
	 * @throws UnsupportedEncodingException
	 */
	public static void processFileDownloadAttach(String filePath, String fileName) throws UnsupportedEncodingException {

		HttpServletResponse response = ServletActionContext.getResponse();
		response.setContentType("text/html;charset=UTF-8");
		response.setContentType("application/x-msdownload;charset=UTF-8");
		response.setHeader("Content-Disposition",
				"attachment; filename=" + new String(fileName.getBytes("gbk"), "ISO8859_1"));
		downLoadCopy(filePath);
	}

	

	/**
	 * 下载文件拷贝
	 * 
	 * @param filePath
	 */
	private static void downLoadCopy(String filePath) {
		HttpServletResponse response = ServletActionContext.getResponse();
		File file = new File(filePath);
		int fileLength = (int) file.length();
		response.setContentLength(fileLength);
		/* 如果文件长度大于0 */
		if (fileLength != 0) {
			/* 创建输入流 */
			InputStream inStream = null;
			/* 创建输出流 */
			ServletOutputStream servletOS = null;
			try {
				inStream = new FileInputStream(file);
				byte[] buf = new byte[4096];
				servletOS = response.getOutputStream();
				int readLength;
				while (((readLength = inStream.read(buf, 0, buf.length)) != -1)) {
					servletOS.write(buf, 0, readLength);
				}
			} catch (Exception e) {
				e.printStackTrace();
			} finally {
				try {
					inStream.close();
					servletOS.flush();
					servletOS.close();
				} catch (Exception e) {
					e.printStackTrace();
				}
			}
		}
	}
	
	

}