C#でエクセルをdatatableに取込むことはたまにあると思います。
備忘録として残しておきます。
実際に作ったファイルはこちらからダウンロードできます。
エクセルをDatatbleに取込み、Datagridviewに表示
色々と解説はありますが、それは後にして、コードはこんな感じです。
エクセルを複数シート取込んで、データグリッドビューに表示します。
エクセルを開かずに処理する方法もあります。
C# エクセルを開かずにDatatbleに取込み(Closed XML)エクセルをC#で処理するときにMicrosoft.Office.Interop.Excelを参照追加して処理することが今のところは主流で...
流れは以下の通りです。
①ダイアログでエクセルを選択
②エクセルを開く
③シートごとにdatatableへ格納
④エクセルを閉じる
|
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 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 |
using System.Data.OleDb; using System.IO; using Excel = Microsoft.Office.Interop.Excel; --------------------------- /// <summary> /// 取込みボタクリック時 /// </summary> /// <param name="sender"></param> /// <param name="e"></param> private void import_Click(object sender, EventArgs e) { DataSet dsExcelSheets = new DataSet(); //エクセルシートごとのdatatableを格納用 DataTable dtSheet = new DataTable(); //エクセルシートのデータを格納 string filePath = ""; //エクセルのパスを格納 Excel.Application objExcel = null; //Excelオブジェクト Excel.Workbooks objWorkBooks = null; //workbooksオブジェクト Excel.Workbook objWorkBook = null; //workbookオブジェクト Excel.Sheets objSheets = null; //複数シートオブジェクト Excel.Worksheet objSheet = null; //シートオブジェクト //ダイアログでファイルパスを取得 filePath = getExcelPath(); //エクセルを開く objExcel = new Excel.Application(); objExcel.Visible = true; // Excelファイルをオープンする objWorkBooks = objExcel.Workbooks; objWorkBook = objWorkBooks.Open(filePath); //シートを取得 objSheets = objWorkBook.Worksheets; //シート数分ループしながら、datatableに取込 for (int i = 1; i <= objSheets.Count; i++) { //シートをセット objSheet = objSheets[i]; //シートからSELECT *する dsExcelSheets.Tables.Add(getDataTableFromExcel(filePath, objSheet.Name,true,true).Copy()); } //---------COMオブジェクトの解放--------- // Sheet解放 System.Runtime.InteropServices.Marshal.ReleaseComObject(objSheet); System.Runtime.InteropServices.Marshal.ReleaseComObject(objSheets); // Book解放 objWorkBook.Close(); System.Runtime.InteropServices.Marshal.ReleaseComObject(objWorkBook); System.Runtime.InteropServices.Marshal.ReleaseComObject(objWorkBooks); // Excelアプリケーションを解放 objExcel.Quit(); System.Runtime.InteropServices.Marshal.ReleaseComObject(objExcel); GC.Collect(); //---------COMオブジェクトの解放--------- //データグリッドビューにセット dataGridViewSupplier.DataSource = dsExcelSheets.Tables["Supplier"]; dataGridViewFoods.DataSource = dsExcelSheets.Tables["Foods"]; dataGridViewGoods.DataSource = dsExcelSheets.Tables["Goods"]; MessageBox.Show("完了"); } /// <summary> /// ファイルパスを返す /// </summary> /// <returns></returns> public string getExcelPath() { string path = ""; //ファイルダイアログを生成する OpenFileDialog op = new OpenFileDialog(); op.Title = "ファイルを開く"; op.InitialDirectory = @"C:\"; op.Filter = "すべてのファイル(*.*)|*.*"; op.FilterIndex = 1; //オープンファイルダイアログを表示する DialogResult dialog = op.ShowDialog(); //「開く」ボタンが選択された時の処理 if (dialog == DialogResult.OK) { path = op.FileName; } //「キャンセル」時の処理 else if (dialog == DialogResult.Cancel) { } 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; } |
解説
それぞれ処理4つのポイントを解説します。
①ダイアログでエクセルを選択
20行目からgetExcelPath()というメソッドに飛んでいます。
詳細は以下をご確認ください。
C# ダイアログで選択したファイルパスを取得
ダイアログで選択したファイルのパスを取得するのってどんなやり方だったっけ？となることが私は多いです。
と言うか、覚える気が...
②エクセルを開く
エクセルを開くにはブックやらシートやらと変数を定義しないとダメなようです。
13行目～17行目です。
そして、objWorkBooks.Open(filePath);でエクセルを開いています。
③シートごとにdatatableへ格納
31行目でシートを変数へ格納し、
34行目からの処理でシート枚数分ループさせています。
getDataTableFromExcelメソッド内でシートのデータすべてを取得しています。
SQLのSELECT * [strSheetName] はシートのすべてのデータを取得するという意味です。
※列が1列空いたり、行が1行空白だと、その前までしか取得されません。
④エクセルを閉じる
44行目～58行目でエクセルを閉じて、オブジェクトを開放しています。
これでエクセルを閉じたり、プロセスやメモリを開放することができます。
エクセルを取込んでSQLで更新する方法も紹介しているのでご覧ください。
C# エクセル取込みしてSQLでデータ更新C#などで開発する時にエクセルの一括取込み機能を作るときがあると思います。
いちいち調べて、プログラムを組んでと言うのが面倒なので...
実行結果
3シートがそれぞれデータグリッドビューに表示されました。
C#アプリ開発 メールお知らせアプリ
開発案件の経験は2案件ほどで、ほとんど経験ありません。
そんな私が独学でプログラミングの勉強をしてきました。
そこで...
補足
Microsoft.ACE.OLEDB.12.0
以下が出た場合
‘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ビットようを使う必要があるからだそうです。
エクセル参照設定
ソリューションに参照設定で「Microsoft Excel XXX Object Library」を追加しておいてください。
参考：Excelの参照設定
