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