Modify existing SQL Data Connection using Aspose.Cells for JavaScript via C++
Contents
[
Hide
]
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.dataConnections collection.
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.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 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.
<!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>