今回はSQLiteに保存されているテーブルやレコードをExcelファイルとして保存し、ExcelファイルのデータをSQLiteに変換できるアプリをつくります。おそらく探せば同じようなことができるアプリは探せばあると思うのですが、ブログネタとしてつくってみることにします。誰です？車輪の再発明といっているのは？
まずSQLiteとExcelファイルの読み書きができるようにしなければなりません。C#でこれをやるのであればNugetでSystem.Data.SQLite.CoreとClosedXMLをインストールします。
ExcelファイルからSQLiteに変換する
まずExcelで適当なデータをつくります。そしてこれを読み込んでSQLiteに変換する方法を考えます。
ボタンをクリックしたらダイアログを表示してユーザーがExcelファイルを選択できるようにします。選択できるのは拡張子が.xlsxのものだけです。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
public partial class Form1 : Form { private void button1_Click(object sender, EventArgs e) { OpenFileDialog dialog = new OpenFileDialog(); dialog.Filter = "Excelファイル(*.xlsx)|*.xlsx"; if (dialog.ShowDialog() == DialogResult.OK) { ExcelToSQLite(dialog.FileName); } dialog.Dispose(); } } |
Excelファイルを選択したらワークシートの名前を取得して1行目のセルにある文字列をつかってカラムを作成します。そして以降の行をレコードとして読み込みSQLiteに保存します。
まずテーブルを作成するメソッドを示します。引数はSQLiteのファイルがあるパス、テーブル名、カラム名の配列です。テーブルに使用できる文字ですが引用符でくくれば大抵の場合はうまくいきます。また気をつけないといけないのがSQLインジェクション攻撃です。これはエスケープで対応します。
文字列の中のシングルクォーテーション(‘)の前にシングルクォーテーション(‘)を記述することでエスケープ処理ができます。SQLインジェクション攻撃の代表格として ;DELETE FROM users–があります。脆弱性がある場合、usersテーブルを削除されてしまうのですが、こんな名前でも問題なくテーブルを作成してくれます。
ただし”sqlite_sequence”という名前のテーブルは自動的に作成されるので同じ名前のテーブルをつくることはできません。そのため最初のif文で除外しています。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 |
public partial class Form1 : Form { void CreateTable(string sqlitePath, string tableName, string[] columnNames) { if (tableName == "sqlite_sequence") return; var sqlConnectionSb = new SQLiteConnectionStringBuilder { DataSource = sqlitePath }; using (var cn = new SQLiteConnection(sqlConnectionSb.ToString())) { cn.Open(); using (var cmd = new SQLiteCommand(cn)) { // "create table tableName("～～")だとすでにテーブルが存在する場合、例外が発生する // ' を '' に置換することでエスケープ処理をしている string sql = $"create table if not exists '{tableName.Replace("'", "''")}'("; sql += "id integer primary key autoincrement,"; columnNames = columnNames.Skip(1).ToArray(); foreach (string name in columnNames) sql += $"'{name.Replace("'", "''")}' text,"; string[] vs = sql.Split(new String[] { "," }, StringSplitOptions.RemoveEmptyEntries); sql = String.Join(", ", vs); sql += $")"; cmd.CommandText = sql; cmd.ExecuteNonQuery(); } } } } |
テーブルが生成されたらつぎにレコードを登録していきます。
前提として一番左側の列にはint型のプライマリキーが入力されているものとします。そして一番左側の列でなにも入力されていない行が存在する場合はそれ以降のレコードは存在しないものとします。
取得したワークシートがworksheetに格納されているとして、最初の行を調べてカラム名を取得します。終わったら2行目以降を調べます。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 |
int col = 1; List<string> vs = new List<string>(); while (true) { // ここでカラム名を取得し、リストに格納する if ((string)worksheet.Cell(1, col).Value == "") break; vs.Add((string)worksheet.Cell(1, col).Value); col++; } // シート名をテーブル名とする string tableName = worksheet.Name; CreateTable(sqlitePath, tableName, columnNames); // カラム名の配列をつくる string[] columnNames = vs.ToArray(); int row = 1; while (true) { row++; try { // 前提として一番左側の列にはint型のプライマリキーが入力されている // 0であったり取得しようとすると例外が発生する場合はそこで終了 if ((double)worksheet.Cell(row, 1).Value <= 0) break; } catch { break; } // 2行目以降の各セルを読み取り、各レコードのデータで文字列の配列をつくり自作メソッドにわたす List<string> values = new List<string>(); for (int i = 1; i <= col; i++) values.Add((string)worksheet.Cell(row, i).Value.ToString()); InsertRecord(sqlitePath, tableName, columnNames, values.ToArray()); } |
InsertRecordメソッドは引数はSQLiteのファイルがあるパス、テーブル名、カラム名の配列、各レコードのデータの配列です。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 |
public partial class Form1 : Form { void InsertRecord(string sqlitePath, string tableName, string[] columnNames, string[] values) { var sqlConnectionSb = new SQLiteConnectionStringBuilder { DataSource = sqlitePath }; using (var cn = new SQLiteConnection(sqlConnectionSb.ToString())) { // ' をエスケープ処理する。カラム名やセルのデータに ;DELETE FROM users-- があっても問題ない string a = String.Join(",", columnNames.Skip(1).Select(_ => $"'{_.Replace("'", "''")}'").ToArray()); List<string> vs = new List<string>(); for (int i = 0; i < columnNames.Length-1; i++) vs.Add($"@{i}"); string b = String.Join(",", vs); string sql = $"insert into '{tableName.Replace("'", "''")}' ({a}) values({b})"; cn.Open(); using (var cmd = new SQLiteCommand(sql, cn)) { for (int i = 1; i < columnNames.Length; i++) cmd.Parameters.Add(new SQLiteParameter(vs[i - 1], values[i])); cmd.ExecuteNonQuery(); } } } } |
まとめるとExcelToSQLiteメソッドは以下のようになります。
SQLiteファイルは実行ファイルがあるフォルダ内にoutputというフォルダを作成してそこに保存します。保存するときのファイル名は他と衝突しないように現在時刻から生成しています。処理が完了したらSQLiteファイルを保存したフォルダを開きます。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 |
public partial class Form1 : Form { private void Form1_Load(object sender, EventArgs e) { System.IO.Directory.CreateDirectory(Application.StartupPath + "\\output"); } void ExcelToSQLite(string excelFilePath) { DateTime time = DateTime.Now; string str = String.Format("{0}-{1:00}{2:00}-{3:00}{4:00}{5:00}", time.Year, time.Month, time.Day, time.Hour, time.Minute, time.Second); string sqlitePath = $"output\\{str}.sqlite3"; var workbook = new XLWorkbook(excelFilePath); foreach (var worksheet in workbook.Worksheets) { List<string> vs = new List<string>(); int col = 1; while (true) { if ((string)worksheet.Cell(1, col).Value == "") break; vs.Add((string)worksheet.Cell(1, col).Value); col++; } string tableName = worksheet.Name; string[] columnNames = vs.ToArray(); CreateTable(sqlitePath, tableName, columnNames); int row = 1; while (true) { row++; try { if ((double)worksheet.Cell(row, 1).Value <= 0) break; } catch { break; } List<string> values = new List<string>(); for (int i = 1; i <= col; i++) values.Add((string)worksheet.Cell(row, i).Value.ToString()); InsertRecord(sqlitePath, tableName, columnNames, values.ToArray()); } } System.Diagnostics.Process.Start(Application.StartupPath + "\\output"); } } |
SQLiteからExcelファイルに変換する
次にSQLiteからExcelファイルに変換する方法を考えます。SQLiteからテーブル名を取得し、レコードを読み取ってワークシートに追加していく処理を繰り返します。繰り返し処理が完了したらExcelファイルを保存します。そしてファイルが保存されたフォルダを開きます。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 |
public partial class Form1 : Form { private void button2_Click(object sender, EventArgs e) { OpenFileDialog dialog = new OpenFileDialog(); dialog.Filter = "SQLiteファイル(*.sqlite3)|*.sqlite3"; if (dialog.ShowDialog() == DialogResult.OK) { SQLiteToExcel(dialog.FileName); } dialog.Dispose(); } void SQLiteToExcel(string sqliteFilePath) { DateTime time = DateTime.Now; string str = String.Format("{0}-{1:00}{2:00}-{3:00}{4:00}{5:00}", time.Year, time.Month, time.Day, time.Hour, time.Minute, time.Second); string excelFilePath = $"output\\{str}.xlsx"; string sqlitePath = sqliteFilePath; List<string> tableNames = GetTableNames(sqlitePath); var workbook = new XLWorkbook(); foreach (string name in tableNames) { IXLWorksheet worksheet = workbook.Worksheets.Add(name); SetDataToWorksheet(sqlitePath, name, worksheet); } workbook.SaveAs(excelFilePath); System.Diagnostics.Process.Start(Application.StartupPath + "\\output"); } } |
これはSQLiteからテーブル名を取得するメソッドです。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 |
public partial class Form1 : Form { List<string> GetTableNames(string sqlitePath) { List<string> vs = new List<string>(); SQLiteConnectionStringBuilder sqlConnectionSb = new SQLiteConnectionStringBuilder { DataSource = sqlitePath }; using (SQLiteConnection cn = new SQLiteConnection(sqlConnectionSb.ToString())) { cn.Open(); using (SQLiteCommand cmd = new SQLiteCommand(cn)) { cmd.CommandText = "SELECT * FROM sqlite_master"; using (SQLiteDataReader reader = cmd.ExecuteReader()) { while (reader.Read()) { // テーブルの名前を取得する string name = reader["name"] as string; vs.Add(name); } } } } return vs; } } |
SQLiteからレコードを読み取り、ワークシートに追加していきます。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 |
public partial class Form1 : Form { void SetDataToWorksheet(string sqlitePath, string tableName, IXLWorksheet worksheet) { var sqlConnectionSb = new SQLiteConnectionStringBuilder { DataSource = sqlitePath }; using (var cn = new SQLiteConnection(sqlConnectionSb.ToString())) { cn.Open(); using (var cmd = new SQLiteCommand(cn)) { string query = $"SELECT * FROM '{tableName.Replace("'", "''")}'"; cmd.CommandText = query; using (SQLiteDataReader reader = cmd.ExecuteReader()) { int row = 1; while (reader.Read()) { if (row == 1) { string[] names = reader.GetValues().AllKeys; int col = 1; foreach (string name in names) { worksheet.Cell(row, col).Value = name; col++; } row++; } var values = Enumerable.Range(0, reader.FieldCount).Select(x => { try { return reader.GetValue(x).ToString(); } catch { return ""; } }).ToArray(); for(int i=0; i<values.Length; i++) worksheet.Cell(row, i + 1).Value = values[i]; row++; } } } } } } |
これで実際にExcelファイルを作成して、シートを複数作成します。そしてシート名やヘッダー名、セルに値を入力して実験してみます。;DELETE FROM users–とか｀とか”で囲まれた文字”とかいろいろ設定して試してみましたが、うまく動作していることが確認できます。
グーグル検索から拝見しております。
勉強させて頂きます。
Visual Studioでもできますか？
はい、できます。