public IQuerySerie <T> ToQuerySerie <T>() where T : struct
{
return(new QuerySerie <T>(Rows.AsTyped <T>().ToList(), StartTime.FromFileTimeUtcToDateTimeUtc(),
EndTime.FromFileTimeUtcToDateTimeUtc())
{
PreviousRow = PreviousRow.ToSingleDataRow <T>(),
NextRow = NextRow.ToSingleDataRow <T>()
});
}
public INullableQuerySerie <T> ToNullableQuerySerie <T>() where T : struct
{
return(new NullableQuerySerie <T>(Rows.AsNullableTyped <T>().ToList(),
StartTime.FromFileTimeUtcToDateTimeUtc(), EndTime.FromFileTimeUtcToDateTimeUtc())
{
PreviousRow = PreviousRow?.ToSingleDataRow <T>(),
NextRow = NextRow?.ToSingleDataRow <T>(),
Name = Name
});
}
protected override void Execute(CodeActivityContext context)
{
//Load Input Fields unto local variables
string headerName = HeaderName.Get(context);
string filePath = FilePath.Get(context);
string[] steps = StepsName.Get(context);
List <string> stepcols = new List <string>();
int stepcounter;
// Initialize Tools create for this project (Class Tools or Tools.cs)
Tools toolKit = new Tools();
//Validate if HeaderName Exists
if (String.IsNullOrEmpty(headerName))
{
//We require at least one of the two Column values to process, if not return false and throw an exception
ReportSet.Set(context, false);
throw new ArgumentNullException("Missing a Header Name");
}
else
{
//If one exists continue
//Validate that file path is not empty
if (!String.IsNullOrEmpty(filePath))
{
try
{
//Initialize Excel Interop objects;
object m = Type.Missing;
Application xlApp = new Application();
Workbooks xlWorkbooks = xlApp.Workbooks;
_Workbook xWB;
//check if file Exists if not Create the file first
if (!File.Exists(filePath))
{
xWB = xlWorkbooks.Add(XlWBATemplate.xlWBATWorksheet);
xWB.SaveAs(filePath, XlFileFormat.xlOpenXMLWorkbook, m, m, false, false, XlSaveAsAccessMode.xlShared, false, false, m, m, m);
xWB.Close();
}
Workbook xlWorkbook = xlWorkbooks.Open(filePath, m, false, m, m, m, m, m, m, m, m, m, m, m, m);
_Worksheet xlWorksheet = xlWorkbook.Sheets[1];
Range xlRange = xlWorksheet.UsedRange;
//Create Header and Status Column
((Range)xlWorksheet.Cells[1, 1]).Value2 = headerName;
((Range)xlWorksheet.Cells[1, 2]).Value2 = "Status";
//Set Status Column
StatusCol.Set(context, "B");
#region Calculate Steps Array values
int StepsCount;
try
{
StepsCount = steps.GetUpperBound(0);
}catch
{
StepsCount = 0;
}
#endregion
Tools mytools = new Tools();
//Process Steps Headers
stepcounter = 3;
if (StepsCount > 0)
{
foreach (string step in steps)
{
//Set Header using Column Index
((Range)xlWorksheet.Cells[1, stepcounter]).Value2 = step;
stepcols.Add(mytools.GetColLetter(stepcounter));
stepcounter++;
}
StepsCols.Set(context, stepcols);
}
//FIND NEXTROW (Next blank row by Column A)
for (stepcounter = 2; stepcounter < 1000000; stepcounter++)
{
if (String.IsNullOrEmpty(((Range)xlWorksheet.Cells[stepcounter, 1]).Value2))
{
NextRow.Set(context, stepcounter);
break;
}
}
if (NextRow.Get(context) == 0)
{
NextRow.Set(context, 2);
}
//Save and Close Workbook
xlWorkbook.Save();
xlWorkbook.Close(true, m, m);
//CLOSE AND 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
ReportSet.Set(context, true);
}
catch (Exception ex)
{
ReportSet.Set(context, false);
throw ex;
}
}
else
{
ReportSet.Set(context, false);
throw new ArgumentNullException("Missing FilePath to create file.");
}
}
}
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();
}
}
