How to Wrap Text in Excel Cell with C#/VB.NET

INTRODUCTION

Generally speaking, if information in one cell is a long sentence or formed with several sentences, it will be displayed within one line by default and the contents over the width of cell will not be displayed if there are data in next cell. In order to have all the contents presented, Excel provides users with function to wrap text in cell.

After setting to wrap text, contents in one cell will be put in several lines according to cell size. When merging cells, this function is often used because contents in merged cell may be a summary of data information.

In this post, I want to share the method to wrap text in Excel with C#/VB.NET.

BACKGROUND

There is an Excel file which saves students information of one class. In Additional column, length of some information is longer than column width so that I will wrap text for these cells. And then set format for cells in which text has been wrapped.

Original File

In order to realize this function more easily and quickly, I use a .NET Excel component, Spire.XLS for .NET in this example. Therefore, at the beginning, I add its dll file as reference in my project.

STEPS

I. Load Excel file which I want to wrap text from computer and initialize worksheet.

C#

            Workbook workbook = new Workbook();
            workbook.LoadFromFile(@”E:\work\Documents\student info.xlsx”);
            Worksheet sheet = workbook.Worksheets[0];

VB.NET

            Dim workbook As New Workbook()
            workbook.LoadFromFile(“E:\work\Documents\student info.xlsx”)
            Dim sheet As Worksheet = workbook.Worksheets(0)

II. Confirm cell range and assign true value for sheet.Range[].IsWrapText.

C#

            sheet.Range[“G3″].IsWrapText = true;
            sheet.Range[“G11″].IsWrapText = true;

VB.NET

            sheet.Range(“G3″).IsWrapText = True
            sheet.Range(“G11″).IsWrapText = True

III. Set format for cells in which text has been wrapped, including background color. Then, set row height and column width to make the appearance better.

C#

            sheet.Range[“G3″].Style.Color = Color.SpringGreen;
            sheet.Range[“G11″].Style.Color = Color.DeepSkyBlue;

            sheet.Range[“G11″].RowHeight = 30;
            sheet.Range[“G11″].ColumnWidth = 40;

VB.NET

            sheet.Range(“G3″).Style.Color = Color.SpringGreen
            sheet.Range(“G11″).Style.Color = Color.DeepSkyBlue

            sheet.Range(“G11″).RowHeight = 30
            sheet.Range(“G11″).ColumnWidth = 40

IV. Save this document with another name by using workbook.SaveToFile() method. Then launch it.

C#

            workbook.SaveToFile(“wraptext.xlsx”, ExcelVersion.Version2010);
            System.Diagnostics.Process.Start(“wraptext.xlsx”);

VB.NET

            workbook.SaveToFile(“wraptext.xlsx”, ExcelVersion.Version2010)
            System.Diagnostics.Process.Start(“wraptext.xlsx”)

RESULT

Freely Download Spire.XLS for .NET

About these ads

One thought on “How to Wrap Text in Excel Cell with C#/VB.NET

  1. I’m curious to find out what blog system you happen to be working with?
    I’m having some small security issues with my latest blog and I would like to find something more
    safeguarded. Do you have any recommendations?

    Reply

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s