C#/vb.net Excel Data breakdown

Source: Internet
Author: User

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

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.