C Sharp Excel Interop Workaround
Many projects use Excel to communicate clearly, but if you are using Microsoft.Office.Interop.Excel then you have likely had to face many complicated lines of code. In this tutorial, we will use IronXL as a C# Excel Interop workaround, so you don't have to use Interop in order to finish your project. You can use Excel file data, create Excel files, edit and manipulate, all using C# programming.
Step 1
1. Download IronXL Library
Download IronXL Library or install with NuGet to access the free library and then follow step by step to work through this tutorial on using Excel without Interop. Licenses available if you want to go live with your project.
PM > Install-Package IronXL.ExcelHow to Tutorial
1. Access Excel File Data
To develop business applications, we need easy and perfect access to data from Excel files and the ability to manipulate them programmatically according to a variety of requirements. With IronXL, use the WorkBook.Load() function, which grants access to reading a specific Excel file.
After accessing the WorkBook, you can then choose a specific WorkSheet using the WorkBook.GetWorkSheet() function. Now you have all Excel file data available. See the following example for how we used these functions to get Excel file data in a C# project.
using IronXL;
static void Main(string[] args)
{
//Access Excel file
WorkBook wb = WorkBook.Load("sample.xlsx");
//Access WorkSheet of Excel file
WorkSheet ws = wb.GetWorkSheet("Sheet1");
//get specific cell value
string a = ws["A5"].Value.ToString();
Console.WriteLine(" Getting Single Value:\n\n Value of Cell A5: {0} ",a);
Console.WriteLine("\n Getting Many Cells Value using Loop:\n");
//get many cell values using range function
foreach (var cell in ws["B2:B10"])
{
Console.WriteLine(" Value is: {0}", cell.Text);
}
Console.ReadKey();
}using IronXL;
static void Main(string[] args)
{
//Access Excel file
WorkBook wb = WorkBook.Load("sample.xlsx");
//Access WorkSheet of Excel file
WorkSheet ws = wb.GetWorkSheet("Sheet1");
//get specific cell value
string a = ws["A5"].Value.ToString();
Console.WriteLine(" Getting Single Value:\n\n Value of Cell A5: {0} ",a);
Console.WriteLine("\n Getting Many Cells Value using Loop:\n");
//get many cell values using range function
foreach (var cell in ws["B2:B10"])
{
Console.WriteLine(" Value is: {0}", cell.Text);
}
Console.ReadKey();
}Imports Microsoft.VisualBasic
Imports IronXL
Shared Sub Main(ByVal args() As String)
'Access Excel file
Dim wb As WorkBook = WorkBook.Load("sample.xlsx")
'Access WorkSheet of Excel file
Dim ws As WorkSheet = wb.GetWorkSheet("Sheet1")
'get specific cell value
Dim a As String = ws("A5").Value.ToString()
Console.WriteLine(" Getting Single Value:" & vbLf & vbLf & " Value of Cell A5: {0} ",a)
Console.WriteLine(vbLf & " Getting Many Cells Value using Loop:" & vbLf)
'get many cell values using range function
For Each cell In ws("B2:B10")
Console.WriteLine(" Value is: {0}", cell.Text)
Next cell
Console.ReadKey()
End SubThis code will produce the following outcome:
With the Excel file looking like this:
We can see that our Excel file, sample.xlsx, has small business in the A5 cell. The other values from B2 to B10 are the same and displayed in the output.
1.1. DataSet and DataTables
We also can also work with Excel files as a dataset, and datatables, using these directions.
//Access WorkBook.
WorkBook wb = WorkBook.Load("sample.xlsx");
//Access WorkSheet.
WorkSheet ws = wb.GetWorkSheet("Sheet1");
//Behave with a workbook as Dataset.
DataSet ds = wb.ToDataSet();
//Behave with workbook as DataTable
DataTable dt = ws.ToDataTable(true);//Access WorkBook.
WorkBook wb = WorkBook.Load("sample.xlsx");
//Access WorkSheet.
WorkSheet ws = wb.GetWorkSheet("Sheet1");
//Behave with a workbook as Dataset.
DataSet ds = wb.ToDataSet();
//Behave with workbook as DataTable
DataTable dt = ws.ToDataTable(true);'Access WorkBook.
Dim wb As WorkBook = WorkBook.Load("sample.xlsx")
'Access WorkSheet.
Dim ws As WorkSheet = wb.GetWorkSheet("Sheet1")
'Behave with a workbook as Dataset.
Dim ds As DataSet = wb.ToDataSet()
'Behave with workbook as DataTable
Dim dt As DataTable = ws.ToDataTable(True)You can read more about how to work with Excel DataSet and DataTables, which provides more code examples and explanations on the process.
Now, we will see other aspect, which is about creating new Excel file in our C# project.
2. Create New Excel File
We can easily create a new Excel SpreadSheet and insert data in it programatically in our CSharp project. To accomplish this, IronXL provides the WorkBook.Create() function, which creates a new Excel file.
We can then create as many WorkSheets as needed using the WorkBook.CreateWorkSheet() function.
After that, we can also insert data, as shown in the example below:
using IronXL;
static void Main(string[] args)
{
//create new WorkBook
WorkBook wb = WorkBook.Create();
//Create new WorkSheet in wb
WorkSheet ws = wb.CreateWorkSheet("sheet1");
//Insert Data
ws["A1"].Value = "New Value A1";
ws["B2"].Value = "New Value B2";
//Save newly created Excel file
wb.SaveAs("NewExcelFile.xlsx");
}using IronXL;
static void Main(string[] args)
{
//create new WorkBook
WorkBook wb = WorkBook.Create();
//Create new WorkSheet in wb
WorkSheet ws = wb.CreateWorkSheet("sheet1");
//Insert Data
ws["A1"].Value = "New Value A1";
ws["B2"].Value = "New Value B2";
//Save newly created Excel file
wb.SaveAs("NewExcelFile.xlsx");
}Imports IronXL
Shared Sub Main(ByVal args() As String)
'create new WorkBook
Dim wb As WorkBook = WorkBook.Create()
'Create new WorkSheet in wb
Dim ws As WorkSheet = wb.CreateWorkSheet("sheet1")
'Insert Data
ws("A1").Value = "New Value A1"
ws("B2").Value = "New Value B2"
'Save newly created Excel file
wb.SaveAs("NewExcelFile.xlsx")
End SubThe above code will create a new Excel file with the name NewExcelFile.xlsx, and insert new data in cell addresses A1 and B1 with values New Value A1 and New Value B2 respectively. With this set up, you can insert data in the same way, as much as you need.
Note: If you are creating a new Excel file or modify the existing one, don't forget to save the file as shown in the example above.
Dig deeper into how to Create new Excel SpreadSheets using C# and try the code in your project.
3. Modify Existing Excel File
We can modify existing Excel files and insert updated data in it programatically. In Excel file modifications, we will see the following aspects:
- Update the cell value
- Replace old values with new one
- Remove the row or column
Let's see how to implement the above topics in our CSharp project.
3.1. Update Cell Value
It is very simple to update the cell value of an existing Excel SpreadSheet. Just access the Excel file in the project and specify its WorkSheet, and then update its data as shown in the example below:
using IronXL;
static void Main(string[] args)
{
WorkBook wb = WorkBook.Load("sample.xlsx");
WorkSheet ws = wb.GetWorkSheet("Sheet1");
//update A3 value
ws["A3"].Value = "New Value of A3";
wb.SaveAs("sample.xlsx");
}using IronXL;
static void Main(string[] args)
{
WorkBook wb = WorkBook.Load("sample.xlsx");
WorkSheet ws = wb.GetWorkSheet("Sheet1");
//update A3 value
ws["A3"].Value = "New Value of A3";
wb.SaveAs("sample.xlsx");
}Imports IronXL
Shared Sub Main(ByVal args() As String)
Dim wb As WorkBook = WorkBook.Load("sample.xlsx")
Dim ws As WorkSheet = wb.GetWorkSheet("Sheet1")
'update A3 value
ws("A3").Value = "New Value of A3"
wb.SaveAs("sample.xlsx")
End SubThe above code will update the value of cell A3 with New Value of A3.
We also can update multiple cells with a static value, using the range function:
ws["A3:C3"].Value = "New Value"; ws["A3:C3"].Value = "New Value";ws("A3:C3").Value = "New Value"This will update row number 3 from A3 to C3 cells with the New Value of Excel file.
Learn more about using the Range Function in C# with these examples.
3.2. Replace Cell Values
It is the beauty of IronXL that we can easily replaces the old values with new values in existing Excel file and it covers all the following aspects:
Replace values of a complete WorkSheet:
WorkSheet.Replace("old value", "new value");WorkSheet.Replace("old value", "new value");WorkSheet.Replace("old value", "new value")VB C#Replace values of a specific row:
WorkSheet.Rows[RowIndex].Replace("old value", "new value");WorkSheet.Rows[RowIndex].Replace("old value", "new value");WorkSheet.Rows(RowIndex).Replace("old value", "new value")VB C#Replace values of a specific column:
WorkSheet.Columns[ColumnIndex].Replace("old value", "new Value")WorkSheet.Columns[ColumnIndex].Replace("old value", "new Value")'INSTANT VB TODO TASK: The following line uses invalid syntax: 'WorkSheet.Columns[ColumnIndex].Replace("old value", "new Value")VB C#- Replace values in a specific range:
WorkSheet["From:To"].Replace("old value", "new value");WorkSheet["From:To"].Replace("old value", "new value");WorkSheet("From:To").Replace("old value", "new value")VB C#
Let's view an example to clearly see how to use the above functions in our CSharp Project to replace the values. For this, we will use the replace function to replace a value in a specific fange.
using IronXL;
static void Main(string[] args)
{
WorkBook wb = WorkBook.Load("sample.xlsx");
WorkSheet ws = wb.GetWorkSheet("Sheet1");
//Specifying Range from B5 to G5
ws["B5:G5"].Replace("Normal", "Good");
wb.SaveAs("sample.xlsx");
}using IronXL;
static void Main(string[] args)
{
WorkBook wb = WorkBook.Load("sample.xlsx");
WorkSheet ws = wb.GetWorkSheet("Sheet1");
//Specifying Range from B5 to G5
ws["B5:G5"].Replace("Normal", "Good");
wb.SaveAs("sample.xlsx");
}Imports IronXL
Shared Sub Main(ByVal args() As String)
Dim wb As WorkBook = WorkBook.Load("sample.xlsx")
Dim ws As WorkSheet = wb.GetWorkSheet("Sheet1")
'Specifying Range from B5 to G5
ws("B5:G5").Replace("Normal", "Good")
wb.SaveAs("sample.xlsx")
End SubThe above code will replace the Normal value with Good from B5 to G5, and the rest of the WorkSheet remains the same. See more about how to Edit Excel Cell Values in a Range and using this function of IronXL.
3.3. Remove Rows of Excel File
In application development, we soemtimes need to remove whole rows of existing Excel files programmatically. For this task, we use the RemoveRow() function of IronXL. Here's an example:
using IronXL;
static void Main(string[] args)
{
WorkBook wb = WorkBook.Load("sample.xlsx");
WorkSheet ws = wb.GetWorkSheet("Sheet1");
//Remove the row number 2
ws.Rows[2].RemoveRow();
wb.SaveAs("sample.xlsx");
}using IronXL;
static void Main(string[] args)
{
WorkBook wb = WorkBook.Load("sample.xlsx");
WorkSheet ws = wb.GetWorkSheet("Sheet1");
//Remove the row number 2
ws.Rows[2].RemoveRow();
wb.SaveAs("sample.xlsx");
}Imports IronXL
Shared Sub Main(ByVal args() As String)
Dim wb As WorkBook = WorkBook.Load("sample.xlsx")
Dim ws As WorkSheet = wb.GetWorkSheet("Sheet1")
'Remove the row number 2
ws.Rows(2).RemoveRow()
wb.SaveAs("sample.xlsx")
End SubThe above code will remove row number 2 of sample.xlsx.
Tutorial Quick Access
IronXL Reference
Read the Object Reference for IronXL to read more about all the functions, features, classes, and namespaces.
IronXL Reference
