1. 首页
  2. 文章中心
  3. 后端

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

101 阅读 0 评论
我是靠谱客的博主 丰富高跟鞋，这篇文章主要介绍Java导出Excel表合并行、合并列，现在分享给大家，希望可以做个参考。

Controller层代码

复制代码
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
@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层代码

复制代码
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
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();
	}

设置样式，设置列宽方法

复制代码
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
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);
	}

设置编码方法

复制代码
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
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;
	}

前端代码就不发了，就一个导出按钮加一个url带参数，没其他的了

最后

以上就是丰富高跟鞋最近收集整理的关于Java导出Excel表合并行、合并列的全部内容，更多相关Java导出Excel表合并行、合并列内容请搜索靠谱客的其他文章。

本图文内容来源于网友提供，作为学习参考使用，或来自网络收集整理，版权属于原作者所有。
点赞(67)

相关文章

java代码通过poi组件合并excel文件
java代码通过poi组件合并excel文件
poi导出excel合并单元格
poi导出excel合并单元格
java导出word表格 行列合并生成word表格行列合并引入pom和使用util
java导出word表格 行列合并生成word表格行列合并引入pom和使用util
Java-合并Excel中同一列中相同内容（EasyExcelPlus）前言一、EasyExcelPlus是什么？二、如何使用-合并列中的相同的值三、总结
Java-合并Excel中同一列中相同内容（EasyExcelPlus）前言一、EasyExcelPlus是什么？二、如何使用-合并列中的相同的值三、总结
Java导出Excel表合并行、合并列
Java导出Excel表合并行、合并列
Java Excel文件导出POI合并单元格
Java Excel文件导出POI合并单元格
Java Excel 合并单元格 Java Excel 实现尾部添加数据 Java Excel 合并单元格 添加数据 poi excel 合并单元格
Java Excel 合并单元格 Java Excel 实现尾部添加数据 Java Excel 合并单元格 添加数据 poi excel 合并单元格
Java POI 导出 Excel 单元格 合并单元格 相邻的相同值 合并
Java POI 导出 Excel 单元格 合并单元格 相邻的相同值 合并

评论列表共有 0 条评论

立即
投稿
返回
顶部