private System.Data.DataTable GetTableList(string sheetName)
{
using (ExcelAccessor xlsAdo = new ExcelAccessor(Config.DbTableListSettingFile))
{
return(xlsAdo.GetTableData(sheetName, "TableList", null));
}
}
private DataTable GetCheckTableList(ExcelAccessor xlsAdo)
{
string sheetName = "目次";
string sql = "Select * from [" + sheetName + "$]";
return(xlsAdo.GetTableData("TableIndex", null, sql));
}
public void CheckDatas(string filePath, string exportFolder)
{
base.Report("チェックテーブル一覧を取得しています");
if (string.IsNullOrEmpty(exportFolder) || !System.IO.Directory.Exists(exportFolder))
{
exportFolder = Config.ReportExportFolder;
}
using (this.xlsReport = new ExcelHelp(Config.ReportTemplateFile))
{
this.currentRow = STRAT_ROW;
using (ExcelAccessor xlsAdo = new ExcelAccessor(filePath))
{
DataTable dtt = GetCheckTableList(xlsAdo);
base.SetStep(dtt.Rows.Count, "対象テーブルをチェックしています");
foreach (DataRow row in dtt.Rows)
{
DataTableInfo tabInfo = new DataTableInfo(row);
CheckExpectData(xlsAdo, tabInfo);
base.ReportStep("{0}\n{1}", tabInfo.DisplayName, tabInfo.TableName);
}
}
base.Report("チェック結果報告を出力しています。");
//枠設定
Excel.Worksheet sheet = this.xlsReport.WorkBook.Sheets[REPORT_SHEET_NAME];
this.xlsReport.AddListObject(sheet, 2, currentRow, 5, "ERRORLIST");
//報告ファイルを保存する
string reportFile = "DataCheckReport_" + DateTime.Now.ToString("yyMMdd_HHmmss") + ".xlsx";
reportFile = System.IO.Path.Combine(exportFolder, reportFile);
this.xlsReport.Save(reportFile);
}
}
public void CreateDBInitData(string[] sheetNames, bool isTarget)
{
DataKind kind = isTarget ? DataKind.LiveInitData : DataKind.SeedInitData;
using (ExcelAccessor xlsAdo = new ExcelAccessor(Config.GetTransferSettingFile(isTarget)))
{
//テーブル一覧取得
foreach (string sheetName in sheetNames)
{
using (ExcelHelp xls = new ExcelHelp(Config.TemplateFile))
{
xls.BeginUpdate();
//接続文字列設定
string connStr = isTarget ? Config.TargetDbConnection : Config.SourceDbConnection;
string adoConnstr = ChangeConnectStringForADO(connStr);
Excel.Worksheet settingSheet = xls.WorkBook.Sheets["設定"];
xls.WriteValue(settingSheet, 1, 2, adoConnstr);
//目次を取得
Excel.Worksheet indexSheet = xls.WorkBook.Sheets["目次"];
int rowNo = 2;
base.Report("テーブル一覧を取得しています");
DataTable tableList = GetSettingTableList(xlsAdo, sheetName);
//移行元データシート作成
base.SetStep(tableList.Rows.Count, "データシートを作成しています");
foreach (DataRow row in tableList.Rows)
{
DataTableInfo tabInfo = new DataTableInfo(row);
CreateDataSheet(xls, tabInfo, isTarget);
//目次作成
xls.WriteValue(indexSheet, rowNo, 1, tabInfo.DisplayName);
xls.WriteValue(indexSheet, rowNo, 2, tabInfo.TableName);
xls.WriteValue(indexSheet, rowNo, 4, tabInfo.SheetName);
//リンク追加
Excel.Range anchor = xls.GetRange(indexSheet, 1, 1, rowNo, rowNo);
indexSheet.Hyperlinks.Add(Anchor: anchor, Address: "", SubAddress: tabInfo.TableName, TextToDisplay: tabInfo.DisplayName);
rowNo++;
base.ReportStep("{0}\n{1}", tabInfo.DisplayName, tabInfo.TableName);
}
xls.AddListObject(indexSheet, 1, rowNo, 4, "Index");
indexSheet.Columns.AutoFit();
indexSheet.Columns["D:D"].EntireColumn.Hidden = true;
indexSheet.Select();
base.Report("「{0}」のデータシートを保存しています", sheetName);
xls.EndUpdate();
xls.Save(Config.GetDataSheetSavePath(sheetName), Excel.XlFileFormat.xlOpenXMLWorkbookMacroEnabled);
}
}
}
}
/// <summary>
/// データ初期化する
/// </summary>
/// <param name="filePath"></param>
/// <param name="isTarget"></param>
public void DoInitlize(string filePath, string batchFile)
{
string outPutPath = System.IO.Path.ChangeExtension(filePath, "sql");
Logging.OutputFileName = outPutPath;
try
{
base.Report("テーブル一覧を取得しています");
using (ExcelAccessor xlsAdo = new ExcelAccessor(filePath))
{
DataTable tableList = GetSheetTableDatas(xlsAdo, "目次");
base.SetStep(tableList.Rows.Count, "データクリア用SQL文を作成しています。");
Logging.WriteLine("/*** データクリア ***/");
DataTableInfo tableInfo = null;
Logging.WriteLine("set search_path to " + Config.DenshowDbSchema + " ;");
foreach (DataRow row in tableList.Rows)
{
tableInfo = new DataTableInfo(row);
string sql = string.Format("TRUNCATE TABLE \"{0}\" CASCADE;", tableInfo.TableName);
Logging.WriteLine(sql);
base.ReportStep("{0}\n{1}", tableInfo.DisplayName, tableInfo.TableName);
}
base.SetStep(tableList.Rows.Count, "インサートSQL文を作成しています。");
foreach (DataRow row in tableList.Rows)
{
tableInfo = new DataTableInfo(row);
DataTable dtt = xlsAdo.GetTableData(tableInfo.SheetName, tableInfo.TableName, null, 2, Config.MaxDataCount + 2);
string sql = CreateSheetDataSql(tableInfo, dtt, true);
Logging.WriteLine(sql);
base.ReportStep("{0}\n{1}", tableInfo.DisplayName, tableInfo.TableName);
}
}
string connStr = Config.DenshowDbConnection;
//Batファイル作成する
//string batFilePath = System.IO.Path.ChangeExtension(filePath, "bat");
//string cmdSqlBat = GetSqlCmdLine(connStr,batchFile);
//System.IO.File.WriteAllText(batFilePath, cmdSqlBat, System.Text.Encoding.Default);
base.Report("SQL文を作成しました。");
}
finally
{
Logging.OutputFileName = "";
}
}
private void CheckExpectData(ExcelAccessor xlsAdo, DataTableInfo info)
{
try
{
DatabaseAcsesser dbAdo = new DatabaseAcsesser();
DataTable dttResult = dbAdo.GetResultData(info.TableName);
DataTable dttSchema = dttResult.Clone();
DataTable dttExpect = xlsAdo.GetTableData(info.SheetName, info.TableName, dttSchema, 2, Config.MaxDataCount + 2);
//データ比較する
DiffDataSouce(info, dttExpect, dttResult);
}
catch (Exception ex)
{
Logging.Exception("", ex);
throw ex;
}
}
private System.Data.DataTable GetTableList(LayoutType layoutType)
{
using (ExcelAccessor xlsAdo = new ExcelAccessor(Config.TemplateFile))
{
string sheetName = "";
string tableName = "";
switch (layoutType)
{
case LayoutType.Live:
//sheetName = "テーブル一覧";
sheetName = "テーブル一覧２";
tableName = "TableIndex";
break;
default:
sheetName = "旧テーブル一覧";
tableName = "OldTableIndex";
break;
}
return(xlsAdo.GetTableData(sheetName, tableName, null));
}
}
private DataTable GetSheetTableDatas(ExcelAccessor xlsAdo, string sheetName)
{
string sql = "Select * from [" + sheetName + "$]";
return(xlsAdo.GetTableData("tableList", null, sql));
}
private DataTable GetSettingTableList(ExcelAccessor xlsAdo, string sheetName)
{
string sql = "Select * from [" + sheetName + "$] where [No] IS NOT NULL";
return(xlsAdo.GetTableData("tableList", null, sql));
}
