excelctrl.cpp

可以将列表控件中的内容导出到EXCEL中

/*-----------------------------------------
作用：综合网络上面的导出Excel功能方法，封装Excel操作功能封装类，
	  可以用该类进行EXCEL的一系列简单操作，而不用考虑SQL语言的运用
作者：戚高
时间:2004-04-01
-----------------------------------------*/

#include "stdafx.h" 
#include "ExcelCtrl.h"  

CExcelCtrl::CExcelCtrl(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;
			}
		}
	}
}

CExcelCtrl::~CExcelCtrl()
{
	if (m_Database != NULL)
	{
		m_Database->Close();
		delete m_Database;
	}
}

// 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 CExcelCtrl::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 CExcelCtrl::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;
}

// Add header row to spreadsheet
bool CExcelCtrl::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;
		}
	}
}

// Insert or replace a row into spreadsheet. 
// Default is add new row.
bool CExcelCtrl::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;
}

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

// Convert Excel column in alphabet into column number
short CExcelCtrl::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;	
}

// Save changes to spreadsheet
bool CExcelCtrl::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();