iCAx开思网

标题: CAA怎样中向excel中写数据 [打印本页]

作者: liuruixiao205    时间: 2005-5-13 11:36
标题: CAA怎样中向excel中写数据
我想用程序新建一个excel同时往里面写数据,我找了一段vc的程序如下
    CDatabase database;
  CString sDriver = "MICROSOFT EXCEL DRIVER (*.XLS)"; // Excel安装驱动
  CString sExcelFile,sPath;  
  CString sSql;
    
  //获取主程序所在路径,存在sPath中
  GetModuleFileName(NULL,sPath.GetBufferSetLength (MAX_PATH+1),MAX_PATH);
  sPath.ReleaseBuffer ();
  int nPos;
  nPos=sPath.ReverseFind ('\\');
  sPath=sPath.Left (nPos);
  
  sExcelFile = sPath + "\\拱圈参数.xls";       // 要建立的Excel文件
  
  TRY
  {
    // 创建进行存取的字符串
    sSql.Format("DRIVER={%s};DSN='';FIRSTROWHASNAMES=1;READONLY=FALSE;CREATE_DB=\"%s\";DBQ=%s",sDriver, sExcelFile, sExcelFile);
  
    // 创建数据库 (既Excel表格文件)
    if( database.OpenEx(sSql,CDatabase::noOdbcDialog) )
    {
      // 创建表结构(姓名、年龄)
      sSql = "CREATE TABLE Exceldemo (Name TEXT,Age NUMBER)";
      database.ExecuteSQL(sSql);
  
      // 插入数值
      sSql = "INSERT INTO Exceldemo (Name,Age) VALUES ('徐景周',26)";
      database.ExecuteSQL(sSql);
  
      sSql = "INSERT INTO Exceldemo (Name,Age) VALUES ('徐志慧',22)";
      database.ExecuteSQL(sSql);
  
      sSql = "INSERT INTO Exceldemo (Name,Age) VALUES ('郭徽',27)";
      database.ExecuteSQL(sSql);
    }      
  
    // 关闭数据库
    database.Close();
  
  AfxMessageBox("Excel文件写入成功!");
  }
  CATCH_ALL(e)
  {
    TRACE1("Excel驱动没有安装: %s",sDriver);
  }
  END_CATCH_ALL;
  //CDialog::OnOK();
作者: liuruixiao205    时间: 2005-5-13 11:39
需要头文件:
#include <afxdb.h>  
#include <odbcinst.h>
  纠错机制可以注释掉,但还是在下面这一句报错,
  sSql.Format("DRIVER={%s};DSN='';FIRSTROWHASNAMES=1;READONLY=FALSE;CREATE_DB=\"%s\";DBQ=%s",sDriver, sExcelFile, sExcelFile);  
应该是类型Cstring的问题,但我对CAA与vc之间的类型转换不太明白。请高手指点应该怎么做。
作者: acoka    时间: 2005-5-13 11:39
这个问题你该上普通的C++的版面去问
  
提醒一点,除comment以外,尽量不要在CAA的cpp里写全角,有时候会system down
作者: liuruixiao205    时间: 2005-5-13 11:42
  acoka真快。这段程序在vc中没错,没法问。caa中没有自己的建立excel和谢数据的接口吗?
作者: liuruixiao205    时间: 2005-5-13 12:07
我觉得应该是Cstring::Format函数不认。CATIA有自己的CATString ,哪位高手能给改一下。或给点这方面的资料
作者: acoka    时间: 2005-5-13 13:29
你用用
CATICkeSheet
以前R10以前,这个interface有问题,没次用后都会留下不用的数据,清理不掉,DS那时候没能尽快解决,后来我们就做了自己的excel读写模块,现在或许解决了,你可以试试看
作者: liuruixiao205    时间: 2005-5-13 16:05
好的,谢谢acoka!
作者: liuruixiao205    时间: 2005-5-24 17:08
找了一下,在CAA V5 Encyclopedia中没有CATICkeSheet这个接口的资料,acoka兄能不能给点相关的资料。百科全书里有CATICkeRelationFactory-> CreateDesignTable 这个函数,用它能不能在目录下新生成一个excel。  
请指教。
作者: acoka    时间: 2005-5-24 17:19
KnowledgeInterfaces Interface CATICkeSheet
System.IUnknown
  |
  +---System.IDispatch
    |
    +---System.CATBaseUnknown
      |
      +---CATICkeSheet
   
Usage: an implementation of this interface is supplied and you must use it as is. You should not reimplement it.
  
--------------------------------------------------------------------------------
  
interface CATICkeSheet  
  
This interface is an encapsulation of an array of arrays (a sheet) of strings contained in a file.
  
Role: This file can be an excel sheet, a lotus sheet or a tabulated text file.The format of the text file  
has to be : string1 (tab) string2 etc  
... (n lines)  
stringN (tab) stringN+1 etc  
Empty strings are allowed, but not in the first row because the number of columns is computed using the first row.
For design table use, the file is parsed until its end or until the first empty row is found.
If you use sheets of format Excel or Lotus, be careful not to leave Excel or Lotus process resident in memory (take care of SetCell arguments)  
  
See also:  
CATIDesignTable  
See also:  
CATICkeRelationFactory  
  
--------------------------------------------------------------------------------
  
Method Index
  
o Cell(int,int)  
Gets the cell r,c.  
o ChangeSourceFile(CATUnicodeString&)  
This method is used to change the source file.  
o Column(int)  
Gets the column c of the sheet.  
o Columns()  
Gets the number of columns in the sheet.  
o CompareAndUpdateFileDate()  
Compares the date of file from which data are imported with the date set as attribute and updates this attribute.  
o CopyMode()  
Returns the value of the CopyMode_ attribute.  
o CreateSourceFile(CATUnicodeString&)  
Creates a file for future sheet.  
o ExportContentToFile(CATUnicodeString&,int)  
This method export all the sheet content to a Text, an Excel or a Lotus file.  
o GetCallbackCommand()  
Gets the associated command that sends the Ole doc.  
o GetFileDateChangedNotification()  
Do not use.  
o GetOrientation()  
Returns the orientation of the sheet.  
o GetSourceFilePath()  
Returns the path and name of the file from which data are imported.  
o GetSourceFormat()  
This method allows to know the format of the linked file.  
o GetSourceStatus()  
This method is used to know the status of the source file.  
o IsFileModified(CATUnicodeString&,CATUnicodeString&)  
Indicates if the file has been modified.  
o LaunchEditor()  
Launchs the sheet editor.  
o Row(int)  
Gets the row r of the sheet.  
o Rows()  
Gets the number of rows in the sheet.  
o SetCell(int,int,CATUnicodeString&,CATCke::Boolean,CATCke::Boolean)  
Sets the content of the cell (r,c) in the sheet and saves the sheet if lastCell = True.  
o SetCopyMode(CATCke::Boolean)  
Sets the CopyMode_ attribute.  
o SetOrientation(int)  
Sets the orientation of the sheet.  
o SetSourceFilePath(CATUnicodeString&)  
Sets the path of the ascii file containing the data.  
o UpdateLocalCopy(int,int)  
Performs analysis of the sheet.  
o UpdateLocalCopyFromSpec()  
Does the same work as UpdateLocalCopy.  
o WhichColumn(int,CATUnicodeString&)  
Gets the index of the column where the sheet takes the value of columnContent in the row r.  
o WhichRow(int,CATUnicodeString&)  
Gets the index of the row where the sheet takes the value of rowContent in the column c.  
Enumerated Type Index
  
o SourceFormat  
o SourceStatus  
Methods
  
o Cell  
public virtual const CATUnicodeString& Cell( const int  r,  
  const int  c) const = 0   
  
Gets the cell r,c.  
Returns:  
the content of the cell r,c. An empty cell if coordinates are wrong  
Parameters:  
r  
the row of the wanted cell (1-based)  
c  
the column of the wanted cell (1-based)  
o ChangeSourceFile  
public virtual CATCke::Boolean ChangeSourceFile( const CATUnicodeString&  path) = 0   
  
This method is used to change the source file. The type of the file must be the same as the old one...(excel <-> excel, text file <-> text file). This method only changes the source file : it doesn't update the content of the sheet (no call to UpdateLocalCopy)  
Returns:  
False if the method fails  
Parameters:  
path  
the path of the new source file  
o Column  
public virtual const CATListOfCATUnicodeString* Column( const int  c) = 0   
  
Gets the column c of the sheet.  
Returns:  
const CATListOfCATUnicodeString* : this list must not be destroyed and doesn't change until this method is called again. If c > Columns() returns NULL  
Parameters:  
c  
the wanted column. 1-based Index  
o Columns  
public virtual int Columns( )const = 0   
  
Gets the number of columns in the sheet.  
Returns:  
the number of columns  
o CompareAndUpdateFileDate  
public virtual CATCke::Boolean CompareAndUpdateFileDate( )= 0   
  
Compares the date of file from which data are imported with the date set as attribute and updates this attribute.  
Returns:  
CATCke::False if dates are different  
o CopyMode  
public virtual CATCke::Boolean CopyMode( )const = 0   
  
Returns the value of the CopyMode_ attribute.  
o CreateSourceFile  
public virtual CATCke::Boolean CreateSourceFile( const CATUnicodeString&  path) = 0   
  
Creates a file for future sheet. If the path contains the .xls extension, an excel file is created. If the file contains the .123 extension, a Lotus 123 sheet is created. Else, a text file is created  
Returns:  
CATCke::False if the creation of the file fails  
Parameters:  
path  
The path of the file to create  
o ExportContentToFile  
public virtual HRESULT ExportContentToFile( const CATUnicodeString&  iFilePath,  
  int  iReplaceFileIfExist) = 0   
  
This method export all the sheet content to a Text, an Excel or a Lotus file.  
Parameters:  
iFilePath  
The path of the file that will be created. If it contains : - ".xls", an excel file will be created - ".123", a Lotus file will be created else, a text file will be created. The link is not kept with the file (if you go on modifying the model sheet after exporting its content to a file, the file content will not be up to date with the model sheet content). The file is just a visible image of the sheet content at a given instant. Notice that this method will fail if you give the path of an existing file without setting the 2d argument to 1 or if the existing file access is not Read/Write.  
iReplaceFileIfExist  
If you give a path that corresponds to an existing file, you have to set it to 1 to force the file replacement (else this method will failed).By default, this argument is set to 0 in order to keep the existing file.  
Returns:  
E_FAIL if the file creation or the file filling failed, and S_OK else.  
o GetCallbackCommand  
public virtual CATCommand * GetCallbackCommand( )= 0   
  
Gets the associated command that sends the Ole doc. Close and modify notification If a client wants to receive this notification, he has to write :  
AddCallback(sheet->GetCallbackCommand(), sheet->GetOleModifyAndCloseNotification(), (CATSubscriberMethod) &CATClientClass::OnSheetModification))  
o GetFileDateChangedNotification  
public virtual CATCallbackEvent GetFileDateChangedNotification( )= 0   
  
Do not use.  
o GetOrientation  
public virtual int GetOrientation( )const = 0   
  
Returns the orientation of the sheet.  
Returns:  
1 for vertical columns and 0 for horizontal ones.  
o GetSourceFilePath  
public virtual CATUnicodeString GetSourceFilePath( )= 0   
  
Returns the path and name of the file from which data are imported.  
o GetSourceFormat  
public virtual CATICkeSheet::SourceFormat GetSourceFormat( )= 0   
  
This method allows to know the format of the linked file.  
Returns:  
CATICkeSheet::Text if text file, CATICkeSheet::Excel if Excel file and CATICkeSheet:otus if Lotus 123 file. If no file is associated, or if the link is lost, returns CATICkeSheet::NoSource  
o GetSourceStatus  
public virtual CATICkeSheet::SourceStatus GetSourceStatus( )= 0   
  
This method is used to know the status of the source file.  
Returns:  
CATICkeSheet::NotFound if the file isn't found, CATICkeSheet::ReadOnly if the file is read-only, CATICkeSheet::ReadWriteOk if the file can be written to, CATICkeSheet::Empty if the file path is empty  
o IsFileModified  
public virtual int IsFileModified( CATUnicodeString&  oOldDate,  
  CATUnicodeString&  oNewDate) = 0   
  
Indicates if the file has been modified.  
Returns:  
1 if date stored in sheet feature is different from file one, 0 else.  
o LaunchEditor  
public virtual HRESULT LaunchEditor( )= 0   
  
Launchs the sheet editor. Warning, the behaviour of this method is different for text files and Excel or Lotus 123 files :  
its call is blocking for text files and not for other ones.
For Excel and Lotus 123 files, to be warned after Excel or Lotus 123 closure in case of a sheet modification, you have to put a callback on CATICkeOleSheet::GetOleModifyAndCloseNotification event.  
Returns:  
S_OK if method succeeds, E_FAIL if method fails : for example, calling it on UNIX for an Excel based design table, or calling it on NT with Excel not installed  
o Row  
public virtual const CATListOfCATUnicodeString* Row( const int  r) = 0   
  
Gets the row r of the sheet.  
Returns:  
const CATListOfCATUnicodeString* : this list mustn't be destroyed and doesn't change until this method is called again. If r > Rows() returns NULL  
Parameters:  
r  
the wanted row. 1-based Index  
o Rows  
public virtual int Rows( )const = 0   
  
Gets the number of rows in the sheet. It includes the columns titles row.  
Returns:  
the number of rows  
o SetCell  
public virtual CATCke::Boolean SetCell( const int  r,  
  const int  c,  
  const CATUnicodeString&  s,  
  CATCke::Boolean  firstCell =1,  
  CATCke::Boolean  lastCell =1) = 0   
  
Sets the content of the cell (r,c) in the sheet and saves the sheet if lastCell = True. Returns CATCke::True if the function succeded, CATCke::False else
WARNING : for text files, this method has strong performance problems. Use CATICkeSheet::AddRow instead of SetCell to fix them  
Parameters:  
r  
(1-based index) row parameter  
c  
(1-based index) column parameter  
s  
cell content  
firstCell  
this flag has to be set to True if the SetCell function is called for the first time since UpdateLocalCopy method was called  
lastCell  
this flag has to be set to True if the SetCell function is called for the last time until UpdateLocalCopy method is called  
o SetCopyMode  
public virtual void SetCopyMode( CATCke::Boolean  mode) = 0   
  
Sets the CopyMode_ attribute.  
Parameters:  
mode  
: if mode = True, performs a persistent copy of the content of the sheet It is useful to swap model from NT (Excel) to Unix  
o SetOrientation  
public virtual void SetOrientation( const int  vertical) = 0   
  
Sets the orientation of the sheet.  
Parameters:  
integer  
equal to 1 for vertical columns and 0 for horizontal ones.  
o SetSourceFilePath  
public virtual void SetSourceFilePath( const CATUnicodeString&  path) = 0   
  
Sets the path of the ascii file containing the data. The format of the file is tabulated.  
Parameters:  
path  
The path to be set  
o UpdateLocalCopy  
public virtual CATCke::Boolean UpdateLocalCopy( const int  forceWidth = 0,  
  const int  forceHeight = 0) = 0   
  
Performs analysis of the sheet. (parses the content of the sheet, updates the number of columns and rows)  
Parameters:  
forceWidth  
this parameter mustn't be used in most cases : it forces the width (nb of columns) looked for (used in CATCkeImportServices to ensure that the columns of formulas and comments will be read). If forceWidth != 0, the nb of column will be >= forceWidth  
forceHeight  
the counterpart of forceWidth for the height of the sheet  
Returns:  
CATCke::False if the parsing fails (empty sheet, no sheet, ...)  
o UpdateLocalCopyFromSpec  
public virtual CATCke::Boolean UpdateLocalCopyFromSpec( )= 0   
  
Does the same work as UpdateLocalCopy. But with data taken from the model (if CopyMode_ is equal to True).  
Returns:  
False if no data was put in the model (CopyMode_ = False)  
o WhichColumn  
public virtual int WhichColumn( const int  r,  
  const CATUnicodeString&  columnContent) = 0   
  
Gets the index of the column where the sheet takes the value of columnContent in the row r.  
Returns:  
the 1-based index of the searched column, 0 if not found  
Parameters:  
r  
the row where the search is done  
columnContent  
the string searched  
o WhichRow  
public virtual int WhichRow( const int  c,  
  const CATUnicodeString&  rowContent) = 0   
  
Gets the index of the row where the sheet takes the value of rowContent in the column c.  
Returns:  
the 1-based index of the searched row, 0 if not found  
Parameters:  
c  
the column where the search is done  
rowContent  
the string searched  
Enumerated Types
  
o SourceFormat  
enum SourceFormat {
  Text,
  Excel,
  Lotus,
  NoSource
}
  
o SourceStatus  
enum SourceStatus {
  ReadOnly,
  NotFound,
  ReadWriteOk,
  Empty,
  NotReadable
}
  
--------------------------------------------------------------------------------
This object is included in the file: CATICkeSheet.h
If needed, your Imakefile.mk should include the module: KnowledgeItf  
--------------------------------------------------------------------------------
作者: acoka    时间: 2005-5-24 17:20
CATICkeParmFactory->CreateSheet()
作者: liuruixiao205    时间: 2005-5-25 10:44
非常感谢acoka兄!
作者: liuruixiao205    时间: 2005-5-25 12:17
acoka怎么得到接口CATICkeSheet?没有这个使用这个接口的例子呀?
只用这几个接口CATICkeParmFactory、CATIDesignTable、 CATICkeRelationFactory 不用CATICkeSheet能实现创建和读取excel的功能吗?
作者: acoka    时间: 2005-5-25 12:45
没有例子,这个接口是公开的呀
  
CATIDesignTable->Sheet()的返回值就是它呀
作者: liuruixiao205    时间: 2005-5-25 13:44
明白,谢谢!




欢迎光临 iCAx开思网 (https://www.icax.org/) Powered by Discuz! X3.3