POI导出excel,基于jgrid的公共组件

红太狼 2022-07-27 12:00 115阅读 0赞

最近由于项目需要,于是做了jgrid表格的公共导出组件,此公共组件可以sql查询导出、hql查询导出,主要采用POI技术实现报表的导出。

前端jgrid公共导出js方法：

//jqgrid导出excel
    function exportGrid(gridId, sqlTemplate, fileName, extraParams){
    	var exportUrl = contextPath + "/export/jqgrid";
    	var checkBoxTemplate = "<input role='checkbox' id='cb_list' class='cbox' type='checkbox'/>";
    	var colModels = $("#" + gridId).jqGrid('getGridParam','colModel');
    	var colNames = $("#" + gridId).jqGrid('getGridParam','colNames');
    	var ids = $("#" + gridId).jqGrid ('getGridParam', 'selarrrow');
    	var postData = $("#" + gridId).jqGrid ('getGridParam', 'postData');
    	var caption = $("#" + gridId).jqGrid ('getGridParam', 'caption');
    	var fields = [];
    	var enabledColNameIndex = [];
    	for(var i = 0;i < colModels.length; i++){
    		var col = colModels[i];
    		if(!col.hidden && col.name != "cb" && col.name.indexOf("customColumn") == -1){
    			enabledColNameIndex.push(i);
    			fields.push(col.index);
    		}
    	}
    	var filterColNames = [];
    	for(var i = 0;i < enabledColNameIndex.length;i++){
    		filterColNames.push(colNames[enabledColNameIndex[i]]);
    	}
    	var fieldsParam = fields.join();
    	var colNameParam = filterColNames.join();
    	//装载列表头参数，列表字段参数
    	var params = {};
    	params.colNameParam = colNameParam;
    	params.fieldsParam = fieldsParam;
    	if(ids.length != 0){
    		idsParam = ids.join();
    		params.ids = idsParam;
    	}
    	//装载当前的jqgrid条件参数
    	params = $.extend(params, postData);
    	//装载hqlTemplaate和文件名称参数
    	if(!fileName){
    		fileName = caption + ".xls";
    	}
    	params.fileName = fileName;
    	params.sqlTemplate = sqlTemplate;
    	//装载格外的参数
    	params = $.extend(params, extraParams);
    	//构造form表单，进行post提交
    	if($("#common_exportForm").get(0)){
    		$("#common_exportForm").remove();
    	}
    	var form = $(document.createElement('form'))
    		.attr("id", "common_exportForm").attr('action', exportUrl)
    			.attr('method','post').css("display", "none");
        $('body').append(form);
        for(var attr in params){
        	var key = attr, value = params[attr];
        	if(value != ""){
    		 	$(document.createElement('input')).attr('type', 'hidden').attr('name', key).attr('value', value).appendTo(form);
    		}
        }
        $(form).submit();
    }

后台controller：

/**
     * 导出controller
     * @author wbw
     *
     */
    @Controller
    @RequestMapping("/export")
    public class ExportController {
    
    	@Autowired
    	private ExportService exportService;
    	/**
    	 * 导出excel文件
    	 * @param queryParams 表格查询参数
    	 * @param fileName 文件名称
    	 * @param sqlTemplate 查询sql
    	 * @param colNameParam 表格列名
    	 * @param fieldsParam  字段
    	 * @param ids ID集合
    	 * @param prefix 前缀
    	 * @param isMeger 是否合并标识
    	 * @return
    	 * @throws Exception
    	 */
    	@RequestMapping(value = "/jqgrid", method = RequestMethod.POST)
    	public @ResponseBody ResponseEntity<byte[]> exportExcel(QueryParams queryParams, String fileName, String sqlTemplate, 
    				String colNameParam, String fieldsParam, String ids, String prefix,boolean flag) throws Exception{
    		PageBean pageBean = JqGridUtil.getPageBean(queryParams);
    		pageBean.setPropPrefix(prefix);
    		ExportParam exportParam =ExportParam.buildExportParam(sqlTemplate, fileName, ids,colNameParam, fieldsParam, pageBean, prefix,flag);
    		return exportService.exportListExcel(exportParam);
    	}
    }

导出参数类,用于封装jqgrid对excel的导出参数封装：

**
     * 
     * 导出参数类，用于封装jqgrid对excel的导出参数封装
     * 
     * @author wbw
     *
     */
    public class ExportParam {
    
    	// 导出文件名称
    	private String exportFileName;
    
    	// 导出的语句模板
    	private String hqlTemplate;
    
    	// 导出的查询条件
    	private List<Condition> conditions;
    
    	// 导出的排序条件
    	private List<Order> orders;
    
    	// 导出的列表头
    	private String colNameParam;
    
    	// 用于sql查询的字段信息
    	private String fieldsParam;
    	
    	// 用于计算的字段信息
    	private String computefieldsParam;
    
    	// 导出的数据id集合
    	private String ids;
    	
    	//字段前缀
    	private String prefix;
    	
        //是否合并标识
    	private boolean  flag;

ExportService服务接口：

/**
     * 导出excel业务接口
     * @author wbw
     *
     */
    public interface ExportService {
        /**
         *	导出excel
         * @param exportParam 导出参数对象
         * @return ResponseEntity<byte[]>
         */
    	public ResponseEntity<byte[]> exportListExcel(ExportParam exportParam);
    }

ExportService实现接口：

/**
     * 导出excel业务实现接口
     * @author wbw
     *
     */
    @Service
    public class ExportServiceImpl implements ExportService {
    
    	@Autowired
    	private IGeneralDao generalDao;
    	
    	@PersistenceContext(unitName = "platform")
    	private EntityManager em;
    	
    	@Override
    	public ResponseEntity<byte[]> exportListExcel(ExportParam exportParam) {
    		if(exportParam.isFlag()==true){//条件检索
    			return  exportCompositeObject(exportParam);
    		}else{//单列表
    			return  exportSingleObject(exportParam);
    		}
    		
    	}
    	/**
    	 * 导出复合对象的excel
    	 * @param exportParam excel参数封装对象
    	 * @return
    	 */
    	@SuppressWarnings("unchecked")
    	private ResponseEntity<byte[]>  exportCompositeObject(ExportParam exportParam){
    		//获取excel表头
    		String[] cols = new String[0];
    		if(!StringUtils.isEmpty(exportParam.getColNameParam())){
    			cols = exportParam.getColNameParam().split(",");
    			
    		}
    		List<Object> resultList = null;
    		String convertSqlTemplate  = null;
    		if(!StringUtils.isEmpty(exportParam.getHqlTemplate())){
    			//去除baseinfo.zfid字段
    			if(exportParam.getHqlTemplate().contains(ExportUtils.REMOVE_FIELD)){
    			   convertSqlTemplate = exportParam.getHqlTemplate().replace(ExportUtils.REMOVE_FIELD, "");
    			}else{
    				convertSqlTemplate = exportParam.getHqlTemplate();
    			}
    			//根据sql从数据库查询要导出的数据 
    			resultList =  em.createNativeQuery(convertSqlTemplate).getResultList();
    		}else{
    			throw new PlatformException("导出的sqlTemplate为null!");
    		}
    		byte[] bytes = null;
    		//创建带资源的try-catch
    		try(Workbook wb = new HSSFWorkbook();ByteArrayOutputStream bos = new ByteArrayOutputStream();){
    			String [] sheetTitle = exportParam.getExportFileName().split("\\.");
    			Sheet sheet = wb.createSheet(sheetTitle[0]);
    			//设置表头单元格样式
    			CellStyle colTopCellStyle = ExportUtils.initTopCellStyle(wb);
    			//设置列头单元格的值
    			ExportUtils.initTopCell(sheet, colTopCellStyle, cols);
    			//单元格样式
    			CellStyle cellType = ExportUtils.initCellStyle(wb);
    			//填充excel数据
    			ExportUtils.initGridData(resultList,sheet,cellType);
    			//合并单元格
    			ExportUtils.mergeColumns(sheet,SpecFieldUtils.getFieldsBySession());
    			wb.write(bos);
    			bytes = bos.toByteArray();
    		} catch (IOException e) {
    			e.printStackTrace();
    			LoggerUtils.info("填充excel数据异常");
    			throw new RuntimeException(e);
    		}
    		return ExportUtil.getResponseEntityByFile(bytes, exportParam.getExportFileName());
    		
    	}
        /**
         * 导出单列表Excel
         * @param exportParam excel参数封装对象
         * @return
         */
    	private ResponseEntity<byte[]>  exportSingleObject(ExportParam exportParam){
    		String[] computeFields = new String[0];
    		String[] cols = new String[0];
    		if(!StringUtils.isEmpty(exportParam.getComputefieldsParam())){
    			computeFields = exportParam.getComputefieldsParam().split(",");
    		}
    		if(!StringUtils.isEmpty(exportParam.getColNameParam())){
    			cols = exportParam.getColNameParam().split(",");
    		}
    		String ids = exportParam.getIds();
    		List<Condition> conditions = exportParam.getConditions();
    		List<Order> orders = exportParam.getOrders();
    		//如果jqgrid传递了数据id集合，则需要对条件进行过滤（应用场景通常为用户勾选了列表的checkbox后进行导出）
     		if(!StringUtils.isEmpty(ids)){
    			String[] idsArray = ids.split(",");
    			Condition condition = new Condition();
    			condition.setOperation(Operation.IN);
    			condition.setPropertyName(exportParam.getPrefix() + "id");
    			condition.setRelateType(RelateType.AND);
    			condition.setPropertyValue(idsArray);
    			conditions.add(condition);
    		}
    		String sql = exportParam.getHqlTemplate().replaceAll("\\{fields\\}", exportParam.getFieldsParam());
    		//根据条件从数据库查询要导出的数据
    		List<?> resultList = generalDao.doList(sql, conditions, orders, true);
    		byte[] bytes = null;
    		try(Workbook wb = new HSSFWorkbook();ByteArrayOutputStream bos = new ByteArrayOutputStream();){
    			Sheet sheet = wb.createSheet(exportParam.getExportFileName());
    			//填充excel表头
    			CellStyle colTopCellStyle = wb.createCellStyle();
    			colTopCellStyle.setFillForegroundColor(IndexedColors.YELLOW.getIndex());
    			colTopCellStyle.setFillPattern(CellStyle.SOLID_FOREGROUND);
    			colTopCellStyle.setBorderBottom(HSSFCellStyle.BORDER_MEDIUM);
    			colTopCellStyle.setBorderTop(HSSFCellStyle.BORDER_MEDIUM);
    			colTopCellStyle.setBorderRight(HSSFCellStyle.BORDER_MEDIUM);
    			colTopCellStyle.setBorderLeft(HSSFCellStyle.BORDER_MEDIUM);
    			Row colRow = sheet.createRow(0);
    			for (int i = 0; i < cols.length; i++) {
    				sheet.setColumnWidth(i, 6000);
    				String colName = cols[i];
    				Cell cell = colRow.createCell(i);
    				cell.setCellValue(colName);
    				cell.setCellStyle(colTopCellStyle);
    			}
    			//填充excel数据
    			for (int i = 0; i < resultList.size(); i++) {
    				Object obj = resultList.get(i);
    				Row row = sheet.createRow(i + 1);
    				Class<?> clazz = obj.getClass();
    				for (int j = 0; j < computeFields.length; j++) {
    					sheet.setColumnWidth(j, 6000);
    					String field = computeFields[j];
    					String methodName = "get" + String.valueOf(field.charAt(0)).toUpperCase() + field.substring(1, field.length());
    					Cell cell = row.createCell(j);
    					Method classMehtod = clazz.getMethod(methodName);
    					if(classMehtod != null){
    						classMehtod.setAccessible(true);
    						if(classMehtod.invoke(obj) instanceof Date){
    							String dateValue = 
    									DateUtil.dateToStr(((Date)classMehtod.invoke(obj)), "yyyy-MM-dd HH:mm:ss");
    							cell.setCellValue(dateValue);
    						}else{
    							if(classMehtod.invoke(obj) != null){
    								cell.setCellValue(classMehtod.invoke(obj).toString());
    							}
    						}
    					}
    				}
    			}
    			wb.write(bos);
    			bytes = bos.toByteArray();
    		} catch (Exception e) {
    			e.printStackTrace();
    			LoggerUtils.info("填充excel数据异常");
    			throw new RuntimeException(e);
    		}
    		return ExportUtil.getResponseEntityByFile(bytes, exportParam.getExportFileName());
    	}

ExportUtils工具类：

/**
         * 默认时间格式
         */
    	private static final String DEFAULT_DATE_FORMAT = "yyyy-MM-dd HH:mm:ss";
    	/**
    	 * 剔除字段
    	 */
    	public static final String REMOVE_FIELD = ",baseinfo.zfid";
    	/**
    	 * 设置头部单元格样式
    	 * 
    	 * @param wb 工作簿
    	 */
    	public static CellStyle initTopCellStyle(Workbook wb) {
    		CellStyle cellStyle = wb.createCellStyle();
    		cellStyle.setFillForegroundColor(IndexedColors.YELLOW.getIndex());
    		cellStyle.setFillPattern(CellStyle.SOLID_FOREGROUND);
    		cellStyle.setBorderBottom(HSSFCellStyle.BORDER_MEDIUM);
    		cellStyle.setBorderTop(HSSFCellStyle.BORDER_MEDIUM);
    		cellStyle.setBorderRight(HSSFCellStyle.BORDER_MEDIUM);
    		cellStyle.setBorderLeft(HSSFCellStyle.BORDER_MEDIUM);
    		cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 居中 
    		return cellStyle;
    	}
    	/**
    	 * 设置单元格样式
    	 * 
    	 * @param wb 工作簿
    	 */
    	public static CellStyle initCellStyle(Workbook wb) {
    		CellStyle cellStyle = wb.createCellStyle();
    		cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 居中 
    		return cellStyle;
    	}
    	/**
    	 * 设置列表头单元格
    	 * @param sheet 
    	 * @param colTopCellStyle
    	 * @param cols
    	 */
    	public static void initTopCell(Sheet sheet,CellStyle colTopCellStyle,String[] cols){
    		Row colRow = sheet.createRow(0);
    		for (int i = 0; i < cols.length; i++) {
    			sheet.setColumnWidth(i, 6000);
    			String colName = cols[i];
    			Cell cell = colRow.createCell(i);
    			cell.setCellValue(colName);
    			cell.setCellStyle(colTopCellStyle);
    		}
    	}
    	/**
    	 * 设置单元格值
    	 * 
    	 * @param cell
    	 *            单元格
    	 * @param value
    	 *            单元格值
    	 */
    	public static void initCellValue(Cell cell, Object value) {
    		if (cell != null) {
    			if (value == null) {
    				cell.setCellType(Cell.CELL_TYPE_BLANK);
    			} else {
    				if (value instanceof Calendar) {
    					cell.setCellValue((Calendar) value);
    				}else if(value instanceof Integer){
    					  cell.setCellValue((Integer)value);
    				} else if (value instanceof Date) {
    					String dateValue = DateUtil.dateToStr(((Date) value),
    							DEFAULT_DATE_FORMAT);
    					cell.setCellValue(dateValue);
    				} else if (value instanceof String) {
    					cell.setCellValue((String) value);
    				} else {
    					cell.setCellValue(value.toString());
    				}
    			}
    		} else {
    			return;
    		}
    
    	}
    	
    	/**
    	 * 填充excel数据
    	 * @param resultList 数据集
    	 * @param sheet Sheet
    	 * @param  cellType 单元格样式
    	 */
    	public static void initGridData(List<Object> resultList,Sheet sheet,CellStyle cellType){
    		for (int rowindex = 0; rowindex < resultList.size(); rowindex++) {
    			Object[] objectRow = (Object[]) resultList.get(rowindex);
    			Row row = sheet.createRow(rowindex+1);//从第二行开始
    			for (int i = 0; i < objectRow.length; i++) {
    				Object cellValue  =  objectRow[i];
    					Cell cell = row.createCell(i);
    					//设置单元格值
    					initCellValue(cell, cellValue);
    					//设置单元格样式
    					cell.setCellStyle(cellType);
    					
    			}
    		}
    	}
    
    	/**
    	 * 合并列单元格
    	 * 
    	 * @param sheet
    	 */
    	public static void mergeColumns(Sheet sheet, LinkedHashMap<Integer,FieldInfo> map) {
    		// 行数
    		int rowsCount = sheet.getPhysicalNumberOfRows();
    		//如果查询出来的数据集不为null的情况下才做合并操作
    		if(!StringUtils.isEmpty(sheet.getRow(1))){
    		// 列数
    		int colsCount = sheet.getRow(1).getPhysicalNumberOfCells();
    		Row cunrrentRow = null;// 记录当前行
    		Cell currentCell = null;// 记录当前单元格
    		String lastZfbh = "";//记录上一个罪犯编号
    		Row lastRow = null;//记录上一行
    		// 循环所有的行数除去第一行表头
    		for (int r = 1; r < rowsCount; r++) {
    			cunrrentRow = sheet.getRow(r);
    			String currentZfbh = cunrrentRow.getCell(2).toString();
    			// 循环所有的列数
    			for (int c = 0; c < colsCount; c++) {
    				currentCell = cunrrentRow.getCell(c);
    				if (!currentZfbh.equals(lastZfbh)) {// 与上一行相比，如果与上一行的罪犯编号不一致，则把当前行赋予给上一行的变量
    					lastZfbh = currentZfbh;
    					lastRow = cunrrentRow;
    					break;
    				} else {// 如果当前行的罪犯编号与上一行的罪犯编号一致
    					if (lastRow != null) {// 如果上一行不等于空
    						Cell lastCell = lastRow.getCell(c);
    						//是否合并标识
    						boolean isMeger  = false;
    						if(map.size() > 0){
    							 isMeger = map.get(c).getIsMeger().equals("true");
    						}
    						// 三个条件
    						// 满足以下三个条件进行合并操作：
    						// 1.上一行罪犯编号与本行罪犯编号一致
    						// 2.上一行对应点列数据与本行对应的列数据一致
    						// 3.一定要标识为merge为true的字段才能合并
    						if (currentZfbh.equals(lastZfbh)
    								&& lastCell.getStringCellValue().equals(
    										currentCell.getStringCellValue())
    								&& isMeger) {
    							sheet.addMergedRegion(new CellRangeAddress(r - 1,
    									r, c, c));
    							//设置居中显示
    							
    						}
    					}
    				}
    
    			}
    		}
    		}
    	}
    	
    }

ExportUtil 导出bytes文件工具类：

public class ExportUtil {
    
    	private static Logger logger = Logger.getLogger(ExportUtil.class);
    	
    	private static final String CONTENT_DISPOSITION = "Content-Disposition";
    	
    	/**
    	 * 构建服务器传输给客户端的二进制件信息
    	 * @param fileName 文件名为空则显示当前时间
    	 * @param resource  word 文件的路径
    	 * @return
    	 * @throws Exception
    	 */
    	public static ResponseEntity<byte[]> getResponseEntityByFile(File file,String fileName) throws Exception {
    		return getResponseEntityByFile(new FileInputStream(file), fileName);
    	}
    	
    	
    	public static ResponseEntity<byte[]> getResponseEntityByFile(FileInputStream stream, String fileName) throws Exception {
            byte[] cbyte = IOUtils.toByteArray(stream);
            stream.close();
            return getResponseEntityByFile(cbyte, fileName);
    	}
    	
    	public static ResponseEntity<byte[]> getResponseEntityByFile(byte[] cbyte,String fileName) throws RuntimeException {
    		HttpHeaders responseHeaders = new HttpHeaders();
        	responseHeaders.setContentType(MediaType.APPLICATION_OCTET_STREAM);
        	responseHeaders.setContentLength(cbyte.length);
        	try {
    			responseHeaders.set(CONTENT_DISPOSITION, "attachment;filename=\""+URLEncoder.encode(fileName,"UTF-8")+"\"");
    		} catch (UnsupportedEncodingException e) {
    			e.printStackTrace();
    			logger.info("导出文件名称URL编码异常");
    			throw new RuntimeException(e);
    		}
    		return new ResponseEntity<byte[]>(cbyte, responseHeaders, HttpStatus.OK);
    	}
    	
    }