C#/VB.NET: Delete Blank Rows and Columns in Excel
When working with an Excel document, some blank rows or columns may appear due to the modification of data. Although having blank rows or columns in a worksheet isn't necessarily a bad thing, in most cases they are still undesirable. Furthermore, such blank rows or columns may cause errors if formulas are applied. To delete these blank rows or columns, you can simply select and delete them, but if there are a lot of blank rows and columns, deleting them manually is a very time-consuming task. In this article, you will learn how to programmatically delete blank rows and columns in an Excel worksheet 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
Delete Blank Rows and Columns in Excel
The detailed steps are as follows.
- Instantiate a Workbook object.
- Load a sample Excel file using Workbook.LoadFromFile() method.
- Get a specified worksheet using Workbook.Worksheets[] property.
- Loop through all used rows in the specified worksheet and determine whether the row is blank using XlsRange.IsBlank property.
- Delete the blank rows using Worksheet.DeleteRow() method.
- Loop through all used columns in the specified worksheet and determine whether the column is blank using XlsRange.IsBlank property.
- Delete the blank columns using Worksheet.DeleteColumn() method.
- Save the result file using Workbook.SaveToFile() method.
- C#
- VB.NET
using System.Linq;
using Spire.Xls;
namespace DeleteBlankRowsAndColumns
{
class Program
{
static void Main(string[] args)
{
//Instantiate a Workbook object
Workbook workbook = new Workbook();
//Load a sample Excel file
workbook.LoadFromFile("sample.xlsx");
//Get the first worksheet
Worksheet sheet = workbook.Worksheets[0];
//Delete blanks rows
for (int i = sheet.Rows.Count() - 1; i >= 0; i--)
{
if (sheet.Rows[i].IsBlank)
{
sheet.DeleteRow(i + 1); //Index parameter in DeleteRow method starts from 1
}
}
//Delete blank columns
for (int j = sheet.Columns.Count() - 1; j >= 0; j--)
{
if (sheet.Columns[j].IsBlank)
{
sheet.DeleteColumn(j + 1); //Index parameter in DeleteColumn method starts from 1
}
}
//Save the file
workbook.SaveToFile("DeleteBlankRowsAndColumns.xlsx", ExcelVersion.Version2013);
}
}
}
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.
C#/VB.NET: AutoFit Column Width and Row Height in Excel
When the text entered in a cell is too long to be fully displayed in the current cell, the “AutoFit” feature in Excel allows you to quickly adjust the column width or row height to fit all the content and make the entire worksheet more readable. In this article, you will learn how to programmatically AutoFit the column width and row height in an Excel worksheet 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
AutoFit Column Width and Row Height in Excel
The detailed steps are as follows.
- Create a Workbook object.
- Load a sample Excel document using Workbook.LoadFromFile() method.
- Get a specified worksheet using Workbook.Worksheets[] property.
- Get the used range on the specified worksheet using Worksheet.AllocatedRange property.
- AutoFit column width and row height in the range using CellRange.AutoFitColumns() and CellRange.AutoFitRows() methods.
- Save the result file using Workbook.SaveToFile() method.
- C#
- VB.NET
using Spire.Xls;
namespace AutofitColumn
{
class Program
{
static void Main(string[] args)
{
//Create a Workbook object
Workbook workbook = new Workbook();
//Load a sample Excel document
workbook.LoadFromFile(@"E:\Files\Test.xlsx");
//Get the first worksheet
Worksheet worksheet = workbook.Worksheets[0];
//AutoFit column width and row height
worksheet.AllocatedRange.AutoFitColumns();
worksheet.AllocatedRange.AutoFitRows();
//Save the result file
workbook.SaveToFile("AutoFit.xlsx", FileFormat.Version2010);
}
}
}
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.
C#VB.NET: Set Row Height and Column Width in Excel
When creating a spreadsheet, you can adjust the layout and appearance of it by setting the row height and column width. Microsoft Excel provides users with various methods to modify column width and row height, such as dragging the boundaries of columns or rows to the desired size, or entering specific values in the column width box or row height box. However, it is crucial for developers to understand how to achieve this functionality through programming. In this article, we will show you how to set row height and column width in Excel by 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 DLLs files can be either downloaded from this link or installed via NuGet.
PM> Install-Package Spire.XLS
Set the Row Height in Excel
Spire.XLS for .NET supports users to set the row height programmatically by calling Worksheet.SetRowHeight() method. The following are detailed steps.
- Create an object of Workbook class.
- Load a sample file using Workbook.LoadFromFile() method.
- Get the first sheet from this file by using Workbook.Worksheets[] property.
- Set the height of the first row by calling Worksheet.SetRowHeight() method.
- Save the result file using Workbook.SaveToFile() method.
- C#
- VB.NET
using Spire.Xls;
namespace SetExcelRow
{
class Program
{
static void Main(string[] args)
{
//Create an object of Workbook class
Workbook workbook = new Workbook();
//Load a sample file from disk
workbook.LoadFromFile(@"sample.xlsx");
//Get the first worksheet from the sample file
Worksheet sheet = workbook.Worksheets[0];
//Set the row height of the first row
sheet.SetRowHeight(1, 25);
//Save the result file
workbook.SaveToFile("SetRow.xlsx", ExcelVersion.Version2013);
workbook.Dispose();
}
}
}
Set the Column Width in Excel
What's more, Spire.XLS for .NET also enable users to set the column width in Excel programmatically by calling Worksheet.SetColumnWidth() method. The following are detailed steps.
- Create an object of Workbook class.
- Load a sample file using Workbook.LoadFromFile() method.
- Get the first sheet from this file by using Workbook.Worksheets[] property.
- Set the width of the fourth column by calling Worksheet.SetColumnWidth() method.
- Save the result file using Workbook.SaveToFile() method.
- C#
- VB.NET
using Spire.Xls;
namespace SetExcelColumn
{
class Program
{
static void Main(string[] args)
{
//Create an object of Workbook class
Workbook workbook = new Workbook();
//Load a sample file from disk
workbook.LoadFromFile(@"sample.xlsx");
//Get the first worksheet from the sample file
Worksheet sheet = workbook.Worksheets[0];
//Set the column width of the fourth column
sheet.SetColumnWidth(4, 15);
//Save the result file
workbook.SaveToFile("SetColumn.xlsx", ExcelVersion.Version2013);
workbook.Dispose();
}
}
}
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.
C#/VB.NET: Delete Rows and Columns from Excel
When the data in specific rows or columns are no longer needed, you can delete those rows or columns from your worksheet. In this article, you will learn how to delete rows and columns from Excel in C# and VB.NET using Spire.XLS for .NET library.
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 DLLs files can be either downloaded from this link or installed via NuGet.
PM> Install-Package Spire.XLS
Delete a Specific Row and Column from Excel in C# and VB.NET
The following are the steps to delete a specific row and column from an Excel worksheet:
- Create a Workbook instance.
- Load an Excel file using Workbook.LoadFromFile() method.
- Get the desired worksheet using Workbook.Worksheets[sheetIndex] property.
- Delete the desired row from the worksheet by its index (1-based) using Worksheet.DeleteRow(rowIndex) method.
- Delete the desired column from the worksheet by its index (1-based) using Worksheet.DeleteColumn(columnIndex) method.
- Save the result file using Workbook.SaveToFile() method.
- C#
- VB.NET
using Spire.Xls;
namespace DeleteRowAndColumn
{
class Program
{
static void Main(string[] args)
{
//Create a Workbook instance
Workbook workbook = new Workbook();
//Load an Excel file
workbook.LoadFromFile("Sample.xlsx");
//Get the first worksheet
Worksheet sheet = workbook.Worksheets[0];
//Delete the 9th row
sheet.DeleteRow(9);
//Delete the 3rd column
sheet.DeleteColumn(3);
//Save the result file
workbook.SaveToFile("DeleteRowAndColumn.xlsx", ExcelVersion.Version2016);
}
}
}
Delete Multiple Rows and Columns from Excel in C# and VB.NET
The following are the steps to delete multiple rows and columns from an Excel worksheet:
- Create a Workbook instance.
- Load an Excel file using Workbook.LoadFromFile() method.
- Get the desired worksheet using Workbook.Worksheets[sheetIndex] property.
- Delete the desired rows from the worksheet using Worksheet.DeleteRow(startRowIndex, rowCount) method.
- Delete the desired columns from the worksheet using Worksheet.DeleteColumn(startColumnIndex, columnCount) method.
- Save the result file using Workbook.SaveToFile() method.
- C#
- VB.NET
using Spire.Xls;
namespace DeleteMultipleRowsAndColumns
{
class Program
{
static void Main(string[] args)
{
//Create a Workbook instance
Workbook workbook = new Workbook();
//Load an Excel file
workbook.LoadFromFile(@"Sample.xlsx");
//Get the first worksheet
Worksheet sheet = workbook.Worksheets[0];
//Delete 3 rows from the worksheet starting from the 7th row
sheet.DeleteRow(7, 3);
//Delete 3 columns from the worksheet starting from the 3rd column
sheet.DeleteColumn(3, 3);
//Save the result file
workbook.SaveToFile("DeleteMultipleRowsAndColumns.xlsx", ExcelVersion.Version2016);
}
}
}
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.
C#/VB.NET Hide or Show Rows and Columns in Excel
When dealing with Excel files containing large amounts of data, you may sometimes need to hide certain rows and columns to conceal useless data so that you can focus on the information you need to analyze. In this article, you will learn how to hide or show rows and columns in Excel in C# and VB.NET using Spire.XLS for .NET.
- Hide Specific Rows and Columns in Excel
- Show Specific Hidden Rows and Columns in Excel
- Hide Multiple Rows and Columns at Once in Excel
- Show All Hidden Rows and Columns in Excel
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
Hide Specific Rows and Columns in Excel in C# and VB.NET
The following steps demonstrate how to hide specific rows and columns in Excel in C# and VB.NET:
- Initialize an instance of the Workbook class.
- Load an Excel file using Workbook.LoadFromFile() method.
- Get a specific worksheet through Workbook.Worksheets[int sheetIndex] property.
- Hide specific rows in the worksheet using Worksheet.HideRow(int rowIndex) method.
- Hide Specific columns in the worksheet using Worksheet.HideColumn(int columnIndex) method.
- Save the result file using Workbook.SaveToFile() method.
- C#
- VB.NET
using Spire.Xls;
namespace HideExcelRowsAndColumns
{
class Program
{
static void Main(string[] args)
{
//Create a Workbook instance
Workbook workbook = new Workbook();
//Load an Excel file
workbook.LoadFromFile("Input.xlsx");
//Get the first worksheet
Worksheet sheet = workbook.Worksheets[0];
//Hide the 3rd and the 7th rows
sheet.HideRow(3);
sheet.HideRow(7);
//Hide the 3rd and the 6th columns
sheet.HideColumn(3);
sheet.HideColumn(6);
//Save the result file
workbook.SaveToFile("HideRowsAndColumns.xlsx", ExcelVersion.Version2013);
}
}
}
Show Specific Hidden Rows and Columns in Excel in C# and VB.NET
The following steps demonstrate how to show specific hidden rows and columns in Excel in C# and VB.NET:
- Initialize an instance of the Workbook class.
- Load an Excel file using Workbook.LoadFromFile() method.
- Get a specific worksheet through Workbook.Worksheets[int sheetIndex] property.
- Show specific hidden rows in the worksheet using Worksheet.ShowRow(int rowIndex) method.
- Show specific hidden columns in the worksheet using Worksheet.ShowColumn(int columnIndex) method.
- Save the result file using Workbook.SaveToFile() method.
- C#
- VB.NET
using Spire.Xls;
namespace ShowExcelRowsAndColumns
{
class Program
{
static void Main(string[] args)
{
//Create a Workbook instance
Workbook workbook = new Workbook();
//Load an Excel file
workbook.LoadFromFile("HideRowsAndColumns.xlsx");
//Get the first worksheet
Worksheet sheet = workbook.Worksheets[0];
//Show the 3rd and the 7th rows
sheet.ShowRow(3);
sheet.ShowRow(7);
//Show the 3rd and the 6th columns
sheet.ShowColumn(3);
sheet.ShowColumn(6);
//Save the result file
workbook.SaveToFile("ShowRowsAndColumns.xlsx", ExcelVersion.Version2013);
}
}
}
Hide Multiple Rows and Columns at Once in Excel in C# and VB.NET
The following steps demonstrate how to hide multiple rows and columns at once in Excel in C# and VB.NET:
- Initialize an instance of the Workbook class.
- Load an Excel file using Workbook.LoadFromFile() method.
- Get a specific worksheet through Workbook.Worksheets[int sheetIndex] property.
- Hide multiple rows in the worksheet using Worksheet.HideRows(int rowIndex, int rowCount) method.
- Hide multiple columns in the worksheet using Worksheet.HideColumns(int columnIndex, int columnCount) method.
- Save the result file using Workbook.SaveToFile() method.
- C#
- VB.NET
using Spire.Xls;
namespace HideMultipleExcelRowsAndColumns
{
class Program
{
static void Main(string[] args)
{
//Create a Workbook instance
Workbook workbook = new Workbook();
//Load an Excel file
workbook.LoadFromFile("Input.xlsx");
//Get the first worksheet
Worksheet sheet = workbook.Worksheets[0];
//Hide 3, 4 and 5 rows
sheet.HideRows(3, 3);
//Hide 5, 6 and 7 columns
sheet.HideColumns(5, 3);
//Save the result file
workbook.SaveToFile("HideMultipleRowsAndColumns.xlsx", ExcelVersion.Version2013);
}
}
}
Show All Hidden Rows and Columns in Excel in C# and VB.NET
The following steps demonstrate how to show all hidden rows and columns in Excel in C# and VB.NET:
- Initialize an instance of the Workbook class.
- Load an Excel file using Workbook.LoadFromFile() method.
- Get a specific worksheet through Workbook.Worksheets[int sheetIndex] property.
- Iterate through the rows in the worksheet and find the hidden rows using Worksheet.GetRowIsHide(int rowIndex) method.
- Show all hidden rows using Worksheet.ShowRow(int rowIndex) method.
- Iterate through the columns in the worksheet and find the hidden columns using Worksheet.GetColumnIsHide(int columnIndex) method.
- Show all hidden columns using Worksheet.ShowColumn(int columnIndex) method.
- Save the result file using Workbook.SaveToFile() method.
- C#
- VB.NET
using Spire.Xls;
namespace ShowAllHiddenRowsAndColumns
{
class Program
{
static void Main(string[] args)
{
//Create a Workbook instance
Workbook workbook = new Workbook();
//Load an Excel file
workbook.LoadFromFile("HideRowsAndColumns.xlsx");
//Get the first worksheet
Worksheet sheet = workbook.Worksheets[0];
//Iterate through the rows in the worksheet
for (int i = 1; i <= sheet.LastRow; i++)
{
//Check if the current row is hidden
if (sheet.GetRowIsHide(i))
{
//Show the hidden row
sheet.ShowRow(i);
}
}
//Iterate through the columns in the worksheet
for (int j = 1; j <= sheet.LastRow; j++)
{
//Check if the current column is hidden
if (sheet.GetColumnIsHide(j))
{
//Show the hidden column
sheet.ShowColumn(j);
}
}
//Save the result file
workbook.SaveToFile("ShowAllHiddenRowsAndColumns.xlsx", ExcelVersion.Version2013);
}
}
}
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.
C#: Create Dropdown Lists in an Excel Document
The purpose of creating dropdown lists in an Excel document is to provide a convenient way for data input, restricting users to select only from predefined options. This helps prevent input errors and ensures data accuracy. With dropdown lists, users can choose from a fixed list of options without the need to manually enter data, thereby reducing the possibility of input errors. This article will introduce how to use Spire.XLS for .NET to create dropdown lists in Excel documents within a C# project.
- Create Dropdown List using String Array in C#
- Create Dropdown List using Data from the Same Worksheet in C#
- Create Dropdown List using Data from Different Worksheets in C#
- Create Linked Dropdown Lists in the Same Worksheet in C#
- Create Linked Dropdown Lists across Different Worksheets in C#
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 Dropdown List using String Array in C#
Using Spire.XLS, you can easily create dropdown lists in cells by assigning a string array to the CellRange.DataValidation.Values property. Here are the detailed steps:
- Create a Workbook object.
- Use Workbook.Worksheets[0] to get the first worksheet in the workbook.
- Create a string array and assign it to the CellRange.DataValidation.Values property.
- Save the document to a specified path using the Workbook.SaveToFile() method.
- C#
using Spire.Xls;
namespace SpireXlsDemo
{
internal class Program
{
static void Main(string[] args)
{
// Create a Workbook object
Workbook workbook = new Workbook();
// Get the first Worksheet in the Workbook object
Worksheet worksheet = workbook.Worksheets[0];
// Create a string array
string[] values = new string[] { "Apple", "Banana", "Orange", "Strawberry", "Grape" };
// Set data validation in cell A1 of the worksheet, restricting selectable values to those in the values array
worksheet.Range["A1"].DataValidation.Values = values;
// Save the Workbook object as an Excel file
workbook.SaveToFile("DropdownListCreatedFromArray.xlsx", ExcelVersion.Version2016);
// Dispose of the resources occupied by the Workbook object
workbook.Dispose();
}
}
}
Create Dropdown List using Data from the Same Worksheet in C#
The following example demonstrates creating a dropdown list in the worksheet where the data is located by specifying the data range and applying it to the corresponding cell. Here are the detailed steps:
- Create a Workbook object.
- Load an Excel document using the Workbook.LoadFromFile() method.
- Access the first worksheet in the workbook using Workbook.Worksheets[0].
- Get the data range using Worksheet.Range[""].
- Specify cell B1 as a dropdown list and set the data range using Worksheet.Range["B1"].DataValidation.DataRange.
- Save the document to a specified path using the Workbook.SaveToFile() method.
- C#
using Spire.Xls;
namespace SpireXlsDemo
{
internal class Program
{
static void Main(string[] args)
{
// Create a new workbook object
Workbook workbook = new Workbook();
// Load workbook data from a file
workbook.LoadFromFile("Sample1.xlsx");
// Get the first worksheet in the workbook
Worksheet worksheet = workbook.Worksheets[0];
// Get the cell range from A3 to A8 in the worksheet
CellRange dataRange = worksheet.Range["A3:A8"];
// Set cell B1 as a dropdown with data range obtained earlier
worksheet.Range["B1"].DataValidation.DataRange = dataRange;
// Save the workbook to a new file
workbook.SaveToFile("CreateDropdownInSameWorksheet.xlsx", ExcelVersion.Version2016);
// Dispose of the workbook resources
workbook.Dispose();
}
}
}
Create Dropdown List using Data from Different Worksheets in C#
This example demonstrates how to specify a data range in one worksheet and apply it to a specific cell in another worksheet. In this process, it is necessary to ensure that Worksheet.ParentWorkbook.Allow3DRangesInDataValidation = true is set to enable the functionality of creating dropdown lists across different worksheets. Here are the detailed steps:
- Create a Workbook object.
- Load an Excel document using the Workbook.LoadFromFile() method.
- Retrieve a specific worksheet from the workbook using Workbook.Worksheets[].
- Set Worksheet.ParentWorkbook.Allow3DRangesInDataValidation = true to enable the use of 3D ranges in data validation within the worksheet.
- Use CellRange.DataValidation.DataRange to specify a cell as a dropdown list and set the data range.
- Save the document to a specified path using the Workbook.SaveToFile() method.
- C#
using Spire.Xls;
namespace SpireXlsDemo
{
internal class Program
{
static void Main(string[] args)
{
// Create a Workbook object
Workbook workbook = new Workbook();
// Load the workbook from a file
workbook.LoadFromFile("Sample2.xlsx");
// Get the first worksheet
Worksheet worksheet1 = workbook.Worksheets[0];
// Get the second worksheet
Worksheet worksheet2 = workbook.Worksheets[1];
// Allow the use of 3D ranges in data validation
worksheet1.ParentWorkbook.Allow3DRangesInDataValidation = true;
// Define the data range
CellRange dataRange = worksheet2.Range["A1:A6"];
// Apply the data range to cell B1 in worksheet 1 as data validation
worksheet1.Range["B1"].DataValidation.DataRange = dataRange;
// Save the workbook to a file
workbook.SaveToFile("Create the Dropdowns Across Different Worksheets.xlsx", ExcelVersion.Version2016);
// Release the resources of the workbook
workbook.Dispose();
}
}
}
Create Linked Dropdown Lists in the Same Worksheet in C#
Linked dropdown menus refer to the automatic updating of options in one dropdown menu based on the selection made in another dropdown menu. This example demonstrates how to create linked dropdown lists (dropdown menus) functionality within the same worksheet. This is achieved by adding an INameRange object and setting the reference range, as well as setting the data range and formula for data validation to achieve the linked dropdown effect. Here are the detailed steps:
- Create a Workbook object.
- Load an Excel document using the Workbook.LoadFromFile() method.
- Retrieve a specific worksheet from the workbook using Workbook.Worksheets[].
- Add an INamedRange object and set the reference range.
- Set Worksheet.ParentWorkbook.Allow3DRangesInDataValidation = true to enable the use of 3D ranges in data validation within the worksheet.
- Use CellRange.DataValidation.DataRange to specify a cell as a dropdown box and set the data range.
- Set CellRange.DataValidation.AllowType = CellDataType.User to specify the data validation type for cells in the worksheet as user-defined.
- Set CellRange.DataValidation.Formula1 = "=INDIRECT(SUBSTITUTE(A2,\" \",\"_\"))", which means that the data validation for this cell will dynamically select the data range based on the value in the cell.
- Save the document to a specified path using the Workbook.SaveToFile() method.
- C#
using Spire.Xls;
namespace SpireXlsDemo
{
internal class Program
{
static void Main(string[] args)
{
// Create a new workbook object
Workbook workbook = new Workbook();
// Load the workbook from a file
workbook.LoadFromFile("Sample3.xlsx");
// Get the first worksheet
Worksheet worksheet1 = workbook.Worksheets[0];
// Get the second worksheet
Worksheet worksheet2 = workbook.Worksheets[1];
// Add named range objects and set reference ranges
Spire.Xls.Core.INamedRange namedRange = workbook.NameRanges.Add(worksheet2.Range["A1"].Text);
namedRange.RefersToRange = worksheet2.Range["B2:B6"];
namedRange = workbook.NameRanges.Add(worksheet2.Range["A2"].Text.Replace(" ", "_"));
namedRange.RefersToRange = worksheet2.Range["C2:C6"];
namedRange = workbook.NameRanges.Add(worksheet2.Range["A3"].Text);
namedRange.RefersToRange = worksheet2.Range["D2:D6"];
namedRange = workbook.NameRanges.Add(worksheet2.Range["A4"].Text);
namedRange.RefersToRange = worksheet2.Range["E2:E6"];
namedRange = workbook.NameRanges.Add(worksheet2.Range["A5"].Text);
namedRange.RefersToRange = worksheet2.Range["F2:F6"];
// Allow 3D ranges in data validation
worksheet1.ParentWorkbook.Allow3DRangesInDataValidation = true;
// Set the data range for data validation
worksheet1.Range["A2"].DataValidation.DataRange = worksheet2.Range["A1:A5"];
worksheet1.Range["B2"].DataValidation.AllowType = CellDataType.User;
// Set the formula
worksheet1.Range["B2"].DataValidation.Formula1 = "=INDIRECT(SUBSTITUTE(A2,\" \",\"_\"))";
// Save the workbook to a file
workbook.SaveToFile("Linked Dropdowns in the Same Worksheet.xlsx", ExcelVersion.Version2016);
// Dispose of the workbook resources
workbook.Dispose();
}
}
}
Create Linked Dropdown Lists across Different Worksheets in C#
This example demonstrates how to implement linked dropdown lists between different worksheets in an Excel document. By adding an INameRange object and setting the reference range, you can set the data validation range in the first worksheet and set the cell formula in the second worksheet to achieve the linked dropdown effect. Here are the detailed steps:
- Create a Workbook object.
- Load an Excel document using the Workbook.LoadFromFile() method.
- Retrieve a specific worksheet from the workbook using Workbook.Worksheets[].
- Add an INamedRange object and set the reference range.
- Set Worksheet.ParentWorkbook.Allow3DRangesInDataValidation = true to enable data validation using 3D ranges in the worksheet.
- Set the data validation range using CellRange.DataValidation.DataRange.
- Set the formula in a cell in the second worksheet using CellRange.Formula = "=INDIRECT(" + worksheet1.Name + "!B1)" to indirectly reference the value of a cell in the first worksheet.
- Save the document to a specified path using the Workbook.SaveToFile() method.
- C#
using Spire.Xls;
namespace SpireXlsDemo
{
internal class Program
{
static void Main(string[] args)
{
// Create a Workbook object
Workbook workbook = new Workbook();
// Load the workbook from a file
workbook.LoadFromFile("Sample4.xlsx");
// Get the first worksheet
Worksheet worksheet1 = workbook.Worksheets[0];
// Get the second worksheet
Worksheet worksheet2 = workbook.Worksheets[1];
// Get the data worksheet
Worksheet dataSheet = workbook.Worksheets[2];
// Add a named range object and set the reference range
Spire.Xls.Core.INamedRange namedRange = workbook.NameRanges.Add(dataSheet.Range["A2"].Text);
namedRange.RefersToRange = dataSheet.Range["B2"];
namedRange = workbook.NameRanges.Add(dataSheet.Range["A3"].Text);
namedRange.RefersToRange = dataSheet.Range["B3"];
namedRange = workbook.NameRanges.Add(dataSheet.Range["A4"].Text);
namedRange.RefersToRange = dataSheet.Range["B4"];
// Allow 3D ranges in data validation
worksheet1.ParentWorkbook.Allow3DRangesInDataValidation = true;
// Set the data range for data validation
worksheet1.Range["B1"].DataValidation.DataRange = dataSheet.Range["A2:A4"];
// Set the formula for cell B1 in the second worksheet
worksheet2.Range["B1"].Formula = "=INDIRECT(" + worksheet1.Name + "!B1)";
// Save the workbook to a file
workbook.SaveToFile("Creating Linked Dropdowns Across Different Worksheets.xlsx", ExcelVersion.Version2016);
// Release the resources of the workbook
workbook.Dispose();
}
}
}
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.
C#/VB.NET: Freeze Rows and Columns in Excel
Excel Panes can be frozen in order to keep certain rows or columns visible when scrolling through the worksheet. This is particularly helpful when you have a huge amount of data that you need to deal with. In this article, you will learn how to freeze rows or/and columns in C# and VB.NET using Sprie.XLS for .NET.
Spire.XLS provides the Worksheet.FreezePanes(int rowIndex, int columnIndex) method to freeze all rows and columns above and left of the selected cell which is determined by the rowIndex and the columnIndex.
The following sections will demonstrate how to:
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
Freeze the Top Row
To freeze the top row, the selected cell should be the cell (2, 1) – “A2”. The following are the steps to freeze the top row using Spire.XLS for .NET.
- Create a Workbook object.
- Load an Excel file using Workbook.LoadFromFile() method.
- Get a specific worksheet through Workbook.Worksheets[sheetIndex] property.
- Freeze the top row by passing (2, 1) to the Worksheet.FreezePanes(int rowIndex, int columnIndex) method as the parameter.
- Save the workbook to another Excel file using Workbook.SaveToFile() method.
- C#
- VB.NET
using Spire.Xls;
namespace FreezeTopRow
{
class Program
{
static void Main(string[] args)
{
//Create a Workbook instance
Workbook workbook = new Workbook();
//Load an Excel document
workbook.LoadFromFile(@"C:\Users\Administrator\Desktop\sample.xlsx");
//Get the first worksheet
Worksheet sheet = workbook.Worksheets[0];
//Freeze the top row
sheet.FreezePanes(2, 1);
//Save to another file
workbook.SaveToFile("FreezeTopRow.xlsx", ExcelVersion.Version2016);
}
}
}
Freeze the First Column
To freeze the first column, the selected cell should be the cell (1, 2) – “B1”. The following are the steps to freeze the first column using Spire.XLS for .NET.
- Create a Workbook object.
- Load an Excel file using Workbook.LoadFromFile() method.
- Get a specific worksheet through Workbook.Worksheets[sheetIndex] property.
- Freeze the top row by passing (1, 2) to the Worksheet.FreezePanes(int rowIndex, int columnIndex) method as the parameter.
- Save the workbook to another Excel file using Workbook.SaveToFile() method.
- C#
- VB.NET
using Spire.Xls;
namespace FreezeFirstColumn
{
class Program
{
static void Main(string[] args)
{
//Create a Workbook instance
Workbook workbook = new Workbook();
//Load an Excel document
workbook.LoadFromFile(@"C:\Users\Administrator\Desktop\sample.xlsx");
//Get the first worksheet
Worksheet sheet = workbook.Worksheets[0];
//Freeze the first column
sheet.FreezePanes(1, 2);
//Save to another file
workbook.SaveToFile("FreezeFirstColumn.xlsx", ExcelVersion.Version2016);
}
}
}
Freeze the First Row and the First Column
To freeze the first row and the first column, the selected cell should be the cell (2, 2) – “B2”. The following are the detailed steps.
- Create a Workbook object.
- Load an Excel file using Workbook.LoadFromFile() method.
- Get a specific worksheet through Workbook.Worksheets[sheetIndex] property.
- Freeze the first row and the first column by passing (2, 2) to the Worksheet.FreezePanes(int rowIndex, int columnIndex) method as the parameter.
- Save the workbook to another Excel file using Workbook.SaveToFile() method.
- C#
- VB.NET
using Spire.Xls;
namespace FreezeFirstRowAndFirstColumn
{
class Program
{
static void Main(string[] args)
{
//Create a Workbook instance
Workbook workbook = new Workbook();
//Load an Excel document
workbook.LoadFromFile(@"C:\Users\Administrator\Desktop\sample.xlsx");
//Get the first worksheet
Worksheet sheet = workbook.Worksheets[0];
//Freeze the first row and the first column
sheet.FreezePanes(2, 2);
//Save to another file
workbook.SaveToFile("FreezeFirstRowAndFirstColumn.xlsx", ExcelVersion.Version2016);
}
}
}
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.
C#: Insert Rows and Columns in Excel
When working with Excel spreadsheets, it is common to add additional rows or columns as your data set grows or the scope of your project expands. By inserting rows and columns, you can quickly modify the structure of your spreadsheet to accommodate new information. In this article, you will learn how to insert rows and columns in Excel in C# 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
Insert a Row and a Column in Excel in C#
Spire.XLS for .NET provides the Worksheet.InsertRow(int rowIndex) and Worksheet.InsertColumn(int columnIndex) methods for inserting a blank row and a blank column in an Excel worksheet. The following are the detailed steps:
- Create a Workbook instance.
- Load a sample Excel file using Workbook.LoadFromFile() method.
- Get a specified worksheet using Workbook.Worksheets[sheetIndex] property.
- Insert a row into the worksheet using Worksheet.InsertRow(int rowIndex) method.
- Insert a column into the worksheet using Worksheet.InsertColumn(int columnIndex) method.
- Save the result file using Workbook.SaveToFile() method.
- C#
using Spire.Xls;
namespace InsertRowColumn
{
class Program
{
static void Main(string[] args)
{
//Create a Workbook instance
Workbook workbook = new Workbook();
//Load an Excel document
workbook.LoadFromFile("input.xlsx");
//Get a specified worksheet
Worksheet worksheet = workbook.Worksheets[0];
//Insert a blank row as the 5th row in the worksheet
worksheet.InsertRow(5);
//Insert a blank column as the 4th column in the worksheet
worksheet.InsertColumn(4);
//Save the result file
workbook.SaveToFile("InsertRowAndColumn.xlsx", ExcelVersion.Version2016);
}
}
}
Insert Multiple Rows and Columns in Excel in C#
To insert multiple rows and columns into a worksheet, you can use the Worksheet.InsertRow(int rowIndex, int rowCount) and Worksheet.InsertColumn(int columnIndex, int columnCount) methods. The following are detailed steps.
- Create a Workbook instance.
- Load a sample Excel file using Workbook.LoadFromFile() method.
- Get a specified worksheet using Workbook.Worksheets[sheetIndex] property.
- Insert multiple rows into the worksheet using Worksheet.InsertRow(int rowIndex, int rowCount) method.
- Insert multiple columns into the worksheet using Worksheet.InsertColumn(int columnIndex, int columnCount) method.
- Save the result file using Workbook.SaveToFile() method.
- C#
using Spire.Xls;
namespace InsertRowColumn
{
class Program
{
static void Main(string[] args)
{
//Create a Workbook instance
Workbook workbook = new Workbook();
//Load an Excel document
workbook.LoadFromFile(@"E:\PythonExcel\input1.xlsx");
//Get a specified worksheet
Worksheet worksheet = workbook.Worksheets[0];
//Insert three blank rows into the worksheet
worksheet.InsertRow(5, 3);
//Insert two blank columns into the worksheet
worksheet.InsertColumn(4, 2);
//Save the result file
workbook.SaveToFile("InsertRowsAndColumns.xlsx", ExcelVersion.Version2016);
}
}
}
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.
C#/VB.NET: Merge or Unmerge Cells in Excel
Merging cells means joining two or more separate cells into one large cell, which is useful when you need to create a label that spans multiple columns. In this article, we will demonstrate how to merge or unmerge cells in Excel in C# and VB.NET using Spire.XLS for .NET library.
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
Merge Cells in Excel in C# and VB.NET
The following are the steps to merge cells in Excel:
- Create a Workbook instance
- Load the Excel file using Workbook.LoadFromFile() method.
- Get the desired worksheet using Workbook.Worksheets[sheetIndex] property.
- Access the specific range of cells and merge them into one using XlsRange.Merge() method.
- Center the text in the merged cell by setting CellRange.Style.HorizontalAlignment property to HorizontalAlignType.Center.
- Save the result file using Workbook.SaveToFile() method.
- C#
- VB.NET
using Spire.Xls;
namespace MergeCells
{
class Program
{
static void Main(string[] args)
{
//Create a Workbook instance
Workbook workbook = new Workbook();
//Load the Excel file
workbook.LoadFromFile("Sample.xlsx");
//Get the first worksheet
Worksheet sheet = workbook.Worksheets[0];
//Merge cells A1-D1 into one cell
CellRange range = sheet.Range["A1:D1"];
range.Merge();
//Center the text in the merged cell
range.Style.HorizontalAlignment = HorizontalAlignType.Center;
//Save the result file
workbook.SaveToFile("MergeCells.xlsx", ExcelVersion.Version2013);
}
}
}
Unmerge Cells in Excel in C# and VB.NET
The following are the steps to unmerge cells in Excel:
- Create a Workbook instance
- Load the Excel file using Workbook.LoadFromFile() method.
- Get the desired worksheet using Workbook.Worksheets[sheetIndex] property.
- Access the specific range of cells and unmerge them using XlsRange.UnMerge() method.
- Save the result file using Workbook.SaveToFile() method.
- C#
- VB.NET
using Spire.Xls;
namespace UnmergeCells
{
class Program
{
static void Main(string[] args)
{
//Create a Workbook instance
Workbook workbook = new Workbook();
//Load the Excel file
workbook.LoadFromFile("MergeCells.xlsx");
//Get the first worksheet
Worksheet sheet = workbook.Worksheets[0];
//Unmerge cells A1-D1
CellRange range = sheet.Range["A1:D1"];
range.UnMerge();
//Save the result file
workbook.SaveToFile("UnMergeCells.xlsx", ExcelVersion.Version2013);
}
}
}
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.
C#/VB.NET: Add or Remove Cell Borders in Excel
Cell borders refer to lines that can be added around a cell or range of cells. They can be used to serve different purposes, such as to separate sections in a worksheet, draw readers' attention to important cells, or make the worksheet look more presentable. This article will introduce how to add or remove cell borders in Excel 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
Add Cell Borders in Excel in C# and VB.NET
Spire.XLS for .NET allows adding various kinds of borders to cells in Excel, such as left border, right border, top border, bottom border, diagonal borders, inside borders and outside borders.
You can add a specific border or multiple borders to individual cells or ranges of cells. In addition, you can also set different line styles and line colors for the borders. The following are the main steps to apply different kinds of cell borders with different line styles and line colors:
- Initialize an instance of the Workbook class.
- Get a specific worksheet by its index through Workbook.Worksheets[int] property.
- Get a specific cell range by its name through Worksheet.Range[string] property.
- Get specific borders (such as left, right, top, bottom and diagonal) from the Borders collection of the cell range through CellRange.Borders[BordersLineType] property.
- Set the line styles of the specific borders through IBorder.LineStyle property.
- Set the line colors of the specific borders through IBorder.Color property.
- Get a specific cell range by its name through Worksheet.Range[string] property.
- Add outside borders and/or inside borders to the cell range using CellRange.BorderAround(LineStyleType, Color) method and/or CellRange.BorderInside(LineStyleType, Color) method. Note that inside borders cannot be applied to a single cell.
- Get a specific cell range by its name through Worksheet.Range[string] property.
- Set the line styles and line colors for borders of the cell range through BordersCollection.LineStyle and BordersCollection.Color properties, then set the line style and color for diagonal borders of the cell range.
- Save the result file using Workbook.SaveToFile() method.
- C#
- VB.NET
using Spire.Xls;
using Spire.Xls.Core;
using System.Drawing;
namespace AddCellBorders
{
class Program
{
static void Main(string[] args)
{
//Create a Workbook instance
Workbook workbook = new Workbook();
//Get the first worksheet
Worksheet sheet = workbook.Worksheets[0];
//Set left, right, top, bottom and diagonal up borders for cell B2
CellRange range = sheet.Range["B2"];
IBorder leftBorder = range.Borders[BordersLineType.EdgeLeft];
leftBorder.LineStyle = LineStyleType.MediumDashDotDot;
leftBorder.Color = Color.Red;
IBorder rightBorder = range.Borders[BordersLineType.EdgeRight];
rightBorder.LineStyle = LineStyleType.MediumDashed;
rightBorder.Color = Color.Red;
IBorder topBorder = range.Borders[BordersLineType.EdgeTop];
topBorder.LineStyle = LineStyleType.Medium;
topBorder.Color = Color.Red;
IBorder bottomBorder = range.Borders[BordersLineType.EdgeBottom];
bottomBorder.LineStyle = LineStyleType.Medium;
bottomBorder.Color = Color.Red;
IBorder diagonalUpBorder = range.Borders[BordersLineType.DiagonalUp];
diagonalUpBorder.LineStyle = LineStyleType.Thin;
diagonalUpBorder.Color = Color.Red;
//Set diagonal borders for cell C4
range = sheet.Range["C4"];
diagonalUpBorder = range.Borders[BordersLineType.DiagonalUp];
diagonalUpBorder.LineStyle = LineStyleType.Double;
diagonalUpBorder.Color = Color.Blue;
IBorder diagonalDownBorder = range.Borders[BordersLineType.DiagonalDown];
diagonalDownBorder.LineStyle = LineStyleType.Double;
diagonalDownBorder.Color = Color.Blue;
//Set outside borders for cell D6
range = sheet.Range["D6"];
range.BorderAround(LineStyleType.Double, Color.Green);
//Set inside borders for cell range E8:F10
range = sheet.Range["E8:F10"];
range.BorderInside(LineStyleType.MediumDashed, Color.DarkGray);
//Set inside and outside borders for cell range F12:G14
range = sheet.Range["F12:G14"];
range.BorderInside(LineStyleType.MediumDashed, Color.Pink);
range.BorderAround(LineStyleType.Medium, Color.Magenta);
//Set borders for cell range G16:H18
range = sheet.Range["G16:H18"];
range.Borders.LineStyle = LineStyleType.Thick;
range.Borders.Color = Color.Cyan;
//Set line style and line color of diagonal borders for cell range G16:H18
diagonalUpBorder = range.Borders[BordersLineType.DiagonalUp];
diagonalUpBorder.LineStyle = LineStyleType.Dotted;
diagonalUpBorder.Color = Color.DarkGray;
diagonalDownBorder = range.Borders[BordersLineType.DiagonalDown];
diagonalDownBorder.LineStyle = LineStyleType.Dotted;
diagonalDownBorder.Color = Color.DarkGray;
//Save the result file
workbook.SaveToFile("AddBorders.xlsx", ExcelVersion.Version2013);
}
}
}
Remove Cell Borders in Excel in C# and VB.NET
You can remove all borders of a cell or range of cells by setting the CellRange.Borders.LineStyle property as LineStyleType.None. The following are the details steps:
- Initialize an instance of the Workbook class.
- Load an Excel file using Workbook.LoadFromFile() method.
- Get a specific worksheet by its index through Workbook.Worksheets[int] property.
- Get a specific cell range by its name through Worksheet.Range[string] property.
- Remove the borders of the cell range by setting CellRange.Borders.LineStyle property as LineStyleType.None.
- Save the result file using Workbook.SaveToFile() method.
- C#
- VB.NET
using Spire.Xls;
namespace RemoveCellBorders
{
class Program
{
static void Main(string[] args)
{
//Create a Workbook instance
Workbook workbook = new Workbook();
//Load an Excel file
workbook.LoadFromFile("AddBorders.xlsx");
//Get the first worksheet
Worksheet sheet = workbook.Worksheets[0];
//Remove borders of cell range G16:H18
CellRange range = sheet.Range["G16:H18"];
range.Borders.LineStyle = LineStyleType.None;
workbook.SaveToFile("RemoveBorders.xlsx", ExcelVersion.Version2013);
}
}
}
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.