/// <summary>
/// Append new line and fill in the trade change hours
/// </summary>
/// <param name="xlSheet"></param>
/// <param name="item"></param>
/// <param name="tradeHours"></param>
/// <param name="effectiveRow"></param>
/// <param name="remainingHours"></param>
public int AppendNewLine(Worksheet xlSheet, KseJson item, long?tradeHours, int effectiveRow, long?remainingHours = null)
{
var line = (Range)xlSheet.Rows[effectiveRow + 1];
line.Insert();
if (remainingHours == null)
{
// Employee Id
xlSheet.Range["A" + effectiveRow].Offset[1, 0].Value = xlSheet.Range["A" + effectiveRow].Value;
// Job
xlSheet.Range["D" + effectiveRow].Offset[1, 0].Value = xlSheet.Range["D" + effectiveRow].Value;
// Phase
xlSheet.Range["H" + effectiveRow].Offset[1, 0].Value = xlSheet.Range["H" + effectiveRow].Value;
// Cost Code
xlSheet.Range["I" + effectiveRow].Offset[1, 0].Value = xlSheet.Range["I" + effectiveRow].Value;
// Pay type
xlSheet.Range["J" + effectiveRow].Offset[1, 0].Value = xlSheet.Range["J" + effectiveRow].Value;
// Pay group
xlSheet.Range["K" + effectiveRow].Offset[1, 0].Value = item.Store.TradeChange.Substring(0, 6);
// Hours
xlSheet.Range["M" + effectiveRow].Offset[1, 0].Value = tradeHours;
return(2);
}
else
{
// Employee Id
xlSheet.Range["A" + effectiveRow].Offset[1, 0].Value = xlSheet.Range["A" + effectiveRow].Value;
// Job
xlSheet.Range["D" + effectiveRow].Offset[1, 0].Value = xlSheet.Range["D" + effectiveRow].Value;
// Phase
xlSheet.Range["H" + effectiveRow].Offset[1, 0].Value = xlSheet.Range["H" + effectiveRow].Value;
// Cost Code
xlSheet.Range["I" + effectiveRow].Offset[1, 0].Value = xlSheet.Range["I" + effectiveRow].Value;
// Pay type
xlSheet.Range["J" + effectiveRow].Offset[1, 0].Value = xlSheet.Range["J" + effectiveRow].Value;
// Pay group
xlSheet.Range["K" + effectiveRow].Offset[1, 0].Value = item.Store.TradeChange.Substring(0, 6);
// Hours
xlSheet.Range["M" + effectiveRow].Offset[1, 0].Value = remainingHours;
return(2);
}
}
/// <summary>
/// Work with imported excel workbook, sort and parse json blobs into C# List
/// </summary>
/// <param name="workbook"></param>
/// <returns>C# List of KseJson Type</returns>
public List <KseJson> ParseJsonBlob(Workbook workbook)
{
var uniqueList = new List <KseJson>();
var allList = new List <KseJson>();
try
{
// Get imported workbook and worksheet and get all the ranges
var xlSheet = (Worksheet)workbook.Sheets[1];
var iLastRow = xlSheet.Cells.SpecialCells(XlCellType.xlCellTypeLastCell).Row;
var oJsonRange = xlSheet.Range["Z2", "Z" + iLastRow];
// Sort for optimization
xlSheet.UsedRange.Select();
xlSheet.Sort.SortFields.Clear();
xlSheet.Sort.SortFields.Add((Range)xlSheet.UsedRange.Columns["Z"], XlSortOn.xlSortOnValues,
XlSortOrder.xlAscending, Type.Missing, XlSortDataOption.xlSortNormal);
var sort = xlSheet.Sort;
sort.SetRange(xlSheet.UsedRange);
sort.Header = XlYesNoGuess.xlYes;
sort.MatchCase = false;
sort.Orientation = XlSortOrientation.xlSortColumns;
sort.Apply();
// Go through the Json blobs int he range and add unto to C# List as unique values by their Ids.
foreach (Range cell in oJsonRange)
{
if ((string)cell.Value == "[]" && cell.Value == null)
{
continue;
}
var jsonString = (string)cell.Value;
var kdeJson = KseJson.FromJson(jsonString);
var employeeId = xlSheet.Range["A" + cell.Row, "A" + cell.Row].Value2;
foreach (var json in kdeJson)
{
allList.Add(json);
var schema = json.Schema;
if (schema != "Trade Change")
{
continue;
}
json.EmployeeId = employeeId.ToString();
var startRow = cell.Row;
if (uniqueList.Any(item => item.Id == json.Id))
{
continue;
}
json.StartingRow = startRow;
uniqueList.Add(json);
}
}
// Add effective number of line items per shift extra entries
foreach (var json in allList)
{
var count = allList.Count(x => x.Id == json.Id);
foreach (var item in uniqueList)
{
if (item.Count < 1 && item.Id == json.Id)
{
item.Count = count;
}
}
}
return(uniqueList);
}
catch (Exception ex)
{
using (var file = new System.IO.StreamWriter(@".\exceptionlog.txt"))
{
file.WriteLine(ex.Message);
}
}
return(null);
}