SEの転職活動で使うべきサイト・エージェント 目指せ年収アップ
SEの転職活動で使うべきサイト・エージェント 目指せ年収アップ
東南アジアをバックパッカーしながら転職活動をした記録
東南アジアをバックパッカーしながら転職活動をした記録
7年目で初の転職活動 2カ月で内定獲得した記録
7年目で初の転職活動 2カ月で内定獲得した記録
おすすめのプログラミングスクール めざせ就職・年収アップ!
おすすめのプログラミングスクール めざせ就職・年収アップ!
メンズクリア2年通い放題入会! その後のヒゲ状況(不定期更新)
メンズクリア2年通い放題入会! その後のヒゲ状況(不定期更新)
ブログ開始3年が経った月収が1万超えてた!
ブログ開始3年が経った月収が1万超えてた!
プログラミング独学・スクール・就職(目次)
プログラミング独学・スクール・就職(目次)
previous arrowprevious arrow
next arrownext arrow
 
Shadow
C#/VB.net

C# エクセル取込みしてSQLでデータ更新

C#などで開発する時にエクセルの一括取込み機能を作るときがあると思います。

いちいち調べて、プログラムを組んでと言うのが面倒なので備忘録として残しておきます。

機能としては「検索」と「エクセル取込更新」の二つの機能があります。

実際に作ったファイルはこちらからダウンロードできます。

データベース(SQLite)と取り込み用のエクセルも入っているのでダウンロードしてもらえればすぐに実行して試すことができます。

デモンストレーションで動かした動画を載せておきます。

開発環境

開発環境はVisualStudio2015

データベースはSQLiteです。

VisualStudio2015のインストール

開発環境はVisualStudio2015なのでインストールが必要です。

設定は特に必要なく、標準インストールで良いです。

1時間くらいかかります。

こちらからダウンロードできます。

Visual Studio 2015(Community)

DB.Browser.for.SQLiteのインストール

SQLiteは専用ソフトがないとデータベースを開いて中を見ることができません。

開くとこんな感じで見ることができます。

こちらからダウンロードできます。

SQLite DB.Browser

エクセル取込み更新のコード

フォーム画面のプログラムは以下です。

※以下のソースはここでは書いていませんので、必要あれば、ダウンロードをお願いします。

・DBを更新や検索するSQLのファイル

・SQLiteのDBへアクセスしたり、更新する部品を書いたファイル

using System.Data.OleDb;
using System.IO;

---------------------------
/// <summary>
/// 検索ボタンクリック時
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void buttonSearch_Click(object sender, EventArgs e)
{
    DataSet ds = new DataSet();                    //datatableを格納用
    DataTable dt = new DataTable();                //データを格納
    DataAccess dataAccess = new DataAccess();      //インスタンス化

    //データを取得
    ds.Tables.Add(dataAccess.getSupplierAll().Copy());

    //データグリッドビューにセット
    dataGridView.DataSource = ds.Tables["Supplier"];
}


/// <summary>
/// エクセル取込更新ボタンクリック時
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void buttonUpdateByExcel_Click(object sender, EventArgs e)
{
    DataSet dsExcelSheets = new DataSet();              //エクセルシートごとのdatatableを格納用
    DataTable dtSheet = new DataTable();                //エクセルシートのデータを格納
    DataAccess dataAccess = new DataAccess();           //インスタンス化
    string filePath = "";                               //エクセルのパスを格納
    

    //ダイアログでファイルパスを取得
    filePath = getExcelPath();

    //エクセルをDatatableに取込む
    dsExcelSheets.Tables.Add(getDataTableFromExcel(filePath, "Supplier", true, true).Copy());
    
    //Datatableの内容でDBを更新
    //Datatableの行数分ループ
    foreach (DataRow dr in dsExcelSheets.Tables[0].Rows)
    {
        dataAccess.updateSupplier(dr);
    }
    
    //Supplierというdatatableがあれば削除
    if(dsExcelSheets.Tables.Contains("Supplier"))
    {
        dsExcelSheets.Tables.Remove("Supplier");
    }

    //データを取得
    dsExcelSheets.Tables.Add(dataAccess.getSupplierAll().Copy());

    //データグリッドビューにセット
    dataGridView.DataSource = dsExcelSheets.Tables["Supplier"];


    MessageBox.Show("エクセル更新完了");
    
}

/// <summary>
/// ファイルパスを返す
/// </summary>
/// <returns></returns>
public string getExcelPath()
{
    string path = "";

    //ファイルダイアログを生成する
    OpenFileDialog op = new OpenFileDialog();
    op.Title = "ファイルを開く";
    op.Filter = "すべてのファイル(*.*)|*.*";
    op.FilterIndex = 1;

    //オープンファイルダイアログを表示する
    DialogResult dialog = op.ShowDialog();

    //「開く」ボタンが選択された時の処理
    if (dialog == DialogResult.OK)
    {
        path = op.FileName;
    }
    //「キャンセル」時の処理
    else if (dialog == DialogResult.Cancel)
    { path = ""; }

    return path;
}

/// <summary>
/// シートをdatatableに格納
/// </summary>
/// <param name="strFilePath"></param>
/// <param name="strSheetName"></param>
/// <param name="isInHeader"></param>
/// <param name="isAllStrColum"></param>
/// <returns></returns>
public DataTable getDataTableFromExcel(string excelFilePath, string strSheetName, Boolean isInHeader = true, Boolean isAllStrColum = true)
{
    DataTable dt = new DataTable();
    string strInHeader = isInHeader ? "YES" : "NO";        // ヘッダー設定
    string strIMEX = isAllStrColum ? "IMEX=1;" : "";       // 文字列型設定
    string strFileEx = Path.GetExtension(excelFilePath);   // ファイル拡張子
    string strExcelVer = "Excel ";                         // Excelファイルver確認

    //テーブル名をセット
    dt.TableName = strSheetName;

    //拡張子ごとにエクセルのバージョンを取得
    if (strFileEx == ".xls")
    {
        strExcelVer += "8.0;";
    }
    else if (strFileEx == ".xlsx" || strFileEx == ".xlsm")
    {
        strExcelVer += "12.0;";
    }
    else
    {
        return null;
    }

    //コネクションストリングを作成
    String strCon = "Provider=Microsoft.ACE.OLEDB.12.0;"            // プロバイダ設定
                                                                    //= "Provider=Microsoft.Jet.OLEDB.4.0;"
                        + "Data Source=" + excelFilePath + "; "     // ソースファイル指定
                        + "Extended Properties=\"" + strExcelVer    // Excelファイルver指定
                        + "HDR=" + strInHeader + ";"                // ヘッダー設定
                        + strIMEX                                   // フィールドの型を強制的にテキスト
                        + "\"";
    OleDbConnection con = new OleDbConnection(strCon);
    String strCmd = "SELECT * FROM [" + strSheetName + "$]";

    // 読み込み
    OleDbCommand cmd = new OleDbCommand(strCmd, con);
    OleDbDataAdapter adp = new OleDbDataAdapter(cmd);
    adp.Fill(dt);

    return dt;
}

解説

エクセルをdatatableに取込み

41行目でエクセルを読み込んで、datatableに取込んでいます。

58行目のgetDataTableFromExcelメソッドでエクセルの内容をSELECT * で全件取得し、datatableに格納させています。

以下のエラーが出る可能性があります。

‘Microsoft.ACE.OLEDB.12.0’ プロバイダーはローカルのコンピューターに登録されていません。

AccessDatabaseEngine_X64.exeをダウンロードしてインストールしてください。

こちらからダウンロード可能です。

https://www.microsoft.com/ja-jp/download/details.aspx?id=13255

参考サイト:Microsoft.ACE.OLEDB.12.0プロバイダーはローカルコンピュータに登録されていません

原因は32ビットのofficeを動かしているため、64ビットようを使う必要があるからだそうです。

 

エクセルを立上げずに読取る方法も紹介しています。

C# エクセルを開かずにDatatbleに取込み(Closed XML)エクセルをC#で処理するときにMicrosoft.Office.Interop.Excelを参照追加して処理することが今のところは主流で...

datatableのデータを使ってDB更新

45行目~48行目でdatatableのレコード分ループして更新しています。

1行ずつdatarowに入った値を元にDBを更新するSQLを発行しています。

dataAccess.updateSupplier(dr);メソッドの中身は以下のように記載しています。

/// <summary>
/// supplier_cdをキーにSupplierへ更新
/// </summary>
/// <param name="drArg"></param>
/// <returns></returns>
public int updateSupplier(DataRow drArg)
{
    int updateCount = 0;                                                                //更新件数を格納
    DbHelper = new SQLiteDBHelper(localDBPath);                                         //インスタンス化
    StringBuilder sb = new StringBuilder();                                             //クエリ格納用

    //パラメータをセット
    SQLiteParameter[] parameters = new SQLiteParameter[]
    {
            new SQLiteParameter("@supplier_cd",drArg.ItemArray[0]),
            new SQLiteParameter("@supplier_name",drArg.ItemArray),
            new SQLiteParameter("@addr",drArg.ItemArray),
            new SQLiteParameter("@post_num",drArg.ItemArray),
            new SQLiteParameter("@tel",drArg.ItemArray),
            new SQLiteParameter("@fax",drArg.ItemArray[5])
    };


    sb.AppendLine("UPDATE Supplier SET");
    sb.AppendLine("	supplier_name         = @supplier_name,");
    sb.AppendLine("	addr                  = @addr,");
    sb.AppendLine("	post_num              = @post_num,");
    sb.AppendLine("	tel                   = @tel,");
    sb.AppendLine("	fax                   = @fax");
    sb.AppendLine("WHERE supplier_cd      = @supplier_cd");

    //更新
    updateCount = DbHelper.ExecuteNonQuery(sb.ToString(), parameters);

    return updateCount;
}

更新後のデータを表示

56行目~60行目で更新後のデータを検索し、表示しています。

更新結果

更新前の状態で検索をすると以下の状態です。

以下の内容でtel列fax列をエクセル更新します。

エクセル取込更新ボタン後は以下が表示されます。

参照設定

SQLite

SQLite.dllがあるので、参照設定追加をしてください。

dllは「~UpdateDatabaseByExcel\SQLite\System.Data.SQLite.dll」のパスに配置されています。

C#アプリ開発 メールお知らせアプリ 開発案件の経験は2案件ほどで、ほとんど経験ありません。 そんな私が独学でプログラミングの勉強をしてきました。 そこで...
ABOUT ME
LooseCarrot
LooseCarrot
ブログ運営をしているLooseCarrotです。 興味のあることにトライして発信していきます! プロフィール

関連している記事