.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
 
Sign In
Register
 
Win Surprise Gifts!!!
Congratulations!!!


Top 5 Contributors of the Month
ffttt

Home >> Articles >> C# >> Post New Resource Bookmark and Share   

 Subscribe to Articles

Display and Manipulate Excel Spreadsheet From Windows Form

Posted By:gorge       Posted Date: May 04, 2016    Points: 200    Category: C#    URL: http://www.dotnetspark.com  

In this article, you'll learn how to open and edit Excel spreadsheet from Windows Forms with C#.
 

Introduction


The use of spreadsheets, especially Excel, helps companies to gather various kinds of data and perform detailed analysis or reports. Sometimes, corporations may want to create a spreadsheet program with their own customized features. This article will present how we can integrate Excel spreadsheet in a Windows Form Application as well as implement many more feathers (like MS Excel) using .NET Spreadsheet control.

Below is the screenshot of the spreadsheet program created with this control. The source code is enclosed in this project. In the following sections, I'll only introduce how to add the control to Toolbox and the usage of the control.



Add Controls to Toolbox

  • Right-click on Toolbox panel, click 'Add Tab' to add new tab
  • Right-click the new tab and select 'Choose Items...'
  • Choose '.NET Framework Components' tab
  • Click 'Browse...' button
  • Choose 'Spire.Spreadsheet.dll' in open file dialog
  • Click 'OK', then the controls including 'Spreadsheet' and 'FormulaBox' will be successfully added to Toolbox

Drag 'Spreadsheet' control to a form and it looks like:



Usage of the Control

If you only need to display Excel spreadsheets in a Windows application, you can simply drag and place the 'Spreadsheet' control to form and call LoadFromFile() method to load an existing Excel file.

spreadsheet.LoadFromFile(string fileName);
If you want to create an interface similar like what I achieved in this sample project, then you must learn more about the classes, methods and properties that are supported by this control. In the following section, I'll make brief introduction about the functions that my program can perform.

In most situations, we need to select a cell or range before we can do some operations on it, for instance, format the cells, clear contents, etc. To let program know on which cell(s) the function will execute, I used GetAllSelectedRanges() to capture the area that has been selected by mouse, and a nested for loops to traverse each cell in the range.

List<Spire.Spreadsheet.Forms.CellRangeInfo> l = spreadsheet1.ActiveWorksheet.GetAllSelectedRanges();
foreach (Spire.Spreadsheet.Forms.CellRangeInfo c in l)
{
    for (int j = c.Top; j <= c.Bottom; j++)
    {
        for (int k = c.Left; k <= c.Right; k++)
        {
            //spreadsheet1.ActiveWorksheet[j, k]...
        }
    }
} 
Font

After we get the selected cell(s), we can easily set font name, size, color and style in it or them by replacing the code that was commented out with following code snippets. This control doesn't provide a FontName or FontSize or FontStyle property but it supports System.Drawing.Font, so we can create an object of System.Drawing.Font and apply it to the Font property of the specific cell.
//set font name, size, style 
Font newFont = new Font(string familyName, float emSize, FontStyle style); spreadsheet1.ActiveWorksheet[j, k].Font = newFont; 
//set font color
Color fontColor = fontColorDialog.Color;
spreadsheet1.ActiveWorksheet[j, k].FontColor = fontColor;
Background Color

Color fillColor = fillColorDialog.Color;
spreadsheet1.ActiveWorksheet[j, k].Style.ForegroundColor = fillColor;
Alignment

Spire.Spreadsheet.Forms.Utility namespace contains HorizontalAlignmentType enumeration and VerticalAlignmentType enumeration, which support to set Align Left, Center, Align Right, Top Align, Middle Align and Bottom Align by choosing the item from them. For example:
//align left
spreadsheet1.ActiveWorksheet[j, k].Style.HorizontalAlignment = Spire.Spreadsheet.Forms.Utility.HorizontalAlignmentType.Left;
//top align
spreadsheet1.ActiveWorksheet[j, k].Style.VerticalAlignment = Spire.Spreadsheet.Forms.Utility.VerticalAlignmentType.Top;
Border

The way to set borders is similar to that of setting alignment. The only thing worth attention is that we must judge if a cell already has a top/bottom/left/right/diagonal border, since the border can be added only when there is no same border available. Otherwise, we choose the remove the border line by click this button.
//set top border
if (spreadsheet1.ActiveWorksheet[j, k].Style.Borders.Top == Spire.Spreadsheet.Forms.Utility.BorderLineType.None)
{
    spreadsheet1.ActiveWorksheet[j, k].Style.Borders.Top = Spire.Spreadsheet.Forms.Utility.BorderLineType.Thin;
}
else
{
    spreadsheet1.ActiveWorksheet[j, k].Style.Borders.Top = Spire.Spreadsheet.Forms.Utility.BorderLineType.None;
}
Merge and Unmerge

It is easy to perform merge and unmerge on selected range by calling the method Merge() and Unmerge().
//Merge
spreadsheet1.ActiveWorksheet.Merge(spreadsheet1.ActiveWorksheet.GetSelectedRange().Top,
                spreadsheet1.ActiveWorksheet.GetSelectedRange().Left,
                spreadsheet1.ActiveWorksheet.GetSelectedRange().Bottom,
 //Unmerge
spreadsheet1.ActiveWorksheet.Unmerge(new CellRangeInfo(spreadsheet1.ActiveWorksheet.GetSelectedRange().Top,
        spreadsheet1.ActiveWorksheet.GetSelectedRange().Left,
        spreadsheet1.ActiveWorksheet.GetSelectedRange().Bottom,
        spreadsheet1.ActiveWorksheet.GetSelectedRange().Right));
}
Undo and Redo
spreadsheet1.Undo();
spreadsheet1.Redo();
Wrap Text

After the text is wrapped, the row height does not change automatically, which might make the content unreadable, so I chose to auto fit the row height by AutoFitRow().
spreadsheet1.ActiveWorksheet[j, k].Style.WrapText = true;
spreadsheet1.ActiveWorksheet.AutoFitRow();
Height and Width
//auto fit row height
spreadsheet1.ActiveWorksheet.AutoFitRow(int rowIndex);
//auto fit column width
spreadsheet1.ActiveWorksheet.AutoFitColumn(int columnIndex);
//set row height
spreadsheet1.ActiveWorksheet.SetRowHeightPixels(int Row, double value));
//set column width
spreadsheet1.ActiveWorksheet.SetColumnWidthInPixels(int columnIndex, double value);
Find and Replace

This control provides a FindDialog, which help us find and replace the searched text without creating similar one by ourselves.
spreadsheet1.ShowFindDialog();


Save


SaveToFile is always a basic but essential function that makes sure all the changes on the form can be updated to Excel file.
spreadsheet1.SaveToFile(string fileName);

Conclusion


I only explored some basic functionalities of the control and some small bugs were found, but I am not sure if they were caused by logic mistakes in my program. Anyway, welcome to download the program and make some improvements.


 Subscribe to Articles

     

Further Readings:

Responses

No response found. Be the first to respond this post

Post Comment

You must Sign In To post reply
Find More Articles on C#, ASP.Net, Vb.Net, SQL Server and more Here

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend