Tutorials
.NET
Spire.XLS for .NET
Program Guide
Document Operation

C#/VB.NET: Create, Read, or Update Excel Documents

Excel spreadsheet is a widely used file format that enables users to organize, analyze, and present data in a tabular format. The ability to interact with Excel files programmatically is highly valuable, as it allows automation and integration of Excel functionality into software applications. This capability is particularly useful when working with large datasets, performing complex calculations, or when data needs to be dynamically generated or updated. In this article, you will learn how to create, read, or update Excel documents in C# and VB.NET using Spire.XLS for .NET.

Install Spire.XLS for .NET

To begin with, you need to add the DLL files included in the Spire.XLS for .NET package as references in your .NET project. The DLL files can be either downloaded from this link or installed via NuGet.

PM> Install-Package Spire.XLS

Create an Excel File in C#, VB.NET

Spire.XLS for .NET offers a variety of classes and interfaces that you can use to create and edit Excel documents. Here is a list of important classes, properties and methods involved in this article.

Member Description
Workbook class Represents an Excel workbook model.
Workbook.Worksheets.Add() method Adds a worksheet to workbook.
Workbook.SaveToFile() method Saves the workbook to an Excel document.
Worksheet class Represents a worksheet in a workbook.
Worksheet.Range property Gets a specific cell or cell range from worksheet.
Worksheet.Range.Value property Gets or sets the value of a cell.
Worksheet.Rows property Gets a collection of rows in worksheet.
Worksheet.InsertDataTable() method Imports data from DataTable to worksheet.
CellRange class Represents a cell or cell range in worksheet.

The following are the steps to create an Excel document from scratch using Spire.XLS for .NET.

  • Create a Workbook object.
  • Add a worksheet using Workbook.Worksheets.Add() method.
  • Write data to a specific cell through Worksheet.Range.Value property.
  • Import data from a DataTable to the worksheet using Worksheet.InsertDataTable() method.
  • Save the workbook to an Excel document using Workbook.SaveToFile() method.
  • C#
  • VB.NET
using Spire.Xls;
using System.Data;

namespace CreateExcelSpreadsheet
{
    class Program
    {
        static void Main(string[] args)
        {
            //Create a Workbook object
            Workbook wb = new Workbook();

            //Remove default worksheets
            wb.Worksheets.Clear();

            //Add a worksheet and name it "Employee"
            Worksheet sheet = wb.Worksheets.Add("Employee");

            //Merge the cells between A1 and G1
            sheet.Range["A1:G1"].Merge();

            //Write data to A1 and apply formatting to it
            sheet.Range["A1"].Value = "Basic Information of Employees of Huanyu Automobile Company";
            sheet.Range["A1"].HorizontalAlignment = HorizontalAlignType.Center;
            sheet.Range["A1"].VerticalAlignment = VerticalAlignType.Center;
            sheet.Range["A1"].Style.Font.IsBold = true;
            sheet.Range["A1"].Style.Font.Size = 13F;

            //Set row height of the first row
            sheet.Rows[0].RowHeight = 30F;

            //Create a DataTable
            DataTable dt = new DataTable();
            dt.Columns.Add("Name");
            dt.Columns.Add("Gender");
            dt.Columns.Add("Birth Date");
            dt.Columns.Add("Educational Background");
            dt.Columns.Add("Contact Number");
            dt.Columns.Add("Position");
            dt.Columns.Add("ID");
            dt.Rows.Add("Allen", "Male", "1990-02-10", "Bachelor", "24756854", "Mechanic", "0021");
            dt.Rows.Add("Patrick", "Male", "1985-06-08", "Master", "59863247", "Mechanic", "0022");
            dt.Rows.Add("Jenna", "Female", "1989-11-25", "Bachelor", "79540352", "Sales", "0023");
            dt.Rows.Add("Tommy", "Male", "1988-04-16", "Master", "52014060", "Mechanic", "0024");
            dt.Rows.Add("Christina", "Female", "1998-01-21", "Bachelor", "35401489", "HR", "0025");

            //Import data from DataTable to worksheet
            sheet.InsertDataTable(dt, true, 2, 1, true);

            //Set row height of a range
            sheet.Range["A2:G7"].RowHeight = 15F;

            //Set column width 
            sheet.Range["A2:G7"].Columns[2].ColumnWidth = 15F;
            sheet.Range["A2:G7"].Columns[3].ColumnWidth = 21F;
            sheet.Range["A2:G7"].Columns[4].ColumnWidth = 15F;

            //Set border style of a range
            sheet.Range["A2:G7"].BorderAround(LineStyleType.Medium);
            sheet.Range["A2:G7"].BorderInside(LineStyleType.Thin);
            sheet.Range["A2:G2"].BorderAround(LineStyleType.Medium);
            sheet.Range["A2:G7"].Borders.KnownColor = ExcelColors.Black;

            //Save to a .xlsx file
            wb.SaveToFile("NewSpreadsheet.xlsx", FileFormat.Version2016);
        }
    }
}

C#/VB.NET: Create, Read, or Update Excel Documents

Read Data of a Worksheet in C#, VB.NET

The Worksheet.Range.Value property returns number value or text value of a cell as a string. To get data of a whole worksheet or a cell range, loop through the cells within it. The following are the steps to get data of a worksheet using Spire.XLS for .NET.

  • Create a Workbook object.
  • Load an Excel document using Workbook.LoadFromFile() method.
  • Get a specific worksheet through Workbook.Worksheets[index] property.
  • Get the cell range containing data though Worksheet.AllocatedRange property.
  • Iterate through the rows and columns to get cells within the range, and return the value of each cell through CellRange.Value property.
  • C#
  • VB.NET
using Spire.Xls;

namespace ReadExcelData
{
    class Program
    {
        static void Main(string[] args)
        {
            //Create a Workbook object
            Workbook wb = new Workbook();

            //Load an existing Excel file
            wb.LoadFromFile(@"C:\Users\Administrator\Desktop\NewSpreadsheet.xlsx");

            //Get the first worksheet
            Worksheet sheet = wb.Worksheets[0];

            //Get the cell range containing data
            CellRange locatedRange = sheet.AllocatedRange;

            //Iterate through the rows
            for (int i = 0;i < locatedRange.Rows.Length;i++)
            {
                //Iterate through the columns
                for (int j = 0; j < locatedRange.Rows[i].ColumnCount; j++)
                {
                    //Get data of a specific cell
                    Console.Write(locatedRange[i + 1, j + 1].Value + "  ");

                }
                Console.WriteLine();            
            }
        }
    }
}

C#/VB.NET: Create, Read, or Update Excel Documents

Update an Excel Document in C#, VB.NET

To change the value of a certain cell, just re-assign a value to it through Worksheet.Range.Value property. The following are the detailed steps.

  • Create a Workbook object.
  • Load an Excel document using Workbook.LoadFromFile() method.
  • Get a specific worksheet through Workbook.Worksheets[index] property.
  • Change the value of a particular cell though Worksheet.Range.Value property.
  • Save the workbook to an Excel file using Workbook.SaveToFile() method.
  • C#
  • VB.NET
using Spire.Xls;

namespace UpdateCellValue
{
    class Program
    {
        static void Main(string[] args)
        {
            //Create a Workbook object
            Workbook wb = new Workbook();

            //Load an existing Excel file
            wb.LoadFromFile(@"C:\Users\Administrator\Desktop\NewSpreadsheet.xlsx");

            //Get the first worksheet
            Worksheet sheet = wb.Worksheets[0];

            //Change the value of a specific cell
            sheet.Range["A1"].Value = "Updated Value";

            //Save to file
            wb.SaveToFile("Updated.xlsx", ExcelVersion.Version2016);
        }
    }
}

C#/VB.NET: Create, Read, or Update Excel Documents

Apply for a Temporary License

If you'd like to remove the evaluation message from the generated documents, or to get rid of the function limitations, please request a 30-day trial license for yourself.

See Also