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