Modify existing SQL Data Connection using Aspose.Cells for JavaScript via C++
Contents
[
Hide
]
Aspose.Cells supports modifying an existing SQL Data Connection. This article explains how to use Aspose.Cells to modify different properties of an SQL Data Connection.
You can add or view Data Connections inside Microsoft Excel by using the Data > Connections menu command.
Similarly, Aspose.Cells provides the means to access and modify the Data Connections using the
You can add or view Data Connections inside 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 for JavaScript via C++ to modify the workbook’s SQL Data Connection. You can download the source Excel file used in this code and the output Excel file generated by the code from the following links.
<!DOCTYPE html>
<html>
<head>
<title>Aspose.Cells Data Connection Example</title>
</head>
<body>
<h1>Data Connection Example</h1>
<input type="file" id="fileInput" accept=".xls,.xlsx,.csv" />
<button id="runExample">Run Example</button>
<a id="downloadLink" style="display: none;">Download Result</a>
<div id="result"></div>
</body>
<script src="aspose.cells.js.min.js"></script>
<script type="text/javascript">
const { Workbook, SaveFormat } = AsposeCells;
AsposeCells.onReady({
license: "/lic/aspose.cells.enc",
fontPath: "/fonts/",
fontList: [
"arial.ttf",
"NotoSansSC-Regular.ttf"
]
}).then(() => {
console.log("Aspose.Cells initialized");
});
document.getElementById('runExample').addEventListener('click', async () => {
const fileInput = document.getElementById('fileInput');
if (!fileInput.files.length) {
document.getElementById('result').innerHTML = '<p style="color: red;">Please select an Excel file.</p>';
return;
}
const file = fileInput.files[0];
const arrayBuffer = await file.arrayBuffer();
// Create workbook object from uploaded file
const workbook = new Workbook(new Uint8Array(arrayBuffer));
// Access first Data Connection
const conn = workbook.dataConnections.get(0);
// Change the Data Connection name and ODC file
conn.name = "MyConnectionName";
conn.odcFile = "C:\\Users\\MyDefaulConnection.odc";
// Change the Command Type, Command, and Connection String
const dbConn = conn;
dbConn.commandType = AsposeCells.OLEDBCommandType.SqlStatement;
dbConn.command = "Select * from AdminTable";
dbConn.connectionString = "Server=myServerAddress;Database=myDataBase;User ID=myUsername;Password=myPassword;Trusted_Connection=False";
// Save the workbook and provide download link
const outputData = workbook.save(SaveFormat.Xlsx);
const blob = new Blob([outputData]);
const downloadLink = document.getElementById('downloadLink');
downloadLink.href = URL.createObjectURL(blob);
downloadLink.download = 'output_out.xlsx';
downloadLink.style.display = 'block';
downloadLink.textContent = 'Download Modified Excel File';
document.getElementById('result').innerHTML = '<p style="color: green;">Data connection updated successfully! Click the download link to get the modified file.</p>';
});
</script>
</html>