springmvc实现导出数据信息为excle表格

1.项目增加导出日志信息

2.项目中导入poi-*.jar等操作excel文件的jar文件

poi-3.7-20120326.jar

poi-excelant-3.7-20101029.jar

poi-ooxml-3.7.jar

poi-ooxml-schemas-3.7.jar

Excel导出就是根据前台条件将参数传到controller，根据参数去数据库中进行查询，查询出list集合，将list集合生成excle数据下载。

代码片段：

Contorller.java

/**
	 * 导出信息
	 * @param model
	 */
	@RequestMapping("exportCustomer.do")
	@SystemControllerLog(description = "数据库表单导出Excle")
	public void exportCustomer(ModelMap model) {
		//TODO 如需添加条件
		//model.addAttribute("username", nameStr);
		//获取需要导出的数据List
		List<CMcustomer> cusList=customerService.exportCustomer(model);
			//使用方法生成excle模板样式
		HSSFWorkbook workbook = customerService.createExcel(cusList, request);
		SimpleDateFormat format = new SimpleDateFormat("yyyyMMddHHmmss"); // 定义文件名格式

		try {
		//定义excle名称 ISO-8859-1防止名称乱码
			String msg = new String(
					("客户信息_" + format.format(new Date()) + ".xls").getBytes(),
					"ISO-8859-1");
			// 以导出时间作为文件名
			response.setContentType("application/vnd.ms-excel");
			response.addHeader("Content-Disposition", "attachment;filename="
					+ msg);
			workbook.write(response.getOutputStream());
		} catch (IOException e) {
			logger.error(e);
		}
	}

2.Service中createExcel方法

public HSSFWorkbook createExcel(List<CMcustomer> cusList,
		HttpServletRequest request) {

		// 创建一个webbook，对应一个excel文件
		HSSFWorkbook workbook = new HSSFWorkbook();
		// 在webbook中添加一个sheet,对应excel文件中的sheet
		HSSFSheet sheet = workbook.createSheet("客户信息表");
		// 设置列宽
		sheet.setColumnWidth(0, 25 * 100);
		sheet.setColumnWidth(1, 35 * 100);
		sheet.setColumnWidth(2, 35 * 100);
		sheet.setColumnWidth(3, 40 * 100);
		sheet.setColumnWidth(4, 45 * 100);
		sheet.setColumnWidth(5, 45 * 100);
		sheet.setColumnWidth(6, 50 * 100);
		sheet.setColumnWidth(7, 80 * 100);
		sheet.setColumnWidth(8, 35 * 100);
		sheet.setColumnWidth(9, 40 * 100);
		// 在sheet中添加表头第0行
		HSSFRow row = sheet.createRow(0);
		// 创建单元格，并设置表头，设置表头居中
		HSSFCellStyle style = workbook.createCellStyle();
		// 创建一个居中格式
		style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
		// 带边框
		style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
		// 生成一个字体
		HSSFFont font = workbook.createFont();
		// 字体增粗
		font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
		// 字体大小
		font.setFontHeightInPoints((short) 12);
		// 把字体应用到当前的样式
		style.setFont(font);

		// 单独设置整列居中或居左
		HSSFCellStyle style1 = workbook.createCellStyle();
		style1.setAlignment(HSSFCellStyle.ALIGN_CENTER);
		HSSFCellStyle style2 = workbook.createCellStyle();
		style2.setAlignment(HSSFCellStyle.ALIGN_LEFT);

		HSSFCellStyle style3 = workbook.createCellStyle();
		style3.setAlignment(HSSFCellStyle.ALIGN_LEFT);
		HSSFFont hssfFont = workbook.createFont();
		hssfFont.setColor(HSSFFont.COLOR_RED);
		hssfFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
		style3.setFont(hssfFont);

		HSSFCellStyle style4 = workbook.createCellStyle();
		style4.setAlignment(HSSFCellStyle.ALIGN_LEFT);
		HSSFFont hssfFont1 = workbook.createFont();
		hssfFont1.setColor(HSSFFont.COLOR_NORMAL);
		hssfFont1.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
		style4.setFont(hssfFont1);

		HSSFCell cell = row.createCell(0);
		cell.setCellValue("序号");
		cell.setCellStyle(style);

		cell = row.createCell(1);
		cell.setCellValue("客户姓名");
		cell.setCellStyle(style);

		cell = row.createCell(2);
		cell.setCellValue("性别");
		cell.setCellStyle(style);

		cell = row.createCell(3);
		cell.setCellValue("状态");
		cell.setCellStyle(style);

		cell = row.createCell(4);
		cell.setCellValue("电话");
		cell.setCellStyle(style);

		cell = row.createCell(5);
		cell.setCellValue("邮箱");
		cell.setCellStyle(style);

		cell = row.createCell(6);
		cell.setCellValue("地址");
		cell.setCellStyle(style);
		for (int i = 0; i < cusList.size(); i++) {
			String logTypeDis = "";
			row = sheet.createRow(i + 1);
			CMcustomer cMcustomer = cusList.get(i);
			// 创建单元格，并设置值
			// 编号列居左
			HSSFCell c1 = row.createCell(0);
			c1.setCellStyle(style2);
			c1.setCellValue(i);
			HSSFCell c2 = row.createCell(1);
			c2.setCellStyle(style1);
			c2.setCellValue(cMcustomer.getCustomername());//客户姓名

			String sexStr = cMcustomer.getSex();//性别 0:女,1:男
			String sex="";
			if ("1".equals(sexStr)) {
				sex="男";
			}
			if ("0".equals(sexStr)) {
				sex="女";
			}
			HSSFCell c3 = row.createCell(2);//性别
			c3.setCellStyle(style1);
			c3.setCellValue(sex);
			
			String statusStr = cMcustomer.getStatus();//客户状态1.在职，2.离职
			String status="";
			if ("1".equals(statusStr)) {
				status="在职";
			}
			if ("2".equals(statusStr)) {
				status="离职";
			}
			HSSFCell c4 = row.createCell(3);//状态
			c4.setCellStyle(style1);
			c4.setCellValue(status);
			String customerid = cMcustomer.getCustomerid();//客户id
			List<CMphone> phoneList = cMphoneMapper.selectByCustomerid(customerid);
			String phone="";
			if (phoneList!=null&&phoneList.size()>0) {
				for (int j = 0; j < phoneList.size(); j++) {
					phone = phoneList.get(j).getPhone();
				}
			}
			HSSFCell c5 = row.createCell(4);//电话
			c5.setCellStyle(style1);
			c5.setCellValue(phone);
			List<CMemail> emailList = cMemailMapper.selectAll(customerid);
			String email="";
			if (emailList!=null&&emailList.size()>0) {
				for (int j = 0; j < emailList.size(); j++) {
					email = emailList.get(j).getEmail();
				}
			}
			HSSFCell c6 = row.createCell(5);//邮箱
			c6.setCellStyle(style1);
			c6.setCellValue(email);
			CMaddress cMaddress=new CMaddress();
			cMaddress.setCustomerid(customerid);
		List<CMaddress> adderssList = cMaddressMapper.selectAll(cMaddress);
			String adderss="";
			if (adderssList!=null&&adderssList.size()>0) {
				for (int j = 0; j < adderssList.size(); j++) {
					adderss = adderssList.get(j).getAddress();
				}
			}
			HSSFCell c7 = row.createCell(6);//地址
			c7.setCellStyle(style1);
			c7.setCellValue(adderss);

			//使用默认格式
			row.createCell(1).setCellValue(cMcustomer.getCustomername());
			row.createCell(2).setCellValue(sex);
			row.createCell(3).setCellValue(status);
			row.createCell(4).setCellValue(phone);
			row.createCell(5).setCellValue(email);
			row.createCell(6).setCellValue(adderss);
		}
		return workbook;
}

3.页面jsp调用

//导出信息
	 	function exporBtn(){
		$.ajax({
			type:"POST",
			url:"<%=path%>/customer/exportCustomer.do",
			success:function(data){
				window.open('<%=path%>/customer/exportCustomer.do');
			}
			
		});
	}