How to export GridView to Excel, render cells, add WordArt, and fill a custom template

• Download demo project (includes source code) - 470 Kb

Introduction

There is not really much to say about this article except that it shows how to automate Microsoft Excel through C#. More precisley, what it shows is how to export a C# GridView/DataView to Excel, and how to format the resultant Excel spreadsheet cells. It also shows how to place WordArt and how to use a custom template, where a few values are filled in at predetermined cell positions.

The Associated GUI

Normally, I would say not to worry too much about the GUI, but in this one, there are some nice features like how do use the BackgroundWorker object and how to use Invoke to marshal threads to allow different threads to change GUI component properties. It also shows the use of anonomous delegates which is something new to .NET (old news in Java).

This is all really covered in the following areas.

//need to call InvokeRequired to check if thread 

//need marshalling, to get the thread onto 

//the same thread as the thread who owns the controls

if (this.InvokeRequired)
{
    this.Invoke(new EventHandler(delegate
    {
        progressBar1.Value = e.ProgressValue;
    }));
}
else
{
    progressBar1.Value = e.ProgressValue;
}

In this snippet, we can see two of the three points mentioned above (although this is nothing to do with Excel automation, it is code of interest I hope), but the export2Excel object raises an event, which is meant to update the GUI. But the GUI component handles were created on a different thread, so we need to use Invoke to get onto the correct thread to change the GUI component properties or call their methods, so why not chuck in an anonomous delegate to demo this.

The code is probably the best place to look at this, for a complete understanding. This article is really about the excel automation so lets stick to that. I just thought I mention this stuff here.

Excel office automation

So what does this code do??

Well it does two things:

• It exports a GridView to Excel and colors cells, applies formatting etc., and also adds some WordArt. All the normal Excel stuff that one might want to do. It then saves this as a new Excel document, as specified by the user.
• It also uses a premade template and puts some values in the correct cell positions and saves that as a new Excel document, as specified by the user.

Using the code

The demo project attached actually contains a Visual Studio 2005 solution, with the following three classes:

Program class

This is the main entry point into the GridviewToExcel application. Essentially, all this class does is create a new Form1 object.

using System;
using System.Collections.Generic;
using System.Windows.Forms;

namespace GridviewToExcel
{
    static class Program
    {
        /// <SUMMARY>

        /// The main entry point for the application.

        /// </SUMMARY>

        [STAThread]
        static void Main()
        {
            Application.EnableVisualStyles();
            Application.SetCompatibleTextRenderingDefault(false);
            Application.Run(new Form1());
        }
    }
}

Form1 class (Designer code not shown, see zip file)

Queries access database (part of the zip) to create a new GridView, and exports the data to a new Excel document, or creates a new Excel document from an existing template, based on what button was clicked.

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;
using System.Data.OleDb;
using System.IO;


namespace GridviewToExcel
{
    public partial class Form1 : Form
    {
        //instance fields

        private export2Excel export2XLS;
        private DataSet _dataSet;

        public Form1()
        {
            InitializeComponent();
        }

        //load the gridview using the local access database

        private void Form1_Load(object sender, EventArgs e)
        {

            Directory.SetCurrentDirectory(Application.StartupPath + 
                                          @"..\..\..\");
            String accessPath = Directory.GetCurrentDirectory() + 
                                @"\Northwind.mdb";

            // Set the connection and sql strings

            // assumes your mdb file is in your root

            string connString = 
              @"Provider=Microsoft.JET.OLEDB.4.0;data source=" + accessPath;
            string sqlString = "SELECT * FROM customers";

            OleDbDataAdapter dataAdapter = null;
            _dataSet = null;

            try
            {
                // Connection object

                OleDbConnection connection = new OleDbConnection(connString);

                // Create data adapter object

                dataAdapter = new OleDbDataAdapter(sqlString, connection);

                // Create a dataset object and fill with data

                // using data adapter's Fill method

                _dataSet = new DataSet();
                dataAdapter.Fill(_dataSet, "customers");
                connection.Close();
            }
            catch (Exception ex)
            {
                MessageBox.Show("Problem with DB access-\n\n   connection: "
                    + connString + "\r\n\r\n            query: " + sqlString
                    + "\r\n\r\n\r\n" + ex.ToString());
                this.Close();
                return;
            }

            DataView dvCust = _dataSet.Tables["customers"].DefaultView;
            dg1.DataSource = dvCust;

        }

        //Do a straight export to a new Excel document 

        //of the gridviews grid data

        private void btn2Excel_Click(object sender, EventArgs e)
        {

            //show a file save dialog and ensure the user selects

            //correct file to allow the export

            saveFileDialog1.Filter = "Excel (*.xls)|*.xls";
            if (saveFileDialog1.ShowDialog() == DialogResult.OK)
            {
                if (!saveFileDialog1.FileName.Equals(String.Empty))
                {
                    FileInfo f = new FileInfo(saveFileDialog1.FileName);
                    if (f.Extension.Equals(".xls"))
                    {
                        StartExport(saveFileDialog1.FileName);
                    }
                    else
                    {
                        MessageBox.Show("Invalid file type");
                    }
                }
                else
                {
                    MessageBox.Show("You did pick a location " + 
                                    "to save file to");
                }
            }
        }

        //starts the export to new excel document

        //@param filepath : the file to export to

        private void StartExport(String filepath)
        {
            btn2Excel.Enabled = false;
            btnUseTemplate.Enabled = false;
            //create a new background worker, to do the exporting

            BackgroundWorker bg = new BackgroundWorker();
            bg.DoWork += new DoWorkEventHandler(bg_DoWork);
            bg.RunWorkerCompleted += 
               new RunWorkerCompletedEventHandler(bg_RunWorkerCompleted);
            bg.RunWorkerAsync(filepath);

            //create a new export2XLS object, providing 

            //DataView as a input parameter

            export2XLS = new export2Excel();
            export2XLS.prg += 
                new export2Excel.ProgressHandler(export2XLS_prg);
        }

        //do the new excel document work using the background worker

        private void bg_DoWork(object sender, DoWorkEventArgs e)
        {
            //get the Gridviews DataView

            DataView dv = _dataSet.Tables["customers"].DefaultView;
            //Pass the path and the sheet to use

            export2XLS.ExportToExcel(dv, (String)e.Argument, "newSheet1");
        }

        //Do a export to a new Excel document, 

        //with the use of a custom template

        private void btnUseTemplate_Click(object sender, EventArgs e)
        {
            //show a file save dialog and ensure the user selects

            //correct file to allow the export

            saveFileDialog1.Filter = "Excel (*.xls)|*.xls";
            if (saveFileDialog1.ShowDialog() == DialogResult.OK)
            {
                if (!saveFileDialog1.FileName.Equals(String.Empty))
                {
                    FileInfo f = new FileInfo(saveFileDialog1.FileName);
                    if (f.Extension.Equals(".xls"))
                    {
                        StartExportUseTemplate(saveFileDialog1.FileName);
                    }
                    else
                    {
                        MessageBox.Show("Invalid file type");
                    }
                }
                else
                {
                    MessageBox.Show("You did pick a location" + 
                                    " to save file to");
                }
            }
        }

        //Create a new excel document from a given template

        //@param filepath : the file to export to

        private void StartExportUseTemplate(String filepath)
        {
            btn2Excel.Enabled = false;
            btnUseTemplate.Enabled = false;
            //create a new background worker, to do the exporting

            BackgroundWorker bg = new BackgroundWorker();
            bg.DoWork += new DoWorkEventHandler(bg_DoWorkUseTemplate);
            bg.RunWorkerCompleted += 
               new RunWorkerCompletedEventHandler(bg_RunWorkerCompleted);
            bg.RunWorkerAsync(filepath);

            //create a new export2XLS object, providing 

            //DataView as a input parameter

            export2XLS = new export2Excel();
            export2XLS.prg += 
             new export2Excel.ProgressHandler(export2XLS_prg);
        }

        //Do the adding to custom template to create new excel document, 

        //work using the background worker

        private void bg_DoWorkUseTemplate(object sender, DoWorkEventArgs e)
        {
            //create some data to whack in the template

            String[,] templateValues =  {    { "task1", 
                      "CompletedBy1", "CompletedDate1" }, 
                      { "task2", "CompletedBy2", "CompletedDate2" } 
                                        };

            //The template path to use

            Directory.SetCurrentDirectory(Application.StartupPath + 
                                          @"..\..\..\");
            String templatePath = Directory.GetCurrentDirectory() + 
                                  @"\TaskList.xlt";

            //Pass the template path and the test values and fill 

            //the template

            export2XLS.UseTemplate((String)e.Argument, templatePath, 
                                    templateValues);
        }

        //Update the progress bar with the a value

        private void export2XLS_prg(object sender, ProgressEventArgs e)
        {
            //need to call InvokeRequired to check 

            //if thread need marshalling, to get the thread onto 

            //the same thread as the thread who owns the controls

            if (this.InvokeRequired)
            {
                this.Invoke(new EventHandler(delegate
                {
                    progressBar1.Value = e.ProgressValue;
                }));
            }
            else
            {
                progressBar1.Value = e.ProgressValue;
            }
        }

        //show a message to the user when the background worker has finished

        //and re-enable the export buttons

        private void bg_RunWorkerCompleted(object sender, 
                        RunWorkerCompletedEventArgs e)
        {
            btn2Excel.Enabled = true;
            btnUseTemplate.Enabled = true;
            MessageBox.Show("Finished");
        }
    }
}

export2Excel class does the excel automation

This is where all the real office automation stuff occurs.

using System;
using System.Collections;
using System.ComponentModel;
using System.Windows.Forms;
using System.Data;
using System.IO;
using Microsoft.Office.Interop.Excel;
using Microsoft.Office.Core;
using System.Runtime.InteropServices; // For COMException

using System.Reflection; // For Missing.Value and BindingFlags

using System.Diagnostics; // to ensure EXCEL process is really killed


namespace GridviewToExcel
{
    #region export2Excel CLASS
    /// This class processes the DataView that it is provided and

    /// Exports this DataView to an Excel document.

    public class export2Excel
    {

        #region InstanceFields
        //Instance Fields

        public delegate void ProgressHandler(object sender, 
                             ProgressEventArgs e);
        public event ProgressHandler prg;
        private DataView dv;
        private Style styleRows;
        private Style styleColumnHeadings;
        private Microsoft.Office.Interop.Excel.Application EXL;
        private Workbook workbook;
        private Sheets sheets;
        private Worksheet worksheet;
        private string[,] myTemplateValues;
        private int position;
        #endregion
        
        #region Constructor
        //Constructs a new export2Excel object. The user must

        //call the createExcelDocument method once a valid export2Excel

        //object has been instantiated

        public export2Excel()
        {

        }
        #endregion

        #region EXCEL : ExportToExcel
        //Exports a DataView to Excel. The following steps are carried out

        //in order to export the DataView to Excel

        //Create Excel Objects

        //Create Column & Row Workbook Cell Rendering Styles

        //Fill Worksheet With DataView

        //Add Auto Shapes To Excel Worksheet

        //Select All Used Cells

        //Create Headers/Footers

        //Set Status Finished

        //Save workbook & Tidy up all objects

        //@param dv : DataView to use

        //@param path : The path to save/open the EXCEL file to/from

        //@param sheetName : The target sheet within the EXCEL file

        public void ExportToExcel(DataView dv,string path, string sheetName)
        {
            try
            {
                //Assign Instance Fields

                this.dv = dv;

                #region NEW EXCEL DOCUMENT : Create Excel Objects

                //create new EXCEL application

                EXL = new Microsoft.Office.Interop.Excel.ApplicationClass();
                //index to hold location of the requested sheetName 

                //in the workbook sheets

                //collection

                int indexOfsheetName;

                #region FILE EXISTS
                //Does the file exist for the given path

                if (File.Exists(path))
                {

                    //Yes file exists, so open the file

                    workbook = EXL.Workbooks.Open(path,
                        0, false, 5, "", "", false, 
                        Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, 
                        "", true, false, 0, true, false, false);

                    //get the workbook sheets collection

                    sheets = workbook.Sheets;

                    //set the location of the requested sheetName to -1, 

                    //need to find where

                    //it is. It may not actually exist

                    indexOfsheetName = -1;

                    //loop through the sheets collection

                    for (int i = 1; i <= sheets.Count; i++)
                    {
                        //get the current worksheet at index (i)

                        worksheet = (Worksheet)sheets.get_Item(i);

                        //is the current worksheet the sheetName 

                        //that was requested

                        if (worksheet.Name.ToString().Equals(sheetName))
                        {
                            //yes it is, so store its index

                            indexOfsheetName = i;

                            //Select all cells, and clear the contents

                            Microsoft.Office.Interop.Excel.Range myAllRange = 
                                                             worksheet.Cells;
                            myAllRange.Select();
                            myAllRange.CurrentRegion.Select();
                            myAllRange.ClearContents();
                        }
                    }

                    //At this point it is known that the sheetName 

                    //that was requested

                    //does not exist within the found file, 

                    //so create a new sheet within the

                    //sheets collection

                    if (indexOfsheetName == -1)
                    {
                        //Create a new sheet for the requested sheet

                        Worksheet sh = (Worksheet)workbook.Sheets.Add(
                            Type.Missing, 
                           (Worksheet)sheets.get_Item(sheets.Count),
                            Type.Missing, Type.Missing);
                        //Change its name to that requested

                        sh.Name = sheetName;
                    }
                }
                #endregion

                #region FILE DOESNT EXIST
                //No the file DOES NOT exist, so create a new file

                else
                {
                    //Add a new workbook to the file

                    workbook = 
                      EXL.Workbooks.Add(XlWBATemplate.xlWBATWorksheet);
                    //get the workbook sheets collection

                    sheets = workbook.Sheets;
                    //get the new sheet

                    worksheet = (Worksheet)sheets.get_Item(1);
                    //Change its name to that requested

                    worksheet.Name = sheetName;
                }
                #endregion

                #region get correct worksheet index for requested sheetName

                //get the workbook sheets collection

                sheets = workbook.Sheets;

                //set the location of the requested sheetName 

                //to -1, need to find where

                //it is. It will definately exist now 

                //as it has just been added

                indexOfsheetName = -1;

                //loop through the sheets collection

                for (int i = 1; i <= sheets.Count; i++)
                {
                    //get the current worksheet at index (i)

                    worksheet = (Worksheet)sheets.get_Item(i);

                    //is the current worksheet the sheetName 

                    //that was requested

                    if (worksheet.Name.ToString().Equals(sheetName))
                    {
                        //yes it is, so store its index

                        indexOfsheetName = i;
                    }
                }

                //set the worksheet that the DataView should 

                //write to, to the known index of the

                //requested sheet

                worksheet = (Worksheet)sheets.get_Item(indexOfsheetName);
                #endregion

                #endregion

                // Set styles 1st

                SetUpStyles();
                //Fill EXCEL worksheet with DataView values

                fillWorksheet_WithDataView();
                //Add the autoshapes to EXCEL

                AddAutoShapesToExcel();
                //Select all used cells within current worksheet

                SelectAllUsedCells();

                try
                {
                    workbook.Close(true, path, Type.Missing);
                    EXL.UserControl = false;
                    EXL.Quit();
                    EXL = null;
                    //kill the EXCEL process as a safety measure

                    killExcel();
                    // Show that processing is finished

                    ProgressEventArgs pe = new ProgressEventArgs(100);
                    OnProgressChange(pe);
                    MessageBox.Show("Finished adding " + 
                      "dataview to Excel", "Info", 
                      MessageBoxButtons.OK, 
                      MessageBoxIcon.Information);
                }
                catch (COMException cex)
                {
                    MessageBox.Show("User closed Excel manually, " + 
                                    "so we don't have to do that");
                }
                catch (Exception ex)
                {
                    MessageBox.Show("Error " + ex.Message);
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show("Error " + ex.Message);
            }
        }
        #endregion
        
        #region EXCEL : UseTemplate
        //Exports a DataView to Excel. The following steps are carried out

        //in order to export the DataView to Excel

        //Create Excel Objects And Open Template File

        //Select All Used Cells

        //Create Headers/Footers

        //Set Status Finished

        //Save workbook & Tidy up all objects

        //@param path : The path to save/open the EXCEL file to/from

        public void UseTemplate(string path, string templatePath, 
                                string[,] myTemplateValues)
        {
            try
            {
                this.myTemplateValues = myTemplateValues;
                //create new EXCEL application

                EXL = new Microsoft.Office.Interop.Excel.ApplicationClass();
                //Yes file exists, so open the file

                workbook = EXL.Workbooks.Open(templatePath,
                    0, false, 5, "", "", false, 
                    Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "",
                    true, false, 0, true, false, false);
                //get the workbook sheets collection

                sheets = workbook.Sheets;
                //get the new sheet

                worksheet = (Worksheet)sheets.get_Item(1);
                //Change its name to that requested

                worksheet.Name = "ATemplate";
                //Fills the Excel Template File Selected With A 2D Test Array

                fillTemplate_WithTestValues();
                //Select all used cells within current worksheet

                SelectAllUsedCells();

                try
                {
                    workbook.Close(true, path, Type.Missing);
                    EXL.UserControl = false;
                    EXL.Quit();
                    EXL = null;
                    //kill the EXCEL process as a safety measure

                    killExcel();
                    // Show that processing is finished

                    ProgressEventArgs pe = new ProgressEventArgs(100);
                    OnProgressChange(pe);
                    MessageBox.Show("Finished adding test values to " + 
                                    "Template", "Info", 
                                    MessageBoxButtons.OK, 
                                    MessageBoxIcon.Information);

                }
                catch (COMException)
                {
                    Console.WriteLine("User closed Excel manually," + 
                                      " so we don't have to do that");
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show("Error " + ex.Message);
            }
        }
        #endregion

        #region STEP 1 : Create Column & Row Workbook Cell Rendering Styles
        //Creates 2 Custom styles for the workbook These styles are

        //  styleColumnHeadings

        //  styleRows

        //These 2 styles are used when filling 

        //the individual Excel cells with the

        //DataView values. If the current cell relates 

        //to a DataView column heading

        //then the style styleColumnHeadings will be used 

        //to render the current cell.

        //If the current cell relates to a DataView row 

        //then the style styleRows will

        //be used to render the current cell.

        private void SetUpStyles()
        {
            // Style styleColumnHeadings

            try
            {
                styleColumnHeadings = workbook.Styles["styleColumnHeadings"];
            }
            // Style doesn't exist yet.

            catch
            {
                styleColumnHeadings = 
                   workbook.Styles.Add("styleColumnHeadings", Type.Missing);
                styleColumnHeadings.Font.Name = "Arial";
                styleColumnHeadings.Font.Size = 14;
                styleColumnHeadings.Font.Color = 
                  (255 << 16) | (255 << 8) | 255;
                styleColumnHeadings.Interior.Color = 
                  (0 << 16) | (0 << 8) | 0;
                styleColumnHeadings.Interior.Pattern = 
                   Microsoft.Office.Interop.Excel.XlPattern.xlPatternSolid;
            }
            // Style styleRows

            try
            {

                styleRows = workbook.Styles["styleRows"];
            }
            // Style doesn't exist yet.

            catch
            {
                styleRows = workbook.Styles.Add("styleRows", Type.Missing);
                styleRows.Font.Name = "Arial";
                styleRows.Font.Size = 10;
                styleRows.Font.Color = (0 << 16) | (0 << 8) | 0;
                styleRows.Interior.Color = 
                    (192 << 16) | (192 << 8) | 192;
                styleRows.Interior.Pattern = 
                     Microsoft.Office.Interop.Excel.XlPattern.xlPatternSolid;
            }
        }
        #endregion

        #region STEP 2 : Fill Worksheet With DataView
        //Fills an Excel worksheet with the values contained in the DataView

        //parameter

        private void fillWorksheet_WithDataView()
        {
            position = 0;
            //Add DataView Columns To Worksheet

            int row = 1;
            int col = 1;
            // Loop thought the columns

            for (int i = 0; i < dv.Table.Columns.Count; i++)
            {

                fillExcelCell(worksheet, row, col++, 
                              dv.Table.Columns[i].ToString(), 
                              styleColumnHeadings.Name);
            }

            //Add DataView Rows To Worksheet

            row = 2;
            col = 1;

            for (int i = 0; i < dv.Table.Rows.Count; i++)
            {

                for (int j = 0; j < dv.Table.Columns.Count; j++)
                {
                    fillExcelCell(worksheet, row, col++, dv[i][j].ToString(),
                                  styleRows.Name);
                }
                col = 1;
                row++;

                position = (100 / dv.Table.Rows.Count) * row + 2;
                ProgressEventArgs pe = new ProgressEventArgs(position);
                OnProgressChange(pe);

            }
        }
        #endregion

        #region STEP 3 : Fill Individual Cell and Render Using Predefined Style
        //Formats the current cell based on the Style setting parameter name

        //provided here

        //@param worksheet : The worksheet

        //@param row : Current row

        //@param col : Current Column

        //@param Value : The value for the cell

        //@param StyleName : The style name to use

        private void fillExcelCell(Worksheet worksheet, int row, int col,
                                   Object Value, string StyleName)
        {
            Range rng = (Range)worksheet.Cells[row, col];
            rng.Select();
            rng.Value2 = Value.ToString();
            rng.Style = StyleName;
            rng.Columns.EntireColumn.AutoFit();
            rng.Borders.Weight = XlBorderWeight.xlThin;
            rng.Borders.LineStyle = XlLineStyle.xlContinuous;
            rng.Borders.ColorIndex = XlColorIndex.xlColorIndexAutomatic;
        }
        #endregion

        #region STEP 4 : Add Auto Shapes To Excel Worksheet
        //Add some WordArt objecs to the Excel worksheet

        private void AddAutoShapesToExcel()
        {

            //Method fields

            float txtSize = 80;
            float Left = 100.0F;
            float Top = 100.0F;
            //Have 2 objects

            int[] numShapes = new int[2];
            Microsoft.Office.Interop.Excel.Shape[] myShapes = 
              new Microsoft.Office.Interop.Excel.Shape[numShapes.Length];

            try
            {
                //loop through the object count

                for (int i = 0; i < numShapes.Length; i++)
                {

                    //Add the object to Excel

                    myShapes[i] = 
                     worksheet.Shapes.AddTextEffect(
                     MsoPresetTextEffect.msoTextEffect1, "DRAFT", 
                     "Arial Black", txtSize, MsoTriState.msoFalse, 
                     MsoTriState.msoFalse, (Left * (i * 3)), Top);

                    //Manipulate the object settings

                    myShapes[i].Rotation = 45F;
                    myShapes[i].Fill.Visible = 
                     Microsoft.Office.Core.MsoTriState.msoFalse;
                    myShapes[i].Fill.Transparency = 0F;
                    myShapes[i].Line.Weight = 1.75F;
                    myShapes[i].Line.DashStyle = 
                      MsoLineDashStyle.msoLineSolid;
                    myShapes[i].Line.Transparency = 0F;
                    myShapes[i].Line.Visible = 
                       Microsoft.Office.Core.MsoTriState.msoTrue;
                    myShapes[i].Line.ForeColor.RGB = 
                      (0 << 16) | (0 << 8) | 0;
                    myShapes[i].Line.BackColor.RGB = 
                      (255 << 16) | (255 << 8) | 255;
                }
            }
            catch (Exception ex)
            {
            }

        }
        #endregion

        #region STEP 5 : Select All Used Cells
        //Selects all used cells for the Excel worksheet

        private void SelectAllUsedCells()
        {

            Microsoft.Office.Interop.Excel.Range myAllRange = worksheet.Cells;
            myAllRange.Select();
            myAllRange.CurrentRegion.Select();

        }
        #endregion

        #region STEP 6 : Fill Template With Test Values
        //Fills the Excel Template File Selected With 

        //A 2D Test Array parameter

        private void fillTemplate_WithTestValues()
        {
            //Initilaise the correct Start Row/Column to match the Template

            int StartRow = 3;
            int StartCol = 2;

            position=0;

            // Display the array elements within the Output 

            // window, make sure its correct before

            for (int i=0; i <= myTemplateValues.GetUpperBound(0); i++) 
            {
                //loop through array and put into EXCEL template

                for (int j = 0 ; 
                     j <= myTemplateValues.GetUpperBound(1) ; 
                     j++)
                {
                    //update position in progress bar

                    position = (100 / myTemplateValues.Length) * i;
                    ProgressEventArgs pe = new ProgressEventArgs(position);
                    OnProgressChange(pe);

                    //put into EXCEL template

                    Range rng = (Range)worksheet.Cells[StartRow,StartCol++];
                    rng.Select();
                    rng.Value2 = myTemplateValues[i,j].ToString();
                    rng.Rows.EntireRow.AutoFit();
                }
                //New row, so column needs to be reset

                StartCol=2;
                StartRow++;
            }
        }

        #endregion

        #region Kill EXCEL
        //As a safety check go through all processes and make

        //doubly sure excel is shutdown. Working with COM

        //have sometimes noticed that the EXL.Quit() call

        //doesn't always do the job

        private void killExcel()
        {
            try
            {
                Process[] ps = Process.GetProcesses();
                foreach (Process p in ps)
                {
                    if (p.ProcessName.ToLower().Equals("excel"))
                    {
                        p.Kill();
                    }
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show("ERROR " + ex.Message);
            }
        }
        #endregion

        #region Events
        /// Raises the OnProgressChange event for the parent form. 

        public virtual void OnProgressChange(ProgressEventArgs e)
        {
            if (prg != null)
            {
                // Invokes the delegates. 

                prg(this, e);
            }
        }
        #endregion
    }
    #endregion
    #region ProgressEventArgs CLASS
    /// Provides the ProgressEventArgs 

    public class ProgressEventArgs : EventArgs
    {
        #region Instance Fields
        //Instance fields

        private int prgValue = 0;
        #endregion
        #region Public Constructor
        /// Constructs a new ProgressEventArgs object

        /// using the parameters provided

        /// @param prgValue : new progress value

        public ProgressEventArgs(int prgValue)
        {
            this.prgValue = prgValue;
        }
        #endregion
        #region Public Methods/Properties

        /// Returns the progress value

        public int ProgressValue
        {
            get { return prgValue; }
        }
        #endregion

    }
    #endregion

}

The result when creating a new Excel file:

The results when using an existing template:

Points of Interest

I hope this article has demonstrated how to write some Office automation using C#. I find the best way to deal with this sort of thing is to fire up Office, record a VBA macro, and convert it from there. This is what I did for this application.

History

• v1.0 09/11/06