public void ブック情報登録()
{
this.StiffForm_Load(null, null);
int reccnt = this.excelFiles.Rows.Count;
var info = new BookInfo
{
FileName = "TestBook.xlxs",
//
Author = "小林礼明",
Title = "タイトル",
Subject = "テスト用ブック",
LastSaveTime = "2013/09/23 7:35:35",
Company = "個人",
Manager = "わし"
};
this.addBookInfo(info);
Assert.True(reccnt + 1 == this.excelFiles.Rows.Count, "レコード数");
var row = this.excelFiles.Rows[reccnt];
Assert.True( (int) row["Seq" ] == 1 , "Seq" );
Assert.True( (string)row["File" ] == info.FileName , "File" );
Assert.True( (string)row["Author" ] == info.Author , "Author" );
Assert.True( (string)row["Title" ] == info.Title , "Title" );
Assert.True( (string)row["Subject" ] == info.Subject , "Subject" );
Assert.True( (string)row["Update" ] == info.LastSaveTime , "Update" );
Assert.True( (string)row["Company" ] == info.Company , "Company" );
Assert.True( (string)row["Manager" ] == info.Manager , "Manager" );
}
/// <summary>
/// ブック情報をデータテーブルへ追加する
/// </summary>
/// <param name="info"></param>
private void addBookInfo(BookInfo info)
{
var row = this.excelFiles.NewRow();
row["Seq" ] = excelFiles.Rows.Count + 1;
row["File" ] = info.FileName;
row["Author" ] = info.Author;
row["Title" ] = info.Title;
row["Subject" ] = info.Subject;
row["Update" ] = info.LastSaveTime;
row["Company" ] = info.Company;
row["Manager" ] = info.Manager;
row["結果"] = (info.Result == null) ? "" : (info.Result == true ? "OK" : "NG"); ;
excelFiles.Rows.Add(row);
return;
}
/// <summary>
/// ブック情報をデータテーブルへ追加する
/// </summary>
/// <param name="info"></param>
private void addBookInfo(BookInfo info)
{
var row = this.excelFiles.NewRow();
row["Seq" ] = excelFiles.Rows.Count + 1;
row["File" ] = info.FileName;
row["Author" ] = info.Author;
row["Title" ] = info.Title;
row["Subject" ] = info.Subject;
row["Update" ] = info.LastSaveTime;
row["Company" ] = info.Company;
row["Manager" ] = info.Manager;
row["セル位置" ] = (info.CheckResult[0] == true ? "OK" : "NG");
row["倍率" ] = (info.CheckResult[1] == true ? "OK" : "NG");
row["枠線" ] = (info.CheckResult[2] == true ? "OK" : "NG");
row["表示" ] = (info.CheckResult[3] == true ? "OK" : "NG");
row["シート" ] = (info.CheckResult[4] == true ? "OK" : "NG");
excelFiles.Rows.Add(row);
return;
}
public void ブック情報登録_重複()
{
this.StiffForm_Load(null, null);
int reccnt = this.excelFiles.Rows.Count;
var info = new BookInfo
{
FileName = "TestBook.xlxs",
//
Author = "小林礼明",
Title = "タイトル",
Subject = "テスト用ブック",
LastSaveTime = "2013/09/23 7:35:35",
Company = "個人",
Manager = "わし"
};
this.addBookInfo(info);
Assert.True(true, "レコード１");
this.addBookInfo(info);
}
/// <summary>
/// ブックに新しいプロパティを設定する
/// </summary>
/// <param name="oBook"></param>
/// <param name="info"></param>
private void SetInformations(Excel.Workbook oBook, BookInfo info)
{
this.SetBuiltinProperty(oBook, "Author" , info.Author);
this.SetBuiltinProperty(oBook, "Title" , info.Title);
this.SetBuiltinProperty(oBook, "Subject", info.Subject);
this.SetBuiltinProperty(oBook, "Manager", info.Manager);
this.SetBuiltinProperty(oBook, "Company", info.Company);
}
/// <summary>
/// 新しい状態、プロパティで保存する
/// </summary>
public void Unification(BookInfo [] infomations)
{
// アプリケーション起動
this.CreateApplication();
foreach( var info in infomations)
{
info.Result = false;
//
Excel.Workbook oBook = null;
Excel.Sheets oSheets = null;
Excel.Worksheet oTopSheet = null;
int topIdx = 0;
string filename = info.FileName;
try
{
// ファイルオープン
oBook = this.OpenBook(filename);
if (oBook == null)
{
return;
}
// プロパティ設定
{
info.Title = "";
info.Subject = "";
info.Company = "";
info.Manager = "";
}
this.SetInformations(oBook, info);
// 状態変更
oSheets = oBook.Worksheets;
for (int i = 1; i <= oSheets.Count; ++i)
{
Excel.Worksheet oSheet = null;
Excel.Range oCells = null;
Excel.Range oRange = null;
try
{
oSheet = (Excel.Worksheet)oSheets[i];
if (oSheet.Visible != Microsoft.Office.Interop.Excel.XlSheetVisibility.xlSheetVisible)
{
continue;
}
oSheet.Activate();
// 最初に表示シートを保持しておく
if (topIdx == 0)
{
topIdx = i;
}
// A1セル
oCells = oSheet.Cells;
oRange = ((Excel.Range)oCells[1, 1]);
oRange.Select();
// 表示倍率
this._app.ActiveWindow.Zoom = 100;
// 枠線
this._app.ActiveWindow.DisplayGridlines = false;
// 表示モード
this._app.ActiveWindow.View = Microsoft.Office.Interop.Excel.XlWindowView.xlNormalView;
}
finally
{
if (oRange != null)
{
Marshal.ReleaseComObject(oRange);
}
oRange = null;
if (oCells != null)
{
Marshal.ReleaseComObject(oCells);
}
oCells = null;
if (oSheet != null)
{
Marshal.ReleaseComObject(oSheet);
}
oSheet = null;
}
}
if (topIdx != 0)
{
oTopSheet = (Excel.Worksheet)oSheets[topIdx];
oTopSheet.Activate();
}
// 保存
oBook.Close(true, filename, Type.Missing);
Marshal.ReleaseComObject(oBook);
oBook = null;
info.Result = true;
}
finally
{
if (oTopSheet != null)
{
Marshal.ReleaseComObject(oTopSheet);
}
oTopSheet = null;
if (oSheets != null)
{
Marshal.ReleaseComObject(oSheets);
}
oSheets = null;
if (oBook != null)
{
oBook.Close(false, filename, Type.Missing);
Marshal.ReleaseComObject(oBook);
}
oBook = null;
}
}
}
/// <summary>
/// Excelブックの各種情報取得
/// </summary>
public BookInfo GetInformations(string filename)
{
Excel.Workbook oBook = null;
BookInfo info = null ;
try
{
// アプリケーション起動
this.CreateApplication();
// ファイルオープン
oBook = this.OpenBook(filename);
if (oBook == null)
{
return null;
}
// ブック情報取得および格納
info = new BookInfo();
info.FileName = filename;
info.Author = this.GetBuiltinProperty(oBook, "Author");
info.Title = this.GetBuiltinProperty(oBook, "Title");
info.Subject = this.GetBuiltinProperty(oBook, "Subject");
info.Manager = this.GetBuiltinProperty(oBook, "Manager");
info.Company = this.GetBuiltinProperty(oBook, "Company");
info.LastSaveTime = this.GetBuiltinProperty(oBook, "Last Save Time");
}
finally
{
if (oBook != null)
{
oBook.Close(false, filename, Type.Missing);
Marshal.ReleaseComObject(oBook);
}
oBook = null;
}
return info;
}
/// <summary>
/// Excelブックの各種情報取得
/// </summary>
public BookInfo GetBookInformations(string filename)
{
Excel.Workbook oBook = null;
Excel.Worksheet oSheet = null;
BookInfo info = null ;
try
{
// アプリケーション起動
this.CreateApplication();
// ファイルオープン
oBook = this.OpenBook(filename);
if (oBook == null)
{
return null;
}
// ブック情報取得および格納
info = new BookInfo();
info.FileName = filename;
info.Author = this.GetBuiltinProperty(oBook, "Author");
info.Title = this.GetBuiltinProperty(oBook, "Title");
info.Subject = this.GetBuiltinProperty(oBook, "Subject");
info.Manager = this.GetBuiltinProperty(oBook, "Manager");
info.Company = this.GetBuiltinProperty(oBook, "Company");
info.LastSaveTime = this.GetBuiltinProperty(oBook, "Last Save Time");
info.Sheets = this.GetSheetInformations(oBook);
info.FirstVisibleSheetName
= this.GetFirstVisibleSheetName(oBook);
// 選択シートが先頭シートになっているかの確認
oSheet = (Excel.Worksheet)oBook.ActiveSheet;
if (oSheet.Name != info.FirstVisibleSheetName)
{
info.CheckResult[4] = false;
}
}
finally
{
if (oSheet != null)
{
Marshal.ReleaseComObject(oSheet);
}
oSheet = null;
if (oBook != null)
{
oBook.Close(false, filename, Type.Missing);
Marshal.ReleaseComObject(oBook);
}
oBook = null;
}
return info;
}
/// <summary>
///
/// </summary>
/// <param name="criteria"></param>
/// <param name="book"></param>
public void CheckSheetInformations(SheetInfo criteria, BookInfo book)
{
try
{
book.Result = true;
foreach (var sheet in book.Sheets)
{
this.CompareSheetInfo(criteria, sheet, book.CheckResult);
}
}
finally
{
}
return;
}