How to open an excel file and save it as a text file

[Alimaz]

Hi everyone! I just want to open an excel file and save it as a text file.
I think it would be simple but i don't know it.( i want it for 1000 files and because of that i need C++ help)

thanks in advance!

[dutch]

I think it would be simple

Don't be so sure! It could be very complicated indeed. The binary .xlsb file structure has a 1000 page document describing it.

Doesn't excel have a built-in language that you might be able to use to automate this task?

If you want to do it in C++, can you upload an example file somewhere and post a link here?

[Computergeek01]

Does this have to be C\C++? it's braindead easy in VBS or Powershell and a waking nightmare in C\C++. The problem is that Excel is a zip file format structured like a mini database. Open it up with 7-zip one day, it's not pretty.

[jonnin]

there are some third party tools out there that can supposedly do this from the command line so a batch file for each file in the folder would knock it out.

No, excel does not have a CLI for this. Excel supports basic for advanced macros and buttons and widgets.

You may be able to drag and drop a number of the files into excel at once and have it open a bunch of them at once, then figure out some way to save them all to csv. Seems clunky.

[thmm]

Which file format do you want to use, the old .xls or the newer .xlsx ?

[seeplus]

You can open an excel file as an object in c++ and then use the automation methods et al to access the spreadsheet cells, ranges etc. Consider for Office 2010 for a Windows 7 laptop as an example only:

#pragma warning (disable : 4471)

#import "c:\\Program Files (x86)\\Common Files\\microsoft shared\\OFFICE14\\mso.dll" \
	rename( "RGB", "MSORGB" ) \
	rename( "DocumentProperties", "MSDocument") \
	rename( "SearchPath", "MSSearch")

#import "c:\\Program Files (x86)\\Common Files\\microsoft shared\\VBA\\VBA6\\VBE6EXT.OLB"

#import "c:\\Program Files (x86)\Microsoft Office\\Office14\\excel.exe" \
	rename( "DialogBox", "ExcelDialogBox" ) \
	rename( "RGB", "ExcelRGB" ) \
	rename( "CopyFile", "ExcelCopyFile" ) \
	rename( "ReplaceText", "ExcelReplaceText" )


#include <iostream>
#include <string>

using namespace Excel;
using namespace std;

const string sname {"c:\\MyProgs\\Level_4_60.xlsm"};	// Complete path name to Excel file

int main()
{
	const auto hr {CoInitializeEx(0, COINIT_MULTITHREADED)};

	if (FAILED(hr))
		return (cout << "Failed to initialize COM library. Error code = 0x" << hex << hr << endl), 1;

	_ApplicationPtr pXL;

	if (FAILED(pXL.CreateInstance("Excel.Application")))
		return (cout << "Failed to initialize Excel::_Application!" << endl), 2;

	_WorkbookPtr pBook;

	try {
		pBook = pXL->Workbooks->Open(sname.c_str());
	}
	catch (...) {
		pXL->Quit();
		return (cout << "Cannot open spreadsheet file " << sname << endl), 3;
	}

	pXL->PutVisible(0, FALSE);

	const _WorksheetPtr pWksheet {pXL->ActiveSheet};
	const RangePtr pRange {pWksheet->Cells};

	const int nRange {12};		// Number of ranges
	const int rRow {15};		// Number of rows per range
	const int rCol {4};			// Number of cols per range
	const int rcStart {8};		// H for starting col of first range
	const int rrStart {10};		// Starting row for the ranges

	int rData[nRange][rRow][rCol] {0};	// Contains the data for the various required ranges from the spreadsheet

	bool bad {false};	// Has data read failed

	int vPar6 {};
	int vMin6 {};
	int vMax6 {};

	try {
		vPar6 = (int)pRange->Item[8][25];		// Y8
		vMin6 = (int)pRange->Item[8][48];		// AV8
		vMax6 = (int)pRange->Item[8][50];		// AX8

		// Obtain the range data
		for (int r {0}; r < nRange; ++r)
			for (int cl {0}; cl < rCol; ++cl)
				for (int rw {0}; rw < rRow; ++rw)
					rData[r][rw][cl] = (int)pRange->Item[rrStart + rw][rcStart + (r * rCol) + cl];
	}
	catch (...) {
		bad = true;
	}

	pWksheet->Release();
	pBook->Release();
	pXL->Quit();

	if (bad)
		return (cout << "Cannot read the data!" << endl), 4;

       // PROCESS DATA HERE
}

This obtains some numeric data from a spreadsheet and puts it into an array for processing.

Depending upon the spreadsheet layout, then obviously the method of extraction might change etc. Once you can extract it from the spreadsheet, then saving it to a text file is fairly easy.