basicexcel.hpp

对EXCEL的操作

				struct BoolErr : public Record
				{
					BoolErr();
					virtual size_t Read(const char* data);
					virtual size_t Write(char* data);	
					short rowIndex_;
					short colIndex_;
					short XFRecordIndex_;
					char value_;
					char error_;	
				};
				struct LabelSST : public Record
				{
					LabelSST();
					virtual size_t Read(const char* data);
					virtual size_t Write(char* data);	
					short rowIndex_;
					short colIndex_;
					short XFRecordIndex_;
					size_t SSTRecordIndex_;	
				};
				struct MulBlank : public Record
				{
					MulBlank();
					virtual size_t Read(const char* data);
					virtual size_t Write(char* data);	
					virtual size_t DataSize();
					virtual size_t RecordSize();
					short rowIndex_;
					short firstColIndex_;
					vector<short> XFRecordIndices_;
					short lastColIndex_;
				};
				struct MulRK : public Record
				{
					MulRK();
					virtual size_t Read(const char* data);
					virtual size_t Write(char* data);
					virtual size_t DataSize();
					virtual size_t RecordSize();
					struct XFRK
					{
						XFRK();
						void Read(const char* data);
						void Write(char* data);
						short XFRecordIndex_;
						int RKValue_;
					};
					short rowIndex_;
					short firstColIndex_;
					vector<XFRK> XFRK_;
					short lastColIndex_;
				};
				struct Number : public Record
				{
					Number();
					virtual size_t Read(const char* data);
					virtual size_t Write(char* data);	
					short rowIndex_;
					short colIndex_;
					short XFRecordIndex_;
					double value_;

				private:
					union 
					{
						long long intvalue_;
						double doublevalue_;
					} intdouble_;
				};
				struct RK : public Record
				{
					RK();
					virtual size_t Read(const char* data);
					virtual size_t Write(char* data);	
					short rowIndex_;
					short colIndex_;
					short XFRecordIndex_;
					int value_;	
				};

				struct Formula : public Record
				{
					struct Array : public Record
					{
						Array();
						virtual size_t Read(const char* data);
						virtual size_t Write(char* data);	
						virtual size_t DataSize();
						virtual size_t RecordSize();
						short firstRowIndex_;
						short lastRowIndex_;
						char firstColIndex_;
						char lastColIndex_;
						short options_;
						int unused_;
						vector<char> formula_;
					};
					struct ShrFmla : public Record
					{
						ShrFmla();
						virtual size_t Read(const char* data);
						virtual size_t Write(char* data);	
						virtual size_t DataSize();
						virtual size_t RecordSize();
						short firstRowIndex_;
						short lastRowIndex_;
						char firstColIndex_;
						char lastColIndex_;
						short unused_;
						vector<char> formula_;
					};
					struct ShrFmla1 : public Record
					{
						ShrFmla1();
						virtual size_t Read(const char* data);
						virtual size_t Write(char* data);	
						virtual size_t DataSize();
						virtual size_t RecordSize();
						short firstRowIndex_;
						short lastRowIndex_;
						char firstColIndex_;
						char lastColIndex_;
						short unused_;
						vector<char> formula_;
					};
					struct Table : public Record
					{
						Table();
						virtual size_t Read(const char* data);
						virtual size_t Write(char* data);	
						short firstRowIndex_;
						short lastRowIndex_;
						char firstColIndex_;
						char lastColIndex_;
						short options_;
						short inputCellRowIndex_;
						short inputCellColIndex_;
						short inputCellColumnInputRowIndex_;
						short inputCellColumnInputColIndex_;
					};
					struct String : public Record
					{
						String();
						virtual size_t Read(const char* data);
						virtual size_t Write(char* data);	
						virtual size_t DataSize();
						virtual size_t RecordSize();
						vector<wchar_t> string_;
					};

					Formula();
					virtual size_t Read(const char* data);
					virtual size_t Write(char* data);	
					virtual size_t DataSize();
					virtual size_t RecordSize();
					short rowIndex_;
					short colIndex_;
					short XFRecordIndex_;
					char result_[8];
					short options_;
					int unused_;
					vector<char> RPNtoken_;
					short type_;

					Array array_;
					ShrFmla shrfmla_;
					ShrFmla1 shrfmla1_;
					Table table_;
					String string_;
				};

				CellBlock();
				~CellBlock();
				void Reset();
				size_t Read(const char* data);
				size_t Write(char* data);
				size_t DataSize();
				size_t RecordSize();
				short RowIndex();
				short ColIndex();
				short LastColIndex();
				short type_;
				bool normalType_;
				
				Blank blank_;
				BoolErr boolerr_;
				LabelSST labelsst_;
				MulBlank mulblank_;
				MulRK mulrk_;
				Number number_;
				RK rk_;
				Formula formula_;
			};
			struct DBCell : public Record
			{
				DBCell();
				virtual size_t Read(const char* data);
				virtual size_t Write(char* data);	
				virtual size_t DataSize();
				virtual size_t RecordSize();
				int firstRowOffset_;
				vector<short> offsets_;
			};			
			
			size_t Read(const char* data);
			size_t Write(char* data);
			size_t DataSize();
			size_t RecordSize();
		
			vector<Row> rows_;
			vector<CellBlock> cellBlocks_;
			DBCell dbcell_;
		};	
		size_t Read(const char* data);
		size_t Write(char* data);
		size_t DataSize();
		size_t RecordSize();
	
		vector<RowBlock> rowBlocks_;		
	};
	struct Window2 : public Record
	{
		Window2();
		virtual size_t Read(const char* data);
		virtual size_t Write(char* data);	
		short options_;
		short firstVisibleRowIndex_;
		short firstVisibleColIndex_;
		short gridLineColourIndex_;
		short unused1_;
		short magnificationFactorPageBreakPreview_;
		short magnificationFactorNormalView_;
		int unused2_;		
	};
	struct SCL;
	struct Pane;
	struct Selection;
	struct MergedCells;
	struct LabelRanges;
	struct ConditionalFormattingTable;
	struct HyperlinkTable;
	struct SheetLayout;
	struct SheetProtection;
	struct RangeProtection;

	size_t Read(const char* data);
	size_t Write(char* data);
	size_t DataSize();
	size_t RecordSize();

	BOF bof_;
	Index index_;
	Dimensions dimensions_;
	CellTable cellTable_;
	Window2 window2_;
	YEOF eof_;
};

bool IsRKValueAnInteger(int rkValue);		///< Returns true if the supplied rk value contains an integer. 
bool IsRKValueADouble(int rkValue);			///< Returns true if the supplied rk value contains a double.
double GetDoubleFromRKValue(int rkValue);	///< Convert a rk value to a double.
int GetIntegerFromRKValue(int rkValue);		///< Convert a rk value to an integer.
int GetRKValueFromDouble(double value);		///< Convert a double to a rk value.
int GetRKValueFromInteger(int value);		///< Convert an integer to a rk value.
bool CanStoreAsRKValue(double value);		///< Returns true if the supplied double can be stored as a rk value.

// Forward declarations
class BasicExcel;
class BasicExcelWorksheet;
class BasicExcelCell;

/*******************************************************************************************************/
/*                         Actual classes to read and write to Excel files                             */
/*******************************************************************************************************/
class BasicExcel
{
public:
	BasicExcel();
	BasicExcel(const char* filename);
	~BasicExcel();

public: // File functions.
	void New(int sheets=3);	///< Create a new Excel workbook with a given number of spreadsheets (Minimum 1).
	bool Load(const char* filename);	///< Load an Excel workbook from a file.
	bool Save();	///< Save current Excel workbook to opened file.
	bool SaveAs(const char* filename);	///< Save current Excel workbook to a file.

public: // Worksheet functions.
	size_t GetTotalWorkSheets();	///< Total number of Excel worksheets in current Excel workbook.

	BasicExcelWorksheet* GetWorksheet(size_t sheetIndex);	///< Get a pointer to an Excel worksheet at the given index. Index starts from 0. Returns 0 if index is invalid.
	BasicExcelWorksheet* GetWorksheet(const char* name);	///< Get a pointer to an Excel worksheet that has given ANSI name. Returns 0 if there is no Excel worksheet with the given name.
	BasicExcelWorksheet* GetWorksheet(const wchar_t* name);	///< Get a pointer to an Excel worksheet that has given Unicode name. Returns 0 if there is no Excel worksheet with the given name.
	
	BasicExcelWorksheet* AddWorksheet(int sheetIndex=-1);	///< Add a new Excel worksheet to the given index. Name given to worksheet is SheetX, where X is a number which starts from 1. Index starts from 0. Worksheet is added to the last position if sheetIndex == -1. Returns a pointer to the worksheet if successful, 0 if otherwise.
	BasicExcelWorksheet* AddWorksheet(const char* name, int sheetIndex=-1);	///< Add a new Excel worksheet with given ANSI name to the given index. Index starts from 0. Worksheet is added to the last position if sheetIndex == -1. Returns a pointer to the worksheet if successful, 0 if otherwise.
	BasicExcelWorksheet* AddWorksheet(const wchar_t* name, int sheetIndex=-1);	///< Add a new Excel worksheet with given Unicode name to the given index. Index starts from 0. Worksheet is added to the last position if sheetIndex == -1. Returns a pointer to the worksheet if successful, 0 if otherwise.

	bool DeleteWorksheet(size_t sheetIndex);	///< Delete an Excel worksheet at the given index. Index starts from 0. Returns true if successful, false if otherwise.
	bool DeleteWorksheet(const char* name);		///< Delete an Excel worksheet that has given ANSI name. Returns true if successful, false if otherwise.
	bool DeleteWorksheet(const wchar_t* name);	///< Delete an Excel worksheet that has given Unicode name. Returns true if successful, false if otherwise.

	char* GetAnsiSheetName(size_t sheetIndex);	///< Get the worksheet name at the given index. Index starts from 0. Returns 0 if name is in Unicode format.
	wchar_t* GetUnicodeSheetName(size_t sheetIndex);///< Get the worksheet name at the given index. Index starts from 0. Returns 0 if name is in Ansi format.
	bool GetSheetName(size_t sheetIndex, char* name);		///< Get the worksheet name at the given index. Index starts from 0. Returns false if name is in Unicode format.
	bool GetSheetName(size_t sheetIndex, wchar_t* name);	///< Get the worksheet name at the given index. Index starts from 0. Returns false if name is in Ansi format.

	bool RenameWorksheet(size_t sheetIndex, const char* to);		///< Rename an Excel worksheet at the given index to the given ANSI name. Index starts from 0. Returns true if successful, false if otherwise.
	bool RenameWorksheet(size_t sheetIndex, const wchar_t* to);		///< Rename an Excel worksheet at the given index to the given Unicode name. Index starts from 0. Returns true if successful, false if otherwise.
	bool RenameWorksheet(const char* from, const char* to);			///< Rename an Excel worksheet that has given ANSI name to another ANSI name. Returns true if successful, false if otherwise.
	bool RenameWorksheet(const wchar_t* from, const wchar_t* to);	///< Rename an Excel worksheet that has given Unicode name to another Unicode name. Returns true if successful, false if otherwise.

private: // Functions to read and write raw Excel format.
	size_t Read(const char* data, size_t dataSize);
	size_t Write(char* data);
	void AdjustStreamPositions();
	void AdjustBoundSheetBOFPositions();
	void AdjustDBCellPositions();
	void AdjustExtSSTPositions();

	enum {WORKBOOK_GLOBALS=0x0005, VISUAL_BASIC_MODULE=0x0006,
		  WORKSHEET=0x0010, CHART=0x0020};
	
private: // Internal functions
	void UpdateYExcelWorksheet();	///< Update yesheets_ using information from worksheets_.
	void UpdateWorksheets();		///< Update worksheets_ using information from yesheets_.

public:
	CompoundFile file_;						///< Compound file handler.
	Workbook workbook_;						///< Raw Workbook.
	vector<Worksheet> worksheets_;			///< Raw Worksheets.
	vector<BasicExcelWorksheet> yesheets_;	///< Parsed Worksheets.
};

class BasicExcelWorksheet
{
	friend class BasicExcel;

public:
	BasicExcelWorksheet(BasicExcel* excel, size_t sheetIndex);

public: // Worksheet functions
	char* GetAnsiSheetName();	///< Get the current worksheet name. Returns 0 if name is in Unicode format.
	wchar_t* GetUnicodeSheetName();///< Get the current worksheet name. Returns 0 if name is in Ansi format.
	bool GetSheetName(char* name);		///< Get the current worksheet name. Returns false if name is in Unicode format.
	bool GetSheetName(wchar_t* name);	///< Get the current worksheet name. Returns false if name is in Ansi format.
	bool Rename(const char* to);	///< Rename current Excel worksheet to another ANSI name. Returns true if successful, false if otherwise.
	bool Rename(const wchar_t* to);///< Rename current Excel worksheet to another Unicode name. Returns true if successful, false if otherwise.
	void Print(ostream& os, char delimiter=',', char textQualifier='\0'); ///< Print entire worksheet to an output stream, separating each column with the defined delimiter and enclosing text using the defined textQualifier. Leave out the textQualifier argument if do not wish to have any text qualifiers.

public: // Cell functions
	size_t GetTotalRows();	///< Total number of rows in current Excel worksheet.
	size_t GetTotalCols();	///< Total number of columns in current Excel worksheet.

	BasicExcelCell* Cell(size_t row, size_t col); ///< Return a pointer to an Excel cell. row and col starts from 0. Returns 0 if row exceeds 65535 or col exceeds 255.
	bool EraseCell(size_t row, size_t col); ///< Erase content of a cell. row and col starts from 0. Returns true if successful, false if row or col exceeds range.

private: // Internal functions
	void UpdateCells();	///< Update cells using information from BasicExcel.worksheets_.

private:
	BasicExcel* excel_;					///< Pointer to instance of BasicExcel.
	size_t sheetIndex_;					///< Index of worksheet in workbook.
	size_t maxRows_;					///< Total number of rows in worksheet.
	size_t maxCols_;					///< Total number of columns in worksheet.
	vector<vector<BasicExcelCell> > cells_;	///< Cells matrix.
};

class BasicExcelCell
{
public:
	BasicExcelCell();

public:
	enum {UNDEFINED, INT, DOUBLE, STRING, WSTRING};
	int Type() const;		///< Get type of value stored in current Excel cell. Returns one of the above enums. 

	bool Get(int& val) const;		///< Get an integer value. Returns false if cell does not contain an integer.
	bool Get(double& val) const;	///< Get a double value. Returns false if cell does not contain a double.
	bool Get(char* str) const;	///< Get an ANSI string. Returns false if cell does not contain an ANSI string.
	bool Get(wchar_t* str) const;	///< Get an Unicode string. Returns false if cell does not contain an Unicode string.
	size_t GetStringLength() const;	///< Return length of ANSI or Unicode string (excluding null character).

	int GetInteger() const;		///< Get an integer value. Returns 0 if cell does not contain an integer.
	double GetDouble() const;		///< Get a double value. Returns 0.0 if cell does not contain a double.
	const char* GetString() const;		///< Get an ANSI string. Returns 0 if cell does not contain an ANSI string.
	const wchar_t* GetWString() const;	///< Get an Unicode string. Returns 0 if cell does not contain an Unicode string.

	friend ostream& operator<<(ostream& os, const BasicExcelCell& cell);	///< Print cell to output stream. Print a null character if cell is undefined.

	void Set(int val);				///< Set content of current Excel cell to an integer.
	void Set(double val);			///< Set content of current Excel cell to a double.
	void Set(const char* str);		///< Set content of current Excel cell to an ANSI string.
	void Set(const wchar_t* str);	///< Set content of current Excel cell to an Unicode string.
	
	void SetInteger(int val);			///< Set content of current Excel cell to an integer.
	void SetDouble(double val);			///< Set content of current Excel cell to a double.
	void SetString(const char* str);	///< Set content of current Excel cell to an ANSI string.
	void SetWString(const wchar_t* str);///< Set content of current Excel cell to an Unicode string.

	void EraseContents();	///< Erase the content of current Excel cell. Set type to UNDEFINED.

private:
	int type_;				///< Type of value stored in current Excel cell. Contains one of the above enums.
	int ival_;				///< Integer value stored in current Excel cell.
	double dval_;			///< Double value stored in current Excel cell.
	vector<char> str_;		///< ANSI string stored in current Excel cell. Include null character.
	vector<wchar_t> wstr_;	///< Unicode string stored in current Excel cell. Include null character.
};

} // Namespace end
#endif