使用ColdFusion在Excel列中进行文本包装或自动宽度调整

I am pulling data from a database and presenting it in Excel for the customer. The only problem is some of the data is too big for the column, and since the data is dynamic (of course) I basically need to be able to either do some text wrapping or have the width of the columns expand as necesarry.

我正在从数据库中提取数据，并在Excel中为客户提供数据。唯一的问题是有些数据对于列来说太大了，而且由于数据是动态的(当然)，我基本上需要能够进行一些文本包装，或者让列的宽度扩展为必需的。

My code is posted below

我的代码发布在下面

<!---Used for streaming to browser--->
<cfset context = getPageContext()>
<cfset context.setFlushOutput(false)>
<cfset response = context.getResponse().getResponse()>
<cfset out = response.getOutputStream()>
<cfset response.setContentType("application/vnd.ms-excel")>
<cfset response.setHeader("Content-Disposition", "attachment; filename=ERD_Report.xls" )>

<!---Create Workbook and Sheets in workbook--->
<cfset wb = createObject("java","org.apache.poi.hssf.usermodel.HSSFWorkbook").init()/>
<cfset sheet1 = wb.createSheet("ERD Report")/>

<!---Formatting--->
<cfset cellStyleStatic = createObject("java","org.apache.poi.hssf.usermodel.HSSFCellStyle")/>
<cfset cellHSSFFont = createObject("java","org.apache.poi.hssf.usermodel.HSSFFont")/>

<!---Title--->
<cfset fontTitle = wb.createFont()/>
<cfset fontTitle.setFontName("Calibri") />
<cfset fontTitle.setFontHeightInPoints(javacast("int",11))/> 
<cfset styleTitle = wb.createCellStyle()/>
<cfset styleTitle.setfont(fontTitle)/>
<cfset styleTitle.setFillPattern(styleTitle.SOLID_FOREGROUND)/>
<cfset styleTitle.setFillForegroundColor(createObject("java","org.apache.poi.hssf.util.HSSFColor$GREY_25_PERCENT").getIndex())/>
<cfset styleTitle.setBorderBottom(styleTitle.BORDER_THIN)/>
<cfset styleTitle.setBorderLeft(styleTitle.BORDER_THIN)/>
<cfset styleTitle.setBorderRight(styleTitle.BORDER_THIN)/>
<cfset styleTitle.setBorderTop(styleTitle.BORDER_THIN)/>

<!---Regular centered white cell with Borders--->
<cfset styleCenter = wb.createCellStyle()/>
<cfset styleCenter.setAlignment(cellStyleStatic.ALIGN_CENTER)/>

<!---Dates--->
<cfset styleDate = wb.createCellStyle()/>
<cfset styleDate.setDataFormat(createObject("java","org.apache.poi.hssf.usermodel.HSSFDataFormat").getBuiltinFormat("m/d/yy"))/>

<!--- Text Wrapping--->
<cfset sheet1.FormatColumn(6, {textwrap="true"})/>

    <!---Sheet 1 (Numbers)--->
<!---Rows and columns--->
    <cfset row = sheet1.createRow(0)/>
    <cfset sheet1.setColumnWidth(0,6000)/>
    <cfset sheet1.setColumnWidth(1,6000)/>
    <cfset sheet1.setColumnWidth(2,6000)/>
    <cfset sheet1.setColumnWidth(3,6000)/>
    <cfset sheet1.setColumnWidth(4,6000)/>
    <cfset sheet1.setColumnWidth(5,6000)/>
    <cfset sheet1.setColumnWidth(6,6000)/>
    <cfset sheet1.setColumnWidth(7,6000)/>
    <cfset sheet1.setColumnWidth(8,6000)/>
    <cfset sheet1.setColumnWidth(9,6000)/>
    <cfset sheet1.setColumnWidth(10,6000)/>
    <cfset sheet1.setColumnWidth(11,6000)/>
    <cfset sheet1.setColumnWidth(12,6000)/>
    <cfset sheet1.setColumnWidth(13,6000)/>
    <cfset sheet1.setColumnWidth(14,6000)/>
    <cfset sheet1.setColumnWidth(15,6000)/>
    <cfset sheet1.setColumnWidth(16,6000)/>
    <cfset sheet1.setColumnWidth(17,6000)/>
    <cfset sheet1.setColumnWidth(18,6000)/>
    <cfset cell = row.createCell(0)/>
        <cfset cell.setCellValue('Header1')/>
        <cfset cell.setCellStyle(styleTitle)/>
    <cfset cell = row.createCell(1)/>
        <cfset cell.setCellValue('Header2')/>
        <cfset cell.setCellStyle(styleTitle)/>
    <cfset cell = row.createCell(2)/>
        <cfset cell.setCellValue('Header3')/>
        <cfset cell.setCellStyle(styleTitle)/>
    <cfset cell = row.createCell(3)/>
        <cfset cell.setCellValue('Header4')/>
        <cfset cell.setCellStyle(styleTitle)/>
    <cfset cell = row.createCell(4)/>
        <cfset cell.setCellValue('Header5')/>
        <cfset cell.setCellStyle(styleTitle)/>
    <cfset cell = row.createCell(5)/>
        <cfset cell.setCellValue('Header6')/>
        <cfset cell.setCellStyle(styleTitle)/>
    <cfset cell = row.createCell(6)/>
        <cfset cell.setCellValue('Header7')/>
        <cfset cell.setCellStyle(styleTitle)/>
    <!---<cfset cell = row.createCell(7)/>
        <cfset cell.setCellValue('Header8')/>
        <cfset cell.setCellStyle(styleTitle)/>--->
    <cfset cell = row.createCell(7)/>
        <cfset cell.setCellValue('Header9')/>
        <cfset cell.setCellStyle(styleTitle)/>
    <cfset cell = row.createCell(8)/>
        <cfset cell.setCellValue('Header10')/>
        <cfset cell.setCellStyle(styleTitle)/>  
    <cfset cell = row.createCell(9)/>
        <cfset cell.setCellValue('Header11')/>
        <cfset cell.setCellStyle(styleTitle)/>
    <cfset cell = row.createCell(10)/>
        <cfset cell.setCellValue('Header12')/>
        <cfset cell.setCellStyle(styleTitle)/>
    <cfset cell = row.createCell(11)/>
        <cfset cell.setCellValue('Header13')/>
        <cfset cell.setCellStyle(styleTitle)/>
    <cfset cell = row.createCell(12)/>
        <cfset cell.setCellValue('Header14')/>
        <cfset cell.setCellStyle(styleTitle)/>
    <cfset cell = row.createCell(13)/>
        <cfset cell.setCellValue('Header15')/>
        <cfset cell.setCellStyle(styleTitle)/>
    <cfset cell = row.createCell(14)/>
        <cfset cell.setCellValue('Header16')/>
        <cfset cell.setCellStyle(styleTitle)/>
    <cfset cell = row.createCell(15)/>
        <cfset cell.setCellValue('Header17')/>
        <cfset cell.setCellStyle(styleTitle)/>
    <cfset cell = row.createCell(16)/>
        <cfset cell.setCellValue('Header18)/>
        <cfset cell.setCellStyle(styleTitle)/>
    <cfset cell = row.createCell(17)/>
        <cfset cell.setCellValue('Header19')/>
        <cfset cell.setCellStyle(styleTitle)/>
    <cfset cell = row.createCell(18)/>
        <cfset cell.setCellValue('Header20')/>
        <cfset cell.setCellStyle(styleTitle)/>  



<cfloop query="getall">

    <!--- Query Engineer Notes for this Tracking Number --->

    <cfquery name="Eng_Notes" datasource="#request.dsn#">
        SELECT Eng_Notes AS Note
        FROM tbl_ERD_Eng_NOTES
        WHERE ERD_ID = <cfqueryparam cfsqltype="cf_sql_varchar" value="#getAll.ID#" />
    </cfquery>

    <cfset variables.lstNotes = valueList(Eng_Notes.Note, " *** ") />

    <cfset row = sheet1.createRow(javacast("int",#getall.currentrow#))/>   
    <cfset cell = row.createCell(0)/>
        <cfset cell.setCellValue('#getall.info1#')/>
    <cfset cell = row.createCell(1)/>
        <cfset cell.setCellValue('#getall.info2#')/>
    <cfset cell = row.createCell(2)/>
        <cfset cell.setCellValue('#getall.info3#')/>
    <cfset cell = row.createCell(3)/>
        <cfset cell.setCellValue('#getall.info4#')/>
    <cfset cell = row.createCell(4)/>
        <cfset cell.setCellValue('#getall.info5#')/>   
    <cfset cell = row.createCell(5)/>
        <cfset cell.setCellValue('#getall.info6#')/>
              <cfset cell = row.createCell(6)/>
        <cfset cell.setCellValue('#variables.info7#')/>
    <!---<cfset cell = row.createCell(7)/>
        <cfset cell.setCellValue('#getall.info8#')/>--->
    <cfset cell = row.createCell(7)/>
        <cfset cell.setCellValue('#getall.info9#')/>
    <cfset cell = row.createCell(8)/>
        <cfset cell.setCellValue('#getall.info10#')/>
    <cfset cell = row.createCell(9)/>
        <cfset cell.setCellValue('#getall.info11#')/>   
    <cfset cell = row.createCell(10)/>
        <cfset cell.setCellValue('#getall.info12#')/>
    <cfset cell = row.createCell(11)/>
        <cfset cell.setCellValue('#getall.info13#')/>
    <cfset cell = row.createCell(12)/>
        <cfset cell.setCellValue('#getall.info14#')/>
    <cfset cell = row.createCell(13)/>
        <cfset cell.setCellValue('#getall.info13#')/>
    <cfset cell = row.createCell(14)/>
        <cfset cell.setCellValue('#getall.info14#')/>
    <cfset cell = row.createCell(15)/>
        <cfset cell.setCellValue('#getall.info15#')/>
    <cfset cell = row.createCell(16)/>
        <cfset cell.setCellValue('#getall.info16#')/>
    <cfset cell = row.createCell(17)/>
        <cfset cell.setCellValue('#getall.info17#')/>
    <cfset cell = row.createCell(18)/>
        <cfset cell.setCellValue('#getall.info18#')/>            
</cfloop>

I have tried to use the SpreadsheetFormatCell, as posted here, although I am aware that it would only text wrap 1 cell. All that accomplished is for my Excel page to display the HTTP 500 page.

我已经尝试过使用SpreadsheetFormatCell，正如这里所贴的，尽管我知道它只包含文本wrap 1 cell。完成的全部工作是让我的Excel页面显示HTTP 500页面。

I have also tried the SpreadsheetFormatColumn method, as posted here, and got a lot a errors.

我还尝试了在这里发布的SpreadsheetFormatColumn方法，并得到了很多错误。

Any help would be awesome, and if there is another post similar to this one please point me in that direction. There is always the possibility I missed it.

任何帮助都是非常棒的，如果有其他类似的帖子，请告诉我这个方向。我总是有可能错过它。

2 个解决方案

#1

Using ColdFusion 9, right? It's frustrating because the ColdFusion 9 documentation states that it works, but it's horribly broken. I think this may have been fixed in the recent ColdFusion 11, but it still uses Apache POI libraries that are multiple versions old.

使用ColdFusion 9,对吧?这很令人沮丧，因为ColdFusion 9文档说明它是有效的，但是它非常糟糕。我认为这可能在最近的ColdFusion 11中得到了修正，但是它仍然使用了多个版本的Apache POI库。

Try using spreadsheetWrite(). I've found that there are differences when saving using tag versus script:

试着用spreadsheetWrite()。我发现在使用标签和脚本进行保存时存在差异:

http://gamesover2600.tumblr.com/post/65341875453/differences-saving-coldfusion-excel-data-w-tag-vs

NOTE: It'd be great if this type of testing could be done using CFLive.net or TryCF.com, but they don't allow for file generation for security reasons.

注意:如果可以使用CFLive.net或TryCF.com进行此类测试，那就太棒了，但出于安全考虑，它们不允许生成文件。

#2

UPDATE:

更新:

<cfset sheet1.FormatColumn(6, {textwrap="true"})/>

< cfset sheet1。FormatColumn({ textwrap = " true " })/ >

Without knowing more, my guess is that line causes an error. The variable #sheet1# is an instance of the POI class: org.apache.poi.ss.usermodel.Sheet. That class does not contain a method named FormatColumn(..), so it would definitely cause some sort of error. But again, is extremely difficult to troubleshoot errors without an actual error message or even a screen shot.

我不知道更多，我猜想这条线会导致错误。变量#sheet1#是POI类的一个实例:org.apache.poi.ss.usermodel.Sheet。该类不包含名为FormatColumn(..)的方法，因此肯定会导致某种错误。但是，在没有实际的错误消息甚至没有屏幕快照的情况下，对错误进行故障排除是极其困难的。

(If you do not have access to the necessary tools for some reason, I would suggest installing the CF Developer version locally (free). Then you will have full access to the CF Administrator, logs, etcetera and can configure the debug settings however you like.)

(如果由于某些原因您无法访问必要的工具，我建议您在本地安装CF Developer版本(免费)。然后，您可以完全访问CF管理员、日志等，并可以配置调试设置。

That said, I am not sure why you are using all that low level java code in the first place. Typically, you only need to dip into java if you are looking for some extra functionality that CF does not provide, or perhaps to work around a bug. Frankly, I am not yet convinced either one applies here ... Did you ever try the example below "as is"? It worked fine with 9.0.1. YMMV

也就是说，我不确定您为什么要在一开始就使用所有低级的java代码。通常，如果您正在寻找CF没有提供的一些额外功能，或者可能是为了解决某个bug，您只需要使用java。坦率地说，我还不相信其中任何一个适用于这里……你有试过下面的例子吗?它在9.0.1处运行良好。YMMV

I have tried to use the SpreadsheetFormatCell ... All that accomplished is for my Excel page to display the HTTP 500 page.

我尝试过使用SpreadsheetFormatCell……完成的全部工作是让我的Excel页面显示HTTP 500页面。

Can you post the actual code you tried and the error message from the logs (and your full version)? There are definitely some bugs with spreadsheet functions in CF9, but a quick test of "textwrap" worked fine for me with CF9.0.1.

您能将您尝试过的代码和日志中的错误消息(以及您的完整版本)发布吗?CF9中的电子表格函数肯定有一些bug，但是对“textwrap”的快速测试在CF9.0.1中运行良好。

<cfset sheet = spreadSheetNew()>

<!--- enabled wrapping for single cell --->
<cfset SpreadsheetFormatCell(sheet, {textwrap="true"}, 2, 1)>

<!---
   enable wrapping for entire column
<cfset SpreadsheetFormatColumn(sheet, {textwrap="true"}, 2)>
--->

<!--- add text --->
<cfset SpreadSheetSetCellValue(sheet, "Short text", 1, 1)>
<cfset SpreadSheetSetCellValue(sheet, RepeatString("aaa ", 50), 2, 1)>
<cfset SpreadSheetSetCellValue(sheet, "More text", 3, 1)>
<cfset SpreadSheetSetCellValue(sheet, "Short text", 1, 2)>
<cfset SpreadSheetSetCellValue(sheet, "More text", 2, 2)>

<!--- resize column --->
<cfset SpreadSheetSetColumnWidth(sheet, 1, 35)>

<!--- display results --->
<cfheader name="Content-Disposition" value="attachment; filename=test.xls">
<cfcontent type="application/vnd.ms-excel" 
          variable="#SpreadSheetReadBinary(sheet)#">

#1