private string GetValue(IExcelDataReader reader, FieldNames fieldName, SheetConfig config)
{
var index = GetFieldIndex(fieldName, config);
var negate = IsFieldValueNegate(fieldName, config);
return(Utils.GetValue(reader, index, negate).TrimWhiteSpace());
}
public XlsPackage()
{
hssfworkbook = new HSSFWorkbook();
this.sheets = new Dictionary <string, ISheet>();
this.sheetConfig = new SheetConfig(this.hssfworkbook);
}
private string GetAmountIncludingVATValue(IExcelDataReader reader, SheetConfig config, List <ExchangeRate> exchangeRates, string vat, decimal vatRate)
{
var amountIncludingVAT = GetCurrency(reader, FieldNames.AmountIncludingVAT, config, exchangeRates);
var amountExcludingVAT = "";
if (!string.IsNullOrWhiteSpace(config.AmountIdentifier.Column))
{
var index = ExcelUtils.ColumnToIndex(config.AmountIdentifier.Column);
var amountIdentifier = Utils.GetValue(reader, index);
var amount = GetCurrency(reader, FieldNames.Amount, config, exchangeRates);
var match = Regex.Match(amountIdentifier, config.AmountIdentifier.Value);
if (config.AmountIdentifier.Type == AmountIdentifier.AmountIdentifierTypeIncludingVat)
{
if (match.Success)
{
amountIncludingVAT = amount;
}
else
{
amountExcludingVAT = amount;
}
}
else
{
if (match.Success)
{
amountExcludingVAT = amount;
}
else
{
amountIncludingVAT = amount;
}
}
}
if (string.IsNullOrWhiteSpace(amountIncludingVAT))
{
var amountExcludingVatString = !string.IsNullOrWhiteSpace(amountExcludingVAT) ? amountExcludingVAT : GetCurrency(reader, FieldNames.AmountExcludingVAT, config, exchangeRates);
var amountExcludingVat = 0m;
var success = Decimal.TryParse(amountExcludingVatString, out amountExcludingVat);
if (!success)
{
return("");
}
if (string.IsNullOrWhiteSpace(vat))
{
vat = Decimal.Round(amountExcludingVat * (vatRate / 100m), 2).ToString();
}
amountIncludingVAT = Decimal.Round(amountExcludingVat + Decimal.Parse(vat), 2).ToString();
}
return(amountIncludingVAT);
}
public void Read_NegateValues()
{
var sheetConfig = new SheetConfig()
{
//No header
HeaderIdentifier = new Identifier()
{
Column = "",
Value = ""
},
Fields = new List <Field>()
{
new Field()
{
Name = Enum.GetName(typeof(FieldNames), FieldNames.PolicyNumber), Column = "A"
},
new Field()
{
Name = Enum.GetName(typeof(FieldNames), FieldNames.AmountIncludingVAT), Column = "B", NegateValue = true
},
},
CommissionTypes = new CommissionTypes()
{
MappingTemplate = "",
DefaultCommissionTypeCode = "unknown"
}
};
var sheet = new Sheet();
sheet.Position = 1;
sheet.Config = sheetConfig;
var config = new Config();
config.Sheets = new List <Sheet>()
{
sheet
};
var bytes = System.Convert.FromBase64String(NegateValues_Base64.STRING);
var stream = new MemoryStream(bytes);
var reader = new CommissionImportReader(config, _vatRate);
var commissions = reader.Read(stream).ToList();
Assert.Equal(2, commissions.Count);
var actual = commissions[0];
Assert.Equal("123456", actual.PolicyNumber);
Assert.Equal("115", actual.AmountIncludingVAT);
Assert.Equal("15", actual.VAT);
actual = commissions[1];
Assert.Equal("654321", actual.PolicyNumber);
Assert.Equal("-200", actual.AmountIncludingVAT);
Assert.Equal(-26.09m, Decimal.Parse(actual.VAT));
}
private string GetCommissionTypeCode(string commissionTypeValue, SheetConfig config)
{
var commissionType = config.CommissionTypes.Types.FirstOrDefault(t => t.Value.IgnoreCaseEquals(commissionTypeValue));
if (commissionType != null)
{
return(commissionType.CommissionTypeCode);
}
return(config.CommissionTypes.DefaultCommissionTypeCode);
}
private bool IsFieldValueNegate(FieldNames fieldName, SheetConfig config)
{
var name = Enum.GetName(typeof(FieldNames), fieldName);
var field = config.Fields.FirstOrDefault(f => f.Name == name);
if (field == null)
{
return(false);
}
return(field.NegateValue);
}
private int?GetFieldIndex(FieldNames fieldName, SheetConfig config)
{
var name = Enum.GetName(typeof(FieldNames), fieldName);
var field = config.Fields.FirstOrDefault(f => f.Name == name);
if (field == null)
{
return(null);
}
return(ExcelUtils.ColumnToIndex(field.Column));
}
private string GetCurrency(IExcelDataReader reader, FieldNames fieldName, SheetConfig config, List <ExchangeRate> exchangeRates)
{
var index = GetFieldIndex(fieldName, config);
var value = GetValue(reader, fieldName, config);
value = value.Replace("R", "");
foreach (var exchangeRate in exchangeRates)
{
value = value.Replace(exchangeRate.Currency, "");
}
return(value);
}
private SheetGroups ApplyCascade(SheetGroups sheetGroup, SheetConfig config)
{
List <GroupValue> lastGroupValues = null;
foreach (var group in config.Groups)
{
var rows = sheetGroup.RowGroups.ToList();
if (group.ReverseOrder)
{
rows.Reverse();
}
foreach (var rowGroups in rows)
{
foreach (var groupValue in rowGroups.GroupValues.Where(g => g.GroupFieldName == group.FieldName))
{
if (lastGroupValues == null)
{
continue;
}
if (!groupValue.IsInherited)
{
continue;
}
var shouldClear = lastGroupValues.Any(g =>
//If last section has a row that
g.IsInherited == false && //is a section row
g.GroupFieldName != groupValue.GroupFieldName && //and is different from current section
g.HierarchyOrder < groupValue.HierarchyOrder //and the hierarchy is less (further up) than current section
);
//Inherit from previous value
if (shouldClear)
{
groupValue.Value = "";
}
else
{
groupValue.Value = lastGroupValues.Find(g => g.GroupFieldName == groupValue.GroupFieldName).Value;
}
}
lastGroupValues = rowGroups.GroupValues;
}
}
return(sheetGroup);
}
public void Read_Formatter()
{
var sheetConfig = new SheetConfig()
{
HeaderIdentifier = new Identifier()
{
Column = "A",
Value = "broker"
},
Fields = new List <Field>()
{
new Field()
{
Name = "PolicyNumber", Column = "B"
},
new Field()
{
Name = "AmountIncludingVAT", Column = "C"
}
},
CommissionTypes = new CommissionTypes()
{
MappingTemplate = "E[^WRAP];F"
}
};
var sheet = new Sheet();
sheet.Position = 1;
sheet.Config = sheetConfig;
var bytes = System.Convert.FromBase64String(Formatter_Base64.STRING);
var stream = new MemoryStream(bytes);
var reader = new UniqueCommissionTypesReader(sheet);
var commissionTypeValues = reader.Read(stream).ToList();
Assert.Equal(3, commissionTypeValues.Count);
Assert.Equal(";val3", commissionTypeValues[0]);
Assert.Equal("wrap;val3", commissionTypeValues[1]);
Assert.Equal("wrap;val6", commissionTypeValues[2]);
}
private decimal GetVATRate(IExcelDataReader reader, SheetConfig config, decimal defaultVatRate)
{
if (!config.VatRates.Any())
{
return(defaultVatRate);
}
foreach (var rate in config.VatRates)
{
var index = ExcelUtils.ColumnToIndex(rate.Column);
var value = Utils.GetValue(reader, index).TrimWhiteSpace();
if (value.IgnoreCaseEquals(rate.Value))
{
return(rate.Rate);
}
}
return(defaultVatRate);
}
private Config GetValidConfig()
{
var sheetConfig = new SheetConfig()
{
HeaderIdentifier = new Identifier()
{
Column = "A",
Value = "Broker"
},
Fields = new List <Field>()
{
new Field()
{
Name = "PolicyNumber", Column = "A"
},
new Field()
{
Name = "AmountIncludingVAT", Column = "B"
}
},
CommissionTypes = new CommissionTypes()
{
MappingTemplate = "A;B",
DefaultCommissionTypeCode = "com_code_1"
}
};
var sheet = new Sheet();
sheet.Position = 1;
sheet.Config = sheetConfig;
var config = new Config();
config.Sheets = new List <Sheet>()
{
sheet
};
return(config);
}
public void Read_GroupingsReverseOrder()
{
var sheetConfig = new SheetConfig()
{
HeaderIdentifier = new Identifier()
{
Column = "A",
Value = "Policy Number"
},
Fields = new List <Field>()
{
new Field()
{
Name = Enum.GetName(typeof(FieldNames), FieldNames.PolicyNumber), Column = "B"
},
new Field()
{
Name = Enum.GetName(typeof(FieldNames), FieldNames.AmountIncludingVAT), Column = "C"
},
},
CommissionTypes = new CommissionTypes()
{
MappingTemplate = "GRP_CT;D",
DefaultCommissionTypeCode = "unknown",
Types = new List <CommissionType>()
{
new CommissionType()
{
CommissionTypeCode = "type_1", Value = "comType3;code_1"
},
new CommissionType()
{
CommissionTypeCode = "type_2", Value = "comType3;code_3"
},
new CommissionType()
{
CommissionTypeCode = "type_3", Value = "comType1;code_2"
},
new CommissionType()
{
CommissionTypeCode = "type_4", Value = "comType1;code_3"
},
new CommissionType()
{
CommissionTypeCode = "type_5", Value = "comType2;code_2"
},
new CommissionType()
{
CommissionTypeCode = "type_6", Value = "comType2;code_1"
},
}
},
Groups = new List <Group>()
{
new Group()
{
FieldName = "BrokerFullName",
Column = "A",
Formatter = ".+?(?= : Broker)", //Take everything before ': Broker'
ReverseOrder = false,
Identifiers = new List <Identifier>()
{
new Identifier()
{
Column = "A", Value = ": Broker"
}, //Contains ': Broker'
new Identifier()
{
Column = "B", Value = "^(?![\\s\\S])"
}, //Empy text
}
},
new Group()
{
FieldName = "CommissionType",
Column = "A",
ReverseOrder = true,
Identifiers = new List <Identifier>()
{
new Identifier()
{
Column = "A", Value = "\\b"
}, //Any word
new Identifier()
{
Column = "B", Value = "\\d"
}, //Any number
new Identifier()
{
Column = "C", Value = "^(?![\\s\\S])"
}, //Empy text
new Identifier()
{
Column = "D", Value = "^(?![\\s\\S])"
}, //Empy text
}
},
},
};
var sheet = new Sheet();
sheet.Position = 1;
sheet.Config = sheetConfig;
var config = new Config();
config.Sheets = new List <Sheet>()
{
sheet
};
var bytes = System.Convert.FromBase64String(GroupingsReverseOrder_Base64.STRING);
var stream = new MemoryStream(bytes);
var reader = new CommissionImportReader(config, _vatRate);
var commissions = reader.Read(stream).ToList();
Assert.Equal(7, commissions.Count);
var actual = commissions[0];
Assert.Equal("11111", actual.PolicyNumber);
Assert.Equal("100", actual.AmountIncludingVAT);
Assert.Equal("Dean van Niekerk", actual.BrokerFullName);
Assert.Equal("type_6", actual.CommissionTypeCode);
actual = commissions[1];
Assert.Equal("22222", actual.PolicyNumber);
Assert.Equal("200", actual.AmountIncludingVAT);
Assert.Equal("Dean van Niekerk", actual.BrokerFullName);
Assert.Equal("type_5", actual.CommissionTypeCode);
actual = commissions[2];
Assert.Equal("33333", actual.PolicyNumber);
Assert.Equal("300", actual.AmountIncludingVAT);
Assert.Equal("Dean van Niekerk", actual.BrokerFullName);
Assert.Equal("type_4", actual.CommissionTypeCode);
actual = commissions[3];
Assert.Equal("44444", actual.PolicyNumber);
Assert.Equal("400", actual.AmountIncludingVAT);
Assert.Equal("Dean van Niekerk", actual.BrokerFullName);
Assert.Equal("type_3", actual.CommissionTypeCode);
actual = commissions[4];
Assert.Equal("55555", actual.PolicyNumber);
Assert.Equal("500", actual.AmountIncludingVAT);
Assert.Equal("Marc Bormann", actual.BrokerFullName);
Assert.Equal("type_3", actual.CommissionTypeCode);
actual = commissions[5];
Assert.Equal("66666", actual.PolicyNumber);
Assert.Equal("600", actual.AmountIncludingVAT);
Assert.Equal("Marc Bormann", actual.BrokerFullName);
Assert.Equal("type_2", actual.CommissionTypeCode);
actual = commissions[6];
Assert.Equal("77777", actual.PolicyNumber);
Assert.Equal("700", actual.AmountIncludingVAT);
Assert.Equal("Marc Bormann", actual.BrokerFullName);
Assert.Equal("type_1", actual.CommissionTypeCode);
}
public void Read_CommissionTypeSubstring()
{
var sheetConfig = new SheetConfig()
{
//No header
HeaderIdentifier = new Identifier()
{
Column = "",
Value = ""
},
Fields = new List <Field>()
{
new Field()
{
Name = Enum.GetName(typeof(FieldNames), FieldNames.PolicyNumber), Column = "A"
},
new Field()
{
Name = Enum.GetName(typeof(FieldNames), FieldNames.AmountIncludingVAT), Column = "B"
},
new Field()
{
Name = Enum.GetName(typeof(FieldNames), FieldNames.VAT), Column = "C"
},
new Field()
{
Name = Enum.GetName(typeof(FieldNames), FieldNames.BrokerFullName), Column = "K"
}
},
CommissionTypes = new CommissionTypes()
{
MappingTemplate = "D(1-6)",
DefaultCommissionTypeCode = "unknown",
Types = new List <CommissionType>()
{
new CommissionType()
{
CommissionTypeCode = "code_1", Value = "code_1"
},
new CommissionType()
{
CommissionTypeCode = "code_2", Value = "code_2"
},
new CommissionType()
{
CommissionTypeCode = "code_3", Value = "code_3"
}
}
}
};
var sheet = new Sheet();
sheet.Position = 1;
sheet.Config = sheetConfig;
var config = new Config();
config.Sheets = new List <Sheet>()
{
sheet
};
var bytes = System.Convert.FromBase64String(CommissionTypeSubstring_Base64.STRING);
var stream = new MemoryStream(bytes);
var reader = new CommissionImportReader(config, _vatRate);
var commissions = reader.Read(stream).ToList();
Assert.Equal(3, commissions.Count);
var actual = commissions[0];
Assert.Equal("123456", actual.PolicyNumber);
Assert.Equal("100", actual.AmountIncludingVAT);
Assert.Equal("14", actual.VAT);
Assert.Equal("code_1", actual.CommissionTypeCode);
Assert.Equal("code_1", actual.CommissionTypeValue);
Assert.Equal("brokerFullName_1", actual.BrokerFullName);
actual = commissions[1];
Assert.Equal("654321", actual.PolicyNumber);
Assert.Equal("200", actual.AmountIncludingVAT);
Assert.Equal("15", actual.VAT);
Assert.Equal("code_2", actual.CommissionTypeCode);
Assert.Equal("code_2", actual.CommissionTypeValue);
Assert.Equal("brokerFullName_2", actual.BrokerFullName);
actual = commissions[2];
Assert.Equal("987654", actual.PolicyNumber);
Assert.Equal("300", actual.AmountIncludingVAT);
Assert.Equal("16", actual.VAT);
Assert.Equal("code_3", actual.CommissionTypeCode);
Assert.Equal("code_3", actual.CommissionTypeValue);
Assert.Equal("brokerFullName_3", actual.BrokerFullName);
}
public void Read_DefaultTemplate()
{
var sheetConfig = new SheetConfig()
{
//No header
HeaderIdentifier = new Identifier()
{
Column = "",
Value = ""
},
Fields = new List <Field>()
{
new Field()
{
Name = Enum.GetName(typeof(FieldNames), FieldNames.PolicyNumber), Column = "A"
},
new Field()
{
Name = Enum.GetName(typeof(FieldNames), FieldNames.AmountIncludingVAT), Column = "B"
},
new Field()
{
Name = Enum.GetName(typeof(FieldNames), FieldNames.VAT), Column = "C"
},
new Field()
{
Name = Enum.GetName(typeof(FieldNames), FieldNames.LastName), Column = "E"
},
new Field()
{
Name = Enum.GetName(typeof(FieldNames), FieldNames.DateOfBirth), Column = "F"
},
new Field()
{
Name = Enum.GetName(typeof(FieldNames), FieldNames.FirstName), Column = "G"
},
new Field()
{
Name = Enum.GetName(typeof(FieldNames), FieldNames.IdNumber), Column = "H"
},
new Field()
{
Name = Enum.GetName(typeof(FieldNames), FieldNames.Initials), Column = "I"
},
new Field()
{
Name = Enum.GetName(typeof(FieldNames), FieldNames.FullName), Column = "J"
},
new Field()
{
Name = Enum.GetName(typeof(FieldNames), FieldNames.BrokerFullName), Column = "K"
}
},
CommissionTypes = new CommissionTypes()
{
MappingTemplate = "D",
DefaultCommissionTypeCode = "unknown",
Types = new List <CommissionType>()
{
new CommissionType()
{
CommissionTypeCode = "code_1", Value = "code_1"
},
new CommissionType()
{
CommissionTypeCode = "code_2", Value = "code_2"
},
new CommissionType()
{
CommissionTypeCode = "code_3", Value = "code_3"
}
}
}
};
var sheet = new Sheet();
sheet.Position = 1;
sheet.Config = sheetConfig;
var config = new Config();
config.Sheets = new List <Sheet>()
{
sheet
};
var bytes = System.Convert.FromBase64String(DefaultTemplate_Base64.STRING);
var stream = new MemoryStream(bytes);
var reader = new CommissionImportReader(config, _vatRate);
var commissions = reader.Read(stream).ToList();
//Check we are testing all fields
var importCommissionProps = typeof(ImportCommission).GetProperties();
Assert.Equal(importCommissionProps.Count() - 4, sheetConfig.Fields.Count()); //minus 4 for Id, Currency, CommissionTypeValue and CommissionTypeCode
Assert.Equal(3, commissions.Count);
var actual = commissions[0];
Assert.Equal("123456", actual.PolicyNumber);
Assert.Equal("100", actual.AmountIncludingVAT);
Assert.Equal("14", actual.VAT);
Assert.Equal("code_1", actual.CommissionTypeCode);
Assert.Equal("code_1", actual.CommissionTypeValue);
Assert.Equal("ln_1", actual.LastName);
Assert.Equal("1982-10-03", actual.DateOfBirth);
Assert.Equal("fn_1", actual.FirstName);
Assert.Equal("8210035032082", actual.IdNumber);
Assert.Equal("ini_1", actual.Initials);
Assert.Equal("fullName_1", actual.FullName);
Assert.Equal("brokerFullName_1", actual.BrokerFullName);
actual = commissions[1];
Assert.Equal("654321", actual.PolicyNumber);
Assert.Equal("200", actual.AmountIncludingVAT);
Assert.Equal("15", actual.VAT);
Assert.Equal("code_2", actual.CommissionTypeCode);
Assert.Equal("code_2", actual.CommissionTypeValue);
Assert.Equal("ln_2", actual.LastName);
Assert.Equal("", actual.DateOfBirth);
Assert.Equal("fn_2", actual.FirstName);
Assert.Equal("8210035032082", actual.IdNumber);
Assert.Equal("ini_2", actual.Initials);
Assert.Equal("fullName_2", actual.FullName);
Assert.Equal("brokerFullName_2", actual.BrokerFullName);
actual = commissions[2];
Assert.Equal("987654", actual.PolicyNumber);
Assert.Equal("300", actual.AmountIncludingVAT);
Assert.Equal("16", actual.VAT);
Assert.Equal("unknown", actual.CommissionTypeCode);
Assert.Equal("", actual.CommissionTypeValue);
Assert.Equal("ln_3", actual.LastName);
Assert.Equal("1982-10-05", actual.DateOfBirth);
Assert.Equal("fn_3", actual.FirstName);
Assert.Equal("", actual.IdNumber);
Assert.Equal("ini_3", actual.Initials);
Assert.Equal("fullName_3", actual.FullName);
Assert.Equal("brokerFullName_3", actual.BrokerFullName);
}
private static async System.Threading.Tasks.Task AddExtraColumnsAsync(SheetsService service, SheetConfig config, Dictionary <string, string> columnToSheetIndex, string spreadsheetId)
{
Dictionary <string, int> columnCount = new Dictionary <string, int>();
foreach (AddColumn column in config.Columns)
{
if (!columnCount.ContainsKey(column.Sheet))
{
columnCount.Add(column.Sheet, ColumnsPerSheet(column.Sheet, columnToSheetIndex));
}
List <string> values = new List <string>();
values.Add(column.Title);
string formula = column.Value;
bool isVLookup = formula.StartsWith("=VLOOKUP");
MatchCollection matches = Regex.Matches(column.Value, @"({[a-zA-Z\._\~]*})", RegexOptions.Compiled | RegexOptions.IgnoreCase);
List <string> refIds = new List <string>();
bool missing = false;
for (int i = 0; i < matches.Count; i++)
{
Match match = matches[i];
string col = match.Value;
col = col.Replace("{", string.Empty);
col = col.Replace("}", string.Empty);
col = col.Replace(".", "|");
if (!columnToSheetIndex.ContainsKey(col.Replace("~", string.Empty)))
{
missing = true;
break;
}
if (i == 0 || !isVLookup)
{
if (col.Contains("~"))
{
col = col.Replace("~", string.Empty);
formula = formula.Replace(match.Value, columnToSheetIndex[col]);
continue;
}
string refId = columnToSheetIndex[col].Split(':')[0];
formula = formula.Replace(match.Value, refId);
refIds.Add(refId);
}
else if (i > 1)
{
formula = formula.Replace(match.Value, columnToSheetIndex[col].Split(':')[1]);
}
else
{
formula = formula.Replace(match.Value, columnToSheetIndex[col].Split(':')[0]);
}
}
if (missing)
{
continue;
}
int rows = RowsPerSheet(column.Sheet, columnToSheetIndex);
foreach (string refId in refIds)
{
string rowId = Regex.Match(refId, @"\d+").Value;
int startRow = Convert.ToInt32(rowId);
string basicId = refId.Replace(rowId, string.Empty);
for (int j = 0; j < rows; j++)
{
string newId = basicId + (startRow + j);
if ((j + 1) < values.Count)
{
values[(j + 1)] = values[(j + 1)].Replace(refId, newId);
continue;
}
values.Add(formula.Replace(refId, newId));
}
}
for (int i = 0; i < values.Count; i++)
{
if (i == 0)
{
//skip header
continue;
}
if (values[i].StartsWith("="))
{
values[i] = values[i].Substring(1);
}
values[i] = "=IFERROR(" + values[i] + ",\"\")";
}
await InsertColumnLineAsync(service, spreadsheetId, GetRange(columnToSheetIndex, columnCount, column, rows, 1), values.ToArray()).ConfigureAwait(false);
if (!string.IsNullOrEmpty(column.Comment))
{
await InsertColumnNoteAsync(service, spreadsheetId, await GetSheetIdFromSheetNameAsync(service, spreadsheetId, column.Sheet).ConfigureAwait(false), columnCount[column.Sheet], column.Comment);
}
columnToSheetIndex[column.Sheet + "|" + column.Title] = GetRange(columnToSheetIndex, columnCount, column, rows, 2);
columnCount[column.Sheet] = columnCount[column.Sheet] + 1;
}
}
private string GetDate(IExcelDataReader reader, FieldNames fieldName, SheetConfig config)
{
var index = GetFieldIndex(fieldName, config);
return(Utils.GetDate(reader, index).TrimWhiteSpace());
}
public Configuration()
{
Email = new EmailServerConfig();
Sheet = new SheetConfig();
}
public async Task <Config> GetDefaultConfig()
{
var sheetConfig = new SheetConfig()
{
//No header
HeaderIdentifier = new Identifier()
{
Column = "",
Value = ""
},
Fields = new List <Field>()
{
new Field()
{
Name = Enum.GetName(typeof(FieldNames), FieldNames.PolicyNumber), Column = "A"
},
new Field()
{
Name = Enum.GetName(typeof(FieldNames), FieldNames.AmountIncludingVAT), Column = "B"
},
new Field()
{
Name = Enum.GetName(typeof(FieldNames), FieldNames.VAT), Column = "C"
},
new Field()
{
Name = Enum.GetName(typeof(FieldNames), FieldNames.LastName), Column = "E"
},
new Field()
{
Name = Enum.GetName(typeof(FieldNames), FieldNames.DateOfBirth), Column = "F"
},
new Field()
{
Name = Enum.GetName(typeof(FieldNames), FieldNames.FirstName), Column = "G"
},
new Field()
{
Name = Enum.GetName(typeof(FieldNames), FieldNames.IdNumber), Column = "H"
},
new Field()
{
Name = Enum.GetName(typeof(FieldNames), FieldNames.Initials), Column = "I"
},
new Field()
{
Name = Enum.GetName(typeof(FieldNames), FieldNames.FullName), Column = "J"
},
new Field()
{
Name = Enum.GetName(typeof(FieldNames), FieldNames.BrokerFullName), Column = "K"
}
},
CommissionTypes = new CommissionTypes()
{
MappingTemplate = "D",
DefaultCommissionTypeCode = "unknown",
Types = new List <CommissionType>()
}
};
var commissionTypes = await _lookupService.GetCommissionTypes();
sheetConfig.CommissionTypes.Types = commissionTypes
.Select(c => new CommissionType()
{
CommissionTypeCode = c.Code,
Value = c.Code
}
)
.ToList();
var sheet = new Sheet();
sheet.Position = 1;
sheet.Config = sheetConfig;
var config = new Config();
config.Sheets = new List <Sheet>()
{
sheet
};
return(config);
}
private void DrawSheetConfig(int index, SheetConfig sheetConfig)
{
GUI.backgroundColor = sheetConfig.isExpand ? index % 2 == 0 ? Color.cyan : Color.green : Color.white;
var style = new GUIStyle(GUI.skin.toggle);
style.fontStyle = FontStyle.Bold;
style.fontSize = 15;
sheetConfig.isExpand = GUILayout.Toggle(sheetConfig.isExpand, $"[{index}] {sheetConfig.findName}", style);
GUI.backgroundColor = Color.white;
if (sheetConfig.isExpand)
{
GUILayout.Label("File name", EditorStyles.helpBox);
sheetConfig.findName = EditorGUILayout.TextField("File name", sheetConfig.findName);
GUILayout.Label("Sheet Config Setting", EditorStyles.helpBox);
sheetConfig.spreadSheetKey = EditorGUILayout.TextField("Spread sheet key", sheetConfig.spreadSheetKey);
GUILayout.Label("Sheet Names:", EditorStyles.helpBox);
int removeId = -1;
for (int i = 0; i < sheetConfig.sheetNames.Count; i++)
{
GUILayout.BeginHorizontal();
GUILayout.Label($"Sheet {i}:", GUILayout.Width(60));
sheetConfig.sheetNames[i].name = EditorGUILayout.TextField(sheetConfig.sheetNames[i].name);
GUILayout.Label("Start Cell:", GUILayout.Width(65));
sheetConfig.sheetNames[i].startCell = EditorGUILayout.TextField(sheetConfig.sheetNames[i].startCell, GUILayout.Width(70));
GUILayout.Label("End Cell:", GUILayout.Width(65));
sheetConfig.sheetNames[i].endCell = EditorGUILayout.TextField(sheetConfig.sheetNames[i].endCell, GUILayout.Width(70));
GUILayout.Label("Text:", GUILayout.Width(30));
sheetConfig.sheetNames[i].buildText = GUILayout.Toggle(sheetConfig.sheetNames[i].buildText, "", GUILayout.Width(20));
GUILayout.Label("Upper:", GUILayout.Width(40));
sheetConfig.sheetNames[i].isUpper = GUILayout.Toggle(sheetConfig.sheetNames[i].isUpper, "", GUILayout.Width(20));
GUI.backgroundColor = Color.green;
if (GUILayout.Button("Build", EditorStyles.miniButton, GUILayout.Width(50)))
{
ExportSheet(sheetConfig.spreadSheetKey, sheetConfig.sheetNames[i].name, sheetConfig.sheetNames[i].startCell, sheetConfig.sheetNames[i].endCell,
sheetConfig.sheetNames[i].buildText, sheetConfig.sheetNames[i].isUpper);
}
GUI.backgroundColor = Color.red;
if (GUILayout.Button("X", EditorStyles.miniButton, GUILayout.Width(20)))
{
if (EditorUtility.DisplayDialog("Note!", $"You will remove sheet {sheetConfig.sheetNames[i].name}. Are you sure?", "Oke", "No"))
{
removeId = i;
}
}
GUI.backgroundColor = Color.white;
GUILayout.EndHorizontal();
}
if (removeId >= 0)
{
sheetConfig.sheetNames.RemoveAt(removeId);
}
GUILayout.Label(sheetConfig.sheetNames.Count <= 0 ? "Download all sheets" : $"Download {sheetConfig.sheetNames.Count} sheets");
GUILayout.BeginHorizontal();
if (GUILayout.Button("Add sheet name", GUILayout.Width(150)))
{
sheetConfig.sheetNames.Add(new SheetName {
name = "", buildText = false
});
}
GUI.backgroundColor = Color.red;
if (GUILayout.Button("X", GUILayout.Width(20)))
{
if (EditorUtility.DisplayDialog("Note!", $"You will remove google sheet. Are you sure?", "Oke", "No"))
{
_sheetSetting.GoogleSheets.Remove(sheetConfig);
}
}
GUI.backgroundColor = Color.white;
GUILayout.EndHorizontal();
}
GUILayout.Label("");
}
private static async System.Threading.Tasks.Task CreateSheetAsync(SheetsService service, string spreadsheetId, string name, SheetConfig config)
{
try
{
var addSheetRequest = new AddSheetRequest();
addSheetRequest.Properties = new SheetProperties();
addSheetRequest.Properties.Title = name;
addSheetRequest.Properties.Hidden = config.IsSheetHidden(name);
BatchUpdateSpreadsheetRequest batchUpdateSpreadsheetRequest = new BatchUpdateSpreadsheetRequest();
batchUpdateSpreadsheetRequest.Requests = new List <Request>();
batchUpdateSpreadsheetRequest.Requests.Add(new Request
{
AddSheet = addSheetRequest
});
var batchUpdateRequest = service.Spreadsheets.BatchUpdate(batchUpdateSpreadsheetRequest, spreadsheetId);
await batchUpdateRequest.ExecuteAsync().ConfigureAwait(false);
await Throttler.ThrottleCheck().ConfigureAwait(false);
var repeatCellRequest = new RepeatCellRequest();
repeatCellRequest.Range = new GridRange()
{
SheetId = await GetSheetIdFromSheetNameAsync(service, spreadsheetId, name).ConfigureAwait(false),
StartRowIndex = 0,
StartColumnIndex = 0,
EndColumnIndex = 1000
};
repeatCellRequest.Fields = "userEnteredFormat.numberFormat";
repeatCellRequest.Cell = new CellData()
{
UserEnteredFormat = new CellFormat()
{
NumberFormat = new NumberFormat()
{
Type = "TEXT"
}
}
};
batchUpdateSpreadsheetRequest = new BatchUpdateSpreadsheetRequest();
batchUpdateSpreadsheetRequest.Requests = new List <Request>();
batchUpdateSpreadsheetRequest.Requests.Add(new Request
{
RepeatCell = repeatCellRequest
});
batchUpdateRequest = service.Spreadsheets.BatchUpdate(batchUpdateSpreadsheetRequest, spreadsheetId);
await batchUpdateRequest.ExecuteAsync().ConfigureAwait(false);
await Throttler.ThrottleCheck().ConfigureAwait(false);
}
catch
{
try
{
ClearValuesRequest clearValuesRequest = new ClearValuesRequest();
SpreadsheetsResource.ValuesResource.ClearRequest request = service.Spreadsheets.Values.Clear(clearValuesRequest, spreadsheetId, name + "!$A$1:$YY");
ClearValuesResponse response = await request.ExecuteAsync().ConfigureAwait(false);
await Throttler.ThrottleCheck().ConfigureAwait(false);
//remove validations
BatchUpdateSpreadsheetRequest batchUpdateSpreadsheetRequest = new BatchUpdateSpreadsheetRequest();
batchUpdateSpreadsheetRequest.Requests = new List <Request>();
batchUpdateSpreadsheetRequest.Requests.Add(new Request()
{
SetDataValidation = new SetDataValidationRequest()
{
Range = new GridRange()
{
SheetId = await GetSheetIdFromSheetNameAsync(service, spreadsheetId, name).ConfigureAwait(false),
StartRowIndex = 0,
StartColumnIndex = 0,
EndColumnIndex = 1000
}
}
});
var batchUpdateRequest = service.Spreadsheets.BatchUpdate(batchUpdateSpreadsheetRequest, spreadsheetId);
await batchUpdateRequest.ExecuteAsync().ConfigureAwait(false);
await Throttler.ThrottleCheck().ConfigureAwait(false);
}
catch (Exception e)
{
//sheet doesn`t exist
throw new ServiceDirectoryException("Unable to create spreadsheet", e);
}
}
}
public void Read_ExchangeRates()
{
var sheetConfig = new SheetConfig()
{
//No header
HeaderIdentifier = new Identifier()
{
Column = "",
Value = ""
},
Fields = new List <Field>()
{
new Field()
{
Name = Enum.GetName(typeof(FieldNames), FieldNames.PolicyNumber), Column = "A"
},
new Field()
{
Name = Enum.GetName(typeof(FieldNames), FieldNames.AmountIncludingVAT), Column = "B"
},
new Field()
{
Name = Enum.GetName(typeof(FieldNames), FieldNames.Currency), Column = "C"
},
},
CommissionTypes = new CommissionTypes()
{
MappingTemplate = "",
DefaultCommissionTypeCode = "unknown"
},
ExchangeRates = new ExchangeRates()
{
HeaderIdentifier = new Identifier()
{
Column = "A", Value = "Fund Currency"
},
CurrencyColumn = "A",
ExchangeRateColumn = "B",
},
};
var sheet = new Sheet();
sheet.Position = 1;
sheet.Config = sheetConfig;
var config = new Config();
config.Sheets = new List <Sheet>()
{
sheet
};
var bytes = System.Convert.FromBase64String(ExchangeRates_Base64.STRING);
var stream = new MemoryStream(bytes);
var reader = new CommissionImportReader(config, _vatRate);
var commissions = reader.Read(stream).ToList();
Assert.Equal(5, commissions.Count); //Additional 2 are bad records
var actual = commissions[0];
Assert.Equal("123456", actual.PolicyNumber);
Assert.Equal("1200", actual.AmountIncludingVAT);
Assert.Equal(156.48m, Decimal.Parse(actual.VAT));
actual = commissions[1];
Assert.Equal("654321", actual.PolicyNumber);
Assert.Equal("3000", actual.AmountIncludingVAT);
Assert.Equal(391.35m, Decimal.Parse(actual.VAT));
actual = commissions[2];
Assert.Equal("987654", actual.PolicyNumber);
Assert.Equal("3600", actual.AmountIncludingVAT);
Assert.Equal(469.56m, Decimal.Parse(actual.VAT));
}
private static async System.Threading.Tasks.Task HideColumnAsync(SheetsService service, string spreadsheetId, string name, int index, SheetConfig config)
{
try
{
var updateDimensionRequest = new UpdateDimensionPropertiesRequest();
updateDimensionRequest.Properties = new DimensionProperties();
updateDimensionRequest.Properties.HiddenByUser = true;
updateDimensionRequest.Fields = "hiddenByUser";
updateDimensionRequest.Range = new DimensionRange()
{
Dimension = "COLUMNS",
SheetId = await GetSheetIdFromSheetNameAsync(service, spreadsheetId, name).ConfigureAwait(false),
StartIndex = index,
EndIndex = index + 1
};
BatchUpdateSpreadsheetRequest batchUpdateSpreadsheetRequest = new BatchUpdateSpreadsheetRequest();
batchUpdateSpreadsheetRequest.Requests = new List <Request>();
batchUpdateSpreadsheetRequest.Requests.Add(new Request
{
UpdateDimensionProperties = updateDimensionRequest
});
var batchUpdateRequest = service.Spreadsheets.BatchUpdate(batchUpdateSpreadsheetRequest, spreadsheetId);
await batchUpdateRequest.ExecuteAsync().ConfigureAwait(false);
}
finally
{
await Throttler.ThrottleCheck().ConfigureAwait(false);
}
}
private SheetGroups GetSheetGroups(IExcelDataReader reader, SheetConfig config, int sheetNumber)
{
var sheetGroups = new SheetGroups()
{
SheetNumber = sheetNumber,
RowGroups = new List <RowGroups>()
};
var rowNumber = 0;
var header = new HeaderLocator(config.HeaderIdentifier);
while (reader.Read())
{
rowNumber++;
if (!header.Found)
{
header.Check(reader);
continue;
}
var rowGroups = new RowGroups(rowNumber);
//Check groupings
int hierarchyOrder = 0;
foreach (var group in config.Groups)
{
hierarchyOrder++;
var groupValue = new GroupValue(group.FieldName, hierarchyOrder);
//Check for match
var isMatch = true;
foreach (var identifier in group.Identifiers)
{
var value = CommissionImportReader.GetValue(reader, identifier.Column) ?? "";
if (Regex.Matches(value, identifier.Value).Count == 0)
{
isMatch = false;
}
}
if (isMatch)
{
var value = CommissionImportReader.GetValue(reader, group.Column) ?? "";
if (!string.IsNullOrEmpty(group.Formatter))
{
var match = Regex.Match(value, group.Formatter);
if (match.Success)
{
value = match.Value;
}
}
groupValue.Value = value;
groupValue.IsInherited = false;
}
rowGroups.GroupValues.Add(groupValue);
}
sheetGroups.RowGroups.Add(rowGroups);
}
;
return(sheetGroups);
}
public static string GetCommissionTypeValue(IExcelDataReader reader, SheetConfig config, List <GroupValue> groupValues)
{
var parts = MappingTemplate.Parse(config.CommissionTypes.MappingTemplate);
if (!parts.Any())
{
return(config.CommissionTypes.DefaultCommissionTypeCode);
}
var values = new List <string>();
foreach (var part in parts)
{
string value = "";
if (part == CommissionTypes.GROUP_COMMISSION_TYPE)
{
value = GetGroupValue(groupValues, GroupFieldNames.CommissionType);
}
else
{
var column = MappingTemplate.GetColumn(part);
var index = ExcelUtils.ColumnToIndex(column);
value = Utils.GetValue(reader, index);
//Update value is substring is defined
if (MappingTemplate.IsSubstring(part))
{
try
{
var subStringIndex = MappingTemplate.GetSubStringIndexes(part);
var startIndex = subStringIndex[0] - 1;
var length = subStringIndex[1] - subStringIndex[0] + 1;
value = value.Substring(startIndex, length);
}
catch { }
}
//Update value is regex is defined
if (MappingTemplate.IsRegex(part))
{
try
{
var regex = MappingTemplate.GetRegex(part);
var match = Regex.Match(value, regex, RegexOptions.IgnoreCase);
if (match.Success)
{
value = match.Value;
}
else
{
value = "";
}
}
catch { }
}
}
values.Add(value);
}
return(MappingTemplate.Format(values));
}
public void DrawSheetConfig(int index, SheetConfig sheetConfig)
{
GUI.backgroundColor = sheetConfig.isExpand ? index % 2 == 0 ? Color.cyan : Color.green : Color.white;
sheetConfig.isExpand = EditorGUILayout.BeginFoldoutHeaderGroup(sheetConfig.isExpand, $"Google Sheet {index}: {sheetConfig.spreadSheetKey}");
GUI.backgroundColor = Color.white;
if (sheetConfig.isExpand)
{
GUILayout.Label("Sheet Config Setting", EditorStyles.helpBox);
sheetConfig.spreadSheetKey = EditorGUILayout.TextField("Spread sheet key", sheetConfig.spreadSheetKey);
GUILayout.Label("Sheet Names", EditorStyles.helpBox);
int removeId = -1;
for (int i = 0; i < sheetConfig.sheetNames.Count; i++)
{
GUILayout.BeginHorizontal();
GUILayout.Label($"Sheet {i}:", GUILayout.Width(60));
sheetConfig.sheetNames[i].name = EditorGUILayout.TextField(sheetConfig.sheetNames[i].name);
GUILayout.Label("Start Cell:", GUILayout.Width(65));
sheetConfig.sheetNames[i].startCell = EditorGUILayout.TextField(sheetConfig.sheetNames[i].startCell, GUILayout.Width(70));
GUILayout.Label("End Cell:", GUILayout.Width(65));
sheetConfig.sheetNames[i].endCell = EditorGUILayout.TextField(sheetConfig.sheetNames[i].endCell, GUILayout.Width(70));
GUILayout.Label("Text:", GUILayout.Width(30));
sheetConfig.sheetNames[i].buildText = GUILayout.Toggle(sheetConfig.sheetNames[i].buildText, "", GUILayout.Width(20));
GUI.backgroundColor = Color.red;
if (GUILayout.Button("X", EditorStyles.miniButton, GUILayout.Width(40)))
{
if (EditorUtility.DisplayDialog("Note!", $"You will remove sheet {sheetConfig.sheetNames[i].name}. Are you sure?", "Oke", "No"))
{
removeId = i;
}
}
GUI.backgroundColor = Color.white;
GUILayout.EndHorizontal();
}
if (removeId >= 0)
{
sheetConfig.sheetNames.RemoveAt(removeId);
}
GUILayout.Label(sheetConfig.sheetNames.Count <= 0 ? "Download all sheets" : $"Download {sheetConfig.sheetNames.Count} sheets");
GUILayout.BeginHorizontal();
if (GUILayout.Button("Add sheet name", GUILayout.Width(150)))
{
sheetConfig.sheetNames.Add(new SheetName {
name = "", buildText = false
});
}
GUI.backgroundColor = Color.red;
if (GUILayout.Button("X", GUILayout.Width(40)))
{
if (EditorUtility.DisplayDialog("Note!", $"You will remove google sheet. Are you sure?", "Oke", "No"))
{
_sheetSetting.GoogleSheets.Remove(sheetConfig);
}
}
GUI.backgroundColor = Color.white;
GUILayout.EndHorizontal();
}
GUILayout.Label("");
EditorGUILayout.EndFoldoutHeaderGroup();
}
public async static System.Threading.Tasks.Task <bool> WriteToSpreadsheetAsync(string spreadsheetId, IConfigurableHttpClientInitializer credential, string apiBaseUrl, string configPath)
{
SheetsService service = CreateService(credential);
try
{
SheetConfig config = JsonConvert.DeserializeObject <SheetConfig>(File.ReadAllText(configPath));
//move text to config
await AddUpdateMessage(spreadsheetId, "Please wait while your export is generated, once completed this sheet will be deleted.", service).ConfigureAwait(false);
DelayeredResult result = await Delayering.DelayerPaginatedData(apiBaseUrl).ConfigureAwait(false);
if (result.Collection.Count == 0)
{
await AddUpdateMessage(spreadsheetId, "ERROR: service directory data not found at the given URL", service);
return(false);
}
Dictionary <string, string> columnToSheetIndex = new Dictionary <string, string>();
List <ForeignKeyAssociaion> foreignKeys = new List <ForeignKeyAssociaion>();
ResourceReader resourceReader = new ResourceReader();
dynamic resources = await resourceReader.GetResources().ConfigureAwait(false);
foreach (dynamic resource in resources)
{
if (!Resources.ShowItem(resource))
{
continue;
}
string sheetName = resource.name;
if (!result.Collection.ContainsKey(sheetName))
{
continue;
}
Console.WriteLine("Create Sheet: " + sheetName);
await CreateSheetAsync(service, spreadsheetId, sheetName, config).ConfigureAwait(false);
int sheetId = await GetSheetIdFromSheetNameAsync(service, spreadsheetId, sheetName).ConfigureAwait(false);
List <ForeignKeyAssociaion> localForeignKeys = new List <ForeignKeyAssociaion>();
if (resource.schema.foreignKeys != null)
{
foreach (dynamic fk in resource.schema.foreignKeys)
{
localForeignKeys.Add(new ForeignKeyAssociaion(sheetId, sheetName, fk.fields.Value, fk.reference.resource.Value, fk.reference.fields.Value));
}
}
int index = 0;
int columnNo = -1;
foreach (dynamic field in resource.schema.fields)
{
if (!Resources.ShowItem(field))
{
continue;
}
string columnName = field.name.Value;
if (columnName == "extent")
{
continue;
}
if (config.IsColumnHidden(sheetName, columnName))
{
await HideColumnAsync(service, spreadsheetId, sheetName, index, config).ConfigureAwait(false);
}
Console.WriteLine("Create Column Name: " + columnName);
columnNo++;
await InsertColumnLineAsync(service, spreadsheetId, sheetName + "!" + GetColumnName(index) + "1", columnName).ConfigureAwait(false);;
string comment = config.GetComment(sheetName, columnName);
if (!string.IsNullOrEmpty(comment))
{
await InsertColumnNoteAsync(service, spreadsheetId, await GetSheetIdFromSheetNameAsync(service, spreadsheetId, sheetName).ConfigureAwait(false), columnNo, comment);
}
List <object> columnValues = new List <object>();
foreach (dynamic obj in result.Collection[sheetName].Values)
{
if (!((IDictionary <String, dynamic>)obj).ContainsKey(columnName))
{
columnValues.Add(string.Empty);
}
else
{
columnValues.Add(((IDictionary <String, dynamic>)obj)[columnName]);
}
}
Console.WriteLine("Load Column Values: " + columnName);
List <List <object> > columnBatches = SplitList(columnValues, 1000);
int rowNumber = 2;
foreach (List <object> columnBatch in columnBatches)
{
string colRange = sheetName + "!" + GetColumnName(index) + rowNumber;
foreach (ForeignKeyAssociaion localForeignKey in localForeignKeys)
{
if (localForeignKey.Field == columnName)
{
localForeignKey.StartColumnIndex = index;
localForeignKey.Found = true;
}
}
SaveColumnRange(columnToSheetIndex, sheetName, columnName, colRange + ":" + GetColumnName(index) + columnValues.Count);
await InsertColumnLineAsync(service, spreadsheetId, colRange, columnBatch.ToArray()).ConfigureAwait(false);
rowNumber += columnBatch.Count;
}
index++;
}
foreignKeys.AddRange(localForeignKeys);
}
await AddForiegnKeysAsync(service, foreignKeys, columnToSheetIndex, spreadsheetId).ConfigureAwait(false);
await AddExtraColumnsAsync(service, config, columnToSheetIndex, spreadsheetId).ConfigureAwait(false);
await DeleteOriginalSheet(service, spreadsheetId).ConfigureAwait(false);
return(true);
}
catch (Exception e)
{
await AddUpdateMessage(spreadsheetId, "ERROR: service directory data not found at the given URL", service);
throw e;
}
}
