We help IT Professionals succeed at work.

Pass displayed table results to another page to export.

MadIce
MadIce asked
on
Medium Priority
176 Views
Last Modified: 2019-10-03
Have a main.cfm page that has an cfinclude to a JavaScript page that retrieves data from a cfc file and returns the results in a datatable and displays on my main.cfm page. The table is always less than 5 rows and can be edited. I also have a submit button that runs cfscript on another page that also retrieves other sets of data and exports to an excel workbook.
So my issue/question is how can I pass my displayed table with changes on the main page to this cfscript page and export with the rest of my data? I assume there's a way to pass the table data to another page. Been searching for a way to pass as is or in a array-structure of some sort.

Using ColdFusion, JavaScript.
Comment
Watch Question

Author

Commented:
So Trying to pass this:

<table id="tblMain" class="display" width="100%" cellspacing="0">
		 	<thead>
	            <tr>
	              <th>Type</th>
	              <th>SerialNumber</th>
	              <th>Product</th>
	            </tr>
	        </thead>
	        <tfoot>
	            <tr>
              <th>Type</th>
	              <th>SerialNumber</th>
	              <th>Product</th>
	            </tr>
	        </tfoot>
		</table>
		
	</div>
	<cfoutput >

	    <form name="TableExport" id="TableExport" action="TableExportAction.cfm" method="post">
        	<INPUT type="hidden" name="Item" VALUE="#Form.Item#">

        <h6>Click on "DownLoad Files" to export to excel for processing</h6>
        <p align="center">
          <input name="btnsubmit" type="submit" class="btnsubmit" value="Download Files">
        </p>
      </form>
    	</cfoutput>

Open in new window


and this would be the page I want to pass it to:

<!DOCTYPE html>
<html lang="en-US">
	<head>
		<meta http-equiv="X-UA-Compatible" content="IE=Edge">
		<meta charset='utf-8'>
			
		<cfparam name="Form.Item" default="">
		

		<cfinvoke component="Comp/ItemPull" method="ItemData" Item='#Form.Item#' returnVariable="qRead">
		
	</head>
	
	<body>
	
		<cfscript>
		    Workbook = SpreadsheetNew("Product");
		    
			spreadsheetAddRow(Workbook, "ProductID
					,Product
					,Method
					,Qty"); 
			
			spreadsheetAddRows(Workbook, qRead); 
			
		   /*Add Table data here*/
		    SpreadSheetCreateSheet(Workbook, "Item");
		    
		    SpreadSheetSetActiveSheet(Workbook, "Item");
		    spreadsheetAddRow(Workbook, "Type
		    	,SerialNumber
		    	,Product"); 
		
		   	spreadsheetAddRows(Workbook, OtherRead); 
		   	
		</cfscript>	
			
		<!---Code to download the excel file in browser--->
		<cfcontent type="application/msexcel">
		<cfheader name="content-disposition" value="attachment; filename=PulledData.xls">
		<cfcontent type="application/msexcel" variable="#spreadsheetReadBinary(workbook)#"   reset="true">	
	
	</body>

</html>

Open in new window

CERTIFIED EXPERT

Commented:
Hi,

If you use Datatables it come with export features so you won't need to send data to another table.
https://datatables.net/extensions/buttons/examples/initialisation/export.html

Author

Commented:
lenamtl thank you for answer. Not sure this will do what I want but maybe I'm not understanding. I can export from the current page but want to pass to where I have my cfscript that pulls other multiple set of data and exports to a single workbook (multiple worksheets) and include these results. This set of data is the only one that the user can alter.
CERTIFIED EXPERT
Most Valuable Expert 2015
Commented:
The table is always less than 5 rows
... also retrieves other sets of data and exports to an excel workbook

If you need something beyond the default export, one possibility is creating a custom button that exports the data as a JSON string and submits it to "yourActionPage.cfm" via your form, or with ajax.

Datatables

		
$('#yourDataTable').DataTable( 
    //... code ...
    , buttons: [
        { text: 'PASS DATA',
          action: function ( e, dt, button, config ) {
              // export data to JSON string
              var data = JSON.stringify( dt.buttons.exportData() );
              // store in hidden field 
              $('#jsonData').val( data );
              // submit to CF
              $('#jsonExport').submit();
          }
      }
    ]			
});

Open in new window


Form

<form id="jsonExport" action="YourActionPage.cfm" method="post">
	<input type="hidden" id="jsonData" name="jsonData" VALUE="">
</form>

Open in new window


YourActionPage.cfm


<!---- Don't need to include <html/header/body> tags on download page --->
<cfparam name="FORM.jsonData" default='{}'>
<!--- deserialize JSON string into CF objects --->
<cfset exportData = deserializeJSON( form.JSONData )>

<cfset Workbook = SpreadsheetNew("Product")>
<!--- add headers from array --->
<cfset SpreadsheetAddRows( Workbook, [ exportData.header ], 1, 1 )>		    
<!--- add data from multi-dimensional array --->
<cfset SpreadsheetAddRows( Workbook, exportData.body, 2, 1 )>		    
			
<!---Trigger download to excel file in browser--->
<cfheader name="content-disposition" value="attachment; filename=PulledData.xls">
<cfcontent type="application/msexcel" variable="#spreadsheetReadBinary(workbook)#" reset="true">	
		

Open in new window

CERTIFIED EXPERT

Commented:
I would put everything on the same page if possible.
Sending table result using Ajax or other method this can result to a lot of data so probably not the best way to do it.

Datatables can save the table setting (row order, filter, column order etc) in localstorage / browser (or you can save that in DB)
then retrieve the table setting to reproduce the table result using the same DB query then add the extra settings.

Also with  Datatables this is possible to join multiple tables so they are related / joined.
CERTIFIED EXPERT
Most Valuable Expert 2015

Commented:
For a small table of 3 columns and < 5 rows,  exporting to json should be fine. However, I agree with lenamtl's comments that it wouldn't be the best approach for larger - or variable sized - data sets.

Author

Commented:
_agx_, to make sure I understand correctly, I should add that button to my current JavaScript/datatable code? I will give this a try.

lenamtl, one of my requirements is to separate code. Can't have any JavaScript or CFScript on the same page.
CERTIFIED EXPERT
Most Valuable Expert 2015

Commented:
@MadIce - From what I remember, your datatable code used the built in export to Excel button like this:

		
buttons: [ {
          extend: 'excel',
          filename: "Whatever_File_Name_You_Want_Here"
}]			

Open in new window


Replace that section with the custom button code above. Note, the example uses "PASS DATA" for the button "text", but it can be anything you want.

Author

Commented:
Thanks for the comments. Sorry for delay. pulled away. I'm trying _agx_ example but getting error on: <cfset exportData = deserializeJSON( form.JSONData )>
JSON parsing failure: Unexpected end of JSON string
Been trying to fix but must be missing something or putting code in wrong spot.

For DataTable code I just added the button code as is.

On my form I changed like below:

Might be the button part in my

	<cfoutput >

	    <form name="TableExport" id="TableExport" action="TableExportAction.cfm" method="post">
        	<INPUT type="hidden" name="Item" VALUE="#Form.Item#">

        <h6>Click on "DownLoad Files" to export to excel for processing</h6>
        <p align="center">
          <input name="btnsubmit" type="submit" class="btnsubmit" value="Download Files">
       	<input type="hidden" id="jsonData" name="jsonData" VALUE="">
        </p>
      </form>
    	</cfoutput>

Open in new window

also tried in it's on form tags. copied and past as provided.

For action page:

			
		<cfparam name="Form.Item" default="">
		<cfinvoke component="Comp/ItemPull" method="ItemData" Item='#Form.Item#' returnVariable="qRead">

		<cfparam name="FORM.jsonData" default='{}'>
	        <cfset exportData = deserializeJSON(form.jsonData)>
		
            <cfscript>
		        Workbook = SpreadsheetNew("Product");
		    
			spreadsheetAddRow(Workbook, "ProductID
					,Product
					,Method
					,Qty"); 
			
			spreadsheetAddRows(Workbook, qRead);

			
		   /*Add Table data here*/
		    SpreadSheetCreateSheet(Workbook, "Item");
		    
		    SpreadSheetSetActiveSheet(Workbook, "Item");
		    <!--- add headers from array --->
                  SpreadsheetAddRows( Workbook, [ exportData.header ], 1, 1) 		    
                 <!--- add data from multi-dimensional array --->
                SpreadsheetAddRows( Workbook, exportData.body, 2, 1 )
		   	
</cfscript>	
			
		<!---Code to download the excel file in browser--->
		<cfcontent type="application/msexcel">
		<cfheader name="content-disposition" value="attachment; filename=PulledData.xls">
		<cfcontent type="application/msexcel" variable="#spreadsheetReadBinary(workbook)#"   reset="true">	
	

Open in new window


I took out the cfset in the SpreadSheet code also. This might be an issue. Was giving an missing token > or /> error. I didn't switch the rest of my code to use cfset. I actually have about five data pulls I export. That was working fine before trying to add this last bit of data. Does this make a difference? will change just to see what happens.
CERTIFIED EXPERT
Most Valuable Expert 2015

Commented:
Might be the button part ...

Yep, the extra button is problem. You don't need a "submit" button in the <form>.  The datatables code already creates one. That extra submit button doesn't do anything to export the data, so it just sends an empty string to the action page. That's why an error occurs. It's like doing this:

              <cfset thisWontWork = deserializeJSON( "" )>

Use the button created by datatables and the deserialization will work, as long as the field names/id's are correct.

Image of button created by datatables

Author

Commented:
_agx_, I'm not even seeing the button so I'm got start over with a test app to see if I can get it running correctly.
CERTIFIED EXPERT

Commented:
Hi,

make sure you have the button plugins https://datatables.net/extensions/buttons/

https://datatables.net/download/
(you need to select the button extension)

and add the JS url under the datatables script url

then if it's fails again check errors in console Chrome right click inspect
CERTIFIED EXPERT
Most Valuable Expert 2015

Commented:

lenamtl wrote:
make sure you have the button plugins

lenamtl's probably right about missing the button plugin.  In addition to their other suggestions, use the original export example as a reference for what's missing/different.
CERTIFIED EXPERT

Commented:
Hi,

Also be aware that only the data that appear on the table will be exported by default,
It may need adjustment if you want different output.

Let say I have 10 rows, I do a search on the table now I have 3 rows that appear in table and click export by default only the result of 3 row will be exported.

You can set it as some column won't be exported if needed, but this is require you to adjust the code.
This is very flexible and powerful but it has some learning curve.

Author

Commented:
The button issue turnout to be a source it didn't like:
<script type="text/javascript" src="https://cdn.datatables.net/1.10.12/js/jquery.dataTables.min.js"></script>

Don't know enough to say why this would be an issue. Now that I'm past that issue, will test the rest.
CERTIFIED EXPERT
Most Valuable Expert 2015

Commented:
It might be blocked.  One thing I forgot to mention is that often EE examples include remote url's for javascript libraries for demo purposes. Just so you can see the code in action. Those url's shouldn't be used in the dev/production code, since they could break or be blocked by the host site.

For your actual code base, download the library to your server (see lenamtl's comment here). Then use the local url in code,

ie. Instead of

<script type="text/javascript" src="https://cdn.datatables.net/1.10.12/js/jquery.dataTables.min.js"></script>

... use

<script type="text/javascript" src="/path/on/yourserver/js/jquery.dataTables.min.js"></script>

Author

Commented:
For production it's done that way. I don't need that particular library. I'm not getting an error but not seeing the exportData. Just getting a blank page when exporting. So trying to test if data is being passed to the action page.
CERTIFIED EXPERT

Commented:
Hi,

I'm confused do you try to use Datatables library or not?

Author

Commented:
lenamtl - Yes I do. That one was from a template. Just needed to remove. Using https://cdn.datatables.net/1.10.19/js/jquery.dataTables.min.js.
It's pulling data and tested that the form data is passing to the action page. Just working on the export to excel part.
CERTIFIED EXPERT

Commented:
Datatables export button required the button extension otherwise the button won't work.
So make sure you load all necessary JS ans CSS
you can download individual file here
https://datatables.net/download/release 

or complete package https://datatables.net/download/index

To make a quick test just use on of the regular export button, does it work?
If it don't work the custom method won't work you need to make the basic export to work first.

Author

Commented:
lenamtl - It is working. I see the button and when I click, it passes the data. I did a cfdump to make sure. Right now, I'm having an issue with this line of code:
<cfset SpreadsheetAddRows( Workbook, exportData.body, 1, 8)>
Just says unexpected error.

Author

Commented:
So the first problem is, it doesn't seem to like null values or empty strings. When I fill in all fields, still not exporting but going to make a change and see if that works.
CERTIFIED EXPERT
Most Valuable Expert 2015

Commented:
>>> <script type="text/javascript" src="https://cdn.datatables.net/1.10.12/js/jquery.dataTables.min.js"></script>

@MadIce -  Weird. The original example (which used that link) worked fine for me and if you click the link, it's clearly valid.  Maybe something was included twice in the new code? Anyway, about the error...

1. cfdump the exportData.body variable.  What does it contain? With version 1.10.12, it contains a multi-dimensional array.
>> Just says unexpected error.
2. CF should include more details.  Could you post a screen shot?
CERTIFIED EXPERT
Most Valuable Expert 2015

Commented:
What happened when you ran the original example (before adding any custom code)?
https://www.experts-exchange.com/questions/29159596/Pass-displayed-table-results-to-another-page-to-export.html#a42951261

Author

Commented:
Everything is working now. I had the rest of my exports inside cfscript tags so I just got rid of the cfset and included it with what I was using. The issue was the empty values. Going to have to setup default values or something to deal with that. The error message didn't show that. I did a search and saw that this was an issue before but supposed to already been fixed in ColdFusion. So not sure. Using 2016 version.
CERTIFIED EXPERT
Most Valuable Expert 2015

Commented:
Hm... I'm not sure where these empty values are - in the JSON string, spreadsheet code, ...?  If you have time, feel free to post an example/screenshot/etc..  I'm curious if there's a simple way around it.

Author

Commented:
Thank you both very much. Very much appreciated. I was considering a work around. Still have a lot to learn and hope to get some training. But have been slowly catching on.

Author

Commented:
Here is the example with the empty strings.
Capture.PNG
CERTIFIED EXPERT
Most Valuable Expert 2015

Commented:
I don't think empty values are a problem, because this example works fine with 2106 + 2018
https://trycf.com/gist/5eff4bfbefdc0fc57a9caa336dd34f62/acf2016?theme=monokai

Might be the mixed data types? Edit: Forget about mixed types. It's only a problem with 2018.
CERTIFIED EXPERT
Most Valuable Expert 2015

Commented:
>The error message didn't show that.

It sounds like robust debugging isn't enabled in the CF Admin. It should almost always be enabled for DEV so  troubleshooting isn't such a guessing game ;-)

See "Enable Robust Exception Information"


https://helpx.adobe.com/coldfusion/developing-applications/developing-cfml-applications/debugging-and-troubleshooting-applications/configuring-debugging-in-the-coldfusion-administrator.html

Author

Commented:
So I checked and got exception "java.lang.ArrayStoreException" on the below line.
SpreadsheetAddRows( Workbook, exportData.body, 2, 1);
It's not because of a null value. My mistake. It's because I have a requirement somewhere the second field has to be changed.

Author

Commented:
I'm kind of lost on this one now. It's not what I thought. If I don't make any change to data that's when I get the error
exception "java.lang.ArrayStoreException" on the below line.
SpreadsheetAddRows( Workbook, exportData.body, 2, 1);

The data is still being passed to the action page. I did a cfdump to make sure. For whatever reason, It doesn't pass with that line of code.
CERTIFIED EXPERT
Most Valuable Expert 2015

Commented:
the error exception "java.lang.ArrayStoreException"

That's only part of the error.  We need to see the whole thing.  If you enabled debugging, you should get a whole screen of information like this:

Image of error message
CERTIFIED EXPERT
Most Valuable Expert 2015

Commented:
Also, can you run this snippet and post the result?

<cfscript>
writeDump( server.coldfusion.productversion );
data = [];
data.append( ["ABC", 1, "" ] );
data.append( ["EFG", "", 2] );
writeDump( data );
Workbook = SpreadSheetNew("Sheet1");
SpreadsheetAddRows( Workbook, data, 2, 1 );
</cfscript>

Open in new window

Author

Commented:
Here's the error message.
With Exceptions
18:27:17.017 - java.lang.ArrayStoreException - in ExportAction.cfm : line 182
ErrorPart1.PNG
ErrorPart2.PNG

Author

Commented:
Got this when running Capture.PNGsnippet
CERTIFIED EXPERT
Most Valuable Expert 2015

Commented:
So it works with a manually constructed 2d array, but not the deserialized one.  This is looking more and more like a CF bug...

I have to head out, but if you could post the JSON that's failing (or the smallest example that does).  It may be related to the values....

If it is a bug, you'll have to use a different approach. Either try looping through the 2d array, adding one row at a time with SpreadsheetAddRow (singular).  If that fails too, you'll have to use a nested loop and add each cell individually with SpreadSheetSetCell. Ugh

Author

Commented:
Was wondering. The first column is the same for each row and I capture for other purposes. Is there a way to loop through that same column and replace with itself?
CERTIFIED EXPERT
Most Valuable Expert 2015

Commented:
1. Loop through the 2d array (i.e. exportData.body) or the workbook cells? Both are possible.

2. The earlier error is likely a bug related to the specific values in your JSON.  Could you post the exportData string? (sanitize any sensitive values first).  If we can reproduce it, we should file a bug report.

3.  Since it's likely a bug, try populating the spreadsheet cells individually.  Try this example and let me know if it works for you:


<cfscript>
	// DEMO: define sample 2d array of data 
	exportData.body  = [];
	exportData.body.append( ["ABC", 1, "" ] );
	exportData.body.append( ["EFG", "", 2] );
	
	// Define workbook 
	Workbook = SpreadSheetNew("Products");
	
        // populate data starting on row=1, col=1
	for (row = 1; row <= exportData.body.len(); row++) 
	{
		rowData = exportData.body[ row ];
		for (col = 1; col <= rowData.len(); col++) 
		{
			cellValue = rowData[ col ];
			SpreadSheetSetCellValue( Workbook, cellValue, row, col );
			writeOutput("<br>row=#row# col=#col#");
		}
	}
</cfscript>
<!---Code to download the excel file in browser--->
<cfheader name="content-disposition" value="attachment; filename=PulledData.xls">
<cfcontent type="application/msexcel" variable="#spreadsheetReadBinary(workbook)#" reset="true">	

Open in new window

Author

Commented:
_agx_,
Thanks again.
That worked. All are exporting. As far as the code data, when I was building a test, I got the same error. I provided a cfdump above. Will that work?
CERTIFIED EXPERT
Most Valuable Expert 2015

Commented:
Great! Glad it's working.

Yes, you did, thanks.  However, the reason I wanted a small sample (in text form), is because the one I came up with didn't fail under the latest update of 2016 (2016,0,12,315717). So either they fixed the problem or .. I got something wrong :-)  

If it was fixed, then at least you know upgrading will let you use the shorter code in the future. If it wasn't (and I can prove it) I'll file a bug report. (I already filed one for 2018).  No worries if you don't have time to do it now.  Whenever you have a free minute.

Here's the CF2016 Example:
https://trycf.com/gist/6b9ac79ed5828ae77977daecabd7e820/acf2016?theme=monokai