C# Workbook.Close方法代码示例

 static void CloseWorkBook(Workbook workbook)
 {
     try
     {
         workbook.Close();
     }
     catch { /* Do Nothing*/ }
 }

        public void CreateCustomerData(ref ISelenium browser)
        {
            try
            {
                var admincommonObj = new AdminSuite.Common();
                string user, password = "12345678";

                _testDataFilePath = _currentDirPath.Parent.FullName;
                _app = new Application();
                //var testDataObj = new TestData();
                _app.DisplayAlerts = false;
                _workBook = _app.Workbooks.Open(_testDataFilePath + "\\TestData\\TestData.xls", 0, false, 5, "", "", false, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "", true, false, 0, true, false, false);
                _workSheets = _workBook.Worksheets;
                var _userSheet = (Microsoft.Office.Interop.Excel.Worksheet)_workSheets.get_Item("Users");
                Random rnd = new Random();
                int rndNumber;

                for (int i = 0; i < 10; i++)
                {
                    rndNumber = rnd.Next(10000);
                    user = "AutoUser" + rndNumber;
                    admincommonObj.CreateCustomer(browser, user, password);
                    _userSheet.Cells[i + 2, "B"] = user;
                    _userSheet.Cells[i +2, "C"] = password;
                    admincommonObj.PerformManualAdjustment(browser, user, "Test Accounts", "100", "Yes", "Test Accounts", "", "", "", "Today");
                    _workBook.Save();
                }
            }
            catch (Exception Ex)
            {
                Framework.BaseTest.CaptureScreenshot(browser, "");
                Console.WriteLine(Ex.StackTrace);
                Framework.BaseTest.Fail(Ex.Message);
            }
            finally
            {
                _workBook.Save();
                _workBook.Close();
                _workSheet = null;
                _workBook = null;
                _app.Quit();
            }
        }

 public bool SaveAs(string fileName, System.Data.DataTable dataTable)
 {
     if (dataTable == null || dataTable.Rows.Count == 0) return false;
     if (xlApp == null) return false;
     _workbook = xlApp.Workbooks.Add(Type.Missing);
     _worksheet = (Worksheet)_workbook.Worksheets[1];
     for (var i = 0; i < dataTable.Columns.Count; i++)
     {
         _worksheet.Cells[1, i + 1] = dataTable.Columns[i].ColumnName;
         //range = (Range)worksheet.Cells[1, i + 1];
         //range.Interior.ColorIndex = 15;
         //range.Font.Bold = true;
     }
     _workbook.SaveAs(fileName,XlFileFormat.xlWorkbookNormal, "", "", false, false, XlSaveAsAccessMode.xlExclusive,XlPlatform.xlWindows, false, false, false, false);
     _workbook.Close(true, fileName, false);
     xlApp.Quit();
     _connectionString = "provider=Microsoft.Jet.OLEDB.4.0;Data Source='" + fileName + "';Extended Properties=Excel 8.0;"; // строка подключения
     using (var excelConn = new System.Data.OleDb.OleDbConnection(_connectionString)) // используем OleDb
     {
         var queryValues = String.Empty;
         excelConn.Open();
         for (var i = 0; i < dataTable.Rows.Count; i++)
         {
             for (var c = 0; c < dataTable.Columns.Count; c++)
             {
                 queryValues += dataTable.Rows[i][c] + "','";
             }
             queryValues = queryValues.Substring(0, queryValues.Length - 3);
             var writeCmd = new System.Data.OleDb.OleDbCommand("INSERT INTO [Лист1$] VALUES ('" + queryValues + "')", excelConn);
             writeCmd.ExecuteNonQuery(); // вставляем данные в лист1 файла - filename
             writeCmd.Dispose();
             queryValues = String.Empty;
         }
         excelConn.Close();
     }
     return System.IO.File.Exists(fileName);
 }

        private void SaveXLS(Patient patient)
        {
            if (patient.results.Count > 0)
            {
                try
                {
                    // Открываем приложение
                    application = new Microsoft.Office.Interop.Excel.Application
                    {
                        DisplayAlerts = false
                    };

                    // Файл шаблона
                    const string template = "resources/template.xlt";

                    // Открываем книгу
                    workBook = application.Workbooks.Open(Path.Combine(Environment.CurrentDirectory, template));

                    // Получаем активную таблицу
                    worksheet = workBook.ActiveSheet as Worksheet;

                    // Записываем данные
                    //worksheet.Range["A2"].Value = DateTime.Now;
                    worksheet.Range["A1"].Value = "Название сеанса";
                    worksheet.Range["B1"].Value = "Дата";
                    worksheet.Range["C1"].Value = "Продолжительность";
                    worksheet.Range["D1"].Value = "Горизонтальное отклонение";
                    worksheet.Range["E1"].Value = "Горизонтальное отклонение (динамика)";
                    worksheet.Range["F1"].Value = "Вертикальное отклонение";
                    worksheet.Range["G1"].Value = "Вертикальное отклонение (динамика)";
                    worksheet.Range["H1"].Value = "Попадания";
                    worksheet.Range["I1"].Value = "Промахи";
                    worksheet.Range["J1"].Value = "Баллы";
                    worksheet.Range["K1"].Value = "Комментарий";
                    for (int i = 0; i < patient.results.Count; i++)
                    {
                        worksheet.Range["A" + (i + 2).ToString()].Value = patient.results[i].Name;
                        worksheet.Range["B" + (i + 2).ToString()].Value = patient.results[i].Date.ToShortDateString() + " " + patient.results[i].Date.ToShortTimeString();
                        worksheet.Range["C" + (i + 2).ToString()].Value = patient.results[i].Length.ToString();
                        worksheet.Range["D" + (i + 2).ToString()].Value = patient.results[i].HorDev.ToString();
                        worksheet.Range["E" + (i + 2).ToString()].Value = patient.results[i].HorDevDin.ToString();
                        worksheet.Range["F" + (i + 2).ToString()].Value = patient.results[i].VerDev.ToString();
                        worksheet.Range["G" + (i + 2).ToString()].Value = patient.results[i].VerDevDin.ToString();
                        worksheet.Range["H" + (i + 2).ToString()].Value = patient.results[i].Hit.ToString();
                        worksheet.Range["I" + (i + 2).ToString()].Value = patient.results[i].Miss.ToString();
                        worksheet.Range["J" + (i + 2).ToString()].Value = patient.results[i].Balls.ToString();
                        worksheet.Range["K" + (i + 2).ToString()].Value = patient.results[i].Comment;
                    }
                    bufar = patient.AverageResult();
                    worksheet.Range["A" + (patient.results.Count + 4).ToString()].Value = "Средний результат";
                    worksheet.Range["C" + (patient.results.Count + 4).ToString()].Value = bufar.Length.ToString();
                    worksheet.Range["D" + (patient.results.Count + 4).ToString()].Value = bufar.HorDev.ToString();
                    worksheet.Range["E" + (patient.results.Count + 4).ToString()].Value = bufar.HorDevDin.ToString();
                    worksheet.Range["F" + (patient.results.Count + 4).ToString()].Value = bufar.VerDev.ToString();
                    worksheet.Range["G" + (patient.results.Count + 4).ToString()].Value = bufar.VerDevDin.ToString();
                    worksheet.Range["H" + (patient.results.Count + 4).ToString()].Value = bufar.Hit.ToString();
                    worksheet.Range["I" + (patient.results.Count + 4).ToString()].Value = bufar.Miss.ToString();
                    worksheet.Range["J" + (patient.results.Count + 4).ToString()].Value = bufar.Balls.ToString();
                    worksheet.Range["K" + (patient.results.Count + 4).ToString()].Value = bufar.Comment;
                    bufbr = patient.BestResults();
                    worksheet.Range["A" + (patient.results.Count + 5).ToString()].Value = "Лучший результат";
                    worksheet.Range["C" + (patient.results.Count + 5).ToString()].Value = bufbr.Length.ToString();
                    worksheet.Range["D" + (patient.results.Count + 5).ToString()].Value = bufbr.HorDev.ToString();
                    worksheet.Range["E" + (patient.results.Count + 5).ToString()].Value = bufbr.HorDevDin.ToString();
                    worksheet.Range["F" + (patient.results.Count + 5).ToString()].Value = bufbr.VerDev.ToString();
                    worksheet.Range["G" + (patient.results.Count + 5).ToString()].Value = bufbr.VerDevDin.ToString();
                    worksheet.Range["H" + (patient.results.Count + 5).ToString()].Value = bufbr.Hit.ToString();
                    worksheet.Range["I" + (patient.results.Count + 5).ToString()].Value = bufbr.Miss.ToString();
                    worksheet.Range["J" + (patient.results.Count + 5).ToString()].Value = bufbr.Balls.ToString();
                    worksheet.Range["K" + (patient.results.Count + 5).ToString()].Value = bufbr.Comment;
                    //сохранение
                     SaveFileDialog sfd = new SaveFileDialog();
                    sfd.Filter = "Таблица результатов пациентов (*.xls)|*.xls";
                    sfd.FileName = patient.Name + "_результаты" + ".xls";
                    if (sfd.ShowDialog() == System.Windows.Forms.DialogResult.OK)
                        workBook.SaveAs(sfd.FileName);

                    //закрытие процесса
                    if (application != null)
                    {
                        int excelProcessId = -1;
                        GetWindowThreadProcessId(application.Hwnd, ref excelProcessId);

                        Marshal.ReleaseComObject(worksheet);
                        workBook.Close();
                        Marshal.ReleaseComObject(workBook);
                        application.Quit();
                        Marshal.ReleaseComObject(application);

                        application = null;
                        // Прибиваем висящий процесс
                        try
                        {
                            Process process = Process.GetProcessById(excelProcessId);
                            process.Kill();
                        }
                        finally { }
                    }
                }
                catch (Exception)
//.........这里部分代码省略.........

        public void ExcelTest(string workbook)
        {
            var outDir = Path.GetDirectoryName(new Uri(System.Reflection.Assembly.GetExecutingAssembly().CodeBase).LocalPath);

            m_xlApp = new Application();
            Assert.IsNotNull(m_xlApp, "Could not create an Excel Application object");

            var xlWorkbooks = m_xlApp.Workbooks;
            var testMacrosPath = Path.Combine(outDir, "testMacros.xlsm");
            var testMacros = xlWorkbooks.Open(testMacrosPath, 0, true, 5, "", "", true, XlPlatform.xlWindows, "\t", false, false, 0, true, 1, 0);
            var pidAsObject = m_xlApp.Run(testMacros.Name + "!getpid");
            m_xlProcess = Process.GetProcessById(pidAsObject);
            var xldnaToLoad = PrepareXlDna(outDir);

            workbook = Path.Combine(outDir, "testWorkbooks", workbook);
            var workbookName = Path.GetFileName(workbook);

            Assert.IsTrue(File.Exists(workbook));
            Assert.IsTrue(m_xlApp.RegisterXLL(xldnaToLoad));

            m_xlApp.DisplayAlerts = false;

            m_xlWorkbook = xlWorkbooks.Open(workbook, 0, true, 5, "", "", true, XlPlatform.xlWindows, "\t", false, false, 0, true, 1, 0);
            var workDir = Path.Combine(outDir, "evidence");
            if (!Directory.Exists(workDir))
            {
                try { Directory.CreateDirectory(workDir); } catch (Exception) { }; // more than 1 thread might get here, but only 1 will succeed
            }
            Assert.IsTrue(Directory.Exists(workDir));
            m_xlApp.Run(testMacros.Name + "!setProcessCurrentDirectory", workDir);

            m_xlApp.CalculateBeforeSave = false;

            bool vbaResult = false;
            try
            {
                vbaResult = m_xlApp.Run(m_xlWorkbook.Name + "!VBATest");
            }
            catch (COMException x)
            {
                // This HR means "Programmatic access to Visual Basic is not trusted"
                // It is returned also in case the macro does not exist
                // If we get anything other than this type of error, we rethrow.
                // Otherwise there's nothing to test and we carry on.
                if ((uint)x.HResult != 0x800a03ec)
                    throw;
                vbaResult = true;
            }
            Assert.IsTrue(vbaResult);

            Range resultRange = m_xlApp.Range["RESULT"];
            Assert.AreEqual(1, resultRange.Count);
            resultRange.Dirty();
            m_xlApp.CalculateFullRebuild();
            dynamic testResult = resultRange.Value;
            if (typeof(int) == testResult.GetType())
                Assert.Fail("RESULT={0}", hashErrorToString((uint)testResult));
            Assert.AreEqual(typeof(bool), testResult.GetType(), "RESULT={0}", testResult.ToString());
            Assert.IsTrue((bool)testResult);

            // the test has passed. we don't need to leave the xlsm around
            m_xlWorkbook.Close(false);
            m_xlWorkbook = null;
            File.Delete(workbook);
        }

//.........这里部分代码省略.........
                       // newfilePath = newfilePath.Replace("\\\\", "\\");
                        theFileName = GetFileName(newfilePath);
                        LogWithoutPath("file path: D: " + theFileName);
                        LogWithoutPath("end get file name");
                        LogWithoutPath("start open work book");
                        //wkb = app.Workbooks.Open(copyExcellTo + theFileName + ".xls");
                        try
                        {

                            //wkb = app.Workbooks.Open(filePaths[i].DocumentPath);
                            wkb = app.Workbooks.Open(newfilePath);

                            LogWithoutPath("end open work book");
                            //Workbook wkb = app.Workbooks.Open("C:\\temp3\\2004107065.xls");
                            //\\hmdn-srseds\SRSServer\Storage\16\1627369463\2004108635

                            //Workbook wkb = app.Workbooks.Open(filePaths[i].DocumentPath);
                            LogWithoutPath("start get work sheet");
                            sheet = (Worksheet)wkb.Worksheets[1];
                            LogWithoutPath("end get work sheet");
                            //sheet.PageSetup.FitToPagesWide = 1;
                            sheet.Select(Type.Missing);
                            //wkb.set.ActiveSheet = sheet;
                            LogWithoutPath("start save to pdf");
                        }
                        catch (Exception er)
                        { 
                        string s1 = er.ToString();
                        }
                        try
                        {
                            sheet.ExportAsFixedFormat(XlFixedFormatType.xlTypePDF, savePDFTo + theFileName + ".pdf", XlFixedFormatQuality.xlQualityStandard, false, false, 1, 1, true);
                        }
                        catch
                        { }

                        LogWithoutPath("end save to pdf");
                        Console.WriteLine("finished with number: " + i.ToString());
                        LogWithoutPath("start close resources");

                        try
                        {
                            wkb.Close(false);
                            app.Application.Quit();
                            app.Quit();

                        }
                        catch { }

                        try
                        {
                            Marshal.ReleaseComObject(sheet);
                            Marshal.ReleaseComObject(wkb);
                            Marshal.ReleaseComObject(app);
                            Marshal.ReleaseComObject(wkb);
                            Marshal.ReleaseComObject(app);
                        }
                        catch { }

                        try
                        {
                            Process[] excelProcess = Process.GetProcessesByName("Excel");
                            excelProcess[0].Kill();
                        }
                        catch { }
                        LogWithoutPath("end close resources");
                        try
                        {
                            SaveID(filePaths[i].DocumentID);
                            //if (i >= 10000)
                            //{
                            //    Environment.Exit(0);
                            //}
                        }
                        catch { }
                    }
                    catch (Exception er )
                    {
                        string s1 = er.ToString();
                        wkb.Close(false);
                        app.Application.Quit();
                        app.Quit();

                        //Marshal.ReleaseComObject(sheets);
                        Marshal.ReleaseComObject(sheet);
                        Marshal.ReleaseComObject(wkb);
                        Marshal.ReleaseComObject(app);

                        Marshal.ReleaseComObject(wkb);
                        Marshal.ReleaseComObject(app);

                        Process[] excelProcess = Process.GetProcessesByName("Excel");
                        excelProcess[0].Kill();
                        LogWithoutPath("Problem with ID: " + filePaths[i].DocumentID);

                    }
                }
                Console.WriteLine("end job: " + DateTime.Now.ToShortDateString() + " " + DateTime.Now.ToShortTimeString());
            }
        }

 public bool Close(Workbook wb)
 {
     wb.Close(false, null, null);
     excel.Quit();
     return true;
 }

//.........这里部分代码省略.........
                            // if (MainForm.StopGracefully)
                            // break;

                            // Sortera på new
                            var column = GetStandardExcelColumnName(colums + 1);
                            var range = newWorksheet.Range["A4", column + rows.ToString(CultureInfo.InvariantCulture)];

                            // "IV"
                            if (name != "DatabaseInfo")
                            {
                                range.Sort(
                                    range.Columns[colums + 1, Type.Missing],
                                    XlSortOrder.xlDescending,
                                    // För att felsöka Excelprogrammering, använd macroEdit for VB i excel...
                                    // range.Columns[2,Type.Missing], Type.Missing, Excel.XlSortOrder.xlDescending
                                    Type.Missing,
                                    Type.Missing,
                                    XlSortOrder.xlDescending,
                                    Type.Missing,
                                    XlSortOrder.xlDescending,
                                    XlYesNoGuess.xlNo,
                                    Type.Missing,
                                    Type.Missing,
                                    XlSortOrientation.xlSortColumns,
                                    // ReSharper disable RedundantArgumentDefaultValue
                                    XlSortMethod.xlPinYin,
                                    XlSortDataOption.xlSortNormal,
                                    XlSortDataOption.xlSortNormal,
                                    XlSortDataOption.xlSortNormal);

                                // ReSharper restore RedundantArgumentDefaultValue
                            }

                            // Ta bort "new"-kolumnen
                            range = newWorksheet.Range[column + 1, column + rows.ToString(CultureInfo.InvariantCulture)];

                            // "IV"
                            range.Delete(Type.Missing); // false//(object)false);//

                            // För old o diff överskriften...
                            if (name == "DatabaseInfo")
                            {
                                range = newWorksheet.Range["C:D", "C:D"];
                                range.EntireColumn.AutoFit(); // autofittar hela columnen för all som loggas
                            }

                            // newWorksheet.set
                            #endregion
                        }

                        // if (MainForm.StopGracefully)
                        // break;
                    }
                    else
                    {
                        Console.WriteLine(name + " didn't exist in old Excel book.");
                    }

                    // oldBook.Add(name, oldRows);

                    // _compareProgress.SetTotal(++sheetsDone);
                }

                // Spara en ny fil
                // newLog.FullName = newLog.FullName + "-Compared";
                // newLog.Save();// .Save();
                newLogFileName = newLog.FullName.Replace(".xls", string.Empty) + "-Compared" + ".xls";
                newLog.SaveCopyAs(newLogFileName); // MainForm.LastLogPath);//newLog.FullName + "-Compared");
                newLog.Close(false, Type.Missing, Type.Missing);

                // _book.Close(false, Type.Missing, Type.Missing);
                excelApp.Quit();
                System.Runtime.InteropServices.Marshal.ReleaseComObject(excelApp);
                excelApp = null;
            }
            catch (Exception e)
            {
                // MessageBox.Show("Error in comparing old log with new log. Was the log opened in Excel during compare processing?\r\n\r\n(Sys err: " + e.Message + ").");
                // throw e;
                throw new Exception(
                    "Error in comparing old log with new log. Was the log opened in Excel during compare processing?\r\n\r\n(Sys err: "
                    + e.Message + ").",
                    e);
            }

            #endregion - read new

            System.Threading.Thread.CurrentThread.CurrentCulture = oldCi;

            // MainForm.ShowTextProgress = false;//Visar inte ens texten längre
            // MainForm.ShowProgress = orgProgresSetting;//Håller på vad progress vad satt till innan

            // Stäng Excel
            if (excelApp != null)
            {
                excelApp.Quit();
                System.Runtime.InteropServices.Marshal.ReleaseComObject(excelApp);
                excelApp = null;
            }
        }

        private void BgwExportaDocumento_DoWork(object sender, DoWorkEventArgs e)
        {
            try
            {
                _app = new Application();
                _workBook = _app.Workbooks.Add(misValue);
                _workSheet = (Worksheet)_workBook.Worksheets.Item[1];

                //Configurações visuais para célula
                _workSheet.Cells.Columns.AutoFit();
                _workSheet.Cells.HorizontalAlignment = XlHAlign.xlHAlignCenter;
                _workSheet.Cells.Font.Size = 13;

                for (var i = 0; i < 2; i++)
                {
                    EscreveDados(tituloForm, dataGridView, headers, headersData);

                    if (!bgwExportaDocumento.CancellationPending) continue;
                    e.Cancel = true;
                    return;
                }

                _workBook.SaveAs(salvar.FileName, XlFileFormat.xlOpenXMLWorkbook, Missing.Value, Missing.Value,
                    false,
                    false,
                    XlSaveAsAccessMode.xlNoChange, XlSaveConflictResolution.xlUserResolution, true, Missing.Value,
                    Missing.Value, Missing.Value);

                e.Result = true;
            }
            catch (Exception ex)
            {
                MessageBox.Show(
                    "Erro ao salvar. Se estiver substituindo um arquivo já existente tenha certeza que ele está fechado e tente novamente.\n" + ex.Message,
                    "ERRO", MessageBoxButtons.OK, MessageBoxIcon.Error);
                e.Result = false;
            }
            finally
            {
                try
                {
                    _workBook.Close(true, misValue, misValue);
                    _app.Quit(); // encerra o excel
                }
                catch (Exception ex)
                {
                    Console.WriteLine(ex.Message);
                }
            }
        }

            //Open the first worksheet and get the information
            //For now I will try to have as parameters the fields I want to get out of the excel document
            //public static object[,] 
            public static void readWorksheet(Workbook workbook, out string[] Course, out string[] IndividualID, out string[] LastName, out string[] FirstName, out string[] Email)
            {
                
            
                //select the first sheet        
                Worksheet worksheet = (Worksheet)workbook.Worksheets[1];

                //find the used range in worksheet
                Range excelRange = worksheet.UsedRange;

                //get an object array of all of the cells in the worksheet (their values)
                object[,] valueArray = (object[,])excelRange.get_Value(
                            XlRangeValueDataType.xlRangeValueDefault);

                //make an array of all the data in each of the columns
                string[] YT = new string[worksheet.UsedRange.Rows.Count];
                string[] MatchCourse = new string[worksheet.UsedRange.Rows.Count];
                string[] Department = new string[worksheet.UsedRange.Rows.Count];
                Course = new string[worksheet.UsedRange.Rows.Count];
                string[] Section = new string[worksheet.UsedRange.Rows.Count];
                string[] FinalGrade = new string[worksheet.UsedRange.Rows.Count];
                IndividualID = new string[worksheet.UsedRange.Rows.Count];
                string[] Classification = new string[worksheet.UsedRange.Rows.Count];
                string[] EnrollmentStatus = new string[worksheet.UsedRange.Rows.Count];
                LastName = new string[worksheet.UsedRange.Rows.Count];
                FirstName = new string[worksheet.UsedRange.Rows.Count];
                string[] Nickname = new string[worksheet.UsedRange.Rows.Count];
                string[] Phone = new string[worksheet.UsedRange.Rows.Count];
                Email = new string[worksheet.UsedRange.Rows.Count];
                string[] Notes = new string[worksheet.UsedRange.Rows.Count];
                
               
                //access the cells
                for (int row =1; row < worksheet.UsedRange.Rows.Count; ++row)
                {
                    
                    
                    int col = 1;
                    YT[row] = valueArray[row,col].ToString();
                    MatchCourse[row] = valueArray[row,++col].ToString();
                    Department[row] = valueArray[row,++col].ToString();
                    Course[row] = valueArray[row,++col].ToString();
                    Section[row] = valueArray[row,++col].ToString();
                    FinalGrade[row] = valueArray[row, ++col].ToString();
                    IndividualID[row] = valueArray[row, ++col].ToString();
                    Classification[row] = valueArray[row, ++col].ToString();
                    EnrollmentStatus[row] = valueArray[row, ++col].ToString();
                    LastName[row] = valueArray[row, ++col].ToString();
                    FirstName[row] = valueArray[row, ++col].ToString();
                    Nickname[row] = valueArray[row, ++col].ToString();
                    Phone[row] = valueArray[row, ++col].ToString();
                    Email[row] = valueArray[row, ++col].ToString();
                    Notes[row] = valueArray[row, ++col].ToString();


                }
                //clean up stuffs
                workbook.Close(false, Type.Missing, Type.Missing);
                Marshal.ReleaseComObject(workbook);

                //return valueArray;



                //_excelApp.Quit();
                //Marshal.FinalReleaseComObject(_excelApp);
            }

//.........这里部分代码省略.........
                                                          {
                                                              Workbook workBook = _excel.Workbooks.Open(file);
                                                              // validate in file
                                                              if (workBook.Sheets.Count != _template.Sheets.Count )
                                                              {
                                                                  log("количесво листов в документе " + file + " равно " + workBook.Sheets.Count, Priority.High);
                                                                  return null;
                                                              }
                                                              return workBook;
                                                          }
                                                          catch (Exception ex)
                                                          {
                                                              logError(file, ex, "Не смог прочитать файл", Priority.High);
                                                              return null;
                                                          }
                                                          ;
                                                      }
                                         ).Where(x => x != null).ToArray());
            report("количество соответсвующих шаблону документов Excel равно " + _filledWorkbooks.Count);
            if (_filledWorkbooks.Count == 0)
            {

                finish();
                return;
            }
            int errorCounter = 0;
            int emptyCounter = 0;
            foreach (Worksheet sheet in _template.Sheets)
            {
                report(sheet.Name);
                var lastCell = sheet.Cells.SpecialCells(XlCellType.xlCellTypeLastCell);
                var r = lastCell.Row;
                var c = sheet.Columns.Count;

                for (int i = 1; i <= r; i++)
                {
                    for (int j = 1; j <= c; j++)
                    {
                        Range cell = sheet.Cells[i, j];
                        int colorNumber = System.Convert.ToInt32(cell.Interior.Color);
                        Color color = System.Drawing.ColorTranslator.FromOle(colorNumber);
                        if (color != Color.White) //cell is gray
                        {
                            decimal summ = 0;
                            foreach (var filled in _filledWorkbooks)
                            {
                                var filledSheet = filled.Sheets[sheet.Name] as Worksheet;
                                var filledCell = filledSheet.Cells[i, j] as Range;
                                var value = filledCell.Value2;
                                string message = null;
                                decimal add = 0;
                                Priority p = Priority.Low;

                                if (value == null)
                                {
                                    emptyCounter++;
                                }
                                else
                                {
                                    string repr = string.Format("{0}", value);
                                    if (String.IsNullOrWhiteSpace(repr))
                                    {
                                        message = "Ячейка пробел";
                                        errorCounter++;
                                        p = Priority.High;
                                    }
                                    else
                                    {
                                        if (!Decimal.TryParse(repr, out add))
                                        {
                                            message = "Нечисленное значение";
                                            errorCounter++;
                                            p = Priority.High;
                                        }
                                    }
                                }
                                if (message != null)
                                {
                                    log(filled.FullName, filledSheet.Name, filledCell.Row, filledCell.Column, message, p, Severity.Error);
                                }
                                summ += add;
                            }
                            cell.Value2 = summ;
                        }
                    }
                }
            }
            report("ошибок " + errorCounter);
            report("пустых цветных ячеек" + emptyCounter);
            if (!Directory.Exists(inDir))
            {
                Directory.CreateDirectory(inDir);
            }
            var outFile = Path.Combine(outDir, "all.xls");
            _template.SaveAs(outFile);
            _template.Close(false);
            report("сделано " + outFile);
            report("нажмите любую клавишу для завершения");
            finish();
        }

        public void Export()
        {
            try
            {
                _workbook = CreateWorkbook();
                this.Application.Visible = !_hideApp;

                BeforeExport();
                WriteHeader();
                WriteColumnHeader();
                WriteContent();
                AfterContentWritten();
            }
            catch (Exception ex)
            {
                try
                {
                    Application.DisplayAlerts = false;
                    if (_workbook != null)
                        _workbook.Close(false, Missing, Missing);
                    Application.DisplayAlerts = true;
                }
                catch (Exception) { }
                Global.ShowError(ex);
                //throw ex;
            }

            if (this.UseIndependentApp)
            {
                try
                {
                    Application.DisplayAlerts = false;
                    Application.Quit();
                }
                catch (Exception) { }
            }
        }

//.........这里部分代码省略.........

                #region URL Param Hits Summary
                if ( hitsPerURLParams.Any () )
                {
                    Console.WriteLine ("{0} Genrating URL parameter statistics", stopWatch.Elapsed.ToString (@"hh\:mm\:ss"));

                    reportSheet = reportSpreadsheet.Worksheets.Add (Type.Missing, reportSheet, 1);
                    startRow = startCol = 1;
                    reportSheet.Name = "URL Parameters";
                    CollectionToTable (urlParamHits.Values, startRow, startCol, "URL Parameters Summary (for the period)");
                }
                #endregion

                #region Summary
                Console.WriteLine ("{0} Genrating Summary", stopWatch.Elapsed.ToString (@"hh\:mm\:ss"));
                reportSheet = reportSpreadsheet.Worksheets.Add (reportSheet, Type.Missing, 1);
                reportRow = reportCol = 1;
                reportSheet.Name = "Summary";
                reportSheet.Cells[reportRow, 1] = "Running From";
                reportSheet.Cells[reportRow++, 2] = curerntPath;

                reportSheet.Cells[reportRow, 1] = "Commandline Argument";
                reportSheet.Cells[reportRow++, 2] = string.Join (";", cmdArgs.Select (x => x.Key + "=" + x.Value));

                reportSheet.Cells[reportRow, 1] = "Files Processed";
                reportSheet.Cells[reportRow++, 2] = fileCount;

                reportSheet.Cells[reportRow, 1] = "From";
                reportSheet.Cells[reportRow++, 2] = firstEntry;

                reportSheet.Cells[reportRow, 1] = "To";
                reportSheet.Cells[reportRow++, 2] = lastEntry;

                reportSheet.Cells[reportRow, 1] = "TotalHits";
                reportSheet.Cells[reportRow++, 2] = TotalHits;

                reportSheet.Cells[reportRow, 1] = "Average Transactions/Sec";
                reportSheet.Cells[reportRow++, 2] = requests.Average (p => p.Tps);

                if ( hourlyHits!=null )
                {
                    reportSheet.Cells[reportRow, 1] = "Average Transactions/Hour";
                    reportSheet.Cells[reportRow++, 2] = hourlyHits.Average (p => p.Sum (q => q.Hits));
                }

                if ( peakHourPages!=null )
                {
                    reportSheet.Cells[reportRow, 1] = "Peak Hour Transactions/Hour";
                    reportSheet.Cells[reportRow++, 2] = peakHourPages.Average (p => p.Sum (q => q.Hits));

                    reportSheet.Cells[reportRow, 1] = "Peak Hour Transactions/Sec";
                    reportSheet.Cells[reportRow++, 2] = peakHourPages.Average (p => p.Sum (q => q.Hits) / 3600);
                }

                reportSheet.Cells[reportRow, 1] = "UniqueIPs";
                reportSheet.Cells[reportRow++, 2] = uniqueIPs.Count;

                reportSheet.Cells[reportRow, 1] = "ServedRequests";
                reportSheet.Cells[reportRow++, 2] = ServedRequests;


                reportRow += 10;
                reportSheet.Cells[reportRow++, 1] = "Http Status code summary";

                reportSheet.Cells[reportRow, 1] = "HTTP Code";
                reportSheet.Cells[reportRow++, 2] = "Count";

                foreach ( var i in httpStatus )
                {
                    reportSheet.Cells[reportRow, reportCol++] = i.Key;
                    reportSheet.Cells[reportRow++, reportCol--] = ( i.Value );
                }
                #endregion

            }
            catch ( Exception e )
            {
                Console.WriteLine ("Error!! {0}:{1} - {2}", e.GetType ().Name, e.Message, e.StackTrace);
                Debug.WriteLine ("Error!! {0}:{1}", e.GetType ().Name, e.Message);
            }
            finally
            {
                if ( excelApp != null )
                {
                    excelApp.Calculation = XlCalculation.xlCalculationAutomatic;
                    if ( reportSpreadsheet != null )
                    {
                        reportSpreadsheet.Save ();
                        reportSpreadsheet.Close ();
                        excelApp.Quit ();
                    }
                }
                File.Delete (tmpFile);
                stopWatch.Stop ();
                Console.WriteLine ("Done, Final time : {0}", stopWatch.Elapsed.ToString (@"hh\:mm\:ss"));
            }
            #endregion

            return 0;
        }

        public void generateReport(string fileName)
        {
            try
            {
                if (Owner != null)
                {
                    if (Owner.TestRunner != null)
                    {
                        m_xlApp = new Microsoft.Office.Interop.Excel.Application();
                        m_xlWorkBook = m_xlApp.Workbooks.Add(misValue);
                        m_xlWorkSheet = (Worksheet)m_xlWorkBook.Worksheets.get_Item(1);
                        m_xlWorkSheet.Cells[1, 1] = "_Test_Phase_Number";
                        m_xlWorkSheet.Cells[1, 2] = "_Type";
                        m_xlWorkSheet.Cells[1, 3] = "Object Text";
                        m_xlWorkSheet.Cells[1, 4] = "_Expected_Result";
                        m_xlWorkSheet.Cells[1, 5] = "TestCaseName";
                        (m_xlWorkSheet.Range[m_xlWorkSheet.Cells[1, 1], m_xlWorkSheet.Cells[1, 5]]).Interior.Color = XlRgbColor.rgbLightCyan;
                        int j = 2;

                        foreach (ITestSuit suit in Owner.TestRunner.TestSuits)
                        {
                            foreach (ItestCase test in suit.TestCases)
                            {

                                m_xlWorkSheet.Cells[j, 1] = test.TestPhaseNumber;
                                m_xlWorkSheet.Cells[j, 2] = "Test Phase Number";
                                m_xlWorkSheet.Cells[j, 3] = test.TestPhaseNumber;
                                j++;
                                m_xlWorkSheet.Cells[j, 2] = "Test Description";
                                m_xlWorkSheet.Cells[j, 3] = test.TestDescription;

                                foreach (string precondition in test.TestPrecondition)
                                {
                                    j++;
                                    m_xlWorkSheet.Cells[j, 2] = "Test Pre-Condition";
                                    m_xlWorkSheet.Cells[j, 3] = precondition + "\n";

                                }

                                foreach (string TestSteps in test.TestSteps)
                                {
                                    j++;
                                    m_xlWorkSheet.Cells[j, 2] = "Test Step";
                                    m_xlWorkSheet.Cells[j, 3] = TestSteps + "\n";

                                }
                                string ExpectedResultString = "";
                                foreach (string ExpectedResult in test.ExpectedResult)
                                {
                                    ExpectedResultString += ExpectedResult + "\n";

                                }
                                m_xlWorkSheet.Cells[j, 4] = ExpectedResultString;
                                m_xlWorkSheet.Cells[j, 5] = test.Name;
                                j++;

                            }

                        }
                        oRange = m_xlWorkSheet.Range[m_xlWorkSheet.Cells[1, 1], m_xlWorkSheet.Cells[j, 5]];
                        oRange.Borders.get_Item(XlBordersIndex.xlEdgeLeft).LineStyle = XlLineStyle.xlContinuous;
                        oRange.Borders.get_Item(XlBordersIndex.xlEdgeRight).LineStyle = XlLineStyle.xlContinuous;
                        oRange.Borders.get_Item(XlBordersIndex.xlInsideHorizontal).LineStyle = XlLineStyle.xlContinuous;
                        oRange.Borders.get_Item(XlBordersIndex.xlInsideVertical).LineStyle = XlLineStyle.xlContinuous;

                        m_xlWorkBook.SaveAs(fileName, XlFileFormat.xlWorkbookNormal, misValue, misValue, misValue, misValue, XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue);
                        m_xlWorkBook.Close(true, misValue, misValue);
                        m_xlApp.Quit();

                        releaseObject(m_xlWorkSheet);
                        releaseObject(m_xlWorkBook);
                        releaseObject(m_xlApp);
                        MessageBox.Show("Excel report Generated");

                    }
                }
            }
            catch
            {

            }
        }

        //
        //**********************************************************************************************
        //
        public List<TMeteo> ReadIrrigation(String aFile)
        {
            Double NewValue = 0.0;
              String SheetName = @"Irrigation";
              List<TMeteo> Irrig = new List<TMeteo>();
              Irrig.Clear();
              MyWorkBook = ExcelApp.Workbooks.Open(aFile, Missing, Missing, Missing,
              Missing, Missing, Missing, Missing, Missing, Missing, Missing,
              Missing, Missing, Missing, Missing);

              try
              {
            try
            {
              MyWorkSheet = (Worksheet)MyWorkBook.Worksheets.get_Item(SheetName);
              Range MyRange = MyWorkSheet.UsedRange;
              System.Array MyValues = (System.Array)MyRange.Cells.get_Value(XlRangeValueDataType.xlRangeValueDefault);
              String[,] MyData = ConvertToStringArray(MyValues);
              for (Int32 i = 1; i < MyData.GetLength(0); i++)
              {
            TMeteo MyMeteo = new TMeteo();
            if ((MyData[i, 0].IndexOf("-") >= 0) || (MyData[i, 0].IndexOf("/") >= 0))
            {
              MyMeteo.Date = Convert.ToDateTime(MyData[i, 0]);
              NewValue = Convert.ToDouble(MyData[i, 1]);
              MyMeteo.Value = NewValue;
              Irrig.Add(MyMeteo);
            }
              }
            }
            catch (Exception e)
            {
              MessageBox.Show("??? Error opening sheet Irrigation in file " + aFile + ": " + e.Message);
            }
              }
              finally
              {
            MyWorkBook.Close(false, "", null);
              }
              return Irrig;
        }