WinForms Excel Library

WinForms Excel Library (.NET Component)

Developer: MSDN.WhiteKnight

Web site: http://smallsoft2.blogspot.com/

Requirements:

.NET Framework 4.0+ or .NET Core 3.1+ (must be present on target machine)

MS Excel 2003+ (must be present on target machine)

Visual Studio 2010+ (for .NET Framework) or Visual Studio 2019+ (for .NET Core)

Download:  Github releases
Source code: WinformsExcel on Github

Overview

WinForms Excel Library allows to host MS Excel interface in Windows Forms application as a user control in order to display and edit one or more tables of data. The Excel application is started in different process, but the interface is fully integrated into your application as for any usual control. Basically, it works like DataGridView, but enables user to take advantage of various data processing and visualization capabilities of MS Excel, which might be useful in scientific, engineering or financial applications. The library interacts with Excel via Primary Interop Assemblies. Currently implemented functionality:

• Filling data from files, DataTable objects or manually on cell-by-cell basis;
• Displaying and editing one or several tables in your form in Excel GUI;
• Getting data back as DataTable objects or getting the content of specific cells;
• Saving data into file;
• Adding charts;
• Manipulating worksheets: adding, deleting, reordering, changing names, changing active sheet;
• Showing/hiding Excel formula bar and status bar;
• Direct access to Excel interoperability interfaces via Application object.

The library distribution package contains demo application project (Visual Studio 2010)

Usage

The library functionality is provided via ExtraControls.AdvancedDataGrid user control. In order to use it in WinForms application, do the following:

1. Copy WinFormsExcel.dll, Interop.Excel.dll, Interop.Microsoft.Office.Core.dll assemblies in your project, and add reference to WinFormsExcel assembly.

2. Open Windows Forms designer, right-click toolbox, click "Choose elements". Click "Browse", and choose WinFormsExcel.dll. AdvancedDataGrid will appear in "NET Components" list.

3. Check AdvancedDataGrid and click OK. AdvancedDataGrid will appear in toolbox under "Other" category.

4. Drag AdvancedDataGrid onto the form.

5. Adjust control's appearance properties such as border, background color, to distinguish it visually from form's background.

Note: control will look empty in designer, because Excel is not initialized yet.

6. Place advancedDataGrid.InitializeExcel() method call in your form's constructor or OnLoad event (this will create Excel process)

7. Fill the grid with data using one of the following methods:

public void OpenFile(string file);

public void SetCellContent(int sheet, int row, int col, object val);

public void SetSheetContent(int sheet, DataTable t);

Receive user input via one of the following methods:

public DataTable GetSheetContent(int sheet, bool FirstRowHasHeaders,int n_col=0,int n_row=0);

public object GetCellContent(int sheet, int row, int col);

Use other functionality, such as manipulating sheets, adding charts, or saving data to file. Note that sheet indices start from 1 in excel, and charts are treated as sheets as well.

When you no longer need the control (for example, in FormClosing event), call advancedDataGrid1.Destroy() method to clean up resources.

If you need your application to be work on machines without MS Excel installed (with lesser functionality), use C_AdvancedDataGrid control instead. It’s a wrapper that uses AdvancedDataGrid if Excel is present, otherwise it substitutes its functionality with a combination of standard controls.

Reference

Assembly WinFormsExcel.dll

namespace ExtraControls

AdvancedDataGrid class

class AdvancedDataGrid : UserControl, IDisposable

Provides the functionality of hosting MS Excel window in Windows Forms application in order to display and edit one or several tables of data

AdvancedDataGrid constructor:

public AdvancedDataGrid()

Creates AdvacedDataGrid control in uninitialized state. Call InitializeExcel method before actually using control.

AdvancedDataGrid properties:

       

public object DataSource

Gets or sets the content of currently active Excel sheet via DataTable object

Note: AdvanceDataGrid does not support data binding. DataSource Property is only a convenient way to manipulate active sheet’s contents.

public bool DisplayStatusBar

Determines whether control should display Excel’s status bar. In Excel 2010 and newer has no effect unless DisplayWindowTitle is set to true

Visible in designer. Default value: false

public bool DisplayFormulaBar

Determines whether control should display Excel’s formula bar

Visible in designer. Default value: false

public bool DisplayWindowTitle

Determines whether control should display Excel’s window title, including ribbon menu in new versions

Visible in designer. Default value: false

public int ActiveSheet

Gets or sets current active sheet.

Returns -1 on error.

public Excel.Application XlApplication

Gets the underlying Excel Application object of this control instance (read-only). You can use it to call Excel interop interfaces directly.

Returns null if Excel is not initialized.

public int SheetsCount

Returns the amount of sheets in current workbook (read-only). Sheets include both worksheets and charts.

Returns -1 on error.

AdvancedDataGrid methods:

public void InitializeExcel()

Initializes Excel application and creates empty workbook for this control instance.

This method creates new Excel process and changes control’s state into initialized.

public void Destroy()

Cleans up resources of current AdvancedDataGrid control and closing Excel application. The control will be in uninitialized state.

This method must be called before application is closing.

public void SetCellContent(int sheet, int row, int col, object val)

Sets contents of the cell specified by sheet, row and column numbers into an object of any type

       

public object GetCellContent(int sheet, int row, int col)

Gets the content of the cell specified by sheet, row and column numbers.

Returns null in case of incorrect arguments.

public string GetCellAddress(int sheet, int row, int col)

Gets the address of the cell specified by sheet, row and column numbers.

The address consists of column represented by letter, and row represented by number (such as “B12” for 2^nd column 12^th row).

public void SetSheetContent(int sheet, DataTable t)

Fills the specified sheet with a content of given DataTable object

public DataTable GetSheetContent(int sheet, bool FirstRowHasHeaders,int n_col=0,int n_row=0)

Loads content of specified sheet as DataTable object.

Tries to guess column types based on first cells (uses string as default type).

Arguments

Sheet: Sheet number

FirstRowHasHeaders: Specifies that first row contains column headers

n_col: Maximum number of columns to load (0 - automatic)

n_row: Maximum number of rows to load (0 - automatic)

public void DeleteSheet(int index)

Removes specified sheet from workbook.

Note: You can't remove all sheets. At least one sheet must be present in workbook all the time.

public void AddSheet(string name="")

Adds new sheet into the workbook of this control instance

Name: Worksheet name (optional). Will generate name automatically if omitted.

public List<XlSheet> GetSheets()

Gets information about all sheets in this control instance as a list of XlSheet objects.

public void SetSheetName(int sheet,string name)

Changes the name of specified sheet.

public string GetSheetName(int sheet)

Returns the name of specified sheet.

public int FindSheet(string name)

Gets the index of sheet with specified name. Returns -1 if the sheet is not found.

public void MoveSheet(int curr_index, int new_index,bool before=true)

Inserts the worksheet before or after the target worksheet in the workbook

Arguments

curr_index: Number of sheet to move

new_index: Target sheet index

before: Specifies that sheet must be placed before the target sheet

public void SaveIntoFile(string file)

Saves the content of workbook into the file with specified path.

public void NewEmptyWorkbook()

Clears content of this contol instance’s workbook, and creates new workbook of default template.

public void OpenFile(string file)

Loads the content of specified file into this control.

public void AddChart(int sheet, string cell1, string cell2, ChartType ct = ChartType.xlXYScatterLines,string title = "" )

Adds Chart based on data of specified sheet into the workbook of this control.

Arguments

sheet: Number of sheet from which to use data for the chart

cell1: Addess of upper-left cell of chart’s source data range

cell2: Addess of lower-right cell of chart’s source data range

ct: Chart type

title: Chart title

XlSheet class

class XlSheet

Represent Excel sheet (worksheet or chart) information. Returned by AdvancedDataGrid.GetSheets() method

Public fields:

public int Index;

Index of sheet in workbook (starts from 1)

public string Name;

Name of sheet (displayed in tab)

public bool IsChart;

Specifies that this sheet is a chart. Unlike with worksheets, you can’t manipulate content of chart sheets.

ChartType enumeration

enum ChartType

Represents Excel Chart type. See Excel interface documentation for more information.

Values:

   

        xlXYScatter = -4169,

        xlRadar = -4151,

        xlDoughnut = -4120,

        xl3DPie = -4102,

        xl3DLine = -4101,

        xl3DColumn = -4100,

        xl3DArea = -4098,

        xlArea = 1,

        xlLine = 4,

        xlPie = 5,

        xlBubble = 15,

        xlColumnClustered = 51,

        xlColumnStacked = 52,

        xlColumnStacked100 = 53,

        xl3DColumnClustered = 54,

        xl3DColumnStacked = 55,

        xl3DColumnStacked100 = 56,

        xlBarClustered = 57,

        xlBarStacked = 58,

        xlBarStacked100 = 59,

        xl3DBarClustered = 60,

        xl3DBarStacked = 61,

        xl3DBarStacked100 = 62,

        xlLineStacked = 63,

        xlLineStacked100 = 64,

        xlLineMarkers = 65,

        xlLineMarkersStacked = 66,

        xlLineMarkersStacked100 = 67,

        xlPieOfPie = 68,

        xlPieExploded = 69,

        xl3DPieExploded = 70,

        xlBarOfPie = 71,

        xlXYScatterSmooth = 72,

        xlXYScatterSmoothNoMarkers = 73,

        xlXYScatterLines = 74,

        xlXYScatterLinesNoMarkers = 75,

        xlAreaStacked = 76,

        xlAreaStacked100 = 77,

        xl3DAreaStacked = 78,

        xl3DAreaStacked100 = 79,

        xlDoughnutExploded = 80,

        xlRadarMarkers = 81,

        xlRadarFilled = 82,

        xlSurface = 83,

        xlSurfaceWireframe = 84,

        xlSurfaceTopView = 85,

        xlSurfaceTopViewWireframe = 86,

        xlBubble3DEffect = 87,

        xlStockHLC = 88,

        xlStockOHLC = 89,

        xlStockVHLC = 90,

        xlStockVOHLC = 91,

        xlCylinderColClustered = 92,

        xlCylinderColStacked = 93,

        xlCylinderColStacked100 = 94,

        xlCylinderBarClustered = 95,

        xlCylinderBarStacked = 96,

        xlCylinderBarStacked100 = 97,

        xlCylinderCol = 98,

        xlConeColClustered = 99,

        xlConeColStacked = 100,

        xlConeColStacked100 = 101,

        xlConeBarClustered = 102,

        xlConeBarStacked = 103,

        xlConeBarStacked100 = 104,

        xlConeCol = 105,

        xlPyramidColClustered = 106,

        xlPyramidColStacked = 107,

        xlPyramidColStacked100 = 108,

        xlPyramidBarClustered = 109,

        xlPyramidBarStacked = 110,

        xlPyramidBarStacked100 = 111,

        xlPyramidCol = 112

IDataGrid interface

public interface IDataGrid

 

Provides a set of properties and methods shared by DataGrid controls able to display multiple data tables. The library contains several implementations: AdvancedDataGrid, S_AdvancedDataGrid, C_AdvancedDataGrid. See AdvancedDataGrid description for more information about the meanings of these properties and methods.

 

Properties:

object DataSource {get;set;}

int ActiveSheet { get; set; }

int SheetsCount { get; }

 

Methods:

void SetCellContent(int sheet, int row, int col, object val);

object GetCellContent(int sheet, int row, int col);       

void SetSheetContent(int sheet, DataTable t);

DataTable GetSheetContent(int sheet, bool FirstRowHasHeaders, int n_col = 0, int n_row = 0);

int GetActiveSheet();

void SetActiveSheet(int index);

void DeleteSheet(int index);

void AddSheet(string name = "");

List<XlSheet> GetSheets();

void SetSheetName(int sheet, string name);

string GetSheetName(int sheet);

int FindSheet(string name);       

void NewEmptyWorkbook();

 

C_AdvancedDataGrid class

 

public class C_AdvancedDataGrid : UserControl, IDisposable, IDataGrid

 

Wrapper control for AdvancedDataGrid/S_AdvancedDataGrid controls. Switches between them at runtime, allowing to use required control depending on Excel presence on the target machine.

 

Constructors:

public C_AdvancedDataGrid()

Creates C_AdvancedDataGrid control in substitute mode

 

public C_AdvancedDataGrid(GridMode mode)

Creates C_AdvancedDataGrid control in specified mode

 

Properties:

public GridMode Mode

Gets or sets the current grid mode. Setting this property will cause control to be initialized again.

 

public IDataGrid BaseGrid

Returns the underlying grid, which type is determined by Mode property

 

Methods:

public void Initialize(GridMode mode)

Initializes this control in specified mode

 

public void Destroy()

Releases resources associated with this control instance.

 

Members from IDataGrid interface:

object DataSource {get;set;}

int ActiveSheet { get; set; }

int SheetsCount { get; }

void SetCellContent(int sheet, int row, int col, object val);

object GetCellContent(int sheet, int row, int col);       

void SetSheetContent(int sheet, DataTable t);

DataTable GetSheetContent(int sheet, bool FirstRowHasHeaders, int n_col = 0, int n_row = 0);

int GetActiveSheet();

void SetActiveSheet(int index);

void DeleteSheet(int index);

void AddSheet(string name = "");

List<XlSheet> GetSheets();

void SetSheetName(int sheet, string name);

string GetSheetName(int sheet);

int FindSheet(string name);       

void NewEmptyWorkbook();

 

See AdvancedDataGrid description for the information about the meaning of these methods.

 

GridMode enumeration

 

enum GridMode

Determines the behavior of C_AdvancedDataGrid control.

 

Values:

Undefined = 0, //not initialized yet

Auto = 1, //Choose mode automatically: Excel mode if possible, otherwise Substitute

Excel = 2, //Uses AdvancedDataGrid

Substitute =3 //Uses S_AdvancedDataGrid

 

namespace ExtraControls.SubstituteGrid

 

S_AdvancedDataGrid class

class S_AdvancedDataGrid : UserControl, IDataGrid

Provides an implementation of IDataGrid interface via standard Windows Forms control, allowing to display and edit one or

more tables of data. Serves as a Excel-free substitute for AdvancedDataGrid control.

Normally you should not use this class directly, use C_AdvancedDataGrid instead.

 

Constructors:

public S_AdvancedDataGrid()

Creates new S_AdvancedDataGrid control and fills it with default empty content

 

Properties:

public bool ReadOnly

Specifies if user is allowed to edit data in this control

 

Methods:

public void Destroy()

Releases resources associated with this control instance.

 

Members from IDataGrid interface:

object DataSource {get;set;}

int ActiveSheet { get; set; }

int SheetsCount { get; }

void SetCellContent(int sheet, int row, int col, object val);

object GetCellContent(int sheet, int row, int col);       

void SetSheetContent(int sheet, DataTable t);

DataTable GetSheetContent(int sheet, bool FirstRowHasHeaders, int n_col = 0, int n_row = 0);

int GetActiveSheet();

void SetActiveSheet(int index);

void DeleteSheet(int index);

void AddSheet(string name = "");

List<XlSheet> GetSheets();

void SetSheetName(int sheet, string name);

string GetSheetName(int sheet);

int FindSheet(string name);       

void NewEmptyWorkbook();

 

See AdvancedDataGrid description for the information about the meaning of these methods.