• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2070
  • Last Modified:

Read/Write Excel with CFSPREADSHEET

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"
            filename="#application.loadpath#\#Session.NewWorking#"
            query="Output"
            sheetname="Export"
            overwrite="true"> 

Open in new window

0
digitalwise
Asked:
digitalwise
  • 6
  • 2
1 Solution
 
_agx_Commented:
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.
0
 
_agx_Commented:
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")>
</cfloop>

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)
0
 
digitalwiseAuthor Commented:
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.
0
Cloud Class® Course: Ruby Fundamentals

This course will introduce you to Ruby, as well as teach you about classes, methods, variables, data structures, loops, enumerable methods, and finishing touches.

 
_agx_Commented:
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?
0
 
_agx_Commented:
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:  .....
0
 
_agx_Commented:
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:

ORIGINAL EXCEL 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  

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


CODE:
<!--- 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")>
	</cfloop>
</cfloop>

<!--- 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

0
 
digitalwiseAuthor Commented:
I hope you are having a beer!   Wish I could buy you one!!    This is perfect.
0
 
_agx_Commented:
Kewl, glad it helped :)
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Microsoft Windows 7 Basic

This introductory course to Windows 7 environment will teach you about working with the Windows operating system. You will learn about basic functions including start menu; the desktop; managing files, folders, and libraries.

  • 6
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now