private void Drop(object sender, DragEventArgs e)
{
string[] fileName = (string[])e.Data.GetData(DataFormats.FileDrop, false);
// とりあえずメッセージウィンドウに表示
switch (SelectedFunction)
{
case "BackUp":
// tableName Get
string colsKey = DateTime.Now.ToString("yyyyMMddhhmmss");
// selected TableName
string inputName = SelectAndInputForm.ShowDialog("用件入力"
, "バックアップに関する名称が有ったら選択、もしくは入力してください。"
, !(BackUpNames.Count <= 0)
? BackUpNames.ToArray()
: new string[] { DEFAULT_BACKUP_NAME }
);
// tableName Set
if (!BackUpNames.Contains(inputName) && inputName != DEFAULT_BACKUP_NAME)
{
BackUpNames.Add(inputName);
Task.Run(() => JsonManager.WriteJson(BackUpNamePath, BackUpNames));
}
if (inputName == DEFAULT_BACKUP_NAME)
{
inputName = "";
}
string backupDir = Path.Combine(Directory.GetParent(fileName[0]).FullName, $"bkup{inputName}_{DateTime.Now:yyyyMMddhhmmss}");
MakeBackUpFile(fileName, backupDir);
break;
case "ExcelNeutral":
ExcelNeutral(fileName);
break;
default:
break;
}
}
public void CreateInsertSQLString(object sender, EventArgs e)
{
#region 暫定設定処理
IsTrim = true;
NullString = "NULL";
#endregion
string inputData = Clipboard.GetText();
// INSERT文の場合実行しない(2度押し想定)
if (inputData.Contains("INSERT INTO") && inputData.Contains("VALUES"))
{
return;
}
// 表形式であるか確認(カンマ・タブ文字、改行コード)
if (String.IsNullOrEmpty(inputData.Trim()))
{
return;
}
string separateLine = inputData.Split(new string[] { "\r\n" }, StringSplitOptions.None).Length > 1 ? "\r\n" : "\r";
string separateCell = inputData.Split(new string[] { "\t" }, StringSplitOptions.None).Length > 1 ? "\t" : ",";
if (!inputData.Contains(separateCell) || !inputData.Contains(separateLine))
{
return;
}
List <List <String> > tableData = inputData.Split(new string[] { separateLine }, StringSplitOptions.None)
.Where(l => l.Contains(separateCell))
.Select(l => l.Split(new string[] { separateCell }, StringSplitOptions.None).ToList()).ToList();
// title & record無し
if (tableData[0].Any(c => String.IsNullOrEmpty(c)))
{
return;
}
if (tableData.Count() <= 1)
{
return;
}
// tableName Get
string colsKey = String.Join(",", tableData[0]).Replace(" ", "").ToUpper();
string tableList = DicTableNamDef.ContainsKey(colsKey) ? DicTableNamDef[colsKey] : "";
// selected TableName
string inputTableName = SelectAndInputForm.ShowDialog("テーブル名入力"
, "テーブル名を選択、もしくは入力してください。"
, !String.IsNullOrEmpty(tableList)
? tableList.Split(new char[] { ',' })
: new string[] { DEFAULT_TABLE_NAME }
);
// tableName Set
if (!tableList.Contains(inputTableName) && inputTableName != DEFAULT_TABLE_NAME)
{
tableList = String.IsNullOrEmpty(tableList) ? inputTableName : $"{tableList},{inputTableName}";
DicTableNamDef[colsKey] = tableList;
Task.Run(() => JsonManager.WriteJson(TableNameDefPath, DicTableNamDef));
}
StringBuilder sbFormat = new StringBuilder();
// header
sbFormat.Append("INSERT INTO ");
sbFormat.Append($"{inputTableName} ");
sbFormat.Append("(" + String.Join(",", tableData[0]) + ") ");
sbFormat.Append("VALUES {0}");
StringBuilder sbRecord = new StringBuilder();
// record
for (int y = 1; y < tableData.Count(); y++)
{
string recordData = string.Empty;
for (int x = 0; x < tableData[y].Count(); x++)
{
bool isNumber = false;
bool isNull = false;
string cellData = tableData[y][x];
if (cellData == NullString)
{
isNull = true;
}
else
{
if (IsTrim)
{
// 全て空白はトリムしない
if (!cellData.All(c => c == ' '))
{
cellData = cellData.TrimEnd(WhiteSpaceDelimiters);
}
}
if (cellData == tableData[y][x])
{
decimal tmpData = new decimal();
if (Decimal.TryParse(cellData, out tmpData))
{
if (cellData == tmpData.ToString())
{
isNumber = true;
}
}
}
}
if (isNull)
{
recordData += ",Null";
}
else if (isNumber)
{
recordData += "," + cellData;
}
else
{
recordData += $",'{cellData}'";
}
}
sbRecord.AppendLine(String.Format(sbFormat.ToString(), "(" + recordData.Substring(1) + ");"));
}
Clipboard.Clear();
Clipboard.SetText(sbRecord.ToString());
}
