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

Aspose.Cells supports modifying existing SQL Data Connection. This 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 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\\MyDefaulConnection.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();
}