数据转换dlg.cpp

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

 {
   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' );
}

#endif

CAboutDlg::CAboutDlg() : CDialog(CAboutDlg::IDD)
{
	//{{AFX_DATA_INIT(CAboutDlg)
	//}}AFX_DATA_INIT
}

void CAboutDlg::DoDataExchange(CDataExchange* pDX)
{
	CDialog::DoDataExchange(pDX);
	//{{AFX_DATA_MAP(CAboutDlg)
	//}}AFX_DATA_MAP
}

BEGIN_MESSAGE_MAP(CAboutDlg, CDialog)
	//{{AFX_MSG_MAP(CAboutDlg)
		// No message handlers
	//}}AFX_MSG_MAP
END_MESSAGE_MAP()

/////////////////////////////////////////////////////////////////////////////
// CMyDlg dialog

CMyDlg::CMyDlg(CWnd* pParent /*=NULL*/)
	: CDialog(CMyDlg::IDD, pParent)
{
	//{{AFX_DATA_INIT(CMyDlg)
		// NOTE: the ClassWizard will add member initialization here
	//}}AFX_DATA_INIT
	// Note that LoadIcon does not require a subsequent DestroyIcon in Win32
	m_hIcon = AfxGetApp()->LoadIcon(IDR_MAINFRAME);
}

void CMyDlg::DoDataExchange(CDataExchange* pDX)
{
	CDialog::DoDataExchange(pDX);
	//{{AFX_DATA_MAP(CMyDlg)
		// NOTE: the ClassWizard will add DDX and DDV calls here
	//}}AFX_DATA_MAP
}

BEGIN_MESSAGE_MAP(CMyDlg, CDialog)
	//{{AFX_MSG_MAP(CMyDlg)
	ON_WM_SYSCOMMAND()
	ON_WM_PAINT()
	ON_WM_QUERYDRAGICON()
	ON_BN_CLICKED(IDOK, OnButtonAddData)
	//}}AFX_MSG_MAP
END_MESSAGE_MAP()

/////////////////////////////////////////////////////////////////////////////
// CMyDlg message handlers

BOOL CMyDlg::OnInitDialog()
{
	CDialog::OnInitDialog();

	// Add "About..." menu item to system menu.

	// IDM_ABOUTBOX must be in the system command range.
	ASSERT((IDM_ABOUTBOX & 0xFFF0) == IDM_ABOUTBOX);
	ASSERT(IDM_ABOUTBOX < 0xF000);

	CMenu* pSysMenu = GetSystemMenu(FALSE);
	if (pSysMenu != NULL)
	{
		CString strAboutMenu;
		strAboutMenu.LoadString(IDS_ABOUTBOX);
		if (!strAboutMenu.IsEmpty())
		{
			pSysMenu->AppendMenu(MF_SEPARATOR);
			pSysMenu->AppendMenu(MF_STRING, IDM_ABOUTBOX, strAboutMenu);
		}
	}

	// Set the icon for this dialog.  The framework does this automatically
	//  when the application's main window is not a dialog
	SetIcon(m_hIcon, TRUE);			// Set big icon
	SetIcon(m_hIcon, FALSE);		// Set small icon
	
	// TODO: Add extra initialization here
	
	return TRUE;  // return TRUE  unless you set the focus to a control
}

void CMyDlg::OnSysCommand(UINT nID, LPARAM lParam)
{
	if ((nID & 0xFFF0) == IDM_ABOUTBOX)
	{
		CAboutDlg dlgAbout;
		dlgAbout.DoModal();
	}
	else
	{
		CDialog::OnSysCommand(nID, lParam);
	}
}

// If you add a minimize button to your dialog, you will need the code below
//  to draw the icon.  For MFC applications using the document/view model,
//  this is automatically done for you by the framework.

void CMyDlg::OnPaint() 
{
	if (IsIconic())
	{
		CPaintDC dc(this); // device context for painting

		SendMessage(WM_ICONERASEBKGND, (WPARAM) dc.GetSafeHdc(), 0);

		// Center icon in client rectangle
		int cxIcon = GetSystemMetrics(SM_CXICON);
		int cyIcon = GetSystemMetrics(SM_CYICON);
		CRect rect;
		GetClientRect(&rect);
		int x = (rect.Width() - cxIcon + 1) / 2;
		int y = (rect.Height() - cyIcon + 1) / 2;

		// Draw the icon
		dc.DrawIcon(x, y, m_hIcon);
	}
	else
	{
		CDialog::OnPaint();
	}
}

// The system calls this to obtain the cursor to display while the user drags
//  the minimized window.
HCURSOR CMyDlg::OnQueryDragIcon()
{
	return (HCURSOR) m_hIcon;
}

void CMyDlg::OnButtonAddData() 
{
	// TODO: Add your control notification handler code here
	 CFileDialog select(TRUE);
 if(select.DoModal()==IDOK)
 {
   appFile=select.GetPathName();
 }
 if(appFile!="")
 {
   if(MessageBox("请确认：\n1.你所选取的文件是excel文件\n2.你所制作的excel文件是按照系统目录下的文件制作的\n确定继续吗？","确认对话框", MB_OKCANCEL)==1)//询问用户是否继续执行文件
   {
     CSpreadSheet SS(appFile, "Sheet1");
     CStringArray Rows;
     CString aa;
     UpdateData();
     //获取Excel中总行数、总列数、当前行
     int icols = SS.GetTotalColumns();   // 总列数
     int irows = SS.GetTotalRows();    // 总行数
     // 读取指定行数据
     if(icols==0&&irows==0)
       MessageBox("没有数据或者数据表不存在，请检查后再试！");
     int row_i=0,column_j=0;
     for(row_i=2;row_i<=icols;row_i++)
       {
         if(SS.ReadRow(Rows,row_i))
         {
           int j=0;
           if(j<irows)
           {
           m1_no=Rows.GetAt(j++);               //学号
           m1_name=Rows.GetAt(j++);             //姓名
           m1_performance=Rows.GetAt(j++);      //成绩
           m1_class=Rows.GetAt(j++);            //班级
           m1_kc=Rows.GetAt(j++);               //课程
           m1_length=Rows.GetAt(j++);           //学时
           m1_mark=Rows.GetAt(j++);             //学分
           m1_teacher=Rows.GetAt(j++);          //任课教师
           m1_date=Rows.GetAt(j++);             //上课时间
           m1_type=Rows.GetAt(j++);             //课程类型
           m1_memo=Rows.GetAt(j++);             //备注
           
         }
         else if(irows==1) 
         {
           AfxMessageBox("数据读取错误！");
           return ;
         }
         sql_insert();                        //插入数据的一个函数，自己定义吧
		 }                                    //for循环结束
     }
     MessageBox("恭喜你！\n数据到入成功！");
   }                                          //外层if循环结束
 }
}                                             //函数结束

}

void CMyDlg::OnCancel() 
{
	// TODO: Add extra cleanup here
	
	CDialog::OnCancel();
}