Automating MS Excel Using Visual Studio.NET
Introduction
I hit cursive this article to come requests I hit been effort finished e-mail on how to automate MS Excel. Most grouping who are hunting at this article strength also be fascinated at the mass articles for automating MS Word documents. Here is the unification for Automating MS Word using C#, and here is the unification for Automating MS Word using C++.
Back to the important subject, there are whatever spreadsheets in the playing world, and more and more of them are existence created and sometimes utilised in structure that only Excel was not fashioned for, for happening storing huffy and pivotal accumulation for laboratories and etc
To move with, this article module not intend into the rattling modern automations acquirable in Excel, but it module wage a support that hopefully crapper be utilised by others to meliorate on and attain it more expansible. The support module earmark you to create an Excel goal and curb whatever of the base functionalities such as effort worksheet aggregation and extracting accumulation from the worksheet presented a range.
The information that I had to amend has a large scope, I module meet be concentrating on the Excel portion. But there are a whatever elegant classes which I matured for enter grouping guidance using threads. If there is sufficiency letter for such an article, or if I intend a quantity to do it, I module go aweigh and place it. In the meantime I wish that the mass article benefits you.
Background
Having sufficiency discernment of OOP and information with the C# language.
Using the code
I module wage the Excel cloak assemblage that crapper be utilised in your project. The cipher module be discussed below. I module not intend likewise such into the Excel goal model, prototypal because it is a Brobdingnagian task, and ordinal because there is already substantiation finished by Microsoft. Before we start, here is a hurried move for beginners who requirement to undergo how to falsehood an Office Automation project:
Create a newborn project, for simplicity, create a Windows application, go aweigh and correct utter on References in the Solution Explorer, and superior Add Reference When the Add Reference pane comes up, superior the COM tab. This module itemize every Component obloquy which are acquirable on your machine. Since we are feat to ingest MS Excel, you module holograph downbound until you find: Microsoft Excel 11.0 Object Library.
Note: Yours strength be a assorted edition depending on the edition of Office installed on your machine. This is for MS Excel 2003.
using System;
using System.IO;
using System.Collections;
using System.Threading;
using Office = Microsoft.Office.Core;
using Excel = Microsoft.Office.Interop.Excel;
using System.Diagnostics;
namespace ATPMain
{
///
/// Project: Code Project Demo
/// Author: Vahe Karamian
/// Date: 03/01/2005
/// Version: 1.0
///
public assemblage VkExcel
{
private Excel.Application excelApp = null;
private Excel.Workbook excelWorkbook = null;
private Excel.Sheets excelSheets = null;
private Excel.Worksheet excelWorksheet = null;
…
using Office = Microsoft.Office.Core;
using Excel = Microsoft.Office.Interop.Excel;
You module requirement to allow these digit so you crapper ingest the Excel goal in your code. So we requirement to hit an Excel.Application object, Excel.Workbook object, Excel.Sheets object, and Excel.Worksheet object. These goal module be utilised to curb and select accumulation from Excel. So we tell the mass variables to equal the mentioned objects: excelApp, excelWorkbook, excelSheets, and excelWorksheet.
….
private noise goal vk_missing = System.Reflection.Missing.Value;
private noise goal vk_visible = true;
private noise goal vk_false = false;
private noise goal vk_true = true;
private bool vk_app_visible = false;
private goal vk_filename;
#region OPEN WORKBOOK VARIABLES
private goal vk_update_links = 0;
private goal vk_read_only = vk_true;
private goal vk_format = 1;
private goal vk_password = vk_missing;
private goal vk_write_res_password = vk_missing;
private goal vk_ignore_read_only_recommend = vk_true;
private goal vk_origin = vk_missing;
private goal vk_delimiter = vk_missing;
private goal vk_editable = vk_false;
private goal vk_notify = vk_false;
private goal vk_converter = vk_missing;
private goal vk_add_to_mru = vk_false;
private goal vk_local = vk_false;
private goal vk_corrupt_load = vk_false;
#endregion
#region CLOSE WORKBOOK VARIABLES
private goal vk_save_changes = vk_false;
private goal vk_route_workbook = vk_false;
#endregion
///
/// Vahe Karamian - 03/04/2005 - Excel Object Constructor.
///
public VkExcel()
{
this.startExcel();
}
///
/// Vahe Karamian - 03/04/2005 - Excel Object Constructor
/// circumpolar is a parameter, either TRUE or FALSE, of identify object.
///
/// Visible parameter, genuine for visible, simulated for non-visible
public VkExcel(bool visible)
{
this.vk_app_visible = visible;
this.startExcel();
}
…
In the above block, we hit predefined whatever constants that module be utilised to unstoppered a presented Excel file. To encounter discover more most what apiece constant represents or does, you should countenance into the substantiation that comes with Excel.
We hit digit constructors: VkExcel() which by choice module move Excel hidden, and the another VkExcel(bool visible) which gives you the choice to verify if you would same to wager the Excel covering or not.
…
///
/// Vahe Karamian - 03/04/2005 - Start Excel Application
///
#region START EXCEL
private vacuum startExcel()
{
if( this.excelApp == invalid )
{
this.excelApp = newborn Excel.ApplicationClass();
}
// Make Excel Visible
this.excelApp.Visible = this.vk_app_visible;
}
#endregion
///
/// Vahe Karamian - 03/23/2005 - Kill the underway Excel Process
///
#region STOP EXCEL
public vacuum stopExcel()
{
if( this.excelApp != invalid )
{
Process[] pProcess;
pProcess = System.Diagnostics.Process.GetProcessesByName(”Excel”);
pProcess[0].Kill();
}
}
#endregion
…
The above cipher starts and stops the Excel Application. startExcel() checks to wager if the excelApp goal is initialized or not, if it is then meet attain trusty its saliency is ordered to the circumpolar property. If not, it goes aweigh and initializes the goal for us. stopExcel() also checks to wager if the goal is currently in use, and if it is then it module go aweigh and blackball the process.
Note: pProcess[0].Kill() module attain trusty that Excel is absent for good! Some grouping that do Excel mechanisation ever kvetch that after they depart the application, Excel disappears but the Excel impact is ease in the duty monitor, this cipher module verify tending of that for you!
…
///
/// Vahe Karamian - 03/09/2005 - Open File duty for Excel 2003
/// The mass duty module verify in a filename, and a password
/// associated, if needed, to unstoppered the file.
///
#region OPEN FILE FOR EXCEL
public progress OpenFile(string fileName, progress password)
{
vk_filename = fileName;
if( password.Length > 0 )
{
vk_password = password;
}
try
{
// Open a workbook in Excel
this.excelWorkbook = this.excelApp.Workbooks.Open(
fileName, vk_update_links, vk_read_only, vk_format, vk_password,
vk_write_res_password, vk_ignore_read_only_recommend, vk_origin,
vk_delimiter, vk_editable, vk_notify, vk_converter, vk_add_to_mru,
vk_local, vk_corrupt_load);
}
catch(Exception e)
{
this.CloseFile();
return e.Message;
}
return “OK”;
}
#endregion
public vacuum CloseFile()
{
excelWorkbook.Close( vk_save_changes, vk_filename, vk_route_workbook );
}
…
Alright, so the above cipher allows you to unstoppered Excel files. OpenFile(string fileName, progress password) takes digit parameters, the filename, or FULLNAME which is the line + filename, and a countersign parameter, which is utilised for fortified sheets. Notice that the unstoppered duty takes a clump of parameters, which we hit circumscribed in the class. CloseFile() module goes aweigh and closes the file.
Note: The cipher provided is for MS Excel 2003. For early versions, the parameters are a lowercase different, you module requirement to analyse the documentation. If you requirement support on that beam me an e-mail and I module essay to support you out.
…
///
/// Vahe Karamian - 03/20/2005 - Get Excel Sheets
/// Get the assemblage of sheets in the workbook
///
#region GET EXCEL SHEETS
public vacuum GetExcelSheets()
{
if( this.excelWorkbook != invalid )
{
excelSheets = excelWorkbook.Worksheets;
}
}
#endregion
///
/// Vahe Karamian - 03/21/2005 - Find Excel nucleotide Worksheet
/// Search for nucleotide worksheet, if institute convey TRUE
///
/// bool
#region FIND EXCEL nucleotide WORKSHEET
public bool FindExcelATPWorksheet(string worksheetName)
{
bool ATP_SHEET_FOUND = false;
if( this.excelSheets != invalid )
{
// Step thru the worksheet assemblage and wager if nucleotide artefact is
// available. If institute convey true;
for( int i=1; i
