public static Workbook CreateOrOpenExcelFile(ExcelApp excelApp, string path)
{
Workbook curWorkbook = null;
path = Path.GetFullPath(path);
try
{
if (File.Exists(path))
{
curWorkbook = excelApp.ExcelAppInstance.Workbooks.Open(path, 0, Missing.Value, Missing.Value, Missing.Value, Missing.Value,
Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value);
return(curWorkbook);
}
curWorkbook = excelApp.ExcelAppInstance.Workbooks.Add(XlWBATemplate.xlWBATWorksheet);
if (!Directory.Exists(Path.GetDirectoryName(path)))
{
Directory.CreateDirectory(Path.GetDirectoryName(path));
}
curWorkbook.SaveAs(path, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value,
XlSaveAsAccessMode.xlExclusive, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value);
}
catch (Exception ex)
{
throw new Exception(string.Format("There's error when openning/ creating excel file {0}. Exception message: {1}", path, ex.Message));
}
return(curWorkbook);
}
protected override void Initialize()
{
base.Initialize();
funds = new List <MutualFund>();
cookies = new CookieContainer();
configObj = Config as MutualFundCreationConfig;
try
{
service = EWSUtility.CreateService(new System.Net.NetworkCredential(configObj.MailUsername, configObj.MailPassword, configObj.MailDomain), new Uri(@"https://apac.mail.erf.thomson.com/EWS/Exchange.asmx"));
app = new Ric.Util.ExcelApp(false, false);
if (app.ExcelAppInstance == null)
{
Logger.Log("Excel cannot be started", Logger.LogType.Error);
}
titles = new List <string>()
{
"MNEM", "NAME", "FUND FAMILY", "SEQN", "LOC", "SECD", "ISIN", "BDATE"
};
}
catch (Exception ex)
{
Logger.Log("Initialization failed. Ex: " + ex.Message);
throw new Exception("Cannot initialize");
}
}
public static Worksheet GetWorksheet(string filePath, string worksheetName)
{
Worksheet worksheet = null;
Workbook workbook = null;
using (ExcelApp app = new ExcelApp(false, false))
{
try
{
workbook = CreateOrOpenExcelFile(app, filePath);
if (!string.IsNullOrEmpty(worksheetName))
{
worksheet = workbook.Worksheets[1] as Worksheet;
}
else
{
worksheet = GetWorksheet(worksheetName, workbook);
}
if (worksheet == null)
{
throw new Exception("Cannot find worksheet " + worksheetName);
}
}
catch (Exception ex)
{
throw new Exception(string.Format("Error happened when getting worksheet {0} from {1}. Exception message is: {2}", worksheetName, filePath, ex.Message));
}
}
return(worksheet);
}
public static bool GenerateXls0rCsv(string path, List <List <string> > listList)
{
if (listList == null || listList.Count <= 1)//title must exist while no data in file
{
string msg = string.Format("no data need to generate");
throw new Exception(msg);
}
using (ExcelApp app = new ExcelApp(false, false))
{
try
{
Workbook wBook = ExcelUtil.CreateOrOpenExcelFile(app, path);
Worksheet wSheet = wBook.Worksheets[1] as Worksheet;
FillExcel(wSheet, listList);
app.ExcelAppInstance.AlertBeforeOverwriting = false;
wBook.Save();
return(true);
}
catch (Exception ex)
{
string msg = string.Format("generate XlsOrCsv file error ,msg:{0}", ex.ToString());
throw new Exception(msg);
}
}
}
public static void ExcelWorksheetToTsv(string excelFilePath, string worksheetName, string textFilePath)
{
using (var app = new ExcelApp(false, false))
{
var wb = ExcelUtil.CreateOrOpenExcelFile(app, excelFilePath);
var ws = ExcelUtil.GetWorksheet(worksheetName, wb);
ws.SaveAs(textFilePath, XlFileFormat.xlUnicodeText,
Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value);
}
}
/// <summary>
/// Creating new CSV with requested companies (new IPOs)
/// </summary>
/// <param></param>
/// <returns></returns>
private void GenerateCSV()
{
var app = new ExcelApp(false, false);
if (app.ExcelAppInstance == null)
{
string msg = "Excel could not be started. Check that your office installation and project reference are correct !";
LogMessage(msg, Logger.LogType.Error);
return;
}
try
{
_resultFilename = _configObj.ResultsWorkbookPath.Replace(".csv", "_" + DateTime.Now.ToString("ddMMM_HH_mm_ss") + ".csv");
Workbook workbook = ExcelUtil.CreateOrOpenExcelFile(app, _resultFilename);
Worksheet worksheet = workbook.Worksheets[1] as Worksheet;
for (int column = 0; column < _xlsTitle.Count; column++)
{
worksheet.Cells[1, column + 1] = _xlsTitle[column];
}
for (int line = 2; line <= ipoList.Count + 1; line++)
{
for (int column = 1; column <= _xlsTitle.Count; column++)
{
if (column == 5 || column == 6)
{
DateTime formatDate = DateTime.FromOADate(Convert.ToDouble(ipoList[line - 2][column - 1]));
worksheet.Cells[line, column] = formatDate.ToString("d");
}
else
{
worksheet.Cells[line, column] = ipoList[line - 2][column - 1];
}
}
}
app.ExcelAppInstance.AlertBeforeOverwriting = false;
AddResult("Result file", _resultFilename, "csv");
workbook.SaveAs(workbook.FullName, XlFileFormat.xlCSV, Type.Missing, Type.Missing, Type.Missing, Type.Missing, XlSaveAsAccessMode.xlExclusive, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
LogMessage("Generated CSV file Successfully. Filepath is " + _resultFilename);
workbook.Close();
}
catch (Exception ex)
{
string msg = "Cannot generate CSV file :" + ex;
LogMessage(msg, Logger.LogType.Error);
}
finally
{
app.Dispose();
File.Delete(_targetFilePath);
}
}
/// <summary>
/// Parsing CSV Finding new IPOs
/// </summary>
/// <param></param>
/// <returns></returns>
private void ReadCSV()
{
var app = new ExcelApp(false, false);
if (app.ExcelAppInstance == null)
{
string msg = "Excel could not be started. Check that your office installation and project reference are correct !";
Logger.Log(msg, Logger.LogType.Error);
return;
}
try
{
Workbook workbook = ExcelUtil.CreateOrOpenExcelFile(app, _targetFilePath);
Worksheet worksheet = workbook.Worksheets[1] as Worksheet;
double yesterday = DateTime.Now.AddDays(-2).ToOADate();
double firstDate = DateTime.Parse("15-Apr-13").ToOADate();
string country = String.Empty;
for (int line = 2; worksheet.get_Range("C" + line, Type.Missing).Value2 != null; line++)
{
var newIpo = new List <string>();
double createStamp = Convert.ToDouble(worksheet.Range["E" + line, Type.Missing].Value2.ToString());
double updateStamp = Convert.ToDouble(worksheet.Range["F" + line, Type.Missing].Value2.ToString());
country = worksheet.Range["M" + line, Type.Missing].Value2.ToString();
if (((updateStamp >= yesterday && createStamp >= firstDate) || createStamp >= yesterday) &&
_countries.Contains(country))
{
if (!updatedCountries.Contains(country))
{
updatedCountries.Add(country);
}
for (int column = 0; column < _xlsTitle.Count; column++)
{
newIpo.Add(worksheet.Range[Alphabet.Substring(column, 1) + line, Type.Missing].Value2.ToString());
}
ipoList.Add(newIpo);
}
}
workbook.Close();
}
catch (Exception ex)
{
string msg = "Cannot read CSV file :" + ex;
LogMessage(msg, Logger.LogType.Error);
}
finally
{
app.Dispose();
}
}
private List <string> GetRicFromFile(string item)
{
List <string> list = new List <string>();
try
{
using (Ric.Util.ExcelApp app = new Ric.Util.ExcelApp(false, false))
{
var workbook = ExcelUtil.CreateOrOpenExcelFile(app, item);
var worksheet = workbook.Worksheets[1] as Worksheet;
if (worksheet != null)
{
int lastUsedRow = worksheet.UsedRange.Row + worksheet.UsedRange.Rows.Count - 1;
for (int i = 2; i <= lastUsedRow; i++)
{
object value = ExcelUtil.GetRange(i, 1, worksheet).Value2;
string ric = string.Empty;
if (value != null && value.ToString().Trim() != string.Empty)
{
ric = value.ToString().Trim();
if (ric.Contains("RIC") && list.Contains(ric))
{
continue;
}
list.Add(ric);
}
}
}
workbook.Close(false, workbook.FullName, Missing.Value);
}
return(list);
}
catch (Exception ex)
{
string msg = string.Format("execute GetRicFromFile(string item) failed. item:{0},msg:{1}", item, ex.Message);
Logger.Log(msg, Logger.LogType.Error);
return(null);
}
}
/// <summary>
/// ReadXlsFile
/// </summary>
/// <param name="listHKIPO">ric</param>
/// <param name="strFilePath">file path</param>
private void GetHKIPOFromExcelFile(List <string> listHKIPO, string strFilePath)
{
try
{
string worksheetName = "FM";
using (Ric.Util.ExcelApp app = new Ric.Util.ExcelApp(false, false))
{
var workbook = ExcelUtil.CreateOrOpenExcelFile(app, strFilePath);
var worksheet = ExcelUtil.GetWorksheet(worksheetName, workbook);
if (worksheet != null)
{
int lastUsedRow = worksheet.UsedRange.Row + worksheet.UsedRange.Rows.Count - 1;
for (int i = 1; i <= lastUsedRow; i++)
{
object key = ExcelUtil.GetRange(i, 1, worksheet).Value2;
object value = ExcelUtil.GetRange(i, 2, worksheet).Value2;
if (key != null && key.ToString().Trim() != string.Empty && value != null && value.ToString().Trim() != string.Empty)
{
if (key.ToString().Trim().Contains("Underlying RIC:") || key.ToString().Trim().Contains("Composite chain RIC:") || key.ToString().Trim().Contains("Broker page RIC:") || key.ToString().Trim().Contains("Misc.Info page RIC:"))
{
if (!listHKIPO.Contains(value.ToString().Trim()))
{
listHKIPO.Add(value.ToString().Trim());
}
}
}
}
}
workbook.Close(false, workbook.FullName, Missing.Value);
}
}
catch (Exception e)
{
Logger.Log(string.Format("Error happens when get data. Ex: {0} .", e.Message));
}
}
private List <string> GetRicFromFile(List <string> list)
{
List <string> listRic = new List <string>();
string filePath = string.Empty;
if (list == null || list.Count == 0)
{
string msg = string.Format("no download file in this email .");
Logger.Log(msg, Logger.LogType.Warning);
return(null);
}
foreach (var item in list)
{
filePath = item;
break;
}
using (Ric.Util.ExcelApp app = new Ric.Util.ExcelApp(false, false))
{
var workbook = ExcelUtil.CreateOrOpenExcelFile(app, filePath);
var worksheet = (Worksheet)workbook.Worksheets[1];
int lastUsedRow = worksheet.UsedRange.Row + worksheet.UsedRange.Rows.Count - 1;
using (ExcelLineWriter reader = new ExcelLineWriter(worksheet, 1, 1, ExcelLineWriter.Direction.Down))
{
while (reader.Row <= lastUsedRow)
{
string key = reader.ReadLineCellText();
if (key.Equals("CHANGE"))
{
reader.PlaceNext(reader.Row + 11, reader.Col);
}
if (key.Equals("DROP"))
{
reader.PlaceNext(reader.Row + 8, reader.Col);
}
if (key.Equals("RIC"))
{
int lastUsedCol = worksheet.UsedRange.Columns.Count;
string value = string.Empty;
string ricSS = string.Empty;
using (ExcelLineWriter readerCol = new ExcelLineWriter(worksheet, reader.Row - 1, reader.Col + 1, ExcelLineWriter.Direction.Right))
{
while (readerCol.Col <= lastUsedCol)
{
value = readerCol.ReadLineCellText();
if (!string.IsNullOrEmpty(value) && !listRic.Contains(value))
{
listRic.Add(value);
}
if (value.EndsWith(".SS") && value.StartsWith("6"))
{
ricSS = string.Format("{0}.SH", value.Substring(0, value.Length - 3));
if (!string.IsNullOrEmpty(ricSS) && !listRic.Contains(ricSS))
{
listRic.Add(ricSS);
}
}
}
}
}
}
reader.PlaceNext(reader.Row, 1);
}
workbook.Close(false, workbook.FullName, false);
}
return(listRic);
}
