Knowledgebase (2330)
Children categories
Excel is a popular spreadsheet software widely used for data analysis, financial management, budgeting, etc. However, when you need to embed Excel files into other files or share them with others, Excel format may have compatibility issues, and in such a case converting Excel to image is an alternative option. In this article, you will learn how to convert an Excel worksheet or a specific cell range to an image in C++ using Spire.XLS for C++.
- Convert an Entire Excel Worksheet to an Image in C++
- Convert a Specific Cell Range to an Image in C++
- Convert a Worksheet to an Image Without White Spaces in 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
Convert an Entire Excel Worksheet to an Image in C++
Spire.XLS for C++ offers the Worksheet->ToImage() method to convert a specific worksheet to an image. The following are the detailed steps.
- Create a Workbook object.
- Load a sample Excel document using Workbook->LoadFromFile() method.
- Get a specified worksheet using Workbook->GetWorksheets()->Get() method.
- Convert the worksheet to an image using Worksheet->ToImage() method.
- C++
#include "Spire.Xls.o.h"
using namespace Spire::Xls;
using namespace std;
int main()
{
//Specify the input and output file paths
wstring inputFile = L"Data\\Planner.xlsx";
wstring outputFile = L"Output\\SheetToImage";
//Create a Workbook object
intrusive_ptr<Workbook> workbook = new Workbook();
//Load an 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));
//Save the image as a PNG file
wstring fileName = outputFile + L".png";
//Convert the worksheet to an image
sheet->ToImage(sheet->GetFirstRow(), sheet->GetFirstColumn(), sheet->GetLastRow(), sheet->GetLastColumn())->Save(fileName.c_str());
workbook->Dispose();
}
Convert a Specific Cell Range to an Image in C++
Spire.XLS for C++ also allows you to use the Worksheet->ToImage(int firstRow, int firstColumn, int lastRow, int lastColumn) method to convert a specified cell range to an image. The following are the steps convert several cell ranges to different image formats.
- Create a Workbook object.
- Load a sample Excel document using Workbook->LoadFromFile() method.
- Get a specified worksheet using Workbook->GetWorksheets()->Get() method.
- Specify a cell range and save it to a specified image format using Worksheet->ToImage()->Save(LPCWSTR_S filename) method.
- C++
#include "Spire.Xls.o.h"
using namespace Spire::Xls;
using namespace std;
int main()
{
//Specify input file path and name
wstring inputFile = L"Data\\Planner.xlsx";
//Create a Workbook object
intrusive_ptr<Workbook> workbook = new Workbook();
//Load an 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));
//Specify cell ranges and save them to certain image formats
sheet->ToImage(3, 1, 11, 4)->Save(L"ToImage\\SpecificCellsToImage.png");
sheet->ToImage(3, 6, 11, 9)->Save(L"ToImage\\SpecificCellsToImage.jpg");
sheet->ToImage(13, 6, 22, 9)->Save(L"ToImage\\SpecificCellsToImage.bmp");
workbook->Dispose();
}
Convert a Worksheet to an Image Without White Spaces in C++
When converting a worksheet directly to an image, there are white spaces around the converted image. If you want to remove these white spaces, you can set the left, right, top and bottom margins of the worksheet to zero while conversion. The following are the detailed steps.
- Create a Workbook object.
- Load a sample Excel document using Workbook->LoadFromFile() method.
- Get a specified worksheet using Workbook->GetWorksheets()->Get() method.
- Return a page setup object using Worksheet->GetPageSetup() method, and then set the left, right, top and bottom margins of the worksheet using the methods of PageSetup class.
- Save the worksheet as an image using Worksheet->ToImage()->Save() method.
- C++
#include "Spire.Xls.o.h"
using namespace Spire::Xls;
using namespace std;
int main()
{
//Specify output file path and name
wstring inputFile = L"Data\\Planner.xlsx";
wstring outputFile = L"Output\\ToImageWithoutWhiteSpace.png";
//Create a Workbook object
intrusive_ptr<Workbook> workbook = new Workbook();
//Load an 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));
//Set the margin as 0 to remove the white space around the image
sheet->GetPageSetup()->SetLeftMargin(0);
sheet->GetPageSetup()->SetBottomMargin(0);
sheet->GetPageSetup()->SetTopMargin(0);
sheet->GetPageSetup()->SetRightMargin(0);
//Save the worksheet as an image
intrusive_ptr<Stream> image = sheet->ToImage(sheet->GetFirstRow(), sheet->GetFirstColumn(), sheet->GetLastRow(), sheet->GetLastColumn());
image->Save(outputFile.c_str());
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.
In a large document, it is inevitable to use the find function when you want to quickly locate specific key information. At the same time, highlighting them with a bright color is also an effective way to make them stand out to grab the reader's attention. In this article, you will learn how to programmatically find and highlight text in a Word document using Spire.Doc for C++.
- Find and Highlight All Instances of a Specified Text in Word
- Find and Highlight the First Instance of a Specified Text in Word
Install Spire.Doc for C++
There are two ways to integrate Spire.Doc 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.Doc for C++ in a C++ Application
Find and Highlight All Instances of a Specified Text in Word in C++
Spire.Doc for C++ offers the Document->FindAllString(LPCWSTR_S matchString, bool caseSensitive, bool wholeWord) method to find all instances of a specified text string, and then you can iterate through these instances to highlight them with a bright color. The following are the detailed steps.
- Create a Document instance.
- Load a Word document using Document->LoadFromFile() method.
- Find all matching text in the document using Document->FindAllString() method.
- Loop through all matching text in the document.
- Get the text range of a specific matching text using TextSelection->GetAsOneRange() method, and then set its highlight color using TextRange->GetCharacterFormat()->SetHighlightColor() method.
- Save the result document using Document->SaveToFile() method.
- C++
#include "Spire.Doc.o.h"
using namespace Spire::Doc;
int main() {
//Specify the input and output file paths
std::wstring inputFile = L"Data\\input1.docx";
std::wstring outputFile = L"Output\\FindAndHighlightAll.docx";
//Create a Document instance
intrusive_ptr<Document> document = new Document();
//Load a Word document from disk
document->LoadFromFile(inputFile.c_str());
//Find all matching text in the document
std::vector<intrusive_ptr<TextSelection>> textSelections = document->FindAllString(L"Transcendentalism", false, true);
//Loop through all matching text and set highlight color for them
for (auto selection : textSelections)
{
selection->GetAsOneRange()->GetCharacterFormat()->SetHighlightColor(Color::GetYellow());
}
//Save the result document
document->SaveToFile(outputFile.c_str(), FileFormat::Docx);
document->Close();
}
Find and Highlight the First Instance of a Specified Text in Word in C++
You can also use the Document->FindString(LPCWSTR_S matchString, bool caseSensitive, bool wholeWord) method to find only the first instance of a specified text string and then set highlight color for it. The following are the detailed steps.
- Create a Document instance.
- Load a Word document using Document->LoadFromFile() method.
- Find the first matching text using Document->FindString() method.
- Get the text range of the first matching text using TextSelection->GetAsOneRange() method, and then set its highlight color using TextRange->GetCharacterFormat()->SetHighlightColor() method.
- Save the result document using Document->SaveToFile() method.
- C++
#include "Spire.Doc.o.h"
using namespace Spire::Doc;
int main() {
//Specify the input and output file paths
std::wstring inputFile = L"Data\\input1.docx";
std::wstring outputFile = L"Output\\FindAndHighlight.docx";
//Create a Document instance
intrusive_ptr<Document> document = new Document();
//Load a Word document from disk
document->LoadFromFile(inputFile.c_str());
//Find the first matching text
intrusive_ptr<TextSelection> textSelection = document->FindString(L"Transcendentalism", false, true);
//Set highlight color for the text
textSelection->GetAsOneRange()->GetCharacterFormat()->SetHighlightColor(Color::GetYellow());
//Save the result document
document->SaveToFile(outputFile.c_str(), FileFormat::Docx);
document->Close();
}
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.
Formulas and functions are fundamental features of Microsoft Excel that allow users to perform a variety of mathematical, statistical, and logical operations on data. Formulas are expressions that can be entered into cells to automate calculations, usually consisting of cell references, constants, and operators that specify the calculation to be performed. Functions, on the other hand, are pre-built formulas that perform specific tasks, such as calculating the sum, average, maximum, or minimum value of a range of cells. Both formulas and functions are essential tools for anyone working with data in Excel. Whether you are analyzing financial data, experimental data, or any other dataset, using formulas and functions can help you quickly and accurately perform calculations on your data and gain insights from it. In this article, you will learn how to insert or Read formulas and functions in an Excel file 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
Insert Formulas and Functions into Excel in C++
The Worksheet->GetRange(int row, int column)->SetFormula(LPCWSTR_S value) method in Spire.XLS for C++ is used to add formulas or functions to specific cells in an Excel worksheet. The main steps are as follows:
- Initialize an instance of the Workbook class.
- Get a specific worksheet by its index using the Workbook->GetWorksheets()->Get(int index) method.
- Add some text and numeric data to specific cells of the worksheet using the Worksheet->GetRange(int row, int column)->SetText(LPCWSTR_S value) and Worksheet->GetRange(int row, int column)->SetNumberValue(double value) methods.
- Add text and formulas to specific cells of the worksheet using the Worksheet->GetRange(int row, int column)->SetText(LPCWSTR_S value) and the Worksheet->GetRange(int row, int column)->SetFormula(LPCWSTR_S value) methods.
- Add text and functions to specific cells of the worksheet using the Worksheet->GetRange(int row, int column)->SetText(LPCWSTR_S value) and the Worksheet->GetRange(int row, int column)->SetFormula(LPCWSTR_S value) methods.
- Save the result file using Workbook->SaveToFile(LPCWSTR_S fileName, ExcelVersion version) method.
- C++
#include "Spire.Xls.o.h";
using namespace Spire::Xls;
using namespace std;
int main()
{
//Initialize an instance of the Workbook class
intrusive_ptr<Worksheet> workbook = new Workbook();
//Get the first worksheet
intrusive_ptr<Worksheet> sheet = dynamic_pointer_cast<Worksheet>(workbook->GetWorksheets()->Get(0));
//Declare two variables: currentRow, currentFormula
int currentRow = 1;
wstring currentFormula = L"";
//Add text to the worksheet and set cell style
sheet->GetRange(currentRow, 1)->SetText(L"Test Data:");
sheet->GetRange(currentRow, 1)->GetStyle()->GetFont()->SetIsBold(true);
sheet->GetRange(currentRow, 1)->GetStyle()->SetFillPattern(ExcelPatternType::Solid);
sheet->GetRange(currentRow, 1)->GetStyle()->SetKnownColor(ExcelColors::LightGreen1);
sheet->GetRange(currentRow, 1)->GetStyle()->GetBorders()->Get(BordersLineType::EdgeBottom)->SetLineStyle(LineStyleType::Medium);
//Add some numeric data to the worksheet
sheet->GetRange(++currentRow, 1)->SetNumberValue(7.3);
sheet->GetRange(currentRow, 2)->SetNumberValue(5);
sheet->GetRange(currentRow, 3)->SetNumberValue(8.2);
sheet->GetRange(currentRow, 4)->SetNumberValue(4);
sheet->GetRange(currentRow, 5)->SetNumberValue(3);
sheet->GetRange(currentRow, 6)->SetNumberValue(11.3);
currentRow++;
//Add text to the worksheet and set cell style
sheet->GetRange(++currentRow, 1)->SetText(L"Formulas");
sheet->GetRange(currentRow, 2)->SetText(L"Results");
sheet->GetRange(currentRow, 1, currentRow, 2)->GetStyle()->GetFont()->SetIsBold(true);
sheet->GetRange(currentRow, 1, currentRow, 2)->GetStyle()->SetKnownColor(ExcelColors::LightGreen1);
sheet->GetRange(currentRow, 1, currentRow, 2)->GetStyle()->SetFillPattern(ExcelPatternType::Solid);
sheet->GetRange(currentRow, 1, currentRow, 2)->GetStyle()->GetBorders()->Get(BordersLineType::EdgeBottom)->SetLineStyle(LineStyleType::Medium);
//Add text and formulas to the worksheet
currentFormula = (L"=\"Hello\"");
sheet->GetRange(++currentRow, 1)->SetText((L"'" + currentFormula).c_str());
sheet->GetRange(currentRow, 2)->SetFormula(currentFormula.c_str());
currentFormula = (L"=300");
sheet->GetRange(++currentRow, 1)->SetText((L"'" + currentFormula).c_str());
sheet->GetRange(currentRow, 2)->SetFormula(currentFormula.c_str());
currentFormula = (L"=3389.639421");
sheet->GetRange(++currentRow, 1)->SetText((L"'" + currentFormula).c_str());
sheet->GetRange(currentRow, 2)->SetFormula(currentFormula.c_str());
currentFormula = (L"=false");
sheet->GetRange(++currentRow, 1)->SetText((L"'" + currentFormula).c_str());
sheet->GetRange(currentRow, 2)->SetFormula(currentFormula.c_str());
currentFormula = (L"=1+2+3+4+5-6-7+8-9");
sheet->GetRange(++currentRow, 1)->SetText((L"'" + currentFormula).c_str());
sheet->GetRange(currentRow, 2)->SetFormula(currentFormula.c_str());
currentFormula = (L"=33*3/4-2+10");
sheet->GetRange(++currentRow, 1)->SetText((L"'" + currentFormula).c_str());
sheet->GetRange(currentRow, 2)->SetFormula(currentFormula.c_str());
currentFormula = (L"=Sheet1!$B$2");
sheet->GetRange(++currentRow, 1)->SetText((L"'" + currentFormula).c_str());
sheet->GetRange(currentRow, 2)->SetFormula(currentFormula.c_str());
//Add text and Functions to the worksheet
//AVERAGE
currentFormula = (L"=AVERAGE(Sheet1!$D$2:F$2)");
sheet->GetRange(++currentRow, 1)->SetText((L"'" + currentFormula).c_str());
sheet->GetRange(currentRow, 2)->SetFormula(currentFormula.c_str());
//COUNT
currentFormula = (L"=COUNT(3,5,8,10,2,34)");
sheet->GetRange(++currentRow, 1)->SetText((L"'" + currentFormula).c_str());
sheet->GetRange(currentRow, 2)->SetFormula(currentFormula.c_str());
//NOW
currentFormula = (L"=NOW()");
sheet->GetRange(++currentRow, 1)->SetText((L"'" + currentFormula).c_str());
sheet->GetRange(currentRow, 2)->SetFormula(currentFormula.c_str());
sheet->GetRange(currentRow, 2)->GetStyle()->SetNumberFormat(L"yyyy-MM-DD");
//SECOND
currentFormula = (L"=SECOND(0.503)");
sheet->GetRange(++currentRow, 1)->SetText((L"'" + currentFormula).c_str());
sheet->GetRange(currentRow++, 2)->SetFormula(currentFormula.c_str());
//MINUTE
currentFormula = (L"=MINUTE(0.78125)");
sheet->GetRange(currentRow, 1)->SetText((L"'" + currentFormula).c_str());
sheet->GetRange(currentRow++, 2)->SetFormula(currentFormula.c_str());
//MONTH
currentFormula = (L"=MONTH(9)");
sheet->GetRange(currentRow, 1)->SetText((L"'" + currentFormula).c_str());
sheet->GetRange(currentRow++, 2)->SetFormula(currentFormula.c_str());
//DAY
currentFormula = (L"=DAY(10)");
sheet->GetRange(currentRow, 1)->SetText((L"'" + currentFormula).c_str());
sheet->GetRange(currentRow++, 2)->SetFormula(currentFormula.c_str());
//TIME
currentFormula = (L"=TIME(4,5,7)");
sheet->GetRange(currentRow, 1)->SetText((L"'" + currentFormula).c_str());
sheet->GetRange(currentRow++, 2)->SetFormula(currentFormula.c_str());
//DATE
currentFormula = (L"=DATE(6,4,2)");
sheet->GetRange(currentRow, 1)->SetText((L"'" + currentFormula).c_str());
sheet->GetRange(currentRow++, 2)->SetFormula(currentFormula.c_str());
//RAND
currentFormula = (L"=RAND()");
sheet->GetRange(currentRow, 1)->SetText((L"'" + currentFormula).c_str());
sheet->GetRange(currentRow++, 2)->SetFormula(currentFormula.c_str());
//HOUR
currentFormula = (L"=HOUR(0.5)");
sheet->GetRange(currentRow, 1)->SetText((L"'" + currentFormula).c_str());
sheet->GetRange(currentRow++, 2)->SetFormula(currentFormula.c_str());
//MOD
currentFormula = (L"=MOD(5,3)");
sheet->GetRange(currentRow, 1)->SetText((L"'" + currentFormula).c_str());
sheet->GetRange(currentRow++, 2)->SetFormula(currentFormula.c_str());
//WEEKDAY
currentFormula = (L"=WEEKDAY(3)");
sheet->GetRange(currentRow, 1)->SetText((L"'" + currentFormula).c_str());
sheet->GetRange(currentRow++, 2)->SetFormula(currentFormula.c_str());
//YEAR
currentFormula = (L"=YEAR(23)");
sheet->GetRange(currentRow, 1)->SetText((L"'" + currentFormula).c_str());
sheet->GetRange(currentRow++, 2)->SetFormula(currentFormula.c_str());
//NOT
currentFormula = (L"=NOT(true)");
sheet->GetRange(currentRow, 1)->SetText((L"'" + currentFormula).c_str());
sheet->GetRange(currentRow++, 2)->SetFormula(currentFormula.c_str());
//OR
currentFormula = (L"=OR(true)");
sheet->GetRange(currentRow, 1)->SetText((L"'" + currentFormula).c_str());
sheet->GetRange(currentRow++, 2)->SetFormula(currentFormula.c_str());
//AND
currentFormula = (L"=AND(TRUE)");
sheet->GetRange(currentRow, 1)->SetText((L"'" + currentFormula).c_str());
sheet->GetRange(currentRow++, 2)->SetFormula(currentFormula.c_str());
//VALUE
currentFormula = (L"=VALUE(30)");
sheet->GetRange(currentRow, 1)->SetText((L"'" + currentFormula).c_str());
sheet->GetRange(currentRow++, 2)->SetFormula(currentFormula.c_str());
//LEN
currentFormula = (L"=LEN(\"world\")");
sheet->GetRange(currentRow, 1)->SetText((L"'" + currentFormula).c_str());
sheet->GetRange(currentRow++, 2)->SetFormula(currentFormula.c_str());
//MID
currentFormula = (L"=MID(\"world\",4,2)");
sheet->GetRange(currentRow, 1)->SetText((L"'" + currentFormula).c_str());
sheet->GetRange(currentRow++, 2)->SetFormula(currentFormula.c_str());
//ROUND
currentFormula = (L"=ROUND(7,3)");
sheet->GetRange(currentRow, 1)->SetText((L"'" + currentFormula).c_str());
sheet->GetRange(currentRow++, 2)->SetFormula(currentFormula.c_str());
//SIGN
currentFormula = (L"=SIGN(4)");
sheet->GetRange(currentRow, 1)->SetText((L"'" + currentFormula).c_str());
sheet->GetRange(currentRow++, 2)->SetFormula(currentFormula.c_str());
//INT
currentFormula = (L"=INT(200)");
sheet->GetRange(currentRow, 1)->SetText((L"'" + currentFormula).c_str());
sheet->GetRange(currentRow++, 2)->SetFormula(currentFormula.c_str());
//ABS
currentFormula = (L"=ABS(-1.21)");
sheet->GetRange(currentRow, 1)->SetText((L"'" + currentFormula).c_str());
sheet->GetRange(currentRow++, 2)->SetFormula(currentFormula.c_str());
//LN
currentFormula = (L"=LN(15)");
sheet->GetRange(currentRow, 1)->SetText((L"'" + currentFormula).c_str());
sheet->GetRange(currentRow++, 2)->SetFormula(currentFormula.c_str());
//EXP
currentFormula = (L"=EXP(20)");
sheet->GetRange(currentRow, 1)->SetText((L"'" + currentFormula).c_str());
sheet->GetRange(currentRow++, 2)->SetFormula(currentFormula.c_str());
//SQRT
currentFormula = (L"=SQRT(40)");
sheet->GetRange(currentRow, 1)->SetText((L"'" + currentFormula).c_str());
sheet->GetRange(currentRow++, 2)->SetFormula(currentFormula.c_str());
//PI
currentFormula = (L"=PI()");
sheet->GetRange(currentRow, 1)->SetText((L"'" + currentFormula).c_str());
sheet->GetRange(currentRow++, 2)->SetFormula(currentFormula.c_str());
//COS
currentFormula = (L"=COS(9)");
sheet->GetRange(currentRow, 1)->SetText((L"'" + currentFormula).c_str());
sheet->GetRange(currentRow++, 2)->SetFormula(currentFormula.c_str());
//SIN
currentFormula = (L"=SIN(45)");
sheet->GetRange(currentRow, 1)->SetText((L"'" + currentFormula).c_str());
sheet->GetRange(currentRow++, 2)->SetFormula(currentFormula.c_str());
//MAX
currentFormula = (L"=MAX(10,30)");
sheet->GetRange(currentRow, 1)->SetText((L"'" + currentFormula).c_str());
sheet->GetRange(currentRow++, 2)->SetFormula(currentFormula.c_str());
//MIN
currentFormula = (L"=MIN(5,7)");
sheet->GetRange(currentRow, 1)->SetText((L"'" + currentFormula).c_str());
sheet->GetRange(currentRow++, 2)->SetFormula(currentFormula.c_str());
//AVERAGE
currentFormula = (L"=AVERAGE(12,45)");
sheet->GetRange(currentRow, 1)->SetText((L"'" + currentFormula).c_str());
sheet->GetRange(currentRow++, 2)->SetFormula(currentFormula.c_str());
//SUM
currentFormula = (L"=SUM(18,29)");
sheet->GetRange(currentRow, 1)->SetText((L"'" + currentFormula).c_str());
sheet->GetRange(currentRow++, 2)->SetFormula(currentFormula.c_str());
//IF
currentFormula = (L"=IF(4,2,2)");
sheet->GetRange(currentRow, 1)->SetText((L"'" + currentFormula).c_str());
sheet->GetRange(currentRow++, 2)->SetFormula(currentFormula.c_str());
//SUBTOTAL
currentFormula = (L"=SUBTOTAL(3,Sheet1!A2:F2)");
sheet->GetRange(currentRow, 1)->SetText((L"'" + currentFormula).c_str());
sheet->GetRange(currentRow++, 2)->SetFormula(currentFormula.c_str());
//Set width of the 1st, 2nd and 3rd columns
sheet->SetColumnWidth(1, 32);
sheet->SetColumnWidth(2, 16);
sheet->SetColumnWidth(3, 16);
//Create a cell style
intrusive_ptr<CellStyle> style = workbook->GetStyles()->Add(L"Style");
//Set the horizontal alignment as left
style->SetHorizontalAlignment(HorizontalAlignType::Left);
//Apply the style to the worksheet
sheet->ApplyStyle(style);
//Save the result file
workbook->SaveToFile(L"InsertFormulasAndFunctions.xlsx", ExcelVersion::Version2016);
workbook->Dispose();
}
Read Formulas and Functions in Excel in C++
To read formulas and functions in an Excel worksheet, you need to iterate through all the cells in the worksheet, after that, find the cells containing formulas or functions using the Cell->GetHasFormula() method, then get the formulas or functions of the cells using the CellRange->GetFormula() method. The detailed steps are as follows:
- Initialize an instance of the Workbook class.
- Load an Excel file using the Workbook->LoadFromFile() method.
- Get a specific worksheet by its index using the Workbook->GetWorksheets()->Get(int index) method.
- Access the used range of the worksheet using the Worksheet->GetAllocatedRange() method.
- Declare a wstring variable.
- Iterate through all the cells in the used range.
- Find the cells containing formulas/functions using the Cell->GetHasFormula() method.
- Get the names and the formulas/functions of the cells using the CellRange->GetRangeAddressLocal() and CellRange->GetFormula() methods.
- Append the cell names and formulas/functions to the wstring variable.
- Write the content of the wstring variable into a .txt file.
- C++
#include "Spire.Xls.o.h";
using namespace Spire::Xls;
using namespace std;
int main()
{
//Initialize an instance of the Workbook class
intrusive_ptr<Worksheet> workbook = new Workbook();
//Load an Excel file
workbook->LoadFromFile(L"InsertFormulasAndFunctions.xlsx");
//Get the first worksheet
intrusive_ptr<Worksheet> sheet = dynamic_pointer_cast<Worksheet>(workbook->GetWorksheets()->Get(0));
//Access the used range of the worksheet
intrusive_ptr<CellRange> usedRange = dynamic_pointer_cast<CellRange>(sheet->GetAllocatedRange());
//Declare a wstring variable
wstring buffer = L"";
//Loop through all the cells in the used range
for (int i = 0; i < usedRange->GetCells()->GetCount(); i++)
{
intrusive_ptr<CellRange> cell = usedRange->GetCells()->GetItem(i);
//Detect if the current cell has formula/function
if (cell->GetHasFormula())
{
//Get the cell name
wstring cellName = cell->GetRangeAddressLocal();
//Get the formula/function
wstring formula = cell->GetFormula();
//Append the cell name and formula/function to the wstring variable
buffer += ((cellName + L" has a formula: " + formula + L"\n").c_str());
}
}
//Write the content of the wstring variable into a .txt file
wofstream write(L"ReadFormulasAndFunctions.txt");
auto LocUtf8 = locale(locale(""), new std::codecvt_utf8<wchar_t>);
write.imbue(LocUtf8);
write << buffer;
write.close();
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.
