// Class to read and write to Excel and text delimited spreadsheet
//
// Created by Yap Chun Wei
// December 2001
// 
// Version 1.1
// Updates: Fix bug in ReadRow() which prevent reading of single column spreadsheet
// Modified by jingzhou xu

#ifndef CSPREADSHEET_H
#define CSPREADSHEET_H

#include <odbcinst.h>
#include <afxdb.h>

class CSpreadSheet
{
public:
	CSpreadSheet(CString File, CString SheetOrSeparator, bool Backup = true); // Open spreadsheet for reading and writing
	~CSpreadSheet(); // Perform some cleanup functions
	bool AddHeaders(CStringArray &FieldNames, bool replace = false); // Add header row to spreadsheet
	bool DeleteSheet(); // Clear text delimited file content
	bool DeleteSheet(CString SheetName); // Clear entire Excel spreadsheet content. The sheet itself is not deleted
	bool AddRow(CStringArray &RowValues, long row = 0, bool replace = false); // Insert or replace a row into spreadsheet. Default is add new row. 
	bool AddCell(CString CellValue, CString column, long row = 0, bool Auto = true); // Replace or add a cell into Excel spreadsheet using header row or column alphabet. Default is add cell into new row. Set Auto to false if want to force column to be used as header name
	bool AddCell(CString CellValue, short column, long row = 0); // Replace or add a cell into spreadsheet using column number. Default is add cell into new row. 
	bool ReplaceRows(CStringArray &NewRowValues, CStringArray &OldRowValues); // Search and replace rows in Excel spreadsheet
	bool ReadRow(CStringArray &RowValues, long row = 0); // Read a row from spreadsheet. Default is read the next row
	bool ReadColumn(CStringArray &ColumnValues, CString column, bool Auto = true); // Read a column from Excel spreadsheet using header row or column alphabet. Set Auto to false if want to force column to be used as header name
	bool ReadColumn(CStringArray &ColumnValues, short column); // Read a column from spreadsheet using column number
	bool ReadCell (CString &CellValue, CString column, long row = 0, bool Auto = true); // Read a cell from Excel spreadsheet using header row or column alphabet. Default is read the next cell in next row. Set Auto to false if want to force column to be used as header name
	bool ReadCell (CString &CellValue, short column, long row = 0); // Read a cell from spreadsheet using column number. Default is read the next cell in next row.
	void BeginTransaction(); // Begin transaction
	bool Commit(); // Save changes to spreadsheet
	bool RollBack(); // Undo changes to spreadsheet
	bool Convert(CString SheetOrSeparator);
	inline void GetFieldNames (CStringArray &FieldNames) {FieldNames.RemoveAll(); FieldNames.Copy(m_aFieldNames);} // Get the header row from spreadsheet
	inline long GetTotalRows() {return m_dTotalRows;} // Get total number of rows in  spreadsheet
	inline short GetTotalColumns() {return m_dTotalColumns;} // Get total number of columns in  spreadsheet
	inline long GetCurrentRow() {return m_dCurrentRow;} // Get the currently selected row in  spreadsheet
	inline bool GetBackupStatus() {return m_bBackup;} // Get status of backup. True if backup is successful, False if spreadsheet is not backup
	inline bool GetTransactionStatus() {return m_bTransaction;} // Get status of Transaction. True if Transaction is started, False if Transaction is not started or has error in starting
	inline CString GetLastError() {return m_sLastError;} // Get last error message

private:
	bool Open(); // Open a text delimited file for reading or writing
	void GetExcelDriver(); // Get the name of the Excel-ODBC driver
	short CalculateColumnNumber(CString column, bool Auto); // Convert Excel column in alphabet into column number

	bool m_bAppend; // Internal flag to denote newly created spreadsheet or previously created spreadsheet
	bool m_bBackup; // Internal flag to denote status of Backup
	bool m_bExcel; // Internal flag to denote whether file is Excel spreadsheet or text delimited spreadsheet
	bool m_bTransaction; // Internal flag to denote status of Transaction

	long m_dCurrentRow; // Index of current row, starting from 1
	long m_dTotalRows; // Total number of rows in spreadsheet
	short m_dTotalColumns; // Total number of columns in Excel spreadsheet. Largest number of columns in text delimited spreadsheet

	CString m_sSql; // SQL statement to open Excel spreadsheet for reading
	CString m_sDsn; // DSN string to open Excel spreadsheet for reading and writing
	CString m_stempSql; // Temporary string for SQL statements or for use by functions
	CString m_stempString; // Temporary string for use by functions
	CString m_sSheetName; // Sheet name of Excel spreadsheet
	CString m_sExcelDriver; // Name of Excel Driver
	CString m_sFile; // Spreadsheet file name
	CString m_sSeparator; // Separator in text delimited spreadsheet
	CString m_sLastError; // Last error message

	CStringArray m_atempArray; // Temporary array for use by functions
	CStringArray m_aFieldNames; // Header row in spreadsheet
	CStringArray m_aRows; // Content of all the rows in spreadsheet

	CDatabase *m_Database; // Database variable for Excel spreadsheet
	CRecordset *m_rSheet; // Recordset for Excel spreadsheet
};

// Open spreadsheet for reading and writing
CSpreadSheet::CSpreadSheet(CString File, CString SheetOrSeparator, bool Backup) :
m_Database(NULL), m_rSheet(NULL), m_sFile(File),
m_dTotalRows(0), m_dTotalColumns(0), m_dCurrentRow(1),
m_bAppend(false), m_bBackup(Backup), m_bTransaction(false)
{
	// Detect whether file is an Excel spreadsheet or a text delimited file
	m_stempString = m_sFile.Right(4);
	m_stempString.MakeLower();
	if (m_stempString == ".xls") // File is an Excel spreadsheet
	{
		m_bExcel = true;
		m_sSheetName = SheetOrSeparator;
		m_sSeparator = ",;.?";
	}
	else // File is a text delimited file
	{
		m_bExcel = false;
		m_sSeparator = SheetOrSeparator;
	}

	if (m_bExcel) // If file is an Excel spreadsheet
	{
		m_Database = new CDatabase;
		GetExcelDriver();
		m_sDsn.Format("DRIVER={%s};DSN='';FIRSTROWHASNAMES=1;READONLY=FALSE;CREATE_DB=\"%s\";DBQ=%s", m_sExcelDriver, m_sFile, m_sFile);

		if (Open())
		{
			if (m_bBackup)
			{
				if ((m_bBackup) && (m_bAppend))
				{
					CString tempSheetName = m_sSheetName;
					m_sSheetName = "CSpreadSheetBackup";
					m_bAppend = false;
					if (!Commit())
					{
						m_bBackup = false;
					}
					m_bAppend = true;
					m_sSheetName = tempSheetName;
					m_dCurrentRow = 1;
				}
			}
		}
	}
	else // if file is a text delimited file
	{
		if (Open())
		{
			if ((m_bBackup) && (m_bAppend))
			{
				m_stempString = m_sFile;
				m_stempSql.Format("%s.bak", m_sFile);
				m_sFile = m_stempSql;
				if (!Commit())
				{
					m_bBackup = false;
				}
				m_sFile = m_stempString;
			}
		}
	}
}

// Perform some cleanup functions
CSpreadSheet::~CSpreadSheet()
{
	if (m_Database != NULL)
	{
		m_Database->Close();
		delete m_Database;
	}
}

// Add header row to spreadsheet
bool CSpreadSheet::AddHeaders(CStringArray &FieldNames, bool replace)
{
	if (m_bAppend) // Append to old Sheet
	{
		if (replace) // Replacing header row rather than adding new columns
		{
			if (!AddRow(FieldNames, 1, true))
			{
				return false;
			}
			else
			{
				return true;
			}
		}

		if (ReadRow(m_atempArray, 1)) // Add new columns
		{
			if (m_bExcel)
			{
				// Check for duplicate header row field
				for (int i = 0; i < FieldNames.GetSize(); i++)
				{
					for (int j = 0; j < m_atempArray.GetSize(); j++)
					{
						if (FieldNames.GetAt(i) == m_atempArray.GetAt(j))
						{
							m_sLastError.Format("Duplicate header row field:%s\n", FieldNames.GetAt(i));
							return false;
						}
					}
				}	
			}

			m_atempArray.Append(FieldNames);
			if (!AddRow(m_atempArray, 1, true))
			{
				m_sLastError = "Problems with adding headers\n";
				return false;
			}

			// Update largest number of columns if necessary
			if (m_atempArray.GetSize() > m_dTotalColumns)
			{
				m_dTotalColumns = m_atempArray.GetSize();
			}
			return true;
		}
		return false;				
	}
	else // New Sheet
	{
		m_dTotalColumns = FieldNames.GetSize();
		if (!AddRow(FieldNames, 1, true))
		{
			return false;
		}
		else
		{
			m_dTotalRows = 1;
			return true;
		}
	}
}

// Clear text delimited file content
bool CSpreadSheet::DeleteSheet()
{
	if (m_bExcel)
	{
		if (DeleteSheet(m_sSheetName))
		{
			return true;
		}
		else
		{
			m_sLastError = "Error deleting sheet\n";
			return false;
		}
	}
	else
	{
		m_aRows.RemoveAll();
		m_aFieldNames.RemoveAll();
		m_dTotalColumns = 0;
		m_dTotalRows = 0;
		if (!m_bTransaction)
		{
			Commit();			
		}
		m_bAppend = false; // Set flag to new sheet
		return true;		
	}
}

// Clear entire Excel spreadsheet content. The sheet itself is not deleted
bool CSpreadSheet::DeleteSheet(CString SheetName)
{
	if (m_bExcel) // If file is an Excel spreadsheet
	{
		// Delete sheet
		m_Database->OpenEx(m_sDsn, CDatabase::noOdbcDialog);
		SheetName = "[" + SheetName + "$A1:IV65536]";
		m_stempSql.Format ("DROP TABLE %s", SheetName);
		try
		{
			m_Database->ExecuteSQL(m_stempSql);
			m_Database->Close();
			m_aRows.RemoveAll();
			m_aFieldNames.RemoveAll();
			m_dTotalColumns = 0;
			m_dTotalRows = 0;
		}
		catch(CDBException *e)
		{
			m_sLastError = e->m_strError;
			m_Database->Close();
			return false;
		}
		return true;
	}
	else // if file is a text delimited file
	{
		return DeleteSheet();
	}
}

// Insert or replace a row into spreadsheet. 
// Default is add new row.
bool CSpreadSheet::AddRow(CStringArray &RowValues, long row, bool replace)
{
	long tempRow;
	
	if (row == 1)
	{
		if (m_bExcel) 
		{
			// Check for duplicate header row field for Excel spreadsheet
			for (int i = 0; i < RowValues.GetSize(); i++)
			{
				for (int j = 0; j < RowValues.GetSize(); j++)
				{
					if ((i != j) && (RowValues.GetAt(i) == RowValues.GetAt(j)))
					{
						m_sLastError.Format("Duplicate header row field:%s\n", RowValues.GetAt(i));
						return false;
					}
				}
			}
			
			// Check for reduced header row columns
			if (RowValues.GetSize() < m_dTotalColumns)
			{
				m_sLastError = "Number of columns in new header row cannot be less than the number of columns in previous header row";
				return false;
			}
			m_dTotalColumns = RowValues.GetSize();
		}

		// Update header row
		m_aFieldNames.RemoveAll();
		m_aFieldNames.Copy(RowValues);
	}
	else
	{
		if (m_bExcel)
		{
			if (m_dTotalColumns == 0)
			{
				m_sLastError = "No header row. Add header row first\n";
				return false;
			}
		}
	}

	if (m_bExcel) // For Excel spreadsheet
	{
		if (RowValues.GetSize() > m_aFieldNames.GetSize())
		{
			m_sLastError = "Number of columns to be added cannot be greater than the number of fields\n";
			return false;
		}
	}
	else // For text delimited spreadsheet
	{
		// Update largest number of columns if necessary
		if (RowValues.GetSize() > m_dTotalColumns)
		{
			m_dTotalColumns = RowValues.GetSize();
		}
	}

	// Convert row values
	m_stempString.Empty();
	for (int i = 0; i < RowValues.GetSize(); i++)
	{
		if (i != RowValues.GetSize()-1) // Not last column
		{
			m_stempSql.Format("\"%s\"%s", RowValues.GetAt(i), m_sSeparator);
			m_stempString += m_stempSql;
		}
		else // Last column
		{
			m_stempSql.Format("\"%s\"", RowValues.GetAt(i));
			m_stempString += m_stempSql;
		}
	}
	
	if (row)
	{
		if (row <= m_dTotalRows) // Not adding new rows
		{
			if (replace) // Replacing row
			{
				m_aRows.SetAt(row-1, m_stempString);
			}
			else // Inserting row
			{
				m_aRows.InsertAt(row-1, m_stempString);
				m_dTotalRows++;
			}

			if (!m_bTransaction)
			{
				Commit();
			}
			return true;
		}
		else // Adding new rows
		{
			// Insert null rows until specified row
			m_dCurrentRow = m_dTotalRows;
			m_stempSql.Empty();
			CString nullString;
			for (int i = 1; i <= m_dTotalColumns; i++)
			{
				if (i != m_dTotalColumns)
				{
					if (m_bExcel)
					{
						nullString.Format("\" \"%s", m_sSeparator);
					}
					else
					{
						nullString.Format("\"\"%s", m_sSeparator);
					}
					m_stempSql += nullString;
				}
				else
				{
					if (m_bExcel)
					{
						m_stempSql += "\" \"";
					}
					else
					{
						m_stempSql += "\"\"";
					}
				}
			}
			for (int j = m_dTotalRows + 1; j < row; j++)
			{
				m_dCurrentRow++;
				m_aRows.Add(m_stempSql);
			}
		}
	}
	else
	{
		tempRow = m_dCurrentRow;
		m_dCurrentRow = m_dTotalRows;
	}

	// Insert new row
	m_dCurrentRow++;
	m_aRows.Add(m_stempString);
	
	if (row > m_dTotalRows)
	{
		m_dTotalRows = row;
	}
	else if (!row)
	{
		m_dTotalRows = m_dCurrentRow;
		m_dCurrentRow = tempRow;
	}
	if (!m_bTransaction)
	{
		Commit();
	}
	return true;
}

// Replace or add a cell into Excel spreadsheet using header row or column alphabet. 
// Default is add cell into new row.
// Set Auto to false if want to force column to be used as header name
bool CSpreadSheet::AddCell(CString CellValue, CString column, long row, bool Auto)
{
	short columnIndex = CalculateColumnNumber(column, Auto);
	if (columnIndex == 0)