Header Ads Widget

Automating MS Excel Using Visual Studio.NET

Introduction


I have written this article to address requests I received via email on how to use MS Excel. Many people who view this article may be interested in the following articles to make MS Word documents work. Here is a link to Automating MS Word using C #, and here is a link to Automating MS Word using C ++.



Going back to the main topic, there are many spreadsheets in the business world, and many of them are created and sometimes used in ways that Excel is not designed for, for example to store sensitive and important laboratory data and so on. .

First of all, this article will not go into the most advanced formats available in Excel, but will provide a framework in the hope that it can be used by others to improve in it and make it more visible. The framework will allow you to create an Excel object and manage other basic functions such as obtaining worksheet information and extracting data from a worksheet given a distance.

The program I was supposed to do is very broad, I will just focus on the Excel section. But there are a few clean sections that I have made for navigating the file system using threads. If there is a sufficient request for that article, or if I have the opportunity to do so, I will continue to send it. In the meantime I hope the following article will benefit you.

In the background

Adequate OOP understanding and familiarity with C # language.

Applying code

I will provide an Excel wrapper section that can be used in your project. The code will be discussed below. I will not go into the Excel object model, firstly because it is a big job, and secondly because there are already documents created by Microsoft. Before we begin, here is a quick start for beginners who need to be able to set up an Office Automation project:

Create a new project, for convenience, create a Windows app, go ahead and right-click on References in Solution Explorer, then select Install Reference ... When the Add Reference window appears, select the COM tab. This will list all Component names found in your machine. Since we will be using MS Excel, you will scroll down to find: Microsoft Excel 11.0 Object Library.

Note: Yours may be a different type depending on the type of Office installed on your machine. This was done for MS Excel 2003.

using the System;

using System.IO;

using System.Collections;

using the System.Training;

using Office = Microsoft.Office.Core;

using Excel = Microsoft.Office.Interop.Excel;

using System.Diagnostics;

ATPMain name space

{

///

/// Project: Code Project Demo

/// Author: Vahe Karamian

/// Date: 03/01/2005

/// Version: 1.0

///

VkExcel community category

{

Private Excel.

Private Excel.S excelWorkbook Workbook = invalid;

Private Excel.Sheets excelSheets = null;

Private Excel.Work WorksheetWorksheet = null;

...
using Office = Microsoft.Office.Core;

using Excel = Microsoft.Office.Interop.Excel;

You will need to enter these two in order to use Excel object in your code. So we need to have Excel.Application object, Excel.Workbook object, Excel.Sheets object, and Excel.Worksheet. This item will be used to control and extract data from Excel. We therefore announce the following variables to represent the mentioned items: excelApp, excelWorkbook, excelSheets, and excelWorksheet.

....

private object vk_missing = System.Reflection.Missing.Value;

private stand vk_visible = true;

private stand vk_false = false;

private stand vk_true = true;

private bool vk_app_visible = false;

secret vk_filename;

#public space WORKBOOKS

private vk_update_links = 0;

secret vk_read_only = vk_fact;

private vk_format = 1;

secret vk_password = vk_missing;

secret vk_write_res_password = vk_missing;

secret vk_ignore_read_only_recommend = vk_true;

secret vk_origin = vk_missing;

secret vk_delimiter = vk_missing;

secret vk_editable = vk_false;

secret vk_notify = vk_false;

secret vk_converter = vk_missing;

secret vk_add_to_mru = vk_false;

secret vk_local = vk_false;

private vk_corrupt_load = vk_false;
#dates

#gion CLOSE THE WORKING BOOKS

private vk_save_changes = vk_false;

secret vk_route_workbook = vk_false;
#dates

///

/// Vahe Karamian - 03/04/2005 - Excel Object Constructor.

///

VkExcel public ()

{

this.startExcel ();

}

///

/// Vahe Karamian - 03/04/2005 - Excel Object Constructor

/// visible parameter, either TRUE or FALSE, of the object type.

///

/// Visual parameter, visually true, false to invisibility

public VkExcel (visible bool)

{

this.vk_app_visible = visible;

this.startExcel ();

}

...
In the block above, we have outlined some of the elements that will be used to open the Excel file. To find out more about what each parameter says or does, you should refer to the documentation that comes with Excel.

We have two builders: VkExcel () will automatically start Excel hidden, and the other VkExcel (visible bool) gives you the opportunity to specify whether you would like to see the Excel program or not.

...

///

/// Vahe Karamian - 03/04/2005 - Launch Excel Application

///
#instart start EXCEL

void startExcel ()

{

if (this.excelApp == null)

{

this.excelApp = new Excel.ApplicationClass ();

}

// Make Excel visible

this.excelApp.Visible = this.vk_app_visible;

}
#dates

///

/// Vahe Karamian - 03/23/2005 - **** Current Excel process

///
#district STOP EXCEL

public void stopExcel ()

{

if (this.excelApp! = null)

{

Process [] process;

pProcess = System.Diagnostics.Process.GetProcessesByName ("Excel");

Process [0]. **** ();

}

}
#dates

...
The code above starts and stops the Excel program. startExcel () checks to see if the excelApp item has been started or not
Automating MS Excel Using Visual Studio.NET



Post a Comment

0 Comments