How to export delimited string in the Excel worksheet into collection object and add it as a string array using C#, VB.NET?

Syncfusion Excel (XlsIO) library is a .NET Excel library used to create, read, and edit Excel documents. Also, converts Excel documents to PDF files.

Using Syncfusion, we can export data from a worksheet into collection objects. In this article, we will explore how to export delimited data from the worksheet, convert it into collection objects, and add it as a string array.

Steps for exporting delimited data from worksheet into string array programmatically:

Step 1: Create a new C# console application project.

Step 2: Install the Syncfusion.XlsIO.Net.Core NuGet package as reference to your .NET Core applications from NuGet.org.

Step 3: Include the following namespaces in Program.cs file.

C#

using Syncfusion.XlsIO;
using System.ComponentModel;

VB.NET

Imports Syncfusion.XlsIO
Imports System. ComponentModel

Step 4: Use the following code snippet to export delimited string from collection objects and add it as a string array.

C#

//Create an instance of ExcelEngine
using (ExcelEngine excelEngine = new ExcelEngine())
{
   IApplication application = excelEngine.Excel;
   application.DefaultVersion = ExcelVersion.Xlsx;

   //Load an input template
   FileStream inputStream = new FileStream("../../../Data/InputTemplate.xlsx", FileMode.Open, FileAccess.Read);
   IWorkbook workbook = application.Workbooks.Open(inputStream);
   IWorksheet worksheet = workbook.Worksheets[0];

   //Export worksheet data into Collection Objects
   List<Report> collectionObjects = worksheet.ExportData<Report>(1, 1, 6, 3);

   //Loop through the list and add the delimitied string to an array
   foreach (var report in collectionObjects)
   {
       report.SalesPersonArray = report.SalesPerson.Split(',');
   }

   //Dispose streams
   inputStream.Dispose();

}

// Helper class to represent a report
public class Report
{
    [DisplayNameAttribute("Sales Person Name")]
    public string SalesPerson { get; set; }
    public string SalesJanJun { get; set; }
    public string SalesJulDec { get; set; }
    public string[] SalesPersonArray { get; set; }
    public Report()
    {

    }
}

VB.NET

//Create an instance of ExcelEngine
Using excelEngine As New ExcelEngine()
   Dim application As IApplication = excelEngine.Excel
   application.DefaultVersion = ExcelVersion.Xlsx

   ' Load an input template
   Dim inputStream As New FileStream("../../Data/InputTemplate.xlsx", FileMode.Open, FileAccess.Read)
   Dim workbook As IWorkbook = application.Workbooks.Open(inputStream)
   Dim worksheet As IWorksheet = workbook.Worksheets(0)

   ' Export worksheet data into Collection Objects
   Dim collectionObjects As List(Of Report) = worksheet.ExportData(Of Report)(1, 1, 6, 3)

   ' Loop through the list and add the delimited string to an array
   For Each report As Report In collectionObjects
       report.SalesPersonArray = report.SalesPerson.Split(",")
   Next

   ' Dispose streams
   inputStream.Dispose()
End Using

// Helper class to represent a report
Public Class Report
   <DisplayNameAttribute("Sales Person Name")>
   Public Property SalesPerson As String
   Public Property SalesJanJun As String
   Public Property SalesJulDec As String
   Public Property SalesPersonArray As String()

   Public Sub New()
   End Sub
End Class

A complete working sample of how to export delimited data from collection object into string array using C# can be downloaded from here.

See Also:

Working with Data using XlsIO

Take a moment to peruse the documentation, where you can find basic worksheet data manipulation options along with features like Conditional Formatting, worksheet calculations through Formulas, adding Charts in worksheets or workbooks, organizing and analyzing data through Tables and Pivot Tables, appending multiple records to a worksheet using Template Markers, and most importantly PDF and Image conversions with code examples.

Refer here to explore the rich set of Syncfusion Excel (XlsIO) library features.