private Task <bool> UpdateDatabase(string secureKey) { var tcs = new TaskCompletionSource <bool>(); Task.Factory.StartNew(async() => { try { dataBaseService.DeleteTable(typeof(DataItemModel)); dataBaseService.CreateTable(typeof(DataItemModel)); //todo 待验证 //await DataBaseHelper.Instance.ConnectDataBase("mypassword"); if (DataList != null) { foreach (var item in DataList) { dataBaseService.SecureInsert(item, secureKey); } } var result = await SaveSecureKeyAsync(); tcs.SetResult(result); } catch (Exception e) { tcs.SetResult(false); } }); return(tcs.Task); }
private async Task TransferWorksheetToDatabase() { var dictionary = new Dictionary <string, Tuple <int, int, Type> >(); Microsoft.Office.Tools.Excel.Worksheet worksheet = Globals.Factory.GetVstoObject(Globals.ThisAddIn.Application.ActiveWorkbook.ActiveSheet); var excelApplication = System.Runtime.InteropServices.Marshal.GetActiveObject("Excel.Application") as Excel.Application; worksheet.CalculateMethod(); Range selectedRange = (excelApplication.Selection as Range); var areas = selectedRange.Areas; for (int i = 1; i <= areas.Count; i++) { if (areas.Item[i].Rows.Count > 1 && !areas.Item[i].MergeCells) { MessageBox.Show("Only one cell per column allowed!", null, MessageBoxButtons.OK, MessageBoxIcon.Error); return; } } var dataTable = new System.Data.DataTable((Globals.ThisAddIn.Application.ActiveSheet as Excel.Worksheet).Name); var cellCount = selectedRange.Cells.Count > selectedRange.EntireColumn.Areas.Count ? selectedRange.Cells.Count : selectedRange.EntireColumn.Areas.Count; var areaCount = areas.Count; bool useArea; useArea = areaCount > 1 ? true : false; int loops = useArea ? areaCount : cellCount; for (int i = 1; i <= loops; i++) { string columnName = String.Empty; int columnNameRow; int columnNameColumn; if (useArea) { if (areas.Item[i].MergeCells) { columnName = (areas.Item[i].Cells[1, 1]).Value; } else { columnName = areas.Item[i].Value; } columnNameRow = areas.Item[i].Row; columnNameColumn = areas.Item[i].Column; } else { columnNameColumn = (selectedRange.Cells[i] as Range).Column; columnNameRow = (selectedRange.Cells[i] as Range).Row; columnName = (selectedRange.Cells[i] as Range).Value; } var dataType = ResolveType(worksheet.Cells[rowCorrection, columnNameColumn].Value, columnName); if (dataType == null) { return; } try { dictionary.Add(columnName, new Tuple <int, int, Type>(columnNameRow, columnNameColumn, dataType)); } catch (ArgumentException) { string newColumnName = columnName + "2"; int counter = 2; while (dictionary.ContainsKey(newColumnName)) { newColumnName = columnName + counter; counter++; } dictionary.Add(newColumnName, new Tuple <int, int, Type>(columnNameRow, columnNameColumn, dataType)); columnName = newColumnName; } dataTable.Columns.Add(new DataColumn() { DataType = dataType, ColumnName = columnName, Caption = columnName, AutoIncrement = false, ReadOnly = false, Unique = false }); } _dataBaseSettings.TableName = (Globals.ThisAddIn.Application.ActiveSheet as Excel.Worksheet).Name; try { excelApplication.Cursor = XlMousePointer.xlWait; await _databaseService.CreateTable(dictionary); for (int k = rowCorrection; k <= worksheet.Cells.SpecialCells(XlCellType.xlCellTypeLastCell, Type.Missing).Row; k++) { var row = dataTable.NewRow(); for (int i = 0; i < dictionary.Count; i++) { var cellValue = Parse(worksheet.Cells[k, dictionary.ElementAt(i).Value.Item2].Value, dictionary.ElementAt(i).Value.Item3); row[dictionary.ElementAt(i).Key] = cellValue; } dataTable.Rows.Add(row); await _databaseService.Insert(dataTable); dataTable.Clear(); rowCounterLlabel.Label = k.ToString(); } excelApplication.Cursor = XlMousePointer.xlDefault; } catch (Exception exception) { excelApplication.Cursor = XlMousePointer.xlDefault; MessageBox.Show(exception.Message, null, MessageBoxButtons.OK, MessageBoxIcon.Error); return; } }