Populate an Excel Worksheet from .NET using the Excel object

You can format your data as a TAB delimited string, use the Excel object to start an instance of Microsoft Excel, select the worksheet and range to populate, and paste in your data.

To use the Excel object you must first set a COM reference to the Microsoft Excel Object Library which is an ActiveX component. Behind the scenes .NET uses the Interop.Excel DLL to talk to Excel. This DLL must be distributed with your application.

An issue exists with the Excel object when the user closes your application before closing the instance of Excel that you started. This instance becomes an orphaned process - i.e. when you look at the Processes tab in Windows Task Manager you will see a process exists for Excel.Exe yet there will be no visible Excel applications or windows present.

The VB.NET code below shows how to populate Excel and account for this scenario.

Module level variables in your form's Declarations section:

    '
    ' Variables to refer to the Excel Object and process.
    '
    Private myExcel As Excel.Application
    Private myExcelPID As Integer
    Private myDataSet As DataSet

Start Excel and get its process ID:

    '
    ' Verify the Excel file to populate exists.
    '
    Dim strExcelFile As String = "C:\MyApp\MyExcel.xls"
    If Not IO.File.Exists(strExcelFile) Then Return
    '
    ' Start Microsoft Excel and make it visible.
    '
    myExcel = New Excel.Application

    If myExcel Is Nothing Then
        Throw (New Exception("Unable to Start Microsoft Excel."))
    Else
        myExcel.Visible = True
        myExcel.WindowState = Excel.XlWindowState.xlMaximized
        '
        ' Get the process ID of the Excel instance. This is used
        ' in the Closing Event to prevent orphaned Excel processes.
        '
        Dim aProcesses() As Process = Process.GetProcesses
        Dim i As Integer

        For i = 0 To aProcesses.GetUpperBound(0)
            If aProcesses(i).MainWindowHandle.ToString = myExcel.Hwnd.ToString Then
                myExcelPID = aProcesses(i).Id
                Exit For
            End If
        Next
    End If

Open the XLS file and activate the worksheet to populate:

    Dim strWorkSheetName As String = "MyData"
    Dim sbExcelData As System.Text.StringBuilder
    Dim aWorkbook As Excel.Workbook
    Dim aWorkSheet As Excel.Worksheet

    aWorkbook = myExcel.Workbooks.Open(strExcelFile)
    aWorkSheet = CType(aWorkbook.Sheets(strWorkSheetName), Excel.Worksheet)
    aWorkSheet.Activate()

Define the Excel Range to populate:

    '
    ' Assume the data to send to Excel is in a dataset and it has 5 columns
    ' and will fill Excel starting with column C and row 4. 
    ' 
    Dim intRows As Integer = myDataSet.Tables("MyTable").Rows.Count - 1
    Dim strRangeString As String = "C4:G" & intRows.ToString
    Dim aRange As Excel.Range = aWorkSheet.Range(strRangeString)
    '
    ' Remove any existing data from Excel.
    '
    aRange.ClearContents()

Convert the data in the dataset to a string to paste into Excel:

    If Not ConvertDataForExcel(sbExcelData) Then
        Return
    End If

    Clipboard.SetDataObject(sbExcelData.ToString, False)
    aRange.Select()
    aWorkSheet.Paste()
    '
    ' Unselect the range.
    '
    aRange = aWorkSheet.Range("A1:A1")
    aRange.Select()

Convert the data to a TAB delimited string. The System.Text.StringBuilder is used for performance:

    Private Function ConvertDataForExcel( _
                   ByRef theExcelData As System.Text.StringBuilder) As Boolean
        Try
            Dim i As Integer
            Dim adr As DataRow
            Dim adt As DataTable = myDataSet.Tables("MyTable")

            For Each adr In adt.Rows
                For i = 0 to 4
                    '
                    ' Convert the data and fill the string. Null values become blanks.
                    '
                    If adr(i) Is DBNull.Value Then
                        theExcelData.Append("")
                    Else
                        theExcelData.Append(adr(i).ToString)
                    End If
                    theExcelData.Append(vbTab)
                Next
                '
                ' Add a line feed to the end of each row.
                '
                theExcelData.Append(vbCrLf)
            Next

        Catch ex As Exception
            ' Display an error message.
            Return False
        End Try

    End Function

Code to prevent orphaned Excel processes. First quit Excel then tell the InterOp layer to release the COM component:

    Private Sub Form1_Closing(ByVal sender As Object, _
            ByVal e As System.ComponentModel.CancelEventArgs) Handles MyBase.Closing

        Try
            '
            ' Close Excel. 
            '
            myExcel.Quit()
            System.Runtime.InteropServices.Marshal.ReleaseComObject(myExcel);

        Finally
            myExcel = Nothing
            '
            ' If this screen is closed before the Excel application
            ' there will be an orphan Excel process.
            '
            Try
                '
                ' Look for an Excel process matching the one we started.
                '
                Dim aProcesses() As Process = Process.GetProcesses
                Dim aProcess As Process
                Dim i As Integer

                For i = 0 To aProcesses.GetUpperBound(0)
                    If aProcesses(i).Id = myExcelPID Then
                        aProcess = aProcesses(i)
                        Exit For
                    End If
                Next

                Try
                    '
                    ' If we found one see if it has exited.
                    '
                    If Not (aProcess Is Nothing) Then
                        If aProcess.ProcessName.ToUpper = "EXCEL" Then
                            If Not aProcess.HasExited Then
                                '
                                ' It hasn't exited. If it has no main window we
                                ' have an orphaned process that we should kill.
                                '
                                If aProcess.MainWindowHandle.ToString = "0" Then
                                    aProcess.Kill()
                                Else
                                    '
                                    ' The Excel process has a main window meaning
                                    ' the Excel file is still open. Tell the user to
                                    ' close Excel before closing this screen.
                                    '
                                    e.Cancel = True
                                    MessageBox.Show( _
                                        "Please close Excel before closing this screen.", _
                                        "Warning", _
                                        MessageBoxButtons.OK, MessageBoxIcon.Exclamation)
                                End If
                            End If
                        End If
                    End If

                Catch

                End Try

            Catch ex As Exception

            End Try
        End Try
    End Sub

Here is a similar sample written in C#. This code is encapsulated in a separate class. Examples on how to call use this class are shown at the bottom of this page. This code dynamically determines the Excel range to use by decoding the Tab delimited data string passed to it. It uses one worksheet to store data and a second worksheet to store the database query that produced the data that was stored.

namespace iExcelClass
{
   public class iExcel
   {
      private Excel.Application _mExcelApp;
      private Excel.Workbook _mWorkbook;
      private Excel.Worksheet _mWorkSheet;
      private int _mintSheet1Row = 1;
      private int _mintSheet2Row = 1;
      private int _mExcelPID = 0;
      private bool _mblnExcelVisible = false;
      private string _mstrXLSFileFullName = "";
      private string _mstrWorkSheetName = "MyWkShtName";

      public iExcel()
	{
      }

      public void StartExcel(string XLSFileFullName)
      {
         //
         // Start Microsoft Excel, make it visible, add a worksheet.
         //
         _mExcelApp = new Excel.Application();

         if (_mExcelApp == null)
         {
            throw (new Exception("Unable to Start Microsoft Excel."));
         }

         _mstrXLSFileFullName = XLSFileFullName;
         _mExcelApp.Visible = _mblnExcelVisible;
         _mExcelApp.WindowState = Excel.XlWindowState.xlMinimized;
         _mWorkbook = _mExcelApp.Workbooks.Add(Excel.XlWBATemplate.xlWBATWorksheet);
         _mWorkSheet = (Excel.Worksheet) _mWorkbook.Worksheets.Add(
                                             Type.Missing, 
                                             Type.Missing, 
                                             Type.Missing, 
                                             Type.Missing);
         _mWorkSheet.Name = _mstrWorkSheetName;
         _mExcelApp.SheetsInNewWorkbook = 1;
         _mWorkSheet.Activate();
         //
         // Get the Excel instance's process ID...used later
         // to prevent orphaned Excel processes.
         //
         Process [] aProcesses = Process.GetProcesses();

         for (int i = 0; i <= aProcesses.GetUpperBound(0); i++)
         {
            if (aProcesses[i].MainWindowHandle.ToString() == _mExcelApp.Hwnd.ToString())
            {
               _mExcelPID = aProcesses[i].Id;
               break;
            }
         }				
      }

      public void AddRow(string TabDelimitedData)
      {
         //
         // Decipher the input string's characteristics to calculate the 
         // Excel Range to paste it into.
         //
         string [] cols = TabDelimitedData.Split('\t');
         if (cols.Length == 0)
         {
            return;
         }

         int i = 1;
         string strCol = "";

         while(i * 26 <= cols.Length)
         {
            i++;
         }

         if (i == 1)
         {
            strCol = Convert.ToChar(64 + cols.Length).ToString();
         }
         else
         {
            strCol = Convert.ToChar(64 + i - 1).ToString();
            strCol += Convert.ToChar(64 + cols.Length - ((i-1)*26)).ToString();
         }
         string strRangeString = 
             "A" + _mintSheet1Row.ToString() + ":" + strCol + _mintSheet1Row.ToString();

         //
         // Define the Range, clear it, paste the data.
         //
         Excel.Range _mRange = _mWorkSheet.get_Range(strRangeString, Type.Missing);
         _mRange.ClearContents();
         _mRange.Select();
         Clipboard.SetDataObject(TabDelimitedData, false);
         _mWorkSheet.Paste(Type.Missing, Type.Missing);
			
         //
         // Unselect the range.
         //
         _mRange = _mWorkSheet.get_Range("A1:A1", Type.Missing);
         _mRange.Select();
         _mintSheet1Row++;
      }

      public void AddQueryDetails(string TabDelimitedData)
      {
         Excel.Worksheet _WorkSheet = ((Excel.Worksheet) _mWorkbook.Sheets[2]);
         _WorkSheet.Name = "Query Details";
         _WorkSheet.Activate();
						
         //
         // Decipher the input string's characteristics to calculate the 
         // Excel Range to paste it into.
         //
         string [] cols = TabDelimitedData.Split('\t');
         if (cols.Length == 0)
         {
            return;
         }

         string strCol = Convert.ToChar(64 + cols.Length).ToString();
         string strRangeString = 
             "A" + _mintSheet2Row.ToString() + ":" + strCol + _mintSheet2Row.ToString();

         //
         // Define the Range, clear it, paste the data.
         //
         Excel.Range _Range = _WorkSheet.get_Range(strRangeString, Type.Missing);
         _Range.ClearContents();
         _Range.Select();
         Clipboard.SetDataObject(TabDelimitedData, false);
         _WorkSheet.Paste(Type.Missing, Type.Missing);
			
         //
         // Unselect the range.
         //
         _Range = _WorkSheet.get_Range("A1:A1", Type.Missing);
         _Range.Select();
         _mintSheet2Row++;
      }

      public void SaveFile()
      {
         //
         // Overwrite existing XLS file without alerting the user.
         //
         _mExcelApp.DisplayAlerts = false;

         _mExcelApp.ActiveWorkbook.SaveAs(
                           _mstrXLSFileFullName, 
                           Excel.XlFileFormat.xlXMLSpreadsheet, 
                           Type.Missing, 
                           Type.Missing, 
                           Type.Missing, 
                           Type.Missing, 
                           Excel.XlSaveAsAccessMode.xlNoChange,
                           Type.Missing, 
                           Type.Missing, 
                           Type.Missing, 
                           Type.Missing, 
                           Type.Missing);
      }

      public void CloseExcel()
      {
         try
         {
            //
            // Close the Excel application. 
            // Tell the stupid interop layer to release the object. 
            //
            _mExcelApp.Quit();
            System.Runtime.InteropServices.Marshal.ReleaseComObject(_mExcelApp);
         }

         finally
         {
            //
            // Prevent an orphaned Excel process by forcibly killing it.
            //
            _mExcelApp = null;
            Process [] aProcesses  = Process.GetProcesses();
            Process aProcess = null; 

            try
            {
               //
               // Look for an Excel process matching the one we started.
               //
               for (int i = 0; i <= aProcesses.GetUpperBound(0); i++)
               {
                  if (aProcesses[i].Id == _mExcelPID)
                  {
                     aProcess = aProcesses[i];
                     break;
                  }
               }

               try
               {
                 //
                  // If we found a matching Excel proceess with no main window
                  // associated main window, kill it.
                  //
                  if (aProcess != null) 
                  { 
                     if (aProcess.ProcessName.ToUpper() == "EXCEL") 
                     {
                        if (!aProcess.HasExited)
                           {
                              if (aProcess.MainWindowHandle.ToString() == "0")
                              {
                                 aProcess.Kill();
                              }
                              else
                              {
                                 //
                                 // The Excel process has a main window meaning the Excel
                                 // file is still open. Tell the user to close Excel.
                                 //
                                 MessageBox.Show("Please close Excel before closing this screen.", 
                                 "Warning", MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
                              }
                           }
                        }
                     }
                  }
                  catch {}
               }
            catch {}
         }
      }

      //
      // Public Properties
      //
      public string XLSFileFullName
      {
         get { return _mstrXLSFileFullName; }
      }
		
      public string WorkSheetName
      {
         get { return _mstrWorkSheetName; }
         set { _mstrWorkSheetName = value; }
      }
	
      public bool ExcelVisible
      {
         get { return _mblnExcelVisible; }
         set { _mblnExcelVisible = value; }
      }
   }
}
>

Sample Calls:

   iExcelClass.iExcel oExcel = new iExcelClass.iExcel();

   oExcel.WorkSheetName = "MyWorkSheet";
   oExcel.ExcelVisible = false;
   oExcel.StartExcel(@"C:\...\MyXLSFile.xls");
   //
   // Add a rows of data to Excel. Datastring is a Tab delimited string
   // such as: "aaa\tbbb\tccc"
   //
   for ...
   {
      oExcel.AddRow(DataString);
   }

   //
   // Add details of the query that produced the data to a second worksheet.
   //
   oExcel.AddQueryDetails("Date Run\tDatabase Name\tQuery Run");
   oExcel.AddQueryDetails("06-01-2004\tProduction\tSelect * FROM Table1");

   oExcel.SaveFile();
   oExcel.CloseExcel();








About TheScarms
About TheScarms


Sample code
version info

If you use this code, please mention "www.TheScarms.com"

Email this page



The World's Number 1 Web Host

© Copyright 2014 TheScarms
Goto top of page