using IronXL; using System; using System.Linq; // Supported for XLSX, XLS, XLSM, XLTX, CSV and TSV WorkBook workBook = WorkBook.Load("sample.xlsx"); // Select worksheet at index 0 WorkSheet workSheet = workBook.WorkSheets[0]; // Get any existing worksheet WorkSheet firstSheet = workBook.DefaultWorkSheet; // Select a cell and return the converted value int cellValue = workSheet["A2"].IntValue; // Read from ranges of cells elegantly. foreach (var cell in workSheet["A2:A10"]) { Console.WriteLine("Cell {0} has value '{1}'", cell.AddressString, cell.Text); } // Calculate aggregate values such as Min, Max and Sum decimal sum = workSheet["A2:A10"].Sum(); // Linq compatible decimal max = workSheet["A2:A10"].Max(c => c.DecimalValue);

using IronSoftware.Drawing; using IronXL; using IronXL.Styles; using System.Linq; WorkBook workBook = WorkBook.Load("sample.xls"); WorkSheet workSheet = workBook.WorkSheets.First(); var range = workSheet["A1:H10"]; var cell = range.First(); // Set background color of the cell with an rgb string cell.Style.SetBackgroundColor("#428D65"); // Apply styling to the whole range. // Set underline property to the font // FontUnderlineType is enum that stands for different types of font underlying range.Style.Font.Underline = FontUnderlineType.SingleAccounting; // Define whether to use horizontal line through the text or not range.Style.Font.Strikeout = false; // Define whether the font is bold or not range.Style.Font.Bold = true; // Define whether the font is italic or not range.Style.Font.Italic = false; // Get or set script property of a font // Font script enum stands for available options range.Style.Font.FontScript = FontScript.Super; // Set the type of the border line // There are also TopBorder,LeftBorder,RightBorder,DiagonalBorder properties // BorderType enum indicates the line style of a border in a cell range.Style.BottomBorder.Type = BorderType.MediumDashed; // Indicate whether the cell should be auto-sized range.Style.ShrinkToFit = true; // Set alignment of the cell range.Style.VerticalAlignment = VerticalAlignment.Bottom; // Set border color range.Style.DiagonalBorder.SetColor("#20C96F"); // Define border type and border direction as well range.Style.DiagonalBorder.Type = BorderType.Thick; // DiagonalBorderDirection enum stands for direction of diagonal border inside cell range.Style.DiagonalBorderDirection = DiagonalBorderDirection.Forward; // Set background color of cells range.Style.SetBackgroundColor(Color.Aquamarine); // Set fill pattern of the cell // FillPattern enum indicates the style of fill pattern range.Style.FillPattern = FillPattern.Diamonds; // Set the number of spaces to intend the text range.Style.Indention = 5; // Indicate if the text is wrapped range.Style.WrapText = true; workBook.SaveAs("stylingOptions.xls");

Published January 28, 2024

How to Save An Excel File in VB .NET

Introduction

Microsoft Excel is a popular multipurpose spreadsheet program that is well-known for its efficiency in data organization, analysis, and visualization. Excel is a program that functions in a manner like a grid. It organizes data into rows and columns and enables users to enter various types of data, including text, numbers, dates, and formulas within individual cells.

Its powerful computation capabilities via formulas and functions allow users to carry out various logical, statistical, and mathematical tasks.

Excel provides capabilities for organizing and analyzing data, as well as creating graphs and charts to depict the data graphically. It promotes teamwork by enabling file sharing and concurrent editing among several people. Because of its versatility in meeting a variety of user demands across sectors, it can be customized and automated through the use of macros, VBA, and add-ins.

Applications for Excel include finance, research, business analytics, and education. It is a vital tool for managing, analyzing, and making decisions with data. In this article, we're going to look at how you can use VB.NET to save Excel files with the help of IronXL.

How to Use VB.NET to Save an Excel File

Create a new console project.
Install the IronXL library.
Create the necessary object to use the IronXL library.
Use the created object to add the values.
Save the Excel document in the desired location and dispose of the object.

What is IronXL

IronXL is a robust .NET framework designed to make working with Excel files in C#, VB.NET, and other .NET languages easier. Both the XLS and XLSX file formats are compatible with it. Developers can write, read, modify, and create Excel spreadsheets more quickly and easily with the help of this library. There are also a wide range of tools and features available.

Salient attributes and functionalities of IronXL include:

Data handling: Reading, writing, and manipulating data in Excel spreadsheets is made simple with IronXL. Cell values can be retrieved using a two-dimensional array, and calculations, formulas, and data formatting are all possible.
Excel file creation and modification: Developers have the ability to create new Excel files and modify existing ones, as well as add, remove, and manage worksheets. They can also interact with a wide range of Excel elements.
Cross-platform compatibility: Due to its cross-platform compatibility, IronXL can be used in various application scenarios and is compatible with multiple .NET platforms, such as Xamarin, .NET Core, and .NET Framework.
Versatility and compatibility: It works with several Excel versions and supports the newer XLSX Excel format in addition to the older XLS Excel format.
Support for both legacy and modern Excel formats: It is capable of supporting both older Excel file formats (XLS, which dates back to Excel 97–2003) and more modern XML-based formats (XLSX, which dates back to Excel 2007).
Usefulness: The library makes Excel-related tasks more accessible to developers with different levels of experience by providing a simple API with clearly understood properties and operations.
Data extraction and export: IronXL makes it easy to connect with databases and other systems by facilitating the extraction of data from Excel files and the export of Excel data to various formats.
Support and documentation: IronXL offers a plethora of tutorials, support, and documentation to help developers utilize its library for Excel-related tasks.
Automation and efficiency: IronXL helps users create data-driven, efficient applications, be more productive, and spend less time on manual labor by automating Excel processes.
Integration and customization: By offering options for exporting Excel data into multiple formats, it facilitates the creation of customized reports and data-driven solutions. It also integrates well with other systems and databases.

Among the many industries that use IronXL are finance, data analysis, reporting, business intelligence, and software development. It enables programmers to interact with Excel files and create reliable solutions by combining data manipulation with Excel integration. For more information, visit this link.

Create a New Visual Studio Project

Creating a Visual Studio console project is simple. Follow these steps to develop a console application using Visual Studio:

Open Visual Studio: Make sure Visual Studio is installed on your computer before opening it.

Start a New Project

To start a new project, follow these steps:

Choose File, New, and finally Project.
Choose your favorite programming language (such as VB.NET) from the left panel of the "Create a new project" box.
Next, select the "Console App" or "Console App (.NET Core)" template from the list of available project templates.
Provide a name for your project in the "Name" field.
Choose the location where you want to save the project.
Click Create to create a new console application project.

Installing IronXL Library

To install the IronXL library, follow these steps:

Install the IronXL library as it is required for the upcoming steps. To do this, open the NuGet Package Manager Console and enter the following command:
```
Install-Package IronXL.Excel
```
Alternatively, you can search for the package "IronXL" using the NuGet Package Manager. From the list of all the NuGet packages related to IronXL, select the ones that need to be downloaded.

Saving an Excel File with IronXL in VB.NET

You can follow these steps to save an Excel file using IronXL in a VB.NET application. Here is an example code snippet that demonstrates how to use IronXL in VB.NET to save an Excel document:

Imports IronXL
Module Program
    Sub Main(args As String())
        Dim excelFilePath As String = "file path here"
        ' Create a new Excel workbook
        Dim workbook As WorkBook = WorkBook.Create(ExcelFileFormat.XLSX)
        ' Add data or perform operations within the workbook (optional)
        Dim worksheet As WorkSheet = workbook.CreateWorkSheet("Sheet1")
        worksheet("A1").Value = "Hello"
        worksheet("B1").Value = "IronXL!"
        ' Save the Excel workbook to the specified path
        workbook.SaveAs(excelFilePath)
        Console.WriteLine("Excel file saved successfully at: " & excelFilePath)
    End Sub
End Module

Imports IronXL
Module Program
    Sub Main(args As String())
        Dim excelFilePath As String = "file path here"
        ' Create a new Excel workbook
        Dim workbook As WorkBook = WorkBook.Create(ExcelFileFormat.XLSX)
        ' Add data or perform operations within the workbook (optional)
        Dim worksheet As WorkSheet = workbook.CreateWorkSheet("Sheet1")
        worksheet("A1").Value = "Hello"
        worksheet("B1").Value = "IronXL!"
        ' Save the Excel workbook to the specified path
        workbook.SaveAs(excelFilePath)
        Console.WriteLine("Excel file saved successfully at: " & excelFilePath)
    End Sub
End Module

VB.NET

To save an Excel document, replace "file path here" with the appropriate location and filename. You can modify the data or perform other operations within the workbook before saving.

This code snippet creates a new Excel workbook, adds a worksheet named "Sheet1", uses IronXL to write data into cells A1 and B1, and saves the workbook in the XLSX format at the specified file path. Ensure that the location where you plan to save the Excel file has the necessary write permissions. You can also use an existing template Excel file to save the documents.

By running this code, you will be able to use IronXL in a VB.NET program to create an Excel sheet in the specified file directory with the desired data and structure. Make any necessary adjustments to the workbook's data and settings before saving it in the XLSX file format.

To learn more about this code, refer to the documentation here.

Conclusion

Software development, finance, data analysis, and reporting are just a few of the industries that heavily rely on the Excel library IronXL.

The ability to create dynamic, data-centric applications that effectively and programmatically manage Excel files makes IronXL a vital tool for developers and enterprises looking to maximize operations using Excel.

In conclusion, IronXL offers a reliable and flexible way to create, load, and save Excel documents programmatically in a VB.NET context. With its comprehensive functionality and user-friendly API, developers can easily generate Excel files with structured data, formatting, and calculations to effectively meet various data management and reporting needs.

A free Community Edition with limitations for non-commercial usage is available from IronXL. Paid versions come with additional features, support, and full capabilities, with prices starting at $599. Licensing options include subscription-based or perpetual-based models.

For up-to-date and comprehensive licensing details, please refer to the official IronXL website. For more information on Iron Software products, visit this link.