Java导出Excel表合并行、合并列

75 阅读 0 评论 50 点赞

我是靠谱客的博主丰富高跟鞋，最近开发中收集的这篇文章主要介绍Java导出Excel表合并行、合并列，觉得挺不错的，现在分享给大家，希望可以做个参考。

概述

Controller层代码

@RequestMapping(value = "/productExportExcel", method = RequestMethod.GET)
	public void productExportExcel(HttpServletRequest request,HttpServletResponse response) throws Exception {
		Record rd = this.getCurrentUser();
		try {
			List<Integer> columnWidthList = new ArrayList<Integer>();//列宽 用list比较方便在某处插入新字段和数数
			columnWidthList.add(20);columnWidthList.add(20);columnWidthList.add(20);
			columnWidthList.add(20);columnWidthList.add(20);columnWidthList.add(20);
			columnWidthList.add(15);
			String[] title = {"一级产品线","详细描述","二级产品线","详细描述","三级产品线","详细描述"};
			String[] key = {"NAME","DETAIL","Z_NAME","Z_DETAIL","ZZ_NAME","ZZ_DETAIL"};
			String fileName = "产品线信息表";
			exportExcel.productExport(request, response, columnWidthList, title, key, fileName);
			logService.showLog(fileName, "4",rd,request);
		} catch (Exception e) {
			// TODO: handle exception
			logger.error(e.getMessage());
			String fileName = "产品线信息表";
			logService.showLog(fileName, "5",rd,request);
		}
	}

Service层代码

public void productExport(HttpServletRequest request,HttpServletResponse response,List<Integer> columnWidthList,String[] title,String[] key,String fileName) throws Exception{
		/*
		 * 1.设置样式
		 */
		HSSFWorkbook workbook = new HSSFWorkbook(); 
		HSSFFont titleFont = workbook.createFont(); //属性字体
		HSSFFont valueFont = workbook.createFont(); //值字体
		HSSFCellStyle titleStyle = workbook.createCellStyle();//属性格式
		HSSFCellStyle valueStyle = workbook.createCellStyle();//值格式
		setStyle(titleFont,valueFont,titleStyle,valueStyle);
		
    	HSSFSheet sheet = workbook.createSheet(fileName);
    	for (int i = 0; i < columnWidthList.size(); i++) {
    		sheet.setColumnWidth(i,(int) ((columnWidthList.get(i)+0.72)*256));
    	}
    	/*
    	 * 2.处理查询条件,获取导出数据 
    	 */
    	List<Object> param = new ArrayList<Object>();
		StringBuffer from = new StringBuffer(" FROM pro_product a left join pro_product b on a.code=b.PARENT_CODE and b.STATE='1' LEFT JOIN pro_product c on b.CODE=c.PARENT_CODE and c.STATE='1' ");
		StringBuffer where = new StringBuffer(" where a.PARENT_CODE='' and a.state='1' ");
		String sql = "SELECT a.CODE,a.NAME,a.DETAIL,a.PARENT_CODE,b.CODE Z_CODE,b.NAME Z_NAME,b.DETAIL Z_DETAIL,b.PARENT_CODE Z_PARNET_CODE,c.CODE ZZ_CODE,c.NAME ZZ_NAME,c.DETAIL ZZ_DETAIL,c.PARENT_CODE ZZ_PARENT_CODE " + from+where.toString()+" ORDER BY a.sort,b.sort,c.sort ASC";//便于打印 
		List<Map<String, Object>> list = registerService.findSQL(sql, param.toArray());
		
    	List<List<Integer>> rowList = new ArrayList<List<Integer>>();//合并行
    	String[] rowKeys = {"NAME","DETAIL","Z_NAME","Z_DETAIL","",""};//需要合并列名，与key对应，不需要合并的列对应的数组位置列名为空字符串即可跳过
    	String[] rowValue = {"NAME","DETAIL","Z_NAME","Z_DETAIL","",""};//
    	for (int i = 0; i < rowKeys.length; i++) {
    		rowList.add(new ArrayList<Integer>());
    	}
		/*
		 * 3.填充数据
		 */
    	if(list.size()>0){
    		//列名
    		HSSFRow rowTitle = sheet.createRow(0);
    		HSSFCell[] cellTitle = new HSSFCell[title.length];
    		for (int i = 0; i < cellTitle.length; i++) {
    			cellTitle[i] = rowTitle.createCell(i);
    			cellTitle[i].setCellStyle(titleStyle);
    			cellTitle[i].setCellValue(new HSSFRichTextString(title[i]));
			}
    		HSSFRow[] row = new HSSFRow[list.size()];
    		HSSFCell[][] cell = new HSSFCell[title.length][list.size()];
    		Map<Object,String> typeMap = new HashMap<Object, String>();
    		for (int i = 0; i < list.size(); i++) {
    			/*System.out.println((i+1)+"/"+list.size());*/
    			//行
    			row[i] = sheet.createRow(i+1);
    			row[i].setHeight((short) ((30+0.72)*20));
    			Map<String, Object> content = list.get(i);
    			//判断合并内容
    			for (int j = 0; j < rowKeys.length; j++) {
    				if(!"".equals(rowKeys[j])){
    					if(null==content.get(rowKeys[j])){
    						rowList.get(j).add(i);
    						rowValue[j] = "";
    					}else if(!rowValue[j].equals(content.get(rowKeys[j]).toString())){
    						rowList.get(j).add(i);
    						rowValue[j] = content.get(rowKeys[j]).toString();
    					}
    				}
    			}
    			//遍历导出数据
    			for (int j = 0; j < title.length; j++) {
    				//列
					cell[j][i] = row[i].createCell(j);
					cell[j][i].setCellStyle(valueStyle);
					
					/*String contentValue = "";
					//特殊数据处理
					cell[j][i].setCellValue(contentValue);*/
					String contentValue = "";
					//特殊数据处理
					if(null!=content.get((key[j]))){
					  if(key[j].equals("DETAIL")||key[j].equals("Z_DETAIL")||key[j].equals("ZZ_DETAIL")){
							contentValue = content.get(key[j]).toString().replaceAll("&lt;br&gt;",String.valueOf((char)10));
						}else{
							contentValue = content.get(key[j]).toString();
						}
					}
					cell[j][i].setCellValue(contentValue);
				}
			}
    		
    		//合并单元格
    		for (int j = 0; j < rowList.size(); j++) {
				List<Integer> rlist = rowList.get(j);
				if(rlist.size()>0){
					if(rlist.get(rlist.size()-1)!=list.size()) rlist.add(list.size());
					for (int i = 0; i < rlist.size()-1; i++) {
						sheet.addMergedRegion(new CellRangeAddress(rlist.get(i)+1, rlist.get(i+1),j, j));
					}
				}
				
			}
    	}
    	fileName += ".xls";
    	response.setContentType("application/octet-stream");
    	response.setHeader(
    			"Content-Disposition",
    			"attachment;"
    					+ (new StringBuilder("filename=").append(encode(
    							request, fileName)).toString()));
    	response.addHeader("Content-Length", "");
    	
    	ServletOutputStream localServletOutputStream = response
    			.getOutputStream();
    	workbook.write(localServletOutputStream);
    	localServletOutputStream.flush();
    	localServletOutputStream.close();
	}

设置样式，设置列宽方法

public void setStyle(HSSFFont titleFont,HSSFFont valueFont,HSSFCellStyle titleStyle,HSSFCellStyle valueStyle){
		//属性字体
		titleFont.setFontHeightInPoints((short) 11);
		titleFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
		//值字体
		valueFont.setFontHeightInPoints((short) 10);
		//属性格式
		titleStyle.setFont(titleFont);
		titleStyle.setBorderBottom((short) 1);
		titleStyle.setBorderLeft((short) 1);
		titleStyle.setBorderRight((short) 1);
		titleStyle.setBorderTop((short) 1);
		titleStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
		titleStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
		//值格式
		valueStyle.setFont(valueFont);
		valueStyle.setWrapText(true);
		valueStyle.setBorderBottom((short) 1);
		valueStyle.setBorderLeft((short) 1);
		valueStyle.setBorderRight((short) 1);
		valueStyle.setBorderTop((short) 1);
		valueStyle.setBottomBorderColor(HSSFColor.BLACK.index);
		valueStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
		valueStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
	}

设置编码方法

public String encode(HttpServletRequest request, String fileName)
			throws UnsupportedEncodingException {
		Boolean isIE = false;
		String[] IEBrowserSignals = {"MSIE", "Trident", "Edge"};
        String userAgent = request.getHeader("User-Agent");
        userAgent = userAgent == null ? "" : userAgent;
        for (String signal : IEBrowserSignals) {
            if (userAgent.contains(signal)){  
            	isIE = true;
            }
        }
        if (isIE) {
        	fileName = java.net.URLEncoder.encode(fileName, "UTF-8");
        } else {
        	fileName = new String(fileName.getBytes("UTF-8"), "ISO-8859-1");
        }
        return fileName;
	}