bootstaptable动态合并单元格和jxls动态合并单元格
项目中有个需求，根据计划单号进行对应的单元格合并，还有对应的导出功能也是需要，前台使用的是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); }
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 }
二、导出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>
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 }
添加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 }
调用的地方需要特别注意的地方
String templateFileName = request.getServletContext().getRealPath("/")
+ "/reportTemplate//ProductionReportTemplate.xls";
String destFileName = "生产产品明细汇总表.xls";
map.put("productionReportList", productionReport);
4.exlce模板
5.导出结果
哈，这样前后台都进行了实现了
