//Update BN underlying column: (Removing the first number redundant "0", 00005.HK --> 0005.HK, 00038.HK --> 0038.HK, and changes "HSC" to "HSCE"
//There's a bug in the macro, this method is a work around
public void UpdateBNColumn(Worksheet worksheet)
{
int lastUsedRow = worksheet.UsedRange.Row + worksheet.UsedRange.Rows.Count - 1;
using (ExcelLineWriter reader = new ExcelLineWriter(worksheet, 2, 66, ExcelLineWriter.Direction.Down))
{
while (reader.Row <= lastUsedRow)
{
var cellValue = ExcelUtil.GetRange(reader.Row, reader.Col, worksheet).Text;
if (cellValue != null)
{
string text = cellValue.ToString().Trim().ToUpper();
if (text.Contains(".HSC"))
{
reader.WriteInPlace(text.Replace(".HSC", ".HSCE"));
}
//00005.HK
else if (text.Contains(".HK"))
{
string underlyingCode = text.Substring(0, text.IndexOf('.'));
if (underlyingCode.Length == 5 && underlyingCode.StartsWith("0"))
{
reader.WriteInPlace(text.Remove(0, 1));
}
}
}
reader.PlaceNext(reader.Row + 1, reader.Col);
}
}
}