private static void OpenExcel(string strFileName)
{
object missing = System.Reflection.Missing.Value;
Application excel = new Application();//lauch excel application
if (excel == null)
{
Console.WriteLine("No file...");
}
else
{
excel.Visible = false; excel.UserControl = true;
Workbook wb = excel.Application.Workbooks.Open(strFileName, missing, true, missing, missing, missing,
missing, missing, missing, true, missing, missing, missing, missing, missing);
//Get First working sheet
Worksheet ws = (Worksheet)wb.Worksheets.get_Item(model.itemindex);
string[] alph = new string[] { "D", "E", "F", "G", "H", "I", "J", "K", "L", "M", "N", "O", "P", "Q", "R", "S", "T", "U", "V", "W", "X", "Y", "Z", "AA", "AB", "AC", "AD", "AE", "AF", "AG", "AH", "AI", "AJ", "AK", "AL", "AM", "AN", "AO", "AP", "AQ", "AR", "AS", "AT", "AU", "AV", "AW", "AX", "AY", "AZ", "BA", "BB", "BC", "BD", "BE", "BF", "BG", "BH", "BI", "BJ", "BK", "BL", "BM", "BN", "BO", "BP", "BQ", "BR", "BS", "BT", "BU", "BV", "BW", "BX", "BY", "BZ", "CA", "CB", "CC", "CD", "CE", "CF", "CG", "CH", "CI", "CJ", "CK", "CL", "CM", "CN", "CO", "CP", "CQ", "CR", "CS", "CT", "CU", "CV", "CW", "CX", "CY", "CZ", "DA", "DB", "DC", "DD", "DE", "DF", "DG", "DH", "DI", "DJ", "DK", "DL", "DM", "DN", "DO", "DP" };
string[] Tenor = new string[] { "21", "21", "21", "21", "21", "21", "21", "21", "21", "21", "21", "21", "21", "63", "63", "63", "63", "63", "63", "63", "63", "63", "63", "63", "63", "63", "126", "126", "126", "126", "126", "126", "126", "126", "126", "126", "126", "126", "126", "189", "189", "189", "189", "189", "189", "189", "189", "189", "189", "189", "189", "189", "252", "252", "252", "252", "252", "252", "252", "252", "252", "252", "252", "252", "252", "504", "504", "504", "504", "504", "504", "504", "504", "504", "504", "504", "504", "504", "756", "756", "756", "756", "756", "756", "756", "756", "756", "756", "756", "756", "756", "1008", "1008", "1008", "1008", "1008", "1008", "1008", "1008", "1008", "1008", "1008", "1008", "1008", "1260", "1260", "1260", "1260", "1260", "1260", "1260", "1260", "1260", "1260", "1260", "1260", "1260" };
string[] Mny = new string[] { "0.70", "0.75", "0.80", "0.85", "0.90", "0.95", "1.00", "1.05", "1.10", "1.15", "1.20", "1.25", "1.30", "0.70", "0.75", "0.80", "0.85", "0.90", "0.95", "1.00", "1.05", "1.10", "1.15", "1.20", "1.25", "1.30", "0.70", "0.75", "0.80", "0.85", "0.90", "0.95", "1.00", "1.05", "1.10", "1.15", "1.20", "1.25", "1.30", "0.70", "0.75", "0.80", "0.85", "0.90", "0.95", "1.00", "1.05", "1.10", "1.15", "1.20", "1.25", "1.30", "0.70", "0.75", "0.80", "0.85", "0.90", "0.95", "1.00", "1.05", "1.10", "1.15", "1.20", "1.25", "1.30", "0.70", "0.75", "0.80", "0.85", "0.90", "0.95", "1.00", "1.05", "1.10", "1.15", "1.20", "1.25", "1.30", "0.70", "0.75", "0.80", "0.85", "0.90", "0.95", "1.00", "1.05", "1.10", "1.15", "1.20", "1.25", "1.30", "0.70", "0.75", "0.80", "0.85", "0.90", "0.95", "1.00", "1.05", "1.10", "1.15", "1.20", "1.25", "1.30", "0.70", "0.75", "0.80", "0.85", "0.90", "0.95", "1.00", "1.05", "1.10", "1.15", "1.20", "1.25", "1.30" };
Range rng1 = ws.Cells.get_Range("B6", "B" + model.rowsend); //Date
Range rng2 = ws.Cells.get_Range("C6", "C" + model.rowsend); //Mny
object[,] arrydate = (object[,])rng1.Value2; //get range's value
object[,] arryPrice = (object[,])rng2.Value2;
StreamWriter sw = null;
string[,] arry = new string[model.rowsint - 1, 2];
for (int i = 1; i <= alph.Count(); i++)
{
for (int j = 1; j <= model.rowsint; j++)
{
Range rng3 = ws.Cells.get_Range(alph[i - 1].ToString() + "6", alph[i - 1].ToString() + model.rowsend); //GetValue
object[,] arryValue = (object[,])rng3.Value2;
string a = arrydate[j, 1].ToString();
string b = arryPrice[j, 1].ToString();
string c = arryValue[j, 1].ToString();
string d = Tenor[i - 1].ToString();
string e = Mny[i - 1].ToString();
string output2 = model.SourceID + "," + model.Underlyingid + "," + Mny[i - 1].ToString() + ",2," + Tenor[i - 1].ToString() + "," + DateTime.FromOADate(Convert.ToInt32(arrydate[j, 1].ToString())).ToString("yyyy-MM-dd") + ",0,OPTIONTYPE,European," + arryValue[j, 1].ToString() + ",0,0,0,0,0,0,2999-12-31,QSDB Import Job,2015-06-19,0";
sw = new StreamWriter(@"I:\Tickers\" + model.filename + ".bcp", true);
sw.WriteLine(output2); sw.Close(); sw.Dispose();
}
}
}
excel.Quit(); excel = null;
Process[] procs = Process.GetProcessesByName("excel");
foreach (Process pro in procs)
{
pro.Kill();
}
GC.Collect();
}
public class model
{
public static string Underlyingid { get; set; }
public static string filename { get; set; }
public static string SourceID { get; set; }
public static int itemindex { get; set; }
public static int rowsint { get; set; }
public static int columnCount { get; set; }
public static int rowsend { get; set; }
public static int columnend { get; set; }
}
{
object missing = System.Reflection.Missing.Value;
Application excel = new Application();//lauch excel application
if (excel == null)
{
Console.WriteLine("No file...");
}
else
{
excel.Visible = false; excel.UserControl = true;
Workbook wb = excel.Application.Workbooks.Open(strFileName, missing, true, missing, missing, missing,
missing, missing, missing, true, missing, missing, missing, missing, missing);
//Get First working sheet
Worksheet ws = (Worksheet)wb.Worksheets.get_Item(model.itemindex);
string[] alph = new string[] { "D", "E", "F", "G", "H", "I", "J", "K", "L", "M", "N", "O", "P", "Q", "R", "S", "T", "U", "V", "W", "X", "Y", "Z", "AA", "AB", "AC", "AD", "AE", "AF", "AG", "AH", "AI", "AJ", "AK", "AL", "AM", "AN", "AO", "AP", "AQ", "AR", "AS", "AT", "AU", "AV", "AW", "AX", "AY", "AZ", "BA", "BB", "BC", "BD", "BE", "BF", "BG", "BH", "BI", "BJ", "BK", "BL", "BM", "BN", "BO", "BP", "BQ", "BR", "BS", "BT", "BU", "BV", "BW", "BX", "BY", "BZ", "CA", "CB", "CC", "CD", "CE", "CF", "CG", "CH", "CI", "CJ", "CK", "CL", "CM", "CN", "CO", "CP", "CQ", "CR", "CS", "CT", "CU", "CV", "CW", "CX", "CY", "CZ", "DA", "DB", "DC", "DD", "DE", "DF", "DG", "DH", "DI", "DJ", "DK", "DL", "DM", "DN", "DO", "DP" };
string[] Tenor = new string[] { "21", "21", "21", "21", "21", "21", "21", "21", "21", "21", "21", "21", "21", "63", "63", "63", "63", "63", "63", "63", "63", "63", "63", "63", "63", "63", "126", "126", "126", "126", "126", "126", "126", "126", "126", "126", "126", "126", "126", "189", "189", "189", "189", "189", "189", "189", "189", "189", "189", "189", "189", "189", "252", "252", "252", "252", "252", "252", "252", "252", "252", "252", "252", "252", "252", "504", "504", "504", "504", "504", "504", "504", "504", "504", "504", "504", "504", "504", "756", "756", "756", "756", "756", "756", "756", "756", "756", "756", "756", "756", "756", "1008", "1008", "1008", "1008", "1008", "1008", "1008", "1008", "1008", "1008", "1008", "1008", "1008", "1260", "1260", "1260", "1260", "1260", "1260", "1260", "1260", "1260", "1260", "1260", "1260", "1260" };
string[] Mny = new string[] { "0.70", "0.75", "0.80", "0.85", "0.90", "0.95", "1.00", "1.05", "1.10", "1.15", "1.20", "1.25", "1.30", "0.70", "0.75", "0.80", "0.85", "0.90", "0.95", "1.00", "1.05", "1.10", "1.15", "1.20", "1.25", "1.30", "0.70", "0.75", "0.80", "0.85", "0.90", "0.95", "1.00", "1.05", "1.10", "1.15", "1.20", "1.25", "1.30", "0.70", "0.75", "0.80", "0.85", "0.90", "0.95", "1.00", "1.05", "1.10", "1.15", "1.20", "1.25", "1.30", "0.70", "0.75", "0.80", "0.85", "0.90", "0.95", "1.00", "1.05", "1.10", "1.15", "1.20", "1.25", "1.30", "0.70", "0.75", "0.80", "0.85", "0.90", "0.95", "1.00", "1.05", "1.10", "1.15", "1.20", "1.25", "1.30", "0.70", "0.75", "0.80", "0.85", "0.90", "0.95", "1.00", "1.05", "1.10", "1.15", "1.20", "1.25", "1.30", "0.70", "0.75", "0.80", "0.85", "0.90", "0.95", "1.00", "1.05", "1.10", "1.15", "1.20", "1.25", "1.30", "0.70", "0.75", "0.80", "0.85", "0.90", "0.95", "1.00", "1.05", "1.10", "1.15", "1.20", "1.25", "1.30" };
Range rng1 = ws.Cells.get_Range("B6", "B" + model.rowsend); //Date
Range rng2 = ws.Cells.get_Range("C6", "C" + model.rowsend); //Mny
object[,] arrydate = (object[,])rng1.Value2; //get range's value
object[,] arryPrice = (object[,])rng2.Value2;
StreamWriter sw = null;
string[,] arry = new string[model.rowsint - 1, 2];
for (int i = 1; i <= alph.Count(); i++)
{
for (int j = 1; j <= model.rowsint; j++)
{
Range rng3 = ws.Cells.get_Range(alph[i - 1].ToString() + "6", alph[i - 1].ToString() + model.rowsend); //GetValue
object[,] arryValue = (object[,])rng3.Value2;
string a = arrydate[j, 1].ToString();
string b = arryPrice[j, 1].ToString();
string c = arryValue[j, 1].ToString();
string d = Tenor[i - 1].ToString();
string e = Mny[i - 1].ToString();
string output2 = model.SourceID + "," + model.Underlyingid + "," + Mny[i - 1].ToString() + ",2," + Tenor[i - 1].ToString() + "," + DateTime.FromOADate(Convert.ToInt32(arrydate[j, 1].ToString())).ToString("yyyy-MM-dd") + ",0,OPTIONTYPE,European," + arryValue[j, 1].ToString() + ",0,0,0,0,0,0,2999-12-31,QSDB Import Job,2015-06-19,0";
sw = new StreamWriter(@"I:\Tickers\" + model.filename + ".bcp", true);
sw.WriteLine(output2); sw.Close(); sw.Dispose();
}
}
}
excel.Quit(); excel = null;
Process[] procs = Process.GetProcessesByName("excel");
foreach (Process pro in procs)
{
pro.Kill();
}
GC.Collect();
}
public class model
{
public static string Underlyingid { get; set; }
public static string filename { get; set; }
public static string SourceID { get; set; }
public static int itemindex { get; set; }
public static int rowsint { get; set; }
public static int columnCount { get; set; }
public static int rowsend { get; set; }
public static int columnend { get; set; }
}