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