void OnMouseDown()
{
if (transform.GetComponentInParent <LastRow>())
{
LastRow = transform.GetComponentInParent <LastRow>();
}
mZCoord = Camera.main.WorldToScreenPoint(gameObject.transform.position).z;
mOffset = gameObject.transform.position - GetMouseAsWorldPoint();
startingPos = transform.parent.transform;
transform.SetParent(draggingLayer);
}
/// <summary>
/// Добавляем кнопку в клавиатуру
/// </summary>
/// <param name="text">Имя кнопки</param>
/// <param name="callBack">Используется только для InlineKeyboardMarkup</param>
/// <returns></returns>
public MarkupWrapper <T> Add(string text, string callBack = "default")
{
if (typeof(T) == typeof(ReplyKeyboardMarkup))
{
KeyboardButton btn = new KeyboardButton();
btn.Text = text;
LastRow.Add(btn);
}
if (typeof(T) == typeof(InlineKeyboardMarkup))
{
InlineKeyboardButton btn = new InlineKeyboardButton();
btn.Text = text;
btn.CallbackData = callBack;
LastRow.Add(btn);
}
return(this);
}
protected override void Execute(CodeActivityContext context)
{
//Load Input Fields unto local variables
string filePath = FilePath.Get(context);
//Validate that file path is not empty
if (!String.IsNullOrEmpty(filePath))
{
//Validate that file exists
if (File.Exists(filePath))
{
try
{
//Initialize Excel Interop objects;
object m = Type.Missing;
Excel.Application xlApp = new Excel.Application();
Excel.Workbooks xlWorkbooks = xlApp.Workbooks;
Excel.Workbook xlWorkbook = xlWorkbooks.Open(filePath, m, false, m, m, m, m, m, m, m, m, m, m, m, m);
Excel._Worksheet xlWorksheet = xlWorkbook.Sheets[1];
//Excel.Range xlRange = xlWorksheet.UsedRange;
Excel.Range xlRange = xlWorksheet.get_Range("a1").EntireRow.EntireColumn;
xlApp.DisplayAlerts = false;
// Initialize Tools create for this project (Class Tools or Tools.cs)
Tools toolKit = new Tools();
// Gather Row and Col counts
Console.WriteLine("Start");
Int32 lastRow = (Int32)xlApp.WorksheetFunction.CountA(xlRange.get_Range("a1").EntireColumn);
Int32 lCol = xlRange.Columns.Count;
Int32 sCol = lCol;
Console.WriteLine(lastRow.ToString());
// Create local variables for processing
int i;
string vLstCol;
string statusValue;
var eValue = "";
//iterate by the existing columns to determine if a current Status column exists
for (i = 1; i <= lCol; i++)
{
vLstCol = xlWorksheet.Cells[1, i].Value;
if (vLstCol == "Status" || String.IsNullOrEmpty(vLstCol))
{
if (vLstCol == "Status") {
sCol = i;
lCol = i - 1;
}
else
{
lCol = i;
sCol = i + 1;
}
}
}
//set Status header (or reset if one already exists)
((Excel.Range)xlWorksheet.Cells[1, sCol]).Value2 = "Status";
//create and assign Last Column value
String lastCol = toolKit.GetColLetter(lCol);
LastCol.Set(context, lastCol);
//create and assign Status Column value
String statusCol = toolKit.GetColLetter(lCol + 1);
StatusCol.Set(context, statusCol);
//Assign Last Row Value
LastRow.Set(context, lastRow);
Console.WriteLine("Set status range");
Excel.Range xlStatus = xlRange.get_Range(statusCol + "1").EntireColumn;
Console.WriteLine(xlStatus.Rows.Count.ToString() + "/" + xlStatus.Columns.Count.ToString());
//Iterate through the rows to determine if status is complete or not (will determine next item to work on)
i = 1;
Console.WriteLine("Get Next Row");
for( i = 1; i < xlStatus.Rows.Count; i++) // while using eachrow on xlStatus seemed the best approach this allow for better value control
{
eValue = ((Excel.Range)xlWorksheet.Cells[i, sCol]).Value2;
//Console.WriteLine(eValue.GetType().ToString());
statusValue = "";
if (eValue != null) {
statusValue = eValue.ToString();
statusValue = statusValue.ToLower().Trim();
}
Console.WriteLine(i.ToString() + "/" + sCol.ToString() + " - " + statusValue);
if (statusValue != "complete" && statusValue != "status")
{
//Assign Next Row value
NextRow.Set(context, i);
break;
}
}
//Save and Close Workbook
xlWorkbook.Save();
xlWorkbook.Close(true, m, m);
//Close all Excel Interop objects and perform Garbage Collect
Marshal.ReleaseComObject(xlWorksheet);
xlWorksheet = null;
Marshal.ReleaseComObject(xlWorkbooks);
xlWorkbooks = null;
Marshal.ReleaseComObject(xlWorkbook);
xlWorkbook = null;
xlApp.Quit();
Marshal.ReleaseComObject(xlApp);
xlApp = null;
GC.Collect(); //Garbage Collect
GC.WaitForPendingFinalizers(); //Wait until Garbage Collect completes
}
catch
{
throw;
}
}
else
{
throw new FileNotFoundException();
}
}
else
{
throw new ArgumentNullException();
}
}
/// <summary>
/// Returns a <see cref="System.String"/> that represents this instance.
/// </summary>
/// <returns>
/// A <see cref="System.String"/> that represents this instance.
/// </returns>
public new string ToString()
{
_stringBuilder.Append(activateCmdShell);
_stringBuilder.Append("'bcp ");
switch (DataSource)
{
case Keys.TAB_SQL:
_stringBuilder.Append(string.Format(@" ""{0}"" queryout ", Regex.Replace(SQLStatment, "(\n|\r)+", string.Empty)));
break;
case Keys.TAB_SP:
StringBuilder storedProcParams = new StringBuilder();
int index = 0;
foreach (var param in (MappingParams)StoredProcedureParameters)
{
storedProcParams.Append(string.Format("{0} {1}",
(index > 0)
? ","
: string.Empty,
(param.Type.ToLower().Contains("char") ||
param.Type.ToLower().Contains("date") ||
param.Type.ToLower().Contains("text"))
? string.Format("'{0}'", EvaluateExpression(param.Value, _variableDispenser))
: EvaluateExpression(param.Value, _variableDispenser)));
index++;
}
_stringBuilder.Append(string.Format(@" ""exec {0}.{1} {2}"" queryout ", Database.Trim(), StoredProcedure.Trim(), storedProcParams));
break;
case Keys.TAB_VIEW:
_stringBuilder.Append(string.Format(@" ""{0}.{1}"" out ", Database.Trim(), View.Trim()));
break;
case Keys.TAB_TABLES:
_stringBuilder.Append(string.Format(@" ""{0}.{1}"" out ", Database.Trim(), Tables.Trim()));
break;
}
_stringBuilder.Append(DestinationByFileConnection.Trim() == Keys.TRUE
? string.Format(@" ""{0}"" ", _connection[DestinationPath].ConnectionString)
: string.Format(@" ""{0}"" ", EvaluateExpression(DestinationPath, _variableDispenser)));
string srvVal = (from srv in _connection[SQLServerInstance].ConnectionString.Split(';')
where srv.Contains("Data Source")
select srv).FirstOrDefault();
if (srvVal != null)
{
_stringBuilder.Append(string.Format(@" -S""{0}""", srvVal.Split('=')[1]));
}
_stringBuilder.Append(TrustedConnection.Trim() == Keys.TRUE
? " -T "
: string.Format(@" -U""{0}"" -P""{1}"" ",
EvaluateExpression(Login, _variableDispenser),
EvaluateExpression(Password, _variableDispenser)));
//if (!string.IsNullOrEmpty(Database))
//{
// _stringBuilder.Append(string.Format(" -d [{0}]", Database));
//}
if (!string.IsNullOrEmpty(FirstRow.Trim()))
{
_stringBuilder.Append(string.Format(" -F{0}", FirstRow));
}
if (!string.IsNullOrEmpty(LastRow.Trim()))
{
_stringBuilder.Append(string.Format(" -L{0}", LastRow));
}
if (!string.IsNullOrEmpty(MaxErrors.Trim()))
{
_stringBuilder.Append(string.Format(" -m{0}", MaxErrors));
}
if (NativeDatabaseDataType == Keys.TRUE)
{
_stringBuilder.Append(" -N ");
}
if (!string.IsNullOrEmpty(FormatFile.Trim()))
{
_stringBuilder.Append(FormatFileByFileConnection == Keys.TRUE
? string.Format(@" -f""{0}"" ", _connection[FormatFile].ConnectionString)
: string.Format(@" -f""{0}"" ", EvaluateExpression(FormatFile, _variableDispenser)));
}
//else
//{
// _stringBuilder.Append(" -c ");
//}
if (!string.IsNullOrEmpty(FieldTermiantor.Trim()))
{
_stringBuilder.Append(string.Format(" -t{0} ", FieldTermiantor));
}
if (!string.IsNullOrEmpty(RowTermiantor.Trim()))
{
_stringBuilder.Append(string.Format(" -r{0} ", RowTermiantor));
}
if (!string.IsNullOrEmpty(CodePage.Trim()))
{
_stringBuilder.Append(string.Format(" -C{0} ", CodePage));
}
if (!string.IsNullOrEmpty(NetworkPacketSize.Trim()))
{
_stringBuilder.Append(string.Format(" -a{0} ", NetworkPacketSize));
}
if (UseRegionalSettings == Keys.TRUE)
{
_stringBuilder.Append(" -R ");
}
if (SET_QUOTED_IDENTIFIERS_ON == Keys.TRUE)
{
_stringBuilder.Append(" -q ");
}
if (UseCharacterDataType == Keys.TRUE)
{
_stringBuilder.Append(" -c ");
}
if (UseUnicodeCharacters == Keys.TRUE)
{
_stringBuilder.Append(" -w ");
}
_stringBuilder.Append("'");
return(_stringBuilder.ToString());
}
