web项目导出excel并下载

web项目导出excel并下载

一、先导入POI的jar包

一般这个版本的jar都有下载，其他的高版本的可能会有冲突。我试过过几个。

二、创建工具类

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;

public class ExportExcel{
	/*
	 * 导出Excel
	 */
	public static HSSFWorkbook getHSSFWorkbook(String sheetName,
			String[] title, String[][] values, HSSFWorkbook wb) {
		// 第一步，创建一个HSSFWorkbook，对应一个Excel文件
		if (wb == null) {
			wb = new HSSFWorkbook();
		}
 
		// 第二步，在workbook中添加一个sheet，对应Excel文件中的sheet
		HSSFSheet sheet = wb.createSheet(sheetName);
 
		// 第三步，在sheet中添加表头第0行，注意老版本poi对Excel行数列数有限制
		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;
	}

}

三、action接口方法

下面展示一些 java码片。

@Action("exportExcelData")
	public void exportExcelData()throws Exception{
//		HttpServletRequest request = ServletActionContext.getRequest();
		HttpServletResponse response=ServletActionContext.getResponse();
		String queryConId = getRequestValue("queryConId");
		//sheet名
		String sheetName = "user";	//sheet名
		String title[] = new String[6];//列数
		String[][] content= {};//内容
		String fileName="usertabel";//文件名
		System.out.println(queryConId);
		if("1002".equals(queryConId)) {
			// 获取数据 用户
			List<TUser> list = sysService.findkselectuser();
			//Excel标题
			//String sql="select cusername,cdeptname,cloginname,sex,telPhone from t_user";
			title[0] ="用户名";
			 title[1] ="部门";
			 title[2] ="登录名";
			 title[3] ="性别";
			 title[4] ="电话";
			//Excel文件名
			 sheetName = "user";	//sheet名
			 fileName = "usertable" + System.currentTimeMillis() + ".xls";
			 content = new String[list.size()][];
			for(int i=0; i<list.size(); i++){
				content[i] = new String[title.length];
				TUser g = list.get(i);
				content[i][0] = g.getCuserName();
				content[i][1] = g.getCdeptName();
				content[i][2] = g.getCloginName();
				content[i][3] = g.getSex().toString();
				content[i][4] = g.getTelPhone();
			}
		}else if("1007".equals(queryConId)) {
			// 获取数据 项目
			List<TProjectInformation> list = sysService.findselectTprojet();
			//Excel标题
			//		String sql="select projectname,projectdescribe,pszjName,projectdate,createdate,createusername from t_projectinformation";
			title[0] ="项目名称";
			title[1] ="项目描述";
			title[2] ="评审人姓名";
			title[3] ="项目时间";
			title[4] ="创建时间";
			title[5]="创建人";
			sheetName = "projet";
			//Excel文件名
			fileName = "projet" + System.currentTimeMillis() + ".xls";
			content = new String[list.size()][];
			for(int i=0; i<list.size(); i++){
				content[i] = new String[title.length];
				TProjectInformation g = list.get(i);
				content[i][0] = g.getProjectName();
				content[i][1] = g.getProjectDescribe();
				content[i][2] = g.getPszjName();
				content[i][3] = g.getProjectDate().toString();
				content[i][4] = g.getCreateDate().toString();
				content[i][5] = g.getCreateUserName();
			}
		}else {
			// 获取数据 用户
			List<TUser> list = sysService.findkselectuser();
			//Excel标题
			//String sql="select cusername,cdeptname,cloginname,sex,telPhone from t_user";
			title[0] ="用户名";
			 title[1] ="部门";
			 title[2] ="登录名";
			 title[3] ="性别";
			 title[4] ="电话";
			//Excel文件名
			 fileName = "usertable" + System.currentTimeMillis() + ".xls";
			  sheetName = "user";	//sheet名
			content = new String[list.size()][];
			for(int i=0; i<list.size(); i++){
				content[i] = new String[title.length];
				TUser g = list.get(i);
				content[i][0] = g.getCuserName();
				content[i][1] = g.getCdeptName();
				content[i][2] = g.getCloginName();
				content[i][3] = g.getSex().toString();
				content[i][4] = g.getTelPhone();
			}
		}
		//创建HSSFWorkbook
		HSSFWorkbook wb = ExportExcel.getHSSFWorkbook(sheetName, title, content, null);
		
		try {
			this.setResponseHeader(response, fileName);
			OutputStream out = response.getOutputStream();
			wb.write(out);
			out.flush();
			out.close();
			
		} catch (IOException e) {
			e.printStackTrace();
		}
	}
	
	
	 //发送响应流方法
    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/octet-stream;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();
        }
    }