一个php中的数据输出到excel的例子(database:PostgreSQL)

来源：互联网发布：质量效应知乎编辑：程序博客网时间：2024/06/07 07:26

显示数据的页面

dbconnect.php

<html>

<head>

<title>社員マスタ</title>

function returnmenu()

{

window.opener = null;

window.self.close();

window.open('../menu.php', '_blank', 'height=245, width=370, top=362, left=440, toolbar=no, menubar=no, scrollbars=no, resizable=no,location=no, status=no, titlebar=no');

}

</script>

<?php
$host = "localhost"; // データベースサーバーアドレス

$user = "username"; // データベース登録アカウント

$passwd = "userpassword"; // データベース登録パスワード

$dbname = "databasename"; // データベース名

$tbname = "tablename"; // テーブル名

$conn = pg_connect("host=$host user=$user password=$passwd dbname=$dbname");

if (!$conn){

echo "データベースの接続でエラーが発生しました<BR> ";

exit;

}

$search = "";

$search .= " select ";

$search .= " (case ";

$search .= " when number < 10 then '00'||number ";

$search .= " when number < 100 then '0'||number ";

$search .= " else cast(number as varchar) ";

$search .= " end) as number ";

$search .= " , name ";

$search .= " , (case ";

$search .= " when joining_date is not null then substring(joining_date, 0, 5) || '年' || substring(joining_date, 6, 2) || '月' || substring(joining_date, 9, 2) || '日' ";

$search .= " end) as joining_date ";

$search .= " , (case sex_kbn ";

$search .= " when 0 then '男性' ";

$search .= " else '女性' ";

$search .= " end) as sex_kbn ";

$search .= " , place ";

$search .= " from ";

$search .= $tbname;

$search .= " order ";

$search .= " by number ";

$search .= " ;";

// get the result

$result = pg_query($conn, $search);

if (!$result) {

echo "検索でエラーが発生しました<BR> ";

exit;

}

$num = pg_num_rows($result);

</head>

<tr>

<td>

<tr>

</tr>

</table>

<tr>

<tr>

<td class="LOCKHEAD LOCKCENTER CELL" width="20%">入社年月日</td>

</tr>

<?php

for ($i = 0; $i < $num; $i++)

{

$row = pg_fetch_row($result, $i);

echo " <tr> ";

echo " <td class='LOCKLIST LOCKLEFT CELL' width='13%'>" . $row[0] . "</td> ";

echo " <td class='LOCKLIST LOCKLEFT CELL' width='20%'>" . $row[1] . "</td> ";

echo " <td class='LOCKLIST LOCKCENTER CELL' width='20%'>" . $row[2] . "</td> ";

echo " <td class='LOCKLIST LOCKCENTER CELL' width='7%'>" . $row[3] . "</td> ";

echo " <td class='LOCKLIST LOCKLEFT CELL' width='40%'>" . $row[4] . "</td> ";

echo " </tr> ";

}

pg_close($conn);

</table>

</div>

</td>

</tr>

</table>

<tr>

<td width="17%" align="left"><input type="submit" class="BUTTON" value="帳票出力"

<?php

if ($num != 0)

{

echo "style='display: yes;'";

}

else

{

echo "style='display: none;'";

}

?>></td>

</tr>

</table>

</td>

</tr>

</table>

</form>

</body>

</html>

效果图

输出excel页面

employeeInformation.php

<html xmlns:o="urn:schemas-microsoft-com:office:office"

xmlns:x="urn:schemas-microsoft-com:office:excel"

xmlns="http://www.w3.org/TR/REC-html40">

<head>

<!--[if gte mso 9]><xml>

<o:DocumentProperties>

<o:LastAuthor>jianglangcaijin</o:LastAuthor>

<o:LastPrinted>2007-10-05T07:30:14Z</o:LastPrinted>

<o:Created>1997-01-08T22:48:59Z</o:Created>

<o:LastSaved>2007-10-05T07:30:37Z</o:LastSaved>

<o:Version>11.8132</o:Version>

</o:DocumentProperties>

</xml><![endif]-->

<?php

$file_type = "vnd.ms-excel";

$file_name = "employeeInformation";

$file_ending = "xls";

header("Content-Type: application/$file_type");

header("Content-Disposition: attachment; filename=$file_name.$file_ending");

header("Pragma: no-cache");

header("Expires: 0");

header("content-type:text/html; charset=EUC-JP");

$rowsPerPage = 40; // 毎ページの行数

$columncount = 34; // 毎ページの列数

$pagecount = 0; // 総ページ数

$headerRows = 5; // 印刷タイトル行数

$footerRows = 3; // フッター行数

$host = "localhost"; // データベースサーバーアドレス

$user = "username"; // データベース登録アカウント

$passwd = "userpassword"; // データベース登録パスワード

$dbname = "databasename"; // データベース名

$tbname = "tablename"; // テーブル名

$conn = pg_connect("host=$host user=$user password=$passwd dbname=$dbname");

if (!$conn){

echo "データベースの接続でエラーが発生しました<BR> ";

exit;

}

$search = "";

$search .= " select ";

$search .= " (case ";

$search .= " when number < 10 then '00'||number ";

$search .= " when number < 100 then '0'||number ";

$search .= " else cast(number as varchar) ";

$search .= " end) as number ";

$search .= " , name ";

$search .= " , (case ";

$search .= " when joining_date is not null then substring(joining_date, 0, 5) || '年' || substring(joining_date, 6, 2) || '月' || substring(joining_date, 9, 2) || '日' ";

$search .= " end) as joining_date ";

$search .= " , (case sex_kbn ";

$search .= " when 0 then '男性' ";

$search .= " else '女性' ";

$search .= " end) as sex_kbn ";

$search .= " , place ";

$search .= " from ";

$search .= $tbname;

$search .= " order ";

$search .= " by number ";

$search .= " ;";

// get the result

$result = pg_query($conn, $search);

if (!$result) {

echo "検索でエラーが発生しました<BR> ";

exit;

}

$num = pg_num_rows($result);

if ($num % $rowsPerPage == 0)

{

$pagecount = (int)($num / $rowsPerPage);

}

else

{

$pagecount = (int)($num / $rowsPerPage) + 1;

}

pg_close($conn);

<style>

<!--table

{mso-displayed-decimal-separator:".";

mso-displayed-thousand-separator:",";}

@page

{margin:.2in .79in .2in .79in;

mso-header-margin:.51in;

mso-footer-margin:.51in;

mso-page-orientation:landscape;

mso-horizontal-page-align:center;

/* mso-vertical-page-align:center;*/}

{mso-height-source:auto;

mso-ruby-visibility:none;}

col

{mso-width-source:auto;

mso-ruby-visibility:none;}

{mso-data-placement:same-cell;}

.style0

{mso-number-format:General;

text-align:general;

vertical-align:bottom;

white-space:nowrap;

mso-rotate:0;

mso-background-source:auto;

mso-pattern:auto;

color:windowtext;

font-size:11.0pt;

font-weight:400;

font-style:normal;

text-decoration:none;

font-family:"ＭＳＰゴシック", monospace;

mso-font-charset:128;

border:none;

mso-protection:locked visible;

mso-style-name:標準;

mso-style-id:0;}

{mso-style-parent:style0;

padding-top:1px;

padding-right:1px;

padding-left:1px;

mso-ignore:padding;

color:windowtext;

font-size:11.0pt;

font-weight:400;

font-style:normal;

text-decoration:none;

font-family:"ＭＳＰゴシック", monospace;

mso-font-charset:128;

mso-number-format:General;

text-align:general;

vertical-align:bottom;

border:none;

mso-background-source:auto;

mso-pattern:auto;

mso-protection:locked visible;

white-space:nowrap;

mso-rotate:0;}

.xl24

{mso-style-parent:style0;

font-size:10.0pt;

font-family:"ＭＳゴシック", monospace;

mso-font-charset:128;}

.xl25

{mso-style-parent:style0;

font-size:10.0pt;

font-family:"ＭＳゴシック", monospace;

mso-font-charset:128;

text-align:left;

border-top:.5pt solid #969696;

border-right:none;

border-bottom:.5pt solid #969696;

border-left:.5pt solid #969696;

background:#CCFFFF;

mso-pattern:auto none;}

.xl34

{mso-style-parent:style0;

font-size:10.0pt;

font-family:"ＭＳゴシック", monospace;

mso-font-charset:128;

text-align:center;

border-top:.5pt solid #969696;

border-right:none;

border-bottom:.5pt solid #969696;

border-left:.5pt solid #969696;

background:#CCFFFF;

mso-pattern:auto none;}

.xl38

{mso-style-parent:style0;

font-size:10.0pt;

font-family:"ＭＳゴシック", monospace;

mso-font-charset:128;

text-align:left;

border-top:.5pt solid #969696;

border-right:none;

border-bottom:.5pt solid #969696;

border-left:.5pt solid #969696;}

.xl44

{mso-style-parent:style0;

font-size:10.0pt;

font-family:"ＭＳゴシック", monospace;

mso-font-charset:128;

text-align:center;

border-top:.5pt solid #969696;

border-right:none;

border-bottom:.5pt solid #969696;

border-left:.5pt solid #969696;}

.xl47

{mso-style-parent:style0;

font-size:10.0pt;

font-family:"ＭＳゴシック", monospace;

mso-font-charset:128;

text-align:left;

border-top:.5pt solid #969696;

border-right:none;

border-bottom:.5pt solid windowtext;

border-left:.5pt solid #969696;

background:#CCFFFF;

mso-pattern:auto none;}

.xl57

{mso-style-parent:style0;

font-size:10.0pt;

font-family:"ＭＳゴシック", monospace;

mso-font-charset:128;

text-align:center;

border-top:.5pt solid #969696;

border-right:none;

border-bottom:.5pt solid windowtext;

border-left:.5pt solid #969696;

background:#CCFFFF;

mso-pattern:auto none;}

.xl59

{mso-style-parent:style0;

font-size:10.0pt;

font-family:"ＭＳゴシック", monospace;

mso-font-charset:128;

text-align:center;}

.xl60

{mso-style-parent:style0;

font-size:10.0pt;

font-family:"ＭＳゴシック", monospace;

mso-font-charset:128;

text-align:center;

border-top:.5pt solid windowtext;

border-right:none;

border-bottom:none;

border-left:.5pt solid windowtext;

background:#CCFFCC;

mso-pattern:auto none;}

{color:windowtext;

font-size:6.0pt;

font-weight:400;

font-style:normal;

text-decoration:none;

font-family:"ＭＳＰゴシック", monospace;

mso-font-charset:128;

mso-char-type:katakana;

display:none;}

-->

</style>

<!--[if gte mso 9]><xml>

<x:ExcelWorkbook>

<x:ExcelWorksheets>

<x:ExcelWorksheet>

<x:Name>社員一覧</x:Name>

<x:WorksheetOptions>

<x:DefaultRowHeight>240</x:DefaultRowHeight>

<x:Print>

<x:ValidPrinterInfo/>

<x:PaperSizeIndex>9</x:PaperSizeIndex>

<x:HorizontalResolution>300</x:HorizontalResolution>

<x:VerticalResolution>300</x:VerticalResolution>

</x:Print>

<x:ShowPageBreakZoom/>

<x:PageBreakZoom>100</x:PageBreakZoom>

<x:Selected/>

<x:Panes>

<x:Pane>

<x:Number>3</x:Number>

<x:ActiveRow>0</x:ActiveRow>

<x:ActiveCol>0</x:ActiveCol>

</x:Pane>

</x:Panes>

<x:ProtectContents>False</x:ProtectContents>

<x:ProtectObjects>False</x:ProtectObjects>

<x:ProtectScenarios>False</x:ProtectScenarios>

</x:WorksheetOptions>

<x:PageBreaks>

<x:ColBreaks>

<x:ColBreak>

<x:Column><?php echo $columncount; ?></x:Column>

</x:ColBreak>

</x:ColBreaks>

<x:RowBreaks>

<?php

for ($breakPageIndex = 0; $breakPageIndex < $pagecount; $breakPageIndex++)

{

$breakRow = $headerRows + ($rowsPerPage + $footerRows) * ($breakPageIndex + 1);

echo " <x:RowBreak> ";

echo " <x:Row>". $breakRow ."</x:Row> ";

echo " </x:RowBreak> ";

}

</x:RowBreaks>

</x:PageBreaks>

</x:ExcelWorksheet>

</x:ExcelWorksheets>

<x:WindowHeight>4725</x:WindowHeight>

<x:WindowWidth>8475</x:WindowWidth>

<x:WindowTopX>480</x:WindowTopX>

<x:WindowTopY>30</x:WindowTopY>

<x:AcceptLabelsInFormulas/>

<x:ProtectStructure>False</x:ProtectStructure>

<x:ProtectWindows>False</x:ProtectWindows>

</x:ExcelWorkbook>

<x:ExcelName>

<x:Name>Print_Area</x:Name>

<x:SheetIndex>1</x:SheetIndex>

<?php

$maxRow = $headerRows + ($rowsPerPage + $footerRows) * $pagecount;

<x:Formula>=社員一覧!$A$1:$AH$<?php echo $maxRow; ?></x:Formula>

</x:ExcelName>

<x:ExcelName>

<x:Name>Print_Titles</x:Name>

<x:SheetIndex>1</x:SheetIndex>

<x:Formula>=社員一覧!$1:$5</x:Formula>

</x:ExcelName>

</xml><![endif]-->

</head>

<?php

for ($k = 0; $k < $columncount; $k++)

{

echo "<td class=xl24 style='width:23pt'></td> ";

}

</tr>

</tr>

</tr>

</tr>

<td colspan=4 class=xl60 style='border-right:.5pt solid #969696;border-left:none'>入社年月日</td>

</tr>

<?php

for ($i = 0; $i < $num; $i++)

{

$row = pg_fetch_row($result, $i);

if (((($i + 1) % $rowsPerPage) == 0) || ($i == $num - 1)) // ページ末尾行或はデータの末尾行

{

$pageIndex = (int)($i / $rowsPerPage) + 1; //現在のページ

echo " <tr style='height:12.0pt'> ";

echo " <td class=xl24 style='height:12.0pt'></td> ";

echo " <td colspan=2 class=xl47>" . $row[0] . "</td> ";

echo " <td colspan=8 class=xl47>" . $row[1] . "</td> ";

echo " <td colspan=4 class=xl57>" . $row[2] . "</td> ";

echo " <td colspan=2 class=xl57>" . $row[3] . "</td> ";

echo " <td colspan=16 class=xl47 style='border-right:.5pt solid black;'>" . $row[4] . "</td> ";

echo " </tr> ";

if ($i == $num - 1) // データの末尾行

{

$nullrowcount = $pagecount * $rowsPerPage - $i - 1; // 空白行数

for ($j = 0; $j < $nullrowcount; $j++)

{

echo " <tr style='height:12.0pt'> ";

echo " <td colspan=33 class=xl24 style='height:12.0pt;mso-ignore:colspan'></td> ";

echo " </tr> ";

}

echo " <tr style='height:12.0pt'> ";

echo " <td colspan=33 class=xl24 style='height:12.0pt;mso-ignore:colspan'></td> ";

echo " </tr> ";

echo " <tr style='height:12.0pt'> ";

echo " <td class=xl24 style='height:12.0pt'></td> ";

echo " <td colspan=32 class=xl59>" . $pageIndex . "/" . $pagecount . "</td> ";

echo " </tr> ";

echo " <tr style='height:12.0pt;'> ";

echo " <td colspan=33 class=xl24 style='height:12.0pt;mso-ignore:colspan;'></td> ";

echo " </tr> ";

}

else if ($i % 2 == 0)

{

echo " <tr style='height:12.0pt;'> ";

echo " <td class=xl24 style='height:12.0pt'></td></td> ";

echo " <td colspan=2 class=xl38>" . $row[0] . "</td> ";

echo " <td colspan=8 class=xl38>" . $row[1] . "</td> ";

echo " <td colspan=4 class=xl44>" . $row[2] . "</td> ";

echo " <td colspan=2 class=xl44>" . $row[3] . "</td> ";

echo " <td colspan=16 class=xl38 style='border-right:.5pt solid black;'>" . $row[4] . "</td> ";

echo " </tr> ";

}

else if ($i % 2 == 1)

{

echo " <tr style='height:12.0pt'> ";

echo " <td class=xl24 style='height:12.0pt'></td> ";

echo " <td colspan=2 class=xl25>" . $row[0] . "</td> ";

echo " <td colspan=8 class=xl25>" . $row[1] . "</td> ";

echo " <td colspan=4 class=xl34>" . $row[2] . "</td> ";

echo " <td colspan=2 class=xl34>" . $row[3] . "</td> ";

echo " <td colspan=16 class=xl25 style='border-right:.5pt solid black;'>" . $row[4] . "</td> ";

echo " </tr> ";

}

<![if supportMisalignedColumns]>

<?php

for ($k = 0; $k < $columncount; $k++)

{

echo " <td style='width:23pt'></td> ";

}

</tr>

<![endif]>

</table>

</body>

</html>

效果图

方法比较笨，以后再研究更好的办法。

得道高人请不吝赐教