Category / Section
How to get hyperlink url from the CSV file using XlsIO
2 mins read
This article explains how get hyperlink URL from CSV file using XlsIO.
To get hyperlink URL from the CSV file that contains a formula, the calculated value property of the cell should be assigned to value property of the cell
To know more about accessing a calculated value, please refer documentation.
Code snippet to get hyperlink URL from CSV file
worksheet.EnableSheetCalculations(); foreach (IRange range in worksheet.UsedRange) { //Assign the calculated value to value of the cell range.Value = range.CalculatedValue; } worksheet.DisableSheetCalculations(); //Export to Datatable DataTable dt = worksheet.ExportDataTable(worksheet.UsedRange,ExcelExportDataTableOptions.None);
The following C#/VB complete code explains how to get hyperlink URL from CSV file using XlsIO.
C#
using Syncfusion.XlsIO; using System.Data; using System.IO; using System.Reflection; namespace HyperLink_URL { class Program { static void Main(string[] args) { using (ExcelEngine excelEngine = new ExcelEngine()) { //Instantiate the excel application object. IApplication application = excelEngine.Excel; //The workbook is opened. IWorkbook workbook; //Open existing workbook with data entered Assembly assembly = typeof(Program).GetTypeInfo().Assembly; Stream fileStream = assembly.GetManifestResourceStream("Hyperlink_URL.Sample.csv"); workbook = application.Workbooks.Open(fileStream, ExcelOpenType.Automatic); //The first worksheet object in the worksheets collection is accessed. IWorksheet worksheet = workbook.Worksheets[0]; worksheet.EnableSheetCalculations(); foreach (IRange range in worksheet.UsedRange) { //Assign the calculated value to value of the cell range.Value = range.CalculatedValue; } worksheet.DisableSheetCalculations(); //Export to Datatable DataTable dt = worksheet.ExportDataTable(worksheet.UsedRange, ExcelExportDataTableOptions.None); //Saving the workbook Stream stream = File.Create("Output.xlsx"); workbook.SaveAs(stream); } } } }
VB
Imports Syncfusion.XlsIO Imports System.Data Imports System.IO Imports System.Reflection Namespace HyperLink_URL Class Program Private Shared Sub Main(ByVal args() As String) Dim excelEngine As ExcelEngine = New ExcelEngine 'Instantiate the excel application object. Dim application As IApplication = excelEngine.Excel 'The workbook is opened. Dim workbook As IWorkbook 'Open existing workbook with data entered Dim assembly As Assembly = GetType(Program).GetTypeInfo.Assembly Dim fileStream As Stream = assembly.GetManifestResourceStream("Hyperlink_URL.Sample.csv") workbook = application.Workbooks.Open(fileStream, ExcelOpenType.Automatic) 'The first worksheet object in the worksheets collection is accessed. Dim worksheet As IWorksheet = workbook.Worksheets(0) worksheet.EnableSheetCalculations() For Each range As IRange In worksheet.UsedRange 'Assign the calculated value to value of the cell range.Value = range.CalculatedValue Next worksheet.DisableSheetCalculations() 'Export to Datatable Dim dt As DataTable = worksheet.ExportDataTable(worksheet.UsedRange, ExcelExportDataTableOptions.None) 'Saving the workbook Dim stream As Stream = File.Create("Output.xlsx") workbook.SaveAs(stream) End Sub End Class End Namespace