どうも！リョクちゃです。

Excelをアプリケーションで操作できるようにしたいなぁ

リョクちゃ

VB.NetではClosedXMLといったライブラリを使うことでできるよ！

お茶

今回は、ClosedXMLといわれるライブラリを使って、Excelを操作する方法、

主に、読み書きについて紹介します。

ちなみに前回の記事はこちら

【VB.Net】自身のコンピュータにインストールされているシリアルポート一覧の取得

普段私たちが使っているコンピュータにインストールされているシリアルポートの一覧を取得し、表示する方法を紹介しています。Windowsのデバイスマネージャの項目にあるポートの一覧をプログラム上で取得しするといった内容になります。シリアル通信する際の確認用に便利です。

dreamparadaisu.com

2021-06-23 07:26

フォームの作成

以下のようなフォームを作成します。

①～⑭のコントロールについては以下の表のとおりです。

No	コントロール	名前
1	Label	Label1
2	Label	Label2
3	Label	Label3
4	Label	Label4
5	TextBox	tbxReadColumn
6	TextBox	tbxReadRow
7	TextBox	tbxWriteColumn
8	TextBox	tbxWriteRow
9	Button	btnRead
10	Label	Label5
11	TextBox	tbxWriteText
12	Label	Label6
13	Label	lbReadResult
14	Button	btnWrite

各コントロールのフォントは、メイリオの18ptを設定しています。

実行環境

筆者の実行環境は、

VisualStudio2019
Windows 10 64bit
.Net Framework 4.5.1

になります。

※　Windows7 8 8.1でも動作はできますが、画面デザインの表示が変わるかもしれません。

アプリケーションの目的

今回は以下の事ができることを目的とします。

“読み取る列”と”読み取る行”にそれぞれ値を入力し、読込むボタンを
押すことで、指定のExcelファイルのシートから読み取ります。
“書込む列”と”書込む行”にそれぞれ値を入力し、書込むボタンを
押すことで、指定のExcelファイルのシートに書込む値で入力された値を
書込みます。

動作

動作としては、

tbxReadRowとtbxReadColumnにそれぞれ値を入力します。
btnReadを押すことで、指定のExcelファイルから入力した
行と列のセルの値を読み取ります。
読み取った値はlbReadResultに出力します。
tbxWriteRowとtbxWriteColumnにそれぞれ値を入力します。
tbxWriteTextに書き込む値を入力します。
btnWriteを押すことで、入力した行と列のセルに
書込みたい値を指定のExcelファイルに出力します。

これらができることとします。

プログラム編

ClosedXMLのインポート方法

最初にClosedXMLを使えるようにするための準備について触れていきます。

作成したプロジェクトの

ツールタブ　→　NuGetパッケージマネージャー

→　ソリューションのNuGetパッケージの管理を選択します。

クリックしたら、NuGetソリューションタブが新たに開きます。

デフォルトでは、インストール済みが選択されていますので、参照を選択します。

検索ボックスに”ClosedXML”と入力します。

赤枠で囲まれた”ClosedXML”を選択し、

インストールしたいプロジェクトのチェックボックスにチェックを入れます。

選択をしたら、インストールをクリックします。

OKボタンを押します。

インストールが完了すると、プルジェクトの参照欄に

ClosedXMLが追加されます。

これでClosedXMLの準備は完了です。

インポートする際は、

Imports ClosedXML

と入力することで使えるようになります。

Excelファイルを読み取る準備

ClosedXMLを使ってExcelファイルを操作するには、次の準備を行います。

Excelブックの設定
Excelブックのシートの設定

Excelブックの設定

Dim 変数名 As New Excel.XLWorkbook(“操作するExcelファイルの絶対パス”)

Excelブックのシートの設定

上で設定した変数を使います。

Dim 変数名 As Excel.IXLWorksheet = 設定した変数名.Worksheet(“操作するシート名”)

これでExcelファイルを読み取る準備は完了です。

今回使うExcelファイルの用意

下図のようなExcelをテスト用に使用します。

ファイル名はtest.xlsxとしました。(シート名はSheet1です。)

これを例に、先ほどのExcelファイルを読み取る準備のコードを書いてみます。

Dim xlBook As New Excel.XLWorkbook("C:\text.xlsx")
Dim xlSheet As Excel.IXLWorksheet = xlBook.Worksheet("Sheet1")

1 2	Dim xlBook As New Excel.XLWorkbook("C:\text.xlsx") Dim xlSheet As Excel.IXLWorksheet = xlBook.Worksheet("Sheet1")

Excelファイルのシートの読み取り

シートの指定の行・列のセルの値を読み取るには、

xlSheet.Cell(セルの番地).Value.ToString()

もしくは

xlSheet.Cell(行番号、列番号).Value.ToString()

で読み取ることができます。

※.ToString()は文字列で受け取れるようにしています。

例）text.xlsxから”りんご”と入力されたセルの値を読み取る場合

xlSheet.Cell("B2").Value.ToString()
xlSheet.Cell(2,2).Value.ToString()

xlSheet.Cell("B2").Value.ToString()

xlSheet.Cell(2,2).Value.ToString()

※xlSheetは例で宣言したIXLWorksheet変数

と書くことで読み取ることが可能です。

今回は、列と行が入力されるので入力値を受け取って読み取りたいセルをアプリケーションに

教えてあげる必要があります。

最初に入力値のチェックを行います。

' 入力されていれば、次の処理へ
If Me.tbxReadColumn.Text <> "" AndAlso Me.tbxReadRow.Text <> "" Then
End If

' 入力されていれば、次の処理へ

If Me.tbxReadColumn.Text <> "" AndAlso Me.tbxReadRow.Text <> "" Then

End If

この中に、読取りに関するコードを書いていきます。

' 入力されていれば、次の処理へ
If Me.tbxReadColumn.Text <> "" AndAlso Me.tbxReadRow.Text <> "" Then
' Excelファイルを読み取る準備
Dim xlBook As New Excel.XLWorkbook("C:\test.xlsx")
Dim xlSheet As Excel.IXLWorksheet = xlBook.Worksheet("Sheet1")
' シートの読み取り
Dim result As String = xlSheet.Cell(Me.tbxReadColumn.Text & Me.tbxReadRow.Text).Value.ToString
' 読み取った情報を表示
Me.lbReadResult.Text = result
End If
End Sub

' 入力されていれば、次の処理へ

If Me.tbxReadColumn.Text <> "" AndAlso Me.tbxReadRow.Text <> "" Then

' Excelファイルを読み取る準備

Dim xlBook As New Excel.XLWorkbook("C:\test.xlsx")

Dim xlSheet As Excel.IXLWorksheet = xlBook.Worksheet("Sheet1")

' シートの読み取り

Dim result As String = xlSheet.Cell(Me.tbxReadColumn.Text & Me.tbxReadRow.Text).Value.ToString

' 読み取った情報を表示

Me.lbReadResult.Text = result

End If

End Sub

この処理を読込みボタンが押されたら行いたいので、

読み込みボタンのClickイベント内に書きます。

''' <summary>
''' 読取りボタンを押下
''' </summary>
''' <param name="sender"></param>
''' <param name="e"></param>
Private Sub btnRead_Click(sender As Object, e As EventArgs) Handles btnRead.Click
' 入力されていれば、次の処理へ
If Me.tbxReadColumn.Text <> "" AndAlso Me.tbxReadRow.Text <> "" Then
' Excelファイルを読み取る準備
Dim xlBook As New Excel.XLWorkbook("E:\ブログ\VisualStudio\Excel操作\test.xlsx")
Dim xlSheet As Excel.IXLWorksheet = xlBook.Worksheet("Sheet1")
' シートの読み取り
Dim result As String = xlSheet.Cell(Me.tbxReadColumn.Text & Me.tbxReadRow.Text).Value.ToString
Console.WriteLine(xlSheet.Cell(2, 2).Value)
' 読み取った情報を表示
Me.lbReadResult.Text = result
End If
End Sub

''' <summary>

''' 読取りボタンを押下

''' </summary>

''' <param name="sender"></param>

''' <param name="e"></param>

Private Sub btnRead_Click(sender As Object, e As EventArgs) Handles btnRead.Click

' 入力されていれば、次の処理へ

If Me.tbxReadColumn.Text <> "" AndAlso Me.tbxReadRow.Text <> "" Then

' Excelファイルを読み取る準備

Dim xlBook As New Excel.XLWorkbook("E:\ブログ\VisualStudio\Excel操作\test.xlsx")

Dim xlSheet As Excel.IXLWorksheet = xlBook.Worksheet("Sheet1")

' シートの読み取り

Dim result As String = xlSheet.Cell(Me.tbxReadColumn.Text & Me.tbxReadRow.Text).Value.ToString

Console.WriteLine(xlSheet.Cell(2, 2).Value)

' 読み取った情報を表示

Me.lbReadResult.Text = result

End If

End Sub

Excelファイルのシートへの書き込み

シートの指定の行・列のセルの値を書き込むには、

シートの読み取り同様、Excelファイルの準備が必要になります。

Excelファイルのシートの指定のセルに書込む場合は、

xlSheet.Cell(セルの番地).Value = “書き込みたい値”

もしくは

xlSheet.Cell(行番号、列番号).Value = “書き込みたい値”

例）text.xlsxのC2セルに”赤い”と書き込みたい場合

xlSheet.Cell("C2").Value = "赤い"
xlSheet.Cell(2,3).Value = "赤い"

xlSheet.Cell("C2").Value = "赤い"

xlSheet.Cell(2,3).Value = "赤い"

※xlSheetは例で宣言したIXLWorksheet変数

と書くことで読み取ることが可能です。

これだけではセルへ書き込みはできたのですが、Excelファイルとしては未完成です。

いうならば編集はしたけど、保存されていない状態です。

Excelファイルを保存してあげる必要があります。

これは

xlBook.Save()

でできます。

また、

xlBook.SaveAs(“保存するファイル名”)

とすることで名前を付けて保存ができます。

※xlBookはExcel.XLWorkbookで宣言した変数になります。

これも書き込むボタンが押されてから読み込むボタン同様の処理を書いていきます。

''' <summary>
''' 書込むボタンを押下
''' </summary>
''' <param name="sender"></param>
''' <param name="e"></param>
Private Sub btnWrite_Click(sender As Object, e As EventArgs) Handles btnWrite.Click
' 入力されていれば、次の処理へ
If Me.tbxWriteColumn.Text <> "" AndAlso Me.tbxWriteRow.Text <> "" Then
' Excelファイルを読み取る準備
Dim xlBook As New Excel.XLWorkbook("C:test.xlsx")
Dim xlSheet As Excel.IXLWorksheet = xlBook.Worksheet("Sheet1")
' 書込む値が入力されていれば、次の処理へ
If Me.tbxWriteText.Text <> "" Then
' シートへの書き込み
xlSheet.Cell(Me.tbxWriteColumn.Text & Me.tbxWriteRow.Text).Value = Me.tbxWriteText.Text
' Excelファイルの保存
xlBook.Save()
End If
End If
End Sub

''' <summary>

''' 書込むボタンを押下

''' </summary>

''' <param name="sender"></param>

''' <param name="e"></param>

Private Sub btnWrite_Click(sender As Object, e As EventArgs) Handles btnWrite.Click

' 入力されていれば、次の処理へ

If Me.tbxWriteColumn.Text <> "" AndAlso Me.tbxWriteRow.Text <> "" Then

' Excelファイルを読み取る準備

Dim xlBook As New Excel.XLWorkbook("C:test.xlsx")

Dim xlSheet As Excel.IXLWorksheet = xlBook.Worksheet("Sheet1")

' 書込む値が入力されていれば、次の処理へ

If Me.tbxWriteText.Text <> "" Then

' シートへの書き込み

xlSheet.Cell(Me.tbxWriteColumn.Text & Me.tbxWriteRow.Text).Value = Me.tbxWriteText.Text

' Excelファイルの保存

xlBook.Save()

End If

End Sub

全体のコードはこちらになります。

Imports ClosedXML
Public Class testExcel
''' <summary>
''' 読取りボタンを押下
''' </summary>
''' <param name="sender"></param>
''' <param name="e"></param>
Private Sub btnRead_Click(sender As Object, e As EventArgs) Handles btnRead.Click
' 入力されていれば、次の処理へ
If Me.tbxReadColumn.Text <> "" AndAlso Me.tbxReadRow.Text <> "" Then
' Excelファイルを読み取る準備
Dim xlBook As New Excel.XLWorkbook("C:test.xlsx")
Dim xlSheet As Excel.IXLWorksheet = xlBook.Worksheet("Sheet1")
' シートの読み取り
Dim result As String = xlSheet.Cell(Me.tbxReadColumn.Text & Me.tbxReadRow.Text).Value.ToString
' 読み取った情報を表示
Me.lbReadResult.Text = result
End If
End Sub
''' <summary>
''' 書込むボタンを押下
''' </summary>
''' <param name="sender"></param>
''' <param name="e"></param>
Private Sub btnWrite_Click(sender As Object, e As EventArgs) Handles btnWrite.Click
' 入力されていれば、次の処理へ
If Me.tbxWriteColumn.Text <> "" AndAlso Me.tbxWriteRow.Text <> "" Then
' Excelファイルを読み取る準備
Dim xlBook As New Excel.XLWorkbook("C:test.xlsx")
Dim xlSheet As Excel.IXLWorksheet = xlBook.Worksheet("Sheet1")
' 書込む値が入力されていれば、次の処理へ
If Me.tbxWriteText.Text <> "" Then
' シートへの書き込み
xlSheet.Cell(Me.tbxWriteColumn.Text & Me.tbxWriteRow.Text).Value = Me.tbxWriteText.Text
' Excelファイルの保存
xlBook.Save()
End If
End If
End Sub
End Class

Imports ClosedXML

Public Class testExcel

''' <summary>

''' 読取りボタンを押下

''' </summary>

''' <param name="sender"></param>

''' <param name="e"></param>

Private Sub btnRead_Click(sender As Object, e As EventArgs) Handles btnRead.Click

' 入力されていれば、次の処理へ

If Me.tbxReadColumn.Text <> "" AndAlso Me.tbxReadRow.Text <> "" Then

' Excelファイルを読み取る準備

Dim xlBook As New Excel.XLWorkbook("C:test.xlsx")

Dim xlSheet As Excel.IXLWorksheet = xlBook.Worksheet("Sheet1")

' シートの読み取り

Dim result As String = xlSheet.Cell(Me.tbxReadColumn.Text & Me.tbxReadRow.Text).Value.ToString

' 読み取った情報を表示

Me.lbReadResult.Text = result

End If

End Sub

''' <summary>

''' 書込むボタンを押下

''' </summary>

''' <param name="sender"></param>

''' <param name="e"></param>

Private Sub btnWrite_Click(sender As Object, e As EventArgs) Handles btnWrite.Click

' 入力されていれば、次の処理へ

If Me.tbxWriteColumn.Text <> "" AndAlso Me.tbxWriteRow.Text <> "" Then

' Excelファイルを読み取る準備

Dim xlBook As New Excel.XLWorkbook("C:test.xlsx")

Dim xlSheet As Excel.IXLWorksheet = xlBook.Worksheet("Sheet1")

' 書込む値が入力されていれば、次の処理へ

If Me.tbxWriteText.Text <> "" Then

' シートへの書き込み

xlSheet.Cell(Me.tbxWriteColumn.Text & Me.tbxWriteRow.Text).Value = Me.tbxWriteText.Text

' Excelファイルの保存

xlBook.Save()

End If

End Sub

End Class

実行編

実際に実行をしていきます。

プログラム起動時

読み取るセルの番地を入力

読取ったセルの情報を表示

C2にあるセルに”赤い”と書き込む場合

書込まれた

書込まれた値を読んでみます。

正しく書き込まれていることがわかります。

まとめ

今回ClosedXMLを使ったExcelの操作方法やClosedXMLのインポート方法を紹介しました。

ClosedXMLを使うことで簡単にExcelファイルを操作できるようになります。

ただし、.xlsxの拡張子のみがサポートされているので、.xlsが使用できません。

これに関しては、別の方法を紹介していきたいと思います。

Excelファイル操作で悩んでる方の参考になれば嬉しいです。

最後までお読みいただきありがとうございます。

フォームの作成

実行環境

アプリケーションの目的

動作

プログラム編

ClosedXMLのインポート方法

Excelファイルを読み取る準備

Excelブックの設定

Excelブックのシートの設定

今回使うExcelファイルの用意

Excelファイルのシートの読み取り

Excelファイルのシートへの書き込み

Excelファイルのシートの指定のセルに書込む場合は、

実行編

まとめ

こちらの記事も読まれています。