//define variables
//read an excel file
public static void ReadFillSheetbyChecking(string path, int rows_begin, int rows_end, int columns_begin, int columns_end, string date)
{
Worksheet sheet = null;
string departure = "";
string destination = "";
if (Directory.Exists(path))
{
string path_file = path + "\\AirlineMatrix.xlsx";
Microsoft.Office.Interop.Excel.Application app = new Microsoft.Office.Interop.Excel.Application();
Workbook book = app.Workbooks.Open(path_file);
sheet = book.Worksheets["sheet1"];
//sheet is not null, and index numbers are correct.
if (sheet != null && rows_begin > 0 && columns_begin > 0 && rows_end >= rows_begin && columns_end >= columns_begin)
{
//there is an additional column to record the Chinese name of the city, other than simple code. Thus, starting from 2.
for (int i = rows_begin; i < rows_end; i++)
{
for (int j = columns_begin; j < columns_end; j++)
{
departure = sheet.Cells[i + 1, 0 + 2].Text;
destination = sheet.Cells[0 + 2, j + 1].Text;
//rewrite the js file
if (i != j)
{
bool success = Form_Airline_Matrix.Modify_JS_Parameters(departure, destination, date);
if (success)
{
//run the batch file which actually calls the JS script.
Process prc = System.Diagnostics.Process.Start("C:\\Phantomjs\\casperjs-1.1.4-1\\casperjs-1.1.4-1\\bin\\CheckPath.bat");
prc.WaitForExit();
//read the interim file
StreamReader reader = new StreamReader("C:\\Phantomjs\\casperjs-1.1.4-1\\casperjs-1.1.4-1\\bin\\check_path_interim" +
"_" + departure + "_" + destination + ".txt");
string[] arr_path_check = reader.ReadToEnd().Split('\n');
//close reader
reader.Close();
//fill the path_check value (end of the interim file) to the sheet in the memory.
sheet.Cells[i + 1, j + 1] = arr_path_check[arr_path_check.Length - 1];
}
else
{
MessageBox.Show("Modify JS file failed.");
}
}
else
{
sheet.Cells[i + 1, j + 1] = 0;
}
}
}
//save the excel.
app.DisplayAlerts = false;
book.Save();
app.Quit();
}
else
{
MessageBox.Show("Issues with the sheet (null, or number of rows/columns)");
}
}
}
private void button_OK_Click(object sender, EventArgs e)
{
Form_Airline_Matrix.ReadFillSheetbyChecking(textBox_excel_path.Text, int.Parse(textBox_row_begin.Text), int.Parse(textBox_row_end.Text),
int.Parse(textBox_col_begin.Text), int.Parse(textBox_col_end.Text), textBox_date.Text);
}