2

I have few tests which run three times and their average is calculated through c# code. I am able to write the three test times and their averages to the xls file if I create the file 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 excel 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 Ranorex;

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 = "Ranorex_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;

        }

    }
}
1

Check if the file exists and if it doesn't exist create it. If it does exist then use the same code you have to modify it.

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

    string sDataFile = "Ranorex_Reports.xls";
    string sFilePath = Path.GetFullPath(sDataFile);
    string sOldvalue = "Vopak_Automation\\bin\\Debug\\" + sDataFile;
    sFilePath = sFilePath.Replace(sOldvalue,"")+ "PEPI_Performance\\ExecutionReport\\" + sDataFile;

    if (File.Exists(sFilePath))
    {
        fnOpenExcel(sFilePath,sheetName);
        writeExcel(row,col,time);
        fnCloseExcel();
    }
    else
    {
        /*I'm not sure what libraries you are particularly using...it sounds like Ranorex proprietary excel code.  
        You can try to utilize that and create the excel file to start with at this point.  or 
        there are some open source libraries that should function for your needs.*/
    }
}

Excel: http://epplus.codeplex.com/

| improve this answer | |
0

@mutt has the basics of what you need to create an excel spreadsheet.

For the rest of your request:

  1. Your batch file to be run by the scheduler should perform the twenty iterations with the file being created if it doesn't exist.
  2. Your scheduled task should be set to run every hour. That will automatically give you a new file every hour
  3. Your code will need to include something using the C# datetime libraries to retrieve the date and hour to append to the filename - so you'll have something like this: string sDataFile = "Ranorex_Reports" + Date("YYYYMMDD").ToString() + Time("HH").ToString() + ".xls" which will give you a file named something like "Ranorex_Reports2014082808.xls" (This is pseudo-code - you will need to look up the C# date time libraries to find the routines that will give you a date-time string)

You could pass in a date/time string from the batch file, but that would require modifying your script to take parameters from a calling file (which gets... interesting if you're working with other tools - Ranorex may have a simple command line interface which allows you to do this: if so use that instead if in-code settings)

Another option is to use an iteration counter and set the file name on iteration 1. Again, use something like a date/time stamp for the file name because you're going to accumulate these quickly unless you're moving the files out on a regular basis.

UPDATE (reference links added from comments):

These links could be helpful

| improve this answer | |
  • I don't know how to create an excel file with respective names with header Run1,Run2,Run3 and Average. I have written another code for that but its always creating a file with name Sheet1 and same header name. Referring link – user8507 Aug 29 '14 at 5:06
  • You may find something more helpful with these references: leniel.net/2009/07/… and stackoverflow.com/questions/10100901/… (which has a list of references in the answer). Let me know if these help and I'll add them to my answer. – Kate Paulk Aug 29 '14 at 10:53