CSpreadSheet类学习，用于操作excel表格[转]

最新推荐文章于 2024-04-21 23:42:10 发布

sai19841003

最新推荐文章于 2024-04-21 23:42:10 发布

阅读量2.7k

点赞数

文章标签： excel spreadsheet header file delete insert

http://blog.csdn.net/xautfengzi/archive/2009/09/03/4516101.aspx

废话不多说，先上代码。

view plaincopy to clipboardprint?
// 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
};

#endif

#include "CSpreadSheet.h"

// 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)
 {
 return false;
 }

 if (AddCell(CellValue, columnIndex, row))
 {
 return true;
 }
 return false;
}

// Replace or add a cell into spreadsheet using column number
// Default is add cell into new row.
bool CSpreadSheet::AddCell(CString CellValue, short column, long row)
{
 if (column == 0)
 {
 m_sLastError = "Column cannot be zero/n";
 return false;
 }

 long tempRow;

 if (m_bExcel) // For Excel spreadsheet
 {
 if (column > m_aFieldNames.GetSize() + 1)
 {
 m_sLastError = "Cell column 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 (column > m_dTotalColumns)
 {
 m_dTotalColumns = column;
 }
 }

 if (row)
 {
 if (row <= m_dTotalRows)
 {
 ReadRow(m_atempArray, row);

 // Change desired row
 m_atempArray.SetAtGrow(column-1, CellValue);

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

 // Update header row
 m_aFieldNames.RemoveAll();
 m_aFieldNames.Copy(m_atempArray);
 }

 if (!AddRow(m_atempArray, row, true))
 {
 return false;
 }

 if (!m_bTransaction)
 {
 Commit();
 }
 return true;
 }
 else
 {
 // 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 cell
 m_dCurrentRow++;
 m_stempString.Empty();
 for (int j = 1; j <= m_dTotalColumns; j++)
 {
 if (j != m_dTotalColumns) // Not last column
 {
 if (j != column)
 {
 if (m_bExcel)
 {
 m_stempSql.Format("/" /"%s", m_sSeparator);
 }
 else
 {
 m_stempSql.Format("/"/"%s", m_sSeparator);
 }
 m_stempString += m_stempSql;
 }
 else
 {
 m_stempSql.Format("/"%s/"%s", CellValue, m_sSeparator);
 m_stempString += m_stempSql;
 }
 }
 else // Last column
 {
 if (j != column)
 {
 if (m_bExcel)
 {
 m_stempString += "/" /"";
 }
 else
 {
 m_stempString += "/"/"";
 }
 }
 else
 {
 m_stempSql.Format("/"%s/"", CellValue);
 m_stempString += m_stempSql;
 }
 }
 }

 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;
}

// Search and replace rows in Excel spreadsheet
bool CSpreadSheet::ReplaceRows(CStringArray &NewRowValues, CStringArray &OldRowValues)
{
 if (m_bExcel) // If file is an Excel spreadsheet
 {
 m_Database->OpenEx(m_sDsn, CDatabase::noOdbcDialog);
 m_stempSql.Format("UPDATE [%s] SET ", m_sSheetName);
 for (int i = 0; i < NewRowValues.GetSize(); i++)
 {
 m_stempString.Format("[%s]='%s', ", m_aFieldNames.GetAt(i), NewRowValues.GetAt(i));
 m_stempSql = m_stempSql + m_stempString;
 }
 m_stempSql.Delete(m_stempSql.GetLength()-2, 2);
 m_stempSql = m_stempSql + " WHERE (";
 for (int j = 0; j < OldRowValues.GetSize()-1; j++)
 {
 m_stempString.Format("[%s]='%s' AND ", m_aFieldNames.GetAt(j), OldRowValues.GetAt(j));
 m_stempSql = m_stempSql + m_stempString;
 }
 m_stempSql.Delete(m_stempSql.GetLength()-4, 5);
 m_stempSql += ")";

 try
 {
 m_Database->ExecuteSQL(m_stempSql);
 m_Database->Close();
 Open();
 return true;
 }
 catch(CDBException *e)
 {
 m_sLastError = e->m_strError;
 m_Database->Close();
 return false;
 }
 }
 else // if file is a text delimited file
 {
 m_sLastError = "Function not available for text delimited file/n";
 return false;
 }
}

// Read a row from spreadsheet.
// Default is read the next row
bool CSpreadSheet::ReadRow(CStringArray &RowValues, long row)
{
 // Check if row entered is more than number of rows in sheet
 if (row <= m_aRows.GetSize())
 {
 if (row != 0)
 {
 m_dCurrentRow = row;
 }
 else if (m_dCurrentRow > m_aRows.GetSize())
 {
 return false;
 }
 // Read the desired row
 RowValues.RemoveAll();
 m_stempString = m_aRows.GetAt(m_dCurrentRow-1);
 m_dCurrentRow++;

 // Search for separator to split row
 int separatorPosition;
 m_stempSql.Format("/"%s/"", m_sSeparator);
 separatorPosition = m_stempString.Find(m_stempSql); // If separator is "?"
 if (separatorPosition != -1)
 {
 // Save columns
 int nCount = 0;
 int stringStartingPosition = 0;
 while (separatorPosition != -1)
 {
 nCount = separatorPosition - stringStartingPosition;
 RowValues.Add(m_stempString.Mid(stringStartingPosition, nCount));
 stringStartingPosition = separatorPosition + m_stempSql.GetLength();
 separatorPosition = m_stempString.Find(m_stempSql, stringStartingPosition);
 }
 nCount = m_stempString.GetLength() - stringStartingPosition;
 RowValues.Add(m_stempString.Mid(stringStartingPosition, nCount));

 // Remove quotes from first column
 m_stempString = RowValues.GetAt(0);
 m_stempString.Delete(0, 1);
 RowValues.SetAt(0, m_stempString);

 // Remove quotes from last column
 m_stempString = RowValues.GetAt(RowValues.GetSize()-1);
 m_stempString.Delete(m_stempString.GetLength()-1, 1);
 RowValues.SetAt(RowValues.GetSize()-1, m_stempString);

 return true;
 }
 else
 {
 // Save columns
 separatorPosition = m_stempString.Find(m_sSeparator); // if separator is ?
 if (separatorPosition != -1)
 {
 int nCount = 0;
 int stringStartingPosition = 0;
 while (separatorPosition != -1)
 {
 nCount = separatorPosition - stringStartingPosition;
 RowValues.Add(m_stempString.Mid(stringStartingPosition, nCount));
 stringStartingPosition = separatorPosition + m_sSeparator.GetLength();
 separatorPosition = m_stempString.Find(m_sSeparator, stringStartingPosition);
 }
 nCount = m_stempString.GetLength() - stringStartingPosition;
 RowValues.Add(m_stempString.Mid(stringStartingPosition, nCount));
 return true;
 }
 else // Treat spreadsheet as having one column
 {
 // Remove opening and ending quotes if any
 int quoteBegPos = m_stempString.Find('/"');
 int quoteEndPos = m_stempString.ReverseFind('/"');
 if ((quoteBegPos == 0) && (quoteEndPos == m_stempString.GetLength()-1))
 {
 m_stempString.Delete(0, 1);
 m_stempString.Delete(m_stempString.GetLength()-1, 1);
 }

 RowValues.Add(m_stempString);
 }
 }
 }
 m_sLastError = "Desired row is greater than total number of rows in spreadsheet/n";
 return false;
}

// 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 CSpreadSheet::ReadColumn(CStringArray &ColumnValues, CString column, bool Auto)
{
 short columnIndex = CalculateColumnNumber(column, Auto);
 if (columnIndex == 0)
 {
 return false;
 }

 if (ReadColumn(ColumnValues, columnIndex))
 {
 return true;
 }
 return false;
}

// Read a column from spreadsheet using column number
bool CSpreadSheet::ReadColumn(CStringArray &ColumnValues, short column)
{
 if (column == 0)
 {
 m_sLastError = "Column cannot be zero/n";
 return false;
 }

 int tempRow = m_dCurrentRow;
 m_dCurrentRow = 1;
 ColumnValues.RemoveAll();
 for (int i = 1; i <= m_aRows.GetSize(); i++)
 {
 // Read each row
 if (ReadRow(m_atempArray, i))
 {
 // Get value of cell in desired column
 if (column <= m_atempArray.GetSize())
 {
 ColumnValues.Add(m_atempArray.GetAt(column-1));
 }
 else
 {
 ColumnValues.Add("");
 }
 }
 else
 {
 m_dCurrentRow = tempRow;
 m_sLastError = "Error reading row/n";
 return false;
 }
 }
 m_dCurrentRow = tempRow;
 return 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 CSpreadSheet::ReadCell (CString &CellValue, CString column, long row, bool Auto)
{
 short columnIndex = CalculateColumnNumber(column, Auto);
 if (columnIndex == 0)
 {
 return false;
 }

 if (ReadCell(CellValue, columnIndex, row))
 {
 return true;
 }
 return false;
}

// Read a cell from spreadsheet using column number.
// Default is read the next cell in next row.
bool CSpreadSheet::ReadCell (CString &CellValue, short column, long row)
{
 if (column == 0)
 {
 m_sLastError = "Column cannot be zero/n";
 return false;
 }

 int tempRow = m_dCurrentRow;
 if (row)
 {
 m_dCurrentRow = row;
 }
 if (ReadRow(m_atempArray, m_dCurrentRow))
 {
 // Get value of cell in desired column
 if (column <= m_atempArray.GetSize())
 {
 CellValue = m_atempArray.GetAt(column-1);
 }
 else
 {
 CellValue.Empty();
 m_dCurrentRow = tempRow;
 return false;
 }
 m_dCurrentRow = tempRow;
 return true;
 }
 m_dCurrentRow = tempRow;
 m_sLastError = "Error reading row/n";
 return false;
}

// Begin transaction
void CSpreadSheet::BeginTransaction()
{
 m_bTransaction = true;
}

// Save changes to spreadsheet
bool CSpreadSheet::Commit()
{
 if (m_bExcel) // If file is an Excel spreadsheet
 {
 m_Database->OpenEx(m_sDsn, CDatabase::noOdbcDialog);

 if (m_bAppend)
 {
 // Delete old sheet if it exists
 m_stempString= "[" + m_sSheetName + "$A1:IV65536]";
 m_stempSql.Format ("DROP TABLE %s", m_stempString);
 try
 {
 m_Database->ExecuteSQL(m_stempSql);
 }
 catch(CDBException *e)
 {
 m_sLastError = e->m_strError;
 m_Database->Close();
 return false;
 }

 // Create new sheet
 m_stempSql.Format("CREATE TABLE [%s$A1:IV65536] (", m_sSheetName);
 for (int j = 0; j < m_aFieldNames.GetSize(); j++)
 {
 m_stempSql = m_stempSql + "[" + m_aFieldNames.GetAt(j) +"]" + " char(255), ";
 }
 m_stempSql.Delete(m_stempSql.GetLength()-2, 2);
 m_stempSql += ")";
 }
 else
 {
 // Create new sheet
 m_stempSql.Format("CREATE TABLE [%s] (", m_sSheetName);
 for (int i = 0; i < m_aFieldNames.GetSize(); i++)
 {
 m_stempSql = m_stempSql + "[" + m_aFieldNames.GetAt(i) +"]" + " char(255), ";
 }
 m_stempSql.Delete(m_stempSql.GetLength()-2, 2);
 m_stempSql += ")";
 }

 try
 {
 m_Database->ExecuteSQL(m_stempSql);
 if (!m_bAppend)
 {
 m_dTotalColumns = m_aFieldNames.GetSize();
 m_bAppend = true;
 }
 }
 catch(CDBException *e)
 {
 m_sLastError = e->m_strError;
 m_Database->Close();
 return false;
 }

 // Save changed data
 for (int k = 1; k < m_dTotalRows; k++)
 {
 ReadRow(m_atempArray, k+1);

 // Create Insert SQL
 m_stempSql.Format("INSERT INTO [%s$A1:IV%d] (", m_sSheetName, k);
 for (int i = 0; i < m_atempArray.GetSize(); i++)
 {
 m_stempString.Format("[%s], ", m_aFieldNames.GetAt(i));
 m_stempSql = m_stempSql + m_stempString;
 }
 m_stempSql.Delete(m_stempSql.GetLength()-2, 2);
 m_stempSql += ") VALUES (";
 for (int j = 0; j < m_atempArray.GetSize(); j++)
 {
 m_stempString.Format("'%s', ", m_atempArray.GetAt(j));
 m_stempSql = m_stempSql + m_stempString;
 }
 m_stempSql.Delete(m_stempSql.GetLength()-2, 2);
 m_stempSql += ")";

 // Add row
 try
 {
 m_Database->ExecuteSQL(m_stempSql);
 }
 catch(CDBException *e)
 {
 m_sLastError = e->m_strError;
 m_Database->Close();
 return false;
 }
 }
 m_Database->Close();
 m_bTransaction = false;
 return true;
 }
 else // if file is a text delimited file
 {
 try
 {
 CFile *File = NULL;
 File = new CFile(m_sFile, CFile::modeCreate | CFile::modeWrite | CFile::shareDenyNone);
 if (File != NULL)
 {
 CArchive *Archive = NULL;
 Archive = new CArchive(File, CArchive::store);
 if (Archive != NULL)
 {
 for (int i = 0; i < m_aRows.GetSize(); i++)
 {
 Archive->WriteString(m_aRows.GetAt(i));
 Archive->WriteString("/r/n");
 }
 delete Archive;
 delete File;
 m_bTransaction = false;
 return true;
 }
 delete File;
 }
 }
 catch(...)
 {
 }
 m_sLastError = "Error writing file/n";
 return false;
 }
}

// Undo changes to spreadsheet
bool CSpreadSheet::RollBack()
{
 if (Open())
 {
 m_bTransaction = false;
 return true;
 }
 m_sLastError = "Error in returning to previous state/n";
 return false;
}

bool CSpreadSheet::Convert(CString SheetOrSeparator)
{
 // Prepare file
 m_stempString = m_sFile;
 m_stempString.Delete(m_stempString.GetLength()-4, 4);
 if (m_bExcel) // If file is an Excel spreadsheet
 {
 m_stempString += ".csv";
 CSpreadSheet tempSheet(m_stempString, SheetOrSeparator, false);

 // Stop convert if text delimited file exists
 if (tempSheet.GetTotalColumns() != 0)
 {
 return false;
 }

 tempSheet.BeginTransaction();

 for (int i = 1; i <= m_dTotalRows; i++)
 {
 if (!ReadRow(m_atempArray, i))
 {
 return false;
 }
 if (!tempSheet.AddRow(m_atempArray, i))
 {
 return false;
 }
 }
 if (!tempSheet.Commit())
 {
 return false;
 }
 return true;
 }
 else // if file is a text delimited file
 {
 m_stempString += ".xls";
 CSpreadSheet tempSheet(m_stempString, SheetOrSeparator, false);

 // Stop convert if Excel file exists
 if (tempSheet.GetTotalColumns() != 0)
 {
 return false;
 }

 GetFieldNames(m_atempArray);

 // Check for duplicate header row field
 bool duplicate = false;
 for (int i = 0; i < m_atempArray.GetSize(); i++)
 {
 for (int j = 0; j < m_atempArray.GetSize(); j++)
 {
 if ((i != j) && (m_atempArray.GetAt(i) == m_atempArray.GetAt(j)))
 {
 m_sLastError.Format("Duplicate header row field:%s/n", m_atempArray.GetAt(i));
 duplicate = true;
 }
 }
 }

 if (duplicate) // Create dummy header row
 {
 m_atempArray.RemoveAll();
 for (int k = 1; k <= m_dTotalColumns; k++)
 {
 m_stempString.Format("%d", k);
 m_atempArray.Add(m_stempString);
 }

 if (!tempSheet.AddHeaders(m_atempArray))
 {
 return false;
 }

 for (int l = 1; l <= m_dTotalRows; l++)
 {
 if (!ReadRow(m_atempArray, l))
 {
 return false;
 }
 if (!tempSheet.AddRow(m_atempArray, l+1))
 {
 return false;
 }
 }
 return true;
 }
 else
 {
 if (!tempSheet.AddHeaders(m_atempArray))
 {
 return false;
 }

 for (int l = 2; l <= m_dTotalRows; l++)
 {
 if (!ReadRow(m_atempArray, l))
 {
 return false;
 }
 if (!tempSheet.AddRow(m_atempArray, l))
 {
 return false;
 }
 }
 return true;
 }
 }
}

// Open a text delimited file for reading or writing
bool CSpreadSheet::Open()
{
 if (m_bExcel) // If file is an Excel spreadsheet
 {
 m_Database->OpenEx(m_sDsn, CDatabase::noOdbcDialog);

 // Open Sheet
 m_rSheet = new CRecordset( m_Database );
 m_sSql.Format("SELECT * FROM [%s$A1:IV65536]", m_sSheetName);
 try
 {
 m_rSheet->Open(CRecordset::forwardOnly, m_sSql, CRecordset::readOnly);
 }
 catch(...)
 {
 delete m_rSheet;
 m_rSheet = NULL;
 m_Database->Close();
 return false;
 }

 // Get number of columns
 m_dTotalColumns = m_rSheet->m_nResultCols;

 if (m_dTotalColumns != 0)
 {
 m_aRows.RemoveAll();
 m_stempString.Empty();
 m_bAppend = true;
 m_dTotalRows++; // Keep count of total number of rows

 // Get field names i.e header row
 for (int i = 0; i < m_dTotalColumns; i++)
 {
 m_stempSql = m_rSheet->m_rgODBCFieldInfos[i].m_strName;
 m_aFieldNames.Add(m_stempSql);

 // Join up all the columns into a string
 if (i != m_dTotalColumns-1) // Not last column
 {
 m_stempString = m_stempString + "/"" + m_stempSql + "/"" + m_sSeparator;
 }
 else // Last column
 {
 m_stempString = m_stempString + "/"" + m_stempSql + "/"";
 }
 }

 // Store the header row as the first row in memory
 m_aRows.Add(m_stempString);

 // Read and store the rest of the rows in memory
 while (!m_rSheet->IsEOF())
 {
 m_dTotalRows++; // Keep count of total number of rows
 try
 {
 // Get all the columns in a row
 m_stempString.Empty();
 for (short column = 0; column < m_dTotalColumns; column++)
 {
 m_rSheet->GetFieldValue(column, m_stempSql);

 // Join up all the columns into a string
 if (column != m_dTotalColumns-1) // Not last column
 {
 m_stempString = m_stempString + "/"" + m_stempSql + "/"" + m_sSeparator;
 }
 else // Last column
 {
 m_stempString = m_stempString + "/"" + m_stempSql + "/"";
 }
 }

 // Store the obtained row in memory
 m_aRows.Add(m_stempString);
 m_rSheet->MoveNext();
 }
 catch (...)
 {
 m_sLastError = "Error reading row/n";
 delete m_rSheet;
 m_rSheet = NULL;
 m_Database->Close();
 return false;
 }
 }
 }

 m_rSheet->Close();
 delete m_rSheet;
 m_rSheet = NULL;
 m_Database->Close();
 m_dCurrentRow = 1;
 return true;
 }
 else // if file is a text delimited file
 {
 try
 {
 CFile *File = NULL;
 File = new CFile(m_sFile, CFile::modeRead | CFile::shareDenyNone);
 if (File != NULL)
 {
 CArchive *Archive = NULL;
 Archive = new CArchive(File, CArchive::load);
 if (Archive != NULL)
 {
 m_aRows.RemoveAll();
 // Read and store all rows in memory
 while(Archive->ReadString(m_stempString))
 {
 m_aRows.Add(m_stempString);
 }
 ReadRow(m_aFieldNames, 1); // Get field names i.e header row
 delete Archive;
 delete File;

 // Get total number of rows
 m_dTotalRows = m_aRows.GetSize();

 // Get the largest number of columns
 for (int i = 0; i < m_aRows.GetSize(); i++)
 {
 ReadRow(m_atempArray, i);
 if (m_atempArray.GetSize() > m_dTotalColumns)
 {
 m_dTotalColumns = m_atempArray.GetSize();
 }
 }

 if (m_dTotalColumns != 0)
 {
 m_bAppend = true;
 }
 return true;
 }
 delete File;
 }
 }
 catch(...)
 {
 }
 m_sLastError = "Error in opening file/n";
 return false;
 }
}

// Convert Excel column in alphabet into column number
short CSpreadSheet::CalculateColumnNumber(CString column, bool Auto)
{
 if (Auto)
 {
 int firstLetter, secondLetter;
 column.MakeUpper();

 if (column.GetLength() == 1)
 {
 firstLetter = column.GetAt(0);
 return (firstLetter - 65 + 1); // 65 is A in ascii
 }
 else if (column.GetLength() == 2)
 {
 firstLetter = column.GetAt(0);
 secondLetter = column.GetAt(1);
 return ((firstLetter - 65 + 1)*26 + (secondLetter - 65 + 1)); // 65 is A in ascii
 }
 }

 // Check if it is a valid field name
 for (int i = 0; i < m_aFieldNames.GetSize(); i++)
 {
 if (!column.Compare(m_aFieldNames.GetAt(i)))
 {
 return (i + 1);
 }
 }
 m_sLastError = "Invalid field name or column alphabet/n";
 return 0;
}

// Get the name of the Excel-ODBC driver
void CSpreadSheet::GetExcelDriver()
{
 char szBuf[2001];
 WORD cbBufMax = 2000;
 WORD cbBufOut;
 char *pszBuf = szBuf;

 // Get the names of the installed drivers ("odbcinst.h" has to be included )
 if(!SQLGetInstalledDrivers(szBuf,cbBufMax,& cbBufOut))
 {
 m_sExcelDriver = "";
 }

 // Search for the driver...
 do
 {
 if( strstr( pszBuf, "Excel" ) != 0 )
 {
 // Found !
 m_sExcelDriver = CString( pszBuf );
 break;
 }
 pszBuf = strchr( pszBuf, '/0' ) + 1;
 }
 while( pszBuf[1] != '/0' );
}
// 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
};

#endif

#include "CSpreadSheet.h"

// 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)
{
return false;
}

if (AddCell(CellValue, columnIndex, row))
{
return true;
}
return false;
}

// Replace or add a cell into spreadsheet using column number
// Default is add cell into new row.
bool CSpreadSheet::AddCell(CString CellValue, short column, long row)
{
if (column == 0)
{
m_sLastError = "Column cannot be zero/n";
return false;
}

long tempRow;

if (m_bExcel) // For Excel spreadsheet
{
  if (column > m_aFieldNames.GetSize() + 1)
  {
   m_sLastError = "Cell column 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 (column > m_dTotalColumns)
  {
   m_dTotalColumns = column;
  }
}

if (row)
{
 if (row <= m_dTotalRows)
 {
 ReadRow(m_atempArray, row);

 // Change desired row
 m_atempArray.SetAtGrow(column-1, CellValue);

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

    // Update header row
    m_aFieldNames.RemoveAll();
    m_aFieldNames.Copy(m_atempArray);
   }

   if (!AddRow(m_atempArray, row, true))
   {
    return false;
   }

if (!m_bTransaction)
 {
 Commit();
 }
 return true;
 }
 else
 {
 // 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 cell
m_dCurrentRow++;
m_stempString.Empty();
for (int j = 1; j <= m_dTotalColumns; j++)
{
 if (j != m_dTotalColumns) // Not last column
 {
 if (j != column)
 {
 if (m_bExcel)
 {
 m_stempSql.Format("/" /"%s", m_sSeparator);
 }
 else
 {
 m_stempSql.Format("/"/"%s", m_sSeparator);
 }
 m_stempString += m_stempSql;
 }
 else
 {
 m_stempSql.Format("/"%s/"%s", CellValue, m_sSeparator);
 m_stempString += m_stempSql;
 }
 }
 else // Last column
 {
 if (j != column)
 {
 if (m_bExcel)
 {
 m_stempString += "/" /"";
 }
 else
 {
 m_stempString += "/"/"";
 }
 }
 else
 {
 m_stempSql.Format("/"%s/"", CellValue);
 m_stempString += m_stempSql;
 }
 }
}

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;
}

// Search and replace rows in Excel spreadsheet
bool CSpreadSheet::ReplaceRows(CStringArray &NewRowValues, CStringArray &OldRowValues)
{
if (m_bExcel) // If file is an Excel spreadsheet
{
 m_Database->OpenEx(m_sDsn, CDatabase::noOdbcDialog);
 m_stempSql.Format("UPDATE [%s] SET ", m_sSheetName);
 for (int i = 0; i < NewRowValues.GetSize(); i++)
 {
 m_stempString.Format("[%s]='%s', ", m_aFieldNames.GetAt(i), NewRowValues.GetAt(i));
 m_stempSql = m_stempSql + m_stempString;
 }
 m_stempSql.Delete(m_stempSql.GetLength()-2, 2);
 m_stempSql = m_stempSql + " WHERE (";
 for (int j = 0; j < OldRowValues.GetSize()-1; j++)
 {
 m_stempString.Format("[%s]='%s' AND ", m_aFieldNames.GetAt(j), OldRowValues.GetAt(j));
 m_stempSql = m_stempSql + m_stempString;
 }
 m_stempSql.Delete(m_stempSql.GetLength()-4, 5);
 m_stempSql += ")";

  try
  {
   m_Database->ExecuteSQL(m_stempSql);
   m_Database->Close();
   Open();
   return true;
  }
  catch(CDBException *e)
  {
   m_sLastError = e->m_strError;
   m_Database->Close();
   return false;
  }
}
else // if file is a text delimited file
{
  m_sLastError = "Function not available for text delimited file/n";
  return false;
}
}

// Read a row from spreadsheet.
// Default is read the next row
bool CSpreadSheet::ReadRow(CStringArray &RowValues, long row)
{
// Check if row entered is more than number of rows in sheet
if (row <= m_aRows.GetSize())
{
 if (row != 0)
 {
 m_dCurrentRow = row;
 }
 else if (m_dCurrentRow > m_aRows.GetSize())
 {
 return false;
 }
 // Read the desired row
 RowValues.RemoveAll();
 m_stempString = m_aRows.GetAt(m_dCurrentRow-1);
 m_dCurrentRow++;

  // Search for separator to split row
  int separatorPosition;
  m_stempSql.Format("/"%s/"", m_sSeparator);
  separatorPosition = m_stempString.Find(m_stempSql); // If separator is "?"
  if (separatorPosition != -1)
  {
   // Save columns
   int nCount = 0;
   int stringStartingPosition = 0;
   while (separatorPosition != -1)
   {
    nCount = separatorPosition - stringStartingPosition;
    RowValues.Add(m_stempString.Mid(stringStartingPosition, nCount));
    stringStartingPosition = separatorPosition + m_stempSql.GetLength();
    separatorPosition = m_stempString.Find(m_stempSql, stringStartingPosition);
   }
   nCount = m_stempString.GetLength() - stringStartingPosition;
   RowValues.Add(m_stempString.Mid(stringStartingPosition, nCount));

   // Remove quotes from first column
   m_stempString = RowValues.GetAt(0);
   m_stempString.Delete(0, 1);
   RowValues.SetAt(0, m_stempString);

   // Remove quotes from last column
   m_stempString = RowValues.GetAt(RowValues.GetSize()-1);
   m_stempString.Delete(m_stempString.GetLength()-1, 1);
   RowValues.SetAt(RowValues.GetSize()-1, m_stempString);

   return true;
  }
  else
  {
   // Save columns
   separatorPosition = m_stempString.Find(m_sSeparator); // if separator is ?
   if (separatorPosition != -1)
   {
    int nCount = 0;
    int stringStartingPosition = 0;
    while (separatorPosition != -1)
    {
     nCount = separatorPosition - stringStartingPosition;
     RowValues.Add(m_stempString.Mid(stringStartingPosition, nCount));
     stringStartingPosition = separatorPosition + m_sSeparator.GetLength();
     separatorPosition = m_stempString.Find(m_sSeparator, stringStartingPosition);
    }
    nCount = m_stempString.GetLength() - stringStartingPosition;
    RowValues.Add(m_stempString.Mid(stringStartingPosition, nCount));
    return true;
   }
   else // Treat spreadsheet as having one column
   {
    // Remove opening and ending quotes if any
    int quoteBegPos = m_stempString.Find('/"');
    int quoteEndPos = m_stempString.ReverseFind('/"');
    if ((quoteBegPos == 0) && (quoteEndPos == m_stempString.GetLength()-1))
    {
     m_stempString.Delete(0, 1);
     m_stempString.Delete(m_stempString.GetLength()-1, 1);
    }

    RowValues.Add(m_stempString);
   }
  }
}
m_sLastError = "Desired row is greater than total number of rows in spreadsheet/n";
return false;
}

// 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 CSpreadSheet::ReadColumn(CStringArray &ColumnValues, CString column, bool Auto)
{
short columnIndex = CalculateColumnNumber(column, Auto);
if (columnIndex == 0)
{
return false;
}

if (ReadColumn(ColumnValues, columnIndex))
{
return true;
}
return false;
}

// Read a column from spreadsheet using column number
bool CSpreadSheet::ReadColumn(CStringArray &ColumnValues, short column)
{
if (column == 0)
{
m_sLastError = "Column cannot be zero/n";
return false;
}

int tempRow = m_dCurrentRow;
m_dCurrentRow = 1;
ColumnValues.RemoveAll();
for (int i = 1; i <= m_aRows.GetSize(); i++)
{
 // Read each row
 if (ReadRow(m_atempArray, i))
 {
 // Get value of cell in desired column
 if (column <= m_atempArray.GetSize())
 {
 ColumnValues.Add(m_atempArray.GetAt(column-1));
 }
 else
 {
 ColumnValues.Add("");
 }
 }
 else
 {
 m_dCurrentRow = tempRow;
 m_sLastError = "Error reading row/n";
 return false;
 }
}
m_dCurrentRow = tempRow;
return 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 CSpreadSheet::ReadCell (CString &CellValue, CString column, long row, bool Auto)
{
short columnIndex = CalculateColumnNumber(column, Auto);
if (columnIndex == 0)
{
return false;
}

if (ReadCell(CellValue, columnIndex, row))
{
return true;
}
return false;
}

// Read a cell from spreadsheet using column number.
// Default is read the next cell in next row.
bool CSpreadSheet::ReadCell (CString &CellValue, short column, long row)
{
if (column == 0)
{
m_sLastError = "Column cannot be zero/n";
return false;
}

int tempRow = m_dCurrentRow;
if (row)
{
 m_dCurrentRow = row;
}
if (ReadRow(m_atempArray, m_dCurrentRow))
{
 // Get value of cell in desired column
 if (column <= m_atempArray.GetSize())
 {
 CellValue = m_atempArray.GetAt(column-1);
 }
 else
 {
 CellValue.Empty();
 m_dCurrentRow = tempRow;
 return false;
 }
 m_dCurrentRow = tempRow;
 return true;
}
m_dCurrentRow = tempRow;
m_sLastError = "Error reading row/n";
return false;
}

// Begin transaction
void CSpreadSheet::BeginTransaction()
{
m_bTransaction = true;
}

// Save changes to spreadsheet
bool CSpreadSheet::Commit()
{
if (m_bExcel) // If file is an Excel spreadsheet
{
m_Database->OpenEx(m_sDsn, CDatabase::noOdbcDialog);

if (m_bAppend)
 {
 // Delete old sheet if it exists
 m_stempString= "[" + m_sSheetName + "$A1:IV65536]";
 m_stempSql.Format ("DROP TABLE %s", m_stempString);
 try
 {
 m_Database->ExecuteSQL(m_stempSql);
 }
 catch(CDBException *e)
 {
 m_sLastError = e->m_strError;
 m_Database->Close();
 return false;
 }

 // Create new sheet
 m_stempSql.Format("CREATE TABLE [%s$A1:IV65536] (", m_sSheetName);
 for (int j = 0; j < m_aFieldNames.GetSize(); j++)
 {
 m_stempSql = m_stempSql + "[" + m_aFieldNames.GetAt(j) +"]" + " char(255), ";
 }
 m_stempSql.Delete(m_stempSql.GetLength()-2, 2);
 m_stempSql += ")";
 }
 else
 {
 // Create new sheet
 m_stempSql.Format("CREATE TABLE [%s] (", m_sSheetName);
 for (int i = 0; i < m_aFieldNames.GetSize(); i++)
 {
 m_stempSql = m_stempSql + "[" + m_aFieldNames.GetAt(i) +"]" + " char(255), ";
 }
 m_stempSql.Delete(m_stempSql.GetLength()-2, 2);
 m_stempSql += ")";
 }

  try
  {
   m_Database->ExecuteSQL(m_stempSql);
   if (!m_bAppend)
   {
    m_dTotalColumns = m_aFieldNames.GetSize();
    m_bAppend = true;
   }
  }
  catch(CDBException *e)
  {
   m_sLastError = e->m_strError;
   m_Database->Close();
   return false;
  }

// Save changed data
 for (int k = 1; k < m_dTotalRows; k++)
 {
 ReadRow(m_atempArray, k+1);

// Create Insert SQL
 m_stempSql.Format("INSERT INTO [%s$A1:IV%d] (", m_sSheetName, k);
 for (int i = 0; i < m_atempArray.GetSize(); i++)
 {
 m_stempString.Format("[%s], ", m_aFieldNames.GetAt(i));
 m_stempSql = m_stempSql + m_stempString;
 }
 m_stempSql.Delete(m_stempSql.GetLength()-2, 2);
 m_stempSql += ") VALUES (";
 for (int j = 0; j < m_atempArray.GetSize(); j++)
 {
 m_stempString.Format("'%s', ", m_atempArray.GetAt(j));
 m_stempSql = m_stempSql + m_stempString;
 }
 m_stempSql.Delete(m_stempSql.GetLength()-2, 2);
 m_stempSql += ")";

// Add row
 try
 {
 m_Database->ExecuteSQL(m_stempSql);
 }
 catch(CDBException *e)
 {
 m_sLastError = e->m_strError;
 m_Database->Close();
 return false;
 }
 }
 m_Database->Close();
 m_bTransaction = false;
 return true;
}
else // if file is a text delimited file
{
 try
 {
 CFile *File = NULL;
 File = new CFile(m_sFile, CFile::modeCreate | CFile::modeWrite | CFile::shareDenyNone);
 if (File != NULL)
 {
 CArchive *Archive = NULL;
 Archive = new CArchive(File, CArchive::store);
 if (Archive != NULL)
 {
 for (int i = 0; i < m_aRows.GetSize(); i++)
 {
 Archive->WriteString(m_aRows.GetAt(i));
 Archive->WriteString("/r/n");
 }
 delete Archive;
 delete File;
 m_bTransaction = false;
 return true;
 }
 delete File;
 }
 }
 catch(...)
 {
 }
 m_sLastError = "Error writing file/n";
 return false;
}
}

// Undo changes to spreadsheet
bool CSpreadSheet::RollBack()
{
if (Open())
{
m_bTransaction = false;
return true;
}
m_sLastError = "Error in returning to previous state/n";
return false;
}

bool CSpreadSheet::Convert(CString SheetOrSeparator)
{
// Prepare file
m_stempString = m_sFile;
m_stempString.Delete(m_stempString.GetLength()-4, 4);
if (m_bExcel) // If file is an Excel spreadsheet
{
  m_stempString += ".csv";
  CSpreadSheet tempSheet(m_stempString, SheetOrSeparator, false);

  // Stop convert if text delimited file exists
  if (tempSheet.GetTotalColumns() != 0)
  {
   return false;
  }

tempSheet.BeginTransaction();

for (int i = 1; i <= m_dTotalRows; i++)
 {
 if (!ReadRow(m_atempArray, i))
 {
 return false;
 }
 if (!tempSheet.AddRow(m_atempArray, i))
 {
 return false;
 }
 }
 if (!tempSheet.Commit())
 {
 return false;
 }
 return true;
}
else // if file is a text delimited file
{
 m_stempString += ".xls";
 CSpreadSheet tempSheet(m_stempString, SheetOrSeparator, false);

  // Stop convert if Excel file exists
  if (tempSheet.GetTotalColumns() != 0)
  {
   return false;
  }

GetFieldNames(m_atempArray);

// Check for duplicate header row field
 bool duplicate = false;
 for (int i = 0; i < m_atempArray.GetSize(); i++)
 {
 for (int j = 0; j < m_atempArray.GetSize(); j++)
 {
 if ((i != j) && (m_atempArray.GetAt(i) == m_atempArray.GetAt(j)))
 {
 m_sLastError.Format("Duplicate header row field:%s/n", m_atempArray.GetAt(i));
 duplicate = true;
 }
 }
 }

if (duplicate) // Create dummy header row
 {
 m_atempArray.RemoveAll();
 for (int k = 1; k <= m_dTotalColumns; k++)
 {
 m_stempString.Format("%d", k);
 m_atempArray.Add(m_stempString);
 }

   if (!tempSheet.AddHeaders(m_atempArray))
   {
    return false;
   }

for (int l = 1; l <= m_dTotalRows; l++)
 {
 if (!ReadRow(m_atempArray, l))
 {
 return false;
 }
 if (!tempSheet.AddRow(m_atempArray, l+1))
 {
 return false;
 }
 }
 return true;
 }
 else
 {
 if (!tempSheet.AddHeaders(m_atempArray))
 {
 return false;
 }

for (int l = 2; l <= m_dTotalRows; l++)
 {
 if (!ReadRow(m_atempArray, l))
 {
 return false;
 }
 if (!tempSheet.AddRow(m_atempArray, l))
 {
 return false;
 }
 }
 return true;
 }
}
}

// Open a text delimited file for reading or writing
bool CSpreadSheet::Open()
{
if (m_bExcel) // If file is an Excel spreadsheet
{
m_Database->OpenEx(m_sDsn, CDatabase::noOdbcDialog);

  // Open Sheet
  m_rSheet = new CRecordset( m_Database );
  m_sSql.Format("SELECT * FROM [%s$A1:IV65536]", m_sSheetName);
  try
  {
   m_rSheet->Open(CRecordset::forwardOnly, m_sSql, CRecordset::readOnly);
  }
  catch(...)
  {
   delete m_rSheet;
   m_rSheet = NULL;
   m_Database->Close();
   return false;
  }

// Get number of columns
m_dTotalColumns = m_rSheet->m_nResultCols;

if (m_dTotalColumns != 0)
 {
 m_aRows.RemoveAll();
 m_stempString.Empty();
 m_bAppend = true;
 m_dTotalRows++; // Keep count of total number of rows

 // Get field names i.e header row
 for (int i = 0; i < m_dTotalColumns; i++)
 {
 m_stempSql = m_rSheet->m_rgODBCFieldInfos[i].m_strName;
 m_aFieldNames.Add(m_stempSql);

    // Join up all the columns into a string
    if (i != m_dTotalColumns-1) // Not last column
    {
     m_stempString = m_stempString + "/"" + m_stempSql + "/"" + m_sSeparator;
    }
    else // Last column
    {
     m_stempString = m_stempString + "/"" + m_stempSql + "/"";
    }
   }

   // Store the header row as the first row in memory
   m_aRows.Add(m_stempString);

// Read and store the rest of the rows in memory
 while (!m_rSheet->IsEOF())
 {
 m_dTotalRows++; // Keep count of total number of rows
 try
 {
 // Get all the columns in a row
 m_stempString.Empty();
 for (short column = 0; column < m_dTotalColumns; column++)
 {
 m_rSheet->GetFieldValue(column, m_stempSql);

      // Join up all the columns into a string
      if (column != m_dTotalColumns-1) // Not last column
      {
       m_stempString = m_stempString + "/"" + m_stempSql + "/"" + m_sSeparator;
      }
      else // Last column
      {
       m_stempString = m_stempString + "/"" + m_stempSql + "/"";
      }
     }

     // Store the obtained row in memory
     m_aRows.Add(m_stempString);
     m_rSheet->MoveNext();
    }
    catch (...)
    {
     m_sLastError = "Error reading row/n";
     delete m_rSheet;
     m_rSheet = NULL;
     m_Database->Close();
     return false;
    }
   }
  }

  m_rSheet->Close();
  delete m_rSheet;
  m_rSheet = NULL;
  m_Database->Close();
  m_dCurrentRow = 1;
  return true;
}
else // if file is a text delimited file
{
  try
  {
   CFile *File = NULL;
   File = new CFile(m_sFile, CFile::modeRead | CFile::shareDenyNone);
   if (File != NULL)
   {
    CArchive *Archive = NULL;
    Archive = new CArchive(File, CArchive::load);
    if (Archive != NULL)
    {
     m_aRows.RemoveAll();
     // Read and store all rows in memory
     while(Archive->ReadString(m_stempString))
     {
      m_aRows.Add(m_stempString);
     }
     ReadRow(m_aFieldNames, 1); // Get field names i.e header row
     delete Archive;
     delete File;

// Get total number of rows
m_dTotalRows = m_aRows.GetSize();

// Get the largest number of columns
 for (int i = 0; i < m_aRows.GetSize(); i++)
 {
 ReadRow(m_atempArray, i);
 if (m_atempArray.GetSize() > m_dTotalColumns)
 {
 m_dTotalColumns = m_atempArray.GetSize();
 }
 }

     if (m_dTotalColumns != 0)
     {
      m_bAppend = true;
     }
     return true;
    }
    delete File;
   }
  }
  catch(...)
  {
  }
  m_sLastError = "Error in opening file/n";
  return false;
}
}

// Convert Excel column in alphabet into column number
short CSpreadSheet::CalculateColumnNumber(CString column, bool Auto)
{
if (Auto)
{
int firstLetter, secondLetter;
column.MakeUpper();

  if (column.GetLength() == 1)
  {
   firstLetter = column.GetAt(0);
   return (firstLetter - 65 + 1); // 65 is A in ascii
  }
  else if (column.GetLength() == 2)
  {
   firstLetter = column.GetAt(0);
   secondLetter = column.GetAt(1);
   return ((firstLetter - 65 + 1)*26 + (secondLetter - 65 + 1)); // 65 is A in ascii
  }
}

// Check if it is a valid field name
for (int i = 0; i < m_aFieldNames.GetSize(); i++)
{
 if (!column.Compare(m_aFieldNames.GetAt(i)))
 {
 return (i + 1);
 }
}
m_sLastError = "Invalid field name or column alphabet/n";
return 0;
}

// Get the name of the Excel-ODBC driver
void CSpreadSheet::GetExcelDriver()
{
char szBuf[2001];
WORD cbBufMax = 2000;
WORD cbBufOut;
char *pszBuf = szBuf;

// Get the names of the installed drivers ("odbcinst.h" has to be included )
if(!SQLGetInstalledDrivers(szBuf,cbBufMax,& cbBufOut))
{
  m_sExcelDriver = "";
}

// Search for the driver...
do
{
  if( strstr( pszBuf, "Excel" ) != 0 )
  {
   // Found !
   m_sExcelDriver = CString( pszBuf );
   break;
  }
  pszBuf = strchr( pszBuf, '/0' ) + 1;
}
while( pszBuf[1] != '/0' );
}

由于需要对excel表格进行操作，在网上找了这个类。用odbc来实现的，具体的使用步骤基本上如下：

CStringArray tempStrArray;

CSpreadSheet ss(FileName, "LABLE", FALSE);
ss.BeginTransaction();

tempStrArray.RemoveAll();

tempStrArray.Add("Item");
tempStrArray.Add("Mac Number");
tempStrArray.Add("RFPI Number");
tempStrArray.Add("Serial Number");
tempStrArray.Add("PCBA Serial Number");
ss.AddHeaders(tempStrArray);

tempStrArray.RemoveAll();
tempStrArray.Add(m_strBaseSerialNo);
tempStrArray.Add(m_strBaseSerialNo);
tempStrArray.Add(m_strBaseMacNo);
tempStrArray.Add(m_strHS_1_RN);
tempStrArray.Add(m_strHS_2_RN);
SS.AddRow(tempStrArray);
SS.Commit();

先添加表头，然后再添加行，具体的控制细节可以看各函数的实现代码。

网上资料说这个类用odbc来实现对excel的操作，速度慢。而且这个类会把所有的输入都当做为字符串来处理，所以生成的单元格的内容前面都被excel强制加了个单引号前缀"'"。这是它的缺点，但总的来说，我觉得这个类还是比较好用的,就看自己的需求了。

说说遇到并解决的几个问题：

1.就是要在头文件中包含预编译头文件

#include "stdafx.h"

这个依据具体的工程设置而定。

2.在初始化一个excel文件的时候，表名里面不能有数字，例如不能为BS-2482.要不然的话只能添加表头，不能添加新行。现在还不知道具体原因是什么，但是大概调试看到好像是提交SQL语句的时候应该对那些数字做一定的处理。

另外，再转一篇中文文档。

CSpreadSheet中文文档
http://www.codeproject.com/info/Licenses.aspx
简介
CSpreadSheet是一个C++编写的Excel读写控件，当我们希望输出Excel文件或以文
本文件分隔以Tab分隔的文件时, CSpreadSheet能使我们的工作事半功倍.该控件
能方便我们读写此类文件,以对象的形式供我们使用.

主要特征

创建Excel文件或文本特征文件.，写入多行或单行.读取多行，列，一行从Excel文件

或文本特征文件.替代、插入、追加到Excel文件或文本特征文件.转换已存在或最近

打开的Excel文件或文本特征文件.

限制
该控件需要MFC(微软基础类库)支持.未测试是否支持Unicode编码.控件以ODBC读写

Excel文件，需要ODBC驱动程序.Excel文件必须列标记.且首行列标记唯一(字段).禁止

删除工作簿，仅允许删除工作簿内容.列值类型参照程序数据类型.不采用Excel格式.

一,如何使用此类?

常用函数:
CSpreadSheet(CString File, CString SheetOrSeparator, bool Backup = true)
bool AddHeaders(CStringArray &FieldNames, bool replace = false)
bool AddRow(CStringArray &RowValues, long row = 0, bool replace = false)
bool AddCell(CString CellValue, short column, long row = 0)
bool AddCell(CString CellValue, CString column, long row = 0,bool Auto=true)
bool ReadRow(CStringArray &RowValues, long row = 0)
bool ReadColumn(CStringArray &ColumnValues, short column)
bool ReadColumn(CStringArray &ColumnValues, CString column,bool Auto = true)
bool ReadCell (CString &CellValue, short column, long row = 0)
bool ReadCell (CString &CellValue,CString column,long row=0,bool Auto=true)
bool DeleteSheet()
bool DeleteSheet(CString SheetName)
bool Convert(CString SheetOrSeparator)
void BeginTransaction()
bool Commit()
bool RollBack()
bool GetTransactionStatus()
void GetFieldNames (CStringArray &FieldNames)
long GetTotalRows()
short GetTotalColumns()
long GetCurrentRow()
bool GetBackupStatus()
CString GetLastError()

Excel特定函数:
bool ReplaceRows(CStringArray &NewRowValues, CStringArray &OldRowValues)
文本函数:
尚无.

函数介绍:
CSpreadSheet(CString File, CString SheetOrSeparator, bool Backup = true)

该构造函数将打开Excel(xls)文件或其他制定工作簿的文件以供读写.创建一个CSpreadSheet对象.
参数：
File: 文件路径,可以是绝对路径或相对路径,如果文件不存在将创建一个文件.
SheetOrSeparator 工作簿名.
Backup 制定是否备份文件,默认未备份文件,如果文件存在，将创建一个名为CSpreadSheetBackup 的备份文件.

bool AddHeaders(CStringArray &FieldNames, bool replace = false)

该函数将在打开的工作簿的首行添加一个头（字段）.对于Excel，每列字段必须唯一.对于特
定特征的文本文件没有限制.对于一个打开的工作簿文件,默认将添加一列,如果设置replace=true
将替代存在的字段值.该函数返回一个Bool类型的值.对于Excel,该函数需
在添加任意行之前调用.对于特定特征的文本文件，该函数可选.
参数:
FieldNames 字段名数组.
Replace 如字段存在，该参数将决定是否替代原有字段.

bool AddRow(CStringArray &RowValues, long row = 0, bool replace = false)

该函数将追加、插入或替代一行到已经打开的文档,默认追加到行的尾部.替代将以变量的值而定,新的一行将插入或替代到指定的行.
参数:
RowValues   行值
Row    行编号,如果Row=1 第一行.即字段行.
Replace   如果该行存在，指示是否替代原有行.

bool AddCell(CString CellValue, short column, long row = 0)
bool AddCell(CString CellValue, CString column, long row = 0,bool Auto=true)
添加或替代一个打开的工作簿中的单元格,默认为该行的最后一个单元格.返回一个Bool类型的值（状态）；
参数:
CellValue   填充的单元格的值。
Column   列编号
column   列名
Row    含编号，如果Row=1 第一行，即字段行.
Auto    是否让函数自动判断自动判断字段.

bool ReadRow(CStringArray &RowValues, long row = 0)

从打开的工作簿中读取一行,默认读取下一行,如果你没有使用连接池,连续运行两次,则第一次读取第一行,第二次读取第二行.返回一个Bool类型的值（状态）；
参数:
RowValues 用于存储读取到的值。
Row 行编号.默认为第一行.

bool ReadColumn(CStringArray &ColumnValues, short column)
bool ReadColumn(CStringArray &ColumnValues, CString column,bool Auto = true)
从打开的工作簿中读取一列.返回一个Bool类型的值（状态）；
参数:
Short column   列编号
CString column   列名或字段名.
Columnvalues   存储读取到的值.
Auto     设置函数自动扫描列名或字段.

bool ReadCell (CString &CellValue, short column, long row = 0)
bool ReadCell (CString &CellValue,CString column,long row=0,bool Auto=true)

从打开的工作簿中读取一个单元格的值。默认读取下一行.返回一个Bool类型的值（状态）；
参数:
CellValue    存储单元格的值.
Short column   列编号.
Row     行编号
CString column   列名
Auto      设置函数自动扫描列名或字段.

bool DeleteSheet()
从打开的文档中删除所有的工作簿内容.返回一个Bool类型的值（状态）；

bool DeleteSheet(CString SheetName)
从打开的文档中删除指定工作簿名的工作簿内容.返回一个Bool类型的值（状态）；
参数:
SheetName 工作簿名.e.G Sheet1

bool Convert(CString SheetOrSeparator)
将Excel(xls)文件转换为特定特征的文本文件(.csv)或将特定特征的文本文件(.csv)转换为Excel(xls)文件.如果
将特定特征的文本文件(.csv)转换为Excel(xls)文件SheetOrSeparator将不会被使用.返回一个Bool类型的值（状态）；
参数:
SheetOrSeparator 特征样式.

void BeginTransaction()
bool Commit()
bool Commit()
与SQL语言函数,函数相似,BeginTransaction开始事务,Commit提交事务.RoolBack回滚至保存点.Commit Commit将返回一个Bool值来表示是否成功.

bool GetTransactionStatus()
查询事务的状态,如果为true 表明已经开始，false表明没有开始或已经结束.

void GetFieldNames (CStringArray &FieldNames)
返回一个工作簿中的字段数组.
参数:
FieldNames 存储字段名的数组.

long GetTotalRows()
获得工作簿中行数.返回行数.

short GetTotalColumns()
获得工作簿中列数.返回列数.

long GetCurrentRow()
获得已选择的当前行的行号.返回行号,当前行将调用默认的ReadRow函数.

bool GetBackupStatus()
获得备份执行情况,true 已经执行,false没有执行（用户选择）或执行错误.

CString GetLastError()

返回最后一次错误信息.
Excel特定函数:
bool ReplaceRows(CStringArray &NewRowValues, CStringArray &OldRowValues)
该函数将搜索且代替多次执行的值.不支持已经回滚或释放的文档.执行完将返回执行的状态.
参数:

NewRowValues 新的值，即更新的值.
OldRowValues 原有的值，即已经存在的值.

示例:
// Create a new Excel spreadsheet, filename is test.xls, sheetname is TestSheet
CSpreadSheet SS("Test.xls", "TestSheet");

// Fill a sample 5 by 5 sheet
CStringArray sampleArray, testRow, Rows, Column;
CString tempString;
char alphabet = 'A';

SS.BeginTransaction();
for (int i = 1; i <= 5; i++)
{
 sampleArray.RemoveAll();
 for (int j = 1; j <= 5; j++)
 {
 tempString.Format("%c%d", alphabet++, i);
 sampleArray.Add(tempString);
 }
 alphabet = 'A';
 if (i == 1) // Add header rows
 {
 SS.AddHeaders(sampleArray);
 }
 else // Add data rows
 {
 SS.AddRow(sampleArray);
 }
}
// Set up test row for appending, inserting and replacing
for (int k = 1; k <= 5; k++)
{
 testRow.Add("Test");
}
SS.AddRow(testRow); // append test row to spreadsheet
SS.AddRow(testRow, 2); // insert test row into second row of spreadsheet
SS.AddRow(testRow, 4, true); // replace fourth row of spreadsheet with test row
SS.Committ();

SS.Convert(";"); // convert Excel spreadsheet into text delimited format
 // with ; as separator
// print out total number of rows
printf("Total number of rows = %d/n/n", SS.GetTotalRows());
// Print out entire spreadsheet
for (i = 1; i <= SS.GetTotalRows(); i++)
{
 // Read row
 SS.ReadRow(Rows, i);
 for (int j = 1; j <= Rows.GetSize(); j++)
 {
 if (j != Rows.GetSize())
 {
 printf("%s/t", Rows.GetAt(j-1));
 }
 else
 {
 printf("%s/n", Rows.GetAt(j-1));
 }
 }
}
// print out total number of columns
printf("/nTotal number of columns = %d/n/n", SS.GetTotalColumns());

// Read and print out contents of second column of spreadsheet
SS.ReadColumn(Column, 2);
for (i = 0; i < Column.GetSize(); i++)
{
 printf("Column 2 row %d: %s/n", i+1, Column.GetAt(i));
}
// Read in and print out the cell value at column 3, row 3 of spreadsheet
if (SS.ReadCell(tempString, 3, 3))
{
 printf("/nCell value at (3,3): %s/n", tempString);
}
else
{
 // print out error message if cell value cannot be read
 printf("Error: %s/n", SS.GetLastError);
}

本文来自CSDN博客，转载请标明出处：http://blog.csdn.net/xautfengzi/archive/2009/09/03/4516101.aspx

sai19841003

关注

0
点赞
踩
2

收藏

觉得还不错? 一键收藏
0
评论
CSpreadSheet类学习，用于操作excel表格[转]

 http://blog.csdn.net/xautfengzi/archive/2009/09/03/4516101.aspx 废话不多说，先上代码。 view plaincopy to clipboardprint? // Class to read and write to Excel and text delimited spreadsheet // // Created by Yap Chun Wei <br
复制链接

扫一扫