Java 导出Excel表格

版本1
思路：
1).将查询出来的数据添加到Excel表格中
2).将表格保存到项目的根目录下
3).获取根目录进行下载
4).删除根目录下的Excel表格
实现代码

public void exportExcelForm(){
	if(resourceId == null && resourceId == 0){
		outPrint(Message.common("导出失败！", false));
		return;
	}

	//通过资源主键查询购买学生列表
	List<ResourceDTO> list = shopResourceService.getBuyResUserListByResId(resourceId);
	Map<String, List<String>> map = new HashMap<String, List<String>>();
	if(list != null && list.size() != 0){
		for(int i=0 ; i<list.size() ; i++){
			ArrayList<String> arrayList = new ArrayList<String>();
			arrayList.add(list.get(i).getFullName());
			arrayList.add(list.get(i).getPhone());
			arrayList.add(list.get(i).getResourceName());
			arrayList.add(list.get(i).getGetModeText());
			arrayList.add(list.get(i).getPayText());
			arrayList.add(String.valueOf(list.get(i).getAmount()));
			map.put(i + "",arrayList);
		}
	}

	//创建Excel表头
	String[] strArray = { "学生姓名", "学生电话", "课程名称", "购买方式", "支付方式", "支付金额" };

	//执行生成表并导出到本地
	ExportExcelUtil.createExcel(map, strArray, resourceName);

}

工具类

public static void createExcel(Map<String, List<String>> map, String[] strArray, String resourceName) {
	ActionContext context = ActionContext.getContext();
	HttpServletRequest request = (HttpServletRequest) context.get(StrutsStatics.HTTP_REQUEST);
	HttpServletResponse response = (HttpServletResponse) context.get(StrutsStatics.HTTP_RESPONSE);

	// 文件名
	String filename = resourceName + ".xls";
	// 写到服务器上
	String path = request.getSession().getServletContext().getRealPath("") + "/" + filename;

	// 创建一个webbook，对应一个Excel文件
	HSSFWorkbook wb = new HSSFWorkbook();
	// 在webbook中添加一个sheet,对应Excel文件中的sheet
	HSSFSheet sheet = wb.createSheet("sheet1");
	sheet.setDefaultColumnWidth(20);// 默认列宽
	// 在sheet中添加表头第0行,注意老版本poi对Excel的行数列数有限制short
	HSSFRow row = sheet.createRow((int) 0);
	// 创建单元格，并设置值表头 设置表头居中
	HSSFCellStyle style = wb.createCellStyle();
	// 创建一个居中格式
	style.setAlignment(HSSFCellStyle.ALIGN_CENTER);

	// 添加excel title
	HSSFCell cell = null;
	for (int i = 0; i < strArray.length; i++) {
		cell = row.createCell((short) i);
		cell.setCellValue(strArray[i]);
		cell.setCellStyle(style);
	}

	// 写入实体数据 实际应用中这些数据从数据库得到,list中字符串的顺序必须和数组strArray中的顺序一致
	int i = 0;
	for (String str : map.keySet()) {
		row = sheet.createRow((int) i + 1);
		List<String> list = map.get(str);

		// 创建单元格，并设置值
		for (int j = 0; j < strArray.length; j++) {
			row.createCell((short) j).setCellValue(list.get(j));
		}

		// 将文件存到指定位置
		try {
			FileOutputStream fout = new FileOutputStream(path);
			wb.write(fout);
			fout.close();
		} catch (Exception e) {
			e.printStackTrace();
		}
		i++;
	}

	//下载excel
	OutputStream out = null;
	try {
		response.addHeader("content-disposition", "attachment;filename="
				+ java.net.URLEncoder.encode(filename, "utf-8"));

		// 下载
		out = response.getOutputStream();
		String path3 = request.getSession().getServletContext().getRealPath("") + "/" + filename;

		// inputStream：读文件，前提是这个文件必须存在，要不就会报错
		InputStream is = new FileInputStream(path3);

		byte[] b = new byte[4096];
		int size = is.read(b);
		while (size > 0) {
			out.write(b, 0, size);
			size = is.read(b);
		}
		out.close();
		is.close();
	} catch (Exception e) {
		e.printStackTrace();
	}

	// 路径为文件且不为空则进行删除
	File file = new File(path);
	if (file.isFile() && file.exists()) {
		file.delete();
	}
}

前端代码

<div style="padding: 5px; padding-left: 340px"><a href="javascript:void(0);" class="easyui-linkbutton" onclick="goImportCustomer();">导出</a></div>
<script>
    function goImportCustomer() {
        location.href=jsContextPath + "/shop/exportExcelForm.action?resourceId=" + resId + "&resourceName=" + resName;
    }
</script>

版本2
优化版本1之后的代码

public void exportExcelForm(){
	ActionContext context = ActionContext.getContext();
	HttpServletResponse response = (HttpServletResponse) context.get(StrutsStatics.HTTP_RESPONSE);

	if(resourceId == null && resourceId == 0){
		outPrint(Message.common("导出失败！", false));
		return;
	}

	//通过资源主键查询购买学生列表
	List<ResourceDTO> list = shopResourceService.getBuyResUserListByResId(resourceId);

	//创建Excel表头
	String[] strArray = { "学生姓名", "学生电话", "课程名称", "购买方式", "支付方式", "支付金额" };

	String[][] content = new String[list.size()][strArray.length];
	for (int i = 0; i < list.size(); i++) {
		ResourceDTO obj = list.get(i);
		content[i][0] = obj.getFullName();
		content[i][1] = obj.getPhone();
		content[i][2] = obj.getResourceName();
		content[i][3] = obj.getGetModeText();
		content[i][4] = obj.getPayText();
		content[i][5] = String.valueOf(list.get(i).getAmount());
	}
	String fileName = resourceName +".xls";

	//执行生成表并导出到本地
	HSSFWorkbook wb =  ExportExcelUtil.getHSSFWorkbook(resourceName, strArray, content, 20);

	//响应到客户端
	try {
		ExportExcelUtil.setResponseHeader(response, fileName);
		OutputStream os = response.getOutputStream();
		wb.write(os);
		os.flush();
		os.close();
	} catch (Exception e) {
		e.printStackTrace();
	}
}

工具类

/**
 * @author
 * @ClassName:
 * @Description: 将数据表格导出为Excel表格的工具
 * @date
 */
public class ExportExcelUtil {
    /**
     * @Title: getHSSFWorkbook
     * @Description: //TODO 创建Excel并添加内容
     * @Param [sheetName, title, values, width] sheet名称，标题，内容，默认列宽
     */
    public static HSSFWorkbook getHSSFWorkbook(String sheetName,String []title,String [][]values, Integer width){
        // 创建一个HSSFWorkbook，对应一个Excel文件
        HSSFWorkbook wb = new HSSFWorkbook();

        // 在workbook中添加一个sheet,对应Excel文件中的sheet
        HSSFSheet sheet = wb.createSheet(sheetName);

        sheet.setDefaultColumnWidth(width);// 默认列宽

        // 在sheet中添加表头第0行,注意老版本poi对Excel的行数列数有限制short
        HSSFRow row = sheet.createRow((int) 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;
    }

    /**
     * @Title: setResponseHeader
     * @Description: //TODO 发送响应流
     * @Param [fileName] 文件名称
     */
    public static void setResponseHeader(HttpServletResponse response, String fileName) {
        try {
            try {
                fileName = new String(fileName.getBytes(),"ISO8859-1");
            } catch (UnsupportedEncodingException e) {
                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();
        }
    }
}