用perl生成带格式的excel（跨平台）

本文介绍如何用perl生成excel,而且excel的样式可以自己事先设计好，不需要在windows下生成。

1.创建一个模板excel

新建一个test.xls文件，打开

自己设计样式如下：

2.把test.xls另存为xml表格 test.xml

3.用文本编辑器打开test.xml,我要介绍要用代码操作的地方

 

4.代码操作完后的结果，注意生成完后的文件的后缀必须改为.xls,这样就可以用excel直接打开了,只要有了模板excel之后，就可以在linux下把文件生成了。

 

5.代码，下面的代码使用时，注意：若是不是在web下使用，请把第1，2个print注释掉。使用时需要把代码保存为UTF-8格式，下面的m***cel="ms"+"excel",至于为什么，你懂的

 

my $filename="test.xls";
@data=([12,8,98],[9,16,99],[7,4,67],[2,64,99],[3,16,67],[1,64,78]);

$RowCount=scalar(@data)+1;

print "Content-type: Application/m***cel\n";
print "Content-Disposition: attachment; filename='$filename'\n\n";

#输出excel格式

print <<EOFEXCELHEAD;

<?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="">
 <DocumentProperties xmlns="urn:schemas-microsoft-com:office:office">
  <Created>1996-12-17T01:32:42Z</Created>
  <LastSaved>2000-11-18T06:53:49Z</LastSaved>
  <Version>11.9999</Version>
 </DocumentProperties>
 <OfficeDocumentSettings xmlns="urn:schemas-microsoft-com:office:office">
  <RemovePersonalInformation/>
 </OfficeDocumentSettings>
 <ExcelWorkbook xmlns="urn:schemas-microsoft-com:office:excel">
  <WindowHeight>4530</WindowHeight>
  <WindowWidth>8505</WindowWidth>
  <WindowTopX>480</WindowTopX>
  <WindowTopY>120</WindowTopY>
  <AcceptLabelsInFormulas/>
  <ProtectStructure>False</ProtectStructure>
  <ProtectWindows>False</ProtectWindows>
 </ExcelWorkbook>
 <Styles>
  <Style ss:ID="Default" ss:Name="Normal">
   <Alignment ss:Vertical="Bottom"/>
   <Borders/>
   <Font ss:FontName="宋体" x:CharSet="134" ss:Size="12"/>
   <Interior/>
   <NumberFormat/>
   <Protection/>
  </Style>
  <Style ss:ID="s23">
   <Font ss:FontName="宋体" x:CharSet="134" ss:Size="12" ss:Color="#FFFFFF"
    ss:Bold="1"/>
   <Interior ss:Color="#000000" ss:Pattern="Solid"/>
  </Style>
  <Style ss:ID="s26">
   <Borders>
    <Border ss:Position="Bottom" ss:LineStyle="Continuous" ss:Weight="1"/>
    <Border ss:Position="Left" ss:LineStyle="Continuous" ss:Weight="1"/>
    <Border ss:Position="Right" ss:LineStyle="Continuous" ss:Weight="1"/>
    <Border ss:Position="Top" ss:LineStyle="Continuous" ss:Weight="1"/>
   </Borders>
  </Style>
  <Style ss:ID="s27">
   <Borders>
    <Border ss:Position="Bottom" ss:LineStyle="Continuous" ss:Weight="1"/>
    <Border ss:Position="Left" ss:LineStyle="Continuous" ss:Weight="1"/>
    <Border ss:Position="Right" ss:LineStyle="Continuous" ss:Weight="1"/>
    <Border ss:Position="Top" ss:LineStyle="Continuous" ss:Weight="1"/>
   </Borders>
   <Interior ss:Color="#CCFFCC" ss:Pattern="Solid"/>
  </Style>
 </Styles>
 <Worksheet ss:Name="Sheet1">
  <Table ss:ExpandedColumnCount="3" ss:ExpandedRowCount="$RowCount" x:FullColumns="1"
   x:FullRows="1" ss:DefaultColumnWidth="54" ss:DefaultRowHeight="14.25">
   <Column ss:AutoFitWidth="0" ss:Width="99.75"/>
   <Column ss:AutoFitWidth="0" ss:Width="82.5"/>
   <Column ss:AutoFitWidth="0" ss:Width="99"/>
   <Row>
    <Cell ss:StyleID="s23"><Data ss:Type="String">响应时间(ms)</Data></Cell>
    <Cell ss:StyleID="s23"><Data ss:Type="String">带宽(Mbps)</Data></Cell>
    <Cell ss:StyleID="s23"><Data ss:Type="String">成功率(%)</Data></Cell>
   </Row>

EOFEXCELHEAD

my $i=0;
for$i(0..$#data){


if($i % 2 == 0){
print <<EOFROW
        <Row>
    <Cell ss:StyleID="s26"><Data ss:Type="Number">$data[$i][0]</Data></Cell>
    <Cell ss:StyleID="s26"><Data ss:Type="Number">$data[$i][1]</Data></Cell>
    <Cell ss:StyleID="s26"><Data ss:Type="Number">$data[$i][2]</Data></Cell>
   </Row>

EOFROW

    }else{
print <<EOFROW
        <Row>
    <Cell ss:StyleID="s27"><Data ss:Type="Number">$data[$i][0]</Data></Cell>
    <Cell ss:StyleID="s27"><Data ss:Type="Number">$data[$i][1]</Data></Cell>
    <Cell ss:StyleID="s27"><Data ss:Type="Number">$data[$i][2]</Data></Cell>
   </Row>

EOFROW

    }
}

print <<EOFEXCELTAIL;
</Table>
  <WorksheetOptions xmlns="urn:schemas-microsoft-com:office:excel">
   <Print>
    <ValidPrinterInfo/>
    <PaperSizeIndex>9</PaperSizeIndex>
    <HorizontalResolution>300</HorizontalResolution>
    <VerticalResolution>300</VerticalResolution>
   </Print>
   <Selected/>
   <Panes>
    <Pane>
     <Number>3</Number>
     <ActiveRow>1</ActiveRow>
    </Pane>
   </Panes>
   <ProtectObjects>False</ProtectObjects>
   <ProtectScenarios>False</ProtectScenarios>
  </WorksheetOptions>
 </Worksheet>
</Workbook>

EOFEXCELTAIL