Connect C# forms with MS Excel
NAME ID
Abdulrahman bin Awad Al-Otaibi 444953081
Fahd Badr Hazaa Al-Hazim 444950008
Majdal Saad Majdal Al-Wathila 445951512
Mubarak Hassan 445951505
Nasser Daraan 444953911
TOPIC PAGE
Introduction 4
IronXL
5
Add a C# 7
Import Excel 9
Protect the Worksheet 10
C# Excel Connection
12
Code Playground
13
CONTENT
STEPS 14
Conclusion 29
REFERENCES 30
Introduction
Generally, in development projects, there is most common
requirement is reading/ editing/ generating an Excel file.
In recants projects, I also worked with the same kind of
requirement.
When the developer hosts that project in shared hosting (in the
case of low-budget projects where a dedicated server is not
necessary).
After some research, I found a software component IronXL which
solved my problem.
IronXL is a powerful software
library designed for .NET
developers, providing them with an
easy-to-use API to read, write, and
manipulate Excel files in their
.NET applications. It offers a
IronXL comprehensive set of features for
creating, editing, and exporting
Excel spreadsheets, all without
requiring Microsoft Office or Excel
to be installed on the target
machine.
Prerequisites
Having Visual Studio installed on your computer to create a
C# project.
Ensuring that ASP.NET is also installed on your system before
creating a C# project.
Installingthe IronXL library on your system to export data.
You can obtain it by downloading the IronXL NuGet package
from the NuGet Package Manager in Visual Studio.
Having SQL installed in Visual Studio.
Add a C# .NET Excel Viewer
Component to the Project
In the Solution Explorer, right-click the solution
name and select Manage NuGet Packages for
Solution. Search the NuGet.org package
source for GrapeCity.Spread.WinForms and
install the WinForms spreadsheet component.
Import Excel (.xlsx) Files into the
WinForms App Excel Viewer
Protect the Worksheet in Your C#
.NET Excel Viewer
Tocustomize the spreadsheet UI and prevent
users from altering the Excel file, you can protect
the workbook and worksheet using Spread.NET’s
Protect methods. This ensures your app will
function as a viewer-only interface.
Add the following code within Form1 to invoke
the Protect method to lock both the worksheet and
the workbook:
C# Excel
Connection
Code Playground
Step 1. Open Visual Studio. Select New Project, then Windows
Form Application. I am using the Visual Studio 2022 community
version.
Step 2. Name it as you want.
Step
3. First of all, add a Reference to the Excel library - IronXL.
Right-click on the Solution in the solution explorer and click the
“Manage NuGet packages” option.
Step4. Browse the tab and type the “IronXL.Excel” keyword. The
following screen will appear.
Step 5. Now add two
buttons, Choose and Read
File and Close, on the form by
dragging the button control
from the Toolbox window.
Step6. Also, add
a DataGridView to see the
result (excel data).
Step 7. Right-click on Windows Form and click
the “View code” option. This will open the code
behind the file of the form.
Step 8. You can also open the code behind the file
by double-clicking on the buttons. It will create
the click event method of the button.
Step 9. You can also manage the click event
of the button in its Properties.
Step 10.
Now create a method
ReadExcel, which
returns a data table
using the following
logic.
Step 11. Add the following logic in button click
events.
After choosing the file Result will be like the following whether
you upload a .xls or .xlsx file.
Read and
Edit Excel
file
if (dataRange != null)
{
Marshal.ReleaseComObject(dataRan
ge);
}
Cleaning Up And this one in VB:
If dataRange IsNot Nothing Then
Marshal.ReleaseComObject(dataRan
ge)
End If
SET ANSI_NULLS ON
How to SET QUOTED_IDENTIFIER ON
import data SET ANSI_PADDING ON
from CREATE TABLE [dbo].[Table1](
spreadsheet [student] [varchar](50) NULL,
[rollno] [int] NULL,
to any
[course] [varchar](50) NULL
database by ) ON [PRIMARY]
csharp code: SET ANSI_PADDING OFF
Prepare an
excel sheet
with the
required
columns.
Conclusion
The IronXL is also helpful in other Excel operations like
editing/styling/generating Excel files. The code of this
tutorial is attached. You can download the code, play
with the code, and modify it according to your
requirements. I tried to make this tutorial as
development-friendly friendly as simple as I could.
Thank you, and enjoy coding!!!
references
1. "InfoQ eMag: A Preview of C# 7". Archived from the original
on April 24, 2023. Retrieved November 11, 2016.
2. https://devblogs.microsoft.com/dotnet/announcing-dotnet-
9/. {{cite web}}: Missing or empty |title= (help)
3. Torgersen, Mads (October 27, 2008).
4. "CoreCLR is the runtime for .NET Core. It includes the garbage
collector, JIT compiler, primitive data types and low-level
classes.: dotnet/coreclr". November 13, 2019. Archived from
the original on October 14, 2019. Retrieved March 8, 2017 – via
GitHub.
