Can a multi-core instance be used inside a single Excel instance?
I need to apply simple formatting to 10,000+ Excel files. I already have a multi-core program. It opens a new instance of Excel for each Excel file. If it matters, this code is currently inside my form code.
I was hoping to use one instance of Excel with many workbooks. Can I use multi-core capabilities if I only use one copy of Excel? How?
-If there is no answer to the above, a more complex question may arise: how many instances of Excel should I spawn and how can I split the workbooks between each instance?
The current code is here:
private void SelectFilesButtonClick(object sender, EventArgs e)
{
var listOfExcelFiles = OpenExcel.FileNames.ToList();
Parallel.ForEach(listOfExcelFiles, TrivialExcelEditFunction);
}
private void TrivialExcelEditFunction(string file)
{
//Open instance of Excel
//Do processing
//Close instance of Excel
}
Updated the code below, but it still isn't limited to native core count. I do not know why.
private void SelectFilesButtonClick(object sender, EventArgs e)
{
var listOfExcelFiles = OpenExcel.FileNames.ToList();
int cores = Environment.ProcessorCount;
//Split one list into list of lists. Number of lists based on number of cpu cores
List<List<object>> listOfLists = Split(listOfExcelFiles, cores);
//Limits number of threads to number of cores
Parallel.ForEach(listOfLists, new ParallelOptions { MaxDegreeOfParallelism = cores }, EditExcel);
}
private void TrivialExcelEditFunction(string file)
{
//Open instance of Excel
foreach (string file in files)
{
//Do processing
}
//Close instance of Excel
}
Let's say I have 4 cores. My thinking was to split the file list into 4 equal lists, limit streams to 4, then I can process files in 4 instances of Excel. I thought this would mean the TrivialExcelEditFunction will only work 4 times. Instead, this function is executed 14 to 27 times. Please tell me where I went wrong.
source to share
I think this is what @Servy means above. I use this daily part of the code I am working with, which handles Excel and has certainly not worked yet. Also make sure you are marshaling the COM object correctly. A little more information, Excel 2010 uses multi-core internal, watch out for performance (especially with a large file).
var tasks = new Task[Environment.ProcessorCount];
for (int i = 0; i< Environment.ProcessorCount; i++)
{
tasks [i] = Task.Factory.StartNew(() =>
{
// your Excel code here.
});
}
Task.WaitAll(tasks);
source to share
Create N tasks / threads to process, where "N" is the number of cores on your computer. Give each task / thread one instance Excel
.
You won't be able to manage a single instance from multiple threads (at least not productively, it will only run one thread task at a time), and creating a large number of Excel instances is pretty inefficient.
source to share
As @Servy pointed out, you can create multiple streams with one Excel instance per stream.
Make sure each thread creates an Excel object that it uses.
Expect weirdness. With 10,000 files, you can probably expect minor problems on at least a few. If a hidden instance of Excel tries to call the user, it might appear frozen.
Word has some problems with some of the functions that call single threaded COM objects, I suspect Excel may have some of them too. In Word, this manifests itself in various ways, including freezing or closing an instance.
If the formatting is really simple and your files are xmlx, there might be some code written to apply the changes through the OOXML SDK, which doesn't require an actual instance of Excel
source to share