Knowledgebase (2417)
Children categories
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.
This section is designed to provide a solution for developers on how to delete PDF page for WPF via this PDF library Spire.PDF for WPF.
Spire.PDF for WPF enables users to delete any PDF page only by one line of code: RemoveAt(int index). As you see, there is only one parameter in this method. This parameter determines which page will be deleted in PDF file. Now let us see the whole task in detail.
Delete PDF Page for WPF
Step 1: Prepare a template PDF File
I pick a PDF file from my system and it has 73 pages, we can see it below:
Step 2: Download and Install Spire.PDF for WPF
Spire.PDF for WPF is a PDF component applied in WPF applications. It enables developers to create, read, edit and handle PDF files with fast speed. Here you can download Spire.PDF for WPF. After installing it on system, Spire.PDF for WPF will run in evaluation mode. This evaluation mode has no time limit and has no bad influence on performing the task.
Step 3: Start a new Project and Add References
Start your Visual Studio and create a new project in Visual C# WPF Application or VB。NET WPF Application. Here is an example of Visual C# in WPF Application. Please do not forget to add a button in the MainWindow.
We will use Spire.PDF for WPF, so we have to add Spire.Pdf for Wdf.dll and Spire.License.dll in our project in Bin folder. The detail Path is "..\Spire.Pdf\Bin\WPF4.0\ Spire.Pdf.Wpf.dll".
Step 4: Delete PDF Page
Below is the complete code in my project:
//Initialize a new instance of PdfDocument
PdfDocument document = new PdfDocument();
//Open a template PDF file
document.LoadFromFile(@"..\Report.pdf");
//Remove the second Page
document.Pages.RemoveAt(1);
//Save the PDF file to disk
document.SaveToFile("DeletePDFPage.pdf");
//Launch the PDF file
System.Diagnostics.Process.Start("DeletePDFPage.pdf ");
'Initialize a new instance of PdfDocument
Dim document As New PdfDocument()
'Open a template PDF file
document.LoadFromFile("..\Report.pdf")
'Remove the second Page
document.Pages.RemoveAt(1)
'Save the PDF file to disk
document.SaveToFile("DeletePDFPage.pdf")
'Launch the PDF file
System.Diagnostics.Process.Start("DeletePDFPage.pdf ")
Output PDF File
After executing above code, the second page of the template PDF file has been deleted, and there are only 72 pages in the target PDF file, we can see here:
In this section, I have expounded the solution on how to delete PDF page in WPF application. I wish it can help you and give you some insight on your development.
Spire.PDF for WPF is a PDF component which is designed to meet customers’ requirements on programming with high efficiency and reliability.
We welcome any kind of queries, comments and advices at E-iceblue Forum. Our professionals will be there to give prompt reply.
Watch this quick demo video to see how multiple Word documents can be merged into one file using C# and VB.NET.
Long papers or research reports are often completed collaboratively by multiple people. To save time, each person can work on their assigned parts in separate documents and then merge these documents into one after finish editing. Apart from manually copying and pasting content from one Word document to another, this article will demonstrate the following two ways to merge Word documents programmatically using Spire.Doc for .NET .
Install Spire.Doc for .NET
To begin with, you need to add the DLL files included in the Spire.Doc 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.Doc
Merge Documents by Inserting the Entire File
The Document.InsertTextFromFile() method provided by Spire.Doc for .NET allows merging Word documents by inserting other documents entirely into a document. Using this method, the contents of the inserted document will start from a new page. The detailed steps are as follows:
- Create a Document instance.
- Load the original Word document using Document.LoadFromFile() method.
- Insert another Word document entirely to the original document using Document.InsertTextFromFile() method.
- Save the result document using Document.SaveToFile() method.
- C#
- VB.NET
using Spire.Doc;
namespace MergeWord
{
class Program
{
static void Main(string[] args)
{
//Create a Document instance
Document document = new Document();
//Load the original Word document
document.LoadFromFile("Doc1.docx", FileFormat.Docx);
//Insert another Word document entirely to the original document
document.InsertTextFromFile("Doc2.docx", FileFormat.Docx);
//Save the result document
document.SaveToFile("MergedWord.docx", FileFormat.Docx);
}
}
}
Merge Documents by Cloning Contents
If you want to merge documents without starting a new page, you can clone the contents of other documents to add to the end of the original document. The detailed steps are as follows:
- Load two Word documents.
- Loop through the second document to get all the sections using Document.Sections property, and then loop through all the sections to get their child objects using Section.Body.ChildObjects property.
- Get the last section of the first document using Document.LastSection property, and then add the child objects to the last section of the first document using LastSection.Body.ChildObjects.Add() method.
- Save the result document using Document.SaveToFile() method.
- C#
- VB.NET
using Spire.Doc;
namespace MergeWord
{
class Program
{
static void Main(string[] args)
{
//Load two Word documents
Document doc1 = new Document("Doc1.docx");
Document doc2 = new Document("Doc2.docx");
//Loop through the second document to get all the sections
foreach (Section section in doc2.Sections)
{
//Loop through the sections of the second document to get their child objects
foreach (DocumentObject obj in section.Body.ChildObjects)
{
// Get the last section of the first document
Section lastSection = doc1.LastSection;
//Add all child objects to the last section of the first document
lastSection.Body.ChildObjects.Add(obj.Clone());
}
}
// Save the result document
doc1.SaveToFile("MergeDocuments.docx", FileFormat.Docx);
}
}
}
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.
