private void button1_Click(object sender, EventArgs e)
{
Excel.Application OrgWorkExcel;
Excel.Application NewWorkExcel;
Excel.Workbook OrgWorkBook;
Excel.Workbook NewWorkBook;
Excel.Worksheet NewWorkSheet;
Excel.Worksheet OrgWorkSheet;
Excel.Range xlCell;
Excel.Range oRange;
Excel.Range Data;
Excel.Range otherData;
Excel.Range WholeCell;
Excel.Range BorderRow;
Excel.Borders BorderOfCell;
int xCol = 1;
int xRow = 1;
int RowCount;
int ColCount;
int Num = 1;
String SignID;
String New_Building;
String New_Level;
String New_Zone;
String Old_Building = "none";
String Old_Level = "none";
String Old_Zone = "none";
String mData;
String IMG_ID;
String[] Title = new String[9];
OrgWorkExcel = new Excel.Application();
OrgWorkBook = OrgWorkExcel.Workbooks.Open(@"E:\Airport_Project\2010511\GenExcel\MFC_Sign_Id.xlsx"); // Open the Original Workbook
OrgWorkSheet = (Microsoft.Office.Interop.Excel.Worksheet)OrgWorkBook.Sheets["Sheet1"]; // Open the Original Workbooks' sheet 1
NewWorkExcel = new Excel.Application();
NewWorkBook = NewWorkExcel.Workbooks.Add(); //Create a new excel file
NewWorkSheet = (Microsoft.Office.Interop.Excel.Worksheet)NewWorkBook.Sheets["Sheet1"]; //Open the new excel sheet 1
WholeCell = NewWorkSheet.get_Range("A1", "AZ10000");
otherData = NewWorkSheet.get_Range("A2", "AZ10000");
otherData.RowHeight = 220;
//Set global attributes
NewWorkExcel.StandardFont = "Gulim"; //This line is set up the Font
WholeCell.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
RowCount = OrgWorkSheet.UsedRange.Rows.Count; //Get the total row of the excel
ColCount = OrgWorkSheet.UsedRange.Columns.Count;
//System.Diagnostics.Debug.Print(Convert.ToString(RowCount));
// PreSet the
Title[0] = "Sign ID";
Title[1] = "Side A Type_ID";
Title[2] = "Side B Type_ID";
Title[3] = "Changed";
Title[4] = "IMG_Side A";
Title[5] = "IMG_Side B";
Title[6] = "Building";
Title[7] = "Level";
Title[8] = "Zone";
for (int iRow = 1; iRow <= RowCount; iRow++)
{
if (xRow == 1)
{
System.Diagnostics.Debug.Print(Convert.ToString(xRow));
for (int iCol = 0; iCol <= 8; iCol++)
{
NewWorkSheet.Cells[xRow, iCol + 1] = Title[iCol];
}
xRow++;
}
else if (xRow > 1)
{
xlCell = (Excel.Range)OrgWorkSheet.Cells[iRow, 1];
SignID = xlCell.Value.ToString();
string[] words = SignID.Split('/');
New_Building = words[0];
New_Level = words[1];
New_Zone = words[2];
//Compare building's part
if (New_Building != Old_Building)
{
NewWorkBook.SaveAs(@"E:\Airport_Project\2010511\GenExcel\" + Old_Building + "_" + Old_Level + "_" + Old_Zone + ".xls", Excel.XlFileFormat.xlWorkbookNormal);
NewWorkBook.Close(true);
NewWorkExcel = new Excel.Application();
NewWorkBook = NewWorkExcel.Workbooks.Add(); //Create a new excel file
NewWorkSheet = (Microsoft.Office.Interop.Excel.Worksheet)NewWorkBook.Sheets["Sheet1"]; //Open the new excel sheet 1
WholeCell = NewWorkSheet.get_Range("A1", "AZ10000");
otherData = NewWorkSheet.get_Range("A2", "AZ10000");
otherData.RowHeight = 220;
//Set global attributes
NewWorkExcel.StandardFont = "Gulim";
WholeCell.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
for (int iCol = 0; iCol <= 8; iCol++)
{
NewWorkSheet.Cells[1, iCol + 1] = Title[iCol];
}
Old_Building = New_Building;
Old_Level = New_Level;
Old_Zone = New_Zone;
xRow = 2;
}
else if (New_Level != Old_Level)
{
NewWorkBook.SaveAs(@"E:\Airport_Project\2010511\GenExcel\" + Old_Building + "_" + Old_Level + "_" + Old_Zone + ".xls", Excel.XlFileFormat.xlWorkbookNormal);
NewWorkBook.Close(true);
NewWorkExcel = new Excel.Application();
NewWorkBook = NewWorkExcel.Workbooks.Add(); //Create a new excel file
NewWorkSheet = (Microsoft.Office.Interop.Excel.Worksheet)NewWorkBook.Sheets["Sheet1"]; //Open the new excel sheet 1
WholeCell = NewWorkSheet.get_Range("A1", "AZ10000");
otherData = NewWorkSheet.get_Range("A2", "AZ10000");
otherData.RowHeight = 220;
//Set global attributes
NewWorkExcel.StandardFont = "Gulim";
WholeCell.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
for (int iCol = 0; iCol <= 8; iCol++)
{
NewWorkSheet.Cells[1, iCol + 1] = Title[iCol];
}
Old_Level = New_Level;
Old_Zone = New_Zone;
xRow = 2;
}
else if (New_Zone != Old_Zone)
{
NewWorkBook.SaveAs(@"E:\Airport_Project\2010511\GenExcel\" + Old_Building + "_" + Old_Level + "_" + Old_Zone + ".xls", Excel.XlFileFormat.xlWorkbookNormal);
NewWorkBook.Close(true);
NewWorkExcel = new Excel.Application();
NewWorkBook = NewWorkExcel.Workbooks.Add(); //Create a new excel file
NewWorkSheet = (Microsoft.Office.Interop.Excel.Worksheet)NewWorkBook.Sheets["Sheet1"]; //Open the new excel sheet 1
WholeCell = NewWorkSheet.get_Range("A1", "AZ10000");
otherData = NewWorkSheet.get_Range("A2", "AZ10000");
otherData.RowHeight = 220;
//Set global attributes
NewWorkExcel.StandardFont = "Gulim";
WholeCell.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
for (int iCol = 0; iCol <= 8; iCol++)
{
NewWorkSheet.Cells[1, iCol + 1] = Title[iCol];
}
Old_Zone = New_Zone;
xRow = 2;
}
//System.Diagnostics.Debug.Print("Building: " + words[0] +"/"+words[1]+"/"+words[2]);
//System.Diagnostics.Debug.Print("New Building: " + Old_Building + "/"+ Old_Level + "/"+ Old_Zone);
int DataLoop = 0;
for (int iCol = 1; iCol <= ColCount; iCol++)
{
if (iCol == 1 || iCol == 2 || iCol == 4)
{
NewWorkSheet.Cells[xRow, xCol] = OrgWorkSheet.Cells[iRow, iCol];
if (iCol == 2 || iCol == 4)
{
int uCol = xCol + 3;
System.Diagnostics.Debug.Print("uCol: " + uCol);
Data = (Excel.Range)OrgWorkSheet.Cells[iRow, iCol];
oRange = (Excel.Range)NewWorkSheet.Cells[xRow, uCol];
float Top = (float)((double)oRange.Top);
float Left = (float)((double)oRange.Left);
int Width = 42;
int Height = 220;
oRange.RowHeight = Height;
oRange.ColumnWidth = Width;
const float ImageSize = 30;
if (Data.Value != null)
{
//System.Diagnostics.Debug.Print("DataLoop: " + Convert.ToString(DataLoop));
mData = Data.Value.ToString();
if (mData.Split('-') != null)
{
string[] mWords = mData.Split('-');
IMG_ID = "DSC0" + mWords[1] + ".jpg";
NewWorkSheet.Cells[xRow, xCol] = IMG_ID;
System.Diagnostics.Debug.Print("Image ID: " + IMG_ID);
NewWorkSheet.Shapes.AddPicture(@"E:\Airport_Project\20170516\QA_IMG_PlatForm\Resize_Image\" + IMG_ID, MsoTriState.msoFalse, MsoTriState.msoCTrue, Left, Top, 255, Height);
//NewWorkSheet.Shapes.AddPicture(@"E:\Airport_Project\20170509\QA_IMG_PlatForm\QA_IMG_Collected_Resize\" + mData[DataLoop] + ".JPG", MsoTriState.msoFalse, MsoTriState.msoCTrue, Left, Top, Width, Height);
DataLoop++;
}
else if (mData.Split('_') != null)
{
string[] mWords = mData.Split('-');
IMG_ID = "IMG_" + mWords[1] + ".jpg";
NewWorkSheet.Cells[xRow, xCol] = IMG_ID;
System.Diagnostics.Debug.Print("Image ID: " + IMG_ID);
NewWorkSheet.Shapes.AddPicture(@"E:\Airport_Project\20170516\QA_IMG_PlatForm\Resize_Image\" + IMG_ID, MsoTriState.msoFalse, MsoTriState.msoCTrue, Left, Top, 255, Height);
//NewWorkSheet.Shapes.AddPicture(@"E:\Airport_Project\20170509\QA_IMG_PlatForm\QA_IMG_Collected_Resize\" + mData[DataLoop] + ".JPG", MsoTriState.msoFalse, MsoTriState.msoCTrue, Left, Top, Width, Height);
DataLoop++;
}
}
}
xCol++;
}
else if (iCol == 6 || iCol == 7 || iCol == 8)
{
xCol = iCol + 1;
NewWorkSheet.Cells[xRow, xCol] = OrgWorkSheet.Cells[iRow, iCol];
//Setup the formate of the Cells
Excel.Range Location = (Microsoft.Office.Interop.Excel.Range)NewWorkSheet.Cells[xRow, xCol];
Location.Font.Bold = true;
xCol = 1;
//NewWorkBook.SaveAs(@"E:\Airport_Project\2010511\GenExcel\"+ Num +".xls", Excel.XlFileFormat.xlWorkbookNormal);
//System.Diagnostics.Debug.Print("Saving!");
Num++;
}
}
}
xRow++;
}
//NewWorkBook.SaveAs(@"E:\Airport_Project\2010511\GenExcel\Demo.xls", Excel.XlFileFormat.xlWorkbookNormal);
NewWorkBook.SaveAs(@"E:\Airport_Project\2010511\GenExcel\" + Old_Building + "_" + Old_Level + "_" + Old_Zone + ".xls", Excel.XlFileFormat.xlWorkbookNormal);
NewWorkBook.Close(true);
OrgWorkBook.Close(true);
MessageBox.Show("File created !");
}
/**
* Removes a sign from the map.
* @param sign_id The sign to remove.
* @returns True if and only if the sign has been removed.
*/
public static bool RemoveSign(SignID sign_id)
{
throw null;
}
/**
* Gets the location of the sign.
* @param sign_id The sign to get the location of.
* @returns The location of the sign.
*/
public static TileIndex GetLocation(SignID sign_id)
{
throw null;
}
/**
* Get the name of the sign.
* @param sign_id The sign to get the name of.
* @returns The name of the sign.
*/
public static string GetName(SignID sign_id)
{
throw null;
}
/**
* Set the name of a sign.
* @param sign_id The sign to set the name for.
* @param name The name for the sign (can be either a raw string, or a ScriptText object).
* @exception AIError.ERR_NAME_IS_NOT_UNIQUE
* @returns True if and only if the name was changed.
*/
public static bool SetName(SignID sign_id, string name)
{
throw null;
}
/**
* Checks whether the given sign index is valid.
* @param sign_id The index to check.
* @returns True if and only if the sign is valid.
*/
public static bool IsValidSign(SignID sign_id)
{
throw null;
}