Read/Write Excel with CFSPREADSHEET

Posted on 2012-09-08
Medium Priority
Last Modified: 2012-09-09
We are reading a worksheet from an Excel file and then creating a new one with just the single worksheet.    Works great but the data has a lot of commas in it so the output has the double quotation marks to preserve those commas.     What can I do to remove these from the new excel file?    Also - we get a garbage header row that we don't want.    Can I suppress that - the deleting of the row only deletes the data but not the row?

<cfspreadsheet action="read" src="cells\tool.xlsx" sheetname="Export" rows="1-80" columns="1-55" query="Output">

<cfspreadsheet action="write"

Open in new window

Question by:digitalwise
  • 6
  • 2
LVL 52

Expert Comment

ID: 38380112
Try the "excludeHeaderRow" attribute to suppress the header row.  

Edit Which step is creating the quotes or are they already present in the xlsx file? If the quotes are already part of the file - OR they're being added by action="read", I think you just have to remove them manually. Loop through the query and use regex to remove them. Then create the new sheet.
LVL 52

Expert Comment

ID: 38380155
As for the quotes, I'm guessing they're already present in the file. So you'll need a loop to remove them. Something along these lines, where "columnName" is the query column containing the leading/trailing double quotes.

<cfloop query="Output">
      <cfset Output.columnName[currentRow] = reReplace(columnName, "^[""]+|[""]+$", "", "all")>

Edit - Sorry, I just remembered excludeHeaderRow only applies to reading, not writing. If you mean the header row is duplicated like this

                Row 1:  HeaderName
                Row 2:  HeaderName
                Row 3:  data xxxxx
That's a bug. Try using spreadSheetAddRows. It adds a header row, but only once.

<cfset newSheet = SpreadSheetNew("Export")>
<cfset spreadSheetAddRows(newSheet, output)>
<cfset spreadSheetWrite(newSheet, "c:\path\to\newFile.xlsx", true)>

If you don't want ANY header row, try using shift rows to overwrite the headers.

           SpreadsheetShiftRows(spreadsheetObj, start, end, rows)

Author Comment

ID: 38381227
The columns don't have headers so this won't work.  I tried just entering 1 but it didn't strip so that clearly isn't helping.     However, I think that your replacement solution and your writing solution aren't taking each other into consideration.   Not quite sure what needs to be done but they aren't working in conjunction.   also - I have 55 columns I need to do this in.
New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

LVL 52

Expert Comment

ID: 38381251
In my tests the replacement worked perfectly. So either there's something about the quotes I'm misunderstanding or you're using different code on your end. Let's back up a minute to answer some of the earlier questions and get on the same page.

1) Where are these quotes coming from? Are they part of the original file itself OR are they being added by either the "read" or "write" action?

2) As far as the headers, you're saying the sheet you're reading from only contains data. So when you "write" the new file you end up with headers like "COL_1,COL_2, ..." - correct?
LVL 52

Expert Comment

ID: 38381263
Also did you see the updates to the previous responses? And can you post a small example demonstrating your actual vs desired output ? That'll help cut to the chase quicker.

ie   Actual
                Row 1:  COL_1 | COL_2 | COL_3           <=== meaningless header row
                Row 2:  data    | "xxx,xxx" | "yyy xxx"  <=== double quotes in the cells
                Row 3:  .....

    Desired    (no quotes and no header row)

                Row 1:  data    | xxx,xxx | yyy xxx
                Row 2:  .....
LVL 52

Accepted Solution

_agx_ earned 2000 total points
ID: 38381355
I've got to head out. But I've run several tests and it works correctly, so it seems like we're doing something different.  Here's the results of simple test I did with a small sample file:

      Row1: John, Smith      "a,b,c"               <=== has embedded quotes
      Row2: Rob Moore      123,456,789
      Row3: "Jenkins, Alan"      456 street

BEFORE - query
  COL_1 COL_2
1 "John, Smith"  ""a,b,c""  
2 Rob Moore  "123,456,789"  
3 ""Jenkins, Alan""  456 street  

AFTER - query
  COL_1 COL_2
1 John, Smith  a,b,c                            <=== quotes are gone
2 Rob Moore  123,456,789  
3 Jenkins, Alan  456 street  

Row1: John, Smith      a,b,c            <=== no quotes or header
Row2: Rob Moore      123,456,789
Row3: Jenkins, Alan      456 street

<!--- read sample file with 3 rows --->
<cfspreadsheet action="read" src="c:/originalFile.xlsx" sheetname="Export" rows="1-3" query="Output">

<!--- debug show original values --->
<cfdump var="#Output#" label="BEFORE">

<!--- must do the replace on EVERY column that contains the double quotes,
	if it's all columns then use query.columnList --->
<cfloop query="Output">
	<cfloop list="#Output.columnList#" index="colName">
		<!--- this replaces leading/traling double quotes ONLY --->
      	<cfset Output[colName][currentRow] = reReplace(Output[colName][currentRow], "^[""]+|[""]+$", "", "all")>

<!--- debug, show fixed data values ---->
<cfdump var="#Output#" label="AFTER">

<!--- write fixed data to workbook on row 1 ---->
<cfset newSheet = SpreadSheetNew("Export", true)>
<cfset spreadSheetAddRows(newSheet, output)>
<cfset spreadSheetWrite(newSheet, "c:/newFile.xlsx", true)>

Open in new window


Author Closing Comment

ID: 38381395
I hope you are having a beer!   Wish I could buy you one!!    This is perfect.
LVL 52

Expert Comment

ID: 38381604
Kewl, glad it helped :)

Featured Post

Upgrade your Question Security!

Add Premium security features to your question to ensure its privacy or anonymity. Learn more about your ability to control Question Security today.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Hi, I will be creating today a basic tutorial on how we can create a Mail Custom Function and use it where ever we want. The main advantage about creating a custom function is that we can accommodate a range of arguments to pass to the Function and …
Hi, Even though I have created this Tutorial on My personal Blog, Some people might not able to find my website, So here i am posting it again Today, from the topic it is very clear that i will be showing you here the very basic usage of how we …
Integration Management Part 2
Please read the paragraph below before following the instructions in the video — there are important caveats in the paragraph that I did not mention in the video. If your PaperPort 12 or PaperPort 14 is failing to start, or crashing, or hanging, …

864 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question