Exporting from ColdFusion to Excel, how to modify

2019-07-31 06:52发布

站内问答 / 前沿技术
770 3 4

I'm exporting from ColdFusion 9 to Excel and I want to set the page orientation and scaling so the exported excel document fits to page and prints landscape. How to accomplish this?

Edit with solution:
Thanks for the assistance. The page-orientation setting worked as advertised.
I used the following hack to get it to fit to page width.

This page contains documentation on the various settings that are possible:
http://msdn.microsoft.com/en-us/library/Aa155477%28office.10%29.aspx

<cfheader name="Content-disposition" value="attachment;filename=export.xls">
<cfcontent type="application/application/vnd.ms-excel">

<!--- 
mso-page-orientation:landscape causes the exported excel spreadsheet to be printed landscape.
Setting Scale=45 causes the printout to fit to page width for me.
Per the documentation, I should be able to set
<x:Print><x:FitWidth>1</x:FitWidth><x:FitHeight>32767</x:FitHeight><x:ValidPrinterInfo/></x:Print>
but it doesn't want to work.
The width and height appear correctly in the Page Setup dialog, but the 'Adjust to' scale size
radio button remains selected instead of the 'Fit to' one page wide by 32767 tall radio button.
--->

<HTML xmlns:x="urn:schemas-microsoft-com:office:excel">
<HEAD>
<STYLE>
  <!--table
  @page {mso-page-orientation:landscape;}
  -->
</STYLE>
  <!--[if gte mso 9]><xml>
   <x:ExcelWorkbook>
    <x:ExcelWorksheets>
     <x:ExcelWorksheet>
      <x:WorksheetOptions>
       <x:Print>
        <x:ValidPrinterInfo/>
        <x:Scale>45</x:Scale>
       </x:Print>
      </x:WorksheetOptions>
     </x:ExcelWorksheet>
    </x:ExcelWorksheets>
   </x:ExcelWorkbook>
  </xml><![endif]--> 
</HEAD>
<BODY>

<cfoutput>
  <cfloop from = "1" to = "#arrayLen(reportItems)#" index = "i">
    <table cellpadding="1" cellspacing="1" bgcolor="dcdcdc" width="100%" border="1">
      ... table contents ...
    </table>
  </cfloop>
</cfoutput>

</BODY>
</HTML>

3条回答
成全新的幸福
2楼-- · 2019-07-31 07:24

IIRC there is nothing baked in. But you can tap into the underlying workbook and use a little POI magic. (Note, these print settings are applied per sheet) 

<!--- get the underlying poi sheet --->
<cfset poiSheet = cfSheetObject.getWorkBook().getSheet("TheSheetName")>
<cfset ps = poiSheet.getPrintSetup()>
<cfset ps.setLandscape(true)>
<!--- fit to one page --->
<cfset ps.setFitHeight(1)>
<cfset ps.setFitWidth(1)>
查看更多
0人赞 添加讨论(0) 举报
【Aperson】
3楼-- · 2019-07-31 07:33

I don't see any attributes to accomplish this in the cfspreadsheet tag. From adobe site:

  <cfspreadsheet  
   action="write" 
   filename = "filepath" 
   format = "csv" 
   name = "text" 
   overwrite = "true | false" 
   password = "password" 
   query = "queryname" 
   sheetname = "text" 
>

You may have to do that after the export in Excel using print ranges or something similar.

Let CF manage exporting the correct data, let Excel handle the formatting/printing.

查看更多
0人赞 添加讨论(0) 举报
时光不老，我们不散
4楼-- · 2019-07-31 07:37

You can use a trick documented on this page:

How to format an Excel workbook while streaming MIME content

Basically, you output a standard HTML table of data that you would like opened as an Excel spreadsheet. You also have to specify the mime-type for Excel, and for good measure I also like to specify the content-disposition header to prompt for a better download file name.

<cfcontent type="application/msexcel"/> 
<cfheader name="content-disposition" value="attachment; filename=myFile.xls">

The key to your specific formatting question, then, is also found in that above link. You need to include a <style> block with the MS Office-specific CSS rule mso-page-orientation:landscape;. From that link:

<style>
  <!--table
  @page
     {mso-header-data:"&CMultiplication Table\000ADate\: &D\000APage &P";
    mso-page-orientation:landscape;}
     br
     {mso-data-placement:same-cell;}

  -->
</style>

This should handle the page orientation question. One thing to note - Office 2007 and newer will warn users about differing content types when opening this file. It's simply an annoyance (that can be disabled with a registry update); everything will still work and function as needed.

查看更多
0人赞 添加讨论(0) 举报
登录 后发表回答
相关问题
查看全部
相关文章
查看全部
收藏的人(4)