Row Grouping success but missing styles
Row Grouping success but missing styles
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;
});
}
Answers
The
excelHtml5
docs list the builtin styles. The buildRow() function takes in a styleNum parameter: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 instyleNum
parameter.Kevin
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
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:
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
I updated the test case to show an example of what I meant. I updated buildRow() with an additional parameter called
cellStyle
.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:
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:
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
can you creatre this chnge in demo, so i can understand and try to add that code in my excel
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
Possibly all you need to do is select the appropriate style number to pass in line 112 of your first code snippet:
Chance the call to buildRow() with the builtin style you want to use, for example:
It just depends on what you want. We will need. a specific example with specific requirements to offer specific suggestions.
Kevin
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/
Your test case doesn't work because of this error:
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
i have that code ready now
https://jsfiddle.net/k7cjbg90/1/
Your test case is getting this error:
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:
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
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)?
Please read the
excelHtml5
docs. Please note this from the docs: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.
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.
https://jsfiddle.net/Lzkapqjb/
Kevin
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
Then in the getTable i called the builddynamicStyles
it seems multiple styles cannot be an option
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: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: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
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
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
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
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
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
I had some time to work with this:
https://jsfiddle.net/3pk8evjs/
I added the new style in the `customize function, line 482:
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:
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:
getTableData takes a new parameter with the appended style index:
Line 257 sets the first cell with this style:
It is somewhat hard coded so feel free to adjust the code as needed to make it more flexible.
Kevin
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
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
In the last example:
https://jsfiddle.net/3pk8evjs/
Setting the
multiSheet
option tofalse
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
@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
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
i want to make it appear like this if multiselect is false
https://prnt.sc/DhrIjJqagb70
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
That is what i said, i need timeout, its too tiring for me now
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