#include "stdafx.h"
#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(_T("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(_T("%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(_T("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 = _T("[") + SheetName + _T("$A1:IV65536]");
m_stempSql.Format (_T("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(_T("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(_T("\"%s\"%s"), RowValues.GetAt(i), m_sSeparator);
m_stempString += m_stempSql;
}
else // Last column
{
m_stempSql.Format(_T("\"%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(_T("\" \"%s"), m_sSeparator);
}
else
{
nullString.Format(_T("\"\"%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.
