VB .NET Excel Files
Published: 2022-03-07 07:47:23

---
canonical_url: https://ironsoftware.com/csharp/excel/docs/questions/vb-net-excel-files/
---

Developers need a smooth and simple approach to accessing VB .NET Excel files. In this walkthrough, we'll use IronXL to read VB dotnet Excel files and access all data for our project use. We'll learn about creating spreadsheets in all formats ( `.xls`, `.xlsx`, `.csv` and `.tsv` ), as well as setting cell styles and inserting data using VB.NET Excel programming.

**VB:**
```
Imports IronXL
Sub Main()
    Dim wb As New WorkBook(ExcelFileFormat.XLSX)
    Dim ws1 As WorkSheet = wb.CreateWorkSheet("Sheet1")
    ws1("A1").Value = "Hello"
    ws1("A2").Value = "World"
    ws1("B1:B8").Value = "RangeValue"
    wb.SaveAs("Sample.xlsx")
End Sub
```

---

**Step 1**

## 1. Excel for VB.NET Library

Get the IronXL Excel for VB.NET Library using [DLL Download](https://ironsoftware.com/csharp/excel/packages/IronXL.Package.For.vb.net.excel.files.zip) or [NuGet](https://www.nuget.org/packages/IronXL.Excel). IronXL is our Step 1 to quickly accessing Excel data in our VB.NET projects, and what we'll be using for this tutorial (free for development).

`PM > Install-Package IronXL.Excel`

---

**How to Tutorial**

## 2. Create Excel Files in VB.NET

IronXL provides the simplest approach to create an Excel (`.xlsx` format) file in a VB.NET project. After this, we can insert data and also set cell properties like font styles or borders.

### 2.1. Create Excel File

Let's first create a WorkBook:

**VB:**
```
Dim wb As New WorkBook
```

The above code is for creating a new Excel file. By default, its extension is `.xlsx`.

## 2.2. Create XLS File

In the case that you want to create an `.xls` extension file, then you can use this code.

**VB:**
```
Dim wb As New WorkBook(ExcelFileFormat.XLS)
```

### 2.3. Create WorkSheet

After creating the WorkBook, an Excel WorkSheet can be created as follows:

**VB:**
```
Dim ws1 As WorkSheet = wb.CreateWorkSheet("Sheet1")
```

The above code will create new WorkSheet `ws1` with name `sheet1` in WorkBook `wb`.

### 2.4. Create Multiple Worksheets

Any number of WorkSheets can be created in the same way:

**VB:**
```
Dim ws2 As WorkSheet = wb.CreateWorkSheet("Sheet2")
Dim ws3 As WorkSheet = wb.CreateWorkSheet("Sheet3")
```

---

## 3. Insert Data into WorkSheet

### 3.1. Insert Data into Cells

Now we can easily insert data into WorkSheet cells as follows:

**VB:**
```
 worksheet("CellAddress").Value = "MyValue"
```

For example, data in worksheet `ws1` can be inserted as:

**VB:**
```
ws1("A1").Value = "Hello World"
```

The above code will write `Hello World` in `A1` cell of WorkSheet `ws1`.

### 3.2. Insert Data into Range

It is also possible to write data into many cells using the range function as follows:

**VB:**
```
ws1("A3:A8").Value = "NewValue"
```

The above code will write `NewValue` from cell `A3` to `A8` in worksheet `ws1`.

### 3.3. Create and Edit WorkSheets Example

We will create a new Excel file `Sample.xlsx` and insert some data in it to showcase the code we learned above.

**VB:**
```
Imports IronXL
Sub Main()
    Dim wb As New WorkBook(ExcelFileFormat.XLSX)
    Dim ws1 As WorkSheet = wb.CreateWorkSheet("Sheet1")
    ws1("A1").Value = "Hello"
    ws1("A2").Value = "World"
    ws1("B1:B8").Value = "RangeValue"
    wb.SaveAs("Sample.xlsx")
End Sub
```

**Note: By default, new Excel file will create in `bin>Debug` folder of the project.If we want to create new file in custom path then: `wb.SaveAs(@"E:\IronXL\Sample.xlsx")`**

Here is the screenshot of our newly created Excel file `sample.xlsx`:

![Image 5](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/gl3o0yz4giod6oy0m3k9.png)

It is clear how simple it can be to create Excel files using `IronXL` in a VB.NET Application.

---

## 4. Read Excel File in VB.Net

IronXl also provides a simple approach to read Excel (`.xlsx`) files in your VB dotnet project. For this purpose, simply get the Excel document, load it in your project, read its data, and use it as per your requirements.

Follow these steps:

### 4.1. Access Excel File in Project

`WorkBook` is the class of IronXL whose object provides full access to the Excel file and its functions. For example, if we want to access the Excel file, we simply use:

**VB:**
```
WorkBook wb = WorkBook.Load("sample.xlsx") 'Excel file path
```

In the code above, `WorkBook.Load()` function load `sample.xlsx` in `wb`. Any type function can be performed on `wb` by accessing specific WorkSheet of Excel file.


### 4.2. Access Specifc WorkSheet

To access a specific sheet in Excel, take the `WorkSheet` class, which can be used in the following different ways:

#### 4.2.1 By Sheet Name

**VB:**
```
Dim ws As WorkSheet = WorkBook.GetWorkSheet("sheet1") 'by sheet name
```

`wb` is the WorkBook declared in the above section.

#### 4.2.2. By Sheet Index

**VB:**
```
 Dim ws As WorkSheet = WorkBook.WorkSheets(0) 'by sheet index
```

#### 4.2.3. Default Sheet

**VB:**
```
Dim ws As WorkSheet = workbook.DefaultWorkSheet() 'for the default sheet: 
```

#### 4.2.4. First Sheet

**VB:**
```
Dim sheet As WorkSheet = workbook.WorkSheets.FirstOrDefault() 'for the first sheet:
```

#### 4.2.5. First or Default Sheet

**VB:**
```
Dim sheet As WorkSheet = workbook.WorkSheets.FirstOrDefault() 'for the first or default sheet:
```

After getting Excel sheet `ws` , you can get any type of data from the corresponding WorkSheet of the Excel file and perform all Excel functions.

---

## 5. Access Data From WorkSheet

Data can be accessed from the ExcelSheet `ws` in this way:

**VB:**
```
Dim int_Value As Integer = sheet("A2").IntValue 'for integer
Dim str_value As String = sheet("A2").ToString() 'for string
```

### 5.1. Data from Specific Column

It is also possible to get data from many cells of specific column by the following way

**VB:**
```
For Each cell In sheet("A2:A10")
    Console.WriteLine("value is: {0}", cell.Text)       
Next cell
```

It will display values from cell `A2` to `A10`. A code example of above whole discussion is given below.

**VB:**
```
Imports IronXL
Sub Main()
    Dim wb As WorkBook = WorkBook.Load("sample.xlsx")
    Dim ws As WorkSheet = wb.WorkSheets.FirstOrDefault()
    For Each cell In ws("A2:A10")
        Console.WriteLine("value is: {0}", cell.Text)
    Next
    Console.ReadKey() 
End Sub
```

This will display the following output:

![Image 6](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/qodhde657sbzypaetmvw.png)

And we can see a Screenshot of Excel file `Sample.xlsx`

![Image 7](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/f1kk6trfjfv0y4vdto43.png)

---

## 6. Perform Functions on Data

It is simple to access filtered data from an Excel WorkSheet by applying aggregate functions like Sum, Min or Max in the following way:

**VB:**
```
Dim sum As Decimal = ws("From:To").Sum()
Dim min As Decimal = ws("From:To").Min()
Dim max As Decimal = ws("From:To").Max()
```
 
You can read more about Excel Aggregate Functions here.

**VB:**
```
Imports IronXL
Sub Main()
    Dim wb As WorkBook = WorkBook.Load("sample.xlsx")
    Dim ws As WorkSheet = wb.WorkSheets.FirstOrDefault()
    Dim sum As Decimal = ws("G2:G10").Sum()
    Dim min As Decimal = ws("G2:G10").Min()
    Dim max As Decimal = ws("G2:G10").Max()
    Console.WriteLine("Sum is: {0}", sum)
    Console.WriteLine("Min is: {0}", min)
    Console.WriteLine("Max is: {0}", max)
    Console.ReadKey()
End Sub
```

This code will give us this display:

![Image 8](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/8w8pmerzd6xgjucwf8zu.png)

And this Excel file `Sample.xlsx`:

![Image 9](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/viqupzm1aov2dc316ayz.png)
 
You can learn more about how to read Excel in the linked article.

---