Random programming things I'd want to remember

Friday, April 15, 2011

Excel and multithreading

I was working on an application that opens an Excel spreadsheet, pulls some data out, manipulates it, and exports it into CSV. I ran into a couple of interesting things, they are worth mentioning.

All the computers that will run the code below need to have Microsoft Office and Microsoft Office PIA (Primary Interop Assemblies) installed. More info here: http://msdn.microsoft.com/en-us/library/15s06t57.aspx. Make sure to match the version of Microsoft Office and PIA components. Then, on the development machine, in the solution, add reference to Microsoft.Office.Interop.Excel dll, which, on my computer, was located at "C:\Program Files (x86)\Microsoft Visual Studio 10.0\Visual Studio Tools for Office\PIA\Office12\Microsoft.Office.Interop.Excel.dll".

My form looks like this:


The use of delegates make the application a bit more complicated, but it adds a nice option to display progress bars. I got the idea and some code here: http://msdn.microsoft.com/en-us/library/ms993020.aspx. The only thing I added is the call on line 69 for a method that would start as soon as the ProcessData method finishes working.

The other thing I changed was disabling the minimize/maximize/close buttons for the form (line 19). The reason for that is if the user quits application by clicking the close button in the top right corner, Excel process still stays in memory. If the user exits the application using the "Close" button that triggers the event on line 72, Excel process is properly disposed.

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using Excel = Microsoft.Office.Interop.Excel;
using System.Threading;

namespace MultiThreadExcel
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
            this.ControlBox = false;
        }

        private Excel.Application app = null;
        private Excel.Workbook workbook = null;
        private Excel.Worksheet worksheet = null;
        private Excel.Sheets sheets;
        string fileName = @"D:\test.xlsx";

        delegate void ProcessDataDelegate(int linesToRead);
        delegate void ShowProgressDelegate(string progressMessage, int totalDigits, int digitsSoFar);
        
        public void AddComplete(IAsyncResult itfAR)
        {
            if (workbook != null)
                workbook.Close(false, fileName, false);
            if (app != null)
                app.Quit();
        }

        private void ProcessData(int linesToRead)
        {
            app = new Excel.Application();
            workbook = app.Workbooks.Open(fileName, 0, false, 5, "", "", false, Excel.XlPlatform.xlWindows, "", true, false, 0, true, false, false);
            sheets = workbook.Worksheets;
            worksheet = (Excel.Worksheet)sheets.get_Item(1);
            for (int i = 0; i < linesToRead; i++)
            {
                ShowProgress("Processing...", linesToRead, (i+1));
                Thread.Sleep(50);
            }
        }

        void ShowProgress(string progressMessage, int totalDigits, int digitsSoFar)
        {
            if (progressBar1.InvokeRequired == false)
            {
                progressBar1.Maximum = totalDigits;
                progressBar1.Value = digitsSoFar;
            }
            else
            {
                ShowProgressDelegate showProgress = new ShowProgressDelegate(ShowProgress);
                BeginInvoke(showProgress, new object[] { progressMessage, totalDigits, digitsSoFar });
            }
        }

        private void btnStart_Click(object sender, EventArgs e)
        {
            ProcessDataDelegate proDa = new ProcessDataDelegate(ProcessData);
            IAsyncResult iftAR = proDa.BeginInvoke(100, new AsyncCallback(AddComplete), null);
        }

        private void btnClose_Click(object sender, EventArgs e)
        {
            Application.Exit();
        }
    }
}