Document Handling - Hands On 26

gistfile1.txt

<p>
	In this hands on, we are going to import and export data into the blog section using Excel.
</p>
<p>
	<strong>Tags Used</strong>: <a href="http://help.adobe.com/en_US/ColdFusion/10.0/CFMLRef/WSc3ff6d0ea77859461172e0811cbec22c24-7fa1.html" target="_new">&lt;cffile></a>, <a href="http://help.adobe.com/en_US/ColdFusion/10.0/CFMLRef/WSc3ff6d0ea77859461172e0811cbec17cba-7f87.html" target="_new">&lt;cfspreadsheet></a>, <a href="http://help.adobe.com/en_US/ColdFusion/10.0/CFMLRef/WSc3ff6d0ea77859461172e0811cbec22c24-7fe2.html" target="_new">&lt;cfloop></a>, <a href="http://help.adobe.com/en_US/ColdFusion/10.0/CFMLRef/WSc3ff6d0ea77859461172e0811cbec22c24-7ffd.html" target="_new">&lt;cfset></a>, <a href="http://help.adobe.com/en_US/ColdFusion/10.0/CFMLRef/WSc3ff6d0ea77859461172e0811cbec22c24-7ebf.html" target="_new">&lt;cfscript></a>, <a href="http://help.adobe.com/en_US/ColdFusion/10.0/CFMLRef/WSc3ff6d0ea77859461172e0811cbec22c24-7aea.html" target="_new">&lt;cfheader></a>, <a href="http://help.adobe.com/en_US/ColdFusion/10.0/CFMLRef/WSc3ff6d0ea77859461172e0811cbec22c24-7c82.html" target="_new">&lt;cfcontent></a>
</p>	
<p>
	<strong>Functions Used</strong>: <a href="http://help.adobe.com/en_US/ColdFusion/10.0/CFMLRef/WSc3ff6d0ea77859461172e0811cbec22c24-7c61.html" target="_new">getTempDirectory</a>, <a href="http://help.adobe.com/en_US/ColdFusion/10.0/CFMLRef/WSfd7453be0f56bba437188258123092b394c-7ffe.html" target="_new">EntityNew</a>, <a href="http://help.adobe.com/en_US/ColdFusion/10.0/CFMLRef/WS89F68C3F-0F5F-4c1b-88C4-4299A1E7F28A.html" target="_new">EntitySave</a>, <a href="http://help.adobe.com/en_US/ColdFusion/10.0/CFMLRef/WS64D9E5CE-6D02-46f2-98C1-785505BBF20B.html" target="_new">ormFlush</a>, <a href="http://help.adobe.com/en_US/ColdFusion/10.0/CFMLRef/WS32C28934-CDCE-497f-8212-6342141C5846.html" target="_new">EntityLoad</a>, <a href="http://help.adobe.com/en_US/ColdFusion/10.0/CFMLRef/WSc3ff6d0ea77859461172e0811cbec22c24-747b.html" target="_new">spreadsheetNew</a>, <a href="http://help.adobe.com/en_US/ColdFusion/10.0/CFMLRef/WSc3ff6d0ea77859461172e0811cbec22c24-67ad.html" target="_new">spreadsheetAddRow</a>, <a href="http://help.adobe.com/en_US/ColdFusion/10.0/CFMLRef/WSc3ff6d0ea77859461172e0811cbec22c24-676f.html" target="_new">spreadsheetFormatRow</a>, <a href="http://help.adobe.com/en_US/ColdFusion/10.0/CFMLRef/WSc3ff6d0ea77859461172e0811cbec22c24-6790.html" target="_new">spreadsheetAddRows</a>, <a href="http://help.adobe.com/en_US/ColdFusion/10.0/CFMLRef/WSe9cbe5cf462523a0-7b585809122c5a12c54-7fff.html" target="_new">spreadsheetWrite</a> 
</p>	
<p>
	<ol>
		<li>
			First, let's look at the import process. Open up the <span class="code">/www/admin/content/blog/importBlog.cfm</span> file in your code editor.
		</li>
		<li>
			Before importing a spreadsheet, we must first move it to the server. Locate the <span class="code">Upload File</span> comment tag. On the line below, create a <span class="code">&lt;cffile></span> tag with the following attributes:
			<ul>
				<li>
					<strong>action</strong>: upload
				</li>
				<li>
					<strong>destination</strong>: #getTempDirectory()#
				</li>
				<li>
					<strong>fileField</strong>: importFile
				</li>
				<li>
					<strong>nameconflict</strong>: makeunique
				</li>
			</ul>
		</li>
		<li>
			Your code should look similar to this:
<pre>
&lt;cffile action="upload" destination="#getTempDirectory()#" filefield="importFile" nameconflict="makeunique" />
</pre>			
		</li>
		<li>
			Once the file is on the server, we can parse the file and put it into a query variable by using the <span class="code">&lt;cfspreadsheet></span> tag. Locate the <span class="code">Read Spreadsheet</span> comment and create a <span class="code">&lt;cfspreadsheet></span> tag on the line below with the following attributes:
			<ul>
				<li>
					<strong>action</strong>: read
				</li>
				<li>
					<strong>src</strong>: #cffile.serverDirectory#/#cffile.serverfile#
				</li>
				<li>
					<strong>query</strong>: importData
				</li>
				<li>
					<strong>headerRow</strong>: 1
				</li>
				<li>
					<strong>excludeHeaderRow</strong>: true
				</li>
			</ul>
		</li>
		<li>
			Your code should look similar to this:
<pre>
&lt;cfspreadsheet action="read" src="#cffile.serverDirectory#/#cffile.serverfile#" query="importData" headerrow="1" excludeheaderrow="true" />	
</pre>			
		</li>
		<li>
			Once the spreadsheet has been read into a query variable, loop over the query and create a new <span class="code">blogPost</span> entity for each row. To do this, locate the <span class="code">Import Data</span> comment tag and create a new <span class="code">&lt;cfloop></span> tag below it with the following attribute:
			<ul>
				<li>
					<strong>query</strong>: importData
				</li>
			</ul>
		</li>
		<li>
			Inside the <span class="code">&lt;cfloop></span> tag, create a <span class="code">&lt;cfset></span> tag that loads in a new <span class="code">blogPost</span> entity and saves it in a variable called <span class="code">blogPost</span>.
		</li>
		<li>
			Below the <span class="code">&lt;cfset></span>, create new <span class="code">&lt;cfset></span> tags that set the title, summary, body, and date posted values. Note that because the Date Posted column in the spreadsheet has a space in it, you must use bracket notation to access the value rather than dot notation. Your code should look similar to this:
<pre>
&lt;cfloop query="importData">
	&lt;cfset blogPost = EntityNew('blogPost') />
	&lt;cfset blogPost.title = importData.title />
	&lt;cfset blogPost.summary = importData.summary />
	&lt;cfset blogPost.body = importData.body />
	&lt;cfset blogPost.dateposted = importData['Date Posted'] />
&lt;/cfloop>	
</pre>			
		</li>
		<li>
			The next step is to save the entity. Just before the closing <span class="code">&lt;/cfloop></span> tag, create a <span class="code">&lt;cfset></span> tag that calls <span class="code">EntitySave</span> on the <span class="code">blogPost</span> entity.
		</li>
		<li>
			After the closing <span class="code">&lt;/cfloop></span> tag, create another <span class="code">&lt;cfset></span> tag that calls <span class="code">ormFlush()</span>. This will commit all changes to the database. Your final code should look similar to this:
<pre>
&lt;!--- Upload File--->
&lt;cffile action="upload" destination="#getTempDirectory()#" filefield="importFile" nameconflict="makeunique" />

&lt;!--- Read Spreadsheet --->
&lt;cfspreadsheet action="read" src="#cffile.serverDirectory#/#cffile.serverfile#" query="importData" headerrow="1" excludeheaderrow="true" />

&lt;!--- Import Data --->
&lt;cfloop query="importData">
	&lt;cfset blogPost = EntityNew('blogPost') />
	&lt;cfset blogPost.title = importData.title />
	&lt;cfset blogPost.summary = importData.summary />
	&lt;cfset blogPost.body = importData.body />
	&lt;cfset blogPost.dateposted = importData['Date Posted'] />
	&lt;cfset EntitySave(blogPost) />
&lt;/cfloop>

&lt;cfset ormFlush() />
</pre>			
		</li>
		<li>
			Open up the <span class="code">/www/admin/content/blog/importBlog.cfm</span> page in your browser.
		</li>
		<li>
			Select an Excel file and click 'Import'. A template Excel file can be found at: <span class="code">/www/assets/blogImport.xslx</span>.
		</li>
		<li>
			Go to the <span class="code">/www/admin/content/blog/listblogpost.cfm</span> page in your browser and you will see the imported blog posts.
		</li>
		<li>
			Now that the import process is completed, you are going to create an export process. Create a new file called <span class="code">exportBlog.cfm</span> in the <span class="code">/www/admin/content/blog/</span> folder.
		</li>
		<li>
			Open up the <span class="code">/www/admin/content/blog/exportBlog.cfm</span> file in your code editor.
		</li>
		<li>
			For this task, you are going to write some of it in <span class="code">&lt;cfscript></span>, so you will need to create a new <span class="code">&lt;cfscript></span> block.
		</li>
		<li>
			Inside the <span class="code">&lt;cfscript></span>, create a variable called <span class="code">blogPosts</span> that contains all the <span class="code">blogPost</span> entities. You can do this by calling <span class="code">EntityLoad('blogPost')</span>.
		</li>
		<li>
			On the next line, create a new spreadsheet called <span class="code">exportSpreadhseet</span> by calling the <span class="code">SpreadsheetNew</span> function and pass it in a string of 'Blog Posts'. This will name our first sheet Blog Posts. Your code should look similar to this:
<pre>
&lt;cfscript>
	blogPosts = EntityLoad('blogPost');
	exportSpreadsheet = SpreadsheetNew('Blog Posts');
&lt;/cfscript>	
</pre>			
		</li>
		<li>
			Once you have the spreadsheet object created, add a heading row. To do this, call the <span class="code">SpreadsheetAddRow</span> function and pass it in the <span class="code">exportSpreadhseet</span> object with a comma delimited string of the column headings you want. For this example, the code will look similar to:
<pre>
SpreadsheetAddRow(exportSpreadsheet,'ID,Title,Summary,Body,Date Posted');
</pre>			
		</li>
		<li>
			As this row is a header row, you will want to add some styles to denote that. Using the <span class="code">SpreadsheetFormatRow</span> function, format the first row so it is Bold and aligned center. To do this, place the following code below the <span class="code">SpreadsheetAddRow</span> call:
<pre>
SpreadsheetFormatRow(exportSpreadsheet,{bold=true,alignment='Center'},1);
</pre>			
		</li>
		<li>
			Once the header is formatted, add the remaining data to the spreadsheet. Loop over the query and call <span class="code">SpreadsheetAddRow</span> on each iteration and pass in the <span class="code">spreadsheetObject</span>, in this case <span class="code">exportSpreadsheet</span>, and a list of data. The code should look like this:
<pre>
for(blogPost in blogPosts){	
	SpreadsheetAddRow(exportSpreadsheet,'#blogPost.id#,#blogPost.title#,#blogPost.summary#,#blogPost.body#,#blogPost.datePosted#');
}
</pre>			
		</li>
		<li>
			Now that all the data is in the spreadsheet, save the spreadsheet to the server. To do that, call the <span class="code">spreadsheetWrite</span> function and pass it in the <span class="code">spreadsheetObject</span>, which is the path of the file we want it to be written to, and you can choose to overwrite the file that might already be there. In this case, write the file to the servers temp directory and have it overwrite any file that might already exist with the same name by using the following code: 
<pre>
SpreadsheetWrite(exportSpreadsheet,getTempDirectory() & 'blogPosts.xls',true);
</pre>
		</li>
		<li>
			Our completed <span class="code">&lt;cfscript</span>> block should look similar to this:
<pre>
&lt;cfscript>
	blogPosts = EntityLoad('blogPost');
	exportSpreadsheet = SpreadsheetNew('Blog Posts');
	SpreadsheetAddRow(exportSpreadsheet,'ID,Title,Summary,Body,Date Posted');
	SpreadsheetFormatRow(exportSpreadsheet,{bold=true,alignment='Center'},1);
	for(blogPost in blogPosts){	
		SpreadsheetAddRow(exportSpreadsheet,'#blogPost.id#,#blogPost.title#,#blogPost.summary#,#blogPost.body#,#blogPost.datePosted#');
	}
	SpreadsheetWrite(exportSpreadsheet,getTempDirectory() & 'blogPosts.xls',true);
&lt;/cfscript>
</pre>			
		</li>
		<li>
			Now that the spreadsheet has been created, you need to serve it up to the user. Use a <span class="code">&lt;cfheader></span> tag and a <span class="code">&lt;cfcontent></span> tag. First, start with the <span class="code">&lt;cfheader></span> tag, which tells the browser to serve it up in line and what the filename should be. Place the following code after the closing <span class="code">&lt;/cfscript></span> tag:
<pre>
&lt;cfheader name="Content-Disposition" value="inline; filename=blogPosts.xls" />	
</pre>			
		</li>
		<li>
			Finally, use the <span class="code">&lt;cfcontent></span> tag, which tells what file needs to be served and what type of file it is.You can do this by using the following code, which should be placed right after the <span class="code">&lt;cfheader></span> tag:
<pre>
&lt;cfcontent file="#getTempDirectory()#blogPosts.xls" type="vnd.ms-excel" />
</pre>			
		</li>
		<li>
			Your completed file should look similar to this:
<pre>
&lt;cfscript>
	blogPosts = EntityLoad('blogPost');
	exportSpreadsheet = SpreadsheetNew('Blog Posts');
	SpreadsheetAddRow(exportSpreadsheet,'ID,Title,Summary,Body,Date Posted');
	SpreadsheetFormatRow(exportSpreadsheet,{bold=true,alignment='Center'},1);
	for(blogPost in blogPosts){	
		SpreadsheetAddRow(exportSpreadsheet,'#blogPost.id#,#blogPost.title#,#blogPost.summary#,#blogPost.body#,#blogPost.datePosted#');
	}
	SpreadsheetWrite(exportSpreadsheet,getTempDirectory() & 'blogPosts.xls',true);
&lt;/cfscript>	

&lt;cfheader name="Content-Disposition" value="inline; filename=blogPosts.xls" />
&lt;cfcontent file="#getTempDirectory()#blogPosts.xls" type="vnd.ms-excel" />
</pre>			
		</li>
		<li>
			In a browser, navigate to the <span class="code">/www/admin/content/blog/exportBlog.cfm</span> page. You might be prompted to download a file if the download does not start automatically. Open up the Excel file and review the data that has been exported.
		</li>
	</ol>
</p>