Is there anyone here familiar with using <br st...
# cfml-general
g
Is there anyone here familiar with using <br style="mso-data-placement:same-cell;" /> to prevent formatting in the data from creating new rows when dumping data into excel?
e
What are you trying to do exactly? I find its easier to dump the data to CSV, but what are you trying to do
g
When putting data into xls I believe it's formatting like <br> tags that cause another complete excel row. The same-cell css makes it just create a new row in the cell.
So I went and stuck a <br style="mso-data-placement:same-cell;" /> after each data output.
Which prevented the formatting from creating new rows in excel. But getting an extra space on every bit of data in the cells.
e
What tag are you using? Are you creating the excel file from scratch, or opening and appending data?
g
Creating the xls with <cfheader name="Content-Disposition" value="attachment;filename=#download_file_name#.xls"> <cfoutput><META http-equiv="Content-Type" content="application/msexcel;">
e
Aww ok, 🙂, anyrate, you will want to create this with tables not breaks. so <table1> <th>header1</th> <td>data 1 in header1</td></table>
g
I do have the data in tables in the xls. The data that people input into various forms in our system may have formatting in it like <br> or ascii carriage returns that cause a new excel row even though they are technically inside a <table><tr><td></td> in the output code to excel.
e
We are not talking DB, we are talking html/ You can output your data from your query to an html table.
Ahh
g
I have data in the database that got entered into forms by clients. Some of this data has formatting. When I output the data in tables to an excel file sometimes that formatting creates a whole new row in the sheet rather than in the cell.
e
So before you generate your output just nuke the HTML, its something like this.
Copy code
<cfloop from="1" to="#ArrayLen(mytable.item)#" index="i">
    <cfset mytable.item[i].description.value = ReplaceList(mytable.item[i].description.value, '<,>', '<,>')>
</cfloop>
g
So I was sticking that <br style="mso-data-placement:same-cell;" /> after each output which fixes the linebreak problem. The issue is that we are getting an extra space in each cell.
e
Did you try using TRIM on the output
g
#trim(this_value)# <br style="mso-data-placement:same-cell;" />
The <br tag is causing the extra space.
e
the br tag is supposed to be a line break, so what you are doing is dumping your data to the cell table, then writing a line break to it, Drop the line break, you do not need it if it's causing an issue.
g
By putting it there it styles any other <br> tags to stay in the same cell. It keeps them from breaking out a whole new row in the excel sheet.
So it fixes that issue.
e
You can Either pre-process your data and strip out html tags, You can find and parse the data, so it cuts it up between many different cells, or you can post-process your output and further remove data. It comes down to what is the data in your database like, and what do you need your output to look like
That is where
Copy code
REReplaceNoCase
comes in handy as you can loop all your data through it and only selectively ignore other data