Exemplo n.º 1
0
        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();
        }
Exemplo n.º 2
0
        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;
        }
Exemplo n.º 3
0
 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();
     }
 }
Exemplo n.º 4
0
        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));
        }
Exemplo n.º 5
0
 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);
     }
 }
Exemplo n.º 6
0
 private TableLayoutInfo ReadTableLayout(ExcelHelp xls, string sheetName, LayoutType layout)
 {
     if (layout == LayoutType.Live)
     {
         return(ReadLiveTableLayout(xls, sheetName));
     }
     else
     {
         return(ReadSeedTableLayout(xls, sheetName));
     }
 }
Exemplo n.º 7
0
        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);
                    }
                }
            }
        }
Exemplo n.º 8
0
        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 = "";
            }
        }
Exemplo n.º 9
0
        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);
        }
Exemplo n.º 10
0
        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);
        }