bootstaptable动态合并单元格和jxls动态合并单元格

时间：2024-01-31 13:10:06

项目中有个需求，根据计划单号进行对应的单元格合并，还有对应的导出功能也是需要，前台使用的是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.导出结果

 

 哈，这样前后台都进行了实现了

相关文章