项目中有个需求，根据计划单号进行对应的单元格合并，还有对应的导出功能也是需要，前台使用的是bootstrap插件，还好bootstrap有自己的合并功能。
下面看下具体的实现
一、前台bootstap实现动态合并单元格
1.在onLoadSuccess : function(data) {
}方法中进行实现合并单元格，注意，一定是其他操作完成之后再合并单元格，我的是有合计功能，刚开始是先合并再实现合计，结果合计功能一直出不来，很纳闷，试了一下更改顺序，结果就出来了，把整个的代码都贴一下吧
function loadTable(flag) {
if(flag=="1")
{
searchStr = $(".search__inp").val().trim();
}
var pageNumber = 1;
$("#mainTable").bootstrapTable("destroy");
var options = {
pagination : true
//, height: 400
,
showFooter : false,
paginationLoop : false
//,sortable: true
,
cache : false,
pageNumber : pageNumber,
pageSize : 10,
pageList : [ 10],//[ 10, 20, 50 ],
method : "get",
contentType : 'application/x-www-form-urlencoded; charset=UTF-8',
url : basePath + "student/productionReport/getReportList",
queryParamsType : "",
sidePagination : "server",
queryParams : queryParamsMain,
columns : [[
{
field : "id",
title : "序号",
align : "center",
valign : "middle",
colspan: 1,
rowspan: 2,
formatter : function(value, row, index) {
//获取每页显示的数量
var pageSize = $('#mainTable').bootstrapTable(
'getOptions').pageSize;
//获取当前是第几页
var pageNumber = $('#mainTable').bootstrapTable(
'getOptions').pageNumber;
//返回序号，注意index是从0开始的，所以要加上1
if (value == "total") {
return "合计";
} else {
return pageSize * (pageNumber - 1) + index + 1;
}
},
//width : 40
},
{
field : "planType",
title : "生产类型",
align : "center",
valign : "middle",
colspan: 1,
rowspan: 2,
formatter : function(value, row, index) {
var str="";
if(value==1)
{
str="内部生产";
}
else if(value==2)
{
str="委托加工";
}
return str;
},
//width : 68
},
{
field : "planCode",
title : "生产计划单号",
align : "center",
valign : "middle",
colspan: 1,
rowspan: 2,
formatter : function(value, row, index) {
return value;
},
//width : 58
},
{
//field : "code",
title : "生产信息",
align : "center",
valign : "middle",
colspan: 4,
rowspan: 1
//width : 145
},
{
//field : "code",
title : "用料明细",
align : "center",
valign : "middle",
colspan: 4,
rowspan: 1
//width : 145
},
{
field : "roundNo",
title : "轮次",
align : "center",
valign : "middle",
colspan: 1,
rowspan: 2,
//sortable: true,
formatter : function(value, row, index) {
if (value != "") {
return "第" + value + "轮";
} else {
return "";
}
},
//width : 46
}
],
[{
field : "productName",
title : "产品名称",
align : "center",
valign : "middle",
formatter : function(value, row, index) {
return value;
},
//width : 67
},
{
field : "productCode",
title : "型料号",
align : "center",
valign : "middle",
formatter : function(value, row, index) {
return value;
},
//width : 73
},
{
field : "actualQuantity",
title : "实际生产数量",
align : "center",
valign : "middle",
formatter : function(value, row, index) {
return value;
},
//width : 28
},
{
field : "qualificationRate",
title : "合格率（%）",
align : "center",
valign : "middle",
formatter : function(value, row, index) {
return value;
},
//width : 28
},
{
field : "bomType",
title : "产品类型",
align : "center",
valign : "middle",
formatter : function(value, row, index) {
switch(value)
{
case 1:value="产成品";break;
case 2:value="半成品";break;
case 3:value="原材料";break;
}
return value;
},
//width : 53
},
{
field : "bomProductName",
title : "产品名称",
align : "center",
valign : "middle",
formatter : function(value, row, index) {
return value;
},
//width : 86
},
{
field : "bomProductCode",
title : "型料号",
align : "center",
valign : "middle",
formatter : function(value, row, index) {
return value;
},
//width : 190
},
{
field : "bomQuantity",
title : "生产实际用量",
align : "center",
valign : "middle",
formatter : function(value, row, index) {
return value;
},
//width : 65
}
]],
onLoadSuccess : function(data) {
var sum_1 = 0;
var sum_2 = 0;
var planCode="";
for ( var o in data.rows) {
if (!isNaN(o)) {
if(data.rows[o].planCode!=planCode)
{
if(!isNaN(data.rows[o].actualQuantity))
{
sum_1 = parseFloat(sum_1)+ parseFloat(data.rows[o].actualQuantity);
}
}
sum_2 = parseFloat(sum_2)+ parseFloat(data.rows[o].bomQuantity);
planCode=data.rows[o].planCode;
}
}
if(data.rows.length>0)
{
var rows = [];
rows.push({
id : "total",
planType : "",
planCode : "",
productName : "",
productCode : "",
actualQuantity : sum_1,
qualificationRate : "",
bomType : "",
bomProductName : "",
bomProductCode : "",
bomQuantity : sum_2,
roundNo : ""
});
$('#mainTable').bootstrapTable('append', rows);
}
mergeCells(data.rows,"planCode", "planCode", 1, $('#mainTable'));
mergeCells(data.rows,"planCode", "actualQuantity", 1, $('#mainTable'));
mergeCells(data.rows,"planCode", "productName", 1, $('#mainTable'));
mergeCells(data.rows,"planCode", "productCode", 1, $('#mainTable'));
mergeCells(data.rows,"planCode", "qualificationRate", 1, $('#mainTable'));
},
responseHandler : function(data) {
return {
total : data.totalCount,
rows : data.result,
};
},
onPageChange : function(number, size) {
pageNumber = number;
},
onLoadError : function(textStatus, XMLHttpRequest) {
}
};
$("#mainTable").bootstrapTable(options);
}View Code
2.具体的进行单元格合并，mergeCells可以实现单元格合并，此功能还是非常简单的
1 /**
2 * 合并单元格
3 * @param data 原始数据（在服务端完成排序）
4 * @param fieldName 合并属性名称
5 * @param colspan 合并列
6 * @param target 目标表格对象
7 */
8 function mergeCells(data,exhibitionName,fieldName,colspan,target){
9 //声明一个map计算相同属性值在data对象出现的次数和
10 var sortMap = {};
11 for(var i = 0 ; i < data.length ; i++){
12 for(var prop in data[i]){
13 if(prop == exhibitionName){
14 var key = data[i][prop];
15 if(sortMap.hasOwnProperty(key)){
16 sortMap[key] = sortMap[key] * 1 + 1;
17 } else {
18 sortMap[key] = 1;
19 }
20 break;
21 }
22 }
23 }
24
25 var index = 0;
26 for(var prop in sortMap){
27 var count = sortMap[prop] * 1;
28 $("#mainTable").bootstrapTable('mergeCells',{index:index, field:fieldName, colspan: colspan, rowspan: count});
29 index += count;
30 }
31 }View Code
二、导出excle中实现动态单元格合并
1、在pom.xml中引入jar包
1 <!-- exel导出依赖 -->
2 <dependency>
3 <groupId>net.sf.jxls</groupId>
4 <artifactId>jxls-reader</artifactId>
5 <version>0.9.9</version>
6 </dependency>
7 <dependency>
8 <groupId>net.sf.jxls</groupId>
9 <artifactId>jxls-core</artifactId>
10 <version>0.9.9</version>
11 </dependency>
12 <dependency>
13 <groupId>jexcelapi</groupId>
14 <artifactId>jxl</artifactId>
15 <version>2.4.2</version>
16 </dependency>View Code
2、业务功能实现，
3、导出方法（合并单元格），含有非合并的代码
List<Merge> ml = getMerge(list, "getPlanCode");这个是合并的关键字
// 起始行号，终止行号， 起始列号，终止列号
sheet.addMergedRegion(new CellRangeAddress(m.getFromRow() + 3, m.getToRow() +3, 3, 3));这个进行具体的合并，从网上找的帖子，用的反射降低耦合度，其实可以不用反射，直接用对应功能实现
1 package com.gta.scm.component.common.utils;
2
3 /**
4 *
5 */
6
7
8 import java.io.BufferedInputStream;
9 import java.io.ByteArrayOutputStream;
10 import java.io.FileInputStream;
11 import java.io.FileNotFoundException;
12 import java.io.IOException;
13 import java.io.InputStream;
14 import java.io.OutputStream;
15 import java.io.UnsupportedEncodingException;
16 import java.lang.reflect.InvocationTargetException;
17 import java.util.ArrayList;
18 import java.util.HashMap;
19 import java.util.List;
20 import java.util.Map;
21 import javax.servlet.ServletOutputStream;
22 import javax.servlet.http.HttpServletRequest;
23 import javax.servlet.http.HttpServletResponse;
24
25 import net.sf.jxls.exception.ParsePropertyException;
26 import net.sf.jxls.transformer.XLSTransformer;
27 import org.apache.commons.codec.binary.Base64;
28 import org.apache.poi.hssf.usermodel.HSSFSheet;
29 import org.apache.poi.hssf.usermodel.HSSFWorkbook;
30 import org.apache.poi.ss.util.CellRangeAddress;
31 import org.slf4j.Logger;
32 import org.slf4j.LoggerFactory;
33
34 import com.fasterxml.jackson.databind.exc.InvalidFormatException;
35 import com.gta.scm.common.entity.Merge;
36
37
38 /**
39 * @author jingshu.deng
40 *
41 */
42 public class FileUtils {
43 private static Logger logger = LoggerFactory.getLogger(FileUtils.class);
44
45 /**
46 * @param fileName
47 * @return
48 */
49 public static String getFileExtension(String fileName) {
50 if (StringUtils.isEmpty(fileName)) {
51 return StringUtils.EMPTY;
52 }
53
54 return StringUtils.substringAfter(fileName, ".");
55 }
56
57 /**
58 *
59 * 解决文件下载时文件名的中文乱码及Firefox下空格被截断的问题
60 *
61 * @param request
62 * @param fileName
63 * @return
64 */
65 public static String decodeFileName(HttpServletRequest request, String fileName) {
66 String agent = (String) request.getHeader(CharEncodingCons.USER_AGENT);
67 try {
68 // check whether Firefox according to USER-AGENT
69 // 增加条件 agent.indexOf("Trident") == -1 针对IE10以上
70 if (agent != null && agent.indexOf(CharEncodingCons.BROWSER_IE) == -1 && agent.indexOf("Trident") == -1) {
71 byte[] bytes = Base64.encodeBase64(fileName.getBytes(CharEncodingCons.HTTP_CHARSET));
72 fileName = new String(bytes, CharEncodingCons.HTTP_CHARSET);
73 fileName = "=?" + CharEncodingCons.HTTP_CHARSET + "?B?" + fileName + "?=";
74 }
75 else {
76 byte[] bytes = fileName.getBytes(CharEncodingCons.DOWNLOAD_CHARSET);
77 fileName = new String(bytes, CharEncodingCons.WEB_CHARSET);
78 }
79 }
80 catch (UnsupportedEncodingException e) {
81 logger.error("UnsupportedEncodingException occurs while flushing file", e);
82 }
83 return fileName;
84 }
85
86 /**
87 * 导出Excel文件，按模板方式
88 * @param response
89 * @param templateName 模板名称及完全路径
90 * @param map
91 * @param fileName 要导出的文件名
92 */
93 public static void exportXlsFile(HttpServletRequest request,HttpServletResponse response,String templateName,Map<String,Object> map,String fileName){
94 try
95 {
96 XLSTransformer transformer=new XLSTransformer();
97 //XLSTransformer transformer = new XLSTransformer();
98 InputStream in = new BufferedInputStream(new FileInputStream(templateName),25000);
99 HSSFWorkbook workbook = transformer.transformXLS(in, map);
100 //write to buffer
101 ByteArrayOutputStream buf = new ByteArrayOutputStream(40000);
102 workbook.write(buf);
103 // write to response
104 in.close();
105 response.setContentType("application/vnd.ms-excel");
106 response.setHeader("Content-disposition", "attachment; filename=" + decodeFileName(request,fileName));
107 ServletOutputStream out = response.getOutputStream();
108 out.write(buf.toByteArray());
109 out.flush();
110 out.close();
111 }catch (IOException e){
112 e.printStackTrace();
113 }
114 }
115
116 // 先用String
117 public static <T> Object useMethod(T t, String sx) throws IllegalAccessException, IllegalArgumentException,
118 InvocationTargetException, NoSuchMethodException, SecurityException {
119 // 一般传入get方法
120 return (Object) t.getClass().getMethod(sx, null).invoke(t, null);
121
122 }
123
124 public static <T> List<Merge> getMerge(List<T> list, String sx) throws IllegalAccessException,
125 IllegalArgumentException, InvocationTargetException, NoSuchMethodException, SecurityException {
126 // 可以传入 想合并的属性值 传入一个字符串 用反射找到相应的get方法 指定调用此方法。。这里先写死
127 List<Merge> ml = new ArrayList<Merge>();
128 for (int i = 0; i < list.size() - 1; i++) {
129 if (useMethod(list.get(i), sx).equals(useMethod(list.get(i + 1), sx))) {
130 Object property = useMethod(list.get(i), sx);
131 logger.debug("property"+property);
132 Merge merge = new Merge();
133 int fromRow = i, toRow = i + 1;
134 if (i + 2 < list.size()) {
135 for (int j = i + 2; j < list.size(); j++) {
136 if (useMethod(list.get(j), sx).equals(property) ) {
137 toRow++;
138 } else {
139 i = j - 1;
140 break;
141 }
142 }
143 }
144 merge.setFromRow(fromRow);
145 merge.setToRow(toRow);
146 ml.add(merge);
147 }
148 }
149 return ml;
150 }
151
152 public static <T> void exportXlsFileMerge(HttpServletRequest request,HttpServletResponse response,String templateName,List<T> list,Map<String,Object> map,String fileName)
153 throws IllegalAccessException, IllegalArgumentException, InvocationTargetException, NoSuchMethodException,
154 SecurityException, FileNotFoundException, ParsePropertyException, InvalidFormatException {
155 String templateFile = fileName;
156 Map<String, Object> beans = new HashMap<String, Object>();
157 InputStream in = new FileInputStream(templateName);
158
159 XLSTransformer transformer = new XLSTransformer();
160
161 HSSFWorkbook workBook = (HSSFWorkbook) transformer.transformXLS(in, map); // 传入模板的输入流和map
162 // 开始进行合并单元格
163 HSSFSheet sheet = workBook.getSheetAt(0);// 1 1
164 List<Merge> ml = getMerge(list, "getPlanCode");
165 for (Merge m : ml) {
166 // 起始行号，终止行号， 起始列号，终止列号
167 sheet.addMergedRegion(new CellRangeAddress(m.getFromRow() + 3, m.getToRow() +3, 3, 3));
168 sheet.addMergedRegion(new CellRangeAddress(m.getFromRow() + 3, m.getToRow() +3, 4, 4));
169 sheet.addMergedRegion(new CellRangeAddress(m.getFromRow() + 3, m.getToRow() +3, 5, 5));
170 sheet.addMergedRegion(new CellRangeAddress(m.getFromRow() + 3, m.getToRow() +3, 6, 6));
171 sheet.addMergedRegion(new CellRangeAddress(m.getFromRow() + 3, m.getToRow() +3, 2, 2));
172 }
173 try {
174
175 ByteArrayOutputStream buf = new ByteArrayOutputStream(40000);
176 workBook.write(buf);
177 // write to response
178 in.close();
179 response.setContentType("application/vnd.ms-excel");
180 response.setHeader("Content-disposition", "attachment; filename=" + decodeFileName(request,fileName));
181 ServletOutputStream out = response.getOutputStream();
182 out.write(buf.toByteArray());
183 out.flush();
184 out.close();
185
186
187 } catch (IOException ie) {
188 ie.printStackTrace();
189 } catch (ParsePropertyException e) {
190 // TODO Auto-generated catch block
191 e.printStackTrace();
192 }
193
194 }
195
196 }View Code
添加merge类进行记录行号
1 public class Merge {
2 private int fromRow;
3 private int toRow;
4 private int fromIndex;
5 private int toIndex;
6 public int getFromRow() {
7 return fromRow;
8 }
9 public void setFromRow(int fromRow) {
10 this.fromRow = fromRow;
11 }
12 public int getToRow() {
13 return toRow;
14 }
15 public void setToRow(int toRow) {
16 this.toRow = toRow;
17 }
18 public int getFromIndex() {
19 return fromIndex;
20 }
21 public void setFromIndex(int fromIndex) {
22 this.fromIndex = fromIndex;
23 }
24 public int getToIndex() {
25 return toIndex;
26 }
27 public void setToIndex(int toIndex) {
28 this.toIndex = toIndex;
29 }
30
31 }View Code
调用的地方需要特别注意的地方
String templateFileName = request.getServletContext().getRealPath("/")
+ "/reportTemplate//ProductionReportTemplate.xls";
String destFileName = "生产产品明细汇总表.xls";
map.put("productionReportList", productionReport);
4.exlce模板
5.导出结果
哈，这样前后台都进行了实现了
本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。
