Background: I'm making a C# application to copy a range of cells into a bitmap image for reporting. All of my users have excel installed on their computer so the Excel COM interop is an easy way to access and render the sheets. I'd like the COM interop to work on a different thread so that it doesn't lock up my winforms UI. Initially, I tried background worker so that I can report progress to the UI as each sheet is processed. I am not trying to use multithreading to increase speed or to asynchronously process multiple workbooks. However, I've ran into two big issues.
- range.CopyPicture relies on the windows clipboard which causes a very sporadic race case.
- The excel interop is single threaded apartment but background worker uses a threadpool.
I've been reading into TAP (Task-based asynchronous pattern) and EAP (Event based asynchronous pattern) but I'm having trouble applying this info into my situation. What is the alternative that will allow both UI progress reporting and play nicely with the excel COM interop?
My current code using background worker:
private void worker_DoWork(object? sender, DoWorkEventArgs e)
{
excelApp = new Excel.Application();
Workbook workbook = excelApp.Workbooks.Open(workbookFilePath, ReadOnly: true);
Worksheet mySheet = workbook.Sheets[1];
worker.ReportProgress(10);
Bitmap image = GenerateImageFromWorksheet(mySheet);
image.Save(outputNamePath);
workbook.Close(0);
excelApp.Quit();
worker.ReportProgress(100);
}
private Bitmap GenerateImageFromWorksheet(Worksheet mySheet)
{
// A bunch of interop code
Excel.Range myRange = mySheet.Range[mySheet.Cells[1, 1], mySheet.Cells[row, column]];
myRange.CopyPicture(XlPictureAppearance.xlScreen, XlCopyPictureFormat.xlBitmap);
return new Bitmap(GetClipboardData());
}
EDIT: This is different from the related question because it provides more context about the actual application of the solution rather than how to apply the abstract method. I am not asking how to create a STA thread with message pump but rather what asynchronous programming model is compatible with my technology.