I have few tests which run three times and their average is calculated through c# code. I am able to the three test times and their averages to the if I the beforehand in the format below:

xls file format .

But now I have to run each test every hour everyday through a batch file using windows scheduler. I want to create the xls file dynamically in every hour in the format above, with a specific name so that at the first iteration the file is created, then for the next 19 iterations it should write in the same file. The next hour a new file should be created with a specific name.

How do I create and write the file dynamically?

If there is any other simple procedure please suggest that. The code which I was using to write in the already created xls file is:

using System;
using System.IO;
using ;

namespace PEPI_Performance.Utility
{
    /// <summary>
    /// Description of ExcelWriter.
    /// </summary>

    public class ExcelWriter
    {
        /// <summary>
        /// Constructs a new instance.
        /// </summary>
        public ExcelWriter()
        {
            // Do not delete - a parameterless constructor is required!
        }


        public void Driver(int row , int col, string time, string sheetName){

            string sDataFile = "_Reports.xls";
            string sFilePath = Path.GetFullPath(sDataFile);

            string sOldvalue = "Automation\bin\Debug\" + sDataFile;
            sFilePath = sFilePath.Replace(sOldvalue,"")+ "PEPI_Performance\ExecutionReport\" + sDataFile;
            fnOpenExcel(sFilePath,sheetName);
            writeExcel(row,col,time);
            fnCloseExcel();
        }
        Excel.Application   exlApp ;
        Excel.Workbook exlWB ;
        Excel.Sheets excelSheets ;
        Excel.Worksheet exlWS;
        //Open Excel file
        public int fnOpenExcel(string sPath, string iSheet){

            int functionReturnValue = 0;
            try {

                exlApp = new Excel.ApplicationClass(); //Microsoft.Office.Interop.Excel.Application();
                exlApp.Visible = true;
                exlWB = exlApp.Workbooks.Open(sPath,Type.Missing,Type.Missing,Type.Missing,Type.Missing,Type.Missing,Type.Missing,Type.Missing,Type.Missing,Type.Missing,Type.Missing,Type.Missing,Type.Missing);

                // get all sheets in workbook
                excelSheets = exlWB.Worksheets;

                // get some sheet
                //string currentSheet = "Cycle1";
                exlWS = (Excel.Worksheet)excelSheets.get_Item(iSheet);
                functionReturnValue = 0;
            }
            catch (Exception ex) {
                functionReturnValue = -1;
                Report.Error(ex.Message);
            }
            return functionReturnValue;
        }


        // Close the excel file and release objects.
        public int fnCloseExcel(){
            //exlWB.Close();

            try{
                exlApp.ActiveWorkbook.Save();
                exlApp.Quit();

                System.Runtime.InteropServices.Marshal.ReleaseComObject(exlWS);
                System.Runtime.InteropServices.Marshal.ReleaseComObject(exlWB);
                System.Runtime.InteropServices.Marshal.ReleaseComObject(exlApp);

                GC.GetTotalMemory(false);
                GC.Collect();
                GC.WaitForPendingFinalizers();
                GC.Collect();
                GC.GetTotalMemory(true);
            }catch(Exception ex){
                Report.Error(ex.Message);
            }
            return 0;
        }

        public void writeExcel(int i, int j , string time){
            Excel.Range exlRange = null;
            exlRange = (Excel.Range)exlWS.UsedRange;
            ((Excel.Range)exlRange.Cells[i,j]).Formula = time;

        }

    }
}



Source link https://sqa.stackexchange.com/questions/9559/how-to-create-and-write-excel-xls-file-using-c

LEAVE A REPLY

Please enter your comment!
Please enter your name here