Modify existing SQL Data Connection using Aspose.Cells
Aspose.Cells supports modifying existing SQL Data Connection. The article will explain how to use Aspose.Cells to modify different properties of SQL Data Connection.
You can add or see Data Connections inside Microsoft Excel by following Data > Connections menu command.
Similarly, Aspose.Cells provides the means to access and modify the Data Connections using Workbook.getDataConnections() collection.
Modify existing SQL Data Connection using Aspose.Cells
The following sample illustrates the use of Aspose.Cells to modify SQL Data Connection of the workbook. You can download the source Excel file used in this code and the output Excel file generated by the code from the following links.
// For complete examples and data files, please go to https://github.com/aspose-cells/Aspose.Cells-for-Java | |
// The path to the documents directory. | |
String dataDir = Utils.getDataDir(ModifyExistingSQLDataConnection.class); | |
// Create a workbook object from source file | |
Workbook workbook = new Workbook(dataDir + "DataConnection.xlsx"); | |
// Access first Data Connection | |
ExternalConnection conn = workbook.getDataConnections().get(0); | |
// Change the Data Connection Name and Odc file | |
conn.setName("MyConnectionName"); | |
conn.setOdcFile(dataDir + "MyDefaulConnection.odc"); | |
// Change the Command Type, Command and Connection String | |
DBConnection dbConn = (DBConnection) conn; | |
dbConn.setCommandType(OLEDBCommandType.SQL_STATEMENT); | |
dbConn.setCommand("Select * from AdminTable"); | |
dbConn.setConnectionInfo( | |
"Server=myServerAddress;Database=myDataBase;User ID=myUsername;Password=myPassword;Trusted_Connection=False"); | |
// Save the workbook | |
workbook.save(dataDir + "outxput.xlsx"); |