利用模板方法模式导出Excel文件
poi版本为3.15，jdk为1.7（poi 4.1.0与jdk1.7不兼容）
数据库表与测试结果
页面按钮（其中在页面加载完毕函数中为按钮绑定点击事件，点击事件请求后台，后台以流的方式响应用户一个文件）
Controller
@RequestMapping(value = "/exportExcel")
@ResponseBody
public void exportExcel(@RequestParam Map<String, Object> params, Model model, HttpServletRequest request, HttpServletResponse resp) {
itemContentService.export(params, request, resp);
}
service接口
package com.alphajuns.ssm.service; import javax.servlet.http.HttpServletRequest; import javax.servlet.http.HttpServletResponse; import java.util.Map; public interface ItemContentService { public void export(Map<String, Object> params, HttpServletRequest request, HttpServletResponse resp); }
service实现类
package com.alphajuns.ssm.service.impl; import com.alphajuns.ssm.service.ItemContentService; import com.alphajuns.ssm.service.ItemContentSheetService; import com.alphajuns.ssm.util.AbstractExcelHelper; import com.alphajuns.ssm.util.AbstractExcelSheetHelper; import org.springframework.beans.factory.annotation.Autowired; import org.springframework.stereotype.Service; import javax.servlet.http.HttpServletRequest; import javax.servlet.http.HttpServletResponse; import java.text.DateFormat; import java.text.SimpleDateFormat; import java.util.ArrayList; import java.util.Date; import java.util.List; import java.util.Map; @Service public class ItemContentServiceImpl extends AbstractExcelHelper implements ItemContentService { @Autowired ItemContentSheetService itemContentSheetService; @Override public void export(Map<String, Object> params, HttpServletRequest request, HttpServletResponse resp) { exportExcel(params, request, resp); } @Override protected void init() { if (excelSheets == null || excelSheets.size() == 0) { synchronized (this) { if (excelSheets == null || excelSheets.size() == 0) { excelSheets = new ArrayList<AbstractExcelSheetHelper>(); excelSheets.add((AbstractExcelSheetHelper) itemContentSheetService); } } } } @Override protected String getSheetName() { return "分类列表"; } @Override protected Map<String, String> getMainHeader() { return null; } @Override protected String getFilename(Map<String, Object> params) { Date date = new Date(); DateFormat df = new SimpleDateFormat("yyyy-MM-dd"); String time = df.format(date); return "分类列表_" + time + ".xls"; } @Override protected List<Map<String, ?>> getMainRecord(Map<String, Object> params) { return null; } }
抽象的Excel帮助类
package com.alphajuns.ssm.util; import java.io.OutputStream; import java.net.URLEncoder; import java.util.List; import java.util.Map; import java.util.Map.Entry; import javax.servlet.http.HttpServletRequest; import javax.servlet.http.HttpServletResponse; 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; /** * 导出excel的帮助类 * */ public abstract class AbstractExcelHelper { protected List<AbstractExcelSheetHelper> excelSheets; public void exportExcel(Map<String, Object> params, HttpServletRequest request, HttpServletResponse resp) { resp.setContentType("application/msexcel"); resp.setCharacterEncoding("UTF-8"); OutputStream out = null; String filename = getFilename(params); init(); HSSFWorkbook workbook = new HSSFWorkbook(); HSSFSheet sheet = workbook.createSheet(getSheetName()); int nextRow = insertMainRecord(getMainRecord(params), getMainHeader(), workbook, sheet, 0); int i = 0; for(AbstractExcelSheetHelper excelSheet: excelSheets) { excelSheet.createSheet(params, workbook, i, i == 0 ? nextRow : 0); i++; } try { String agent = (String)request.getHeader("USER-AGENT"); if(agent != null && agent.toLowerCase().indexOf("firefox") > 0) { resp.setHeader("Content-Disposition","attachment; filename=" + new String(filename.getBytes("GB2312"),"ISO-8859-1")); } else { resp.setHeader("Content-Disposition","attachment; filename=" + URLEncoder.encode(filename, "UTF-8")); } out = resp.getOutputStream(); workbook.write(out); } catch (Exception e) { e.printStackTrace(); } finally { try { out.flush(); workbook.close(); out.close(); } catch (Exception e) { e.printStackTrace(); } } } protected abstract void init(); protected abstract String getSheetName(); protected abstract Map<String, String> getMainHeader(); protected abstract String getFilename(Map<String, Object> params); protected abstract List<Map<String,?>> getMainRecord(Map<String, Object> params); private int insertMainRecord(List<Map<String,?>> mapList, Map<String, String> header, HSSFWorkbook workbook, HSSFSheet sheet, int startRow) { int headerByRow = 3; HSSFCellStyle headerStyle = PoiExcelUtils.createTitleCellStyle(workbook); HSSFCellStyle contentStyle = PoiExcelUtils.createContentCellStyle(workbook); if (mapList == null || mapList.size() == 0) return startRow; Map<String, ?> map = mapList.get(0); int i = 0; int columnIdx = 0; HSSFCell headCell = null; HSSFCell contentCell = null; HSSFRow row = null; for (Entry<String, String> entry : header.entrySet()) { if (i % headerByRow == 0) { columnIdx = 0; row = sheet.createRow(startRow); startRow++; } headCell = row.createCell(columnIdx++); headCell.setCellValue(entry.getValue()); headCell.setCellStyle(headerStyle); Object object = map.get(entry.getKey()); contentCell = row.createCell(columnIdx++); if (object == null) { contentCell.setCellValue(""); } else { contentCell.setCellValue(String.valueOf(object)); } contentCell.setCellStyle(contentStyle); i++; } return ++startRow; } }
抽象的Excel Sheet帮助类
package com.alphajuns.ssm.util; import java.util.ArrayList; import java.util.List; import java.util.Map; import java.util.Map.Entry; import org.apache.log4j.Logger; 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; /** * 导出excel sheet的帮助类 * */ public abstract class AbstractExcelSheetHelper { /** * logger */ private Logger logger = Logger.getLogger(AbstractExcelSheetHelper.class); public void createSheet(Map<String, Object> params, HSSFWorkbook workbook, int sheetNo, int startRow) { HSSFSheet sheet = null; int rows = 5000; if (sheetNo == 0) { sheet = workbook.getSheetAt(sheetNo); } else { sheet = workbook.createSheet(getSheetName()); } Map<String, String> header = getHeader(params); int nextRow = insertHeaders(header, workbook, sheet, startRow); prepareParams(params); params.put("rows", rows); params.put("page", 1); /*params.put("isHandelStart", "Y");*/ List<Map<String,?>> mapList = getRecords(params); int totalCount = 0; if(mapList!=null&&!mapList.isEmpty()){ if (mapList.get(0).containsKey("TOTALCOUNT")) { Number nTotalCount = (Number) mapList.get(0).get("TOTALCOUNT"); totalCount = nTotalCount.intValue(); } else { totalCount = mapList.size(); } } String reportMaxRow = "20000"; int intReportMaxRow = 20000; if(reportMaxRow!=null && !reportMaxRow.trim().equals("")){ intReportMaxRow = Integer.parseInt(reportMaxRow.trim()); } if(totalCount>intReportMaxRow){ String errMsg = "不能导出大于"+intReportMaxRow+"条的数据，请缩小选择范围！"; HSSFCell contentCell = null; HSSFRow row = sheet.createRow(startRow); contentCell = row.createCell(0); contentCell.setCellValue(errMsg); logger.error(errMsg); return; //throw new ReportBusinessException(errMsg); } nextRow = this.insertRecords(mapList, header, workbook, sheet, nextRow); int totalPage = (int) Math.ceil((double)totalCount / rows); for (int i = 1; i < totalPage; i++) { params.put("page", i + 1); mapList = getRecords(params); nextRow = this.insertRecords(mapList, header, workbook, sheet, nextRow); } autoSizeColumn(header, sheet); } protected abstract Map<String, String> getHeader(Map<String,Object> params); protected abstract String getSheetName(); protected abstract List<Map<String,?>> getRecords(Map<String, Object> params); protected abstract void prepareParams(Map<String, Object> params); private int insertHeaders(Map<String, String> header, HSSFWorkbook workbook, HSSFSheet sheet, int startRow) { HSSFCellStyle headerStyle = PoiExcelUtils.createTitleCellStyle(workbook); HSSFRow headerRow = sheet.createRow(startRow); HSSFCell headCell = null; int i = 0; for (Entry<String, String> entry : header.entrySet()) { headCell = headerRow.createCell(i); headCell.setCellValue(entry.getValue()); headCell.setCellStyle(headerStyle); i++; } return ++startRow; } protected int insertRecords(List<Map<String,?>> mapList, Map<String, String> header, HSSFWorkbook workbook, HSSFSheet sheet, int startRow) { HSSFCellStyle contentStyle = PoiExcelUtils.createContentCellStyle(workbook); HSSFCell contentCell = null; for (Map<String, ?> map : mapList) { HSSFRow row = sheet.createRow(startRow); int cellIndex = 0; for (Entry<String, String> entry : header.entrySet()) { contentCell = row.createCell(cellIndex); Object object = map.get(entry.getKey()); if (object == null) { contentCell.setCellValue(""); } else { contentCell.setCellValue(String.valueOf(object)); } contentCell.setCellStyle(contentStyle); cellIndex++; } startRow++; } return startRow; } protected void autoSizeColumn(Map<String, String> header, HSSFSheet sheet) { for (int i = 0; i < header.size(); i++) { sheet.autoSizeColumn(i); } } }
某个类的抽象的Excel Sheet
package com.alphajuns.ssm.service;
import com.alphajuns.ssm.util.AbstractExcelSheetHelper;
public abstract class ItemContentSheetService extends AbstractExcelSheetHelper {
}
Excel Sheet的具体实现类
package com.alphajuns.ssm.service.impl; import com.alphajuns.ssm.mybatis.ItemMapper; import com.alphajuns.ssm.service.ItemContentSheetService; import org.springframework.beans.factory.annotation.Autowired; import org.springframework.stereotype.Service; import java.util.LinkedHashMap; import java.util.List; import java.util.Map; @Service public class ItemContentSheetServiceImpl extends ItemContentSheetService { @Autowired private ItemMapper itemMapper; @Override protected Map<String, String> getHeader(Map<String, Object> params) { Map<String, String> header = new LinkedHashMap<String, String>(); header.put("id", "类目ID"); header.put("parent_id", "父类目ID"); header.put("title", "分类名称"); header.put("status", "状态"); header.put("sort_order", "排列序号"); header.put("is_parent", "是否为父类目"); header.put("created", "创建时间"); header.put("updated", "更新时间"); return header; } @Override protected String getSheetName() { return "详情列表"; } @Override protected List<Map<String, ?>> getRecords(Map<String, Object> params) { return itemMapper.queryItemContentCategory(params); } @Override protected void prepareParams(Map<String, Object> params) { } }
mapper接口
List<Map<String,?>> queryItemContentCategory(Map<String, Object> params);
mapper.xml
<select id="queryItemContentCategory" resultType="map">
select * from tb_content_category
</select>
