Mitch,
I don't know the CF answer. I'm pretty sure that there is one, because I've
seen this topic discussed here before. You might search the archive.
I do, however, know the quick-and-dirty fix for this in Excel. After you
paste your data in, you have blank rows, right? So let's say that you have
five columns of data (columns A through E), where those columns are filled
in the good rows, and blank in the blank rows. In cell F1, enter this
formula:
=IF(ISBLANK(A1),999999999,ROW())
Then copy that formula into ALL cells in column F down to the last row of
data. Select all of the cells that you populated, from F1 down to F{n}.
Then do Edit > Copy. Then, without changing the selection, do Edit > Paste
Special > Values > OK. Now, select all cells from A1 down to F{n}. In
otherwords, select all of your data and blank rows from column A through
column F. Then do Data > Sort, and select column F, ascending. The blank
rows will get pushed the bottom, and the filled rows will come to the top,
in their original order. I use this all the time to clean up ugly,
pasted-in data, and it only takes a minute to do once you've done it a
couple of times. It saves me loads of time.
Hope this helps.
Matthieu
-----Original Message-----
From: Aunger, Mitch [mailto:[EMAIL PROTECTED]]
Sent: Monday, September 02, 2002 5:39 PM
To: CF-Talk
Subject: extra blank lines problem
Hello, (don't you people take american holidays off? ;)
I have a page that exports data to an Excel spreadsheet. I am able to export
all of the data with headers into Excel, however I am experiencing some
difficulty with blank rows in between my rows of data (5 blank rows in
between each to be exact).
Here is my code:
<cfquery name="get_metrics">
select *
from table
</cfquery>
<!--- Set the special character variables --->
<!--- Tab --->
<cfset tabchar = Chr(9)>
<!--- Line Break --->
<cfset newline = Chr(13) & Chr(10)>
<cfcontent type="application/msexcel">
<!--- Write headers to Excel - metrics.xls. This file resides on the server,
opens on the user's desktop. --->
<cfheader name="Content-Disposition" value="filename=metrics_customer.xls">
<cfoutput>CATEGORY#tabchar#TYPE#tabchar#SYSTEM NAME#tabchar#SURVEY
DATE#tabchar#REQUESTOR NAME#tabchar#REQUESTOR PHONE#tabchar#REQUESTOR
DEPT#tabchar#REQUESTOR EMAIL#tabchar#RESPONDANT ORG#tabchar#SLC
PHASE#tabchar#RESPONDANT NAME#tabchar#RESPONDANT PHONE#tabchar#RESPONDANT
EMAIL#tabchar#SITE#tabchar#QUESTION 1#tabchar#QUESTION 1
COMMENT#tabchar#QUESTION 2#tabchar#QUSTION 2 COMMENT#tabchar#QUESTION
3#tabchar#QUESTION 3 COMMENT#tabchar#QUESTION 4#tabchar#QUESTION 4
COMMENT#newline#</cfoutput>
<cfloop query="get_metrics">
<cfif get_metrics.category eq "1">
<cfset cat = "Application Implementation">
<cfelseif get_metrics.category eq "2">
<cfset cat = "Application / Tool Support">
<cfelseif get_metrics.category eq "3">
<cfset cat = "Program Support">
<cfelse>
<cfset cat="SEPG">
</cfif>
<cfif get_metrics.type eq "0">
<cfset typeof = "System">
<cfelse>
<cfset typeof = "Service">
</cfif>
<!--- Line Break --->
<cfset br = Chr(13) & Chr(10)>
<cfoutput>
<!--- Remove all Line Breaks from the fields, replace with a space. --->
<cfset q1_commenta = Replace(q1_comment, br, " ", "ALL")>
<cfset q2_commenta = Replace(q2_comment, br, " ", "ALL")>
<cfset q3_commenta = Replace(q3_comment, br, " ", "ALL")>
<cfset q4_commenta = Replace(q4_comment, br, " ", "ALL")>
</cfoutput>
<!--- Write data to Excel. --->
<cfoutput>#cat##tabchar##typeof##tabchar##system_name##tabchar##dateformat(s
urvey_date, "yyyy mmm
dd")##tabchar##request_name##tabchar##request_phone##tabchar##request_dept##
tabchar##request_email##tabchar##respon_org##tabchar##slc_phase##tabchar##re
spon_name##tabchar##respon_phone##tabchar##respon_email##tabchar##site##tabc
har##q1_response##tabchar##q1_commenta##tabchar##q2_response##tabchar##q2_co
mmenta##tabchar##q3_response##tabchar##q3_commenta##tabchar##q4_response##ta
bchar##q4_commenta#</cfoutput>
</cfloop>
I think the problem is with the cfoutput tags around the cfset tags. Is
there a way to work around this? I have tried using the replace directly in
my variables that I write to Excel, but I get an error.
Any help would be greatly appreciated!
Thanks,
Mitch
______________________________________________________________________
Get the mailserver that powers this list at http://www.coolfusion.com
FAQ: http://www.thenetprofits.co.uk/coldfusion/faq
Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
