Excel与数据库数据交换类

// 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 #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("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_dCurre