In 2017, Microsoft announced that the development of C# and VB. NET was set to diverge - C# to pick up more complex features and VB. NET to maintain readability and keep the amount of different concepts manageable. While these two languages serve developers differently (depending on skill level), it's important to remain current and still be able to perform basic macros in programs like Word and Excel.

This post will show you how to import and export your Microsoft Excel XLSX and XLSM spreadsheets directly in your .NET Windows Forms applications using Spread.NET, our .NET spreadsheet.

We will use the Spread Designer tool in run-time for editing spreadsheet instances inside your running application with just one line of code. I will show how to create a simple front-end user interface which integrates the FpSpread spreadsheet control with the NameBox and FormulaTextBox controls using splitter panes to create the main user interface, and how to implement menu items to handle File - Open, File - Save, and File - Design commands in C# and VB.

These steps require:

Step 1: Create the Project

Figure 1 – Create new Windows Forms project (either C# or VB)

Create a new project in Visual Studio 2019 by selecting C#, Windows, and Desktop to filter the projects and then select either C# or VB Windows Forms.

Step 2: Configure the Project

Figure 2 – Configure new Windows Forms project

Type _Spread.NET_GettingStarted for Project Name and confirm the Framework target at the bottom is at least .NET Framework 4.5.2 (the minimum framework version supported).

Step 3: Create the File Menu

Drag and drop a MenuStrip control to the form and create the File menu and menu items underneath for File - Open, File - Save, File - Save As, File - Design, and File -Exit:

Figure 3 – Create the File menu

Note that the File - Save menu item should initially be disabled, and the menu separators and shortcut keys are optional but recommended. We will add code for those menu items in a later step.

Step 4: Create SplitContainer1

Figure 4 – Create SplitContainer1

Double-click the SplitContainer control in the toolbox (under Containers) to create the split container for the main spreadsheet control and the formula bar user interface.

Step 5: Configure SplitContainer1

Figure 5 – Configure SplitContainer1

Using the Property Grid, change SplitContainer1.Orientation from Vertical to Horizontal and change Panel1MinSize to 21, then resize the top page to the minimum size as shown above. The spreadsheet will be in the bottom pane, and the formula bar interface will be in the top pane, and the splitter will determine the height of the FormulaTextBox control for showing long formulas that wrap to new lines.

Step 6: Create SplitContainer2

Figure 6 – Create SplitContainer2

Using the Toolbox, drag-and-drop another instance of SplitContainer into the top pane (Panel1) of SplitContainer1 to create SplitContainer2 inside the top pane as shown above. This new SplitContainer will contain the NameBox and FormulaTextBox controls and allow the user to resize the relative widths of those controls across the width of the form.

Step 7: Create the FpSpread

Figure 7 – Create FpSpread

Using the Toolbox, drag-and-drop an instance of FpSpread into the bottom pane (Panel2) of SplitContainer1. The Spread Designer tool may open when you create the control (that is the default behavior) – for now, just close the Spread Designer if it appears. Using the control's Smart Tags, click Dock in Parent Container to set the Dock property to Fill.

Step 8: Create the NameBox

Figure 8 – Create NameBox control

Using the Toolbox, drag-and-drop an instance of NameBox into the left side (Panel1) of SplitContainer2 in the top pane (Panel1) of SplitContainer1, then use the Property Grid to set the Dock property to Fill, then open the control's Smart Tag and attach the NameBox to the FpSpread control:

Figure 9 – Attach NameBox to FpSpread

Step 9: Create the FormulaTextBox

First drag-and-drop a Panel control into the right side (Panel2) of SplitContainer2 in the top pane (Panel1) of SplitContainer1, then use the Property Grid to set the Dock property to Fill and BorderStyle to Fixed Single:

Figure 10 – Create Panel with BorderStyle Fixed Single

The drag-and-drop a FormulaTextBox into that Panel control, use the Property Grid to set its Dock property to Fill and BorderStyle to None, then use the Smart Tags to attach the FormulaTextBox to the FpSpread:

Figure 11 – Attach FormulaTextBox to FpSpread

Step 10: Create the FpSpreadDesigner Component

Figure 12 – Create FpSpreadDesigner component

Drag-and-drop a FpSpreadDesigner component onto the form from the toolbox to create an instance in the component tray next to the SheetView and MenuStrip instances.

Step 11: Create Event Handlers for File Menu Items

Figure 13 – Create event handlers for File menu items

For each menu item in the File menu, double-click that menu item in the design view, until each menu item has an associated menu handler generated in the VB or C# code:

Figure 14 – Generate event handlers in C# or VB code

Step 12: Add Event Handler Code

Copy the following code to implement the event handlers.

[C#]

private string mFileName = null;

private void openToolStripMenuItem_Click(object sender, EventArgs e)
{
    OpenFileDialog ofd = new OpenFileDialog();
    ofd.Filter = "Excel Spreadsheet (*.XLSX;*.XLSM)|*.XLSX;*.XLSM";
    ofd.FilterIndex = 0;
    if (ofd.ShowDialog() == DialogResult.OK)
    {
        mFileName = ofd.FileName;
        fpSpread1.OpenExcel(mFileName);
        saveToolStripMenuItem.Enabled = true;
    }
}

private void saveToolStripMenuItem_Click(object sender, EventArgs e)
{
    fpSpread1.SaveExcel(mFileName);
}

private void saveAsToolStripMenuItem_Click(object sender, EventArgs e)
{
    SaveFileDialog sfd = new SaveFileDialog();
    sfd.Filter = "Excel Spreadsheet (*.XLSX;*.XLSM)|*.XLSX;*.XLSM";
    sfd.FilterIndex = 0;
    sfd.FileName = mFileName;
    if (sfd.ShowDialog() == DialogResult.OK)
    {
        mFileName = sfd.FileName;
        fpSpread1.SaveExcel(mFileName);
        saveToolStripMenuItem.Enabled = true;
    }
}

private void designToolStripMenuItem_Click(object sender, EventArgs e)
{
    fpSpreadDesigner1.ShowDialog(fpSpread1);
}

private void exitToolStripMenuItem_Click(object sender, EventArgs e)
{
    DialogResult ret = MessageBox.Show("Do you want to save this file before closing?", "Save Spreadsheet", MessageBoxButtons.YesNoCancel);
    if (ret == DialogResult.Cancel)
        return;
    else if (ret == DialogResult.Yes)
        saveToolStripMenuItem_Click(null, EventArgs.Empty);
    Close();
}

[VB]

Private mFileName As String = Nothing

Private Sub OpenToolStripMenuItem_Click(sender As Object, e As EventArgs) Handles OpenToolStripMenuItem.Click
    Dim ofd As New OpenFileDialog
    ofd.Filter = "Excel Spreadsheet (*.XLSX;*.XLSM)|*.XLSX;*.XLSM"
    ofd.FilterIndex = 0
    If ofd.ShowDialog() = DialogResult.OK Then
        mFileName = ofd.FileName
        FpSpread1.OpenExcel(mFileName)
        SaveToolStripMenuItem.Enabled = True
    End If
End Sub


Private Sub SaveToolStripMenuItem_Click(sender As Object, e As EventArgs) Handles SaveToolStripMenuItem.Click
    FpSpread1.SaveExcel(mFileName)
End Sub

Private Sub SaveAsToolStripMenuItem_Click(sender As Object, e As EventArgs) Handles SaveAsToolStripMenuItem.Click
    Dim sfd As SaveFileDialog = New SaveFileDialog()
    sfd.Filter = "Excel Spreadsheet (*.XLSX;*.XLSM)|*.XLSX;*.XLSM"
    sfd.FilterIndex = 0
    sfd.FileName = mFileName
    If sfd.ShowDialog() = DialogResult.OK Then
        mFileName = sfd.FileName
        FpSpread1.SaveExcel(mFileName)
        SaveToolStripMenuItem.Enabled = True
    End If
End Sub

Private Sub DesignToolStripMenuItem_Click(sender As Object, e As EventArgs) Handles DesignToolStripMenuItem.Click
    FpSpreadDesigner1.ShowDialog(FpSpread1)
End Sub

Private Sub ExitToolStripMenuItem_Click(sender As Object, e As EventArgs) Handles ExitToolStripMenuItem.Click
    Dim ret As DialogResult = MessageBox.Show("Do you want to save this file before closing?", "Closing", MessageBoxButtons.YesNoCancel)
    If ret = DialogResult.Cancel Then
        Return
    ElseIf ret = DialogResult.Yes Then
        SaveToolStripMenuItem_Click(Nothing, EventArgs.Empty)
    End If
    Close()
End Sub

The code for File - Open uses the OpenFileDialog to browse for a spreadsheet file, then uses the FpSpread.OpenExcel method to open the selected file. The code for File - Save uses FpSpread.SaveExcel to save the spreadsheet, and the code for File - Save As uses the SaveFileDialog to allow the user to save the file to another location or using another name.

The code in File - Design uses the FpSpreadDesigner.ShowDialog method to show the Spread Designer tool in run-time, make changes, and then apply those changes back to the spreadsheet instance in the form. Finally, the code in File - Exit prompts the user whether to save the file and then uses the Close method to close the form.

Ready to Build and Run!

Figure 15 – Running the project and using File - Design

The project is ready to build and run. The File - Design menu will open the Spread Designer tool in run-time as shown above, which can apply changes back to the spreadsheet instance running in the form.

In another article series, we demonstrate how to import and export Excel spreadsheets with C# and WPF, import/export Excel spreadsheets using JavaScript and how to import and export Excel spreadsheets in Angular.

Download the C# Sample | Download the VB Sample

Try Spread.NET free for 30 days

Download the latest version of Spread.NET

Download Now!