Tutorial Concept
Learn how to split text into columns programmatically in .NET WinForms applications using Spread.NET's TextToColumns method, TEXTSPLIT function, and the built-in Text to Columns Wizard, streamlining data management with C# or VB.NET.
What You Will Need
- Visual Studio - This example using VS2022
- .NET 6+ WinForms Application
- NuGet Package: GrapeCity.Spread.WinForms
Controls Referenced
When dealing with Excel data in .NET applications, it's common to encounter text that needs to be split into multiple columns. In this guide, we’ll show how to split text to columns programmatically using an API or by invoking a runtime Convert Text to Columns Wizard using the .NET spreadsheet component, Spread.NET, in C# or VB.NET, ensuring you and your users can efficiently manage data within your WinForms applications.
Split Text to Columns Using C#/VB.NET in .NET WinForms Apps
1.Programmatically Split Text into Columns in C# and VB.NET
2.Invoke the Text to Columns Wizard in WinForms Apps
a. Programmatically Invoking the Convert Text to Columns Wizard
b. No-Code Solution with Spread Designer and Ribbon Control
Programmatically Split Text into Columns in C# and VB.NET
To automate the "Text to Columns" functionality in a .NET application, developers can use Spread.NET's API to invoke the TextToColumns method or use the built-in TEXTSPLIT function and efficiently split text in cells, similar to Excel's built-in feature. This approach ensures consistent data organization while reducing the risk of manual errors.
TextToColumns Method
The following code demonstrates invoking the TextToColumns method to split text into separate columns using a delimiter or column width option.
C#:
private void OnTextToColumnByCodeClick(object sender, EventArgs e)
{
var sheet = _fpSpread.AsWorkbook().ActiveSheet;
var selection = sheet.Selection;
sheet.Cells[selection.Row, selection.Column, selection.Row2, selection.Column2]
.TextToColumns("$B$1", TextParsingType.Delimited, TextQualifier.DoubleQuote, false, false, false, true);
}
VB.NET:
Private Sub OnTextToColumnByCodeClick(ByVal sender As Object, ByVal e As EventArgs)
Dim sheet = _fpSpread.AsWorkbook().ActiveSheet
Dim selection = sheet.Selection
sheet.Cells(selection.Row, selection.Column, selection.Row2, selection.Column2).TextToColumns("$B$1", TextParsingType.Delimited, TextQualifier.DoubleQuote, False, False, False, True)
End Sub
Download the Spread.NET WinForms Demo Explorer and check out the Worksheet’s Text to Column demo to learn more:
TEXTSPLIT Function
The following code shows how to use the TEXTSPLIT function in a cell with C# or VB.NET. This is equivalent to the TextToColumns method for splitting text, operating on rows with custom delimiters.
C#:
IWorkbook workbook = fpSpread1.AsWorkbook();
workbook.WorkbookSet.CalculationEngine.CalcFeatures = CalcFeatures.All;
...
fpSpread1.ActiveSheet.Cells["C2"].Formula = "TEXTSPLIT(A2,\" \")";
fpSpread1.ActiveSheet.Cells["C3"].Formula = "TEXTSPLIT(A3,\" \")";
fpSpread1.ActiveSheet.Cells["C4"].Formula = "TEXTSPLIT(A4,{\",\",\";\"})";
VB.NET:
Dim workbook As IWorkbook = fpSpread1.AsWorkbook()
workbook.WorkbookSet.CalculationEngine.CalcFeatures = CalcFeatures.All
...
fpSpread1.ActiveSheet.Cells("C2").Formula = "TEXTSPLIT(A2,"" "")"
fpSpread1.ActiveSheet.Cells("C3").Formula = "TEXTSPLIT(A3,"" "")"
fpSpread1.ActiveSheet.Cells("C4").Formula = "TEXTSPLIT(A4,{"","","";""})"
Download the Spread.NET WinForms Demo Explorer to explore the Calculation’s Text and Array Functions demo and learn more about the TEXTSPLIT function.
Invoke the Text to Columns Wizard in WinForms Apps
Spread.NET provides multiple ways for users to access the powerful built-in Excel-like Convert Text to Column Wizard dialog. You can either invoke the wizard programmatically using the BuiltInDialogs classor let users access it through the Spread Designeror the Ribbon Controlduring runtime with no extra code required.
Programmatically Invoking the Convert Text to Columns Wizard
The BuiltInDialogs class allows you to call TextToColums to invoke the built-in Excel-like Convert Text to Columns Wizard within your .NET WinForms app. This enables users to manually parse and format data during runtime.
C#:
private void OnTextToColumnMenuItemClick(object sender, EventArgs e)
{
var textToColumnDialog = FarPoint.Win.Spread.Dialogs.BuiltInDialogs.TextToColumns(_fpSpread);
if (textToColumnDialog != null)
textToColumnDialog.Show(_fpSpread);
}
VB.NET:
Private Sub OnTextToColumnMenuItemClick(ByVal sender As Object, ByVal e As EventArgs)
Dim textToColumnDialog = FarPoint.Win.Spread.Dialogs.BuiltInDialogs.TextToColumns(_fpSpread)
If textToColumnDialog IsNot Nothing Then textToColumnDialog.Show(_fpSpread)
End Sub
Download the Spread.NET WinForms Demo Explorer and check out the Worksheet’s Text to Column demo to learn more.
No-Code Solution with Spread Designer and Ribbon Control
Spread.NET also includes a Spread Designer and a Ribbon Control, which provide users with a simple, code-free way to access the Text to Columns functionality. These tools feature a Text to Columns button in the Data Tools tab, allowing users to split text directly within the application at runtime.
The below depicts the C# .NET Ribbon Control invoking the Text to Columns Wizard during the app’s runtime.
Both approaches enable a seamless experience, whether you're automating the process programmatically or giving users control via a familiar UI.
Learn More About C# .NET Spreadsheet Components
This article only scratches the surface of the full capabilities of Spread.NET, the .NET C# spreadsheet component. Review the documentation to see some of the many available features, and download our demo explorer to see the features in action and interact with the sample code. Integrating a spreadsheet component into your applications allows you to customize your users' experience and provide them with familiar spreadsheet functionality without referring them to an external program. To learn more about Spread.NET and the new features added in the latest release, check out our release pages.
Top comments (0)