公司一个变态要求把表格导出为excel,另一个变态用的是个鸟(此鸟专指我一同学)都没听说过的tcl。而tcl对这个的支持就根本没有,网上资料也找不到,尼玛的。
经过几天的折磨,最后发现,导出excel文件的需求很常见,java有poi,c#是微软开发的,调同样微软开发的excel更是简单的一塔,别的语言也都有很多人已经开发出组件或者有很多解决方案了,但是tcl这种狗屁语言,网上资料根本就没有,找到一个tcom,用activetcl可以运行,但是更悲惨的是公司用的是一个openacs的开源项目,又是一个鸟都没听说过的东东,完全无法把tcom配置进去。
后来尝试用js直接前台导出,也算所见即所得了,但是activex只支持IE,并且还要求客户端要安装了excel,我了个擦的。
还有一个方法就是csv,但是这个方法无法设置表格背景颜色啥的,那变态肯定不同意。
所以最终的方法是,excel还有个xml的表示形式,2007 excel,可以直接把后缀名改为zip,解压就能查看里面的东东了,很复杂,所以最后用的是2003版的。此方法下载后的文件后缀名是xml,但是因为用的是excel的语言,所以默认会是excel打开,只是有些功能还是会受到限制。不过总算可以实现下载了,如果只是看的话,足够了,但是如果还想有复杂操作的话,直接另存为xls格式即可。
具体方法是,打开excel,2007或2003都可以,编辑文件,然后在另存为中选择 other formats,在弹出的保存框中,保存类型选择XML Spreadsheet 2003 (*.xml),点击保存即可。
编辑前的文件,无法放上截图,公司不给用破解软件,好像也没别的软件可用了。用文本编辑器打开保存的文件,既可看到代码,把代码贴到新建的文本文件中,后缀名改为xml,打开就能看到到底是啥样子了。
代码如下
<?xml version="1.0"?>
<?mso-application progid="Excel.Sheet"?>
<Workbook xmlns="urn:schemas-microsoft-com:office:spreadsheet"
xmlns:o="urn:schemas-microsoft-com:office:office"
xmlns:x="urn:schemas-microsoft-com:office:excel"
xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet"
xmlns:html="http://www.w3.org/TR/REC-html40">
//DocumentProperties标签的内容是可以删除的
<DocumentProperties xmlns="urn:schemas-microsoft-com:office:office">
<Author>huw</Author>
<LastAuthor>huw</LastAuthor>
<Created>2013-08-07T06:25:56Z</Created>
<Company>GRC</Company>
<Version>12.00</Version>
</DocumentProperties>
//DocumentProperties标签的内容是可以删除的
<ExcelWorkbook xmlns="urn:schemas-microsoft-com:office:excel">
<WindowHeight>6420</WindowHeight>
<WindowWidth>17115</WindowWidth>
<WindowTopX>360</WindowTopX>
<WindowTopY>45</WindowTopY>
<ProtectStructure>False</ProtectStructure>
<ProtectWindows>False</ProtectWindows>
</ExcelWorkbook>
//style 保存的便是excel文件的一些风格样式了,下面的行或者列会通过其ID知道的值类引用
<Styles>
<Style ss:ID="Default" ss:Name="Normal">
<Alignment ss:Vertical="Center"/>
<Borders/>
<Font ss:FontName="宋体" x:CharSet="134" ss:Size="11" ss:Color="#000000"/>
<Interior/>
<NumberFormat/>
<Protection/>
</Style>
<Style ss:ID="s62">
<Font ss:FontName="宋体" x:CharSet="134" ss:Size="11" ss:Color="#000000"
ss:Bold="1"/>
</Style>
</Styles>
<Worksheet ss:Name="Sheet1">
//下面的ss:ExpandedColumnCount="15" ss:ExpandedRowCount="2"可以删除,如果不删除的话,可以把rowcount的值设置的大一点,否则,当行数多于给点的值的时候,文件是打不开的。
<Table ss:ExpandedColumnCount="15" ss:ExpandedRowCount="2" x:FullColumns="1"
x:FullRows="1" ss:DefaultColumnWidth="54" ss:DefaultRowHeight="13.5">
<Row ss:StyleID="s62">
<Cell><Data ss:Type="String">Engineering Issue ID</Data></Cell>
<Cell><Data ss:Type="String">project_nr</Data></Cell>
<Cell><Data ss:Type="String">Submit Date</Data></Cell>
<Cell><Data ss:Type="String">Last update</Data></Cell>
<Cell><Data ss:Type="String">Deadline</Data></Cell>
<Cell><Data ss:Type="String">Submitter</Data></Cell>
<Cell><Data ss:Type="String">I/T</Data></Cell>
<Cell><Data ss:Type="String">Description</Data></Cell>
<Cell><Data ss:Type="String">Priority</Data></Cell>
<Cell><Data ss:Type="String">Persons Responsible</Data></Cell>
<Cell><Data ss:Type="String">Department</Data></Cell>
<Cell><Data ss:Type="String">Comments</Data></Cell>
<Cell><Data ss:Type="String">Status</Data></Cell>
<Cell><Data ss:Type="String">Closed Date</Data></Cell>
<Cell><Data ss:Type="String">Closed By</Data></Cell>
</Row>
<Row>
<Cell><Data ss:Type="String">a</Data></Cell>
<Cell><Data ss:Type="String">a</Data></Cell>
<Cell><Data ss:Type="String">a</Data></Cell>
<Cell><Data ss:Type="String">a</Data></Cell>
<Cell><Data ss:Type="String">a</Data></Cell>
<Cell><Data ss:Type="String">a</Data></Cell>
<Cell><Data ss:Type="String">a</Data></Cell>
<Cell><Data ss:Type="String">a</Data></Cell>
<Cell><Data ss:Type="String">a</Data></Cell>
<Cell><Data ss:Type="String">a</Data></Cell>
<Cell><Data ss:Type="String">a</Data></Cell>
<Cell><Data ss:Type="String">a</Data></Cell>
<Cell><Data ss:Type="String">a</Data></Cell>
<Cell><Data ss:Type="String">a</Data></Cell>
<Cell><Data ss:Type="String">a</Data></Cell>
</Row>
</Table>
//WorksheetOptions标签的内容也是可以删除的,应该使用来记录上次打开时,文本的状态的,比如正在编辑的单元格等等
<WorksheetOptions xmlns="urn:schemas-microsoft-com:office:excel">
<PageSetup>
<Header x:Margin="0.3"/>
<Footer x:Margin="0.3"/>
<PageMargins x:Bottom="0.75" x:Left="0.7" x:Right="0.7" x:Top="0.75"/>
</PageSetup>
<Print>
<ValidPrinterInfo/>
<HorizontalResolution>600</HorizontalResolution>
<VerticalResolution>600</VerticalResolution>
</Print>
<Selected/>
<Panes>
<Pane>
<Number>3</Number>
<RangeSelection>R1</RangeSelection>
</Pane>
</Panes>
<ProtectObjects>False</ProtectObjects>
<ProtectScenarios>False</ProtectScenarios>
</WorksheetOptions>
</Worksheet>
</Workbook>
所以在知道了,这个以后,下面就好做了,http输出嘛,做好同文件,直接http输出就可。支予文件的各个风格,也不需要记,直接用excel编辑后,打开代码看即可。下面附上tcl的代码,万一以后不在公司了,也好备个份。
ad_page_contract {
@author stefan.fritzemeier@grc.cn.com
} {
{return_url "/grc-project/engineering-issues-list"}
}
set date_format "YYYY-MM-DD"
set content "<?xml version="1.0"?>\n"
append content "<?mso-application progid="Excel.Sheet"?>
<Workbook xmlns="urn:schemas-microsoft-com:office:spreadsheet"
xmlns:o="urn:schemas-microsoft-com:office:office"
xmlns:x="urn:schemas-microsoft-com:office:excel"
xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet"
xmlns:html="http://www.w3.org/TR/REC-html40">
<Styles>
<Style ss:ID="Default" ss:Name="Normal">
<Alignment ss:Vertical="Center"/>
<Borders/>
<Font ss:FontName="Arial Unicode MS" x:CharSet="134" ss:Size="11" ss:Color="#000000"/>
<Interior/>
<NumberFormat/>
<Protection/>
</Style>
<Style ss:ID="s63">
<Font ss:FontName="Arial Unicode MS" x:CharSet="134" x:Family="Swiss"
ss:Size="11" ss:Color="#000000" ss:Bold="1"/>
</Style>
</Styles>\n"
#column settings and title row
append content "<Worksheet ss:Name="Sheet1">
<Table ss:ExpandedColumnCount="15" ss:ExpandedRowCount="50000" x:FullColumns="1"
x:FullRows="1" ss:DefaultColumnWidth="54" ss:DefaultRowHeight="13.5">
<Row ss:AutoFitHeight="0" ss:StyleID="s63">
<Cell><Data ss:Type="String">Engineering Issue ID</Data></Cell>
<Cell><Data ss:Type="String">project_nr</Data></Cell>
<Cell><Data ss:Type="String">Submit Date</Data></Cell>
<Cell><Data ss:Type="String">Last update</Data></Cell>
<Cell><Data ss:Type="String">Deadline</Data></Cell>
<Cell><Data ss:Type="String">Submitter</Data></Cell>
<Cell><Data ss:Type="String">I/T</Data></Cell>
<Cell><Data ss:Type="String">Description</Data></Cell>
<Cell><Data ss:Type="String">Priority</Data></Cell>
<Cell><Data ss:Type="String">Persons Responsible</Data></Cell>
<Cell><Data ss:Type="String">Department</Data></Cell>
<Cell><Data ss:Type="String">Comments</Data></Cell>
<Cell><Data ss:Type="String">Status</Data></Cell>
<Cell><Data ss:Type="String">Closed Date</Data></Cell>
<Cell><Data ss:Type="String">Closed By</Data></Cell>
</Row>\n"
set sql "select
ei.*,
im_category_from_id(ei.priority) as priority_name,
im_category_from_id(ei.issue_status) as issue_status_name,
im_name_from_user_id(ei.submitter_id) as submitter_name,
im_name_from_user_id(ei.closer_id) as closer_name,
to_char(ei.submit_date, :date_format) as submit_date_format,
to_char(ei.deadline, :date_format) as deadline,
to_char(ei.closed_date, :date_format) as closed_date_format,
im_project_nr_from_id(ei.project_id) as project_nr,
im_project_name_from_id(ei.project_id) as project_name,
im_cost_center_name_from_id(department_id) as department_name,
to_char(o.last_modified, :date_format) as last_modified
from
grc_project_eng_issues ei,
acs_objects o
where
ei.issue_id = o.object_id"
#append rows
db_foreach sql $sql {
#replace description and comment html tags and put the new string to issue_description_format
regsub -all {<[^{><}]*>} $issue_description "" issue_description_format
regsub -all {<[^{><}]*>} $issue_comments "" issue_comments_format
#get the responsible persons
set responsible_persons ""
set resp_count 0
db_foreach getResponsiblePersons "select im_name_from_user_id(resp.employee_id) as resp_person_name from grc_project_eng_issues_resp resp where resp.issue_id = :issue_id" {
incr resp_count
append responsible_persons "$resp_person_name;"
}
if {$resp_count == 0} {
set responsible_persons " "
}
append content "<Row ss:AutoFitHeight="0">
<Cell><Data ss:Type="String">$issue_id</Data></Cell>
<Cell><Data ss:Type="String">$project_nr</Data></Cell>
<Cell><Data ss:Type="String">$submit_date_format</Data></Cell>
<Cell><Data ss:Type="String">$last_modified</Data></Cell>
<Cell><Data ss:Type="String">$deadline</Data></Cell>
<Cell><Data ss:Type="String">$submitter_name</Data></Cell>
<Cell><Data ss:Type="String">$is_external_p</Data></Cell>
<Cell><Data ss:Type="String">$issue_description_format</Data></Cell>
<Cell><Data ss:Type="String">$priority_name</Data></Cell>
<Cell><Data ss:Type="String">$responsible_persons</Data></Cell>
<Cell><Data ss:Type="String">$department_name</Data></Cell>
<Cell><Data ss:Type="String">$issue_comments_format</Data></Cell>
<Cell><Data ss:Type="String">$issue_status_name</Data></Cell>
<Cell><Data ss:Type="String">$closed_date_format</Data></Cell>
<Cell><Data ss:Type="String">$closer_name</Data></Cell>
</Row>\n"
}
#WorksheetOptions
append content "</Table>
<WorksheetOptions xmlns="urn:schemas-microsoft-com:office:excel">
<Unsynced/>
<Print>
<ValidPrinterInfo/>
<HorizontalResolution>600</HorizontalResolution>
<VerticalResolution>600</VerticalResolution>
</Print>
<Selected/>
<ProtectObjects>False</ProtectObjects>
<ProtectScenarios>False</ProtectScenarios>
</WorksheetOptions>
</Worksheet>
</Workbook>"
set content_type "application/x-xls"
set all_the_headers "HTTP/1.0 200 OK
MIME-Version: 1.0
Content-Disposition: attachment;filename="test.xml"
Content-Type: $content_type; charset=utf-8\r\n"
util_WriteWithExtraOutputHeaders $all_the_headers
ns_startcontent -type "$content_type; charset=utf-8"
#ns_write "\ufeff",此处是utf编码是否without BOM,前面加上去后及时utf编码,不加的话,文件的编码是utf8 without BOM
ns_write $content