数据转换dlg.cpp

这是一个Excel数据表导入SQL Server2000数据库的应用程序。

 {
   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())