Modify existing SQL Data Connection with C++ using Aspose.Cells

Aspose.Cells supports modifying existing SQL Data Connections. This article will explain how to use Aspose.Cells to modify different properties of SQL Data Connections.

You can add or view Data Connections in Microsoft Excel by using the Data > Connections menu command.

Similarly, Aspose.Cells provides the means to access and modify the Data Connections using the Workbook.DataConnections 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.

• Source Excel File
• Output Excel File

#include <iostream>
#include "Aspose.Cells.h"
using namespace Aspose::Cells;

int main()
{
    Aspose::Cells::Startup();

    // Source directory path
    U16String srcDir(u"..\\Data\\01_SourceDirectory\\");

    // Output directory path
    U16String outDir(u"..\\Data\\02_OutputDirectory\\");

    // Path of input Excel file
    U16String inputFilePath = srcDir + u"DataConnection.xlsx";

    // Path of output Excel file
    U16String outputFilePath = outDir + u"output_out.xlsx";

    // Create workbook object
    Workbook workbook(inputFilePath);

    // Access first Data Connection
    ExternalConnection conn = workbook.GetDataConnections().Get(0);

    // Change the Data Connection name and ODC file
    conn.SetName(u"MyConnectionName");
    conn.SetOdcFile(u"C:\\Users\\MyDefaultConnection.odc");

    // Change the Command Type, Command and Connection String
    DBConnection dbConn = conn;
    dbConn.SetCommandType(OLEDBCommandType::SqlStatement);
    dbConn.SetCommand(u"Select * from AdminTable");
    dbConn.SetConnectionString(u"Server=myServerAddress;Database=myDataBase;User ID=myUsername;Password=myPassword;Trusted_Connection=False");

    // Save the workbook
    workbook.Save(outputFilePath);

    std::cout << "Data connection updated successfully!" << std::endl;

    Aspose::Cells::Cleanup();
}