PHPアプリケーションの作成
では，
使い勝手を考えると，
必要な処理としては，
- ファイルのアップロードと保存
- MySQLへの接続
- XLSデータから内容の読み込みとMySQLへのデータ書き込み
- CSVファイルの作成
- MySQLからデータの読み込みと一覧表示
このうち， まず， XLSデータを読み込むには， アップロードしたファイル名をcsvに変えたうえで， 部分的に見るとわかりにくいかもしれませんが， 次回は，XLSデータから内容の読み込み
<?php
require_once 'Classes/PHPExcel.php';
require_once 'Classes/PHPExcel/IOFactory.php';
?><?php
##Excel5(Excel97-2003形式)の読み込み
$xlsReader = PHPExcel_IOFactory::createReader('Excel5');
$xlsObject = $xlsReader->load($updir.$xlsfile);
##アクティブなシートを選択
$xlsObject->setActiveSheetIndex(0);
$sheet = $xlsObject->getActiveSheet();
##経費精算書のセルからデータを取得
$month = strftime("%Y/%m/%d",PHPExcel_Shared_Date::ExcelToPHP($sheet->getCell('A4') ->getValue()));
$kosai = $sheet->getCell('C23')->getCalculatedValue();
$kaigi = $sheet->getCell('D23')->getCalculatedValue();
$kotsu = $sheet->getCell('E23')->getCalculatedValue();
$tusin = $sheet->getCell('F23')->getCalculatedValue();
$shomo = $sheet->getCell('G23')->getCalculatedValue();
$tosho = $sheet->getCell('H23')->getCalculatedValue();
$other = $sheet->getCell('I23')->getCalculatedValue();
$gokei = $sheet->getCell('C24')->getCalculatedValue();
##MySQLにデータ登録
$result = mysql_db_query($database, "INSERT INTO sample_table (
month, kosai, kaigi, kotsu, tusin, shomo, tosho, other, gokei, xlsfile, csvfile
) VALUES (
'$month','$kosai','$kaigi','$kotsu','$tusin','$shomo','$tosho','$other','$gokei','$xlsfile','$csvfile'
)" );
if (!$result) { die(mysql_error()); }
?>CSVファイルの作成
<?php
$csvfile = basename($xlsfile,"xls")."csv";
$fp = fopen($updir.$csvfile,"w");
$str =
'"2110",,"","'.$month.'","","","","対象外",0,0,"未払費用","立替経費","","対象外","'.$gokei.'",0,"","","",3,"","","0","0","no"'."\n".
'"2100",,"","'.$month.'","交際費","","","課対仕入込","'.$kosai.'",0,"","","","対象外",0,0,"","","",3,"","","0","0","no"'."\n".
'"2100",,"","'.$month.'","会議費","","","課対仕入込","'.$kaigi.'",0,"","","","対象外",0,0,"","","",3,"","","0","0","no"'."\n".
'"2100",,"","'.$month.'","旅費交通費","","","課対仕入込","'.$kotsu.'",0,"","","","対象外",0,0,"","","",3,"","","0","0","no"'."\n".
'"2100",,"","'.$month.'","通信費","","","課対仕入込","'.$tusin.'",0,"","","","対象外",0,0,"","","",3,"","","0","0","no"'."\n".
'"2100",,"","'.$month.'","消耗品費","","","課対仕入込","'.$shomo.'",0,"","","","対象外",0,0,"","","",3,"","","0","0","no"'."\n".
'"2100",,"","'.$month.'","新聞図書費","","","課対仕入込","'.$tosho.'",0,"","","","対象外",0,0,"","","",3,"","","0","0","no"'."\n".
'"2101",,"","'.$month.'","雑費","","","課対仕入込","'.$other.'",0,"","","","対象外",0,0,"","","",3,"","","0","0","no"'."\n";
$str = mb_convert_encoding($str,"SJIS","UTF-8");
fwrite($fp,$str);
fclose($fp);
?>
