使用poi导出excel合并表头

朱雀 2020-10-14 12:17 885阅读 0赞

一、js代码

$("body").on("click", "#btn_print", function () {
            	assessRankQueryPrint();
            });
    function assessRankQueryPrint(){
        	var value_columns1 = ["考核年度","被考核人员","单位","岗位","专职","第一季度","第一季度","第二季度","第二季度","第三季度","第三季度","第四季度","第四季度","备注"];
        	var key_columns2 = ["year_","name_user","name_org","name_postduty","label","info01","info02","info04","info05","info07","info08","info10","info11","remarks"];
        	var value_columns2 = ["考核年度","被考核人员","单位","岗位","专职","考核得分","本周期岗位排名","考核得分","本周期岗位排名","考核得分","本周期岗位排名","考核得分","本周期岗位排名","备注"];
        	var url = "jee/VAssessQuarterRankQueryC/exportExecl?key_columns2="+key_columns2+"&value_columns1="+value_columns1+"&value_columns2="+value_columns2;
        	window.location.href= url;
        }

二、后台代码

（1）controller

@RequestMapping(value="jee/VAssessQuarterRankQueryC")
    @Controller
    public class VAssessQuarterRankQueryController extends BaseEntityController<VAssessQuarterRankQuery>{
    
    	public VAssessQuarterRankQueryController(){
    		super(VAssessQuarterRankQuery.class);
    	}
    	
    	@Resource
    	private VAssessQuarterRankQueryService vAssessQuarterRankQueryService;
    
    	@ResponseBody
    	@SystemControllerLog(description="考核结果排名查询--导出功能")
    	@RequestMapping(value="exportExecl")
    	public ReturnDatas exportExecl(String[] key_columns2,String[] value_columns1,String[] value_columns2,HttpServletResponse response){
    		ReturnDatas returnDatas = ReturnDatas.getSuccessReturnDatas();
    		try {
    			response.setCharacterEncoding("UTF-8");
                response.setContentType("application/msexcle");   
                response.setHeader("content-disposition", "attachment;filename=test.xlsx");
                HSSFWorkbook workbook = vAssessQuarterRankQueryService.exportExecl(key_columns2,value_columns1,value_columns2);
                //写入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;
    	}
    }

（2）service

@Service("vAssessQuarterRankQueryService")
    public class VAssessQuarterRankQueryServiceImpl implements VAssessQuarterRankQueryService {
    	private List<VAssessQuarterRankQuery> list;
    	@Resource
    	private IbaseInfoService baseInfoService;
    
    	/**
    	 * 
    	 * @Description 考核结果排名查询--查询操作
    	 * @param rows
    	 * @param page
    	 * @param sort
    	 * @param order
    	 * @param key
    	 * @param year_
    	 * @param id_postduty
    	 * @param label
    	 * @return PageResult
    	 * @throws Exception 
    	 *
    	 */
    	@Override
    	public PageResult listAllVAssessQuarterRankQuery(int rows, int page, String sort, String order, String key, String year_, String id_postduty, String label) throws Exception {
    		List<Object> ls_param = new ArrayList<Object>();
    		String hql = "select u from VAssessQuarterRankQuery u where (del_flag is null or del_flag != '1')";
    		String hqlCount = "select count(*) from VAssessQuarterRankQuery u where (del_flag is null or del_flag != '1')";
    		if (StringUtils.isBlank(key)) {
    			key = "";
    		}
    		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(year_)) {
    			year_ = year_.substring(0,4);
    			hql += " and year_ = ?";
    			hqlCount += " and year_ = ?";
    			ls_param.add(year_);
    		}
    		if (StringUtils.isNotBlank(label)) {
    			hql += " and soleduty_type = ?";
    			hqlCount += " and soleduty_type = ?";
    			ls_param.add(label);
    		}
    		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_columns1,String[] value_columns1,String[] value_columns2) throws Exception {
    		List<List<String>> list1 = new ArrayList<>(); //所有单元格数据
    		List<String> list4 = new ArrayList<>(); //需要排名的字段
    		list4.add("info02");
    		list4.add("info05");
    		list4.add("info08");
    		list4.add("info11");
    		for (VAssessQuarterRankQuery v : list) {
    			List<String> list2 = new ArrayList<>(); //每一行数据
    			for (int i = 0; i < key_columns1.length; i++) {
    				String field = ExportUtil.getFiled(v, key_columns1[i]);
    				if(list4.contains(key_columns1[i])){
    					String filed = key_columns1[i].replace("info", "");
    					String total_person = "0";
    					if(filed.matches("\\d+")) {//判断能否转为数字
    						int f = Integer.parseInt(filed);
    						String info = "info";
    						if(f<10){
    			                info += 0;
    			            }
    						total_person = ExportUtil.getFiled(v, info+(f+1));
    					}
    					if(StringUtils.isBlank(field)||"0".equals(field)) {
    						list2.add("--/--");
    					}else if("0".equals(total_person)) {
    						list2.add("--/--");
    					}else {
    						list2.add(field+"/"+total_person);
    					}
    				}else {
    					list2.add(field);
    				}
    			}
    			list1.add(list2);
    		}
    		List<Integer[]> list3 = new ArrayList<>(); //要合并的行和列
    		Integer[] array1 = {0, 1, 0, 0};  
    		Integer[] array2 = {0, 1, 1, 1}; 
    		Integer[] array3 = {0, 1, 2, 2}; 
    		Integer[] array4 = {0, 1, 3, 3}; 
    		Integer[] array5 = {0, 1, 4, 4}; 
    		Integer[] array6 = {0, 0, 5, 6}; 
    		Integer[] array7 = {0, 0, 7, 8}; 
    		Integer[] array8 = {0, 0, 9, 10}; 
    		Integer[] array9 = {0, 0, 11, 12}; 
    		Integer[] array10 = {0, 1, 13, 13}; 
    		list3.add(array1);
    		list3.add(array2);
    		list3.add(array3);
    		list3.add(array4);
    		list3.add(array5);
    		list3.add(array6);
    		list3.add(array7);
    		list3.add(array8);
    		list3.add(array9);
    		list3.add(array10);
    		return ExportUtil.exportCell("季度考核成绩排名",key_columns1,value_columns1,value_columns2,list1,list3);
    	}
    }

（3）导出工具类

public class ExportUtil {
        
        public static HSSFWorkbook exportCell(String sheetName,String[] key_columns2,String[] value_columns1,String[] value_columns2,List<List<String>> list,List<Integer[]> list3){
            //创建一个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_columns1.length; i++) {
            	//添加表头内容
                HSSFCell headCell = hssfRow.createCell(i);
                headCell.setCellValue(value_columns1[i]);
                headCell.setCellStyle(cellStyle);
    		}
            hssfRow = sheet.createRow(1);
            for (int i = 0; i < value_columns2.length; i++) {
            	//添加表头内容
                HSSFCell headCell = hssfRow.createCell(i);
                headCell.setCellValue(value_columns2[i]);
                headCell.setCellStyle(cellStyle);
    		}
            for (Integer[] arr : list3) {//合并表头
    			sheet.addMergedRegion(new CellRangeAddress(arr[0], arr[1], arr[2], arr[3])); 
    		}
            //把数据添加到excel
            for (int i = 0; i < list.size(); i++) {
            	hssfRow = sheet.createRow(i + 2);
            	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);
    				
    			}
            }
            return workbook;
        }  
        
        /**
         * 
         * @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));