SSM框架poi实现excel导出

SSM框架poi实现excel导出

前台页面

<td style="vertical-align:top;padding-left:2px;">
	<a class="btn btn-light btn-xs" onclick="toExcel();"title="导出到EXCEL"><i id="nav-search-icon"
	class="ace-icon fa fa-download bigger-110 nav-search-icon blue"></i></a>
</td>

//导出excel
	function toExcel() {
		var xlsName = "小区管理表_" + new Date().Format("yyyyMMddhhmmss");
	//	var apartment_name= $("#apartment_name").value;
		bootbox.dialog({
		 	message: "<span class='bigger-110'>请选择导出Excel版本：</span>",
		 	title: "",
		 	buttons: { 
		 		"button1":{ "label":"Excel2003", "className":"btn-sm btn-success", "callback": function () { excelVersion(xlsName + '.xls') } }, 
		 		"button2":{ "label":"Excel2007", "className":"btn-sm btn-success", "callback": function () { excelVersion(xlsName + '.xlsx') } }
		 	}
		});
	}
	
	function excelVersion(xlsName) {
		var apartment_name = $("#apartment_name").val()==null?'':$("#apartment_name").val();
		var region = $("#region").val()==null?'':$("#region").val();
		window.location.href = '<%=basePath%>village/excel.do?xlsName=' + xlsName+ '&apartment_name=' + apartment_name+'&region='+region;
	}

后台代码

	/**
	 * 导入到excel
	 * @return
	 * @throws Exception
	 */
	@RequestMapping(value="excel")
	public ModelAndView excel() throws Exception{
		Page page= new Page();
		Map<String, Object> dataMap = new HashMap<String, Object>();
		List<List<Object>> varList = new ArrayList<List<Object>>();
		PageData pd=this.getPageData();
		System.out.println(pd.toString());
		page.setPd(pd);	
		List<PageData> alist=appointmentService.listApp(page);
		List<Object> data;
		for(PageData app:alist){
			data=new ArrayList<Object>();
			data.add(app.getString("name"));
			data.add(app.getString("phone"));
			data.add(app.getString("address"));
			int state =Integer.parseInt(app.getString("state"));
			String states;
			if(state==1){
				states="已分配";
			}else{
				states="待分配";
			}
			data.add(states);
			data.add(app.getString("user_id"));
			data.add(app.getString("username"));
			varList.add(data);
		}
		ExcelInfoModel info = new ExcelInfoModel();
		String xlsName = pd.getString("xlsName");				// 获取Excel 文件名称
		info.setFileName(xlsName);
		info.setSheetIndex(0);
		info.setSheetName(xlsName.substring(0, xlsName.lastIndexOf(".")));
		String[] sheetTitles = { "租客姓名", "租客手机号", "意向房屋地址", "状态", "负责人编号", "负责人" };
		info.setSheetTitles(sheetTitles);
		info.setDatas(varList);
		dataMap.put(ExcelSettingHelper.EXTRA_EXCEL_INFO, info);
		ModelAndView mv = new ModelAndView(xlsName.endsWith(".xlsx") ? new ObjectXlsxView() : new ObjectXlsView(), dataMap);
		return mv;
	}

4个工具类代码

public class ExcelInfoModel {
	
	public enum ExcelVersion {
		VERSION2003(".xls"), VERSION2007(".xlsx"), UNKNOEW("");

		public String suffix;

		private ExcelVersion(String suffix) {
			this.suffix = suffix;
		}

		public String getSuffix() {
			return suffix;
		}

		public void setSuffix(String suffix) {
			this.suffix = suffix;
		}
	}
	
	private int sheetIndex;
	private String sheetName;
	// fileName 即 Excel 文件名称，文件名称包含后缀
	private String fileName;
	private String[] sheetTitles;
	private List<List<Object>> datas;
	
	public int getSheetIndex() {
		return sheetIndex;
	}

	public void setSheetIndex(int sheetIndex) {
		this.sheetIndex = sheetIndex;
	}

	public String getSheetName() {
		return sheetName;
	}

	public void setSheetName(String sheetName) {
		this.sheetName = sheetName;
	}

	public String getFileName() {
		return fileName;
	}

	public void setFileName(String fileName) {
		this.fileName = fileName;
	}

	public String[] getSheetTitles() {
		return sheetTitles;
	}

	public void setSheetTitles(String[] sheetTitles) {
		this.sheetTitles = sheetTitles;
	}

	public List<List<Object>> getDatas() {
		return datas;
	}

	public void setDatas(List<List<Object>> datas) {
		this.datas = datas;
	}

}

/**
 * ===========================================================================
 * @FileName 	ExcelSettingHelper.java
 * @Author 		mythwind
 * @Description	TODO 文件功能说明
 * 				处理Excel数据导入导出功能
 * ===========================================================================
 */
public class ExcelSettingHelper {
	public static final String EXTRA_EXCEL_INFO = "extra_excel_info";
	private static final short WIDTH = 20, HEIGHT = 25 * 20;
	
	private ExcelSettingHelper() { }
	public static ExcelSettingHelper instance;
	public static ExcelSettingHelper getInstance() {
		synchronized (ExcelSettingHelper.class) {
			if(instance == null) {
				synchronized (ExcelSettingHelper.class) {
					instance = new ExcelSettingHelper();
				}
			}
			return instance;
		}
	}
	
	public ExcelInfoModel.ExcelVersion getExcelVersion(String suffix) {
		if(suffix.equalsIgnoreCase(ExcelVersion.VERSION2003.getSuffix())) {
			return ExcelVersion.VERSION2003;
		} else if(suffix.equalsIgnoreCase(ExcelVersion.VERSION2007.getSuffix())) {
			return ExcelVersion.VERSION2007;
		}
		return ExcelVersion.UNKNOEW;
	}
	
	/**
	 * 导出 Excel，利用 SpringMvc自带ExcelView下载Excel
	 * @param model
	 * @param workbook
	 * @param request
	 * @param response
	 * @throws Exception
	 */
	public void buildExcelDocument(Map<String, Object> model, Workbook workbook, HttpServletRequest request,
			HttpServletResponse response) throws Exception {
		ExcelInfoModel info = (ExcelInfoModel) model.get(EXTRA_EXCEL_INFO);
		// System.out.println(info.getFileName());
		response.setContentType("application/ms-excel");
		String filename = Encodes.encodeFileURL(request, info.getFileName());
		response.setHeader("Content-Disposition", "attachment;filename=" + filename);
		Sheet sheet = workbook.createSheet(info.getSheetName());
		sheet.setDefaultColumnWidth(WIDTH);
		
		CellStyle headerStyle = buildHeaderCellStyle(workbook);
		Row row = sheet.createRow(0);
		row.setHeight(HEIGHT);
		Cell cell;
		String[] titles = info.getSheetTitles();
		for (int i = 0; i < titles.length; i++) { // 设置标题
			cell = row.createCell(i);
			cell.setCellStyle(headerStyle);
			cell.setCellValue(titles[i]);
		}
		
		CellStyle contentStyle = buildContentCellStyle(workbook);
		List<List<Object>> varList = info.getDatas();
		int rowCount = 1;
		for (List<Object> vpd : varList) {
			row = sheet.createRow(rowCount++);
			for (int j = 0; j < vpd.size(); j++) {
				String varstr = vpd.get(j) != null ? vpd.get(j).toString() : "";
				cell = row.createCell(j);
				cell.setCellStyle(contentStyle);
				cell.setCellValue(varstr);
			}
		}
	}


	/**
	 * 设置导出 Excel 表头样式
	 * @param workbook
	 * @return
	 */
	private CellStyle buildHeaderCellStyle(Workbook workbook) {
		CellStyle headerStyle = workbook.createCellStyle(); //标题样式
		headerStyle.setAlignment(CellStyle.ALIGN_CENTER);
		headerStyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
		Font headerFont = workbook.createFont();	//标题字体
		headerFont.setBoldweight(Font.BOLDWEIGHT_BOLD);
		headerFont.setFontHeightInPoints((short) 11);
		headerStyle.setFont(headerFont);
		// 自定义 Color
		// Color color = new Color() { };
		// 设置背景颜色
		headerStyle.setFillForegroundColor(IndexedColors.LEMON_CHIFFON.getIndex());
		headerStyle.setFillPattern(CellStyle.SOLID_FOREGROUND);
		// 设置边框
		headerStyle.setBorderLeft(CellStyle.BORDER_THIN);
		headerStyle.setBorderTop(CellStyle.BORDER_THIN);
		headerStyle.setBorderRight(CellStyle.BORDER_THIN);
		headerStyle.setBorderBottom(CellStyle.BORDER_THIN);
		return headerStyle;
	}
	
	/**
	 * 设置导出 Excel 主体数据的样式
	 * @param workbook
	 * @return
	 */
	private CellStyle buildContentCellStyle(Workbook workbook) {
		CellStyle contentStyle = workbook.createCellStyle(); //内容样式
		contentStyle.setAlignment(CellStyle.ALIGN_CENTER);
		contentStyle.setBorderLeft(CellStyle.BORDER_THIN);
		contentStyle.setBorderTop(CellStyle.BORDER_THIN);
		contentStyle.setBorderRight(CellStyle.BORDER_THIN);
		contentStyle.setBorderBottom(CellStyle.BORDER_THIN);
		return contentStyle;
	}
	
}

/**
 * ===========================================================================
 * @FileName 	ObjectXlsView.java
 * @Author 		mythwind
 * @Description	TODO 文件功能说明: 导出到EXCEL2007 以及以上版本
 * 				PS：使用过程中注意事项
 * 				传递的 Map key value 必须是  EXTRA_EXCEL_INFO  ExcelInfoModel
 * ===========================================================================
 */
public class ObjectXlsxView extends AbstractXlsxView {
	
	@Override
	protected void buildExcelDocument(Map<String, Object> model, Workbook workbook, HttpServletRequest request,
			HttpServletResponse response) throws Exception {
		ExcelSettingHelper.getInstance().buildExcelDocument(model, workbook, request, response);
	}
	
}

/**
 * ===========================================================================
 * @FileName 	ObjectXlsView.java
 * @Author 		mythwind
 * @Description	TODO 文件功能说明: 导出到EXCEL2003 以及以下版本
 * 				PS：使用过程中注意事项
 * 				传递的 Map key value 必须是  EXTRA_EXCEL_INFO  ExcelInfoModel
 * ===========================================================================
 */
public class ObjectXlsView extends AbstractXlsView {
	
	@Override
	protected void buildExcelDocument(Map<String, Object> model, Workbook workbook, HttpServletRequest request,
			HttpServletResponse response) throws Exception {
		
		ExcelSettingHelper.getInstance().buildExcelDocument(model, workbook, request, response);
	}
	
}