VB.NET-TIPS などプログラミングについて

2025/02/03

[PR]

×

[PR]上記の広告は3ヶ月以上新規記事投稿のないブログに表示されています。新しい記事を書く事で広告が消えます。

CM

2024/01/12

MySQL 同一構造のテーブルで片方が存在しないデータのコピーの方法について（INSERT ... SELECT ... EXISTS）

トランザクション的なテーブルを、同一構造のワークテーブルにバックアップコピーを取っておいて、ワークテーブルから元のテーブルに差分を復活させたい場合がよくあると思います。

とあるシステムのデバッグを行う時に、元のテーブルに対して削除処理が入る場合、デバッグの前にワークテーブルにバックアップコピーを取っておきます。
その後、デバッグを行っていると削除でデータが無くなるので、データの復活が出来れば、再度最初からデバッグ処理が行えます。
そこで、最初にバックアップを行うSQLから示します。
尚、今回のテストではテーブルの内容については深く考えていませんので、テーブル名は TT_TEST としワークテーブルは TW_TEST とし、データをにはユニークキーとして KEY の名前のカラムが存在するとします。

バックアップを行うSQL

' ワークテーブルの作成
CREATE TABLE TW_TEST LIKE TT_TEST;

' ワークテーブルへのコピー
TRUNCATE TABLE TW_TEST;
INSERT INTO TW_TEST SELECT * FROM TT_TEST;

取敢えず、ワークテーブルの作成についても記しています。
「CREATE TABLE ... LIKE ステートメント」はすでに存在するテーブルのテーブル定義のみをコピーして、新規にテーブルを作成することができます。
なお、オラクルのSQLでも同様のことができるようです。

実テーブルに存在しないワークテーブルのレコードの追加を行うSQL

MySQLにはオラクルの様に集合演算子の MINUS が無いため、ユニークキーの結合で存在しないものを選択し、そのデータをINSERTすることになります。
SQLは以下の様になります。

INSERT INTO TT_TEST
SELECT *
FROM TW_TEST AS T1
WHERE NOT EXISTS (
	SELECT *
	FROM TT_TEST AS T2
	WHERE T1.KEY = T2.KEY
);

PR

MySQL
0CM

2023/11/22

PHP PhpSpreadsheet ワークシート上にチャート(グラフ)を作成する方法のバグについて（displayBlanksAs）

以前、以下の記事などで紹介した内容ですが、PHPのバージョンを「7.4」にアップした時についでにPhpSpreadsheetを「1.25」のバージョンにしたのですが、下のプログラムを実行するとエクセルファイルは作成されますが、チャートはエラーが出て表示されません。
⇒PHP PhpSpreadsheet ワークシート上にチャート(グラフ)を作成する方法について（PhpSpreadsheet\Chart）

該当エクセルファイルを開くとエラーが表示される。

■チャートが表示されないソース

<?php
// ライブラリ読込
require '../vendor/autoload.php';
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Chart\Chart;
use PhpOffice\PhpSpreadsheet\Chart\DataSeries;
use PhpOffice\PhpSpreadsheet\Chart\DataSeriesValues;
use PhpOffice\PhpSpreadsheet\Chart\PlotArea;
use PhpOffice\PhpSpreadsheet\Chart\Title;
use PhpOffice\PhpSpreadsheet\IOFactory;

// Spreadsheetオブジェクト生成
$objSpreadsheet = new Spreadsheet();
// ワークシートオブジェクト取得
$objWorksheet = $objSpreadsheet->getActiveSheet();
//  チャート用テストデータ生成
$objWorksheet->fromArray(
	array(
		array('売上' , 2016, 2017, 2018, 2019, 2020),
		array('商品1',   12,   15,   21,   18,   20),
		array('商品2',   18,   19,   23,   14,   21),   //追加
		array('商品3',   15,   10,   20,   12,   23),   //追加
	)
);

//  X軸ラベルの指定
$arrCategorysDataSeries = array(
	new DataSeriesValues(DataSeriesValues::DATASERIES_TYPE_STRING, 'Worksheet!$B$1:$F$1', NULL, 5),	//	2016 to 2020
);
//  描画データの指定
$arrDataSeriesValues = array(
	new DataSeriesValues(DataSeriesValues::DATASERIES_TYPE_NUMBER, 'Worksheet!$B$2:$F$2', NULL, 5),  //商品1
	new DataSeriesValues(DataSeriesValues::DATASERIES_TYPE_NUMBER, 'Worksheet!$B$3:$F$3', NULL, 5),  //商品2　追加
	new DataSeriesValues(DataSeriesValues::DATASERIES_TYPE_NUMBER, 'Worksheet!$B$4:$F$4', NULL, 5),  //商品3　追加
);
//	チャート・データシリーズの生成
$objSeries = new DataSeries(
	DataSeries::TYPE_LINECHART,                     // plotType
    NULL,                                           // plotGrouping(DataSeries::GROUPING_STANDARD)
	range(0, count($arrDataSeriesValues) - 1),      // plotOrder
	array(),                                        // plotLabel
	$arrCategorysDataSeries,                        // plotCategory
	$arrDataSeriesValues                            // plotValues
);

//	プロットエリアにチャート・データシリーズに設定
$objPlotArea = new PlotArea(NULL, array($objSeries));

//  チャート・タイトル生成
$objTitle = new Title('売上データ');
//	チャート生成
$objChart = new Chart(
	'chart1',       // name
	$objTitle,      // title
	NULL,           // legend
	$objPlotArea,   // plotArea
	TRUE,           // plotVisibleOnly
	0,              // displayBlanksAs
	NULL,           // xAxisLabel
	NULL		    // yAxisLabel
);
//	ワークシート内のチャート位置設定
$objChart->setTopLeftPosition('A6');        // 左上
$objChart->setBottomRightPosition('G18');   // 右下

//	ワークシートにチャート追加
$objWorksheet->addChart($objChart);

// [test-g-1-2.xlsx]:Excel2007形式で保存する
$objWriter = IOFactory::createWriter($objSpreadsheet, 'Xlsx');
$objWriter->setIncludeCharts(TRUE);
$objWriter->save('test-g-1-2.xlsx');
exit();
?>

このソースでチャートを表示させるには「チャート生成:new Chart」の引数の中の「displayBlanksAs」を「0」ではなく以下のどれかを指定する必要があります。

■PhpOffice\PhpSpreadsheet\Chart\DataSeries.php 内の宣言

    const EMPTY_AS_GAP = 'gap';
    const EMPTY_AS_ZERO = 'zero';
    const EMPTY_AS_SPAN = 'span';

よって「チャート生成:new Chart」部分のソースは以下の様にします。

//	チャート生成
$objChart = new Chart(
	'chart1',       // name
	$objTitle,      // title
	NULL,           // legend
	$objPlotArea,   // plotArea
	TRUE,           // plotVisibleOnly
	DataSeries::EMPTY_AS_GAP,              // displayBlanksAs
	NULL,           // xAxisLabel
	NULL		    // yAxisLabel
);

PhpSpreadsheetの記事の中え「チャート生成:new Chart」の引数の中の「displayBlanksAs」を「0」にしているものがほとんどですので、上記の様に読み替えて下さい。

■LightBox2の例

<!DOCTYPE html>
<head>
<link href="./js/lightbox2-2.11.4/css/lightbox.min.css" rel="stylesheet">
<script type="text/javascript" src="./js/jquery-3.6.0/jquery-3.6.0.min.js"></script>
<script src="./js/lightbox2-2.11.4/js/lightbox-plus-jquery.min.js"></script>
<script>
//  lightbox start
$(function() {
	lightbox.option({
		"positionFromTop": 150,
	});
});
</script>

</head>
<body>
<h1>LightBox2-TEST</h1>
<a href="./test-1.jpg" data-lightbox="album" >
  <img src="./test-1.jpg" id="" width="100" >
</a>
</body>
</html>

上のHTMLでは特に難しくはありませんでした。そこで、このHTMLにボタンと、10個の適当な画像ファイル（add-1.jpg ～add-10.jpg）を用意して、そのボタンをクリックすることで、上記の「test-1.jpg」のファイルの後ろに表示する様にしてみます。

画像ファイルの指定を追加しやすくするために、<div id="imgdiv"></div> で囲っています。
この35行は、divタグ、aタグ、imgタグを1行で記述していますが、こうしないと余計なキャプションが付くための対策です。

■LightBox2の例・その２

<!DOCTYPE html>
<head>
<link href="./js/lightbox2-2.11.4/css/lightbox.min.css" rel="stylesheet">
<script type="text/javascript" src="./js/jquery-3.6.0/jquery-3.6.0.min.js"></script>
<script src="./js/lightbox2-2.11.4/js/lightbox-plus-jquery.min.js"></script>

<script>
//  lightbox start
$(function() {
	lightbox.option({
		"positionFromTop": 150,
	});
});

$(function() {
	$("#btn").on('click', function() {
		for (i = 1; i <= 10; i++) {
			var add = "";
			add += '<a href="./add-'+i+'.jpg" data-lightbox="album" >';
			add += '<img src="./add-'+i+'.jpg" width="100" >';
			add += '</a>';
			$("#imgdiv").append(add);
		}

	});

});
</script>

</head>
<body>
<h1>LightBox2-TEST</h1>
<button id="btn">add image</button>  

<div id="imgdiv"><a href="./test-1.jpg" data-lightbox="album"><img src="./test-1.jpg" width="100"></a></div>
</body>
</html>

「add image」をクリックする前の表示は以下の通りです。

「add image」を2回クリックした後の表示は以下の様になります。

画像のどれかをクリックすると「lightbox2」が機能し、画像の拡大が行われます。

JQuery でのセレクタに対する append メソッドで上手くいくのか懸念していましたが、今回のテストでは特に問題なく「lightbox2」は動作しました。

■LightBox2の例

<!DOCTYPE html>
<head>
<link href="./js/lightbox2-2.11.4/css/lightbox.min.css" rel="stylesheet">
<script type="text/javascript" src="./js/jquery-3.6.0/jquery-3.6.0.min.js"></script>
<script type="text/javascript" src="./js/lightbox2-2.11.4/js/lightbox-plus-jquery.min.js"></script>
<script>
//  lightbox start
$(function() {
	lightbox.option({
		"positionFromTop": 150,
	});
});
</script>

</head>
<body>
<h4>LightBox2テスト</h4>
<a href="./example1.jpg" data-lightbox="album" >
  <img src="./example1.jpg" id="" width="100" >
</a>
<a href="./example2.jpg" data-lightbox="album" >
  <img src="./example2.jpg" id="" width="100" >
</a>
<a href="./example3.png" data-lightbox="album" >
  <img src="./example3.png" id="" width="100" >
</a>
</body>
</html>

上のHTMLでは何も問題は無いのですが、ここに以下の Javascript ファイルを読込んで、そのファイル内の関数を呼出した時にエラーが発生しました。

■Javascriptの例（test.js とする）

(function($) {
    $.fnc = {};
    $.fnc.test = function () {	// ただ単に"test"を表示させる
        alert("test");
    };
})(jQuery);

それでは、先ほどのHTMLファイルに上記の「test.js」を追加してみます。

■LightBox2の例・その２

<!DOCTYPE html>
<head>
<link href="./js/lightbox2-2.11.4/css/lightbox.min.css" rel="stylesheet">
<script type="text/javascript" src="./js/jquery-3.6.0/jquery-3.6.0.min.js"></script>
<script type="text/javascript" src="./js/test.js"></script>
<script type="text/javascript" src="./js/lightbox2-2.11.4/js/lightbox-plus-jquery.min.js"></script>
<script>
//  lightbox start
$(function() {
	lightbox.option({
		"positionFromTop": 150,
	});
});

// test display
$.fnc.test();
</script>

</head>
<body>
<h4>LightBox2テスト</h4>
<a href="./example1.jpg" data-lightbox="album" >
  <img src="./example1.jpg" id="" width="100" >
</a>
<a href="./example2.jpg" data-lightbox="album" >
  <img src="./example2.jpg" id="" width="100" >
</a>
<a href="./example3.png" data-lightbox="album" >
  <img src="./example3.png" id="" width="100" >
</a>
</body>
</html>

このファイルをブラウザで開いても、「alert("test");」の実行が行われませんでした。
Chromeのデベロッパーツールでソースを開いて上記の16行目でブレイクさせると、「$.fnc」は「undefined」となっていました。「undefined」なので当然「$.fnc.test();」は実行不可能でした。

「lightbox2」のJavascriptを読込むと、どうも「JQuery」のプロパティ（この表現が正しいかどうかも分かりませんが）がクリアされる様です。

それならばと「test.js」ファイルの読込を「lightbox2」の後に持ってきたら、「$.fnc.test();」が実行されアラートが表示されました。

少し、便宜的な方法かもしれませんが、何かの参考になればと思い記事を書きました。

■XLSXの読込・書込クラスを使用したワークシートのコピー・セルデータを追加

今回の処理では「XLSX読込クラス」の内部に持っているワークシートを順次取得する getSheetIterator() 関数（イテレータ）を使用します。この関数を foreach 文で呼出すことでワークシートのオブジェクトが取得できます。

そのワークシートのオブジェクトの内部に持っている行データを順次取得する getRowIterator() 関数（イテレータ）を使用します。この関数を foreach 文で呼出すことで行データのオブジェクトが取得できます。

その後、行データのオブジェクトがNULLの場合には、空の行データを生成し、XLSX書込クラスへの行追加を行います。 NULLではない場合にはそのままXLSX書込クラスへの行追加を行います。

その行データからセルデータオブジェクト配列を取得し、その配列からを順次カラムデータとして取り出すために array_map() 関数で行います。結果としてデータの配列が生成されます。

生成されたデータの配列から新規の追加用行データオブジェクトを作成します。行データオブジェクトの setCellAtIndex($cell, $Index) 関数でセルデータの設定（置き換え）ができます。

<?php
require '../vendor/autoload.php';
use Box\Spout\Writer\Common\Creator\WriterEntityFactory;
use Box\Spout\Reader\Common\Creator\ReaderEntityFactory;

// XLSX読込クラス生成
$strReadFile = 'test7-1.xlsx';
$reader = ReaderEntityFactory::createReaderFromFile($strReadFile);
// 空行の読み込み許可
$reader->setShouldPreserveEmptyRows(true);
// 読み込み
$reader->open($strReadFile);

// XLSX書込クラス生成
$writer = WriterEntityFactory::createXLSXWriter();
$writer->openToFile('test7-1-New.xlsx');

// 全てのワークシートを取得
foreach($reader->getSheetIterator() as $sheet) {
    // ワークシートの全行を取得
    foreach($sheet->getRowIterator() as $row) {
        // [$row]がNULLの場合を考慮し、セル値の配列生成
        $cellValues = array_map(function($cell) {
            return $cell->getValue();
        }, $row->getCells());
        // セル値の配列から行データ生成
        $newRow = WriterEntityFactory::createRowFromArray($cellValues);

        // 新規追加するセルデータ生成
        $newCell = WriterEntityFactory::createCell('A001');
        // セルデータ設定
        $newRow->setCellAtIndex($newCell, 5);

        // 書込みへの追加
        $writer->addRow($newRow);
    }
}
// XLSX読込・書込クラスクローズ
$reader->close();
$writer->close();
?>

出力されたエクセルファイルを見てみると以下の様になります。

左側が読込んだエクセルで、右側が新たに書込んだエクセルです。 6番目のセルに「'A001'」が設定されたことが分かります。

■XLSXの読込・書込クラスを使用したワークシートのコピー・データ行を指定してセルデータを追加

ワークシートのオブジェクトの内部に持っている行データを順次取得する getRowIterator() 関数（イテレータ）ではキーとして、行Indexを返すことができます。この行Indexを判定すれば、意図した行にのみ処理が可能となります。

<?php
require '../vendor/autoload.php';
use Box\Spout\Writer\Common\Creator\WriterEntityFactory;
use Box\Spout\Reader\Common\Creator\ReaderEntityFactory;

// XLSX読込クラス生成
$strReadFile = 'test7-1.xlsx';
$reader = ReaderEntityFactory::createReaderFromFile($strReadFile);
// 空行の読み込み許可
$reader->setShouldPreserveEmptyRows(true);
// 読み込み
$reader->open($strReadFile);

// XLSX書込クラス生成
$writer = WriterEntityFactory::createXLSXWriter();
$writer->openToFile('test7-2-New.xlsx');

// 全てのワークシートを取得
foreach($reader->getSheetIterator() as $sheet) {
    // ワークシートの全行を取得
    foreach ($sheet->getRowIterator() as $rowIndex => $row) {
        // [$row]がNULLの場合を考慮し、セル値の配列生成
        $cellValues = array_map(function($cell) {
            return $cell->getValue();
        }, $row->getCells());
        // セル値の配列から行データ生成
        $newRow = WriterEntityFactory::createRowFromArray($cellValues);

        // 行が2行目と5行目のみ処理
        if ($rowIndex == 2 || $rowIndex == 5) {
            $cell = WriterEntityFactory::createCell('A001');
            $newRow->setCellAtIndex($cell, 5);
        }
        // 書込みへの追加
        $writer->addRow($newRow);
    }
}
// XLSX読込・書込クラスクローズ
$reader->close();
$writer->close();
?>

出力されたエクセルファイルを見てみると以下の様になります。 2行目と5行目のみにセルデータが設定されています。