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); }