使用poi导出excel根据指定列自动合并行

最新推荐文章于 2025-07-05 14:56:37 发布

原创最新推荐文章于 2025-07-05 14:56:37 发布 · 5.9w 阅读

CC 4.0 BY-SA版权

本文介绍如何使用Java的POI库来实现Excel导出时，根据指定列进行自动行合并。内容包括：1. 效果图展示；2. 分别展示了JavaScript代码和Java后台代码，其中控制器部分处理了中文文件名乱码问题，使用了特定编码转换；3. 服务层和导出工具类的详细代码，特别提示了合并单元格的API来自`org.apache.poi.ss.util.CellRangeAddress`包，注意避免使用已废弃的`org.apache.poi.hssf.util.CellRangeAddress`。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

一、效果图

二、js代码

function assemble_dg_columns() {
	    var arr = new Array();
	    arr.push({"field": "ck", checkbox: true,"hidden": false});
	    arr.push({"field": "id", "title": "id", "width": "1%", "align": "center", "hidden": true});
	    arr.push({"field": "find_date", "title": "发现日期", "width": "9%", "align": "center","sortable": true});
	    arr.push({"field": "question_type", "title": "问题类别", "width": "7%", "align": "center"});
	    arr.push({"field": "question_property", "title": "问题属性", "width": "9%", "align": "center"});
	    arr.push({"field": "content", "title": "问题描述", "width": "18%", "align": "center"});
	    arr.push({"field": "question_evalstandard", "title": "评价标准", "width": "15%", "align": "center"});
	    arr.push({"field": "name_org", "title": "单位", "width": "6%", "align": "center"});
	    arr.push({"field": "postduty_name", "title": "岗位", "width": "6%", "align": "center"});
	    arr.push({"field": "soleduty_type_label", "title": "专职", "width": "6%", "align": "center"});
	    arr.push({"field": "username", "title": "被考核人员", "width": "7%", "align": "center"});
	    arr.push({"field": "performance", "title": "扣分值", "width": "6%", "align": "center","sortable": true,"formatter": performance});
	    arr.push({"field": "createname", "title": "考核组成员", "width": "7%", "align": "center"});
	    return arr;
	}
$("body").on("click", "#btn_print", function () {
        	assessEvaluationSummaryPrint();
        });
function assessEvaluationSummaryPrint() {
    	var arr_columns = assemble_dg_columns();
    	var key_columns = [];
    	var value_columns = [];
        for (var i = 0; i < arr_columns.length; i++) {
            if(NOTNULL(arr_columns[i].title)&&arr_columns[i].title!="id"){
            	key_columns.push(arr_columns[i].field);
                value_columns.push(arr_columns[i].title);
            }
        }
    	window.location.href= "jee/VAssessEvaluationSummaryC/exportExecl?key_columns="+key_columns+"&value_columns="+value_columns;
    }

三、后台代码

（1）controller

@RequestMapping(value="jee/VAssessEvaluationSummaryC")
@Controller
public class VAssessEvaluationSummaryController extends BaseEntityController<VAssessEvaluation>{

	public VAssessEvaluationSummaryController(){
		super(VAssessEvaluation.class);
	}
	
	@Resource
	private VAssessEvaluationSummaryService vAssessEvaluationSummaryService;

	
	@ResponseBody
	@SystemControllerLog(description="考核评价汇总--导出功能")
	@RequestMapping(value="exportExecl")
	public ReturnDatas exportExecl(String[] key_columns,String[] value_columns,HttpServletResponse response){
		ReturnDatas returnDatas = ReturnDatas.getSuccessReturnDatas();
		try {
			response.setCharacterEncoding("UTF-8");
            response.setContentType("application/msexcle");   
            response.setHeader("content-disposition", "attachment;filename="+new String("考核评价".getBytes("gb2312"),"ISO8859-1")+".xlsx");
            HSSFWorkbook workbook = vAssessEvaluationSummaryService.exportExecl(key_columns,value_columns);
            //写入Excel文件  
            OutputStream outputStream =  response.getOutputStream();  
            workbook.write(outputStream);  
            outputStream.close();  
			returnDatas.setStatus(ReturnDatas.SUCCESS);
			return returnDatas;
		} catch (Exception e) {
			e.printStackTrace();
			LogUtil.error("考核评价汇总--导出功能异常："+e.getMessage(),e);
			returnDatas.setStatus(ReturnDatas.ERROR);
			returnDatas.setMessage("考核评价汇总--导出功能异常。");
		}
		return returnDatas;
	}
}

注：excel文件中文名乱码使用"+new String("考核评价".getBytes("gb2312"),"ISO8859-1")+".xlsx"解决

（2）service

@Service("vAssessEvaluationSummaryService")
public class VAssessEvaluationSummaryServiceImpl implements VAssessEvaluationSummaryService {
	private List<VAssessEvaluation> list;
	@Resource
	private IbaseInfoService baseInfoService;
	
	/**
	 * 
	 * @Description 考核评价汇总--查询操作 
	 * @param rows
	 * @param page
	 * @param sort
	 * @param order
	 * @param key
	 * @param start_time
	 * @param end_time
	 * @param id_postduty
	 * @param question_type
	 * @return PageResult
	 * @throws Exception 
	 *
	 */
	@Override
	public PageResult listAllVassessEvaluationSummary(int rows,int page,String sort, String order,String key,String start_time, String end_time,
			String id_postduty,String question_type) throws Exception {
		if(StringUtils.isBlank(key)) {
			key = "";
		}
		List<Object> ls_param = new ArrayList<Object>();
		String hql = "select u from VAssessEvaluation u where (del_flag is null or del_flag != '1')";
		String hqlCount = "select count(u) from VAssessEvaluation u where (del_flag is null or del_flag != '1')";
		if(StringUtils.isNotBlank(key)) {
			hql+= " and ( instr(type,?) > 0 or instr(updateuser,?)>0 or instr(updatetime,?)>0 )";
			hqlCount += " and ( instr(type,?) > 0 or instr(updateuser,?)>0 or instr(updatetime,?)>0 )";
			ls_param.add(key);
			ls_param.add(key);
			ls_param.add(key);
		}
		if(StringUtils.isNotBlank(start_time)) {
			hql += " and find_date >= ?";
			hqlCount += " and find_date >= ?";
			ls_param.add(start_time);
		}
		if(StringUtils.isNotBlank(end_time)) { 
			hql += " and find_date <= ?";
			hqlCount += " and find_date <= ?";
			ls_param.add(end_time);
		}
		if(StringUtils.isNotBlank(question_type)) { 
			hql += " and id_question_type = ?";
			hqlCount += " and id_question_type = ?";
			ls_param.add(question_type);
		}
		if(StringUtils.isNotBlank(id_postduty)) { 
			hql += " and id_postduty = ?";
			hqlCount += " and id_postduty = ?";
			ls_param.add(id_postduty);
		}
		if(StringUtils.isNotBlank(sort)&&StringUtils.isNotBlank(order)) {
			hql += " order by " + sort + " " + order;
		}
		list = baseInfoService.listQueryByHql(hql,ls_param,rows,page);
		Object total = baseInfoService.getObjectByHql(hqlCount,ls_param);
		PageResult result = new PageResult();
		if (list != null) {
			result.setRows(list);
			if (total != null) {
				result.setTotal(Integer.parseInt(total.toString()));
			} else {
				result.setTotal(0);
			}
		}
		return result;
	}
	
	/**
	 * 
	 * @Description 考核评价汇总--导出功能 
	 * @param key_columns
	 * @param value_columns
	 * @return HSSFWorkbook
	 * @throws Exception 
	 *
	 */
	@Override
	public HSSFWorkbook exportExecl(String[] key_columns, String[] value_columns) throws Exception {
		List<List<String>> list1 = new ArrayList<>();
		for (VAssessEvaluation v : list) {
			List<String> list2 = new ArrayList<>();
			for (int i = 0; i < key_columns.length; i++) {
				String field = ExportUtil.getFiled(v, key_columns[i]);
				if("performance".equals(key_columns[i])){
					if(v.getPerformance()!=0) {
						list2.add("-"+field);
					}else {
						list2.add(field);
					}
				}else {
					list2.add(field);
				}
			}
			list1.add(list2);
		}
		int[] arr = {1,2,3}; //要合并的列，从0开始
		int judge = 10; //判断字段，没有则为-1
		return ExportUtil.exportAutoCell("考核评价汇总",key_columns,value_columns,list1,arr,judge);
	}
	
}

（3）导出工具类

public class ExportUtil {
    
    /**
     * 
     * @Date 2018年6月19日 下午3:46:27
     * @Description 导出自动合并单元格 
     * @Fcunction exportAutoCell
     * @param sheetName
     * @param key_columns
     * @param value_columns
     * @param list
     * @param field_arr
     * @param judge
     * @return HSSFWorkbook
     *
     */
    public static HSSFWorkbook exportAutoCell(String sheetName,String[] key_columns,String[] value_columns,List<List<String>> list,int[] field_arr,int judge){
        //创建一个Excel文件
        HSSFWorkbook workbook = new HSSFWorkbook();
        //创建一个工作表
        HSSFSheet sheet = workbook.createSheet(sheetName);
        //添加表头行
        HSSFRow hssfRow = sheet.createRow(0);
        //设置单元格格式
        HSSFCellStyle cellStyle = workbook.createCellStyle();
        //设置单元格边框
        cellStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
        cellStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);
        cellStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
        cellStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
        cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); //水平居中
        cellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); //垂直居中
        for (int i = 0; i < value_columns.length; i++) {
        	//添加表头内容
            HSSFCell headCell = hssfRow.createCell(i);
            headCell.setCellValue(value_columns[i]);
            headCell.setCellStyle(cellStyle);
		}
        //把数据添加到excel
        for (int i = 0; i < list.size(); i++) { //每一行
        	hssfRow = sheet.createRow(i + 1);
        	for (int j = 0; j < list.get(i).size(); j++) { //每一行的每一格
        		//创建单元格，并设置值
        		HSSFCell cell = hssfRow.createCell(j);
        		cell.setCellValue(list.get(i).get(j));
        		cell.setCellStyle(cellStyle);
				
			}
        }
        
        //自动合并
    	for (int k = 0; k < field_arr.length; k++) {
    		int field = field_arr[k]; //要排序的字段
    		int d = 0; //下次要合并的行
            int n = list.size()-1; //当前合并的行
    		for (int i = list.size()-1; i >= 1; i--) { //每一行
    			if(i!=(n-d)) {
    				continue;
    			}
    			if(list.get(i).get(field).equals(list.get(i-1).get(field))){ //相邻的上下两行
    				if(judge!=-1) {
						if(!list.get(i).get(judge).equals(list.get(i-1).get(judge))){
							break;
						}
					}
    				int rowspan = 2;
    				d = rowspan;
    				for (int j = 2; i-j >= 0; j++) { //判断上下多行内容一样
    					n = i;
    					if(!list.get(i).get(field).equals(list.get(i-j).get(field))){
    						break;
    					}else{
    						if(judge!=-1) {
    							if(!list.get(i).get(judge).equals(list.get(i-j).get(judge))){
    								break;
    							}
    						}
    						rowspan = j+1;
    						d = rowspan;
    					}
    				}
    				sheet.addMergedRegion(new CellRangeAddress(i-rowspan+2, i+1, field, field)); 
    			}else {
    				d = 0;
    				n = i-1;
    			}
    			
    		}
    	}
        return workbook;
    }

    /**
     * 
     * @Author zhangshizhe
     * @Date 2018年6月15日 下午1:53:52
     * @Description 利用反射获取get方法 
     * @Fcunction getFiled
     * @param object
     * @param field
     * @return String
     *
     */
    public static String getFiled(Object object, String field) {
        Class<? extends Object> clazz  = object.getClass();
        PropertyDescriptor pd = null;
        Method getMethod = null;
        try {
            pd = new PropertyDescriptor(field, clazz);
            if (null != pd) {
                // 获取  这个 field 属性 的get方法
                getMethod = pd.getReadMethod();
                Object invoke = getMethod.invoke(object);
                if(invoke==null) {
                	invoke = "";
                }
                return invoke.toString();
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        return "";
    }
    
}

注：合并导org.apache.poi.ss.util.CellRangeAddress包，org.apache.poi.hssf.util.CellRangeAddress包已废弃；

import org.apache.poi.ss.util.CellRangeAddress;

合并addMergedRegion方法四个属性分别是（开始行，结束行，开始列，结束列）

sheet.addMergedRegion(new CellRangeAddress(0, 1, 0, 1));