Excelでの経費精算を支援するPHP＋MySQLアプリ
前回まででAzure上でPHPとMySQLを動作させる環境が整いましたので、
最近は、
一方、
これらを踏まえ、
- ユーザがExcelを使って経費精算書を作成
（印刷、 押印） - 上長などがサイトにXLSファイルをアップロード
（承認） - 経理担当者がサイトからCSVファイルをダウンロード
- 経理担当者が会計ソフトにCSVファイルをインポート
アプリケーションとしては、
アップロードしたXLSファイルからセルのデータを読み取ってデータベースに格納し、
今回は、
Excelの経費精算書を元にsqlファイルを作成
まず、
また、
ここでは、
これをもとにsqlファイルを作成します。sample_
CREATE TABLE sample_table (
id INTEGER NOT NULL AUTO_INCREMENT,
month DATE,
kosai INTEGER,
kaigi INTEGER,
kotsu INTEGER,
tusin INTEGER,
shomo INTEGER,
tosho INTEGER,
other INTEGER,
gokei INTEGER,
xlsfile VARCHAR(20),
csvfile VARCHAR(20),
primary key (id)
);各科目はint型、
MySQLに登録するには、
mysql -u sample_db_user -p sample_db
なお、
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);
?>