private void ExportButton_Click(object sender, EventArgs e)
{
if (Utility.GetExcelVersion() < 0)
{
MessageBox.Show("Excel was not installed !");
return;
}
ExcelExportSettingForm exportSettingForm = new ExcelExportSettingForm();
if (exportSettingForm.ShowDialog() == DialogResult.Cancel)
return;
string reportTemplateFile;
SaveFileDialog saveFileDialog = new SaveFileDialog();
saveFileDialog.Title = "Select report file name";
if (exportSettingForm.ExportOffice2003)
{
reportTemplateFile = System.AppDomain.CurrentDomain.BaseDirectory
+ "1HopReportTemplate.xls";
saveFileDialog.Filter = "report file(*.xls)|*.xls";
}
else
{
reportTemplateFile = System.AppDomain.CurrentDomain.BaseDirectory
+ "1HopReportTemplate.xlsx";
saveFileDialog.Filter = "report file(*.xlsx)|*.xlsx";
}
do
{
if (DialogResult.Cancel == saveFileDialog.ShowDialog())
return;
if (saveFileDialog.FileName.Equals(reportTemplateFile,
StringComparison.OrdinalIgnoreCase))
{
MessageBox.Show("Can't select report template file !");
continue;
}
try
{
File.Copy(reportTemplateFile, saveFileDialog.FileName, true);
}
catch (System.Exception ex)
{
MessageBox.Show("Can't save file " + saveFileDialog.FileName
+ "!\r\n" + "Select another file name for report.");
continue;
}
mExportFileName = saveFileDialog.FileName;
break;
} while (true);
Hide();
mExportStatusForm.Show();
mCancelExport = false;
mExportThread = new Thread(delegate()
{
bool isReportSucceed = false;
string[] subBands = new string[2];
System.Globalization.CultureInfo Oldci = null;
Excel._Application app = null;
Excel.WorkbookClass workBook = null;
Excel.Sheets sheets = null;
Excel.Worksheet sheet = null;
Excel.Worksheet summarySheet;
Excel.Worksheet combinationSheet;
try
{
Oldci = System.Threading.Thread.CurrentThread.CurrentCulture;
System.Threading.Thread.CurrentThread.CurrentCulture = new CultureInfo("en-us");
app = new Excel.Application();
app.DisplayAlerts = false;
object objOpt = System.Reflection.Missing.Value;
UpdateStatus("Initialize ...");
workBook = (Excel.WorkbookClass)app.Workbooks.Open(
mExportFileName, objOpt, false, objOpt, objOpt, objOpt, true,
objOpt, objOpt, true, objOpt, objOpt, objOpt, objOpt, objOpt);
sheets = workBook.Worksheets;
summarySheet = (Excel.Worksheet)sheets["Summary"];
summarySheet.Cells[1, 4] = mEmiA.Site_ID;
summarySheet.Cells[1, 12] = mEmiB.Site_ID;
combinationSheet = (Excel.Worksheet)sheets["available channels combination"];
if (AnalysisChannelCombination)
UpdateCombinationSheet(combinationSheet);
else
combinationSheet.Delete();
UpdateStatus("Export Cover sheet ...");
/* Cover Sheet */
sheet = (Excel.Worksheet)sheets["Cover"];
if (!string.IsNullOrEmpty(mEmiA.PI_ID))
sheet.Cells[15, 5] = mEmiA.PI_ID;
if (!string.IsNullOrEmpty(mEmiA.PA_UserName))
sheet.Cells[17, 5] = mEmiA.PA_UserName;
double startFrequencyA = double.MaxValue;
double startFrequencyB = double.MaxValue;
double endFrequencyA = double.MinValue;
double endFrequencyB = double.MinValue;
WatsEmiDataManager dataManagerA, dataManagerB;
dataManagerA = Utility.GetEmiDataManager(mEmiA, mChannelSettings);
dataManagerB = Utility.GetEmiDataManager(mEmiB, mChannelSettings);
foreach (DG_Type dataGroup in mEmiA.DataGroups)
{
if (dataGroup.DG_FB_Angle != mAzimuthA)
continue;
if (dataGroup.DG_FB_Start < startFrequencyA)
startFrequencyA = dataGroup.DG_FB_Start;
if (dataGroup.DG_FB_End > endFrequencyA)
endFrequencyA = dataGroup.DG_FB_End;
}
foreach (DG_Type dataGroup in mEmiB.DataGroups)
{
if (dataGroup.DG_FB_Angle != mAzimuthB)
continue;
if (dataGroup.DG_FB_Start < startFrequencyB)
startFrequencyB = dataGroup.DG_FB_Start;
if (dataGroup.DG_FB_End > endFrequencyB)
endFrequencyB = dataGroup.DG_FB_End;
}
for (int i = 1; i < mChannelSettings.Count; i++)
{
if (mCancelExport)
return;
((Excel.Range)summarySheet.Rows[3, objOpt]).Copy(objOpt);
((Excel.Range)summarySheet.Cells[3 + i, 1]).EntireRow.Insert(objOpt, objOpt);
}
for (int i = 0; i < mChannelSettings.Count; i++)
{
subBands[0] = "";
subBands[1] = "";
if (mChannelSettings[i].ODUSubBand.Length == 1)
subBands[0] = mChannelSettings[i].ODUSubBand;
else if (mChannelSettings[i].ODUSubBand.Length == 3)
{
subBands[0] = mChannelSettings[i].ODUSubBand.Substring(0, 1);
subBands[1] = mChannelSettings[i].ODUSubBand.Substring(2);
}
summarySheet.Cells[3 + i, 1] = subBands[0];
summarySheet.Cells[3 + i, 2] = subBands[1];
summarySheet.Cells[3 + i, 3] = mChannelSettings[i].ChannelName;
summarySheet.Cells[3 + i, 4] = mChannelSettings[i].CenterFreq.ToString();
summarySheet.Cells[3 + i, 7] = mChannelSettings[i].Pair.ChannelName;
summarySheet.Cells[3 + i, 8] = mChannelSettings[i].Pair.CenterFreq.ToString();
summarySheet.Cells[3 + i, 11] = mChannelSettings[i].ChannelName;
summarySheet.Cells[3 + i, 12] = mChannelSettings[i].CenterFreq.ToString();
summarySheet.Cells[3 + i, 15] = mChannelSettings[i].Pair.ChannelName;
summarySheet.Cells[3 + i, 16] = mChannelSettings[i].Pair.CenterFreq.ToString();
summarySheet.Cells[3 + i, 5] = summarySheet.Cells[3 + i, 6]
= summarySheet.Cells[3 + i, 9] = summarySheet.Cells[3 + i, 10]
= summarySheet.Cells[3 + i, 13] = summarySheet.Cells[3 + i, 14]
= summarySheet.Cells[3 + i, 17] = summarySheet.Cells[3 + i, 18]
= summarySheet.Cells[3 + i, 19] = summarySheet.Cells[3 + i, 20]
= "X";
}
UpdateStatus("Export Device Info sheet ...");
/* Device Info Sheet */
sheet = (Excel.Worksheet)sheets["Device info"];
sheet.Cells[18, 12] = mEmiA.SA_RBW + "kHz";
sheet.Cells[19, 12] = mEmiA.SA_VBW + "kHz";
sheet.Cells[20, 12] = mEmiA.SA_Detector;
sheet.Cells[21, 12] = mEmiA.SA_Trace;
sheet.Cells[22, 12] = mEmiA.SA_Attenuation_Value + "dB";
sheet.Cells[23, 12] = mEmiA.SA_REF_LEVEL + "dBm";
List<EMIFileData> emis = new List<EMIFileData>();
emis.Add(mEmiA);
emis.Add(mEmiB);
List<double> startFrequencies = new List<double>();
startFrequencies.Add(startFrequencyA);
startFrequencies.Add(startFrequencyB);
List<double> endFrequencies = new List<double>();
endFrequencies.Add(endFrequencyA);
endFrequencies.Add(endFrequencyB);
List<double> azimuths = new List<double>();
azimuths.Add(mAzimuthA);
azimuths.Add(mAzimuthB);
List<List<BitMapInfo>> bitmapInfos = new List<List<BitMapInfo>>();
bitmapInfos.Add(mBmpInfosA);
bitmapInfos.Add(mBmpInfosB);
List<WatsEmiDataManager> dataManagers = new List<WatsEmiDataManager>();
dataManagers.Add(dataManagerA);
dataManagers.Add(dataManagerB);
for (int i = 0; i < 2; i++)
{
UpdateStatus("Export Information of EMI file '" + emis[i].Site_ID + "'");
sheet = (Excel.Worksheet)sheets["EMI" + (i+1).ToString()];
sheet.Name = emis[i].Site_ID;
sheet.Cells[2, 3] = emis[i].Site_ID;
sheet.Cells[2, 11] = emis[i].Site_ID;
sheet.Cells[3, 3] = emis[i].Site_Address;
sheet.Cells[4, 3] = Utility.ConvertLatitude(emis[i].Site_Latitude);
sheet.Cells[4, 11] = Utility.ConvertLongtitude(emis[i].Site_Longitude);
if (mLimitSetting.UseChannelPowerLimit)
sheet.Cells[5, 11] = mLimitSetting.ChannelPowerLimit.ToString();
else
sheet.Cells[5, 11] = "";
if (mLimitSetting.UseDeltaPowerLimit)
sheet.Cells[5, 15] = mLimitSetting.DeltaPowerLimit.ToString();
else
sheet.Cells[5, 15] = "";
sheet.Cells[6, 3] = Utility.ConvertToDate(emis[i].PA_TestTime);
sheet.Cells[6, 11] = emis[i].PA_UserName;
int channelIndex = 0;
Excel.Range range;
Dictionary<ChannelSetting, WatsEmiData> channelSamples = dataManagers[i].AllChannelSamples[azimuths[i]];
foreach (KeyValuePair<ChannelSetting, WatsEmiData> channelSamplePair in channelSamples)
{
if (mCancelExport)
return;
if (channelIndex > 0)
{
((Excel.Range)sheet.Rows[11, objOpt]).Copy(objOpt);
((Excel.Range)sheet.Cells[11 + channelIndex, 1]).EntireRow.Insert(objOpt, objOpt);
}
sheet.Cells[11 + channelIndex, 3] = channelSamplePair.Key.ChannelName;
sheet.Cells[11 + channelIndex, 4] = channelSamplePair.Key.CenterFreq;
sheet.Cells[11 + channelIndex, 5] = channelSamplePair.Key.BandWidth;
//channel pair
sheet.Cells[11 + channelIndex, 10] = channelSamplePair.Key.Pair.ChannelName;
sheet.Cells[11 + channelIndex, 11] = channelSamplePair.Key.Pair.CenterFreq;
sheet.Cells[11 + channelIndex, 12] = channelSamplePair.Key.Pair.BandWidth;
ChannelPower channelPower = new ChannelPower(emis[i].SA_RBW, channelSamplePair.Key, mLimitSetting, channelSamplePair.Value);
sheet.Cells[11 + channelIndex, 8] = channelPower.HPower;
sheet.Cells[11 + channelIndex, 15] = channelPower.HPairPower;
sheet.Cells[11 + channelIndex, 6] = channelPower.VPower;
sheet.Cells[11 + channelIndex, 13] = channelPower.VPairPower;
if (!channelPower.IsValidVPower)
{
sheet.Cells[11 + channelIndex, 7] = "X";
}
else
{
sheet.Cells[11 + channelIndex, 7] = "";
}
if (!channelPower.IsValidHPower)
{
sheet.Cells[11 + channelIndex, 9] = "X";
}
else
{
sheet.Cells[11 + channelIndex, 9] = "";
}
if (!channelPower.IsValidVPairPower)
{
sheet.Cells[11 + channelIndex, 14] = "X";
}
else
{
sheet.Cells[11 + channelIndex, 14] = "";
}
if (!channelPower.IsValidHPairPower)
{
sheet.Cells[11 + channelIndex, 16] = "X";
}
else
{
sheet.Cells[11 + channelIndex, 16] = "";
}
UpdateSummarySheet(summarySheet, channelSamplePair.Key, i == 0,
channelPower.IsValidVPower, channelPower.IsValidHPower,
channelPower.IsValidVPairPower, channelPower.IsValidHPairPower);
channelIndex++;
}
if (channelIndex > 1)
{
range = sheet.get_Range(sheet.Cells[11, 1], sheet.Cells[11 + channelIndex - 1, 1]);
range.ClearContents();
range.Merge(objOpt);
range = sheet.get_Range(sheet.Cells[11, 2], sheet.Cells[11 + channelIndex - 1, 2]);
range.ClearContents();
range.Merge(objOpt);
}
sheet.Cells[11, 1] = startFrequencies[i].ToString() + "-" + endFrequencies[i].ToString();
sheet.Cells[11, 2] = azimuths[i].ToString() + "\x00B0";
int pictureRows = bitmapInfos[i].Count;
if (pictureRows > 1)
{
for (int j = 0; j < pictureRows - 1; j++)
{
((Excel.Range)sheet.Rows[12 + channelIndex, objOpt]).Copy(objOpt);
((Excel.Range)sheet.Cells[13 + channelIndex + j, 1]).EntireRow.Insert(objOpt, objOpt);
}
}
for (int j = 0; j < pictureRows; j++)
{
UpdateStatus("Export sheet " + azimuths[i].ToString() + "\x00B0 vertical picture "
+ (j + 1).ToString() + " ...");
if (mCancelExport)
return;
sheet.Cells[12 + channelIndex + j, 1] = bitmapInfos[i][j].Title1;
range = (Excel.Range)sheet.Cells[12 + channelIndex + j, 1];
//range.Select();
float left = Convert.ToSingle(range.Left) + 15;
float top = Convert.ToSingle(range.Top) + 15;
float width = 310;
float height = 150;
sheet.Shapes.AddPicture(bitmapInfos[i][j].BmpFile1, Microsoft.Office.Core.MsoTriState.msoFalse,
Microsoft.Office.Core.MsoTriState.msoTrue, left, top, width, height);
UpdateStatus("Export sheet " + azimuths[i] + "\x00B0 horizontal picture "
+ (j + 1).ToString() + " ...");
if (mCancelExport)
return;
sheet.Cells[12 + channelIndex + j, 9] = bitmapInfos[i][j].Title2;
range = (Excel.Range)sheet.Cells[12 + channelIndex + j, 9];
//range.Select();
left = Convert.ToSingle(range.Left) + 15;
top = Convert.ToSingle(range.Top) + 15;
width = 310;
height = 150;
sheet.Shapes.AddPicture(bitmapInfos[i][j].BmpFile2, Microsoft.Office.Core.MsoTriState.msoFalse,
Microsoft.Office.Core.MsoTriState.msoTrue, left, top, width, height);
}
}
isReportSucceed = true;
UpdateStatus("Save export file, please wait ...");
workBook.Save();
UpdateStatus("Export succeed !");
}
catch (Exception ex)
{
MessageBox.Show("Create report failed !\r\nException: " + ex.Message);
}
finally
{
if (Oldci != null)
{
System.Threading.Thread.CurrentThread.CurrentCulture = Oldci;
}
if (app != null)
app.Quit();
ExcelAppKiller.Kill(app);
Utility.ReleaseCom(sheet);
Utility.ReleaseCom(sheets);
Utility.ReleaseCom(workBook);
Utility.ReleaseCom(app);
GC.Collect(System.GC.GetGeneration(sheet));
GC.Collect(System.GC.GetGeneration(sheets));
GC.Collect(System.GC.GetGeneration(workBook));
GC.Collect(System.GC.GetGeneration(app));
GC.Collect();
/*
ReleaseCom(sheet);
ReleaseCom(sheets);
ReleaseCom(workBook);
if (app != null)
app.Quit();
ExcelAppKiller.Kill(app);
ReleaseCom(app);
GC.Collect();
*/
}
if (isReportSucceed)
{
try
{
UpdateStatus("Open excel ...");
Process process = Process.Start("excel", "\"" + mExportFileName + "\"");
process.Close();
}
catch (System.Exception ex)
{
}
}
UpdateStatus("Finished");
}
);
mExportThread.Start();
}
private void ExportButton_Click(object sender, EventArgs e)
{
if (Utility.GetExcelVersion() < 0)
{
MessageBox.Show("Excel was not installed !");
return;
}
ExcelExportSettingForm exportSettingForm = new ExcelExportSettingForm();
if (exportSettingForm.ShowDialog() == DialogResult.Cancel)
return;
string reportTemplateFile;
SaveFileDialog saveFileDialog = new SaveFileDialog();
saveFileDialog.Title = "Select report file name";
Excel.XlFileFormat excelFormat;
if (exportSettingForm.ExportOffice2003)
{
reportTemplateFile = System.AppDomain.CurrentDomain.BaseDirectory
+ "ReportTemplate.xls";
//excelFormat = Excel.XlFileFormat.xlExcel8;
//excelFormat = Excel.XlFileFormat.xlWorkbookNormal;
saveFileDialog.Filter = "report file(*.xls)|*.xls";
}
else
{
reportTemplateFile = System.AppDomain.CurrentDomain.BaseDirectory
+ "ReportTemplate.xlsx";
//excelFormat = Excel.XlFileFormat.xlExcel12;
saveFileDialog.Filter = "report file(*.xlsx)|*.xlsx";
}
do
{
if (DialogResult.Cancel == saveFileDialog.ShowDialog())
return;
if (saveFileDialog.FileName.Equals(reportTemplateFile,
StringComparison.OrdinalIgnoreCase))
{
MessageBox.Show("Can't select report template file !");
continue;
}
try
{
File.Copy(reportTemplateFile, saveFileDialog.FileName, true);
}
catch (System.Exception ex)
{
MessageBox.Show("Can't save file " + saveFileDialog.FileName
+ "!\r\n" + "Select another file name for report.");
continue;
}
mExportFileName = saveFileDialog.FileName;
break;
} while (true);
Hide();
mExportStatusForm.Show();
mCancelExport = false;
//ExportButton.Enabled = false;
mExportThread = new Thread(delegate()
{
bool isReportSucceed = false;
string[] subBands = new string[2];
System.Globalization.CultureInfo Oldci = null;
Excel._Application app = null;
Excel.WorkbookClass workBook = null;
Excel.Sheets sheets = null;
Excel.Worksheet sheet = null;
Excel.Worksheet summarySheet;
try
{
Oldci = System.Threading.Thread.CurrentThread.CurrentCulture;
System.Threading.Thread.CurrentThread.CurrentCulture = new CultureInfo("en-us");
app = new Excel.Application();
app.DisplayAlerts = false;
object objOpt = System.Reflection.Missing.Value;
UpdateStatus("Initialize ...");
workBook = (Excel.WorkbookClass)app.Workbooks.Open(
mExportFileName, objOpt, false, objOpt, objOpt, objOpt, true,
objOpt, objOpt, true, objOpt, objOpt, objOpt, objOpt, objOpt);
sheets = workBook.Worksheets;
summarySheet = (Excel.Worksheet)sheets["Summary"];
summarySheet.Cells[1, 6] = mEmiFileData.Site_ID;
UpdateStatus("Export Cover sheet ...");
/* Cover Sheet */
sheet = (Excel.Worksheet)sheets["Cover"];
if (!string.IsNullOrEmpty(mEmiFileData.PI_ID))
sheet.Cells[15, 5] = mEmiFileData.PI_ID;
if (!string.IsNullOrEmpty(mEmiFileData.PA_UserName))
sheet.Cells[17, 5] = mEmiFileData.PA_UserName;
Dictionary<double, double> startFrequencys = new Dictionary<double, double>();
Dictionary<double, double> endFrequencys = new Dictionary<double, double>();
WatsEmiDataManager dataManager = new WatsEmiDataManager();
Dictionary<ChannelSetting, WatsEmiData> datas;
Dictionary<int, List<WatsEmiSample>> samples = null;
ChannelSetting curChannelSetting;
WatsEmiData curData;
foreach (DG_Type dataGroup in mEmiFileData.DataGroups)
{
if (mCancelExport)
return;
if (!startFrequencys.ContainsKey(dataGroup.DG_FB_Angle))
startFrequencys[dataGroup.DG_FB_Angle] = dataGroup.DG_FB_Start;
else if (dataGroup.DG_FB_Start < startFrequencys[dataGroup.DG_FB_Angle])
startFrequencys[dataGroup.DG_FB_Angle] = dataGroup.DG_FB_Start;
if (!endFrequencys.ContainsKey(dataGroup.DG_FB_Angle))
endFrequencys[dataGroup.DG_FB_Angle] = dataGroup.DG_FB_End;
else if (dataGroup.DG_FB_End > endFrequencys[dataGroup.DG_FB_Angle])
endFrequencys[dataGroup.DG_FB_Angle] = dataGroup.DG_FB_End;
if (!dataManager.AllChannelSamples.TryGetValue(dataGroup.DG_FB_Angle, out datas))
{
datas = new Dictionary<ChannelSetting, WatsEmiData>();
dataManager.AllChannelSamples.Add(dataGroup.DG_FB_Angle, datas);
}
if (!dataManager.AllSamples.TryGetValue(dataGroup.DG_FB_Angle, out samples))
{
samples = new Dictionary<int, List<WatsEmiSample>>();
samples[0] = new List<WatsEmiSample>();
samples[1] = new List<WatsEmiSample>();
dataManager.AllSamples.Add(dataGroup.DG_FB_Angle, samples);
}
foreach (DG_Data_Type data in dataGroup.DGDatas)
{
if (mCancelExport)
return;
if (dataGroup.DB_FB_AntennaPolarization == 0)
samples[0].Add(new WatsEmiSample(data.DG_DI_Freq, data.DG_DI_RSSI));
else //if (dataGroup.DB_FB_AntennaPolarization == 1)
samples[1].Add(new WatsEmiSample(data.DG_DI_Freq, data.DG_DI_RSSI));
curChannelSetting = null;
foreach (ChannelSetting channelSetting in mChannelSettings)
{
if (mCancelExport)
return;
if (data.DG_DI_Freq >= channelSetting.StartFreq
&& data.DG_DI_Freq <= channelSetting.EndFreq
|| data.DG_DI_Freq >= channelSetting.Pair.StartFreq
&& data.DG_DI_Freq <= channelSetting.Pair.EndFreq)
{
curChannelSetting = channelSetting;
break;
}
}
if (curChannelSetting == null)
continue;
if (!datas.TryGetValue(curChannelSetting, out curData))
{
curData = new WatsEmiData();
datas.Add(curChannelSetting, curData);
}
if (dataGroup.DB_FB_AntennaPolarization == 0)
{
if (data.DG_DI_Freq >= curChannelSetting.StartFreq
&& data.DG_DI_Freq <= curChannelSetting.EndFreq)
curData.mVSamples.Add(new WatsEmiSample(data.DG_DI_Freq, data.DG_DI_RSSI));
else
curData.mVPairSamples.Add(new WatsEmiSample(data.DG_DI_Freq, data.DG_DI_RSSI));
}
else
{
if (data.DG_DI_Freq >= curChannelSetting.StartFreq
&& data.DG_DI_Freq <= curChannelSetting.EndFreq)
curData.mHSamples.Add(new WatsEmiSample(data.DG_DI_Freq, data.DG_DI_RSSI));
else
curData.mHPairSamples.Add(new WatsEmiSample(data.DG_DI_Freq, data.DG_DI_RSSI));
}
}
}
for (int i = 1; i < dataManager.AllChannelSamples.Count; i++)
{
if (mCancelExport)
return;
((Excel.Range)summarySheet.Rows[3, objOpt]).Copy(objOpt);
((Excel.Range)summarySheet.Cells[3 + i, 1]).EntireRow.Insert(objOpt, objOpt);
}
UpdateStatus("Export Device Info sheet ...");
/* Device Info Sheet */
sheet = (Excel.Worksheet)sheets["Device info"];
sheet.Cells[18, 12] = mEmiFileData.SA_RBW + "kHz";
sheet.Cells[19, 12] = mEmiFileData.SA_VBW + "kHz";
sheet.Cells[20, 12] = mEmiFileData.SA_Detector;
sheet.Cells[21, 12] = mEmiFileData.SA_Trace;
sheet.Cells[22, 12] = mEmiFileData.SA_Attenuation_Value + "dB";
sheet.Cells[23, 12] = mEmiFileData.SA_REF_LEVEL + "dBm";
int channelIndex;
Excel.Range range;
int summaryAngleStartRowIndex = 3;
List<Excel.Range> boldBorderRanges = new List<Excel.Range>();
boldBorderRanges.Add(summarySheet.get_Range(summarySheet.Cells[1, 1],
summarySheet.Cells[2, 14]));
foreach (KeyValuePair<double, Dictionary<ChannelSetting, WatsEmiData>> pair in dataManager.AllChannelSamples)
{
UpdateStatus("Export sheet " + pair.Key.ToString() + "\x00B0" + " ...");
if (mCancelExport)
return;
((Excel.Worksheet)sheets["template"]).Copy(objOpt, workBook.ActiveSheet);
sheet = (Excel.Worksheet)workBook.ActiveSheet;
sheet.Name = pair.Key.ToString() + "\x00B0";
sheet.Cells[2, 3] = mEmiFileData.Site_ID;
sheet.Cells[2, 11] = mEmiFileData.Site_ID;
sheet.Cells[3, 3] = mEmiFileData.Site_Address;
sheet.Cells[4, 3] = Utility.ConvertLatitude(mEmiFileData.Site_Latitude);
sheet.Cells[4, 11] = Utility.ConvertLongtitude(mEmiFileData.Site_Longitude);
if (mLimitSetting.UseChannelPowerLimit)
sheet.Cells[5, 11] = mLimitSetting.ChannelPowerLimit.ToString();
else
sheet.Cells[5, 11] = "";
if (mLimitSetting.UseDeltaPowerLimit)
sheet.Cells[5, 15] = mLimitSetting.DeltaPowerLimit.ToString();
else
sheet.Cells[5, 15] = "";
sheet.Cells[6, 3] = Utility.ConvertToDate(mEmiFileData.PA_TestTime);
sheet.Cells[6, 11] = mEmiFileData.PA_UserName;
summarySheet.Cells[summaryAngleStartRowIndex, 1] = pair.Key.ToString() + "\x00B0";
channelIndex = 0;
foreach (KeyValuePair<ChannelSetting, WatsEmiData> dataPair in pair.Value)
{
if (mCancelExport)
return;
if (channelIndex > 0)
{
((Excel.Range)sheet.Rows[11, objOpt]).Copy(objOpt);
((Excel.Range)sheet.Cells[11 + channelIndex, 1]).EntireRow.Insert(objOpt, objOpt);
((Excel.Range)summarySheet.Rows[summaryAngleStartRowIndex, objOpt]).Copy(objOpt);
((Excel.Range)summarySheet.Cells[summaryAngleStartRowIndex + channelIndex, 1]).EntireRow.Insert(objOpt, objOpt);
}
sheet.Cells[11 + channelIndex, 3] = dataPair.Key.ChannelName;
sheet.Cells[11 + channelIndex, 4] = dataPair.Key.CenterFreq;
sheet.Cells[11 + channelIndex, 5] = dataPair.Key.BandWidth;
//channel pair
sheet.Cells[11 + channelIndex, 10] = dataPair.Key.Pair.ChannelName;
sheet.Cells[11 + channelIndex, 11] = dataPair.Key.Pair.CenterFreq;
sheet.Cells[11 + channelIndex, 12] = dataPair.Key.Pair.BandWidth;
summarySheet.Cells[summaryAngleStartRowIndex + channelIndex, 5] = dataPair.Key.ChannelName;
summarySheet.Cells[summaryAngleStartRowIndex + channelIndex, 6] = dataPair.Key.CenterFreq;
summarySheet.Cells[summaryAngleStartRowIndex + channelIndex, 9] = dataPair.Key.Pair.ChannelName;
summarySheet.Cells[summaryAngleStartRowIndex + channelIndex, 10] = dataPair.Key.Pair.CenterFreq;
ChannelPower channelPower = new ChannelPower(mEmiFileData.SA_RBW, dataPair.Key, mLimitSetting, dataPair.Value);
sheet.Cells[11 + channelIndex, 8] = channelPower.HPower;
sheet.Cells[11 + channelIndex, 15] = channelPower.HPairPower;
sheet.Cells[11 + channelIndex, 6] = channelPower.VPower;
sheet.Cells[11 + channelIndex, 13] = channelPower.VPairPower;
subBands[0] = "";
subBands[1] = "";
if (dataPair.Key.ODUSubBand.Length == 1)
subBands[0] = dataPair.Key.ODUSubBand;
else if (dataPair.Key.ODUSubBand.Length == 3)
{
subBands[0] = dataPair.Key.ODUSubBand.Substring(0, 1);
subBands[1] = dataPair.Key.ODUSubBand.Substring(2);
}
summarySheet.Cells[summaryAngleStartRowIndex + channelIndex, 3] = subBands[0];
summarySheet.Cells[summaryAngleStartRowIndex + channelIndex, 4] = subBands[1];
if (!channelPower.IsValidVPower || !channelPower.IsValidVPairPower)
summarySheet.Cells[summaryAngleStartRowIndex + channelIndex, 13] = "X";
else
summarySheet.Cells[summaryAngleStartRowIndex + channelIndex, 13] = "";
if (!channelPower.IsValidHPower || !channelPower.IsValidHPairPower)
summarySheet.Cells[summaryAngleStartRowIndex + channelIndex, 14] = "X";
else
summarySheet.Cells[summaryAngleStartRowIndex + channelIndex, 14] = "";
if (!channelPower.IsValidVPower)
{
summarySheet.Cells[summaryAngleStartRowIndex + channelIndex, 7] = "X";
sheet.Cells[11 + channelIndex, 7] = "X";
}
else
{
summarySheet.Cells[summaryAngleStartRowIndex + channelIndex, 7] = "";
sheet.Cells[11 + channelIndex, 7] = "";
}
if (!channelPower.IsValidHPower)
{
summarySheet.Cells[summaryAngleStartRowIndex + channelIndex, 8] = "X";
sheet.Cells[11 + channelIndex, 9] = "X";
}
else
{
summarySheet.Cells[summaryAngleStartRowIndex + channelIndex, 8] = "";
sheet.Cells[11 + channelIndex, 9] = "";
}
if (!channelPower.IsValidVPairPower)
{
summarySheet.Cells[summaryAngleStartRowIndex + channelIndex, 11] = "X";
sheet.Cells[11 + channelIndex, 14] = "X";
}
else
{
summarySheet.Cells[summaryAngleStartRowIndex + channelIndex, 11] = "";
sheet.Cells[11 + channelIndex, 14] = "";
}
if (!channelPower.IsValidHPairPower)
{
summarySheet.Cells[summaryAngleStartRowIndex + channelIndex, 12] = "X";
sheet.Cells[11 + channelIndex, 16] = "X";
}
else
{
summarySheet.Cells[summaryAngleStartRowIndex + channelIndex, 12] = "";
sheet.Cells[11 + channelIndex, 16] = "";
}
channelIndex++;
}
if (channelIndex > 1)
{
range = sheet.get_Range(sheet.Cells[11, 1], sheet.Cells[11 + channelIndex - 1, 1]);
range.ClearContents();
range.Merge(objOpt);
range = sheet.get_Range(sheet.Cells[11, 2], sheet.Cells[11 + channelIndex - 1, 2]);
range.ClearContents();
range.Merge(objOpt);
range = summarySheet.get_Range(summarySheet.Cells[summaryAngleStartRowIndex, 1],
summarySheet.Cells[summaryAngleStartRowIndex + channelIndex - 1, 1]);
range.Merge(objOpt);
}
boldBorderRanges.Add(summarySheet.get_Range(summarySheet.Cells[summaryAngleStartRowIndex, 1],
summarySheet.Cells[summaryAngleStartRowIndex + channelIndex - 1, 14]));
summaryAngleStartRowIndex += channelIndex;
sheet.Cells[11, 1] = startFrequencys[pair.Key].ToString()
+ "-" + endFrequencys[pair.Key].ToString();
sheet.Cells[11, 2] = pair.Key.ToString() + "\x00B0";
List<BitMapInfo> bitmapInfos = mBitmaps[pair.Key];
int pictureRows = bitmapInfos.Count;
if (pictureRows > 1)
{
for (int i = 0; i < pictureRows - 1; i++)
{
((Excel.Range)sheet.Rows[12 + channelIndex, objOpt]).Copy(objOpt);
((Excel.Range)sheet.Cells[13 + channelIndex + i, 1]).EntireRow.Insert(objOpt, objOpt);
}
}
for (int i = 0; i < pictureRows; i++)
{
UpdateStatus("Export sheet " + pair.Key.ToString() + "\x00B0 vertical picture "
+ (i + 1).ToString() + " ...");
if (mCancelExport)
return;
sheet.Cells[12 + channelIndex + i, 1] = bitmapInfos[i].Title1;
range = (Excel.Range)sheet.Cells[12 + channelIndex + i, 1];
range.Select();
float left = Convert.ToSingle(range.Left) + 15;
float top = Convert.ToSingle(range.Top) + 15;
float width = 310;
float height = 150;
sheet.Shapes.AddPicture(bitmapInfos[i].BmpFile1, Microsoft.Office.Core.MsoTriState.msoFalse,
Microsoft.Office.Core.MsoTriState.msoTrue, left, top, width, height);
UpdateStatus("Export sheet " + pair.Key.ToString() + "\x00B0 horizontal picture "
+ (i + 1).ToString() + " ...");
if (mCancelExport)
return;
sheet.Cells[12 + channelIndex + i, 9] = bitmapInfos[i].Title2;
range = (Excel.Range)sheet.Cells[12 + channelIndex + i, 9];
range.Select();
left = Convert.ToSingle(range.Left) + 15;
top = Convert.ToSingle(range.Top) + 15;
width = 310;
height = 150;
sheet.Shapes.AddPicture(bitmapInfos[i].BmpFile2, Microsoft.Office.Core.MsoTriState.msoFalse,
Microsoft.Office.Core.MsoTriState.msoTrue, left, top, width, height);
}
}
foreach (Excel.Range boldBorderRange in boldBorderRanges)
{
boldBorderRange.Borders.get_Item(Excel.XlBordersIndex.xlEdgeBottom).Weight = Excel.XlBorderWeight.xlMedium;
boldBorderRange.Borders.get_Item(Excel.XlBordersIndex.xlEdgeTop).Weight = Excel.XlBorderWeight.xlMedium;
boldBorderRange.Borders.get_Item(Excel.XlBordersIndex.xlEdgeLeft).Weight = Excel.XlBorderWeight.xlMedium;
boldBorderRange.Borders.get_Item(Excel.XlBordersIndex.xlEdgeRight).Weight = Excel.XlBorderWeight.xlMedium;
}
UpdateStatus("Delete template sheet ...");
((Excel.Worksheet)sheets["template"]).Delete();
isReportSucceed = true;
UpdateStatus("Save export file, please wait ...");
workBook.Save();
/*
workBook.SaveAs(mExportFileName, Excel.XlFileFormat.xlWorkbookNormal,
Type.Missing, Type.Missing, Type.Missing, Type.Missing,
Excel.XlSaveAsAccessMode.xlNoChange, Type.Missing,
Type.Missing, Type.Missing, Type.Missing, Type.Missing);
*/
UpdateStatus("Export succeed !");
}
catch (Exception ex)
{
MessageBox.Show("Create report failed !\r\nException: " + ex.Message);
}
finally
{
if (Oldci != null)
{
System.Threading.Thread.CurrentThread.CurrentCulture = Oldci;
}
if (app != null)
app.Quit();
ExcelAppKiller.Kill(app);
Utility.ReleaseCom(sheet);
Utility.ReleaseCom(sheets);
Utility.ReleaseCom(workBook);
Utility.ReleaseCom(app);
GC.Collect(System.GC.GetGeneration(sheet));
GC.Collect(System.GC.GetGeneration(sheets));
GC.Collect(System.GC.GetGeneration(workBook));
GC.Collect(System.GC.GetGeneration(app));
GC.Collect();
/*
ReleaseCom(sheet);
ReleaseCom(sheets);
ReleaseCom(workBook);
if (app != null)
app.Quit();
ExcelAppKiller.Kill(app);
ReleaseCom(app);
GC.Collect();
*/
}
if (isReportSucceed)
{
try
{
UpdateStatus("Open excel ...");
Process process = Process.Start("excel", "\"" + mExportFileName + "\"");
process.Close();
}
catch (System.Exception ex)
{
}
}
UpdateStatus("Finished");
}
);
mExportThread.Start();
}
