Export pivot table

Ask the Community Get Answers
1

Is there a way to export a pivot table? I have a great report, I just need to export it and print it.

2

Hi @Joshua3 - welcome to the forum :waving_hand:

Unfortunately there is no native way to export the values in a Pivot Table. I’m sure it could be achieved with custom code, if there is a developer out there who can help :technologist:

3

@Joshua3 @CarlHolmes is right and we have used a Javacript plugin (table2excel) to download the table as an Excel file.

I can’t lay my hands on that code right now but see How to Convert HTML to Excel using table2excel | Medium

Having said that does printing the webpage not work for you given the report is well formatted already? Can the report be shared with users another way?

4

I copy/paste them into emails and such.

I always start the copy function outside the table and it paste as a Table in email or word.
If I start the copy completely inside the table, it just pastes the text. :man_shrugging:

Works pretty well to copy paste to excel too. I start the copy fully inside the table for that.
Spruce it up a little by Formatting the Data as a Table (CTRL + T) and you have a pretty nice report.

1 Like
5

Hi

Here’s some code Claude quickly generated re-purposing an old Exporting function.

Just click the “Export Pivot Table” button and then double-click the downloaded CSV file and it will open in Excel.

image
image1344×429 15.8 KB

image
image533×205 16.7 KB

image
image405×155 3.32 KB

Just change the view#.

// ******************** Function to export pivot table in CSV format ******************** //
$(document).on(‘knack-view-render.view_XXXX’, function(event, view) {
// Create a container for the export button and notification
var containerHtml = ‘

’ +
‘Export Pivot Table’ +
‘
CSV file has been downloaded successfully.
’ +
‘
’;

// Add the container above the pivot table
$(‘#’ + view.key).prepend(containerHtml);

// Event listener for “Export Pivot Table” button
$(‘#export-pivot-button’).click(function() {
exportPivotTable(view.key);
});
});

// Function to export the pivot table data to CSV
function exportPivotTable(viewKey) {
// Find the pivot table
var pivotTable = $(‘#’ + viewKey + ’ .kn-pivot-table’);
if (pivotTable.length === 0) {
// If .kn-pivot-table selector doesn’t work, try other possible selectors
pivotTable = $(‘#’ + viewKey + ’ table’);
}

if (pivotTable.length === 0) {
alert(“No pivot table found in this view. Please check the table structure.”);
return;
}

// Capture all data from the pivot table
var csvContent = “”;
var headers = ;

// Get the headers - first find the header row
var headerRow = pivotTable.find(‘thead tr’).last(); // Get the last row in the thead which should contain all column headers

// Extract headers
headerRow.find(‘th’).each(function() {
headers.push($(this).text().trim());
});

// Add headers to CSV
csvContent += headers.join(“,”) + “\n”;

// Get the data rows
var dataRows = pivotTable.find(‘tbody tr’);

dataRows.each(function() {
var rowData = ;
var rowCells = $(this).find(‘td, th’); // Include th for row headers

rowCells.each(function() {
  // Handle thousands separators and other formatting
  let cellText = $(this).text().trim();
  
  // If it's a number with commas, remove the commas
  if(!isNaN(cellText.replace(/,/g, ''))) {
    cellText = cellText.replace(/,/g, '');
  }
  
  // Escape commas and quotes for CSV format
  if(cellText.includes(',') || cellText.includes('"')) {
    cellText = '"' + cellText.replace(/"/g, '""') + '"';
  }
  
  rowData.push(cellText);
});

csvContent += rowData.join(",") + "\n";

});

// Check if we have data
if (csvContent.trim() === headers.join(“,”)) {
alert(“No data was found in the pivot table. Please check the structure.”);
return;
}

// Add a BOM (Byte Order Mark) for Excel to interpret the file correctly
var BOM = “\uFEFF”;
csvContent = BOM + csvContent;

// Create a blob for the CSV file
var blob = new Blob([csvContent], { type: ‘text/csv;charset=utf-8’ });
var url = URL.createObjectURL(blob);

// Create and click a link to download the file
var a = document.createElement(‘a’);
a.href = url;
a.download = ‘pivot_table_export.csv’;
document.body.appendChild(a);
a.click();
document.body.removeChild(a);
}

// END ******************** Function to export pivot table in CSV format ******************** //

Regards
Dean

1 Like
6

That’s what I’ve been doing. I need something simple for my non-tech savvy boss so he can run the report when I’m on vacation next month.

1 Like
7

This is perfect! Thanks for sharing it.

1 Like