I am in no way familiar with Excel automation but I would start with something like this.

What I do is start with something and observe how it behaves. Then I iterate through changes until find the most optimal code. This means that I will try different variations.

But before I can develop good code I must establish a base version that is simple and works. I don't care about fast or perfect, I must learn how Excel and OLE behaves, how it uses up memory, CPU, bottlenecks and how to recover from hanging first.

And most importantly the code should be readable. Note: I am doing this without C# just out of my head and I never developed for Excel ;-)

var excel=new Excel()
excel.Open("myExcel.xls");
excel.Close();

Stage 2

public Excel : IDisposable {
    public bool IsOpened {get; set;}
    public void Open(string fileToOpen) {
        ....
        IsOpened=true;
    }
    public void Close() {
        ...
        IsOpened=false;
      }
    void public Dispose(){
        if (IsOpened) {
            Close()
        }
    }
}

Stage 3 we get somethign like this:

using (var excel=new Excel()) {
    excel.Open("myExcel.xls");
    excel.Close();
}

Now we try a prototype not caring about bugs, naming or reliability. The goal is to have a concept, something that works just to find the weakness and flaws on this part

using Microsoft.Office.Interop.Excel;
public Excel : IDisposable {
    public bool IsOpened {get; set;}
    private Application exelHandle;
    private Workbook    workBook
    public void Open(string fileToOpen) {
           exelHandle = new Excel.Application();
           workBook = (Workbook)exelHandle.Workbooks.Open(fileToOpen, false, false, m_objOpt, m_objOpt,
                                                         m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt,
                                                         m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt);
        ....
        IsOpened=true;
    }
    public void Close() {
        ...
        if (wBook != null) workBook.Close(false, m_objOpt, m_objOpt);
        workBook=null;
        if (exelHandle!=null) exelHandle.Quit();
        exelHandle=null;
        IsOpened=false;
      }
    void public Dispose(){
        if (IsOpened) {
            Close()
        }
    }
}

I will continue in other posts (could take some time or days depending on my time I have)

But I think it is interesting to see my workflow.

Edit: Just a question Do you mean this:

* Open 1...n workbooks
* iterate through 1...N worksheets
* set parameters
* set formats

Or this

* Open 1...n workbooks
* iterate through 1...N worksheets
         * set parameters
         * set formats

Update, when I started to take the challenge then I thought it would be simple but this simple example is behind the scenes pretty complicated. I don't think I can post this here on Voat nor have the time to create one. I estimate 3 working days for a first framework and then 2 more days to have a stable working version that can become the basis of anything direction you would like to take it.

In order to get a good basic frame you need to think about this.

• When I get the excel file is it one fixed template? Or a series of templates?
• Do the excel files come in pairs with the data and do I need to merge them?
• Is the data to merge fixed and do I have different excel files to merge?
• Do I have the merging data before the excel file? Or do I first need to load the Excel file and then determine what data I need from that file?
• Do we have a limited number of data files and excel files? Can I preload both of them?
• Can the data be already be preprocessed so that the merging takes minimal effort?
• Is the import files source the same as the output file source?
• Are the excel files huge? Os small size?
• Are the excel files and data files constant or changes on the fly?
• Do the cell data depend on each other?
• Do the cell data cross different tabs?
• Difference in file size? Bigger excel spread sheets need more time to load and save and can block smaller files that is waiting for the big one.
• Order of processing, are the excel files to be processes in the correct sequence as they come in or can this be a random order.
• The data to be inserted, Is it a simple push of data to known cells? Is it a search and replace of all cells?
• Testing, how would the users test every stage?

I surely would start with a separation of the Excel specific logic and and the processing logic. Design classes for an Excel subpart so that each part can be easily used.

If the excel template files are constant than I could is something like this: We prevent reloading the excel template every single time, just copy it reducing hard disk bottle neck

var mainTemplate=new Excel()
var isSuccess=mainTemplate.Load("Template.xls", MaxRetry:6, DelaybetweenRetries: 500)
if (isSuccess) {
      var datafiles=new DataFiles()
      var isLoaded=dataFiles.Load();
      while (isLoaded) {
          var newDest=new Excel(mainTemplate);  // Copy constructor that copies from the template files)
          var isProcessed=dataProcesser.Process(newDest))
          if (isProcessed) newDest.Save("destination.pdf", FileType.PDF)
      }
}
if (isSuccess.Close(KillProcessIfNeeded:True))

If data is not a search and replace the something like this can happen:

var mainTemplate=new Excel()
var isSuccess=mainTemplate.Load("Template.xls", MaxRetry:6, DelaybetweenRetries: 500)
var newDest=new Excel(mainTemplate);  // Copy constructor that copies from the template files)
if (isSuccess) {
     var datafiles=new DataFiles()
     var isLoaded=dataFiles.Load();
      while (isLoaded) {
          var dataProcesser=new DataProcessor(;
          var isProcessed=dataProcesser.Process(newDest))
          if (isProcessed) newDest.Save("destination.pdf", FileType.PDF)
      }
}
if (isSuccess.Close(KillProcessIfNeeded:True))

I don't have time to get in this project deeper but it is very interesting to solve it for my future projects.