C#でエクセルをdatatableに取込むことはたまにあると思います。
備忘録として残しておきます。
実際に作ったファイルはこちらからダウンロードできます。
目次
エクセルをDatatbleに取込み、Datagridviewに表示
色々と解説はありますが、それは後にして、コードはこんな感じです。
エクセルを複数シート取込んで、データグリッドビューに表示します。
エクセルを開かずに処理する方法もあります。
流れは以下の通りです。
①ダイアログでエクセルを選択
②エクセルを開く
③シートごとにdatatableへ格納
④エクセルを閉じる
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()というメソッドに飛んでいます。
詳細は以下をご確認ください。
②エクセルを開く
エクセルを開くにはブックやらシートやらと変数を定義しないとダメなようです。
13行目~17行目です。
そして、objWorkBooks.Open(filePath);でエクセルを開いています。
③シートごとにdatatableへ格納
31行目でシートを変数へ格納し、
34行目からの処理でシート枚数分ループさせています。
getDataTableFromExcelメソッド内でシートのデータすべてを取得しています。
SQLのSELECT * [strSheetName] はシートのすべてのデータを取得するという意味です。
※列が1列空いたり、行が1行空白だと、その前までしか取得されません。
④エクセルを閉じる
44行目~58行目でエクセルを閉じて、オブジェクトを開放しています。
これでエクセルを閉じたり、プロセスやメモリを開放することができます。
エクセルを取込んでSQLで更新する方法も紹介しているのでご覧ください。
実行結果
3シートがそれぞれデータグリッドビューに表示されました。
補足
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の参照設定