检索SQL连接数据

Contents
[ ]

在Microsoft Excel中,通过以下步骤连接数据库:

  1. 单击数据菜单,选择来自其他源,然后选择来自SQL Server
  2. 然后选择数据,然后选择连接。 3.使用连接向导连接到数据库并创建数据库查询。

Aspose.Cells提供了用于检索外部连接的Workbook.DataConnections属性。它返回工作簿中的ExternalConnection对象集合。

如果ExternalConnection对象包含SQL连接数据,它可以被类型转换为DBConnection对象,并且它的属性可以用于检索数据库命令、命令类型、连接描述、连接信息、凭据等。

// For complete examples and data files, please go to https://github.com/aspose-cells/Aspose.Cells-for-.NET
// The path to the documents directory.
string dataDir = RunExamples.GetDataDir(System.Reflection.MethodBase.GetCurrentMethod().DeclaringType);
// Create a workbook object from source file
Workbook workbook = new Workbook(dataDir+ "connection.xlsx");
// Access the external collections
ExternalConnectionCollection connections = workbook.DataConnections;
int connectionCount = connections.Count;
ExternalConnection connection = null;
for (int i = 0; i < connectionCount; i++)
{
connection = connections[i];
// Check if the Connection is DBConnection, then retrieve its various properties
if (connection is DBConnection)
{
DBConnection dbConn = (DBConnection)connection;
// Retrieve DB Connection Command
Console.WriteLine("Command: " + dbConn.Command);
// Retrieve DB Connection Command Type
Console.WriteLine("Command Type: " + dbConn.CommandType);
// Retrieve DB Connection Description
Console.WriteLine("Description: " + dbConn.ConnectionDescription);
// Retrieve DB Connection ID
Console.WriteLine("Id: " + dbConn.Id);
// Retrieve DB Connection Info
Console.WriteLine("Info: " + dbConn.ConnectionString);
// Retrieve DB Connection Credentials
Console.WriteLine("Credentials: " + dbConn.CredentialsMethodType);
// Retrieve DB Connection Name
Console.WriteLine("Name: " + dbConn.Name);
// Retrieve DB Connection ODC File
Console.WriteLine("OdcFile: " + dbConn.OdcFile);
// Retrieve DB Connection Source File
Console.WriteLine("Source file: " + dbConn.SourceFile);
// Retrieve DB Connection Type
Console.WriteLine("Type: " + dbConn.SourceType);
// Retrieve DB Connection Parameters Collection
ConnectionParameterCollection paramCollection = dbConn.Parameters;
int paramCount = paramCollection.Count;
// Iterate the Parameter Collection
for (int j = 0; j < paramCount; j++)
{
ConnectionParameter param = paramCollection[j];
// Retrieve Parameter Cell Reference
Console.WriteLine("Cell reference: " + param.CellReference);
// Retrieve Parameter Name
Console.WriteLine("Parameter name: " + param.Name);
// Retrieve Parameter Prompt
Console.WriteLine("Prompt: " + param.Prompt);
// Retrieve Parameter SQL Type
Console.WriteLine("SQL Type: " + param.SqlType);
// Retrieve Parameter Type
Console.WriteLine("Param Type: " + param.Type);
// Retrieve Parameter Value
Console.WriteLine("Param Value: " + param.Value);
}// End for
}// End if
}// End for