显示数据的页面
dbconnect.php
<
html
>
< head >
< title > 社員マスタ </ title >
< META HTTP - EQUIV = " Content-type " Content = " text-html;Charset=EUC-JP " >
< LINK href = " ../common/CommonStyle.css " rel = stylesheet type = " text/css " >
< script language = " javascript " >
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 >
< body class = " body " >
< form action = " employeeInformation.php " >
< table height = " 100% " width = " 100% " border = " 0 " cellpadding = " 0 " cellspacing = " 0 " >
< tr >
< td >
< table height = " 15% " >
< tr >
< td ></ td >
</ tr >
</ table >
< table class = " TBLSETTING " width = " 100% " border = " 0 " cellpadding = " 0 " cellspacing = " 0 " height = " 70% " >
< tr >
< td colspan = " 5 " valign = " top " >
< div style = " overflow: auto; overflow-x: hidden; border: 0; height: 100%; " class = " body " >
< table class = " TBLSETTING " border = " 1 " width = " 100% " >
< tr >
< td class = " LOCKHEAD LOCKCENTER CELL " width = " 13% " > 社員番号 </ td >
< td class = " LOCKHEAD LOCKCENTER CELL " width = " 20% " > 社員氏名 </ td >
< td class = " LOCKHEAD LOCKCENTER CELL " width = " 20% " > 入社年月日 </ td >
< td class = " LOCKHEAD LOCKCENTER CELL " width = " 7% " > 性別 </ td >
< td class = " LOCKHEAD LOCKCENTER CELL " width = " 40% " > 勤務地 </ 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 >
< table height = " 15% " width = " 100% " border = " 0 " cellpadding = " 0 " cellspacing = " 0 " >
< 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 >
< td width = " 17% " align = " center " >< input type = " button " class = " BUTTON " style = " display: none; " >< br ></ td >
< td width = " 16% " align = " center " >< input type = " button " class = " BUTTON " style = " display: none; " >< br ></ td >
< td width = " 16% " align = " center " >< input type = " button " class = " BUTTON " style = " display: none; " >< br ></ td >
< td width = " 17% " align = " center " >< input type = " button " class = " BUTTON " style = " display: none; " >< br ></ td >
< td width = " 17% " align = " right " >< input type = " button " class = " BUTTON " value = " キャンセル " onclick = " returnmenu() " ></ td >
</ tr >
</ table >
</ td >
</ tr >
</ table >
</ form >
</ body >
</ html >
< head >
< title > 社員マスタ </ title >
< META HTTP - EQUIV = " Content-type " Content = " text-html;Charset=EUC-JP " >
< LINK href = " ../common/CommonStyle.css " rel = stylesheet type = " text/css " >
< script language = " javascript " >
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 >
< body class = " body " >
< form action = " employeeInformation.php " >
< table height = " 100% " width = " 100% " border = " 0 " cellpadding = " 0 " cellspacing = " 0 " >
< tr >
< td >
< table height = " 15% " >
< tr >
< td ></ td >
</ tr >
</ table >
< table class = " TBLSETTING " width = " 100% " border = " 0 " cellpadding = " 0 " cellspacing = " 0 " height = " 70% " >
< tr >
< td colspan = " 5 " valign = " top " >
< div style = " overflow: auto; overflow-x: hidden; border: 0; height: 100%; " class = " body " >
< table class = " TBLSETTING " border = " 1 " width = " 100% " >
< tr >
< td class = " LOCKHEAD LOCKCENTER CELL " width = " 13% " > 社員番号 </ td >
< td class = " LOCKHEAD LOCKCENTER CELL " width = " 20% " > 社員氏名 </ td >
< td class = " LOCKHEAD LOCKCENTER CELL " width = " 20% " > 入社年月日 </ td >
< td class = " LOCKHEAD LOCKCENTER CELL " width = " 7% " > 性別 </ td >
< td class = " LOCKHEAD LOCKCENTER CELL " width = " 40% " > 勤務地 </ 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 >
< table height = " 15% " width = " 100% " border = " 0 " cellpadding = " 0 " cellspacing = " 0 " >
< 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 >
< td width = " 17% " align = " center " >< input type = " button " class = " BUTTON " style = " display: none; " >< br ></ td >
< td width = " 16% " align = " center " >< input type = " button " class = " BUTTON " style = " display: none; " >< br ></ td >
< td width = " 16% " align = " center " >< input type = " button " class = " BUTTON " style = " display: none; " >< br ></ td >
< td width = " 17% " align = " center " >< input type = " button " class = " BUTTON " style = " display: none; " >< br ></ td >
< td width = " 17% " align = " right " >< input type = " button " class = " BUTTON " value = " キャンセル " onclick = " returnmenu() " ></ 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 >
< meta http - equiv = Content - Type content = " text/html; charset=EUC-JP " >
< meta name = ProgId content = Excel . Sheet >
< meta name = Generator content = " Microsoft Excel 11 " >
< link rel = File - List href = " new.php.files/filelist.xml " >
< link rel = Edit - Time - Data href = " new.php.files/editdata.mso " >
< link rel = OLE - Object - Data href = " new.php.files/oledata.mso " >
<!-- [ 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; */ }
tr
{mso - height - source : auto;
mso - ruby - visibility : none;}
col
{mso - width - source : auto;
mso - ruby - visibility : none;}
br
{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 : " MS Pゴシック " , monospace;
mso - font - charset : 128 ;
border : none;
mso - protection : locked visible;
mso - style - name : 標準;
mso - style - id : 0 ;}
td
{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 : " MS Pゴシック " , 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 : " MS ゴシック " , monospace;
mso - font - charset : 128 ;}
. xl25
{mso - style - parent : style0;
font - size : 10 . 0pt;
font - family : " MS ゴシック " , 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 : " MS ゴシック " , 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 : " MS ゴシック " , 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 : " MS ゴシック " , 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 : " MS ゴシック " , 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 : " MS ゴシック " , 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 : " MS ゴシック " , monospace;
mso - font - charset : 128 ;
text - align : center;}
. xl60
{mso - style - parent : style0;
font - size : 10 . 0pt;
font - family : " MS ゴシック " , 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;}
rt
{color : windowtext;
font - size : 6 . 0pt;
font - weight : 400 ;
font - style : normal;
text - decoration : none;
font - family : " MS Pゴシック " , 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 >
< body link = blue vlink = purple class = xl24 >
< table x : str border = 0 cellpadding = 0 cellspacing = 0 style = ' border-collapse:collapse;table-layout:fixed;width:759pt ' >
< col class = xl24 span = 256 style = ' mso-width-source:userset;mso-width-alt:960;width:23pt ' >
< tr style = ' height:12.0pt ' >
<? php
for ( $k = 0 ; $k < $columncount ; $k ++ )
{
echo " <td class=xl24 style='width:23pt'></td> " ;
}
?>
</ tr >
< tr style = ' height:12.0pt ' >
< td colspan = 33 class = xl24 style = ' height:12.0pt;mso-ignore:colspan ' ></ td >
</ tr >
< tr style = ' height:12.0pt ' >
< td class = xl24 style = ' height:12.0pt ' ></ td >
< td colspan = 32 class = xl59 > *** 社員一覧 *** </ td >
</ tr >
< tr style = ' height:12.0pt ' >
< td colspan = 33 class = xl24 style = ' height:12.0pt;mso-ignore:colspan ' ></ td >
</ tr >
< tr style = ' height:12.0pt ' >
< td class = xl24 style = ' height:12.0pt ' ></ td >
< td colspan = 2 class = xl60 style = ' border-right:.5pt solid #969696 ' > 社員番号 </ td >
< td colspan = 8 class = xl60 style = ' border-right:.5pt solid #969696;border-left:none ' > 社員氏名 </ td >
< td colspan = 4 class = xl60 style = ' border-right:.5pt solid #969696;border-left:none ' > 入社年月日 </ td >
< td colspan = 2 class = xl60 style = ' border-right:.5pt solid #969696;border-left:none ' > 性別 </ td >
< td colspan = 16 class = xl60 style = ' border-right:.5pt solid black;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] >
< tr height = 0 style = ' display:none ' >
<? php
for ( $k = 0 ; $k < $columncount ; $k ++ )
{
echo " <td style='width:23pt'></td> " ;
}
?>
</ tr >
<! [ endif ] >
</ table >
</ body >
</ html >
xmlns : x = " urn:schemas-microsoft-com:office:excel "
xmlns = " http://www.w3.org/TR/REC-html40 " >
< head >
< meta http - equiv = Content - Type content = " text/html; charset=EUC-JP " >
< meta name = ProgId content = Excel . Sheet >
< meta name = Generator content = " Microsoft Excel 11 " >
< link rel = File - List href = " new.php.files/filelist.xml " >
< link rel = Edit - Time - Data href = " new.php.files/editdata.mso " >
< link rel = OLE - Object - Data href = " new.php.files/oledata.mso " >
<!-- [ 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; */ }
tr
{mso - height - source : auto;
mso - ruby - visibility : none;}
col
{mso - width - source : auto;
mso - ruby - visibility : none;}
br
{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 : " MS Pゴシック " , monospace;
mso - font - charset : 128 ;
border : none;
mso - protection : locked visible;
mso - style - name : 標準;
mso - style - id : 0 ;}
td
{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 : " MS Pゴシック " , 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 : " MS ゴシック " , monospace;
mso - font - charset : 128 ;}
. xl25
{mso - style - parent : style0;
font - size : 10 . 0pt;
font - family : " MS ゴシック " , 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 : " MS ゴシック " , 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 : " MS ゴシック " , 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 : " MS ゴシック " , 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 : " MS ゴシック " , 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 : " MS ゴシック " , 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 : " MS ゴシック " , monospace;
mso - font - charset : 128 ;
text - align : center;}
. xl60
{mso - style - parent : style0;
font - size : 10 . 0pt;
font - family : " MS ゴシック " , 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;}
rt
{color : windowtext;
font - size : 6 . 0pt;
font - weight : 400 ;
font - style : normal;
text - decoration : none;
font - family : " MS Pゴシック " , 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 >
< body link = blue vlink = purple class = xl24 >
< table x : str border = 0 cellpadding = 0 cellspacing = 0 style = ' border-collapse:collapse;table-layout:fixed;width:759pt ' >
< col class = xl24 span = 256 style = ' mso-width-source:userset;mso-width-alt:960;width:23pt ' >
< tr style = ' height:12.0pt ' >
<? php
for ( $k = 0 ; $k < $columncount ; $k ++ )
{
echo " <td class=xl24 style='width:23pt'></td> " ;
}
?>
</ tr >
< tr style = ' height:12.0pt ' >
< td colspan = 33 class = xl24 style = ' height:12.0pt;mso-ignore:colspan ' ></ td >
</ tr >
< tr style = ' height:12.0pt ' >
< td class = xl24 style = ' height:12.0pt ' ></ td >
< td colspan = 32 class = xl59 > *** 社員一覧 *** </ td >
</ tr >
< tr style = ' height:12.0pt ' >
< td colspan = 33 class = xl24 style = ' height:12.0pt;mso-ignore:colspan ' ></ td >
</ tr >
< tr style = ' height:12.0pt ' >
< td class = xl24 style = ' height:12.0pt ' ></ td >
< td colspan = 2 class = xl60 style = ' border-right:.5pt solid #969696 ' > 社員番号 </ td >
< td colspan = 8 class = xl60 style = ' border-right:.5pt solid #969696;border-left:none ' > 社員氏名 </ td >
< td colspan = 4 class = xl60 style = ' border-right:.5pt solid #969696;border-left:none ' > 入社年月日 </ td >
< td colspan = 2 class = xl60 style = ' border-right:.5pt solid #969696;border-left:none ' > 性別 </ td >
< td colspan = 16 class = xl60 style = ' border-right:.5pt solid black;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] >
< tr height = 0 style = ' display:none ' >
<? php
for ( $k = 0 ; $k < $columncount ; $k ++ )
{
echo " <td style='width:23pt'></td> " ;
}
?>
</ tr >
<! [ endif ] >
</ table >
</ body >
</ html >
效果图
方法比较笨,以后再研究更好的办法。
得道高人请不吝赐教