Row Grouping success but missing styles

maniya · June 18

I successfully Implemented the rowGroup by using this code
https://datatables.net/forums/discussion/71503/excel-export-with-rowgroup

https://live.datatables.net/hemelesi/42/edit

it works weel, i see the totals generated at my bottom where i added some custom code to do some calulations, the only issue i am having is that i am unable to add some styles to the row data at the bottom, where i am doing some calculations.

Here is the code i have and trying to fix it

where i am trying to make the column, bold, align to the right and have a boder at the top and bottom in black and bacjground as grey:

This is my getTableRow Function

function getTableData(groupName, title, button, dt) {
    var dataSrc = getRow(dt);    
    var header = getHeaderNames(dt);
    var rowNum = 1;
    var mergeCells = [];
    var mergeCol = ((header.length - 1) + 10).toString(36).toUpperCase();
    var ws = '';


    var selectorModifier = {};

    if (button.exportOptions.hasOwnProperty('modifier') ) {
      selectorModifier = button.exportOptions.modifier;
    }

    ws += buildCols(header);
    ws += '<sheetData>';

    // Print button.title
    if (button.title.length > 0) {
      if (button.title === '*') {
        button.title = document.getElementsByTagName("title")[0].innerHTML;
      }
      ws += buildRow([button.title], rowNum, 51);      
      mergeCells.push('<mergeCell ref="A' +rowNum + ':' + mergeCol + '' +rowNum + '"/>');
      rowNum++;             
    }


    // Print button.messageTop
    if (button.messageTop.length > 0 && button.messageTop.length != '*') {
      ws += buildRow([button.messageTop], rowNum, 51);       
      mergeCells.push('<mergeCell ref="A' +rowNum + ':' + mergeCol + '' +rowNum + '"/>');
      rowNum++;
    }

    // All rows on one page with group names separating groups
    if ( Array.isArray( groupName ) ) {
      if (button.header) {
        ws += buildRow(header, rowNum, 2);
        rowNum++;
      }
      var currentGroup = '';
      // Loop through each row to append to sheet.    
      thisTable.rows(selectorModifier).every( function ( rowIdx, tableLoop, rowLoop ) {
        var _data = this.data().slice(0, header.length);
        var data = transformData(_data);

        if (data[dataSrc] !== currentGroup ) {
          currentGroup = data[dataSrc];
          ws += buildRow([currentGroup], rowNum, 51);
          mergeCells.push('<mergeCell ref="A' +rowNum + ':' + mergeCol + '' +rowNum + '"/>');
          rowNum++;
        }          
        // If data is object based then it needs to be converted 
        // to an array before sending to buildRow()
        ws += buildRow(data, rowNum, '');
        rowNum++;
      } );

    } else {
      // Place each group on individual sheets
        if ( title ) {
          ws += buildRow([title], rowNum, 51);  
          mergeCells.push('<mergeCell ref="A' +rowNum + ':' + mergeCol + '' +rowNum + '"/>');
          rowNum++;
        }          
      if (button.header) {
        ws += buildRow(header, rowNum, 2);
        rowNum++;
      }
      // Loop through each row to append to sheet.    
      thisTable.rows(
        function ( idx, data, node ) {
            let displayText = data[dataSrc].display;
                const match = displayText.match(/<a[^>]*>(.*?)<\/a>/);
                if (match && match[1]) {
                  displayText = match[1].trim();
                } else {
                  displayText = displayText.trim();
                }
                    if(displayText == '-') {
                        displayText = 'No Group';
                    }
          return displayText === groupName ?
            true : false;
        },
        selectorModifier
      ).every( function ( rowIdx, tableLoop, rowLoop ) {
        var _data = this.data().slice(0, header.length);
        var data = transformData(_data);
        // If data is object based then it needs to be converted 
        // to an array before sending to buildRow()
        ws += buildRow(data, rowNum, '');

        rowNum++;
      });

    }

    // Calculate totals for specific columns before the footer
    const totalColumns = [14, 15, 18, 19, 20, 21, 22];
    const totals = calculateColumnTotals(dt, totalColumns, groupName);

    // Build the totals row
    let totalsRow = Array(header.length).fill(''); // Fill all cells with empty string
    totalsRow[0] = groupName + ' :'; // Set the group name in the first cell
    totalColumns.forEach((colIndex, index) => {
        totalsRow[colIndex] = totals[index].toFixed(2); // Place totals in specified columns
    });

    ws += buildRow(totalsRow, rowNum, '');
    mergeCells.push('<mergeCell ref="A' + rowNum + ':N' + rowNum + '"/>'); // Merge first 14 columns
    rowNum++;


    // Output footer
    if (button.footer) {
      ws += buildRow(getFooterNames(dt), rowNum, 2);
      rowNum++;
    }

    // Print button.messageBottom
    if (button.messageBottom.length > 0 && button.messageBottom.length != '*') {

      ws += buildRow([button.messageBottom], rowNum, 51);

      mergeCells.push('<mergeCell ref="A' +rowNum + ':' + mergeCol + '' +rowNum + '"/>');
      rowNum++;
    }

    mergeCellsElement = '';

    if (mergeCells) {
      mergeCellsElement = '<mergeCells count="' + mergeCells.length + '">'+
        mergeCells +
       '</mergeCells>';
    }
    ws += '</sheetData>' + mergeCellsElement;

    return ws;

  }

function calculateColumnTotals(dt, columns, groupName) {
    let totals = new Array(columns.length).fill(0);
    dt.rows({ search: 'applied' }).every(function() {
        var data = this.data();
        var _currentGroup = data[getRow(dt)]; // Ensure this is fetching the correct group
        var currentGroup = extractPlainText(_currentGroup);
        if(currentGroup == '-') {
            currentGroup = 'No Group'
        }
        if (currentGroup === groupName) {
            columns.forEach((colIndex, i) => {
                let cleanValue = data[colIndex].toString().replace(/[^\d.-]/g, '');
                let value = parseFloat(cleanValue);
                if (!isNaN(value)) {
                    totals[i] += value;
                }
            });
        }
    });
    return totals;
    }


function transformData(dataArray) {
        return dataArray.map(item => {
            // Initialize an object to hold the transformed data
            let transformedItem = {};

            if (typeof item === 'object' && item !== null) {
                // If it's an object, use the 'display' property directly
                transformedItem = item.display || ""; // Use an empty string if 'display' is not available
            } else if (typeof item === 'string') {
                transformedItem = stripHtmlJS(item);
            } else {
                // For any other types (e.g., null, undefined), use an empty string
                transformedItem = "";
            }

            return transformedItem;
        });
    }

kthorngren · June 18

The excelHtml5 docs list the builtin styles. The buildRow() function takes in a styleNum parameter:

  function buildRow(data, rowNum, styleNum) {
    // Builds row XML.
    //Params:
    //  data: Row data.
    //  rowNum: Excel row number.
    //  styleNum: style number or empty string for no style.
    //Returns:
    //  String of XML formatted row.
    
    var style = styleNum ? ' s="' + styleNum + '"' : '';
    
    var row = '<row r="' + rowNum + '">';

    for (i=0; i<data.length; i++) {
      colNum = (i + 10).toString(36).toUpperCase();  // Convert to alpha
      
      var cr = colNum + rowNum;
      
      row += '<c t="inlineStr" r="' + cr + '"' + style + '>' +
              '<is>' +
                '<t>' + data[i] + '</t>' +
              '</is>' +
            '</c>';
    }
      
    row += '</row>';
        
    return row;
  }

In the getTableData() function buildRow is called multiple times with different style numbers; 51, 2 and "". The style passed into buildRow() is applied to each cell in that row. You can change the number.

Or if you want to apply styles to specific cells in the row then you will need to refactor the buildRow() function apply the styles to specified cells. I would look at adding a new parameter that is in array containing cell indexes with assigned styles. For example [ [[0, 1], 12], [[2], 18] ] applies style number 12 to cells 0 and 1 and style 18 to cell index 2. Otherwise apply the passed in styleNum parameter.

Kevin

kthorngren · June 18

Better might be to use an abject that way you can look for the override style with the for loop's i index. For example: { 0: 12, 1: 12, 2: 18 }.

Kevin

maniya · June 18

ok, i did a try but here lies the confusion, i am building the extra row when i doing a group, so my grouping code is render end like this:

rowGroup: {
        enable: false, // Start with row grouping disabled
        dataSrc: 6,
        endRender: function(rows, group) {
            var columnIndices = [14, 15, 17, 18, 19, 20,21,22]; // Adjusted indices for 0-based index
            var totals = new Array(columnIndices.length).fill(0); // Initialize totals array

            rows.data().each(function(row) {
                columnIndices.forEach(function(colIndex, index) {
                        let cValue = row[colIndex].toString().replace(/[\s,]+/g, '');
                    totals[index] += parseFloat(cValue) || 0; // Calculate totals
                });
            });

            var totalRow = $('<tr class="group-total"/>').append('<td colspan="12">Total for ' + group + ':</td>');
            totals.forEach(function(total, index) {
                        // Append the total for the current column
                        var columnName = thisTable.column(columnIndices[index]).header().textContent;
                        if (columnName.includes('Note')) {
                            totalRow.append('<td style="text-align:center;">&nbsp;</td>');
                        } else {
                            totalRow.append('<td style="text-align:right;">' + total.toFixed(2) + '</td>');
                        }
                        });

                        // Append empty cells until the next total column or the end
                    var nextColumnIndex = columnIndices[columnIndices.length - 1] + 1;
                    for (var j = columnIndices[columnIndices.length - 1] + 1; j < nextColumnIndex; j++) {
                            totalRow.append('<td>&nbsp;</td>');
                        }

            return totalRow;
        }
        },

the above code is not perfect but it works somehow,

i cannot put the colors here because it reflects the on screen page, but when i export to excel, i want to add that design, so just before the footer in the excel, this row gets added and it just displays the totals and the name of the group.

in excel, i tried the way you mentioned, but it seems, if i have to do for a column, it will do that column for all rows which is not what i am trying to solve here,

i am trying to get the totals row and all its column to have the style applied

kthorngren · June 18

I updated the test case to show an example of what I meant. I updated buildRow() with an additional parameter called cellStyle.

  function buildRow(data, rowNum, styleNum, cellStyle={}) {
    // Builds row XML.
    //Params:
    //  data: Row data.
    //  rowNum: Excel row number.
    //  styleNum: style number or empty string for no style.
    //Returns:
    //  String of XML formatted row.

    var style = styleNum ? ' s="' + styleNum + '"' : '';
    
    var row = '<row r="' + rowNum + '">';

    for (i=0; i<data.length; i++) {
      colNum = (i + 10).toString(36).toUpperCase();  // Convert to alpha
      
      var cr = colNum + rowNum;
      
      // Override default style if style defined for cell
      if ( cellStyle.hasOwnProperty(i) ) {
        style = ' s="' + cellStyle[i] + '"'
      }

      row += '<c t="inlineStr" r="' + cr + '"' + style + '>' +
              '<is>' +
                '<t>' + data[i] + '</t>' +
              '</is>' +
            '</c>';
    }
      
    row += '</row>';
        
    return row;
  }

Its an object like I explained earlier with the key as the cell index and the value as the style from the Builtin styles.

I updated this section of the getTableData() function:

    // All rows on one page with group names separating groups
    if ( Array.isArray( groupName ) ) {

      if (button.header) {
        ws += buildRow(header, rowNum, 2);
        rowNum++;
      }

      var currentGroup = '';

      // Loop through each row to append to sheet.    
      table.rows(selectorModifier).every( function ( rowIdx, tableLoop, rowLoop ) {
        var data = this.data();
        
        if ( data[dataSrc] !== currentGroup ) {
          currentGroup = data[dataSrc];
          
          // Apply custom style to cell index 2
          cellStyle = {2: 15}

          ws += buildRow([currentGroup, '', 5], rowNum, 51, cellStyle);
                    
          // Don't merge cells if adding multiple cells to rowGroup
          //mergeCells.push('<mergeCell ref="A' +rowNum + ':' + mergeCol + '' +rowNum + '"/>');
          rowNum++;
        }          
        
        
        // If data is object based then it needs to be converted 
        // to an array before sending to buildRow()
        ws += buildRow(data, rowNum, '');

        rowNum++;
      } );

Added line 19 to define the specific cell styles and in line 21 it passes three RowGroup cells; first is the rowGroup name, second an empty cell and last is a simulated total value. The "total" cell will have a green background.

I commented out the mergeCells statement to allow for multiple cells in the RowGroup row.

I also set it to export all on one Excel page:

        customize: function( xlsx, button, dt ) {

          //console.log(button);
          multiSheet = false;  // Export all groups in one sheet or multiple

You will need to do something similar with the footer total code you have for the multiple sheet option. If you need specific help with this then please update the test case to show the operation row totals and tell us specifically how you want the cells formatted with the style number.

Kevin

maniya · June 19

can you creatre this chnge in demo, so i can understand and try to add that code in my excel

kthorngren · June 19

Yes, that is why I asked you to update the test case with an example of your data and code to sum the data and the formatting you want. This way we can provide a more specific example.

Kevin

kthorngren · June 19

Possibly all you need to do is select the appropriate style number to pass in line 112 of your first code snippet:

    ws += buildRow(totalsRow, rowNum, '');
    mergeCells.push('<mergeCell ref="A' + rowNum + ':N' + rowNum + '"/>'); // Merge first 14 columns
    rowNum++;

Chance the call to buildRow() with the builtin style you want to use, for example:

    ws += buildRow(totalsRow, rowNum, 15);  // RowGroup style 15
    mergeCells.push('<mergeCell ref="A' + rowNum + ':N' + rowNum + '"/>'); // Merge first 14 columns
    rowNum++;

It just depends on what you want. We will need. a specific example with specific requirements to offer specific suggestions.

Kevin

maniya · June 19

so i am missing something for sure
Here is the attached Code and the datatables excel i have,

and here is the fill code

i do not know if this works or not
but here
https://jsbin.com/sofacamuha/1/edit?html,css,js,console,output

Here is the code for JS

https://jsfiddle.net/k7cjbg90/

kthorngren · June 19

Your test case doesn't work because of this error:

TypeError: $(...).DataTable is not a function

You aren't loading datatables.js nor the buttons code. Also you are loading jquery.js twice.

It might be easier to update the test case you linked by replacing the HTML table and Javascript code. This way all the required libraries are being loaded.
https://live.datatables.net/hemelesi/42/edit

Please make sure it executes properly and shows what you currently have. Also provide details of what you want changed.

Kevin

maniya · June 19

i have that code ready now
https://jsfiddle.net/k7cjbg90/1/

kthorngren · June 19

Your test case is getting this error:

Uncaught TypeError: n.title is not a function

You are loading buttons.js twice which is causing the problem. I commented out the second instance in this update:
https://jsfiddle.net/fmupj59t/

Now this error is occurring:

Uncaught TypeError: this.data(...).slice is not a function

I'm not sure what you are trying to do in this code as its not something from the original examples I wrote.

Please make sure the test case runs properly, is able to export to Excel and shows the problem you are trying to solve.

Kevin

maniya · June 19

i just ran it with a small change and i was able to download the excel
https://jsfiddle.net/k7cjbg90/2/
try now now
i design i am talking is for the row which has the group name and the totals

Background colour = #d3d3d3

Solid border on top and double line border on bottom

Bold fonts with the “Total for #eventName#” aligned to the right?

unable to extend the totals formatting to the far-right end of the table (i.e. in the Date Paid, Notes, and Actions columns as well)?

kthorngren · June 19

Please read the excelHtml5 docs. Please note this from the docs:

XLSX file format
It is important to note that Buttons does not provide any kind of abstraction for the XLSX XML! If you want to modify the output Excel file you need to understand the XML used for the spreadsheet.

The Excel export button provides the following builtin styles. If you need custom styles not provided then you will find threads, like this, that provide information of how to add custom styles. Its complicated and I haven't looked at doing this in many years.

I added an object to the call to buildRows() to define styles to override the default style for columns 14, 15 and 18. Lines 227 in the test case.

        ws += buildRow(totalsRow, rowNum, '', {14: 15, 15: 14, 18: 22});

Add to add the else clause to make sure to reset back to the default style if a cell style isn't provided: Line 76 in the test case.

            if (cellStyle.hasOwnProperty(i)) {
                style = ' s="' + cellStyle[i] + '"';
            } else {
                style = styleNum ? ' s="' + styleNum + '"' : '';
            }

https://jsfiddle.net/Lzkapqjb/

Kevin

maniya · June 19

i tried as you said, but the result is still not right,

Created two functions to get the style to all the headers values i am geting

function buildDynamicStyles(numColumns) {
    var styles = {};
    for (var i = 1; i <= numColumns; i++) {
        styles[i] = 7; // Assign style number 7 to each column
    }
    return styles;
    }

    function adjustStylesBasedOnRowNum(styles, rowNum) {
    var adjustedStyles = {};
    var startColumn = 1; // Start from column 1
    var totalColumns = Object.keys(styles).length;

    if (rowNum > 0) {
        // Adjust the keys based on rowNum
        for (var i = 1; i <= totalColumns - rowNum; i++) {
            adjustedStyles[i] = styles[i + rowNum];
        }
    } else {
        // If rowNum is 0 or undefined, use the original styles
        adjustedStyles = styles;
    }

    return adjustedStyles;
    }

Then in the getTable i called the builddynamicStyles

var header = getHeaderNames(dt);
    var numColumns = header.length;
    var rowNum = 1;
    var ws = '';
    var styles = buildDynamicStyles(numColumns);


   console.log(styles);

// rowNum for first 14 coluns, i am not getting the grey back for that entire row 

    var adjustedStyles = adjustStylesBasedOnRowNum(styles, rowNum);

    console.log(adjustedStyles);

    ws += buildRow(totalsRow, rowNum, '',adjustedStyles);

it seems multiple styles cannot be an option

kthorngren · June 19

Only one style number can be assigned to a cell. Each style number can contain multiple styles like red and bold.

If you want the default style for the row to be 7 then do this:

ws += buildRow(totalsRow, rowNum, 7,adjustedStyles);

rowNum keeps track of the row number in the SS. Not sure its correct to use it in the statement to access the styles array elements:

adjustedStyles[i] = styles[i + rowNum];

I'm not sure what your goal is with adjustStylesBasedOnRowNum(). Please post a link with the updates so we can debug how the code is working.

Kevin

maniya · June 19

i am updating the last fiddle which was working, just to add the functions i created, i want to do the entire row as grey bordered which is 32 i found and do it till the headers end, so for that i have written he function to go from 1 to how many columns i have do all grey for the totals row,

last part is to move the first 14 merged columns text in that to the right side where the 14 colspan ends

https://jsfiddle.net/b29rk5g3/1/

now the above code shows gey background for some columns and for others it is just showing the border, i want to do entire as gray background with border and put the text of the merged cols to the right side

kthorngren · June 19

Sounds like you want a combined style of 32 and 52. I set the first cell to 52 (adjustedStyles[0] = 52;) on line 257 here:
https://jsfiddle.net/ma4rsve5/

The fun begins. A custom style will need to be added that combines the two builtin styles. See the threa I linked to and the thread rf1234 linked to at the bottom of the thread.

Kevin

maniya · June 19

the grey color should expand to the start and end but i am only seeing few columns, i tried some more changes but all ending up on same place
https://prnt.sc/3-qxPtsDQpn1

kthorngren · June 19

That is correct. Style 52 is only right aligned text. You will need to create a custom style that combines styles 32 and 52 to have riight aligned text with a grey background. See the thread I linked to for more info.

Kevin

kthorngren · June 19

Here is a simple example that adds two styles to the builtin styles:
https://live.datatables.net/jurunozo/1/edit

There is a link that shows the builtin styles. Try combining the number 32 and 52 to create a new style.

Kevin

kthorngren · June 20

I had some time to work with this:
https://jsfiddle.net/3pk8evjs/

I added the new style in the `customize function, line 482:

                customize: function(xlsx, button, dt) {
                        var styles = $('cellXfs', xlsx.xl['styles.xml']);
                            styles.append('<xf numFmtId="0" fontId="2" fillId="2" borderId="1" applyFont="1" applyFill="1" applyBorder="1">' + '<alignment horizontal="right"/>' + '</xf>')

I took the format defined for style 32 and combined with 52 from the buttons.html5.js code.

I added a function at line 315:

    function getStyleIndex(xlsx) {
      var oxf = $('xf', xlsx.xl['styles.xml']);
      var styleIndex = oxf.length;
      var rightAlignGrey = styleIndex - 2;
      
      return rightAlignGrey;

    }

It simply finds the index of last style added and returns it. If you add more styles this will need changed to accomodate.

The addSheet and updateSheet1 functions call getStyleIndex, for example:

    function addSheet(xlsx, groupName, title, name, sheetId, button, dt) {
            var rightAlignGrey = getStyleIndex(xlsx);

getTableData takes a new parameter with the appended style index:

   function getTableData(groupName, title, button, dt, rightAlignGrey) {

Line 257 sets the first cell with this style:

    var adjustedStyles = adjustStylesBasedOnRowNum(styles, rowNum);

        adjustedStyles[0] = rightAlignGrey;  // Right aligned text

It is somewhat hard coded so feel free to adjust the code as needed to make it more flexible.

Kevin

maniya · June 26

is there a way in the current code you have to display all the row grouped along with created tr to show in one sheet instead of multiple, i tried setting multiple to false but it just brought plain data to excel instead of grouped

allan · June 26

As you can see from Kevin's example, row grouping on export is entirely custom, so you'd need to modify that code in order for it to work.

Allan

kthorngren · June 26

In the last example:
https://jsfiddle.net/3pk8evjs/

Setting the multiSheet option to false does result in one page with two groups in the SS.. Plus there is a row at the bottom with the two groups combined.

You may need to refactor your summing code to sum each group, etc depending on what you want.

Kevin

maniya · June 26

@kthorngren i have already written a lot of javascript and now my brain has exhausted continuing fixing it, i would rather take a break on rather than doing work on the multisheet: false, i analyzed it and it seems quite a work, so it seems i have somehow integrate the rowgroup functionality in the customize but am not sure how to, i would keep this question open and will do later or if anyone is willing to do it, happy to do it

Thanks

kthorngren · June 26

Sorry, I don't understand what issues you are having. The screenshot shows that exporting to a single sheet still provides the groups. You are welcome to revisit this thread later. When ready to work on it again please provide more details about the problem.

Kevin

maniya · June 26

i want to make it appear like this if multiselect is false

https://prnt.sc/DhrIjJqagb70

kthorngren · June 26

I believe that will require updating the getTableData() function to perform the summing for each group and updating the styles applied. Probably the loop starting at line 193.

You may need to take the code starting at line 245 and refactor it into a function to allow for calling within the line 193 loop to output after each group for single sheet and call between 242 and 243 to output at the end of each sheet for multi sheet.

It may take a bit more than that. Don't really know without digging into it.

Kevin

maniya · June 26

That is what i said, i need timeout, its too tiring for me now

kthorngren · June 26

Sorry wasn’t trying to push you to work on this. Just trying to give some pointers. You work on it when you want.

Kevin

Row Grouping success but missing styles

Row Grouping success but missing styles

Answers

Howdy, Stranger!

Categories

DataTables

Row Grouping success but missing styles

Row Grouping success but missing styles

Answers

Howdy, Stranger!

Quick Links

Categories

DataTables