public ChangeInfo GetAllBrokerChangeInfo(Worksheet worksheet, int lastRowContainInfo)
{
ChangeInfo ChangedBrokerInfo = new ChangeInfo();
ChangedBrokerInfo.AllChangedBrokerList = new List <BrokerChangeInfo>();
for (int rowNum = 2; rowNum <= lastRowContainInfo; rowNum++)
{
BrokerChangeInfo brokerChangeInfo = new BrokerChangeInfo();
brokerChangeInfo.Name = ExcelUtil.GetRange(rowNum, 1, worksheet).Value2.ToString();
brokerChangeInfo.Ric = ExcelUtil.GetRange(rowNum, 2, worksheet).Value2.ToString();
brokerChangeInfo.Type = ExcelUtil.GetRange(rowNum, 3, worksheet).Value2.ToString();
brokerChangeInfo.Date = ExcelUtil.GetRange(rowNum, 4, worksheet).Value2.ToString();
ChangedBrokerInfo.AllChangedBrokerList.Add(brokerChangeInfo);
}
ChangedBrokerInfo.DifDayChangeInfo = ChangedBrokerInfo.GetDifDayChangeInfo();
//ChangedBrokerInfo.AllChangedBrokerList.Sort();
return(ChangedBrokerInfo);
}
public void UpdateHKSEAFile(ChangeInfo ChangedBrokerInfo, List <BordLotSizeChangeInfo> bordLotSizeChangeList)
{
using (ExcelApp appSEA = new ExcelApp(false, false))
{
string SEAFilePath = BackupFiles(configObj.HKSEA_Z2_FILE_PATH);
var workbookSEA = ExcelUtil.CreateOrOpenExcelFile(appSEA, SEAFilePath);
var worksheetSEA = ExcelUtil.GetWorksheet("Sheet2", workbookSEA);
int rowPos = 1;
using (ExcelLineWriter writer = new ExcelLineWriter(worksheetSEA, 1, 1, ExcelLineWriter.Direction.Right))
{
int curRowPos = worksheetSEA.UsedRange.Row + worksheetSEA.UsedRange.Rows.Count - 1;
for (int i = ChangedBrokerInfo.DifDayChangeInfo.Count - 1; i > -1; i--)
{
SameDayChangeInfo sameDayChangedInfo = ChangedBrokerInfo.DifDayChangeInfo[i];
rowPos = curRowPos;
while (rowPos > 0)
{
if (ExcelUtil.GetRange(rowPos, 4, worksheetSEA).Value2 == null || string.IsNullOrEmpty(ExcelUtil.GetRange(rowPos, 4, worksheetSEA).Value2.ToString()))
{
rowPos--;
continue;
}
else if (DateTimeCompare(sameDayChangedInfo.Date, ExcelUtil.GetRange(rowPos, 4, worksheetSEA).Text.ToString()))
{
if (rowPos == curRowPos)
{
curRowPos = rowPos;
InsertChangeInfo(rowPos + 1, writer, sameDayChangedInfo, worksheetSEA, bordLotSizeChangeList);
break;
}
else if (ExcelUtil.GetRange(rowPos + 1, 4, worksheetSEA) == null || (string.IsNullOrEmpty(ExcelUtil.GetRange(rowPos + 1, 4, worksheetSEA).Text.ToString())))
{
rowPos--;
}
else if (!DateTimeCompare(sameDayChangedInfo.Date, ExcelUtil.GetRange(rowPos + 1, 4, worksheetSEA).Text.ToString()))
{
curRowPos = rowPos;
InsertChangeInfo(rowPos + 1, writer, sameDayChangedInfo, worksheetSEA, bordLotSizeChangeList);
break;
}
else
{
rowPos--;
continue;
}
}
else
{
if (rowPos == 1)
{
curRowPos = rowPos;
InsertChangeInfo(rowPos + 1, writer, sameDayChangedInfo, worksheetSEA, bordLotSizeChangeList);
break;
}
else if (ExcelUtil.GetRange(rowPos - 1, 4, worksheetSEA) == null || (ExcelUtil.GetRange(rowPos - 1, 4, worksheetSEA).Text == null))
{
rowPos--;
}
else if (DateTimeCompare(sameDayChangedInfo.Date, ExcelUtil.GetRange(rowPos - 1, 4, worksheetSEA).Text.ToString()))
{
curRowPos = rowPos;
InsertChangeInfo(rowPos + 1, writer, sameDayChangedInfo, worksheetSEA, bordLotSizeChangeList);
break;
}
else
{
rowPos--;
continue;
}
}
}
}
}
//workbookSEA.SaveCopyAs(Path.Combine(Path.GetDirectoryName(workbookSEA.FullName), Path.GetFileName(configObj.HKSEA_Z2_FILE_PATH)));
workbookSEA.SaveCopyAs(configObj.HKSEA_Z2_FILE_PATH);
workbookSEA.Close(false, workbookSEA.FullName, false);
//File.Delete(SEAFilePath);
}
}
public void UpdateFiles()
{
int lastUsedRowNum = -1;
List <BordLotSizeChangeInfo> bordLotSizeChangeList = new List <BordLotSizeChangeInfo>();
//Range AKRange = GetAKRange(out lastUsedRowNum);
//Range AKRange = null;
using (ExcelApp app = new ExcelApp(false, false))
{
string addDropFilePath = BackupFiles(configObj.ADD_DROP_FILE_PATH);
var workbook = ExcelUtil.CreateOrOpenExcelFile(app, addDropFilePath);
Worksheet worksheetRaw = ExcelUtil.GetWorksheet(configObj.ADD_DROP_RAW_WORKSHEET_NAME, workbook);
using (ExcelApp appSource = new ExcelApp(false, false))
{
var workbookSource = ExcelUtil.CreateOrOpenExcelFile(appSource, configObj.SOURCE_FILE_PATH);
Range AKRange = GetAKRange(workbookSource, out lastUsedRowNum, out bordLotSizeChangeList);
Range r = ExcelUtil.GetRange(1, 1, lastUsedRowNum, 11, worksheetRaw);
AKRange.Copy(Missing.Value);
r.PasteSpecial(XlPasteType.xlPasteFormats, XlPasteSpecialOperation.xlPasteSpecialOperationNone, Missing.Value, Missing.Value);
ExcelUtil.GetRange(1, 1, worksheetRaw).Copy(Missing.Value);
workbookSource.Close(false, workbookSource.FullName, false);
}
//Run Macros
worksheetRaw.Activate();
app.ExcelAppInstance.GetType().InvokeMember("Run",
BindingFlags.Default | BindingFlags.InvokeMethod,
null,
app.ExcelAppInstance,
new object[] { "Macro1" });
var worksheet5 = ExcelUtil.GetWorksheet("Sheet5", workbook);
if (worksheet5 == null)
{
logger.LogErrorAndRaiseException(string.Format("Cannot get worksheet5 from workbook {0}", workbook.FullName));
}
UpdateSheet5(worksheet5, bordLotSizeChangeList);
var worksheetAddDrop = ExcelUtil.GetWorksheet("ADD DROP", workbook);
if (worksheetAddDrop == null)
{
logger.LogErrorAndRaiseException(string.Format("Cannot get worksheet ADD DROP from workbook {0}", workbook.FullName));
}
UpdateAddDropSheet(worksheetAddDrop, bordLotSizeChangeList);
int lastRowContainInfo = 2;
while (ExcelUtil.GetRange(lastRowContainInfo, 1, worksheetAddDrop).Value2 != null)
{
string value = (ExcelUtil.GetRange(lastRowContainInfo, 1, worksheetAddDrop).Value2.ToString().Trim().ToLower());
if (value.Contains("add") || (value.Contains("delete") || (value.Contains("change"))))
{
lastRowContainInfo++;
}
else
{
break;
}
}
ChangeInfo ChangedBrokerInfo = GetAllBrokerChangeInfo(worksheet5, lastRowContainInfo - 1);
UpdateHKSEAFile(ChangedBrokerInfo, bordLotSizeChangeList);
Range A2Range = ExcelUtil.GetRange(2, 1, lastRowContainInfo - 1, 16, worksheetAddDrop);
UpdateTemplateV2File(A2Range, lastRowContainInfo);
workbook.SaveCopyAs(Path.Combine(Path.GetDirectoryName(workbook.FullName), Path.GetFileName(configObj.ADD_DROP_FILE_PATH)));
workbook.Close(false, workbook.FullName, false);
File.Delete(addDropFilePath);
}
}
