How to create and write Excel .xls file using C#



  • 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
    {
        /// 
        /// Description of ExcelWriter.
        /// 
    
        public class ExcelWriter
        {
            /// 
            /// Constructs a new instance.
            /// 
            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;
    
            }
    
        }
    }
    


  • 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/


Log in to reply
 

Suggested Topics

  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2