C++: Apply or Remove Data Validation in Excel
Data validation in Excel controls what types of information can be entered into a cell. Using it, you can restrict only specific data types such as numbers or dates to be in a cell, or limit numbers to a certain range and text to a certain length. In addition, it also allows you to present a list of predefined values in a drop-down menu for users to choose from. In this article, you will learn how to apply or remove data validation in Excel in C++ using Spire.XLS for C++.
Install Spire.XLS for C++
There are two ways to integrate Spire.XLS for C++ into your application. One way is to install it through NuGet, and the other way is to download the package from our website and copy the libraries into your program. Installation via NuGet is simpler and more recommended. You can find more details by visiting the following link.
Integrate Spire.XLS for C++ in a C++ Application
Apply Data Validation to Excel Cells in C++
Spire.XLS for C++ allows you to create validation rules for numbers, dates, text values, lists, etc. The following are the steps to apply different data validation types to specified cells in Excel.
- Create a Workbook object.
- Get a specified worksheet using Workbook->GetWorksheets()->Get() method.
- Get a specific cell using Worksheet->GetRange() method.
- Set the data type allowed in the cell using CellRange->GetDataValidation()->SetAllowType() method. You can select different data type such as Decimal, Time, Date, TextLength and Integer.
- Set the comparison operator using CellRange->GetDataValidation()->SetCompareOperator() method. The comparison operators include Between, NotBetween, Less, Greater, and Equal.
- Set one or two formulas for the data validation using CellRange->GetDataValidation()->SetFormula1() and CellRange->GetDataValidation()->SetFormula2() methods.
- Set the input prompt using CellRange->GetDataValidation()->SetInputMessage() method.
- Set the error message using CellRange->GetDataValidation()->SetErrorMessage() method.
- Set to show the error alert and set its alert style when invalid data is entered.
- Save the result document using Workbook->SaveToFile() method.
- C++
#include "Spire.Xls.o.h";
using namespace Spire::Xls;
int main() {
//Specify the output file
std::wstring outputFile = L"DataValidation.xlsx";
//Create a Workbook object
intrusive_ptr<Workbook> workbook = new Workbook();
//Get the first worksheet
intrusive_ptr<Worksheet> sheet = dynamic_pointer_cast<Worksheet>(workbook->GetWorksheets()->Get(0));
//Insert text in specified cells
dynamic_pointer_cast<CellRange>(sheet->GetRange(L"B2"))->SetText(L"Number Validation: ");
dynamic_pointer_cast<CellRange>(sheet->GetRange(L"B4"))->SetText(L"Date Validation: ");
dynamic_pointer_cast<CellRange>(sheet->GetRange(L"B6"))->SetText(L"Text Length Validation: ");
dynamic_pointer_cast<CellRange>(sheet->GetRange(L"B8"))->SetText(L"List Validation: ");
dynamic_pointer_cast<CellRange>(sheet->GetRange(L"B10"))->SetText(L"Time Validation: ");
//Add a number validation to C2
intrusive_ptr<CellRange> rangeNumber = dynamic_pointer_cast<CellRange>(sheet->GetRange(L"C2"));
rangeNumber->GetDataValidation()->SetAllowType(CellDataType::Decimal);
rangeNumber->GetDataValidation()->SetCompareOperator(ValidationComparisonOperator::Between);
rangeNumber->GetDataValidation()->SetFormula1(L"3");
rangeNumber->GetDataValidation()->SetFormula2(L"6");
rangeNumber->GetDataValidation()->SetInputMessage(L"Enter a number between 1 and 10");
rangeNumber->GetDataValidation()->SetErrorMessage(L"Please input correct number!");
rangeNumber->GetDataValidation()->SetShowError(true);
rangeNumber->GetDataValidation()->SetAlertStyle(AlertStyleType::Warning);
rangeNumber->GetStyle()->SetKnownColor(ExcelColors::Gray25Percent);
//Add a date validation to C4
intrusive_ptr<CellRange> rangeDate = dynamic_pointer_cast<CellRange>(sheet->GetRange(L"C4"));
rangeDate->GetDataValidation()->SetAllowType(CellDataType::Date);
rangeDate->GetDataValidation()->SetCompareOperator(ValidationComparisonOperator::Between);
rangeDate->GetDataValidation()->SetFormula1(L"1/1/2021");
rangeDate->GetDataValidation()->SetFormula2(L"12/31/2021");
rangeDate->GetDataValidation()->SetInputMessage(L"Enter a date between 1/1/2021 and 12/31/2021");
rangeDate->GetStyle()->SetKnownColor(ExcelColors::Gray25Percent);
//Add a text length validation to C6
intrusive_ptr<CellRange> rangeTextLength = dynamic_pointer_cast<CellRange>(sheet->GetRange(L"C6"));
rangeTextLength->GetDataValidation()->SetAllowType(CellDataType::TextLength);
rangeTextLength->GetDataValidation()->SetCompareOperator(ValidationComparisonOperator::LessOrEqual);
rangeTextLength->GetDataValidation()->SetFormula1(L"5");
rangeTextLength->GetDataValidation()->SetErrorMessage(L"Enter a Valid String!");
rangeTextLength->GetDataValidation()->SetShowError(true);
rangeTextLength->GetDataValidation()->SetAlertStyle(AlertStyleType::Stop);
rangeTextLength->GetStyle()->SetKnownColor(ExcelColors::Gray25Percent);
//Apply a list validation to C8
intrusive_ptr<CellRange> rangeList = dynamic_pointer_cast<CellRange>(sheet->GetRange(L"C8"));
std::vector<LPCWSTR_S> files = { L"United States", L"Canada", L"United Kingdom" };
rangeList->GetDataValidation()->SetValues(files);
rangeList->GetDataValidation()->SetIsSuppressDropDownArrow(false);
rangeList->GetDataValidation()->SetInputMessage(L"Choose an item from the list");
rangeList->GetStyle()->SetKnownColor(ExcelColors::Gray25Percent);
//Apply a time validation to C10
intrusive_ptr<CellRange> rangeTime = dynamic_pointer_cast<CellRange>(sheet->GetRange(L"C10"));
rangeTime->GetDataValidation()->SetAllowType(CellDataType::Time);
rangeTime->GetDataValidation()->SetCompareOperator(ValidationComparisonOperator::Between);
rangeTime->GetDataValidation()->SetFormula1(L"9:00");
rangeTime->GetDataValidation()->SetFormula2(L"12:00");
rangeTime->GetDataValidation()->SetInputMessage(L"Enter a time between 9:00 and 12:00");
rangeTime->GetStyle()->SetKnownColor(ExcelColors::Gray25Percent);
//Auto fit width of column 2
sheet->AutoFitColumn(2);
//Set the width of column 3
sheet->GetColumns()->GetItem(2)->SetColumnWidth(20);
//Save the result document
workbook->SaveToFile(outputFile.c_str(), ExcelVersion::Version2016);
workbook->Dispose();
}
Remove Data Validation from Excel Cells in C++
To remove data validation applied to the cells, Spire.XLS for C++ provides the Worksheet->GetDVTable()->Remove() method. The following are the detailed steps.
- Create a Workbook object.
- Load a sample Excel document containing data validation using Workbook->LoadFromFile() method.
- Get a specified worksheet using Workbook->GetWorksheets()->Get() method.
- Create an array of rectangles, which is used to locate the cells where the validation will be removed.
- Remove the data validation from the selected cells using Worksheet->GetDVTable()->Remove() method.
- Save the result document using Workbook->SaveToFile() method.
- C++
#include "Spire.Xls.o.h";
using namespace Spire::Xls;
int main() {
//Specify the input and output files
std::wstring inputFile = L"DataValidation.xlsx";
std::wstring outputFile = L"RemoveDataValidation.xlsx";
//Create a Workbook object
intrusive_ptr<Workbook> workbook = new Workbook();
//Load a sample Excel document from disk
workbook->LoadFromFile(inputFile.c_str());
//Get the first worksheet
intrusive_ptr<Worksheet> sheet = dynamic_pointer_cast<Worksheet>(workbook->GetWorksheets()->Get(0));
//Create an array of rectangles, which is used to locate the ranges in worksheet
std::vector<intrusive_ptr<Spire::Common::Rectangle>> rectangles(1);
//Assign value to the first element of the array. A rectangle specifies a cell range
rectangles[0] = Spire::Common::Rectangle::FromLTRB(0, 0, 2, 9);
//Remove validations in the ranges represented by rectangles
sheet->GetDVTable()->Remove(rectangles);
//Save the result document
workbook->SaveToFile(outputFile.c_str(), ExcelVersion::Version2016);
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++: Write Data to Excel Worksheets
Excel spreadsheets are grid-based documents used to organize information in an organized manner. People all around the world use spreadsheets to create tables for business or personal needs. Over the years, Excel spreadsheets have played an important role in easing the tedious process of managing data with a variety of functionalities. In this article, you will learn how to create an Excel document in C++ and how to write data into Excel sheets in C++ by using Spire.XLS for C++.
Install Spire.XLS for C++
There are two ways to integrate Spire.XLS for C++ into your application. One way is to install it through NuGet, and the other way is to download the package from our website and copy the libraries into your program. Installation via NuGet is simpler and more recommended. You can find more details by visiting the following link.
Integrate Spire.XLS for C++ in a C++ Application
Write Text or Number Values to Cells in C++
Spire.XLS for C++ provides the Workbook class and the Worksheet class to represent an Excel document and a worksheet, respectively. Users can have access to a specific cell using Worksheet->GetRange(int row, int column) method. Then, assign a text value or a number value to the cell using CellRange->SetText() or CellRange->SetNumberValue() method. The following are the detailed steps:
- Create a Workbook object.
- Get the first worksheet using Workbook->GetWorksheets()->Get() method.
- Get a specific cell using Worksheet->GetRange(int row, int column) method.
- Add a text value or a number values to a specified cell using CellRange->SetText() or CellRange->SetNumberValue() method.
- Save the workbook to an Excel file using Workbook->SaveToFile() method.
- C++
#include "Spire.Xls.o.h";
using namespace Spire::Xls;
int main() {
//Specify output file path and name
std::wstring outputPath = L"Output\\";
std::wstring outputFile = outputPath + L"WriteIndividualValuesToCells.xlsx";
//Create a Workbook object
intrusive_ptr<Workbook> workbook = new Workbook();
//Get the first sheet
intrusive_ptr<Worksheet> sheet = dynamic_pointer_cast<Worksheet>(workbook->GetWorksheets()->Get(0));
//Write text and numbers to the specified cells
sheet->GetRange(1, 1)->SetText(L"Name");
sheet->GetRange(1, 2)->SetText(L"Age");
sheet->GetRange(1, 3)->SetText(L"Department");
sheet->GetRange(1, 4)->SetText(L"Hire Date");
sheet->GetRange(1, 1)->SetText(L"Name");
sheet->GetRange(2, 1)->SetText(L"Hazel");
sheet->GetRange(2, 2)->SetNumberValue(29);
sheet->GetRange(2, 3)->SetText(L"Marketing");
sheet->GetRange(2, 4)->SetText(L"2021-02-26");
sheet->GetRange(3, 1)->SetText(L"Tina");
sheet->GetRange(3, 2)->SetNumberValue(27);
sheet->GetRange(3, 3)->SetText(L"Human Resource");
sheet->GetRange(3, 4)->SetText(L"2020-07-13");
sheet->GetRange(4, 1)->SetText(L"Amy");
sheet->GetRange(4, 2)->SetNumberValue(35);
sheet->GetRange(4, 3)->SetText(L"Development");
sheet->GetRange(4, 4)->SetText(L"2019-04-01");
//Autofit column width
sheet->GetAllocatedRange()->AutoFitColumns();
//Apply style to the first row
intrusive_ptr <CellStyle> style = dynamic_pointer_cast<CellStyle>(workbook->GetStyles()->Add(L"newStyle"));
style->GetFont()->SetIsBold(true);
sheet->GetRange(1, 1, 1, 4)->SetStyle(style);
//Save to file
workbook->SaveToFile(outputFile.c_str(), ExcelVersion::Version2016);
workbook->Dispose();
}
Write Arrays to Specified Cell Ranges in C++
Spire.XLS for C++ provides the Worksheet->InsertArray() method, allowing programmers to write vectors into the specified cell range of a worksheet. Before writing arrays to worksheets, you need to convert them to vectors. The steps to write arrays to a worksheet are as follows:
- Create a Workbook object.
- Get the first worksheet using Workbook->GetWorksheets()->Get() method.
- Create an array and convert it to one vector or multiple vectors.
- Insert the vector(s) to worksheet using Worksheet->InsertArray() method.
- Save the workbook to an Excel file using Workbook->SaveToFile() method.
- C++
#include "Spire.Xls.o.h";
using namespace Spire::Xls;
using namespace std;
int main() {
//Specify output file path and name
wstring outputPath = L"Output\\";
wstring outputFile = outputPath + L"WriteArraysToCellRanges.xlsx";
//Create a Workbook object
intrusive_ptr<Workbook> workbook = new Workbook();
//Get the first sheet
intrusive_ptr<Worksheet> sheet = dynamic_pointer_cast<Worksheet>(workbook->GetWorksheets()->Get(0));
//Create a one-dimensional array
wstring oneDimensionalArray[6] = { L"January", L"February", L"March", L"April", L"May", L"June" };
//Convert array to vector
vector<LPCWSTR> vec;
for (size_t i = 0; i < sizeof(oneDimensionalArray) / sizeof(oneDimensionalArray[0]); i++)
{
vec.push_back(oneDimensionalArray[i].c_str());
}
//Insert vector to worksheet
sheet->InsertArray(vec, 1, 1, false);
//Create a two-dimensional array
wstring twoDimensionalArray[4][5] = {
{L"Name", L"Age", L"Sex", L"Dept.", L"Tel."},
{L"John", L"25", L"Male", L"Development", L"654214"},
{L"Albert", L"24", L"Male", L"Support", L"624847"},
{L"Amy", L"26", L"Female", L"Sales", L"624758"}
};
//Get row number and column number
int rowNum = sizeof(twoDimensionalArray) / sizeof(twoDimensionalArray[0]);
int columnNum = sizeof(twoDimensionalArray[0]) / sizeof(twoDimensionalArray[0][0]);
//Split 2D array into multiple 1D vectors
for (size_t i = 0; i < rowNum; i++)
{
vector<LPCWSTR> vec_temp;
for (size_t j = 0; j < columnNum; j++)
{
vec_temp.push_back(twoDimensionalArray[i][j].c_str());
}
//Insert vector into worksheet
sheet->InsertArray(vec_temp, 4 + i, 1, false);
}
//Autofit column width
sheet->GetAllocatedRange()->AutoFitColumns();
//Apply style to the first row
intrusive_ptr <CellStyle> style = dynamic_pointer_cast<CellStyle>(workbook->GetStyles()->Add(L"newStyle"));
style->GetFont()->SetIsBold(true);
sheet->GetRange(1, 1, 1, 6)->SetStyle(style);
sheet->GetRange(4, 1, 4, 5)->SetStyle(style);
//Save to file
workbook->SaveToFile(outputFile.c_str(), ExcelVersion::Version2016);
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.