public void MoveSheet(long SheetIndex = -1)
{
if (CheckSheetIndex(SheetIndex))
{
SheetIndex = SheetTotal();
}
CurrSheet.Move(CurrSheets[SheetIndex]);
}
public static void CreateWorkbook(string suf, int MaxColumnLength, string dir, int MinG, int MaxG, int MinR, int MaxR)
{
var dirs = FileReader.DigForFiles(dir, suf);
// creating Excel Application
Microsoft.Office.Interop.Excel._Application app = new Microsoft.Office.Interop.Excel.Application();
// creating new WorkBook within Excel application
Microsoft.Office.Interop.Excel._Workbook workbook = app.Workbooks.Add(Type.Missing);
// creating new Excelsheet in workbook
Microsoft.Office.Interop.Excel._Worksheet worksheet = null;
Sheets xlSheets = null;
xlSheets = workbook.Sheets as Sheets;
// see the excel sheet behind the program
app.Visible = false;
//Select the sheet
worksheet = workbook.Worksheets[1];
//Rename the sheet
worksheet.Name = "Summary";
string[] names = GetNames(dirs);
Console.WriteLine("Processing Files:");
for (int i = 0; i < names.Length; i++)
{
Console.WriteLine(names[i]);
Microsoft.Office.Interop.Excel._Workbook csvWorkbook = app.Workbooks.Open(dirs[i]);
Microsoft.Office.Interop.Excel._Worksheet worksheetCSV = ((Microsoft.Office.Interop.Excel._Worksheet)csvWorkbook.Worksheets[1]);
worksheetCSV.Copy(xlSheets[1]);
xlSheets[1].Name = names[i];
((_Worksheet)xlSheets[1]).Cells[1, 24] = "Mean_Cell - Mean_Noise";
for (int row = 2; row < MaxColumnLength; row++)
{
((_Worksheet)xlSheets[1]).Cells[row, 24] = "=E" + row + "-U" + row;
}
((_Worksheet)xlSheets[1]).Cells[1, 25] = "Max_Cell";
((_Worksheet)xlSheets[1]).Cells[2, 25] = "=MAX(X2:X" + MaxColumnLength + ")";
// Exit from the application
csvWorkbook.Close();
}
worksheet.Move(Before: workbook.Sheets[1]);
Console.WriteLine("Preparing the summary...");
//worksheet.Cells[row, column] = "=cell57_Q2_Ch0_Green_Results!E2";
int currentColumn = 1;
int currentRow = 1;
int increment = names.Length;
for (currentRow = 1; currentRow < MaxColumnLength; currentRow++)
{
worksheet.Cells[currentRow, currentColumn] = "=" + names[0] + "!C" + currentRow;
}
currentRow = 1;
for (int i = 0; i < names.Length; i++)
{
//Area cell
currentColumn = i + 2;
worksheet.Cells[currentRow, currentColumn] = "Area_Cell_" + names[i];
//Mean cell
currentColumn += increment;
worksheet.Cells[currentRow, currentColumn] = "Mean_Cell_" + names[i];
//Area noise
currentColumn += increment;
worksheet.Cells[currentRow, currentColumn] = "Area_Noise_" + names[i];
//Mean noise
currentColumn += increment;
worksheet.Cells[currentRow, currentColumn] = "Mean_Noise_" + names[i];
//Area spots
currentColumn += increment;
worksheet.Cells[currentRow, currentColumn] = "Area_Spot_" + names[i];
//Mean spots
currentColumn += increment;
worksheet.Cells[currentRow, currentColumn] = "Mean_Spot_" + names[i];
//Mean-Noise cell
currentColumn += increment;
worksheet.Cells[currentRow, currentColumn] = "Mean-Noise_Cell_" + names[i];
//Max cell
currentColumn += increment;
worksheet.Cells[currentRow, currentColumn] = "Max_Cell_" + names[i];
worksheet.Cells[currentRow + 1, currentColumn] = "=" + names[i] + "!Y2";
//Max-Results
currentColumn += increment;
worksheet.Cells[currentRow, currentColumn] = "Max-Results_" + names[i];
//Results
currentColumn += increment;
worksheet.Cells[currentRow, currentColumn] = "Results_" + names[i];
//ResultsTo0
currentColumn += increment;
worksheet.Cells[currentRow, currentColumn] = "To0_Results_" + names[i];
//ResultsTo1
currentColumn += increment;
worksheet.Cells[currentRow, currentColumn] = "nTo0_Results_" + names[i];
}
//Mean result
worksheet.Cells[currentRow, currentColumn + 1] = "nAvgMob";
worksheet.Cells[currentRow, currentColumn + 2] = "nnAvgMob";
worksheet.Cells[currentRow, currentColumn + 3] = "nStDevMob";
worksheet.Cells[currentRow, currentColumn + 4] = "AvgMob";
worksheet.Cells[currentRow, currentColumn + 5] = "StDevMob";
int spotSignal, MaxCell, CellMinusNoise;
for (currentRow = 2; currentRow < MaxColumnLength; currentRow++)
{
for (int i = 0; i < names.Length; i++)
{
//Area cell
currentColumn = i + 2;
worksheet.Cells[currentRow, currentColumn] = "=" + names[i] + "!D" + currentRow;
//Mean cell
currentColumn += increment;
worksheet.Cells[currentRow, currentColumn] = "=" + names[i] + "!E" + currentRow;
//Area noise
currentColumn += increment;
worksheet.Cells[currentRow, currentColumn] = "=" + names[i] + "!T" + currentRow;
//Mean noise
currentColumn += increment;
worksheet.Cells[currentRow, currentColumn] = "=" + names[i] + "!U" + currentRow;
//Area spots
currentColumn += increment;
worksheet.Cells[currentRow, currentColumn] =
"=AVERAGE(" + names[i] + "!H" + currentRow + ","
+ names[i] + "!L" + currentRow + ","
+ names[i] + "!P" + currentRow + ")";
//Mean spots
currentColumn += increment;
worksheet.Cells[currentRow, currentColumn] =
"=AVERAGE(" + names[i] + "!I" + currentRow + ","
+ names[i] + "!M" + currentRow + ","
+ names[i] + "!Q" + currentRow + ")";
spotSignal = currentColumn;
//Cell-Noise mean
currentColumn += increment;
worksheet.Cells[currentRow, currentColumn] = "=" + names[i] + "!X" + currentRow;
CellMinusNoise = currentColumn;
//Max Cell
currentColumn += increment;
MaxCell = currentColumn;
//Min-Results
currentColumn += increment;
worksheet.Cells[currentRow, currentColumn] = "=" + ColumnLabel(MaxCell) + "2-(" + ColumnLabel(MaxCell) + "2/" +
ColumnLabel(CellMinusNoise) + currentRow + ")*(" + ColumnLabel(spotSignal) + currentRow + "-" +
names[i] + "!U" + currentRow +
")";
//Results
currentColumn += increment;
worksheet.Cells[currentRow, currentColumn] = "=(" + ColumnLabel(MaxCell) + "2/" +
ColumnLabel(CellMinusNoise) + currentRow + ")*(" + ColumnLabel(spotSignal) + currentRow + "-" +
names[i] + "!U" + currentRow +
")";
}
}
//calculations
currentColumn++;
int MaxMinResults = 2 + 8 * increment;
int ResultsTo0 = currentColumn;
for (int i = 0; i < names.Length; i++)
{
for (currentRow = 2; currentRow < MaxColumnLength; currentRow++)
{
if (suf == "Green")
{
worksheet.Cells[currentRow, currentColumn] = "=(" +
ColumnLabel(MaxMinResults) + currentRow +
"-AVERAGE(" + ColumnLabel(MaxMinResults) + MinG + ":" + ColumnLabel(MaxMinResults) + MaxG + ")" +
")";
}
else
{
worksheet.Cells[currentRow, currentColumn] = "=(" +
ColumnLabel(MaxMinResults) + currentRow +
"-AVERAGE(" + ColumnLabel(MaxMinResults) + MinR + ":" + ColumnLabel(MaxMinResults) + MaxR + ")" +
")";
}
}
currentColumn++;
MaxMinResults++;
}
//NormTo1
MaxMinResults = currentColumn - increment;
for (int i = 0; i < names.Length; i++)
{
for (currentRow = 2; currentRow < MaxColumnLength; currentRow++)
{
worksheet.Cells[currentRow, currentColumn] = "=(" +
ColumnLabel(MaxMinResults) + currentRow +
"/MAX(" + ColumnLabel(MaxMinResults) + "2" + ":" + ColumnLabel(MaxMinResults) + MaxColumnLength + ")" +
")";
}
currentColumn++;
MaxMinResults++;
}
{
int MinInd = currentColumn - increment;
int MaxInd = currentColumn - 1;
int Mean = currentColumn;
int nMean = currentColumn + 1;
int StDev = currentColumn + 2;
for (currentRow = 2; currentRow < MaxColumnLength; currentRow++)
{
worksheet.Cells[currentRow, Mean] = "=" +
"AVERAGE(" + ColumnLabel(MinInd) + currentRow + ":" + ColumnLabel(MaxInd) + currentRow + ")";
worksheet.Cells[currentRow, StDev] = "=" +
"STDEV.S(" + ColumnLabel(MinInd) + currentRow + ":" + ColumnLabel(MaxInd) + currentRow + ")";
}
for (currentRow = 2; currentRow < MaxColumnLength; currentRow++)
{
worksheet.Cells[currentRow, nMean] = "=(" +
ColumnLabel(Mean) + currentRow +
"/MAX(" + ColumnLabel(Mean) + "2:" + ColumnLabel(Mean) + MaxColumnLength + ")" +
")";
}
MinInd -= increment;
MaxInd -= increment;
Mean = currentColumn + 3;
StDev = currentColumn + 4;
for (currentRow = 2; currentRow < MaxColumnLength; currentRow++)
{
worksheet.Cells[currentRow, Mean] = "=" +
"AVERAGE(" + ColumnLabel(MinInd) + currentRow + ":" + ColumnLabel(MaxInd) + currentRow + ")";
worksheet.Cells[currentRow, StDev] = "=" +
"STDEV.S(" + ColumnLabel(MinInd) + currentRow + ":" + ColumnLabel(MaxInd) + currentRow + ")";
}
}
string name = dir.Substring(dir.LastIndexOf("\\") + 1, dir.Length - dir.LastIndexOf("\\") - 1);
name = dir + "\\" + "Res_" + suf + ".xlsx";
app.Visible = true;
workbook.SaveAs(name, Type.Missing,
Type.Missing, Type.Missing, Type.Missing, Type.Missing, XlSaveAsAccessMode.xlNoChange,
Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
Console.WriteLine("Results saved to :\n" + name);
app.Quit();//Check is working
Console.WriteLine("Done!");
}
/// <summary>
/// 方法名称： Write
/// 内容描述： 写入数据，包括（列名，数据集）
/// 实现流程：
/// 作 者： 林付国
/// 日 期： 2006-5-19 10:26:31
/// </summary>
/// <returns></returns>
public bool Write()
{
System.Data.DataTable dt = null;
Excel.Range m_objRange;
bool bolRet = false;
string strCell = "A1";
dt = this.DataSource.Tables[0];
Object[,] AData;
int irowcount = 0;
int iSheetCount = wb.Sheets.Count;
app.ActiveCell.SpecialCells(Excel.XlCellType.xlCellTypeLastCell, Type.Missing).Select();
irowcount = app.ActiveCell.Row;
// 写入检查
this.WriteException(dt);
try
{
switch (this.WriteType)
{
case EnumType.WriteType.None:
strCell = "A1";
AData = new object[dt.Rows.Count, dt.Columns.Count];
for (int i = 0; i <= dt.Columns.Count - 1; i++)
{
AData[0, i] = dt.Columns[i].ColumnName;
}
ConvertArrayDataTable(AData, dt, 0, dt.Rows.Count - 1, 1);
//ws = (excel.Worksheet)(wb.Sheets.Add(objOpt,objOpt,objOpt,objOpt)) ;
// 改变Sheet表名称
if (this.SheetName != "")
{
ws.Name = this.SheetName;;
}
m_objRange = ws.get_Range(strCell, Type.Missing);
m_objRange = m_objRange.get_Resize(dt.Rows.Count, dt.Columns.Count);
m_objRange.Value2 = AData;
#region // 删除默认Sheet表
//ws.Activate();
//ws.Move(wb.Sheets[1],objOpt);
// while(iSheetCount > 0)
// {
// Excel._Worksheet tempXSheet = (Excel._Worksheet) (wb.Worksheets[iSheetCount]) ;
// tempXSheet.Delete() ;
// System.Runtime.InteropServices.Marshal.ReleaseComObject(tempXSheet) ;
// tempXSheet=null ;
// iSheetCount--;
// }
#endregion
break;
case EnumType.WriteType.Insert:
{
// strCell = "A1";
// AData = new object[dt.Rows.Count,dt.Columns.Count];
// for(int i = 0;i <=dt.Columns.Count - 1;i++)
// {
// AData[0,i] = dt.Columns[i].ColumnName;
// }
// ConvertArrayDataTable(AData,dt,0,dt.Rows.Count - 1,1);
// // 改变Sheet表名称
// if(this.SheetName != "")
// {
// ws.Name = this.SheetName;;
// }
// m_objRange = ws.get_Range(strCell, Type.Missing);
// //m_objRange.EntireRow.Insert();
// m_objRange = m_objRange.get_Resize(dt.Rows.Count,dt.Columns.Count).EntireRow;
// m_objRange.Insert();
// //m_objRange.Value2 = AData;
break;
}
case EnumType.WriteType.Append:
string strSheetName;
if (this.SheetName != "")
{
ws.Name = this.SheetName;;
}
strSheetName = ws.Name.ToString();
strCell = "A" + Convert.ToString(irowcount + 1);
if ((dt.Rows.Count + irowcount) <= iMaxRow)
{
AData = new object[dt.Rows.Count, dt.Columns.Count];
ConvertArrayDataTable(AData, dt, 0, dt.Rows.Count - 1, 0);
m_objRange = ws.get_Range(strCell, Type.Missing);
m_objRange = m_objRange.get_Resize(dt.Rows.Count, dt.Columns.Count);
m_objRange.Value2 = AData;
//m_objRange.EntireRow.Insert();
}
else
{
int iFristInsertRow, iSeceondInRow;
iFristInsertRow = iMaxRow - irowcount;
AData = new object[iFristInsertRow, dt.Columns.Count];
ConvertArrayDataTable(AData, dt, 0, iFristInsertRow, 0);
m_objRange = ws.get_Range(strCell, Type.Missing);
m_objRange = m_objRange.get_Resize(iFristInsertRow, dt.Columns.Count);
m_objRange.Value2 = AData;
Excel._Worksheet xSheet = null;
xSheet = (Excel._Worksheet)(wb.Sheets.Add(objOpt, objOpt, objOpt, objOpt));
xSheet.Name = strSheetName + "(2)";
iSeceondInRow = dt.Rows.Count - iFristInsertRow;
AData = new object[iSeceondInRow, dt.Columns.Count];
ConvertArrayDataTable(AData, dt, 0, iSeceondInRow, 0);
m_objRange = xSheet.get_Range("A1", Type.Missing);
m_objRange = m_objRange.get_Resize(iSeceondInRow, dt.Columns.Count);
m_objRange.Value2 = AData;
xSheet.Activate();
xSheet.Move(objOpt, wb.Sheets[ws.Index]);
}
break;
default:
strCell = "A1";
break;
}
this.Save();
bolRet = true;
}
catch
{
throw new Exception("写入Excel文件出错，请检查！");
}
return(bolRet);
}
/// <summary>
/// 方法名称： ReWrite
/// 内容描述： 重写数据
/// 作 者： 林付国
/// 日 期： 2006-5-25 11:22:30
/// </summary>
/// <param name="iRow">重写开始行数</param>
/// <returns></returns>
public bool ReWrite(int iRow)
{
System.Data.DataTable dt = null;
Excel.Range m_objRange;
bool bolRet = false;
string strCell = "A1";
dt = this.DataSource.Tables[0];
Object[,] AData;
int iSheetCount = wb.Sheets.Count;
if (!CellCheck(iRow, 1))
{
bolRet = false;
throw new Exception("写入单元格超出范围，请检查！");
}
// 写入检查
this.WriteException(dt);
string strSheetName;
if (this.SheetName != "")
{
ws.Name = this.SheetName;;
}
strSheetName = ws.Name.ToString();
strCell = "A" + Convert.ToString(iRow);
try
{
// 插入的测试
// if(this.WriteType == EnumType.WriteType.Insert)
// {
// AData = new object[dt.Rows.Count,dt.Columns.Count];
// ConvertArrayDataTable(AData,dt,0,dt.Rows.Count - 1,0);
//
// m_objRange = ws.get_Range(strCell, Type.Missing);
// //m_objRange = m_objRange.get_Resize(dt.Rows.Count,dt.Columns.Count);
// //m_objRange.EntireRow.Select();
// for(int i = 0 ; i <= dt.Rows.Count ; i++)
// {
// m_objRange.EntireRow.Insert();
// }
// //m_objRange.Value2 = AData;
// }
// else
// {
// 重写部分
if ((dt.Rows.Count + iRow) <= iMaxRow)
{
AData = new object[dt.Rows.Count, dt.Columns.Count];
ConvertArrayDataTable(AData, dt, 0, dt.Rows.Count - 1, 0);
m_objRange = ws.get_Range(strCell, Type.Missing);
m_objRange = m_objRange.get_Resize(dt.Rows.Count, dt.Columns.Count);
m_objRange.Value2 = AData;
}
else
{
int iFristInsertRow, iSeceondInRow;
iFristInsertRow = iMaxRow - iRow;
AData = new object[iFristInsertRow, dt.Columns.Count];
ConvertArrayDataTable(AData, dt, 0, iFristInsertRow, 0);
m_objRange = ws.get_Range(strCell, Type.Missing);
m_objRange = m_objRange.get_Resize(iFristInsertRow, dt.Columns.Count);
m_objRange.Value2 = AData;
Excel._Worksheet xSheet = null;
xSheet = (Excel._Worksheet)(wb.Sheets.Add(objOpt, objOpt, objOpt, objOpt));
xSheet.Name = strSheetName + "(2)";
iSeceondInRow = dt.Rows.Count - iFristInsertRow;
AData = new object[iSeceondInRow, dt.Columns.Count];
ConvertArrayDataTable(AData, dt, 0, iSeceondInRow, 0);
m_objRange = xSheet.get_Range("A1", Type.Missing);
m_objRange = m_objRange.get_Resize(iSeceondInRow, dt.Columns.Count);
m_objRange.Value2 = AData;
xSheet.Activate();
xSheet.Move(objOpt, wb.Sheets[ws.Index]);
}
//}
this.Save();
bolRet = true;
}
catch
{
throw new Exception("写入Excel文件出错，请检查！");
}
return(bolRet);
}
public void AddData(string reportFile, string[,] results, double time)
{
var excel = new Excel.Application {
DisplayAlerts = false
};
// Open report file
excel.Workbooks.Open(reportFile);
// Open the report Excel sheet
Excel._Worksheet workSheet = (Excel.Worksheet)excel.ActiveSheet;
// BLOCK EDIT
var colomn = 0;
var sheetNames = new List <string>();
var arrayLength = results.GetLength(0) - 1;
for (int i = 0; i <= arrayLength; i++)
{
var arraySecondLength = results.GetLength(1) - 1;
for (int j = 0; j <= arraySecondLength; j++)
{
if (j == 0 && results[i, j] != null)
{
// Set the sheetname based on metric from last /
var sheetName = results[i, j];
if (sheetName.Contains('\\'))
{
if (sheetName.Contains("(vmhba"))
{
sheetName = sheetName.Substring(sheetName.LastIndexOf('('));
}
else if (sheetName.Contains("vmnic"))
{
sheetName = sheetName.Substring(sheetName.LastIndexOf(":") + 1);
sheetName = "(" + sheetName;
}
else
{
sheetName = sheetName.Substring(sheetName.LastIndexOf('\\') + 1);
}
}
else if (sheetName.Contains(':'))
{
sheetName = sheetName.Substring(sheetName.LastIndexOf(':') + 1);
}
char[] charsToTrim = { '\\', '/', '?', '*', '[', ']', ':' };
// Replace each carh for a <space>
foreach (var trimChar in charsToTrim)
{
sheetName = sheetName.Replace(trimChar, ' ');
}
var maxLength = 30;
if (sheetName.Length > maxLength)
{
//var maxBeginPoint = sheetName.Length - maxLength;
sheetName = sheetName.Substring(0, maxLength);
}
if (!sheetNames.Exists(s => s.Equals(sheetName)))
{
sheetNames.Add(sheetName);
}
else
{
var sheetIndex = sheetNames.FindIndex(s => s.Equals(sheetName));
sheetName = sheetIndex.ToString() + sheetName;
sheetNames.Add(sheetName);
}
// Add new sheet
workSheet = excel.Application.Worksheets.Add();
workSheet.Move(After: excel.Sheets[excel.Sheets.Count]);
workSheet.Name = sheetName;
// Add time range to excel sheet
TimeSpan timeStamp = new TimeSpan(0, 0, 0);
var rowLength = results.GetLength(1);
for (int k = 1; k <= rowLength; k++)
{
if (k == 1)
{
workSheet.Cells[k, 1] = "Time";
}
else
{
workSheet.Cells[k, 1] = timeStamp.ToString();
timeStamp = timeStamp.Add(TimeSpan.FromSeconds(time));
}
}
colomn = 2;
}
var row = j + 1;
// Add results to excell sheet
if (j == 0)
{
workSheet.Cells[row, colomn] = "Results";
}
else if (results[i, j] != null)
{
workSheet.Cells[row, colomn] = results[i, j];
}
}
}
var error = new ErrorHandler();
try
{
// Save excel sheet
workSheet.SaveAs(reportFile);
}
catch (Exception)
{
excel.Quit();
error.Exit(93);
}
// Close sheet
excel.Quit();
}
