C#/vb.net Excel Data breakdown

C#/vb.net Excel Data breakdown

Sometimes we need to split the combined data saved in Excel cells into multiple columns (such as splitting the full name into two columns) to make it easier for us to process, remember, or save. To avoid duplication and a lot of manual input work, Excel provides us with a data-disaggregated function. The following diagram shows how to implement data disaggregation in Excel:

This article will show you how to use C # and vb.net programming to achieve the functionality of Excel data disaggregation.

Steps and Code:

The first step: Create a new Workbook class object and load the Excel document.

New Workbook (); LoadFromFile (" test. xlsx");

Step Two: get the worksheet in your Excel document that contains the data you want to split, and here is the first worksheet.

Worksheet sheet = Book. worksheets[0];

Step three: split the data into multiple columns by separating the symbols.

Initializes a string array and a string, iterates through the specified rows in the worksheet, divides the column with the data as the full name by separating the columns into first and last names, saves the results to a string array, and assigns the contents of the array to the other two columns of the worksheet.

string[] Splittext =NULL;stringText =NULL; for(inti =1; I < sheet. LastRow; i++) {text= Sheet. Range[i +1,1].    Text; Splittext= text. Split ('·');  for(intj =0; J < Splittext.length; J + +) {sheet. Range[i+1,1+ j +1]. Text =Splittext[j]; }} 

Fourth Step: Save the document.

Book. SaveToFile (" output. xlsx", excelversion.version2010);

Run the code to get the following result:

All code:

C#:

usingSpire.xls;namespacesplit_column_data_to_several_columns{classProgram {Static voidMain (string[] args) {Workbook Book=NewWorkbook (); Book. LoadFromFile ("test. xlsx"); Worksheet sheet= Book. worksheets[0]; string[] Splittext =NULL; stringText =NULL;  for(inti =1; I < sheet. LastRow; i++) {text= Sheet. Range[i +1,1].                Text; Splittext= text. Split ('·');  for(intj =0; J < Splittext.length; J + +) {sheet. Range[i+1,1+ j +1]. Text =Splittext[j]; }} book. SaveToFile ("output. xlsx", excelversion.version2010); }    }}

Vb. NET:

Imports spire.xlsnamespace split_column_data_to_several_columns Class Program                                                Private Shared Sub Main (args as String ()) Dim book as New Workbook () Book. LoadFromFile ("test. xlsx") Dim sheet as Worksheet= Book. Worksheets (0) Dim Splittext as String ()=Nothing Dim text as String=Nothing for I as Integer=1to sheet. LastRow-1text= Sheet. Range (i +1,1). Text Splittext= text. Split ("·"C) for J as Integer=0To Splittext.length-1sheet. Range (i+1,1+ j +1). Text =Splittext (j) Next Next Book. SaveToFile ("output. xlsx", excelversion.version2010) End Sub End Classend Namespace

(Note: I use a free Excel component here)

C#/vb.net Excel Data breakdown