ExcelファイルのデータをGoogleスプレッドシートに書き込む

GoogleスプレッドシートをExcelファイルでダウンロードします。またアップロードしたExcelファイルでGoogleスプレッドシートに書き込みます。

動作確認はこちらから

今回はExcelファイルのデータをGoogleスプレッドシートに書き込んでみます。

前回同様にGoogle Cloud PlatformにおけるProjectの作成、Google Sheet APIの有効化、サービスアカウントの作成と認証情報を含んだJSONファイルのダウンロードをしておく必要があります。前回のダウンロードしたJSONファイルをそのまま使ってもかまいません。

最初にGoogleスプレッドシートのデータをExcelファイルとしてダウンロードする処理をやってみましょう。

excelファイルをphpで扱うにはphpspreadsheetが必要です。composerがインストールされているのであればコマンドラインから

composer require phpoffice/phpspreadsheet

1	composer require phpoffice/phpspreadsheet

これでインストール完了です。ファイル数は900個くらい。サイズは6MBくらいです。これを前回使用したGoogle Api Clientのフォルダといっしょにサーバーにアップロードしておきます。ワードプレスがインストールされているディレクトリのなかにsampleというディレクトリを作成し、そのなかへアップロードしました。

sampleディレクトリのなかにspreadsheet-excelというディレクトリをつくり、そのなかに以下のようなファイルを作成します。

これはスプレッドシートのA1:E5の範囲を取得し、excelファイルとしてダウンロードするためのコードです。

download.php

<?php
	$spreadsheet_id = "XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX";
    $sheetName = "シート名";
    $keyFile = __DIR__. "/XXXX.json"; // ダウンロードしたファイルを指定する

    require '../google-api-php-client/vendor/autoload.php';
    require '../phpspreadsheet/vendor/autoload.php';

    // アカウント認証情報インスタンスを作成
    $client = new Google_Client();
    $client->setAuthConfig($keyFile);

    //任意名
    $client->setApplicationName("Sheet API TEST");

    //サービスの権限スコープ
    $scopes = [Google_Service_Sheets::SPREADSHEETS];
    $client->setScopes($scopes);

    //シート情報を操作するインスタンスを生成
    $sheet = new Google_Service_Sheets($client);

    $range = $sheetName.'!A1:E5'; // 取得する範囲

    $response = $sheet->spreadsheets_values->get($spreadsheet_id, $range);
    $values = $response->getValues();

    $excel_book = new \PhpOffice\PhpSpreadsheet\Spreadsheet();
    $excel_sheet = $excel_book->getSheet(0);

    //fromArray(セットする配列, セットしない値, 開始場所のセル)
    $excel_sheet->fromArray($values, null, 'A1');

    header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
    header('Content-Disposition: attachment;filename="myfile.xlsx"');
    header('Cache-Control: max-age=0');

    $writer = new PhpOffice\PhpSpreadsheet\Writer\Xlsx($excel_book);
    $writer->save('php://output');

<?php

$spreadsheet_id = "XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX";

$sheetName = "シート名";

$keyFile = __DIR__. "/XXXX.json"; // ダウンロードしたファイルを指定する

require '../google-api-php-client/vendor/autoload.php';

require '../phpspreadsheet/vendor/autoload.php';

// アカウント認証情報インスタンスを作成

$client = new Google_Client();

$client->setAuthConfig($keyFile);

//任意名

$client->setApplicationName("Sheet API TEST");

//サービスの権限スコープ

$scopes = [Google_Service_Sheets::SPREADSHEETS];

$client->setScopes($scopes);

//シート情報を操作するインスタンスを生成

$sheet = new Google_Service_Sheets($client);

$range = $sheetName.'!A1:E5'; // 取得する範囲

$response = $sheet->spreadsheets_values->get($spreadsheet_id, $range);

$values = $response->getValues();

$excel_book = new \PhpOffice\PhpSpreadsheet\Spreadsheet();

$excel_sheet = $excel_book->getSheet(0);

//fromArray(セットする配列, セットしない値, 開始場所のセル)

$excel_sheet->fromArray($values, null, 'A1');

header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');

header('Content-Disposition: attachment;filename="myfile.xlsx"');

header('Cache-Control: max-age=0');

$writer = new PhpOffice\PhpSpreadsheet\Writer\Xlsx($excel_book);

$writer->save('php://output');

アップロードしたphpファイルにアクセスするとスプレッドシートの内容がexcelファイルとしてダウンロードされます。注意する点としてecho文でどのようなデータが取得できるか表示させてみたいのですが、これをするとダウンロードされるファイルに変更が加えられ、開こうとしてもうまくいかなくなることです。余計なものは出力してはいけません。

つぎにexcelファイルをアップロードして、このデータをスプレッドシートに追加する方法を考えます。

まずはindex.htmlを作成します。Downloadリンクをクリックすると上記のダウンロードの処理が実行されます。アップロードするとup.phpにかかれている処理が実行されます。

index.html

<!DOCTYPE html>
<html lang="ja">
<head>
<meta charset="UTF-8">
<title>GoogleスプレッドシートとExcelファイル</title>
<meta http-equiv="X-UA-Compatible" content="IE=edge">
<meta name="viewport" content="width=device-width, initial-scale=1.0">
</head>

<body>

<p>スプレッドシートをExcelファイルに変換してダウンロードする<br />
<a href="download.php" target="_blank" rel="noopener">Download</a></p>

<form action="up.php" method="post" enctype="multipart/form-data">
  <input type="file" name="fname">
  <input type="submit" value="UPLOAD">
</form>

</body>
</html>

<!DOCTYPE html>

<head>

<title>GoogleスプレッドシートとExcelファイル</title>

</head>

<body>

<p>スプレッドシートをExcelファイルに変換してダウンロードする<br />

<a href="download.php" target="_blank" rel="noopener">Download</a></p>

</form>

</body>

</html>

アップロードするとup.phpにかかれている処理が実行されるのですが、どのような処理が実行されるのでしょうか？

ファイルをアップロードすると自作のgetValueArray関数によってファイルのデータが配列として取得され、setDataToSpreadsheet自作関数によって配列がスプレッドシートに書き込まれます。

up.php

<?php
$tempfile = $_FILES['fname']['tmp_name'];
$filename = './' . $_FILES['fname']['name'];

$spreadsheet_id = "XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX";
$sheetName = "シート名";
$keyFile = __DIR__. "/XXXX.json"; // ダウンロードしたファイルを指定する

if (is_uploaded_file($tempfile)) {
    if ( move_uploaded_file($tempfile , $filename )) {
        try{
            $array = getValueArray($filename);
            setDataToSpreadsheet($spreadsheet_id, $SheetName, $array);
            unlink($filename);
            echo "書き込みました。";
        }
        catch (\Exception $e){
            unlink($filename);
            echo "処理はできませんでした。";
        }
    } else {
        echo "ファイルをアップロードできません。";
    }
} else {
    echo "ファイルが選択されていません。";
}

// getValueArray関数とsetDataToSpreadsheet関数は後述

<?php

$tempfile = $_FILES['fname']['tmp_name'];

$filename = './' . $_FILES['fname']['name'];

$spreadsheet_id = "XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX";

$sheetName = "シート名";

$keyFile = __DIR__. "/XXXX.json"; // ダウンロードしたファイルを指定する

if (is_uploaded_file($tempfile)) {

if ( move_uploaded_file($tempfile , $filename )) {

try{

$array = getValueArray($filename);

setDataToSpreadsheet($spreadsheet_id, $SheetName, $array);

unlink($filename);

echo "書き込みました。";

}

catch (\Exception $e){

unlink($filename);

echo "処理はできませんでした。";

}

} else {

echo "ファイルをアップロードできません。";

}

} else {

echo "ファイルが選択されていません。";

}

// getValueArray関数とsetDataToSpreadsheet関数は後述

ファイルのデータが配列として取得するgetValueArray関数を示します。

取得したデータがnullのとき書き込み処理でエラーになるので空白文字をいれています。

up.php

function getValueArray($filename)
{
    require '../phpspreadsheet/vendor/autoload.php';
    $filePath = $filename;
    $reader = new PhpOffice\PhpSpreadsheet\Reader\Xlsx();
    $excel_book = $reader->load($filePath);
    $excel_sheet = $excel_book->getSheet(0);

    $str = "";
    $array1 = array();

    foreach( $excel_sheet->getRowIterator() as $row ){
        $array2 = array();
        foreach( $excel_sheet->getColumnIterator() as $column ){
            $str = $excel_sheet->getCell( $column->getColumnIndex().$row->getRowIndex() )->getValue();
			if($str != null)
	            $array2[] = $str;
			else
	            $array2[] = "";
        }
        $array1[] = $array2;
    }
    return $array1;
}

function getValueArray($filename)

{

require '../phpspreadsheet/vendor/autoload.php';

$filePath = $filename;

$reader = new PhpOffice\PhpSpreadsheet\Reader\Xlsx();

$excel_book = $reader->load($filePath);

$excel_sheet = $excel_book->getSheet(0);

$str = "";

$array1 = array();

foreach( $excel_sheet->getRowIterator() as $row ){

$array2 = array();

foreach( $excel_sheet->getColumnIterator() as $column ){

$str = $excel_sheet->getCell( $column->getColumnIndex().$row->getRowIndex() )->getValue();

if($str != null)

$array2[] = $str;

else

$array2[] = "";

}

$array1[] = $array2;

}

return $array1;

}

次にsetDataToSpreadsheet関数を示します。

up.php

function setDataToSpreadsheet($spreadsheet_id, $SheetName, $array){
    require '../google-api-php-client/vendor/autoload.php';
    $keyFile = __DIR__. "/my-project-php-sheet-2a593b2e9368.json"; // ダウンロードしたファイルを指定する

    // アカウント認証情報インスタンスを作成
    $client = new Google_Client();
    $client->setAuthConfig($keyFile);

    //任意名
    $client->setApplicationName("Sheet API TEST");

    //サービスの権限スコープ
    $scopes = [Google_Service_Sheets::SPREADSHEETS];
    $client->setScopes($scopes);

    //シート情報を操作するインスタンスを生成
    $spreadsheet_service = new Google_Service_Sheets($client);

    $range = $SheetName.'!A1';

    $body = new \Google_Service_Sheets_ValueRange([
        'values' => $array
    ]);

    $params = ['valueInputOption' => 'USER_ENTERED'];
    $result = $spreadsheet_service->spreadsheets_values->append($spreadsheet_id, $range, $body, $params);
}

function setDataToSpreadsheet($spreadsheet_id, $SheetName, $array){

require '../google-api-php-client/vendor/autoload.php';

$keyFile = __DIR__. "/my-project-php-sheet-2a593b2e9368.json"; // ダウンロードしたファイルを指定する

// アカウント認証情報インスタンスを作成

$client = new Google_Client();

$client->setAuthConfig($keyFile);

//任意名

$client->setApplicationName("Sheet API TEST");

//サービスの権限スコープ

$scopes = [Google_Service_Sheets::SPREADSHEETS];

$client->setScopes($scopes);

//シート情報を操作するインスタンスを生成

$spreadsheet_service = new Google_Service_Sheets($client);

$range = $SheetName.'!A1';

$body = new \Google_Service_Sheets_ValueRange([

'values' => $array

]);

$params = ['valueInputOption' => 'USER_ENTERED'];

$result = $spreadsheet_service->spreadsheets_values->append($spreadsheet_id, $range, $body, $params);

}

ExcelファイルのデータをGoogleスプレッドシートに書き込む

鳩でも分かるC#管理人からのお願い

コメントについて

管理人のモチベーションアップのために

コメントをどうぞ