private string CreateSheetDataSql(DataTableInfo tInfo, DataTable dtt, bool isIdInsert)
{
NpgTableLayoutInfo tinfo = GetTableLayoutInfo(tInfo.TableName);
StringBuilder sbSql = new StringBuilder();
sbSql.AppendFormat("/****【{0}】（{1}） ****/", tInfo.DisplayName, tInfo.TableName);
sbSql.AppendLine();
bool hasIdColumn = tinfo.Columns.Any(col => col.IsIdentity);
if (isIdInsert && hasIdColumn)
{
//sbSql.AppendFormat("SET IDENTITY_INSERT [{0}] ON", tinfo.TableName);
}
foreach (DataRow row in dtt.Rows)
{
sbSql.AppendLine(tinfo.GetInsertScript(row, true));
}
//if (isIdInsert && hasIdColumn)
//{
// sbSql.AppendFormat("SET IDENTITY_INSERT [{0}] OFF", tinfo.TableName);
// sbSql.AppendLine("GO");
//}
return(sbSql.ToString());
}
private NpgTableLayoutInfo GetTableLayoutInfo(string tableName)
{
DenshowDataAccesser dba = new DenshowDataAccesser();
NpgTableLayoutInfo info = dba.GetTableLayout(tableName);
return(info);
}
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();
}
/// <summary>
/// テーブル列の書式を設定する
/// </summary>
/// <param name="info"></param>
/// <param name="sheet"></param>
private void SetColumnFormat(NpgTableLayoutInfo info, Excel.Worksheet sheet)
{
string rangeName;
foreach (NpgColumnInfo column in info.Columns)
{
switch (column.DataType)
{
case "datetime":
rangeName = string.Format("{0}[{1}]", info.TableName, column.ColumnName);
sheet.Range[rangeName].NumberFormat = "yyyy/MM/dd HH:mm:ss";
break;
case "nvarchar":
case "varchar":
case "char":
rangeName = string.Format("{0}[{1}]", info.TableName, column.ColumnName);
sheet.Range[rangeName].NumberFormat = "@";
break;
default:
break;
}
}
}
public NpgTableLayoutInfo GetTableLayout(string tableName)
{
using (PostgreDao dao = GetDao())
{
using (NpgsqlCommand cmd = dao.CreateCommand())
{
cmd.CommandText = Properties.Resources.GetNpgTableLayout;
cmd.Parameters.AddWithValue("@schema", Common.Config.DenshowDbSchema);
cmd.Parameters.AddWithValue("@tableName", tableName);
DataTable dtt = dao.ExecuteResultSet(cmd, tableName, false);
NpgTableLayoutInfo tableInfo = new NpgTableLayoutInfo(tableName, dtt);
return(tableInfo);
}
}
}
