Exemple #1
0
        private System.Data.DataTable GetTableList(ExcelHelp xls, TemplateInfo.DocumentTemplateRow layoutInfo)
        {
            string sheetName = layoutInfo.IndexSheet;

            Excel.Worksheet sheet       = xls.WorkBook.Sheets[sheetName];
            string[]        columnNames = { "TableName", "DisplayName", "Comment", "SheetName" };
            string[]        columns     = { layoutInfo.IndexSheet_TableName, layoutInfo.IndexSheet_DisplayName, layoutInfo.IndexSheet_Summary, layoutInfo.IndexSheet_Link };

            System.Data.DataTable dttSource = new System.Data.DataTable();
            foreach (string column in columnNames)
            {
                dttSource.Columns.Add(column, typeof(string));
            }
            int maxRow = sheet.UsedRange.Rows.Count;

            for (int r = layoutInfo.IndexSheet_StartRow; r <= maxRow; r++)
            {
                DataRow row = dttSource.NewRow();
                for (int c = 0; c < columns.Length; c++)
                {
                    if (columnNames[c].Equals("SheetName"))
                    {
                        if (sheet.Range[columns[c] + r].Hyperlinks.Count > 0)
                        {
                            string link = sheet.Range[columns[c] + r].Hyperlinks[1].SubAddress;
                            if (!string.IsNullOrEmpty(link) && link.Contains("!"))
                            {
                                link = link.Substring(0, link.IndexOf("!"));
                                link = link.Replace("'", "");
                            }
                            row[c] = string.IsNullOrEmpty(link) ? sheet.Range[columns[c] + r].Value : link;
                        }
                    }
                    else
                    {
                        row[c] = sheet.Range[columns[c] + r].Value;
                    }
                }
                dttSource.Rows.Add(row);
            }
            return(dttSource);
        }
Exemple #2
0
        public void CreateDBScript(TableCreateInfo info)
        {
            string outPutPath = System.IO.Path.ChangeExtension(info.LayoutFileName, "sql");

            Logging.OutputFileName = outPutPath;
            try
            {
                using (ExcelHelp xls = new ExcelHelp(info.LayoutFileName))
                {
                    //int sheectCount = xls.WorkBook.Sheets.Count;
                    System.Data.DataTable dttList = GetTableList(xls, info.TemplateInfo);
                    foreach (System.Data.DataRow row in dttList.Rows)
                    {
                        DataTableInfo tbInfo = new DataTableInfo(row);
                        try
                        {
                            TableLayout tableInfo = ReadTableLayout(xls, tbInfo, info.TemplateInfo);
                            //Script出力
                            CreateSqlScript(info.Options, tableInfo);
                        }
                        catch (Exception ex)
                        {
                            Logging.WriteLine("/*");
                            Logging.WriteLine(Resources.StringTable.ScriptCreateFailed, tbInfo.SheetName, tbInfo.TableName);
                            Logging.Exception("", ex);
                            Logging.WriteLine("*/");
                        }
                    }
                    xls.Close();
                }
            }
            finally
            {
                Logging.OutputFileName = "";
            }
        }
        /// <summary>
        ///
        /// </summary>
        /// <param name="info"></param>
        public void CreateDocument(DbDocumentInfo docInfo)
        {
            using (ExcelHelp xls = new ExcelHelp(GetTemplateFile(docInfo)))
            {
                TemplateInfo.DocumentTemplateRow info = docInfo.TemplateInfo;

                xls.BeginUpdate();
                //表題
                if (!string.IsNullOrEmpty(info.CoverSheet))
                {
                    Excel.Worksheet coverSheet = xls.WorkBook.Sheets[info.CoverSheet];
                    xls.WriteValue(coverSheet, info.SystemNameCell, docInfo.SystemName);
                    xls.WriteValue(coverSheet, info.SubSystemNameCell, docInfo.SubSystemName);
                    xls.WriteValue(coverSheet, info.UpdateDate, DateTime.Today.ToString("yyyy/MM/dd"));
                }
                //目次を取得
                Excel.Worksheet indexSheet = xls.WorkBook.Sheets[info.IndexSheet];
                int             rowNo      = info.IndexSheet_StartRow;
                foreach (string tableName in docInfo.TableNames)
                {
                    TableList table = LinqSqlHelp.GetTable(tableName);
                    if (table != null)
                    {
                        string          sheetName     = string.IsNullOrEmpty(table.TableDisplayName) ? table.TableName : table.TableDisplayName;
                        Excel.Worksheet templateSheet = xls.WorkBook.Sheets[info.TemplateSheet];
                        Excel.Worksheet tableSheet    = xls.CreateSheet(sheetName, templateSheet);
                        //テーブル情報を書く
                        xls.WriteValue(tableSheet, info.TableId, table.TableName);
                        xls.WriteValue(tableSheet, info.TableName, table.TableName);
                        xls.WriteValue(tableSheet, info.TableDisplayName, table.TableDisplayName);
                        xls.WriteValue(tableSheet, info.TableComment, "");
                        xls.WriteValue(tableSheet, info.TableSummary, table.Comment);
                        //目次に書く
                        if (rowNo > info.IndexSheet_StartRow)
                        {
                            xls.CopyRow(indexSheet, info.IndexSheet_StartRow, rowNo);
                        }
                        //xls.WriteValue(indexSheet, info.IndexSheet_TableId , rowNo, table.TableName);
                        xls.WriteValue(indexSheet, info.IndexSheet_TableName, rowNo, table.TableName);
                        xls.WriteValue(indexSheet, info.IndexSheet_DisplayName, rowNo, table.TableDisplayName);
                        xls.WriteValue(indexSheet, info.IndexSheet_Summary, rowNo, table.Comment);
                        //リンク追加
                        if (!string.IsNullOrEmpty(info.IndexSheet_Link))
                        {
                            Excel.Range anchor = indexSheet.Range[info.IndexSheet_Link + rowNo];
                            indexSheet.Hyperlinks.Add(Anchor: anchor, Address: "", SubAddress: "'" + tableSheet.Name + "'!A1", TextToDisplay: sheetName);
                        }
                        //テーブルレイアウトを書く
                        var columns  = LinqSqlHelp.GetTableLayout(tableName);
                        int colIndex = info.ColumnStartRow;
                        //カラム情報を書く
                        foreach (TableLayoutInfo column in columns)
                        {
                            if (colIndex > info.ColumnMaxRow)
                            {
                                xls.CopyRow(tableSheet, info.ColumnStartRow, colIndex);
                            }
                            xls.WriteValue(tableSheet, info.ColumnNo, colIndex, (colIndex - info.ColumnStartRow + 1).ToString());
                            xls.WriteValue(tableSheet, info.ColumnName, colIndex, column.ColumnName);
                            xls.WriteValue(tableSheet, info.ColumnDisplayName, colIndex, column.ColumnDisplayName);
                            xls.WriteValue(tableSheet, info.ColumnDataType, colIndex, column.DataType);
                            xls.WriteValue(tableSheet, info.ColumnDataLength, colIndex, column.DataLengthDisplay);
                            xls.WriteValue(tableSheet, info.ColumnNullable, colIndex, column.Nullable ? "○" : "");
                            xls.WriteValue(tableSheet, info.ColumnIsPrimaryKey, colIndex, column.IsPrimaryKey ? "○" : "");
                            xls.WriteValue(tableSheet, info.ColumnIndex, colIndex,
                                           column.IndexId.HasValue?Convert.ToString(column.IndexId.Value):"");
                            xls.WriteValue(tableSheet, info.ColumnComment, colIndex, column.Comment);
                            colIndex++;
                        }
                        tableSheet.Columns.AutoFit();
                        rowNo++;
                    }
                }
                indexSheet.Columns.AutoFit();
                xls.EndUpdate();
                xls.Save(docInfo.FileName);
            }
        }
Exemple #4
0
        private TableLayout ReadTableLayout(ExcelHelp xls, DataTableInfo tabInfo, TemplateInfo.DocumentTemplateRow LiveLayoutInfo)
        {
            Excel.Worksheet sheet     = xls.WorkBook.Sheets[tabInfo.SheetName];
            string          tableName = Utility.DBToString(sheet.Range[LiveLayoutInfo.TableName].Value);
            TableLayout     tableInfo = new TableLayout(tableName)
            {
                DisplayName = sheet.Range[LiveLayoutInfo.TableDisplayName].Value,
                Comment     = tabInfo.Comment
            };
            //列作成
            int    rowIndex = LiveLayoutInfo.ColumnStartRow;
            string No       = Utility.DBToString(sheet.Range[LiveLayoutInfo.ColumnNo + 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.ColumnName + rowIndex].Value).Trim();
                column.DisplayName = Utility.DBToString(sheet.Range[LiveLayoutInfo.ColumnDisplayName + rowIndex].Value).Trim();
                column.DataType    = Utility.DBToString(sheet.Range[LiveLayoutInfo.ColumnDataType + rowIndex].Value).Trim();
                //Length
                string lenVal = Utility.DBToString(sheet.Range[LiveLayoutInfo.ColumnDataLength + 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.ColumnNullable + rowIndex].Value);
                column.Nullable = (!string.IsNullOrWhiteSpace(nullable));
                //InPrimary
                string InPrimary = Utility.DBToString(sheet.Range[LiveLayoutInfo.ColumnIsPrimaryKey + rowIndex].Value);
                column.IsPrimaryKey = (!string.IsNullOrWhiteSpace(InPrimary));
                //Index Key
                string indexNo = Utility.DBToString(sheet.Range[LiveLayoutInfo.ColumnIndex + 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}",
                                      tabInfo.SheetName, tableInfo.TableName, No,
                                      string.IsNullOrWhiteSpace(column.ColumnName) ? "列名なし" : "型なし");
                }
                //Comment
                column.Comment = Utility.DBToString(sheet.Range[LiveLayoutInfo.ColumnComment + rowIndex].Value).Trim();
                rowIndex++;
                No = Utility.DBToString(sheet.Range[LiveLayoutInfo.ColumnNo + rowIndex].Value);
            }

            return(tableInfo);
        }