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(); }