Esempio n. 1
0
        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());
        }