How to get multiple worsheets and maintain the names

[daynahrod]

Spire.XLS.Cpp Version 13.6.1

How do I get multiple worksheets from my input XLS file and maintain their worksheet names before converting to a CSV?

This is my code so far:

#include "Spire.Xls.o.h";

using namespace Spire::Xls;

int main()
{
//Initialize an instance of the Workbook class
Workbook* workbook = new Workbook();

//Load an Excel file
workbook->LoadFromFile(L"filename");

//Get the first worksheet
intrusive_ptr<Worksheet> sheet = dynamic_pointer_cast<Worksheet>(workbook->GetWorksheets()->Get(0));

//Delete the first row from the Spreashsheet database as the text is wrapped and it gets messed up when converted to CSV
sheet->DeleteRow(1);

//Save the worksheet to a CSV file
sheet->SaveToFile(L"ExcelToCsv.csv", L",", Encoding::GetUTF8());
workbook->Dispose();
delete workbook;


}

Thank you,
Daynah

[Triste.Dai]

Hi,

To extract multiple worksheets from your input XLS file and preserve their worksheet names before converting to a CSV format, you can use the following code snippet:

Code: Select all

vector<intrusive_ptr<Worksheet>> v;
// Obtain the top five sheet
for (int i = 0; i < 5; i++)
{
    intrusive_ptr<Worksheet> sheet = dynamic_pointer_cast<Worksheet> (workbook->GetWorksheets()->Get(i));
    v.push_back(sheet);
}

// get the name of the sescond sheet;
intrusive_ptr<Worksheet> sheet2 = v.at(1);
wstring sheet_name = sheet2->GetName();



This sample demonstrates how to retrieve the top five worksheets from the XLS file and store them in a vector. It then retrieves the name of the second sheet using sheet2->GetName(). Note that you can modify the loop condition (i < 5) to specify the number of worksheets you want to extract.

If you have any further questions or need additional assistance, please feel free to ask.

Best regarsd,
Triste
E-iceblue support team

[daynahrod]

Thank you

How would you save those worksheets in the same workbook before the conversion? Would you use .Add to make sure they are added?

[Triste.Dai]

Hi,

Thanks for your feedback.
I'm having some difficulty fully understanding your question. To assist you better, could you please provide more details regarding your specific needs or perhaps include some screenshots illustrating the desired outcome? This additional information will greatly help me in addressing your query accurately.

Looking forward to your response and assisting you further.

Best regards,
Triste
E-iceblue support team

[daynahrod]

Sorry about that.

Using the snippet that you provided, here is what I am trying to do:

vector<intrusive_ptr<Worksheet>> v;

for (int i = 0; i < 5; i++)
{
//Gets each worksheet
intrusive_ptr<Worksheet> sheet = dynamic_pointer_cast<Worksheet> (workbook->GetWorksheets()->Get(i));
v.push_back(sheet);

// get the name of each sheet;
sheet = v.at(i);
wstring sheet_name = sheet->GetName();

//Save each worksheet from the workbook and have them appear in the CSV

//Delete the first row from the Spreashsheet database as the text is wrapped and it gets messed up when converted to CSV
sheet->DeleteRow(1);

//Save the worksheet to a CSV file
sheet->SaveToFile(L"ExcelToCsv.csv", L",", Encoding::GetUTF8());

}


workbook->Dispose();
delete workbook;

[daynahrod]

Hello,

This is for reference for anyone that wants to extract the worksheets into individual CSV files. I had misunderstood the way that CSV files work in Excel. You can't merge worksheets into one CSV file, so you have to convert them separately.

#include "Spire.Xls.o.h"

using namespace std;
using namespace Spire::Xls;

int main()
{

//Initialize an instance of the Workbook class
intrusive_ptr<Workbook> workbook = new Workbook();


//Load an Excel file
workbook->LoadFromFile(L"filename");


vector<intrusive_ptr<Worksheet>> v;

// Obtain the top six sheets
for (int i = 0; i < 6; i++)
{
//Gets each worksheet
intrusive_ptr<Worksheet> sheet = dynamic_pointer_cast<Worksheet> (workbook->GetWorksheets()->Get(i));
v.push_back(sheet);

// get the name of each sheet;
sheet = v.at(i);
wstring sheet_name = sheet->GetName();
wstring output = sheet_name + L".csv";

// Create a instance of the Workbook class to represent the new workbook
intrusive_ptr<Workbook> newWorkbook = new Workbook();
// Copy the previously retrieved worksheet to the new workbook
newWorkbook->GetWorksheets()->AddCopy(sheet);


//Delete the first row from the Spreashsheet database as the text is wrapped and it gets messed up when converted to CSV
sheet->DeleteRow(1);


//convert to CSV file
sheet->SaveToFile(output.c_str(), L",", Encoding::GetUTF8());
newWorkbook->Dispose();


}


}

[Triste.Dai]

Hi,

Thanks for your feedback.
Apologize for the late response due to the Mid-Autumn Festival and National Day holidays.
Glad to know that you have found the solution. If you have any other questions, just feel free to contact us. We are here to assist you.

Have a nice day!

Best regards,
Triste
E-iceblue support team