【C#】Excelの書き込みで入力規則をチェックする（その１）
おはようございます。
前回に引き続き、Excelの操作をやっていきます。
今回は書き込みをする際に、入力規則（のリスト）をチェックして規則にない値は書き込まないようにしてみました。
プログラムは前回のものを流用します。
スポンサーリンク
プログラムの修正
ExcelManager.cs
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Runtime.InteropServices;
using Microsoft.Office.Interop.Excel;
namespace SampleExcel
{
class ExcelManager : IDisposable
{
private Application _app = null;
private Workbooks _books = null;
private Workbook _book = null;
private Sheets _sheets = null;
private Worksheet _sheet = null;
private Ranges _ranges = null;
private Range _range = null;
private string[] _validationAddressList;
/// <summary>
/// Excelを表示するかどうか（デフォルト非表示）
/// </summary>
private bool _application_visible = false;
#region IDisposable Support
/// <summary>
/// 重複する呼び出しを検出するには
/// </summary>
private bool disposedValue = false;
/// <summary>
/// デフォルトコンストラクタ
/// </summary>
public ExcelManager()
{
_app = new Application();
_app.Visible = _application_visible;
}
/// <summary>
/// ファイル名を指定
/// </summary>
/// <param name="filePath"></param>
public ExcelManager(String filePath)
{
_app = new Application();
_app.Visible = _application_visible;
_books = _app.Workbooks;
_book = _books.Open(filePath);
_sheets = _book.Worksheets;
_sheet = _book.ActiveSheet;
// 入力規則の条件を持つセル番地のリストを予め取得
SetValidationAddressList();
}
/// <summary>
/// ファイル名、シート名を指定
/// </summary>
/// <param name="filePath"></param>
/// <param name="sheetName"></param>
public ExcelManager(string filePath, string sheetName)
{
_app = new Application();
_app.Visible = _application_visible;
_books = _app.Workbooks;
_book = _books.Open(filePath);
_sheets = _book.Worksheets;
for (int sheetId = 1; sheetId <= _sheets.Count; sheetId++)
{
_sheet = _sheets[sheetId];
if (_sheet.Name == sheetName)
{
_sheet.Activate();
break;
}
}
SetValidationAddressList();
}
/// <summary>
/// 入力規則の条件を持つセル番地のリストを設定します.
/// </summary>
private void SetValidationAddressList()
{
Range range = _sheet.UsedRange.SpecialCells(XlCellType.xlCellTypeAllValidation);
_validationAddressList = range.Address.Replace(":", ",").Replace("$", "").Split(new char[] { ',' });
}
/// <summary>
/// セルレンジを指定して値を読み込む
/// </summary>
/// <param name="cell_range"></param>
/// <returns></returns>
public string ReadCellStringValue(string cell_range)
{
_range = _sheet.Range[cell_range];
if (_range.Value is string)
{
return _range.Value;
}
else if (_range.Value is double)
{
return ((double)_range.Value).ToString();
}
return _range.Value;
}
/// <summary>
/// セルに値を書き込みます
/// </summary>
/// <param name="cell_range"></param>
/// <param name="value"></param>
public bool WriteValue(string cell_range, string value)
{
_range = _sheet.Range[cell_range];
// 入力規則を持つセルであれば
if (_validationAddressList.Contains(cell_range))
{
// 入力規則の内容を取得してチェック
string fml = _sheet.Range[cell_range].Validation.Formula1.Replace(" ","");
if(fml.Split(new char[] { ',' }).Contains(value) == false)
{
System.Windows.Forms.MessageBox.Show("設定できない値です。", "Excelサンプル");
return false ;
}
}
_range.Value = value;
return true;
}
/// <summary>
/// ファイルを保存します.
/// </summary>
/// <param name="isOverwrite"></param>
public void SaveFile(bool isOverwrite)
{
if (_book.Saved == false)
{
if (isOverwrite)
{
_book.Save();
}
else
{
System.Windows.Forms.SaveFileDialog ofDialog = new System.Windows.Forms.SaveFileDialog();
ofDialog.InitialDirectory = @"C:\wk\tmp";
ofDialog.RestoreDirectory = true;
ofDialog.FileName = _book.Name;
ofDialog.Title = "名前を付けて保存";
//ダイアログを表示する
if (ofDialog.ShowDialog() == System.Windows.Forms.DialogResult.OK)
{
_book.SaveAs(ofDialog.FileName);
}
}
}
}
/// <summary>
/// COMオブジェクトの解放、破棄をする.
/// </summary>
/// <param name="disposing"></param>
protected virtual void Dispose(bool disposing)
{
if (!disposedValue)
{
if (disposing)
{
// TODO: マネージ状態を破棄します (マネージ オブジェクト)。
}
if (_range != null)
{
Marshal.ReleaseComObject(_range);
}
if (_ranges != null)
{
Marshal.ReleaseComObject(_ranges);
}
if (_sheet != null)
{
Marshal.ReleaseComObject(_sheet);
}
if (_sheets != null)
{
Marshal.ReleaseComObject(_sheets);
}
if (_book != null)
{
_book.Close();
Marshal.ReleaseComObject(_book);
}
if (_books != null)
{
_books.Close();
Marshal.ReleaseComObject(_books);
}
if (_app != null)
{
_app.Quit();
Marshal.ReleaseComObject(_app);
}
disposedValue = true;
}
}
/// <summary>
/// ファイナライザー
/// </summary>
~ExcelManager()
{
// このコードを変更しないでください。クリーンアップ コードを上の Dispose(bool disposing) に記述します。
Dispose(false);
}
/// <summary>
/// 破棄します
/// </summary>
public void Dispose()
{
Dispose(true);
GC.SuppressFinalize(this);
}
#endregion
}
}
予め、SpecialCells メソッドを利用して入力規則が設定されているセルをチェックし、
書き込み時に対象セルだった場合、書き込み値とリスト値のチェックをするようにしました。
Form1.cs
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using System.Runtime.InteropServices;
using Excel = Microsoft.Office.Interop.Excel;
namespace SampleExcel
{
public partial class Form1 : Form
{
public Form1()
{
InitializeComponent();
}
/// <summary>
/// 参照ボタンクリック
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void btn_file_Click(object sender, EventArgs e)
{
OpenFileDialog ofDialog = new OpenFileDialog();
ofDialog.InitialDirectory = @"C:\wk\tmp";
ofDialog.RestoreDirectory = true;
ofDialog.Title = "対象ファイルを選択";
//ダイアログを表示する
if (ofDialog.ShowDialog() == DialogResult.OK)
{
this.txb_file.Text = ofDialog.FileName;
}
}
/// <summary>
/// 書き込みボタンクリック
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void btn_output_Click(object sender, EventArgs e)
{
try
{
// ファイルを指定して ExcelManager を生成
bool result = false;
using (var excel = new ExcelManager(txb_file.Text))
{
// 書き込みと同時に書き込みが出来たかどうかを返してもらう
result = excel.WriteValue(txb_cell_range.Text, txb_value.Text);
excel.SaveFile(false);
}
if (result)
{
MessageBox.Show("書き込みました。", "Excelサンプル");
}
else
{
MessageBox.Show("書き込みに失敗しました", "Excelサンプル");
}
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
}
/// <summary>
/// 読み込みボタンクリック
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void btn_input_Click(object sender, EventArgs e)
{
try
{
// ファイルを指定して ExcelManager を生成
using (var excel = new ExcelManager(txb_file.Text))
{
// 指定した箇所の値を読み込み表示
MessageBox.Show(excel.ReadCellStringValue(txb_cell_range.Text), "Excelサンプル");
}
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
}
}
}
Formの方では、書き込みが出来たかどうかを返してもらってメッセージを分岐。
まとめ
予め入力規則が設定されているセルを取得しておく理由は、
入力規則が設定されていないセルに対して Validation.Formula1 で入力規則の内容を取得しようとするとエラーを投げてくるためです。
Null 判定もできず、こんな形に。。
もっといい方法があるとは思うんですけどねぇ。
何かのお役に立てれば。
ではでは。