private void CreateDataSheet(ExcelHelp xls, DataTableInfo tableInfo)
{
DenshowDataAccesser dba = new DenshowDataAccesser();
NpgTableLayoutInfo info = dba.GetTableLayout(tableInfo.TableName);
Excel.Worksheet templateSheet = xls.WorkBook.Sheets["Template"];
Excel.Worksheet sheet = xls.CreateSheet(tableInfo.SheetName, templateSheet);
int colIndex = 0;
xls.WriteValue(sheet, 1, 2, info.TableName);
foreach (NpgColumnInfo column in info.Columns)
{
colIndex++;
xls.WriteValue(sheet, STRAT_ROW, colIndex, column.ColumnName);
if (!string.IsNullOrEmpty(column.DisplayName))
{
string comment = column.DisplayName + "\n" + column.GetSqlType();
sheet.Range[xls.GetColumnName(colIndex, STRAT_ROW)].AddComment(comment);
}
else
{
string comment = column.GetSqlType();
sheet.Range[xls.GetColumnName(colIndex, STRAT_ROW)].AddComment(comment);
}
if (column.IsPrimaryKey)
{
Excel.Range cell = sheet.Range[xls.GetColumnName(colIndex, STRAT_ROW)];
cell.Font.Color = Excel.XlRgbColor.rgbRed;
}
}
xls.AddListObject(sheet, STRAT_ROW, STRAT_ROW + 1, colIndex, tableInfo.TableName);
SetColumnFormat(info, sheet);
sheet.Columns.AutoFit();
}
private void CreateDataSheet(ExcelHelp xls, DataTableInfo tableInfo, bool isTarget)
{
DatabaseAcsesser dba = new DatabaseAcsesser();
DatabaseAcsesser.DbConnections connType = isTarget ? DatabaseAcsesser.DbConnections.TargetDbConnection
: DatabaseAcsesser.DbConnections.SourceDbConnection;
TableLayoutInfo info = dba.GetTableLayout(connType, tableInfo.TableName);
Excel.Worksheet templateSheet = xls.WorkBook.Sheets["Template"];
Excel.Worksheet sheet = xls.CreateSheet(tableInfo.SheetName, templateSheet);
int colIndex = 0;
xls.WriteValue(sheet, 1, 2, info.TableName);
foreach (ColumnInfo column in info.Columns)
{
colIndex++;
xls.WriteValue(sheet, STRAT_ROW, colIndex, column.ColumnName);
if (!string.IsNullOrEmpty(column.DisplayName))
{
string comment = column.DisplayName + "\n" + column.GetSqlType();
sheet.Range[xls.GetColumnName(colIndex, STRAT_ROW)].AddComment(comment);
}
if (column.IsPrimaryKey)
{
Excel.Range cell = sheet.Range[xls.GetColumnName(colIndex, STRAT_ROW)];
cell.Font.Color = Excel.XlRgbColor.rgbRed;
}
}
xls.AddListObject(sheet, STRAT_ROW, STRAT_ROW + 1, colIndex, tableInfo.TableName);
SetColumnFormat(info, sheet);
sheet.Columns.AutoFit();
sheet.Tab.Color = isTarget ? Excel.XlRgbColor.rgbBlue : Excel.XlRgbColor.rgbRed;
}
public void CreateDBScript(LayoutType layout, string tableLayoutFile, ScriptOptions opt)
{
base.Report(Resource.StringTable.Messages.OpenDesignFile);
using (ExcelHelp xls = new ExcelHelp(tableLayoutFile))
{
//int sheectCount = xls.WorkBook.Sheets.Count;
base.Report(Resource.StringTable.Messages.ReadingTableList);
System.Data.DataTable dttList = GetTableList(layout);
//System.Data.DataTable dttList = GetTableList(xls,layout );
base.SetStep(dttList.Rows.Count, Resource.StringTable.Messages.CreatingSqlScript);
foreach (System.Data.DataRow row in dttList.Rows)
{
string sheetName = Utility.DBToString(row["DisplayName"]);
string tabName = Utility.DBToString(row["TableName"]);
try
{
TableLayoutInfo tableInfo = ReadTableLayout(xls, sheetName, layout);
//Script出力
CreateSqlScript(opt, tableInfo);
ReportStep(Resource.StringTable.Messages.CreatedTableSqlScript, tableInfo.DisplayName, tableInfo.TableName);
}
catch (Exception ex)
{
Logging.WriteLine("/*");
Logging.WriteLine(Resource.StringTable.Messages.ScriptCreateFailed, sheetName, tabName);
Logging.Exception("", ex);
Logging.WriteLine("*/");
}
}
Report(Resource.StringTable.Messages.ProcessFinished);
xls.Close();
}
}
private System.Data.DataTable GetTableList(ExcelHelp xls, LayoutType layoutType)
{
string sheetName = "";
string tableNameCol = "";
string tableDisplayCol = "";
string commentCol = "";
int startRow = 0;
switch (layoutType)
{
case LayoutType.Live:
sheetName = "テーブル一覧";
tableNameCol = "AJ";
tableDisplayCol = "J";
commentCol = "T";
startRow = 5;
break;
default:
sheetName = "テーブル一覧（Live）";
tableNameCol = "F";
tableDisplayCol = "J";
commentCol = "S";
startRow = 5;
break;
}
return(xls.GetTableData(sheetName,
new string[] { tableNameCol, tableDisplayCol, commentCol },
new string[] { "TableName", "DisplayName", "Comment" },
startRow));
}
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);
}
}
private TableLayoutInfo ReadTableLayout(ExcelHelp xls, string sheetName, LayoutType layout)
{
if (layout == LayoutType.Live)
{
return(ReadLiveTableLayout(xls, sheetName));
}
else
{
return(ReadSeedTableLayout(xls, sheetName));
}
}
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);
}
}
}
}
public void CreateDBScript(TableCreateInfo info)
{
string outPutPath = System.IO.Path.ChangeExtension(info.LayoutFileName, "sql");
Logging.OutputFileName = outPutPath;
try
{
base.Report(Resource.StringTable.Messages.OpenDesignFile);
using (ExcelHelp xls = new ExcelHelp(info.LayoutFileName))
{
//int sheectCount = xls.WorkBook.Sheets.Count;
base.Report(Resource.StringTable.Messages.ReadingTableList);
System.Data.DataTable dttList = GetTableList(info.TableListSheetName);
base.SetStep(dttList.Rows.Count, Resource.StringTable.Messages.CreatingSqlScript);
foreach (System.Data.DataRow row in dttList.Rows)
{
DataTableInfo tbInfo = new DataTableInfo(row);
try
{
TableLayoutInfo tableInfo = ReadTableLayout(xls, tbInfo.SheetName, info.LayoutKind);
//Script出力
CreateSqlScript(info.Options, tableInfo);
ReportStep(Resource.StringTable.Messages.CreatedTableSqlScript, tableInfo.DisplayName, tableInfo.TableName);
}
catch (Exception ex)
{
Logging.WriteLine("/*");
Logging.WriteLine(Resource.StringTable.Messages.ScriptCreateFailed, tbInfo.SheetName, tbInfo.TableName);
Logging.Exception("", ex);
Logging.WriteLine("*/");
}
}
Report(Resource.StringTable.Messages.ProcessFinished);
xls.Close();
}
}
finally
{
Logging.OutputFileName = "";
}
}
private TableLayoutInfo ReadSeedTableLayout(ExcelHelp xls, string sheetName)
{
Excel.Worksheet sheet = xls.WorkBook.Sheets[sheetName];
string tableName = Utility.DBToString(sheet.Range[SeedLayoutInfo.TABLE_NAME].Value);
TableLayoutInfo tableInfo = new TableLayoutInfo(tableName)
{
DisplayName = sheetName
};
//列作成
int rowIndex = SeedLayoutInfo.START_ROW;
int columnId = 0;
string no = Utility.DBToString(sheet.Range[SeedLayoutInfo.COLUMN_NO + rowIndex].Value);
string columnName = Utility.DBToString(sheet.Range[SeedLayoutInfo.COLUMN_NAME + rowIndex].Value).Trim();
while (!string.IsNullOrEmpty(columnName) || !string.IsNullOrEmpty(no))
{
//ColumnName
ColumnInfo column = new ColumnInfo();
if (int.TryParse(no, out columnId))
{
column.ColumnId = columnId;
}
column.ColumnName = columnName;
string displayName = Utility.DBToString(sheet.Range[SeedLayoutInfo.COLUMN_DISPLAY + rowIndex].Value).Trim();
if (sheet.Range[SeedLayoutInfo.COLUMN_DISPLAY + rowIndex].MergeCells == true)
{
displayName = Utility.DBToString(sheet.Range[SeedLayoutInfo.COLUMN_DISPLAY + rowIndex].MergeArea[1, 1].Value);
string displayName2 = Utility.DBToString(sheet.Range[SeedLayoutInfo.COLUMN_DISPLAY2 + rowIndex].Value).Trim();
displayName = displayName + " " + displayName2;
displayName = displayName.Replace("\n", "");
}
column.DisplayName = displayName;
column.DataType = Utility.DBToString(sheet.Range[SeedLayoutInfo.COLUMN_TYPE + rowIndex].Value).Trim();
//Length
string lenVal = Utility.DBToString(sheet.Range[SeedLayoutInfo.COLUMN_LENGTH + rowIndex].Value).Trim();
if (!string.IsNullOrWhiteSpace(lenVal))
{
int length = 0;
if (int.TryParse(lenVal, out length))
{
column.Length = length;
}
else
{
if (lenVal.ToLower().Equals("max"))
{
column.Length = -1;
}
}
}
//Scale
string scaleVal = Utility.DBToString(sheet.Range[SeedLayoutInfo.COLUMN_SCALE + rowIndex].Value).Trim();
if (!string.IsNullOrWhiteSpace(scaleVal))
{
int scale = 0;
if (int.TryParse(lenVal, out scale))
{
column.NumericPrecision = column.Length;
column.NumericScale = scale;
}
}
//Nullable
string nullable = Utility.DBToString(sheet.Range[SeedLayoutInfo.COLUMN_NULLABLE + rowIndex].Value);
if (!string.IsNullOrWhiteSpace(nullable) && nullable.Equals("N"))
{
column.Nullable = false;
}
else
{
column.Nullable = true;
}
//Primary Key
string indexNo = Utility.DBToString(sheet.Range[SeedLayoutInfo.COLUMN_PRIMARYINDEX + rowIndex].Value);
int indexId = 0;
if (!string.IsNullOrWhiteSpace(indexNo) && int.TryParse(indexNo, out indexId))
{
column.IndexColumnId = indexId;
}
//InPrimary
if (column.IndexColumnId > 0)
{
column.IsPrimaryKey = true;
}
if (!string.IsNullOrWhiteSpace(column.ColumnName) && !string.IsNullOrWhiteSpace(column.DataType))
{
tableInfo.Columns.Add(column);
}
//Comment
column.Comment = Utility.DBToString(sheet.Range[SeedLayoutInfo.COLUMN_COMMENT + rowIndex].Value).Trim();
rowIndex++;
no = Utility.DBToString(sheet.Range[SeedLayoutInfo.COLUMN_NO + rowIndex].Value);
columnName = Utility.DBToString(sheet.Range[SeedLayoutInfo.COLUMN_NAME + rowIndex].Value).Trim();
}
return(tableInfo);
}
private TableLayoutInfo ReadLiveTableLayout(ExcelHelp xls, string sheetName)
{
Excel.Worksheet sheet = xls.WorkBook.Sheets[sheetName];
string tableName = Utility.DBToString(sheet.Range[LiveLayoutInfo.TABLE_NAME].Value);
TableLayoutInfo tableInfo = new TableLayoutInfo(tableName)
{
DisplayName = sheet.Range[LiveLayoutInfo.TABLE_DISPLAY_NAME].Value
};
//列作成
int rowIndex = LiveLayoutInfo.START_ROW;
string No = Utility.DBToString(sheet.Range[LiveLayoutInfo.COLUMN_NO + rowIndex].Value);
int columnId = 0;
while (!string.IsNullOrEmpty(No) && int.TryParse(No, out columnId))
{
//ColumnName
ColumnInfo column = new ColumnInfo();
column.ColumnId = columnId;
column.ColumnName = Utility.DBToString(sheet.Range[LiveLayoutInfo.COLUMN_NAME + rowIndex].Value).Trim();
column.DisplayName = Utility.DBToString(sheet.Range[LiveLayoutInfo.COLUMN_DISPLAY + rowIndex].Value).Trim();
column.DataType = Utility.DBToString(sheet.Range[LiveLayoutInfo.COLUMN_TYPE + rowIndex].Value).Trim();
//Length
string lenVal = Utility.DBToString(sheet.Range[LiveLayoutInfo.COLUMN_LENGTH + rowIndex].Value).Trim();
if (!string.IsNullOrWhiteSpace(lenVal))
{
int length = 0;
if (int.TryParse(lenVal, out length))
{
column.Length = length;
}
else if (lenVal.Contains(","))
{
int num = 0;
string[] sect = lenVal.Split(',');
if (int.TryParse(sect[0], out num))
{
column.NumericPrecision = num;
}
if (int.TryParse(sect[1], out num))
{
column.NumericScale = num;
}
}
else
{
if (lenVal.ToLower().Equals("max"))
{
column.Length = -1;
}
}
}
//Nullable
string nullable = Utility.DBToString(sheet.Range[LiveLayoutInfo.COLUMN_NULLABLE + rowIndex].Value);
column.Nullable = (!string.IsNullOrWhiteSpace(nullable));
//InPrimary
string InPrimary = Utility.DBToString(sheet.Range[LiveLayoutInfo.COLUMN_ISPRIMARY + rowIndex].Value);
column.IsPrimaryKey = (!string.IsNullOrWhiteSpace(InPrimary));
//Index Key
string indexNo = Utility.DBToString(sheet.Range[LiveLayoutInfo.COLUMN_INDEX + rowIndex].Value);
int indexId = 0;
if (!string.IsNullOrWhiteSpace(indexNo) && int.TryParse(indexNo, out indexId))
{
column.IndexColumnId = indexId;
}
if (!string.IsNullOrWhiteSpace(column.ColumnName) && !string.IsNullOrWhiteSpace(column.DataType))
{
tableInfo.Columns.Add(column);
}
else
{
Logging.WriteLine("-- ERROR:: {0}-{1} 行:{2} {3}",
sheetName, tableInfo.TableName, No,
string.IsNullOrWhiteSpace(column.ColumnName) ? "列名なし" : "型なし");
}
//Comment
column.Comment = Utility.DBToString(sheet.Range[LiveLayoutInfo.COLUMN_COMMENT + rowIndex].Value).Trim();
rowIndex++;
No = Utility.DBToString(sheet.Range[LiveLayoutInfo.COLUMN_NO + rowIndex].Value);
}
return(tableInfo);
}