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