working on it ...

Filters

Explore Public Snippets

Sort by

Found 6 snippets matching: "merge cells"

    public by fabio.nosenzo  3311  14  6  1

    Excel VBA Merge cells with same value

    Merge cells in different columns with the same value in an ordered list
    Sub merge_cells()
    Application.DisplayAlerts = False
        
        'row to be avaluated
        iRow = 1
        'number of columns to be evaluated
        iNumberOfColumns = 12
        'first Column to be evaluated
        iFirstColumn = 4
        For i = iFirstColumn + 1 To iNumberOfColumns + iFirstColumn + 1
            If Cells(iRow, i) <> Cells(iRow, i - 1) Then
            iLastRow = i - 1
            'get first column letter
            vArr = Split(Cells(1, iFirstColumn).Address(True, False), "$")
            sFirstColumn = vArr(0)
            'get last column letter
            vArr = Split(Cells(1, i - 1).Address(True, False), "$")
            sLastColumn = vArr(0)
            sRange = sFirstColumn & iRow & ":" & sLastColumn & iRow
            Range(sRange).Select
                With Selection
                    .HorizontalAlignment = xlCenter
                    .VerticalAlignment = xlCenter
                    .WrapText = True
                    .Orientation = 0
                    .AddIndent = False
                    .IndentLevel = 0
                    .ShrinkToFit = False
                    .ReadingOrder = xlContext
                    .MergeCells = False
                End With
            Selection.Merge
            iFirstColumn = i
            End If
        Next i
          
    Application.DisplayAlerts = True
    End Sub
    

    external by Ted Potma  606  1  3  0

    Extract Merged Cells and Formatted Values for Google Spreadsheets in Apps Script

    Extract Merged Cells and Formatted Values for Google Spreadsheets in Apps Script: parseHtml.js
    function columnToLetter(column)
    {
      var temp, letter = '';
      while (column > 0)
      {
        temp = (column - 1) % 26;
        letter = String.fromCharCode(temp + 65) + letter;
        column = (column - temp - 1) / 26;
      }
      return letter;
    }
    
    function createBooleanArray(length) {
      
      if (arguments.length === 0) { return false }
      
      var arr = new Array(length),
          i = length;
      
      var args = Array.prototype.slice.call(arguments, 1);
      while(i--) arr[length-1 - i] = createBooleanArray.apply(this, args);
      
      return arr;
    }
    
    var _ = underscorejs.load();
    var docId = '<insert doc id here>';
    
    //Download the spreadsheet as HTML
    var zipUrl = Utilities.formatString('https://docs.google.com/spreadsheets/d/%s/export?format=zip', docId);
    var zipUrlParams = {
      method      : "get",
      headers     : {"Authorization": "Bearer " + ScriptApp.getOAuthToken()},
      muteHttpExceptions:true,
    };
    //Build a hash of cell index (e.g. 'Sheet1:A3') to rowspan and colspan.  If a cell is part of a merged range and
    //not the top left cell in that range it will not appear in the hash.
    var parsedHtml = _.chain(Utilities.unzip(UrlFetchApp.fetch(zipUrl,zipUrlParams).getBlob()))
    .filter(function (x) { return _.contains(sheetNames, x.getName().slice(0, -5)) })
    .map(function (x) {
      var sheetName = x.getName().slice(0, -5);
      var sheet = document.getSheetByName(sheetName);
      var dataRange = sheet.getDataRange();
      var visited = createBooleanArray(dataRange.getNumRows(), dataRange.getNumColumns());
      var xmlString = x.getDataAsString().match(/<tbody>(.*?)<\/tbody>/g);
      
      if (!xmlString || xmlString.length === 0) { throw ('Could not find tbody element') }
      
      //Clean up unclosed tags.
      xmlString = xmlString[0].replace(/<br>/g, '');
      
      return _.map(
        XmlService.parse(xmlString).getRootElement().getChildren('tr'), 
        function (tableRow, tableRowIndex) {
          var rowIndices = _.chain(this.visited[tableRowIndex])
          .map(function (value, index) { return {index: index, value: value }})
          .filter(function (x) { return !x.value })
          .pluck('index')
          .value();
          
          return _.map(
            tableRow.getChildren('td'),
            function (tableCell, tableColumnIndex) {
              if (tableColumnIndex + this.increaseBy >= rowIndices.length) { return null }
              
              var rowspan = tableCell.getAttribute('rowspan') ? parseInt(tableCell.getAttribute('rowspan').getValue()) : 1;
              var colspan = tableCell.getAttribute('colspan') ? parseInt(tableCell.getAttribute('colspan').getValue()) : 1;
              var columnLetter = columnToLetter(rowIndices[tableColumnIndex + this.increaseBy] + 1);
              
              for (var i = 0; i < rowspan; i++) {
                if (tableRowIndex + i > this.visited.length) { break; }
                for (var j = 0; j < colspan; j++) {
                  if (tableColumnIndex + this.increaseBy >= rowIndices.length 
                      || rowIndices[tableColumnIndex + this.increaseBy]+j >= this.visited[0].length) { 
                    break; 
                  }
                  this.visited[tableRowIndex+i][rowIndices[tableColumnIndex + this.increaseBy]+j] = true; 
                }
              }
              
              if (colspan > 1) { this.increaseBy += colspan-1 }
              
              return {
                index: Utilities.formatString('%s!%s%s', sheetName, columnLetter, (tableRowIndex+1)),
                text: _.chain(tableCell.getDescendants())
                  .filter(function (x) { return x.getType() == XmlService.ContentTypes.TEXT })
                  .map(function (x) { return x.asText().getText() })
                  .value().join(' ').trim(),
                rowspan: rowspan,
                colspan: colspan
              };
            },
            {increaseBy: 0, visited: this.visited})
        },
        {visited: visited})
    })
    .flatten()
    .filter(function (x) { return x })
    .indexBy(function (x) { return x.index })
    .value();
      
    
    

    external by hfxu  17  3  1  0

    the common function for EasyUI dataGrid merge cells[合并单元格通用方法]

    the common function for EasyUI dataGrid merge cells[合并单元格通用方法]: tools.js
    void function (t) {
        /**
         * 计算相对宽度
         * @param percent 百分比整数
         * @param totalWidth 表格总宽度
         * @param checkboxCount 单选框列数
         * @return {number}
         */
        t.getWidth = function (percent, totalWidth, checkboxCount) {
            totalWidth = totalWidth ? totalWidth : document.body.clientWidth;
            checkboxCount = checkboxCount ? checkboxCount : 1;//默认表格中有一个checkbox 占用28px
            return (totalWidth - 28 * checkboxCount) * percent / 100;
        };
      
        /**
         * 合并EasyUI DataGrid 单元格
         * @param $grid jQuery对象
         * @param data dataGrid数据
         * @param fieldArray 需要合并的单元格名称数组
         */
        t.mergeRows = function ($grid, data, fieldArray) {
            var rows = data.rows;
            var result = {};
            if (rows.length > 0 && fieldArray.length > 0) {
                for (var i in fieldArray) {
                    if (fieldArray.hasOwnProperty(i)) {
                        var field = fieldArray[i];
                        var index, rowspan, value;
                        result[field] = [];
                        for (var j in rows) {
                            if (rows.hasOwnProperty(j)) {
                                var row = rows[j];
                                if (j == 0) {
                                    //初始化
                                    index = 0;
                                    rowspan = 0;
                                    value = row[field];
                                }
                                if (value == row[field]) {
                                    //当值相同时,合并单元格数增加
                                    rowspan++;
                                } else {
                                    //当值不相同时
                                    if (rowspan > 1) {
                                        //只合并2个以上单元格
                                        result[field].push({
                                            index: index,
                                            rowspan: rowspan
                                        });
                                    } else {
                                        //否则重置rowspan
                                        index = j;
                                        rowspan = 1;
                                    }
                                }
                                value = row[field];
                            }
                        }
                        //最后判断一次
                        if (rowspan > 1) {
                            //只合并2个以上单元格
                            result[field].push({
                                index: index,
                                rowspan: rowspan
                            });
                        }
                    }
                }
                // console.log(result);
                //计算出需要合并的数组 index-列 rowspan-合并行数
                for (var key in result) {
                    var merges = result[key];
                    for (var k = 0; k < merges.length; k++) {
                        $grid.iDatagrid('mergeCells', {
                            index: merges[k].index,
                            field: key,
                            rowspan: merges[k].rowspan
                        });
                    }
                }
            }
        };
    }(window.tools = {});
    
    //-----------------------------以上方法的调用示例----------------------------
    $("#grid").iDatagrid({
        rownumbers: false,
        remoteFilter: true,
        url: '/customer_list.json',//客户数据数组
        columns: [[
            {field: '', title: '', checkbox: true},
            {field: 'customerCode', title: '客户编号', sortable: true, width: tools.getWidth(10)},
            {field: 'customerName', title: '客户名称', sortable: true, width: tools.getWidth(20)},
            {field: 'contacts', title: '客户联系人', sortable: true, width: tools.getWidth(10)},
            {field: 'contactPhone', title: '客户联系方式', sortable: true, width: tools.getWidth(15)},
            {field: 'address', title: '客户地址', sortable: true, width: tools.getWidth(45)}
        ]], onLoadSuccess: function (data) {
            //合并相同客户编号与客户名称的数据,必须是经过排序之后的相邻数据才能合并
            tools.mergeRows($(this), data, ['customerCode','customerName']);
        }
    });
    
    

    external by mmustala  152  1  2  0

    Spreadsheet merged cells question

    Spreadsheet merged cells question: Console_example.rb
    # Should 'used_merge' indicate that the cell has been merged to another cell?
    require 'spreadsheet'
    excel = Spreadsheet.open('merged_cells.xls'); nil
    sheet = excel.worksheet(0)
    row = sheet.row(0)
    row.formats.each do |format|
      p format.used_merge
    end
    
    
    

    external by Ananda Mahto  175  0  2  0

    Benchmarking options at http://stackoverflow.com/questions/26049028/condense-merge-cells-in-a-table-in-r

    Benchmarking options at http://stackoverflow.com/questions/26049028/condense-merge-cells-in-a-table-in-r: SO26049028.R
    R
    ## Required packages
    library(data.table)
    library(dplyr)
    library(reshape2)
    library(microbenchmark)
    
    ## Small sample `data.frame`
    dat <- structure(list(Pos = c("Position1", "Position2", "Position3", 
    "Position1", "Position3", "Position2"), M = c(34L, 45L, 89L, 
    56L, 54L, 56L), P = c(56L, 23L, 78L, 45L, 35L, 89L)), .Names = c("Pos", 
    "M", "P"), class = "data.frame", row.names = c(NA, -6L))
    
    ## Small sample `matrix`
    m1 <- structure(c(34, 45, 89, 56, 54, 56, 56, 23, 78, 45, 35, 89), .Dim = c(6L, 
    2L), .Dimnames = list(c("Position1", "Position2", "Position3", 
    "Position1", "Position3", "Position2"), c("M", "P")))
    
    ## Built up to ~ 1M rows
    dat <- do.call(rbind, replicate(16667, dat, FALSE))
    m1 <- do.call(rbind, replicate(16667, m1, FALSE))
    
    
    ## rnso's double aggregate + merge
    funRNSO <- function() {
      a1 = aggregate(M ~ Pos, dat, sum)
      a2 = aggregate(P ~ Pos, dat, sum)
      merge(a1, a2)
    }
    
    ## my suggestion in comments
    funRNSOb <- function() aggregate(. ~ Pos, dat, sum)
    
    ## Richard Scriven
    funRS <- function() t(sapply(split(dat[-1], dat$Pos), colSums))
    
    ## Mine
    funAM <- function() xtabs(Freq ~ Var1 + Var2, data.frame(as.table(m1)))
    
    ## Akrun - dplyr
    funAdplyr <- function() {
      dat %>% 
        group_by(Pos) %>% 
        summarise_each(funs(sum=sum(., na.rm=TRUE)))
    }
    
    ## Akrun - data.table
    funAdt <- function() {
      as.data.table(dat)[, lapply(.SD, sum, na.rm=TRUE), by=Pos]
    }
    
    ## Akrun - by
    funAby <- function() {
      do.call(rbind, by(m1, list(rownames(m1)), colSums, na.rm=TRUE))
    }
    
    ## Akrun - acast
    funAacast <- function() {
      acast(melt(m1), Var1~Var2, value.var="value", sum, na.rm=TRUE)
    }
    
    system.time(funRNSO())
    #    user  system elapsed 
    #    3.20    0.00    3.22
    system.time(funRNSOb())
    #    user  system elapsed 
    #    1.72    0.00    1.72
    
    ## ^^ Imagine if we had to aggregate more than 2 columns!
    
    microbenchmark(tapply = funRS(), 
                   xtabs = funAM(), 
                   dplyr = funAdplyr(), 
                   datatable = funAdt(), 
                   by = funAby(), 
                   acast = funAacast(), 
                   times = 20)
    # Unit: milliseconds
    #       expr        min         lq    median        uq       max neval
    #     tapply 155.003662 164.664556 175.49503 209.98393 275.68028    20
    #      xtabs 463.060971 520.557225 580.92654 611.13589 720.41927    20
    #      dplyr  17.555662  19.042972  21.12384  24.53657  29.02181    20
    #  datatable   7.277477   8.253766  11.49202  18.26767  26.67410    20
    #         by 232.103088 255.488470 288.51957 318.80311 378.95220    20
    #      acast 159.206876 172.837925 213.22048 253.78435 331.10764    20
    
    

    external by alezhu  121  0  2  0

    Excel ColumnWidth for merged cells too

    Excel ColumnWidth for merged cells too: GetCellWidth.excel.vba
    Function GetCellWidth(ra As range) As Double
        Dim vColWidth As Double
        Dim cell As range
        vColWidth = ra.ColumnWidth
        If ra.MergeCells Then
            vColWidth = 0
            For Each cell In ra.MergeArea.Cells
              vColWidth = vColWidth + cell.ColumnWidth
            Next
        End If
        GetCellWidth = vColWidth
    End Function
    
    
    

    List Complete ( 6 snippets total )

    • Public Snippets
    • Channels Snippets